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