def test_render_add_uq_constraint_realcol(self):
t, t2 = self._constraint_create_fixture()
constraint = UniqueConstraint(t2.c.a, t2.c.b, name="uq_cs2")
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE t2 ADD CONSTRAINT uq_cs2 UNIQUE (a, b)")
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_render_add_uq_constraint_stringcol(self):
t, t2 = self._constraint_create_fixture()
constraint = UniqueConstraint("a", "b", name="uq_cst")
t2.append_constraint(constraint)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE t2 ADD CONSTRAINT uq_cst UNIQUE (a, b)")
def test_render_add_fk_constraint_realcol(self):
t, t2 = self._constraint_create_fixture()
constraint = ForeignKeyConstraint([t.c.a], [t2.c.b])
t.append_constraint(constraint)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD FOREIGN KEY(a) REFERENCES t2 (b)")
def test_render_add_fk_constraint_stringcol(self):
t, t2 = self._constraint_create_fixture()
constraint = ForeignKeyConstraint(["b"], ["t2.a"])
t.append_constraint(constraint)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD FOREIGN KEY(b) REFERENCES t2 (a)")
def test_exclude_constraint_text(self):
m = MetaData()
cons = ExcludeConstraint((text('room::TEXT'), '='))
Table('testtbl', m, Column('room', String), cons)
self.assert_compile(
schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room::TEXT WITH =)')
def test_render_add_pk_constraint(self):
t, t2 = self._constraint_create_fixture()
assert t.c.a.primary_key is False
constraint = PrimaryKeyConstraint(t.c.a)
assert t.c.a.primary_key is True
self.assert_compile(schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD PRIMARY KEY (a)")
def test_exclude_constraint_when(self):
m = MetaData()
tbl = Table('testtbl', m, Column('room', String))
cons = ExcludeConstraint(('room', '='), where=tbl.c.room.in_(['12']))
tbl.append_constraint(cons)
self.assert_compile(schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room WITH =) WHERE (testtbl.room IN (\'12\'))',
dialect=postgresql.dialect())
def test_exclude_constraint_cast_quote(self):
m = MetaData()
tbl = Table('testtbl', m, Column('Room', String))
cons = ExcludeConstraint((cast(tbl.c.Room, Text), '='))
tbl.append_constraint(cons)
self.assert_compile(
schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(CAST("Room" AS TEXT) WITH =)')
def test_exclude_constraint_min(self):
m = MetaData()
tbl = Table('testtbl', m, Column('room', Integer, primary_key=True))
cons = ExcludeConstraint(('room', '='))
tbl.append_constraint(cons)
self.assert_compile(schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room WITH =)',
dialect=postgresql.dialect())
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_exclude_constraint_copy(self):
m = MetaData()
cons = ExcludeConstraint(('room', '='))
tbl = Table('testtbl', m, Column('room', Integer, primary_key=True),
cons)
# apparently you can't copy a ColumnCollectionConstraint until
# after it has been bound to a table...
cons_copy = cons.copy()
tbl.append_constraint(cons_copy)
self.assert_compile(
schema.AddConstraint(cons_copy),
'ALTER TABLE testtbl ADD EXCLUDE USING gist '
'(room WITH =)')
def test_fk_match_clause(self):
t = Table('tbl', MetaData(), Column('a', Integer),
Column('b', Integer, ForeignKey('tbl.a', match="SIMPLE")))
self.assert_compile(
schema.CreateTable(t),
"CREATE TABLE tbl (a INTEGER, b INTEGER, "
"FOREIGN KEY(b) REFERENCES tbl "
"(a) MATCH SIMPLE)",
)
self.assert_compile(
schema.AddConstraint(list(t.foreign_keys)[0].constraint),
"ALTER TABLE tbl ADD FOREIGN KEY(b) "
"REFERENCES tbl (a) MATCH SIMPLE")
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 test_exclude_constraint_full(self):
m = MetaData()
room = Column('room', Integer, primary_key=True)
tbl = Table('testtbl', m, room, Column('during', TSRANGE))
room = Column('room', Integer, primary_key=True)
cons = ExcludeConstraint((room, '='), ('during', '&&'),
name='my_name',
using='gist',
where="room > 100",
deferrable=True,
initially='immediate')
tbl.append_constraint(cons)
self.assert_compile(schema.AddConstraint(cons),
'ALTER TABLE testtbl ADD CONSTRAINT my_name '
'EXCLUDE USING gist '
'(room WITH =, during WITH '
'&&) WHERE '
'(room > 100) DEFERRABLE INITIALLY immediate',
dialect=postgresql.dialect())
def add_constraint(self, const):
if const._create_rule is None or \
const._create_rule(self):
self._exec(schema.AddConstraint(const))
def add_constraint(self, table, constraint, dialect=None):
self.add(table,
schema.AddConstraint(constraint),
DropConstraint(constraint, if_exists=True),
dialect=dialect)
def test_add_drop_constraint(self):
m = MetaData()
t = Table('tbl', m, Column('a', Integer), Column('b', Integer))
t2 = Table('t2', m, Column('a', Integer), Column('b', Integer))
constraint = 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"
")")
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD CONSTRAINT my_test_constraint "
"CHECK (a < b) DEFERRABLE INITIALLY DEFERRED")
# 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"
")")
self.assert_compile(
schema.DropConstraint(constraint),
"ALTER TABLE tbl DROP CONSTRAINT my_test_constraint")
self.assert_compile(
schema.DropConstraint(constraint, cascade=True),
"ALTER TABLE tbl DROP CONSTRAINT my_test_constraint CASCADE")
constraint = ForeignKeyConstraint(["b"], ["t2.a"])
t.append_constraint(constraint)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD FOREIGN KEY(b) REFERENCES t2 (a)")
constraint = ForeignKeyConstraint([t.c.a], [t2.c.b])
t.append_constraint(constraint)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD FOREIGN KEY(a) REFERENCES t2 (b)")
constraint = UniqueConstraint("a", "b", name="uq_cst")
t2.append_constraint(constraint)
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE t2 ADD CONSTRAINT uq_cst UNIQUE (a, b)")
constraint = UniqueConstraint(t2.c.a, t2.c.b, name="uq_cs2")
self.assert_compile(
schema.AddConstraint(constraint),
"ALTER TABLE t2 ADD CONSTRAINT uq_cs2 UNIQUE (a, b)")
assert t.c.a.primary_key is False
constraint = PrimaryKeyConstraint(t.c.a)
assert t.c.a.primary_key is True
self.assert_compile(schema.AddConstraint(constraint),
"ALTER TABLE tbl ADD PRIMARY KEY (a)")
