private IEnumerable <IPlatformTableSourceIndexInfo> GetIndexes(TableSourceInfo tableSource, IEnumerable <IPlatformTableSourceColumnInfo> columns)
{
IDictionary <string, IPlatformTableSourceIndexInfo> indexes = new Dictionary <string, IPlatformTableSourceIndexInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDataReader reader = GetTableSourceIndexes(conn, tableSource)) {
while (reader.Read())
{
string indexName = Convert.ToString(reader["idxName"]);
if (indexName.StartsWith("_WA_SYS_")) // _WA_SYS_* are statistics, not indexes
{
continue;
}
bool isUnique = (bool)reader["isUnique"];
bool isPrimaryKey = (bool)reader["isPrimaryKey"];
string columnName = Convert.ToString(reader["colName"]);
if (!indexName.IsNullOrEmpty())
{
IPlatformTableSourceIndexInfo index;
if (!indexes.TryGetValue(indexName, out index))
{
index = new PlatformTableSourceIndexInfo(tableSource, indexName, isUnique, isPrimaryKey);
indexes.Add(indexName, index);
}
IPlatformTableSourceColumnInfo column = columns.Single(c => c.Name.EqualsIgnoreCase(columnName));
((PlatformTableSourceIndexInfo)index).AddColumn(column);
}
}
return(indexes.Values.ToList());
}
}
}
public void AddColumn(IPlatformTableSourceColumnInfo columnInfo)
{
if (columnInfo != null && !columns.Any(col => StringUtils.EqualsIgnoreCase(col.Name, columnInfo.Name)))
{
columns.Add(columnInfo);
}
}
public override IEnumerable <string> DropColumn(IPlatformTableSourceColumnInfo existingColumn)
{
var result = new List <string>();
if (existingColumn.IsAutoGenerated)
{
var columnInfo = (PlatformTableSourceColumnInfo)existingColumn;
IDatabaseInfo databaseInfo = existingColumn.TableSource.Database;
string triggerName = columnInfo.AutoNumberTriggerName;
if (!String.IsNullOrEmpty(triggerName))
{
result.Add(String.Format("DROP TRIGGER {0}", Identifiers.EscapeAndQualifyIdentifier(databaseInfo, triggerName)));
}
string sequenceName = columnInfo.AutoNumberSequenceName;
if (!String.IsNullOrEmpty(sequenceName))
{
result.Add(String.Format("DROP SEQUENCE {0}", Identifiers.EscapeAndQualifyIdentifier(databaseInfo, sequenceName)));
}
}
result.AddRange(base.DropColumn(existingColumn));
return(result);
}
public override IEnumerable <string> CreateColumn(IPlatformTableSourceColumnInfo newColumn, string defaultValue)
{
IList <string> statements = new List <string>();
string blobStorage = "";
if (newColumn.DataType.Type == DBDataType.BINARY_DATA)
{
blobStorage = LOBStorageStatement(newColumn.Name);
}
string addColumnStatement = string.Format("ALTER TABLE {0} ADD ({1}){2}", newColumn.TableSource.QualifiedName,
GetColumnDefinition(newColumn, defaultValue), blobStorage);
statements.Add(addColumnStatement);
if (newColumn.IsPrimaryKey)
{
statements.AddRange(CreatePrimaryKey(newColumn.TableSource, newColumn));
}
if (newColumn.IsAutoGenerated)
{
statements.AddRange(SetColumnToAutonumber(newColumn));
}
return(statements);
}
/// <summary>
/// Returns the final mandatory value to use for the column definition. This can change depending on the column,
/// and depending on the default value.
/// </summary>
/// <param name="column">The column information.</param>
/// <param name="defaultValue">The default value for the column.</param>
/// <param name="mandatory">The mandatory value that we want to change to (tentatively).</param>
/// <returns>The final mandatory value that we want to change.</returns>
protected virtual bool GetFinalMandatoryValue(IPlatformTableSourceColumnInfo column, string defaultValue, bool mandatory)
{
if (UseDefaultValue(column, defaultValue) && (defaultValue == DatabaseServices.DMLService.DefaultValues.Null))
{
return(false); // Make sure we don't ask "DEFAULT NULL NOT NULL"
}
return(mandatory);
}
public override bool CanCreateColumn(IPlatformTableSourceColumnInfo newColumn, out string errorMessage)
{
errorMessage = "";
if (newColumn.IsAutoGenerated && newColumn.IsPrimaryKey == false)
{
errorMessage = string.Format("Column {0} of table {1} can't be Auto Number if it's not the identifier of the entity.", newColumn.Name, newColumn.TableSource.Name);
return(false);
}
return(true);
}
/// <summary>
/// This method returns the SQL for the column definition to be used inside the create table and create column statements.
/// This implementation returns "escapedColumnName columnSQLDataType DEFAULT defaultValue NOT NULL"
/// </summary>
/// <param name="column">The column information for the column to create.</param>
/// <param name="defaultValue">The default value for the column to create.</param>
/// <returns>SQL for the column definition.</returns>
protected virtual string GetColumnDefinition(IPlatformTableSourceColumnInfo column, string defaultValue)
{
return(Identifiers.EscapeIdentifier(column.Name) + " " + column.DataType.SqlDataType
+ (UseDefaultValue(column, defaultValue)
? " DEFAULT " + defaultValue
: String.Empty)
+ (GetFinalMandatoryValue(column, defaultValue, column.IsMandatory)
? " NOT"
: String.Empty)
+ " NULL");
}
/// <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="lightEventQueueTable">Table source that stores the light 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 void FillEventTriggerQuery(StringBuilder sql, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn,
IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys,
ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable, string triggerDataAccessor, bool needsTriggerDataAccessorInFrom,
string isUpdateVariableAccessor)
{
FillEventTriggerQueryInternal(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable,
triggerDataAccessor, needsTriggerDataAccessorInFrom, isUpdateVariableAccessor, /*isLight*/ false);
FillEventTriggerQueryInternal(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, lightEventQueueTable,
triggerDataAccessor, needsTriggerDataAccessorInFrom, isUpdateVariableAccessor, /*isLight*/ true);
}
public void AddColumn(IPlatformTableSourceColumnInfo columnInfo, bool isFunctionIndex)
{
if (columnInfo != null && !columns.Any(col => StringUtils.EqualsIgnoreCase(col.Name, columnInfo.Name)))
{
columns.Add(columnInfo);
if (isFunctionIndex)
{
functionIndexColumns.Add(columnInfo);
}
}
}
/// <summary>
/// This method generates the SQL to create a new table.
/// This implementation returns the statement "CREATE TABLE FullyQualifiedTableName (columnsDefinitionStatements, primaryKeyStatement)"
/// </summary>
/// <param name="newTable">Info about the table to create.</param>
/// <param name="columns">The columns information for the table to create along with the default values.
/// Note that some of them may be primary keys, as indicated on the IsPrimaryKey property.
/// This will lead to the creation of Primary Key Constraints.
/// Also note that a column could be an autonumber column, there's no need to call the AlterColumnChangeAutoNumber after.</param>
/// <returns>SQL statements to create the table.</returns>
public virtual IEnumerable <string> CreateTable(ITableSourceInfo newTable, params ColumnDetails[] columns)
{
IEnumerable <string> columnDefinitions = columns.Select(col => GetColumnDefinition(col.Column, col.DefaultValue));
IPlatformTableSourceColumnInfo primaryKey = columns.Select(col => col.Column).SingleOrDefault(col => col.IsPrimaryKey);
string primaryKeyStatement = primaryKey == null ? string.Empty:
GetPrimaryKeyTableConstraint(GetNewPrimaryKeyName(newTable.Name), primaryKey);
yield return(String.Format("CREATE TABLE {0} ({1}{2})", newTable.QualifiedName,
columnDefinitions.StrCat(", "), primaryKeyStatement.IsNullOrEmpty() ? "" : ", " + primaryKeyStatement));
}
public override bool CanCreateTable(ITableSourceInfo newTable, ColumnDetails[] columns, out string errorMessage)
{
errorMessage = "";
IPlatformTableSourceColumnInfo badColumn = columns.Select(c => c.Column).Where(c => c.IsAutoGenerated).FirstOrDefault(c => c.IsPrimaryKey == false);
if (badColumn != null)
{
errorMessage = string.Format("Column {0} of table {1} can't be Auto Number if it's not the identifier of the entity.", badColumn.Name, badColumn.TableSource.Name);
return(false);
}
return(true);
}
private IEnumerable <string> SetColumnToAutonumber(IPlatformTableSourceColumnInfo existingColumn)
{
IList <string> statements = new List <string>();
ITableSourceInfo tableSourceInfo = existingColumn.TableSource;
string sequenceName = ObjectFactory.GetNewUniqueIdentifer(DatabaseServices, AUTO_NUMBER_SEQUENCE_PREFIX + tableSourceInfo.Name);
string triggerName = ObjectFactory.GetNewUniqueIdentifer(DatabaseServices, AUTO_NUMBER_TRIGGER_PREFIX + tableSourceInfo.Name);
statements.Add(GetCreateAutoNumberSequenceStatement(tableSourceInfo.Database, sequenceName));
statements.Add(GetCreateAutoNumberTriggerStatement(existingColumn, triggerName, sequenceName));
return(statements);
}
private string CreateAdditionalPrimaryKeyIndexForCIAI(string tableName, string primaryKeyConstraintName,
IPlatformTableSourceColumnInfo column)
{
IPlatformDMLIdentifiers dmlIdentifiers = DatabaseServices.DMLService.Identifiers;
string idxName = primaryKeyConstraintName.Replace(PlatformDatabaseObjectConstants.PrimaryConstPrefix, PrimaryConstInvariantPrefix).Replace(
PlatformDatabaseObjectConstants.PrimarySysConstPrefix, PrimarySysConstInvariantPrefix);
return(string.Format("CREATE UNIQUE INDEX {0} ON {1} (NLSSORT({2}, 'NLS_SORT=BINARY_AI'))",
dmlIdentifiers.EscapeAndQualifyIdentifierForLocalDatabase(idxName),
dmlIdentifiers.EscapeAndQualifyIdentifierForLocalDatabase(tableName), dmlIdentifiers.EscapeIdentifier(column.Name)));
}
protected override bool UseDefaultValue(IPlatformTableSourceColumnInfo column, string defaultValue)
{
//mysql running on windows doesn't support default values on text columns
if (column.DataType.Type == DBDataType.TEXT && column.DataType.Length > 2000)
{
return(false);
}
else
{
return(base.UseDefaultValue(column, defaultValue));
}
}
private string GetDropDefaultContraintStatement(IPlatformTableSourceColumnInfo existingColumn)
{
const string sqlFormat = "DECLARE @ObjectName NVARCHAR(100);"
+ " SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM sys.columns"
+ " WHERE [object_id] = OBJECT_ID('{0}') AND [name] = '{1}';"
+ " IF @ObjectName IS NOT NULL"
+ " EXEC('ALTER TABLE {0} DROP CONSTRAINT ' + @ObjectName)";
ITableSourceInfo tableSourceInfo = existingColumn.TableSource;
return(GetSpecialSchemaStatement(tableSourceInfo.Database,
String.Format(sqlFormat, Identifiers.EscapeIdentifier(tableSourceInfo.Name), existingColumn.Name)));
}
/// <summary>
/// This method generates the SQL to create a new column.
/// This implementation returns "ALTER TABLE FullyQualifiedTableName ADD columnsDefinitionStatement"
/// and adds the SQL of the CreatePrimaryKey if the column is primary key.
/// </summary>
/// <param name="newColumn">Info about the column to create.</param>
/// <param name="defaultValue">Column default value. It could be empty.</param>
/// <returns>SQL statements to create the column.</returns>
public virtual IEnumerable <string> CreateColumn(IPlatformTableSourceColumnInfo newColumn, string defaultValue)
{
string addColumnStatement = String.Format("ALTER TABLE {0} ADD {1}", newColumn.TableSource.QualifiedName,
GetColumnDefinition(newColumn, defaultValue));
var result = new List <string> {
addColumnStatement
};
if (newColumn.IsPrimaryKey)
{
result.AddRange(CreatePrimaryKey(newColumn.TableSource, newColumn));
}
return(result);
}
public override IEnumerable <string> AlterColumn(IPlatformTableSourceColumnInfo existingColumn, IPlatformTableSourceColumnInfo newColumn,
string defaultValue)
{
string errorMessage;
if (!CanAlterColumn(existingColumn, newColumn, out errorMessage))
{
throw new InvalidOperationException(errorMessage);
}
var result = new List <string>();
string tablename = existingColumn.TableSource.QualifiedName;
string columName = Identifiers.EscapeIdentifier(existingColumn.Name);
bool useDefaultValue = UseDefaultValue(newColumn, defaultValue);
if (useDefaultValue || (existingColumn.IsMandatory != newColumn.IsMandatory) ||
!existingColumn.DataType.IsEquivalent(newColumn.DataType))
{
/* Drop old default if exists */
result.Add(GetDropDefaultContraintStatement(existingColumn));
string alterTable = String.Format("ALTER TABLE {0} ALTER COLUMN {1} {2}{3}", tablename, columName,
newColumn.DataType.SqlDataType,
(GetFinalMandatoryValue(newColumn, defaultValue, newColumn.IsMandatory) ? " NOT" : String.Empty) + " NULL");
result.Add(alterTable);
if (useDefaultValue)
{
// Add new default
string constrName = ObjectFactory.GetNewUniqueIdentifer(DatabaseServices,
"DF_" + Guid.NewGuid().ToString().Replace("-", "_"));
string addConstraint = String.Format("ALTER TABLE {0} ADD CONSTRAINT {1} DEFAULT {2} FOR {3}", tablename,
Identifiers.EscapeIdentifier(constrName), defaultValue, columName);
result.Add(addConstraint);
}
}
if (newColumn.IsPrimaryKey && !existingColumn.IsPrimaryKey)
{
result.AddRange(CreatePrimaryKey(newColumn.TableSource, newColumn));
}
return(result);
}
public override IEnumerable <string> AlterColumn(IPlatformTableSourceColumnInfo existingColumn, IPlatformTableSourceColumnInfo newColumn,
string defaultValue)
{
string errorMessage;
if (!CanAlterColumn(existingColumn, newColumn, out errorMessage))
{
throw new InvalidOperationException(errorMessage);
}
var result = new List <string>();
if (UseDefaultValue(newColumn, defaultValue) || (existingColumn.IsMandatory != newColumn.IsMandatory) ||
!existingColumn.DataType.IsEquivalent(newColumn.DataType))
{
bool isMandatory = GetFinalMandatoryValue(newColumn, defaultValue, newColumn.IsMandatory);
string isMandatoryStatement = isMandatory == existingColumn.IsMandatory ? "" : (isMandatory ? " NOT NULL" : " NULL");
string statement = String.Format("ALTER TABLE {0} MODIFY {1} {2}{3}{4}", existingColumn.TableSource.QualifiedName,
Identifiers.EscapeIdentifier(existingColumn.Name), newColumn.DataType.SqlDataType,
UseDefaultValue(newColumn, defaultValue) ? " DEFAULT " + defaultValue : String.Empty,
isMandatoryStatement);
result.Add(statement);
}
if (newColumn.IsPrimaryKey && !existingColumn.IsPrimaryKey)
{
result.AddRange(CreatePrimaryKey(newColumn.TableSource, newColumn));
}
var oracleExistingColumn = existingColumn as PlatformTableSourceColumnInfo;
if (newColumn.IsPrimaryKey)
{
result.AddRange(CreateAdditionalPrimaryKeyIndexForCIAI_IfNeeded(newColumn.TableSource, newColumn, oracleExistingColumn.HasCIAI_Index));
}
if (newColumn.IsAutoGenerated && !existingColumn.IsAutoGenerated)
{
result.AddRange(SetColumnToAutonumber(newColumn));
}
return(result);
}
/// <summary>
/// This method returns the SQL for the column definition to be used inside the create table and create column statements.
/// This implementation returns "escapedColumnName columnSQLDataType DEFAULT defaultValue NOT NULL"
/// </summary>
/// <param name="column">The column information for the column to create.</param>
/// <param name="defaultValue">The default value for the column to create.</param>
/// <returns>SQL for the column definition.</returns>
protected virtual string GetColumnDefinition(IPlatformTableSourceColumnInfo column, string defaultValue)
{
/* workaround */
var dataType = column.DataType.SqlDataType;
if (!string.IsNullOrEmpty(dataType) && dataType.StartsWith("decimal"))
{
dataType = "decimal(31, 8)";
}
return(Identifiers.EscapeIdentifier(column.Name) + " " + dataType
+ (UseDefaultValue(column, defaultValue)
? " DEFAULT " + defaultValue
: String.Empty)
+ (GetFinalMandatoryValue(column, defaultValue, column.IsMandatory)
? " NOT"
: String.Empty)
+ " NULL");
}
public override IEnumerable <string> CreateColumn(IPlatformTableSourceColumnInfo newColumn, string defaultValue)
{
IList <string> statements = new List <string>();
string addColumnStatement = string.Format("ALTER TABLE {0} ADD COLUMN ({1})", Identifiers.EscapeIdentifier(newColumn.TableSource.Name),
GetColumnDefinition(newColumn, defaultValue));
statements.Add(addColumnStatement);
if (newColumn.IsPrimaryKey)
{
statements.AddRange(CreatePrimaryKey(newColumn.TableSource, newColumn));
}
if (newColumn.IsAutoGenerated)
{
statements.AddRange(SetColumnToAutonumber(newColumn));
}
return(statements);
}
public override IEnumerable <string> CreateTable(ITableSourceInfo newTable, params ColumnDetails[] columns)
{
var dbConfig = DatabaseServices.DatabaseConfiguration as RuntimeDatabaseConfiguration;
if (dbConfig == null)
{
return(Enumerable.Empty <string>());
}
IList <IPlatformTableSourceColumnInfo> columnInfos = columns.Select(col => col.Column).ToList();
bool hasAutoGeneratedColumns = columns.Select(col => col.Column).Any(c => c.IsAutoGenerated);
IPlatformTableSourceColumnInfo primaryKey = columnInfos.SingleOrDefault(col => col.IsPrimaryKey);
bool isTextPK = primaryKey != null && primaryKey.DataType.Type == DBDataType.TEXT;
// set the storage properties of Blob field types
string blobStorage =
columnInfos.Where(c => c.DataType.Type == DBDataType.BINARY_DATA).Select(c => LOBStorageStatement(c.Name)).StrCat("");
string createTableStatement = string.Format("{0}{1} TABLESPACE {2}", base.CreateTable(newTable, columns).Single(),
blobStorage, Identifiers.EscapeIdentifier(dbConfig.Tablespace));
IList <string> statements = new List <string>();
statements.Add(createTableStatement);
if (hasAutoGeneratedColumns)
{
foreach (IPlatformTableSourceColumnInfo column in columnInfos.Where(col => col.IsAutoGenerated))
{
statements.AddRange(SetColumnToAutonumber(column));
}
}
if (isTextPK && dbConfig.CI_AI)
{
statements.Add(
CreateAdditionalPrimaryKeyIndexForCIAI(newTable.Name, GetGeneratedPrimaryKeyConstraintNameForTable(newTable), primaryKey));
}
return(statements);
}
public abstract IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn, IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable);
/// <summary>
/// Returns true if we can use the default value passed as argument in the column definition.
/// </summary>
/// <param name="column">The column to use.</param>
/// <param name="defaultValue">The default value for the column that we want to change to (tentatively).</param>
/// <returns>True if we can use the default value, false otherwise.</returns>
protected virtual bool UseDefaultValue(IPlatformTableSourceColumnInfo column, string defaultValue)
{
return(!column.IsPrimaryKey && (defaultValue != null));
}
public virtual bool CanCreateColumn(IPlatformTableSourceColumnInfo newColumn, out string errorMessage)
{
errorMessage = "";
return(true);
}
/// <summary>
/// Generates a message stating that a given column (<paramref name="existingColumn"/>) cannot be changed to another type, defined by the <paramref name="newColumn"/>.
/// </summary>
/// <param name="existingColumn">Existing column to be changed</param>
/// <param name="newColumn">New version of the column, based on the model</param>
/// <param name="isExistingColumnImmutable">True if the existing column cannot be changed into any other type, False if some conversions are allowed</param>
/// <returns>A user-friendly error message</returns>
protected static string GetColumnCannotBeChangedMessage(IPlatformTableSourceColumnInfo existingColumn, IPlatformTableSourceColumnInfo newColumn, bool isExistingColumnImmutable)
{
return(GetColumnCannotBeChangedMessage(existingColumn.TableSource.Name, existingColumn.Name, existingColumn.DataType.SqlDataType, existingColumn.DataType.Type.ToText(), newColumn.DataType.SqlDataType, newColumn.DataType.Type.ToText(), isExistingColumnImmutable));
}
/// <summary>
/// This method generates the SQL to drop a column previously obtained through the IIntrospectionService API.
/// This implementation returns "ALTER TABLE FullyQualifiedTableName DROP COLUMN escapedColumnName"
/// </summary>
/// <param name="existingColumn">Info about the column to drop.</param>
/// <returns>SQL statements to drop the column.</returns>
public virtual IEnumerable <string> DropColumn(IPlatformTableSourceColumnInfo existingColumn)
{
return(String.Format("ALTER TABLE {0} DROP COLUMN {1}", existingColumn.TableSource.QualifiedName,
Identifiers.EscapeIdentifier(existingColumn.Name)).ToEnumerable());
}
public abstract bool CanAlterColumn(IPlatformTableSourceColumnInfo existingColumn, IPlatformTableSourceColumnInfo newColumn,
out string errorMessage);
public abstract IEnumerable <string> AlterColumn(IPlatformTableSourceColumnInfo existingColumn,
IPlatformTableSourceColumnInfo newColumn, string defaultValue);
/// <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(")");
}
/// <summary>
/// This method generates the SQL to create a new primary key.
/// This implementation returns "ALTER TABLE FullyQualifiedTableName ADD primaryKeyStatement"
/// </summary>
/// <param name="existingTable">Info about the table to create a new primary key. This info is obtained through the IIntrospectionServiceAPI</param>
/// <param name="column">Info about the column that composes the primary key.</param>
/// <returns>SQL statements to create the primary key.</returns>
protected virtual IEnumerable <string> CreatePrimaryKey(ITableSourceInfo existingTable, IPlatformTableSourceColumnInfo column)
{
string constraintName = GetNewPrimaryKeyName(existingTable.Name);
string alterTableStatement = String.Format("ALTER TABLE {0} ADD {1}", existingTable.QualifiedName,
GetPrimaryKeyTableConstraint(constraintName, column));
return(alterTableStatement.ToEnumerable());
}
