public void CanFormatObjectNameWithoutSchema()
{
var table = new SchemaQualifiedObjectName {Name = "Moo"};
string sql = string.Format(formatter, "select * from {0:NAME}", table);
Assert.AreEqual(sql, "select * from <Moo>");
}
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;
}
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 ConstraintExists(SchemaQualifiedObjectName table, string name)
{
string sql = FormatSql(
"SELECT {0:NAME} FROM {1:NAME} " +
"WHERE {0:NAME} = '{2}' AND {3:NAME} = '{4}'",
"CONSTRAINT_NAME", "TABLE_CONSTRAINTS".WithSchema("INFORMATION_SCHEMA"),
name, "TABLE_NAME", table.Name);
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 (onUpdateConstraint != ForeignKeyConstraint.NoAction)
{
throw new NotSupportedException("Oracle не поддерживает действий при обновлении внешнего ключа");
}
if (onDeleteConstraint == ForeignKeyConstraint.SetDefault)
{
throw new NotSupportedException("Oracle не поддерживает SET DEFAULT при удалении записи, на которую ссылается внешний ключ");
}
base.AddForeignKey(name, primaryTable, primaryColumns, refTable, refColumns, onDeleteConstraint, onUpdateConstraint);
}
public override void ChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
{
string defaultConstraintName = GetDefaultConstraintName(table, column);
if (!string.IsNullOrWhiteSpace(defaultConstraintName))
{
RemoveConstraint(table, defaultConstraintName);
}
if (newDefaultValue != null)
{
string sql = GetSqlChangeDefaultValue(table, column, newDefaultValue);
ExecuteNonQuery(sql);
}
}
public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
{
string nspname = table.SchemaIsEmpty ? "current_schema()" : string.Format("'{0}'", table.Schema);
string tablename = NeedQuotesForNames ? table.Name : table.Name.ToLower();
string colname = NeedQuotesForNames ? column : column.ToLower();
string sql = FormatSql(
"SELECT {0:NAME} FROM {1:NAME}.{2:NAME} WHERE {3:NAME} = {4} AND {5:NAME} = '{6}' AND {7:NAME} = '{8}'",
"column_name", "information_schema", "columns", "table_schema",
nspname, "table_name", tablename, "column_name", colname);
using (IDataReader reader = ExecuteReader(sql))
{
return reader.Read();
}
}
public virtual string GetDefaultConstraintName(SchemaQualifiedObjectName table, string column)
{
var sqlBuilder = new StringBuilder();
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}') AND {0:NAME}.{3:NAME} = '{4}'",
"col", "object_id", table, "name", column));
using (var reader = ExecuteReader(sqlBuilder.ToString()))
{
if (reader.Read())
{
return reader.GetString(0);
}
return null;
}
}
public override bool ConstraintExists(SchemaQualifiedObjectName table, string name)
{
string constraintsTableName = table.SchemaIsEmpty ? "USER_CONSTRAINTS" : "ALL_CONSTRAINTS";
string sql = FormatSql(
"SELECT COUNT(*) FROM {0:NAME} WHERE {1:NAME} = '{2}' AND {3:NAME} = '{4}'",
constraintsTableName, "CONSTRAINT_NAME", name, "TABLE_NAME", table.Name);
if (!table.SchemaIsEmpty)
{
sql += FormatSql(" and {0:NAME} = '{1}'", "OWNER", table.Schema);
}
object scalar = ExecuteScalar(sql);
return Convert.ToInt32(scalar) > 0;
}
protected override string GetSqlRemoveTable(SchemaQualifiedObjectName table)
{
return FormatSql("DROP TABLE {0:NAME} CASCADE", table);
}
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 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 GetSqlAddColumn(SchemaQualifiedObjectName table, string columnSql)
{
return FormatSql("ALTER TABLE {0:NAME} ADD ({1})", table, columnSql);
}
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 AddCheckConstraint(string name, SchemaQualifiedObjectName table, string checkSql)
{
throw new NotSupportedException("SqlServerCe doesn't support check constraints");
}
protected override string FindConstraints(SchemaQualifiedObjectName table, string column)
{
return
FormatSql("SELECT {0:NAME} FROM {1:NAME} WHERE {2:NAME}='{3}' AND {4:NAME} = '{5}'",
"CONSTRAINT_NAME", "KEY_COLUMN_USAGE".WithSchema("INFORMATION_SCHEMA"),
"TABLE_NAME", table.Name, "COLUMN_NAME", column);
}
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 void RenameColumn(SchemaQualifiedObjectName tableName, string oldColumnName, string newColumnName)
{
throw new NotSupportedException("SqlServerCe doesn't support column renaming");
}
public override bool TableExists(SchemaQualifiedObjectName table)
{
string nspname = table.SchemaIsEmpty ? "SCHEMA_NAME()" : string.Format("'{0}'", table.Schema);
string sql = FormatSql(
"SELECT * FROM {0:NAME} WHERE {1:NAME}='{2}' AND {3:NAME} = {4}",
"TABLES".WithSchema("INFORMATION_SCHEMA"), "TABLE_NAME", table.Name, "TABLE_SCHEMA", nspname);
using (IDataReader reader = ExecuteReader(sql))
{
return reader.Read();
}
}
public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
{
string indexesTableName = tableName.SchemaIsEmpty ? "USER_INDEXES" : "ALL_INDEXES";
string sql = FormatSql(
"select count(*) from {0:NAME} where {1:NAME} = '{2}' and {3:NAME} = '{4}'",
indexesTableName, "TABLE_NAME", tableName.Name, "INDEX_NAME", indexName);
if (!tableName.SchemaIsEmpty)
{
sql += FormatSql(" and {0:NAME} = '{1}' and {2:NAME} = '{1}'", "TABLE_OWNER", tableName.Schema, "OWNER");
}
int count = Convert.ToInt32(ExecuteScalar(sql));
return count > 0;
}
public override bool TableExists(SchemaQualifiedObjectName table)
{
string schemaName = table.SchemaIsEmpty ? "user" : string.Format("'{0}'", table.Schema);
string sql = FormatSql("SELECT COUNT(*) from {0:NAME} where {1:NAME} = '{2}' and {3:NAME} = {4}",
"ALL_TABLES", "TABLE_NAME", table.Name, "OWNER", schemaName);
object count = ExecuteScalar(sql);
return Convert.ToInt32(count) > 0;
}
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);
}
protected override 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.WithSchema(table.Schema), table, columns);
return sql;
}
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);
}
protected override string GetSqlChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
{
return FormatSql("ALTER TABLE {0:NAME} MODIFY {1:NAME} {2}", table, column, GetSqlDefaultValue(newDefaultValue));
}
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);
}
protected override string GetSqlRemoveIndex(string indexName, SchemaQualifiedObjectName tableName)
{
return FormatSql("DROP INDEX {0:NAME}", indexName.WithSchema(tableName.Schema));
}
public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
{
string sql = FormatSql(
"SELECT COUNT(*) FROM {0:NAME} " +
"WHERE {1:NAME} = '{2}' and {3:NAME} = '{4}'",
"INDEXES".WithSchema("INFORMATION_SCHEMA"),
"TABLE_NAME", tableName.Name,
"INDEX_NAME", indexName);
int count = Convert.ToInt32(ExecuteScalar(sql));
return count > 0;
}
public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
{
string columnsTableName = table.SchemaIsEmpty ? "USER_TAB_COLUMNS" : "ALL_TAB_COLUMNS";
string sql = FormatSql(
"SELECT COUNT(*) from {0:NAME} where {1:NAME} = '{2}' and {3:NAME} = '{4}'",
columnsTableName, "TABLE_NAME", table.Name, "COLUMN_NAME", column);
if (!table.SchemaIsEmpty)
{
sql += FormatSql(" and {0:NAME} = '{1}'", "OWNER", table.Schema);
}
object scalar = ExecuteScalar(sql);
return Convert.ToInt32(scalar) > 0;
}
public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
{
string nspname = tableName.SchemaIsEmpty ? "current_schema()" : string.Format("'{0}'", tableName.Schema);
string tablename = NeedQuotesForNames ? tableName.Name : tableName.Name.ToLower();
string indname = NeedQuotesForNames ? indexName : indexName.ToLower();
var builder = new StringBuilder();
builder.Append("SELECT count(*) FROM pg_class c ");
builder.Append("JOIN pg_index i ON i.indexrelid = c.oid ");
builder.Append("JOIN pg_class c2 ON i.indrelid = c2.oid ");
builder.Append("LEFT JOIN pg_user u ON u.usesysid = c.relowner ");
builder.Append("LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ");
builder.Append("WHERE c.relkind = 'i' ");
builder.AppendFormat("AND n.nspname = {0} ", nspname);
builder.AppendFormat("AND c2.relname = '{0}' ", tablename);
builder.AppendFormat("AND c.relname = '{0}' ", indname);
int count = Convert.ToInt32(ExecuteScalar(builder.ToString()));
return count > 0;
}