def test_bulk_save_return_defaults(self):
(User, ) = self.classes("User")
s = Session()
objects = [User(name="u1"), User(name="u2"), User(name="u3")]
assert "id" not in objects[0].__dict__
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, return_defaults=True)
asserter.assert_(
CompiledSQL("INSERT INTO users (name) VALUES (:name)",
[{
"name": "u1"
}]),
CompiledSQL("INSERT INTO users (name) VALUES (:name)",
[{
"name": "u2"
}]),
CompiledSQL("INSERT INTO users (name) VALUES (:name)",
[{
"name": "u3"
}]),
)
eq_(objects[0].__dict__["id"], 1)
def test_noload_append(self):
# test that a load of User.addresses is not emitted
# when flushing an append
User, Address = self._user_address_fixture()
sess = Session()
u1 = User(name="jack", addresses=[Address(email_address="a1")])
sess.add(u1)
sess.commit()
u1_id = u1.id
sess.expire_all()
u1.addresses.append(Address(email_address="a2"))
self.assert_sql_execution(
testing.db,
sess.flush,
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
}],
),
CompiledSQL(
"INSERT INTO addresses (user_id, email_address) "
"VALUES (:user_id, :email_address)",
lambda ctx: [{
"email_address": "a2",
"user_id": u1_id
}],
),
)
def test_threelevel_selectin_to_inline_mapped(self):
self._fixture_from_geometry(
{
"a": {
"subclasses": {
"b": {"polymorphic_load": "selectin"},
"c": {
"subclasses": {
"d": {
"polymorphic_load": "inline",
"single": True,
},
"e": {
"polymorphic_load": "inline",
"single": True,
},
},
"polymorphic_load": "selectin",
},
}
}
}
)
a, b, c, d, e = self.classes("a", "b", "c", "d", "e")
sess = Session()
sess.add_all([d(d_data="d1"), e(e_data="e1")])
sess.commit()
q = sess.query(a)
result = self.assert_sql_execution(
testing.db,
q.all,
CompiledSQL(
"SELECT a.type AS a_type, a.id AS a_id, "
"a.a_data AS a_a_data FROM a",
{},
),
Or(
CompiledSQL(
"SELECT a.type AS a_type, c.id AS c_id, a.id AS a_id, "
"c.c_data AS c_c_data, c.e_data AS c_e_data, "
"c.d_data AS c_d_data "
"FROM a JOIN c ON a.id = c.id "
"WHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id",
[{"primary_keys": [1, 2]}],
),
CompiledSQL(
"SELECT a.type AS a_type, c.id AS c_id, a.id AS a_id, "
"c.c_data AS c_c_data, "
"c.d_data AS c_d_data, c.e_data AS c_e_data "
"FROM a JOIN c ON a.id = c.id "
"WHERE a.id IN ([EXPANDING_primary_keys]) ORDER BY a.id",
[{"primary_keys": [1, 2]}],
),
),
)
with self.assert_statement_count(testing.db, 0):
eq_(result, [d(d_data="d1"), e(e_data="e1")])
def test_selectinload(self):
A, B = self.classes("A", "B")
sess = Session()
with self.sql_execution_asserter() as asserter:
# note this is many-to-one. use_get is unconditionally turned
# off for relationship to aliased class for now.
a1 = sess.query(A).options(selectinload(A.b)).first()
eq_(a1.b, B(id=1))
asserter.assert_(
CompiledSQL(
"SELECT a.id AS a_id, a.b_id AS a_b_id "
"FROM a LIMIT :param_1",
[{
"param_1": 1
}],
),
CompiledSQL(
"SELECT a_1.id AS a_1_id, b.id AS b_id FROM a AS a_1 "
"JOIN (b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) "
"ON a_1.b_id = b.id WHERE a_1.id "
"IN ([EXPANDING_primary_keys])",
[{
"primary_keys": [1]
}],
),
)
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 _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(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(checkfirst=False),
else:
with self.sql_execution_asserter() as asserter:
metadata.drop_all(checkfirst=False),
asserter.assert_(*assertions)
def test_cycle_named_fks(self):
metadata = MetaData(testing.db)
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(checkfirst=False)
if testing.db.dialect.supports_alter:
asserter.assert_(*assertions)
with self.sql_execution_asserter() as asserter:
metadata.drop_all(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(checkfirst=False)
asserter.assert_(
AllOf(CompiledSQL("DROP TABLE b"),
CompiledSQL("DROP TABLE a")))
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 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",
{},
),
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",
{},
),
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",
{},
),
)
def test_bulk_save_updated_include_unchanged(self):
(User, ) = self.classes("User")
s = Session(expire_on_commit=False)
objects = [User(name="u1"), User(name="u2"), User(name="u3")]
s.add_all(objects)
s.commit()
objects[0].name = "u1new"
objects[2].name = "u3new"
s = Session()
with self.sql_execution_asserter() as asserter:
s.bulk_save_objects(objects, update_changed_only=False)
asserter.assert_(
CompiledSQL(
"UPDATE users SET name=:name WHERE "
"users.id = :users_id",
[
{
"users_id": 1,
"name": "u1new"
},
{
"users_id": 2,
"name": "u2"
},
{
"users_id": 3,
"name": "u3new"
},
],
))
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))"),
CompiledSQL("CREATE INDEX myindex ON sometable (data DESC)"),
)
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_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 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(
# 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])",
{"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])",
{"primary_keys": [1, 2]},
),
),
)
self.assert_sql_execution(testing.db, lambda: self._run_query(result))
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_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_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_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_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_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_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_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_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 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 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 ([EXPANDING_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(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_bulk_update(self):
(User, ) = self.classes("User")
s = Session(expire_on_commit=False)
objects = [User(name="u1"), User(name="u2"), User(name="u3")]
s.add_all(objects)
s.commit()
s = Session()
with self.sql_execution_asserter() as asserter:
s.bulk_update_mappings(
User,
[
{
"id": 1,
"name": "u1new"
},
{
"id": 2,
"name": "u2"
},
{
"id": 3,
"name": "u3new"
},
],
)
asserter.assert_(
CompiledSQL(
"UPDATE users SET name=:name WHERE users.id = :users_id",
[
{
"users_id": 1,
"name": "u1new"
},
{
"users_id": 2,
"name": "u2"
},
{
"users_id": 3,
"name": "u3new"
},
],
))
def test_useget_cancels_eager_propagated_present(self):
"""test that a one to many lazyload cancels the unnecessary
eager many-to-one join on the other side, even when a propagated
option is present."""
User = self.classes.User
Address = self.classes.Address
mapper(User, self.tables.users)
mapper(
Address,
self.tables.addresses,
properties={
"user":
relationship(
User,
lazy="joined",
backref=backref("addresses", lazy="baked_select"),
)
},
)
from sqlalchemy_1_3.orm.interfaces import MapperOption
class MyBogusOption(MapperOption):
propagate_to_loaders = True
sess = Session()
u1 = (sess.query(User).options(
MyBogusOption()).filter(User.id == 8).one())
def go():
eq_(u1.addresses[0].user, u1)
self.assert_sql_execution(
testing.db,
go,
CompiledSQL(
"SELECT addresses.id AS addresses_id, addresses.user_id AS "
"addresses_user_id, addresses.email_address AS "
"addresses_email_address FROM addresses WHERE :param_1 = "
"addresses.user_id",
{"param_1": 8},
),
)
def test_lazyload(self):
A, B = self.classes("A", "B")
sess = Session()
a1 = sess.query(A).first()
with self.sql_execution_asserter() as asserter:
# note this is many-to-one. use_get is unconditionally turned
# off for relationship to aliased class for now.
eq_(a1.b, B(id=1))
asserter.assert_(
CompiledSQL(
"SELECT b.id AS b_id FROM b JOIN d ON d.b_id = b.id "
"JOIN c ON c.id = d.c_id WHERE :param_1 = b.id",
[{
"param_1": 1
}],
))
