/// <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(")"));
}
}
