Source

Inside.Util / Sql / QueryPlan.cs

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

using NLog;

namespace Inside.Util.Sql {
	public class QueryPlan {
		public const string Rownum = "_rownum_";

		string _originalSQLTxt;
		string _relation;
		string _alias;

		#region 需要参与reset的部分。
		DyObj _column;
		List<Condition> _conditions = new List<Condition>(23);
		List<dynamic> _sorts = new List<dynamic>(3);

		decimal _limit = 0;
		decimal _offset = 0;

		public Func<dynamic, dynamic> RowHandler;
		public SqlHelper Sql { get; private set; }
		#endregion

		/// <example>
		/// <code>
		/// //数据结构示意
		/// {
		///		column_name:{
		///			_guid:"" //用于标识该列
		///			, attr_name:"" //用于DyObj属性的列名,column_name与attr_name相等。
		///			, original:"" //从sql语句中解析出来的原始列名
		///			, alias:"" //从sql语句中解析出来的别名
		///			, join:{
		///				column:{} //要join的目标列
		///				, plan:{} //要join的目标
		///				, conditionType:{} //join的条件类型
		///				, joinType:{} //join的方式
		///			}
		///		}
		///		, ...
		/// }
		/// </code>
		/// </example>
		public dynamic Column {
			get {
				return this._column;
			}
		}

		public string Text {
			get {
				return _GenerateSQLObject(this)["txt"] as string;
			}
		}

		public QueryPlan(string sql, SqlHelper sh, QueryPlanType type = QueryPlanType.Oracle) {
			if(string.IsNullOrEmpty(sql) || null == sh) {
				throw new ArgumentNullException();
			}

			this._originalSQLTxt = sql.Trim();
			this.Sql = sh;
			this._Setup();
		}

		public DataTable GetDataTable() {
			dynamic sql = _GenerateSQLObject(this);
			var comm = this.Sql.Init(sql.txt as string);
			if(null != sql.parameters) {
				comm.AddParameters((sql.parameters as List<DbParameter>).ToArray());
			}

			return comm.GetDataTable();
		}

		public List<dynamic> Execute() {
			var dt = this.GetDataTable();

			return dt.ToDynamicObjects((dr) => {
				var dyRow = _ParseQueryResult(dr, this);
				return null == this.RowHandler ? dyRow : this.RowHandler(dyRow);
			});
		}

		public void Reset() {
			this._conditions.Clear();
			this._sorts.Clear();
			this._limit = 0;
			this._offset = 0;

			this._Setup();
		}

		public decimal Count() {
			dynamic sqlObj = _GenerateSQLObject(this);
			DbCommand comm = this.Sql.Init(string.Format("SELECT count(*) FROM ({0})", sqlObj.txt));
			if(!ObjectHelper.IsNil(sqlObj.parameters)) {
				comm.AddParameters((sqlObj.parameters as List<DbParameter>).ToArray());
			}

			return (decimal)comm.ExecuteScalarAuto();
		}

		/// <summary>
		/// 只对自身拥有的列进行条件过滤,无法过滤Join进来的子查询计划的列。
		/// </summary>
		public QueryPlan Filter(ConditionType condType, string colName, object value = null) {
			if(!this._column.ContainsAttr(colName)) {
				throw new ArgumentException("在当前的查询计划中没有找到此列 : " + colName);
			}

			return this.Filter(new Condition(condType, this._column[colName] as DyObj, value));
		}

		public QueryPlan Filter(Condition cond) {
			this._conditions.Add(cond);

			return this;
		}

		public QueryPlan Join(QueryPlan queryPlan, JoinType joinType, dynamic leftCol, ConditionType condType, dynamic rightCol) {
			switch(condType) {
				case ConditionType.Equal:
					break;
				default:
					throw new ArgumentException("不支持在JOIN中使用此条件 : " + condType.ToString());
			}

			dynamic left = this._FindColumn(leftCol._guid as string);
			if(null == left) {
				throw new ArgumentException("在当前的查询计划及其子计划中没有找到此列 : " + leftCol.column_name);
			}

			dynamic right = queryPlan._FindColumn(rightCol._guid as string);
			if(null == right) {
				throw new ArgumentException("在要Join的目标中没有找到此列 : " + rightCol.column_name);
			}

			dynamic join = new DyObj();
			join.column = rightCol;
			join.plan = queryPlan;
			join.condition_type = condType;
			join.join_type = joinType;

			left.join = join;

			return this;
		}

		public QueryPlan Limit(decimal limit) {
			this._limit = limit;

			return this;
		}

		public QueryPlan Offset(decimal offset) {
			this._offset = offset;

			return this;
		}

		public QueryPlan Sort(dynamic col, SortDirection direction = SortDirection.Asc, SortNullPosition nullPos = SortNullPosition.NullFirst) {
			dynamic result = this._FindColumn(col._guid);
			if(null == result) {
				throw new ArgumentException("在当前的查询计划及其子计划中没有找到此列 : " + col.column_name);
			}

			dynamic sort = new DyObj();
			sort.column_guid = result._guid;
			sort.direction = direction;
			sort.null_position = nullPos;
			this._sorts.Add(sort);

			return this;
		}

		DyObj _FindColumn(string guid) {
			foreach(var kv in this._column) {
				dynamic col = kv.Value;
				if(col._guid == guid) {
					return col;
				}
				else if(!ObjectHelper.IsNil(col["join"])) {
					var result = col.join.plan._FindColumn(guid);
					if(null != result) {
						return result;
					}
				}
			}

			return null;
		}

		void _Setup() {
			dynamic parsedSqlObj = _ParseSql(this._originalSQLTxt, this.Sql);
			this._relation = parsedSqlObj.relation;
			this._column = parsedSqlObj.column;
			this._alias = parsedSqlObj.alias;
		}

		/// <summary>
		/// 通过列的guid获取该列所属QueryPlan对象的别名,通过Join进入column数据结构的QueryPlan也在查询范围内并且返回的别名将依赖与其在column结构中的位置(除非它自身已经声明了别名)。
		/// </summary>
		/// <param name="queryPlan">作为查询起点的QueryPlan</param>
		static DyObj _FindQueryPlanIndex(QueryPlan rootPlan, QueryPlan targetPlan, int tbIndex = 0) {
			dynamic result = new DyObj();
			result.found = false;

			if(null == rootPlan) {
				result.found = true;
			}
			else {
				foreach(var kv in rootPlan._column) {
					dynamic col = kv.Value;
					if(!ObjectHelper.IsNil(col["join"])) {
						++tbIndex;
						var plan = col.join.plan;
						if(targetPlan == plan) {
							result.found = true;
							break;
						}
						else {
							var r = _FindQueryPlanIndex(plan, targetPlan, tbIndex);
							tbIndex = r.index;
							if(r.found) {
								result.found = true;
								break;
							}
						}
					}
				}
			}

			result.index = tbIndex;
			return result;
		}

		static string _GenerateQueryPlanAlias(QueryPlan plan, int index) {
			return string.IsNullOrEmpty(plan._alias) ? "t" + index : (plan._alias as string).Substr();
		}

		static DyObj _GenerateSQLObject(QueryPlan queryPlan, QueryPlan rootPlan = null, int conditionIndexer = 0, List<DbParameter> parameters = null) {
			parameters = parameters ?? new List<DbParameter>();

			var joinClause = new StringBuilder();
			var contextualColumns = new DyObj();
			var columnClause = new StringBuilder();
			var tbIndex = (int)_FindQueryPlanIndex(rootPlan, queryPlan)["index"];
			var tbAlias = _GenerateQueryPlanAlias(queryPlan, tbIndex);

			foreach(var kv in queryPlan._column) {
				dynamic c = kv.Value;
				var contextualColName = string.Empty;
				var contextualColAlias = string.Empty;
				if(string.IsNullOrEmpty(c.alias)) {
					if(c.original.IndexOf('(') > 0) {
						throw new ArgumentException("使用聚集函数时,必须使用列别名 : " + c.column_name);
					}

					if(c.original.StartsWith(tbAlias + ".")) {
						//如果解析出来的列没有列别名,又以自己的表别名限定了,那么其上下文相关的列名与其上下文无关列名相同。
						contextualColName = c.original;
					}
					else {
						contextualColName = string.Format("{0}.{1}", tbAlias, c.original);
						contextualColAlias = string.Format("{0}_{1}", tbAlias, (c.original as string).Substr());
					}
				}
				else {
					contextualColName = string.Format("{0}.{1}", tbAlias, (c.alias as string));
					contextualColAlias = string.Format("{0}_{1}", tbAlias, (c.alias as string).Substr());
				}

				dynamic contextualCol = new DyObj();
				contextualCol.name = contextualColName;
				contextualCol.alias = contextualColAlias;
				contextualColumns[c._guid] = contextualCol;

				if(!ObjectHelper.IsNil(c["join"])) {
					dynamic join = _GenerateSQLObject(c.join.plan as QueryPlan, rootPlan ?? queryPlan, conditionIndexer, parameters);

					conditionIndexer = join._condition_indexer;

					var joinTxt = string.Empty;
					var joinTypeTxt = string.Empty;
					var joinType = (JoinType)c.join.join_type;
					var conditionTxt = string.Empty;
					var conditionType = (ConditionType)c.join.condition_type;

					switch(joinType) {
						case JoinType.Full:
							joinTypeTxt = "FULL JOIN";
							break;
						case JoinType.Left:
							joinTypeTxt = "LEFT JOIN";
							break;
						case JoinType.Right:
							joinTypeTxt = "RIGHT JOIN";
							break;
						case JoinType.Inner:
							joinTypeTxt = "JOIN";
							break;
						default:
							throw new Exception("代码不该到达这个位置");
					}

					switch(conditionType) {
						case ConditionType.Equal:
							conditionTxt = "{0} = {1}".Fmt(contextualColName, (join._contextual_columns[c.join.column._guid] as DyObj)["name"]);
							break;
						default:
							throw new Exception("代码不该到达这个位置");
					}

					joinClause.Append("\n{0} (\n{1}\n) {2} ON {3}".Fmt(joinTypeTxt, join.txt as string, join._contextual_tb_alias as string, conditionTxt));
					joinClause.Append(join._join_clause);

					contextualColumns.Merge(join._contextual_columns);
				}
			}

			var txt = new StringBuilder();
			if(tbIndex > 0) {
				txt.Append(queryPlan._originalSQLTxt);
				if(string.IsNullOrEmpty(queryPlan._alias)) {
					txt.Append(" " + tbAlias);
				}
			}
			else {
				txt.Append("SELECT\n");
				foreach(var kv in contextualColumns) {
					dynamic c = kv.Value;
					if(string.IsNullOrEmpty(c.alias)) {
						txt.AppendFormat(" {0},", c.name);
					}
					else {
						txt.AppendFormat(" {0} AS {1},", c.name, c.alias);
					}
				}
				txt.Remove(txt.Length - 1, 1);
				txt.AppendFormat("\nFROM (\n{0}\n) {1}", queryPlan._originalSQLTxt, string.IsNullOrEmpty(queryPlan._alias) ? tbAlias : queryPlan._alias);
				txt.Append(joinClause.ToString());
			}

			if(queryPlan._conditions.Count > 0) {
				txt.Append("\nWHERE");
				foreach(var cond in queryPlan._conditions) {
					txt.AppendFormat(" {0} AND", _ParseCondition(cond, parameters, contextualColumns, queryPlan.Sql.CreateParameter, ref conditionIndexer));
				}
				txt.Remove(txt.Length - 4, 4);
			}

			if(queryPlan._sorts.Count > 0) {
				txt.Append("\nORDER BY");
				foreach(var s in queryPlan._sorts) {
					txt.AppendFormat(" {0} {1} {2},"
						, (contextualColumns[s.column_guid]).name
						, SortDirection.Asc == (SortDirection)s.direction ? "ASC" : "DESC"
						, SortNullPosition.NullFirst == (SortNullPosition)s.null_position ? "NULLS FIRST" : "NULLS LAST");
				}
				txt.Remove(txt.Length - 1, 1);
			}

			if(queryPlan._offset >= 0) {
				txt.Insert(0, string.Format(@"SELECT * FROM (
SELECT {0}.*, ROWNUM AS ""{1}"" FROM (
", tbAlias, Rownum));
				txt.AppendFormat(@"
) {0}
) WHERE ""{2}"" > {1}", tbAlias, queryPlan._offset, Rownum);
				if(queryPlan._limit > 0) {
					txt.AppendFormat(@" AND ""{2}"" <= {1}", tbAlias, queryPlan._offset + queryPlan._limit, Rownum);
				}
			}
			else if(queryPlan._limit > 0) {
				txt.Insert(0, string.Format(@"SELECT * FROM (
SELECT {0}.*, ROWNUM AS ""{1}"" FROM (", tbAlias, Rownum));
				txt.AppendFormat(@"
) {0}
) WHERE ""{2}"" <= {1}", tbAlias, queryPlan._limit, Rownum);
			}

			dynamic result = new DyObj();
			result.txt = txt.ToString();
			result.parameters = parameters;
			result._contextual_columns = contextualColumns;
			result._contextual_tb_alias = tbAlias;

			result._condition_indexer = conditionIndexer;
			result._join_clause = joinClause.ToString();

			return result;
		}

		static string _ParseCondition(Condition condition, List<DbParameter> parameters, DyObj columns, Func<DbParameter> parameterProvider, ref int conditionIndexer) {
			var sb = new StringBuilder();
			if(ConditionType.Or == condition.Type || ConditionType.And == condition.Type) {
				sb.AppendFormat("({0} {1} {2})"
					, _ParseCondition(condition.Left, parameters, columns, parameterProvider, ref conditionIndexer)
					, condition.Type == ConditionType.And ? "AND" : "OR"
					, _ParseCondition(condition.Right, parameters, columns, parameterProvider, ref conditionIndexer)
					);
				return sb.ToString();
			}

			var name = (columns[condition.Column["_guid"] as string] as DyObj)["name"] as string;

			switch(condition.Type) {
				case ConditionType.Equal:
					sb.AppendFormat("({0} = :{1})", name, conditionIndexer);
					break;
				case ConditionType.NotEqual:
					sb.AppendFormat("({0} <> :{1})", name, conditionIndexer);
					break;
				case ConditionType.GreaterThan:
					sb.AppendFormat("({0} > :{1})", name, conditionIndexer);
					break;
				case ConditionType.GreaterOrEqual:
					sb.AppendFormat("({0} >= :{1})", name, conditionIndexer);
					break;
				case ConditionType.IsNotNull:
					sb.AppendFormat("({0} IS NOT NULL)", name);
					return sb.ToString();
				case ConditionType.IsNull:
					sb.AppendFormat("({0} IS NULL)", name);
					return sb.ToString();
				case ConditionType.LessOrEqual:
					sb.AppendFormat("({0} <= :{1})", name, conditionIndexer);
					break;
				case ConditionType.LessThan:
					sb.AppendFormat("({0} < :{1})", name, conditionIndexer);
					break;
				case ConditionType.LeftLike:
					sb.AppendFormat("({0} LIKE :{1}||'%')", name, conditionIndexer);
					break;
				case ConditionType.RightLike:
					sb.AppendFormat("({0} LIKE '%'||:{1})", name, conditionIndexer);
					break;
				case ConditionType.Like:
					sb.AppendFormat("({0} LIKE '%'||:{1}||'%')", name, conditionIndexer);
					break;
				case ConditionType.In:
				case ConditionType.NotIn:
					sb.AppendFormat("({0} {1} (", name, ConditionType.In == condition.Type ? "IN" : "NOT IN");
					foreach(var val in condition.Value as IEnumerable) {
						var p = parameterProvider();
						p.ParameterName = conditionIndexer.ToString();
						p.Value = val;
						sb.AppendFormat(":{0},", conditionIndexer);
						parameters.Add(p);
						++conditionIndexer;
					}
					sb.Remove(sb.Length - 1, 1);
					sb.Append("))");

					return sb.ToString();
				default:
					throw new Exception("代码不该到达这个位置");
			}

			var parameter = parameterProvider();
			parameter.ParameterName = conditionIndexer.ToString();
			parameter.Value = condition.Value;
			parameters.Add(parameter);

			++conditionIndexer;

			return sb.ToString();
		}

		static DyObj _ParseSql(string sql, SqlHelper sh) {
			var regExp = new Regex(@"
^\s*
select
\s+
(?<columnTxt>.+?)
\s+
from
(?:
	\s*(?<subQuery>\(.+\))(?:\s*(?<alias>\w+|\""\w+\""))?
	|
	\s+(?<relationName>\w+|\""\w+\"")(?:\s+(?<alias>\w+|\""\w+\""))?
)
\s*$
"
			, RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.IgnorePatternWhitespace | RegexOptions.ExplicitCapture);

			var m = regExp.Match(sql);
			if(!m.Success) {
				throw new ArgumentException("无法识别的查询语句。\n" + sql);
			}

			var relationName = m.Groups["relationName"].Value;
			var subQuery = m.Groups["subQuery"].Value;

			if(string.IsNullOrEmpty(relationName) && string.IsNullOrEmpty(subQuery)) {
				throw new ArgumentException("无法识别的查询语句,找不到关系名或子查询。\n" + sql);
			}

			dynamic result = new DyObj();
			result.column = _ParseColumn(m.Groups["columnTxt"].Value, relationName, sh);
			result.relation = string.IsNullOrEmpty(relationName) ? subQuery : relationName;
			result.alias = m.Groups["alias"].Value;

			return result;
		}

		static DyObj _ParseColumn(string columnTxt, string relationName, SqlHelper sh) {
			dynamic result = new DyObj();

			if("*" == columnTxt) {
				if(string.IsNullOrEmpty(relationName)) {
					throw new ArgumentException("当使用*通配符时,from子句不能是子查询。");
				}

				var colNames = InputPlan.CreateInstance(relationName, sh).ColumnNames;
				foreach(var nm in colNames) {
					dynamic c = new DyObj();
					c._guid = Guid.NewGuid().ToString();
					c.original = nm.Trim();
					c.alias = null;
					c.attr_name = c.original;

					result[c.attr_name] = c;
				}
			}
			else {
				var aliasRegexp = new Regex(@"([""\w\.\(\)]+)(?:\s+as\s+)([""\w]+)", RegexOptions.IgnoreCase);
				foreach(var txt in columnTxt.Split(',')) {
					var original = txt.Trim();
					var alias = string.Empty;
					var attrName = original;

					if(aliasRegexp.IsMatch(original)) {
						var m = aliasRegexp.Match(original);
						original = m.Groups[1].Value;
						alias = m.Groups[2].Value;
						attrName = m.Groups[2].Value;
					}
					else {
						var dotIndex = attrName.IndexOf('.');
						if(dotIndex > 0) {
							attrName = attrName.Substring(dotIndex + 1);
						}
					}

					attrName = attrName.Substr();

					dynamic c = new DyObj();
					c._guid = Guid.NewGuid().ToString();
					c.original = original;
					c.alias = alias;
					c.attr_name = attrName;

					result[attrName] = c;
				}
			}

			return result;
		}

		//参数guid由父级传递进来,用于判断当前被join的对象是否应为null。
		static dynamic _ParseQueryResult(DataRow dr, QueryPlan queryPlan, QueryPlan rootPlan = null, string guid = null) {
			dynamic result = new DyObj();
			var tbAlias = _GenerateQueryPlanAlias(queryPlan, (int)_FindQueryPlanIndex(rootPlan, queryPlan)["index"]);

			foreach(var kv in queryPlan._column) {
				dynamic c = kv.Value;
				var colName = "{0}_{1}".Fmt(tbAlias, string.IsNullOrEmpty(c.alias) ? c.attr_name as string : (c.alias as string).Substr());
				if(ObjectHelper.IsNil(c["join"])) {
					if(!string.IsNullOrEmpty(guid) && c._guid == guid && ObjectHelper.IsNil(dr[colName])) {
						return null;
					}
					result[c.attr_name] = dr[colName] is DBNull ? null : dr[colName];
				}
				else {
					result[c.attr_name] = _ParseQueryResult(dr, c.join.plan, rootPlan ?? queryPlan, c.join.column._guid);
				}

				if(dr.Table.Columns.Contains(Rownum)) {
					result[Rownum] = dr[Rownum];
				}
			}

			return result;
		}
	}

	public enum QueryPlanType {
		Oracle,
		MSSQL,
		PGSQL
	}

	public enum SortDirection {
		Asc,
		Desc
	}

	public enum SortNullPosition {
		NullFirst,
		NullLast
	}

	public enum JoinType {
		Left,
		Full,
		Right,
		Inner
	}

	enum SqlTxtStatus {
		Normal,
		Limit,
		Offset,
		Sort,
		Condition,
		Join,
		SubJoin
	}
}