PageRenderTime 9ms CodeModel.GetById 2ms app.highlight 2ms RepoModel.GetById 2ms app.codeStats 0ms

/wheels/model/adapters/MicrosoftSQLServer.cfc

http://cfwheels.googlecode.com/
ColdFusion CFScript | 167 lines | 139 code | 18 blank | 10 comment | 13 complexity | 848637d62ed7b3d96d79e2646b3a0fb1 MD5 | raw file
  1<cfcomponent extends="Base" output="false">
  2
  3	<cffunction name="$generatedKey" returntype="string" access="public" output="false">
  4		<cfreturn "identitycol">
  5	</cffunction>
  6	
  7	<cffunction name="$randomOrder" returntype="string" access="public" output="false">
  8		<cfreturn "NEWID()">
  9	</cffunction>
 10	
 11	<cffunction name="$getType" returntype="string" access="public" output="false">
 12		<cfargument name="type" type="string" required="true">
 13		<cfscript>
 14			var loc = {};
 15			switch(arguments.type)
 16			{
 17				case "bigint": {loc.returnValue = "cf_sql_bigint"; break;}
 18				case "binary": case "timestamp": {loc.returnValue = "cf_sql_binary"; break;}
 19				case "bit": {loc.returnValue = "cf_sql_bit"; break;}
 20				case "char": case "nchar": case "uniqueidentifier": {loc.returnValue = "cf_sql_char"; break;}
 21				case "date": {loc.returnValue = "cf_sql_date"; break;}
 22				case "datetime": case "datetime2": case "smalldatetime": {loc.returnValue = "cf_sql_timestamp"; break;}
 23				case "decimal": case "money": case "smallmoney": {loc.returnValue = "cf_sql_decimal"; break;}
 24				case "float": {loc.returnValue = "cf_sql_float"; break;}
 25				case "int": {loc.returnValue = "cf_sql_integer"; break;}
 26				case "image": {loc.returnValue = "cf_sql_longvarbinary"; break;}
 27				case "text": case "ntext": case "xml": {loc.returnValue = "cf_sql_longvarchar";	break;}
 28				case "numeric":	{loc.returnValue = "cf_sql_numeric"; break;}
 29				case "real": {loc.returnValue = "cf_sql_real"; break;}
 30				case "smallint": {loc.returnValue = "cf_sql_smallint"; break;}
 31				case "time": {loc.returnValue = "cf_sql_time"; break;}
 32				case "tinyint":	{loc.returnValue = "cf_sql_tinyint"; break;}
 33				case "varbinary": {loc.returnValue = "cf_sql_varbinary"; break;}
 34				case "varchar": case "nvarchar": {loc.returnValue = "cf_sql_varchar"; break;}
 35			}
 36		</cfscript>
 37		<cfreturn loc.returnValue>
 38	</cffunction>
 39	
 40	<cffunction name="$query" returntype="struct" access="public" output="false">
 41		<cfargument name="sql" type="array" required="true">
 42		<cfargument name="limit" type="numeric" required="false" default=0>
 43		<cfargument name="offset" type="numeric" required="false" default=0>
 44		<cfargument name="parameterize" type="boolean" required="true">
 45		<cfargument name="$primaryKey" type="string" required="false" default="">
 46		<cfscript>
 47			var loc = {};
 48	
 49			if (arguments.limit + arguments.offset gt 0)
 50			{
 51				loc.containsGroup = false;
 52				loc.afterWhere = "";
 53	
 54				if (IsSimpleValue(arguments.sql[ArrayLen(arguments.sql) - 1]) and FindNoCase("GROUP BY", arguments.sql[ArrayLen(arguments.sql) - 1]))
 55					loc.containsGroup = true;
 56				if (arguments.sql[ArrayLen(arguments.sql)] Contains ",")
 57				{
 58					// fix for pagination issue when ordering multiple columns with same name
 59					loc.order = arguments.sql[ArrayLen(arguments.sql)];
 60					loc.newOrder = "";
 61					loc.doneColumns = "";
 62					loc.done = 0;
 63					loc.iEnd = ListLen(loc.order);
 64					for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
 65					{
 66						loc.item = ListGetAt(loc.order, loc.i);
 67						loc.column = SpanExcluding(Reverse(SpanExcluding(Reverse(loc.item), ".")), " ");
 68						if (ListFind(loc.doneColumns, loc.column))
 69						{
 70							loc.done++;
 71							loc.item = loc.item & " AS tmp" & loc.done;
 72						}
 73						loc.doneColumns = ListAppend(loc.doneColumns, loc.column);
 74						loc.newOrder = ListAppend(loc.newOrder, loc.item);
 75					}
 76					arguments.sql[ArrayLen(arguments.sql)] = loc.newOrder;
 77				}
 78	
 79				// select clause always comes first in the array, the order by clause last, remove the leading keywords leaving only the columns and set to the ones used in the inner most sub query
 80				loc.thirdSelect = ReplaceNoCase(ReplaceNoCase(arguments.sql[1], "SELECT DISTINCT ", ""), "SELECT ", "");
 81				loc.thirdOrder = ReplaceNoCase(arguments.sql[ArrayLen(arguments.sql)], "ORDER BY ", "");
 82				if (loc.containsGroup)
 83					loc.thirdGroup = ReplaceNoCase(arguments.sql[ArrayLen(arguments.sql) - 1], "GROUP BY ", "");
 84	
 85				// the first select is the outer most in the query and need to contain columns without table names and using aliases when they exist
 86				loc.firstSelect = $columnAlias(list=$tableName(list=loc.thirdSelect, action="remove"), action="keep");
 87	
 88				// we need to add columns from the inner order clause to the select clauses in the inner two queries
 89				loc.iEnd = ListLen(loc.thirdOrder);
 90				for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
 91				{
 92					loc.iItem = ReReplace(ReReplace(ListGetAt(loc.thirdOrder, loc.i), " ASC\b", ""), " DESC\b", "");
 93					if (!ListFindNoCase(loc.thirdSelect, loc.iItem))
 94						loc.thirdSelect = ListAppend(loc.thirdSelect, loc.iItem);
 95					if (loc.containsGroup) {
 96						loc.iItem = REReplace(loc.iItem, "[[:space:]]AS[[:space:]][A-Za-z1-9]+", "", "all");
 97						if (!ListFindNoCase(loc.thirdGroup, loc.iItem))
 98							loc.thirdGroup = ListAppend(loc.thirdGroup, loc.iItem);
 99					}
100				}
101	
102				// the second select also needs to contain columns without table names and using aliases when they exist (but now including the columns added above)
103				loc.secondSelect = $columnAlias(list=$tableName(list=loc.thirdSelect, action="remove"), action="keep");
104	
105				// first order also needs the table names removed, the column aliases can be kept since they are removed before running the query anyway
106				loc.firstOrder = $tableName(list=loc.thirdOrder, action="remove");
107	
108				// second order clause is the same as the first but with the ordering reversed
109				loc.secondOrder = Replace(ReReplace(ReReplace(loc.firstOrder, " DESC\b", chr(7), "all"), " ASC\b", " DESC", "all"), chr(7), " ASC", "all");
110	
111				// fix column aliases from order by clauses
112				loc.thirdOrder = $columnAlias(list=loc.thirdOrder, action="remove");
113				loc.secondOrder = $columnAlias(list=loc.secondOrder, action="keep");
114				loc.firstOrder = $columnAlias(list=loc.firstOrder, action="keep");
115	
116				// build new sql string and replace the old one with it
117				loc.beforeWhere = "SELECT " & loc.firstSelect & " FROM (SELECT TOP " & arguments.limit & " " & loc.secondSelect & " FROM (SELECT ";
118				if (ListRest(arguments.sql[2], " ") Contains " ")
119					loc.beforeWhere = loc.beforeWhere & "DISTINCT ";
120				loc.beforeWhere = loc.beforeWhere & "TOP " & arguments.limit+arguments.offset & " " & loc.thirdSelect & " " & arguments.sql[2];
121				if (loc.containsGroup)
122					loc.afterWhere = "GROUP BY " & loc.thirdGroup & " ";
123				loc.afterWhere = "ORDER BY " & loc.thirdOrder & ") AS tmp1 ORDER BY " & loc.secondOrder & ") AS tmp2 ORDER BY " & loc.firstOrder;
124				ArrayDeleteAt(arguments.sql, 1);
125				ArrayDeleteAt(arguments.sql, 1);
126				ArrayDeleteAt(arguments.sql, ArrayLen(arguments.sql));
127				if (loc.containsGroup)
128					ArrayDeleteAt(arguments.sql, ArrayLen(arguments.sql));
129				ArrayPrepend(arguments.sql, loc.beforeWhere);
130				ArrayAppend(arguments.sql, loc.afterWhere);
131	
132			}
133			else
134			{
135				arguments.sql = $removeColumnAliasesInOrderClause(arguments.sql);
136			}
137			// sql server doesn't support limit and offset in sql
138			StructDelete(arguments, "limit", false);
139			StructDelete(arguments, "offset", false);
140			loc.returnValue = $performQuery(argumentCollection=arguments);
141		</cfscript>
142		<cfreturn loc.returnValue>
143	</cffunction>
144	
145	<cffunction name="$identitySelect" returntype="any" access="public" output="false">
146		<cfargument name="queryAttributes" type="struct" required="true">
147		<cfargument name="result" type="struct" required="true">
148		<cfargument name="primaryKey" type="string" required="true">
149		<cfset var loc = {}>
150		<cfset var query = {}>
151		<cfset loc.sql = Trim(arguments.result.sql)>
152		<cfif Left(loc.sql, 11) IS "INSERT INTO" AND NOT StructKeyExists(arguments.result, $generatedKey())>
153			<cfset loc.startPar = Find("(", loc.sql) + 1>
154			<cfset loc.endPar = Find(")", loc.sql)>
155			<cfset loc.columnList = ReplaceList(Mid(loc.sql, loc.startPar, (loc.endPar-loc.startPar)), "#Chr(10)#,#Chr(13)#, ", ",,")>
156			<cfif NOT ListFindNoCase(loc.columnList, ListFirst(arguments.primaryKey))>
157				<cfset loc.returnValue = {}>
158				<cfquery attributeCollection="#arguments.queryAttributes#">SELECT SCOPE_IDENTITY() AS lastId</cfquery>
159				<cfset loc.returnValue[$generatedKey()] = query.name.lastId>
160				<cfreturn loc.returnValue>
161			</cfif>
162		</cfif>
163	</cffunction>
164
165	<cfinclude template="../../plugins/injection.cfm">
166
167</cfcomponent>