/// <summary> /// Query query /// </summary> /// <param name="query"></param> public SqlStatement CreateQuery <TModel>(IEnumerable <KeyValuePair <String, Object> > query, String tablePrefix, params ColumnMapping[] selector) { var tableType = m_mapper.MapModelType(typeof(TModel)); var tableMap = TableMapping.Get(tableType); List <TableMapping> scopedTables = new List <TableMapping>() { tableMap }; bool skipParentJoin = true; SqlStatement selectStatement = null; KeyValuePair <SqlStatement, List <TableMapping> > cacheHit; if (!s_joinCache.TryGetValue($"{tablePrefix}.{typeof(TModel).Name}", out cacheHit)) { selectStatement = new SqlStatement($" FROM {tableMap.TableName} AS {tablePrefix}{tableMap.TableName} "); Stack <TableMapping> fkStack = new Stack <TableMapping>(); fkStack.Push(tableMap); // Always join tables? do { var dt = fkStack.Pop(); foreach (var jt in dt.Columns.Where(o => o.IsAlwaysJoin)) { var fkTbl = TableMapping.Get(jt.ForeignKey.Table); var fkAtt = fkTbl.GetColumn(jt.ForeignKey.Column); selectStatement.Append($"INNER JOIN {fkAtt.Table.TableName} AS {tablePrefix}{fkAtt.Table.TableName} ON ({tablePrefix}{jt.Table.TableName}.{jt.Name} = {tablePrefix}{fkAtt.Table.TableName}.{fkAtt.Name}) "); if (!scopedTables.Contains(fkTbl)) { fkStack.Push(fkTbl); } scopedTables.Add(fkAtt.Table); } } while (fkStack.Count > 0); // Add the heavy work to the cache lock (s_joinCache) if (!s_joinCache.ContainsKey($"{tablePrefix}.{typeof(TModel).Name}")) { s_joinCache.Add($"{tablePrefix}.{typeof(TModel).Name}", new KeyValuePair <SqlStatement, List <TableMapping> >(selectStatement.Build(), scopedTables)); } } else { selectStatement = cacheHit.Key.Build(); scopedTables = cacheHit.Value; } // Column definitions var columnSelector = selector; if (selector == null || selector.Length == 0) { columnSelector = scopedTables.SelectMany(o => o.Columns).ToArray(); } // columnSelector = scopedTables.SelectMany(o => o.Columns).ToArray(); List <String> flatNames = new List <string>(); var columnList = String.Join(",", columnSelector.Select(o => { var rootCol = tableMap.GetColumn(o.SourceProperty); skipParentJoin &= rootCol != null; if (!flatNames.Contains(o.Name)) { flatNames.Add(o.Name); return($"{tablePrefix}{o.Table.TableName}.{o.Name} AS {o.Name}"); } else if (skipParentJoin) { return($"{tablePrefix}{rootCol.Table.TableName}.{rootCol.Name}"); } else { return($"{tablePrefix}{o.Table.TableName}.{o.Name}"); } })); selectStatement = new SqlStatement($"SELECT {columnList} ").Append(selectStatement); // We want to process each query and build WHERE clauses - these where clauses are based off of the JSON / XML names // on the model, so we have to use those for the time being before translating to SQL Regex re = new Regex(m_extractionRegex); List <KeyValuePair <String, Object> > workingParameters = new List <KeyValuePair <string, object> >(query); // Where clause SqlStatement whereClause = new SqlStatement(); List <SqlStatement> cteStatements = new List <SqlStatement>(); // Construct while (workingParameters.Count > 0) { var parm = workingParameters.First(); workingParameters.RemoveAt(0); // Match the regex and process var matches = re.Match(parm.Key); if (!matches.Success) { throw new ArgumentOutOfRangeException(parm.Key); } // First we want to collect all the working parameters string propertyPath = matches.Groups[PropertyRegexGroup].Value, castAs = matches.Groups[CastRegexGroup].Value, guard = matches.Groups[GuardRegexGroup].Value, subProperty = matches.Groups[SubPropertyRegexGroup].Value; // Next, we want to construct the var otherParms = workingParameters.Where(o => { var sm = re.Match(o.Key); return(sm.Groups[PropertyRegexGroup].Value == propertyPath && sm.Groups[CastRegexGroup].Value == castAs); }).ToArray(); // Remove the working parameters if the column is FK then all parameters if (otherParms.Any() || !String.IsNullOrEmpty(guard) || !String.IsNullOrEmpty(subProperty)) { foreach (var o in otherParms) { workingParameters.Remove(o); } // We need to do a sub query IEnumerable <KeyValuePair <String, Object> > queryParms = new List <KeyValuePair <String, Object> >() { parm }.Union(otherParms); // Grab the appropriate builder var subProp = typeof(TModel).GetXmlProperty(propertyPath, true); if (subProp == null) { throw new MissingMemberException(propertyPath); } // Link to this table in the other? // Is this a collection? if (typeof(IList).GetTypeInfo().IsAssignableFrom(subProp.PropertyType.GetTypeInfo())) // Other table points at this on { var propertyType = subProp.PropertyType.StripGeneric(); // map and get ORM def'n var subTableType = m_mapper.MapModelType(propertyType); var subTableMap = TableMapping.Get(subTableType); var linkColumns = subTableMap.Columns.Where(o => scopedTables.Any(s => s.OrmType == o.ForeignKey?.Table)); var linkColumn = linkColumns.Count() > 1 ? linkColumns.FirstOrDefault(o => subProperty.StartsWith("source") ? o.SourceProperty.Name != "SourceUuid" : o.SourceProperty.Name == "SourceUuid") : linkColumns.FirstOrDefault(); // Link column is null, is there an assoc attrib? SqlStatement subQueryStatement = new SqlStatement(); var subTableColumn = linkColumn; string existsClause = String.Empty; if (linkColumn == null) { var tableWithJoin = scopedTables.Select(o => o.AssociationWith(subTableMap)).FirstOrDefault(); linkColumn = tableWithJoin.Columns.SingleOrDefault(o => scopedTables.Any(s => s.OrmType == o.ForeignKey?.Table)); var targetColumn = tableWithJoin.Columns.SingleOrDefault(o => o.ForeignKey?.Table == subTableMap.OrmType); subTableColumn = subTableMap.GetColumn(targetColumn.ForeignKey.Column); // The sub-query statement needs to be joined as well var lnkPfx = IncrementSubQueryAlias(tablePrefix); subQueryStatement.Append($"SELECT {lnkPfx}{tableWithJoin.TableName}.{linkColumn.Name} FROM {tableWithJoin.TableName} AS {lnkPfx}{tableWithJoin.TableName} WHERE "); existsClause = $"{lnkPfx}{tableWithJoin.TableName}.{targetColumn.Name}"; //throw new InvalidOperationException($"Cannot find foreign key reference to table {tableMap.TableName} in {subTableMap.TableName}"); } // Local Table var localTable = scopedTables.Where(o => o.GetColumn(linkColumn.ForeignKey.Column) != null).FirstOrDefault(); if (String.IsNullOrEmpty(existsClause)) { existsClause = $"{tablePrefix}{localTable.TableName}.{localTable.GetColumn(linkColumn.ForeignKey.Column).Name}"; } // Guards var guardConditions = queryParms.GroupBy(o => re.Match(o.Key).Groups[GuardRegexGroup].Value); int nGuards = 0; foreach (var guardClause in guardConditions) { var subQuery = guardClause.Select(o => new KeyValuePair <String, Object>(re.Match(o.Key).Groups[SubPropertyRegexGroup].Value, o.Value)).ToList(); // TODO: GUARD CONDITION HERE!!!! if (!String.IsNullOrEmpty(guardClause.Key)) { StringBuilder guardCondition = new StringBuilder(); var clsModel = propertyType; while (clsModel.GetTypeInfo().GetCustomAttribute <ClassifierAttribute>() != null) { var clsProperty = clsModel.GetRuntimeProperty(clsModel.GetTypeInfo().GetCustomAttribute <ClassifierAttribute>().ClassifierProperty); clsModel = clsProperty.PropertyType.StripGeneric(); var redirectProperty = clsProperty.GetCustomAttribute <SerializationReferenceAttribute>()?.RedirectProperty; if (redirectProperty != null) { clsProperty = clsProperty.DeclaringType.GetRuntimeProperty(redirectProperty); } guardCondition.Append(clsProperty.GetCustomAttributes <XmlElementAttribute>().First().ElementName); if (typeof(IdentifiedData).GetTypeInfo().IsAssignableFrom(clsModel.GetTypeInfo())) { guardCondition.Append("."); } } subQuery.Add(new KeyValuePair <string, object>(guardCondition.ToString(), guardClause.Key.Split('|'))); } // Generate method var prefix = IncrementSubQueryAlias(tablePrefix); var genMethod = typeof(QueryBuilder).GetGenericMethod("CreateQuery", new Type[] { propertyType }, new Type[] { subQuery.GetType(), typeof(String), typeof(ColumnMapping[]) }); subQueryStatement.And($" {existsClause} IN ("); nGuards++; existsClause = $"{prefix}{subTableColumn.Table.TableName}.{subTableColumn.Name}"; subQueryStatement.Append(genMethod.Invoke(this, new Object[] { subQuery, prefix, new ColumnMapping[] { subTableColumn } }) as SqlStatement); //// TODO: Check if limiting the the query is better //if (guardConditions.Last().Key != guardClause.Key) // subQueryStatement.Append(" INTERSECT "); } // Unwind guards while (nGuards-- > 0) { subQueryStatement.Append(")"); } if (subTableColumn != linkColumn) { whereClause.And($"{tablePrefix}{localTable.TableName}.{localTable.GetColumn(linkColumn.ForeignKey.Column).Name} IN (").Append(subQueryStatement).Append(")"); } else { whereClause.And(subQueryStatement); } } else // this table points at other { var subQuery = queryParms.Select(o => new KeyValuePair <String, Object>(re.Match(o.Key).Groups[SubPropertyRegexGroup].Value, o.Value)); TableMapping tableMapping = null; var subPropKey = typeof(TModel).GetXmlProperty(propertyPath); // Get column info PropertyInfo domainProperty = scopedTables.Select(o => { tableMapping = o; return(m_mapper.MapModelProperty(typeof(TModel), o.OrmType, subPropKey)); })?.FirstOrDefault(o => o != null); ColumnMapping linkColumn = null; // If the domain property is not set, we may have to infer the link if (domainProperty == null) { var subPropType = m_mapper.MapModelType(subProp.PropertyType); // We find the first column with a foreign key that points to the other !!! linkColumn = scopedTables.SelectMany(o => o.Columns).FirstOrDefault(o => o.ForeignKey?.Table == subPropType); } else { linkColumn = tableMapping.GetColumn(domainProperty); } var fkTableDef = TableMapping.Get(linkColumn.ForeignKey.Table); var fkColumnDef = fkTableDef.GetColumn(linkColumn.ForeignKey.Column); // Create the sub-query SqlStatement subQueryStatement = null; if (String.IsNullOrEmpty(castAs)) { var genMethod = typeof(QueryBuilder).GetGenericMethod("CreateQuery", new Type[] { subProp.PropertyType }, new Type[] { subQuery.GetType(), typeof(ColumnMapping[]) }); subQueryStatement = genMethod.Invoke(this, new Object[] { subQuery, new ColumnMapping[] { fkColumnDef } }) as SqlStatement; } else // we need to cast! { var castAsType = new OpenIZ.Core.Model.Serialization.ModelSerializationBinder().BindToType("OpenIZ.Core.Model", castAs); var genMethod = typeof(QueryBuilder).GetGenericMethod("CreateQuery", new Type[] { castAsType }, new Type[] { subQuery.GetType(), typeof(ColumnMapping[]) }); subQueryStatement = genMethod.Invoke(this, new Object[] { subQuery, new ColumnMapping[] { fkColumnDef } }) as SqlStatement; } cteStatements.Add(new SqlStatement($"{tablePrefix}cte{cteStatements.Count} AS (").Append(subQueryStatement).Append(")")); //subQueryStatement.And($"{tablePrefix}{tableMapping.TableName}.{linkColumn.Name} = {sqName}{fkTableDef.TableName}.{fkColumnDef.Name} "); //selectStatement.Append($"INNER JOIN {tablePrefix}cte{cteStatements.Count - 1} ON ({tablePrefix}{tableMapping.TableName}.{linkColumn.Name} = {tablePrefix}cte{cteStatements.Count - 1}.{fkColumnDef.Name})"); whereClause.And($"{tablePrefix}{tableMapping.TableName}.{linkColumn.Name} IN (SELECT {tablePrefix}cte{cteStatements.Count - 1}.{fkColumnDef.Name} FROM {tablePrefix}cte{cteStatements.Count - 1})"); } } else { whereClause.And(CreateWhereCondition <TModel>(propertyPath, parm.Value, tablePrefix, scopedTables)); } } // Return statement SqlStatement retVal = new SqlStatement(); if (cteStatements.Count > 0) { retVal.Append("WITH "); foreach (var c in cteStatements) { retVal.Append(c); if (c != cteStatements.Last()) { retVal.Append(","); } } } retVal.Append(selectStatement.Where(whereClause)); return(retVal); }
/// <summary> /// Private ctor for table mapping /// </summary> private TableMapping(Type t) { this.OrmType = t; this.TableName = t.GetTypeInfo().GetCustomAttribute <TableAttribute>()?.Name ?? t.Name; this.Columns = t.GetRuntimeProperties().Where(o => o.GetCustomAttribute <ColumnAttribute>() != null).Select(o => ColumnMapping.Get(o, this)).ToList(); foreach (var itm in this.Columns) { this.m_mappings.Add(itm.SourceProperty.Name, itm); } }