Esempio n. 1
0
 def list(cls):
     return cls.query\
         .options(joinedload('house'),
                  joinedload('province'),
                  joinedload('memberships.committee'))\
         .filter(Member.current == True)\
         .order_by(Member.name)  # noqa
    def get_top_display(self, **kwargs):
        '''
        Returns all concepts or collections that form the top-level of a display
        hierarchy.

        As opposed to the :meth:`get_top_concepts`, this method can possibly
        return both concepts and collections. 

        :rtype: Returns a list of concepts and collections. For each an
            id is present and a label. The label is determined by looking at 
            the `**kwargs` parameter, the default language of the provider 
            and falls back to `en` if nothing is present.
        '''
        tco = self.session\
                  .query(ConceptModel)\
                  .options(joinedload('labels'))\
                  .filter(
                    ConceptModel.conceptscheme_id == self.conceptscheme_id,
                    ConceptModel.broader_concepts == None,
                    ConceptModel.member_of == None
                  ).all()
        tcl = self.session\
                  .query(CollectionModel)\
                  .options(joinedload('labels'))\
                  .filter(
                    CollectionModel.conceptscheme_id == self.conceptscheme_id,
                    CollectionModel.broader_concepts == None,
                    CollectionModel.member_of == None
                  ).all()
        res = tco + tcl
        lan = self._get_language(**kwargs)
        sort = self._get_sort(**kwargs)
        sort_order = self._get_sort_order(**kwargs)
        return [self._get_id_and_label(c, lan) for c in self._sort(res, sort, lan, sort_order=='desc')]
    def _get_concept_scheme(self):
        '''
        Find a :class:`skosprovider.skos.ConceptScheme` for this provider.

        :param id: Id of a conceptscheme.
        :rtype: :class:`skosprovider.skos.ConceptScheme`
        '''
        csm = self.session\
                  .query(ConceptSchemeModel)\
                  .options(joinedload('labels'))\
                  .options(joinedload('notes'))\
                  .options(joinedload('languages'))\
                  .options(joinedload('sources'))\
                  .get(self.conceptscheme_id)
        return ConceptScheme(
            uri=csm.uri,
            labels=[
                Label(l.label, l.labeltype_id, l.language_id)
                for l in csm.labels
            ],
            notes=[
                Note(n.note, n.notetype_id, n.language_id, n.markup)
                for n in csm.notes
            ],
            languages=[
                l.id for l in csm.languages
            ],
            sources=[
                Source(s.citation, s.markup) for s in csm.sources
            ]
        )
Esempio n. 4
0
    def _process(self):
        if self.event_new.type == 'conference':
            reviewing_status = get_reviewing_status(self.contrib, self._conf)
            show_paper = ((self._conf.getConfPaperReview().hasReviewing() and
                           self.contrib.can_manage(session.user, 'submit')) or reviewing_status == 'Accept')
            paper_upload_form = PaperUploadForm()
            paper_file_data = self.contrib.paper_files.filter_by(revision_id=None) if show_paper else None
        else:
            reviewing_status = paper_upload_form = paper_file_data = None
            show_paper = False

        ical_params = get_base_ical_parameters(session.user, 'contributions',
                                               '/export/event/{0}.ics'.format(self.event_new.id))
        contrib = (Contribution.query
                   .filter_by(id=self.contrib.id)
                   .options(joinedload('type'),
                            joinedload('session'),
                            joinedload('subcontributions'),
                            joinedload('timetable_entry').lazyload('*'))
                   .one())
        return self.view_class.render_template('display/contribution_display.html', self._conf,
                                               contribution=contrib,
                                               event=self.event_new,
                                               reviewing_status=reviewing_status,
                                               show_paper=show_paper,
                                               can_submit_paper=self.contrib.can_manage(session.user, 'submit'),
                                               paper_files=paper_file_data,
                                               paper_upload_form=paper_upload_form,
                                               **ical_params)
    def get_by_uri(self, uri):
        '''Get all information on a concept or collection, based on a
        :term:`URI`.

        This method will only find concepts or collections whose :term:`URI` is 
        actually stored in the database. It will not find anything that has
        no :term:`URI` in the database, but does have a matching :term:`URI`
        after generation.

        :rtype: :class:`skosprovider.skos.Concept` or
            :class:`skosprovider.skos.Collection` or `False` if the concept or
            collection is unknown to the provider.
        '''
        try:
            thing = self.session\
                        .query(Thing)\
                        .options(joinedload('labels'))\
                        .options(joinedload('notes'))\
                        .options(joinedload('sources'))\
                        .filter(
                            Thing.uri == uri,
                            Thing.conceptscheme_id == self.conceptscheme_id
                        ).one()
        except NoResultFound:
            return False
        return self._from_thing(thing)
Esempio n. 6
0
def get_edges(db_session, edges, order=desc(Edge.updated_at), page=0, page_size=10, partial_matching=False):
	edges = [('%%%s%%' % u, '%%%s%%' % v) for u, v in edges] if partial_matching else edges
	filter_group = [and_(Edge.head_node_id.ilike(u), Edge.tail_node_id.ilike(v)) for u, v in edges]
	filter_group.append(
		[and_(Edge.head_node.has(Node.label.ilike(u)), Edge.tail_node.has(Node.label.ilike(v))) for u, v in edges])
	return db_session.query(Graph).options(joinedload('head_node'), joinedload('tail_node')).filter(
		or_(*filter_group)).order_by(order).limit(page_size).offset(page * page_size)
Esempio n. 7
0
 def _process(self):
     self.user.settings.set('suggest_categories', True)
     tz = session.tzinfo
     hours, minutes = timedelta_split(tz.utcoffset(datetime.now()))[:2]
     categories = get_related_categories(self.user)
     categories_events = []
     if categories:
         category_ids = {c['categ'].id for c in categories.itervalues()}
         today = now_utc(False).astimezone(tz).date()
         query = (Event.query
                  .filter(~Event.is_deleted,
                          Event.category_chain_overlaps(category_ids),
                          Event.start_dt.astimezone(session.tzinfo) >= today)
                  .options(joinedload('category').load_only('id', 'title'),
                           joinedload('series'),
                           subqueryload('acl_entries'),
                           load_only('id', 'category_id', 'start_dt', 'end_dt', 'title', 'access_key',
                                     'protection_mode', 'series_id', 'series_pos', 'series_count'))
                  .order_by(Event.start_dt, Event.id))
         categories_events = get_n_matching(query, 10, lambda x: x.can_access(self.user))
     from_dt = now_utc(False) - relativedelta(weeks=1, hour=0, minute=0, second=0)
     linked_events = [(event, {'management': bool(roles & self.management_roles),
                               'reviewing': bool(roles & self.reviewer_roles),
                               'attendance': bool(roles & self.attendance_roles)})
                      for event, roles in get_linked_events(self.user, from_dt, 10).iteritems()]
     return WPUser.render_template('dashboard.html', 'dashboard',
                                   offset='{:+03d}:{:02d}'.format(hours, minutes), user=self.user,
                                   categories=categories,
                                   categories_events=categories_events,
                                   suggested_categories=get_suggested_categories(self.user),
                                   linked_events=linked_events)
Esempio n. 8
0
def _queue_items_dict_with_track_dict(queue_query):
    queue_dicts = [queue_item.to_dict('full') for queue_item in queue_query]

    # Fetch all tracks with id's in the queue
    trackids = [queue_item['track_id'] for queue_item in queue_dicts]
    tracks = {}
    if trackids:
        tracks = DBSession.query(Track).\
                            filter(Track.id.in_(trackids)).\
                            options(\
                                joinedload(Track.tags),\
                                joinedload(Track.attachments),\
                                joinedload('tags.parent'),\
                                #defer(Track.lyrics),\
                            )
        tracks = {track['id']:track for track in [track.to_dict('full', exclude_fields='lyrics') for track in tracks]}

    # HACK
    # AllanC - Hack to overlay title on API return.
    # This technically cant be part of the model because the title rendering in 'helpers' uses the dict version of a track object rather than the DB object
    # This is half the best place for it. We want the model to be as clean as possible
    # But we need the 'title' field to be consistant for all API returns for tracks ... more consideration needed
    #
    # Solution: Setup SQLAlchemy event to render the title before commiting a track to the DB - like a DB trigger by handled Python size for cross db compatibility
    #           Stub created in model_track.py
    #           This is to be removed ...
    for track in tracks.values():
        track['title'] = track_title(track['tags'])

    # Attach track to queue_item
    for queue_item in queue_dicts:
        queue_item['track'] = tracks.get(queue_item['track_id'], {})

    return queue_dicts
Esempio n. 9
0
def get_graphs_by_edges_and_nodes_and_names(db_session, group_ids=None, names=None, nodes=None, edges=None, tags=None,
                                            order=desc(Graph.updated_at), page=0, page_size=10, partial_matching=False,
                                            owner_email=None, is_public=None):
	query = db_session.query(Graph)

	edges = [] if edges is None else edges
	nodes = [] if nodes is None else nodes
	names = [] if names is None else names
	tags = [] if tags is None else tags

	edges = [('%%%s%%' % u, '%%%s%%' % v) for u, v in edges] if partial_matching else edges
	nodes = ['%%%s%%' % node for node in nodes] if partial_matching else nodes
	names = ['%%%s%%' % name for name in names] if partial_matching else names
	tags = ['%%%s%%' % tag for tag in tags]

	graph_filter_group = []
	if is_public is not None:
		graph_filter_group.append(Graph.is_public == is_public)
	if owner_email is not None:
		graph_filter_group.append(Graph.owner_email == owner_email)
	if group_ids is not None:
		query = query.filter(Graph.shared_with_groups.any(Group.id.in_(group_ids)))
	if len(graph_filter_group) > 0:
		query = query.filter(*graph_filter_group)

	names_filter_group = [Graph.name.ilike(name) for name in names]
	tags_filter_group = [GraphTag.name.ilike(tag) for tag in tags]
	nodes_filter_group = [Node.label.ilike(node) for node in nodes]
	nodes_filter_group.extend([Node.name.ilike(node) for node in nodes])
	edges_filter_group = [and_(Edge.head_node.has(Node.name.ilike(u)), Edge.tail_node.has(Node.name.ilike(v))) for u, v
	                      in edges]
	edges_filter_group.extend(
		[and_(Edge.tail_node.has(Node.name.ilike(u)), Edge.head_node.has(Node.name.ilike(v))) for u, v in edges])
	edges_filter_group.extend(
		[and_(Edge.head_node.has(Node.label.ilike(u)), Edge.tail_node.has(Node.label.ilike(v))) for u, v in edges])
	edges_filter_group.extend(
		[and_(Edge.tail_node.has(Node.label.ilike(u)), Edge.head_node.has(Node.label.ilike(v))) for u, v in edges])

	options_group = []
	if len(nodes_filter_group) > 0:
		options_group.append(joinedload('nodes'))
	if len(edges_filter_group) > 0:
		options_group.append(joinedload('edges'))
	if len(options_group) > 0:
		query = query.options(*options_group)

	combined_filter_group = []
	if len(nodes_filter_group) > 0:
		combined_filter_group.append(Graph.nodes.any(or_(*nodes_filter_group)))
	if len(edges_filter_group) > 0:
		combined_filter_group.append(Graph.edges.any(or_(*edges_filter_group)))
	if len(names_filter_group) > 0:
		combined_filter_group.append(*names_filter_group)
	if len(tags_filter_group) > 0:
		combined_filter_group.append(*tags_filter_group)

	if len(combined_filter_group) > 0:
		query = query.filter(or_(*combined_filter_group))

	return query.order_by(order).limit(page_size).offset(page * page_size).all()
Esempio n. 10
0
    def list_tasks(self, limit=None, details=False, category=None,
                   offset=None, status=None, sample_id=None, not_status=None):
        """Retrieve list of task.
        @param limit: specify a limit of entries.
        @param details: if details about must be included
        @param category: filter by category
        @param offset: list offset
        @param status: filter by task status
        @param sample_id: filter tasks for a sample
        @param not_status: exclude this task status from filter
        @return: list of tasks.
        """
        session = self.Session()
        try:
            search = session.query(Task)

            if status:
                search = search.filter_by(status=status)
            if not_status:
                search = search.filter(Task.status != not_status)
            if category:
                search = search.filter_by(category=category)
            if details:
                search = search.options(joinedload("guest"), joinedload("errors"), joinedload("tags"))
            if sample_id is not None:
                search = search.filter_by(sample_id=sample_id)

            tasks = search.order_by("added_on desc").limit(limit).offset(offset).all()
        except SQLAlchemyError as e:
            log.debug("Database error listing tasks: {0}".format(e))
            return []
        finally:
            session.close()
        return tasks
Esempio n. 11
0
    def test_weakref_with_cycles_o2o(self):
        Address, addresses, users, User = (self.classes.Address,
                                self.tables.addresses,
                                self.tables.users,
                                self.classes.User)

        s = sessionmaker()()
        mapper(User, users, properties={
            "address": relationship(Address, backref="user", uselist=False)
        })
        mapper(Address, addresses)
        s.add(User(name="ed", address=Address(email_address="ed1")))
        s.commit()

        user = s.query(User).options(joinedload(User.address)).one()
        user.address.user
        eq_(user, User(name="ed", address=Address(email_address="ed1")))

        del user
        gc_collect()
        assert len(s.identity_map) == 0

        user = s.query(User).options(joinedload(User.address)).one()
        user.address.email_address = 'ed2'
        user.address.user  # lazyload

        del user
        gc_collect()
        assert len(s.identity_map) == 2

        s.commit()
        user = s.query(User).options(joinedload(User.address)).one()
        eq_(user, User(name="ed", address=Address(email_address="ed2")))
Esempio n. 12
0
    def drawlines(self, h):
        sl = (
            td.s.query(StockLine)
            .filter(StockLine.location.in_(self.locations))
            .filter(StockLine.capacity == None)
            .order_by(StockLine.name)
            .options(joinedload("stockonsale"))
            .options(joinedload("stockonsale.stocktype"))
            .options(undefer_group("qtys"))
            .all()
        )
        f = ui.tableformatter("pl l L r rp")
        header = f("Line", "StockID", "Stock", "Used", "Remaining")

        def fl(line):
            if line.stockonsale:
                sos = line.stockonsale[0]
                return (line.name, sos.id, sos.stocktype.format(), sos.used, sos.remaining)
            return (line.name, "", "", "", "")

        ml = [header] + [f(*fl(line)) for line in sl]
        y = 0
        for l in ml:
            for line in l.display(self.w):
                self.addstr(y, 0, line)
                y = y + 1
            if y >= h:
                break
Esempio n. 13
0
def image_get(context, image_id, session=None):
    """Get an image or raise if it does not exist."""
    session = session or get_session()
    try:
        #NOTE(bcwaldon): this is to prevent false matches when mysql compares
        # an integer to a string that begins with that integer
        image_id = int(image_id)
    except (TypeError, ValueError):
        raise exception.NotFound("No image found")

    try:
        image = session.query(models.Image).\
                       options(joinedload(models.Image.properties)).\
                       options(joinedload(models.Image.members)).\
                       filter_by(deleted=_deleted(context)).\
                       filter_by(id=image_id).\
                       one()
    except exc.NoResultFound:
        raise exception.NotFound("No image found with ID %s" % image_id)

    # Make sure they can look at it
    if not context.is_image_visible(image):
        raise exception.NotAuthorized("Image not visible to you")

    return image
Esempio n. 14
0
    def _paginate_offset(self, clazz, schema, adapt_schema):
        """Return a batch of documents with the given `offset` and `limit`.
        """
        validated = self.request.validated
        offset = validated['offset'] if 'offset' in validated else 0
        limit = min(
            validated['limit'] if 'limit' in validated else LIMIT_DEFAULT,
            LIMIT_MAX)

        base_query = DBSession.query(clazz)

        documents = base_query. \
            options(joinedload(getattr(clazz, 'locales'))). \
            options(joinedload(getattr(clazz, 'geometry'))). \
            order_by(clazz.document_id.desc()). \
            slice(offset, offset + limit). \
            all()
        set_available_cultures(documents, loaded=True)

        if validated.get('lang') is not None:
            set_best_locale(documents, validated.get('lang'))

        total = base_query.count()

        return {
            'documents': [
                to_json_dict(
                    doc,
                    schema if not adapt_schema else adapt_schema(schema, doc)
                ) for doc in documents
            ],
            'total': total
        }
Esempio n. 15
0
    def _get_ips_except_admin(self, node_id=None,
                              network_id=None, joined=False):
        """Method for receiving IP addresses for node or network
        excluding Admin Network IP address.

        :param node_id: Node database ID.
        :type  node_id: int
        :param network_id: Network database ID.
        :type  network_id: int
        :returns: List of free IP addresses as SQLAlchemy objects.
        """
        ips = db().query(IPAddr).order_by(IPAddr.id)
        if joined:
            ips = ips.options(
                joinedload('network_data'),
                joinedload('network_data.network_group'))
        if node_id:
            ips = ips.filter_by(node=node_id)
        if network_id:
            ips = ips.filter_by(network=network_id)

        admin_net_id = self.get_admin_network_id(False)
        if admin_net_id:
            ips = ips.filter(
                not_(IPAddr.network == admin_net_id)
            )

        return ips.all()
Esempio n. 16
0
def drafts(namespace_id, thread_public_id, limit, offset, db_session):
    query = db_session.query(Message).filter(Message.is_draft == True)

    thread_criteria = [Thread.namespace_id == namespace_id]

    if thread_public_id is not None:
        # TODO(emfree) this is a common case that we should handle
        # separately by just fetching the thread's messages and only
        # filtering more if needed.
        thread_criteria.append(Thread.public_id == thread_public_id)

    thread_predicate = and_(*thread_criteria)
    thread_query = db_session.query(Thread).filter(thread_predicate)

    query = query.join(thread_query.subquery())

    # Eager-load some objects in order to make constructing API
    # representations faster.
    query = query.options(
        joinedload(Message.parts).load_only("public_id", "content_disposition"),
        joinedload(Message.thread).load_only("public_id", "discriminator"),
    )

    query = query.limit(limit)
    if offset:
        query = query.offset(offset)

    return [m for m in query.all() if m.is_latest]
Esempio n. 17
0
    def _load_data(self, id):
        c.users_group.permissions = {
            'repositories': {},
            'repositories_groups': {}
        }

        ugroup_repo_perms = UsersGroupRepoToPerm.query()\
            .options(joinedload(UsersGroupRepoToPerm.permission))\
            .options(joinedload(UsersGroupRepoToPerm.repository))\
            .filter(UsersGroupRepoToPerm.users_group_id == id)\
            .all()

        for gr in ugroup_repo_perms:
            c.users_group.permissions['repositories'][gr.repository.repo_name]  \
                = gr.permission.permission_name

        ugroup_group_perms = UsersGroupRepoGroupToPerm.query()\
            .options(joinedload(UsersGroupRepoGroupToPerm.permission))\
            .options(joinedload(UsersGroupRepoGroupToPerm.group))\
            .filter(UsersGroupRepoGroupToPerm.users_group_id == id)\
            .all()

        for gr in ugroup_group_perms:
            c.users_group.permissions['repositories_groups'][gr.group.group_name] \
                = gr.permission.permission_name

        c.group_members_obj = [x.user for x in c.users_group.members]
        c.group_members = [(x.user_id, x.username) for x in
                           c.group_members_obj]
        c.available_members = [(x.user_id, x.username) for x in
                               User.query().all()]
Esempio n. 18
0
def port_find(context, limit=None, sorts=None, marker_obj=None, fields=None,
              **filters):
    query = context.session.query(models.Port).options(
        orm.joinedload(models.Port.ip_addresses))
    model_filters = _model_query(context, models.Port, filters)
    if filters.get("ip_address_id"):
        model_filters.append(models.Port.ip_addresses.any(
            models.IPAddress.id.in_(filters["ip_address_id"])))

    if filters.get("device_id"):
        model_filters.append(models.Port.device_id.in_(filters["device_id"]))

    if filters.get("service"):
        model_filters.append(models.Port.associations.any(
            models.PortIpAssociation.service == filters["service"]))

    if "join_security_groups" in filters:
        query = query.options(orm.joinedload(models.Port.security_groups))

    if fields and "port_subnets" in fields:
        query = query.options(orm.joinedload("ip_addresses.subnet"))
        query = query.options(
            orm.joinedload("ip_addresses.subnet.dns_nameservers"))
        query = query.options(
            orm.joinedload("ip_addresses.subnet.routes"))
    return paginate_query(query.filter(*model_filters), models.Port, limit,
                          sorts, marker_obj)
Esempio n. 19
0
def _subnet_find(context, limit, sorts, marker, page_reverse, fields,
                 defaults=None, provider_query=False, **filters):
    query = context.session.query(models.Subnet)
    model_filters = _model_query(context, models.Subnet, filters, query)

    if defaults:
        invert_defaults = False
        if INVERT_DEFAULTS in defaults:
            invert_defaults = True
            defaults.pop(0)
        if filters and invert_defaults:
            query = query.filter(and_(not_(models.Subnet.id.in_(defaults)),
                                      and_(*model_filters)))
        elif not provider_query and filters and not invert_defaults:
            query = query.filter(or_(models.Subnet.id.in_(defaults),
                                     and_(*model_filters)))

        elif not invert_defaults:
            query = query.filter(models.Subnet.id.in_(defaults))
    else:
        query = query.filter(*model_filters)

    if "join_dns" in filters:
        query = query.options(orm.joinedload(models.Subnet.dns_nameservers))

    if "join_routes" in filters:
        query = query.options(orm.joinedload(models.Subnet.routes))

    if "join_pool" in filters:
        query = query.options(orm.undefer('_allocation_pool_cache'))

    return paginate_query(query, models.Subnet, limit, sorts, marker)
Esempio n. 20
0
    def render(self, session, **arguments):
        q = session.query(Chassis)

        q = q.options(subqueryload('model'),
                      joinedload('model.machine_specs'),
                      subqueryload('location'),
                      joinedload('slots'),
                      subqueryload('slots.machine'),

                      # A rare case when we don't need primary name/host
                      lazyload('slots.machine.primary_name'),
                      lazyload('slots.machine.host'),

                      subqueryload('interfaces'),
                      joinedload('interfaces.assignments'),
                      joinedload('interfaces.assignments.network'),
                      joinedload('interfaces.assignments.dns_records'))

        # Prefer the primary name for ordering
        q = q.outerjoin(DnsRecord, (Fqdn, DnsRecord.fqdn_id == Fqdn.id),
                        DnsDomain)
        q = q.options(contains_eager('primary_name'),
                      contains_eager('primary_name.fqdn'),
                      contains_eager('primary_name.fqdn.dns_domain'))
        q = q.order_by(Fqdn.name, DnsDomain.name, Chassis.label)
        return q.all()
Esempio n. 21
0
def get_indexable_contents(session):
    from assembl.models import AgentProfile, Idea, Post
    from assembl.models.post import PublicationStates

    query = session.query(Idea
        ).filter(Idea.tombstone_condition()
        ).filter(Idea.hidden==False
        ).options(
            joinedload(Idea.title).joinedload("entries"),
            joinedload(Idea.synthesis_title).joinedload("entries"),
            joinedload(Idea.description).joinedload("entries")
        )

    for idea in query:
        yield idea

    query = session.query(AgentProfile)
    for user in query:
        yield user

    AllPost = with_polymorphic(Post, '*')
    query = session.query(AllPost
        ).filter(AllPost.tombstone_condition()
        ).filter(AllPost.hidden==False
        ).filter(AllPost.publication_state == PublicationStates.PUBLISHED
        ).options(
            joinedload(AllPost.subject).joinedload("entries"),
            joinedload(AllPost.body).joinedload("entries")
        )
    for post in query:
        for extract in post.extracts:
            yield extract

        yield post
Esempio n. 22
0
    def __call__(self, model, req):
        query = req.db.query(model).filter(model.id == req.matchdict['id'])
        custom_query = self.refined_query(query, model, req)

        if query == custom_query:
            # no customizations done, apply the defaults
            if model == common.Contribution:
                query = query.options(
                    joinedload_all(
                        common.Contribution.valuesets,
                        common.ValueSet.parameter,
                    ),
                    joinedload_all(
                        common.Contribution.valuesets,
                        common.ValueSet.values,
                        common.Value.domainelement),
                    joinedload_all(
                        common.Contribution.references,
                        common.ContributionReference.source),
                    joinedload(common.Contribution.data),
                )
            if model == common.ValueSet:
                query = query.options(
                    joinedload(common.ValueSet.values),
                    joinedload(common.ValueSet.parameter),
                    joinedload(common.ValueSet.language),
                )
        else:
            query = custom_query  # pragma: no cover

        return query.one()
Esempio n. 23
0
def cycle_tasks_cache(notifications):
  """Compile and return Task instances related to given notification records.

  Args:
    notifications: a list of Notification instances for which to fetch the
      corresponding CycleTaskGroupObjectTask instances
      accessible by their ID as a key
  Returns:
    Dictionary containing task instances with their IDs used as keys.
  """
  task_ids = [n.object_id for n in notifications
              if n.object_type == "CycleTaskGroupObjectTask"]

  if not task_ids:
    return {}

  results = db.session\
      .query(CycleTaskGroupObjectTask)\
      .options(
          orm.joinedload("related_sources"),
          orm.joinedload("related_destinations")
      )\
      .filter(CycleTaskGroupObjectTask.id.in_(task_ids))

  return {task.id: task for task in results}
Esempio n. 24
0
    def get_for_linked_object(cls, linked_object, preload_event=True):
        """Gets the note for the given object.

        This only returns a note that hasn't been deleted.

        :param linked_object: An event, session, contribution or
                              subcontribution.
        :param preload_event: If all notes for the same event should
                              be pre-loaded and cached in the app
                              context.
        """
        event = linked_object.event
        try:
            return g.event_notes[event].get(linked_object)
        except (AttributeError, KeyError):
            if not preload_event:
                return linked_object.note if linked_object.note and not linked_object.note.is_deleted else None
            if 'event_notes' not in g:
                g.event_notes = {}
            query = (event.all_notes
                     .filter_by(is_deleted=False)
                     .options(joinedload(EventNote.linked_event),
                              joinedload(EventNote.session),
                              joinedload(EventNote.contribution),
                              joinedload(EventNote.subcontribution)))
            g.event_notes[event] = {n.object: n for n in query}
            return g.event_notes[event].get(linked_object)
Esempio n. 25
0
    def test_get_node_networks_optimization(self):
        self.env.create(
            cluster_kwargs={},
            nodes_kwargs=[
                {"pending_addition": True, "api": True},
                {"pending_addition": True, "api": True}
            ]
        )

        self.env.network_manager.assign_ips(
            [n.id for n in self.env.nodes],
            "management"
        )

        nodes = self.db.query(Node).options(
            joinedload('cluster'),
            joinedload('interfaces'),
            joinedload('interfaces.assigned_networks')).all()

        ips_mapped = self.env.network_manager.get_grouped_ips_by_node()
        networks_grouped = self.env.network_manager.\
            get_networks_grouped_by_cluster()
        full_results = []
        for node in nodes:
            result = self.env.network_manager.get_node_networks_optimized(
                node, ips_mapped.get(node.id, []),
                networks_grouped.get(node.cluster_id, []))
            full_results.append(result)
        self.assertEqual(len(full_results), 2)
Esempio n. 26
0
def question_dump():
    cols = ['name', 'legislature', 'date', 'title', 'score']
    ask_query = (
        models.Ask.query
        .join(models.Ask.question)
        .options(
            joinedload('question'),
            joinedload('mandate'),
            joinedload('mandate.person'),
            joinedload('match_row'),
        )
        .order_by(models.Question.date.desc())
    )
    def make_row(ask):
        score = ask.match.score
        return {
            'name': ask.mandate.person.name,
            'legislature': str(ask.mandate.year),
            'date': str(ask.question.date),
            'title': str(ask.question.title),
            'score': '' if score is None else str(score),
        }
    rows = (make_row(ask) for ask in ask_query.yield_per(10))
    data = buffer_on_disk(csv_lines(cols, rows))
    return flask.Response(data, mimetype='text/csv')
Esempio n. 27
0
def _get(context, artifact_id, session, type_name=None, type_version=None,
         show_level=ga.Showlevel.BASIC):
    values = dict(id=artifact_id)
    if type_name is not None:
        values['type_name'] = type_name
    if type_version is not None:
        values['type_version'] = type_version
    _set_version_fields(values)
    try:
        if show_level == ga.Showlevel.NONE:
            query = (
                session.query(models.Artifact).
                options(joinedload(models.Artifact.tags)).
                filter_by(**values))
        else:
            query = (
                session.query(models.Artifact).
                options(joinedload(models.Artifact.properties)).
                options(joinedload(models.Artifact.tags)).
                options(joinedload(models.Artifact.blobs).
                        joinedload(models.ArtifactBlob.locations)).
                filter_by(**values))

        artifact = query.one()
    except orm.exc.NoResultFound:
        LOG.warn(_LW("Artifact with id=%s not found") % artifact_id)
        raise exception.ArtifactNotFound(id=artifact_id)
    if not _check_visibility(context, artifact):
        LOG.warn(_LW("Artifact with id=%s is not accessible") % artifact_id)
        raise exception.ArtifactForbidden(id=artifact_id)
    return artifact
Esempio n. 28
0
def index(request):
    project_ids = [
        r[0]
        for r in (
            request.db.query(Project.id)
            .order_by(Project.zscore.desc().nullslast(), func.random())
            .limit(5)
            .all()
        )
    ]
    release_a = aliased(
        Release,
        request.db.query(Release)
        .distinct(Release.project_id)
        .filter(Release.project_id.in_(project_ids))
        .order_by(
            Release.project_id,
            Release.is_prerelease.nullslast(),
            Release._pypi_ordering.desc(),
        )
        .subquery(),
    )
    trending_projects = (
        request.db.query(release_a)
        .options(joinedload(release_a.project))
        .order_by(func.array_idx(project_ids, release_a.project_id))
        .all()
    )

    latest_releases = (
        request.db.query(Release)
        .options(joinedload(Release.project))
        .order_by(Release.created.desc())
        .limit(5)
        .all()
    )

    counts = dict(
        request.db.query(RowCount.table_name, RowCount.count)
        .filter(
            RowCount.table_name.in_(
                [
                    Project.__tablename__,
                    Release.__tablename__,
                    File.__tablename__,
                    User.__tablename__,
                ]
            )
        )
        .all()
    )

    return {
        "latest_releases": latest_releases,
        "trending_projects": trending_projects,
        "num_projects": counts.get(Project.__tablename__, 0),
        "num_releases": counts.get(Release.__tablename__, 0),
        "num_files": counts.get(File.__tablename__, 0),
        "num_users": counts.get(User.__tablename__, 0),
    }
Esempio n. 29
0
def unit_update_stat():
    dbsession = DBSession()

    units = dbsession.query(Unit).options(joinedload(Unit.protocol_o), joinedload(Unit.protocol_i))
    dbsession.query(UnitStat).delete()

    for unit in units:
        stat = UnitStat(unit_id=unit.id)
        stat.official = unit.protocol_o != None    
        stat.independent = unit.protocol_i != None    
        stat.diff = False
        stat.diff_value = 0

        if unit.protocol_i and unit.protocol_o:

            check = dict()
            for i in unit.protocol_o.vote:
                check[i.party_id] = i.vote_c

            for i in unit.protocol_i.vote:
                if check[i.party_id] != i.vote_c:
                    stat.diff_value += abs(i.vote_c - i.party_id)
                    stat.diff = True
        
        dbsession.add(stat)

    dbsession.add(stat)
Esempio n. 30
0
def _image_get(context, image_id, session=None, force_show_deleted=False):
    """Get an image or raise if it does not exist."""
    _check_image_id(image_id)
    session = session or get_session()

    try:
        query = session.query(models.Image)\
                       .options(sa_orm.joinedload(models.Image.properties))\
                       .options(sa_orm.joinedload(models.Image.locations))\
                       .filter_by(id=image_id)

        # filter out deleted images if context disallows it
        if not force_show_deleted and not _can_show_deleted(context):
            query = query.filter_by(deleted=False)

        image = query.one()

    except sa_orm.exc.NoResultFound:
        msg = "No image found with ID %s" % image_id
        LOG.debug(msg)
        raise exception.NotFound(msg)

    # Make sure they can look at it
    if not is_image_visible(context, image):
        msg = "Forbidding request, image %s not visible" % image_id
        LOG.debug(msg)
        raise exception.Forbidden(msg)

    return image
Esempio n. 31
0
    def get(self, obj_id=None):
        """
        ---
        single:
          description: Retrieve a candidate
          tags:
            - candidates
          parameters:
            - in: path
              name: obj_id
              required: true
              schema:
                type: string
          responses:
            200:
              content:
                application/json:
                  schema: SingleObj
            400:
              content:
                application/json:
                  schema: Error
        multiple:
          tags:
            - candidates
          description: Retrieve all candidates
          parameters:
          - in: query
            name: numPerPage
            nullable: true
            schema:
              type: integer
            description: |
              Number of candidates to return per paginated request. Defaults to 25
          - in: query
            name: pageNumber
            nullable: true
            schema:
              type: integer
            description: Page number for paginated query results. Defaults to 1
          - in: query
            name: totalMatches
            nullable: true
            schema:
              type: integer
            description: |
              Used only in the case of paginating query results - if provided, this
              allows for avoiding a potentially expensive query.count() call.
          - in: query
            name: savedStatus
            nullable: true
            schema:
                type: string
                enum: [all, savedToAllSelected, savedToAnySelected, savedToAnyAccessible, notSavedToAnyAccessible, notSavedToAnySelected, notSavedToAllSelected]
            description: |
                String indicating the saved status to filter candidate results for. Must be one of the enumerated values.
          - in: query
            name: startDate
            nullable: true
            schema:
              type: string
            description: |
              Arrow-parseable date string (e.g. 2020-01-01). If provided, filter by
              Candidate.passed_at >= startDate
          - in: query
            name: endDate
            nullable: true
            schema:
              type: string
            description: |
              Arrow-parseable date string (e.g. 2020-01-01). If provided, filter by
              Candidate.passed_at <= endDate
          - in: query
            name: groupIDs
            nullable: true
            schema:
              type: array
              items:
                type: integer
            explode: false
            style: simple
            description: |
              Comma-separated string of group IDs (e.g. "1,2"). Defaults to all of user's
              groups if filterIDs is not provided.
          - in: query
            name: filterIDs
            nullable: true
            schema:
              type: array
              items:
                type: integer
            explode: false
            style: simple
            description: |
              Comma-separated string of filter IDs (e.g. "1,2"). Defaults to all of user's
              groups' filters if groupIDs is not provided.
          - in: query
            name: annotationExcludeOrigin
            nullable: true
            schema:
              type: string
            description: |
              Only load objects that do not have annotations from this origin.
              If the annotationsExcludeOutdatedDate is also given, then annotations with
              this origin will still be loaded if they were modified before that date.
          - in: query
            name: annotationExcludeOutdatedDate
            nullable: true
            schema:
              type: string
            description: |
              An Arrow parseable string designating when an existing annotation is outdated.
              Only relevant if giving the annotationExcludeOrigin argument.
              Will treat objects with outdated annotations as if they did not have that annotation,
              so it will load an object if it doesn't have an annotation with the origin specified or
              if it does have it but the annotation modified date < annotationsExcludeOutdatedDate
          - in: query
            name: sortByAnnotationOrigin
            nullable: true
            schema:
              type: string
            description: |
              The origin of the Annotation to sort by
          - in: query
            name: sortByAnnotationKey
            nullable: true
            schema:
              type: string
            description: |
              The key of the Annotation data value to sort by
          - in: query
            name: sortByAnnotationOrder
            nullable: true
            schema:
              type: string
            description: |
              The sort order for annotations - either "asc" or "desc".
              Defaults to "asc".
          - in: query
            name: annotationFilterList
            nullable: true
            schema:
              type: array
              items:
                type: string
            explode: false
            style: simple
            description: |
              Comma-separated string of JSON objects representing annotation filters.
              Filter objects are expected to have keys { origin, key, value } for
              non-numeric value types, or { origin, key, min, max } for numeric values.
          - in: query
            name: includePhotometry
            nullable: true
            schema:
              type: boolean
            description: |
              Boolean indicating whether to include associated photometry. Defaults to
              false.
          - in: query
            name: includeSpectra
            nullable: true
            schema:
              type: boolean
            description: |
              Boolean indicating whether to include associated spectra. Defaults to false.
          - in: query
            name: classifications
            nullable: true
            schema:
              type: array
              items:
                type: string
            explode: false
            style: simple
            description: |
              Comma-separated string of classification(s) to filter for candidates matching
              that/those classification(s).
          - in: query
            name: redshiftRange
            nullable: True
            schema:
                type: list
            description: |
                lowest and highest redshift to return, e.g. "(0,0.5)"
          responses:
            200:
              content:
                application/json:
                  schema:
                    allOf:
                      - $ref: '#/components/schemas/Success'
                      - type: object
                        properties:
                          data:
                            type: object
                            properties:
                              candidates:
                                type: array
                                items:
                                  allOf:
                                    - $ref: '#/components/schemas/Obj'
                                    - type: object
                                      properties:
                                        is_source:
                                          type: boolean
                              totalMatches:
                                type: integer
                              pageNumber:
                                type: integer
                              numPerPage:
                                type: integer
            400:
              content:
                application/json:
                  schema: Error
        """
        user_accessible_group_ids = [
            g.id for g in self.current_user.accessible_groups
        ]
        include_photometry = self.get_query_argument("includePhotometry",
                                                     False)
        include_spectra = self.get_query_argument("includeSpectra", False)

        if obj_id is not None:
            query_options = [
                joinedload(Candidate.obj).joinedload(Obj.thumbnails)
            ]
            if include_photometry:
                query_options.append(
                    joinedload(Candidate.obj).joinedload(
                        Obj.photometry).joinedload(Photometry.instrument))
            if include_spectra:
                query_options.append(
                    joinedload(Candidate.obj).joinedload(
                        Obj.spectra).joinedload(Spectrum.instrument))
            c = Candidate.get_obj_if_readable_by(
                obj_id,
                self.current_user,
                options=query_options,
            )
            if c is None:
                return self.error("Invalid ID")
            accessible_candidates = (
                DBSession().query(Candidate).join(Filter).filter(
                    Candidate.obj_id == obj_id,
                    Filter.group_id.in_(
                        [g.id for g in self.current_user.accessible_groups]),
                ).all())
            filter_ids = [cand.filter_id for cand in accessible_candidates]

            passing_alerts = [{
                "filter_id": cand.filter_id,
                "passing_alert_id": cand.passing_alert_id,
                "passed_at": cand.passed_at,
            } for cand in accessible_candidates]

            candidate_info = c.to_dict()
            candidate_info["filter_ids"] = filter_ids
            candidate_info["passing_alerts"] = passing_alerts
            candidate_info["comments"] = sorted(
                [
                    cmt.to_dict()
                    for cmt in c.get_comments_readable_by(self.current_user)
                ],
                key=lambda x: x["created_at"],
                reverse=True,
            )
            candidate_info["annotations"] = sorted(
                c.get_annotations_readable_by(self.current_user),
                key=lambda x: x.origin,
            )
            candidate_info["is_source"] = len(c.sources) > 0
            if candidate_info["is_source"]:
                candidate_info["saved_groups"] = (
                    DBSession().query(Group).join(Source).filter(
                        Source.obj_id == obj_id).filter(
                            Source.active.is_(True)).filter(
                                Group.id.in_(user_accessible_group_ids)).all())
                candidate_info[
                    "classifications"] = c.get_classifications_readable_by(
                        self.current_user)
            candidate_info["last_detected_at"] = c.last_detected_at
            candidate_info["gal_lon"] = c.gal_lon_deg
            candidate_info["gal_lat"] = c.gal_lat_deg
            candidate_info["luminosity_distance"] = c.luminosity_distance
            candidate_info["dm"] = c.dm
            candidate_info[
                "angular_diameter_distance"] = c.angular_diameter_distance
            self.verify_permissions()
            return self.success(data=candidate_info)

        page_number = self.get_query_argument("pageNumber", None) or 1
        n_per_page = self.get_query_argument("numPerPage", None) or 25
        saved_status = self.get_query_argument("savedStatus", "all")
        total_matches = self.get_query_argument("totalMatches", None)
        start_date = self.get_query_argument("startDate", None)
        end_date = self.get_query_argument("endDate", None)
        group_ids = self.get_query_argument("groupIDs", None)
        filter_ids = self.get_query_argument("filterIDs", None)
        annotation_exclude_origin = self.get_query_argument(
            'annotationExcludeOrigin', None)
        annotation_exclude_date = self.get_query_argument(
            'annotationExcludeOutdatedDate', None)
        sort_by_origin = self.get_query_argument("sortByAnnotationOrigin",
                                                 None)
        annotation_filter_list = self.get_query_argument(
            "annotationFilterList", None)
        classifications = self.get_query_argument("classifications", None)
        redshift_range_str = self.get_query_argument("redshiftRange", None)
        user_accessible_group_ids = [
            g.id for g in self.current_user.accessible_groups
        ]
        user_accessible_filter_ids = [
            filtr.id for g in self.current_user.accessible_groups
            for filtr in g.filters if g.filters is not None
        ]
        if group_ids is not None:
            if isinstance(group_ids, str) and "," in group_ids:
                group_ids = [int(g_id) for g_id in group_ids.split(",")]
            elif isinstance(group_ids, str) and group_ids.isdigit():
                group_ids = [int(group_ids)]
            else:
                return self.error(
                    "Invalid groupIDs value -- select at least one group")
            filter_ids = [
                f.id
                for f in Filter.query.filter(Filter.group_id.in_(group_ids))
            ]
        elif filter_ids is not None:
            if "," in filter_ids:
                filter_ids = [int(f_id) for f_id in filter_ids.split(",")]
            elif filter_ids.isdigit():
                filter_ids = [int(filter_ids)]
            else:
                return self.error("Invalid filterIDs paramter value.")
            group_ids = [
                f.group_id
                for f in Filter.query.filter(Filter.id.in_(filter_ids))
            ]
        else:
            # If 'groupIDs' & 'filterIDs' params not present in request, use all user groups
            group_ids = user_accessible_group_ids
            filter_ids = user_accessible_filter_ids

        # Ensure user has access to specified groups/filters
        if not (all([gid in user_accessible_group_ids
                     for gid in group_ids]) and
                all([fid in user_accessible_filter_ids
                     for fid in filter_ids])):
            return self.error(
                "Insufficient permissions - you must only specify "
                "groups/filters that you have access to.")
        try:
            page = int(page_number)
        except ValueError:
            return self.error("Invalid page number value.")
        try:
            n_per_page = int(n_per_page)
        except ValueError:
            return self.error("Invalid numPerPage value.")

        # We'll join in the nested data for Obj (like photometry) later
        q = (DBSession().query(Obj).join(Candidate).filter(
            Obj.id.in_(DBSession().query(Candidate.obj_id).filter(
                Candidate.filter_id.in_(filter_ids)))).outerjoin(Annotation)
             )  # Join in annotations info for sort/filter
        if classifications is not None:
            if isinstance(classifications, str) and "," in classifications:
                classifications = [
                    c.strip() for c in classifications.split(",")
                ]
            elif isinstance(classifications, str):
                classifications = [classifications]
            else:
                return self.error(
                    "Invalid classifications value -- must provide at least one string value"
                )
            q = q.join(Classification).filter(
                Classification.classification.in_(classifications))
        if sort_by_origin is None:
            # Don't apply the order by just yet. Save it so we can pass it to
            # the LIMT/OFFSET helper function down the line once other query
            # params are set.
            order_by = [Candidate.passed_at.desc().nullslast(), Obj.id]

        if saved_status in [
                "savedToAllSelected",
                "savedToAnySelected",
                "savedToAnyAccessible",
                "notSavedToAnyAccessible",
                "notSavedToAnySelected",
                "notSavedToAllSelected",
        ]:
            notin = False
            active_sources = (DBSession().query(Source.obj_id).filter(
                Source.active.is_(True)))
            if saved_status == "savedToAllSelected":
                # Retrieve objects that have as many active saved groups that are
                # in 'group_ids' as there are items in 'group_ids'
                subquery = (active_sources.filter(
                    Source.group_id.in_(group_ids)).group_by(
                        Source.obj_id).having(
                            func.count(Source.group_id) == len(group_ids)))
            elif saved_status == "savedToAnySelected":
                subquery = active_sources.filter(
                    Source.group_id.in_(group_ids))
            elif saved_status == "savedToAnyAccessible":
                subquery = active_sources.filter(
                    Source.group_id.in_(user_accessible_group_ids))
            elif saved_status == "notSavedToAnyAccessible":
                subquery = active_sources.filter(
                    Source.group_id.in_(user_accessible_group_ids))
                notin = True
            elif saved_status == "notSavedToAnySelected":
                subquery = active_sources.filter(
                    Source.group_id.in_(group_ids))
                notin = True
            elif saved_status == "notSavedToAllSelected":
                # Retrieve objects that have as many active saved groups that are
                # in 'group_ids' as there are items in 'group_ids', and select
                # the objects not in that set
                subquery = (active_sources.filter(
                    Source.group_id.in_(group_ids)).group_by(
                        Source.obj_id).having(
                            func.count(Source.group_id) == len(group_ids)))
                notin = True
            q = (q.filter(Obj.id.notin_(subquery)) if notin else q.filter(
                Obj.id.in_(subquery)))
        elif saved_status != "all":
            return self.error(
                f"Invalid savedStatus: {saved_status}. Must be one of the enumerated options."
            )

        if start_date is not None and start_date.strip() not in [
                "",
                "null",
                "undefined",
        ]:
            start_date = arrow.get(start_date).datetime
            q = q.filter(Candidate.passed_at >= start_date)
        if end_date is not None and end_date.strip() not in [
                "", "null", "undefined"
        ]:
            end_date = arrow.get(end_date).datetime
            q = q.filter(Candidate.passed_at <= end_date)
        if redshift_range_str is not None:
            redshift_range = ast.literal_eval(redshift_range_str)
            if not (isinstance(redshift_range,
                               (list, tuple)) and len(redshift_range) == 2):
                return self.error('Invalid argument for `redshiftRange`')
            if not (isinstance(redshift_range[0], (float, int))
                    and isinstance(redshift_range[1], (float, int))):
                return self.error('Invalid arguments in `redshiftRange`')
            q = q.filter(Obj.redshift >= redshift_range[0],
                         Obj.redshift <= redshift_range[1])

        if annotation_exclude_origin is not None:

            if annotation_exclude_date is None:
                right = (DBSession().query(Obj.id).join(Annotation).filter(
                    Annotation.origin == annotation_exclude_origin).subquery())
            else:
                expire_date = arrow.get(annotation_exclude_date).datetime
                right = (DBSession().query(Obj.id).join(Annotation).filter(
                    Annotation.origin == annotation_exclude_origin,
                    Annotation.modified >= expire_date,
                ).subquery())

            q = q.outerjoin(right,
                            Obj.id == right.c.id).filter(right.c.id.is_(None))

        if annotation_filter_list is not None:
            # Parse annotation filter list objects from the query string
            # and apply the filters to the query

            for item in re.split(r",(?={)", annotation_filter_list):
                try:
                    new_filter = json.loads(item)
                except json.decoder.JSONDecodeError:
                    return self.error(
                        "Could not parse JSON objects for annotation filtering"
                    )

                if "origin" not in new_filter:
                    self.error(
                        f"Invalid annotation filter list item {item}: \"origin\" is required."
                    )

                if "key" not in new_filter:
                    self.error(
                        f"Invalid annotation filter list item {item}: \"key\" is required."
                    )

                if "value" in new_filter:
                    value = new_filter["value"]
                    if isinstance(value, bool):
                        q = q.filter(
                            Annotation.origin == new_filter["origin"],
                            Annotation.data[new_filter["key"]].astext.cast(
                                Boolean) == value,
                        )
                    else:
                        # Test if the value is a nested object
                        try:
                            value = json.loads(value)
                            # If a nested object, we put the value through the
                            # JSON loads/dumps pipeline to get a string formatted
                            # like Postgres will for its JSONB ->> text operation
                            # For some reason, for example, not doing this will
                            # have value = { "key": "value" } (with the extra
                            # spaces around the braces) and cause the filter to
                            # fail.
                            value = json.dumps(value)
                        except json.decoder.JSONDecodeError:
                            # If not, this is just a string field and we don't
                            # need the string formatting above
                            pass
                        q = q.filter(
                            Annotation.origin == new_filter["origin"],
                            Annotation.data[new_filter["key"]].astext == value,
                        )
                elif "min" in new_filter and "max" in new_filter:
                    try:
                        min_value = float(new_filter["min"])
                        max_value = float(new_filter["max"])
                        q = q.filter(
                            Annotation.origin == new_filter["origin"],
                            Annotation.data[new_filter["key"]].cast(Float) >=
                            min_value,
                            Annotation.data[new_filter["key"]].cast(Float) <=
                            max_value,
                        )
                    except ValueError:
                        return self.error(
                            f"Invalid annotation filter list item: {item}. The min/max provided is not a valid number."
                        )
                else:
                    return self.error(
                        f"Invalid annotation filter list item: {item}. Should have either \"value\" or \"min\" and \"max\""
                    )

        if sort_by_origin is not None:
            sort_by_key = self.get_query_argument("sortByAnnotationKey", None)
            sort_by_order = self.get_query_argument("sortByAnnotationOrder",
                                                    None)
            # Define a custom sort order to have annotations from the correct
            # origin first, all others afterwards
            origin_sort_order = case(
                value=Annotation.origin,
                whens={sort_by_origin: 1},
                else_=None,
            )
            annotation_sort_criterion = (
                Annotation.data[sort_by_key].desc().nullslast()
                if sort_by_order == "desc" else
                Annotation.data[sort_by_key].nullslast())
            # Don't apply the order by just yet. Save it so we can pass it to
            # the LIMT/OFFSET helper function.
            order_by = [
                origin_sort_order.nullslast(),
                annotation_sort_criterion,
                Candidate.passed_at.desc().nullslast(),
                Obj.id,
            ]
        try:
            query_results = grab_query_results(
                q,
                total_matches,
                page,
                n_per_page,
                "candidates",
                order_by=order_by,
                include_photometry=include_photometry,
                include_spectra=include_spectra,
            )
        except ValueError as e:
            if "Page number out of range" in str(e):
                return self.error("Page number out of range.")
            raise
        matching_source_ids = (DBSession().query(Source.obj_id).filter(
            Source.group_id.in_(user_accessible_group_ids)).filter(
                Source.obj_id.in_(
                    [obj.id for obj in query_results["candidates"]])).all())
        candidate_list = []
        for obj in query_results["candidates"]:
            with DBSession().no_autoflush:
                obj.is_source = (obj.id, ) in matching_source_ids
                if obj.is_source:
                    obj.saved_groups = (DBSession().query(Group).join(
                        Source).filter(Source.obj_id == obj.id).filter(
                            Source.active.is_(True)).filter(
                                Group.id.in_(user_accessible_group_ids)).all())
                    obj.classifications = obj.get_classifications_readable_by(
                        self.current_user)
                obj.passing_group_ids = [
                    f.group_id for f in (DBSession().query(Filter).filter(
                        Filter.id.in_(user_accessible_filter_ids)).filter(
                            Filter.id.in_(DBSession().query(
                                Candidate.filter_id).filter(
                                    Candidate.obj_id == obj.id))).all())
                ]
                candidate_list.append(obj.to_dict())
                candidate_list[-1]["comments"] = sorted(
                    [
                        cmt.to_dict() for cmt in obj.get_comments_readable_by(
                            self.current_user)
                    ],
                    key=lambda x: x["created_at"],
                    reverse=True,
                )
                candidate_list[-1]["annotations"] = sorted(
                    obj.get_annotations_readable_by(self.current_user),
                    key=lambda x: x.origin,
                )
                candidate_list[-1]["last_detected_at"] = obj.last_detected_at
                candidate_list[-1]["gal_lat"] = obj.gal_lat_deg
                candidate_list[-1]["gal_lon"] = obj.gal_lon_deg
                candidate_list[-1][
                    "luminosity_distance"] = obj.luminosity_distance
                candidate_list[-1]["dm"] = obj.dm
                candidate_list[-1][
                    "angular_diameter_distance"] = obj.angular_diameter_distance

        query_results["candidates"] = candidate_list
        self.verify_permissions()
        return self.success(data=query_results)
Esempio n. 32
0
def get_comments(cids, v=None, nSession=None, sort_type="new",
                 load_parent=False, **kwargs):

    if not cids:
        return []

    cids=tuple(cids)

    nSession = nSession or kwargs.get('session') or g.db

    exile=nSession.query(ModAction
        ).options(
        lazyload('*')
        ).filter(
        ModAction.kind=="exile_user",
        ModAction.target_comment_id.in_(cids)
        ).subquery()

    if v:
        vt = nSession.query(CommentVote).filter(
            CommentVote.comment_id.in_(cids), 
            CommentVote.user_id==v.id
            ).subquery()

        mod=nSession.query(ModRelationship
            ).filter_by(
            user_id=v.id,
            accepted=True
            ).subquery()



        query = nSession.query(
            Comment, 
            aliased(CommentVote, alias=vt),
            aliased(ModRelationship, alias=mod),
            aliased(ModAction, alias=exile)
            ).options(
            joinedload(Comment.author).joinedload(User.title)
            )

        if v.admin_level >=4:
            query=query.options(joinedload(Comment.oauth_app))

        if load_parent:
            query = query.options(
                joinedload(
                    Comment.parent_comment
                    ).joinedload(
                    Comment.author
                    ).joinedload(
                    User.title
                    )
                )

        query = query.join(
            vt,
            vt.c.comment_id == Comment.id,
            isouter=True
            ).join(
            Comment.post,
            isouter=True
            ).join(
            mod,
            mod.c.board_id==Submission.board_id,
            isouter=True
            ).join(
            exile,
            and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
            isouter=True
            ).filter(
            Comment.id.in_(cids)
            )



        query=query.options(
    #        contains_eager(Comment.post).contains_eager(Submission.board)
            ).order_by(None).all()

        comments=[x for x in query]

        output = [x[0] for x in comments]
        for i in range(len(output)):
            output[i]._voted = comments[i][1].vote_type if comments[i][1] else 0
            output[i]._is_guildmaster = comments[i][2]
            output[i]._is_exiled_for = comments[i][3]



    else:
        query = nSession.query(
            Comment,
            aliased(ModAction, alias=exile)
        ).options(
            joinedload(Comment.author).joinedload(User.title),
            joinedload(Comment.post).joinedload(Submission.board)
        ).filter(
            Comment.id.in_(cids)
        ).join(
            exile,
            and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
            isouter=True
        ).order_by(None).all()

        comments=[x for x in query]

        output=[x[0] for x in comments]
        for i in range(len(output)):
            output[i]._is_exiled_for=comments[i][1]


    output = sorted(output, key=lambda x: cids.index(x.id))

    return output
Esempio n. 33
0
def get_comment(cid, nSession=None, v=None, graceful=False, **kwargs):

    if isinstance(cid, str):
        i = base36decode(cid)
    else:
        i = cid

    nSession = nSession or kwargs.get('session') or g.db

    exile = nSession.query(ModAction
         ).options(
         lazyload('*')
         ).filter_by(
         kind="exile_user"
         ).subquery()

    if v:
        blocking = v.blocking.subquery()
        blocked = v.blocked.subquery()
        vt = nSession.query(CommentVote).filter(
            CommentVote.user_id == v.id,
            CommentVote.comment_id == i).subquery()

        mod=nSession.query(ModRelationship
            ).filter_by(
            user_id=v.id,
            accepted=True
            ).subquery()


        items = nSession.query(
            Comment, 
            vt.c.vote_type,
            aliased(ModRelationship, alias=mod),
            aliased(ModAction, alias=exile)
        ).options(
            joinedload(Comment.author).joinedload(User.title)
        )

        if v.admin_level >=4:
            items=items.options(joinedload(Comment.oauth_app))

        items=items.filter(
            Comment.id == i
        ).join(
            vt, 
            vt.c.comment_id == Comment.id, 
            isouter=True
        ).join(
            Comment.post,
            isouter=True
        ).join(
            mod,
            mod.c.board_id==Submission.board_id,
            isouter=True
        ).join(
            exile,
            and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
            isouter=True
        ).first()

        if not items and not graceful:
            abort(404)

        x = items[0]
        x._voted = items[1] or 0
        x._is_guildmaster=items[2] or 0
        x._is_exiled_for=items[3] or 0

        block = nSession.query(UserBlock).filter(
            or_(
                and_(
                    UserBlock.user_id == v.id,
                    UserBlock.target_id == x.author_id
                ),
                and_(UserBlock.user_id == x.author_id,
                     UserBlock.target_id == v.id
                     )
            )
        ).first()

        x._is_blocking = block and block.user_id == v.id
        x._is_blocked = block and block.target_id == v.id

    else:
        q = nSession.query(
            Comment,
            aliased(ModAction, alias=exile)
        ).options(
            joinedload(Comment.author).joinedload(User.title)
        ).join(
            exile,
            and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
            isouter=True
        ).filter(Comment.id == i).first()

        if not q and not graceful:
            abort(404)

        x=q[0]
        x._is_exiled_for=q[1]


    return x
Esempio n. 34
0
def get_post_with_comments(pid, sort_type="top", v=None):

    post = get_post(pid, v=v)

    exile=g.db.query(ModAction
        ).options(
        lazyload('*')
        ).filter_by(
        kind="exile_user"
        ).subquery()

    if v:
        votes = g.db.query(CommentVote).filter_by(user_id=v.id).subquery()

        blocking = v.blocking.subquery()

        blocked = v.blocked.subquery()

        comms = g.db.query(
            Comment,
            votes.c.vote_type,
            blocking.c.id,
            blocked.c.id,
            aliased(ModAction, alias=exile)
        ).options(
            joinedload(Comment.author).joinedload(User.title)
        )
        if v.admin_level >=4:

            comms=comms.options(joinedload(Comment.oauth_app))

        comms=comms.filter(
            Comment.parent_submission == post.id,
            Comment.level <= 6
        ).join(
            votes,
            votes.c.comment_id == Comment.id,
            isouter=True
        ).join(
            blocking,
            blocking.c.target_id == Comment.author_id,
            isouter=True
        ).join(
            blocked,
            blocked.c.user_id == Comment.author_id,
            isouter=True
        ).join(
            exile,
            and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
            isouter=True
        )

        if sort_type == "hot":
            comments = comms.order_by(Comment.score_hot.desc()).all()
        elif sort_type == "top":
            comments = comms.order_by(Comment.score_top.desc()).all()
        elif sort_type == "new":
            comments = comms.order_by(Comment.created_utc.desc()).all()
        elif sort_type == "disputed":
            comments = comms.order_by(Comment.score_disputed.desc()).all()
        elif sort_type == "random":
            c = comms.all()
            comments = random.sample(c, k=len(c))
        else:
            abort(422)

        output = []
        for c in comments:
            comment = c[0]
            comment._voted = c[1] or 0
            comment._is_blocking = c[2] or 0
            comment._is_blocked = c[3] or 0
            comment._is_guildmaster=post._is_guildmaster
            comment._is_exiled_for=c[4]
            output.append(comment)
        post._preloaded_comments = output

    else:
        comms = g.db.query(
            Comment,
            aliased(ModAction, alias=exile)
        ).options(
            joinedload(Comment.author).joinedload(User.title)
        ).filter(
            Comment.parent_submission == post.id,
            Comment.level <= 6
        ).join(
            exile,
            and_(exile.c.target_comment_id==Comment.id, exile.c.board_id==Comment.original_board_id),
            isouter=True
        )

        if sort_type == "hot":
            comments = comms.order_by(Comment.score_hot.desc()).all()
        elif sort_type == "top":
            comments = comms.order_by(Comment.score_top.desc()).all()
        elif sort_type == "new":
            comments = comms.order_by(Comment.created_utc.desc()).all()
        elif sort_type == "disputed":
            comments = comms.order_by(Comment.score_disputed.desc()).all()
        elif sort_type == "random":
            c = comms.all()
            comments = random.sample(c, k=len(c))
        else:
            abort(422)

        output = []
        for c in comments:
            comment=c[0]
            comment._is_exiled_for=c[1]
            output.append(comment)

        # output=[x for x in comments]


        post._preloaded_comments = output

    return post
Esempio n. 35
0
def get_posts(pids, sort="hot", v=None):

    if not pids:
        return []

    pids=tuple(pids)

    # exile=g.db.query(ModAction).options(
    #     lazyload('*')
    #     ).filter(
    #     ModAction.kind=="exile_user",
    #     ModAction.target_submission_id.in_(pids)
    #     ).subquery()

    if v:
        vt = g.db.query(Vote).filter(
            Vote.submission_id.in_(pids), 
            Vote.user_id==v.id
            ).subquery()

        mod = g.db.query(ModRelationship).filter_by(
            user_id=v.id, accepted=True, invite_rescinded=False).subquery()

        boardblocks = g.db.query(BoardBlock).filter_by(
            user_id=v.id).subquery()
        blocking = v.blocking.subquery()
        blocked = v.blocked.subquery()
        subs = g.db.query(Subscription).filter_by(user_id=v.id, is_active=True).subquery()

        query = g.db.query(
            Submission,
            vt.c.vote_type,
            aliased(ModRelationship, alias=mod),
            boardblocks.c.id,
            blocking.c.id,
            blocked.c.id,
            subs.c.id,
            # aliased(ModAction, alias=exile)
        ).options(
            joinedload(Submission.author).joinedload(User.title)
        ).filter(
            Submission.id.in_(pids)
        ).join(
            vt, vt.c.submission_id==Submission.id, isouter=True
        ).join(
            mod, 
            mod.c.board_id == Submission.board_id, 
            isouter=True
        ).join(
            boardblocks, 
            boardblocks.c.board_id == Submission.board_id, 
            isouter=True
        ).join(
            blocking, 
            blocking.c.target_id == Submission.author_id, 
            isouter=True
        ).join(
            blocked, 
            blocked.c.user_id == Submission.author_id, 
            isouter=True
        ).join(
            subs, 
            subs.c.board_id == Submission.board_id, 
            isouter=True
        # ).join(
        #     exile,
        #     and_(exile.c.target_submission_id==Submission.id, exile.c.board_id==Submission.original_board_id),
        #     isouter=True
        ).order_by(None).all()

        posts=[x for x in query]

        output = [p[0] for p in query]
        for i in range(len(output)):
            output[i]._voted = posts[i][1] or 0
            output[i]._is_guildmaster = posts[i][2] or 0
            output[i]._is_blocking_guild = posts[i][3] or 0
            output[i]._is_blocking = posts[i][4] or 0
            output[i]._is_blocked = posts[i][5] or 0
            output[i]._is_subscribed = posts[i][6] or 0
            # output[i]._is_exiled_for=posts[i][7] or 0
    else:
        query = g.db.query(
            Submission,
            # aliased(ModAction, alias=exile)
        ).options(
            joinedload(Submission.author).joinedload(User.title)
        ).filter(Submission.id.in_(pids)
        # ).join(
        #     exile,
        #     and_(exile.c.target_submission_id==Submission.id, exile.c.board_id==Submission.original_board_id),
        #     isouter=True
        ).order_by(None).all()

        output=[x for x in query]

        # output=[]
        # for post in posts:
        #     p=post[0]
        #     p._is_exiled_for=post[1] or 0
        #     output.append(p)

    return sorted(output, key=lambda x: pids.index(x.id))
Esempio n. 36
0
def content(sess, start_date, finish_date, site_id, user):
    sess = None
    try:
        sess = Session()
        running_name, finished_name = chellow.dloads.make_names(
            "site_hh_data.csv", user)
        f = open(running_name, mode="w", newline="")
        writer = csv.writer(f, lineterminator="\n")
        write_row(
            writer,
            "Site Id",
            "Site Name",
            "Associated Site Ids",
            "Sources",
            "Generator Types",
            "HH Start Clock-Time",
            "Imported kWh",
            "Displaced kWh",
            "Exported kWh",
            "Used kWh",
            "Parasitic kWh",
            "Generated kWh",
            "3rd Party Import",
            "3rd Party Export",
            "Meter Type",
        )

        sites = sess.query(Site).order_by(Site.code)
        if site_id is not None:
            sites = sites.filter(Site.id == site_id)

        for site in sites:
            sources = set()
            generator_types = set()
            assoc = site.find_linked_sites(sess, start_date, finish_date)
            metering_type = ""
            for era in (sess.query(Era).join(SiteEra).filter(
                    SiteEra.site == site,
                    SiteEra.is_physical == true(),
                    Era.start_date <= finish_date,
                    or_(Era.finish_date == null(),
                        Era.finish_date >= start_date),
                    Source.code != "sub",
            ).options(
                    joinedload(Era.supply).joinedload(Supply.source),
                    joinedload(Era.supply).joinedload(Supply.generator_type),
            )):
                mtype = era.meter_category
                if metering_type == "" or TYPE_ORDER[mtype] < TYPE_ORDER[
                        metering_type]:
                    metering_type = mtype
                sources.add(era.supply.source.code)
                generator_type = era.supply.generator_type
                if generator_type is not None:
                    generator_types.add(generator_type.code)

            assoc_str = ",".join(sorted(s.code for s in assoc))
            sources_str = ",".join(sorted(list(sources)))
            generators_str = ",".join(sorted(list(generator_types)))

            for hh in site.hh_data(sess, start_date, finish_date):
                write_row(
                    writer,
                    site.code,
                    site.name,
                    assoc_str,
                    sources_str,
                    generators_str,
                    hh["start_date"],
                    hh["imp_net"],
                    hh["displaced"],
                    hh["exp_net"],
                    hh["used"],
                    hh["exp_gen"],
                    hh["imp_gen"],
                    hh["imp_3p"],
                    hh["exp_3p"],
                    metering_type,
                )

    except BaseException:
        msg = traceback.format_exc()
        sys.stderr.write(msg)
        writer.writerow([msg])
    finally:
        if sess is not None:
            sess.close()
        if f is not None:
            f.close()
            os.rename(running_name, finished_name)
Esempio n. 37
0
def grab_query_results(
    q,
    total_matches,
    page,
    n_items_per_page,
    items_name,
    order_by=None,
    include_photometry=False,
    include_spectra=False,
):
    # The query will return multiple rows per candidate object if it has multiple
    # annotations associated with it, with rows appearing at the end of the query
    # for any annotations with origins not equal to the one being sorted on (if applicable).
    # We want to essentially grab only the candidate objects as they first appear
    # in the query results, and ignore these other irrelevant annotation entries.

    # Add a "row_num" column to the desire query that explicitly encodes the ordering
    # of the query results - remember that these query rows are essentially
    # (Obj, Annotation) tuples, so the earliest row numbers for a given Obj is
    # the one we want to adhere to (and the later ones are annotation records for
    # the candidate that are not being sorted/filtered on right now)
    #
    # The row number must be preserved like this in order to remember the desired
    # ordering info even while using the passed in query as a subquery to select
    # from. This is because subqueries provide a set of results to query from,
    # losing any order_by information.
    row = func.row_number().over(order_by=order_by).label("row_num")
    full_query = q.add_column(row)

    info = {}
    full_query = full_query.subquery()
    # Using the PostgreSQL DISTINCT ON keyword, we grab the candidate Obj ids
    # in the order that they first appear in the query (per the row_num values)
    # NOTE: It is probably possible to grab the full Obj records here instead of
    # just the ID values, but querying "full_query" here means we lost the original
    # ORM mappings, so we would have to explicity re-label the columns here.
    # It is much more straightforward to just get an ordered list of Obj ID
    # values here and get the corresponding n_items_per_page full Obj objects
    # at the end, I think, for minimal additional overhead.
    ids_with_row_nums = (DBSession().query(
        full_query.c.id,
        full_query.c.row_num).distinct(full_query.c.id).order_by(
            full_query.c.id, full_query.c.row_num).subquery())
    # Grouping and getting the first distinct obj_id above messed up the order
    # in the query set, so re-order by the row_num we used to remember the
    # original ordering
    ordered_ids = (DBSession().query(ids_with_row_nums.c.id).order_by(
        ids_with_row_nums.c.row_num))

    if total_matches:
        info["totalMatches"] = int(total_matches)
    else:
        info["totalMatches"] = ordered_ids.count()

    if page:
        if (((info["totalMatches"] < (page - 1) * n_items_per_page
              and info["totalMatches"] % n_items_per_page != 0) or
             (info["totalMatches"] < page * n_items_per_page
              and info["totalMatches"] % n_items_per_page == 0)
             and info["totalMatches"] != 0) or page <= 0
                or (info["totalMatches"] == 0 and page != 1)):
            raise ValueError("Page number out of range.")

        # Now bring in the full Obj info for the candidates
        page_ids = (ordered_ids.limit(n_items_per_page).offset(
            (page - 1) * n_items_per_page).all())
        info["pageNumber"] = page
        info["numPerPage"] = n_items_per_page
    else:
        page_ids = ordered_ids.all()

    items = []
    query_options = [joinedload(Obj.thumbnails)]
    if include_photometry:
        query_options.append(
            joinedload(Obj.photometry).joinedload(Photometry.instrument))
    if include_spectra:
        query_options.append(
            joinedload(Obj.spectra).joinedload(Spectrum.instrument))
    for item_id in page_ids:
        items.append(Obj.query.options(query_options).get(item_id))
    info[items_name] = items
    return info
Esempio n. 38
0
def get_role_list(
        session, ):
    return session.query(Roles).options(joinedload(Roles.groups)).order_by(
        Roles.role_name).all()
Esempio n. 39
0
def get_post_with_comments(pid, sort_type="top", v=None):

    post = get_post(pid, v=v)

    if v:
        votes = g.db.query(CommentVote).filter_by(user_id=v.id).subquery()

        blocking = v.blocking.subquery()

        blocked = v.blocked.subquery()

        comms = g.db.query(
            Comment, votes.c.vote_type, blocking.c.id, blocked.c.id).options(
                joinedload(Comment.author).joinedload(User.title)).filter(
                    Comment.parent_submission == post.id,
                    Comment.level <= 6).join(
                        votes, votes.c.comment_id == Comment.id,
                        isouter=True).join(
                            blocking,
                            blocking.c.target_id == Comment.author_id,
                            isouter=True).join(
                                blocked,
                                blocked.c.user_id == Comment.author_id,
                                isouter=True)

        if sort_type == "hot":
            comments = comms.order_by(Comment.score_hot.desc()).all()
        elif sort_type == "top":
            comments = comms.order_by(Comment.score_top.desc()).all()
        elif sort_type == "new":
            comments = comms.order_by(Comment.created_utc.desc()).all()
        elif sort_type == "disputed":
            comments = comms.order_by(Comment.score_disputed.desc()).all()
        elif sort_type == "random":
            c = comms.all()
            comments = random.sample(c, k=len(c))
        else:
            abort(422)

        output = []
        for c in comments:
            comment = c[0]
            comment._voted = c[1] or 0
            comment._is_blocking = c[2] or 0
            comment._is_blocked = c[3] or 0
            output.append(comment)
        post._preloaded_comments = output

    else:
        comms = g.db.query(Comment).options(
            joinedload(Comment.author).joinedload(User.title)).filter(
                Comment.parent_submission == post.id, Comment.level <= 6)

        if sort_type == "hot":
            comments = comms.order_by(Comment.score_hot.desc()).all()
        elif sort_type == "top":
            comments = comms.order_by(Comment.score_top.desc()).all()
        elif sort_type == "new":
            comments = comms.order_by(Comment.created_utc.desc()).all()
        elif sort_type == "disputed":
            comments = comms.order_by(Comment.score_disputed.desc()).all()
        elif sort_type == "random":
            c = comms.all()
            comments = random.sample(c, k=len(c))
        else:
            abort(422)

        output = [c for c in comments]

        post._preloaded_comments = output

    return post
Esempio n. 40
0
def get_instance_list(
        session, ):
    return session.query(Instances).options(
        joinedload(Instances.groups),
        joinedload(Instances.plugins)).order_by(Instances.hostname).all()
Esempio n. 41
0
## That was relationships

# Eager loading

from sqlalchemy.orm import subqueryload
jack = session.query(User).\
                options(subqueryload(User.addresses)).\
                filter_by(name='jack').one()
jack

jack.addresses

from sqlalchemy.orm import joinedload

jack = session.query(User).\
                       options(joinedload(User.addresses)).\
                       filter_by(name='jack').one()
jack

jack.addresses

from sqlalchemy.orm import contains_eager
jacks_addresses = session.query(Address).\
                            join(Address.user).\
                            filter(User.name=='jack').\
                            options(contains_eager(Address.user)).\
                            all()
jacks_addresses

jacks_addresses[0].user
Esempio n. 42
0
def get_repository_in_tool_shed(app, id, eagerload_columns=None):
    """Get a repository on the tool shed side from the database via id."""
    q = get_repository_query(app)
    if eagerload_columns:
        q = q.options(joinedload(*eagerload_columns))
    return q.get(app.security.decode_id(id))
Esempio n. 43
0
 def go():
     eq_(
         sess.query(Parent).options(joinedload(Parent.children)).all(),
         [p1, p2])
Esempio n. 44
0
    def _ensure_valid_session(self, session_cookie, create=True):
        """
        Ensure that a valid Galaxy session exists and is available as
        trans.session (part of initialization)

        Support for universe_session and universe_user cookies has been
        removed as of 31 Oct 2008.
        """
        # Try to load an existing session
        secure_id = self.get_cookie(name=session_cookie)
        galaxy_session = None
        prev_galaxy_session = None
        user_for_new_session = None
        invalidate_existing_session = False
        # Track whether the session has changed so we can avoid calling flush
        # in the most common case (session exists and is valid).
        galaxy_session_requires_flush = False
        if secure_id:
            # Decode the cookie value to get the session_key
            try:
                session_key = self.security.decode_guid(secure_id)
                if session_key:
                    # Retrieve the galaxy_session id via the unique session_key
                    galaxy_session = self.sa_session.query(self.app.model.GalaxySession) \
                                                    .filter(and_(self.app.model.GalaxySession.table.c.session_key == session_key,
                                                                 self.app.model.GalaxySession.table.c.is_valid == true())).options(joinedload("user")).first()
            except Exception:
                # We'll end up creating a new galaxy_session
                session_key = None
        # If remote user is in use it can invalidate the session and in some
        # cases won't have a cookie set above, so we need to to check some
        # things now.
        if self.app.config.use_remote_user:
            remote_user_email = self.environ.get(self.app.config.remote_user_header, None)
            if galaxy_session:
                if remote_user_email and galaxy_session.user is None:
                    # No user, associate
                    galaxy_session.user = self.get_or_create_remote_user(remote_user_email)
                    galaxy_session_requires_flush = True
                elif (remote_user_email and
                      (galaxy_session.user.email != remote_user_email) and
                      ((not self.app.config.allow_user_impersonation) or
                       (remote_user_email not in self.app.config.admin_users_list))):
                    # Session exists but is not associated with the correct
                    # remote user, and the currently set remote_user is not a
                    # potentially impersonating admin.
                    invalidate_existing_session = True
                    user_for_new_session = self.get_or_create_remote_user(remote_user_email)
                    log.warning("User logged in as '%s' externally, but has a cookie as '%s' invalidating session",
                                remote_user_email, galaxy_session.user.email)
            elif remote_user_email:
                # No session exists, get/create user for new session
                user_for_new_session = self.get_or_create_remote_user(remote_user_email)
            if ((galaxy_session and galaxy_session.user is None) and user_for_new_session is None):
                raise Exception("Remote Authentication Failure - user is unknown and/or not supplied.")
        else:
            if galaxy_session is not None and galaxy_session.user and galaxy_session.user.external:
                # Remote user support is not enabled, but there is an existing
                # session with an external user, invalidate
                invalidate_existing_session = True
                log.warning("User '%s' is an external user with an existing session, invalidating session since external auth is disabled",
                            galaxy_session.user.email)
            elif galaxy_session is not None and galaxy_session.user is not None and galaxy_session.user.deleted:
                invalidate_existing_session = True
                log.warning("User '%s' is marked deleted, invalidating session" % galaxy_session.user.email)
        # Do we need to invalidate the session for some reason?
        if invalidate_existing_session:
            prev_galaxy_session = galaxy_session
            prev_galaxy_session.is_valid = False
            galaxy_session = None
        # No relevant cookies, or couldn't find, or invalid, so create a new session
        if galaxy_session is None:
            galaxy_session = self.__create_new_session(prev_galaxy_session, user_for_new_session)
            galaxy_session_requires_flush = True
            self.galaxy_session = galaxy_session
            self.__update_session_cookie(name=session_cookie)
        else:
            self.galaxy_session = galaxy_session
        # Do we need to flush the session?
        if galaxy_session_requires_flush:
            self.sa_session.add(galaxy_session)
            # FIXME: If prev_session is a proper relation this would not
            #        be needed.
            if prev_galaxy_session:
                self.sa_session.add(prev_galaxy_session)
            self.sa_session.flush()
        # If the old session was invalid, get a new (or existing default,
        # unused) history with our new session
        if invalidate_existing_session:
            self.get_or_create_default_history()
Esempio n. 45
0
 def go():
     for a in session.query(a_poly).\
         options(
                 joinedload(a_poly.B.related),
                 joinedload(a_poly.C.related)):
         eq_(a.related, [d])
Esempio n. 46
0
 def go():
     eq_(
         sess.query(Subparent).options(joinedload("children")).all(),
         [p1, p2])
Esempio n. 47
0
def main(client_id, epoch_start, rends_per_epoch, dry_run, max_dist, host,
         port, required_substances):
    loop = asyncio.get_event_loop()
    http_sess = loop.run_until_complete(make_http_client())

    app = brender.Brender()

    collector_ctx = {
        'host': host,
        'port': port,
        'sess': http_sess,
        'client_id': client_id,
    }

    if required_substances:
        required_substances = set(required_substances.split(','))
        assert all(s in SUBSTANCES for s in required_substances)

    for epoch in itertools.count(start=epoch_start):
        logger.info('Starting epoch %d', epoch)
        with session_scope() as sess:
            pairs = (sess.query(ExemplarShapePair).join(Shape).join(
                Exemplar).filter(
                    sa.and_(
                        ExemplarShapePair.distance < max_dist,
                        sa.not_(Shape.exclude),
                        sa.not_(Exemplar.exclude),
                        Shape.split_set.isnot(None),
                    )).options(orm.joinedload(ExemplarShapePair.exemplar),
                               orm.joinedload(
                                   ExemplarShapePair.shape)).order_by(
                                       Shape.id.asc()).all())
            materials = sess.query(
                models.Material).filter_by(enabled=True).all()
            envmaps = sess.query(models.Envmap).filter_by(enabled=True).all()

        cam_angles = [(p.azimuth, p.elevation) for p in pairs]
        logger.info('Loaded %d pairs and %d camera angles', len(pairs),
                    len(cam_angles))

        mats_by_subst = collections.defaultdict(list)
        for material in materials:
            mats_by_subst[material.substance].append(material)

        envmaps_by_split = {
            'train': [e for e in envmaps if e.split_set == 'train'],
            'validation': [e for e in envmaps if e.split_set == 'validation'],
        }

        for i in range(1000):
            pair = random.choice(pairs)
            if not pair.data_exists(config.PAIR_SHAPE_CLEAN_SEGMENT_MAP_NAME):
                continue

            app.init()
            logger.info('pair %d, shape %s, exemplar %d', pair.id,
                        pair.shape.id, pair.exemplar.id)
            try:
                loop.run_until_complete(
                    process_pair(app,
                                 pair,
                                 cam_angles,
                                 mats_by_subst,
                                 envmaps_by_split=envmaps_by_split,
                                 num_rends=rends_per_epoch,
                                 is_dry_run=dry_run,
                                 epoch=epoch,
                                 collector_ctx=collector_ctx,
                                 required_substances=required_substances))
            except Exception as e:
                logger.exception('Uncaught exception', exc_info=True)
                continue
Esempio n. 48
0
 def go():
     for a in session.query(A).with_polymorphic([B, C]).\
         options(joinedload(B.related), joinedload(C.related)):
         eq_(a.related, [d])
Esempio n. 49
0
    def get(self, group_id, slug=None, discipline_id=None):
        group = Group.query.get_or_404(group_id)

        if current_user_is_logged():
            disciplines = Discipline.query
        else:
            disciplines = group.disciplines

        if not discipline_id:
            discipline_id = request.cookies.get('discipline_id', None)
            if not discipline_id:
                discipline = disciplines.first()
                if discipline:
                    discipline_id = discipline.id
                else:
                    return redirect(url_for("university.index"))

        discipline = disciplines.filter(Discipline.id == discipline_id) \
            .options(joinedload('labs'), joinedload('files'), joinedload('articles')).first()
        if discipline is None:
            discipline = disciplines.first()

        # if not discipline and user is not logged then redirect
        if not discipline and not current_user_is_logged():
            return redirect(url_for('security.login', next=request.path))

        if group.year not in Group.current_year(
        ) and not current_user_is_logged():
            return redirect(url_for('security.login', next=request.path))

        def make_cache_key():
            return cache_key_for_students_marks(group.id, discipline.id)

        def get_all_data(group, discipline):
            lessons = Lesson.query.filter(Lesson.group_id == group.id) \
                .filter(Lesson.discipline_id == discipline.id) \
                .order_by(Lesson.date, Lesson.id).all()

            students = group.students \
                .outerjoin(Student.marks) \
                .options(contains_eager(Student.marks)) \
                .filter(or_(Mark.lesson_id == None, Mark.lesson_id.in_([i.id for i in lessons]))).all()

            labs = discipline.labs

            students_info = {}
            for student in students:
                student_info = {
                    'marks': {},
                    'tasks': {},
                    'points': 0,
                    'percents': 0,
                }
                students_info[student.id] = student_info
                for mark in student.marks:
                    student_info['marks'][mark.lesson_id] = mark

                for task in student.tasks:
                    student_info['tasks'][task.task_id] = task

                student_info['points'], student_info['percents'] \
                    = student.points(student_info['marks'],
                                     lessons,
                                     sum([len([t for t in lab.tasks if not t.ignore]) for lab in labs if lab.regular and lab.visible]),
                                     len(student_info['tasks']))

            return {
                'students': students,
                'lessons': lessons,
                'labs': labs,
                'students_info': students_info
            }

        data = get_all_data(group, discipline)

        template = "university/group.html"
        if request.headers.get('X-Pjax', None):
            template = "university/_marks.html"

        response = make_response(
            render_template(
                template,
                group=group,
                discipline=discipline,
                students=data['students'],
                lessons=data['lessons'],
                labs=data['labs'],
                articles=[
                    a for a in discipline.articles
                    if a.visible or current_user_is_logged()
                ],
                disciplines=disciplines.order_by(Discipline.title).all(),
                has_visible_labs=len(
                    [lab for lab in data['labs'] if lab.visible]) > 0,
                students_info=data['students_info'],
                lesson_types=Lesson.LESSON_TYPES,
                marks_types=Mark.MARKS,
            ))

        response.set_cookie('discipline_id', str(discipline_id))

        return response
Esempio n. 50
0
def fetch_multiple_recordings(mbids, includes=None):
    """ Fetch multiple recordings with MusicBrainz IDs.

    Args:
        mbids (list): list of uuid (MBID(gid)) of the recordings.
        includes (list): List of values to be included.
                        For list of possible values visit https://bitbucket.org/lalinsky/mbdata/wiki/API/v1/includes#!recording
    Returns:
        Dictionary containing the recording information with MBIDs as keys.
            - id: Recording mbid
            - name: Name of the recording
            - length: length of the recording
            - artists:
                - artist information: id, name, credited_name and join_phrase
    """
    if includes is None:
        includes = []
    includes_data = defaultdict(dict)
    check_includes('recording', includes)

    with mb_session() as db:
        query = db.query(Recording)
        
        if 'artist' in includes or 'artists' in includes:
            query = query.options(joinedload("artist_credit", innerjoin=True))

        if 'artists' in includes:
            query = query.\
            options(subqueryload("artist_credit.artists")).\
            options(joinedload("artist_credit.artists.artist", innerjoin=True))

        recordings = get_entities_by_gids(
            query=query,
            entity_type='recording',
            mbids=mbids,
        )

        recording_ids = [recording.id for recording in recordings.values()]

        if 'artist' in includes:
            for recording in recordings.values():
                includes_data[recording.id]['artist'] = recording.artist_credit

        if 'artists' in includes:
            for recording in recordings.values():
                includes_data[recording.id]['artists'] = recording.artist_credit.artists

        if 'url-rels' in includes:
            get_relationship_info(
                db=db,
                target_type='url',
                source_type='recording',
                source_entity_ids=recording_ids,
                includes_data=includes_data,
            )

        if 'work-rels' in includes:
            get_relationship_info(
                db=db,
                target_type='work',
                source_type='recording',
                source_entity_ids=recording_ids,
                includes_data=includes_data,
            )

        serial_recordings = {str(mbid): serialize_recording(recordings[mbid], includes_data[recordings[mbid].id]) for mbid in mbids}

    return serial_recordings
Esempio n. 51
0
    def toggle_order_shipped(self):
        """
            toggle Shipped Date, to trigger
                * balance adjustment
                * cascade to OrderDetails
                * and Product adjustment
            also test join
        """

        # fails on post_cust with TypeError: my_load_listener() got an unexpected keyword argument 'attrs'
        # @sqlalchemy.event.listens_for(sqlalchemy.orm.Mapper, 'refresh', named=True)
        def my_load_listener(target, context):
            print("on load!")

        from sqlalchemy.ext.declarative import declarative_base
        Base = declarative_base()
        print(f'type(Base): {type(Base)}'
              )  # sqlalchemy.ext.declarative.api.DeclarativeMeta
        do_listen = False
        if do_listen:
            event.listen(
                sqlalchemy.ext.declarative.api.
                DeclarativeMeta,  # models.Customer is fine
                'load',
                my_load_listener)
            # , propagate=True)  # TypeError: descriptor '__subclasses__' of 'type' object needs an argument

        pre_cust = session.query(
            models.Customer).filter(models.Customer.Id == "ALFKI").one()
        supers = pre_cust.__class__.__bases__
        for each_super in supers:
            print(f'pre_cust super: {type(each_super)}')
        session.expunge(pre_cust)

        print("")
        test_order = session.query(models.Order).filter(models.Order.Id == 11011).\
            join(models.Employee).options(joinedload(models.Order.SalesRep)).\
            one()
        if test_order.ShippedDate is None or test_order.ShippedDate == "":
            test_order.ShippedDate = str(datetime.now())
            print(
                prt("Shipping order - ShippedDate: ['' -> " +
                    test_order.ShippedDate + "]"))
        else:
            test_order.ShippedDate = None
            print(prt("Returning order - ShippedDate: [ -> None]"))
        session.commit()

        print("")
        post_cust = session.query(
            models.Customer).filter(models.Customer.Id == "ALFKI").one()
        logic_row = LogicRow(row=post_cust,
                             old_row=pre_cust,
                             ins_upd_dlt="*",
                             nest_level=0,
                             a_session=session,
                             row_sets=None)

        if abs(post_cust.Balance - pre_cust.Balance) == 960:
            logic_row.log("Correct adjusted Customer Result")
            assert True
        else:
            self.fail(
                logic_row.log(
                    "ERROR - incorrect adjusted Customer Result (not 960 delta)"
                ))

        if post_cust.Balance == 56:
            pass
        else:
            self.fail(logic_row.log("ERROR - balance should be 56"))

        if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
            pass
        else:
            self.fail(logic_row.log("Error - UnpaidOrderCount should be 3"))
Esempio n. 52
0
 def eager_query(cls):
     query = super(Directive, cls).eager_query()
     return cls.eager_inclusions(query, Directive._include_links).options(
         orm.joinedload('audit_frequency'),
         orm.joinedload('audit_duration'), orm.subqueryload('controls'))
Esempio n. 53
0
| Subquery Load - loading all collections at once
+-------------------------------------------------------------------------+
"""
from sqlalchemy.orm import subqueryload

for user in session.query(User).options(subqueryload(User.addresses)):
    print(user, user.addresses)
pass
"""
+-------------------------------------------------------------------------+
| 12. Joined Load - users LEFT OUTER JOIN to load parent + child in on query
+-------------------------------------------------------------------------+
"""
from sqlalchemy.orm import joinedload

for user in session.query(User).options(joinedload(User.addresses)):
    print(user, user.addresses)
pass
"""
+-------------------------------------------------------------------------+
| 13. Join and a subquery load - use contains_eager
+-------------------------------------------------------------------------+
"""
from sqlalchemy.orm import contains_eager

for address in (session.query(Address).join(Address.user).options(
        joinedload(Address.user))):
    print(address, address.user)
pass

# we can go like this instead
Esempio n. 54
0
    def test_unbound_options(self):
        User, Address, Keyword, Order, Item = self.classes(
            "User", "Address", "Keyword", "Order", "Item"
        )

        self._run_cache_key_fixture(
            lambda: (
                joinedload(User.addresses),
                joinedload(User.addresses.of_type(aliased(Address))),
                joinedload("addresses"),
                joinedload(User.orders),
                joinedload(User.orders.and_(Order.id != 5)),
                joinedload(User.orders.and_(Order.id == 5)),
                joinedload(User.orders.and_(Order.description != "somename")),
                joinedload(User.orders).selectinload("items"),
                joinedload(User.orders).selectinload(Order.items),
                defer(User.id),
                defer("id"),
                defer("*"),
                defer(Address.id),
                subqueryload(User.orders),
                selectinload(User.orders),
                joinedload(User.addresses).defer(Address.id),
                joinedload(aliased(User).addresses).defer(Address.id),
                joinedload(User.addresses).defer("id"),
                joinedload(User.orders).joinedload(Order.items),
                joinedload(User.orders).subqueryload(Order.items),
                subqueryload(User.orders).subqueryload(Order.items),
                subqueryload(User.orders)
                .subqueryload(Order.items)
                .defer(Item.description),
                defaultload(User.orders).defaultload(Order.items),
                defaultload(User.orders),
            ),
            compare_values=True,
        )
Esempio n. 55
0
 def base_query(self, query):
     return query.join(Cognateset.contribution)\
         .options(joinedload(Cognateset.contribution))
def budget_index():
    budgets = Budget.query.options(joinedload("user")).all()
    return jsonify(budgets_schema.dump(budgets))
Esempio n. 57
0
def test_i18n_table_creation():
    """Creates and manipulates a magical i18n table, completely independent of
    the existing schema and data.  Makes sure that the expected behavior of the
    various proxies and columns works.
    """
    Base = declarative_base()
    engine = create_engine("sqlite:///:memory:", echo=True)

    Base.metadata.bind = engine

    # Need this for the foreign keys to work!
    class Language(Base):
        __tablename__ = 'languages'
        id = Column(Integer, primary_key=True, nullable=False)
        identifier = Column(String(2), nullable=False, unique=True)

    class Foo(Base):
        __tablename__ = 'foos'
        __singlename__ = 'foo'
        id = Column(Integer, primary_key=True, nullable=False)
        translation_classes = []

    FooText = create_translation_table(
        'foo_text',
        Foo,
        'texts',
        language_class=Language,
        name=Column(String(100)),
    )

    # OK, create all the tables and gimme a session
    Base.metadata.create_all()
    sm = sessionmaker(class_=MultilangSession)
    sess = MultilangScopedSession(sm)

    # Create some languages and foos to bind together
    lang_en = Language(identifier='en')
    sess.add(lang_en)
    lang_jp = Language(identifier='jp')
    sess.add(lang_jp)
    lang_ru = Language(identifier='ru')
    sess.add(lang_ru)

    foo = Foo()
    sess.add(foo)

    # Commit so the above get primary keys filled in, then give the
    # session the language id
    sess.commit()
    # Note that this won't apply to sessions created in other threads, but that
    # ought not be a problem!
    sess.default_language_id = lang_en.id

    # Give our foo some names, as directly as possible
    foo_text = FooText()
    foo_text.foreign_id = foo.id
    foo_text.local_language_id = lang_en.id
    foo_text.name = 'english'
    sess.add(foo_text)

    foo_text = FooText()
    foo_text.foo_id = foo.id
    foo_text.local_language_id = lang_jp.id
    foo_text.name = 'nihongo'
    sess.add(foo_text)

    # Commit!  This will expire all of the above.
    sess.commit()

    ### Test 1: re-fetch foo and check its attributes
    foo = sess.query(Foo).params(_default_language='en').one()

    # Dictionary of language identifiers => names
    assert foo.name_map[lang_en] == 'english'
    assert foo.name_map[lang_jp] == 'nihongo'

    # Default language, currently English
    assert foo.name == 'english'

    sess.expire_all()

    ### Test 2: querying by default language name should work
    foo = sess.query(Foo).filter_by(name='english').one()

    assert foo.name == 'english'

    sess.expire_all()

    ### Test 3: joinedload on the default name should appear to work
    # THIS SHOULD WORK SOMEDAY
    #    .options(joinedload(Foo.name)) \
    foo = sess.query(Foo) \
        .options(joinedload(Foo.texts_local)) \
        .one()

    assert foo.name == 'english'

    sess.expire_all()

    ### Test 4: joinedload on all the names should appear to work
    # THIS SHOULD ALSO WORK SOMEDAY
    #    .options(joinedload(Foo.name_map)) \
    foo = sess.query(Foo) \
        .options(joinedload(Foo.texts)) \
        .one()

    assert foo.name_map[lang_en] == 'english'
    assert foo.name_map[lang_jp] == 'nihongo'

    sess.expire_all()

    ### Test 5: Mutating the dict collection should work
    foo = sess.query(Foo).one()

    foo.name_map[lang_en] = 'different english'
    foo.name_map[lang_ru] = 'new russian'

    sess.commit()

    assert foo.name_map[lang_en] == 'different english'
    assert foo.name_map[lang_ru] == 'new russian'
Esempio n. 58
0
 def base_query(self, query):
     query = Sources.base_query(self, query)
     query = query.join(LexibankSource.provider).options(
         joinedload(LexibankSource.provider))
     return query
Esempio n. 59
0
    def _test_options_dont_pollute(self, enable_baked):
        Parent, ChildSubclass1, Other = self.classes("Parent",
                                                     "ChildSubclass1", "Other")
        session = fixture_session(enable_baked_queries=enable_baked)

        def no_opt():
            q = session.query(Parent).options(
                joinedload(Parent.children.of_type(ChildSubclass1)))

            return self.assert_sql_execution(
                testing.db,
                q.all,
                CompiledSQL(
                    "SELECT parent.id AS parent_id, "
                    "anon_1.child_id AS anon_1_child_id, "
                    "anon_1.child_parent_id AS anon_1_child_parent_id, "
                    "anon_1.child_type AS anon_1_child_type, "
                    "anon_1.child_subclass1_id AS anon_1_child_subclass1_id "
                    "FROM parent "
                    "LEFT OUTER JOIN (SELECT child.id AS child_id, "
                    "child.parent_id AS child_parent_id, "
                    "child.type AS child_type, "
                    "child_subclass1.id AS child_subclass1_id "
                    "FROM child "
                    "LEFT OUTER JOIN child_subclass1 "
                    "ON child.id = child_subclass1.id) AS anon_1 "
                    "ON parent.id = anon_1.child_parent_id",
                    {},
                ),
                CompiledSQL(
                    "SELECT child_subclass1.id AS child_subclass1_id, "
                    "child.id AS child_id, "
                    "child.parent_id AS child_parent_id, "
                    "child.type AS child_type "
                    "FROM child JOIN child_subclass1 "
                    "ON child.id = child_subclass1.id "
                    "WHERE child.id IN ([POSTCOMPILE_primary_keys]) "
                    "ORDER BY child.id",
                    [{
                        "primary_keys": [1]
                    }],
                ),
            )

        result = no_opt()
        with self.assert_statement_count(testing.db, 1):
            eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])

        session.expunge_all()

        q = session.query(Parent).options(
            joinedload(Parent.children.of_type(ChildSubclass1)).joinedload(
                ChildSubclass1.others))

        result = self.assert_sql_execution(
            testing.db,
            q.all,
            CompiledSQL(
                "SELECT parent.id AS parent_id, "
                "anon_1.child_id AS anon_1_child_id, "
                "anon_1.child_parent_id AS anon_1_child_parent_id, "
                "anon_1.child_type AS anon_1_child_type, "
                "anon_1.child_subclass1_id AS anon_1_child_subclass1_id, "
                "other_1.id AS other_1_id, "
                "other_1.child_subclass_id AS other_1_child_subclass_id "
                "FROM parent LEFT OUTER JOIN "
                "(SELECT child.id AS child_id, "
                "child.parent_id AS child_parent_id, "
                "child.type AS child_type, "
                "child_subclass1.id AS child_subclass1_id "
                "FROM child LEFT OUTER JOIN child_subclass1 "
                "ON child.id = child_subclass1.id) AS anon_1 "
                "ON parent.id = anon_1.child_parent_id "
                "LEFT OUTER JOIN other AS other_1 "
                "ON anon_1.child_subclass1_id = other_1.child_subclass_id",
                {},
            ),
            CompiledSQL(
                "SELECT child_subclass1.id AS child_subclass1_id, "
                "child.id AS child_id, child.parent_id AS child_parent_id, "
                "child.type AS child_type, other_1.id AS other_1_id, "
                "other_1.child_subclass_id AS other_1_child_subclass_id "
                "FROM child JOIN child_subclass1 "
                "ON child.id = child_subclass1.id "
                "LEFT OUTER JOIN other AS other_1 "
                "ON child_subclass1.id = other_1.child_subclass_id "
                "WHERE child.id IN ([POSTCOMPILE_primary_keys]) "
                "ORDER BY child.id",
                [{
                    "primary_keys": [1]
                }],
            ),
        )

        with self.assert_statement_count(testing.db, 0):
            eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])

        session.expunge_all()

        result = no_opt()
        with self.assert_statement_count(testing.db, 1):
            eq_(result, [Parent(children=[ChildSubclass1(others=[Other()])])])
Esempio n. 60
0
    def banphrases_create(**options):
        session.pop("banphrase_created_id", None)
        session.pop("banphrase_edited_id", None)
        if request.method == "POST":
            id = None
            try:
                if "id" in request.form:
                    id = int(request.form["id"])
                name = request.form["name"].strip()
                permanent = request.form.get("permanent", "off")
                warning = request.form.get("warning", "off")
                notify = request.form.get("notify", "off")
                case_sensitive = request.form.get("case_sensitive", "off")
                sub_immunity = request.form.get("sub_immunity", "off")
                remove_accents = request.form.get("remove_accents", "off")
                length = int(request.form["length"])
                phrase = request.form["phrase"]
                operator = request.form["operator"].strip().lower()
            except (KeyError, ValueError):
                abort(403)

            permanent = permanent == "on"
            warning = warning == "on"
            notify = notify == "on"
            case_sensitive = case_sensitive == "on"
            sub_immunity = sub_immunity == "on"
            remove_accents = remove_accents == "on"

            if not name:
                abort(403)

            if not phrase:
                abort(403)

            if length < 0 or length > 1209600:
                abort(403)

            valid_operators = [
                "contains", "startswith", "endswith", "exact", "regex"
            ]
            if operator not in valid_operators:
                abort(403)

            user = options.get("user", None)

            if user is None:
                abort(403)

            options = {
                "name": name,
                "phrase": phrase,
                "permanent": permanent,
                "warning": warning,
                "notify": notify,
                "case_sensitive": case_sensitive,
                "sub_immunity": sub_immunity,
                "remove_accents": remove_accents,
                "length": length,
                "added_by": user.discord_id,
                "edited_by": user.discord_id,
                "operator": operator,
            }

            if id is None:
                banphrase = Banphrase(**options)
                banphrase.data = BanphraseData(banphrase.id,
                                               added_by=options["added_by"])

            with DBManager.create_session_scope(
                    expire_on_commit=False) as db_session:
                if id is not None:
                    banphrase = (db_session.query(Banphrase).options(
                        joinedload(
                            Banphrase.data)).filter_by(id=id).one_or_none())
                    if banphrase is None:
                        return redirect("/admin/banphrases/", 303)
                    banphrase.set(**options)
                    banphrase.data.set(edited_by=options["edited_by"])
                    log.info(
                        f"Updated banphrase ID {banphrase.id} by user ID {options['edited_by']}"
                    )
                    AdminLogManager.post("Banphrase edited", user.discord_id,
                                         banphrase.id, banphrase.phrase)
                else:
                    db_session.add(banphrase)
                    db_session.add(banphrase.data)
                    db_session.flush()
                    log.info(
                        f"Added a new banphrase by user ID {options['added_by']}"
                    )
                    AdminLogManager.post("Banphrase added", user.discord_id,
                                         banphrase.id, banphrase.phrase)

            SocketClientManager.send("banphrase.update", {"id": banphrase.id})
            if id is None:
                session["banphrase_created_id"] = banphrase.id
            else:
                session["banphrase_edited_id"] = banphrase.id
            return redirect("/admin/banphrases/", 303)
        else:
            return render_template("admin/create_banphrase.html")