/// <summary>
/// Append an AND condition
/// </summary>
public SqlStatement Or(SqlStatement clause)
{
if (String.IsNullOrEmpty(this.m_sql) && this.m_rhs == null)
{
return(this.Append(clause));
}
else
{
return(this.Append(new SqlStatement(" OR ")).Append(clause.Build()));
}
}
/// <summary>
/// Append an AND condition
/// </summary>
public SqlStatement And(SqlStatement clause)
{
if (String.IsNullOrEmpty(this.m_sql) && (this.m_rhs == null || this.m_rhs.Build().SQL.TrimEnd().EndsWith("where", StringComparison.OrdinalIgnoreCase)))
{
return(this.Append(clause));
}
else
{
return(this.Append(" AND ").Append(clause.Build()));
}
}
/// <summary>
/// Query query
/// </summary>
/// <param name="query"></param>
public SqlStatement CreateQuery <TModel>(IEnumerable <KeyValuePair <String, Object> > query, String tablePrefix, bool skipJoins, ModelSort <TModel>[] orderBy, 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;
if (skipJoins)
{
selectStatement = new SqlStatement($" FROM {tableMap.TableName} AS {tablePrefix}{tableMap.TableName} ");
}
else
{
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);
if (typeof(IDbHideable).IsAssignableFrom(fkTbl.OrmType))
{
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} AND {tablePrefix}{fkAtt.Table.TableName}.hidden = 0) ");
}
else
{
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));
}
}
// 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();
string[] guardValues = guardClause.Key.Split('|');
// 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.GetSerializationName());
if (typeof(IdentifiedData).GetTypeInfo().IsAssignableFrom(clsModel.GetTypeInfo()))
{
guardCondition.Append(".");
}
if (clsProperty.PropertyType.GetTypeInfo().IsEnum)
{
guardValues = guardValues.Select(o => ((int)Enum.Parse(clsProperty.PropertyType, o)).ToString()).ToArray();
}
}
subQuery.Add(new KeyValuePair <string, object>(guardCondition.ToString(), guardValues));
}
// Generate method
var prefix = IncrementSubQueryAlias(tablePrefix);
var genMethod = typeof(QueryBuilder).GetGenericMethod("CreateQuery", new Type[] { propertyType }, new Type[] { subQuery.GetType(), typeof(String), typeof(bool), typeof(ModelSort <>).MakeGenericType(propertyType).MakeArrayType(), 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, null, new ColumnMapping[] { subTableColumn } }) as SqlStatement);
}
else
{
subQueryStatement.Append(genMethod.Invoke(this, new Object[] { subQuery, prefix, false, null, 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(ModelSort <>).MakeGenericType(subProp.PropertyType).MakeArrayType(), typeof(ColumnMapping[]) });
subQueryStatement = genMethod.Invoke(this, new Object[] { subQuery, null, subSkipJoins, null, new ColumnMapping[] { fkColumnDef } }) as SqlStatement;
}
else // we need to cast!
{
var castAsType = new SanteDB.Core.Model.Serialization.ModelSerializationBinder().BindToType("SanteDB.Core.Model", propertyPredicate.CastAs);
var genMethod = typeof(QueryBuilder).GetGenericMethod("CreateQuery", new Type[] { castAsType }, new Type[] { subQuery.GetType(), typeof(String), typeof(bool), typeof(ModelSort <>).MakeGenericType(castAsType).MakeArrayType(), typeof(ColumnMapping[]) });
subQueryStatement = genMethod.Invoke(this, new Object[] { subQuery, null, false, null, 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));
// Is the type hideable
if (typeof(IDbHideable).IsAssignableFrom(tableType))
{
retVal.And(" hidden = 0");
}
// TODO: Order by?
if (orderBy != null && orderBy.Length > 0)
{
retVal.Append(" ORDER BY");
foreach (var ob in orderBy)
{
// Query property path
var orderStatement = this.CreateOrderBy(typeof(TModel), tablePrefix, scopedTables, ob.SortProperty.Body, ob.SortOrder);
retVal.Append(orderStatement).Append(",");
}
retVal.RemoveLast();
}
return(retVal);
}