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; }