def visibility_horizon_query(self): """Get a query object that returns the highest category this one is visible from.""" cte_query = ( select( [ Category.id, Category.parent_id, db.case([(Category.visibility.is_(None), None)], else_=(Category.visibility - 1)).label("n"), literal(0).label("level"), ] ) .where(Category.id == self.id) .cte("visibility_horizon", recursive=True) ) parent_query = select( [ Category.id, Category.parent_id, db.case( [(Category.visibility.is_(None) & cte_query.c.n.is_(None), None)], else_=db.func.least(Category.visibility, cte_query.c.n) - 1, ), cte_query.c.level + 1, ] ).where(db.and_(Category.id == cte_query.c.parent_id, (cte_query.c.n > 0) | cte_query.c.n.is_(None))) cte_query = cte_query.union_all(parent_query) return db.session.query(cte_query.c.id, cte_query.c.n).order_by(cte_query.c.level.desc()).limit(1)
def get_track_reviewer_abstract_counts(event, user): """Get the numbers of abstracts per track for a specific user. Note that this does not take into account if the user is a reviewer for a track; it just checks whether the user has reviewed an abstract in a track or not. :return: A dict mapping tracks to dicts containing the counts. """ # COUNT() does not count NULL values so we pass NULL in case an # abstract is not in the submitted state. That way we still get # the track - filtering using WHERE would only include tracks # that have some abstract in the submitted state. count_total = db.func.count(Abstract.id) count_reviewable = db.func.count(db.case({AbstractState.submitted.value: Abstract.id}, value=Abstract.state)) count_reviewable_reviewed = db.func.count(db.case({AbstractState.submitted.value: AbstractReview.id}, value=Abstract.state)) count_total_reviewed = db.func.count(AbstractReview.id) query = (Track.query.with_parent(event) .with_entities(Track, count_total, count_total_reviewed, count_reviewable - count_reviewable_reviewed) .outerjoin(Track.abstracts_reviewed) .outerjoin(AbstractReview, db.and_(AbstractReview.abstract_id == Abstract.id, AbstractReview.user_id == user.id)) .group_by(Track.id)) return {track: {'total': total, 'reviewed': reviewed, 'unreviewed': unreviewed} for track, total, reviewed, unreviewed in query}
def _make_occurrence_date_filter(date_column, default_values, room_columns, value_col=Reservation.repeat_frequency): notification_before = db.case({RepeatFrequency.WEEK.value: room_columns['weekly'], RepeatFrequency.MONTH.value: room_columns['monthly']}, else_=room_columns['default'], value=value_col) notification_before_default = db.case({RepeatFrequency.WEEK.value: default_values['weekly'], RepeatFrequency.MONTH.value: default_values['monthly']}, else_=default_values['default'], value=value_col) notification_before_days = db.func.coalesce(notification_before, notification_before_default) days_until = db.cast(date_column, db.Date) - date.today() return days_until == notification_before_days
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 _make_occurrence_date_filter(): _default = rb_settings.get('notification_before_days') _default_weekly = rb_settings.get('notification_before_days_weekly') _default_monthly = rb_settings.get('notification_before_days_monthly') notification_before_days_room = db.case({RepeatFrequency.WEEK.value: Room.notification_before_days_weekly, RepeatFrequency.MONTH.value: Room.notification_before_days_monthly}, else_=Room.notification_before_days, value=Reservation.repeat_frequency) notification_before_days_default = db.case({RepeatFrequency.WEEK.value: _default_weekly, RepeatFrequency.MONTH.value: _default_monthly}, else_=_default, value=Reservation.repeat_frequency) notification_before_days = db.func.coalesce(notification_before_days_room, notification_before_days_default) days_until_occurrence = db.cast(ReservationOccurrence.start_dt, db.Date) - date.today() return days_until_occurrence == notification_before_days
def visibility_horizon_query(self): """Get a query object that returns the highest category this one is visible from.""" cte_query = (select([Category.id, Category.parent_id, db.case([(Category.visibility.is_(None), None)], else_=(Category.visibility - 1)).label('n'), literal(0).label('level')]) .where(Category.id == self.id) .cte('visibility_horizon', recursive=True)) parent_query = (select([Category.id, Category.parent_id, db.case([(Category.visibility.is_(None) & cte_query.c.n.is_(None), None)], else_=db.func.least(Category.visibility, cte_query.c.n) - 1), cte_query.c.level + 1]) .where(db.and_(Category.id == cte_query.c.parent_id, (cte_query.c.n > 0) | cte_query.c.n.is_(None)))) cte_query = cte_query.union_all(parent_query) return db.session.query(cte_query.c.id, cte_query.c.n).order_by(cte_query.c.level.desc()).limit(1)
def calculate_rooms_booked_time(rooms, start_date=None, end_date=None): if end_date is None: end_date = date.today() - relativedelta(days=1) if start_date is None: start_date = end_date - relativedelta(days=29) # Reservations on working days reservations = Reservation.find( Reservation.room_id.in_(r.id for r in rooms), db.extract('dow', ReservationOccurrence.start_dt).between(1, 5), db.cast(ReservationOccurrence.start_dt, db.Date) >= start_date, db.cast(ReservationOccurrence.end_dt, db.Date) <= end_date, ReservationOccurrence.is_valid, _join=ReservationOccurrence) rsv_start = db.cast(ReservationOccurrence.start_dt, db.TIME) rsv_end = db.cast(ReservationOccurrence.end_dt, db.TIME) slots = ((db.cast(start, db.TIME), db.cast(end, db.TIME)) for start, end in Location.working_time_periods) # this basically handles all possible ways an occurrence overlaps with each one of the working time slots overlaps = sum( db.case([((rsv_start < start) & (rsv_end > end), db.extract('epoch', end - start)), ((rsv_start < start) & (rsv_end > start) & (rsv_end <= end), db.extract('epoch', rsv_end - start)), ((rsv_start >= start) & (rsv_start < end) & (rsv_end > end), db.extract('epoch', end - rsv_start)), ((rsv_start >= start) & (rsv_end <= end), db.extract('epoch', rsv_end - rsv_start))], else_=0) for start, end in slots) return reservations.with_entities(db.func.sum(overlaps)).scalar() or 0
def _mappers_configured(): from .revisions import EditingRevision, InitialRevisionState, FinalRevisionState # Editable.state -- the state of the editable itself cases = db.cast(db.case({ FinalRevisionState.none: db.case({ InitialRevisionState.new: EditableState.new, InitialRevisionState.ready_for_review: EditableState.ready_for_review, InitialRevisionState.needs_submitter_confirmation: EditableState.needs_submitter_confirmation }, value=EditingRevision.initial_state), # the states resulting in None are always followed by another revision, so we don't ever # expect the latest revision of an editable to have such a state FinalRevisionState.replaced: None, FinalRevisionState.needs_submitter_confirmation: None, FinalRevisionState.needs_submitter_changes: EditableState.needs_submitter_changes, FinalRevisionState.accepted: EditableState.accepted, FinalRevisionState.rejected: EditableState.rejected, }, value=EditingRevision.final_state), PyIntEnum(EditableState)) query = (select([cases]) .where(EditingRevision.editable_id == Editable.id) .order_by(EditingRevision.created_dt.desc()) .limit(1) .correlate_except(EditingRevision)) Editable.state = column_property(query) # Editable.revision_count -- the number of revisions the editable has query = (select([db.func.count(EditingRevision.id)]) .where(EditingRevision.editable_id == Editable.id) .correlate_except(EditingRevision)) Editable.revision_count = column_property(query)
def calculate_rooms_booked_time(rooms, start_date=None, end_date=None): if end_date is None: end_date = date.today() - relativedelta(days=1) if start_date is None: start_date = end_date - relativedelta(days=29) # Reservations on working days reservations = Reservation.find(Reservation.room_id.in_(r.id for r in rooms), db.extract('dow', ReservationOccurrence.start_dt).between(1, 5), db.cast(ReservationOccurrence.start_dt, db.Date) >= start_date, db.cast(ReservationOccurrence.end_dt, db.Date) <= end_date, ReservationOccurrence.is_valid, _join=ReservationOccurrence) rsv_start = db.cast(ReservationOccurrence.start_dt, db.TIME) rsv_end = db.cast(ReservationOccurrence.end_dt, db.TIME) slots = ((db.cast(start, db.TIME), db.cast(end, db.TIME)) for start, end in WORKING_TIME_PERIODS) # this basically handles all possible ways an occurrence overlaps with each one of the working time slots overlaps = sum(db.case([ ((rsv_start < start) & (rsv_end > end), db.extract('epoch', end - start)), ((rsv_start < start) & (rsv_end > start) & (rsv_end <= end), db.extract('epoch', rsv_end - start)), ((rsv_start >= start) & (rsv_start < end) & (rsv_end > end), db.extract('epoch', end - rsv_start)), ((rsv_start >= start) & (rsv_end <= end), db.extract('epoch', rsv_end - rsv_start)) ], else_=0) for start, end in slots) return reservations.with_entities(db.func.sum(overlaps)).scalar() or 0
def _clone_timetable(self, new_event): offset = new_event.start_dt - self.old_event.start_dt # no need to copy the type; it's set automatically based on the object attrs = get_simple_column_attrs(TimetableEntry) - {'type', 'start_dt'} break_strategy = defaultload('break_') break_strategy.joinedload('own_venue') break_strategy.joinedload('own_room').lazyload('*') entry_key_order = db.case({ TimetableEntryType.SESSION_BLOCK: db.func.concat('s', TimetableEntry.id), TimetableEntryType.CONTRIBUTION: db.func.concat('c', TimetableEntry.id), TimetableEntryType.BREAK: db.func.concat('b', TimetableEntry.id), }, value=TimetableEntry.type) query = (self.old_event.timetable_entries .options(joinedload('parent').lazyload('*'), break_strategy) .order_by(TimetableEntry.parent_id.is_(None).desc(), entry_key_order)) # iterate over all timetable entries; start with top-level # ones so we can build a mapping that can be used once we # reach nested entries entry_map = {} for old_entry in query: entry = TimetableEntry() entry.start_dt = old_entry.start_dt + offset entry.populate_from_attrs(old_entry, attrs) if old_entry.parent is not None: entry.parent = entry_map[old_entry.parent] if old_entry.session_block is not None: entry.session_block = self._session_block_map[old_entry.session_block] if old_entry.contribution is not None: entry.contribution = self._contrib_map[old_entry.contribution] if old_entry.break_ is not None: entry.break_ = self._clone_break(old_entry.break_) new_event.timetable_entries.append(entry) entry_map[old_entry] = entry
def is_active(cls): submissions = (db.session.query(db.func.count(db.m.SurveySubmission.id)) .filter(db.m.SurveySubmission.survey_id == cls.id) .correlate(Survey) .as_scalar()) limit_criterion = db.case([(cls.submission_limit.is_(None), True)], else_=(submissions < cls.submission_limit)) return ~cls.is_deleted & cls.questions.any() & cls.has_started & ~cls.has_ended & limit_criterion
def get_protection_parent_cte(self): cte_query = (select([Category.id, db.cast(literal(None), db.Integer).label('protection_parent')]) .where(Category.id == self.id) .cte(recursive=True)) rec_query = (select([Category.id, db.case({ProtectionMode.inheriting.value: func.coalesce(cte_query.c.protection_parent, self.id)}, else_=Category.id, value=Category.protection_mode)]) .where(Category.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_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 _mapper_configured(): # Session.effective_protection_mode -- the effective protection mode # (public/protected) of the session, even if it's inheriting it from its # parent event query = (select([db.case({ProtectionMode.inheriting.value: Event.effective_protection_mode}, else_=Session.protection_mode, value=Session.protection_mode)]) .where(Event.id == Session.event_id) .correlate(Session) .scalar_subquery()) Session.effective_protection_mode = column_property(query, deferred=True) Session.register_location_events() Session.register_protection_events()
def _make_occurrence_date_filter(): _default = rb_settings.get('notification_before_days') _default_weekly = rb_settings.get('notification_before_days_weekly') _default_monthly = rb_settings.get('notification_before_days_monthly') notification_before_days_room = db.case( { RepeatFrequency.WEEK.value: Room.notification_before_days_weekly, RepeatFrequency.MONTH.value: Room.notification_before_days_monthly }, else_=Room.notification_before_days, value=Reservation.repeat_frequency) notification_before_days_default = db.case( { RepeatFrequency.WEEK.value: _default_weekly, RepeatFrequency.MONTH.value: _default_monthly }, else_=_default, value=Reservation.repeat_frequency) notification_before_days = db.func.coalesce( notification_before_days_room, notification_before_days_default) days_until_occurrence = db.cast(ReservationOccurrence.start_dt, db.Date) - date.today() return days_until_occurrence == notification_before_days
def _mapper_configured(): # Contribution.effective_protection_mode -- the effective protection mode # (public/protected) of the contribution, even if it's inheriting it from # its event or session protection_mode_case = db.case([ ((Contribution.protection_mode == ProtectionMode.inheriting) & Contribution.session_id.is_(None), Event.effective_protection_mode), ((Contribution.protection_mode == ProtectionMode.inheriting) & Contribution.session_id.isnot(None), select([Session.effective_protection_mode ]).where(Session.id == Contribution.session_id).correlate( Contribution).scalar_subquery()) ], else_=Contribution.protection_mode) query = (select([protection_mode_case ]).where(Event.id == Contribution.event_id).correlate( Contribution).scalar_subquery()) Contribution.effective_protection_mode = column_property(query, deferred=True) Contribution.register_location_events() @listens_for(Contribution.session, 'set') def _set_session_block(target, value, *unused): if value is None: target.session_block = None @listens_for(Contribution.timetable_entry, 'set') @no_autoflush def _set_timetable_entry(target, value, *unused): if value is None: target.session_block = None else: if target.session is not None: target.session_block = value.parent.session_block @listens_for(Contribution.duration, 'set') def _set_duration(target, value, oldvalue, *unused): from indico.modules.events.util import register_time_change if oldvalue in (NEVER_SET, NO_VALUE): return if value != oldvalue and target.timetable_entry is not None: register_time_change(target.timetable_entry)
def duration(cls): from indico.modules.events.contributions import Contribution from indico.modules.events.sessions.models.blocks import SessionBlock from indico.modules.events.timetable.models.breaks import Break return db.case( { TimetableEntryType.SESSION_BLOCK.value: db.select([SessionBlock.duration]).where( SessionBlock.id == cls.session_block_id).correlate_except( SessionBlock).as_scalar(), TimetableEntryType.CONTRIBUTION.value: db.select([Contribution.duration]).where( Contribution.id == cls.contribution_id).correlate_except( Contribution).as_scalar(), TimetableEntryType.BREAK.value: db.select([Break.duration]).where(Break.id == cls.break_id). correlate_except(Break).as_scalar(), }, value=cls.type)
def duration(cls): from indico.modules.events.contributions import Contribution from indico.modules.events.sessions.models.blocks import SessionBlock from indico.modules.events.timetable.models.breaks import Break return db.case({ TimetableEntryType.SESSION_BLOCK.value: db.select([SessionBlock.duration]) .where(SessionBlock.id == cls.session_block_id) .correlate_except(SessionBlock) .as_scalar(), TimetableEntryType.CONTRIBUTION.value: db.select([Contribution.duration]) .where(Contribution.id == cls.contribution_id) .correlate_except(Contribution) .as_scalar(), TimetableEntryType.BREAK.value: db.select([Break.duration]) .where(Break.id == cls.break_id) .correlate_except(Break) .as_scalar(), }, value=cls.type)