|
Programmatically adding a datasource to cf server |
In the midsts of developing a new project I stumbled upon the need for administrators to be able to create new instances of the entire application, on the fly.
This threw up a few issues, primarily how do we re-create the generic data structure, but with specific names etc for a new instance.
I figured that you can do it in cf admin, so it must be do-able right? Handily there is an article on this in the coldFusion Cookbook (here.)
So expanding on that article we can use the same code, but populate it with variables to create the datasource:
2// Login is always required.
3createObject("component","cfide.adminapi.administrator").login("admin");
4
5// Instantiate the data source object.
6myObj = createObject("component","cfide.adminapi.datasource");
7
8// Required arguments for a data source.
9stDSN = structNew();
10stDSN.driver = "MSSQLServer";
11stDSN.name="#variables.dbName#";
12stDSN.host = "#variables.dbServer#";
13stDSN.port = "1433";
14stDSN.database = "#variables.dbName#";
15stDSN.username = "#variables.userName#";
16stDSN.password = "#variables.password#";
17
18// Optional and advanced arguments.
19stDSN.login_timeout = "29";
20stDSN.timeout = "23";
21stDSN.interval = 6;
22stDSN.buffer = "64000";
23stDSN.blob_buffer = "64000";
24stDSN.setStringParameterAsUnicode = "false";
25stDSN.description = "#variables.dbName# intranet datasource, auto generated.";
26stDSN.pooling = true;
27stDSN.maxpooledstatements = 999;
28stDSN.enableMaxConnections = "true";
29stDSN.maxConnections = "299";
30stDSN.enable_clob = true;
31stDSN.enable_blob = true;
32stDSN.disable = false;
33stDSN.storedProc = true;
34stDSN.alter = false;
35stDSN.grant = true;
36stDSN.select = true;
37stDSN.update = true;
38stDSN.create = true;
39stDSN.delete = true;
40stDSN.drop = false;
41stDSN.revoke = false;
42
43//Create a DSN.
44myObj.setMSSQL(argumentCollection=stDSN);
45</cfscript>
Then simply run a script to create the actual database.
2USE [master]
3
4CREATE DATABASE [#variables.dbName#] ON PRIMARY
5( NAME = N'#variables.dbName#_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\#variables.dbName#_Data.MDF' , SIZE = 1792KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
6 LOG ON
7( NAME = N'#variables.dbName#_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\#variables.dbName#_Log.LDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
8
9EXEC dbo.sp_dbcmptlevel @dbname=N'#variables.dbName#', @new_cmptlevel=80
10
11IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
12begin
13EXEC [#variables.dbName#].[dbo].[sp_fulltext_database] @action = 'disable'
14end
15</cfquery>
Note that this is based on using SQL server, and you need a datasource in cf admin configured for the 'Master' datasource, as thats where the other datasource references are held.