/// <summary> /// Create SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } if (parms.Length == 1) // There is a threshold { var dtValue = DateTime.Parse(value); switch (parms[0].Replace("\"", "")) { case "y": return(current.Append($"{filterColumn} BETWEEN ? AND ?", new DateTime(dtValue.Year, 01, 01), new DateTime(dtValue.Year, 12, 31, 23, 59, 59))); case "M": return(current.Append($"{filterColumn} BETWEEN ? AND ?", new DateTime(dtValue.Year, dtValue.Month, 01), new DateTime(dtValue.Year, dtValue.Month, DateTime.DaysInMonth(dtValue.Year, dtValue.Month), 23, 59, 59))); case "d": return(current.Append($"{filterColumn} BETWEEN ? AND ?", dtValue.Date, dtValue.Date.AddHours(23).AddMinutes(59).AddSeconds(59))); default: throw new NotSupportedException("Date truncate precision must be y, M, or d"); } } else { throw new InvalidOperationException("date_trunc requires a precision"); } }
// Inserted identity is returned by extra select; command postprocessor IdentityReader gets it from DataReader // and puts it into EntityRecord private void AppendIdentityReturn(SqlStatement sql, DbTableInfo table) { sql.Append(_myDialect.SqlSelectIdentity); // returned value is decimal! sql.Append(SqlTerms.NewLine); sql.ExecutionType = DbExecutionType.Reader; sql.ResultProcessor = this._identityReader; }
/// <summary> /// Create the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) op = "="; if (TimeSpan.TryParse(value, out TimeSpan timespan)) { if (parms.Length == 1) return current.Append($"ABS(DATEDIFF(millisecond, {filterColumn}, cast(? as TIMESTAMP))) {op} {timespan.TotalSeconds}", QueryBuilder.CreateParameterValue(parms[0], operandType)); else return current.Append($"ABS(DATEDIFF(millisecond, {filterColumn}, CURRENT_TIMESTAMP))) {op} {timespan.TotalSeconds}"); } else { try { // Try to parse as ISO date timespan = XmlConvert.ToTimeSpan(value); if (parms.Length == 1) return current.Append($"ABS(DATEDIFF(millisecond, {filterColumn}, cast(? as TIMESTAMP))) {op} {timespan.TotalSeconds}", QueryBuilder.CreateParameterValue(parms[0], operandType)); else return current.Append($"ABS(DATEDIFF(millisecond, {filterColumn}, CURRENT_TIMESTAMP))) {op} {timespan.TotalSeconds}"); } catch { throw new InvalidOperationException("Age needs to have whole number distance and single character unit or be a valid TimeSpan"); } } }
/// <summary> /// Create sql statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } match = new Regex(@"^(\d*?)([yMdwhms])$").Match(value); if (match.Success) { String qty = match.Groups[1].Value, unit = match.Groups[2].Value; long qtyParse = long.Parse(qty); switch (unit) { case "y": qtyParse *= TimeSpan.TicksPerDay * 365; break; case "M": qtyParse *= TimeSpan.TicksPerDay * 30; break; case "d": qtyParse *= TimeSpan.TicksPerDay; break; case "w": qtyParse *= TimeSpan.TicksPerDay * 7; break; case "h": qtyParse *= TimeSpan.TicksPerHour; break; case "m": qtyParse *= TimeSpan.TicksPerMinute; break; case "s": qtyParse *= TimeSpan.TicksPerSecond; break; } return(current.Append($"ABS({filterColumn} - ?) {op} {qtyParse}", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(parms[0], operandType))); } else if (TimeSpan.TryParse(value, out TimeSpan timespan)) { return(current.Append($"ABS({filterColumn} - ?) {op} {timespan.Ticks}", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(parms[0], operandType))); } else { throw new InvalidOperationException("Date difference needs to have whole number distance and single character unit or be a valid TimeSpan"); } }
/// <summary> /// Hack the particular query /// </summary> public bool HackQuery(QueryBuilder builder, SqlStatement sqlStatement, SqlStatement whereClause, PropertyInfo property, String queryPrefix, QueryPredicate predicate, object values, IEnumerable <TableMapping> scopedTables) { // Hack mnemonic queries if (typeof(Concept).IsAssignableFrom(property.PropertyType) && predicate.SubPath == "mnemonic") { // Has this already been joined? var declType = TableMapping.Get(this.m_mapper.MapModelType(property.DeclaringType)); var keyProperty = property.PropertyType == typeof(Guid) ? property : property.DeclaringType.GetRuntimeProperty(property.Name + "Key"); var declProp = declType.GetColumn(this.m_mapper.MapModelProperty(property.DeclaringType, declType.OrmType, keyProperty)); if (declProp.ForeignKey == null) { return(false); // No FK link } var tblMap = TableMapping.Get(this.m_mapper.MapModelType(property.PropertyType)); var fkTbl = TableMapping.Get(declProp.ForeignKey.Table); string directFkName = $"{queryPrefix}{fkTbl.TableName}"; // We have to join to the FK table if (!declProp.IsAlwaysJoin) { var fkColumn = fkTbl.GetColumn(declProp.ForeignKey.Column); sqlStatement.Append($" INNER JOIN {fkTbl.TableName} AS {directFkName}_{declProp.Name} ON ({queryPrefix}{declType.TableName}.{declProp.Name} = {directFkName}_{declProp.Name}.{fkColumn.Name})"); directFkName += $"_{declProp.Name}"; } // We aren't yet joined to our table, we need to join to our table though!!!! if (declProp.ForeignKey.Table != tblMap.OrmType) { var fkKeyColumn = fkTbl.Columns.FirstOrDefault(o => o.ForeignKey?.Table == tblMap.OrmType && o.Name == tblMap.PrimaryKey.First().Name) ?? tblMap.Columns.FirstOrDefault(o => o.ForeignKey?.Table == fkTbl.OrmType && o.Name == fkTbl.PrimaryKey.First().Name); if (fkKeyColumn == null) { return(false); // couldn't find the FK link } // Now we want to filter our FK var tblName = $"{queryPrefix}{declProp.Name}_{tblMap.TableName}"; sqlStatement.Append($" INNER JOIN {tblMap.TableName} AS {tblName} ON ({directFkName}.{fkKeyColumn.Name} = {tblName}.{fkKeyColumn.Name})"); // Append the where clause whereClause.And(builder.CreateWhereCondition(property.PropertyType, predicate.SubPath, values, $"{queryPrefix}{declProp.Name}_", new List <TableMapping>() { tblMap })); // Add obslt_utc version? if (typeof(IDbBaseData).IsAssignableFrom(tblMap.OrmType)) { whereClause.And($"{tblName}.{tblMap.GetColumn(nameof(IDbBaseData.ObsoletionTime)).Name} IS NULL"); } } return(true); } else { return(false); } }
/// <summary> /// Create the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) op = "="; match = new Regex(@"^(\d*?)([yMdwhms])$").Match(value); if (match.Success) { String qty = match.Groups[1].Value, unit = match.Groups[2].Value; switch (unit) { case "y": unit = "year"; break; case "M": unit = "month"; break; case "d": unit = "day"; break; case "h": unit = "hour"; break; case "m": unit = "minute"; break; case "s": unit = "second"; break; } return current.Append($"ABS(DATEDIFF({unit}, {filterColumn}, cast(? as TIMESTAMP))) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(parms[0], typeof(Int32))); } else if (TimeSpan.TryParse(value, out TimeSpan timespan)) { return current.Append($"ABS(DATEDIFF(millisecond, {filterColumn}, cast(? as TIMESTAMP))) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), timespan.TotalMilliseconds); } else { try { // Try to parse as ISO date timespan = XmlConvert.ToTimeSpan(value); return current.Append($"ABS(DATEDIFF(millisecond, {filterColumn}, cast(? as TIMESTAMP))) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), timespan.TotalMilliseconds); } catch { throw new InvalidOperationException("Date difference needs to have whole number distance and single character unit or be a valid TimeSpan"); } } }
private void AppendIdentityReturn(SqlStatement sql, DbTableInfo table) { var idCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.Identity)); var dbType = idCol.Member.DataType.GetIntDbType(); var idPrmPh = new SqlColumnValuePlaceHolder(idCol, ParameterDirection.Output); sql.PlaceHolders.Add(idPrmPh); var getIdSql = _msDialect.SqlGetIdentityTemplate.Format(idPrmPh); sql.Append(getIdSql); sql.Append(SqlTerms.NewLine); }
/// <summary> /// Insert specified object /// </summary> private Guid InsertObject(DataContext context, String path, IDatamartSchemaPropertyContainer pcontainer, dynamic obj, Guid?scope = null) { // Conver to expando IDictionary <String, Object> tuple = new ExpandoObject(); if (obj is IDictionary <String, object> ) { foreach (var kv in obj as IDictionary <String, object> ) { tuple.Add(kv.Key, kv.Value); } } else { foreach (var pi in obj.GetType().GetProperties()) { tuple.Add(pi.Name, pi.GetValue(obj, null)); } } tuple.Add("uuid", Guid.NewGuid()); //tuple.Add("cont_id", scope); //tuple.Add("ext_time", DateTime.Now); // Now time to store SqlStatement sbQuery = context.CreateSqlStatement("INSERT INTO "), sbValues = context.CreateSqlStatement(); sbQuery.Append(path); sbQuery.Append("("); foreach (var p in tuple.Where(o => pcontainer.Properties.FirstOrDefault(p => p.Name == o.Key)?.Type != SchemaPropertyType.Object)) { sbQuery.Append(p.Key); sbValues.Append("?", p.Value); if (p.Key != tuple.Last().Key) { sbQuery.Append(","); sbValues.Append(","); } } sbQuery = sbQuery.Append(") VALUES (").Append(sbValues).Append(")"); context.ExecuteNonQuery(sbQuery); // Sub-properties foreach (var p in pcontainer.Properties.Where(o => o.Type == SchemaPropertyType.Object)) { this.InsertObject(context, String.Format("{0}_{1}", path, p.Name), p, obj[p.Name], (Guid)tuple["uuid"]); } return((Guid)tuple["uuid"]); }
private void AppendIdentityReturn(SqlStatement sql, DbTableInfo table) { sql.TrimEndingSemicolon(); // Append returning clause var idCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.Identity)); var dbType = idCol.Member.DataType.GetIntDbType(); // we create placeholder based on Id column, only with OUTPUT direction - this results in parameter to return value var idPrmPh = new SqlColumnValuePlaceHolder(idCol, ParameterDirection.Output); sql.PlaceHolders.Add(idPrmPh); var getIdSql = _pgDialect.SqlCrudTemplateReturningIdentity.Format(idCol.SqlColumnNameQuoted); sql.Append(getIdSql); sql.Append(SqlTerms.NewLine); }
/// <summary> /// Append a returning statement /// </summary> public SqlStatement Returning(SqlStatement sqlStatement, params ColumnMapping[] returnColumns) { if (returnColumns.Length == 0) { return(sqlStatement); } return(sqlStatement.Append($" RETURNING {String.Join(",", returnColumns.Select(o => o.Name))}")); }
/// <summary> /// Create SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length == 1) { return(current.Append($"soundex({filterColumn}) = soundex(?)", QueryBuilder.CreateParameterValue(parms[0], operandType))); } else { switch (parms[1]) { case "soundex": return(current.Append($"soundex({filterColumn}) = soundex(?)", QueryBuilder.CreateParameterValue(parms[0], operandType))); default: throw new NotSupportedException($"Sounds-like algorithm {parms[1]} is not supported"); } } }
/// <summary> /// Creates the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } parms = parms.Where(p => !String.IsNullOrEmpty(p)).ToArray(); if (parms.Length == 1) // There is a threshold { return(current.Append($"difference({filterColumn}, ?) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(value, operandType))); } else { return(current.Append($"soundex({filterColumn}) {op} soundex(?)", QueryBuilder.CreateParameterValue(value, operandType))); } }
/// <summary> /// Creates the SQL statement /// </summary> /// <example> /// ?name.component.value=:(metaphone)Justin /// or /// ?name.component.value=:(metaphone|5)Hamilton /// </example> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } if (op != "=") // There is a threshold { return(current.Append($"metaphone({filterColumn}, {parms[0]}) {op} metaphone(?, {parms[0]})", QueryBuilder.CreateParameterValue(value, operandType))); } else { return(current.Append($"metaphone({filterColumn}, 4) {op} metaphone(?, 4)", QueryBuilder.CreateParameterValue(value, operandType))); } }
/// <summary> /// Create SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } if (parms.Length == 1) // There is a threshold { return(current.Append($"editdist3(spellfix1_phonehash({filterColumn}), spellfix1_phonehash(?)) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(value, operandType))); } else { return(current.Append($"spellfix1_phonehash({filterColumn}) {op} spellfix1_phonehash(?)", QueryBuilder.CreateParameterValue(value, operandType))); } }
/// <summary> /// Create the specified sql statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length != 1) { throw new ArgumentException("alias requires one parameter"); } else { return(current.Append($"EXISTS (SELECT 1 FROM MPI_NAME_SYN_CDTBL WHERE LOWER(pri_name) = LOWER(?) AND LOWER(syn_name) = LOWER({filterColumn}) OR LOWER(pri_name) = LOWER({filterColumn}) LOWER(syn_name) = LOWER(?))", parms[0], parms[0])); } }
/// <summary> /// Creates the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } return(current.Append($"((dmetaphone({filterColumn}) {op} dmetaphone(?)) OR (dmetaphone_alt({filterColumn}) {op} dmetaphone_alt(?)))", QueryBuilder.CreateParameterValue(value, operandType), QueryBuilder.CreateParameterValue(value, operandType))); }
/// <summary> /// Append order by query /// </summary> protected override SqlStatement AppendOrderBy(SqlStatement rawQuery, ModelSort <TModel>[] orderBy) { if (orderBy == null || orderBy.Length == 0) { return(rawQuery.Append(" ORDER BY vrsn_seq_id DESC ")); } else { return(base.AppendOrderBy(rawQuery, orderBy)); } }
/// <summary> /// Create the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type type) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } return(current.Append($"RIGHT({filterColumn}, {parms[0]}) {op} RIGHT(?, {parms[0]})", QueryBuilder.CreateParameterValue(value, type))); }
/// <summary> /// Create the SQL for first /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type type) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } switch (parms.Length) { case 1: return(current.Append($"SUBSTRING({filterColumn} FROM {parms[0]}) {op} SUBSTRING(? FROM {parms[0]})", QueryBuilder.CreateParameterValue(value, type))); case 2: return(current.Append($"SUBSTRING({filterColumn} FROM {parms[0]} FOR {parms[1]}) {op} SUBSTRING(? FROM {parms[0]} FOR {parms[1]})", QueryBuilder.CreateParameterValue(value, type))); } return(current.Append($"SUBSTRING({filterColumn}, {parms[0]}) {op} LEFT(?, {parms[0]})", QueryBuilder.CreateParameterValue(value, type))); }
/// <summary> /// Apply the filter /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } switch (parms.Length) { case 1: return(current.Append($"{filterColumn} % ? AND levenshtein(TRIM(LOWER({filterColumn})), TRIM(LOWER(?))) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(value, typeof(Int32)))); case 4: // with insert, delete and substitute costs return(current.Append($"{filterColumn} % ? AND levenshtein(TRIM(LOWER({filterColumn})), TRIM(LOWER(?)), {String.Join(",", parms.Skip(1))}) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(value, typeof(Int32)))); default: throw new ArgumentOutOfRangeException("Invalid number of parameters of string diff"); } }
/// <summary> /// Creates the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { var match = new Regex(@"^([<>]?=?)(.*?)$").Match(operand); String op = match.Groups[1].Value, value = match.Groups[2].Value; if (String.IsNullOrEmpty(op)) { op = "="; } switch (parms.Length) { case 0: return(current.Append($"{filterColumn} % ?", QueryBuilder.CreateParameterValue(parms[0], operandType))); case 1: // with levenshtein return(current.Append($"{filterColumn} % ? AND similarity({filterColumn}, ?) {op} ?", QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(parms[0], operandType), QueryBuilder.CreateParameterValue(value, typeof(double)))); default: throw new ArgumentOutOfRangeException("Invalid number of parameters of string diff"); } }
/// <summary> /// Create the SQL statement for the extension function /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length == 1) { if (!String.IsNullOrEmpty(parms[0])) { switch (filterColumn.Split('.').Last()) { case "ent_id": // entity search case "src_ent_id": case "trg_ent_id": return(current.Append($"{filterColumn} IN (SELECT ent_id FROM ft_ent_systbl WHERE terms @@ fti_tsquery(?))", QueryBuilder.CreateParameterValue(parms[0], typeof(String)))); case "act_id": // act search case "src_act_id": case "trg_act_id": return(current.Append($"{filterColumn} IN (SELECT act_id FROM ft_act_systbl WHERE terms @@ fti_tsquery(?))", QueryBuilder.CreateParameterValue(parms[0], typeof(String)))); case "cd_id": // code search case "src_cd_id": // code search case "trg_cd_id": // code search return(current.Append($"{filterColumn} IN (SELECT cd_id FROM ft_cd_systbl WHERE terms @@ fti_tsquery(?))", QueryBuilder.CreateParameterValue(parms[0].Split(' '), typeof(String)))); default: throw new InvalidOperationException("PostgreSQL does not understand freetext search on this type of data"); } } else { return(current.Append($"{filterColumn} IS NULL")); // Return no results } } else { throw new InvalidOperationException("Freetext requires a parameter"); } }
private void AppendRowVersionCheckReturn(SqlStatement sql, DbTableInfo table, EntityRecord record) { var rvCol = table.Columns.First(c => c.Flags.IsSet(DbColumnFlags.RowVersion)); // do row count check for update only, not for insert if (record.Status == EntityStatus.Modified) { var tag = new TextSqlFragment($"'ConcurrentUpdate/{table.Entity.Name}/{record.PrimaryKey.ValuesToString()}'"); var checkRowsSql = _msDialect.SqlCheckRowCountIsOne.Format(tag); sql.Append(checkRowsSql); } // return RowVersion in parameter var rvPrmPholder = new SqlColumnValuePlaceHolder(rvCol, ParameterDirection.InputOutput); sql.PlaceHolders.Add(rvPrmPholder); rvPrmPholder.PreviewParameter = (prm, ph) => { prm.DbType = DbType.Binary; prm.Size = 8; }; var getRvSql = _msDialect.SqlGetRowVersionTemplate.Format(rvPrmPholder); sql.Append(getRvSql); sql.Append(SqlTerms.NewLine); }
/// <summary> /// Query hack for creation time /// </summary> public bool HackQuery(QueryBuilder builder, SqlStatement sqlStatement, SqlStatement whereClause, Type tmodel, PropertyInfo property, string queryPrefix, QueryPredicate predicate, object values, IEnumerable <TableMapping> scopedTables, params KeyValuePair <String, object>[] queryFilter) { if (property.Name == nameof(IBaseEntityData.CreationTime) && typeof(IVersionedEntity).IsAssignableFrom(tmodel)) // filter by first creation time { // Get the version table (which has var ormMap = scopedTables.SelectMany(o => o.Columns); var replacesVersion = ormMap.FirstOrDefault(o => o.SourceProperty.Name == nameof(IDbVersionedData.ReplacesVersionKey)); var joinCol = replacesVersion.Table.Columns.FirstOrDefault(o => o.SourceProperty.Name == nameof(IDbVersionedData.Key)); whereClause.And($"EXISTS (SELECT 1 FROM {replacesVersion.Table.TableName} AS crt{replacesVersion.Table.TableName} WHERE crt{replacesVersion.Table.TableName}.{joinCol.Name} = {queryPrefix}{replacesVersion.Table.TableName}.{joinCol.Name} AND crt{replacesVersion.Table.TableName}.{replacesVersion.Name} IS NULL "); whereClause.And(builder.CreateWhereCondition(tmodel, predicate.Path, values, "crt", scopedTables.ToList())); whereClause.Append(")"); return(true); } return(false); }
/// <summary> /// Creates the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length != 1) { throw new ArgumentException("Approx requires at least one parameter"); } var config = ApplicationServiceContext.Current.GetService <IConfigurationManager>().GetSection <ApproximateMatchingConfigurationSection>(); if (config == null) { config = new ApproximateMatchingConfigurationSection() { ApproxSearchOptions = new List <ApproxSearchOption>() { new ApproxPatternOption() { Enabled = true, IgnoreCase = true } } } } ; var filter = new SqlStatement(current.DbProvider); foreach (var alg in config.ApproxSearchOptions.Where(o => o.Enabled)) { if (alg is ApproxPatternOption pattern) { if (pattern.IgnoreCase) { filter.Or($"LOWER({filterColumn}) like LOWER(?)", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } else { filter.Or($"{filterColumn} like ?", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } } } return(current.Append("(").Append(filter).Append(")")); } }
/// <summary> /// Execute the current operation /// </summary> public IEnumerable <object> Execute(SubscriptionDefinition subscription, NameValueCollection parameters, int offset, int?count, out int totalResults, Guid queryId) { if (subscription == null || subscription.ServerDefinitions.Count == 0) { throw new InvalidOperationException("Subscription does not have server definition"); } try { var preArgs = new QueryRequestEventArgs <IdentifiedData>(o => o.Key == subscription.Key, offset, count, queryId, AuthenticationContext.Current.Principal, new ModelSort <IdentifiedData> [0], parameters); this.Executing?.Invoke(this, preArgs); if (preArgs.Cancel) { this.m_tracer.TraceWarning("Pre-Event for executor failed"); totalResults = preArgs.TotalResults; return(preArgs.Results); } var persistenceType = typeof(IDataPersistenceService <>).MakeGenericType(subscription.ResourceType); var persistenceInstance = ApplicationServiceContext.Current.GetService(persistenceType) as IAdoPersistenceService; var queryService = ApplicationServiceContext.Current.GetService <IQueryPersistenceService>(); var cacheService = ApplicationServiceContext.Current.GetService <IDataCachingService>(); // Get the definition var definition = subscription.ServerDefinitions.FirstOrDefault(o => o.InvariantName == m_configuration.Provider.Invariant); if (definition == null) { throw new InvalidOperationException($"Subscription does not provide definition for provider {m_configuration.Provider.Invariant}"); } // No obsoletion time? if (typeof(IBaseEntityData).IsAssignableFrom(subscription.ResourceType) && !parameters.ContainsKey("obsoletionTime")) { parameters.Add("obsoletionTime", "null"); } // Query expression var queryExpression = typeof(QueryExpressionParser).GetGenericMethod( nameof(QueryExpressionParser.BuildLinqExpression), new Type[] { subscription.ResourceType }, new Type[] { typeof(NameValueCollection) } ).Invoke(null, new object[] { parameters }); // Query has been registered? IEnumerable <IdentifiedData> result = null; if (queryId != Guid.Empty && queryService?.IsRegistered(queryId) == true) { totalResults = (int)queryService.QueryResultTotalQuantity(queryId); result = queryService.GetQueryResults(queryId, offset, count ?? 100) .Select(o => { try { var retVal = cacheService.GetCacheItem(o); if (retVal == null) { using (var ctx = m_configuration.Provider.GetReadonlyConnection()) { ctx.Open(); ctx.LoadState = LoadState.FullLoad; retVal = persistenceInstance.Get(ctx, o) as IdentifiedData; cacheService?.Add(retVal); } } return(retVal); } catch (Exception e) { this.m_tracer.TraceError("Error fetching query results for {0}: {1}", queryId, e); throw new DataPersistenceException("Error fetching query results", e); } }).OfType <IdentifiedData>().ToList(); } else { // Now grab the context and query!!! using (var connection = m_configuration.Provider.GetReadonlyConnection()) { try { connection.Open(); connection.LoadState = LoadState.FullLoad; // First, build the query using the query build TableMapping tableMapping = null; if (typeof(Entity).IsAssignableFrom(subscription.ResourceType)) { tableMapping = TableMapping.Get(typeof(DbEntityVersion)); } else if (typeof(Act).IsAssignableFrom(subscription.ResourceType)) { tableMapping = TableMapping.Get(typeof(DbActVersion)); } else if (typeof(Concept).IsAssignableFrom(subscription.ResourceType)) { tableMapping = TableMapping.Get(typeof(DbConceptVersion)); } else { throw new InvalidOperationException("ADO Subscriptions only support Entities and Acts (or sub-types)"); } var query = (typeof(QueryBuilder).GetGenericMethod( nameof(QueryBuilder.CreateQuery), new Type[] { subscription.ResourceType }, new Type[] { queryExpression.GetType(), typeof(ColumnMapping).MakeArrayType() } ).Invoke(this.m_queryBuilder, new object[] { queryExpression, tableMapping.Columns.ToArray() }) as SqlStatement).Build(); // Now we want to remove the portions of the built query statement after FROM and before WHERE as the definition will be the source of our selection SqlStatement domainQuery = new SqlStatement(m_configuration.Provider, query.SQL.Substring(0, query.SQL.IndexOf(" FROM "))); // Append our query var definitionQuery = definition.Definition; List <Object> values = new List <object>(); definitionQuery = this.m_parmRegex.Replace(definitionQuery, (o) => { if (parameters.TryGetValue("_" + o.Groups[2].Value.Substring(1, o.Groups[2].Value.Length - 2), out List <String> qValue)) { Guid uuid = Guid.Empty; if (Guid.TryParse(qValue.First(), out uuid)) { values.AddRange(qValue.Select(v => Guid.Parse(v)).OfType <Object>()); } else { values.AddRange(qValue); } return(o.Groups[1].Value + String.Join(",", qValue.Select(v => "?"))); } return("NULL"); }); // Now we want to append domainQuery.Append(" FROM (").Append(definitionQuery, values.ToArray()).Append($") AS {tableMapping.TableName} "); domainQuery.Append(query.SQL.Substring(query.SQL.IndexOf("WHERE ")), query.Arguments.ToArray()); // Now we want to create the result type var resultType = tableMapping.OrmType; if (typeof(IDbVersionedData).IsAssignableFrom(resultType)) // type is versioned so we have to join { var fkType = tableMapping.GetColumn("Key").ForeignKey.Table; resultType = typeof(CompositeResult <,>).MakeGenericType(resultType, fkType); } // Now we want to select out our results if (count == 0) { totalResults = connection.Count(domainQuery); return(null); } else { // Fetch var domainResults = typeof(DataContext).GetGenericMethod( nameof(DataContext.Query), new Type[] { resultType }, new Type[] { typeof(SqlStatement) }).Invoke(connection, new object[] { domainQuery }) as IOrmResultSet; IEnumerable <object> resultObjects = null; // Register query if query id specified if (queryId != Guid.Empty) { var results = domainResults.Keys <Guid>().OfType <Guid>().ToArray(); this.m_tracer.TraceVerbose("Query for Keys: {0}", connection.GetQueryLiteral(domainResults.Keys <Guid>().ToSqlStatement())); totalResults = results.Count(); ApplicationServiceContext.Current.GetService <IQueryPersistenceService>()?.RegisterQuerySet(queryId, results, null, totalResults); resultObjects = results.Skip(offset).Take(count ?? 100).OfType <Object>(); } else if (m_configuration.UseFuzzyTotals || preArgs.UseFuzzyTotals) { this.m_tracer.TraceVerbose("Query for Objects: {0}", connection.GetQueryLiteral(domainResults.ToSqlStatement())); resultObjects = domainResults.Skip(offset).Take((count ?? 100) + 1).OfType <Object>(); totalResults = domainResults.Count(); } else { this.m_tracer.TraceVerbose("Query for Objects: {0}", connection.GetQueryLiteral(domainResults.ToSqlStatement())); totalResults = domainResults.Count(); resultObjects = domainResults.Skip(offset).Take(count ?? 100).OfType <Object>(); } this.m_tracer.TraceVerbose("If i show up in the log, the log is ???????? WHY?????"); // Return result = resultObjects .Take(count ?? 100) .OfType <Object>() .Select(o => { try { if (o is Guid) { var retVal = cacheService.GetCacheItem((Guid)o); if (retVal == null) { using (var subConn = connection.OpenClonedContext()) { retVal = persistenceInstance.Get(subConn, (Guid)o) as IdentifiedData; cacheService?.Add(retVal); } } return(retVal); } else { var idData = (o as CompositeResult)?.Values.OfType <IDbIdentified>().FirstOrDefault() ?? o as IDbIdentified; var retVal = cacheService.GetCacheItem(idData.Key); if (retVal == null) { using (var subConn = connection.OpenClonedContext()) { retVal = persistenceInstance.ToModelInstance(o, subConn) as IdentifiedData; cacheService?.Add(retVal); } } return(retVal); } } catch (Exception e) { this.m_tracer.TraceError("Error converting result: {0}", e); throw; } }).OfType <IdentifiedData>().ToList(); } } catch (Exception e) { #if DEBUG this.m_tracer.TraceError("Error executing subscription: {0}", e); #else this.m_tracer.TraceError("Error executing subscription: {0}", e.Message); #endif throw new DataPersistenceException($"Error executing subscription: {e.Message}", e); } } // using conn } // if var postEvt = new QueryResultEventArgs <IdentifiedData>(o => o.Key == subscription.Key, result, offset, count, totalResults, queryId, AuthenticationContext.Current.Principal); this.Executed?.Invoke(this, postEvt); // Now set the overridden data return(postEvt.Results); } catch (Exception e) { this.m_tracer.TraceError("Error executing core ADO Subscription logic for {0}: {1}", subscription.Key, e); throw new Exception($"Error executing core ADO subscription logic for {subscription.Key}", e); } }
/// <summary> /// Parses a filter dictionary and creates the necessary SQL /// </summary> private SqlStatement ParseFilterDictionary(DataContext context, String objectName, IDictionary <string, object> parms, List <DatamartSchemaProperty> properties) { SqlStatement retVal = context.CreateSqlStatement(); if (parms.Count() > 0) { foreach (var s in parms) { object rValue = s.Value; if (!(rValue is IList)) { rValue = new List <Object>() { rValue } } ; retVal.Append("("); string key = s.Key.Replace(".", "_").Replace("[]", ""), scopedQuery = objectName + "."; // Property info var pi = properties.FirstOrDefault(o => o.Name == key); foreach (var itm in rValue as IList) { var value = itm; String filter = String.Empty; var op = "AND"; if (value is String) { var sValue = itm as String; switch (sValue[0]) { case '<': if (sValue[1] == '=') { filter = $" {key} <= ?"; value = sValue.Substring(2); } else { filter = $" {key} < ?"; value = sValue.Substring(1); } break; case '>': if (sValue[1] == '=') { filter = $"{key} >= ?"; value = sValue.Substring(2); } else { filter = $"{key} > ?"; value = sValue.Substring(1); } break; case '!': if (sValue.Equals("!null")) { filter = $"{key} IS NOT NULL"; value = sValue = null; } else { filter = $"{key} <> ?"; value = sValue.Substring(1); } break; case '~': filter = $"{key} {this.m_configuration.Provider.CreateSqlKeyword(OrmLite.Providers.SqlKeyword.ILike)} '%' || ? || '%'"; value = sValue.Substring(1); op = "OR"; break; default: if (sValue.Equals("null")) { filter = $"{key} IS NULL"; value = sValue = null; } else { filter = $"{key} = ?"; op = "OR"; } break; } sValue = value as String; if (sValue != null) { switch (pi.Type) { case SchemaPropertyType.Binary: value = Convert.FromBase64String(sValue); break; case SchemaPropertyType.Boolean: value = Boolean.Parse(sValue); break; case SchemaPropertyType.Date: value = DateTime.Parse(sValue).Date; break; case SchemaPropertyType.DateTime: case SchemaPropertyType.TimeStamp: value = DateTimeOffset.Parse(sValue); break; case SchemaPropertyType.Decimal: value = Decimal.Parse(sValue); break; case SchemaPropertyType.Float: value = float.Parse(sValue); break; case SchemaPropertyType.Integer: value = int.Parse(sValue); break; case SchemaPropertyType.Uuid: value = Guid.Parse(sValue); break; } } } else if (value != null) { filter = $"{key} = ?"; } else { filter = $"{key} IS NULL"; value = null; } // Append if (value != null) { retVal.Append(filter, value); } else { retVal.Append(filter); } retVal.Append(op); } retVal.RemoveLast(); retVal.Append(")").Append("AND"); } // exist or value retVal.RemoveLast(); } else { retVal.Append("1 = 1"); } return(retVal); }
/// <summary> /// Hack query builder based on clause /// </summary> public bool HackQuery(QueryBuilder builder, SqlStatement sqlStatement, SqlStatement whereClause, Type tmodel, PropertyInfo property, string queryPrefix, QueryPredicate predicate, object values, IEnumerable <TableMapping> scopedTables, params KeyValuePair <String, object>[] queryFilter) { string columnName = String.Empty; Type scanType = null; // Filter values if (typeof(Concept).IsAssignableFrom(property.PropertyType) && predicate.SubPath == "mnemonic") { Regex removeRegex = null; if (predicate.Path == "participationRole" && property.DeclaringType == typeof(ActParticipation)) { columnName = "rol_cd_id"; scanType = typeof(ActParticipationKey); // We want to remove the inner join for cd_tbl removeRegex = new Regex(@"INNER\sJOIN\scd_tbl\s.*\(.*?rol_cd_id.*"); } else if (predicate.Path == "relationshipType" && property.DeclaringType == typeof(EntityRelationship)) { columnName = "rel_typ_cd_id"; scanType = typeof(EntityRelationshipTypeKeys); removeRegex = new Regex(@"INNER\sJOIN\scd_tbl\s.*\(.*?rel_typ_cd_id.*"); } else if (predicate.Path == "relationshipType" && property.DeclaringType == typeof(ActRelationship)) { columnName = "rel_typ_cd_id"; scanType = typeof(ActRelationshipTypeKeys); removeRegex = new Regex(@"INNER\sJOIN\scd_tbl\s.*\(.*?rel_typ_cd_id.*"); } else { return(false); } // Now we scan List <Object> qValues = new List <object>(); if (values is IEnumerable) { foreach (var i in values as IEnumerable) { var fieldInfo = scanType.GetRuntimeField(i.ToString()); if (fieldInfo == null) { return(false); } qValues.Add(fieldInfo.GetValue(null)); } } else { var fieldInfo = scanType.GetRuntimeField(values.ToString()); if (fieldInfo == null) { return(false); } qValues.Add(fieldInfo.GetValue(null)); } // Now add to query whereClause.And($"{columnName} IN ({String.Join(",", qValues.Select(o=>$"'{o}'").ToArray())})"); // Remove the inner join var remStack = new Stack <SqlStatement>(); SqlStatement last; while (sqlStatement.RemoveLast(out last)) { var m = removeRegex.Match(last.SQL); if (m.Success) { // The last thing we added was the if (m.Index == 0 && m.Length == last.SQL.Length) { remStack.Pop(); } else { sqlStatement.Append(last.SQL.Remove(m.Index, m.Length), last.Arguments.ToArray()); } break; } else { remStack.Push(last); } } while (remStack.Count > 0) { sqlStatement.Append(remStack.Pop()); } return(true); } else { return(false); } }
/// <summary> /// Create the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type type) { return(current.Append($"LOWER({filterColumn}) = LOWER(?)", QueryBuilder.CreateParameterValue(operand, type))); }
/// <summary> /// Creates the SQL statement /// </summary> public SqlStatement CreateSqlStatement(SqlStatement current, string filterColumn, string[] parms, string operand, Type operandType) { if (parms.Length != 1) { throw new ArgumentException("Approx requires at least one parameter"); } var config = ApplicationServiceContext.Current.GetService <IConfigurationManager>().GetSection <ApproximateMatchingConfigurationSection>(); if (config == null) { config = new ApproximateMatchingConfigurationSection() { ApproxSearchOptions = new List <ApproxSearchOption>() { new ApproxPatternOption() { Enabled = true, IgnoreCase = true }, new ApproxPhoneticOption() { Enabled = true, Algorithm = ApproxPhoneticOption.PhoneticAlgorithmType.Metaphone }, new ApproxDifferenceOption() { Enabled = true, MaxDifference = 1 } } } } ; var filter = new SqlStatement(current.DbProvider); foreach (var alg in config.ApproxSearchOptions.Where(o => o.Enabled)) { if (alg is ApproxDifferenceOption difference) { filter.Or($"(length(trim({filterColumn})) > {difference.MaxDifference * 2} AND levenshtein(TRIM(LOWER({filterColumn})), TRIM(LOWER(?))) <= {difference.MaxDifference})", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (alg is ApproxPhoneticOption phonetic) { var min = phonetic.MinSimilarity; if (!phonetic.MinSimilaritySpecified) { min = 1.0f; } if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.Soundex) { filter.Or($"soundex({filterColumn}) = soundex(?)", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.Metaphone) { filter.Or($"metaphone({filterColumn},4) = metaphone(?,4)", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else if (phonetic.Algorithm == ApproxPhoneticOption.PhoneticAlgorithmType.DoubleMetaphone) { filter.Or($"dmetaphone({filterColumn}) = dmetaphone(?)", QueryBuilder.CreateParameterValue(parms[0], typeof(String))); } else { throw new InvalidOperationException($"Phonetic algorithm {phonetic.Algorithm} is not valid"); } } else if (alg is ApproxPatternOption pattern) { if (pattern.IgnoreCase) { filter.Or($"{filterColumn} ilike ?", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } else { filter.Or($"{filterColumn} like ?", QueryBuilder.CreateParameterValue(parms[0].Replace("*", "%").Replace("?", "_"), typeof(String))); } } } return(current.Append("(").Append(filter).Append(")")); } }