PageRenderTime 210ms CodeModel.GetById 81ms app.highlight 7ms RepoModel.GetById 113ms app.codeStats 1ms

/wheels/model/sql.cfm

http://cfwheels.googlecode.com/
ColdFusion | 744 lines | 707 code | 37 blank | 0 comment | 158 complexity | 8cb2403daa19ba81182660cf5e9b94bb MD5 | raw file
  1<cffunction name="$addDeleteClause" returntype="array" access="public" output="false">
  2	<cfargument name="sql" type="array" required="true">
  3	<cfargument name="softDelete" type="boolean" required="true">
  4	<cfscript>
  5		var loc = {};
  6		if (variables.wheels.class.softDeletion && arguments.softDelete)
  7		{
  8			ArrayAppend(arguments.sql, "UPDATE #tableName()# SET #variables.wheels.class.softDeleteColumn# = ");
  9			loc.param = {value=Now(), type="cf_sql_timestamp"};
 10			ArrayAppend(arguments.sql, loc.param);
 11		}
 12		else
 13		{
 14			ArrayAppend(arguments.sql, "DELETE FROM #tableName()#");
 15		}
 16	</cfscript>
 17	<cfreturn arguments.sql>
 18</cffunction>
 19
 20<cffunction name="$fromClause" returntype="string" access="public" output="false">
 21	<cfargument name="include" type="string" required="true">
 22	<cfscript>
 23		var loc = {};
 24
 25		// start the from statement with the SQL keyword and the table name for the current model
 26		loc.returnValue = "FROM " & tableName();
 27
 28		// add join statements if associations have been specified through the include argument
 29		if (Len(arguments.include))
 30		{
 31			// get info for all associations
 32			loc.associations = $expandedAssociations(include=arguments.include);
 33
 34			// add join statement for each include separated by space
 35			loc.iEnd = ArrayLen(loc.associations);
 36			for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
 37				loc.returnValue = ListAppend(loc.returnValue, loc.associations[loc.i].join, " ");
 38		}
 39	</cfscript>
 40	<cfreturn loc.returnValue>
 41</cffunction>
 42
 43<cffunction name="$addKeyWhereClause" returntype="array" access="public" output="false">
 44	<cfargument name="sql" type="array" required="true">
 45	<cfscript>
 46		var loc = {};
 47		ArrayAppend(arguments.sql, " WHERE ");
 48		loc.iEnd = ListLen(primaryKeys());
 49		for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
 50		{
 51			loc.key = primaryKeys(loc.i);
 52			ArrayAppend(arguments.sql, "#variables.wheels.class.properties[loc.key].column# = ");
 53			if (hasChanged(loc.key))
 54				loc.value = changedFrom(loc.key);
 55			else
 56				loc.value = this[loc.key];
 57			if (Len(loc.value))
 58				loc.null = false;
 59			else
 60				loc.null = true;
 61			loc.param = {value=loc.value, type=variables.wheels.class.properties[loc.key].type, dataType=variables.wheels.class.properties[loc.key].dataType, scale=variables.wheels.class.properties[loc.key].scale, null=loc.null};
 62			ArrayAppend(arguments.sql, loc.param);
 63			if (loc.i < loc.iEnd)
 64				ArrayAppend(arguments.sql, " AND ");
 65		}
 66	</cfscript>
 67	<cfreturn arguments.sql>
 68</cffunction>
 69
 70<cffunction name="$orderByClause" returntype="string" access="public" output="false">
 71	<cfargument name="order" type="string" required="true">
 72	<cfargument name="include" type="string" required="true">
 73	<cfscript>
 74		var loc = {};
 75		loc.returnValue = "";
 76		if (Len(arguments.order))
 77		{
 78			if (arguments.order == "random")
 79			{
 80				loc.returnValue = variables.wheels.class.adapter.$randomOrder();
 81			}
 82			else if (arguments.order Contains "(")
 83			{
 84				loc.returnValue = arguments.order;
 85			}
 86			else
 87			{
 88				// setup an array containing class info for current class and all the ones that should be included
 89				loc.classes = [];
 90				if (Len(arguments.include))
 91					loc.classes = $expandedAssociations(include=arguments.include);
 92				ArrayPrepend(loc.classes, variables.wheels.class);
 93
 94				loc.returnValue = "";
 95				loc.iEnd = ListLen(arguments.order);
 96				for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
 97				{
 98					loc.iItem = Trim(ListGetAt(arguments.order, loc.i));
 99					if (!FindNoCase(" ASC", loc.iItem) && !FindNoCase(" DESC", loc.iItem))
100						loc.iItem = loc.iItem & " ASC";
101					if (loc.iItem Contains ".")
102					{
103						loc.returnValue = ListAppend(loc.returnValue, loc.iItem);
104					}
105					else
106					{
107						loc.property = ListLast(SpanExcluding(loc.iItem, " "), ".");
108						loc.jEnd = ArrayLen(loc.classes);
109						for (loc.j=1; loc.j <= loc.jEnd; loc.j++)
110						{
111							loc.toAdd = "";
112							loc.classData = loc.classes[loc.j];
113
114							// we need the name of the table on the first go, and the alias otherwise
115							loc.tableName = loc.classData.tableName;
116							if (loc.j != 1)
117								loc.tableName = loc.classData.alias;
118
119							if (ListFindNoCase(loc.classData.propertyList, loc.property))
120								loc.toAdd = loc.tableName & "." & loc.classData.properties[loc.property].column;
121							else if (ListFindNoCase(loc.classData.calculatedPropertyList, loc.property))
122								loc.toAdd = Replace(loc.classData.calculatedProperties[loc.property].sql, ",", "[[comma]]", "all");
123							if (Len(loc.toAdd))
124							{
125								if (!ListFindNoCase(loc.classData.columnList, loc.property))
126									loc.toAdd = loc.toAdd & " AS " & loc.property;
127								loc.toAdd = loc.toAdd & " " & UCase(ListLast(loc.iItem, " "));
128								if (!ListFindNoCase(loc.returnValue, loc.toAdd))
129								{
130									loc.returnValue = ListAppend(loc.returnValue, loc.toAdd);
131									break;
132								}
133							}
134						}
135						if (application.wheels.showErrorInformation && !Len(loc.toAdd))
136							$throw(type="Wheels.ColumnNotFound", message="Wheels looked for the column mapped to the `#loc.property#` property but couldn't find it in the database table.", extendedInfo="Verify the `order` argument and/or your property to column mappings done with the `property` method inside the model's `init` method to make sure everything is correct.");
137					}
138				}
139			}
140			loc.returnValue = "ORDER BY " & loc.returnValue;
141		}
142	</cfscript>
143	<cfreturn loc.returnValue>
144</cffunction>
145
146<cffunction name="$groupByClause" returntype="string" access="public" output="false">
147	<cfargument name="select" type="string" required="true">
148	<cfargument name="include" type="string" required="true">
149	<cfargument name="group" type="string" required="true">
150	<cfargument name="distinct" type="boolean" required="true">
151	<cfargument name="returnAs" type="string" required="true">
152	<cfscript>
153		var returnValue = "";
154		// if we want a distinct statement, we can do it grouping every field in the select
155		if (arguments.distinct)
156			returnValue = $createSQLFieldList(list=arguments.select, include=arguments.include, returnAs=arguments.returnAs, renameFields=false, addCalculatedProperties=false);
157		else if (Len(arguments.group))
158			returnValue = $createSQLFieldList(list=arguments.group, include=arguments.include, returnAs=arguments.returnAs, renameFields=false, addCalculatedProperties=false);
159		if (Len(returnValue))
160			returnValue = "GROUP BY " & returnValue;
161	</cfscript>
162	<cfreturn returnValue>
163</cffunction>
164
165<cffunction name="$selectClause" returntype="string" access="public" output="false">
166	<cfargument name="select" type="string" required="true">
167	<cfargument name="include" type="string" required="true">
168	<cfargument name="returnAs" type="string" required="true">
169	<cfscript>
170		var returnValue = "";
171		returnValue = $createSQLFieldList(list=arguments.select, include=arguments.include, returnAs=arguments.returnAs);
172		returnValue = "SELECT " & returnValue;
173	</cfscript>
174	<cfreturn returnValue>
175</cffunction>
176
177<cffunction name="$createSQLFieldList" returntype="string" access="public" output="false">
178	<cfargument name="list" type="string" required="true">
179	<cfargument name="include" type="string" required="true">
180	<cfargument name="returnAs" type="string" required="true">
181	<cfargument name="renameFields" type="boolean" required="false" default="true">
182	<cfargument name="addCalculatedProperties" type="boolean" required="false" default="true">
183	<cfargument name="useExpandedColumnAliases" type="boolean" required="false" default="#application.wheels.useExpandedColumnAliases#">
184	<cfscript>
185		var loc = {};
186		// setup an array containing class info for current class and all the ones that should be included
187		loc.classes = [];
188		if (Len(arguments.include))
189			loc.classes = $expandedAssociations(include=arguments.include);
190		ArrayPrepend(loc.classes, variables.wheels.class);
191
192		// if the develop passes in tablename.*, translate it into the list of fields for the developer
193		// this is so we don't get *'s in the group by
194		if (Find(".*", arguments.list))
195			arguments.list = $expandProperties(list=arguments.list, classes=loc.classes);
196
197		// add properties to select if the developer did not specify any
198		if (!Len(arguments.list))
199		{
200			loc.iEnd = ArrayLen(loc.classes);
201			for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
202			{
203				loc.classData = loc.classes[loc.i];
204				arguments.list = ListAppend(arguments.list, loc.classData.propertyList);
205				if (Len(loc.classData.calculatedPropertyList))
206					arguments.list = ListAppend(arguments.list, loc.classData.calculatedPropertyList);
207			}
208		}
209
210		// go through the properties and map them to the database unless the developer passed in a table name or an alias in which case we assume they know what they're doing and leave the select clause as is
211		if (arguments.list Does Not Contain "." AND arguments.list Does Not Contain " AS ")
212		{
213			loc.list = "";
214			loc.addedProperties = "";
215			loc.addedPropertiesByModel = {};
216			loc.iEnd = ListLen(arguments.list);
217			for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
218			{
219				loc.iItem = Trim(ListGetAt(arguments.list, loc.i));
220
221				// look for duplicates
222				loc.duplicateCount = ListValueCountNoCase(loc.addedProperties, loc.iItem);
223				loc.addedProperties = ListAppend(loc.addedProperties, loc.iItem);
224
225				// loop through all classes (current and all included ones)
226				loc.jEnd = ArrayLen(loc.classes);
227				for (loc.j=1; loc.j <= loc.jEnd; loc.j++)
228				{
229					loc.toAppend = "";
230					loc.classData = loc.classes[loc.j];
231
232					// we need the name of the table on the first go, and the alias otherwise
233					loc.tableName = loc.classData.tableName;
234					if (loc.j != 1)
235						loc.tableName = loc.classData.alias;
236
237					// create a struct for this model unless it already exists
238					if (!StructKeyExists(loc.addedPropertiesByModel, loc.classData.alias))
239						loc.addedPropertiesByModel[loc.classData.alias] = "";
240
241					// if we find the property in this model and it's not already added we go ahead and add it to the select clause
242					if ((ListFindNoCase(loc.classData.propertyList, loc.iItem) || ListFindNoCase(loc.classData.calculatedPropertyList, loc.iItem)) && !ListFindNoCase(loc.addedPropertiesByModel[loc.classData.alias], loc.iItem))
243					{
244						// if expanded column aliases is enabled then mark all columns from included classes as duplicates in order to prepend them with their class name
245						loc.flagAsDuplicate = false;
246						if (arguments.renameFields)
247						{
248							if (loc.duplicateCount)
249							{
250								// always flag as a duplicate when a property with this name has already been added
251								loc.flagAsDuplicate  = true;
252							}
253							else if (loc.j gt 1)
254							{
255								if (arguments.useExpandedColumnAliases)
256								{
257									// when on included models and using the new setting we flag every property as a duplicate so that the model name always gets prepended
258									loc.flagAsDuplicate  = true;
259								}
260								else if (!arguments.useExpandedColumnAliases && arguments.returnAs != "query")
261								{
262									// with the old setting we only do it when we're returning object(s) since when creating instances on none base models we need the model name prepended
263									loc.flagAsDuplicate  = true;
264								}
265							}
266						}
267						if (loc.flagAsDuplicate )
268							loc.toAppend = loc.toAppend & "[[duplicate]]" & loc.j;
269						if (ListFindNoCase(loc.classData.propertyList, loc.iItem))
270						{
271							loc.toAppend = loc.toAppend & loc.tableName & ".";
272							if (ListFindNoCase(loc.classData.columnList, loc.iItem))
273							{
274								loc.toAppend = loc.toAppend & loc.iItem;
275							}
276							else
277							{
278								loc.toAppend = loc.toAppend & loc.classData.properties[loc.iItem].column;
279								if (arguments.renameFields)
280									loc.toAppend = loc.toAppend & " AS " & loc.iItem;
281							}
282						}
283						else if (ListFindNoCase(loc.classData.calculatedPropertyList, loc.iItem) && arguments.addCalculatedProperties)
284						{
285							loc.toAppend = loc.toAppend & "(" & Replace(loc.classData.calculatedProperties[loc.iItem].sql, ",", "[[comma]]", "all") & ") AS " & loc.iItem;
286						}
287						loc.addedPropertiesByModel[loc.classData.alias] = ListAppend(loc.addedPropertiesByModel[loc.classData.alias], loc.iItem);
288						break;
289					}
290				}
291				if (Len(loc.toAppend))
292					loc.list = ListAppend(loc.list, loc.toAppend);
293				else if (application.wheels.showErrorInformation && (not arguments.addCalculatedProperties && not ListFindNoCase(loc.classData.calculatedPropertyList, loc.iItem)))
294					$throw(type="Wheels.ColumnNotFound", message="Wheels looked for the column mapped to the `#loc.iItem#` property but couldn't find it in the database table.", extendedInfo="Verify the `select` argument and/or your property to column mappings done with the `property` method inside the model's `init` method to make sure everything is correct.");
295			}
296
297			// let's replace eventual duplicates in the clause by prepending the class name
298			if (Len(arguments.include) && arguments.renameFields)
299			{
300				loc.newSelect = "";
301				loc.addedProperties = "";
302				loc.iEnd = ListLen(loc.list);
303				for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
304				{
305					loc.iItem = ListGetAt(loc.list, loc.i);
306
307					// get the property part, done by taking everytyhing from the end of the string to a . or a space (which would be found when using " AS ")
308					loc.property = Reverse(SpanExcluding(Reverse(loc.iItem), ". "));
309
310					// check if this one has been flagged as a duplicate, we get the number of classes to skip and also remove the flagged info from the item
311					loc.duplicateCount = 0;
312					loc.matches = REFind("^\[\[duplicate\]\](\d+)(.+)$", loc.iItem, 1, true);
313					if (loc.matches.pos[1] gt 0)
314					{
315						loc.duplicateCount = Mid(loc.iItem, loc.matches.pos[2], loc.matches.len[2]);
316						loc.iItem = Mid(loc.iItem, loc.matches.pos[3], loc.matches.len[3]);
317					}
318
319					if (!loc.duplicateCount)
320					{
321						// this is not a duplicate so we can just insert it as is
322						loc.newItem = loc.iItem;
323						loc.newProperty = loc.property;
324					}
325					else
326					{
327						// this is a duplicate so we prepend the class name and then insert it unless a property with the resulting name already exist
328						loc.classData = loc.classes[loc.duplicateCount];
329
330						// prepend class name to the property
331						loc.newProperty = loc.classData.modelName & loc.property;
332
333						if (loc.iItem Contains " AS ")
334							loc.newItem = ReplaceNoCase(loc.iItem, " AS " & loc.property, " AS " & loc.newProperty);
335						else
336							loc.newItem = loc.iItem & " AS " & loc.newProperty;
337					}
338					if (!ListFindNoCase(loc.addedProperties, loc.newProperty))
339					{
340						loc.newSelect = ListAppend(loc.newSelect, loc.newItem);
341						loc.addedProperties = ListAppend(loc.addedProperties, loc.newProperty);
342					}
343				}
344				loc.list = loc.newSelect;
345			}
346		}
347		else
348		{
349			loc.list = arguments.list;
350			if (!arguments.renameFields && Find(" AS ", loc.list))
351				loc.list = REReplace(loc.list, variables.wheels.class.RESQLAs, "", "all");
352		}
353	</cfscript>
354	<cfreturn loc.list />
355</cffunction>
356
357<cffunction name="$addWhereClause" returntype="array" access="public" output="false">
358	<cfargument name="sql" type="array" required="true">
359	<cfargument name="where" type="string" required="true">
360	<cfargument name="include" type="string" required="true">
361	<cfargument name="includeSoftDeletes" type="boolean" required="true">
362	<cfscript>
363		var loc = {};
364		loc.whereClause = $whereClause(where=arguments.where, include=arguments.include, includeSoftDeletes=arguments.includeSoftDeletes);
365		loc.iEnd = ArrayLen(loc.whereClause);
366		for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
367			ArrayAppend(arguments.sql, loc.whereClause[loc.i]);
368	</cfscript>
369	<cfreturn arguments.sql>
370</cffunction>
371
372<cffunction name="$whereClause" returntype="array" access="public" output="false">
373	<cfargument name="where" type="string" required="true">
374	<cfargument name="include" type="string" required="false" default="">
375	<cfargument name="includeSoftDeletes" type="boolean" required="false" default="false">
376	<cfscript>
377		var loc = {};
378		loc.returnValue = [];
379		if (Len(arguments.where))
380		{
381			// setup an array containing class info for current class and all the ones that should be included
382			loc.classes = [];
383			if (Len(arguments.include))
384				loc.classes = $expandedAssociations(include=arguments.include);
385			ArrayPrepend(loc.classes, variables.wheels.class);
386			ArrayAppend(loc.returnValue, "WHERE");
387			loc.wherePos = ArrayLen(loc.returnValue) + 1;
388			loc.params = ArrayNew(1);
389			loc.where = ReplaceList(REReplace(arguments.where, variables.wheels.class.RESQLWhere, "\1?\8" , "all"), "AND,OR", "#chr(7)#AND,#chr(7)#OR");
390			for (loc.i=1; loc.i <= ListLen(loc.where, Chr(7)); loc.i++)
391			{
392				loc.param = {};
393				loc.element = Replace(ListGetAt(loc.where, loc.i, Chr(7)), Chr(7), "", "one");
394				if (Find("(", loc.element) && Find(")", loc.element))
395					loc.elementDataPart = SpanExcluding(Reverse(SpanExcluding(Reverse(loc.element), "(")), ")");
396				else if (Find("(", loc.element))
397					loc.elementDataPart = Reverse(SpanExcluding(Reverse(loc.element), "("));
398				else if (Find(")", loc.element))
399					loc.elementDataPart = SpanExcluding(loc.element, ")");
400				else
401					loc.elementDataPart = loc.element;
402				loc.elementDataPart = Trim(ReplaceList(loc.elementDataPart, "AND,OR", ""));
403				loc.temp = REFind("^([a-zA-Z0-9-_\.]*) ?#variables.wheels.class.RESQLOperators#", loc.elementDataPart, 1, true);
404				if (ArrayLen(loc.temp.len) gt 1)
405				{
406					loc.where = Replace(loc.where, loc.element, Replace(loc.element, loc.elementDataPart, "?", "one"));
407					loc.param.property = Mid(loc.elementDataPart, loc.temp.pos[2], loc.temp.len[2]);
408					loc.jEnd = ArrayLen(loc.classes);
409					for (loc.j=1; loc.j <= loc.jEnd; loc.j++)
410					{
411						// defaults for cfqueryparam, will be overridden and set appropriately when a column mapping is found below
412						loc.param.type = "CF_SQL_CHAR";
413						loc.param.dataType = "char";
414						loc.param.scale = 0;
415						loc.param.list = false;
416
417						loc.classData = loc.classes[loc.j];
418						// we need the name of the table on the first go, and the alias otherwise
419						loc.tableName = loc.classData.tableName;
420						if (loc.j != 1)
421							loc.tableName = loc.classData.alias;
422
423						if (loc.param.property Does Not Contain "." || ListFirst(loc.param.property, ".") == loc.classData.tableName)
424						{
425							if (ListFindNoCase(loc.classData.propertyList, ListLast(loc.param.property, ".")))
426							{
427								loc.param.type = loc.classData.properties[ListLast(loc.param.property, ".")].type;
428								loc.param.dataType = loc.classData.properties[ListLast(loc.param.property, ".")].dataType;
429								loc.param.scale = loc.classData.properties[ListLast(loc.param.property, ".")].scale;
430								loc.param.column = loc.tableName & "." & loc.classData.properties[ListLast(loc.param.property, ".")].column;
431								break;
432							}
433							else if (ListFindNoCase(loc.classData.calculatedPropertyList, ListLast(loc.param.property, ".")))
434							{
435								loc.param.column = loc.classData.calculatedProperties[ListLast(loc.param.property, ".")].sql;
436								break;
437							}
438						}
439					}
440					if (application.wheels.showErrorInformation && !StructKeyExists(loc.param, "column"))
441						$throw(type="Wheels.ColumnNotFound", message="Wheels looked for the column mapped to the `#loc.param.property#` property but couldn't find it in the database table.", extendedInfo="Verify the `where` argument and/or your property to column mappings done with the `property` method inside the model's `init` method to make sure everything is correct.");
442					loc.temp = REFind("^[a-zA-Z0-9-_\.]* ?#variables.wheels.class.RESQLOperators#", loc.elementDataPart, 1, true);
443					loc.param.operator = Trim(Mid(loc.elementDataPart, loc.temp.pos[2], loc.temp.len[2]));
444					if (Right(loc.param.operator, 2) == "IN")
445						loc.param.list = true;
446					ArrayAppend(loc.params, loc.param);
447				}
448			}
449			loc.where = ReplaceList(loc.where, "#Chr(7)#AND,#Chr(7)#OR", "AND,OR");
450
451			// add to sql array
452			loc.where = " #loc.where# ";
453			loc.iEnd = ListLen(loc.where, "?");
454			for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
455			{
456				loc.item = ListGetAt(loc.where, loc.i, "?");
457				if (Len(Trim(loc.item)))
458					ArrayAppend(loc.returnValue, loc.item);
459				if (loc.i < ListLen(loc.where, "?"))
460				{
461					loc.column = loc.params[loc.i].column;
462					ArrayAppend(loc.returnValue, "#loc.column# #loc.params[loc.i].operator#");
463					loc.param = {type=loc.params[loc.i].type, dataType=loc.params[loc.i].dataType, scale=loc.params[loc.i].scale, list=loc.params[loc.i].list};
464					ArrayAppend(loc.returnValue, loc.param);
465				}
466			}
467		}
468
469		if (!arguments.includeSoftDeletes)
470		{
471			/// add soft delete sql
472			loc.classes = [];
473			if (Len(arguments.include))
474				loc.classes = $expandedAssociations(include=arguments.include);
475			ArrayPrepend(loc.classes, variables.wheels.class);
476			loc.addToWhere = "";
477			for (loc.i=1; loc.i <= ArrayLen(loc.classes); loc.i++)
478			{
479				// we need the name of the table on the first go, and the alias otherwise
480				loc.classData = loc.classes[loc.i];
481				loc.tableName = loc.classData.tableName;
482				if (loc.i != 1)
483					loc.tableName = loc.classData.alias;
484				loc.model = model(loc.classData.modelName);
485				if (loc.model.$softDeletion())
486					loc.addToWhere = ListAppend(loc.addToWhere, loc.model.$aliasName() & "." & loc.model.$softDeleteColumn() & " IS NULL");
487			}
488			loc.addToWhere = Replace(loc.addToWhere, ",", " AND ", "all");
489			if (Len(loc.addToWhere))
490			{
491				if (Len(arguments.where))
492				{
493					ArrayInsertAt(loc.returnValue, loc.wherePos, " (");
494					ArrayAppend(loc.returnValue, ") AND (");
495					ArrayAppend(loc.returnValue, loc.addToWhere);
496					ArrayAppend(loc.returnValue, ")");
497				}
498				else
499				{
500					ArrayAppend(loc.returnValue, "WHERE ");
501					ArrayAppend(loc.returnValue, loc.addToWhere);
502				}
503			}
504		}
505	</cfscript>
506	<cfreturn loc.returnValue>
507</cffunction>
508
509<cffunction name="$addWhereClauseParameters" returntype="array" access="public" output="false">
510	<cfargument name="sql" type="array" required="true">
511	<cfargument name="where" type="string" required="true">
512	<cfscript>
513		var loc = {};
514		if (Len(arguments.where))
515		{
516			loc.start = 1;
517			loc.originalValues = [];
518			while (!StructKeyExists(loc, "temp") || ArrayLen(loc.temp.len) gt 1)
519			{
520				loc.temp = REFind(variables.wheels.class.RESQLWhere, arguments.where, loc.start, true);
521				if (ArrayLen(loc.temp.len) gt 1)
522				{
523					loc.start = loc.temp.pos[4] + loc.temp.len[4];
524					ArrayAppend(loc.originalValues, ReplaceList(Chr(7) & Mid(arguments.where, loc.temp.pos[4], loc.temp.len[4]) & Chr(7), "#Chr(7)#(,)#Chr(7)#,#Chr(7)#','#Chr(7)#,#Chr(7)#"",""#Chr(7)#,#Chr(7)#", ",,,,,,"));
525				}
526			}
527
528			loc.pos = ArrayLen(loc.originalValues);
529			loc.iEnd = ArrayLen(arguments.sql);
530			for (loc.i=loc.iEnd; loc.i gt 0; loc.i--)
531			{
532				if (IsStruct(arguments.sql[loc.i]) && loc.pos gt 0)
533				{
534					arguments.sql[loc.i].value = loc.originalValues[loc.pos];
535					if (loc.originalValues[loc.pos] == "")
536						arguments.sql[loc.i].null = true;
537					loc.pos--;
538				}
539			}
540		}
541	</cfscript>
542	<cfreturn arguments.sql>
543</cffunction>
544
545<cffunction name="$expandProperties" returntype="string" access="public" output="false">
546	<cfargument name="list" type="string" required="true">
547	<cfargument name="classes" type="array" required="true">
548	<cfscript>
549		var loc = {};
550		loc.matches = REMatch("[A-Za-z1-9]+\.\*", arguments.list);
551		loc.iEnd = ArrayLen(loc.matches);
552		for (loc.i = 1; loc.i lte loc.iEnd; loc.i++)
553		{
554			loc.match = loc.matches[loc.i];
555			loc.fields = "";
556			loc.tableName = ListGetAt(loc.match, 1, ".");
557			loc.jEnd = ArrayLen(arguments.classes);
558			for (loc.j = 1; loc.j lte loc.jEnd; loc.j++)
559			{
560				loc.class = arguments.classes[loc.j];
561				if (loc.class.tableName == loc.tableName)
562				{
563					for (loc.item in loc.class.properties)
564						loc.fields = ListAppend(loc.fields, "#loc.class.tableName#.#loc.item#");
565					break;
566				}
567			}
568
569			if (Len(loc.fields))
570				arguments.list = Replace(arguments.list, loc.match, loc.fields, "all");
571			else if (application.wheels.showErrorInformation)
572				$throw(type="Wheels.ModelNotFound", message="Wheels looked for the model mapped to table name `#loc.tableName#` but couldn't find it.", extendedInfo="Verify the `select` argument and/or your model association mappings are correct.");
573		}
574	</cfscript>
575	<cfreturn arguments.list />
576</cffunction>
577
578<cffunction name="$expandedAssociations" returntype="array" access="public" output="false">
579	<cfargument name="include" type="string" required="true">
580	<cfscript>
581		var loc = {};
582		loc.returnValue = [];
583
584		// add the current class name so that the levels list start at the lowest level
585		loc.levels = variables.wheels.class.modelName;
586
587		// count the included associations
588		loc.iEnd = ListLen(Replace(arguments.include, "(", ",", "all"));
589
590		// clean up spaces in list and add a comma at the end to indicate end of string
591		loc.include = Replace(arguments.include, " ", "", "all") & ",";
592
593		loc.pos = 1;
594		for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
595		{
596			// look for the next delimiter sequence in the string and set it (can be single delims or a chain, e.g ',' or ')),'
597			loc.delimFind = ReFind("[(\(|\)|,)]+", loc.include, loc.pos, true);
598			loc.delimSequence = Mid(loc.include, loc.delimFind.pos[1], loc.delimFind.len[1]);
599
600			// set current association name and set new position to start search in the next loop
601			loc.previousName = "";
602			if (StructKeyExists(loc, "name"))
603				loc.previousName = loc.name;
604			loc.name = Mid(loc.include, loc.pos, loc.delimFind.pos[1]-loc.pos);
605			loc.pos = REFindNoCase("[a-z]", loc.include, loc.delimFind.pos[1]);
606
607			// create a reference to current class in include string and get its association info
608			loc.className = ListLast(loc.levels);
609			loc.class = model(loc.className);
610			loc.classAssociations = loc.class.$classData().associations;
611
612			// throw an error if the association was not found
613			if (application.wheels.showErrorInformation && !StructKeyExists(loc.classAssociations, loc.name))
614				$throw(type="Wheels.AssociationNotFound", message="An association named `#loc.name#` could not be found on the `#ListLast(loc.levels)#` model.", extendedInfo="Setup an association in the `init` method of the `models/#capitalize(ListLast(loc.levels))#.cfc` file and name it `#loc.name#`. You can use the `belongsTo`, `hasOne` or `hasMany` method to set it up.");
615
616			// create a reference to the associated class
617			loc.associatedClass = model(loc.classAssociations[loc.name].modelName);
618
619			// create the join string if it hasn't already been done
620			// (no need to lock this code since when multiple requests process it they will end up setting the same value (no intermediate value is ever set on the join variable in the application scoped model object)
621			if (!StructKeyExists(loc.classAssociations[loc.name], "join"))
622			{
623				// figure out the foreignKey for this association
624				if (!Len(loc.classAssociations[loc.name].foreignKey))
625				{
626					if (loc.classAssociations[loc.name].type == "belongsTo")
627					{
628						loc.classAssociations[loc.name].foreignKey = loc.associatedClass.$classData().modelName & Replace(loc.associatedClass.$classData().keys, ",", ",#loc.associatedClass.$classData().modelName#", "all");
629					}
630					else
631					{
632						loc.classAssociations[loc.name].foreignKey = loc.class.$classData().modelName & Replace(loc.class.$classData().keys, ",", ",#loc.class.$classData().modelName#", "all");
633					}
634				}
635
636				// figure out the joinKey for this association
637				if (!Len(loc.classAssociations[loc.name].joinKey))
638				{
639					if (loc.classAssociations[loc.name].type == "belongsTo")
640					{
641						loc.classAssociations[loc.name].joinKey = loc.associatedClass.$classData().keys;
642					}
643					else
644					{
645						loc.classAssociations[loc.name].joinKey = loc.class.$classData().keys;
646					}
647				}
648
649				// set our alias to the tableName if we do not have one
650				loc.classAssociations[loc.name].alias = loc.associatedClass.$classData().tableName;
651				loc.classAssociations[loc.name].tableName = loc.associatedClass.$classData().tableName;
652				loc.classAssociations[loc.name].columnList = loc.associatedClass.$classData().columnList;
653				loc.classAssociations[loc.name].properties = loc.associatedClass.$classData().properties;
654				loc.classAssociations[loc.name].propertyList = loc.associatedClass.$classData().propertyList;
655				loc.classAssociations[loc.name].calculatedProperties = loc.associatedClass.$classData().calculatedProperties;
656				loc.classAssociations[loc.name].calculatedPropertyList = loc.associatedClass.$classData().calculatedPropertyList;
657
658				// check to see if we have a self join and make the joining table name unique
659				if (loc.class.$classData().tableName == loc.associatedClass.$classData().tableName)
660				{
661					loc.associatedClass.$alias(associationName=loc.name);
662					loc.classAssociations[loc.name].alias = loc.associatedClass.$aliasName(associationName=loc.name);
663				}
664
665				loc.joinType = ReplaceNoCase(loc.classAssociations[loc.name].joinType, "outer", "left outer", "one");
666				//loc.join = UCase(loc.joinType) & " JOIN #loc.classAssociations[loc.name].tableName# AS #loc.classAssociations[loc.name].alias# ON  ";
667				loc.join = UCase(loc.joinType) & " JOIN " & variables.wheels.class.adapter.$tableAliasForJoin(loc.classAssociations[loc.name].tableName, loc.classAssociations[loc.name].alias) & " ON  ";
668				loc.toAppend = "";
669				loc.jEnd = ListLen(loc.classAssociations[loc.name].foreignKey);
670				for (loc.j=1; loc.j <= loc.jEnd; loc.j++)
671				{
672					loc.key1 = ListGetAt(loc.classAssociations[loc.name].foreignKey, loc.j);
673					if (loc.classAssociations[loc.name].type == "belongsTo")
674					{
675						loc.key2 = ListFindNoCase(loc.classAssociations[loc.name].joinKey, loc.key1);
676						if (loc.key2)
677							loc.key2 = ListGetAt(loc.classAssociations[loc.name].joinKey, loc.key2);
678						else
679							loc.key2 = ListGetAt(loc.classAssociations[loc.name].joinKey, loc.j);
680						loc.first = loc.key1;
681						loc.second = loc.key2;
682					}
683					else
684					{
685						loc.key2 = ListFindNoCase(loc.classAssociations[loc.name].joinKey, loc.key1);
686						if (loc.key2)
687							loc.key2 = ListGetAt(loc.classAssociations[loc.name].joinKey, loc.key2);
688						else
689							loc.key2 = ListGetAt(loc.classAssociations[loc.name].joinKey, loc.j);
690						loc.first = loc.key2;
691						loc.second = loc.key1;
692					}
693					loc.toAppend = ListAppend(loc.toAppend, "#loc.class.$aliasName(associationname=loc.previousName)#.#loc.class.$classData().properties[loc.first].column# = #loc.classAssociations[loc.name].alias#.#loc.associatedClass.$classData().properties[loc.second].column#");
694				}
695				loc.classAssociations[loc.name].join = loc.join & Replace(loc.toAppend, ",", " AND ", "all");
696			}
697
698			// loop over each character in the delimiter sequence and move up/down the levels as appropriate
699			for (loc.x=1; loc.x lte Len(loc.delimSequence); loc.x++)
700			{
701				loc.delimChar = Mid(loc.delimSequence, loc.x, 1);
702				if (loc.delimChar == "(")
703					loc.levels = ListAppend(loc.levels, loc.classAssociations[loc.name].modelName);
704				else if (loc.delimChar == ")")
705					loc.levels = ListDeleteAt(loc.levels, ListLen(loc.levels));
706			}
707
708			// add info to the array that we will return
709			ArrayAppend(loc.returnValue, loc.classAssociations[loc.name]);
710		}
711		</cfscript>
712		<cfreturn loc.returnValue>
713</cffunction>
714
715<cffunction name="$keyWhereString" returntype="string" access="public" output="false">
716	<cfargument name="properties" type="any" required="false" default="#primaryKeys()#">
717	<cfargument name="values" type="any" required="false" default="">
718	<cfargument name="keys" type="any" required="false" default="">
719	<cfscript>
720		var loc = {};
721		loc.returnValue = "";
722		loc.iEnd = ListLen(arguments.properties);
723		for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
724		{
725			loc.key = Trim(ListGetAt(arguments.properties, loc.i));
726			if (Len(arguments.values))
727				loc.value = Trim(ListGetAt(arguments.values, loc.i));
728			else if (Len(arguments.keys))
729				loc.value = this[ListGetAt(arguments.keys, loc.i)];
730			else
731				loc.value = "";
732			loc.toAppend = loc.key & "=";
733			if (!IsNumeric(loc.value))
734				loc.toAppend = loc.toAppend & "'";
735			loc.toAppend = loc.toAppend & loc.value;
736			if (!IsNumeric(loc.value))
737				loc.toAppend = loc.toAppend & "'";
738			loc.returnValue = ListAppend(loc.returnValue, loc.toAppend, " ");
739			if (loc.i < loc.iEnd)
740				loc.returnValue = ListAppend(loc.returnValue, "AND", " ");
741		}
742	</cfscript>
743	<cfreturn loc.returnValue>
744</cffunction>