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