/plugins/dbmigrate/basefunctions.cfm

  1<!--- we include wheels global functions for:-
  2	pluralize
  3	model
  4	$file
  5	$dbinfo
  6	 --->
  7<cfinclude template="../../wheels/global/functions.cfm">
  8
  9<cffunction name="announce" access="public">
 10	<cfargument name="message" type="string" required="yes">
 11	<cfparam name="Request.migrationOutput" default="">
 12	<cfset Request.migrationOutput = Request.migrationOutput & arguments.message & chr(13)>
 13</cffunction>
 14
 15<cffunction name="$execute" access="private">
 16	<cfargument name="sql" type="string" required="yes">
 17	<cfscript>
 18	// trim and remove trailing semicolon (appears to cause problems for Oracle thin client JDBC driver)
 19	arguments.sql = REReplace(trim(arguments.sql),";$","","ONE");
 20	if(IsDefined("Request.migrationSQLFile")) {
 21		$file(action="append",file=Request.migrationSQLFile,output="#arguments.sql#;",addNewLine="yes",fixNewLine="yes");
 22	}
 23	</cfscript>
 24	<cfquery datasource="#application.wheels.dataSourceName#" username="#application.wheels.dataSourceUserName#" password="#application.wheels.dataSourcePassword#">
 25	#PreserveSingleQuotes(arguments.sql)#
 26	</cfquery>
 27</cffunction>
 28
 29<cffunction name="$getDBType" returntype="string" access="private" output="false">
 30	<cfscript>
 31	var loc = {};
 32	loc.info = $dbinfo(type="version",datasource=application.wheels.dataSourceName,username=application.wheels.dataSourceUserName,password=application.wheels.dataSourcePassword);
 33	if (loc.info.driver_name Contains "SQLServer" || loc.info.driver_name Contains "Microsoft SQL Server")
 34		loc.adapterName = "MicrosoftSQLServer";
 35	else if (loc.info.driver_name Contains "MySQL")
 36		loc.adapterName = "MySQL";
 37	else if (loc.info.driver_name Contains "Oracle")
 38		loc.adapterName = "Oracle";
 39	else if (loc.info.driver_name Contains "PostgreSQL")
 40		loc.adapterName = "PostgreSQL";
 41	else if (loc.info.driver_name Contains "SQLite")
 42			loc.adapterName = "SQLite";
 43	else {
 44		loc.adapterName = "";
 45	}
 46	</cfscript>
 47	<cfreturn loc.adapterName>
 48</cffunction>
 49
 50<cffunction name="$getForeignKeys" returntype="string" access="private" output="false">
 51	<cfargument name="table" type="string" required="yes">
 52	<cfscript>
 53	var loc = {};
 54	loc.foreignKeys = $dbinfo(type="foreignkeys",table=arguments.table,datasource=application.wheels.dataSourceName,username=application.wheels.dataSourceUserName,password=application.wheels.dataSourcePassword);
 55	loc.foreignKeyList = ValueList(loc.foreignKeys.FKCOLUMN_NAME);
 56	</cfscript>
 57	<cfreturn loc.foreignKeyList>
 58</cffunction>
 59
 60<cffunction name="$getColumns" returntype="string" access="public" output="false">
 61	<cfargument name="tableName" type="string" required="yes" hint="table name">
 62	<cfset var loc = {}>
 63  	<cfif $getDBType() eq "Oracle">
 64  		<!--- oracle thin client jdbc throws error when usgin cfdbinfo to access column data --->
 65  		<!--- because of this error wheels can't load models anyway so maybe we don't need to support this driver --->
 66  		<cfquery name="loc.columns" datasource="#application.wheels.dataSourceName#" username="#application.wheels.dataSourceUserName#" password="#application.wheels.dataSourcePassword#">
 67  		SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '#this.name#'
 68  		</cfquery>
 69  	<cfelse>
 70  		<!--- use cfdbinfo --->
 71  		<cfset loc.columns = $dbinfo(type="columns",table=arguments.tableName,datasource=application.wheels.dataSourceName,username=application.wheels.dataSourceUserName,password=application.wheels.dataSourcePassword)>
 72  	</cfif>
 73	<cfreturn ValueList(loc.columns.COLUMN_NAME)>
 74</cffunction>
 75
 76<cffunction name="$getColumnDefinition" returntype="string" access="private">
 77	<cfargument name="tableName" type="string" required="yes" hint="table name">
 78	<cfargument name="columnName" type="string" required="yes" hint="column name">
 79	<cfset var loc = {}>
 80	<cfdbinfo name="loc.columns" type="columns" table="#arguments.tableName#" datasource="#application.wheels.dataSourceName#" username="#application.wheels.dataSourceUserName#" password="#application.wheels.dataSourcePassword#">
 81    <!--- <cfdump var="#loc#" abort> --->
 82	<cfscript>
 83	loc.columnDefinition = "";
 84	loc.iEnd = loc.columns.RecordCount;
 85	for (loc.i=1; loc.i <= loc.iEnd; loc.i++) {
 86		if(loc.columns["COLUMN_NAME"][loc.i] == arguments.columnName) {
 87			loc.columnType = loc.columns["TYPE_NAME"][loc.i];
 88			loc.columnDefinition = loc.columnType;
 89			if(ListFindNoCase("char,varchar,int,bigint,smallint,tinyint,binary,varbinary",loc.columnType)) {
 90				loc.columnDefinition = loc.columnDefinition & "(#loc.columns["COLUMN_SIZE"][loc.i]#)";
 91			} else if(ListFindNoCase("decimal,float,double",loc.columnType)) {
 92				loc.columnDefinition = loc.columnDefinition & "(#loc.columns["COLUMN_SIZE"][loc.i]#,#loc.columns["DECIMAL_DIGITS"][loc.i]#)";
 93			}
 94			if(loc.columns["IS_NULLABLE"][loc.i]) {
 95				loc.columnDefinition = loc.columnDefinition & " NULL";
 96			} else {
 97				loc.columnDefinition = loc.columnDefinition & " NOT NULL";
 98			}
 99			if(Len(loc.columns["COLUMN_DEFAULT_VALUE"][loc.i]) == 0) {
100				loc.columnDefinition = loc.columnDefinition & " DEFAULT NULL";
101			} else if(ListFindNoCase("char,varchar,binary,varbinary",loc.columnType)) {
102				loc.columnDefinition = loc.columnDefinition & " DEFAULT '#loc.columns["COLUMN_DEFAULT_VALUE"][loc.i]#'";
103			} else if(ListFindNoCase("int,bigint,smallint,tinyint,decimal,float,double",loc.columnType)) {
104				loc.columnDefinition = loc.columnDefinition & " DEFAULT #loc.columns["COLUMN_DEFAULT_VALUE"][loc.i]#";
105			}
106			break;
107		}
108	}
109	</cfscript>
110	<cfreturn loc.columnDefinition>
111</cffunction>