def get_courses() -> JSONResponse[t.Sequence[t.Mapping[str, t.Any]]]:
"""Return all :class:`.models.Course` objects the current user is a member
of.
.. :quickref: Course; Get all courses the current user is enrolled in.
:returns: A response containing the JSON serialized courses
:param str extended: If set to ``true``, ``1`` or the empty string all the
assignments and group sets for each course are also included under the
key ``assignments`` and ``group_sets`` respectively.
:>jsonarr str role: The name of the role the current user has in this
course.
:>jsonarr ``**rest``: JSON serialization of :py:class:`psef.models.Course`.
:raises PermissionException: If there is no logged in user. (NOT_LOGGED_IN)
"""
def _get_rest(course: models.Course) -> t.Mapping[str, t.Any]:
if helpers.extended_requested():
snippets: t.Sequence[models.CourseSnippet] = []
if (current_user.has_permission(GPerm.can_use_snippets)
and current_user.has_permission(
CPerm.can_view_course_snippets, course_id=course.id)):
snippets = course.snippets
return {
'assignments': course.get_all_visible_assignments(),
'group_sets': course.group_sets,
'snippets': snippets,
**course.__to_json__(),
}
return course.__to_json__()
extra_loads: t.Optional[t.List[t.Any]] = None
if helpers.extended_requested():
extra_loads = [
selectinload(models.Course.assignments),
selectinload(models.Course.snippets),
selectinload(models.Course.group_sets)
]
# We don't use `helpers.get_or_404` here as preloading doesn't seem to work
# when we do.
user = models.User.query.filter_by(id=current_user.id).options([
selectinload(models.User.courses, ).selectinload(
models.CourseRole._permissions, # pylint: disable=protected-access
),
]).first()
assert user is not None
return jsonify([{
'role': user.courses[c.id].name,
**_get_rest(c),
} for c in helpers.get_in_or_error(
models.Course,
t.cast(models.DbColumn[int], models.Course.id),
[cr.course_id for cr in user.courses.values()],
extra_loads,
)])
def events():
query = db.query(model.Event)\
.options(
selectinload(model.Event.teams_part)\
.joinedload(model.EventTeam.team, innerjoin=True),
selectinload(model.Event.judgings)\
.joinedload(model.EventJudging.judge, innerjoin=True)
)
params = flask.request.values
query = filter_events(query, params)
if "limit" in params:
query = query.limit(params.get("limit", type=int))
ret = query.all()
return [{
"id": e.id,
"block": {
"id": e.block_id,
"name": get_block(e).name
},
"ts_sched": e.ts_sched,
"arena": e.arena,
"teams": list(map(jsonify, e.teams)),
"judges": list(map(jsonify, e.judges))
} for e in ret]
def setup_query(self, types):
query = None
class_ = None
if self.get_argument('relationships', 'true').lower() == 'false':
multi_loader = noload
else:
multi_loader = selectinload
if types == 'rooms':
query = select(Room).options(selectinload(Room.floor),
multi_loader(Room.items),
selectinload(Room.sample))
class_ = Room
elif types == 'floors':
query = select(Floor).options(multi_loader(Floor.rooms),
multi_loader(Floor.samples),
multi_loader(Floor.topics))
class_ = Floor
elif types == 'items':
query = select(Item).options(selectinload(Item.room))
class_ = Item
elif types == 'floor-topics':
query = select(FloorTopic).options(selectinload(FloorTopic.group),
selectinload(FloorTopic.floor))
class_ = FloorTopic
elif types == 'groups':
query = select(Group)
class_ = Group
return (query, class_)
def update_query_for_extended_jsonify(
cls: t.Type['Work'], query: _MyQuery['Work']) -> _MyQuery['Work']:
"""Update the given query to load all attributes needed for an extended
jsonify eagerly.
:param query: The query to update.
:returns: The updated query, which now loads all attributes needed for
an extended jsonify eagerly.
"""
return query.options(
selectinload(cls.selected_items, ),
# We want to load all users directly. We do this by loading the
# user, which might be a group. For such groups we also load all
# users. The users in this group will never be a group, so the
# last `selectinload` here might be seen as strange. However,
# during the serialization of a group we access `User.group`, which
# checks if a user is really not a group. To prevent these last
# queries the last `selectinload` is needed here.
selectinload(cls.user, ).selectinload(
user_models.User.group, ).selectinload(
group_models.Group.members, ).selectinload(
user_models.User.group, ),
undefer(cls.comment),
selectinload(cls.comment_author),
)
async def get_certificate(session,
account_id=None,
certificate_id=None,
certificate=None):
if account_id and certificate_id:
statement = (select(Certificate).options(
selectinload(Order.certificate),
selectinload(Order.account),
selectinload(Certificate.order).selectinload(Order.account),
).join(Order, Certificate.order_id == Order.order_id).join(
Account, Order.account_id == Account.account_id).filter(
(account_id == Account.account_id)
& (Certificate.certificate_id == certificate_id)))
elif certificate:
statement = (select(Certificate).filter(
Certificate.cert == certificate).options(
selectinload(Certificate.order).selectinload(
Order.account)).join(
Order,
Certificate.order_id == Order.order_id).join(
Account,
Order.account_id == Account.account_id))
else:
raise ValueError(
"Either kid and certificate_id OR certificate should be specified"
)
try:
result = (await session.execute(statement)).first()
except DBAPIError:
# certificate_id is not a valid UUID
raise web.HTTPNotFound
return result[0] if result else None
def does_game_exist(session, user_id, channel_id):
game = session.query(Game). \
options(selectinload(Game.pits)). \
options(selectinload(Game.bats)). \
filter(and_(Game.user_id == user_id, Game.channel_id == channel_id)). \
one_or_none()
return game is not None
def go():
# NOTE: subqueryload is broken for this case, first found
# when cartesian product detection was added.
with expect_deprecated_20(with_polymorphic_dep):
for a in (session.query(A).with_polymorphic([B, C]).options(
selectinload(B.related), selectinload(C.related))):
eq_(a.related, [d])
def get_rubric(submission_id: int) -> JSONResponse[t.Mapping[str, t.Any]]:
"""Return full rubric of the :class:`.models.Assignment` of the given
submission (:class:`.models.Work`).
.. :quickref: Submission; Get a rubric and its selected items.
:param int submission_id: The id of the submission
:returns: A response containing the JSON serialized rubric as described in
:py:meth:`.Work.__rubric_to_json__`.
:raises APIException: If the submission with the given id does not exist.
(OBJECT_ID_NOT_FOUND)
:raises PermissionException: If there is no logged in user. (NOT_LOGGED_IN)
:raises PermissionException: If the user can not see the assignment of the
given submission. (INCORRECT_PERMISSION)
"""
work = helpers.filter_single_or_404(
models.Work,
models.Work.id == submission_id,
~models.Work.deleted,
options=[
selectinload(models.Work.assignment, ).selectinload(
models.Assignment.rubric_rows, ).selectinload(
models.RubricRow.items, ),
selectinload(models.Work.selected_items),
])
auth.WorkPermissions(work).ensure_may_see()
return jsonify(work.__rubric_to_json__())
async def delete(
primary_key: int,
db: AsyncSession = Depends(get_db),
):
# Get the panel
statement = (
select(Panel)
.where(Panel.id == primary_key)
.options(selectinload(Panel.reactions))
.options(selectinload(Panel.reactions, Reaction.category))
)
result = await db.execute(statement)
panel = result.scalars().first()
# Delete it if it exists
if panel is not None:
# Delete the message
channel = await get_channel(panel.channel_id)
if channel is not None:
message = await get_message(channel, panel.message_id)
if message is not None:
await message.delete()
# Delete all the reactions
for reaction in panel.reactions:
await db.delete(reaction)
await db.delete(panel)
await db.commit()
return {"success": True}
async def read(
primary_key: int,
db: AsyncSession = Depends(get_db),
):
# Get the panel and the reactions
statement = (
select(Panel)
.where(Panel.id == primary_key)
.options(selectinload(Panel.reactions))
.options(selectinload(Panel.reactions, Reaction.category))
)
result = await db.execute(statement)
panel = result.scalars().first()
if panel is None:
raise HTTPException(status_code=404, detail="not found")
# Get the channel name from the id
channel = await get_channel(panel.channel_id)
channel_name = channel.name if channel is not None else "<DNE>"
return SpecificPanel(
id=panel.id,
title=panel.title,
content=panel.content,
channel=channel_name,
reactions=panel.reactions,
)
def view_movies(self,
start,
number,
director=None,
actors=None,
genres=None):
session = self.session_factory()
query = session.query(Movie)
if director is not None and director != "":
query = query.join(Director).filter(Director.full_name == director)
for actor_name in actors:
a = aliased(Actor)
m = aliased(movie_actor)
query = query.join(m, Movie.id == m.c.movie_id).join(
a, m.c.actor_id == a.id)
query = query.filter(a.full_name == actor_name)
for genre_name in genres:
g = aliased(Genre)
mg = aliased(movie_genre)
query = query.join(mg, Movie.id == mg.c.movie_id).join(
g, mg.c.genre_id == g.id)
query = query.filter(g.name == genre_name)
count = query.count()
# paginate
query = query.options(selectinload(Movie.actors),
selectinload(Movie.director))
results = query.limit(number).offset(start).all()
return results, start + number < count
def utterances(self, session: Session = None) -> sqlalchemy.orm.Query:
"""
Get all utterances in the corpus
Parameters
----------
session: sqlalchemy.orm.Session, optional
Session to use in querying
Returns
-------
sqlalchemy.orm.Query
Utterance query
"""
close = False
if session is None:
session = Session(self.db_engine)
close = True
utterances = session.query(Utterance).options(
joinedload(Utterance.file, innerjoin=True),
joinedload(Utterance.speaker, innerjoin=True),
selectinload(Utterance.phone_intervals),
selectinload(Utterance.word_intervals),
selectinload(Utterance.reference_phone_intervals),
)
if close:
session.close()
return utterances
async def get_challenge(session, account_id, challenge_id):
statement = (select(Challenge).options(
selectinload(Challenge.authorization).options(
selectinload(Authorization.challenges),
selectinload(Authorization.identifier).options(
selectinload(Identifier.authorization),
selectinload(Identifier.order).selectinload(
Order.identifiers).joinedload(
Identifier.authorization),
),
)).join(
Authorization,
Challenge.authorization_id == Authorization.authorization_id,
).join(
Identifier,
Authorization.identifier_id == Identifier.identifier_id).join(
Order, Identifier.order_id == Order.order_id).join(
Account,
Order.account_id == Account.account_id).filter(
(account_id == Account.account_id)
& (Challenge.challenge_id == challenge_id)))
try:
result = (await session.execute(statement)).first()
except DBAPIError:
# challenge_id is not a valid UUID
raise web.HTTPNotFound
return result[0] if result else None
def speakers(self, session: Session = None) -> sqlalchemy.orm.Query:
"""
Get all speakers in the corpus
Parameters
----------
session: sqlalchemy.orm.Session, optional
Session to use in querying
Returns
-------
sqlalchemy.orm.Query
Speaker query
"""
close = False
if session is None:
session = self.session()
close = True
speakers = session.query(Speaker).options(
selectinload(Speaker.utterances),
selectinload(Speaker.files).joinedload(SpeakerOrdering.file,
innerjoin=True),
joinedload(Speaker.dictionary),
)
if close:
session.close()
return speakers
def files(self, session: Session = None) -> sqlalchemy.orm.Query:
"""
Get all files in the corpus
Parameters
----------
session: sqlalchemy.orm.Session, optional
Session to use in querying
Returns
-------
sqlalchemy.orm.Query
File query
"""
close = False
if session is None:
session = self.session()
close = True
files = session.query(File).options(
selectinload(File.utterances),
selectinload(File.speakers).joinedload(SpeakerOrdering.speaker,
innerjoin=True),
joinedload(File.sound_file),
joinedload(File.text_file),
)
if close:
session.close()
return files
def search_events(self, q, user, page, category_id,
admin_override_enabled):
filters = [Event.title_matches(q), ~Event.is_deleted]
if category_id is not None:
filters.append(Event.category_chain_overlaps(category_id))
query = (Event.query.filter(*filters).options(
load_only('id', 'category_id', 'access_key', 'protection_mode'),
undefer(Event.effective_protection_mode),
_apply_acl_entry_strategy(selectinload(Event.acl_entries),
EventPrincipal)))
objs, pagenav = self._paginate(query, page, Event.id, user,
admin_override_enabled)
query = (Event.query.filter(Event.id.in_(e.id for e in objs)).options(
undefer(Event.detailed_category_chain),
selectinload(Event.person_links).joinedload('person').joinedload(
'user').load_only('is_system'),
joinedload(Event.own_venue),
joinedload(Event.own_room).options(raiseload('*'),
joinedload('location')),
))
events_by_id = {e.id: e for e in query}
events = [events_by_id[e.id] for e in objs]
res = HTMLStrippingEventSchema(many=True).dump(events)
return pagenav, EventResultSchema(many=True).load(res)
def test_orm_query_using_with_entities(self):
"""test issue #6503"""
User, Address, Keyword, Order, Item = self.classes(
"User", "Address", "Keyword", "Order", "Item")
self._run_cache_key_fixture(
lambda: stmt_20(
fixture_session().query(User).join(User.addresses).
with_entities(Address.id),
#
fixture_session().query(Address.id).join(User.addresses),
#
fixture_session().query(User).options(
selectinload(User.addresses)).with_entities(User.id),
#
fixture_session().query(User).options(
selectinload(User.addresses)),
#
fixture_session().query(User).with_entities(User.id),
#
# here, propagate_attr->orm is Address, entity is Address.id,
# but the join() + with_entities() will log a
# _MemoizedSelectEntities to differentiate
fixture_session().query(Address, Order).join(
Address.dingaling).with_entities(Address.id),
#
# same, propagate_attr->orm is Address, entity is Address.id,
# but the join() + with_entities() will log a
# _MemoizedSelectEntities to differentiate
fixture_session().query(Address, User).join(
Address.dingaling).with_entities(Address.id),
),
compare_values=True,
)
def list_groups_and_maps_for_stops_in_route(route_pk):
"""
This function is used to get the service maps for a route.
It returns a list of tuples (service map group, service map) for each
service map group having use_for_stops_in_route equal True.
:param route_pk: the route's PK
:return: the list described above
"""
session = dbconnection.get_session()
query = (session.query(models.ServiceMapGroup, models.ServiceMap).join(
models.System,
models.System.pk == models.ServiceMapGroup.system_pk).join(
models.Route,
models.Route.system_pk == models.System.pk).outerjoin(
models.ServiceMap,
sql.and_(
models.ServiceMap.route_pk == models.Route.pk,
models.ServiceMap.group_pk == models.ServiceMapGroup.pk,
),
).filter(models.ServiceMapGroup.use_for_stops_in_route).filter(
models.Route.pk == route_pk).options(
selectinload(models.ServiceMap.vertices)).options(
selectinload(models.ServiceMap.vertices,
models.ServiceMapVertex.stop)))
return [(group, map_) for (group, map_) in query]
def test_selectinload_w_joinedload_after(self, calling_style):
"""test has been enhanced to also test #7224"""
A, B, C = self.classes("A", "B", "C")
s = Session(testing.db)
partitioned_b = self.partitioned_b
if calling_style == "ac_attribute":
opt = selectinload(A.partitioned_bs).joinedload(partitioned_b.cs)
elif calling_style == "ac_attr_w_of_type":
# this would have been a workaround for people who encountered
# #7224. The exception that was raised for "ac_attribute" actually
# suggested to use of_type() so we can assume this pattern is
# probably being used
opt = selectinload(
A.partitioned_bs.of_type(partitioned_b)
).joinedload(partitioned_b.cs)
else:
assert False
def go():
for a1 in s.query(A).options(opt):
for b in a1.partitioned_bs:
eq_(len(b.cs), 2)
self.assert_sql_count(testing.db, go, 2)
def scan_database(base, session, conn):
"""
Синхронизирует одну базу данных целиком.
"""
original_db = original_models.OriginalDatabase.fetch_from_metadata(conn)
if original_db.last_update == base.last_update:
logging.info(f"В оригинальной БД не было изменений, выходим")
return
base = session.query(Database).options(
selectinload(Database.scripts),
selectinload(Database.tables)).filter(Database.id == base.id).one()
logging.debug(f"Достаём оригиналы объектов БД {base.name}")
original_proc_data_set = original_models.OriginalProcedure.get_all(conn)
original_views_data_set = original_models.OriginalView.get_all(conn)
original_tabfunc_data_set = original_models.OriginalTableFunction.get_all(
conn)
original_sfunc_data_set = original_models.OriginalScalarFunction.get_all(
conn)
original_tables_data_set = original_models.OriginalTable.get_all(conn)
logging.debug(f"Синхронизируем хранимые процедуры БД {base.name}")
sync_subordinate_members(original_proc_data_set, DBStoredProcedure,
base.procedures, session, base)
logging.debug(f"Синхронизируем представления БД {base.name}")
sync_subordinate_members(original_views_data_set, DBView, base.views,
session, base)
logging.debug(f"Синхронизируем табличные функции БД {base.name}")
sync_subordinate_members(original_tabfunc_data_set, DBTableFunction,
base.table_functions, session, base)
logging.debug(f"Синхронизируем скалярные функции БД {base.name}")
sync_subordinate_members(original_sfunc_data_set, DBScalarFunction,
base.scalar_functions, session, base)
logging.debug(f"Синхронизируем таблицы БД {base.name}")
sync_subordinate_members(original_tables_data_set, DBTable, base.tables,
session, base)
persistent_tables = {
table.name: table
for table in session if isinstance(table, DBTable)
}
logging.debug(
f"Сопоставляем триггеры для оставшихся таблиц БД {base.name}")
for table_name in persistent_tables:
table = persistent_tables[table_name]
logging.debug(
f"Собираем триггеры для таблицы {table_name} в БД {base.name}")
original_triggers_data_set = original_models.OriginalTrigger.get_triggers_for_table(
conn, table.database_object_id)
sync_subordinate_members(original_triggers_data_set, DBTrigger,
table.triggers, session, table)
# обновляем метаданные самой базы
base.update_from(original_db)
logging.info(f"Обработка базы {base.name} завершена")
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(selectinload(ASub.cs),
selectinload(A.bs)))
self._assert_all_selectin(q)
def _route_get_event(event_id: int):
with Session() as session:
item: Event = (session.query(Event).options(
selectinload(Event.artists),
selectinload(Event.location)).get_or_404(event_id))
return camelize(
item.to_dict(
mapper=event_mapper(load_artist=True, load_location=True)))
def company_queries_with_all_relations():
return Company.query.options(
selectinload(Company.employments).selectinload(
Employment.user).selectinload(User.activities).options(
selectinload(Activity.mission).selectinload(
Mission.expenditures)).options(
selectinload(Activity.revisions))).options(
selectinload(Company.vehicles))
def transactions(args):
# For performance reasons, eager load some extra data we will use later for CSV's.
return _filter(
db.session.query(Transaction).join(Activity).options(
orm.selectinload(Transaction.recipient_country_percentages),
orm.selectinload(Transaction.recipient_region_percentages),
orm.selectinload(Transaction.sector_percentages),
), args)
def getBuilds(self, tenant=None, project=None, pipeline=None,
change=None, branch=None, patchset=None, ref=None,
newrev=None, event_id=None, uuid=None, job_name=None,
voting=None, node_name=None, result=None, provides=None,
final=None, held=None, limit=50, offset=0):
build_table = self.connection.zuul_build_table
buildset_table = self.connection.zuul_buildset_table
provides_table = self.connection.zuul_provides_table
# contains_eager allows us to perform eager loading on the
# buildset *and* use that table in filters (unlike
# joinedload).
q = self.session().query(self.connection.buildModel).\
join(self.connection.buildSetModel).\
outerjoin(self.connection.providesModel).\
options(orm.contains_eager(self.connection.buildModel.buildset),
orm.selectinload(self.connection.buildModel.provides),
orm.selectinload(self.connection.buildModel.artifacts))
# If the query planner isn't able to reduce either the number
# of rows returned by the buildset or build tables, then it
# tends to produce a very slow query. This hint produces
# better results, but only in those cases. When we can narrow
# things down with indexes, it's better to omit the hint.
# job_name is a tricky one. It is indexed, but if there are a
# lot of rows, it is better to include the hint, but if there
# are few, it is better to not include it. We include the hint
# regardless of whether job_name is specified (optimizing for
# the more common case).
if not (project or change or uuid):
q = q.with_hint(build_table, 'USE INDEX (PRIMARY)', 'mysql')
q = self.listFilter(q, buildset_table.c.tenant, tenant)
q = self.listFilter(q, buildset_table.c.project, project)
q = self.listFilter(q, buildset_table.c.pipeline, pipeline)
q = self.listFilter(q, buildset_table.c.change, change)
q = self.listFilter(q, buildset_table.c.branch, branch)
q = self.listFilter(q, buildset_table.c.patchset, patchset)
q = self.listFilter(q, buildset_table.c.ref, ref)
q = self.listFilter(q, buildset_table.c.newrev, newrev)
q = self.listFilter(q, buildset_table.c.event_id, event_id)
q = self.listFilter(q, build_table.c.uuid, uuid)
q = self.listFilter(q, build_table.c.job_name, job_name)
q = self.listFilter(q, build_table.c.voting, voting)
q = self.listFilter(q, build_table.c.node_name, node_name)
q = self.listFilter(q, build_table.c.result, result)
q = self.listFilter(q, build_table.c.final, final)
q = self.listFilter(q, provides_table.c.name, provides)
q = self.listFilter(q, build_table.c.held, held)
q = q.order_by(build_table.c.id.desc()).\
limit(limit).\
offset(offset)
try:
return q.all()
except sqlalchemy.orm.exc.NoResultFound:
return []
async def storylet_view(request: Request, area_id: int, storylet_id: int):
with get_session() as session:
area = session.query(Area).get(area_id)
storylet = session.query(Storylet)\
.options(
selectinload(Storylet.areas),
selectinload(Storylet.branches).selectinload(Branch.observations)
).get(storylet_id)
return {"area": area, "storylet": storylet}
async def patch(cls: T, session: AsyncSession, _id: int,
new_data: dict) -> Optional[Row]:
# Патч по сути только для курьеров
update_data = {}
for key, value in new_data.items():
if value is None:
continue
if key == "working_hours":
update_data[
"working_hours_timedeltas"] = get_timedeltas_from_string(
value)
elif key == "delivery_hours":
update_data[
"delivery_hours_timedeltas"] = get_timedeltas_from_string(
value)
update_data[key] = value
response = await session.execute(
update(cls).where(cls.id == _id).options(selectinload(
cls.orders)).values(update_data).returning(cls))
if not response.fetchall():
return None
new_object = (await session.execute(
select(cls).where(cls.id == _id).options(selectinload(cls.orders))
)).first()[0]
new_object_orders = get_best_orders(new_object.orders,
capacity=new_object.get_capacity())
if new_object_orders:
new_orders = []
orders_sum_weight = 0
for order in new_object_orders:
for order_timedelta in order.delivery_hours_timedeltas:
for courier_timedelta in new_object.working_hours_timedeltas:
if (check_courier_can_delivery_by_time(
order_timedelta=order_timedelta,
courier_timedelta=courier_timedelta,
) and (round(orders_sum_weight + order.weight, 2) <=
new_object.get_capacity())
and (order not in new_orders)
and (order.region in new_object.regions)):
orders_sum_weight = round(
orders_sum_weight + order.weight, 2)
new_orders.append(order)
for order in new_object.orders:
if order not in new_orders:
order.assign_time = None
order.courier_id = None
await session.commit()
return new_object
def get_user_posts(user_id, *, category_id=None, limit=10, offset=0):
user_posts = Post.query.filter(Post.owner_id == user_id)
if category_id:
user_posts = user_posts.filter(Post.category_id == category_id)
limit = limit if 1 <= limit <= 50 else 10
offset = offset if offset >= 0 else 0
return (user_posts.options(selectinload(Post.comments)).options(
selectinload(Post.owner)).options(selectinload(
Post.categories)).offset(offset).limit(limit))
def transactions_by_sector(args):
return _filter(
db.session.query(Transaction, SectorPercentage).join(
Activity, Activity.iati_identifier ==
Transaction.activity_id).join(SectorPercentage).options(
orm.selectinload(Transaction.recipient_country_percentages),
orm.selectinload(Transaction.recipient_region_percentages),
orm.selectinload(Transaction.sector_percentages),
), args)
def list_team_games(self, team_name):
try:
dataframe = self.session.query(Game).options(selectinload(Game.team1), selectinload(Game.team2)). \
filter(and_(Game.best_of == 3,
or_(Game.team1.has(Team.name == team_name), Game.team2.has(Team.name == team_name)))).all()
except:
self.session.rollback()
raise
return dataframe
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(
selectinload(ASub.cs),
selectinload(A.bs)
)
self._assert_all_selectin(q)
def get_avatar(request):
"""
Returns the current User object
"""
logger.info("# Retrieving avatar #")
login = request.unauthenticated_userid
logger.info(u" + Login : %s" % login)
result = None
if login is not None:
logger.info(" + Returning the user")
query = request.dbsession.query(User)
query = query.join(Login)
query = query.options(load_only("firstname", "lastname"))
query = query.options(
contains_eager(User.login).load_only('login').selectinload(
Login._groups).load_only('name'),
selectinload(User.companies).load_only('id', 'active'),
)
query = query.filter(Login.login == login)
result = query.first()
if result is None:
forget(request)
raise HTTPFound("/")
else:
logger.info(" + No user found")
logger.debug(u"-> End of the avatar collection")
return result
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]) ORDER BY a_1.id",
[{"primary_keys": [1]}],
),
)
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 getBuilds(self, tenant=None, project=None, pipeline=None,
change=None, branch=None, patchset=None, ref=None,
newrev=None, uuid=None, job_name=None, voting=None,
node_name=None, result=None, provides=None,
limit=50, offset=0):
build_table = self.connection.zuul_build_table
buildset_table = self.connection.zuul_buildset_table
provides_table = self.connection.zuul_provides_table
# contains_eager allows us to perform eager loading on the
# buildset *and* use that table in filters (unlike
# joinedload).
q = self.session().query(self.connection.buildModel).\
join(self.connection.buildSetModel).\
outerjoin(self.connection.providesModel).\
options(orm.contains_eager(self.connection.buildModel.buildset),
orm.selectinload(self.connection.buildModel.provides),
orm.selectinload(self.connection.buildModel.artifacts)).\
with_hint(build_table, 'USE INDEX (PRIMARY)', 'mysql')
q = self.listFilter(q, buildset_table.c.tenant, tenant)
q = self.listFilter(q, buildset_table.c.project, project)
q = self.listFilter(q, buildset_table.c.pipeline, pipeline)
q = self.listFilter(q, buildset_table.c.change, change)
q = self.listFilter(q, buildset_table.c.branch, branch)
q = self.listFilter(q, buildset_table.c.patchset, patchset)
q = self.listFilter(q, buildset_table.c.ref, ref)
q = self.listFilter(q, buildset_table.c.newrev, newrev)
q = self.listFilter(q, build_table.c.uuid, uuid)
q = self.listFilter(q, build_table.c.job_name, job_name)
q = self.listFilter(q, build_table.c.voting, voting)
q = self.listFilter(q, build_table.c.node_name, node_name)
q = self.listFilter(q, build_table.c.result, result)
q = self.listFilter(q, provides_table.c.name, provides)
q = q.order_by(build_table.c.id.desc()).\
limit(limit).\
offset(offset)
try:
return q.all()
except sqlalchemy.orm.exc.NoResultFound:
return []
def test_round_trip_results(self):
A, B, C = self.classes("A", "B", "C")
sess = Session()
q = sess.query(A).options(selectinload(A.bs).selectinload(B.cs))
@profiling.function_call_count()
def go():
for i in range(100):
obj = q.all()
list(obj)
sess.close()
go()
def test_selectinload_query(self):
session = ShardedSession(
shards={"test": testing.db},
shard_chooser=lambda *args: "test",
id_chooser=lambda *args: None,
query_chooser=lambda *args: ["test"],
)
Book, Page = self.classes("Book", "Page")
book = Book()
book.pages.append(Page())
session.add(book)
session.commit()
result = session.query(Book).options(selectinload("pages")).all()
eq_(result, [book])
def query(self):
business_type_id = self._get_training_business_type()
query = self.dbsession.query(
distinct(Business.id), Business
).filter(
Business.business_type_id == business_type_id
)
query = query.options(
joinedload(Business.project).load_only('id').
selectinload(Project.company).load_only(
Company.id, Company.name
),
selectinload(Business.tasks).
selectinload(Project.customers).load_only(
Customer.id, Customer.label
)
)
return query
def get_room_blockings(timeframe=None, created_by=None, in_rooms_owned_by=None):
query = (Blocking.query
.join(Blocking.blocked_rooms)
.join(BlockedRoom.room)
.options(contains_eager('blocked_rooms').contains_eager('room'),
selectinload('_allowed')))
criteria = []
if timeframe == 'recent':
criteria.append(Blocking.end_date >= date.today())
elif timeframe == 'year':
criteria.extend([Blocking.start_date <= date(date.today().year, 12, 31),
Blocking.end_date >= date(date.today().year, 1, 1)])
if created_by:
criteria.append(Blocking.created_by_user == created_by)
if in_rooms_owned_by:
criteria.append(BlockedRoom.room_id.in_(get_managed_room_ids(in_rooms_owned_by)))
query = query.filter(db.and_(*criteria))
return query.all()
def go():
for a1 in s.query(A).options(
noload("*"), selectinload(A.partitioned_bs)
):
for b in a1.partitioned_bs:
eq_(b.cs, [])
def go():
for a1 in s.query(A).options(
selectinload(A.partitioned_bs).joinedload("cs")
):
for b in a1.partitioned_bs:
eq_(len(b.cs), 2)
