/// <summary> /// Create a where condition /// </summary> public SqlStatement CreateWhereCondition(Type tmodel, String propertyPath, Object value, String tablePrefix, List <TableMapping> scopedTables) { SqlStatement retVal = new SqlStatement(this.m_provider); // Map the type var tableMapping = scopedTables.First(); var propertyInfo = tmodel.GetXmlProperty(propertyPath); if (propertyInfo == null) { throw new ArgumentOutOfRangeException(propertyPath); } PropertyInfo domainProperty = scopedTables.Select(o => { tableMapping = o; return(m_mapper.MapModelProperty(tmodel, o.OrmType, propertyInfo)); }).FirstOrDefault(o => o != null); // Now map the property path var tableAlias = $"{tablePrefix}{tableMapping.TableName}"; if (domainProperty == null) { throw new ArgumentException($"Can't find SQL based property for {propertyPath} on {tableMapping.TableName}"); } var columnData = tableMapping.GetColumn(domainProperty); // List of parameters var lValue = value as IList; if (lValue == null) { lValue = new List <Object>() { value } } ; retVal.Append("("); foreach (var itm in lValue) { retVal.Append($"{tableAlias}.{columnData.Name}"); var semantic = " OR "; var iValue = itm; if (iValue is String) { var sValue = itm as String; switch (sValue[0]) { case '<': semantic = " AND "; if (sValue[1] == '=') { retVal.Append(" <= ?", CreateParameterValue(sValue.Substring(2), propertyInfo.PropertyType)); } else { retVal.Append(" < ?", CreateParameterValue(sValue.Substring(1), propertyInfo.PropertyType)); } break; case '>': semantic = " AND "; if (sValue[1] == '=') { retVal.Append(" >= ?", CreateParameterValue(sValue.Substring(2), propertyInfo.PropertyType)); } else { retVal.Append(" > ?", CreateParameterValue(sValue.Substring(1), propertyInfo.PropertyType)); } break; case '!': semantic = " AND "; if (sValue.Equals("!null")) { retVal.Append(" IS NOT NULL"); } else { retVal.Append(" <> ?", CreateParameterValue(sValue.Substring(1), propertyInfo.PropertyType)); } break; case '~': if (sValue.Contains("*") || sValue.Contains("?")) { retVal.Append(" ILIKE ? ", CreateParameterValue(sValue.Substring(1).Replace("*", "%"), propertyInfo.PropertyType)); } else { retVal.Append(" ILIKE '%' || ? || '%'", CreateParameterValue(sValue.Substring(1), propertyInfo.PropertyType)); } break; case '^': retVal.Append(" ILIKE ? || '%'", CreateParameterValue(sValue.Substring(1), propertyInfo.PropertyType)); break; default: if (sValue.Equals("null")) { retVal.Append(" IS NULL"); } else { retVal.Append(" = ? ", CreateParameterValue(sValue, propertyInfo.PropertyType)); } break; } } else { retVal.Append(" = ? ", CreateParameterValue(iValue, propertyInfo.PropertyType)); } if (lValue.IndexOf(itm) < lValue.Count - 1) { retVal.Append(semantic); } } retVal.Append(")"); return(retVal); }
/// <summary> /// Insert the specified object /// </summary> public TModel Insert <TModel>(TModel value) { #if DEBUG var sw = new Stopwatch(); sw.Start(); try { #endif // First we want to map object to columns var tableMap = TableMapping.Get(typeof(TModel)); SqlStatement columnNames = this.CreateSqlStatement(), values = this.CreateSqlStatement(); foreach (var col in tableMap.Columns) { var val = col.SourceProperty.GetValue(value); if (val == null || !col.IsNonNull && ( val.Equals(default(Guid)) || val.Equals(default(DateTime)) || val.Equals(default(DateTimeOffset)) || val.Equals(default(Decimal)))) { val = null; } if (col.IsAutoGenerated && val == null) { continue; } columnNames.Append($"{col.Name}"); // Append value values.Append("?", val); values.Append(","); columnNames.Append(","); } values.RemoveLast(); columnNames.RemoveLast(); var returnKeys = tableMap.Columns.Where(o => o.IsAutoGenerated); // Return arrays var stmt = this.m_provider.Returning( this.CreateSqlStatement($"INSERT INTO {tableMap.TableName} (").Append(columnNames).Append(") VALUES (").Append(values).Append(")"), returnKeys.ToArray() ); // Execute lock (this.m_lockObject) { var dbc = this.m_provider.CreateCommand(this, stmt); try { if (returnKeys.Count() > 0 && this.m_provider.Features.HasFlag(SqlEngineFeatures.ReturnedInserts)) { using (var rdr = dbc.ExecuteReader()) if (rdr.Read()) { foreach (var itm in returnKeys) { object ov = null; if (MapUtil.TryConvert(rdr[itm.Name], itm.SourceProperty.PropertyType, out ov)) { itm.SourceProperty.SetValue(value, ov); } } } } else { dbc.ExecuteNonQuery(); } } finally { if (!this.IsPreparedCommand(dbc)) { dbc.Dispose(); } } } if (value is IAdoLoadedData) { (value as IAdoLoadedData).Context = this; } return(value); #if DEBUG } finally { sw.Stop(); this.m_tracer.TraceVerbose("INSERT executed in {0} ms", sw.ElapsedMilliseconds); } #endif }
/// <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(this.m_provider, $" FROM {tableMap.TableName} AS {tablePrefix}{tableMap.TableName} "); Stack <TableMapping> fkStack = new Stack <TableMapping>(); fkStack.Push(tableMap); List <JoinFilterAttribute> joinFilters = new List <JoinFilterAttribute>(); // 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} "); foreach (var flt in jt.JoinFilters.Union(joinFilters).GroupBy(o => o.PropertyName).ToArray()) { var fltCol = fkTbl.GetColumn(flt.Key); if (fltCol == null) { joinFilters.AddRange(flt); } else { selectStatement.And($"({String.Join(" OR ", flt.Select(o => $"{tablePrefix}{fltCol.Table.TableName}.{fltCol.Name} = '{o.Value}'"))})"); joinFilters.RemoveAll(o => flt.Contains(o)); } } selectStatement.Append(")"); 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) { selectStatement = new SqlStatement(this.m_provider, $"SELECT * ").Append(selectStatement); // columnSelector = scopedTables.SelectMany(o => o.Columns).ToArray(); } else { var columnList = String.Join(",", columnSelector.Select(o => { var rootCol = tableMap.GetColumn(o.SourceProperty); skipParentJoin &= rootCol != null; if (skipParentJoin) { return($"{tablePrefix}{rootCol.Table.TableName}.{rootCol.Name}"); } else { return($"{tablePrefix}{o.Table.TableName}.{o.Name}"); } })); selectStatement = new SqlStatement(this.m_provider, $"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 List <KeyValuePair <String, Object> > workingParameters = new List <KeyValuePair <string, object> >(query); // Where clause SqlStatement whereClause = new SqlStatement(this.m_provider); List <SqlStatement> cteStatements = new List <SqlStatement>(); // Construct while (workingParameters.Count > 0) { var parm = workingParameters.First(); workingParameters.RemoveAt(0); // Match the regex and process var propertyPredicate = QueryPredicate.Parse(parm.Key); if (propertyPredicate == null) { throw new ArgumentOutOfRangeException(parm.Key); } // Next, we want to construct the other parms var otherParms = workingParameters.Where(o => QueryPredicate.Parse(o.Key).Path == propertyPredicate.Path).ToArray(); // Remove the working parameters if the column is FK then all parameters if (otherParms.Any() || !String.IsNullOrEmpty(propertyPredicate.Guard) || !String.IsNullOrEmpty(propertyPredicate.SubPath)) { 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(propertyPredicate.Path, true); if (subProp == null) { throw new MissingMemberException(propertyPredicate.Path); } // Link to this table in the other? // Is this a collection? if (typeof(IList).IsAssignableFrom(subProp.PropertyType)) // 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=>o.SourceProperty.Name == "SourceKey") : linkColumns.FirstOrDefault(); var linkColumn = linkColumns.Count() > 1 ? linkColumns.FirstOrDefault(o => propertyPredicate.SubPath.StartsWith("source") ? o.SourceProperty.Name != "SourceKey" : o.SourceProperty.Name == "SourceKey") : linkColumns.FirstOrDefault(); // Link column is null, is there an assoc attrib? SqlStatement subQueryStatement = new SqlStatement(this.m_provider); var subTableColumn = linkColumn; string existsClause = String.Empty; if (linkColumn == null) { var tableWithJoin = scopedTables.Select(o => o.AssociationWith(subTableMap)).FirstOrDefault(o => o != null); 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}"); } 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}"; } var guardConditions = queryParms.GroupBy(o => QueryPredicate.Parse(o.Key).Guard); int nGuards = 0; foreach (var guardClause in guardConditions) { var subQuery = guardClause.Select(o => new KeyValuePair <String, Object>(QueryPredicate.Parse(o.Key).SubPath, o.Value)).ToList(); // TODO: GUARD CONDITION HERE!!!! if (!String.IsNullOrEmpty(guardClause.Key)) { StringBuilder guardCondition = new StringBuilder(); var clsModel = propertyType; while (clsModel.GetCustomAttribute <ClassifierAttribute>() != null) { var clsProperty = clsModel.GetRuntimeProperty(clsModel.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).IsAssignableFrom(clsModel)) { guardCondition.Append("."); } } subQuery.Add(new KeyValuePair <string, object>(guardCondition.ToString(), guardClause.Key.Split('|'))); // Filter by effective version if (typeof(IVersionedAssociation).IsAssignableFrom(clsModel)) { subQuery.Add(new KeyValuePair <string, object>("obsoleteVersionSequence", new String[] { "null" })); } } // 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); } // 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 if (!this.m_hacks.Any(o => o.HackQuery(this, selectStatement, whereClause, subProp, tablePrefix, propertyPredicate, parm.Value, scopedTables))) // this table points at other { var subQuery = queryParms.Select(o => new KeyValuePair <String, Object>(QueryPredicate.Parse(o.Key).SubPath, o.Value)).ToList(); if (!subQuery.Any(o => o.Key == "obsoletionTime") && typeof(IBaseEntityData).IsAssignableFrom(subProp.PropertyType)) { subQuery.Add(new KeyValuePair <string, object>("obsoletionTime", "null")); } TableMapping tableMapping = null; var subPropKey = typeof(TModel).GetXmlProperty(propertyPredicate.Path); // 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 //var genMethod = typeof(QueryBuilder).GetGenericMethod("CreateQuery", new Type[] { subProp.PropertyType }, new Type[] { subQuery.GetType(), typeof(ColumnMapping[]) }); //SqlStatement subQueryStatement = genMethod.Invoke(this, new Object[] { subQuery, new ColumnMapping[] { fkColumnDef } }) as SqlStatement; SqlStatement subQueryStatement = null; if (String.IsNullOrEmpty(propertyPredicate.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", propertyPredicate.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(this.m_provider, $"{tablePrefix}cte{cteStatements.Count} AS (").Append(subQueryStatement).Append(")")); //subQueryStatement.And($"{tablePrefix}{tableMapping.TableName}.{linkColumn.Name} = {sqName}{fkTableDef.TableName}.{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(typeof(TModel), propertyPredicate.Path, parm.Value, tablePrefix, scopedTables)); } } // Return statement SqlStatement retVal = new SqlStatement(this.m_provider); 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> /// Updates the specified object /// </summary> public TModel Update <TModel>(TModel value) { #if DEBUG var sw = new Stopwatch(); sw.Start(); try { #endif // Build the command var tableMap = TableMapping.Get(typeof(TModel)); SqlStatement <TModel> query = this.CreateSqlStatement <TModel>().UpdateSet(); SqlStatement whereClause = this.CreateSqlStatement(); foreach (var itm in tableMap.Columns) { var itmValue = itm.SourceProperty.GetValue(value); if (itmValue == null || itmValue.Equals(default(Guid)) || itmValue.Equals(default(DateTime)) || itmValue.Equals(default(DateTimeOffset)) || itmValue.Equals(default(Decimal))) { itmValue = null; } query.Append($"{itm.Name} = ? ", itmValue); query.Append(","); if (itm.IsPrimaryKey) { whereClause.And($"{itm.Name} = ?", itmValue); } } query.RemoveLast(); query.Where(whereClause); // Now update lock (this.m_lockObject) { var dbc = this.m_provider.CreateCommand(this, query); try { dbc.ExecuteNonQuery(); } finally { if (!this.IsPreparedCommand(dbc)) { dbc.Dispose(); } } } if (value is IAdoLoadedData) { (value as IAdoLoadedData).Context = this; } return(value); #if DEBUG } finally { sw.Stop(); this.m_tracer.TraceVerbose("UPDATE executed in {0} ms", sw.ElapsedMilliseconds); } #endif }