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 buildAllQuery(sql, all, qMetaInfo, metaInfo, qMetaData, metaData):
'''
Builds the query for all criteria.
@param sql: SQL alchemy
The sql alchemy query to use.
@param qMetaInfo: query
The REST query object to provide filtering on for meta info.
@param metaInfo: class
The meta info mapped model class to use the query on.
@param qMetaData: query
The REST query object to provide filtering on for meta data
@param metaData: class
The meta data mapped model class to use the query on.
'''
infoMapper = mappingFor(metaInfo)
assert isinstance(infoMapper, Mapper)
dataMapper = mappingFor(metaData)
assert isinstance(dataMapper, Mapper)
baseInfoMapper = mappingFor(MetaInfoMapped)
assert isinstance(infoMapper, Mapper)
baseDataMapper = mappingFor(MetaDataMapped)
assert isinstance(dataMapper, Mapper)
infoProperties = {cp.key.lower(): getattr(infoMapper.c, cp.key)
for cp in infoMapper.iterate_properties if isinstance(cp, ColumnProperty)}
dataProperties = {cp.key.lower(): getattr(dataMapper.c, cp.key)
for cp in dataMapper.iterate_properties if isinstance(cp, ColumnProperty)}
baseInfoProperties = {cp.key.lower(): getattr(baseInfoMapper.c, cp.key)
for cp in baseInfoMapper.iterate_properties if isinstance(cp, ColumnProperty)}
baseDataProperties = {cp.key.lower(): getattr(baseDataMapper.c, cp.key)
for cp in baseDataMapper.iterate_properties if isinstance(cp, ColumnProperty)}
infoQueryType = typeFor(qMetaInfo)
dataQueryType = typeFor(qMetaData)
baseInfoQueryType = typeFor(QMetaInfo)
baseDataQueryType = typeFor(QMetaData)
if all.inc:
for value in all.inc:
clauses = list()
for criteria, crtClass in infoQueryType.query.criterias.items():
column = infoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
for criteria, crtClass in dataQueryType.query.criterias.items():
column = dataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaInfo != MetaInfoMapped:
for criteria, crtClass in baseInfoQueryType.query.criterias.items():
column = baseInfoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaData != MetaDataMapped:
for criteria, crtClass in baseDataQueryType.query.criterias.items():
column = baseDataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
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))
if all.ext:
clauses = list()
for value in all.ext:
for criteria, crtClass in infoQueryType.query.criterias.items():
column = infoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
for criteria, crtClass in dataQueryType.query.criterias.items():
column = dataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaInfo != MetaInfoMapped:
for criteria, crtClass in baseInfoQueryType.query.criterias.items():
column = baseInfoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaData != MetaDataMapped:
for criteria, crtClass in baseDataQueryType.query.criterias.items():
column = baseDataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
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))
if all.exc:
clauses = list()
for value in all.exc:
for criteria, crtClass in infoQueryType.query.criterias.items():
column = infoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
for criteria, crtClass in dataQueryType.query.criterias.items():
column = dataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
if metaInfo != MetaInfoMapped:
for criteria, crtClass in baseInfoQueryType.query.criterias.items():
column = baseInfoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
if metaData != MetaDataMapped:
for criteria, crtClass in baseDataQueryType.query.criterias.items():
column = baseDataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
length = len(clauses)
if length == 1: sql = sql.filter(clauses[0])
elif length > 1: sql = sql.filter(and_(*clauses))
return sql
def buildAllQuery(sql, all, qMetaInfo, metaInfo, qMetaData, metaData):
'''
Builds the query for all criteria.
@param sql: SQL alchemy
The sql alchemy query to use.
@param qMetaInfo: query
The REST query object to provide filtering on for meta info.
@param metaInfo: class
The meta info mapped model class to use the query on.
@param qMetaData: query
The REST query object to provide filtering on for meta data
@param metaData: class
The meta data mapped model class to use the query on.
'''
infoMapper = mappingFor(metaInfo)
assert isinstance(infoMapper, Mapper)
dataMapper = mappingFor(metaData)
assert isinstance(dataMapper, Mapper)
baseInfoMapper = mappingFor(MetaInfoMapped)
assert isinstance(infoMapper, Mapper)
baseDataMapper = mappingFor(MetaDataMapped)
assert isinstance(dataMapper, Mapper)
infoProperties = {
cp.key.lower(): getattr(infoMapper.c, cp.key)
for cp in infoMapper.iterate_properties
if isinstance(cp, ColumnProperty)
}
dataProperties = {
cp.key.lower(): getattr(dataMapper.c, cp.key)
for cp in dataMapper.iterate_properties
if isinstance(cp, ColumnProperty)
}
baseInfoProperties = {
cp.key.lower(): getattr(baseInfoMapper.c, cp.key)
for cp in baseInfoMapper.iterate_properties
if isinstance(cp, ColumnProperty)
}
baseDataProperties = {
cp.key.lower(): getattr(baseDataMapper.c, cp.key)
for cp in baseDataMapper.iterate_properties
if isinstance(cp, ColumnProperty)
}
infoQueryType = typeFor(qMetaInfo)
dataQueryType = typeFor(qMetaData)
baseInfoQueryType = typeFor(QMetaInfo)
baseDataQueryType = typeFor(QMetaData)
if all.inc:
for value in all.inc:
clauses = list()
for criteria, crtClass in infoQueryType.query.criterias.items():
column = infoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
for criteria, crtClass in dataQueryType.query.criterias.items():
column = dataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaInfo != MetaInfoMapped:
for criteria, crtClass in baseInfoQueryType.query.criterias.items(
):
column = baseInfoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaData != MetaDataMapped:
for criteria, crtClass in baseDataQueryType.query.criterias.items(
):
column = baseDataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
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))
if all.ext:
clauses = list()
for value in all.ext:
for criteria, crtClass in infoQueryType.query.criterias.items():
column = infoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
for criteria, crtClass in dataQueryType.query.criterias.items():
column = dataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaInfo != MetaInfoMapped:
for criteria, crtClass in baseInfoQueryType.query.criterias.items(
):
column = baseInfoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(column.like(processLike(value)))
if metaData != MetaDataMapped:
for criteria, crtClass in baseDataQueryType.query.criterias.items(
):
column = baseDataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
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))
if all.exc:
clauses = list()
for value in all.exc:
for criteria, crtClass in infoQueryType.query.criterias.items():
column = infoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
for criteria, crtClass in dataQueryType.query.criterias.items():
column = dataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
if metaInfo != MetaInfoMapped:
for criteria, crtClass in baseInfoQueryType.query.criterias.items(
):
column = baseInfoProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
if metaData != MetaDataMapped:
for criteria, crtClass in baseDataQueryType.query.criterias.items(
):
column = baseDataProperties.get(criteria.lower())
if column is None: continue
if crtClass == AsLikeExpression or crtClass == AsLikeExpressionOrdered:
clauses.append(not_(column.like(processLike(value))))
length = len(clauses)
if length == 1: sql = sql.filter(clauses[0])
elif length > 1: sql = sql.filter(and_(*clauses))
return sql
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 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
