public ParentToChild(
SchemaQualifiedObjectName parent,
SchemaQualifiedObjectName child)
{
Parent = parent;
Child = child;
}
/// <summary>
/// The apply.
/// </summary>
public override void Apply()
{
var tableMedIns = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "MedicalInsurance"
};
var tableInsuredPerson = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "InsuredPerson"
};
if (!Database.ColumnExists(tableMedIns, "StateDateFrom"))
{
var col = new Column("StateDateFrom", new ColumnType(DbType.DateTime), ColumnProperty.NotNull, "Getdate()");
Database.AddColumn(tableMedIns, col);
}
if (!Database.ColumnExists(tableMedIns, "StateDateTo"))
{
var col = new Column("StateDateTo", new ColumnType(DbType.DateTime), ColumnProperty.NotNull, "Getdate()");
Database.AddColumn(tableMedIns, col);
}
if (!Database.ColumnExists(tableMedIns, "InsuredPersonId"))
{
var col = new Column(
"InsuredPersonId",
new ColumnType(DbType.Guid),
ColumnProperty.NotNull,
"'00000000-0000-0000-0000-000000000000'");
Database.AddColumn(tableMedIns, col);
////Database.AddForeignKey("FK_MedicalInsurance_InsuredPerson", tableMedIns,"InsuredPersonId" , tableInsuredPerson, "RowId");
}
}
protected override string GetSqlChangeNotNullConstraint(SchemaQualifiedObjectName table, string column, bool notNull, ref string sqlChangeColumnType)
{
string colsTable = table.SchemaIsEmpty ? "USER_TAB_COLUMNS" : "ALL_TAB_COLUMNS";
string sqlCheckNotNull = FormatSql(
"select {0:NAME} from {1:NAME} where {2:NAME} = '{3}' and {4:NAME} = '{5}'",
"NULLABLE", colsTable, "TABLE_NAME", table.Name, "COLUMN_NAME", column);
if (!table.SchemaIsEmpty)
{
sqlCheckNotNull += FormatSql(" and {0:NAME} = '{1}'", "OWNER", table.Schema);
}
using (var reader = ExecuteReader(sqlCheckNotNull))
{
if (reader.Read())
{
bool columnAlreadyNotNull = reader[0].ToString().Equals("n", StringComparison.CurrentCultureIgnoreCase);
if (notNull == columnAlreadyNotNull)
{
return(null);
}
}
}
return(base.GetSqlChangeNotNullConstraint(table, column, notNull, ref sqlChangeColumnType));
}
/// <summary>
/// The apply.
/// </summary>
public override void Apply()
{
var tableMedIns = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "MedicalInsurance"
};
var tableInsuredPerson = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "InsuredPerson"
};
var tablePeriodIns = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "PeriodInsurance"
};
if (Database.ColumnExists(tableMedIns, "InsuredPersonId") && Database.TableExists(tablePeriodIns))
{
Database.ExecuteNonQuery(@"update MedicalInsurance
set StateDateFrom = t.StateDateFrom,
StateDateTo = t.StateDateTo,
InsuredPersonId = t.InsuredPersonId
from PeriodInsurance t
where MedicalInsurance.RowId = t.MedicalInsuranceId");
Database.AddForeignKey(
"FK_MedicalInsurance_InsuredPerson",
tableMedIns,
"InsuredPersonId",
tableInsuredPerson,
"RowId");
}
}
protected string FindConstraints(SchemaQualifiedObjectName table, string column)
{
var sqlBuilder = new StringBuilder();
sqlBuilder.Append(FormatSql("SELECT {0:NAME} ", "CONSTRAINT_NAME"));
sqlBuilder.Append(FormatSql("FROM {0:NAME} ", "CONSTRAINT_COLUMN_USAGE".WithSchema("INFORMATION_SCHEMA")));
sqlBuilder.Append(FormatSql("WHERE {0:NAME} = '{1}' and {2:NAME} = '{3}' ",
"TABLE_NAME", table.Name, "COLUMN_NAME", column));
if (!table.SchemaIsEmpty)
{
sqlBuilder.Append(FormatSql("AND {0:NAME} = '{1}' ", "TABLE_SCHEMA", table.Schema));
}
sqlBuilder.Append("UNION ALL ");
sqlBuilder.Append(FormatSql("SELECT {0:NAME}.{1:NAME} as {2:NAME} ", "dobj", "name", "CONSTRAINT_NAME"));
sqlBuilder.Append(FormatSql("FROM {0:NAME} {1:NAME} ", "columns".WithSchema("sys"), "col"));
sqlBuilder.Append(FormatSql("INNER JOIN {0:NAME} {1:NAME} ", "objects".WithSchema("sys"), "dobj"));
sqlBuilder.Append(FormatSql("ON {0:NAME}.{1:NAME} = {2:NAME}.{3:NAME} AND {0:NAME}.{4:NAME} = 'D' ",
"dobj", "object_id", "col", "default_object_id", "type"));
sqlBuilder.Append(FormatSql(
"WHERE {0:NAME}.{1:NAME} = object_id(N'{2:NAME}') AND {0:NAME}.{3:NAME} = '{4:NAME}'",
"col", "object_id", table, "name", column));
return(sqlBuilder.ToString());
}
protected override string GetSqlChangeColumnType(SchemaQualifiedObjectName table, string column,
ColumnType columnType)
{
string columnTypeSql = typeMap.Get(columnType);
return(FormatSql("ALTER TABLE {0:NAME} MODIFY {1:NAME} {2}", table, column, columnTypeSql));
}
protected override string GetSqlChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
{
string dfConstraintName = string.Format("DF_{0}", Guid.NewGuid().ToString("N"));
string sqlDefaultValue = GetSqlDefaultValue(newDefaultValue);
return(FormatSql("ALTER TABLE {0:NAME} ADD CONSTRAINT {1:NAME} {2} FOR {3:NAME}", table, dfConstraintName, sqlDefaultValue, column));
}
public virtual void AddColumn(SchemaQualifiedObjectName table, Column column)
{
string sqlColumnDef = GetSqlColumnDef(column, false);
string sqlAddColumn = GetSqlAddColumn(table, sqlColumnDef);
ExecuteNonQuery(sqlAddColumn);
}
/// <summary>
/// The apply.
/// </summary>
public override void Apply()
{
var table = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "SearchKey"
};
if (Database.ConstraintExists(table, "FK_SearchKey_Statement"))
{
Database.RemoveConstraint(table, "FK_SearchKey_Statement");
}
if (Database.ConstraintExists(table, "FK_SearchKey_InsuredPerson"))
{
Database.RemoveConstraint(table, "FK_SearchKey_InsuredPerson");
}
var table1 = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "Contents"
};
if (Database.ConstraintExists(table1, "FK_Contents_InsuredPersonData"))
{
Database.RemoveConstraint(table1, "FK_Contents_InsuredPersonData");
}
}
protected override string GetSqlChangeNotNullConstraint(SchemaQualifiedObjectName table, string column, bool notNull, ref string sqlChangeColumnType)
{
const string SQL_TEMPLATE = "UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = {0} WHERE RDB$FIELD_NAME = '{1}' AND RDB$RELATION_NAME = '{2}';";
string sqlNotNull = notNull ? "1" : "NULL";
return(FormatSql(SQL_TEMPLATE, sqlNotNull, column, table.Name));
}
protected virtual string GetSqlChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
{
string defaultValueSql = newDefaultValue == null
? "DROP DEFAULT"
: "SET " + GetSqlDefaultValue(newDefaultValue);
return(FormatSql("ALTER TABLE {0:NAME} ALTER COLUMN {1:NAME} {2}", table, column, defaultValueSql));
}
protected override string GetSqlRemoveConstraint(SchemaQualifiedObjectName table, string name)
{
string constraintSql = (name ?? string.Empty).ToUpper() == "PRIMARY"
? "PRIMARY KEY"
: FormatSql("KEY {0:NAME}", name);
return(FormatSql("ALTER TABLE {0:NAME} DROP {1}", table, constraintSql));
}
/// <summary>
/// Check if a table already exists
/// </summary>
/// <param name="table">The name of the table that you want to check on.</param>
public override bool TableExists(SchemaQualifiedObjectName table)
{
string sql = FormatSql("SELECT [name] FROM [sqlite_master] WHERE [type]='table' and [name]='{0}'", table.Name);
using (IDataReader reader = ExecuteReader(sql))
{
return(reader.Read());
}
}
public void CanFormatObjectNameWithoutSchema()
{
var table = new SchemaQualifiedObjectName {
Name = "Moo"
};
string sql = string.Format(Formatter, "select * from {0:NAME}", table);
Assert.Equal("select * from <Moo>", sql);
}
/// <summary>
/// The apply.
/// </summary>
public override void Apply()
{
var table = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "MedicalInsurance"
};
var columnType = new ColumnType(DbType.Guid);
Database.ChangeColumn(table, "InsuredPersonId", columnType, false);
}
public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
{
string sql = FormatSql(
"SELECT COUNT(*) FROM {0:NAME} WHERE {1:NAME} = '{2}' AND {3:NAME} = object_id(N'{4:NAME}')",
"indexes".WithSchema("sys"), "name", indexName, "object_id", tableName);
int count = Convert.ToInt32(ExecuteScalar(sql));
return(count > 0);
}
public override void ChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
{
if (newDefaultValue != null)
{
base.ChangeDefaultValue(table, column, null);
}
base.ChangeDefaultValue(table, column, newDefaultValue);
}
public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
{
string sql = FormatSql("SHOW COLUMNS FROM {0:NAME} WHERE Field='{1}'", table, column);
using (IDataReader reader = ExecuteReader(sql))
{
return(reader.Read());
}
}
public override void AddForeignKey(string name, SchemaQualifiedObjectName primaryTable, string[] primaryColumns, SchemaQualifiedObjectName refTable, string[] refColumns, ForeignKeyConstraint onDeleteConstraint = ForeignKeyConstraint.NoAction, ForeignKeyConstraint onUpdateConstraint = ForeignKeyConstraint.NoAction)
{
if (onDeleteConstraint == ForeignKeyConstraint.SetDefault ||
onUpdateConstraint == ForeignKeyConstraint.SetDefault)
{
throw new NotSupportedException("MySQL не поддерживает SET DEFAULT для внешних ключей");
}
base.AddForeignKey(name, primaryTable, primaryColumns, refTable, refColumns, onDeleteConstraint, onUpdateConstraint);
}
public override bool TableExists(SchemaQualifiedObjectName table)
{
string sql = FormatSql(
"select count(*) from rdb$relations " +
"where rdb$system_flag = 0 and rdb$relation_name = '{0}'", table.Name);
int cnt = Convert.ToInt32(ExecuteScalar(sql));
return(cnt > 0);
}
protected override string GetSqlChangeNotNullConstraint(SchemaQualifiedObjectName table, string column, bool notNull, ref string sqlChangeColumnType)
{
// ���� ��������� ���� ������� � �������� NOT NULL ���������� ����� ��������,
// �� �������� �������� sqlChangeColumnType � ���������� NULL
// ����� ���������� ������, �������� ������� NOT NULL
string sqlNotNull = notNull ? "SET NOT NULL" : "DROP NOT NULL";
return(FormatSql("ALTER TABLE {0:NAME} ALTER COLUMN {1:NAME} {2}", table, column, sqlNotNull));
}
protected virtual string GetSqlChangeNotNullConstraint(SchemaQualifiedObjectName table, string column, bool notNull, ref string sqlChangeColumnType)
{
// если изменение типа колонки и признака NOT NULL происходит одним запросом,
// то изменяем параметр sqlChangeColumnType и возвращаем NULL
// иначе возвращаем запрос, меняющий признак NOT NULL
sqlChangeColumnType += notNull ? " NOT NULL" : " NULL";
return(null);
}
protected virtual string GetSqlAddIndex(string name, bool unique, SchemaQualifiedObjectName table, params string[] columns)
{
Require.That(columns.Length > 0, "Not specified columns of the table to create an index");
string uniqueString = unique ? "UNIQUE" : string.Empty;
string sql =
FormatSql("CREATE {0} INDEX {1:NAME} ON {2:NAME} ({3:COLS})", uniqueString, name, table, columns);
return(sql);
}
public virtual int Delete(SchemaQualifiedObjectName table, string whereSql = null)
{
string format = string.IsNullOrWhiteSpace(whereSql)
? "DELETE FROM {0:NAME}"
: "DELETE FROM {0:NAME} WHERE {1}";
string sql = FormatSql(format, table, whereSql);
return(ExecuteNonQuery(sql));
}
public override bool ConstraintExists(SchemaQualifiedObjectName table, string name)
{
string sql = FormatSql(
"select count(*) from rdb$relation_constraints " +
"where rdb$relation_name = '{0}' and rdb$constraint_name = '{1}'", table.Name, name);
int cnt = Convert.ToInt32(ExecuteScalar(sql));
return(cnt > 0);
}
protected override string GetSqlChangeNotNullConstraint(SchemaQualifiedObjectName table, string column, bool notNull, ref string sqlChangeColumnType)
{
// если изменение типа колонки и признака NOT NULL происходит одним запросом,
// то измен¤ем параметр sqlChangeColumnType и возвращаем NULL
// иначе возвращаем запрос, мен¤ющий признак NOT NULL
string sqlNotNull = notNull ? "SET NOT NULL" : "DROP NOT NULL";
return(FormatSql("ALTER TABLE {0:NAME} ALTER COLUMN {1:NAME} {2}", table, column, sqlNotNull));
}
public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
{
string sql = FormatSql(
"select count(*) from rdb$indices " +
"where rdb$relation_name = '{0}' and rdb$index_name = '{1}' " +
"and not (rdb$index_name starting with 'rdb$')", tableName.Name, indexName);
int cnt = Convert.ToInt32(ExecuteScalar(sql));
return(cnt > 0);
}
/// <summary>
/// The apply.
/// </summary>
public override void Apply()
{
var tableKladr = new SchemaQualifiedObjectName {
Schema = "dbo", Name = "Address"
};
if (Database.ConstraintExists(tableKladr, "FK_Address_KLADR_2"))
{
Database.RemoveConstraint(tableKladr, "FK_Address_KLADR_2");
}
}
public void AddForeignKey(string name,
SchemaQualifiedObjectName primaryTable, string primaryColumn,
SchemaQualifiedObjectName refTable, string refColumn,
ForeignKeyConstraint onDeleteConstraint = ForeignKeyConstraint.NoAction,
ForeignKeyConstraint onUpdateConstraint = ForeignKeyConstraint.NoAction)
{
AddForeignKey(name,
primaryTable, new[] { primaryColumn },
refTable, new[] { refColumn },
onDeleteConstraint, onUpdateConstraint);
}
public override bool TableExists(SchemaQualifiedObjectName table)
{
string sql = FormatSql(
"SELECT * FROM {0:NAME} WHERE {1:NAME}='{2}'",
"TABLES".WithSchema("INFORMATION_SCHEMA"), "TABLE_NAME", table.Name);
using (IDataReader reader = ExecuteReader(sql))
{
return(reader.Read());
}
}
public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
{
string sql = FormatSql(
"SELECT * FROM {0:NAME} " +
"WHERE {1:NAME}='{2}' AND {3:NAME}='{4}'",
"COLUMNS".WithSchema("INFORMATION_SCHEMA"), "TABLE_NAME", table.Name,
"COLUMN_NAME", column);
if (!table.SchemaIsEmpty)
{
sql += FormatSql(" AND {0:NAME} = '{1}'", "TABLE_SCHEMA", table.Schema);
}
using (IDataReader reader = ExecuteReader(sql))
{
return reader.Read();
}
}
protected virtual string FindConstraints(SchemaQualifiedObjectName table, string column)
{
var sqlBuilder = new StringBuilder();
sqlBuilder.Append(FormatSql("SELECT {0:NAME} ", "CONSTRAINT_NAME"));
sqlBuilder.Append(FormatSql("FROM {0:NAME} ", "CONSTRAINT_COLUMN_USAGE".WithSchema("INFORMATION_SCHEMA")));
sqlBuilder.Append(FormatSql("WHERE {0:NAME} = '{1}' and {2:NAME} = '{3}' ",
"TABLE_NAME", table.Name, "COLUMN_NAME", column));
if (!table.SchemaIsEmpty)
{
sqlBuilder.Append(FormatSql("AND {0:NAME} = '{1}' ", "TABLE_SCHEMA", table.Schema));
}
sqlBuilder.Append("UNION ALL ");
sqlBuilder.Append(FormatSql("SELECT {0:NAME}.{1:NAME} as {2:NAME} ", "dobj", "name", "CONSTRAINT_NAME"));
sqlBuilder.Append(FormatSql("FROM {0:NAME} {1:NAME} ", "columns".WithSchema("sys"), "col"));
sqlBuilder.Append(FormatSql("INNER JOIN {0:NAME} {1:NAME} ", "objects".WithSchema("sys"), "dobj"));
sqlBuilder.Append(FormatSql("ON {0:NAME}.{1:NAME} = {2:NAME}.{3:NAME} AND {0:NAME}.{4:NAME} = 'D' ",
"dobj", "object_id", "col", "default_object_id", "type"));
sqlBuilder.Append(FormatSql("WHERE {0:NAME}.{1:NAME} = object_id(N'{2:NAME}') AND {0:NAME}.{3:NAME} = '{4:NAME}'",
"col", "object_id", table, "name", column));
return sqlBuilder.ToString();
}
protected override string GetSqlRenameColumn(SchemaQualifiedObjectName tableName, string oldColumnName, string newColumnName)
{
return FormatSql("EXEC sp_rename '{0}.{1}', '{2}', 'COLUMN'", tableName, oldColumnName, newColumnName);
}
protected override string GetSqlAddColumn(SchemaQualifiedObjectName table, string columnSql)
{
return FormatSql("ALTER TABLE {0:NAME} ADD {1}", table, columnSql);
}
public override void AddPrimaryKey(string name, SchemaQualifiedObjectName table, params string[] columns)
{
throw new NotSupportedException("SLQite не поддерживает добавление ограничений после создания колонки");
}
public override void AddForeignKey(
string name,
SchemaQualifiedObjectName primaryTable,
string[] primaryColumns,
SchemaQualifiedObjectName refTable,
string[] refColumns,
ForeignKeyConstraint onDeleteConstraint = ForeignKeyConstraint.NoAction,
ForeignKeyConstraint onUpdateConstraint = ForeignKeyConstraint.NoAction)
{
throw new NotSupportedException("SQLite не поддерживает внешние ключи");
}
public override void ChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
{
throw new NotSupportedException("SLQite не поддерживает изменение колонок");
}
// Deletes all constraints linked to a column.
// Sql Server doesn't seems to do this.
private void DeleteColumnConstraints(SchemaQualifiedObjectName table, string column)
{
string sqlContraints = FindConstraints(table, column);
var constraints = new List<string>();
using (IDataReader reader = ExecuteReader(sqlContraints))
{
while (reader.Read())
{
constraints.Add(reader.GetString(0));
}
}
// Can't share the connection so two phase modif
foreach (string constraint in constraints)
{
RemoveConstraint(table, constraint);
}
}
public override void RemoveColumn(SchemaQualifiedObjectName table, string column)
{
DeleteColumnConstraints(table, column);
base.RemoveColumn(table, column);
}
public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
{
string sql = FormatSql(
"SELECT [name] FROM [sqlite_master] WHERE [type]='index' and [name]='{0}' and [tbl_name] = '{1}'",
indexName, tableName.Name);
using (IDataReader reader = ExecuteReader(sql))
{
return reader.Read();
}
}
/// <summary>
/// Determines if a constraint exists.
/// </summary>
/// <param name="table">Table owning the constraint</param>
/// <param name="name">Constraint name</param>
/// <returns><c>true</c> if the constraint exists.</returns>
public override bool ConstraintExists(SchemaQualifiedObjectName table, string name)
{
throw new NotSupportedException();
}
public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
{
string sql = FormatSql("SELECT {0:NAME} FROM {1:NAME}", column, table.Name);
try
{
using (ExecuteReader(sql))
{
return true;
}
}
catch (Exception)
{
return false;
}
}
protected override string GetSqlRenameTable(SchemaQualifiedObjectName oldName, string newName)
{
return FormatSql("EXEC sp_rename '{0}', '{1}'", oldName, newName);
}
protected override string GetSqlRemoveIndex(string indexName, SchemaQualifiedObjectName tableName)
{
return FormatSql("DROP INDEX {0:NAME}", indexName);
}
public override void AddCheckConstraint(string name, SchemaQualifiedObjectName table, string checkSql)
{
throw new NotSupportedException("SQLite не поддерживает создание CHECK CONSTRAINTS после создания колонки");
}
public override void RemoveColumn(SchemaQualifiedObjectName table, string column)
{
throw new NotSupportedException("SQLite не поддерживает удаление колонок");
}
public override void ChangeColumn(SchemaQualifiedObjectName table, string column, ColumnType columnType, bool notNull)
{
throw new NotSupportedException("SLQite не поддерживает изменение колонок");
}
public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
{
string sql = FormatSql(
"SELECT COUNT(*) FROM {0:NAME} WHERE {1:NAME} = '{2}' AND {3:NAME} = object_id(N'{4:NAME}')",
"indexes".WithSchema("sys"), "name", indexName, "object_id", tableName);
int count = Convert.ToInt32(ExecuteScalar(sql));
return count > 0;
}
public override void RenameColumn(SchemaQualifiedObjectName tableName, string oldColumnName, string newColumnName)
{
throw new NotSupportedException("SLQite не поддерживает переименование колонок");
}
/// <summary>
/// Check if a table already exists
/// </summary>
/// <param name="table">The name of the table that you want to check on.</param>
public override bool TableExists(SchemaQualifiedObjectName table)
{
string sql = FormatSql("SELECT [name] FROM [sqlite_master] WHERE [type]='table' and [name]='{0}'", table.Name);
using (IDataReader reader = ExecuteReader(sql))
{
return reader.Read();
}
}
public override bool ConstraintExists(SchemaQualifiedObjectName table, string name)
{
SchemaQualifiedObjectName fullConstraintName = name.WithSchema(table.Schema);
string sql = FormatSql(
"SELECT TOP 1 {0:NAME} FROM {1:NAME} " +
"WHERE {2:NAME} = object_id('{3:NAME}') " +
"AND {4:NAME} = object_id('{5:NAME}') " +
"AND {6:NAME} IN ('D', 'F', 'PK', 'UQ')" +
"UNION ALL " +
"SELECT TOP 1 {0:NAME} FROM {7:NAME} " +
"WHERE {2:NAME} = OBJECT_ID(N'{3:NAME}') AND " +
"{4:NAME} = OBJECT_ID(N'{5:NAME}')",
"name", "objects".WithSchema("sys"), "parent_object_id", table,
"object_id", fullConstraintName, "type", "check_constraints".WithSchema("sys"));
using (IDataReader reader = ExecuteReader(sql))
{
return reader.Read();
}
}
