protected override string GetPrimaryKeyTableConstraint(string constraintName, IPlatformTableSourceColumnInfo column)
{
string tableSpaceName = Identifiers.EscapeIdentifier(
((RuntimeDatabaseConfiguration)DatabaseServices.DatabaseConfiguration).TablespaceIndex);
return(string.Format("{0} USING INDEX TABLESPACE {1}", base.GetPrimaryKeyTableConstraint(constraintName, column), tableSpaceName));
}
/// <summary>
/// This method generates the SQL to create a new foreign key.
/// This implementation returns
/// ALTER TABLE FullyQualifiedTableName ADD CONSTRAINT escapedConstraintName FOREIGN KEY (escapedForeignKeyColumnNames) REFERENCES FullyQualifiedForeignTableName (escapedForeignColumnNames) ON DELETE CASCADE
/// </summary>
/// <param name="newForeignKey">Info about the foreign key to create.</param>
/// <returns>SQL statements to create the foreign key.</returns>
public virtual IEnumerable <string> CreateForeignKey(ITableSourceForeignKeyInfo newForeignKey)
{
yield return(String.Format("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4}){5}",
newForeignKey.TableSource.QualifiedName, Identifiers.EscapeIdentifier(newForeignKey.Name),
Identifiers.EscapeIdentifier(newForeignKey.ColumnName), newForeignKey.ReferencedTableSource.QualifiedName,
Identifiers.EscapeIdentifier(newForeignKey.ReferencedColumnName), newForeignKey.IsCascadeDelete? " ON DELETE CASCADE": String.Empty));
}
public override IEnumerable <string> CreateIndex(IPlatformTableSourceIndexInfo newIndex)
{
var createStatement = new StringBuilder();
var dbConfig = DatabaseServices.DatabaseConfiguration as MySQLRuntimeDatabaseConfiguration;
if (dbConfig == null)
{
yield break;
}
createStatement.AppendFormat("CREATE {0} INDEX {1}", newIndex.IsUnique? "UNIQUE": String.Empty,
Identifiers.EscapeIdentifier(newIndex.Name));
var indexFields = new StringBuilder();
foreach (var col in newIndex.Columns)
{
if (col.DataType.Type == DBDataType.TEXT && col.DataType.Length > 191)
{
//mysql has a key size limit of 766 bytes which translates to 191 chars on utf8mb4
//we are truncating the index which should be ok on most big columns
indexFields.Append(Identifiers.EscapeIdentifier(col.Name));
indexFields.Append("(191),");
}
else
{
indexFields.Append(Identifiers.EscapeIdentifier(col.Name));
indexFields.Append(",");
}
}
createStatement.AppendFormat(" ON {0} ({1});", newIndex.TableSource.Name, indexFields.ToString(0, indexFields.Length - 1));
yield return(createStatement.ToString());
}
public override IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn,
IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys,
ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable)
{
var sql = new StringBuilder();
string triggerName = ((newTrigger.Name.ToUpperInvariant().EndsWith("_I") || newTrigger.Name.ToUpperInvariant().EndsWith("_U")) ?
newTrigger.Name.Substring(0, newTrigger.Name.Length - 2) : newTrigger.Name).Right(Identifiers.MaxLength - 2);
ITableSourceInfo table = newTrigger.TableSource;
sql.Append("CREATE TRIGGER " + Identifiers.EscapeIdentifier(triggerName + "_I"));
sql.Append(" AFTER INSERT ON " + Identifiers.EscapeIdentifier(table.Name));
sql.Append(" FOR EACH ROW ");
sql.Append(" BEGIN ");
sql.Append(" DECLARE isUpdating integer default 0;");
FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable,
eventQueueTable, lightEventQueueTable, "NEW", false, "isUpdating");
sql.AppendLine(" END;");
sql.Append("CREATE TRIGGER " + Identifiers.EscapeIdentifier(triggerName + "_U"));
sql.Append(" AFTER UPDATE ON " + Identifiers.EscapeIdentifier(table.Name));
sql.Append(" FOR EACH ROW ");
sql.Append(" BEGIN ");
sql.Append(" DECLARE isUpdating integer default 1;");
FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable,
eventQueueTable, lightEventQueueTable, "NEW", false, "isUpdating");
sql.AppendLine(" END;");
return(sql.ToString().ToEnumerable());
}
/// <summary>
/// This method generates the SQL to drop a foreign key previously obtained through the IIntrospectionService API.
/// This implementation returns "ALTER TABLE FullyQualifiedTableName DROP CONSTRAINT escapedConstraintName"
/// </summary>
/// <param name="existingForeignKey">Info about the foreign key to drop.</param>
/// <returns>SQL statements to drop the foreign key.</returns>
public virtual IEnumerable <string> DropForeignKey(ITableSourceForeignKeyInfo existingForeignKey)
{
string statement = String.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", existingForeignKey.TableSource.QualifiedName,
Identifiers.EscapeIdentifier(existingForeignKey.Name));
return(statement.ToEnumerable());
}
/// <summary>
/// This method generates the SQL to grant permissions on a table source to a user.
/// This implementation returns "GRANT permissions ON FullyQualifiedTableName TO userName"
/// </summary>
/// <param name="existingTableSource">Info about the table or view which we want to grant permissions on.</param>
/// <param name="username">User to grant permissions.</param>
/// <param name="permissions">Permissions to grant to the user.</param>
/// <returns>SQL statements to grant permissions.</returns>
public virtual IEnumerable <string> GrantPermissions(ITableSourceInfo existingTableSource, string username, Permissions permissions)
{
IList <string> permissionList = new List <string>();
if (permissions.HasPermissions(Permissions.Select))
{
permissionList.Add("SELECT");
}
if (permissions.HasPermissions(Permissions.Insert))
{
permissionList.Add("INSERT");
}
if (permissions.HasPermissions(Permissions.Update))
{
permissionList.Add("UPDATE");
}
if (permissions.HasPermissions(Permissions.Delete))
{
permissionList.Add("DELETE");
}
string statement = String.Format("GRANT {0} ON {1} TO {2}", permissionList.StrCat(","), existingTableSource.QualifiedName,
Identifiers.EscapeIdentifier(username));
return(statement.ToEnumerable());
}
public override IEnumerable <string> DropForeignKey(ITableSourceForeignKeyInfo existingForeignKey)
{
string statement = String.Format("ALTER TABLE {0} DROP FOREIGN KEY {1};", Identifiers.EscapeIdentifier(existingForeignKey.TableSource.Name),
Identifiers.EscapeIdentifier(existingForeignKey.Name));
return(statement.ToEnumerable());
}
/// <summary>
/// This method generates the SQL to create a new index.
/// This implementation returns the statement "CREATE UNIQUE INDEX FullyQualifiedIndexName ON FullyQualifiedTableName (escapedColumnNames)"
/// </summary>
/// <param name="newIndex">Info about the index to create.</param>
/// <returns>SQL statements to create the index.</returns>
public virtual IEnumerable <string> CreateIndex(IPlatformTableSourceIndexInfo newIndex)
{
string createStatement = String.Format("CREATE {0}INDEX {1} ON {2} ({3})", newIndex.IsUnique? "UNIQUE " : "",
Identifiers.EscapeAndQualifyIdentifier(newIndex.TableSource.Database, newIndex.Name), newIndex.TableSource.QualifiedName,
newIndex.Columns.Select(col => Identifiers.EscapeIdentifier(col.Name)).StrCat(","));
return(createStatement.ToEnumerable());
}
/// <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");
}
public override IEnumerable <string> CreateIndex(IPlatformTableSourceIndexInfo newIndex)
{
var createStatement = new StringBuilder();
ITableSourceInfo table = newIndex.TableSource;
createStatement.AppendFormat("CREATE {0}INDEX {1} ON dbo.{2} ({3})", newIndex.IsUnique ? "UNIQUE " : "",
Identifiers.EscapeIdentifier(newIndex.Name), Identifiers.EscapeIdentifier(table.Name),
newIndex.Columns.Select(col => Identifiers.EscapeIdentifier(col.Name)).StrCat(","));
yield return(GetSpecialSchemaStatement(table.Database, createStatement.ToString()));
}
private string GetCreateAutoNumberTriggerStatement(ITableSourceColumnInfo column, string triggerName, string sequenceName)
{
string createTrigger = "CREATE OR REPLACE TRIGGER {0} BEFORE INSERT ON {1} FOR EACH ROW" +
" BEGIN" +
" IF :NEW.{2} IS NULL THEN SELECT oshe_globals.setidentity({3}.nextval) INTO :NEW.{2} FROM dual; END IF;" +
" END;";
return(String.Format(createTrigger,
Identifiers.EscapeAndQualifyIdentifier(column.TableSource.Database, triggerName), Identifiers.EscapeIdentifier(column.TableSource.Name),
Identifiers.EscapeIdentifier(column.Name), Identifiers.EscapeIdentifier(sequenceName)));
}
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)));
}
public override IEnumerable <string> CreateOrReplaceView(ITableSourceInfo newView, string viewSQL, bool withCheckOption)
{
var result = new StringBuilder();
var dbInfo = (DatabaseInfo)newView.Database;
result.Append(
String.Format("IF EXISTS (SELECT * FROM {0}.sys.views WHERE object_id = OBJECT_ID(N'{1}')) ", Identifiers.EscapeIdentifier(dbInfo.Catalog), newView.QualifiedName));
string viewName = Identifiers.EscapeIdentifier(newView.Name);
string withCheckOptionSQL = withCheckOption ? " WITH CHECK OPTION" : "";
result.Append(GetSpecialSchemaStatement(dbInfo, String.Format("ALTER VIEW [dbo].{0} AS {1}{2}", viewName, viewSQL, withCheckOptionSQL)));
result.Append(" ELSE ");
result.Append(GetSpecialSchemaStatement(dbInfo, String.Format("CREATE VIEW [dbo].{0} AS {1}{2}", viewName, viewSQL, withCheckOptionSQL)));
yield return(result.ToString());
}
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> CreateIndex(IPlatformTableSourceIndexInfo newIndex)
{
var createStatement = new StringBuilder();
var dbConfig = DatabaseServices.DatabaseConfiguration as RuntimeDatabaseConfiguration;
if (dbConfig == null)
{
yield break;
}
createStatement.AppendFormat("CREATE {0}INDEX {1}", newIndex.IsUnique? "UNIQUE ": String.Empty,
Identifiers.EscapeAndQualifyIdentifier(newIndex.TableSource.Database, newIndex.Name));
var indexFields = new StringBuilder();
if (dbConfig.CI_AI)
{
foreach (var column in newIndex.Columns)
{
if (indexFields.Length > 0)
{
indexFields.Append(", ");
}
var index = newIndex as PlatformTableSourceIndexInfo;
if (index != null && index.IsFunctionIndexColumn(column))
{
indexFields.AppendFormat("NLSSORT({0}, 'NLS_SORT=BINARY_AI')", Identifiers.EscapeIdentifier(column.Name));
}
else
{
indexFields.Append(Identifiers.EscapeIdentifier(column.Name));
}
}
}
else
{
indexFields.Append(newIndex.Columns.Select(col => Identifiers.EscapeIdentifier(col.Name.ToUpperInvariant())).StrCat(","));
}
createStatement.AppendFormat(" ON {0} ({1}) TABLESPACE {2}", newIndex.TableSource.QualifiedName, indexFields,
Identifiers.EscapeIdentifier(dbConfig.TablespaceIndex));
yield return(createStatement.ToString());
}
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> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn,
IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys, ITableSourceInfo eventTable,
ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable)
{
string triggername = Identifiers.EscapeIdentifier(newTrigger.Name);
var sql = new StringBuilder();
sql.AppendFormat("CREATE TRIGGER {0} ON {1} AFTER INSERT, UPDATE AS", triggername, newTrigger.TableSource.QualifiedName);
sql.Append(" BEGIN");
sql.Append(" DECLARE @isupdate BIT;");
sql.Append(" SET @isupdate=1;");
sql.Append(" IF (SELECT COUNT(1) FROM deleted)=0 SET @isupdate=0;");
FillEventTriggerQuery(sql, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable, eventQueueTable, lightEventQueueTable,
"inserted", true, "@isupdate");
sql.Append(";");
sql.Append(" END");
yield return(GetSpecialSchemaStatement(newTrigger.TableSource.Database, sql.ToString()));
}
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 override IEnumerable <string> CreateEventTrigger(IPlatformTableSourceEventTriggerInfo newTrigger, IPlatformTableSourceColumnInfo triggerTablePrimaryKeyColumn,
IEnumerable <IPlatformTableSourceColumnInfo> triggerTableEventColumns, IEnumerable <ITableSourceForeignKeyInfo> triggerTableForeignKeys,
ITableSourceInfo eventTable, ITableSourceInfo eventQueueTable, ITableSourceInfo lightEventQueueTable)
{
var createStatement = new StringBuilder();
ITableSourceInfo table = newTrigger.TableSource;
createStatement.Append("CREATE OR REPLACE TRIGGER " + Identifiers.EscapeAndQualifyIdentifier(table.Database, newTrigger.Name));
createStatement.Append(" AFTER INSERT OR UPDATE ON " + Identifiers.EscapeIdentifier(table.Name));
createStatement.Append(" FOR EACH ROW");
createStatement.Append(" DECLARE isUpdating NUMBER(1,0) := 0;");
createStatement.Append(" BEGIN ");
createStatement.Append(" IF UPDATING THEN");
createStatement.Append(" isUpdating := 1;");
createStatement.Append(" END IF;");
FillEventTriggerQuery(createStatement, triggerTablePrimaryKeyColumn, triggerTableEventColumns, triggerTableForeignKeys, eventTable,
eventQueueTable, lightEventQueueTable, ":new", false, "isUpdating");
createStatement.Append(" END;");
return(createStatement.ToString().ToEnumerable());
}
public override IEnumerable <string> DropIndex(IPlatformTableSourceIndexInfo existingIndex)
{
ITableSourceInfo table = existingIndex.TableSource;
yield return(GetSpecialSchemaStatement(table.Database,
String.Format("DROP INDEX {0}.{1}", Identifiers.EscapeIdentifier(table.Name), Identifiers.EscapeIdentifier(existingIndex.Name))));
}
public override IEnumerable <string> DropIndex(IPlatformTableSourceIndexInfo existingIndex)
{
yield return(String.Format("DROP INDEX {0} ON {1};",
Identifiers.EscapeIdentifier(existingIndex.Name), Identifiers.EscapeIdentifier(existingIndex.TableSource.Name)));
}
/// <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());
}
private IEnumerable <string> SetColumnToAutonumber(IPlatformTableSourceColumnInfo existingColumn)
{
yield return(String.Format("ALTER TABLE {0} MODIFY COLUMN {1} {2} auto_increment;",
Identifiers.EscapeIdentifier(existingColumn.TableSource.Name), Identifiers.EscapeIdentifier(existingColumn.Name), existingColumn.DataType.SqlDataType));
}
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);
}
/// <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 returns the SQL for the primary key table constraint to be used inside the create table and create column statements.
/// This implementation returns "CONSTRAINT escapedConstraintName PRIMARY KEY (escapedColumnNames)"
/// </summary>
/// <param name="constraintName">Name of the primary key constrain</param>
/// <param name="column">The primary key column</param>
/// <returns>SQL for the primary key table constraint.</returns>
protected virtual string GetPrimaryKeyTableConstraint(string constraintName, IPlatformTableSourceColumnInfo column)
{
return(string.Format("CONSTRAINT {0} PRIMARY KEY ({1})", Identifiers.EscapeIdentifier(constraintName),
Identifiers.EscapeIdentifier(column.Name)));
}
protected override string GetColumnDefinition(IPlatformTableSourceColumnInfo column, string defaultValue)
{
return(String.Format("{0} {1}{2}{3}{4}", Identifiers.EscapeIdentifier(column.Name), column.DataType.SqlDataType, column.IsAutoGenerated ? " IDENTITY": String.Empty,
UseDefaultValue(column, defaultValue)? " DEFAULT " + defaultValue : String.Empty,
(GetFinalMandatoryValue(column, defaultValue, column.IsMandatory)? " NOT" : String.Empty) + " NULL"));
}
public override IEnumerable <string> DropEventTrigger(IPlatformTableSourceEventTriggerInfo existingTrigger)
{
yield return(GetSpecialSchemaStatement(existingTrigger.TableSource.Database,
String.Format("DROP TRIGGER {0}", Identifiers.EscapeIdentifier(existingTrigger.Name))));
}
private string LOBStorageStatement(string columnName)
{
return(string.Format(" LOB({0}) STORE AS ( ENABLE STORAGE IN ROW NOCACHE CHUNK 32768)", Identifiers.EscapeIdentifier(columnName)));
}
