def test_person_selectin_subclasses(self):
s = fixture_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 ([POSTCOMPILE_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 ([POSTCOMPILE_primary_keys]) "
"ORDER BY people.person_id",
{"primary_keys": [3, 4]},
),
),
)
eq_(result, self.all_employees)
def test_execute_full_uncached(self, user_address_fixture):
users, addresses = user_address_fixture
def go(name):
stmt = lambda_stmt(
lambda: select(users.c.id).where(users.c.name == name) # noqa
)
with testing.db.connect().execution_options(
compiled_cache=None) as conn:
conn.execute(stmt)
with self.sql_execution_asserter(testing.db) as asserter:
go("name1")
go("name2")
go("name1")
go("name3")
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{
"name_1": "name1"
}],
),
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{
"name_1": "name2"
}],
),
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{
"name_1": "name1"
}],
),
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{
"name_1": "name3"
}],
),
)
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_update_fetch_returning(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
with self.sql_execution_asserter() as asserter:
sess.query(User).filter(User.age > 29).update(
{"age": User.age - 10}, synchronize_session="fetch")
# these are simple values, these are now evaluated even with
# the "fetch" strategy, new in 1.4, so there is no expiry
eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27])
if testing.db.dialect.full_returning:
asserter.assert_(
CompiledSQL(
"UPDATE users SET age_int=(users.age_int - %(age_int_1)s) "
"WHERE users.age_int > %(age_int_2)s RETURNING users.id",
[{
"age_int_1": 10,
"age_int_2": 29
}],
dialect="postgresql",
), )
else:
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users "
"WHERE users.age_int > :age_int_1",
[{
"age_int_1": 29
}],
),
CompiledSQL(
"UPDATE users SET age_int=(users.age_int - :age_int_1) "
"WHERE users.age_int > :age_int_2",
[{
"age_int_1": 10,
"age_int_2": 29
}],
),
)
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 ([POSTCOMPILE_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 ([POSTCOMPILE_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 ([POSTCOMPILE_primary_keys]) "
"ORDER BY b.a_id",
{"primary_keys": [1, 2]},
),
),
)
self.assert_sql_execution(testing.db, lambda: self._run_query(result))
def test_all_subq_query(self):
A, B, B2, C, C2, D = self.classes('A', 'B', 'B2', 'C', 'C2', 'D')
session = Session(testing.db)
b_b2 = with_polymorphic(B, [B2], flat=True)
c_c2 = with_polymorphic(C, [C2], flat=True)
q = session.query(A).options(
subqueryload(A.bs.of_type(b_b2)).subqueryload(
b_b2.cs.of_type(c_c2)).subqueryload(c_c2.ds))
self.assert_sql_execution(
testing.db, q.all,
CompiledSQL("SELECT t_a.id AS t_a_id FROM t_a", {}),
CompiledSQL(
"SELECT t_b_1.type AS t_b_1_type, t_b_1.id AS t_b_1_id, "
"t_b_1.a_id AS t_b_1_a_id, t_b2_1.id AS t_b2_1_id, "
"anon_1.t_a_id AS anon_1_t_a_id FROM "
"(SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 "
"JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
"ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id "
"ORDER BY anon_1.t_a_id", {}),
CompiledSQL(
"SELECT t_c_1.type AS t_c_1_type, t_c_1.id AS t_c_1_id, "
"t_c_1.b_id AS t_c_1_b_id, t_c2_1.id AS t_c2_1_id, "
"t_b_1.id AS t_b_1_id FROM (SELECT t_a.id AS t_a_id FROM t_a) "
"AS anon_1 JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
"ON t_b_1.id = t_b2_1.id) ON anon_1.t_a_id = t_b_1.a_id "
"JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 ON "
"t_c_1.id = t_c2_1.id) ON t_b_1.id = t_c_1.b_id "
"ORDER BY t_b_1.id", {}),
CompiledSQL(
"SELECT t_d.id AS t_d_id, t_d.c_id AS t_d_c_id, "
"t_c_1.id AS t_c_1_id "
"FROM (SELECT t_a.id AS t_a_id FROM t_a) AS anon_1 "
"JOIN (t_b AS t_b_1 LEFT OUTER JOIN t_b2 AS t_b2_1 "
"ON t_b_1.id = t_b2_1.id) "
"ON anon_1.t_a_id = t_b_1.a_id "
"JOIN (t_c AS t_c_1 LEFT OUTER JOIN t_c2 AS t_c2_1 "
"ON t_c_1.id = t_c2_1.id) "
"ON t_b_1.id = t_c_1.b_id "
"JOIN t_d ON t_c_1.id = t_d.c_id ORDER BY t_c_1.id", {}))
def test_selectinload_local_criteria(self, user_address_fixture):
User, Address = user_address_fixture
s = Session(testing.db, future=True)
stmt = (
select(User)
.options(
selectinload(
User.addresses.and_(Address.email_address != "*****@*****.**")
),
)
.order_by(User.id)
)
with self.sql_execution_asserter() as asserter:
result = s.execute(stmt)
eq_(
result.scalars().unique().all(),
self._user_minus_edwood(*user_address_fixture),
)
asserter.assert_(
CompiledSQL(
"SELECT users.id, users.name FROM users ORDER BY users.id"
),
CompiledSQL(
"SELECT addresses.user_id AS addresses_user_id, "
"addresses.id AS addresses_id, addresses.email_address "
"AS addresses_email_address FROM addresses "
"WHERE addresses.user_id IN ([POSTCOMPILE_primary_keys]) "
"AND addresses.email_address != :email_address_1 "
"ORDER BY addresses.id",
[
{
"primary_keys": [7, 8, 9, 10],
"email_address_1": "*****@*****.**",
}
],
),
)
def test_update_attrs(self):
asserter = self._test_update(self.classes.PersonAttrs)
asserter.assert_(
CompiledSQL(
"UPDATE people_attrs SET name=:name "
"WHERE people_attrs.person_id = :people_attrs_person_id",
[{
'name': 'newname',
'people_attrs_person_id': 5
}]), )
def test_issue_6793(self):
User = self.classes.User
session = fixture_session()
with self.sql_execution_asserter() as asserter:
session.bulk_save_objects([User(name="A"), User(name="B")])
session.add(User(name="C"))
session.add(User(name="D"))
session.flush()
asserter.assert_(
Conditional(
testing.db.dialect.insert_executemany_returning,
[
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{"name": "A"}, {"name": "B"}],
),
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{"name": "C"}, {"name": "D"}],
),
],
[
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{"name": "A"}, {"name": "B"}],
),
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{"name": "C"}],
),
CompiledSQL(
"INSERT INTO users (name) VALUES (:name)",
[{"name": "D"}],
),
],
)
)
def test_update_both(self):
# want to make sure that before [ticket:3849], this did not have
# a successful behavior or workaround
asserter = self._test_update(self.classes.PersonBoth)
asserter.assert_(
CompiledSQL(
"UPDATE people_both SET name=:name_key "
"WHERE people_both.person_id = :people_both_person_id",
[{"name_key": "newname", "people_both_person_id": 5}],
)
)
def test_insert_both(self):
asserter = self._test_insert(self.classes.PersonBoth)
asserter.assert_(
CompiledSQL(
"INSERT INTO people_both (person_id, name) "
"VALUES (:id_key, :name_key)",
[{
"id_key": 5,
"name_key": "thename"
}],
))
def test_insert_keys(self):
asserter = self._test_insert(self.classes.PersonKeys)
asserter.assert_(
CompiledSQL(
"INSERT INTO people_keys (person_id, name) "
"VALUES (:id, :personname)",
[{
"id": 5,
"personname": "thename"
}],
))
def test_update_attrs(self):
asserter = self._test_update(self.classes.PersonAttrs)
asserter.assert_(
CompiledSQL(
"UPDATE people_attrs SET name=:name "
"WHERE people_attrs.person_id = :people_attrs_person_id",
[{
"name": "newname",
"people_attrs_person_id": 5
}],
))
def test_insert_attrs(self):
asserter = self._test_insert(self.classes.PersonAttrs)
asserter.assert_(
CompiledSQL(
"INSERT INTO people_attrs (person_id, name) "
"VALUES (:person_id, :name)",
[{
"person_id": 5,
"name": "thename"
}],
))
def test_drop_ordering(self):
with self.sql_execution_asserter(testing.db) as asserter:
self.metadata.drop_all(checkfirst=False)
asserter.assert_(
AllOf(
CompiledSQL("DROP TABLE t_seq_test_2", {}),
EachOf(
CompiledSQL("DROP TABLE t_seq_test", {}),
CompiledSQL(
"DROP SEQUENCE t_seq", # dropped as part of t_seq_test
{},
),
),
),
CompiledSQL(
"DROP SEQUENCE t_seq_2", # dropped as part of metadata level
{},
),
)
def test_pk_fk_constraint_create(self):
metadata = self.metadata
Table(
"employees",
metadata,
Column("id", Integer),
Column("soc", String(40)),
Column("name", String(30)),
PrimaryKeyConstraint("id", "soc"),
)
Table(
"elements",
metadata,
Column("id", Integer),
Column("stuff", String(30)),
Column("emp_id", Integer),
Column("emp_soc", String(40)),
PrimaryKeyConstraint("id", name="elements_primkey"),
ForeignKeyConstraint(["emp_id", "emp_soc"],
["employees.id", "employees.soc"]),
)
self.assert_sql_execution(
testing.db,
lambda: metadata.create_all(testing.db, checkfirst=False),
CompiledSQL("CREATE TABLE employees ("
"id INTEGER NOT NULL, "
"soc VARCHAR(40) NOT NULL, "
"name VARCHAR(30), "
"PRIMARY KEY (id, soc)"
")"),
CompiledSQL("CREATE TABLE elements ("
"id INTEGER NOT NULL, "
"stuff VARCHAR(30), "
"emp_id INTEGER, "
"emp_soc VARCHAR(40), "
"CONSTRAINT elements_primkey PRIMARY KEY (id), "
"FOREIGN KEY(emp_id, emp_soc) "
"REFERENCES employees (id, soc)"
")"),
)
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_delete_fetch_returning(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
in_(john, sess)
in_(jack, sess)
with self.sql_execution_asserter() as asserter:
sess.query(User).filter(User.age > 29).delete(
synchronize_session="fetch"
)
if testing.db.dialect.full_returning:
asserter.assert_(
CompiledSQL(
"DELETE FROM users WHERE users.age_int > %(age_int_1)s "
"RETURNING users.id",
[{"age_int_1": 29}],
dialect="postgresql",
),
)
else:
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users "
"WHERE users.age_int > :age_int_1",
[{"age_int_1": 29}],
),
CompiledSQL(
"DELETE FROM users WHERE users.age_int > :age_int_1",
[{"age_int_1": 29}],
),
)
in_(john, sess)
not_in_(jack, sess)
in_(jill, sess)
not_in_(jane, sess)
def test_update_keys(self, type_):
if type_ == "states":
asserter = self._test_update_states(self.classes.PersonKeys)
else:
asserter = self._test_update(self.classes.PersonKeys)
asserter.assert_(
CompiledSQL(
"UPDATE people_keys SET name=:personname "
"WHERE people_keys.person_id = :people_keys_person_id",
[{"personname": "newname", "people_keys_person_id": 5}],
)
)
def test_subqueryload_local_criteria(self, user_address_fixture):
User, Address = user_address_fixture
s = Session(testing.db, future=True)
stmt = (
select(User)
.options(
subqueryload(
User.addresses.and_(Address.email_address != "*****@*****.**")
),
)
.order_by(User.id)
)
with self.sql_execution_asserter() as asserter:
result = s.execute(stmt)
eq_(
result.scalars().unique().all(),
self._user_minus_edwood(*user_address_fixture),
)
asserter.assert_(
CompiledSQL(
"SELECT users.id, users.name FROM users ORDER BY users.id"
),
CompiledSQL(
"SELECT addresses.id AS addresses_id, addresses.user_id "
"AS addresses_user_id, addresses.email_address "
"AS addresses_email_address, anon_1.users_id "
"AS anon_1_users_id FROM (SELECT users.id AS users_id "
"FROM users) AS anon_1 JOIN addresses ON anon_1.users_id = "
"addresses.user_id AND "
"addresses.email_address != :email_address_1 "
"ORDER BY addresses.id",
[{"email_address_1": "*****@*****.**"}],
),
)
def test_noload_remove(self):
# test that a load of User.addresses is not emitted
# when flushing a remove
User, Address = self._user_address_fixture()
sess = Session()
u1 = User(name="jack", addresses=[Address(email_address="a1")])
a2 = Address(email_address='a2')
u1.addresses.append(a2)
sess.add(u1)
sess.commit()
u1_id = u1.id
a2_id = a2.id
sess.expire_all()
u1.addresses.remove(a2)
self.assert_sql_execution(
testing.db,
sess.flush,
CompiledSQL(
"SELECT addresses.id AS addresses_id, addresses.email_address "
"AS addresses_email_address FROM addresses "
"WHERE addresses.id = :param_1", lambda ctx: [{
'param_1': a2_id
}]),
CompiledSQL(
"UPDATE addresses SET user_id=:user_id WHERE addresses.id = "
":addresses_id", lambda ctx: [{
'addresses_id': a2_id,
'user_id': None
}]),
CompiledSQL(
"SELECT users.id AS users_id, users.name AS users_name "
"FROM users WHERE users.id = :param_1",
lambda ctx: [{
"param_1": u1_id
}]),
)
def _assert_cyclic_constraint(self, metadata):
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)'
')'),
]
if testing.db.dialect.supports_alter:
assertions.append(
CompiledSQL('ALTER TABLE b ADD CONSTRAINT bfk '
'FOREIGN KEY(aid) REFERENCES a (id)'))
self.assert_sql_execution(
testing.db, lambda: metadata.create_all(checkfirst=False),
*assertions)
assertions = []
if testing.db.dialect.supports_alter:
assertions.append(CompiledSQL('ALTER TABLE b DROP CONSTRAINT bfk'))
assertions.extend([
CompiledSQL("DROP TABLE a"),
CompiledSQL("DROP TABLE b"),
])
self.assert_sql_execution(testing.db,
lambda: metadata.drop_all(checkfirst=False),
*assertions)
def test_index_functional_create(self):
metadata = self.metadata
t = Table('sometable', metadata, Column('id',
Integer,
primary_key=True),
Column('data', String(50)))
Index('myindex', t.c.data.desc())
self.assert_sql_execution(
testing.db, lambda: t.create(testing.db),
CompiledSQL('CREATE TABLE sometable (id INTEGER NOT NULL, '
'data VARCHAR(50), PRIMARY KEY (id))'),
ExactSQL('CREATE INDEX myindex ON sometable (data DESC)'))
def test_pk_fk_constraint_create(self):
metadata = self.metadata
Table('employees', metadata,
Column('id', Integer),
Column('soc', String(40)),
Column('name', String(30)),
PrimaryKeyConstraint('id', 'soc')
)
Table('elements', metadata,
Column('id', Integer),
Column('stuff', String(30)),
Column('emp_id', Integer),
Column('emp_soc', String(40)),
PrimaryKeyConstraint('id', name='elements_primkey'),
ForeignKeyConstraint(['emp_id', 'emp_soc'],
['employees.id', 'employees.soc'])
)
self.assert_sql_execution(
testing.db,
lambda: metadata.create_all(checkfirst=False),
CompiledSQL('CREATE TABLE employees ('
'id INTEGER NOT NULL, '
'soc VARCHAR(40) NOT NULL, '
'name VARCHAR(30), '
'PRIMARY KEY (id, soc)'
')'
),
CompiledSQL('CREATE TABLE elements ('
'id INTEGER NOT NULL, '
'stuff VARCHAR(30), '
'emp_id INTEGER, '
'emp_soc VARCHAR(40), '
'CONSTRAINT elements_primkey PRIMARY KEY (id), '
'FOREIGN KEY(emp_id, emp_soc) '
'REFERENCES employees (id, soc)'
')'
)
)
def test_execute_constructed_cached(self, user_address_fixture):
users, addresses = user_address_fixture
cache = {}
def go(name):
stmt = select([lambda: users.c.id]).where(
lambda: users.c.name == name
)
with testing.db.connect().execution_options(
compiled_cache=cache
) as conn:
conn.execute(stmt)
with self.sql_execution_asserter(testing.db) as asserter:
go("name1")
go("name2")
go("name1")
go("name3")
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{"name_1": "name1"}],
),
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{"name_1": "name2"}],
),
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{"name_1": "name1"}],
),
CompiledSQL(
"SELECT users.id FROM users WHERE users.name = :name_1",
lambda ctx: [{"name_1": "name3"}],
),
)
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_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 no_opt():
q = session.query(Parent).options(
joinedload(Parent.children.of_type(ChildSubclass1)))
return self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT parent.id AS parent_id, "
"anon_1.child_id AS anon_1_child_id, "
"anon_1.child_parent_id AS anon_1_child_parent_id, "
"anon_1.child_type AS anon_1_child_type, "
"anon_1.child_subclass1_id AS anon_1_child_subclass1_id "
"FROM parent "
"LEFT OUTER JOIN (SELECT child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type, "
"child_subclass1.id AS child_subclass1_id "
"FROM child "
"LEFT OUTER JOIN child_subclass1 "
"ON child.id = child_subclass1.id) AS anon_1 "
"ON parent.id = anon_1.child_parent_id",
{},
),
CompiledSQL(
"SELECT child_subclass1.id AS child_subclass1_id, "
"child.id AS child_id, "
"child.parent_id AS child_parent_id, "
"child.type AS child_type "
"FROM child JOIN child_subclass1 "
"ON child.id = child_subclass1.id "
"WHERE child.id IN ([POSTCOMPILE_primary_keys]) "
"ORDER BY child.id",
[{
"primary_keys": [1]
}],
),
)
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)"
")"),
),
)