def getMetaInfos(self, scheme, offset=None, limit=None, qi=None, qd=None, thumbSize=None): ''' Provides the meta data based on unified multi-plugin criteria. ''' sql = self.session().query(MetaInfoMapped) if qi is not None: assert isinstance(qi, self.QMetaInfo), 'Invalid query %s' % qi metaInfos = self.queryIndexer.metaInfos.copy() assert isinstance(metaInfos, set) for name in namesForQuery(qi): if getattr(self.QMetaInfo, name) not in qi: continue criteriaMetaInfos = self.queryIndexer.metaInfoByCriteria.get( name) assert criteriaMetaInfos, 'No model class available for %s' % name metaInfos.intersection(criteriaMetaInfos) sql = buildQuery(sql, qi, MetaInfoMapped) for metaInfo in metaInfos: sql = sql.join(metaInfo) try: sql = buildQuery(sql, qi, metaInfo) except: raise Exception('Cannot build query for meta info %s' % metaInfo) if qd is not None: assert isinstance(qd, self.QMetaData), 'Invalid query %s' % qd metaDatas = self.queryIndexer.metaDatas.copy() assert isinstance(metaDatas, set) for name in namesForQuery(qd): if getattr(self.QMetaData, name) not in qd: continue criteriaMetaDatas = self.queryIndexer.metaDataByCriteria.get( name) assert criteriaMetaDatas, 'No model class available for %s' % name metaDatas.intersection(criteriaMetaDatas) sql = sql.join(MetaDataMapped) sql = buildQuery(sql, qd, MetaDataMapped) for metaData in metaDatas: sql = sql.join(metaData) sql = buildQuery(sql, qd, metaData) sql = buildLimits(sql, offset, limit) return sql.all()
def getMetaInfos(self, scheme, offset=None, limit=None, qi=None, qd=None, thumbSize=None): ''' Provides the meta data based on unified multi-plugin criteria. ''' sql = self.session().query(MetaInfoMapped) if qi is not None: assert isinstance(qi, self.QMetaInfo), 'Invalid query %s' % qi metaInfos = self.queryIndexer.metaInfos.copy() assert isinstance(metaInfos, set) for name in namesForQuery(qi): if getattr(self.QMetaInfo, name) not in qi: continue criteriaMetaInfos = self.queryIndexer.metaInfoByCriteria.get(name) assert criteriaMetaInfos, 'No model class available for %s' % name metaInfos.intersection(criteriaMetaInfos) sql = buildQuery(sql, qi, MetaInfoMapped) for metaInfo in metaInfos: sql = sql.join(metaInfo) try: sql = buildQuery(sql, qi, metaInfo) except : raise Exception('Cannot build query for meta info %s' % metaInfo) if qd is not None: assert isinstance(qd, self.QMetaData), 'Invalid query %s' % qd metaDatas = self.queryIndexer.metaDatas.copy() assert isinstance(metaDatas, set) for name in namesForQuery(qd): if getattr(self.QMetaData, name) not in qd: continue criteriaMetaDatas = self.queryIndexer.metaDataByCriteria.get(name) assert criteriaMetaDatas, 'No model class available for %s' % name metaDatas.intersection(criteriaMetaDatas) sql = sql.join(MetaDataMapped) sql = buildQuery(sql, qd, MetaDataMapped) for metaData in metaDatas: sql = sql.join(metaData) sql = buildQuery(sql, qd, metaData) sql = buildLimits(sql, offset, limit) return sql.all()
def buildQuery(sqlQuery, query, mapped): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. ''' assert query is not None, 'A query object is required' clazz = query.__class__ ordered, unordered = [], [] mapper = mappingFor(mapped) assert isinstance(mapper, Mapper) properties = {cp.key.lower(): getattr(mapper.c, cp.key) for cp in mapper.iterate_properties if isinstance(cp, ColumnProperty)} for criteria in namesForQuery(clazz): column = properties.get(criteria.lower()) if column is not None and getattr(clazz, criteria) in query: crt = getattr(query, criteria) if isinstance(crt, AsBoolean): assert isinstance(crt, AsBoolean) if AsBoolean.value in crt: sqlQuery = sqlQuery.filter(column == crt.value) elif isinstance(crt, AsLike): assert isinstance(crt, AsLike) if AsLike.like in crt: sqlQuery = sqlQuery.filter(column.like(crt.like)) elif AsLike.ilike in crt: sqlQuery = sqlQuery.filter(column.ilike(crt.ilike)) elif isinstance(crt, AsEqual): assert isinstance(crt, AsEqual) if AsEqual.equal in crt: sqlQuery = sqlQuery.filter(column == crt.equal) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: sqlQuery = sqlQuery.filter(column >= crt.start) elif crt.__class__.until in crt: sqlQuery = sqlQuery.filter(column < crt.until) if crt.__class__.end in crt: sqlQuery = sqlQuery.filter(column <= crt.end) elif crt.__class__.since in crt: sqlQuery = sqlQuery.filter(column > crt.since) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((column, crt.ascending, crt.priority)) else: unordered.append((column, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for column, asc, __ in chain(ordered, unordered): if asc: sqlQuery = sqlQuery.order_by(column) else: sqlQuery = sqlQuery.order_by(column.desc()) return sqlQuery
def register(self, EntryMetaInfoClass, QMetaInfoClass, EntryMetaDataClass, QMetaDataClass, type): ''' see: IQueryIndexer.register() ''' assert isclass(EntryMetaInfoClass) and issubclass(EntryMetaInfoClass, Base), \ 'Invalid entry meta info class %s' % EntryMetaInfoClass assert isclass(EntryMetaInfoClass) and EntryMetaInfoClass is MetaInfoMapped or \ not issubclass(EntryMetaInfoClass, MetaInfoMapped), \ 'The Entry class should be registered, not extended class %s' % EntryMetaInfoClass assert isclass(QMetaInfoClass) and issubclass(QMetaInfoClass, QMetaInfo), \ 'Invalid meta info query class %s' % QMetaInfoClass assert isclass(EntryMetaDataClass) and issubclass(EntryMetaDataClass, Base), \ 'Invalid entry meta data class %s' % EntryMetaDataClass assert isclass(EntryMetaDataClass) and EntryMetaDataClass is MetaDataMapped or \ not issubclass(EntryMetaDataClass, MetaDataMapped), \ 'The Entry class should be registered, not extended class %s' % EntryMetaInfoClass assert isclass(QMetaDataClass) and issubclass(QMetaDataClass, QMetaData), \ 'Invalid meta data query class %s' % QMetaDataClass if (EntryMetaInfoClass in self.metaInfos): raise Exception('Already registered the meta info class %s' % EntryMetaInfoClass) if (EntryMetaDataClass in self.metaDatas): raise Exception('Already registered the meta data class %s' % EntryMetaInfoClass) self.metaDatasByInfo[EntryMetaInfoClass.__name__] = EntryMetaDataClass self.metaInfosByData[EntryMetaDataClass.__name__] = EntryMetaInfoClass self.typesByMetaData[EntryMetaDataClass.__name__] = type self.typesByMetaInfo[EntryMetaInfoClass.__name__] = type self.queryByData[EntryMetaDataClass.__name__] = QMetaDataClass self.queryByInfo[EntryMetaInfoClass.__name__] = QMetaInfoClass for criteria in namesForQuery(QMetaInfoClass): criteriaClass = self.infoCriterias.get(criteria) if (criteriaClass is None): continue criteriaType = typeFor(getattr(QMetaInfoClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) if (criteriaType.clazz != criteriaClass): raise Exception("Can't register meta data %s because the %s criteria has type %s " \ "and this criteria already exist with a different type %s" % \ (EntryMetaInfoClass, criteria, criteriaType.clazz, criteriaClass)) for criteria in namesForQuery(QMetaDataClass): criteriaClass = self.dataCriterias.get(criteria) if (criteriaClass is None): continue criteriaType = typeFor(getattr(QMetaDataClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) if (criteriaType.clazz != criteriaClass): raise Exception("Can't register meta data %s because the %s criteria has type %s " \ "and this criteria already exist with a different type %s" % \ (EntryMetaDataClass, criteria, criteriaType.clazz, criteriaClass)) self.metaInfos.add(EntryMetaInfoClass) self.metaDatas.add(EntryMetaDataClass) for criteria in namesForQuery(QMetaInfoClass): criteriaType = typeFor(getattr(QMetaInfoClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) infoSet = self.metaInfoByCriteria.get(criteria) if infoSet is None: infoSet = self.metaInfoByCriteria[criteria] = set() self.infoCriterias[criteria] = criteriaType.clazz infoSet.add(EntryMetaInfoClass) for criteria in namesForQuery(QMetaDataClass): criteriaType = typeFor(getattr(QMetaDataClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) dataSet = self.metaDataByCriteria.get(criteria) if dataSet is None: dataSet = self.metaDataByCriteria[criteria] = set() self.dataCriterias[criteria] = criteriaType.clazz dataSet.add(EntryMetaDataClass)
def buildSolrQuery(si, solrQuery, query, orClauses): ''' Builds the Solr query based on a given REST query. @param si: SorlInterface The current connection to Solr server. @param solrQuery: SolrSearch The solr query to use. @param query: query The REST query object to provide querying on. @param mapped: List The list of OR clauses. ''' ordered, unordered = [], [] clazz = query.__class__ for criteria in namesForQuery(clazz): if getattr(clazz, criteria) not in query: continue if criteria in si.schema.fields: field = criteria else: upperCriteria = criteria[0].upper() + criteria[1:] if upperCriteria in si.schema.fields: field = upperCriteria else: continue crt = getattr(query, criteria) if isinstance(crt, AsBoolean): if AsBoolean.value in crt: if solrQuery is None: solrQuery = si.query(**{field : crt.value}) else: solrQuery = solrQuery.query(**{field : crt.value}) elif isinstance(crt, AsLike): if AsLike.like in crt: if solrQuery is None: solrQuery = si.query(**{field : crt.like}) else: solrQuery = solrQuery.query(**{field : crt.like}) elif AsLike.ilike in crt: if solrQuery is None: solrQuery = si.query(**{field : crt.ilike}) else: solrQuery = solrQuery.query(**{field : crt.ilike}) elif isinstance(crt, AsEqual): if AsEqual.equal in crt: if solrQuery is None: solrQuery = si.query(**{field : crt.equal}) else: solrQuery = solrQuery.query(**{field : crt.equal}) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: if solrQuery is None: solrQuery = si.query(**{field + '__gte' : crt.start}) else: solrQuery = solrQuery.query(**{field + '__gte' : crt.start}) elif crt.__class__.until in crt: if solrQuery is None: solrQuery = si.query(**{field + '__lt' : crt.until}) else: solrQuery = solrQuery.query(**{field + '__lt' : crt.until}) if crt.__class__.end in crt: if solrQuery is None: solrQuery = si.query(**{field + '__lte' : crt.end}) else: solrQuery = solrQuery.query(**{field + '__lte' : crt.end}) elif crt.__class__.since in crt: if solrQuery is None: solrQuery = si.query(**{field + '__gt' : crt.since}) else: solrQuery = solrQuery.query(**{field + '__gt' : crt.since}) elif isinstance(crt, AsLikeExpression): if AsLikeExpression.inc in crt: for value in crt.inc: if solrQuery is None: solrQuery = si.query(**{field : value}) else: solrQuery = solrQuery.query(**{field : value}) if crt and AsLikeExpression.ext in crt: for value in crt.ext: orClauses.append(si.Q(**{field : value})) if crt and AsLikeExpression.exc in crt: for value in crt.exc: if solrQuery is None: solrQuery = si.exclude(**{field : value}) else: solrQuery = solrQuery.exclude(**{field : value}) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((field, crt.ascending, crt.priority)) else: unordered.append((field, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for field, asc, __ in chain(ordered, unordered): if asc: if solrQuery is None: solrQuery = si.sort_by(field) else: solrQuery = solrQuery.sort_by(field) else: if solrQuery is None: solrQuery = si.sort_by('-' + field) else: solrQuery = solrQuery.sort_by('-' + field) return solrQuery
def buildQuery(sqlQuery, query, mapped, only=None, exclude=None): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. @param only: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to build the query for, if no criteria is provided then all the query criteria are considered. @param exclude: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to be excluded when processing the query. If you provided a only parameter you cannot provide an exclude. ''' assert query is not None, 'A query object is required' clazz = query.__class__ columns, ordered, unordered = {}, [], [] for name in namesForModel(mapped): cp, name = getattr(mapped, name), name.lower() if name not in columns and isinstance(cp, (PropertyAttribute, _Case)): columns[name] = cp columns = { criteria: columns.get(criteria.lower()) for criteria in namesForQuery(clazz) } if only: if not isinstance(only, tuple): only = (only, ) assert not exclude, 'Cannot have only \'%s\' and exclude \'%s\' criteria at the same time' % ( only, exclude) onlyColumns = {} for criteria in only: if isinstance(criteria, str): column = columns.get(criteria) assert column is not None, 'Invalid only criteria name \'%s\' for query class %s' % ( criteria, clazz) onlyColumns[criteria] = column else: typ = typeFor(criteria) assert isinstance( typ, TypeCriteriaEntry), 'Invalid only criteria %s' % criteria column = columns.get(criteria) assert column is not None, 'Invalid only criteria \'%s\' for query class %s' % ( criteria, clazz) onlyColumns[criteria] = column columns = onlyColumns elif exclude: if not isinstance(exclude, tuple): exclude = (exclude, ) for criteria in exclude: if isinstance(criteria, str): column = columns.pop(criteria, None) assert column is not None, 'Invalid exclude criteria name \'%s\' for query class %s' % ( criteria, clazz) else: typ = typeFor(criteria) assert isinstance(typ, TypeCriteriaEntry ), 'Invalid exclude criteria %s' % criteria column = columns.pop(typ.name, None) assert column is not None, 'Invalid exclude criteria \'%s\' for query class %s' % ( criteria, clazz) for criteria, column in columns.items(): if column is None or getattr(clazz, criteria) not in query: continue crt = getattr(query, criteria) if isinstance(crt, AsBoolean): assert isinstance(crt, AsBoolean) if AsBoolean.value in crt: sqlQuery = sqlQuery.filter(column == crt.value) elif isinstance(crt, AsLike): assert isinstance(crt, AsLike) if AsLike.like in crt: sqlQuery = sqlQuery.filter(column.like(crt.like)) elif AsLike.ilike in crt: sqlQuery = sqlQuery.filter(column.ilike(crt.ilike)) elif isinstance(crt, AsEqual): assert isinstance(crt, AsEqual) if AsEqual.equal in crt: sqlQuery = sqlQuery.filter(column == crt.equal) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: sqlQuery = sqlQuery.filter(column >= crt.start) elif crt.__class__.until in crt: sqlQuery = sqlQuery.filter(column < crt.until) if crt.__class__.end in crt: sqlQuery = sqlQuery.filter(column <= crt.end) elif crt.__class__.since in crt: sqlQuery = sqlQuery.filter(column > crt.since) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((column, crt.ascending, crt.priority)) else: unordered.append((column, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for column, asc, __ in chain(ordered, unordered): if asc: sqlQuery = sqlQuery.order_by(column) else: sqlQuery = sqlQuery.order_by(column.desc()) return sqlQuery
def register(self, EntryMetaInfoClass, QMetaInfoClass, EntryMetaDataClass, QMetaDataClass): ''' Construct the meta info base service for the provided classes. @param EntryMetaInfoClass: class A class that contains the specific for media meta info related columns. @param QMetaInfoClass: class A class that extends QMetaInfo API class. @param MetaDataClass: class A class that contains the specific for media meta data related columns. @param QMetaDataClass: class A class that extends QMetaData API class. ''' assert isclass(EntryMetaInfoClass) and issubclass(EntryMetaInfoClass, Base), \ 'Invalid entry meta info class %s' % EntryMetaInfoClass assert not issubclass(EntryMetaInfoClass, MetaInfoMapped), \ 'The Entry class should be registered, not extended class %s' % EntryMetaInfoClass assert isclass(QMetaInfoClass) and issubclass(QMetaInfoClass, QMetaInfo), \ 'Invalid meta info query class %s' % QMetaInfoClass assert isclass(EntryMetaDataClass) and issubclass(EntryMetaDataClass, Base), \ 'Invalid entry meta data class %s' % EntryMetaDataClass assert not issubclass(EntryMetaDataClass, MetaDataMapped), \ 'The Entry class should be registered, not extended class %s' % EntryMetaInfoClass assert isclass(QMetaDataClass) and issubclass(QMetaDataClass, QMetaData), \ 'Invalid meta data query class %s' % QMetaDataClass if (EntryMetaInfoClass in self.metaInfos): raise Exception('Already registered the meta info class %s' % EntryMetaInfoClass) if (EntryMetaDataClass in self.metaDatas): raise Exception('Already registered the meta data class %s' % EntryMetaInfoClass) for criteria in namesForQuery(QMetaInfoClass): criteriaClass = self.infoCriterias.get(criteria) if (criteriaClass is None): continue criteriaType = typeFor(getattr(QMetaInfoClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) if (criteriaType.clazz != criteriaClass): raise Exception("Can't register meta data %s because the %s criteria has type %s " \ "and this criteria already exist with a different type %s" % \ (EntryMetaInfoClass, criteria, criteriaType.clazz, criteriaClass)) for criteria in namesForQuery(QMetaDataClass): criteriaClass = self.dataCriterias.get(criteria) if (criteriaClass is None): continue criteriaType = typeFor(getattr(QMetaDataClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) if (criteriaType.clazz != criteriaClass): raise Exception("Can't register meta data %s because the %s criteria has type %s " \ "and this criteria already exist with a different type %s" % \ (EntryMetaDataClass, criteria, criteriaType.clazz, criteriaClass)) self.metaInfos.add(EntryMetaInfoClass) self.metaDatas.add(EntryMetaDataClass) for criteria in namesForQuery(QMetaInfoClass): criteriaType = typeFor(getattr(QMetaInfoClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) infoSet = self.metaInfoByCriteria.get(criteria) if infoSet is None: infoSet = self.metaInfoByCriteria[criteria] = set() self.infoCriterias[criteria] = criteriaType.clazz infoSet.add(EntryMetaInfoClass) for criteria in namesForQuery(QMetaDataClass): criteriaType = typeFor(getattr(QMetaDataClass, criteria)) assert isinstance(criteriaType, TypeCriteriaEntry) dataSet = self.metaDataByCriteria.get(criteria) if dataSet is None: dataSet = self.metaDataByCriteria[criteria] = set() self.dataCriterias[criteria] = criteriaType.clazz dataSet.add(EntryMetaDataClass)
def buildSolrQuery(si, solrQuery, query, orClauses): ''' Builds the Solr query based on a given REST query. @param si: SorlInterface The current connection to Solr server. @param solrQuery: SolrSearch The solr query to use. @param query: query The REST query object to provide querying on. @param mapped: List The list of OR clauses. ''' ordered, unordered = [], [] clazz = query.__class__ for criteria in namesForQuery(clazz): if getattr(clazz, criteria) not in query: continue if criteria in si.schema.fields: field = criteria else: upperCriteria = criteria[0].upper() + criteria[1:] if upperCriteria in si.schema.fields: field = upperCriteria else: continue crt = getattr(query, criteria) if isinstance(crt, AsBoolean): if AsBoolean.value in crt: if solrQuery is None: solrQuery = si.query(**{field: crt.value}) else: solrQuery = solrQuery.query(**{field: crt.value}) elif isinstance(crt, AsLike): if AsLike.like in crt: if solrQuery is None: solrQuery = si.query(**{field: crt.like}) else: solrQuery = solrQuery.query(**{field: crt.like}) elif AsLike.ilike in crt: if solrQuery is None: solrQuery = si.query(**{field: crt.ilike}) else: solrQuery = solrQuery.query(**{field: crt.ilike}) elif isinstance(crt, AsEqual): if AsEqual.equal in crt: if solrQuery is None: solrQuery = si.query(**{field: crt.equal}) else: solrQuery = solrQuery.query(**{field: crt.equal}) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: if solrQuery is None: solrQuery = si.query(**{field + '__gte': crt.start}) else: solrQuery = solrQuery.query(**{field + '__gte': crt.start}) elif crt.__class__.until in crt: if solrQuery is None: solrQuery = si.query(**{field + '__lt': crt.until}) else: solrQuery = solrQuery.query(**{field + '__lt': crt.until}) if crt.__class__.end in crt: if solrQuery is None: solrQuery = si.query(**{field + '__lte': crt.end}) else: solrQuery = solrQuery.query(**{field + '__lte': crt.end}) elif crt.__class__.since in crt: if solrQuery is None: solrQuery = si.query(**{field + '__gt': crt.since}) else: solrQuery = solrQuery.query(**{field + '__gt': crt.since}) elif isinstance(crt, AsLikeExpression): if AsLikeExpression.inc in crt: for value in crt.inc: if solrQuery is None: solrQuery = si.query(**{field: value}) else: solrQuery = solrQuery.query(**{field: value}) if crt and AsLikeExpression.ext in crt: for value in crt.ext: orClauses.append(si.Q(**{field: value})) if crt and AsLikeExpression.exc in crt: for value in crt.exc: if solrQuery is None: solrQuery = si.exclude(**{field: value}) else: solrQuery = solrQuery.exclude(**{field: value}) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((field, crt.ascending, crt.priority)) else: unordered.append((field, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for field, asc, __ in chain(ordered, unordered): if asc: if solrQuery is None: solrQuery = si.sort_by(field) else: solrQuery = solrQuery.sort_by(field) else: if solrQuery is None: solrQuery = si.sort_by('-' + field) else: solrQuery = solrQuery.sort_by('-' + field) return solrQuery
def buildQuery(self, session, scheme, offset=None, limit=1000, qa=None, qi=None, qd=None): ''' @see: ISearchProvider.buildQuery() ''' metaInfos = set() metaDatas = set() sqlUnion = None sqlList = list() types = [self.queryIndexer.typesByMetaData[key] for key in self.queryIndexer.typesByMetaData.keys()] if qa is not None: assert isinstance(qa, QMetaDataInfo), 'Invalid query %s' % qa if QMetaDataInfo.type in qa: types = qa.type.values for name, criteria in self.queryIndexer.infoCriterias.items(): if criteria is AsLikeExpression or criteria is AsLikeExpressionOrdered: criteriaMetaInfos = self.queryIndexer.metaInfoByCriteria.get(name) # if MetaInfo is present, add only MetaInfo if MetaInfoMapped not in criteriaMetaInfos: for metaInfo in criteriaMetaInfos: if self.queryIndexer.typesByMetaInfo[metaInfo.__name__] in types: metaInfos.add(metaInfo) elif self.queryIndexer.typesByMetaInfo[getattr(MetaInfoMapped, '__name__')] in types: metaInfos.add(MetaInfoMapped) for name, criteria in self.queryIndexer.dataCriterias.items(): if criteria is AsLikeExpression or criteria is AsLikeExpressionOrdered: criteriaMetaDatas = self.queryIndexer.metaDataByCriteria.get(name) # if MetaData is present, add only MetaData if MetaDataMapped not in criteriaMetaDatas: for metaData in criteriaMetaDatas: if self.queryIndexer.typesByMetaData[metaData.__name__] in types: metaDatas.add(metaData) elif self.queryIndexer.typesByMetaData[getattr(MetaDataMapped, '__name__')] in types: metaDatas.add(MetaDataMapped) if qi is not None: assert isinstance(qi, self.QMetaInfo), 'Invalid query %s' % qi for name in namesForQuery(qi): if getattr(self.QMetaInfo, name) not in qi: continue criteriaMetaInfos = self.queryIndexer.metaInfoByCriteria.get(name) assert criteriaMetaInfos, 'No model class available for %s' % name # if MetaInfo is present, add only MetaInfo if MetaInfoMapped not in criteriaMetaInfos: for metaInfo in criteriaMetaInfos: if self.queryIndexer.typesByMetaInfo[metaInfo.__name__] in types: metaInfos.add(metaInfo) elif self.queryIndexer.typesByMetaInfo[getattr(MetaInfoMapped, '__name__')] in types: metaInfos.add(MetaInfoMapped) if qd is not None: assert isinstance(qd, self.QMetaData), 'Invalid query %s' % qd for name in namesForQuery(qd): if getattr(self.QMetaData, name) not in qd: continue criteriaMetaDatas = self.queryIndexer.metaDataByCriteria.get(name) assert criteriaMetaDatas, 'No model class available for %s' % name # if MetaData is present, add only MetaData if MetaDataMapped not in criteriaMetaDatas: for metaData in criteriaMetaDatas: if self.queryIndexer.typesByMetaData[metaData.__name__] in types: metaDatas.add(metaData) elif self.queryIndexer.typesByMetaData[getattr(MetaDataMapped, '__name__')] in types: metaDatas.add(MetaDataMapped) if not metaInfos and not metaDatas: pass; elif metaInfos and not metaDatas: for metaInfo in metaInfos: sql = self.buildSubquery(session, metaInfo, MetaDataMapped, qa, qi, qd, types) if sql: sqlList.append(sql) elif not metaInfos and metaDatas: for metaData in metaDatas: sql = self.buildSubquery(session, MetaInfoMapped, metaData, qa, qi, qd, types) if sql: sqlList.append(sql) else: for metaInfo in metaInfos: metaData = self.queryIndexer.metaDatasByInfo[metaInfo.__name__] if metaData in metaDatas: sql = self.buildSubquery(session, metaInfo, metaData, qa, qi, qd, types) if sql: sqlList.append(sql) else: sql = self.buildSubquery(session, metaInfo, MetaDataMapped, qa, qi, qd, types) if sql: sqlList.append(sql) for metaData in metaDatas: if metaData is MetaDataMapped: continue if self.queryIndexer.metaInfosByData[metaData.__name__] not in metaInfos: sql = self.buildSubquery(session, MetaInfoMapped, metaData, qa, qi, qd, types) if sql: sqlList.append(sql) sqlLength = len(sqlList) if sqlLength == 0: sqlUnion = self.buildSubquery(session, MetaInfoMapped, MetaDataMapped, qa, qi, qd, types) elif sqlLength == 1: sqlUnion = sqlList[0] else: sqlUnion = sqlList.pop() sqlUnion = sqlUnion.union(*sqlList) count = sqlUnion.count() sqlUnion = buildLimits(sqlUnion, offset, limit) return (sqlUnion, count)
def buildExpressionQuery(sql, query, mapped, qa): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. ''' assert query is not None, 'A query object is required' clazz = query.__class__ mapper = mappingFor(mapped) assert isinstance(mapper, Mapper) all = None if qa: all = qa.all columns = {cp.key.lower(): getattr(mapper.c, cp.key) for cp in mapper.iterate_properties if isinstance(cp, ColumnProperty)} columns = {criteria:columns.get(criteria.lower()) for criteria in namesForQuery(clazz)} for criteria, column in columns.items(): if column is None or getattr(clazz, criteria) not in query: continue crt = getattr(query, criteria) if isinstance(crt, AsLikeExpression) or isinstance(crt, AsLikeExpressionOrdered): # include if AsLikeExpression.inc in crt: for value in crt.inc: sql = sql.filter(column.like(processLike(value))) if all and AsLikeExpression.inc in all: for value in all.inc: sql = sql.filter(column.like(processLike(value))) # extend clauses = list() if AsLikeExpression.ext in crt: for value in crt.ext: clauses.append(column.like(processLike(value))) if all and AsLikeExpression.ext in all: for value in all.ext: clauses.append(column.like(processLike(value))) length = len(clauses) if length == 1: sql = sql.filter(clauses[0]) elif length > 1: sql = sql.filter(or_(*clauses)) # exclude if AsLikeExpression.exc in crt: for value in crt.exc: sql = sql.filter(not_(column.like(processLike(value)))) if all and AsLikeExpression.exc in all: for value in all.exc: sql = sql.filter(not_(column.like(processLike(value)))) return sql
def buildQuery(self, session, scheme, offset=None, limit=1000, qa=None, qi=None, qd=None): ''' @see: ISearchProvider.buildQuery() ''' metaInfos = set() metaDatas = set() sqlUnion = None sqlList = list() types = [ self.queryIndexer.typesByMetaData[key] for key in self.queryIndexer.typesByMetaData.keys() ] if qa is not None: assert isinstance(qa, QMetaDataInfo), 'Invalid query %s' % qa if QMetaDataInfo.type in qa: types = qa.type.values for name, criteria in self.queryIndexer.infoCriterias.items(): if criteria is AsLikeExpression or criteria is AsLikeExpressionOrdered: criteriaMetaInfos = self.queryIndexer.metaInfoByCriteria.get( name) # if MetaInfo is present, add only MetaInfo if MetaInfoMapped not in criteriaMetaInfos: for metaInfo in criteriaMetaInfos: if self.queryIndexer.typesByMetaInfo[ metaInfo.__name__] in types: metaInfos.add(metaInfo) elif self.queryIndexer.typesByMetaInfo[getattr( MetaInfoMapped, '__name__')] in types: metaInfos.add(MetaInfoMapped) for name, criteria in self.queryIndexer.dataCriterias.items(): if criteria is AsLikeExpression or criteria is AsLikeExpressionOrdered: criteriaMetaDatas = self.queryIndexer.metaDataByCriteria.get( name) # if MetaData is present, add only MetaData if MetaDataMapped not in criteriaMetaDatas: for metaData in criteriaMetaDatas: if self.queryIndexer.typesByMetaData[ metaData.__name__] in types: metaDatas.add(metaData) elif self.queryIndexer.typesByMetaData[getattr( MetaDataMapped, '__name__')] in types: metaDatas.add(MetaDataMapped) if qi is not None: assert isinstance(qi, self.QMetaInfo), 'Invalid query %s' % qi for name in namesForQuery(qi): if getattr(self.QMetaInfo, name) not in qi: continue criteriaMetaInfos = self.queryIndexer.metaInfoByCriteria.get( name) assert criteriaMetaInfos, 'No model class available for %s' % name # if MetaInfo is present, add only MetaInfo if MetaInfoMapped not in criteriaMetaInfos: for metaInfo in criteriaMetaInfos: if self.queryIndexer.typesByMetaInfo[ metaInfo.__name__] in types: metaInfos.add(metaInfo) elif self.queryIndexer.typesByMetaInfo[getattr( MetaInfoMapped, '__name__')] in types: metaInfos.add(MetaInfoMapped) if qd is not None: assert isinstance(qd, self.QMetaData), 'Invalid query %s' % qd for name in namesForQuery(qd): if getattr(self.QMetaData, name) not in qd: continue criteriaMetaDatas = self.queryIndexer.metaDataByCriteria.get( name) assert criteriaMetaDatas, 'No model class available for %s' % name # if MetaData is present, add only MetaData if MetaDataMapped not in criteriaMetaDatas: for metaData in criteriaMetaDatas: if self.queryIndexer.typesByMetaData[ metaData.__name__] in types: metaDatas.add(metaData) elif self.queryIndexer.typesByMetaData[getattr( MetaDataMapped, '__name__')] in types: metaDatas.add(MetaDataMapped) if not metaInfos and not metaDatas: pass elif metaInfos and not metaDatas: for metaInfo in metaInfos: sql = self.buildSubquery(session, metaInfo, MetaDataMapped, qa, qi, qd, types) if sql: sqlList.append(sql) elif not metaInfos and metaDatas: for metaData in metaDatas: sql = self.buildSubquery(session, MetaInfoMapped, metaData, qa, qi, qd, types) if sql: sqlList.append(sql) else: for metaInfo in metaInfos: metaData = self.queryIndexer.metaDatasByInfo[metaInfo.__name__] if metaData in metaDatas: sql = self.buildSubquery(session, metaInfo, metaData, qa, qi, qd, types) if sql: sqlList.append(sql) else: sql = self.buildSubquery(session, metaInfo, MetaDataMapped, qa, qi, qd, types) if sql: sqlList.append(sql) for metaData in metaDatas: if metaData is MetaDataMapped: continue if self.queryIndexer.metaInfosByData[ metaData.__name__] not in metaInfos: sql = self.buildSubquery(session, MetaInfoMapped, metaData, qa, qi, qd, types) if sql: sqlList.append(sql) sqlLength = len(sqlList) if sqlLength == 0: sqlUnion = self.buildSubquery(session, MetaInfoMapped, MetaDataMapped, qa, qi, qd, types) elif sqlLength == 1: sqlUnion = sqlList[0] else: sqlUnion = sqlList.pop() sqlUnion = sqlUnion.union(*sqlList) count = sqlUnion.count() sqlUnion = buildLimits(sqlUnion, offset, limit) return (sqlUnion, count)
def buildExpressionQuery(sql, query, mapped, qa): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. ''' assert query is not None, 'A query object is required' clazz = query.__class__ mapper = mappingFor(mapped) assert isinstance(mapper, Mapper) all = None if qa: all = qa.all columns = { cp.key.lower(): getattr(mapper.c, cp.key) for cp in mapper.iterate_properties if isinstance(cp, ColumnProperty) } columns = { criteria: columns.get(criteria.lower()) for criteria in namesForQuery(clazz) } for criteria, column in columns.items(): if column is None or getattr(clazz, criteria) not in query: continue crt = getattr(query, criteria) if isinstance(crt, AsLikeExpression) or isinstance( crt, AsLikeExpressionOrdered): # include if AsLikeExpression.inc in crt: for value in crt.inc: sql = sql.filter(column.like(processLike(value))) if all and AsLikeExpression.inc in all: for value in all.inc: sql = sql.filter(column.like(processLike(value))) # extend clauses = list() if AsLikeExpression.ext in crt: for value in crt.ext: clauses.append(column.like(processLike(value))) if all and AsLikeExpression.ext in all: for value in all.ext: clauses.append(column.like(processLike(value))) length = len(clauses) if length == 1: sql = sql.filter(clauses[0]) elif length > 1: sql = sql.filter(or_(*clauses)) # exclude if AsLikeExpression.exc in crt: for value in crt.exc: sql = sql.filter(not_(column.like(processLike(value)))) if all and AsLikeExpression.exc in all: for value in all.exc: sql = sql.filter(not_(column.like(processLike(value)))) return sql
def buildQuery(sqlQuery, query, mapped, only=None, exclude=None): ''' Builds the query on the SQL alchemy query. @param sqlQuery: SQL alchemy The sql alchemy query to use. @param query: query The REST query object to provide filtering on. @param mapped: class The mapped model class to use the query on. @param only: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to build the query for, if no criteria is provided then all the query criteria are considered. @param exclude: tuple(string|TypeCriteriaEntry)|string|TypeCriteriaEntry|None The criteria names or references to be excluded when processing the query. If you provided a only parameter you cannot provide an exclude. ''' assert query is not None, 'A query object is required' clazz = query.__class__ columns, ordered, unordered = {}, [], [] for name in namesForModel(mapped): cp, name = getattr(mapped, name), name.lower() if name not in columns and isinstance(cp, (PropertyAttribute, _Case)): columns[name] = cp columns = {criteria:columns.get(criteria.lower()) for criteria in namesForQuery(clazz)} if only: if not isinstance(only, tuple): only = (only,) assert not exclude, 'Cannot have only \'%s\' and exclude \'%s\' criteria at the same time' % (only, exclude) onlyColumns = {} for criteria in only: if isinstance(criteria, str): column = columns.get(criteria) assert column is not None, 'Invalid only criteria name \'%s\' for query class %s' % (criteria, clazz) onlyColumns[criteria] = column else: typ = typeFor(criteria) assert isinstance(typ, TypeCriteriaEntry), 'Invalid only criteria %s' % criteria column = columns.get(criteria) assert column is not None, 'Invalid only criteria \'%s\' for query class %s' % (criteria, clazz) onlyColumns[criteria] = column columns = onlyColumns elif exclude: if not isinstance(exclude, tuple): exclude = (exclude,) for criteria in exclude: if isinstance(criteria, str): column = columns.pop(criteria, None) assert column is not None, 'Invalid exclude criteria name \'%s\' for query class %s' % (criteria, clazz) else: typ = typeFor(criteria) assert isinstance(typ, TypeCriteriaEntry), 'Invalid exclude criteria %s' % criteria column = columns.pop(typ.name, None) assert column is not None, 'Invalid exclude criteria \'%s\' for query class %s' % (criteria, clazz) for criteria, column in columns.items(): if column is None or getattr(clazz, criteria) not in query: continue crt = getattr(query, criteria) if isinstance(crt, AsBoolean): assert isinstance(crt, AsBoolean) if AsBoolean.value in crt: sqlQuery = sqlQuery.filter(column == crt.value) elif isinstance(crt, AsLike): assert isinstance(crt, AsLike) if AsLike.like in crt: sqlQuery = sqlQuery.filter(column.like(crt.like)) elif AsLike.ilike in crt: sqlQuery = sqlQuery.filter(column.ilike(crt.ilike)) elif isinstance(crt, AsEqual): assert isinstance(crt, AsEqual) if AsEqual.equal in crt: sqlQuery = sqlQuery.filter(column == crt.equal) elif isinstance(crt, (AsDate, AsTime, AsDateTime, AsRange)): if crt.__class__.start in crt: sqlQuery = sqlQuery.filter(column >= crt.start) elif crt.__class__.until in crt: sqlQuery = sqlQuery.filter(column < crt.until) if crt.__class__.end in crt: sqlQuery = sqlQuery.filter(column <= crt.end) elif crt.__class__.since in crt: sqlQuery = sqlQuery.filter(column > crt.since) if isinstance(crt, AsOrdered): assert isinstance(crt, AsOrdered) if AsOrdered.ascending in crt: if AsOrdered.priority in crt and crt.priority: ordered.append((column, crt.ascending, crt.priority)) else: unordered.append((column, crt.ascending, None)) ordered.sort(key=lambda pack: pack[2]) for column, asc, __ in chain(ordered, unordered): if asc: sqlQuery = sqlQuery.order_by(column) else: sqlQuery = sqlQuery.order_by(column.desc()) return sqlQuery