/// <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
}