def test_threelevel_selectin_to_inline_options(self):
self._fixture_from_geometry({
"a": {
"subclasses": {
"b": {},
"c": {
"subclasses": {
"d": {
"single": True
},
"e": {
"single": True
},
}
},
}
}
})
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()
c_alias = with_polymorphic(c, (d, e))
q = sess.query(a).options(selectin_polymorphic(a, [b, c_alias]))
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 ([POSTCOMPILE_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 ([POSTCOMPILE_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 _get_eagerloaded_query(self, *args, **kwargs):
"""Eager hostnames loading.
This is too complex to get_joinedloads so I have to
override the function
"""
query = super(VulnerabilityView,
self)._get_eagerloaded_query(*args, **kwargs)
joinedloads = [
joinedload(Vulnerability.host).load_only(
Host.id) # Only hostnames are needed
.joinedload(Host.hostnames),
joinedload(Vulnerability.service).joinedload(
Service.host).joinedload(Host.hostnames),
joinedload(VulnerabilityWeb.service).joinedload(
Service.host).joinedload(Host.hostnames),
joinedload(VulnerabilityGeneric.update_user),
undefer(VulnerabilityGeneric.creator_command_id),
undefer(VulnerabilityGeneric.creator_command_tool),
undefer(VulnerabilityGeneric.target_host_ip),
undefer(VulnerabilityGeneric.target_host_os),
joinedload(VulnerabilityGeneric.evidence),
joinedload(VulnerabilityGeneric.tags),
]
return query.options(
selectin_polymorphic(VulnerabilityGeneric,
[Vulnerability, VulnerabilityWeb]),
*joinedloads)
def _get_eagerloaded_query(self, *args, **kwargs):
"""Eager hostnames loading.
This is too complex to get_joinedloads so I have to
override the function
"""
query = super(VulnerabilityView, self)._get_eagerloaded_query(
*args, **kwargs)
joinedloads = [
joinedload(Vulnerability.host)
.load_only(Host.id) # Only hostnames are needed
.joinedload(Host.hostnames),
joinedload(Vulnerability.service)
.joinedload(Service.host)
.joinedload(Host.hostnames),
joinedload(VulnerabilityWeb.service)
.joinedload(Service.host)
.joinedload(Host.hostnames),
joinedload(VulnerabilityGeneric.update_user),
undefer(VulnerabilityGeneric.creator_command_id),
undefer(VulnerabilityGeneric.creator_command_tool),
undefer(VulnerabilityGeneric.target_host_ip),
undefer(VulnerabilityGeneric.target_host_os),
joinedload(VulnerabilityGeneric.evidence),
joinedload(VulnerabilityGeneric.tags),
]
return query.options(selectin_polymorphic(
VulnerabilityGeneric,
[Vulnerability, VulnerabilityWeb]
), *joinedloads)
def lugar(cls, session, lid):
query = session.query(Lugar).options(
selectin_polymorphic(Lugar, [
Direccion, Escuela, LugarDictado, Secretaria, Instituto,
Prosecretaria, Maestria, Catedra
]), joinedload(Catedra.materia))
return query.filter(Lugar.id == lid).one_or_none()
def test_load(self):
A, B, ASub, C = self.classes("A", "B", "ASub", "C")
s = Session()
q = s.query(A).order_by(A.id).options(selectin_polymorphic(A, [ASub]),
selectinload(ASub.cs),
selectinload(A.bs))
self._assert_all_selectin(q)
def test_load(self):
A, B, ASub, C = self.classes("A", "B", "ASub", "C")
s = Session()
q = s.query(A).order_by(A.id).options(
selectin_polymorphic(A, [ASub]),
selectinload(ASub.cs),
selectinload(A.bs)
)
self._assert_all_selectin(q)
def test_threelevel_selectin_to_inline_options(self):
self._fixture_from_geometry(
{
"a": {
"subclasses": {
"b": {},
"c": {
"subclasses": {
"d": {"single": True},
"e": {"single": True},
}
},
}
}
}
)
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()
c_alias = with_polymorphic(c, (d, e))
q = sess.query(a).options(selectin_polymorphic(a, [b, c_alias]))
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_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 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 test_threelevel_selectin_to_inline_awkward_alias_options(self):
self._fixture_from_geometry(
{
"a": {
"subclasses": {
"b": {},
"c": {"subclasses": {"d": {}, "e": {}}},
}
}
}
)
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()
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)
)
.apply_labels()
.alias("poly")
)
c_alias = with_polymorphic(c, (d, e), poly)
q = (
sess.query(a)
.options(selectin_polymorphic(a, [b, c_alias]))
.order_by(a.id)
)
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 ORDER BY a.id",
{},
),
Or(
# here, the test is that the adaptation of "a" takes place
CompiledSQL(
"SELECT poly.a_type AS poly_a_type, "
"poly.c_id AS poly_c_id, "
"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 ([EXPANDING_primary_keys]) "
"ORDER BY poly.a_id",
[{"primary_keys": [1, 2]}],
),
CompiledSQL(
"SELECT poly.a_type AS poly_a_type, "
"poly.c_id AS poly_c_id, "
"poly.a_id AS poly_a_id, 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 ([EXPANDING_primary_keys]) "
"ORDER BY poly.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_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):
self._fixture_from_geometry({
"a": {
"subclasses": {
"b": {},
"c": {
"subclasses": {
"d": {},
"e": {},
},
}
}
}
})
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()
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)
).apply_labels().alias('poly')
c_alias = with_polymorphic(c, (d, e), poly)
q = sess.query(a).options(
selectin_polymorphic(a, [b, c_alias])
).order_by(a.id)
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 ORDER BY a.id",
{}
),
Or(
# here, the test is that the adaptation of "a" takes place
CompiledSQL(
"SELECT poly.a_type AS poly_a_type, "
"poly.c_id AS poly_c_id, "
"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 ([EXPANDING_primary_keys]) "
"ORDER BY poly.a_id",
[{'primary_keys': [1, 2]}]
),
CompiledSQL(
"SELECT poly.a_type AS poly_a_type, "
"poly.c_id AS poly_c_id, "
"poly.a_id AS poly_a_id, 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 ([EXPANDING_primary_keys]) "
"ORDER BY poly.a_id",
[{'primary_keys': [1, 2]}]
)
)
)
with self.assert_statement_count(testing.db, 0):
eq_(
result,
[d(d_data="d1"), e(e_data="e1")]
)
async def management_changes(self, request):
pms = PerformanceMeasurementSystem(
enable=request.query.get("pms", False))
async with self._session(request) as session:
f = []
for value in request.query.getall("q", []):
# JSON Patch query for value regex like
# FIXME … though ' is taken care of " in q still breaks it but everything below does not work either
v = sqlalchemy.String().literal_processor(
dialect=session._proxied.bind.dialect)(value=value)
v = v.replace('"', ".")
f.append(
Change.data.op("@@")(sqlalchemy.text(
f"'$[*].value like_regex \"{v[1:-1]}\"'::jsonpath")))
# This text() construct doesn't define a bound parameter named 'n'
# f.append(Change.data.op('@@')(
# sqlalchemy.text('\'$[*].value like_regex \"\:n\"\'::jsonpath').bindparams(n=value)))
# the server expects 0 arguments for this query, 1 was passed
# f.append(Change.data.op('@@')(
# sqlalchemy.text('\'$[*].value like_regex ":n"\'::jsonpath').params(n=value)))
# the resultset is incomplete
# f.append(Change.data.op('@@')(
# sqlalchemy.text('\'$[*].value like_regex \"\:n\"\'::jsonpath').params(n=value)))
# remote host ipaddress cidr query
try:
import ipaddress
ipaddress.ip_interface(value)
f.append(
Change.remote_host.op("<<=")(sqlalchemy.cast(
value, sqlalchemy.dialects.postgresql.INET)))
except ValueError:
pass
q = select(sqlalchemy.func.count(Change.change))
if f:
q = q.filter(sqlalchemy.or_(*f))
async with pms.measure():
total = (await session.execute(q)).scalars().first()
q = select(Change).options(
selectin_polymorphic(Change.entity, [Account]),
selectinload(
Change.entity.of_type(Authorization)).selectinload(
Authorization.identifier).selectinload(
Identifier.order).selectinload(Order.account),
selectinload(Change.entity.of_type(Challenge)).selectinload(
Challenge.authorization).selectinload(
Authorization.identifier).selectinload(
Identifier.order).selectinload(Order.account),
selectinload(Change.entity.of_type(Certificate)).selectinload(
Certificate.order).selectinload(Order.account),
selectinload(Change.entity.of_type(Identifier)).selectinload(
Identifier.order).selectinload(Order.account),
selectinload(Change.entity.of_type(Order)).selectinload(
Order.account),
)
if f:
q = q.filter(sqlalchemy.or_(*f))
q = q.order_by(Change.change.desc())
if f:
limit = "limit"
else:
limit = Change.change
page = await paginate(session, request, q, limit, total, pms)
return {"changes": page.items, "page": page, "pms": pms}
