def test_cycle_named_fks(self): metadata = MetaData() Table( "a", metadata, Column("id", Integer, primary_key=True), Column("bid", Integer, ForeignKey("b.id")), ) Table( "b", metadata, Column("id", Integer, primary_key=True), Column( "aid", Integer, ForeignKey("a.id", use_alter=True, name="aidfk"), ), ) assertions = [ AllOf( CompiledSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id)" ")"), CompiledSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")"), ), CompiledSQL("ALTER TABLE b ADD CONSTRAINT aidfk " "FOREIGN KEY(aid) REFERENCES a (id)"), ] with self.sql_execution_asserter() as asserter: metadata.create_all(testing.db, checkfirst=False) if testing.db.dialect.supports_alter: asserter.assert_(*assertions) with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False) asserter.assert_( CompiledSQL("ALTER TABLE b DROP CONSTRAINT aidfk"), AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")), ) else: with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False) asserter.assert_( AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")))
def _assert_cyclic_constraint_no_alter(self, metadata, auto=False, sqlite_warning=False): table_assertions = [] if auto: table_assertions.append( DialectSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id), " "CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)" ")")) table_assertions.append( DialectSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")")) else: table_assertions.append( DialectSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id), " "CONSTRAINT bfk FOREIGN KEY(aid) REFERENCES a (id)" ")")) table_assertions.append( DialectSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")")) assertions = [AllOf(*table_assertions)] with self.sql_execution_asserter() as asserter: metadata.create_all(testing.db, checkfirst=False) asserter.assert_(*assertions) assertions = [ AllOf(CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b")) ] if sqlite_warning: with expect_warnings("Can't sort tables for DROP; "): with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False), else: with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False), asserter.assert_(*assertions)
def _assert_cyclic_constraint_supports_alter(self, metadata, auto=False): table_assertions = [] if auto: table_assertions = [ CompiledSQL('CREATE TABLE b (' 'id INTEGER NOT NULL, ' 'aid INTEGER, ' 'PRIMARY KEY (id)' ')'), CompiledSQL('CREATE TABLE a (' 'id INTEGER NOT NULL, ' 'bid INTEGER, ' 'PRIMARY KEY (id)' ')') ] else: table_assertions = [ CompiledSQL('CREATE TABLE b (' 'id INTEGER NOT NULL, ' 'aid INTEGER, ' 'PRIMARY KEY (id)' ')'), CompiledSQL('CREATE TABLE a (' 'id INTEGER NOT NULL, ' 'bid INTEGER, ' 'PRIMARY KEY (id), ' 'FOREIGN KEY(bid) REFERENCES b (id)' ')') ] assertions = [AllOf(*table_assertions)] fk_assertions = [] fk_assertions.append( CompiledSQL('ALTER TABLE b ADD CONSTRAINT bfk ' 'FOREIGN KEY(aid) REFERENCES a (id)')) if auto: fk_assertions.append( CompiledSQL('ALTER TABLE a ADD ' 'FOREIGN KEY(bid) REFERENCES b (id)')) assertions.append(AllOf(*fk_assertions)) with self.sql_execution_asserter() as asserter: metadata.create_all(checkfirst=False) asserter.assert_(*assertions) assertions = [ CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk'), CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b") ] with self.sql_execution_asserter() as asserter: metadata.drop_all(checkfirst=False), asserter.assert_(*assertions)
def _assert_cyclic_constraint_supports_alter(self, metadata, auto=False): table_assertions = [] if auto: table_assertions = [ CompiledSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id)" ")"), CompiledSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id)" ")"), ] else: table_assertions = [ CompiledSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id)" ")"), CompiledSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id), " "FOREIGN KEY(bid) REFERENCES b (id)" ")"), ] assertions = [AllOf(*table_assertions)] fk_assertions = [] fk_assertions.append( CompiledSQL("ALTER TABLE b ADD CONSTRAINT bfk " "FOREIGN KEY(aid) REFERENCES a (id)")) if auto: fk_assertions.append( CompiledSQL("ALTER TABLE a ADD " "FOREIGN KEY(bid) REFERENCES b (id)")) assertions.append(AllOf(*fk_assertions)) with self.sql_execution_asserter() as asserter: metadata.create_all(testing.db, checkfirst=False) asserter.assert_(*assertions) assertions = [ CompiledSQL("ALTER TABLE b DROP CONSTRAINT bfk"), CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b"), ] with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False), asserter.assert_(*assertions)
def test_drop_ordering(self): with self.sql_execution_asserter(testing.db) as asserter: self.tables_test_metadata.drop_all(testing.db, checkfirst=False) asserter.assert_( AllOf( CompiledSQL("DROP TABLE t_seq_test_2", {}), CompiledSQL("DROP TABLE t_seq_test", {}), ), AllOf( # dropped as part of metadata level CompiledSQL("DROP SEQUENCE t_seq", {}), CompiledSQL("DROP SEQUENCE t_seq_2", {}), ), )
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_unique_constraint_create(self): metadata = self.metadata Table('foo', metadata, Column('id', Integer, primary_key=True), Column('value', String(30), unique=True)) Table('bar', metadata, Column('id', Integer, primary_key=True), Column('value', String(30)), Column('value2', String(30)), UniqueConstraint('value', 'value2', name='uix1')) self.assert_sql_execution( testing.db, lambda: metadata.create_all(checkfirst=False), AllOf( CompiledSQL('CREATE TABLE foo (' 'id INTEGER NOT NULL, ' 'value VARCHAR(30), ' 'PRIMARY KEY (id), ' 'UNIQUE (value)' ')'), CompiledSQL('CREATE TABLE bar (' 'id INTEGER NOT NULL, ' 'value VARCHAR(30), ' 'value2 VARCHAR(30), ' 'PRIMARY KEY (id), ' 'CONSTRAINT uix1 UNIQUE (value, value2)' ')')))
def test_index_create(self): metadata = self.metadata employees = Table('employees', metadata, Column('id', Integer, primary_key=True), Column('first_name', String(30)), Column('last_name', String(30)), Column('email_address', String(30))) i = Index('employee_name_index', employees.c.last_name, employees.c.first_name) assert i in employees.indexes i2 = Index('employee_email_index', employees.c.email_address, unique=True) assert i2 in employees.indexes self.assert_sql_execution( testing.db, lambda: metadata.create_all(checkfirst=False), RegexSQL("^CREATE TABLE"), AllOf( CompiledSQL( 'CREATE INDEX employee_name_index ON ' 'employees (last_name, first_name)', []), CompiledSQL( 'CREATE UNIQUE INDEX employee_email_index ON ' 'employees (email_address)', [])))
def test_index_create_camelcase(self): """test that mixed-case index identifiers are legal""" metadata = self.metadata employees = Table('companyEmployees', metadata, Column('id', Integer, primary_key=True), Column('firstName', String(30)), Column('lastName', String(30)), Column('emailAddress', String(30))) Index('employeeNameIndex', employees.c.lastName, employees.c.firstName) Index('employeeEmailIndex', employees.c.emailAddress, unique=True) self.assert_sql_execution( testing.db, lambda: metadata.create_all(checkfirst=False), RegexSQL("^CREATE TABLE"), AllOf( CompiledSQL( 'CREATE INDEX "employeeNameIndex" ON ' '"companyEmployees" ("lastName", "firstName")', []), CompiledSQL( 'CREATE UNIQUE INDEX "employeeEmailIndex" ON ' '"companyEmployees" ("emailAddress")', [])))
def test_fk_cant_drop_cycled_unnamed(self): metadata = MetaData() Table("a", metadata, Column('id', Integer, primary_key=True), Column('bid', Integer), ForeignKeyConstraint(["bid"], ["b.id"])) Table("b", metadata, Column('id', Integer, primary_key=True), Column("aid", Integer), ForeignKeyConstraint(["aid"], ["a.id"])) metadata.create_all(testing.db) if testing.db.dialect.supports_alter: assert_raises_message( exc.CircularDependencyError, "Can't sort tables for DROP; an unresolvable foreign key " "dependency exists between tables: a, b. Please ensure " "that the ForeignKey and ForeignKeyConstraint objects " "involved in the cycle have names so that they can be " "dropped using DROP CONSTRAINT.", metadata.drop_all, testing.db) else: with expect_warnings("Can't sort tables for DROP; an unresolvable " "foreign key dependency "): with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False) asserter.assert_( AllOf(CompiledSQL("DROP TABLE a"), CompiledSQL("DROP TABLE b")))
def _assert_all_selectin(self, q): result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT a.id AS a_id, a.adata AS a_adata, " "a.type AS a_type FROM a ORDER BY a.id", {}), AllOf( EachOf( CompiledSQL( "SELECT asub.id AS asub_id, a.id AS a_id, a.type AS a_type, " "asub.asubdata AS asub_asubdata FROM a JOIN asub " "ON a.id = asub.id WHERE a.id IN ([EXPANDING_primary_keys]) " "ORDER BY a.id", {"primary_keys": [2]}), CompiledSQL( # note this links c.a_sub_id to a.id, even though # primaryjoin is to asub.id. this is because the # cols a.id / asub.id are listed in the mapper's # equivalent_columns so they are guaranteed to store # the same value. "SELECT c.a_sub_id AS c_a_sub_id, " "c.id AS c_id " "FROM c WHERE c.a_sub_id " "IN ([EXPANDING_primary_keys]) ORDER BY c.a_sub_id", {"primary_keys": [2]}), ), CompiledSQL( "SELECT b.a_id AS b_a_id, b.id AS b_id FROM b " "WHERE b.a_id IN ([EXPANDING_primary_keys]) " "ORDER BY b.a_id", {"primary_keys": [1, 2]}))) self.assert_sql_execution( testing.db, lambda: self._run_query(result), )
def test_load_company_plus_employees(self): s = Session() q = s.query(Company).options( selectinload(Company.employees). selectin_polymorphic([Engineer, Manager]) ).order_by(Company.company_id) result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT companies.company_id AS companies_company_id, " "companies.name AS companies_name FROM companies " "ORDER BY companies.company_id", {} ), CompiledSQL( "SELECT companies_1.company_id AS companies_1_company_id, " "people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, people.type AS people_type " "FROM companies AS companies_1 JOIN people " "ON companies_1.company_id = people.company_id " "WHERE companies_1.company_id IN ([EXPANDING_primary_keys]) " "ORDER BY companies_1.company_id, people.person_id", {"primary_keys": [1, 2]} ), AllOf( CompiledSQL( "SELECT managers.person_id AS managers_person_id, " "people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, people.type AS people_type, " "managers.status AS managers_status, " "managers.manager_name AS managers_manager_name " "FROM people JOIN managers " "ON people.person_id = managers.person_id " "WHERE people.person_id IN ([EXPANDING_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [3, 4]} ), CompiledSQL( "SELECT engineers.person_id AS engineers_person_id, " "people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, people.type AS people_type, " "engineers.status AS engineers_status, " "engineers.engineer_name AS engineers_engineer_name, " "engineers.primary_language AS engineers_primary_language " "FROM people JOIN engineers " "ON people.person_id = engineers.person_id " "WHERE people.person_id IN ([EXPANDING_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [1, 2, 5]} ) ) ) eq_(result, [self.c1, self.c2])
def test_drop_ordering_single_table(self): with self.sql_execution_asserter(testing.db) as asserter: for table in self.tables_test_metadata.tables.values(): table.drop(testing.db, checkfirst=False) asserter.assert_( AllOf( CompiledSQL("DROP TABLE t_seq_test_2", {}), EachOf( CompiledSQL("DROP TABLE t_seq_test", {}), CompiledSQL("DROP SEQUENCE t_seq", {}), ), ))
def test_index_create_inline(self): # test an index create using index=True, unique=True metadata = self.metadata events = Table( "events", metadata, Column("id", Integer, primary_key=True), Column("name", String(30), index=True, unique=True), Column("location", String(30), index=True), Column("sport", String(30)), Column("announcer", String(30)), Column("winner", String(30)), ) Index("sport_announcer", events.c.sport, events.c.announcer, unique=True) Index("idx_winners", events.c.winner) eq_( set(ix.name for ix in events.indexes), set([ "ix_events_name", "ix_events_location", "sport_announcer", "idx_winners", ]), ) self.assert_sql_execution( testing.db, lambda: events.create(testing.db), RegexSQL("^CREATE TABLE events"), AllOf( CompiledSQL("CREATE UNIQUE INDEX ix_events_name ON events " "(name)"), CompiledSQL("CREATE INDEX ix_events_location ON events " "(location)"), CompiledSQL("CREATE UNIQUE INDEX sport_announcer ON events " "(sport, announcer)"), CompiledSQL("CREATE INDEX idx_winners ON events (winner)"), ), )
def test_person_selectin_subclasses(self): s = Session() q = s.query(Person).options( selectin_polymorphic(Person, [Engineer, Manager]) ) result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, " "people.type AS people_type FROM people", {}, ), AllOf( CompiledSQL( "SELECT engineers.person_id AS engineers_person_id, " "people.person_id AS people_person_id, " "people.type AS people_type, " "engineers.status AS engineers_status, " "engineers.engineer_name AS engineers_engineer_name, " "engineers.primary_language AS engineers_primary_language " "FROM people JOIN engineers " "ON people.person_id = engineers.person_id " "WHERE people.person_id IN ([EXPANDING_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [1, 2, 5]}, ), CompiledSQL( "SELECT managers.person_id AS managers_person_id, " "people.person_id AS people_person_id, " "people.type AS people_type, " "managers.status AS managers_status, " "managers.manager_name AS managers_manager_name " "FROM people JOIN managers " "ON people.person_id = managers.person_id " "WHERE people.person_id IN ([EXPANDING_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [3, 4]}, ), ), ) eq_(result, self.all_employees)
def _assert_all_selectin(self, q): result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT a.id AS a_id, a.adata AS a_adata, " "a.type AS a_type FROM a ORDER BY a.id", {} ), AllOf( EachOf( CompiledSQL( "SELECT asub.id AS asub_id, a.id AS a_id, a.type AS a_type, " "asub.asubdata AS asub_asubdata FROM a JOIN asub " "ON a.id = asub.id WHERE a.id IN ([EXPANDING_primary_keys]) " "ORDER BY a.id", {"primary_keys": [2]} ), CompiledSQL( "SELECT anon_1.a_id AS anon_1_a_id, c.id AS c_id, " "c.a_sub_id AS c_a_sub_id FROM (SELECT a.id AS a_id, a.adata " "AS a_adata, a.type AS a_type, asub.id AS asub_id, " "asub.asubdata AS asub_asubdata FROM a JOIN asub " "ON a.id = asub.id) AS anon_1 JOIN c " "ON anon_1.asub_id = c.a_sub_id " "WHERE anon_1.a_id IN ([EXPANDING_primary_keys]) " "ORDER BY anon_1.a_id", {"primary_keys": [2]} ), ), CompiledSQL( "SELECT a_1.id AS a_1_id, b.id AS b_id, b.a_id AS b_a_id " "FROM a AS a_1 JOIN b ON a_1.id = b.a_id " "WHERE a_1.id IN ([EXPANDING_primary_keys]) ORDER BY a_1.id", {"primary_keys": [1, 2]} ) ) ) self.assert_sql_execution( testing.db, lambda: self._run_query(result), )
def test_index_create(self): metadata = self.metadata employees = Table( "employees", metadata, Column("id", Integer, primary_key=True), Column("first_name", String(30)), Column("last_name", String(30)), Column("email_address", String(30)), ) i = Index( "employee_name_index", employees.c.last_name, employees.c.first_name, ) assert i in employees.indexes i2 = Index("employee_email_index", employees.c.email_address, unique=True) assert i2 in employees.indexes self.assert_sql_execution( testing.db, lambda: metadata.create_all(testing.db, checkfirst=False), RegexSQL("^CREATE TABLE"), AllOf( CompiledSQL( "CREATE INDEX employee_name_index ON " "employees (last_name, first_name)", [], ), CompiledSQL( "CREATE UNIQUE INDEX employee_email_index ON " "employees (email_address)", [], ), ), )
def test_index_create_inline(self): # test an index create using index=True, unique=True metadata = self.metadata events = Table('events', metadata, Column('id', Integer, primary_key=True), Column('name', String(30), index=True, unique=True), Column('location', String(30), index=True), Column('sport', String(30)), Column('announcer', String(30)), Column('winner', String(30))) Index('sport_announcer', events.c.sport, events.c.announcer, unique=True) Index('idx_winners', events.c.winner) eq_( set(ix.name for ix in events.indexes), set([ 'ix_events_name', 'ix_events_location', 'sport_announcer', 'idx_winners' ])) self.assert_sql_execution( testing.db, lambda: events.create(testing.db), RegexSQL("^CREATE TABLE events"), AllOf( CompiledSQL('CREATE UNIQUE INDEX ix_events_name ON events ' '(name)'), CompiledSQL('CREATE INDEX ix_events_location ON events ' '(location)'), CompiledSQL('CREATE UNIQUE INDEX sport_announcer ON events ' '(sport, announcer)'), CompiledSQL('CREATE INDEX idx_winners ON events (winner)'), ))
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(testing.db, 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_unique_constraint_create(self): metadata = self.metadata Table( "foo", metadata, Column("id", Integer, primary_key=True), Column("value", String(30), unique=True), ) Table( "bar", metadata, Column("id", Integer, primary_key=True), Column("value", String(30)), Column("value2", String(30)), UniqueConstraint("value", "value2", name="uix1"), ) self.assert_sql_execution( testing.db, lambda: metadata.create_all(testing.db, checkfirst=False), AllOf( CompiledSQL("CREATE TABLE foo (" "id INTEGER NOT NULL, " "value VARCHAR(30), " "PRIMARY KEY (id), " "UNIQUE (value)" ")"), CompiledSQL("CREATE TABLE bar (" "id INTEGER NOT NULL, " "value VARCHAR(30), " "value2 VARCHAR(30), " "PRIMARY KEY (id), " "CONSTRAINT uix1 UNIQUE (value, value2)" ")"), ), )
def test_one(self): """Post_update only fires off when needed. This test case used to produce many superfluous update statements, particularly upon delete """ node, Node = self.tables.node, self.classes.Node mapper( Node, node, properties={ 'children': relationship(Node, primaryjoin=node.c.id == node.c.parent_id, cascade="all", backref=backref("parent", remote_side=node.c.id)), 'prev_sibling': relationship(Node, primaryjoin=node.c.prev_sibling_id == node.c.id, remote_side=node.c.id, uselist=False), 'next_sibling': relationship(Node, primaryjoin=node.c.next_sibling_id == node.c.id, remote_side=node.c.id, uselist=False, post_update=True) }) session = create_session() def append_child(parent, child): if parent.children: parent.children[-1].next_sibling = child child.prev_sibling = parent.children[-1] parent.children.append(child) def remove_child(parent, child): child.parent = None node = child.next_sibling node.prev_sibling = child.prev_sibling child.prev_sibling.next_sibling = node session.delete(child) root = Node('root') about = Node('about') cats = Node('cats') stories = Node('stories') bruce = Node('bruce') append_child(root, about) assert (about.prev_sibling is None) append_child(root, cats) assert (cats.prev_sibling is about) assert (cats.next_sibling is None) assert (about.next_sibling is cats) assert (about.prev_sibling is None) append_child(root, stories) append_child(root, bruce) session.add(root) session.flush() remove_child(root, cats) # pre-trigger lazy loader on 'cats' to make the test easier cats.children self.assert_sql_execution( testing.db, session.flush, AllOf( CompiledSQL( "UPDATE node SET prev_sibling_id=:prev_sibling_id " "WHERE node.id = :node_id", lambda ctx: { 'prev_sibling_id': about.id, 'node_id': stories.id }), CompiledSQL( "UPDATE node SET next_sibling_id=:next_sibling_id " "WHERE node.id = :node_id", lambda ctx: { 'next_sibling_id': stories.id, 'node_id': about.id }), CompiledSQL( "UPDATE node SET next_sibling_id=:next_sibling_id " "WHERE node.id = :node_id", lambda ctx: { 'next_sibling_id': None, 'node_id': cats.id }), ), CompiledSQL("DELETE FROM node WHERE node.id = :id", lambda ctx: [{ 'id': cats.id }])) session.delete(root) self.assert_sql_execution( testing.db, session.flush, CompiledSQL( "UPDATE node SET next_sibling_id=:next_sibling_id " "WHERE node.id = :node_id", lambda ctx: [{ 'node_id': about.id, 'next_sibling_id': None }, { 'node_id': stories.id, 'next_sibling_id': None }]), AllOf( CompiledSQL("DELETE FROM node WHERE node.id = :id", lambda ctx: {'id': about.id}), CompiledSQL("DELETE FROM node WHERE node.id = :id", lambda ctx: {'id': stories.id}), CompiledSQL("DELETE FROM node WHERE node.id = :id", lambda ctx: {'id': bruce.id}), ), CompiledSQL("DELETE FROM node WHERE node.id = :id", lambda ctx: {'id': root.id}), ) about = Node('about') cats = Node('cats') about.next_sibling = cats cats.prev_sibling = about session.add(about) session.flush() session.delete(about) cats.prev_sibling = None session.flush()
def test_cycle_unnamed_fks(self): metadata = MetaData() Table( "a", metadata, Column("id", Integer, primary_key=True), Column("bid", Integer, ForeignKey("b.id")), ) Table( "b", metadata, Column("id", Integer, primary_key=True), Column("aid", Integer, ForeignKey("a.id")), ) assertions = [ AllOf( CompiledSQL("CREATE TABLE b (" "id INTEGER NOT NULL, " "aid INTEGER, " "PRIMARY KEY (id)" ")"), CompiledSQL("CREATE TABLE a (" "id INTEGER NOT NULL, " "bid INTEGER, " "PRIMARY KEY (id)" ")"), ), AllOf( CompiledSQL("ALTER TABLE b ADD " "FOREIGN KEY(aid) REFERENCES a (id)"), CompiledSQL("ALTER TABLE a ADD " "FOREIGN KEY(bid) REFERENCES b (id)"), ), ] with self.sql_execution_asserter() as asserter: metadata.create_all(testing.db, checkfirst=False) if testing.db.dialect.supports_alter: asserter.assert_(*assertions) assert_raises_message( exc.CircularDependencyError, "Can't sort tables for DROP; an unresolvable foreign key " "dependency exists between tables: a, b. " "Please ensure that the " "ForeignKey and ForeignKeyConstraint objects involved in the " "cycle have names so that they can be dropped using " "DROP CONSTRAINT.", metadata.drop_all, testing.db, checkfirst=False, ) else: with expect_warnings( "Can't sort tables for DROP; an unresolvable " "foreign key dependency exists between tables"): with self.sql_execution_asserter() as asserter: metadata.drop_all(testing.db, checkfirst=False) asserter.assert_( AllOf(CompiledSQL("DROP TABLE b"), CompiledSQL("DROP TABLE a")))
def test_wp(self, mapping_fixture, connection): BaseClass, A, B = mapping_fixture stmt = union( select(A.id, literal("a").label("type")), select(B.id, literal("b").label("type")), ).subquery() wp = with_polymorphic( BaseClass, [A, B], selectable=stmt, polymorphic_on=stmt.c.type, ) session = Session(connection) with self.sql_execution_asserter() as asserter: result = session.scalars( select(wp).options(selectin_polymorphic(wp, [A, B])).order_by( wp.id)) for obj in result: if isinstance(obj, A): obj.thing1 else: obj.thing2 asserter.assert_( CompiledSQL( "SELECT anon_1.id, anon_1.type FROM " "(SELECT a.id AS id, :param_1 AS type FROM baseclass " "JOIN a ON baseclass.id = a.id " "UNION SELECT b.id AS id, :param_2 AS type " "FROM baseclass JOIN b ON baseclass.id = b.id) AS anon_1 " "ORDER BY anon_1.id", [{ "param_1": "a", "param_2": "b" }], ), AllOf( CompiledSQL( "SELECT a.id AS a_id, baseclass.id AS baseclass_id, " "a.thing1 AS a_thing1 FROM baseclass " "JOIN a ON baseclass.id = a.id " "WHERE baseclass.id IN (__[POSTCOMPILE_primary_keys]) " "ORDER BY baseclass.id", {"primary_keys": [1, 2, 5, 6]}, ), CompiledSQL( "SELECT b.id AS b_id, baseclass.id AS baseclass_id, " "b.thing2 AS b_thing2 FROM baseclass " "JOIN b ON baseclass.id = b.id " "WHERE baseclass.id IN (__[POSTCOMPILE_primary_keys]) " "ORDER BY baseclass.id", {"primary_keys": [3, 4, 7, 8]}, ), ), )
def test_threelevel_selectin_to_inline_awkward_alias_options( self, use_aliased_class): self._fixture_from_geometry({ "a": { "subclasses": { "b": {}, "c": { "subclasses": { "d": {}, "e": {} } }, } } }) a, b, c, d, e = self.classes("a", "b", "c", "d", "e") sess = fixture_session() sess.add_all([d(d_data="d1"), e(e_data="e1")]) sess.commit() from sqlalchemy import select a_table, c_table, d_table, e_table = self.tables("a", "c", "d", "e") poly = (select( a_table.c.id, a_table.c.type, c_table, d_table, e_table).select_from( a_table.join(c_table).outerjoin(d_table).outerjoin(e_table)). set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).alias("poly")) c_alias = with_polymorphic(c, (d, e), poly) if use_aliased_class: opt = selectin_polymorphic(a, [b, c_alias]) else: opt = selectin_polymorphic( a, [b, c_alias, d, e], ) q = sess.query(a).options(opt).order_by(a.id) if use_aliased_class: result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT a.id AS a_id, a.type AS a_type, " "a.a_data AS a_a_data FROM a ORDER BY a.id", {}, ), Or( # here, the test is that the adaptation of "a" takes place CompiledSQL( "SELECT poly.c_id AS poly_c_id, " "poly.a_type AS poly_a_type, " "poly.a_id AS poly_a_id, poly.c_c_data " "AS poly_c_c_data, " "poly.e_id AS poly_e_id, poly.e_e_data " "AS poly_e_e_data, " "poly.d_id AS poly_d_id, poly.d_d_data " "AS poly_d_d_data " "FROM (SELECT a.id AS a_id, a.type AS a_type, " "c.id AS c_id, " "c.c_data AS c_c_data, d.id AS d_id, " "d.d_data AS d_d_data, " "e.id AS e_id, e.e_data AS e_e_data FROM a JOIN c " "ON a.id = c.id LEFT OUTER JOIN d ON c.id = d.id " "LEFT OUTER JOIN e ON c.id = e.id) AS poly " "WHERE poly.a_id IN (__[POSTCOMPILE_primary_keys]) " "ORDER BY poly.a_id", [{ "primary_keys": [1, 2] }], ), CompiledSQL( "SELECT poly.c_id AS poly_c_id, " "poly.a_id AS poly_a_id, poly.a_type AS poly_a_type, " "poly.c_c_data AS poly_c_c_data, " "poly.d_id AS poly_d_id, poly.d_d_data " "AS poly_d_d_data, " "poly.e_id AS poly_e_id, poly.e_e_data " "AS poly_e_e_data " "FROM (SELECT a.id AS a_id, a.type AS a_type, " "c.id AS c_id, c.c_data AS c_c_data, d.id AS d_id, " "d.d_data AS d_d_data, e.id AS e_id, " "e.e_data AS e_e_data FROM a JOIN c ON a.id = c.id " "LEFT OUTER JOIN d ON c.id = d.id " "LEFT OUTER JOIN e ON c.id = e.id) AS poly " "WHERE poly.a_id IN (__[POSTCOMPILE_primary_keys]) " "ORDER BY poly.a_id", [{ "primary_keys": [1, 2] }], ), ), ) else: result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT a.id AS a_id, a.type AS a_type, " "a.a_data AS a_a_data FROM a ORDER BY a.id", {}, ), AllOf( CompiledSQL( "SELECT d.id AS d_id, c.id AS c_id, a.id AS a_id, " "a.type AS a_type, d.d_data AS d_d_data FROM a " "JOIN c ON a.id = c.id JOIN d ON c.id = d.id " "WHERE a.id IN (__[POSTCOMPILE_primary_keys]) " "ORDER BY a.id", [{ "primary_keys": [1] }], ), CompiledSQL( "SELECT e.id AS e_id, c.id AS c_id, a.id AS a_id, " "a.type AS a_type, e.e_data AS e_e_data FROM a " "JOIN c ON a.id = c.id JOIN e ON c.id = e.id " "WHERE a.id IN (__[POSTCOMPILE_primary_keys]) " "ORDER BY a.id", [{ "primary_keys": [2] }], ), ), ) with self.assert_statement_count(testing.db, 0): eq_(result, [d(d_data="d1"), e(e_data="e1")])