def query_subcontributions(ids=None):
contrib_event = db.aliased(Event)
contrib_session = db.aliased(Session)
contrib_block = db.aliased(SessionBlock)
contrib_strategy = contains_eager(SubContribution.contribution)
contrib_strategy.joinedload(Contribution.own_venue)
contrib_strategy.joinedload(Contribution.own_room).options(
raiseload('*'), joinedload('location'))
contrib_strategy.joinedload(Contribution.timetable_entry)
apply_acl_entry_strategy(
contrib_strategy.selectinload(Contribution.acl_entries),
ContributionPrincipal)
event_strategy = contrib_strategy.contains_eager(
Contribution.event.of_type(contrib_event))
event_strategy.joinedload(contrib_event.own_venue)
event_strategy.joinedload(contrib_event.own_room).options(
raiseload('*'), joinedload('location'))
apply_acl_entry_strategy(
event_strategy.selectinload(contrib_event.acl_entries), EventPrincipal)
session_strategy = contrib_strategy.contains_eager(
Contribution.session.of_type(contrib_session))
apply_acl_entry_strategy(
session_strategy.selectinload(contrib_session.acl_entries),
SessionPrincipal)
session_strategy.joinedload(contrib_session.own_venue)
session_strategy.joinedload(contrib_session.own_room).options(
raiseload('*'), joinedload('location'))
session_block_strategy = contrib_strategy.contains_eager(
Contribution.session_block.of_type(contrib_block))
session_block_strategy.joinedload(contrib_block.own_venue)
session_block_strategy.joinedload(contrib_block.own_room).options(
raiseload('*'), joinedload('location'))
if ids is None:
export_filter = db.and_(~SubContribution.is_deleted,
~Contribution.is_deleted,
~contrib_event.is_deleted,
_get_excluded_category_filter(contrib_event))
else:
export_filter = SubContribution.id.in_(ids)
return (SubContribution.query.join(Contribution).join(
Contribution.event.of_type(contrib_event)).outerjoin(
Contribution.session.of_type(contrib_session)).outerjoin(
Contribution.session_block.of_type(contrib_block)).
filter(export_filter).options(
selectinload(SubContribution.person_links).joinedload(
'person').joinedload('user').load_only('is_system'),
contrib_strategy, event_strategy, session_strategy,
session_block_strategy).order_by(SubContribution.id))
def get_tree_cte(cls, col='id'):
"""Create a CTE for the category tree.
The CTE contains the followign columns:
- ``id`` -- the category id
- ``path`` -- an array containing the path from the root to
the category itself
- ``is_deleted`` -- whether the category is deleted
:param col: The name of the column to use in the path or a
callable receiving the category alias that must
return the expression used for the 'path'
retrieved by the CTE.
"""
cat_alias = db.aliased(cls)
if callable(col):
path_column = col(cat_alias)
else:
path_column = getattr(cat_alias, col)
cte_query = (select([cat_alias.id, array([path_column]).label('path'), cat_alias.is_deleted])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
cte_query.c.path.op('||')(path_column),
cte_query.c.is_deleted | cat_alias.is_deleted])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def get_tree_cte(cls, col='id'):
"""Create a CTE for the category tree.
The CTE contains the following columns:
- ``id`` -- the category id
- ``path`` -- an array containing the path from the root to
the category itself
- ``is_deleted`` -- whether the category is deleted
:param col: The name of the column to use in the path or a
callable receiving the category alias that must
return the expression used for the 'path'
retrieved by the CTE.
"""
cat_alias = db.aliased(cls)
if callable(col):
path_column = col(cat_alias)
else:
path_column = getattr(cat_alias, col)
cte_query = (select([
cat_alias.id,
array([path_column]).label('path'), cat_alias.is_deleted
]).where(cat_alias.parent_id.is_(None)).cte(recursive=True))
rec_query = (select([
cat_alias.id,
cte_query.c.path.op('||')(path_column),
cte_query.c.is_deleted | cat_alias.is_deleted
]).where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def _associate_registrations(user, **kwargs):
from indico.modules.events.registration.models.registrations import Registration
reg_alias = db.aliased(Registration)
subquery = db.session.query(reg_alias).filter(reg_alias.user_id == user.id,
reg_alias.registration_form_id == Registration.registration_form_id,
~reg_alias.is_deleted)
registrations = (Registration
.find(Registration.user_id == None, # noqa
Registration.email.in_(user.all_emails),
~subquery.exists(),
~Registration.is_deleted)
.order_by(Registration.submitted_dt.desc())
.all())
if not registrations:
return
done = set()
for registration in registrations:
if registration.registration_form_id in done:
continue
logger.info('Associating %s with %s', registration, user)
registration.user = user
done.add(registration.registration_form_id)
db.session.flush()
num = len(done)
flash(ngettext("A registration has been linked to your account.",
"{n} registrations have been linked to your account.", num).format(n=num), 'info')
def _associate_registrations(user, **kwargs):
from indico.modules.events.registration.models.registrations import Registration
reg_alias = db.aliased(Registration)
subquery = db.session.query(reg_alias).filter(
reg_alias.user_id == user.id,
reg_alias.registration_form_id == Registration.registration_form_id,
~reg_alias.is_deleted)
registrations = (Registration.query.filter(
Registration.user_id.is_(None),
Registration.email.in_(user.all_emails), ~subquery.exists(),
~Registration.is_deleted).order_by(
Registration.submitted_dt.desc()).all())
if not registrations:
return
done = set()
for registration in registrations:
if registration.registration_form_id in done:
continue
logger.info('Associating %s with %s', registration, user)
registration.user = user
done.add(registration.registration_form_id)
db.session.flush()
num = len(done)
flash(
ngettext('A registration has been linked to your account.',
'{n} registrations have been linked to your account.',
num).format(n=num), 'info')
def get_attachment_count(category_id=None):
"""Get the number of attachments in events in a category.
:param category_id: The category ID to get statistics for.
Attachments from subcategories are also
included.
:return: The number of attachments
"""
category_filter = Event.category_chain_overlaps(category_id) if category_id else True
subcontrib_contrib = db.aliased(Contribution)
query = (db.session
.query(db.func.count(Attachment.id))
.join(Attachment.folder)
.join(AttachmentFolder.event)
.outerjoin(AttachmentFolder.session)
.outerjoin(AttachmentFolder.contribution)
.outerjoin(AttachmentFolder.subcontribution)
.outerjoin(subcontrib_contrib, subcontrib_contrib.id == SubContribution.contribution_id)
.filter(AttachmentFolder.link_type != LinkType.category,
~Attachment.is_deleted,
~AttachmentFolder.is_deleted,
~Event.is_deleted,
# we have exactly one of those or none if the attachment is on the event itself
~db.func.coalesce(Session.is_deleted, Contribution.is_deleted, SubContribution.is_deleted, False),
# in case of a subcontribution we also need to check that the contrib is not deleted
(subcontrib_contrib.is_deleted.is_(None) | ~subcontrib_contrib.is_deleted),
category_filter))
return query.scalar()
def get_attachment_count(category_id=None):
"""Get the number of attachments in events in a category.
:param category_id: The category ID to get statistics for.
Attachments from subcategories are also
included.
:return: The number of attachments
"""
category_filter = Event.category_chain_overlaps(
category_id) if category_id else True
subcontrib_contrib = db.aliased(Contribution)
query = (
db.session.query(db.func.count(Attachment.id)).join(
Attachment.folder).join(AttachmentFolder.event).outerjoin(
AttachmentFolder.session).outerjoin(
AttachmentFolder.contribution).outerjoin(
AttachmentFolder.subcontribution).outerjoin(
subcontrib_contrib, subcontrib_contrib.id ==
SubContribution.contribution_id).
filter(
AttachmentFolder.link_type != LinkType.category,
~Attachment.is_deleted,
~AttachmentFolder.is_deleted,
~Event.is_deleted,
# we have exactly one of those or none if the attachment is on the event itself
~db.func.coalesce(Session.is_deleted, Contribution.is_deleted,
SubContribution.is_deleted, False),
# in case of a subcontribution we also need to check that the contrib is not deleted
(subcontrib_contrib.is_deleted.is_(None)
| ~subcontrib_contrib.is_deleted),
category_filter))
return query.scalar()
def get_protection_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id, cat_alias.protection_mode])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
db.case({ProtectionMode.inheriting.value: cte_query.c.protection_mode},
else_=cat_alias.protection_mode, value=cat_alias.protection_mode)])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def get_protection_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id, cat_alias.protection_mode])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
db.case({ProtectionMode.inheriting.value: cte_query.c.protection_mode},
else_=cat_alias.protection_mode, value=cat_alias.protection_mode)])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def get_protection_parent_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id, db.cast(literal(None), db.Integer).label('protection_parent')])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
db.case({ProtectionMode.inheriting.value: func.coalesce(cte_query.c.protection_parent, 0)},
else_=cat_alias.id, value=cat_alias.protection_mode)])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def get_merged_from_users_recursive(self):
"""Get the users merged into this users recursively."""
user_alias = db.aliased(User)
cte_query = (select([
user_alias.id.label('merged_from_id')
]).where(user_alias.merged_into_id == self.id).cte(recursive=True))
rec_query = (select([
user_alias.id
]).where(user_alias.merged_into_id == cte_query.c.merged_from_id))
cte = cte_query.union_all(rec_query)
return User.query.join(cte, User.id == cte.c.merged_from_id).all()
def get_icon_data_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id, cat_alias.id.label('source_id'), cat_alias.icon_metadata])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
db.case({'null': cte_query.c.source_id}, else_=cat_alias.id,
value=db.func.json_typeof(cat_alias.icon_metadata)),
db.case({'null': cte_query.c.icon_metadata}, else_=cat_alias.icon_metadata,
value=db.func.json_typeof(cat_alias.icon_metadata))])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def get_icon_data_cte(cls):
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id, cat_alias.id.label('source_id'), cat_alias.icon_metadata])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
db.case({'null': cte_query.c.source_id}, else_=cat_alias.id,
value=db.func.json_typeof(cat_alias.icon_metadata)),
db.case({'null': cte_query.c.icon_metadata}, else_=cat_alias.icon_metadata,
value=db.func.json_typeof(cat_alias.icon_metadata))])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def _query_events(categ_ids, day_start, day_end):
event = db.aliased(Event)
dates_overlap = lambda t: (t.start_dt >= day_start) & (t.start_dt <=
day_end)
return (db.session.query(Event.id, TimetableEntry.start_dt).filter(
Event.category_chain_overlaps(categ_ids), ~Event.is_deleted,
((Event.timetable_entries.any(dates_overlap(TimetableEntry))) |
(Event.query.exists().where(
Event.happens_between(day_start, day_end)
& (Event.id == event.id))))).group_by(
Event.id, TimetableEntry.start_dt).order_by(
Event.id, TimetableEntry.start_dt).join(
TimetableEntry, (TimetableEntry.event_id == Event.id)
& (dates_overlap(TimetableEntry)),
isouter=True))
def get_subtree_ids_cte(cls, ids):
"""Create a CTE for a category subtree.
This CTE contains a single ``id`` column that contains all the specified
IDs and those of all their subcategories.
This is likely to be much more performant than `get_tree_cte` when the
query is used with LIMIT, especially in large databases.
"""
cat_alias = db.aliased(cls)
cte_query = (select([cat_alias.id])
.where(cat_alias.id.in_(ids))
.cte(recursive=True))
rec_query = (select([cat_alias.id])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)
def _query_events(categ_ids, day_start, day_end):
event = db.aliased(Event)
dates_overlap = lambda t: (t.start_dt >= day_start) & (t.start_dt <= day_end)
return (db.session.query(Event.id, TimetableEntry.start_dt)
.filter(
Event.category_chain_overlaps(categ_ids),
~Event.is_deleted,
((Event.timetable_entries.any(dates_overlap(TimetableEntry))) |
(Event.query.exists().where(
Event.happens_between(day_start, day_end) &
(Event.id == event.id)))))
.group_by(Event.id, TimetableEntry.start_dt)
.order_by(Event.id, TimetableEntry.start_dt)
.join(TimetableEntry,
(TimetableEntry.event_id == Event.id) & (dates_overlap(TimetableEntry)),
isouter=True))
def query_attachments(ids=None):
contrib_event = db.aliased(Event)
contrib_session = db.aliased(Session)
subcontrib_contrib = db.aliased(Contribution)
subcontrib_session = db.aliased(Session)
subcontrib_event = db.aliased(Event)
session_event = db.aliased(Event)
attachment_strategy = apply_acl_entry_strategy(
selectinload(Attachment.acl_entries), AttachmentPrincipal)
folder_strategy = contains_eager(Attachment.folder)
folder_strategy.load_only('id', 'protection_mode', 'link_type',
'category_id', 'event_id', 'linked_event_id',
'contribution_id', 'subcontribution_id',
'session_id')
apply_acl_entry_strategy(
folder_strategy.selectinload(AttachmentFolder.acl_entries),
AttachmentFolderPrincipal)
# event
apply_acl_entry_strategy(
folder_strategy.contains_eager(
AttachmentFolder.linked_event).selectinload(Event.acl_entries),
EventPrincipal)
# contribution
contrib_strategy = folder_strategy.contains_eager(
AttachmentFolder.contribution)
apply_acl_entry_strategy(
contrib_strategy.selectinload(Contribution.acl_entries),
ContributionPrincipal)
apply_acl_entry_strategy(
contrib_strategy.contains_eager(
Contribution.event.of_type(contrib_event)).selectinload(
contrib_event.acl_entries), EventPrincipal)
apply_acl_entry_strategy(
contrib_strategy.contains_eager(
Contribution.session.of_type(contrib_session)).selectinload(
contrib_session.acl_entries), SessionPrincipal)
# subcontribution
subcontrib_strategy = folder_strategy.contains_eager(
AttachmentFolder.subcontribution)
subcontrib_contrib_strategy = subcontrib_strategy.contains_eager(
SubContribution.contribution.of_type(subcontrib_contrib))
apply_acl_entry_strategy(
subcontrib_contrib_strategy.selectinload(
subcontrib_contrib.acl_entries), ContributionPrincipal)
apply_acl_entry_strategy(
subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.event.of_type(subcontrib_event)).selectinload(
subcontrib_event.acl_entries), EventPrincipal)
apply_acl_entry_strategy(
subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.session.of_type(
subcontrib_session)).selectinload(
subcontrib_session.acl_entries), SessionPrincipal)
# session
session_strategy = folder_strategy.contains_eager(AttachmentFolder.session)
session_strategy.contains_eager(
Session.event.of_type(session_event)).selectinload(
session_event.acl_entries)
apply_acl_entry_strategy(
session_strategy.selectinload(Session.acl_entries), SessionPrincipal)
if ids is None:
export_filter = db.and_(
~Attachment.is_deleted, ~AttachmentFolder.is_deleted,
db.or_(
AttachmentFolder.link_type != LinkType.event,
~Event.is_deleted & _get_excluded_category_filter(),
),
db.or_(
AttachmentFolder.link_type != LinkType.contribution,
~Contribution.is_deleted & ~contrib_event.is_deleted
& _get_excluded_category_filter(contrib_event)),
db.or_(
AttachmentFolder.link_type != LinkType.subcontribution,
db.and_(~SubContribution.is_deleted,
~subcontrib_contrib.is_deleted,
~subcontrib_event.is_deleted,
_get_excluded_category_filter(subcontrib_event))),
db.or_(
AttachmentFolder.link_type != LinkType.session,
~Session.is_deleted & ~session_event.is_deleted
& _get_excluded_category_filter(session_event)))
else:
export_filter = Attachment.id.in_(ids)
return (
Attachment.query.join(Attachment.folder).options(
folder_strategy, attachment_strategy,
joinedload(Attachment.user).joinedload('_affiliation')).outerjoin(
AttachmentFolder.linked_event).outerjoin(
AttachmentFolder.contribution).outerjoin(
Contribution.event.of_type(contrib_event)).outerjoin(
Contribution.session.of_type(contrib_session)).
outerjoin(AttachmentFolder.subcontribution).outerjoin(
SubContribution.contribution.of_type(subcontrib_contrib)
).outerjoin(
subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin(
subcontrib_contrib.session.of_type(subcontrib_session)).
outerjoin(AttachmentFolder.session).outerjoin(
Session.event.of_type(session_event)).filter(export_filter).filter(
AttachmentFolder.link_type != LinkType.category).order_by(
Attachment.id))
def query_notes(ids=None):
contrib_event = db.aliased(Event)
contrib_session = db.aliased(Session)
subcontrib_contrib = db.aliased(Contribution)
subcontrib_session = db.aliased(Session)
subcontrib_event = db.aliased(Event)
session_event = db.aliased(Event)
note_strategy = load_only('id', 'link_type', 'event_id', 'linked_event_id',
'contribution_id', 'subcontribution_id',
'session_id', 'html')
# event
apply_acl_entry_strategy(
note_strategy.contains_eager(EventNote.linked_event).selectinload(
Event.acl_entries), EventPrincipal)
# contribution
contrib_strategy = note_strategy.contains_eager(EventNote.contribution)
apply_acl_entry_strategy(
contrib_strategy.selectinload(Contribution.acl_entries),
ContributionPrincipal)
apply_acl_entry_strategy(
contrib_strategy.contains_eager(
Contribution.event.of_type(contrib_event)).selectinload(
contrib_event.acl_entries), EventPrincipal)
apply_acl_entry_strategy(
contrib_strategy.contains_eager(
Contribution.session.of_type(contrib_session)).selectinload(
contrib_session.acl_entries), SessionPrincipal)
# subcontribution
subcontrib_strategy = note_strategy.contains_eager(
EventNote.subcontribution)
subcontrib_contrib_strategy = subcontrib_strategy.contains_eager(
SubContribution.contribution.of_type(subcontrib_contrib))
apply_acl_entry_strategy(
subcontrib_contrib_strategy.selectinload(
subcontrib_contrib.acl_entries), ContributionPrincipal)
apply_acl_entry_strategy(
subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.event.of_type(subcontrib_event)).selectinload(
subcontrib_event.acl_entries), EventPrincipal)
apply_acl_entry_strategy(
subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.session.of_type(
subcontrib_session)).selectinload(
subcontrib_session.acl_entries), SessionPrincipal)
# session
session_strategy = note_strategy.contains_eager(EventNote.session)
session_strategy.contains_eager(
Session.event.of_type(session_event)).selectinload(
session_event.acl_entries)
apply_acl_entry_strategy(
session_strategy.selectinload(Session.acl_entries), SessionPrincipal)
if ids is None:
export_filter = db.and_(
~EventNote.is_deleted,
db.or_(EventNote.link_type != LinkType.event,
~Event.is_deleted & _get_excluded_category_filter()),
db.or_(
EventNote.link_type != LinkType.contribution,
~Contribution.is_deleted & ~contrib_event.is_deleted
& _get_excluded_category_filter(contrib_event)),
db.or_(
EventNote.link_type != LinkType.subcontribution,
db.and_(~SubContribution.is_deleted,
~subcontrib_contrib.is_deleted,
~subcontrib_event.is_deleted,
_get_excluded_category_filter(subcontrib_event))),
db.or_(
EventNote.link_type != LinkType.session,
~Session.is_deleted & ~session_event.is_deleted
& _get_excluded_category_filter(session_event)))
else:
export_filter = EventNote.id.in_(ids)
return (EventNote.query.outerjoin(EventNote.linked_event).outerjoin(
EventNote.contribution
).outerjoin(Contribution.event.of_type(contrib_event)).outerjoin(
Contribution.session.of_type(contrib_session)
).outerjoin(EventNote.subcontribution).outerjoin(
SubContribution.contribution.of_type(subcontrib_contrib)).outerjoin(
subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin(
subcontrib_contrib.session.of_type(subcontrib_session)).
outerjoin(EventNote.session).outerjoin(
Session.event.of_type(session_event)).filter(
export_filter).options(
note_strategy,
joinedload(EventNote.current_revision).joinedload(
EventNoteRevision.user).joinedload('_affiliation'),
).order_by(EventNote.id))
def _mappers_configured():
# We create some column properties here since even with `declared_attr`
# the code runs at import time, making it impossible/risky to import other
# modules or reference the object itself in there.
# The advantage of those column properties is that they behave like regular
# (read-only) columns even though they are generated by subqueries. This
# allows them to be loaded together with the rest of the data, avoiding
# extra queries. To load them automatically you need to undefer them using
# the `undefer` query option, e.g. `.options(undefer('chain_titles'))`.
from indico.modules.events import Event
# Category.effective_protection_mode -- the effective protection mode
# (public/protected) of the category, even if it's inheriting it from its
# parent category
cte = Category.get_protection_cte()
query = select([
cte.c.protection_mode
]).where(cte.c.id == Category.id).correlate_except(cte).scalar_subquery()
Category.effective_protection_mode = column_property(query,
deferred=True,
expire_on_flush=False)
# Category.effective_icon_data -- the effective icon data of the category,
# either set on the category itself or inherited from it
cte = Category.get_icon_data_cte()
query = (select([
db.func.json_build_object('source_id', cte.c.source_id, 'metadata',
cte.c.icon_metadata)
]).where(cte.c.id == Category.id).correlate_except(cte).scalar_subquery())
Category.effective_icon_data = column_property(query, deferred=True)
# Category.event_count -- the number of events in the category itself,
# excluding deleted events
query = (select([
db.func.count(Event.id)
]).where((Event.category_id == Category.id)
& ~Event.is_deleted).correlate_except(Event).scalar_subquery())
Category.event_count = column_property(query, deferred=True)
# Category.has_events -- whether the category itself contains any
# events, excluding deleted events
query = (exists([1]).where((Event.category_id == Category.id)
& ~Event.is_deleted).correlate_except(Event))
Category.has_events = column_property(query, deferred=True)
# Category.has_children -- whether the category contains any
# non-deleted subcategories
cat_alias = db.aliased(Category)
query = (exists(
[1]).where((cat_alias.parent_id == Category.id)
& ~cat_alias.is_deleted).correlate_except(cat_alias))
Category.has_children = column_property(query, deferred=True)
# Category.chain_titles -- a list of the titles in the parent chain,
# starting with the root category down to the current category.
cte = Category.get_tree_cte('title')
query = select([
cte.c.path
]).where(cte.c.id == Category.id).correlate_except(cte).scalar_subquery()
Category.chain_titles = column_property(query, deferred=True)
# Category.chain_ids -- a list of the ids in the parent chain,
# starting with the root category down to the current category.
# This is equivalent to the `category_chain` in the Event model.
cte = Category.get_tree_cte()
query = select([
cte.c.path
]).where(cte.c.id == Category.id).correlate_except(cte).scalar_subquery()
Category.chain_ids = column_property(query, deferred=True)
# Category.chain -- a list of the ids and titles in the parent
# chain, starting with the root category down to the current
# category. Each chain entry is a dict containing 'id' and `title`.
cte = Category.get_tree_cte(lambda cat: db.func.json_build_object(
'id', cat.id, 'title', cat.title))
query = select([
cte.c.path
]).where(cte.c.id == Category.id).correlate_except(cte).scalar_subquery()
Category.chain = column_property(query, deferred=True)
# Category.deep_events_count -- the number of events in the category
# or any child category (excluding deleted events)
cte = Category.get_tree_cte()
crit = db.and_(cte.c.id == Event.category_id,
cte.c.path.contains(array([Category.id])),
~cte.c.is_deleted, ~Event.is_deleted)
query = select([db.func.count()
]).where(crit).correlate_except(Event).scalar_subquery()
Category.deep_events_count = column_property(query, deferred=True)
# Category.deep_children_count -- the number of subcategories in the
# category or any child category (excluding deleted ones)
cte = Category.get_tree_cte()
crit = db.and_(cte.c.path.contains(array([Category.id])),
cte.c.id != Category.id, ~cte.c.is_deleted)
query = select([db.func.count()
]).where(crit).correlate_except(cte).scalar_subquery()
Category.deep_children_count = column_property(query, deferred=True)
def search_notes(self, q, user, page, category_id, event_id,
admin_override_enabled):
contrib_event = db.aliased(Event)
contrib_session = db.aliased(Session)
subcontrib_contrib = db.aliased(Contribution)
subcontrib_session = db.aliased(Session)
subcontrib_event = db.aliased(Event)
session_event = db.aliased(Event)
note_strategy = load_only('id', 'link_type', 'event_id',
'linked_event_id', 'contribution_id',
'subcontribution_id', 'session_id', 'html')
# event
event_strategy = note_strategy.contains_eager(EventNote.linked_event)
event_strategy.undefer(Event.effective_protection_mode)
_apply_event_access_strategy(event_strategy)
_apply_acl_entry_strategy(
event_strategy.selectinload(Event.acl_entries), EventPrincipal)
# contribution
contrib_strategy = note_strategy.contains_eager(EventNote.contribution)
_apply_contrib_access_strategy(contrib_strategy)
_apply_acl_entry_strategy(
contrib_strategy.selectinload(Contribution.acl_entries),
ContributionPrincipal)
contrib_event_strategy = contrib_strategy.contains_eager(
Contribution.event.of_type(contrib_event))
_apply_event_access_strategy(contrib_event_strategy)
_apply_acl_entry_strategy(
contrib_event_strategy.selectinload(contrib_event.acl_entries),
EventPrincipal)
contrib_session_strategy = contrib_strategy.contains_eager(
Contribution.session.of_type(contrib_session))
contrib_session_strategy.load_only('id', 'event_id', 'protection_mode')
_apply_acl_entry_strategy(
contrib_session_strategy.selectinload(contrib_session.acl_entries),
SessionPrincipal)
# subcontribution
subcontrib_strategy = note_strategy.contains_eager(
EventNote.subcontribution)
subcontrib_contrib_strategy = subcontrib_strategy.contains_eager(
SubContribution.contribution.of_type(subcontrib_contrib))
_apply_contrib_access_strategy(subcontrib_contrib_strategy)
_apply_acl_entry_strategy(
subcontrib_contrib_strategy.selectinload(
subcontrib_contrib.acl_entries), ContributionPrincipal)
subcontrib_event_strategy = subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.event.of_type(subcontrib_event))
_apply_event_access_strategy(subcontrib_event_strategy)
_apply_acl_entry_strategy(
subcontrib_event_strategy.selectinload(
subcontrib_event.acl_entries), EventPrincipal)
subcontrib_session_strategy = subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.session.of_type(subcontrib_session))
subcontrib_session_strategy.load_only('id', 'event_id',
'protection_mode')
_apply_acl_entry_strategy(
subcontrib_session_strategy.selectinload(
subcontrib_session.acl_entries), SessionPrincipal)
# session
session_strategy = note_strategy.contains_eager(EventNote.session)
session_strategy.load_only('id', 'event_id', 'protection_mode')
session_event_strategy = session_strategy.contains_eager(
Session.event.of_type(session_event))
_apply_event_access_strategy(session_event_strategy)
session_event_strategy.selectinload(session_event.acl_entries)
_apply_acl_entry_strategy(
session_strategy.selectinload(Session.acl_entries),
SessionPrincipal)
note_filters = [
EventNote.html_matches(q), ~EventNote.is_deleted,
db.or_(EventNote.link_type != LinkType.event, ~Event.is_deleted),
db.or_(EventNote.link_type != LinkType.contribution,
~Contribution.is_deleted & ~contrib_event.is_deleted),
db.or_(
EventNote.link_type != LinkType.subcontribution,
db.and_(~SubContribution.is_deleted,
~subcontrib_contrib.is_deleted,
~subcontrib_event.is_deleted)),
db.or_(EventNote.link_type != LinkType.session,
~Session.is_deleted & ~session_event.is_deleted)
]
if category_id is not None:
note_filters.append(
EventNote.event.has(
Event.category_chain_overlaps(category_id)))
if event_id is not None:
note_filters.append(EventNote.event_id == event_id)
query = (
EventNote.query.filter(
*note_filters).options(note_strategy).outerjoin(
EventNote.linked_event).outerjoin(
EventNote.contribution).outerjoin(
Contribution.event.of_type(contrib_event)).
outerjoin(Contribution.session.of_type(contrib_session)).outerjoin(
EventNote.subcontribution).outerjoin(
SubContribution.contribution.of_type(subcontrib_contrib)).
outerjoin(
subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin(
subcontrib_contrib.session.of_type(
subcontrib_session)).outerjoin(
EventNote.session).outerjoin(
Session.event.of_type(session_event)))
objs, pagenav = self._paginate(query, page, EventNote.id, user,
admin_override_enabled)
query = (EventNote.query.filter(EventNote.id.in_(
n.id for n in objs)).options(
joinedload(EventNote.contribution),
joinedload(EventNote.subcontribution).joinedload(
SubContribution.contribution),
joinedload(EventNote.event).options(
undefer(Event.detailed_category_chain)),
joinedload(EventNote.current_revision).joinedload(
EventNoteRevision.user).joinedload('_affiliation'),
))
notes_by_id = {n.id: n for n in query}
notes = [notes_by_id[n.id] for n in objs]
res = HTMLStrippingEventNoteSchema(many=True).dump(notes)
return pagenav, EventNoteResultSchema(many=True).load(res)
def search_attachments(self, q, user, page, category_id, event_id,
admin_override_enabled):
contrib_event = db.aliased(Event)
contrib_session = db.aliased(Session)
subcontrib_contrib = db.aliased(Contribution)
subcontrib_session = db.aliased(Session)
subcontrib_event = db.aliased(Event)
session_event = db.aliased(Event)
attachment_strategy = _apply_acl_entry_strategy(
selectinload(Attachment.acl_entries), AttachmentPrincipal)
folder_strategy = contains_eager(Attachment.folder)
folder_strategy.load_only('id', 'protection_mode', 'link_type',
'category_id', 'event_id', 'linked_event_id',
'contribution_id', 'subcontribution_id',
'session_id')
_apply_acl_entry_strategy(
folder_strategy.selectinload(AttachmentFolder.acl_entries),
AttachmentFolderPrincipal)
# event
event_strategy = folder_strategy.contains_eager(
AttachmentFolder.linked_event)
_apply_event_access_strategy(event_strategy)
_apply_acl_entry_strategy(
event_strategy.selectinload(Event.acl_entries), EventPrincipal)
# contribution
contrib_strategy = folder_strategy.contains_eager(
AttachmentFolder.contribution)
_apply_contrib_access_strategy(contrib_strategy)
_apply_acl_entry_strategy(
contrib_strategy.selectinload(Contribution.acl_entries),
ContributionPrincipal)
contrib_event_strategy = contrib_strategy.contains_eager(
Contribution.event.of_type(contrib_event))
_apply_event_access_strategy(contrib_event_strategy)
_apply_acl_entry_strategy(
contrib_event_strategy.selectinload(contrib_event.acl_entries),
EventPrincipal)
contrib_session_strategy = contrib_strategy.contains_eager(
Contribution.session.of_type(contrib_session))
contrib_session_strategy.load_only('id', 'event_id', 'protection_mode')
_apply_acl_entry_strategy(
contrib_session_strategy.selectinload(contrib_session.acl_entries),
SessionPrincipal)
# subcontribution
subcontrib_strategy = folder_strategy.contains_eager(
AttachmentFolder.subcontribution)
subcontrib_strategy.load_only('id', 'contribution_id', 'title')
subcontrib_contrib_strategy = subcontrib_strategy.contains_eager(
SubContribution.contribution.of_type(subcontrib_contrib))
_apply_contrib_access_strategy(subcontrib_contrib_strategy)
_apply_acl_entry_strategy(
subcontrib_contrib_strategy.selectinload(
subcontrib_contrib.acl_entries), ContributionPrincipal)
subcontrib_event_strategy = subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.event.of_type(subcontrib_event))
_apply_event_access_strategy(subcontrib_event_strategy)
_apply_acl_entry_strategy(
subcontrib_event_strategy.selectinload(
subcontrib_event.acl_entries), EventPrincipal)
subcontrib_session_strategy = subcontrib_contrib_strategy.contains_eager(
subcontrib_contrib.session.of_type(subcontrib_session))
subcontrib_session_strategy.load_only('id', 'event_id',
'protection_mode')
_apply_acl_entry_strategy(
subcontrib_session_strategy.selectinload(
subcontrib_session.acl_entries), SessionPrincipal)
# session
session_strategy = folder_strategy.contains_eager(
AttachmentFolder.session)
session_strategy.load_only('id', 'event_id', 'protection_mode')
session_event_strategy = session_strategy.contains_eager(
Session.event.of_type(session_event))
_apply_event_access_strategy(session_event_strategy)
session_event_strategy.selectinload(session_event.acl_entries)
_apply_acl_entry_strategy(
session_strategy.selectinload(Session.acl_entries),
SessionPrincipal)
attachment_filters = [
Attachment.title_matches(q), ~Attachment.is_deleted,
~AttachmentFolder.is_deleted,
AttachmentFolder.link_type != LinkType.category,
db.or_(
AttachmentFolder.link_type != LinkType.event,
~Event.is_deleted,
),
db.or_(AttachmentFolder.link_type != LinkType.contribution,
~Contribution.is_deleted & ~contrib_event.is_deleted),
db.or_(
AttachmentFolder.link_type != LinkType.subcontribution,
db.and_(
~SubContribution.is_deleted,
~subcontrib_contrib.is_deleted,
~subcontrib_event.is_deleted,
)),
db.or_(AttachmentFolder.link_type != LinkType.session,
~Session.is_deleted & ~session_event.is_deleted)
]
if category_id is not None:
attachment_filters.append(
AttachmentFolder.event.has(
Event.category_chain_overlaps(category_id)))
if event_id is not None:
attachment_filters.append(AttachmentFolder.event_id == event_id)
query = (
Attachment.query.join(
Attachment.folder).filter(*attachment_filters).options(
folder_strategy, attachment_strategy,
joinedload(
Attachment.user).joinedload('_affiliation')).outerjoin(
AttachmentFolder.linked_event).outerjoin(
AttachmentFolder.contribution).outerjoin(
Contribution.event.of_type(contrib_event)).
outerjoin(Contribution.session.of_type(contrib_session)).outerjoin(
AttachmentFolder.subcontribution).outerjoin(
SubContribution.contribution.of_type(subcontrib_contrib)).
outerjoin(
subcontrib_contrib.event.of_type(subcontrib_event)).outerjoin(
subcontrib_contrib.session.of_type(
subcontrib_session)).outerjoin(
AttachmentFolder.session).outerjoin(
Session.event.of_type(session_event)))
objs, pagenav = self._paginate(query, page, Attachment.id, user,
admin_override_enabled)
query = (Attachment.query.filter(Attachment.id.in_(
a.id for a in objs)).options(
joinedload(Attachment.folder).options(
joinedload(AttachmentFolder.subcontribution),
joinedload(AttachmentFolder.event).options(
undefer(Event.detailed_category_chain)))))
attachments_by_id = {a.id: a for a in query}
attachments = [attachments_by_id[a.id] for a in objs]
res = AttachmentSchema(many=True).dump(attachments)
return pagenav, AttachmentResultSchema(many=True).load(res)
