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