Example #1
0
    def clear_data_for_current_system(self):
        p = alias(FileStorage)
        ps = alias(FileStorage)
        pl = alias(RpmDetailPatchStorageLink)
        pls = alias(RpmDetailPatchStorageLink)
        rd = alias(RpmDetail)
        s = alias(System)

        delete_links_sql = delete(pl).where(
            exists(
                select([1]).select_from(
                    pls.join(
                        rd, pls.c.rpm_detail_id == rd.c.rpm_detail_id).join(
                            s, rd.c.system_id == s.c.system_id)).where(
                                s.c.system_id == self.system.system_id).where(
                                    pl.c.id == pls.c.id)))

        delete_patches_sql = delete(p).where(
            not_(
                exists(
                    select([1]).select_from(
                        pl.join(ps, pl.c.file_storage_id == ps.c.id)).where(
                            p.c.id == ps.c.id))))

        result_links = self._session.execute(delete_links_sql)

        if result_links.rowcount:
            log.info(f"Removed {result_links.rowcount} previous patch links")

        result_patches = self._session.execute(delete_patches_sql)

        if result_patches.rowcount:
            log.info(f"Removed {result_patches.rowcount} previous patches")
Example #2
0
 def purge(self, days):
     thresh = datetime_to_unix(datetime.now() - timedelta(days=days))
     rel = self._db.query(Release)
     torr = self._db.query(Torrent)
     r_orphans = exists().where(ReleaseMonitor.release_id == Release.id)
     l_orphans = exists().where(Torrent.monitor_id == ReleaseMonitor.id)
     return (self.all_query.filter_by(downloaded=True).filter(
         or_(ReleaseMonitor.download_date_stamp.is_(None),
             ReleaseMonitor.download_date_stamp < thresh)).delete(),
             rel.filter(~r_orphans).delete(False),
             torr.filter(~l_orphans).delete(False))
Example #3
0
async def add_contact_method(session: AsyncSession,
                             user_profile_id: UUID,
                             email_address: str = None,
                             phone_number: str = None) -> UUID:
    if email_address is not None:
        email_exists = exists().where(
            email_contact_method.c.email_address == email_address)
        q = select(literal(True)).\
            select_from(user_profile.join(contact_method).join(email_contact_method)).\
            where(email_exists)
        result = await session.execute(q)
        ex = result.scalar()
        if ex:
            raise ValueError(
                f"user profile with email {email_address} already exists")

        r = await session.execute(contact_method.insert().values(
            user_profile_id=user_profile_id,
            contact_method_type=ContactMethodType.Email,
            verified=False).returning(contact_method.c.contact_method_id))
        email_id = r.scalar()
        await session.execute(email_contact_method.insert().values(
            user_profile_id=user_profile_id,
            contact_method_id=email_id,
            email_address=email_address))
        return email_id

    elif phone_number is not None:
        pn_exists = exists().where(
            phone_contact_method.c.phone_number == phone_number)
        q = select(literal(True)).\
            select_from(user_profile.join(contact_method).join(phone_contact_method)).\
            where(pn_exists)
        result = await session.execute(q)
        ex = result.scalar()
        if ex:
            raise ValueError(
                f"user profile with phone number {phone_number} already exists"
            )

        r = await session.execute(contact_method.insert().values(
            user_profile_id=user_profile_id,
            contact_method_type=ContactMethodType.Phone,
            verified=False).returning(contact_method.c.contact_method_id))
        pn_id = r.scalar()
        await session.execute(phone_contact_method.insert().values(
            user_profile_id=user_profile_id,
            contact_method_id=pn_id,
            phone_number=phone_number))
        return pn_id

    raise ValueError("one of email_address or phone_number is required")
Example #4
0
def validate_version(request, **kwargs):
    document_id = request.validated['document_id']
    lang = request.validated['lang']
    version_id = request.validated['version_id']

    # check the version to revert to actually exists
    version_exists = DBSession.query(
        exists().where(
            and_(DocumentVersion.id == version_id,
                 DocumentVersion.document_id == document_id,
                 DocumentVersion.lang == lang))
    ).scalar()
    if not version_exists:
        raise HTTPBadRequest('Unknown version {}/{}/{}'.format(
            document_id, lang, version_id))

    # check the version to revert to is not the latest one
    last_version_id, = DBSession.query(DocumentVersion.id). \
        filter(and_(
            DocumentVersion.document_id == document_id,
            DocumentVersion.lang == lang)). \
        order_by(DocumentVersion.id.desc()).first()
    if version_id == last_version_id:
        raise HTTPBadRequest(
            'Version {}/{}/{} is already the latest one'.format(
                document_id, lang, version_id))
Example #5
0
    def query_multiple(
            self,
            data,
            filter_col="piece.id",
            table="clefs_ins_piece"):
        if self.validate_table(table):
            _table = self.tables[table]
            _filter_col = getattr(_table.columns, filter_col)
            q = select([_filter_col])
            for elem in data:
                query = _table.select()
                nxtalias = alias(_table)
                for key in elem:
                    col = getattr(nxtalias.columns, key)
                    expr = self.mk_or_expr(elem[key], col)
                    query = query.where(expr)

                alias_filter = getattr(nxtalias.columns, filter_col)
                query = query.where(alias_filter == _filter_col)
                q = q.where(exists(query))

            result_prox = self.execute(q)
            return set([elem[0] for elem in result_prox])
        else:
            raise BadTableException(
                "table {} not in {}".format(
                    table, self.tables.keys()))
Example #6
0
 def _get_stalled_work_requests(self, session, work_requests, results):
     """
     :param session:
     :type session:
     :param work_requests:
     :type work_requests: dict
     :param results:
     :type results: list
     :return:
     :rtype:
     """
     action_alias = aliased(ActionInstance)
     inner_query = ~exists()\
         .where(action_alias.workflow_instance_id == WorkflowInstance.id) \
         .where(action_alias.order < ActionInstance.order)\
         .where(action_alias.end_date == None)\
         .correlate(ActionInstance) \
         .correlate(WorkflowInstance)
     for action_instance, pipeline_parameters, action_instance_config in session.query(ActionInstance, PipelineParameters, ActionInstanceConfig) \
             .join(PipelineInstance, PipelineInstance.id == ActionInstance.pipeline_instance_id) \
             .join(WorkflowInstance, WorkflowInstance.id == ActionInstance.workflow_instance_id) \
             .outerjoin(PipelineParameters, PipelineParameters.pipeline_instance_id == ActionInstance.pipeline_instance_id) \
             .outerjoin(ActionInstanceConfig, ActionInstanceConfig.action_instance_id == ActionInstance.id) \
             .filter(ActionInstance.status_id == StatusConstants.NEW) \
             .filter(ActionInstance.manual == 0) \
             .filter(PipelineInstance.status_id == StatusConstants.INPROGRESS) \
             .filter(inner_query) \
             .order_by(PipelineInstance.priority.desc(),
                       PipelineInstance.created_date.asc(),
                       PipelineInstance.id.asc(),
                       ActionInstance.order.asc(),
                       ActionInstance.slice.asc()).all():
         action_instance.configuration = action_instance_config
         self.configure_work_request(action_instance, pipeline_parameters,
                                     work_requests, results)
Example #7
0
def get_user_pending_org_address(orm, user, org_id):
    Address_v_all = aliased(Address_v)
    Address_v_new = aliased(Address_v)

    query = orm.query(Address_v_all) \
        .outerjoin((
            Address,
            Address.address_id == Address_v_all.address_id
        )) \
        .join((
            org_address_v,
            and_(
                org_address_v.c.address_id == Address_v_all.address_id,
                org_address_v.c.org_id == org_id,
                org_address_v.c.existence == 1,
            )
        )) \
        .filter(
            Address_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Address_v_new.address_id == Address_v_all.address_id,
                Address_v_new.a_time > Address_v_all.a_time,
            )),
            or_(
                Address.a_time == None,
                Address_v_all.a_time > Address.a_time,
            )
        ) \
        .order_by(Address_v_all.a_time.desc())

    return query.all()
Example #8
0
def project_exists(*filter_params):
    from sqlalchemy.sql.expression import select, exists
    session = DBSession()
    project_query = session.query(Project).filter_by(*filter_params)
    project_exists_select = select((exists(project_query.statement),))
    engine = Base.metadata.bind
    return engine.execute(project_exists_select).scalar()
Example #9
0
def get_user_pending_event_contact(orm, user, event_id):
    Contact_v_all = aliased(Contact_v)
    Contact_v_new = aliased(Contact_v)

    query = orm.query(Contact_v_all) \
        .outerjoin((
            Contact,
            Contact.contact_id == Contact_v_all.contact_id
            )) \
        .join((
            event_contact_v,
            and_(
                event_contact_v.c.contact_id == Contact_v_all.contact_id,
                event_contact_v.c.event_id == event_id,
                event_contact_v.c.existence == 1,
                )
            )) \
        .filter(
            Contact_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Contact_v_new.contact_id == Contact_v_all.contact_id,
                Contact_v_new.a_time > Contact_v_all.a_time,
            )),
            or_(
                Contact.a_time == None,
                Contact_v_all.a_time > Contact.a_time,
            )
        ) \
        .order_by(Contact_v_all.a_time.desc())

    return query.all()
Example #10
0
 def __nonzero__(self):
     session = Session.object_session(self.instance)
     if session is None:
         return bool(self.count())
     for v, in session.query(exists(self.as_scalar())):
         return bool(v)
     return False
    def handle(self, *args, **options):
        # set up
        config = get_config()
        if config is None:
            raise CommandError('Unable to process configuration file p_to_p.yml')

        connection = get_connection(config)
        pedsnet_session = init_pedsnet(connection)
        init_pcornet(connection)

        observation_period = pedsnet_session.query(ObservationPeriod.person_id,
                                                   ObservationPeriod.observation_period_start_date,
                                                   ObservationPeriod.observation_period_end_date,
                                                   ObservationPeriod.site,
                                                   bindparam("chart", 'Y'),
                                                   bindparam("enr_basis", 'E')
                                                   ).filter(
            exists().where(ObservationPeriod.person_id == PersonVisit.person_id)).all()

        odo(observation_period, Enrollment.__table__,
            dshape='var * {patid: string, enr_start_date: date, enr_end_date: date, site: string, chart: String, '
                   'enr_basis: String} '
            )
        # close session
        pedsnet_session.close()

        # ouutput result
        self.stdout.ending = ''
        print('Enrollment ETL completed successfully', end='', file=self.stdout)
Example #12
0
 def is_coordinator(self):
     ## circular import :/
     from intranet3.models import Project, Client
     is_coordinator = DBSession.query(exists().where(
         or_(Client.coordinator_id == self.id,
             Project.coordinator_id == self.id))).scalar()
     return is_coordinator
Example #13
0
def get_user_pending_contact_event(orm, user, contact_id):
    Event_v_all = aliased(Event_v)
    Event_v_new = aliased(Event_v)

    query = orm.query(Event_v_all) \
        .outerjoin((
            Event,
            Event.event_id == Event_v_all.event_id
            )) \
        .join((
            event_contact_v,
            and_(
                event_contact_v.c.event_id == Event_v_all.event_id,
                event_contact_v.c.contact_id == contact_id,
                event_contact_v.c.existence == 1,
                )
            )) \
        .filter(
            Event_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Event_v_new.event_id == Event_v_all.event_id,
                Event_v_new.a_time > Event_v_all.a_time,
            )),
            or_(
                Event.a_time == None,
                Event_v_all.a_time > Event.a_time,
            )
        ) \
        .order_by(Event_v_all.a_time.desc()) \

    return query.all()
Example #14
0
 def __nonzero__(self):
     session = Session.object_session(self.instance)
     if session is None:
         return bool(self.count())
     for v, in session.query(exists(self.as_scalar())):
         return bool(v)
     return False
Example #15
0
    def query_multiple(self,
                       data,
                       filter_col="piece.id",
                       table="clefs_ins_piece"):
        if self.validate_table(table):
            _table = self.tables[table]
            _filter_col = getattr(_table.columns, filter_col)
            q = select([_filter_col])
            for elem in data:
                query = _table.select()
                nxtalias = alias(_table)
                for key in elem:
                    col = getattr(nxtalias.columns, key)
                    expr = self.mk_or_expr(elem[key], col)
                    query = query.where(expr)

                alias_filter = getattr(nxtalias.columns, filter_col)
                query = query.where(alias_filter == _filter_col)
                q = q.where(exists(query))

            result_prox = self.execute(q)
            return set([elem[0] for elem in result_prox])
        else:
            raise BadTableException("table {} not in {}".format(
                table, self.tables.keys()))
Example #16
0
def get_user_pending_contact_org(orm, user, contact_id):
    Org_v_all = aliased(Org_v)
    Org_v_new = aliased(Org_v)

    query = orm.query(Org_v_all) \
        .outerjoin((
            Org,
            Org.org_id == Org_v_all.org_id
            )) \
        .join((
            org_contact_v,
            and_(
                org_contact_v.c.org_id == Org_v_all.org_id,
                org_contact_v.c.contact_id == contact_id,
                org_contact_v.c.existence == 1,
                )
            )) \
        .filter(
            Org_v_all.moderation_user_id == user.user_id,
            ~exists().where(and_(
                Org_v_new.org_id == Org_v_all.org_id,
                Org_v_new.a_time > Org_v_all.a_time,
            )),
            or_(
                Org.a_time == None,
                Org_v_all.a_time > Org.a_time,
            )
        ) \
        .order_by(Org_v_all.a_time.desc()) \

    return query.all()
Example #17
0
 def operate(self, op, *other, **kwargs):
     q = select([self.remote_attr])
     q = q.where(self.target_class.foreign_id == self.owning_class.id)
     q = q.where(self.target_class.local_language_id == bindparam(
         '_default_language_id'))
     q = q.where(op(self.remote_attr, *other))
     return exists(q)
Example #18
0
    def _buildQuery(self, languageId=None, userId=None, q=None):
        '''
        Builds the general query for blogs.
        '''
        sql = self.session().query(BlogMapped)
        #Hide deleted blogs
        sql = sql.filter(BlogMapped.DeletedOn == None)
        
        if languageId: sql = sql.filter(BlogMapped.Language == languageId)
        if userId:
            #TODO: change it for the new version of Ally-Py, where it is a complete implementation of security 
            qRole = QRole()
            qRole.name = self.admin_role
            isAdmin = len(self.roleService.getRoles(userId, q=qRole))
            if not isAdmin:
                userFilter = (BlogMapped.Creator == userId) | exists().where((CollaboratorMapped.User == userId) \
                                             & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \
                                             & (BlogCollaboratorMapped.Blog == BlogMapped.Id))
                sql = sql.filter(userFilter)

        if q:
            assert isinstance(q, QBlog), 'Invalid query %s' % q
            sql = buildQuery(sql, q, BlogMapped)

            if (QBlog.isOpen in q) and (AsBoolean.value in q.isOpen):
                if q.isOpen.value:
                    sql = sql.filter(BlogMapped.ClosedOn == None)
                else:
                    sql = sql.filter(BlogMapped.ClosedOn != None)

        return sql
Example #19
0
    def _buildQuery(self, languageId=None, userId=None, q=None):
        '''
        Builds the general query for blogs.
        '''
        sql = self.session().query(BlogMapped)
        #Hide deleted blogs
        sql = sql.filter(BlogMapped.DeletedOn == None)

        if languageId: sql = sql.filter(BlogMapped.Language == languageId)
        if userId:
            #TODO: change it for the new version of Ally-Py, where it is a complete implementation of security
            qRole = QRole()
            qRole.name = self.admin_role
            isAdmin = len(self.roleService.getRoles(userId, q=qRole))
            if not isAdmin:
                userFilter = (BlogMapped.Creator == userId) | exists().where((CollaboratorMapped.User == userId) \
                                             & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \
                                             & (BlogCollaboratorMapped.Blog == BlogMapped.Id))
                sql = sql.filter(userFilter)

        if q:
            assert isinstance(q, QBlog), 'Invalid query %s' % q
            sql = buildQuery(sql, q, BlogMapped)

            if (QBlog.isOpen in q) and (AsBoolean.value in q.isOpen):
                if q.isOpen.value:
                    sql = sql.filter(BlogMapped.ClosedOn == None)
                else:
                    sql = sql.filter(BlogMapped.ClosedOn != None)

        return sql
Example #20
0
 def _has_permission(self, user_id, outing_id):
     """Check if the user with the given id has permission to change an
     outing. That is only users that are currently assigned to the outing
     can modify it.
     """
     return DBSession.query(exists().where(
         and_(Association.parent_document_id == user_id,
              Association.child_document_id == outing_id))).scalar()
Example #21
0
 def contains(name):
     with get_conn() as conn:
         # This is:
         #   SELECT EXISTS (SELECT 1 FROM sent_invitation WHERE entry = ?)
         return conn.execute(
             select([
                 exists(select([1]).where(sent_invitation.c.entry == name))
             ])).scalar()
Example #22
0
    def delete_orphaned(cls):
        with new_session() as session:
            subquery = select([ErrorReport.id])\
                .where(ErrorReport.item_id == Item.id)\
                .limit(1)

            query = delete(ErrorReport).where(~exists(subquery))
            session.execute(query)
Example #23
0
 def is_coordinator(self):
     ## circular import :/
     from intranet3.models import Project, Client
     is_coordinator = DBSession.query(exists().where(or_(
         Client.coordinator_id==self.id,
         Project.coordinator_id==self.id
     ))).scalar()
     return is_coordinator
Example #24
0
    def delete_orphaned(cls):
        with new_session() as session:
            subquery = select([ErrorReport.id])\
                .where(ErrorReport.item_id == Item.id)\
                .limit(1)

            query = delete(ErrorReport).where(~exists(subquery))
            session.execute(query)
 def where_clause_fn(id) :
   beam_me = beam.alias(name='beam_me')
   beam_other = beam.alias(name='beam_other')
   stmt = select([beam_other.c.id]).\
      where(and_(stem_direction.c.id == beam_other.c.id,
         beam_other.c.val == beam_me.c.val,
         beam_me.c.id == id))
   return exists(stmt)
Example #26
0
def containsDuplicate(transaction):
    return client.session.query(exists()\
    .where(Transaction.amount==transaction.amount)\
       .where(Transaction.entities_account_id==transaction.entities_account_id)\
       .where(Transaction.date==transaction.date.date())\
        .where(Transaction.imported_payee==transaction.imported_payee)\
        .where(Transaction.source==transaction.source)\
        ).scalar()
    def contains(self, url_uuid: UUID, source: DiscussionSource) -> bool:
        """Return true if the given url and source are currently in the frontier."""
        query = self._build_frontier_query().where(
            and_(
                self.b.c.url_uuid == url_uuid,
                self.ds.c.discussion_source_id == source.value,
            ))

        return self.session.execute(select([exists(query)])).scalar()
 def where_clause_fn(id) :
   stmt = select([literal(id).label('id')]).cte(name="anchors", recursive=True)
   stmt_prev = stmt.alias(name='stmt_prev')
   stmt = stmt.union_all(
     select([
       anchor.c.id
     ]).where(anchor.c.val == stmt_prev.c.id)
   )
   return exists(select([stmt.c.id]).where(anchored_table.c.id == stmt.c.id))
Example #29
0
	def checkHaveHistory(self, url):
		# Only do version fiddling if versioning is enabled.
		if not settings.DO_VERSIONING:
			return 99

		ctbl = version_table(db.WebPages.__table__)

		query = self.db_sess.query(exists().where(ctbl.c.url == url))

		return query.scalar()
Example #30
0
 def _has_permission(self, user_id, outing_id):
     """Check if the user with the given id has permission to change an
     outing. That is only users that are currently assigned to the outing
     can modify it.
     """
     return DBSession.query(exists().where(
         and_(
             Association.parent_document_id == user_id,
             Association.child_document_id == outing_id
         ))).scalar()
  def pushDB(self, db, domain_id, rr, value):
    rr = filter_rrtype[rr]

    for ttl, v in self.ttls.items():
      oid = None

      try:
        q = db.query(Entry)
        q = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value))
        q.update({Entry.first_seen: case([(Entry.first_seen > v[1], v[1])], else_=Entry.first_seen),
                  Entry.last_seen: case([(Entry.last_seen < v[2], v[2])], else_=Entry.last_seen),
                  Entry.count: Entry.count + v[0]}, synchronize_session=False)

        q = db.query(Entry.entry_id)
        o = q.filter(and_(Entry.domain_id == domain_id, Entry.type == rr, Entry.ttl == ttl, Entry.value == value)).one()

        oid = o[0]
      except NoResultFound:
        entry = Entry()
        entry.domain_id = domain_id
        entry.type = rr
        entry.ttl = ttl
        entry.value = value
        entry.first_seen = v[1]
        entry.last_seen = v[2]
        entry.count = v[0]
        db.add(entry)
        db.flush()

        oid = entry.entry_id

      for s, sv in v[3].items():
        try:
          q = db.query(exists().where((and_(DNS_Server.entry_id == oid, DNS_Server.ip == s)))).scalar()

          if not q:
            raise NoResultFound('')

          q = db.query(DNS_Server)
          q = q.filter(and_(DNS_Server.entry_id == oid, DNS_Server.ip == s))
          q.update({DNS_Server.first_seen: case([(DNS_Server.first_seen > sv[1], sv[1])], else_=DNS_Server.first_seen),
                    DNS_Server.last_seen: case([(DNS_Server.last_seen < sv[2], sv[2])], else_=DNS_Server.last_seen),
                    DNS_Server.count: DNS_Server.count + sv[0]}, synchronize_session=False)

        except NoResultFound:
          dns_server = DNS_Server()
          dns_server.entry_id = oid
          dns_server.ip = s
          dns_server.first_seen = sv[1]
          dns_server.last_seen = sv[2]
          dns_server.count = sv[0]
          db.add(dns_server)
          db.flush()

    db.flush()
Example #32
0
 def check_if_database_has(self, name: str) -> Query:
     """
     Checks if specified name in database
     :param name: string name
     :return: boolean result of query
     """
     session = self._sessionmaker()
     found_pokemon_bool = session.query(
         exists().where(Pokemon.name == name))
     session.close()
     return found_pokemon_bool
Example #33
0
def createBlogMediaType(key):
    creator = alchemySessionCreator()
    session = creator()
    assert isinstance(session, Session)

    if not session.query(exists().where(BlogMediaTypeMapped.Key == key)).scalar():
        blogMediaTypeDb = BlogMediaTypeMapped()
        blogMediaTypeDb.Key = key
        session.add(blogMediaTypeDb)

    session.commit()
    session.close()
Example #34
0
def has_been_created_by(document_id, user_id):
    """Check if passed user_id is the id of the user that has created
    the initial version of this document, whatever the language.
    """
    return DBSession.query(
        exists().where(and_(
            ArchiveDocument.document_id == document_id,
            ArchiveDocument.version == 1,
            DocumentVersion.document_id == document_id,
            HistoryMetaData.user_id == user_id
        ))
    ).scalar()
Example #35
0
def __verify_record__(user_id):
    try:
        (user_record, ), = db.session.query(
                exists().where(User.id==user_id).where(User.last_run.isnot(None))
        )
        if user_record == False:
            # @TODO: Build page for general error & log custom exception
            raise Exception
        # Persists change to database
        db.session.commit()
    except Exception as e:
        raise
Example #36
0
async def checkRowsExists(rowSpecs: List[RowSpec]):
    conditions = []
    for tableCondition in rowSpecs:
        # build a EXISTS() query
        query = tableCondition.table.select()
        for condition in tableCondition.conditions:
            # combine conditions into the WHERE clause
            query = query.where(condition)
        conditions.append(exists(query))
    # combine the exists queries together
    query = select([and_(*conditions)])
    return await db.execute(query)
def createPostType(key):
    creator = alchemySessionCreator()
    session = creator()
    assert isinstance(session, Session)

    if not session.query(exists().where(PostTypeMapped.Key == key)).scalar():
        postTypeDb = PostTypeMapped()
        postTypeDb.Key = key
        session.add(postTypeDb)

    session.commit()
    session.close()
Example #38
0
def createVerificationStatus(key):
    creator = alchemySessionCreator()
    session = creator()
    assert isinstance(session, Session)

    if not session.query(exists().where(VerificationStatusMapped.Key == key)).scalar():
        verificationStatus = VerificationStatusMapped()
        verificationStatus.Key = key
        session.add(verificationStatus)

    session.commit()
    session.close()
Example #39
0
def createBlogMediaType(key):
    creator = alchemySessionCreator()
    session = creator()
    assert isinstance(session, Session)

    if not session.query(exists().where(BlogMediaTypeMapped.Key == key)).scalar():
        blogMediaTypeDb = BlogMediaTypeMapped()
        blogMediaTypeDb.Key = key
        session.add(blogMediaTypeDb)

    session.commit()
    session.close()
def createPostType(key):
    creator = alchemySessionCreator()
    session = creator()
    assert isinstance(session, Session)

    if not session.query(exists().where(PostTypeMapped.Key == key)).scalar():
        postTypeDb = PostTypeMapped()
        postTypeDb.Key = key
        session.add(postTypeDb)

    session.commit()
    session.close()
Example #41
0
def has_permission_for_outing(request, outing_id):
    """Check if the user with the given id has permission to change an
    outing. That is only users that are currently assigned to the outing
    can modify it.
    """
    if request.has_permission('moderator'):
        # moderators can change everything
        return True

    user_id = request.authenticated_userid
    return DBSession.query(exists().where(
        and_(Association.parent_document_id == user_id,
             Association.child_document_id == outing_id))).scalar()
    def _index_query_restricted_by_export_recipient(
            self, q: Query) -> Optional[Query]:
        """
        For exports.

        Filters via our
        :class:`camcops_server.cc_modules.cc_exportrecipient.ExportRecipient`,
        except for the bits already implemented via our
        :class:`camcops_server.cc_modules.cc_taskfilter.TaskFilter`.

        The main job here is for incremental exports: to find tasks that have
        not yet been exported.

        Compare :meth:`_task_query_restricted_by_export_recipient`.

        Args:
            q: the starting SQLAlchemy ORM Query

        Returns:
            the original query, a modified query, or ``None`` if no tasks
            would pass the filter

        """
        from camcops_server.cc_modules.cc_exportmodels import (
            ExportedTask, )  # delayed import

        r = self.export_recipient
        if not r.is_incremental():
            # Full database export; no restrictions
            return q
        # Otherwise, restrict to tasks not yet sent to this recipient.
        # Remember: q is a query on TaskIndexEntry.
        # noinspection PyUnresolvedReferences
        q = q.filter(
            # "There is not a successful export record for this task/recipient"
            ~exists().select_from(
                ExportedTask.__table__.join(
                    ExportRecipient.__table__,
                    ExportedTask.recipient_id == ExportRecipient.id,
                )).
            where(
                and_(
                    ExportRecipient.recipient_name == r.recipient_name,
                    ExportedTask.basetable == TaskIndexEntry.task_table_name,
                    # ... don't use ".tablename" as a property doesn't play
                    # nicely with SQLAlchemy here
                    ExportedTask.task_server_pk == TaskIndexEntry.task_pk,
                    ExportedTask.success == True,  # noqa: E712
                    ExportedTask.cancelled == False,  # noqa: E712
                )))
        return q
Example #43
0
 def _struct_filter(self, class_, bindParam, rpt):
     '''
     Private method to filter reagent or product by smarts pattern
     
     @param class_: class to filter, either RxnReagent or RxnProduct
     @type class_: class
     @param bindParam: name of bind parameter for SMARTS pattern
     @type bindParam: str
     @param rpt: report to filter
     @type rpt: app.model.gridconf.Report 
     '''
     rpt.filter(exists().where(and_(class_.mol_id == RxnMol.mol_id, class_.rxn_id == Rxn.rxn_id,
                                    func.chem.compareSmartsToMol(bindparam(bindParam), RxnMol.mol_struct),
                                    RxnMol.fingerprint.op('|')(func.chem.convertSmilesToFingerprint(bindparam(bindParam))) == RxnMol.fingerprint)))
    def _task_query_restricted_by_export_recipient(
            self, q: Query, cls: Type[Task]) -> Optional[Query]:
        """
        For exports.

        Filters via our
        :class:`camcops_server.cc_modules.cc_exportrecipient.ExportRecipient`,
        except for the bits already implemented via our
        :class:`camcops_server.cc_modules.cc_taskfilter.TaskFilter`.

        The main job here is for incremental exports: to find tasks that have
        not yet been exported. We look for any tasks not yet exported to a
        recipient of the same name (regardless of ``ExportRecipient.id``, which
        changes when the export recipient is reconfigured).

        Compare :meth:`_index_query_restricted_by_export_recipient`.

        Args:
            q: the starting SQLAlchemy ORM Query
            cls: the task class

        Returns:
            the original query, a modified query, or ``None`` if no tasks
            would pass the filter
        """
        from camcops_server.cc_modules.cc_exportmodels import (
            ExportedTask, )  # delayed import

        r = self.export_recipient
        if not r.is_incremental():
            # Full database export; no restrictions
            return q
        # Otherwise, restrict to tasks not yet sent to this recipient.
        # noinspection PyUnresolvedReferences
        q = q.filter(
            # "There is not a successful export record for this task/recipient"
            ~exists().select_from(
                ExportedTask.__table__.join(
                    ExportRecipient.__table__,
                    ExportedTask.recipient_id == ExportRecipient.id,
                )).where(
                    and_(
                        ExportRecipient.recipient_name == r.recipient_name,
                        ExportedTask.basetable == cls.__tablename__,
                        ExportedTask.task_server_pk == cls._pk,
                        ExportedTask.success == True,  # noqa: E712
                        ExportedTask.cancelled == False,  # noqa: E712
                    )))
        return q
Example #45
0
 def _buildQuery(self,
                 languageId=None,
                 adminId=None,
                 collaboratorId=None,
                 q=None):
     '''
     Builds the general query for blogs.
     '''
     sql = self.session().query(BlogMapped)
     if languageId: sql = sql.filter(BlogMapped.Language == languageId)
     userFilter = None
     if adminId:
         userFilter = (BlogMapped.Creator == adminId) | exists().where(
             (AdminEntry.adminId == adminId)
             & (AdminEntry.Blog == BlogMapped.Id))
     if collaboratorId:
         userFilter |= exists().where((CollaboratorMapped.Person == collaboratorId) \
                                      & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \
                                      & (BlogCollaboratorMapped.Blog == BlogMapped.Id))
     if userFilter is not None: sql = sql.filter(userFilter)
     if q:
         assert isinstance(q, QBlog), 'Invalid query %s' % q
         sql = buildQuery(sql, q, BlogMapped)
     return sql
Example #46
0
 def where_clause_fn(id) :
   linked_list_fwd = bound_range(id,
                             generic_next,
                             name="fwd_list")
   linked_list_bwd = bound_range(id,
                             generic_next,
                             forward=False,
                             name = "bwd_list")
   linked_list = select([linked_list_bwd]).union(select([linked_list_fwd])).cte(name="combined_lists")
   stmt = select([linked_list]).select_from(linked_list).where(linked_list.c.elt == table_to_delete_on.c.id)
   # the second clause guarantees that we are only deleting when
   # we are getting a new referent
   if not generic_anchor_statement :
      stmt = stmt.where(generic_anchor.c.id == id)
   return exists(stmt)
Example #47
0
def _is_last_route_of_outing(association):
    if not(association.parent_document_type == ROUTE_TYPE and
            association.child_document_type == OUTING_TYPE):
        # other association type, nothing to check
        return False

    outing_has_other_routes = exists(). \
        where(Association.parent_document_type == ROUTE_TYPE). \
        where(Association.child_document_type == OUTING_TYPE). \
        where(Association.parent_document_id !=
              association.parent_document_id). \
        where(
            Association.child_document_id == association.child_document_id)

    return not DBSession.query(outing_has_other_routes).scalar()
Example #48
0
def _is_last_route_of_outing(association):
    if not (association.parent_document_type == ROUTE_TYPE
            and association.child_document_type == OUTING_TYPE):
        # other association type, nothing to check
        return False

    outing_has_other_routes = exists(). \
        where(Association.parent_document_type == ROUTE_TYPE). \
        where(Association.child_document_type == OUTING_TYPE). \
        where(Association.parent_document_id !=
              association.parent_document_id). \
        where(
            Association.child_document_id == association.child_document_id)

    return not DBSession.query(outing_has_other_routes).scalar()
Example #49
0
def _is_last_waypoint_of_route(association):
    if not (association.parent_document_type == WAYPOINT_TYPE
            and association.child_document_type == ROUTE_TYPE):
        # other association type, nothing to check
        return False

    route_has_other_waypoints = exists(). \
        where(Association.parent_document_type == WAYPOINT_TYPE). \
        where(Association.child_document_type == ROUTE_TYPE). \
        where(Association.parent_document_id !=
              association.parent_document_id). \
        where(
            Association.child_document_id == association.child_document_id)

    return not DBSession.query(route_has_other_waypoints).scalar()
Example #50
0
def has_permission_for_outing(request, outing_id):
    """Check if the user with the given id has permission to change an
    outing. That is only users that are currently assigned to the outing
    can modify it.
    """
    if request.has_permission('moderator'):
        # moderators can change everything
        return True

    user_id = request.authenticated_userid
    return DBSession.query(exists().where(
        and_(
            Association.parent_document_id == user_id,
            Association.child_document_id == outing_id
        ))).scalar()
Example #51
0
def _is_last_waypoint_of_route(association):
    if not(association.parent_document_type == WAYPOINT_TYPE and
            association.child_document_type == ROUTE_TYPE):
        # other association type, nothing to check
        return False

    route_has_other_waypoints = exists(). \
        where(Association.parent_document_type == WAYPOINT_TYPE). \
        where(Association.child_document_type == ROUTE_TYPE). \
        where(Association.parent_document_id !=
              association.parent_document_id). \
        where(
            Association.child_document_id == association.child_document_id)

    return not DBSession.query(route_has_other_waypoints).scalar()
Example #52
0
    def list_volume_files(self):
        volume = self._get_volume(self.request.matchdict["volume_reference"])

        j = file_table.join(blob_table,
                            file_table.c.blob_id == blob_table.c.id)
        s = (select([
            file_table, blob_table.c.handle
        ]).select_from(j).where(file_table.c.volume_id == volume["id"]))

        if "without_statements" in self.request.GET:
            filter_query = (select(
                [statement_table.c.id]).select_from(statement_table).where(
                    statement_table.c.object_blob_id == blob_table.c.id))
            s = s.where(not_(exists(filter_query)))

        if "path" in self.request.GET:
            paths = [
                base64.urlsafe_b64decode(p)
                for p in self.request.GET.getall("path")
            ]
            s = s.where(file_table.c.path.in_(paths))

        if "after" in self.request.GET:
            after = base64.urlsafe_b64decode(self.request.GET["after"])
            s = s.where(file_table.c.path > after)

        limit = 1000
        if "limit" in self.request.GET:
            limit = min(int(self.request.GET["limit"]), self.max_limit)
        s = s.order_by(file_table.c.path).limit(limit)

        files = [{
            "path":
            os.fsdecode(r[file_table.c.path]),
            "size":
            r[file_table.c.size],
            "mtime":
            r[file_table.c.mtime].isoformat(),
            "lastverify":
            r[file_table.c.lastverify].isoformat(),
            "handle":
            base64.urlsafe_b64encode(r[blob_table.c.handle]).decode("utf-8"),
        } for r in self.db.execute(s)]

        return {
            "results": files,
            "limit": limit,
        }
Example #53
0
    def _buildQuery(self, languageId=None, userId=None, q=None):
        '''
        Builds the general query for blogs.
        '''
        sql = self.session().query(BlogMapped)
        if languageId: sql = sql.filter(BlogMapped.Language == languageId)
        if userId:
            userFilter = (BlogMapped.Creator == userId) | exists().where((CollaboratorMapped.User == userId) \
                                         & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \
                                         & (BlogCollaboratorMapped.Blog == BlogMapped.Id))
            sql = sql.filter(userFilter)

        if q:
            assert isinstance(q, QBlog), 'Invalid query %s' % q
            sql = buildQuery(sql, q, BlogMapped)
        return sql
 def create_file():
     """
     File from request will be saved to database
     """
     file = request.files["file"]
     filename = secure_filename(file.filename)
     if db.session.query(exists().where(File.name == filename)).scalar() == False:
         if file.mimetype.split('/', 1)[0] == "image":
             file.save(os.path.join(os.path.realpath('.') + '/static/', filename))
             file_object = File(name=filename, path='', owner_id=login.current_user.id)
             save_to_db(file_object, "file saved")
             flash("Image added")
         else:
             flash("The selected file is not an image. Please select a image file and try again.")
     else:
         flash("A file named \"" + filename + "\" already exists")
Example #55
0
 def _buildQuery(self, languageId=None, adminId=None, q=None):
     """
     Builds the general query for blogs.
     """
     sql = self.session().query(BlogMapped)
     if languageId:
         sql = sql.filter(BlogMapped.Language == languageId)
     if adminId:
         sql = sql.filter(
             (BlogMapped.Creator == adminId)
             | exists().where((AdminEntry.adminId == adminId) & (AdminEntry.Blog == BlogMapped.Id))
         )
     if q:
         assert isinstance(q, QBlog), "Invalid query %s" % q
         sql = buildQuery(sql, q, BlogMapped)
     return sql
Example #56
0
 def _buildQuery(self, languageId=None, userId=None, q=None):
     '''
     Builds the general query for blogs.
     '''
     sql = self.session().query(BlogMapped)
     if languageId: sql = sql.filter(BlogMapped.Language == languageId)
     if userId:
         userFilter = (BlogMapped.Creator == userId) | exists().where((CollaboratorMapped.User == userId) \
                                      & (BlogCollaboratorMapped.blogCollaboratorId == CollaboratorMapped.Id) \
                                      & (BlogCollaboratorMapped.Blog == BlogMapped.Id))
         sql = sql.filter(userFilter)
         
     if q:
         assert isinstance(q, QBlog), 'Invalid query %s' % q
         sql = buildQuery(sql, q, BlogMapped)
     return sql
Example #57
0
  def _domain_exists(self, domain, search_like=False):
    q = self.db.query(Domain.domain_id, Domain.domain_name)
    ret = {}

    if search_like:
      res = self._search_domain(domain)

      for r in res:
        ret[r] = True
    else:
      res = self.db.query(exists().where(Domain.domain_name == domain)).scalar()

      if res:
        ret[domain] = True
      else:
        ret[domain] = False

    return ret
    def handle(self, *args, **options):
        # set up
        config = get_config()
        if config is None:
            raise CommandError('Unable to process configuration file p_to_p.yml')

        connection = get_connection(config)
        pedsnet_session = init_pedsnet(connection)
        init_pcornet(connection)

        pedsnet_pcornet_valueset_map = aliased(ValueSetMap)

        # extract the data from the death table
        death_pedsnet = pedsnet_session.query(DeathPedsnet.death_date,
                                              coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT'),
                                              bindparam("death_match_confidence", None),
                                              bindparam("death_source", "L"),
                                              DeathPedsnet.person_id,
                                              min(DeathPedsnet.site)
                                              ). \
            outerjoin(pedsnet_pcornet_valueset_map,
                      and_(pedsnet_pcornet_valueset_map.source_concept_class == 'Death date impute',
                           cast(DeathPedsnet.death_impute_concept_id, String(200)) ==
                           pedsnet_pcornet_valueset_map.source_concept_id)) \
            .filter(and_(exists().where(DeathPedsnet.person_id == PersonVisit.person_id),
                         DeathPedsnet.death_type_concept_id == 38003569)) \
            .group_by(DeathPedsnet.person_id, DeathPedsnet.death_date,
                      coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT')) \
            .all()

        # transform data to pcornet names and types
        # load to demographic table
        odo(death_pedsnet, DeathPcornet.__table__,
            dshape='var * {death_date: date, death_date_impute: string, death_match_confidence: string,'
                   'death_source: string, patid:string, site: string}'
            )

        # close session
        pedsnet_session.close()

        # output result
        self.stdout.ending = ''
        print('Death ETL completed successfully', end='', file=self.stdout)