public override string ExistsTable(string databaseName, TableName tableName)
{
// For the TeradataOdbc provider, we need to call the built-in DATABASE function in order to find out the current database as
// the passed in parameter for the databaseName is empty.
// Unfortunately, the DATABASE function does not work when connected we the native driver.
return string.Format(CultureInfo.InvariantCulture, @"SELECT COUNT(*) FROM DBC.TABLES WHERE DATABASENAME=DATABASE AND TABLENAME='{0}'", tableName.Name);
}
public IEnumerable<string> AddColumn(TableName tableName, Column column)
{
AddMethodName();
AddNewObjectNames(column.Name);
_dataTypes.Add(new UsedDataType(column.DataType, false, false));
return Enumerable.Empty<string>();
}
public IEnumerable<string> AddForeignKey(TableName tableName, TableName referencedTableName, IEnumerable<ColumnReference> columnNames, string constraintName, bool cascadeOnDelete)
{
// Do nothing. SQLite only supports foreign keys under special circumstances (see: http://www.sqlite.org/foreignkeys.html).
// We do not throw a NotSupportedException since not having foreign keys does not change anything about how the database is used.
// Therefore, we do not want to force clients to branch migration logic for SQLite.
return Enumerable.Empty<string>();
}
public IEnumerable<string> AddIndex(TableName tableName, IEnumerable<string> columnNames, string indexName)
{
yield return string.Format(CultureInfo.InvariantCulture, "CREATE INDEX {0} ON {1} ({2})",
Escape(indexName),
Escape(tableName.Name),
string.Join(", ", columnNames.Select(Escape).ToArray()));
}
public IEnumerable<string> AddColumn(TableName tableName, Column column)
{
string commandText = string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE {0} ADD {1}",
Escape(tableName.Name),
GetColumnSpec(column));
yield return commandText;
}
public IEnumerable<string> AddColumn(TableName tableName, Column column)
{
if (column.IsRowVersion)
{
ThrowRowVersionNotSupportedException();
}
yield return string.Format(CultureInfo.InvariantCulture, @"ALTER TABLE ""{0}"" ADD COLUMN {1}", tableName.Name, GetColumnDefinition(column));
}
public IEnumerable<string> AddPrimaryKey(TableName tableName, IEnumerable<string> columnNames, string constraintName)
{
AddMethodName();
AddNewObjectNames(constraintName);
// FEATURE: try to find out the data types of the pk columns add them to the _primaryKeyDataTypes list
// This can only be done if the same migration has added the table, and if the columnNames are tracked, along with their data type.
return Enumerable.Empty<string>();
}
public override IEnumerable<string> RenameColumn(TableName tableName, string oldName, string newName)
{
string schemaName = GetSchemaName(tableName);
string defaultConstraintName = GetDefaultConstraintName(tableName.Name, oldName);
string renameDefaultConstraintName = string.Format(CultureInfo.InvariantCulture, "EXEC dbo.sp_rename @objname = N'{0}.{1}', @newname = N'{2}', @objtype = N'OBJECT'", schemaName, Escape(defaultConstraintName), GetDefaultConstraintName(tableName.Name, newName));
yield return PrefixIfObjectExists(defaultConstraintName, schemaName, renameDefaultConstraintName);
yield return string.Format(CultureInfo.InvariantCulture, "EXEC dbo.sp_rename @objname=N'{0}.{1}', @newname=N'{2}', @objtype=N'COLUMN'", GetTableQualifier(tableName), Escape(oldName), newName);
}
public IEnumerable<string> AddIndex(TableName tableName, IEnumerable<string> columnNames, string indexName)
{
yield return string.Format(CultureInfo.InvariantCulture, "CREATE INDEX {0} ON {1} {2}({2}\t{3}{2}){4}",
Escape(indexName),
GetTableQualifier(tableName),
Environment.NewLine,
string.Join(string.Format(CultureInfo.InvariantCulture, ",{0}\t", Environment.NewLine), columnNames.Select(Escape).ToArray()),
SpecifyWith ? "WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)" : string.Empty);
}
public IEnumerable<string> AddForeignKey(TableName tableName, TableName referencedTableName, IEnumerable<ColumnReference> columnNames, string constraintName, bool cascadeOnDelete)
{
yield return AlterTable(tableName) + string.Format(CultureInfo.InvariantCulture, " ADD CONSTRAINT [{0}] FOREIGN KEY({1}){2}REFERENCES {3} ({4}){5}",
constraintName,
string.Join(", ", columnNames.Select(n => Escape(n.ColumnName)).ToArray()),
Environment.NewLine,
GetTableQualifier(referencedTableName),
string.Join(", ", columnNames.Select(n => Escape(n.ReferencedColumnName)).ToArray()),
cascadeOnDelete ? " ON DELETE CASCADE" : string.Empty);
}
public IEnumerable<string> AddForeignKey(TableName tableName, TableName referencedTableName, IEnumerable<ColumnReference> columnNames, string constraintName, bool cascadeOnDelete)
{
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) {3} REFERENCES {4} ({5}) {6}",
Escape(tableName.Name),
Escape(constraintName),
string.Join(", ", columnNames.Select(n => Escape(n.ColumnName)).ToArray()),
Environment.NewLine,
Escape(referencedTableName.Name),
string.Join(", ", columnNames.Select(n => Escape(n.ReferencedColumnName)).ToArray()),
cascadeOnDelete ? " ON DELETE CASCADE" : string.Empty);
}
public IEnumerable<string> AddColumn(TableName tableName, Column column)
{
if (column.IsRowVersion)
{
ThrowRowVersionNotSupportedException();
}
// assemble ALTER TABLE statements
string commandText = string.Format(CultureInfo.InvariantCulture, @"{0} ADD ", AlterTable(tableName.Name));
commandText += GetColumnString(column);
yield return commandText;
}
public IEnumerable<string> AddColumn(TableName tableName, Column column)
{
// assemble ALTER TABLE statements
string commandText = string.Format(CultureInfo.InvariantCulture, @"{0} ADD ", AlterTable(tableName));
string defaultConstraintClause = GetDefaultConstraintClause(tableName, column.Name, column.DefaultValue, column.DataType);
commandText += string.Format(CultureInfo.InvariantCulture, "{0} {1} {2}NULL{3}",
Escape(column.Name),
GetTypeSpecifier(column.DataType, column.IsRowVersion),
column.IsNullable ? string.Empty : "NOT ",
defaultConstraintClause);
yield return commandText;
}
public IEnumerable<string> AddForeignKey(TableName tableName, TableName referencedTableName, IEnumerable<ColumnReference> columnNames, string constraintName, bool cascadeOnDelete)
{
string sourceCols = String.Empty;
string targetCols = String.Empty;
foreach (ColumnReference cr in columnNames)
{
sourceCols += Escape(cr.ColumnName) + ",";
targetCols += Escape(cr.ReferencedColumnName) + ",";
}
sourceCols = sourceCols.TrimEnd(',');
targetCols = targetCols.TrimEnd(',');
yield return string.Format(CultureInfo.InvariantCulture, "{0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3}({4}){5}", AlterTable(tableName.Name), Escape(constraintName), sourceCols, Escape(referencedTableName.Name), targetCols, cascadeOnDelete ? " ON DELETE CASCADE" : string.Empty);
}
public IEnumerable<string> AlterColumn(TableName tableName, Column column)
{
// remove any existing default value constraints (before possibly adding new ones)
foreach (string text in DropDefaultConstraint(tableName, column.Name, true))
{
yield return text;
}
yield return AlterTable(tableName) + string.Format(CultureInfo.InvariantCulture, " ALTER COLUMN [{0}] {1} {2}NULL",
column.Name,
GetTypeSpecifier(column.DataType, column.IsRowVersion),
column.IsNullable ? string.Empty : "NOT ");
if (column.DefaultValue != null)
{
yield return AlterTable(tableName) + string.Format(CultureInfo.InvariantCulture, " ADD {0} FOR {1}",
GetDefaultConstraintClause(tableName, column.Name, column.DefaultValue, column.DataType),
Escape(column.Name));
}
}
public SqlAggregateMigrationGenerator(Server server, Database database, GeneratorOptions options)
: base(server, database, options)
{
var providerLocator = new ProviderLocator(new ProviderFactory()); // CLEAN: use DI container
ProviderInfo provider = providerLocator.GetExactly(DbPlatform.SqlServer2008);
var versioningTableName = new TableName(options.VersioningTableName, options.VersioningTableSchema);
_history = new History(versioningTableName, provider.Metadata);
IDbConnection connection = server.ConnectionContext.SqlConnectionObject;
connection.Open();
connection.ChangeDatabase(Database.Name); // ATTENTION: possibly has side-effects
try
{
_history.Load(connection, null);
}
finally
{
connection.Close();
}
}
internal Versioning(IRuntimeConfiguration configuration, TableName versioningTableName)
{
_configuration = configuration;
_versioningTableName = versioningTableName;
_versioningTableExists = new Lazy<bool>(() =>
{
int exists;
using (IDbConnection connection = configuration.OpenConnection())
{
IDbCommand command = connection.CreateCommand();
command.CommandTimeout = 0; // do not timeout; the client is responsible for not causing lock-outs
command.CommandText = configuration.ProviderInfo.Provider.ExistsTable(connection.Database, _versioningTableName);
Log.Verbose(LogCategory.Sql, command.CommandText);
exists = Convert.ToInt32(command.ExecuteScalar(), CultureInfo.InvariantCulture);
}
return exists != 0;
});
}
public IEnumerable<string> AddForeignKey(TableName tableName, TableName referencedTableName, IEnumerable<ColumnReference> columnNames, string constraintName, bool cascadeOnDelete)
{
if (cascadeOnDelete)
{
throw new NotSupportedException("Teradata does not have an update action concept.");
}
string sourceCols = String.Empty;
string targetCols = String.Empty;
foreach (ColumnReference cr in columnNames)
{
sourceCols += Escape(cr.ColumnName) + ",";
targetCols += Escape(cr.ReferencedColumnName) + ",";
}
sourceCols = sourceCols.TrimEnd(',');
targetCols = targetCols.TrimEnd(',');
yield return string.Format("{0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES WITH CHECK OPTION {3}({4})", AlterTable(tableName.Name), Escape(constraintName), sourceCols, referencedTableName, targetCols);
}
public string ExistsTable(string databaseName, TableName tableName)
{
return string.Format(CultureInfo.InvariantCulture, @"SELECT COUNT(TABLE_NAME)
FROM information_schema.tables
WHERE table_schema = '{0}'
AND table_name = '{1}';", databaseName, tableName.Name);
}
public BootstrapMigration(TableName versioningTable)
{
_versioningTable = versioningTable;
}
/// <summary>
/// Gets the content of the specified table or null if the table does not exist.
/// </summary>
private DataTable GetTable(TableName table)
{
var dataTable = new DataTable(table.Name) { Locale = CultureInfo.InvariantCulture };
try
{
DbCommandBuilder builder;
using (DbDataAdapter adapter = GetDataAdapter(table.Name, table.Schema, out builder))
{
adapter.Fill(dataTable);
}
}
catch (Exception x)
{
if (!x.IsDbException())
{
throw;
}
dataTable = null;
}
return dataTable;
}
public History(TableName tableName, IProviderMetadata providerMetadata)
{
_tableName = tableName;
_providerMetadata = providerMetadata;
}
private static string Escape(TableName tableName)
{
string schemaPrefix = tableName.Schema != null ? "\"" + tableName.Schema + "\"." : string.Empty;
return schemaPrefix + "\"" + tableName.Name + "\"";
}
public IEnumerable<string> AlterColumn(TableName tableName, Column column)
{
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} MODIFY COLUMN {1}",
Escape(tableName.Name),
GetColumnSpec(column));
}
public IEnumerable<string> AddUniqueConstraint(TableName tableName, IEnumerable<string> columnNames, string constraintName)
{
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} ADD UNIQUE KEY {1} ({2})",
Escape(tableName.Name),
Escape(constraintName),
string.Join(", ", columnNames.Select(Escape).ToArray()));
}
public IEnumerable<string> AddPrimaryKey(TableName tableName, IEnumerable<string> columnNames, string constraintName)
{
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} ADD PRIMARY KEY({1})",
Escape(tableName.Name),
string.Join(", ", columnNames.Select(Escape).ToArray()));
}
public IEnumerable<string> DropUniqueConstraint(TableName tableName, string constraintName)
{
yield return string.Format(CultureInfo.InvariantCulture, "ALTER TABLE {0} DROP KEY {1}",
Escape(tableName.Name),
Escape(constraintName));
}
public IEnumerable<string> RenameColumn(TableName tableName, string oldName, string newName)
{
// MySQL requires the full column definition (including data type, nullability, default value) for column rename
// (see: http://stackoverflow.com/questions/8553130/rename-a-column-in-mysql-table-without-having-to-repeat-its-type-definition).
// The set of commands works but requires "Allow User Variables=True" in the connection string and it loses
// all extra column definitions.
// yield return string.Format(CultureInfo.InvariantCulture, @"SELECT @DB := DATABASE();
//SELECT @CTYPE := COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @DB AND TABLE_NAME = '{3}' AND COLUMN_NAME = '{4}';
//SET @STMT = CONCAT('ALTER TABLE {0} CHANGE {1} {2} ', @CTYPE);
//PREPARE REN_STMT FROM @STMT;
//EXECUTE REN_STMT;
//DEALLOCATE PREPARE REN_STMT;", Escape(tableName), Escape(oldName), Escape(newName), tableName, oldName);
throw new NotSupportedException("Rename column is not supported by the MySQL provider.");
}
public IEnumerable<string> RenameTable(TableName oldName, string newName)
{
yield return string.Format("RENAME TABLE {0} TO {1}", Escape(oldName.Name), Escape(newName));
}
public IEnumerable<string> RenamePrimaryKey(TableName tableName, string oldName, string newName)
{
// http://dev.mysql.com/doc/refman/5.0/en/drop-index.html
throw new NotSupportedException("The primary key name is always PRIMARY.");
}
