/// <summary>
/// This method generates the SQL to drop a trigger previously obtained through the IIntrospectionService API.
/// This implementation returns "DROP TRIGGER FullyQualifiedTriggerName"
/// </summary>
/// <param name="existingTrigger">Info about the trigger to drop.</param>
/// <returns>SQL statements to drop the trigger.</returns>
public virtual IEnumerable <string> DropEventTrigger(IPlatformTableSourceEventTriggerInfo existingTrigger)
{
string statement =
String.Format("DROP TRIGGER {0}", Identifiers.EscapeAndQualifyIdentifier(existingTrigger.TableSource.Database, existingTrigger.Name));
return(statement.ToEnumerable());
}
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);
}
/// <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());
}
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 GetCreateAutoNumberSequenceStatement(IDatabaseInfo database, string sequenceName)
{
return(string.Format("DECLARE v_Seq_Count number; " +
"BEGIN SELECT COUNT(1) INTO v_Seq_Count FROM user_sequences WHERE upper(sequence_name) = upper('{0}'); " +
"IF v_Seq_Count = 0 THEN" +
" EXECUTE IMMEDIATE 'CREATE SEQUENCE {1} MINVALUE 1 NOMAXVALUE START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE'; " +
"END IF; " +
"END;",
sequenceName,
Identifiers.EscapeAndQualifyIdentifier(database, sequenceName)));
}
//dnv: review this hack
public override IEnumerable <string> DropEventTrigger(IPlatformTableSourceEventTriggerInfo existingTrigger)
{
var sql = new StringBuilder();
string triggerName = existingTrigger.Name;
string secondTriggerName = triggerName.ToUpperInvariant().EndsWith("_I") ? triggerName.Substring(0, triggerName.Length - 2) + "_U" :
triggerName.Substring(0, triggerName.Length - 2) + "_I";
List <string> statements = new List <string>();
statements.Add(String.Format("drop trigger if exists {0}", Identifiers.EscapeAndQualifyIdentifier(existingTrigger.TableSource.Database, triggerName)));
statements.Add(String.Format("drop trigger if exists {0}", Identifiers.EscapeAndQualifyIdentifier(existingTrigger.TableSource.Database, secondTriggerName)));
return(statements);
}
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> 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());
}
/// <summary>
/// This method generates the SQL to drop an index previously obtained through the IIntrospectionService API.
/// This implementation returns "DROP INDEX FullyQualifiedIndexName"
/// </summary>
/// <param name="existingIndex">Info about the index to drop.</param>
/// <returns>SQL statements to drop the index.</returns>
public virtual IEnumerable <string> DropIndex(IPlatformTableSourceIndexInfo existingIndex)
{
yield return(String.Format("DROP INDEX {0}",
Identifiers.EscapeAndQualifyIdentifier(existingIndex.TableSource.Database, existingIndex.Name)));
}
private string GetSpecialSchemaStatement(IDatabaseInfo database, string originalStatement)
{
return(String.Format("EXEC {0} N'{1}'", Identifiers.EscapeAndQualifyIdentifier(database, "sp_executesql"),
DatabaseServices.DMLService.EscapeTextValue(originalStatement)));
}
