Esempio n. 1
0
 /// <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()));
     }
 }
Esempio n. 2
0
 /// <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()));
     }
 }
Esempio n. 3
0
        /// <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);
        }