def test_create_index_table_col_event(): context = op_fixture() op.create_index('ik_test', 'tbl_with_auto_appended_column', ['foo', 'bar']) context.assert_( "CREATE INDEX ik_test ON tbl_with_auto_appended_column (foo, bar)" )
def test_add_foreign_key_self_referential(): context = op_fixture() op.create_foreign_key("fk_test", "t1", "t1", ["foo"], ["bar"]) context.assert_( "ALTER TABLE t1 ADD CONSTRAINT fk_test " "FOREIGN KEY(foo) REFERENCES t1 (bar)" )
def test_add_column_fk_self_referential(): context = op_fixture() op.add_column('t1', Column('c1', Integer, ForeignKey('t1.c2'), nullable=False)) context.assert_( "ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL", "ALTER TABLE t1 ADD FOREIGN KEY(c1) REFERENCES t1 (c2)" )
def test_rename_column_serv_default(): context = op_fixture('mysql') op.alter_column('t1', 'c1', new_column_name="c2", existing_type=Integer, existing_server_default="q") context.assert_( "ALTER TABLE t1 CHANGE c1 c2 INTEGER NULL DEFAULT 'q'" )
def test_alter_column_schema_type_unnamed(): context = op_fixture('mssql') op.alter_column("t", "c", type_=Boolean()) context.assert_( 'ALTER TABLE t ALTER COLUMN c BIT', 'ALTER TABLE t ADD CHECK (c IN (0, 1))' )
def test_alter_column_set_compiled_default(): context = op_fixture() op.alter_column("t", "c", server_default=func.utc_thing(func.current_timestamp())) context.assert_( "ALTER TABLE t ALTER COLUMN c SET DEFAULT utc_thing(CURRENT_TIMESTAMP)" )
def test_alter_column_schema_type_existing_type(): context = op_fixture('mssql') op.alter_column("t", "c", type_=String(10), existing_type=Boolean(name="xyz")) context.assert_( 'ALTER TABLE t DROP CONSTRAINT xyz', 'ALTER TABLE t ALTER COLUMN c VARCHAR(10)' )
def test_alter_column_schema_schema_type_named(): context = op_fixture('mssql') op.alter_column("t", "c", type_=Boolean(name="xyz"), schema='foo') context.assert_( 'ALTER TABLE foo.t ALTER COLUMN c BIT', 'ALTER TABLE foo.t ADD CONSTRAINT xyz CHECK (c IN (0, 1))' )
def test_alter_column_schema_schema_type_existing_type_no_new_type(): context = op_fixture('postgresql') op.alter_column("t", "c", nullable=False, existing_type=Boolean(), schema='foo') context.assert_( 'ALTER TABLE foo.t ALTER COLUMN c SET NOT NULL' )
def test_alter_column_schema_schema_type_existing_type_no_const(): context = op_fixture('postgresql') op.alter_column("t", "c", type_=String(10), existing_type=Boolean(), schema='foo') context.assert_( 'ALTER TABLE foo.t ALTER COLUMN c TYPE VARCHAR(10)' )
def test_add_column_fk_schema(): context = op_fixture() op.add_column('t1', Column('c1', Integer, ForeignKey('remote.t2.c2'), nullable=False)) context.assert_( 'ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL', 'ALTER TABLE t1 ADD FOREIGN KEY(c1) REFERENCES remote.t2 (c2)' )
def test_col_add_autoincrement(): context = op_fixture('mysql') op.alter_column('t1', 'c1', new_column_name="c2", existing_type=Integer, autoincrement=True) context.assert_( 'ALTER TABLE t1 CHANGE c1 c2 INTEGER NULL AUTO_INCREMENT' )
def test_alter_replace_server_default(self): context = op_fixture('mssql') op.alter_column("t", "c", server_default="5", existing_server_default="6") context.assert_contains("exec('alter table t drop constraint ' + @const_name)") context.assert_contains( "ALTER TABLE t ADD DEFAULT '5' FOR c" )
def test_add_column_fk(): context = op_fixture() op.add_column('t1', Column('c1', Integer, ForeignKey('c2.id'), nullable=False)) context.assert_( "ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL", "ALTER TABLE t1 ADD FOREIGN KEY(c1) REFERENCES c2 (id)" )
def test_alter_column_dont_touch_constraints(self): context = op_fixture('mssql') from sqlalchemy import Boolean op.alter_column('tests', 'col', existing_type=Boolean(), nullable=False) context.assert_('ALTER TABLE tests ALTER COLUMN col BIT NOT NULL')
def test_alter_column_nullable_w_new_type(self): context = op_fixture('oracle') op.alter_column("t", "c", nullable=True, type_=Integer) context.assert_( "ALTER TABLE t MODIFY c NULL", 'ALTER TABLE t MODIFY c INTEGER' )
def test_drop_check(): context = op_fixture('mysql') assert_raises_message( NotImplementedError, "MySQL does not support CHECK constraints.", op.drop_constraint, "f1", "t1", "check" )
def test_col_alter_type_required(): context = op_fixture('mysql') assert_raises_message( util.CommandError, "All MySQL ALTER COLUMN operations require the existing type.", op.alter_column, 't1', 'c1', nullable=False, server_default="q" )
def test_alter_column_schema_not_nullable(): context = op_fixture() op.alter_column("t", "c", nullable=False, schema='foo') context.assert_( # TODO: not sure if this is PG only or standard # SQL "ALTER TABLE foo.t ALTER COLUMN c SET NOT NULL" )
def test_col_remove_autoincrement(): context = op_fixture('mysql') op.alter_column('t1', 'c1', new_column_name="c2", existing_type=Integer, existing_autoincrement=True, autoincrement=False) context.assert_( 'ALTER TABLE t1 CHANGE c1 c2 INTEGER NULL' )
def test_alter_column_nullable(): context = op_fixture() op.alter_column("t", "c", nullable=True) context.assert_( # TODO: not sure if this is PG only or standard # SQL "ALTER TABLE t ALTER COLUMN c DROP NOT NULL" )
def test_drop_generic_constraint(): context = op_fixture('mysql') assert_raises_message( NotImplementedError, "No generic 'DROP CONSTRAINT' in MySQL - please " "specify constraint type", op.drop_constraint, "f1", "t1" )
def test_drop_unknown(): context = op_fixture('mysql') assert_raises_message( TypeError, "'type' can be one of 'check', 'foreignkey', " "'primary', 'unique', None", op.drop_constraint, "f1", "t1", "typo" )
def test_add_unique_constraint_col_event(): context = op_fixture() op.create_unique_constraint('ik_test', 'tbl_with_auto_appended_column', ['foo', 'bar']) context.assert_( "ALTER TABLE tbl_with_auto_appended_column " "ADD CONSTRAINT ik_test UNIQUE (foo, bar)" )
def test_add_column_schema_type_checks_rule(): """Test that a schema type doesn't generate a constraint based on check rule.""" context = op_fixture('postgresql') op.add_column('t1', Column('c1', Boolean, nullable=False)) context.assert_( 'ALTER TABLE t1 ADD COLUMN c1 BOOLEAN NOT NULL', )
def _big_t_table_fixture(dialect, as_sql): context = op_fixture(dialect, as_sql) t1 = Table("ins_table", MetaData(), Column('id', Integer, primary_key=True), Column('v1', String()), Column('v2', String()), ) return context, t1
def test_add_foreign_key(): context = op_fixture() op.create_foreign_key('fk_test', 't1', 't2', ['foo', 'bar'], ['bat', 'hoho']) context.assert_( "ALTER TABLE t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) " "REFERENCES t2 (bat, hoho)" )
def _table_fixture(dialect, as_sql): context = op_fixture(dialect, as_sql) t1 = table("ins_table", column('id', Integer), column('v1', String()), column('v2', String()), ) return context, t1
def test_add_column_schema_schema_type(): """Test that a schema type generates its constraints....""" context = op_fixture() op.add_column('t1', Column('c1', Boolean, nullable=False), schema='foo') context.assert_( 'ALTER TABLE foo.t1 ADD COLUMN c1 BOOLEAN NOT NULL', 'ALTER TABLE foo.t1 ADD CHECK (c1 IN (0, 1))' )
def test_alter_do_everything(self): context = op_fixture('mssql') op.alter_column("t", "c", name="c2", nullable=True, type_=Integer, server_default="5") context.assert_( 'ALTER TABLE t ALTER COLUMN c INTEGER NULL', "ALTER TABLE t ADD DEFAULT '5' FOR c", "EXEC sp_rename 't.c', 'c2', 'COLUMN'" )
def test_add_check_constraint(): context = op_fixture() op.create_check_constraint( "ck_user_name_len", "user_table", func.len(column('name')) > 5 ) context.assert_( "ALTER TABLE user_table ADD CONSTRAINT ck_user_name_len " "CHECK (len(name) > 5)" )
def test_alter_do_everything(self): context = op_fixture('mssql') op.alter_column("t", "c", name="c2", nullable=True, type_=Integer, server_default="5") context.assert_('ALTER TABLE t ALTER COLUMN c INTEGER NULL', "ALTER TABLE t ADD DEFAULT '5' FOR c", "EXEC sp_rename 't.c', 'c2', 'COLUMN'")
def test_create_table_selfref(): context = op_fixture() op.create_table( "some_table", Column('id', Integer, primary_key=True), Column('st_id', Integer, ForeignKey('some_table.id')) ) context.assert_( "CREATE TABLE some_table (" "id INTEGER NOT NULL, " "st_id INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(st_id) REFERENCES some_table (id))" )
def test_create_table_fk_and_schema(): context = op_fixture() op.create_table( "some_table", Column('id', Integer, primary_key=True), Column('foo_id', Integer, ForeignKey('foo.id')), schema='schema' ) context.assert_( "CREATE TABLE schema.some_table (" "id INTEGER NOT NULL, " "foo_id INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(foo_id) REFERENCES foo (id))" )
def test_add_unique_constraint_auto_cols(): context = op_fixture() from sqlalchemy import event, DateTime @event.listens_for(Table, "after_parent_attach") def _table_standard_cols(table, metadata): table.append_column(Column('created_at', DateTime)) try: op.create_unique_constraint('uk_test', 't1', ['foo', 'bar']) context.assert_( "ALTER TABLE t1 ADD CONSTRAINT uk_test UNIQUE (foo, bar)" ) finally: Table.dispatch._clear()
def test_bulk_insert_wrong_cols(): context = op_fixture('postgresql') t1 = table( "ins_table", column('id', Integer), column('v1', String()), column('v2', String()), ) op.bulk_insert(t1, [ { 'v1': 'row v1', }, ]) context.assert_( 'INSERT INTO ins_table (id, v1, v2) VALUES (%(id)s, %(v1)s, %(v2)s)')
def test_create_table_two_fk(): context = op_fixture() op.create_table( "some_table", Column('id', Integer, primary_key=True), Column('foo_id', Integer, ForeignKey('foo.id')), Column('foo_bar', Integer, ForeignKey('foo.bar')), ) context.assert_( "CREATE TABLE some_table (" "id INTEGER NOT NULL, " "foo_id INTEGER, " "foo_bar INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(foo_id) REFERENCES foo (id), " "FOREIGN KEY(foo_bar) REFERENCES foo (bar))" )
def test_inline_literal(): context = op_fixture() from sqlalchemy.sql import table, column from sqlalchemy import String, Integer account = table('account', column('name', String), column('id', Integer) ) op.execute( account.update().\ where(account.c.name==op.inline_literal('account 1')).\ values({'name':op.inline_literal('account 2')}) ) op.execute( account.update().\ where(account.c.id==op.inline_literal(1)).\ values({'id':op.inline_literal(2)}) ) context.assert_( "UPDATE account SET name='account 2' WHERE account.name = 'account 1'", "UPDATE account SET id=2 WHERE account.id = 1" )
def test_drop_column(): context = op_fixture() op.drop_column('t1', 'c1') context.assert_("ALTER TABLE t1 DROP COLUMN c1")
def test_alter_column_not_nullable_w_existing_type(self): context = op_fixture('oracle') op.alter_column("t", "c", nullable=False, existing_type=Integer) context.assert_( "ALTER TABLE t MODIFY c NOT NULL" )
def test_drop_column_w_check(self): context = op_fixture('oracle') op.drop_column('t1', 'c1') context.assert_( "ALTER TABLE t1 DROP COLUMN c1" )
def test_alter_column_rename_mssql(self): context = op_fixture('mssql') op.alter_column("t", "c", name="x") context.assert_("EXEC sp_rename 't.c', 'x', 'COLUMN'")
def test_add_column(self): context = op_fixture('mssql') op.add_column('t1', Column('c1', Integer, nullable=False)) context.assert_("ALTER TABLE t1 ADD c1 INTEGER NOT NULL")
def test_alter_remove_server_default(self): context = op_fixture('mssql') op.alter_column("t", "c", server_default=None) context.assert_contains( "exec('alter table t drop constraint ' + @const_name)")
def test_alter_replace_server_default(self): context = op_fixture('oracle') op.alter_column("t", "c", server_default="5", existing_server_default="6") context.assert_( "ALTER TABLE t MODIFY c DEFAULT '5'" )
def test_add_column_implicit_constraint(): context = op_fixture('sqlite') op.add_column('t1', Column('c1', Boolean)) context.assert_('ALTER TABLE t1 ADD COLUMN c1 BOOLEAN')
def test_drop_index(self): context = op_fixture('mssql') op.drop_index('my_idx', 'my_table') # TODO: annoying that SQLA escapes unconditionally context.assert_contains("DROP INDEX [my_table].my_idx")
def test_drop_index(): context = op_fixture() op.drop_index('ik_test') context.assert_( "DROP INDEX ik_test" )
def test_alter_column_not_nullable_w_new_type(self): context = op_fixture('mssql') op.alter_column("t", "c", nullable=False, type_=Integer) context.assert_("ALTER TABLE t ALTER COLUMN c INTEGER NOT NULL")
def test_alter_remove_server_default(self): context = op_fixture('oracle') op.alter_column("t", "c", server_default=None) context.assert_( "ALTER TABLE t MODIFY c DEFAULT NULL" )
def test_alter_add_server_default(self): context = op_fixture('mssql') op.alter_column("t", "c", server_default="5") context.assert_("ALTER TABLE t ADD DEFAULT '5' FOR c")
def test_alter_column_rename_oracle(self): context = op_fixture('oracle') op.alter_column("t", "c", name="x") context.assert_( "ALTER TABLE t RENAME COLUMN c TO x" )
def test_alter_column_rename(): context = op_fixture() op.alter_column("t", "c", name="x") context.assert_( "ALTER TABLE t RENAME c TO x" )
def test_alter_column_new_type(self): context = op_fixture('oracle') op.alter_column("t", "c", type_=Integer) context.assert_( 'ALTER TABLE t MODIFY c INTEGER' )
def test_add_column_with_default(self): context = op_fixture("mssql") op.add_column( 't1', Column('c1', Integer, nullable=False, server_default="12")) context.assert_("ALTER TABLE t1 ADD c1 INTEGER NOT NULL DEFAULT '12'")
def test_drop_index(self): context = op_fixture('oracle') op.drop_index('my_idx', 'my_table') context.assert_contains("DROP INDEX my_idx")
def test_alter_column_new_type(self): context = op_fixture('mssql') op.alter_column("t", "c", type_=Integer) context.assert_('ALTER TABLE t ALTER COLUMN c INTEGER')
def test_drop_table(): context = op_fixture() op.drop_table('tb_test') context.assert_( "DROP TABLE tb_test" )
def test_alter_column_nullable_w_existing_type(self): context = op_fixture('mssql') op.alter_column("t", "c", nullable=True, existing_type=Integer) context.assert_("ALTER TABLE t ALTER COLUMN c INTEGER NULL")
def test_add_column(): context = op_fixture('sqlite') op.add_column('t1', Column('c1', Integer)) context.assert_('ALTER TABLE t1 ADD COLUMN c1 INTEGER')