/// <summary> /// 分析DataKey中Model属性的字符串,分离出主从各表信息用于 /// 下一步生成表连接子句 /// </summary> /// <param name="key"></param> /// <returns></returns> IEnumerable <JoinInfo> GetJoinInfos(DataGateKey key) { if (key.Model.IsEmpty()) { return(null); } key.Model = Regex.Replace(key.Model, "\\s+", " "); string[] models = key.Model.Replace(">", ">%").Replace("=", "=%").Split('%'); return(models.Select(n => { n = n.Trim(); string joinFlag = null; string alias = null; string name = null; if (n.EndsWith(">") || n.EndsWith("=")) { joinFlag = n.Last().ToString(); n = n.Substring(0, n.Length - 1); } var arr = n.Split(' '); name = arr[0]; if (arr.Length > 1) { alias = arr[1]; } TableMeta tm = null; //如果Models.json中没有定义表模型,则找Keys.json中 //的QueryFields属性生成一个表模型并放到模型字典中 if (!_tableMetas.ContainsKey(name)) { if (key.QueryFields.IsEmpty()) { throw new Exception($"在解析[Key:{key.Key}]的过程中,没有找到[Model:{name}]的定义:在*Models.json中定义的模型," + $"或者{nameof(DataGateKey.QueryFields)}中定义的字段列表"); } tm = CreateTableMeta(key.Model, new JValue(key.QueryFields)); _tableMetas.Add(tm.Name, tm); } else { tm = _tableMetas[name]; } var db = GetTempDB(key); TranslateModelNames(db, tm); var joinInfo = new JoinInfo { Name = name, Table = tm, Alias = alias ?? //当模型名和数据库实际名称不对应时,(是手动配置指定的),则用模型名作查询的别名 (tm.DbName == db.GetDbObjName(tm.Name) ? null : tm.Name), JoinFlag = joinFlag }; return joinInfo; })); }
private void CheckPlusField(TableMeta tm) { _callDeepth++; if (_callDeepth > 10) { throw new Exception($"递归层次太多,是否产生了循环引用在模型:'{tm.Name}'?"); } int cnt = tm.Fields.Count; //从后往前循环加,是为了让排后面的属性优先级高于前面的同名属性 for (int i = cnt - 1; i >= 0; i--) { if (tm.Fields[i].Name.StartsWith("+")) { var name = tm.Fields[i].Name.Substring(1); var child = _tableMetas[name]; CheckPlusField(child); tm.Fields.RemoveAt(i); int j = i; foreach (var field in child.Fields.ToArray()) { //如果已有相同属性则跳过 if (tm.Fields.All(f => !f.Name.Equals(field.Name, StringComparison.OrdinalIgnoreCase))) { tm.Fields.Insert(j++, field); } } } } _callDeepth--; }
//更新单条记录的条件固定为主键值相等 private string CreateKeyFilter(TableMeta tableMeta) { var r = String.Join(" AND ", tableMeta.Fields.Where(t => t.PrimaryKey) .Select(t => $"{t.FixDbName}=@{t.DbName}")); if (r.IsEmpty()) { throw new Exception($"PrimaryKey(s) not defined in {tableMeta.Name}"); } return(r); }
void TranslateModelNames(DBHelper db, TableMeta tm) { tm.DbName = tm.DbName ?? db.GetDbObjName(tm.Name); tm.FixDbName = db.AddFix(tm.DbName); foreach (var fm in tm.Fields) { if (fm.DbName.IsEmpty()) { fm.DbName = db.GetDbObjName(fm.Name); } fm.FixDbName = db.AddFix(fm.DbName); } }
/// <summary> /// 在结果集中对主表数据去重,并获取子表明细数据放到主表指定字段值中 /// </summary> /// <param name="tableMeta"></param> /// <param name="dt"></param> /// <returns></returns> private JArray CreateMasterArray(TableMeta tableMeta, DataTable dt) { var pkey = tableMeta.PrimaryKey; JArray newDt = new JArray(); var ids = dt.Rows.Cast <DataRow>().Select(dr => dr[pkey.DbName]).Distinct(); ids.Each(id => { var drs = dt.Rows.Cast <DataRow>().Where(dr => dr[pkey.DbName].Equals(id)); var firstDr = drs.First(); var newRow = new JObject(); tableMeta.Fields.Each(col => { if (col.IsArray) { newRow[col.Name] = GetChildItems(col, drs); return; } else if (col.UIType == Consts.OperatorUIType) { return; } if (col.ForeignField.IsEmpty() && !dt.Columns.Contains(col.DbName)) { return; } if (!col.ForeignField.IsEmpty() && !dt.Columns.Contains(col.Name)) { return; } //在定义了外表字段时,表查询中是用别名表示外表字段名,所以这里应该分开处理 if (!col.ForeignField.IsEmpty()) { if (dt.Columns.Contains(col.Name)) { newRow[col.Name] = new JValue(firstDr[col.Name]); } } else { newRow[col.Name] = new JValue(firstDr[col.DbName]); } }); newDt.Add(newRow); }); return(newDt); }
//在新增记录时,对于单一主键的记录,检查主键字段是否自增或是guid,是否值为空, //如果是自增,则去掉传过来的值,由库自动生成,如果是guid并且为空,则生成一个guid private void CheckPrimaryKey(TableMeta tableMeta, IDictionary <string, object> psin, out string id, out string getMaxIdSql) { id = null; getMaxIdSql = null; var pKeyField = tableMeta.PrimaryKey; if (pKeyField == null) { return; } if (pKeyField.DataType == "Number") { getMaxIdSql = $"select max({pKeyField.FixDbName}) from {tableMeta.FixDbName}"; } var pkey = psin.Keys.FirstOrDefault(k => k.Equals(pKeyField.Name, StringComparison.OrdinalIgnoreCase)); //没有传主键字段过来 if (pkey == null) { pkey = pKeyField.Name; psin.Add(pkey, null); } //当主键为Number型时,认为是自增字段,为空时从参数中去掉,让数据库自动生成 if (pKeyField.DataType == "Number") { psin.Remove(pkey); } //非number型,并且为空,则认为是32位的guid字符串,为它自动生成 else if (CommOp.IsEmpty(psin[pkey])) { id = CommOp.NewId(); psin[pkey] = id; } else { id = CommOp.ToStr(psin[pkey]); } }
//生成排序子句,排序子句的传入规则是 field1 a/d field2 a/d ... private void CreateOrderStr(DataGateKey gkey, TableMeta tableMeta, IDictionary <string, object> ps) { if (!ps.ContainsKey(Consts.SortKey)) { return; } var sortStr = CommOp.ToStr(ps[Consts.SortKey]); ps.Remove(Consts.SortKey); var sortArr = sortStr.Split(' '); List <string> sorts = new List <string>(); for (var i = 0; i < sortArr.Length - 1; i += 2) { string f = sortArr[i]; string field = GetSortField(f, gkey); if (field.IsEmpty()) { continue; } string ad = sortArr[i + 1]; if (ad.StartsWith("d")) { sorts.Add(field + " desc"); } else { sorts.Add(field.ToStr()); } } string orderby = String.Join(",", sorts); if (!orderby.IsEmpty()) { gkey.OrderBy = orderby; } }
//将DataTable结果表中来自数据库的名称转成对象属性名称 private void ReNameColumns(TableMeta tableMeta, DataTable dt) { //tableMeta==null表示是直接执行的sql,Model为空 if (tableMeta == null) { foreach (DataColumn dc in dt.Columns) { dc.ColumnName = DB.DbNameConverter.ToPropName(dc.ColumnName); } } else { var fieldsDict = tableMeta.Fields .ToDictionary(f => f.DbName, f => f.Name); foreach (DataColumn dc in dt.Columns) { if (fieldsDict.ContainsKey(dc.ColumnName)) { dc.ColumnName = fieldsDict[dc.ColumnName]; } } } }
/// <summary> /// 根据传过来的地址栏filter参数来获取查询条件 /// 与服务端配置的filter合并 /// </summary> /// <param name="tableMeta"></param> /// <param name="ps"></param> /// <returns></returns> private void CreateFilterStr(DataGateKey gkey, TableMeta tableMeta, IDictionary <string, object> ps) { if (!ps.ContainsKey(Consts.FilterKey)) { return; } var filterStr = CommOp.ToStr(ps[Consts.FilterKey]); var requests = JsonConvert.DeserializeObject <FilterRequest[]>(filterStr); ps.Remove(Consts.FilterKey); filterStr = string.Join(" and ", requests.Select(r => { var field = tableMeta.Fields.FirstOrDefault(f => f.Name.Equals(r.Name, StringComparison.OrdinalIgnoreCase)); if (field == null) { return(null); } string left = field.ForeignField.IsEmpty() ? (gkey.TableJoins[0].Alias ?? tableMeta.Name) + "." + field.Name : field.ForeignField; //当有sql语句并且有模型定义时 if (!gkey.Sql.IsEmpty() && gkey.TableJoins.Count > 0) { left = field.Name; } string pName = r.Name + "_f"; //加后缀以免和未知的key冲突 ps[pName] = r.Value; switch (r.Operator) { case "e": return($"{left}=@{pName}"); //判断日期相等,日期相等比较特殊,很难精确相等, //因此转成只判断是否在当天 case "de": var date = CommOp.ToDateTime(r.Value).Date; var date1 = date.AddDays(1).AddTicks(-1); ps[pName] = date; ps[pName + 1] = date1; return($"{left} between @{pName} and @{pName}1"); case "ne": return($"{left}!=@{pName}"); case "in": ps[pName] = CommOp.ToStr(ps[pName]).Split(','); return($"{left} in (@{pName})"); case "nin": ps[pName] = CommOp.ToStr(ps[pName]).Split(','); return($"{left} not in (@{pName})"); case "i": ps[pName] = "%" + r.Value + '%'; return($"{left} like @{pName}"); case "ni": ps[pName] = "%" + r.Value + '%'; return($"{left} not like @{pName}"); case "lte": return($"{left} <= @{pName}"); case "gte": return($"{left} >= @{pName}"); case "bt": if (r.Value1.IsDefault()) { return($"{left} >= @{pName}"); } if (field.DataType == "Date" || field.DataType == "DateTime") { r.Value1 = CommOp.ToDateTime(r.Value1).Date.AddDays(1).AddTicks(-1); } ps[pName + 1] = r.Value1; return($"{left} between @{pName} and @{pName}1"); case "n": return($"{left} is null"); case "nn": return($"{left} is not null"); default: return(null); //throw new ArgumentException("非法的查询请求:" + r.Operator); } }).Where(r => r != null)); //与原有的gkey.Filter合并得到一个and条件 if (!filterStr.IsEmpty()) { gkey.Filter = gkey.Filter.IsEmpty() ? filterStr : $"({gkey.Filter}) and {filterStr}"; } }
/// <summary> /// 在结果集中对主表数据去重,并获取子表明细数据放到主表指定字段值中 /// </summary> /// <param name="tableMeta"></param> /// <param name="dt"></param> /// <returns></returns> private JArray CreateMasterArray(TableMeta tableMeta, DataTable dt) { var pkeys = tableMeta.PrimaryKeys; JArray newDt = new JArray(); var getPKeyValues = new Func <DataRow, string>(dr => { var keyValues = pkeys.Select(pkey => { return(dr[pkey.DbName].ToString()); }); return(String.Join("^", keyValues)); }); //查询主表中不重复的主键字段值 var keyRowsGroup = dt.Rows.Cast <DataRow>().GroupBy(dr => getPKeyValues(dr)).ToList(); //遍历DataTable结果集, 找到相同的主键DataRow,并组装子表所在子集合 keyRowsGroup.ForEach(drs => { var newRow = new JObject(); //遍历主表的每个字段,找出各类字段定义 tableMeta.Fields.Each(fm => { if (fm.IsArray) { _deep = 0; newRow[fm.Name] = GetChildItems(fm, drs); return; } else if (fm.DataType == Consts.OperatorUIType) { return; } if (fm.ForeignField.IsEmpty() && !dt.Columns.Contains(fm.DbName)) { return; } if (!fm.ForeignField.IsEmpty() && !dt.Columns.Contains(fm.Name)) { return; } var firstDr = drs.First(); //在定义了外表字段时,表查询中是用别名表示外表字段名,所以这里应该分开处理 if (!fm.ForeignField.IsEmpty()) { if (dt.Columns.Contains(fm.Name)) { newRow[fm.Name] = new JValue(firstDr[fm.Name]); } } else { newRow[fm.Name] = new JValue(firstDr[fm.DbName]); } }); newDt.Add(newRow); }); return(newDt); }
/// <summary> /// 判断是否是虚表(name和DbName不对应) /// </summary> /// <param name="meta"></param> /// <returns></returns> private bool IsVirtualTable(TableMeta meta) { return(DB.GetDbObjName(meta.Name) != meta.DbName); }
/// <summary> /// 根据名称查找字段,不分大小写 /// </summary> /// <param name="gkey"></param> /// <param name="name"></param> /// <returns></returns> public static FieldMeta GetField(this TableMeta tableMeta, string name) { return(tableMeta.Fields.FirstOrDefault(f => f.Name.Equals(name, StringComparison.OrdinalIgnoreCase))); }