def test_add_column_computed(self):
context = op_fixture("mssql")
op.add_column(
"t1",
Column("some_column", Integer, sqla_compat.Computed("foo * 5")),
)
context.assert_("ALTER TABLE t1 ADD some_column AS (foo * 5)")
def test_add_column_computed(self):
context = op_fixture("postgresql")
op.add_column(
"t1",
Column("some_column", Integer, sqla_compat.Computed("foo * 5")),
)
context.assert_("ALTER TABLE t1 ADD COLUMN some_column "
"INTEGER GENERATED ALWAYS AS (foo * 5) STORED")
def test_alter_column_computed_add_not_supported(self):
op_fixture()
assert_raises_message(
exc.CompileError,
'Adding or removing a "computed" construct, e.g. '
"GENERATED ALWAYS AS, to or from an existing column is not "
"supported.",
op.alter_column,
"t1",
"c1",
server_default=sqla_compat.Computed("foo * 5"),
)
class PostgresqlOpTest(TestBase):
def test_rename_table_postgresql(self):
context = op_fixture("postgresql")
op.rename_table("t1", "t2")
context.assert_("ALTER TABLE t1 RENAME TO t2")
def test_rename_table_schema_postgresql(self):
context = op_fixture("postgresql")
op.rename_table("t1", "t2", schema="foo")
context.assert_("ALTER TABLE foo.t1 RENAME TO t2")
def test_create_index_postgresql_expressions(self):
context = op_fixture("postgresql")
op.create_index(
"geocoded",
"locations",
[text("lower(coordinates)")],
postgresql_where=text("locations.coordinates != Null"),
)
context.assert_(
"CREATE INDEX geocoded ON locations (lower(coordinates)) "
"WHERE locations.coordinates != Null"
)
def test_create_index_postgresql_where(self):
context = op_fixture("postgresql")
op.create_index(
"geocoded",
"locations",
["coordinates"],
postgresql_where=text("locations.coordinates != Null"),
)
context.assert_(
"CREATE INDEX geocoded ON locations (coordinates) "
"WHERE locations.coordinates != Null"
)
def test_create_index_postgresql_concurrently(self):
context = op_fixture("postgresql")
op.create_index(
"geocoded",
"locations",
["coordinates"],
postgresql_concurrently=True,
)
context.assert_(
"CREATE INDEX CONCURRENTLY geocoded ON locations (coordinates)"
)
def test_drop_index_postgresql_concurrently(self):
context = op_fixture("postgresql")
op.drop_index("geocoded", "locations", postgresql_concurrently=True)
context.assert_("DROP INDEX CONCURRENTLY geocoded")
def test_alter_column_type_using(self):
context = op_fixture("postgresql")
op.alter_column("t", "c", type_=Integer, postgresql_using="c::integer")
context.assert_(
"ALTER TABLE t ALTER COLUMN c TYPE INTEGER USING c::integer"
)
def test_col_w_pk_is_serial(self):
context = op_fixture("postgresql")
op.add_column("some_table", Column("q", Integer, primary_key=True))
context.assert_("ALTER TABLE some_table ADD COLUMN q SERIAL NOT NULL")
def test_create_exclude_constraint(self):
context = op_fixture("postgresql")
op.create_exclude_constraint(
"ex1", "t1", ("x", ">"), where="x > 5", using="gist"
)
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT ex1 EXCLUDE USING gist (x WITH >) "
"WHERE (x > 5)"
)
def test_create_exclude_constraint_quoted_literal(self):
context = op_fixture("postgresql")
op.create_exclude_constraint(
"ex1",
"SomeTable",
(column("SomeColumn"), ">"),
where='"SomeColumn" > 5',
using="gist",
)
context.assert_(
'ALTER TABLE "SomeTable" ADD CONSTRAINT ex1 EXCLUDE USING gist '
'("SomeColumn" WITH >) WHERE ("SomeColumn" > 5)'
)
def test_create_exclude_constraint_quoted_column(self):
context = op_fixture("postgresql")
op.create_exclude_constraint(
"ex1",
"SomeTable",
(column("SomeColumn"), ">"),
where=column("SomeColumn") > 5,
using="gist",
)
context.assert_(
'ALTER TABLE "SomeTable" ADD CONSTRAINT ex1 EXCLUDE '
'USING gist ("SomeColumn" WITH >) WHERE ("SomeColumn" > 5)'
)
def test_add_column_with_comment(self):
context = op_fixture("postgresql")
op.add_column("t", Column("q", Integer, comment="This is a comment"))
context.assert_(
"ALTER TABLE t ADD COLUMN q INTEGER",
"COMMENT ON COLUMN t.q IS 'This is a comment'",
)
def test_alter_column_with_comment(self):
context = op_fixture("postgresql")
op.alter_column(
"t",
"c",
nullable=False,
existing_type=Boolean(),
schema="foo",
comment="This is a column comment",
)
context.assert_(
"ALTER TABLE foo.t ALTER COLUMN c SET NOT NULL",
"COMMENT ON COLUMN foo.t.c IS 'This is a column comment'",
)
def test_alter_column_add_comment(self):
context = op_fixture("postgresql")
op.alter_column(
"t",
"c",
existing_type=Boolean(),
schema="foo",
comment="This is a column comment",
)
context.assert_(
"COMMENT ON COLUMN foo.t.c IS 'This is a column comment'"
)
def test_alter_column_add_comment_table_and_column_quoting(self):
context = op_fixture("postgresql")
op.alter_column(
"T",
"C",
existing_type=Boolean(),
schema="foo",
comment="This is a column comment",
)
context.assert_(
'COMMENT ON COLUMN foo."T"."C" IS \'This is a column comment\''
)
def test_alter_column_add_comment_quoting(self):
context = op_fixture("postgresql")
op.alter_column(
"t",
"c",
existing_type=Boolean(),
schema="foo",
comment="This is a column 'comment'",
)
context.assert_(
"COMMENT ON COLUMN foo.t.c IS 'This is a column ''comment'''"
)
def test_alter_column_drop_comment(self):
context = op_fixture("postgresql")
op.alter_column(
"t",
"c",
existing_type=Boolean(),
schema="foo",
comment=None,
existing_comment="This is a column comment",
)
context.assert_("COMMENT ON COLUMN foo.t.c IS NULL")
def test_create_table_with_comment(self):
context = op_fixture("postgresql")
op.create_table(
"t2",
Column("c1", Integer, primary_key=True),
Column("c2", Integer),
comment="t2 comment",
)
context.assert_(
"CREATE TABLE t2 (c1 SERIAL NOT NULL, "
"c2 INTEGER, PRIMARY KEY (c1))",
"COMMENT ON TABLE t2 IS 't2 comment'",
)
def test_create_table_with_column_comments(self):
context = op_fixture("postgresql")
op.create_table(
"t2",
Column("c1", Integer, primary_key=True, comment="c1 comment"),
Column("c2", Integer, comment="c2 comment"),
comment="t2 comment",
)
context.assert_(
"CREATE TABLE t2 (c1 SERIAL NOT NULL, "
"c2 INTEGER, PRIMARY KEY (c1))",
"COMMENT ON TABLE t2 IS 't2 comment'",
"COMMENT ON COLUMN t2.c1 IS 'c1 comment'",
"COMMENT ON COLUMN t2.c2 IS 'c2 comment'",
)
def test_create_table_comment(self):
# this is handled by SQLAlchemy's compilers
context = op_fixture("postgresql")
op.create_table_comment("t2", comment="t2 table", schema="foo")
context.assert_("COMMENT ON TABLE foo.t2 IS 't2 table'")
def test_drop_table_comment(self):
# this is handled by SQLAlchemy's compilers
context = op_fixture("postgresql")
op.drop_table_comment("t2", existing_comment="t2 table", schema="foo")
context.assert_("COMMENT ON TABLE foo.t2 IS NULL")
@config.requirements.computed_columns
def test_add_column_computed(self):
context = op_fixture("postgresql")
op.add_column(
"t1",
Column("some_column", Integer, sqla_compat.Computed("foo * 5")),
)
context.assert_(
"ALTER TABLE t1 ADD COLUMN some_column "
"INTEGER GENERATED ALWAYS AS (foo * 5) STORED"
)
@combinations(
(lambda: sqla_compat.Computed("foo * 5"), lambda: None),
(lambda: None, lambda: sqla_compat.Computed("foo * 5")),
(
lambda: sqla_compat.Computed("foo * 42"),
lambda: sqla_compat.Computed("foo * 5"),
),
)
@config.requirements.computed_columns
def test_alter_column_computed_not_supported(self, sd, esd):
op_fixture("postgresql")
assert_raises_message(
exc.CompileError,
'Adding or removing a "computed" construct, e.g. '
"GENERATED ALWAYS AS, to or from an existing column is not "
"supported.",
op.alter_column,
"t1",
"c1",
server_default=sd(),
existing_server_default=esd(),
)
@config.requirements.identity_columns
@combinations(
({}, None),
(dict(always=True), None),
(
dict(start=3, increment=33, maxvalue=99, cycle=True),
"INCREMENT BY 33 START WITH 3 MAXVALUE 99 CYCLE",
),
)
def test_add_column_identity(self, kw, text):
context = op_fixture("postgresql")
op.add_column(
"t1",
Column("some_column", Integer, sqla_compat.Identity(**kw)),
)
qualification = "ALWAYS" if kw.get("always", False) else "BY DEFAULT"
options = " (%s)" % text if text else ""
context.assert_(
"ALTER TABLE t1 ADD COLUMN some_column "
"INTEGER GENERATED %s AS IDENTITY%s" % (qualification, options)
)
@config.requirements.identity_columns
@combinations(
({}, None),
(dict(always=True), None),
(
dict(start=3, increment=33, maxvalue=99, cycle=True),
"INCREMENT BY 33 START WITH 3 MAXVALUE 99 CYCLE",
),
)
def test_add_identity_to_column(self, kw, text):
context = op_fixture("postgresql")
op.alter_column(
"t1",
"some_column",
server_default=sqla_compat.Identity(**kw),
existing_server_default=None,
)
qualification = "ALWAYS" if kw.get("always", False) else "BY DEFAULT"
options = " (%s)" % text if text else ""
context.assert_(
"ALTER TABLE t1 ALTER COLUMN some_column ADD "
"GENERATED %s AS IDENTITY%s" % (qualification, options)
)
@config.requirements.identity_columns
def test_remove_identity_from_column(self):
context = op_fixture("postgresql")
op.alter_column(
"t1",
"some_column",
server_default=None,
existing_server_default=sqla_compat.Identity(),
)
context.assert_(
"ALTER TABLE t1 ALTER COLUMN some_column DROP IDENTITY"
)
@config.requirements.identity_columns
@combinations(
({}, dict(always=True), "SET GENERATED ALWAYS"),
(
dict(always=True),
dict(always=False, start=3),
"SET GENERATED BY DEFAULT SET START WITH 3",
),
(
dict(always=True, start=3, increment=2, minvalue=-3, maxvalue=99),
dict(
always=True,
start=3,
increment=1,
minvalue=-3,
maxvalue=99,
cycle=True,
),
"SET CYCLE SET INCREMENT BY 1",
),
(
dict(
always=False,
start=3,
maxvalue=9999,
minvalue=0,
),
dict(always=False, start=3, order=True, on_null=False, cache=2),
"SET CACHE 2",
),
(
dict(always=False),
dict(always=None, minvalue=0),
"SET MINVALUE 0",
),
)
def test_change_identity_in_column(self, existing, updated, text):
context = op_fixture("postgresql")
op.alter_column(
"t1",
"some_column",
server_default=sqla_compat.Identity(**updated),
existing_server_default=sqla_compat.Identity(**existing),
)
context.assert_("ALTER TABLE t1 ALTER COLUMN some_column %s" % text)
class OpTest(TestBase):
def test_rename_table(self):
context = op_fixture()
op.rename_table("t1", "t2")
context.assert_("ALTER TABLE t1 RENAME TO t2")
def test_rename_table_schema(self):
context = op_fixture()
op.rename_table("t1", "t2", schema="foo")
context.assert_("ALTER TABLE foo.t1 RENAME TO foo.t2")
def test_create_index_no_expr_allowed(self):
op_fixture()
assert_raises_message(
ValueError,
r"String or text\(\) construct expected",
op.create_index,
"name",
"tname",
[func.foo(column("x"))],
)
def test_add_column_schema_hard_quoting(self):
context = op_fixture("postgresql")
op.add_column(
"somename",
Column("colname", String),
schema=quoted_name("some.schema", quote=True),
)
context.assert_(
'ALTER TABLE "some.schema".somename ADD COLUMN colname VARCHAR')
def test_rename_table_schema_hard_quoting(self):
context = op_fixture("postgresql")
op.rename_table("t1",
"t2",
schema=quoted_name("some.schema", quote=True))
context.assert_('ALTER TABLE "some.schema".t1 RENAME TO t2')
def test_add_constraint_schema_hard_quoting(self):
context = op_fixture("postgresql")
op.create_check_constraint(
"ck_user_name_len",
"user_table",
func.len(column("name")) > 5,
schema=quoted_name("some.schema", quote=True),
)
context.assert_('ALTER TABLE "some.schema".user_table ADD '
"CONSTRAINT ck_user_name_len CHECK (len(name) > 5)")
def test_create_index_quoting(self):
context = op_fixture("postgresql")
op.create_index("geocoded", "locations", ["IShouldBeQuoted"])
context.assert_(
'CREATE INDEX geocoded ON locations ("IShouldBeQuoted")')
def test_create_index_expressions(self):
context = op_fixture()
op.create_index("geocoded", "locations", [text("lower(coordinates)")])
context.assert_(
"CREATE INDEX geocoded ON locations (lower(coordinates))")
def test_add_column(self):
context = op_fixture()
op.add_column("t1", Column("c1", Integer, nullable=False))
context.assert_("ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL")
def test_add_column_already_attached(self):
context = op_fixture()
c1 = Column("c1", Integer, nullable=False)
Table("t", MetaData(), c1)
op.add_column("t1", c1)
context.assert_("ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL")
def test_add_column_w_check(self):
context = op_fixture()
op.add_column(
"t1",
Column("c1", Integer, CheckConstraint("c1 > 5"), nullable=False),
)
context.assert_(
"ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL CHECK (c1 > 5)")
def test_add_column_schema(self):
context = op_fixture()
op.add_column("t1",
Column("c1", Integer, nullable=False),
schema="foo")
context.assert_("ALTER TABLE foo.t1 ADD COLUMN c1 INTEGER NOT NULL")
def test_add_column_with_default(self):
context = op_fixture()
op.add_column(
"t1", Column("c1", Integer, nullable=False, server_default="12"))
context.assert_(
"ALTER TABLE t1 ADD COLUMN c1 INTEGER DEFAULT '12' NOT NULL")
def test_add_column_with_index(self):
context = op_fixture()
op.add_column("t1", Column("c1", Integer, nullable=False, index=True))
context.assert_(
"ALTER TABLE t1 ADD COLUMN c1 INTEGER NOT NULL",
"CREATE INDEX ix_t1_c1 ON t1 (c1)",
)
def test_add_column_schema_with_default(self):
context = op_fixture()
op.add_column(
"t1",
Column("c1", Integer, nullable=False, server_default="12"),
schema="foo",
)
context.assert_(
"ALTER TABLE foo.t1 ADD COLUMN c1 INTEGER DEFAULT '12' NOT NULL")
def test_add_column_fk(self):
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_add_column_schema_fk(self):
context = op_fixture()
op.add_column(
"t1",
Column("c1", Integer, ForeignKey("c2.id"), nullable=False),
schema="foo",
)
context.assert_(
"ALTER TABLE foo.t1 ADD COLUMN c1 INTEGER NOT NULL",
"ALTER TABLE foo.t1 ADD FOREIGN KEY(c1) REFERENCES c2 (id)",
)
def test_add_column_schema_type(self):
"""Test that a schema type generates its constraints...."""
context = op_fixture()
op.add_column(
"t1", Column("c1", Boolean(create_constraint=True),
nullable=False))
context.assert_(
"ALTER TABLE t1 ADD COLUMN c1 BOOLEAN NOT NULL",
"ALTER TABLE t1 ADD CHECK (c1 IN (0, 1))",
)
def test_add_column_schema_schema_type(self):
"""Test that a schema type generates its constraints...."""
context = op_fixture()
op.add_column(
"t1",
Column("c1", Boolean(create_constraint=True), 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_add_column_schema_type_checks_rule(self):
"""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(create_constraint=True),
nullable=False))
context.assert_("ALTER TABLE t1 ADD COLUMN c1 BOOLEAN NOT NULL")
def test_add_column_fk_self_referential(self):
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_add_column_schema_fk_self_referential(self):
context = op_fixture()
op.add_column(
"t1",
Column("c1", Integer, ForeignKey("foo.t1.c2"), nullable=False),
schema="foo",
)
context.assert_(
"ALTER TABLE foo.t1 ADD COLUMN c1 INTEGER NOT NULL",
"ALTER TABLE foo.t1 ADD FOREIGN KEY(c1) REFERENCES foo.t1 (c2)",
)
def test_add_column_fk_schema(self):
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_add_column_schema_fk_schema(self):
context = op_fixture()
op.add_column(
"t1",
Column("c1", Integer, ForeignKey("remote.t2.c2"), nullable=False),
schema="foo",
)
context.assert_(
"ALTER TABLE foo.t1 ADD COLUMN c1 INTEGER NOT NULL",
"ALTER TABLE foo.t1 ADD FOREIGN KEY(c1) REFERENCES remote.t2 (c2)",
)
def test_drop_column(self):
context = op_fixture()
op.drop_column("t1", "c1")
context.assert_("ALTER TABLE t1 DROP COLUMN c1")
def test_drop_column_schema(self):
context = op_fixture()
op.drop_column("t1", "c1", schema="foo")
context.assert_("ALTER TABLE foo.t1 DROP COLUMN c1")
def test_alter_column_nullable(self):
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_alter_column_schema_nullable(self):
context = op_fixture()
op.alter_column("t", "c", nullable=True, schema="foo")
context.assert_(
# TODO: not sure if this is PG only or standard
# SQL
"ALTER TABLE foo.t ALTER COLUMN c DROP NOT NULL")
def test_alter_column_not_nullable(self):
context = op_fixture()
op.alter_column("t", "c", nullable=False)
context.assert_(
# TODO: not sure if this is PG only or standard
# SQL
"ALTER TABLE t ALTER COLUMN c SET NOT NULL")
def test_alter_column_schema_not_nullable(self):
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_alter_column_rename(self):
context = op_fixture()
op.alter_column("t", "c", new_column_name="x")
context.assert_("ALTER TABLE t RENAME c TO x")
def test_alter_column_schema_rename(self):
context = op_fixture()
op.alter_column("t", "c", new_column_name="x", schema="foo")
context.assert_("ALTER TABLE foo.t RENAME c TO x")
def test_alter_column_type(self):
context = op_fixture()
op.alter_column("t", "c", type_=String(50))
context.assert_("ALTER TABLE t ALTER COLUMN c TYPE VARCHAR(50)")
def test_alter_column_schema_type(self):
context = op_fixture()
op.alter_column("t", "c", type_=String(50), schema="foo")
context.assert_("ALTER TABLE foo.t ALTER COLUMN c TYPE VARCHAR(50)")
def test_alter_column_set_default(self):
context = op_fixture()
op.alter_column("t", "c", server_default="q")
context.assert_("ALTER TABLE t ALTER COLUMN c SET DEFAULT 'q'")
def test_alter_column_schema_set_default(self):
context = op_fixture()
op.alter_column("t", "c", server_default="q", schema="foo")
context.assert_("ALTER TABLE foo.t ALTER COLUMN c SET DEFAULT 'q'")
def test_alter_column_set_compiled_default(self):
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_set_compiled_default(self):
context = op_fixture()
op.alter_column(
"t",
"c",
server_default=func.utc_thing(func.current_timestamp()),
schema="foo",
)
context.assert_("ALTER TABLE foo.t ALTER COLUMN c "
"SET DEFAULT utc_thing(CURRENT_TIMESTAMP)")
def test_alter_column_drop_default(self):
context = op_fixture()
op.alter_column("t", "c", server_default=None)
context.assert_("ALTER TABLE t ALTER COLUMN c DROP DEFAULT")
def test_alter_column_schema_drop_default(self):
context = op_fixture()
op.alter_column("t", "c", server_default=None, schema="foo")
context.assert_("ALTER TABLE foo.t ALTER COLUMN c DROP DEFAULT")
@combinations(
(lambda: sqla_compat.Computed("foo * 5"), lambda: None),
(lambda: None, lambda: sqla_compat.Computed("foo * 5")),
(
lambda: sqla_compat.Computed("foo * 42"),
lambda: sqla_compat.Computed("foo * 5"),
),
)
@config.requirements.computed_columns_api
def test_alter_column_computed_not_supported(self, sd, esd):
op_fixture()
assert_raises_message(
exc.CompileError,
'Adding or removing a "computed" construct, e.g. '
"GENERATED ALWAYS AS, to or from an existing column is not "
"supported.",
op.alter_column,
"t1",
"c1",
server_default=sd(),
existing_server_default=esd(),
)
@combinations(
(lambda: sqla_compat.Identity(), lambda: None),
(lambda: None, lambda: sqla_compat.Identity()),
(
lambda: sqla_compat.Identity(),
lambda: sqla_compat.Identity(),
),
)
@config.requirements.identity_columns_api
def test_alter_column_identity_not_supported(self, sd, esd):
op_fixture()
assert_raises_message(
exc.CompileError,
'Adding, removing or modifying an "identity" construct, '
"e.g. GENERATED AS IDENTITY, to or from an existing "
"column is not supported in this dialect.",
op.alter_column,
"t1",
"c1",
server_default=sd(),
existing_server_default=esd(),
)
def test_alter_column_schema_type_unnamed(self):
context = op_fixture("mssql", native_boolean=False)
op.alter_column("t", "c", type_=Boolean(create_constraint=True))
context.assert_(
"ALTER TABLE t ALTER COLUMN c BIT",
"ALTER TABLE t ADD CHECK (c IN (0, 1))",
)
def test_alter_column_schema_schema_type_unnamed(self):
context = op_fixture("mssql", native_boolean=False)
op.alter_column("t",
"c",
type_=Boolean(create_constraint=True),
schema="foo")
context.assert_(
"ALTER TABLE foo.t ALTER COLUMN c BIT",
"ALTER TABLE foo.t ADD CHECK (c IN (0, 1))",
)
def test_alter_column_schema_type_named(self):
context = op_fixture("mssql", native_boolean=False)
op.alter_column("t",
"c",
type_=Boolean(name="xyz", create_constraint=True))
context.assert_(
"ALTER TABLE t ALTER COLUMN c BIT",
"ALTER TABLE t ADD CONSTRAINT xyz CHECK (c IN (0, 1))",
)
def test_alter_column_schema_schema_type_named(self):
context = op_fixture("mssql", native_boolean=False)
op.alter_column(
"t",
"c",
type_=Boolean(name="xyz", create_constraint=True),
schema="foo",
)
context.assert_(
"ALTER TABLE foo.t ALTER COLUMN c BIT",
"ALTER TABLE foo.t ADD CONSTRAINT xyz CHECK (c IN (0, 1))",
)
@combinations((True, ), (False, ), argnames="pass_existing_type")
@combinations((True, ), (False, ), argnames="change_nullability")
def test_generic_alter_column_type_and_nullability(self,
pass_existing_type,
change_nullability):
# this test is also on the mssql dialect in test_mssql
context = op_fixture()
args = dict(type_=Integer)
if pass_existing_type:
args["existing_type"] = String(15)
if change_nullability:
args["nullable"] = False
op.alter_column("t", "c", **args)
if change_nullability:
context.assert_(
"ALTER TABLE t ALTER COLUMN c SET NOT NULL",
"ALTER TABLE t ALTER COLUMN c TYPE INTEGER",
)
else:
context.assert_("ALTER TABLE t ALTER COLUMN c TYPE INTEGER")
def test_alter_column_schema_type_existing_type(self):
context = op_fixture("mssql", native_boolean=False)
op.alter_column(
"t",
"c",
type_=String(10),
existing_type=Boolean(name="xyz", create_constraint=True),
)
context.assert_(
"ALTER TABLE t DROP CONSTRAINT xyz",
"ALTER TABLE t ALTER COLUMN c VARCHAR(10)",
)
def test_alter_column_schema_schema_type_existing_type(self):
context = op_fixture("mssql", native_boolean=False)
op.alter_column(
"t",
"c",
type_=String(10),
existing_type=Boolean(name="xyz", create_constraint=True),
schema="foo",
)
context.assert_(
"ALTER TABLE foo.t DROP CONSTRAINT xyz",
"ALTER TABLE foo.t ALTER COLUMN c VARCHAR(10)",
)
def test_alter_column_schema_type_existing_type_no_const(self):
context = op_fixture("postgresql")
op.alter_column("t", "c", type_=String(10), existing_type=Boolean())
context.assert_("ALTER TABLE t ALTER COLUMN c TYPE VARCHAR(10)")
def test_alter_column_schema_schema_type_existing_type_no_const(self):
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_alter_column_schema_type_existing_type_no_new_type(self):
context = op_fixture("postgresql")
op.alter_column("t", "c", nullable=False, existing_type=Boolean())
context.assert_("ALTER TABLE t ALTER COLUMN c SET NOT NULL")
def test_alter_column_schema_schema_type_existing_type_no_new_type(self):
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_add_foreign_key(self):
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 test_add_foreign_key_schema(self):
context = op_fixture()
op.create_foreign_key(
"fk_test",
"t1",
"t2",
["foo", "bar"],
["bat", "hoho"],
source_schema="foo2",
referent_schema="bar2",
)
context.assert_(
"ALTER TABLE foo2.t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) "
"REFERENCES bar2.t2 (bat, hoho)")
def test_add_foreign_key_schema_same_tablename(self):
context = op_fixture()
op.create_foreign_key(
"fk_test",
"t1",
"t1",
["foo", "bar"],
["bat", "hoho"],
source_schema="foo2",
referent_schema="bar2",
)
context.assert_(
"ALTER TABLE foo2.t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) "
"REFERENCES bar2.t1 (bat, hoho)")
def test_add_foreign_key_onupdate(self):
context = op_fixture()
op.create_foreign_key(
"fk_test",
"t1",
"t2",
["foo", "bar"],
["bat", "hoho"],
onupdate="CASCADE",
)
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) "
"REFERENCES t2 (bat, hoho) ON UPDATE CASCADE")
def test_add_foreign_key_ondelete(self):
context = op_fixture()
op.create_foreign_key(
"fk_test",
"t1",
"t2",
["foo", "bar"],
["bat", "hoho"],
ondelete="CASCADE",
)
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) "
"REFERENCES t2 (bat, hoho) ON DELETE CASCADE")
def test_add_foreign_key_deferrable(self):
context = op_fixture()
op.create_foreign_key(
"fk_test",
"t1",
"t2",
["foo", "bar"],
["bat", "hoho"],
deferrable=True,
)
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) "
"REFERENCES t2 (bat, hoho) DEFERRABLE")
def test_add_foreign_key_initially(self):
context = op_fixture()
op.create_foreign_key(
"fk_test",
"t1",
"t2",
["foo", "bar"],
["bat", "hoho"],
initially="deferred",
)
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) "
"REFERENCES t2 (bat, hoho) INITIALLY deferred")
@config.requirements.foreign_key_match
def test_add_foreign_key_match(self):
context = op_fixture()
op.create_foreign_key(
"fk_test",
"t1",
"t2",
["foo", "bar"],
["bat", "hoho"],
match="SIMPLE",
)
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT fk_test FOREIGN KEY(foo, bar) "
"REFERENCES t2 (bat, hoho) MATCH SIMPLE")
def test_add_foreign_key_dialect_kw(self):
op_fixture()
with mock.patch("sqlalchemy.schema.ForeignKeyConstraint") as fkc:
op.create_foreign_key(
"fk_test",
"t1",
"t2",
["foo", "bar"],
["bat", "hoho"],
foobar_arg="xyz",
)
if config.requirements.foreign_key_match.enabled:
eq_(
fkc.mock_calls[0],
mock.call(
["foo", "bar"],
["t2.bat", "t2.hoho"],
onupdate=None,
ondelete=None,
name="fk_test",
foobar_arg="xyz",
deferrable=None,
initially=None,
match=None,
),
)
else:
eq_(
fkc.mock_calls[0],
mock.call(
["foo", "bar"],
["t2.bat", "t2.hoho"],
onupdate=None,
ondelete=None,
name="fk_test",
foobar_arg="xyz",
deferrable=None,
initially=None,
),
)
def test_add_foreign_key_self_referential(self):
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_primary_key_constraint(self):
context = op_fixture()
op.create_primary_key("pk_test", "t1", ["foo", "bar"])
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT pk_test PRIMARY KEY (foo, bar)")
def test_add_primary_key_constraint_schema(self):
context = op_fixture()
op.create_primary_key("pk_test", "t1", ["foo"], schema="bar")
context.assert_(
"ALTER TABLE bar.t1 ADD CONSTRAINT pk_test PRIMARY KEY (foo)")
def test_add_check_constraint(self):
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_add_check_constraint_schema(self):
context = op_fixture()
op.create_check_constraint(
"ck_user_name_len",
"user_table",
func.len(column("name")) > 5,
schema="foo",
)
context.assert_(
"ALTER TABLE foo.user_table ADD CONSTRAINT ck_user_name_len "
"CHECK (len(name) > 5)")
def test_add_unique_constraint(self):
context = op_fixture()
op.create_unique_constraint("uk_test", "t1", ["foo", "bar"])
context.assert_(
"ALTER TABLE t1 ADD CONSTRAINT uk_test UNIQUE (foo, bar)")
def test_add_unique_constraint_schema(self):
context = op_fixture()
op.create_unique_constraint("uk_test",
"t1", ["foo", "bar"],
schema="foo")
context.assert_(
"ALTER TABLE foo.t1 ADD CONSTRAINT uk_test UNIQUE (foo, bar)")
def test_drop_constraint(self):
context = op_fixture()
op.drop_constraint("foo_bar_bat", "t1")
context.assert_("ALTER TABLE t1 DROP CONSTRAINT foo_bar_bat")
def test_drop_constraint_schema(self):
context = op_fixture()
op.drop_constraint("foo_bar_bat", "t1", schema="foo")
context.assert_("ALTER TABLE foo.t1 DROP CONSTRAINT foo_bar_bat")
def test_create_index(self):
context = op_fixture()
op.create_index("ik_test", "t1", ["foo", "bar"])
context.assert_("CREATE INDEX ik_test ON t1 (foo, bar)")
def test_create_unique_index(self):
context = op_fixture()
op.create_index("ik_test", "t1", ["foo", "bar"], unique=True)
context.assert_("CREATE UNIQUE INDEX ik_test ON t1 (foo, bar)")
def test_create_index_quote_flag(self):
context = op_fixture()
op.create_index("ik_test", "t1", ["foo", "bar"], quote=True)
context.assert_('CREATE INDEX "ik_test" ON t1 (foo, bar)')
def test_create_index_table_col_event(self):
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_unique_constraint_col_event(self):
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_create_index_schema(self):
context = op_fixture()
op.create_index("ik_test", "t1", ["foo", "bar"], schema="foo")
context.assert_("CREATE INDEX ik_test ON foo.t1 (foo, bar)")
def test_drop_index(self):
context = op_fixture()
op.drop_index("ik_test")
context.assert_("DROP INDEX ik_test")
def test_drop_index_schema(self):
context = op_fixture()
op.drop_index("ik_test", schema="foo")
context.assert_("DROP INDEX foo.ik_test")
def test_drop_table(self):
context = op_fixture()
op.drop_table("tb_test")
context.assert_("DROP TABLE tb_test")
def test_drop_table_schema(self):
context = op_fixture()
op.drop_table("tb_test", schema="foo")
context.assert_("DROP TABLE foo.tb_test")
def test_create_table_selfref(self):
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(self):
context = op_fixture()
t1 = 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))")
eq_(t1.c.id.name, "id")
eq_(t1.schema, "schema")
def test_create_table_no_pk(self):
context = op_fixture()
t1 = op.create_table(
"some_table",
Column("x", Integer),
Column("y", Integer),
Column("z", Integer),
)
context.assert_(
"CREATE TABLE some_table (x INTEGER, y INTEGER, z INTEGER)")
assert not t1.primary_key
def test_create_table_two_fk(self):
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(self):
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_cant_op(self):
if hasattr(op, "_proxy"):
del op._proxy
assert_raises_message(
NameError,
"Can't invoke function 'inline_literal', as the "
"proxy object has not yet been established "
"for the Alembic 'Operations' class. "
"Try placing this code inside a callable.",
op.inline_literal,
"asdf",
)
def test_naming_changes(self):
context = op_fixture()
op.alter_column("t", "c", new_column_name="x")
context.assert_("ALTER TABLE t RENAME c TO x")
context = op_fixture("mysql")
op.drop_constraint("f1", "t1", type_="foreignkey")
context.assert_("ALTER TABLE t1 DROP FOREIGN KEY f1")
def test_naming_changes_drop_idx(self):
context = op_fixture("mssql")
op.drop_index("ik_test", table_name="t1")
context.assert_("DROP INDEX ik_test ON t1")
@config.requirements.comments
def test_create_table_comment_op(self):
context = op_fixture()
op.create_table_comment("some_table", "table comment")
context.assert_("COMMENT ON TABLE some_table IS 'table comment'")
@config.requirements.comments
def test_drop_table_comment_op(self):
context = op_fixture()
op.drop_table_comment("some_table")
context.assert_("COMMENT ON TABLE some_table IS NULL")
class OpTest(TestBase):
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_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_alter_column_rename_mssql(self):
context = op_fixture("mssql")
op.alter_column("t", "c", new_column_name="x")
context.assert_("EXEC sp_rename 't.c', x, 'COLUMN'")
def test_alter_column_rename_quoted_mssql(self):
context = op_fixture("mssql")
op.alter_column("t", "c", new_column_name="SomeFancyName")
context.assert_("EXEC sp_rename 't.c', [SomeFancyName], 'COLUMN'")
@combinations((True, ), (False, ), argnames="pass_existing_type")
@combinations((True, ), (False, ), argnames="change_nullability")
def test_alter_column_type_and_nullability(self, pass_existing_type,
change_nullability):
context = op_fixture("mssql")
args = dict(type_=Integer)
if pass_existing_type:
args["existing_type"] = String(15)
if change_nullability:
args["nullable"] = False
op.alter_column("t", "c", **args)
if change_nullability:
context.assert_("ALTER TABLE t ALTER COLUMN c INTEGER NOT NULL")
else:
context.assert_("ALTER TABLE t ALTER COLUMN c INTEGER")
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_drop_index(self):
context = op_fixture("mssql")
op.drop_index("my_idx", "my_table")
context.assert_contains("DROP INDEX my_idx ON my_table")
def test_drop_column_w_default(self):
context = op_fixture("mssql")
op.drop_column("t1", "c1", mssql_drop_default=True)
op.drop_column("t1", "c2", mssql_drop_default=True)
context.assert_contains(
"exec('alter table t1 drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE t1 DROP COLUMN c1")
def test_drop_column_w_default_in_batch(self):
context = op_fixture("mssql")
with op.batch_alter_table("t1", schema=None) as batch_op:
batch_op.drop_column("c1", mssql_drop_default=True)
batch_op.drop_column("c2", mssql_drop_default=True)
context.assert_contains(
"exec('alter table t1 drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE t1 DROP COLUMN c1")
def test_alter_column_drop_default(self):
context = op_fixture("mssql")
op.alter_column("t", "c", server_default=None)
context.assert_contains(
"declare @const_name varchar(256)select @const_name = [name] "
"from sys.default_constraintswhere parent_object_id = "
"object_id('t')and col_name(parent_object_id, "
"parent_column_id) = 'c'")
context.assert_contains(
"exec('alter table t drop constraint ' + @const_name)")
def test_alter_column_drop_default_w_schema(self):
context = op_fixture("mssql")
op.alter_column("t", "c", server_default=None, schema="xyz")
context.assert_contains(
"declare @const_name varchar(256)select @const_name = [name] "
"from sys.default_constraintswhere parent_object_id = "
"object_id('xyz.t')and col_name(parent_object_id, "
"parent_column_id) = 'c'")
context.assert_contains(
"exec('alter table xyz.t drop constraint ' + @const_name)")
def test_alter_column_dont_drop_default(self):
context = op_fixture("mssql")
op.alter_column("t", "c", server_default=False)
context.assert_()
def test_drop_column_w_schema(self):
context = op_fixture("mssql")
op.drop_column("t1", "c1", schema="xyz")
context.assert_contains("ALTER TABLE xyz.t1 DROP COLUMN c1")
def test_drop_column_w_check(self):
context = op_fixture("mssql")
op.drop_column("t1", "c1", mssql_drop_check=True)
op.drop_column("t1", "c2", mssql_drop_check=True)
context.assert_contains(
"exec('alter table t1 drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE t1 DROP COLUMN c1")
def test_drop_column_w_check_in_batch(self):
context = op_fixture("mssql")
with op.batch_alter_table("t1", schema=None) as batch_op:
batch_op.drop_column("c1", mssql_drop_check=True)
batch_op.drop_column("c2", mssql_drop_check=True)
context.assert_contains(
"exec('alter table t1 drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE t1 DROP COLUMN c1")
def test_drop_column_w_check_quoting(self):
context = op_fixture("mssql")
op.drop_column("table", "column", mssql_drop_check=True)
context.assert_contains(
"exec('alter table [table] drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE [table] DROP COLUMN [column]")
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_drop_column_w_fk(self):
context = op_fixture("mssql")
op.drop_column("t1", "c1", mssql_drop_foreign_key=True)
context.assert_contains(
"declare @const_name varchar(256)\n"
"select @const_name = [name] from\n"
"sys.foreign_keys fk join sys.foreign_key_columns fkcon "
"fk.object_id=fkc.constraint_object_id\n"
"where fkc.parent_object_id = object_id('t1')\nand "
"col_name(fkc.parent_object_id, fkc.parent_column_id) = 'c1'\n"
"exec('alter table t1 drop constraint ' + @const_name)")
context.assert_contains(
"exec('alter table t1 drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE t1 DROP COLUMN c1")
def test_drop_column_w_fk_schema(self):
context = op_fixture("mssql")
op.drop_column("t1", "c1", schema="xyz", mssql_drop_foreign_key=True)
context.assert_contains(
"declare @const_name varchar(256)\n"
"select @const_name = [name] from\n"
"sys.foreign_keys fk join sys.foreign_key_columns fkcon "
"fk.object_id=fkc.constraint_object_id\n"
"where fkc.parent_object_id = object_id('xyz.t1')\nand "
"col_name(fkc.parent_object_id, fkc.parent_column_id) = 'c1'\n"
"exec('alter table xyz.t1 drop constraint ' + @const_name)")
context.assert_contains(
"exec('alter table xyz.t1 drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE xyz.t1 DROP COLUMN c1")
def test_drop_column_w_fk_in_batch(self):
context = op_fixture("mssql")
with op.batch_alter_table("t1", schema=None) as batch_op:
batch_op.drop_column("c1", mssql_drop_foreign_key=True)
context.assert_contains(
"exec('alter table t1 drop constraint ' + @const_name)")
context.assert_contains("ALTER TABLE t1 DROP COLUMN c1")
def test_alter_column_not_nullable_w_existing_type(self):
context = op_fixture("mssql")
op.alter_column("t", "c", nullable=False, existing_type=Integer)
context.assert_("ALTER TABLE t ALTER COLUMN c INTEGER NOT NULL")
def test_alter_column_nullable_w_new_type(self):
context = op_fixture("mssql")
op.alter_column("t", "c", nullable=True, type_=Integer)
context.assert_("ALTER TABLE t ALTER COLUMN c INTEGER NULL")
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_column_nullable_type_required(self):
op_fixture("mssql")
assert_raises_message(
util.CommandError,
"MS-SQL ALTER COLUMN operations with NULL or "
"NOT NULL require the existing_type or a new "
"type_ be passed.",
op.alter_column,
"t",
"c",
nullable=False,
)
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_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_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)")
@config.requirements.computed_columns_api
def test_add_column_computed(self):
context = op_fixture("mssql")
op.add_column(
"t1",
Column("some_column", Integer, sqla_compat.Computed("foo * 5")),
)
context.assert_("ALTER TABLE t1 ADD some_column AS (foo * 5)")
def test_alter_do_everything(self):
context = op_fixture("mssql")
op.alter_column(
"t",
"c",
new_column_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_rename_table(self):
context = op_fixture("mssql")
op.rename_table("t1", "t2")
context.assert_contains("EXEC sp_rename 't1', t2")
def test_rename_table_schema(self):
context = op_fixture("mssql")
op.rename_table("t1", "t2", schema="foobar")
context.assert_contains("EXEC sp_rename 'foobar.t1', t2")
def test_rename_table_casesens(self):
context = op_fixture("mssql")
op.rename_table("TeeOne", "TeeTwo")
# yup, ran this in SQL Server 2014, the two levels of quoting
# seems to be understood. Can't do the two levels on the
# target name though !
context.assert_contains("EXEC sp_rename '[TeeOne]', [TeeTwo]")
def test_rename_table_schema_casesens(self):
context = op_fixture("mssql")
op.rename_table("TeeOne", "TeeTwo", schema="FooBar")
# yup, ran this in SQL Server 2014, the two levels of quoting
# seems to be understood. Can't do the two levels on the
# target name though !
context.assert_contains("EXEC sp_rename '[FooBar].[TeeOne]', [TeeTwo]")
def test_alter_column_rename_mssql_schema(self):
context = op_fixture("mssql")
op.alter_column("t", "c", new_column_name="x", schema="y")
context.assert_("EXEC sp_rename 'y.t.c', x, 'COLUMN'")
def test_create_index_mssql_include(self):
context = op_fixture("mssql")
op.create_index(
op.f("ix_mytable_a_b"),
"mytable",
["col_a", "col_b"],
unique=False,
mssql_include=["col_c"],
)
context.assert_contains("CREATE INDEX ix_mytable_a_b ON mytable "
"(col_a, col_b) INCLUDE (col_c)")
def test_create_index_mssql_include_is_none(self):
context = op_fixture("mssql")
op.create_index(op.f("ix_mytable_a_b"),
"mytable", ["col_a", "col_b"],
unique=False)
context.assert_contains("CREATE INDEX ix_mytable_a_b ON mytable "
"(col_a, col_b)")
@combinations(
(lambda: sqla_compat.Computed("foo * 5"), lambda: None),
(lambda: None, lambda: sqla_compat.Computed("foo * 5")),
(
lambda: sqla_compat.Computed("foo * 42"),
lambda: sqla_compat.Computed("foo * 5"),
),
)
@config.requirements.computed_columns
def test_alter_column_computed_not_supported(self, sd, esd):
op_fixture("mssql")
assert_raises_message(
exc.CompileError,
'Adding or removing a "computed" construct, e.g. '
"GENERATED ALWAYS AS, to or from an existing column is not "
"supported.",
op.alter_column,
"t1",
"c1",
server_default=sd(),
existing_server_default=esd(),
)
@config.requirements.identity_columns
@combinations(
({}, ),
(dict(always=True), ),
(dict(start=3), ),
(dict(start=3, increment=3), ),
)
def test_add_column_identity(self, kw):
context = op_fixture("mssql")
op.add_column(
"t1",
Column("some_column", Integer, sqla_compat.Identity(**kw)),
)
if "start" in kw or "increment" in kw:
options = "(%s,%s)" % (
kw.get("start", 1),
kw.get("increment", 1),
)
else:
options = ""
context.assert_(
"ALTER TABLE t1 ADD some_column INTEGER NOT NULL IDENTITY%s" %
options)
@combinations(
(lambda: sqla_compat.Identity(), lambda: None),
(lambda: None, lambda: sqla_compat.Identity()),
(
lambda: sqla_compat.Identity(),
lambda: sqla_compat.Identity(),
),
)
@config.requirements.identity_columns
def test_alter_column_identity_add_not_supported(self, sd, esd):
op_fixture("mssql")
assert_raises_message(
exc.CompileError,
'Adding, removing or modifying an "identity" construct, '
"e.g. GENERATED AS IDENTITY, to or from an existing "
"column is not supported in this dialect.",
op.alter_column,
"t1",
"c1",
server_default=sd(),
existing_server_default=esd(),
)
class MySQLOpTest(TestBase):
def test_create_table_with_comment(self):
context = op_fixture("mysql")
op.create_table(
"t2",
Column("c1", Integer, primary_key=True),
comment="This is a table comment",
)
context.assert_contains("COMMENT='This is a table comment'")
def test_create_table_with_column_comments(self):
context = op_fixture("mysql")
op.create_table(
"t2",
Column("c1", Integer, primary_key=True, comment="c1 comment"),
Column("c2", Integer, comment="c2 comment"),
comment="This is a table comment",
)
context.assert_(
"CREATE TABLE t2 "
"(c1 INTEGER NOT NULL COMMENT 'c1 comment' AUTO_INCREMENT, "
# TODO: why is there no space at the end here? is that on the
# SQLA side?
"c2 INTEGER COMMENT 'c2 comment', PRIMARY KEY (c1))"
"COMMENT='This is a table comment'")
def test_add_column_with_comment(self):
context = op_fixture("mysql")
op.add_column("t", Column("q", Integer, comment="This is a comment"))
context.assert_(
"ALTER TABLE t ADD COLUMN q INTEGER COMMENT 'This is a comment'")
def test_rename_column(self):
context = op_fixture("mysql")
op.alter_column("t1",
"c1",
new_column_name="c2",
existing_type=Integer)
context.assert_("ALTER TABLE t1 CHANGE c1 c2 INTEGER NULL")
def test_rename_column_quotes_needed_one(self):
context = op_fixture("mysql")
op.alter_column(
"MyTable",
"ColumnOne",
new_column_name="ColumnTwo",
existing_type=Integer,
)
context.assert_(
"ALTER TABLE `MyTable` CHANGE `ColumnOne` `ColumnTwo` INTEGER NULL"
)
def test_rename_column_quotes_needed_two(self):
context = op_fixture("mysql")
op.alter_column(
"my table",
"column one",
new_column_name="column two",
existing_type=Integer,
)
context.assert_("ALTER TABLE `my table` CHANGE `column one` "
"`column two` INTEGER NULL")
def test_rename_column_serv_default(self):
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_rename_column_serv_compiled_default(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
existing_type=Integer,
server_default=func.utc_thing(func.current_timestamp()),
)
# this is not a valid MySQL default but the point is to just
# test SQL expression rendering
context.assert_("ALTER TABLE t1 ALTER COLUMN c1 "
"SET DEFAULT utc_thing(CURRENT_TIMESTAMP)")
def test_rename_column_autoincrement(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
new_column_name="c2",
existing_type=Integer,
existing_autoincrement=True,
)
context.assert_(
"ALTER TABLE t1 CHANGE c1 c2 INTEGER NULL AUTO_INCREMENT")
def test_col_add_autoincrement(self):
context = op_fixture("mysql")
op.alter_column("t1", "c1", existing_type=Integer, autoincrement=True)
context.assert_("ALTER TABLE t1 MODIFY c1 INTEGER NULL AUTO_INCREMENT")
def test_col_remove_autoincrement(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
existing_type=Integer,
existing_autoincrement=True,
autoincrement=False,
)
context.assert_("ALTER TABLE t1 MODIFY c1 INTEGER NULL")
def test_col_dont_remove_server_default(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
existing_type=Integer,
existing_server_default="1",
server_default=False,
)
context.assert_()
def test_alter_column_drop_default(self):
context = op_fixture("mysql")
op.alter_column("t", "c", existing_type=Integer, server_default=None)
context.assert_("ALTER TABLE t ALTER COLUMN c DROP DEFAULT")
def test_alter_column_remove_schematype(self):
context = op_fixture("mysql")
op.alter_column(
"t",
"c",
type_=Integer,
existing_type=Boolean(create_constraint=True, name="ck1"),
server_default=None,
)
context.assert_("ALTER TABLE t MODIFY c INTEGER NULL")
def test_alter_column_modify_default(self):
context = op_fixture("mysql")
# notice we dont need the existing type on this one...
op.alter_column("t", "c", server_default="1")
context.assert_("ALTER TABLE t ALTER COLUMN c SET DEFAULT '1'")
def test_alter_column_modify_datetime_default(self):
# use CHANGE format when the datatype is DATETIME or TIMESTAMP,
# as this is needed for a functional default which is what you'd
# get with a DATETIME/TIMESTAMP. Will also work in the very unlikely
# case the default is a fixed timestamp value.
context = op_fixture("mysql")
op.alter_column(
"t",
"c",
existing_type=DATETIME(),
server_default=text("CURRENT_TIMESTAMP"),
)
context.assert_(
"ALTER TABLE t CHANGE c c DATETIME NULL DEFAULT CURRENT_TIMESTAMP")
def test_alter_column_modify_programmatic_default(self):
# test issue #736
# when autogenerate.compare creates the operation object
# programmatically, the server_default of the op has the full
# DefaultClause present. make sure the usual renderer works.
context = op_fixture("mysql")
m1 = MetaData()
autogen_context = api.AutogenContext(context, m1)
operation = ops.AlterColumnOp("t", "c")
for fn in (
compare._compare_nullable,
compare._compare_type,
compare._compare_server_default,
):
fn(
autogen_context,
operation,
None,
"t",
"c",
Column("c", Float(), nullable=False, server_default=text("0")),
Column("c", Float(), nullable=True, default=0),
)
op.invoke(operation)
context.assert_("ALTER TABLE t MODIFY c FLOAT NULL DEFAULT 0")
def test_col_not_nullable(self):
context = op_fixture("mysql")
op.alter_column("t1", "c1", nullable=False, existing_type=Integer)
context.assert_("ALTER TABLE t1 MODIFY c1 INTEGER NOT NULL")
def test_col_not_nullable_existing_serv_default(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
nullable=False,
existing_type=Integer,
existing_server_default="5",
)
context.assert_(
"ALTER TABLE t1 MODIFY c1 INTEGER NOT NULL DEFAULT '5'")
def test_col_nullable(self):
context = op_fixture("mysql")
op.alter_column("t1", "c1", nullable=True, existing_type=Integer)
context.assert_("ALTER TABLE t1 MODIFY c1 INTEGER NULL")
def test_col_multi_alter(self):
context = op_fixture("mysql")
op.alter_column("t1",
"c1",
nullable=False,
server_default="q",
type_=Integer)
context.assert_(
"ALTER TABLE t1 MODIFY c1 INTEGER NOT NULL DEFAULT 'q'")
def test_alter_column_multi_alter_w_drop_default(self):
context = op_fixture("mysql")
op.alter_column("t1",
"c1",
nullable=False,
server_default=None,
type_=Integer)
context.assert_("ALTER TABLE t1 MODIFY c1 INTEGER NOT NULL")
def test_col_alter_type_required(self):
op_fixture("mysql")
assert_raises_message(
util.CommandError,
"MySQL CHANGE/MODIFY COLUMN operations require the existing type.",
op.alter_column,
"t1",
"c1",
nullable=False,
server_default="q",
)
def test_alter_column_add_comment(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
comment="This is a column comment",
existing_type=Boolean(),
schema="foo",
)
context.assert_("ALTER TABLE foo.t1 MODIFY c1 BOOL NULL "
"COMMENT 'This is a column comment'")
def test_alter_column_add_comment_quoting(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
comment="This is a 'column' comment",
existing_type=Boolean(),
schema="foo",
)
context.assert_("ALTER TABLE foo.t1 MODIFY c1 BOOL NULL "
"COMMENT 'This is a ''column'' comment'")
def test_alter_column_drop_comment(self):
context = op_fixture("mysql")
op.alter_column(
"t",
"c",
existing_type=Boolean(),
schema="foo",
comment=None,
existing_comment="This is a column comment",
)
context.assert_("ALTER TABLE foo.t MODIFY c BOOL NULL")
def test_alter_column_existing_comment(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
nullable=False,
existing_comment="existing column comment",
existing_type=Integer,
)
context.assert_("ALTER TABLE t1 MODIFY c1 INTEGER NOT NULL "
"COMMENT 'existing column comment'")
def test_rename_column_existing_comment(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
new_column_name="newc1",
existing_nullable=False,
existing_comment="existing column comment",
existing_type=Integer,
)
context.assert_("ALTER TABLE t1 CHANGE c1 newc1 INTEGER NOT NULL "
"COMMENT 'existing column comment'")
def test_alter_column_new_comment_replaces_existing(self):
context = op_fixture("mysql")
op.alter_column(
"t1",
"c1",
nullable=False,
comment="This is a column comment",
existing_comment="existing column comment",
existing_type=Integer,
)
context.assert_("ALTER TABLE t1 MODIFY c1 INTEGER NOT NULL "
"COMMENT 'This is a column comment'")
def test_create_table_comment(self):
# this is handled by SQLAlchemy's compilers
context = op_fixture("mysql")
op.create_table_comment("t2", comment="t2 table", schema="foo")
context.assert_("ALTER TABLE foo.t2 COMMENT 't2 table'")
def test_drop_table_comment(self):
# this is handled by SQLAlchemy's compilers
context = op_fixture("mysql")
op.drop_table_comment("t2", existing_comment="t2 table", schema="foo")
context.assert_("ALTER TABLE foo.t2 COMMENT ''")
@config.requirements.computed_columns_api
def test_add_column_computed(self):
context = op_fixture("mysql")
op.add_column(
"t1",
Column("some_column", Integer, sqla_compat.Computed("foo * 5")),
)
context.assert_("ALTER TABLE t1 ADD COLUMN some_column "
"INTEGER GENERATED ALWAYS AS (foo * 5)")
def test_drop_fk(self):
context = op_fixture("mysql")
op.drop_constraint("f1", "t1", "foreignkey")
context.assert_("ALTER TABLE t1 DROP FOREIGN KEY f1")
def test_drop_fk_quoted(self):
context = op_fixture("mysql")
op.drop_constraint("MyFk", "MyTable", "foreignkey")
context.assert_("ALTER TABLE `MyTable` DROP FOREIGN KEY `MyFk`")
def test_drop_constraint_primary(self):
context = op_fixture("mysql")
op.drop_constraint("primary", "t1", type_="primary")
context.assert_("ALTER TABLE t1 DROP PRIMARY KEY")
def test_drop_unique(self):
context = op_fixture("mysql")
op.drop_constraint("f1", "t1", "unique")
context.assert_("ALTER TABLE t1 DROP INDEX f1")
def test_drop_unique_quoted(self):
context = op_fixture("mysql")
op.drop_constraint("MyUnique", "MyTable", "unique")
context.assert_("ALTER TABLE `MyTable` DROP INDEX `MyUnique`")
def test_drop_check_mariadb(self):
context = op_fixture("mariadb")
op.drop_constraint("f1", "t1", "check")
context.assert_("ALTER TABLE t1 DROP CONSTRAINT f1")
def test_drop_check_quoted_mariadb(self):
context = op_fixture("mariadb")
op.drop_constraint("MyCheck", "MyTable", "check")
context.assert_("ALTER TABLE `MyTable` DROP CONSTRAINT `MyCheck`")
def test_drop_check_mysql(self):
context = op_fixture("mysql")
op.drop_constraint("f1", "t1", "check")
context.assert_("ALTER TABLE t1 DROP CHECK f1")
def test_drop_check_quoted_mysql(self):
context = op_fixture("mysql")
op.drop_constraint("MyCheck", "MyTable", "check")
context.assert_("ALTER TABLE `MyTable` DROP CHECK `MyCheck`")
def test_drop_unknown(self):
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_drop_generic_constraint(self):
op_fixture("mysql")
assert_raises_message(
NotImplementedError,
"No generic 'DROP CONSTRAINT' in MySQL - please "
"specify constraint type",
op.drop_constraint,
"f1",
"t1",
)
@combinations(
(lambda: sqla_compat.Computed("foo * 5"), lambda: None),
(lambda: None, lambda: sqla_compat.Computed("foo * 5")),
(
lambda: sqla_compat.Computed("foo * 42"),
lambda: sqla_compat.Computed("foo * 5"),
),
)
@config.requirements.computed_columns_api
def test_alter_column_computed_not_supported(self, sd, esd):
op_fixture("mssql")
assert_raises_message(
exc.CompileError,
'Adding or removing a "computed" construct, e.g. '
"GENERATED ALWAYS AS, to or from an existing column is not "
"supported.",
op.alter_column,
"t1",
"c1",
server_default=sd(),
existing_server_default=esd(),
)
@combinations(
(lambda: sqla_compat.Identity(), lambda: None),
(lambda: None, lambda: sqla_compat.Identity()),
(
lambda: sqla_compat.Identity(),
lambda: sqla_compat.Identity(),
),
)
@config.requirements.identity_columns_api
def test_alter_column_identity_not_supported(self, sd, esd):
op_fixture()
assert_raises_message(
exc.CompileError,
'Adding, removing or modifying an "identity" construct, '
"e.g. GENERATED AS IDENTITY, to or from an existing "
"column is not supported in this dialect.",
op.alter_column,
"t1",
"c1",
server_default=sd(),
existing_server_default=esd(),
)
