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