/// <summary>
/// Creates a new postgresql query expression builder
/// </summary>
public SqlQueryExpressionBuilder(String alias)
{
this.m_tableAlias = alias;
this.m_sqlStatement = new SqlStatement();
}
/// <summary>
/// Create a single where condition based on the property info
/// </summary>
public SqlStatement CreateWhereCondition(Type tmodel, String propertyPath, Object value, String tablePrefix, List <TableMapping> scopedTables, String tableAlias = null)
{
SqlStatement retVal = new SqlStatement();
// Map the type
var tableMapping = scopedTables.First();
var propertyInfo = tmodel.GetQueryProperty(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
if (String.IsNullOrEmpty(tableAlias))
{
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(" LIKE ? ", CreateParameterValue(sValue.Substring(1).Replace("*", "%"), propertyInfo.PropertyType));
}
else
{
retVal.Append(" LIKE '%' || ? || '%'", CreateParameterValue(sValue.Substring(1), propertyInfo.PropertyType));
}
break;
case '^':
retVal.Append(" LIKE ? || '%'", CreateParameterValue(sValue.Substring(1), propertyInfo.PropertyType));
break;
case '$':
retVal.Append(" LIKE '%' || ?", 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>
/// Removes the last statement from the list
/// </summary>
public bool RemoveLast(out SqlStatement last)
{
last = this.RemoveLast();
return(last != null);
}
/// <summary>
/// Query query
/// </summary>
/// <param name="query"></param>
public SqlStatement CreateQuery <TModel>(IEnumerable <KeyValuePair <String, Object> > query, String tablePrefix, bool skipJoins, 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 (skipJoins)
{
selectStatement = new SqlStatement($" FROM {tableMap.TableName} AS {tablePrefix}{tableMap.TableName} ");
}
else
{
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
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 propertyPredicate = QueryPredicate.Parse(parm.Key);
if (propertyPredicate == null)
{
throw new ArgumentOutOfRangeException(parm.Key);
}
// Next, we want to construct the
var otherParms = workingParameters.Where(o => QueryPredicate.Parse(o.Key).ToString(QueryPredicatePart.PropertyAndCast) == propertyPredicate.ToString(QueryPredicatePart.PropertyAndCast)).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).GetQueryProperty(propertyPredicate.Path, true);
if (subProp == null)
{
throw new MissingMemberException(propertyPredicate.Path);
}
// Link to this table in the other?
// Is this a collection?
if (!this.m_hacks.Any(o => o.HackQuery(this, selectStatement, whereClause, typeof(TModel), subProp, tablePrefix, propertyPredicate, parm.Value, scopedTables)))
{
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 => propertyPredicate.SubPath.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 => 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).ToString(QueryPredicatePart.SubPath), 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(bool), typeof(ColumnMapping[]) });
// Sub path is specified
if (String.IsNullOrEmpty(propertyPredicate.SubPath) && "null".Equals(parm.Value))
{
subQueryStatement.And($" {existsClause} NOT IN (");
}
else
{
subQueryStatement.And($" {existsClause} IN (");
}
nGuards++;
existsClause = $"{prefix}{subTableColumn.Table.TableName}.{subTableColumn.Name}";
if (subQuery.Count(p => !p.Key.Contains(".")) == 0)
{
subQueryStatement.Append(genMethod.Invoke(this, new Object[] { subQuery, prefix, true, new ColumnMapping[] { subTableColumn } }) as SqlStatement);
}
else
{
subQueryStatement.Append(genMethod.Invoke(this, new Object[] { subQuery, prefix, false, 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>(QueryPredicate.Parse(o.Key).ToString(QueryPredicatePart.SubPath), o.Value)).ToList();
TableMapping tableMapping = null;
var subPropKey = typeof(TModel).GetQueryProperty(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
SqlStatement subQueryStatement = null;
var subSkipJoins = subQuery.Count(o => !o.Key.Contains(".") && o.Key != "obsoletionTime") == 0;
if (String.IsNullOrEmpty(propertyPredicate.CastAs))
{
var genMethod = typeof(QueryBuilder).GetGenericMethod("CreateQuery", new Type[] { subProp.PropertyType }, new Type[] { subQuery.GetType(), typeof(string), typeof(bool), typeof(ColumnMapping[]) });
subQueryStatement = genMethod.Invoke(this, new Object[] { subQuery, null, subSkipJoins, 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(String), typeof(bool), typeof(ColumnMapping[]) });
subQueryStatement = genMethod.Invoke(this, new Object[] { subQuery, null, false, 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 if (!this.m_hacks.Any(o => o.HackQuery(this, selectStatement, whereClause, typeof(TModel), typeof(TModel).GetQueryProperty(propertyPredicate.Path), tablePrefix, propertyPredicate, parm.Value, scopedTables)))
{
whereClause.And(CreateWhereCondition(typeof(TModel), propertyPredicate.Path, 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);
}
