SqlGenerator_Helper.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298
  1. using Ant.Core;
  2. using Ant.DbExpressions;
  3. using System;
  4. using System.Collections;
  5. using System.Collections.Generic;
  6. using System.Collections.ObjectModel;
  7. using System.Linq;
  8. using System.Reflection;
  9. using System.Text;
  10. namespace Ant.Oracle
  11. {
  12. partial class SqlGenerator : DbExpressionVisitor<DbExpression>
  13. {
  14. void LeftBracket()
  15. {
  16. this._sqlBuilder.Append("(");
  17. }
  18. void RightBracket()
  19. {
  20. this._sqlBuilder.Append(")");
  21. }
  22. static string GenParameterName(int ordinal)
  23. {
  24. if (ordinal < CacheParameterNames.Count)
  25. {
  26. return CacheParameterNames[ordinal];
  27. }
  28. return ParameterPrefix + ordinal.ToString();
  29. }
  30. static string GenRowNumberName(List<DbColumnSegment> columns)
  31. {
  32. int ROW_NUMBER_INDEX = 1;
  33. string row_numberName = "ROW_NUMBER_0";
  34. while (columns.Any(a => string.Equals(a.Alias, row_numberName, StringComparison.OrdinalIgnoreCase)))
  35. {
  36. row_numberName = "ROW_NUMBER_" + ROW_NUMBER_INDEX.ToString();
  37. ROW_NUMBER_INDEX++;
  38. }
  39. return row_numberName;
  40. }
  41. static DbExpression EnsureDbExpressionReturnCSharpBoolean(DbExpression exp)
  42. {
  43. if (exp.Type != UtilConstants.TypeOfBoolean && exp.Type != UtilConstants.TypeOfBoolean_Nullable)
  44. return exp;
  45. if (SafeDbExpressionTypes.Contains(exp.NodeType))
  46. {
  47. return exp;
  48. }
  49. //将且认为不符合上述条件的都是诸如 a.Id>1,a.Name=="name" 等不能作为 bool 返回值的表达式
  50. //构建 case when
  51. return ConstructReturnCSharpBooleanCaseWhenExpression(exp);
  52. }
  53. public static DbCaseWhenExpression ConstructReturnCSharpBooleanCaseWhenExpression(DbExpression exp)
  54. {
  55. // case when 1>0 then 1 when not (1>0) then 0 else Null end
  56. DbCaseWhenExpression.WhenThenExpressionPair whenThenPair = new DbCaseWhenExpression.WhenThenExpressionPair(exp, DbConstantExpression.True);
  57. DbCaseWhenExpression.WhenThenExpressionPair whenThenPair1 = new DbCaseWhenExpression.WhenThenExpressionPair(DbExpression.Not(exp), DbConstantExpression.False);
  58. List<DbCaseWhenExpression.WhenThenExpressionPair> whenThenExps = new List<DbCaseWhenExpression.WhenThenExpressionPair>(2);
  59. whenThenExps.Add(whenThenPair);
  60. whenThenExps.Add(whenThenPair1);
  61. DbCaseWhenExpression caseWhenExpression = DbExpression.CaseWhen(whenThenExps, DbConstantExpression.Null, UtilConstants.TypeOfBoolean);
  62. return caseWhenExpression;
  63. }
  64. static Stack<DbExpression> GatherBinaryExpressionOperand(DbBinaryExpression exp)
  65. {
  66. DbExpressionType nodeType = exp.NodeType;
  67. Stack<DbExpression> items = new Stack<DbExpression>();
  68. items.Push(exp.Right);
  69. DbExpression left = exp.Left;
  70. while (left.NodeType == nodeType)
  71. {
  72. exp = (DbBinaryExpression)left;
  73. items.Push(exp.Right);
  74. left = exp.Left;
  75. }
  76. items.Push(left);
  77. return items;
  78. }
  79. static void EnsureMethodDeclaringType(DbMethodCallExpression exp, Type ensureType)
  80. {
  81. if (exp.Method.DeclaringType != ensureType)
  82. throw UtilExceptions.NotSupportedMethod(exp.Method);
  83. }
  84. static void EnsureMethod(DbMethodCallExpression exp, MethodInfo methodInfo)
  85. {
  86. if (exp.Method != methodInfo)
  87. throw UtilExceptions.NotSupportedMethod(exp.Method);
  88. }
  89. static void EnsureTrimCharArgumentIsSpaces(DbExpression exp)
  90. {
  91. var m = exp as DbMemberExpression;
  92. if (m == null)
  93. throw new NotSupportedException();
  94. DbParameterExpression p;
  95. if (!DbExpressionExtensions.TryParseToParameterExpression(m, out p))
  96. {
  97. throw new NotSupportedException();
  98. }
  99. var arg = p.Value;
  100. if (arg == null)
  101. throw new NotSupportedException();
  102. var chars = arg as char[];
  103. if (chars.Length != 1 || chars[0] != ' ')
  104. {
  105. throw new NotSupportedException();
  106. }
  107. }
  108. static bool TryGetCastTargetDbTypeString(Type sourceType, Type targetType, out string dbTypeString, bool throwNotSupportedException = true)
  109. {
  110. dbTypeString = null;
  111. sourceType = Utils.GetUnderlyingType(sourceType);
  112. targetType = Utils.GetUnderlyingType(targetType);
  113. if (sourceType == targetType)
  114. return false;
  115. if (CastTypeMap.TryGetValue(targetType, out dbTypeString))
  116. {
  117. return true;
  118. }
  119. if (throwNotSupportedException)
  120. throw new NotSupportedException(AppendNotSupportedCastErrorMsg(sourceType, targetType));
  121. else
  122. return false;
  123. }
  124. static string AppendNotSupportedCastErrorMsg(Type sourceType, Type targetType)
  125. {
  126. return string.Format("Does not support the type '{0}' converted to type '{1}'.", sourceType.FullName, targetType.FullName);
  127. }
  128. static void DbFunction_DATEADD(SqlGenerator generator, string interval, DbMethodCallExpression exp)
  129. {
  130. /*
  131. * Just support hour/minute/second
  132. * systimestamp + numtodsinterval(1,'HOUR')
  133. * sysdate + numtodsinterval(50,'MINUTE')
  134. * sysdate + numtodsinterval(45,'SECOND')
  135. */
  136. generator._sqlBuilder.Append("(");
  137. exp.Object.Accept(generator);
  138. generator._sqlBuilder.Append(" + ");
  139. generator._sqlBuilder.Append("NUMTODSINTERVAL(");
  140. exp.Arguments[0].Accept(generator);
  141. generator._sqlBuilder.Append(",'");
  142. generator._sqlBuilder.Append(interval);
  143. generator._sqlBuilder.Append("')");
  144. generator._sqlBuilder.Append(")");
  145. }
  146. static void DbFunction_DATEPART(SqlGenerator generator, string interval, DbExpression exp, bool castToTimestamp = false)
  147. {
  148. /* cast(to_char(sysdate,'yyyy') as number) */
  149. generator._sqlBuilder.Append("CAST(TO_CHAR(");
  150. if (castToTimestamp)
  151. {
  152. generator.BuildCastState(exp, "TIMESTAMP");
  153. }
  154. else
  155. exp.Accept(generator);
  156. generator._sqlBuilder.Append(",'");
  157. generator._sqlBuilder.Append(interval);
  158. generator._sqlBuilder.Append("') AS NUMBER)");
  159. }
  160. static void DbFunction_DATEDIFF(SqlGenerator generator, string interval, DbExpression startDateTimeExp, DbExpression endDateTimeExp)
  161. {
  162. throw new NotSupportedException("DATEDIFF is not supported.");
  163. }
  164. #region AggregateFunction
  165. static void Aggregate_Count(SqlGenerator generator)
  166. {
  167. generator._sqlBuilder.Append("COUNT(1)");
  168. }
  169. static void Aggregate_LongCount(SqlGenerator generator)
  170. {
  171. generator._sqlBuilder.Append("COUNT(1)");
  172. }
  173. static void Aggregate_Max(SqlGenerator generator, DbExpression exp, Type retType)
  174. {
  175. AppendAggregateFunction(generator, exp, retType, "MAX", false);
  176. }
  177. static void Aggregate_Min(SqlGenerator generator, DbExpression exp, Type retType)
  178. {
  179. AppendAggregateFunction(generator, exp, retType, "MIN", false);
  180. }
  181. static void Aggregate_Sum(SqlGenerator generator, DbExpression exp, Type retType)
  182. {
  183. AppendAggregateFunction(generator, exp, retType, "SUM", false);
  184. }
  185. static void Aggregate_Average(SqlGenerator generator, DbExpression exp, Type retType)
  186. {
  187. AppendAggregateFunction(generator, exp, retType, "AVG", false);
  188. }
  189. static void AppendAggregateFunction(SqlGenerator generator, DbExpression exp, Type retType, string functionName, bool withCast)
  190. {
  191. string dbTypeString = null;
  192. if (withCast == true)
  193. {
  194. Type unType = Utils.GetUnderlyingType(retType);
  195. if (CastTypeMap.TryGetValue(unType, out dbTypeString))
  196. {
  197. generator._sqlBuilder.Append("CAST(");
  198. }
  199. }
  200. generator._sqlBuilder.Append(functionName, "(");
  201. exp.Accept(generator);
  202. generator._sqlBuilder.Append(")");
  203. if (dbTypeString != null)
  204. {
  205. generator._sqlBuilder.Append(" AS ", dbTypeString, ")");
  206. }
  207. }
  208. #endregion
  209. static void CopyColumnSegments(List<DbColumnSegment> sourceList, List<DbColumnSegment> destinationList, DbTable newTable)
  210. {
  211. for (int i = 0; i < sourceList.Count; i++)
  212. {
  213. DbColumnSegment newColumnSeg = CloneColumnSegment(sourceList[i], newTable);
  214. destinationList.Add(newColumnSeg);
  215. }
  216. }
  217. static DbColumnSegment CloneColumnSegment(DbColumnSegment rawColumnSeg, DbTable newBelongTable)
  218. {
  219. DbColumnAccessExpression columnAccessExp = new DbColumnAccessExpression(rawColumnSeg.Body.Type, newBelongTable, rawColumnSeg.Alias);
  220. DbColumnSegment newColumnSeg = new DbColumnSegment(columnAccessExp, rawColumnSeg.Alias);
  221. return newColumnSeg;
  222. }
  223. static void AppendLimitCondition(DbSqlQueryExpression sqlQuery, int limitCount)
  224. {
  225. DbLessThanExpression lessThanExp = DbExpression.LessThan(OracleSemantics.DbMemberExpression_ROWNUM, DbExpression.Constant(limitCount + 1));
  226. DbExpression condition = lessThanExp;
  227. if (sqlQuery.Condition != null)
  228. condition = DbExpression.AndAlso(sqlQuery.Condition, condition);
  229. sqlQuery.Condition = condition;
  230. }
  231. static DbSqlQueryExpression WrapSqlQuery(DbSqlQueryExpression sqlQuery, DbTable table, List<DbColumnSegment> columnSegments = null)
  232. {
  233. DbSubQueryExpression subQuery = new DbSubQueryExpression(sqlQuery);
  234. DbSqlQueryExpression newSqlQuery = new DbSqlQueryExpression();
  235. DbTableSegment tableSeg = new DbTableSegment(subQuery, table.Name);
  236. DbFromTableExpression fromTableExp = new DbFromTableExpression(tableSeg);
  237. newSqlQuery.Table = fromTableExp;
  238. CopyColumnSegments(columnSegments ?? subQuery.SqlQuery.ColumnSegments, newSqlQuery.ColumnSegments, table);
  239. return newSqlQuery;
  240. }
  241. static DbSqlQueryExpression CloneWithoutLimitInfo(DbSqlQueryExpression sqlQuery, string wraperTableName = "T")
  242. {
  243. DbSqlQueryExpression newSqlQuery = new DbSqlQueryExpression();
  244. newSqlQuery.Table = sqlQuery.Table;
  245. newSqlQuery.ColumnSegments.AddRange(sqlQuery.ColumnSegments);
  246. newSqlQuery.Condition = sqlQuery.Condition;
  247. newSqlQuery.GroupSegments.AddRange(sqlQuery.GroupSegments);
  248. newSqlQuery.HavingCondition = sqlQuery.HavingCondition;
  249. newSqlQuery.Orderings.AddRange(sqlQuery.Orderings);
  250. if (sqlQuery.Orderings.Count > 0 || sqlQuery.GroupSegments.Count > 0)
  251. {
  252. newSqlQuery = WrapSqlQuery(newSqlQuery, new DbTable(wraperTableName));
  253. }
  254. return newSqlQuery;
  255. }
  256. }
  257. }