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_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}