Inside Zhou avatar Inside Zhou committed ed07b47

QueryPlan在条件过滤时可以使用文本模板,以便对列使用函数及其他复杂功能;
InputPlan在读取写入值时可以使用自定义文本值。

Comments (0)

Files changed (5)

 			}
 		}
 
-		public static decimal NilToZero(dynamic obj) {
-			return ObjectHelper.IsNil(obj) ? 0 : obj;
+		public static dynamic NilToZero(object obj) {
+			return ObjectHelper.IsNil(obj) ? 0m : obj;
 		}
 
 		/// <summary>
-		/// 获取枚举类型的字符串表示与其对应值。
+		/// 获取枚举类型的字符串表示与其对应值,值不是枚举类型
 		/// </summary>
 		public static List<KeyValuePair<string, object>> GetEnumKVs(Type enumType) {
 			var result = new List<KeyValuePair<string, object>>();
 	/// 表示一个数据查询条件。
 	/// </summary>
 	public class Condition {
+		public static Condition And(Condition left, Condition right) {
+			return new Condition(left, ConditionType.And, right);
+		}
+
+		public static Condition Or(Condition left, Condition right) {
+			return new Condition(left, ConditionType.Or, right);
+		}
+
+		public static Condition Simple(DyObj col, ConditionType type, object value = null, string colFormat = null) {
+			return new Condition(col, type, value, colFormat);
+		}
+
 		public DyObj Column { get; private set; }
 		public object Value { get; private set; }
+		public string ColumnFormat { get; private set; }
 
 		public ConditionType Type { get; private set; }
 
 		/// <summary>
 		/// 构造不包含嵌套的简单条件。
 		/// </summary>
-		public Condition(ConditionType type, DyObj col, object value = null) {
+		Condition(DyObj col, ConditionType type, object value, string colFormat) {
 			if(type == ConditionType.And || type == ConditionType.Or) {
-				throw new ArgumentException("此构造函数只用于构造简单条件,嵌套条件请使用public Condition(ConditionType type, Condition left, Condition right)");
+				throw new ArgumentException("此构造函数不能用于生成嵌套条件");
 			}
 
 			if(ObjectHelper.IsNil(col)) {
 
 			this.Type = type;
 			this.Column = col;
+			this.ColumnFormat = colFormat;
 			this.Value = value;
 		}
 
 		/// <summary>
 		/// 构造嵌套条件。
 		/// </summary>
-		public Condition(ConditionType type, Condition left, Condition right) {
-			if(type != ConditionType.And && type != ConditionType.Or) {
-				throw new ArgumentException("此构造函数只用于构造嵌套条件,简单条件请使用public Condition(ConditionType type, string name, string value)");
-			}
-
+		Condition(Condition left, ConditionType type, Condition right) {
 			if(null == left || null == right) {
 				throw new ArgumentException("当条件为and、or时,左右值都不能为空。");
 			}

Sql/DbDataObjectExtension.cs

 		/// <summary>
 		/// 将DataTable转换为dynamic类型列表。
 		/// </summary>
-		public static List<dynamic> ToDynamicObjects(this DataTable dt, Func<DataRow, DyObj> rowHandler = null) {
-			var items = new List<dynamic>();
+		public static List<DyObj> ToDynamicObjects(this DataTable dt, Func<DataRow, DyObj> rowHandler = null) {
+			var items = new List<DyObj>();
 			if(null == rowHandler) {
 				foreach(DataRow dr in dt.Rows) {
 					items.Add(dr.ToDynamicObject());
 			}
 
 			var i = new DyObj();
-			foreach(DataColumn col in row.Table.Columns) {
-				if(null == predicate || predicate(col.ColumnName)) {
+			if(null == predicate) {
+				foreach(DataColumn col in row.Table.Columns) {
 					i[col.ColumnName] = row[col] is DBNull ? null : row[col];
 				}
 			}
+			else {
+				foreach(DataColumn col in row.Table.Columns) {
+					if(predicate(col.ColumnName)) {
+						i[col.ColumnName] = row[col] is DBNull ? null : row[col];
+					}
+				}
+			}
 
 			return i;
 		}
 		///		, nullable:该列是否可空
 		/// }
 		/// </summary>
-		List<dynamic> _columns = new List<dynamic>(17);
+		List<DyObj> _columns = new List<DyObj>(17);
 		/// <summary>
 		/// 拥有主键的列名。
 		/// </summary>
-		List<dynamic> _pkColumns = new List<dynamic>(3);
+		List<DyObj> _pkColumns = new List<DyObj>(3);
 
 		public string RelationName { get; private set; }
 
 		public List<string> ColumnNames {
 			get {
-				return (from c in this._columns select c.name as string).ToList();
+				return (from c in this._columns select c["name"] as string).ToList();
 			}
 		}
 
 			}
 
 			var cols = sql.Init("select column_name, case when data_default is null then 0 else 1 end as data_default, data_length, case nullable when 'Y' then 1 when 'N' then 0 end as nullable from user_tab_columns where table_name = upper(:relationName)").AddParameter("relationName", this.RelationName).GetDataTable().ToDynamicObjects();
-			foreach(var c in cols) {
-				var col = (from column in this._columns where column.name == c.column_name select column).Single();
+			foreach(dynamic c in cols) {
+				dynamic col = (from dynamic column in this._columns where column.name == c.column_name select column).Single();
 
 				col.nullable = 1 == c.nullable;
 
 		public DyObj GetMeta() {
 			dynamic result = new DyObj();
 
-			foreach(var col in this._columns) {
+			foreach(dynamic col in this._columns) {
 				dynamic meta = new DyObj();
 				meta.type = col.type;
 				meta.nullable = col.nullable;
 			if(0 == this._pkColumns.Count) {
 				var whereClause = new StringBuilder();
 				var parameters = new List<DbParameter>();
-				foreach(var c in this._columns) {
+				foreach(dynamic c in this._columns) {
 					object value = input[c.name];
 
 					if(ObjectHelper.IsNil(value)) {
 			else {
 				var sb = new StringBuilder("delete from {0} where ".Fmt(this.RelationName));
 				var parameters = new List<DbParameter>();
-				foreach(var col in this._pkColumns) {
+				foreach(dynamic col in this._pkColumns) {
 					sb.Append(string.Format("{0} = :{0} AND ", col.name));
 					var p = sql.CreateParameter();
 					p.ParameterName = col.name;
 
 			var parameters = new List<DbParameter>();
 			var outParam = new List<DbParameter>();
-			foreach(var c in this._columns) {
+			foreach(dynamic c in this._columns) {
 				var value = input[c.name];
 
 				colClause.Append(c.name + ",");
 						valClause.Append("null,");
 					}
 				}
-				else if(value is DbValue) {
-					switch((DbValue)value) {
-						case DbValue.CurrentTimestamp:
-						default:
-							valClause.Append("CURRENT_TIMESTAMP,");
-							break;
-					}
+				else if(value is LiteralValue) {
+					valClause.Append(string.Format("{0},", (value as LiteralValue).Value));
+				}
+				else if(value is DbParameter) {
+					var p = value as DbParameter;
+					valClause.Append(string.Format(":{0},", p.ParameterName));
+					parameters.Add(p);
 				}
 				else {
-					if(value is DbParameter) {
-						var p = value as DbParameter;
-						valClause.Append(string.Format(":{0},", p.ParameterName));
-						parameters.Add(p);
+					valClause.Append(string.Format(":{0},", c.name));
+
+					var p = sql.CreateParameter();
+					p.ParameterName = c.name;
+#if DEBUG
+					try {
+						p.Value = value;
 					}
-					else {
-						valClause.Append(string.Format(":{0},", c.name));
-
-						var p = sql.CreateParameter();
-						p.ParameterName = c.name;
-#if DEBUG
-						try {
-							p.Value = value;
-						}
-						catch(ArgumentException) {
-							throw new ArgumentException(string.Format("生成查询参数时出错。\nname : {0}\nvalue : {1}", c.name, value.ToString()));
-						}
+					catch(ArgumentException) {
+						throw new ArgumentException(string.Format("生成查询参数时出错。\nname : {0}\nvalue : {1}", c.name, value.ToString()));
+					}
 #else
 							p.Value = value;
 #endif
-						parameters.Add(p);
-					}
+					parameters.Add(p);
 				}
 			}
 			colClause.Remove(colClause.Length - 1, 1);
 
 			var setClause = new StringBuilder();
 			var parameters = new List<DbParameter>();
-			foreach(var c in this._columns) {
+			foreach(dynamic c in this._columns) {
 				if(!input.ContainsAttr(c.name)) {
 					continue;
 				}
 				if(ObjectHelper.IsNil(value)) {
 					setClause.Append(string.Format("{0} = null,", c.name));
 				}
-				else if(value is DbValue) {
-					switch((DbValue)value) {
-						case DbValue.CurrentTimestamp:
-						default:
-							setClause.Append(string.Format("{0} = CURRENT_TIMESTAMP,", c.name));
-							break;
-					}
+				else if(value is LiteralValue) {
+					setClause.Append(string.Format("{0} = {1},", c.name, (value as LiteralValue).Value));
+				}
+				else if(value is DbParameter) {
+					var p = value as DbParameter;
+					setClause.Append(string.Format("{0} = :{1},", c.name, p.ParameterName));
+					parameters.Add(p);
 				}
 				else {
-					if(value is DbParameter) {
-						var p = value as DbParameter;
-						setClause.Append(string.Format("{0} = :{0},", p.ParameterName));
-						parameters.Add(p);
+					setClause.Append(string.Format("{0} = :{0},", c.name));
+					var p = sql.CreateParameter();
+					p.ParameterName = c.name;
+#if DEBUG
+					try {
+						p.Value = value;
 					}
-					else {
-						setClause.Append(string.Format("{0} = :{0},", c.name));
-						var p = sql.CreateParameter();
-						p.ParameterName = c.name;
-#if DEBUG
-						try {
-							p.Value = value;
-						}
-						catch(ArgumentException) {
-							throw new ArgumentException(string.Format("生成查询参数时出错。\nname : {0}\nvalue : {1}", c.name, value.ToString()));
-						}
+					catch(ArgumentException) {
+						throw new ArgumentException(string.Format("生成查询参数时出错。\nname : {0}\nvalue : {1}", c.name, value.ToString()));
+					}
 #else
 							p.Value = value;
 #endif
-						parameters.Add(p);
-					}
+					parameters.Add(p);
 				}
 			}
 			setClause.Remove(setClause.Length - 1, 1);
 
 			var sb = new StringBuilder("update {0} set {1} where ".Fmt(this.RelationName, setClause.ToString()));
 			var pk_parameters = new List<DbParameter>();
-			foreach(var col in this._pkColumns) {
+			foreach(dynamic col in this._pkColumns) {
 				var p = sql.CreateParameter();
 				p.ParameterName = "pk_" + col.name;
 				p.Value = input[col.name];
 		}
 	}
 
-	public enum DbValue {
-		CurrentTimestamp
+	public class LiteralValue {
+		public readonly string  Value;
+		public LiteralValue(object value) {
+			this.Value = value.ToString();
+		}
 	}
 }
 		string _alias;
 
 		#region 需要参与reset的部分。
-		DyObj _column;
-		List<Condition> _conditions = new List<Condition>(23);
-		List<dynamic> _sorts = new List<dynamic>(3);
+		List<Condition> _conditions = new List<Condition>(7);
+		List<DyObj> _sorts = new List<DyObj>(3);
+		List<DyObj> _joins = new List<DyObj>(3);
 
 		decimal _limit = 0;
 		decimal _offset = 0;
 
-		public Func<dynamic, dynamic> RowHandler;
+		public Func<DyObj, DyObj> RowHandler;
 		public SqlHelper Sql { get; private set; }
-		#endregion
 
 		/// <example>
 		/// <code>
 		///			, 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 DyObj Column { get; private set; }
+		#endregion
 
 		public string Text {
 			get {
-				return _GenerateSQLObject(this)["txt"] as string;
+				return this._GenerateSQLObject()["txt"] as string;
 			}
 		}
 
 		}
 
 		public DataTable GetDataTable() {
-			dynamic sql = _GenerateSQLObject(this);
+			dynamic sql = this._GenerateSQLObject();
 			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() {
+		public List<DyObj> Execute() {
 			var dt = this.GetDataTable();
 
 			return dt.ToDynamicObjects((dr) => {
-				var dyRow = _ParseQueryResult(dr, this);
+				var dyRow = this._ParseQueryResult(dr);
 				return null == this.RowHandler ? dyRow : this.RowHandler(dyRow);
 			});
 		}
 		public void Reset() {
 			this._conditions.Clear();
 			this._sorts.Clear();
+			this._joins.Clear();
 			this._limit = 0;
 			this._offset = 0;
 
 		}
 
 		public decimal Count() {
-			dynamic sqlObj = _GenerateSQLObject(this);
+			dynamic sqlObj = this._GenerateSQLObject();
 			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());
 		/// <summary>
 		/// 只对自身拥有的列进行条件过滤,无法过滤Join进来的子查询计划的列。
 		/// </summary>
-		public QueryPlan Filter(ConditionType condType, string colName, object value = null) {
-			if(!this._column.ContainsAttr(colName)) {
+		public QueryPlan Filter(string colName, ConditionType condType, object value = null, string colFormat = null) {
+			if(!this.Column.ContainsAttr(colName)) {
 				throw new ArgumentException("在当前的查询计划中没有找到此列 : " + colName);
 			}
 
-			return this.Filter(new Condition(condType, this._column[colName] as DyObj, value));
+			return this.Filter(Condition.Simple(this.Column[colName] as DyObj, condType, value, colFormat));
 		}
 
 		public QueryPlan Filter(Condition cond) {
 			return this;
 		}
 
-		public QueryPlan Join(QueryPlan queryPlan, JoinType joinType, dynamic leftCol, ConditionType condType, dynamic rightCol) {
+		public QueryPlan EqualJoin(QueryPlan queryPlan, JoinType joinType, string leftColName, string rightColName, string leftColFormat = null, string rightColFormat = null) {
+			return this.Join(queryPlan, joinType, leftColName, ConditionType.Equal, rightColName, leftColFormat, rightColFormat);
+		}
+
+		public QueryPlan EqualJoin(QueryPlan queryPlan, JoinType joinType, DyObj leftCol, DyObj rightCol, string leftColFormat = null, string rightColFormat = null) {
+			return this.Join(queryPlan, joinType, leftCol, ConditionType.Equal, rightCol, leftColFormat, rightColFormat);
+		}
+
+		public QueryPlan Join(QueryPlan queryPlan, JoinType joinType, string leftColName, ConditionType condType, string rightColName, string leftColFormat = null, string rightColFormat = null) {
+			dynamic left = (from item in this.Column where item.Key.ToUpper() == leftColName.ToUpper() select item.Value).Single();
+			dynamic right = (from item in queryPlan.Column where item.Key.ToUpper() == rightColName.ToUpper() select item.Value).Single();
+			return this.Join(queryPlan, joinType, left, condType, right, leftColFormat, rightColFormat);
+		}
+
+		public QueryPlan Join(QueryPlan queryPlan, JoinType joinType, DyObj leftCol, ConditionType condType, DyObj rightCol, string leftColFormat = null, string rightColFormat = null) {
 			switch(condType) {
-				case ConditionType.Equal:
+				case ConditionType.And:
+				case ConditionType.Or:
+				case ConditionType.IsNotNull:
+				case ConditionType.IsNull:
+				case ConditionType.In:
+				case ConditionType.NotIn:
+					throw new ArgumentException("不支持在JOIN中使用此条件 : " + condType);
+				default:
 					break;
-				default:
-					throw new ArgumentException("不支持在JOIN中使用此条件 : " + condType.ToString());
 			}
 
-			dynamic left = this._FindColumn(leftCol._guid as string);
+			var left = this._FindColumn(leftCol["_guid"] as string);
 			if(null == left) {
-				throw new ArgumentException("在当前的查询计划及其子计划中没有找到此列 : " + leftCol.column_name);
+				throw new ArgumentException("在当前的查询计划及其子计划中没有找到此列 : " + leftCol["attr_name"]);
 			}
 
-			dynamic right = queryPlan._FindColumn(rightCol._guid as string);
+			var right = queryPlan._FindColumn(rightCol["_guid"] as string);
 			if(null == right) {
-				throw new ArgumentException("在要Join的目标中没有找到此列 : " + rightCol.column_name);
+				throw new ArgumentException("在要Join的目标中没有找到此列 : " + rightCol["attr_name"]);
 			}
 
 			dynamic join = new DyObj();
-			join.column = rightCol;
-			join.plan = queryPlan;
+			join.left = leftCol;
+			join.right = rightCol;
+			join.target_plan = queryPlan;
 			join.condition_type = condType;
 			join.join_type = joinType;
+			join.left_format = leftColFormat;
+			join.right_format = rightColFormat;
 
-			left.join = join;
+			this._joins.Add(join);
 
 			return this;
 		}
 			return this;
 		}
 
-		public QueryPlan Sort(dynamic col, SortDirection direction = SortDirection.Asc, SortNullPosition nullPos = SortNullPosition.NullFirst) {
-			dynamic result = this._FindColumn(col._guid);
+		public QueryPlan Sort(string colName, SortDirection direction = SortDirection.Asc, SortNullPosition nullPos = SortNullPosition.NullFirst) {
+			var col = (from item in this.Column where colName.ToUpper() == item.Key.ToUpper() select item.Value as DyObj).Single();
+			return this.Sort(col, direction, nullPos);
+		}
+
+		public QueryPlan Sort(DyObj col, SortDirection direction = SortDirection.Asc, SortNullPosition nullPos = SortNullPosition.NullFirst) {
+			dynamic c = col;
+			dynamic result = this._FindColumn(c._guid);
 			if(null == result) {
-				throw new ArgumentException("在当前的查询计划及其子计划中没有找到此列 : " + col.column_name);
+				throw new ArgumentException("在当前的查询计划及其子计划中没有找到此列 : " + c.attr_name);
 			}
 
 			dynamic sort = new DyObj();
 		}
 
 		DyObj _FindColumn(string guid) {
-			foreach(var kv in this._column) {
+			foreach(var kv in this.Column) {
 				dynamic col = kv.Value;
 				if(col._guid == guid) {
 					return col;
 		void _Setup() {
 			dynamic parsedSqlObj = _ParseSql(this._originalSQLTxt, this.Sql);
 			this._relation = parsedSqlObj.relation;
-			this._column = parsedSqlObj.column;
+			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) {
+		DyObj _FindQueryPlanIndex(QueryPlan targetPlan, int planIndex = 0) {
 			dynamic result = new DyObj();
 			result.found = false;
 
-			if(null == rootPlan) {
+			if(this == targetPlan) {
 				result.found = true;
+				result.index = planIndex;
 			}
 			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;
-							}
+				foreach(var item in this._joins) {
+					++planIndex;
+					var join = item["target_plan"] as QueryPlan;
+
+					if(join == targetPlan) {
+						result.found = true;
+						result.index = planIndex;
+						break;
+					}
+
+					if(join._joins.Count > 0) {
+						dynamic r = join._FindQueryPlanIndex(targetPlan, planIndex);
+						if(r.found) {
+							return r;
 						}
 					}
 				}
 			}
 
-			result.index = tbIndex;
 			return result;
 		}
 
-		static string _GenerateQueryPlanAlias(QueryPlan plan, int index) {
-			return string.IsNullOrEmpty(plan._alias) ? "t" + index : (plan._alias as string).Substr();
+		string _GenerateQueryPlanAlias(int index) {
+			return string.IsNullOrEmpty(this._alias) ? "t" + index : (this._alias as string).Substr();
 		}
 
-		static DyObj _GenerateSQLObject(QueryPlan queryPlan, QueryPlan rootPlan = null, int conditionIndexer = 0, List<DbParameter> parameters = null) {
+		DyObj _GenerateSQLObject(QueryPlan rootPlan = null, int conditionIndex = 0, List<DbParameter> parameters = null) {
 			parameters = parameters ?? new List<DbParameter>();
+			rootPlan = rootPlan ?? this;
 
 			var joinClause = new StringBuilder();
 			var contextualColumns = new DyObj();
 			var columnClause = new StringBuilder();
-			var tbIndex = (int)_FindQueryPlanIndex(rootPlan, queryPlan)["index"];
-			var tbAlias = _GenerateQueryPlanAlias(queryPlan, tbIndex);
+			var planIndex = (int)rootPlan._FindQueryPlanIndex(this)["index"];
+			var planAlias = this._GenerateQueryPlanAlias(planIndex);
 
-			foreach(var kv in queryPlan._column) {
+			foreach(var kv in this.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);
+						throw new ArgumentException("使用聚集函数时,必须使用列别名 : " + c.attr_name);
 					}
 
-					if(c.original.StartsWith(tbAlias + ".")) {
+					if(c.original.StartsWith(planAlias + ".")) {
 						//如果解析出来的列没有列别名,又以自己的表别名限定了,那么其上下文相关的列名与其上下文无关列名相同。
 						contextualColName = c.original;
 					}
 					else {
-						contextualColName = string.Format("{0}.{1}", tbAlias, c.original);
-						contextualColAlias = string.Format("{0}_{1}", tbAlias, (c.original as string).Substr());
+						contextualColName = string.Format("{0}.{1}", planAlias, c.original);
+						contextualColAlias = string.Format("{0}_{1}", planAlias, (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());
+					contextualColName = string.Format("{0}.{1}", planAlias, (c.alias as string));
+					contextualColAlias = string.Format("{0}_{1}", planAlias, (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);
+			foreach(dynamic item in this._joins) {
+				dynamic join = (item.target_plan as QueryPlan)._GenerateSQLObject(rootPlan, conditionIndex, parameters);
 
-					conditionIndexer = join._condition_indexer;
+				conditionIndex = join._condition_index;
 
-					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;
+				var joinTxt = string.Empty;
+				var joinTypeTxt = string.Empty;
+				var joinType = (JoinType)item.join_type;
+				var conditionTxt = string.Empty;
+				var conditionType = (ConditionType)item.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(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("代码不该到达这个位置");
-					}
+				var leftColName = (contextualColumns[item.left._guid] as DyObj)["name"] as string;
+				var rightColName = (join._contextual_columns[item.right._guid] as DyObj)["name"] as string;
+				switch(conditionType) {
+					case ConditionType.Equal:
+						conditionTxt = "{0} = {1}".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.GreaterOrEqual:
+						conditionTxt = "{0} >= {1}".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.GreaterThan:
+						conditionTxt = "{0} > {1}".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.LeftLike:
+						conditionTxt = "{0} LIKE :{1}||'%'".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.RightLike:
+						conditionTxt = "{0} LIKE '%'||:{1}".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.Like:
+						conditionTxt = "{0} LIKE '%'||:{1}||'%'".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.LesserOrEqual:
+						conditionTxt = "{0} <= :{1}".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.LesserThan:
+						conditionTxt = "{0} < :{1}".Fmt(leftColName, rightColName);
+						break;
+					case ConditionType.NotEqual:
+						conditionTxt = "{0} <> :{1}".Fmt(leftColName, rightColName);
+						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);
+				joinClause.AppendFormat("\n{0} (\n{1}\n) {2} ON {3}", joinTypeTxt, join.txt, join._contextual_tb_alias, conditionTxt);
+				joinClause.Append(join._join_clause);
 
-					contextualColumns.Merge(join._contextual_columns);
-				}
+				contextualColumns.Merge(join._contextual_columns);
 			}
 
 			var txt = new StringBuilder();
-			if(tbIndex > 0) {
-				txt.Append(queryPlan._originalSQLTxt);
-				if(string.IsNullOrEmpty(queryPlan._alias)) {
-					txt.Append(" " + tbAlias);
+			if(planIndex > 0) {
+				txt.Append(this._originalSQLTxt);
+				if(string.IsNullOrEmpty(this._alias)) {
+					txt.Append(" " + planAlias);
 				}
 			}
 			else {
 					}
 				}
 				txt.Remove(txt.Length - 1, 1);
-				txt.AppendFormat("\nFROM (\n{0}\n) {1}", queryPlan._originalSQLTxt, string.IsNullOrEmpty(queryPlan._alias) ? tbAlias : queryPlan._alias);
+				txt.AppendFormat("\nFROM (\n{0}\n) {1}", this._originalSQLTxt, string.IsNullOrEmpty(this._alias) ? planAlias : this._alias);
 				txt.Append(joinClause.ToString());
 			}
 
-			if(queryPlan._conditions.Count > 0) {
+			if(this._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));
+				foreach(var cond in this._conditions) {
+					txt.AppendFormat(" {0} AND", _ParseCondition(cond, parameters, contextualColumns, this.Sql.CreateParameter, ref conditionIndex));
 				}
 				txt.Remove(txt.Length - 4, 4);
 			}
 
-			if(queryPlan._sorts.Count > 0) {
+			if(this._sorts.Count > 0) {
 				txt.Append("\nORDER BY");
-				foreach(var s in queryPlan._sorts) {
+				foreach(dynamic s in this._sorts) {
 					txt.AppendFormat(" {0} {1} {2},"
 						, (contextualColumns[s.column_guid]).name
 						, SortDirection.Asc == (SortDirection)s.direction ? "ASC" : "DESC"
 				txt.Remove(txt.Length - 1, 1);
 			}
 
-			if(queryPlan._offset >= 0) {
+			if(this._offset >= 0) {
 				txt.Insert(0, string.Format(@"SELECT * FROM (
 SELECT {0}.*, ROWNUM AS ""{1}"" FROM (
-", tbAlias, Rownum));
+", planAlias, 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);
+) WHERE ""{2}"" > {1}", planAlias, this._offset, Rownum);
+				if(this._limit > 0) {
+					txt.AppendFormat(@" AND ""{2}"" <= {1}", planAlias, this._offset + this._limit, Rownum);
 				}
 			}
-			else if(queryPlan._limit > 0) {
+			else if(this._limit > 0) {
 				txt.Insert(0, string.Format(@"SELECT * FROM (
-SELECT {0}.*, ROWNUM AS ""{1}"" FROM (", tbAlias, Rownum));
+SELECT {0}.*, ROWNUM AS ""{1}"" FROM (", planAlias, Rownum));
 				txt.AppendFormat(@"
 ) {0}
-) WHERE ""{2}"" <= {1}", tbAlias, queryPlan._limit, Rownum);
+) WHERE ""{2}"" <= {1}", planAlias, this._limit, Rownum);
 			}
 
 			dynamic result = new DyObj();
 			result.txt = txt.ToString();
 			result.parameters = parameters;
 			result._contextual_columns = contextualColumns;
-			result._contextual_tb_alias = tbAlias;
+			result._contextual_tb_alias = planAlias;
 
-			result._condition_indexer = conditionIndexer;
+			result._condition_index = conditionIndex;
 			result._join_clause = joinClause.ToString();
 
 			return result;
 		}
 
+		DyObj _ParseQueryResult(DataRow dr, QueryPlan rootPlan = null, DyObj rightCol = null) {
+			rootPlan = rootPlan ?? this;
+
+			dynamic result = new DyObj();
+			var planAlias = this._GenerateQueryPlanAlias((int)rootPlan._FindQueryPlanIndex(this)["index"]);
+
+			foreach(var kv in this.Column) {
+				dynamic c = kv.Value;
+				var colName = "{0}_{1}".Fmt(planAlias, string.IsNullOrEmpty(c.alias) ? c.attr_name as string : (c.alias as string).Substr());
+				var value = dr[colName];
+
+				if(null != rightCol && ObjectHelper.IsNil(value)) {
+					return null;
+				}
+
+				result[c.attr_name] = value is DBNull ? null : value;
+			}
+
+			foreach(dynamic item in this._joins) {
+				result[item.left.attr_name] = (item.target_plan as QueryPlan)._ParseQueryResult(dr, rootPlan, item.right);
+			}
+
+			if(dr.Table.Columns.Contains(Rownum)) {
+				result[Rownum] = dr[Rownum];
+			}
+
+			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) {
 			}
 
 			var name = (columns[condition.Column["_guid"] as string] as DyObj)["name"] as string;
+			if(!string.IsNullOrEmpty(condition.ColumnFormat)) {
+				name = string.Format(condition.ColumnFormat, name);
+			}
 
 			switch(condition.Type) {
 				case ConditionType.Equal:
 
 			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 {
 		Right,
 		Inner
 	}
-
-	enum SqlTxtStatus {
-		Normal,
-		Limit,
-		Offset,
-		Sort,
-		Condition,
-		Join,
-		SubJoin
-	}
 }
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.