public DMLService(IDatabaseServices databaseServices) : base(databaseServices)
{
queries = new DMLQueries(this);
identifiers = new DMLIdentifiers(this);
operators = new DMLOperators(this);
functions = new DMLFunctions(this);
aggregateFunctions = new DMLAggregateFunctions(this);
defaultValues = new DMLDefaultValues(this);
syntaxHighlightDefinitions = new DMLSyntaxHighlightDefinitions(this);
}
/// <summary>
/// Initializes a new instance of the <see cref="DMLService"/> class.
/// </summary>
/// <param name="databaseServices">The database services.</param>
public DMLService(IDatabaseServices databaseServices)
: base(databaseServices)
{
_queries = new DMLQueries(this);
_identifiers = new DMLIdentifiers(this);
_operators = new DMLOperators(this);
_functions = new DMLFunctions(this);
_aggregateFunctions = new DMLAggregateFunctions(this);
_defaultValues = new DMLDefaultValues(this);
}
/// <summary>
/// This method generates the query that will be used in the event trigger.
/// This assumes that the underlying database has the NULLIF and COALESCE functions.
/// </summary>
/// <param name="sql">StringBuilder that will receive the query SQL.</param>
/// <param name="triggerTablePrimaryKeyColumn">Primary key column of the table associated with the trigger.</param>
/// <param name="triggerTableEventColumns">Columns of the table associated with the trigger that fire events.</param>
/// <param name="triggerTableForeignKeys">Foreign keys of the table associated with the trigger.</param>
/// <param name="eventTable">Table source that stores the events for the table associated with the trigger. This table resides in the same database as the table where the trigger is defined.</param>
/// <param name="eventQueueTable">Table source that stores the events to be fired by the platform.</param>
/// <param name="triggerDataAccessor">SQL snippet to access the newly triggered data (new or updated line in trigger table).</param>
/// <param name="needsTriggerDataAccessorInFrom">True if we need to include the <paramref name="triggerDataAccessor"/> in a from clause to access it in a query.</param>
/// <param name="isUpdateVariableAccessor">SQL snippet to access the variable that is true if this trigger is an update.</param>
protected virtual void FillEventTriggerQuery(StringBuilder sql, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn,
IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys,
ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, string triggerDataAccessor, bool needsTriggerDataAccessorInFrom,
string isUpdateVariableAccessor)
{
// We don't use the qualified name if both tables are in the same database, because this breaks database clone processes
sql.AppendFormat(" INSERT INTO {0}", eventTable.Database.Equals(eventQueueTable.Database) ? eventQueueTable.Name : eventQueueTable.QualifiedName);
sql.Append("(ESPACE_ID, TENANT_ID, ACTIVITY_ID, PROCESS_DEF_ID, DATA_ID, ENQUEUE_TIME, ERROR_COUNT, NEXT_RUN)");
var triggerTableEventColumnsList = triggerTableEventColumns.ToList();
ITableSourceColumnInfo tenantFilterField = GetTenantFilterField(triggerTableEventColumnsList);
string defaultTenantIdField = Identifiers.EscapeIdentifier("_TENANT_ID");
string tenantIdInSelect = (tenantFilterField == null)? defaultTenantIdField:
"COALESCE(" + defaultTenantIdField + ", " + triggerDataAccessor + "."
+ Identifiers.EscapeIdentifier(tenantFilterField.Name) + ")";
IDMLService dmlService = DatabaseServices.DMLService;
string dataIdInSelect = String.Format("{0}.{1}", triggerDataAccessor, Identifiers.EscapeIdentifier(triggerTablePrimaryKeyColumn.Name));
dataIdInSelect = (triggerTablePrimaryKeyColumn.DataType.Type == DBDataType.INTEGER)? dmlService.Functions.IntegerToText(dataIdInSelect):
dataIdInSelect;
sql.AppendFormat(" (SELECT {0}, {1}, {2}, {3}, {4}, GETDATE(), 0, GETDATE() FROM {5} evt{6}",
Identifiers.EscapeIdentifier("_ESPACE_ID"), tenantIdInSelect, Identifiers.EscapeIdentifier("_ACTIVITY_ID"),
Identifiers.EscapeIdentifier("_PROCESS_DEF_ID"), dataIdInSelect, Identifiers.EscapeIdentifier(eventTable.Name),
needsTriggerDataAccessorInFrom? (", " + triggerDataAccessor): String.Empty);
sql.Append(" WHERE ");
IDMLOperators operators = dmlService.Operators;
string whereClause = operators.Equal("evt." + Identifiers.EscapeIdentifier("_IS_UPDATE"), isUpdateVariableAccessor);
var triggerTableColumnNames = new HashSet <string>(triggerTableForeignKeys.Select(fk => fk.ColumnName.ToUpperInvariant()));
foreach (var column in triggerTableEventColumnsList)
{
string insertedFieldSnippet = triggerDataAccessor + "." + Identifiers.EscapeIdentifier(column.Name);
string evtFieldSnippet = "evt." + Identifiers.EscapeIdentifier(column.Name);
string coalesceSnippet = "{0}";
string nullIfSnippet = "{0}";
if (triggerTableColumnNames.Contains(column.Name.ToUpperInvariant()) || (column == tenantFilterField))
{
switch (column.DataType.Type)
{
case DBDataType.INTEGER:
nullIfSnippet = "NULLIF({0}, " + GetDefaultValue(DBDataType.INTEGER) + ")";
coalesceSnippet = "COALESCE({0}, " + GetDefaultValue(DBDataType.INTEGER) + ")";
break;
case DBDataType.LONGINTEGER:
nullIfSnippet = "NULLIF({0}, " + GetDefaultValue(DBDataType.LONGINTEGER) + ")";
coalesceSnippet = "COALESCE({0}, " + GetDefaultValue(DBDataType.LONGINTEGER) + ")";
break;
case DBDataType.TEXT:
nullIfSnippet = "NULLIF({0}, " + GetDefaultValue(DBDataType.TEXT) + ")";
coalesceSnippet = "COALESCE({0}, " + GetDefaultValue(DBDataType.TEXT) + ")";
break;
}
}
string condition = operators.Or(operators.IsNull(nullIfSnippet.F(evtFieldSnippet)),
operators.Equal(evtFieldSnippet, coalesceSnippet.F(insertedFieldSnippet)));
whereClause = operators.And(whereClause, "(" + condition + ")");
}
sql.Append(whereClause);
sql.Append(")");
}
