/plugins/dbmigrate/adapters/MicrosoftSQLServer.cfc

 1<cfcomponent extends="Abstract">
 2
 3	<cfset variables.sqlTypes = {}>
 4	<cfset variables.sqlTypes['primaryKey'] = "INT NOT NULL IDENTITY(1,1) PRIMARY KEY">
 5	<cfset variables.sqlTypes['binary'] = {name='IMAGE'}>
 6	<cfset variables.sqlTypes['boolean'] = {name='BIT'}>
 7	<cfset variables.sqlTypes['date'] = {name='DATETIME'}>
 8	<cfset variables.sqlTypes['datetime'] = {name='DATETIME'}>
 9	<cfset variables.sqlTypes['decimal'] = {name='DECIMAL'}>
10	<cfset variables.sqlTypes['float'] = {name='FLOAT'}>
11	<cfset variables.sqlTypes['integer'] = {name='INT'}>
12	<cfset variables.sqlTypes['string'] = {name='VARCHAR',limit=255}>
13	<cfset variables.sqlTypes['text'] = {name='TEXT'}>
14	<cfset variables.sqlTypes['time'] = {name='DATETIME'}>
15	<cfset variables.sqlTypes['timestamp'] = {name='DATETIME'}>
16
17	<cffunction name="adapterName" returntype="string" access="public" hint="name of database adapter">
18		<cfreturn "MicrosoftSQLServer">
19	</cffunction>
20
21	<!---  SQL Server uses square brackets to escape table and column names --->
22	<cffunction name="quoteTableName" returntype="string" access="public" hint="surrounds table or index names with quotes">
23		<cfargument name="name" type="string" required="true" hint="column name">
24		<cfreturn "[#Replace(arguments.name,".","`.`","ALL")#]">
25	</cffunction>
26
27	<cffunction name="quoteColumnName" returntype="string" access="public" hint="surrounds column names with quotes">
28		<cfargument name="name" type="string" required="true" hint="column name">
29		<cfreturn "[#arguments.name#]">
30	</cffunction>
31
32	<!--- createTable - use default --->
33	
34	<cffunction name="renameTable" returntype="string" access="public" hint="generates sql to rename a table">
35		<cfargument name="oldName" type="string" required="true" hint="old table name">
36		<cfargument name="newName" type="string" required="true" hint="new table name">
37		<cfreturn "EXEC sp_rename '#arguments.oldName#', '#arguments.newName#'">
38	</cffunction>
39
40	<cffunction name="dropTable" returntype="string" access="public" hint="generates sql to drop a table">
41		<cfargument name="name" type="string" required="true" hint="table name">
42		<cfreturn "IF EXISTS(SELECT name FROM sysobjects WHERE name = N'#LCase(arguments.name)#' AND xtype='U') DROP TABLE #quoteTableName(LCase(arguments.name))#">
43	</cffunction>
44	
45	<cffunction name="addColumnToTable" returntype="string" access="public" hint="generates sql to add a new column to a table">
46		<cfargument name="name" type="string" required="true" hint="table name">
47		<cfargument name="column" type="any" required="true" hint="column definition object">
48		<cfreturn "ALTER TABLE #quoteTableName(LCase(arguments.name))# ADD #arguments.column.toSQL()#">
49	</cffunction>
50	
51	<cffunction name="changeColumnInTable" returntype="string" access="public" hint="generates sql to change an existing column in a table">
52		<cfargument name="name" type="string" required="true" hint="table name">
53		<cfargument name="column" type="any" required="true" hint="column definition object">
54		<cfreturn "ALTER TABLE #quoteTableName(LCase(arguments.name))# ALTER COLUMN #arguments.column.toSQL()#">
55	</cffunction>
56	
57	<cffunction name="renameColumnInTable" returntype="string" access="public" hint="generates sql to rename an existing column in a table">
58		<cfargument name="name" type="string" required="true" hint="table name">
59		<cfargument name="columnName" type="string" required="true" hint="old column name">
60		<cfargument name="newColumnName" type="string" required="true" hint="new column name">
61		<cfreturn "EXEC sp_rename '#LCase(arguments.name)#.#arguments.columnName#', '#arguments.newColumnName#'">
62	</cffunction>
63	
64	<!--- dropColumnFromTable - use default --->
65	
66	<!--- addForeignKeyToTable - use default --->
67	
68	<cffunction name="dropForeignKeyFromTable" returntype="string" access="public" hint="generates sql to add a foreign key constraint to a table">
69		<cfargument name="name" type="string" required="true" hint="table name">
70		<cfargument name="keyName" type="any" required="true" hint="foreign key name">
71		<cfreturn "ALTER TABLE #quoteTableName(LCase(arguments.name))# DROP CONSTRAINT #arguments.keyname#">
72	</cffunction>
73	
74	<!--- foreignKeySQL - use default --->
75	
76	<!--- addIndex - use default --->
77	
78	<cffunction name="removeIndex" returntype="string" access="public" hint="generates sql to remove a database index">
79		<cfargument name="table" type="string" required="true" hint="table name">
80		<cfargument name="indexName" type="string" required="false" default="" hint="index name">
81		<cfreturn "DROP INDEX #arguments.table#.#quoteColumnName(arguments.indexName)#">
82	</cffunction>
83
84</cfcomponent>