dbvalidate
Example Output
This handy function allows the developer to skip the hassle of manually creating tables or adding columns. I pesonally have my modules call this function when I restart my application to check for any database changes. If I wish to add a table or add a column to a table I quickly edit the .sql script file and restart the application.

Usage: dbValidate(dbCfgFile='#getDirectoryFromPath(getCurrentTemplatePath())#example.sql',dsn=Request.Cfg.Dsn);

Source of example sql script file (example.sql)
CREATE TABLE User_Master (
	UserId int counter NOT NULL PRIMARY KEY
	, UserUid varchar(36) NOT NULL
	, UserName varchar(25) NOT NULL
	, UserPasswordHash char(32) NOT NULL
	, DateCreated datetime NOT NULL DEFAULT getDate()
	, DateLast datetime NOT NULL DEFAULT getDate()
	, UserEmail varchar(100) NULL
	, Birthdate datetime NULL
	, FailedLogin tinyint NOT NULL DEFAULT 0
)
CREATE TABLE User_Address (
	AddressUid varchar(36) NULL
	, UserUid varchar(36) NULL
	, Address1  varchar(25) NULL
	, Address2  varchar(25) NULL
	, City varchar(25) NULL
	, State varchar(15) NULL
	, Zipcode varchar(15) NULL
	, Country varchar(20) NULL
)
CREATE TABLE User_Roles (
	RoleId int counter NOT NULL PRIMARY KEY
	, RoleName varchar(15) NOT NULL
	, RoleOwner varchar(16) Null
	, RoleDesc varchar(255) NULL
)

        
Source code <div> This handy function allows the developer to skip the hassle of manually creating tables or adding columns. I pesonally have my modules call this function when I restart my application to check for any database changes. If I wish to add a table or add a column to a table I quickly edit the .sql script file and restart the application. </div> <br> <span style="font-weight:bold;">Usage:</span> dbValidate(dbCfgFile='#getDirectoryFromPath(getCurrentTemplatePath())#example.sql',dsn=Request.Cfg.Dsn); <br><br> <div class="Source"> <span class="Label">Source of example sql script file (example.sql)</span> <pre> <cfinclude template="example.sql"> </pre> </div> <!--- Function below ---> <cffunction name="dbValidate"> <cfargument name="dbCfgFile" required="Yes" type="string" /> <cfargument name="Dsn" required="Yes" type="string" /> <cfset var Local = structNew() /> <cfset var tbl = structNew() /> <!--- Read sql script file to variable ---> <cffile action="READ" file="#Arguments.dbCfgFile#" variable="Local.Sql"> <!--- Create array of tables to deal with on individual basis ---> <cfset Local.Tables = Local.Sql.trim().replace('#chr(10)#)','#chr(10)#)~!~').split('~!~') /> <!--- Loop thru tables array ---> <cfloop array="#Local.Tables#" index="tbl.Sql"> <cfset tbl.Name = listLast(listFirst(tbl.Sql,'('),' ') /> <!--- Create array of columns for formatting and/or alter table adds ---> <cfset tbl.Columns = listRest(tbl.Sql,'(').split(',|#chr(10)#\)') /> <!--- Change/fix any datatype conversions that may have been used with other drivers ---> <cfloop from="1" to="#arrayLen(tbl.Columns)#" index="Local.iCol"> <cfswitch expression="#getToken(tbl.Columns[local.iCol],2,' ')#"> <cfcase value="integer"> <cfset tbl.Columns[Local.iCol] = listSetAt(tbl.Columns[Local.iCol],2,'integer',' ')/> </cfcase> <cfcase value="memo"> <cfset tbl.Columns[Local.iCol] = listSetAt(tbl.Columns[Local.iCol],2,'varchar(max)',' ')/> </cfcase> <cfcase value="Currency"> <cfset tbl.Columns[Local.iCol] = listSetAt(tbl.Columns[Local.iCol],2,'Money',' ')/> </cfcase> <cfcase value="long"> <cfset tbl.Columns[Local.iCol] = listSetAt(tbl.Columns[Local.iCol],2,'bigint',' ')/> </cfcase> <cfcase value="byte"> <cfset tbl.Columns[Local.iCol] = listSetAt(tbl.Columns[Local.iCol],2,'tinyint',' ')/> </cfcase> <cfcase value="decimal"> <cfset tbl.Columns[Local.iCol] = listSetAt(tbl.Columns[Local.iCol],2,'float',' ')/> </cfcase> <cfcase value="double"> <cfset tbl.Columns[Local.iCol] = listSetAt(tbl.Columns[Local.iCol],2,'real',' ')/> </cfcase> </cfswitch> </cfloop> <!--- Query table to get table and column info ---> <cfquery name="Local.Qry.Cols" datasource="#Arguments.Dsn#"> SELECT column_name 'ColumnName' , data_type 'DataType' , Character_maximum_length 'MaximumLength' FROM information_schema.columns WHERE table_name = '#tbl.Name#' </cfquery> <cfif Local.Qry.Cols.RecordCount> <!--- IF table exists, validate columns exist ---> <cfset Local.ColNames = valueList(Local.Qry.Cols.ColumnName) /> <cfset Local.ColAdds = arrayNew(1) /> <!--- Contains columns to add ---> <cfloop array="#tbl.Columns#" index="iCol"> <cfif NOT listFindNoCase(Local.ColNames,trim(getToken(iCol,1,' ')))> <!--- If column is missing, add it to que ---> <cfset arrayAppend(Local.ColAdds,iCol) /> </cfif> </cfloop> <!--- /tbl.Columns ---> <cfif arrayLen(Local.ColAdds)> <!--- if columns need adding ---> <cfquery name="Local.Qry.Alter.#tbl.Name#" datasource="#Arguments.Dsn#"> ALTER TABLE #tbl.Name# ADD #arrayToList(Local.ColAdds)# </cfquery> </cfif> <!--- arrayLen(Local.ColAdds) ---> <cfelse> <!--- if table does not exist, create it ---> <cfquery name="Local.Qry.Create.#tbl.Name#" datasource="#Arguments.Dsn#"> CREATE TABLE #tbl.Name# ( #arrayToList(tbl.Columns)#) </cfquery> </cfif> <!--- /Qry.Cols.RecordCount ---> </cfloop> <!--- /Local.Tables ---> </cffunction>
© 2017 usaRaydar
v.
IP:23.20.86.177 css:/assets/css/raycomweb.css?v=16021511