def test_quote_flag_propagate_check_constraint(self):
m = MetaData()
t = Table("t", m, Column("x", Integer, quote=True))
CheckConstraint(t.c.x > 5)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE t (" '"x" INTEGER, ' 'CHECK ("x" > 5)' ")",
)
def test_render_check_constraint_sql_literal(self):
t, t2 = self._constraint_create_fixture()
constraint = CheckConstraint(t.c.a > 5)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD CHECK (a > 5)",
)
def test_drop_constraint_mysql(self):
m = MetaData()
table_name = "testtbl"
constraint_name = "constraint"
constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
Table(table_name, m, Column("data", String(255)), constraint)
dialect = mysql.dialect()
self.assert_compile(
schema.DropConstraint(constraint),
"ALTER TABLE %s DROP CHECK `%s`" % (table_name, constraint_name),
dialect=dialect,
)
def test_render_check_constraint_inline_sql_literal(self):
t, t2 = self._constraint_create_fixture()
m = MetaData()
t = Table(
"t",
m,
Column("a", Integer, CheckConstraint(Column("a", Integer) > 5)),
)
self.assert_compile(schema.CreateColumn(t.c.a),
"a INTEGER CHECK (a > 5)")
def test_check_constraint_create(self):
metadata = self.metadata
Table(
"foo",
metadata,
Column("id", Integer, primary_key=True),
Column("x", Integer),
Column("y", Integer),
CheckConstraint("x>y"),
)
Table(
"bar",
metadata,
Column("id", Integer, primary_key=True),
Column("x", Integer, CheckConstraint("x>7")),
Column("z", Integer),
)
self.assert_sql_execution(
testing.db,
lambda: metadata.create_all(checkfirst=False),
AllOf(
CompiledSQL("CREATE TABLE foo ("
"id INTEGER NOT NULL, "
"x INTEGER, "
"y INTEGER, "
"PRIMARY KEY (id), "
"CHECK (x>y)"
")"),
CompiledSQL("CREATE TABLE bar ("
"id INTEGER NOT NULL, "
"x INTEGER CHECK (x>7), "
"z INTEGER, "
"PRIMARY KEY (id)"
")"),
),
)
def test_drop_constraint_mariadb(self):
m = MetaData()
table_name = "testtbl"
constraint_name = "constraint"
constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
Table(table_name, m, Column("data", String(255)), constraint)
dialect = mysql.dialect()
dialect.server_version_info = (10, 1, 1, "MariaDB")
self.assert_compile(
schema.DropConstraint(constraint),
"ALTER TABLE %s DROP CONSTRAINT `%s`" %
(table_name, constraint_name),
dialect=dialect,
)
def test_column_level_ck_name(self):
t = Table(
"tbl",
MetaData(),
Column(
"a",
Integer,
CheckConstraint("a > 5", name="ck_a_greater_five"),
),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl (a INTEGER CONSTRAINT "
"ck_a_greater_five CHECK (a > 5))",
)
def test_render_drop_constraint_cascade(self):
t, t2 = self._constraint_create_fixture()
constraint = CheckConstraint(
"a < b",
name="my_test_constraint",
deferrable=True,
initially="DEFERRED",
table=t,
)
self.assert_compile(
schema.DropConstraint(constraint, cascade=True),
"ALTER TABLE tbl DROP CONSTRAINT my_test_constraint CASCADE",
)
def test_render_ck_constraint_external(self):
t, t2 = self._constraint_create_fixture()
constraint = CheckConstraint(
"a < b",
name="my_test_constraint",
deferrable=True,
initially="DEFERRED",
table=t,
)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD CONSTRAINT my_test_constraint "
"CHECK (a < b) DEFERRABLE INITIALLY DEFERRED",
)
def test_deferrable_column_check(self):
t = Table(
"tbl",
MetaData(),
Column("a", Integer),
Column(
"b",
Integer,
CheckConstraint("a < b", deferrable=True,
initially="DEFERRED"),
),
)
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl (a INTEGER, b INTEGER CHECK (a < b) "
"DEFERRABLE INITIALLY DEFERRED)",
)
def test_render_ck_constraint_inline(self):
t, t2 = self._constraint_create_fixture()
CheckConstraint(
"a < b",
name="my_test_constraint",
deferrable=True,
initially="DEFERRED",
table=t,
)
# before we create an AddConstraint,
# the CONSTRAINT comes out inline
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl ("
"a INTEGER, "
"b INTEGER, "
"CONSTRAINT my_test_constraint CHECK (a < b) "
"DEFERRABLE INITIALLY DEFERRED"
")",
)
def test_external_ck_constraint_cancels_internal(self):
t, t2 = self._constraint_create_fixture()
constraint = CheckConstraint(
"a < b",
name="my_test_constraint",
deferrable=True,
initially="DEFERRED",
table=t,
)
schema.AddConstraint(constraint)
# once we make an AddConstraint,
# inline compilation of the CONSTRAINT
# is disabled
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl ("
"a INTEGER, "
"b INTEGER"
")",
)
def factory(**kw):
return CheckConstraint("a < b", **kw)