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')
