def from_path(cls, path_in, add_id=False):
path = deque(path_in)
root_table = datamodel.get_table(path.popleft(), strict=True)
join_path = []
node = root_table
while len(path) > 0:
fieldname = path.popleft()
field = node.get_field(fieldname, strict=True)
join_path.append(field)
if field.is_relationship:
node = datamodel.get_table(field.relatedModelName)
else:
assert len(path) == 0
assert not add_id
if add_id:
join_path.append(node.idField)
return cls(root_table=root_table,
join_path=join_path,
table=node,
date_part='Full Date' if
(join_path and join_path[-1].is_temporal()) else None,
tree_rank=None,
tree_field=None)
def from_path(cls, path_in, add_id=False):
path = deque(path_in)
root_table = datamodel.get_table(path.popleft(), strict=True)
join_path = []
node = root_table
while len(path) > 0:
fieldname = path.popleft()
field = node.get_field(fieldname, strict=True)
join_path.append(field)
if field.is_relationship:
node = datamodel.get_table(field.relatedModelName)
else:
assert len(path) == 0
assert not add_id
if add_id:
join_path.append(node.idField)
return cls(root_table=root_table,
join_path=join_path,
table=node,
date_part='Full Date' if (join_path and join_path[-1].is_temporal()) else None,
tree_rank=None,
tree_field=None)
def build_join(self, table, model, join_path):
query = self
path = deque(join_path)
field = None
while len(path) > 0:
field = path.popleft()
if isinstance(field, str):
field = table.get_field(field, strict=True)
if not field.is_relationship:
break
next_table = datamodel.get_table(field.relatedModelName,
strict=True)
logger.debug("joining: %r to %r via %r", table, next_table, field)
if (model, field.name) in query.join_cache:
aliased = query.join_cache[(model, field.name)]
logger.debug("using join cache for %r.%s", model, field.name)
else:
aliased = orm.aliased(getattr(models, next_table.name))
query = query.outerjoin(aliased, getattr(model, field.name))
logger.debug("adding to join cache %r, %r",
(model, field.name), aliased)
query = query._replace(join_cache=query.join_cache.copy())
query.join_cache[(model, field.name)] = aliased
table, model = next_table, aliased
return query, model, table, field
def aggregate(self, query, field, rel_table, aggregator_name):
logger.info('aggregating field %s on %s using %s', field, rel_table, aggregator_name)
specify_model = datamodel.get_table(field.relatedModelName, strict=True)
aggregatorNode = self.getAggregatorDef(specify_model, aggregator_name)
if aggregatorNode is None:
logger.warn("aggregator is not defined")
return literal("<Aggregator not defined.>")
logger.debug("using aggregator: %s", ElementTree.tostring(aggregatorNode))
formatter_name = aggregatorNode.attrib.get('format', None)
separator = aggregatorNode.attrib.get('separator', ',')
order_by = aggregatorNode.attrib.get('orderfieldname', '')
orm_table = getattr(models, field.relatedModelName)
order_by = [getattr(orm_table, order_by)] if order_by != '' else []
join_column = list(inspect(getattr(orm_table, field.otherSideName)).property.local_columns)[0]
subquery = QueryConstruct(
collection=query.collection,
objectformatter=self,
query=orm.Query([]).select_from(orm_table) \
.filter(join_column == getattr(rel_table, rel_table._id)) \
.correlate(rel_table)
)
subquery, formatted = self.objformat(subquery, orm_table, formatter_name)
aggregated = blank_nulls(group_concat(formatted, separator, *order_by))
return subquery.query.add_column(aggregated).as_scalar()
def aggregate(self, query, field, rel_table, aggregator_name):
logger.info('aggregating field %s on %s using %s', field, rel_table,
aggregator_name)
specify_model = datamodel.get_table(field.relatedModelName,
strict=True)
aggregatorNode = self.getAggregatorDef(specify_model, aggregator_name)
if aggregatorNode is None:
logger.warn("aggregator is not defined")
return literal("<Aggregator not defined.>")
logger.debug("using aggregator: %s",
ElementTree.tostring(aggregatorNode))
formatter_name = aggregatorNode.attrib.get('format', None)
separator = aggregatorNode.attrib.get('separator', None)
order_by = aggregatorNode.attrib.get('orderfieldname', None)
orm_table = getattr(models, field.relatedModelName)
if order_by is not None and order_by != '':
order_by = getattr(orm_table, order_by)
join_column = list(
inspect(getattr(orm_table,
field.otherSideName)).property.local_columns)[0]
subquery = orm.Query([]).select_from(orm_table) \
.filter(join_column == getattr(rel_table, rel_table._id)) \
.correlate(rel_table)
subquery, formatted = self.objformat(subquery, orm_table,
formatter_name, {})
aggregated = coalesce(group_concat(formatted, separator, order_by), '')
return subquery.add_column(aggregated).as_scalar()
def objformat(self, query, orm_table, formatter_name, join_cache=None):
logger.info('formatting %s using %s', orm_table, formatter_name)
specify_model = datamodel.get_table(inspect(orm_table).class_.__name__, strict=True)
formatterNode = self.getFormatterDef(specify_model, formatter_name)
if formatterNode is None:
logger.warn("no dataobjformatter for %s", specify_model)
return query, literal("<Formatter not defined.>")
logger.debug("using dataobjformatter: %s", ElementTree.tostring(formatterNode))
def case_value_convert(value): return value
switchNode = formatterNode.find('switch')
single = switchNode.attrib.get('single', 'true') == 'true'
if not single:
sp_control_field = specify_model.get_field(switchNode.attrib['field'])
if sp_control_field.type == 'java.lang.Boolean':
def case_value_convert(value): return value == 'true'
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = build_join(query, specify_model, orm_table, path, join_cache)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name, join_cache)
else:
expr = self._fieldformat(specify_field, getattr(table, specify_field.name))
if 'format' in fieldNode.attrib:
expr = self.pseudo_sprintf(fieldNode.attrib['format'], expr)
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, coalesce(expr, '')
def make_case(query, caseNode):
field_exprs = []
for node in caseNode.findall('field'):
query, expr = make_expr(query, node)
field_exprs.append(expr)
expr = concat(*field_exprs) if len(field_exprs) > 1 else field_exprs[0]
return query, case_value_convert(caseNode.attrib.get('value', None)), expr
cases = []
for caseNode in switchNode.findall('fields'):
query, value, expr = make_case(query, caseNode)
cases.append((value, expr))
if single:
value, expr = cases[0]
else:
control_field = getattr(orm_table, switchNode.attrib['field'])
expr = case(cases, control_field)
return query, coalesce(expr, '')
def from_stringid(cls, stringid, is_relation):
path_str, table_name, field_name = STRINGID_RE.match(stringid).groups()
path = deque(path_str.split(','))
root_table = datamodel.get_table_by_id(int(path.popleft()))
if is_relation:
path.pop()
join_path = []
node = root_table
for elem in path:
try:
tableid, fieldname = elem.split('-')
except ValueError:
tableid, fieldname = elem, None
table = datamodel.get_table_by_id(int(tableid))
field = node.get_field(fieldname) if fieldname else node.get_field(
table.name)
join_path.append(field)
node = table
extracted_fieldname, date_part = extract_date_part(field_name)
field = node.get_field(extracted_fieldname, strict=False)
tree_rank = tree_field = None
if field is None:
tree_id_match = TREE_ID_FIELD_RE.match(extracted_fieldname)
if tree_id_match:
tree_rank = tree_id_match.group(1)
tree_field = 'ID'
else:
tree_field_match = TAXON_FIELD_RE.match(extracted_fieldname) \
if node is datamodel.get_table('Taxon') else None
if tree_field_match:
tree_rank = tree_field_match.group(1)
tree_field = tree_field_match.group(2)
else:
tree_rank = extracted_fieldname if extracted_fieldname else None
else:
join_path.append(field)
if field.is_temporal() and date_part is None:
date_part = "Full Date"
result = cls(root_table=root_table,
join_path=join_path,
table=node,
date_part=date_part,
tree_rank=tree_rank,
tree_field=tree_field)
logger.debug(
'parsed %s (is_relation %s) to %s. extracted_fieldname = %s',
stringid, is_relation, result, extracted_fieldname)
return result
def objformat(self, query, orm_table, formatter_name, join_cache=None):
logger.info('formatting %s using %s', orm_table, formatter_name)
specify_model = datamodel.get_table(inspect(orm_table).class_.__name__,
strict=True)
formatterNode = self.getFormatterDef(specify_model, formatter_name)
if formatterNode is None:
logger.warn("no dataobjformatter for %s", specify_model)
return query, literal("<Formatter not defined.>")
logger.debug("using dataobjformatter: %s",
ElementTree.tostring(formatterNode))
switchNode = formatterNode.find('switch')
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = build_join(
query, specify_model, orm_table, path, join_cache)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name,
join_cache)
else:
expr = self._fieldformat(specify_field,
getattr(table, specify_field.name))
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, coalesce(expr, '')
def make_case(query, caseNode):
field_exprs = []
for node in caseNode.findall('field'):
query, expr = make_expr(query, node)
field_exprs.append(expr)
expr = concat(
*field_exprs) if len(field_exprs) > 1 else field_exprs[0]
return query, caseNode.attrib.get('value', None), expr
cases = []
for caseNode in switchNode.findall('fields'):
query, value, expr = make_case(query, caseNode)
cases.append((value, expr))
if switchNode.attrib.get('single', 'true') == 'true':
value, expr = cases[0]
else:
control_field = getattr(orm_table, switchNode.attrib['field'])
expr = case(cases, control_field)
return query, coalesce(expr, '')
def from_stringid(cls, stringid, is_relation):
path_str, table_name, field_name = STRINGID_RE.match(stringid).groups()
path = deque(path_str.split(','))
root_table = datamodel.get_table_by_id(int(path.popleft()))
if is_relation:
path.pop()
join_path = []
node = root_table
for elem in path:
try:
tableid, fieldname = elem.split('-')
except ValueError:
tableid, fieldname = elem, None
table = datamodel.get_table_by_id(int(tableid))
field = node.get_field(fieldname) if fieldname else node.get_field(table.name)
join_path.append(field)
node = table
extracted_fieldname, date_part = extract_date_part(field_name)
field = node.get_field(extracted_fieldname, strict=False)
tree_rank = tree_field = None
if field is None:
tree_id_match = TREE_ID_FIELD_RE.match(extracted_fieldname)
if tree_id_match:
tree_rank = tree_id_match.group(1)
tree_field = 'ID'
else:
tree_field_match = TAXON_FIELD_RE.match(extracted_fieldname) \
if node is datamodel.get_table('Taxon') else None
if tree_field_match:
tree_rank = tree_field_match.group(1)
tree_field = tree_field_match.group(2)
else:
tree_rank = extracted_fieldname if extracted_fieldname else None
else:
join_path.append(field)
if field.is_temporal() and date_part is None:
date_part = "Full Date"
result = cls(root_table=root_table,
join_path=join_path,
table=node,
date_part=date_part,
tree_rank=tree_rank,
tree_field=tree_field)
logger.debug('parsed %s related %s to %s', stringid, is_relation, result)
return result
def __new__(cls, name, bases, dict):
Rs = super(RelatedSearchMeta, cls).__new__(cls, name, bases, dict)
if Rs.definitions is None:
return Rs
root_table_name = Rs.definitions[0].split('.')[0]
Rs.root = datamodel.get_table(root_table_name, strict=True)
def col_to_fs(col, add_id=False):
return QueryFieldSpec.from_path([root_table_name] + col.split('.'),
add_id)
Rs.display_fields = [
QueryField(fieldspec=col_to_fs(col),
op_num=1,
value="",
negate=False,
display=True,
sort_type=0) for col in Rs.columns
]
if Rs.link:
Rs.display_fields.append(
QueryField(fieldspec=col_to_fs(Rs.link, add_id=True),
op_num=1,
value="",
negate=False,
display=True,
sort_type=0))
def make_filter(f, negate):
field, op, val = f
return QueryField(
fieldspec=col_to_fs(field),
op_num=QueryOps.OPERATIONS.index(op.__name__),
value=col_to_fs(val) if isinstance(val, F) else val,
negate=negate,
display=False,
sort_type=0)
Rs.filter_fields = [make_filter(f, False) for f in Rs.filters] + \
[make_filter(f, True) for f in Rs.excludes]
return Rs
def querycbx_search(request, modelname):
table = datamodel.get_table(modelname)
model = getattr(models, table.name)
fields = [
table.get_field(fieldname, strict=True) for fieldname in request.GET
if fieldname not in ('limit', 'offset', 'forcecollection')
]
if 'forcecollection' in request.GET:
collection = Collection.objects.get(pk=request.GET['forcecollection'])
else:
collection = request.specify_collection
filters = []
for field in fields:
filters_for_field = []
terms = parse_search_str(collection, request.GET[field.name.lower()])
logger.debug("found terms: %s for %s", terms, field)
for term in terms:
filter_for_term = term.create_filter(table, field)
if filter_for_term is not None:
filters_for_field.append(filter_for_term)
logger.debug("filtering %s with %s", field, filters_for_field)
if len(filters_for_field) > 0:
filters.append(reduce(or_, filters_for_field))
if len(filters) > 0:
with models.session_context() as session:
combined = reduce(and_, filters)
query = session.query(getattr(model,
table.idFieldName)).filter(combined)
query = filter_by_collection(model, query, collection).limit(10)
ids = [id for (id, ) in query]
else:
ids = []
from specifyweb.specify.api import get_model_or_404, obj_to_data
specify_model = get_model_or_404(modelname)
qs = specify_model.objects.filter(id__in=ids)
results = [obj_to_data(obj) for obj in qs]
return HttpResponse(toJson(results), content_type='application/json')
def objformat(self, query, orm_table, formatter_name, join_cache=None):
logger.info('formatting %s using %s', orm_table, formatter_name)
specify_model = datamodel.get_table(inspect(orm_table).class_.__name__, strict=True)
formatterNode = self.getFormatterDef(specify_model, formatter_name)
logger.debug("using dataobjformater: %s", ElementTree.tostring(formatterNode))
switchNode = formatterNode.find('switch')
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = build_join(query, specify_model, orm_table, path, join_cache)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name, join_cache)
else:
expr = self._fieldformat(specify_field, getattr(table, specify_field.name))
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, coalesce(expr, '')
def make_case(query, caseNode):
field_exprs = []
for node in caseNode.findall('field'):
query, expr = make_expr(query, node)
field_exprs.append(expr)
expr = concat(*field_exprs) if len(field_exprs) > 1 else field_exprs[0]
return query, caseNode.attrib.get('value', None), expr
cases = []
for caseNode in switchNode.findall('fields'):
query, value, expr = make_case(query, caseNode)
cases.append((value, expr))
if switchNode.attrib.get('single', 'true') == 'true':
value, expr = cases[0]
else:
control_field = getattr(orm_table, switchNode.attrib['field'])
expr = case(cases, control_field)
return query, coalesce(expr, '')
def __new__(cls, name, bases, dict):
Rs = super(RelatedSearchMeta, cls).__new__(cls, name, bases, dict)
if Rs.definitions is None:
return Rs
root_table_name = Rs.definitions[0].split(".")[0]
Rs.root = datamodel.get_table(root_table_name, strict=True)
def col_to_fs(col, add_id=False):
return QueryFieldSpec.from_path([root_table_name] + col.split("."), add_id)
Rs.display_fields = [
QueryField(fieldspec=col_to_fs(col), op_num=1, value="", negate=False, display=True, sort_type=0)
for col in Rs.columns
]
if Rs.link:
Rs.display_fields.append(
QueryField(
fieldspec=col_to_fs(Rs.link, add_id=True),
op_num=1,
value="",
negate=False,
display=True,
sort_type=0,
)
)
def make_filter(f, negate):
field, op, val = f
return QueryField(
fieldspec=col_to_fs(field),
op_num=QueryOps.OPERATIONS.index(op.__name__),
value=col_to_fs(val) if isinstance(val, F) else val,
negate=negate,
display=False,
sort_type=0,
)
Rs.filter_fields = [make_filter(f, False) for f in Rs.filters] + [make_filter(f, True) for f in Rs.excludes]
return Rs
def build_primary_query(session,
searchtable,
terms,
collection,
as_scalar=False):
table = datamodel.get_table(searchtable.find('tableName').text)
model = getattr(models, table.name)
id_field = getattr(model, table.idFieldName)
fields = [
table.get_field(fn.text)
for fn in searchtable.findall('.//searchfield/fieldName')
]
q_fields = [id_field]
if not as_scalar:
q_fields.extend([
getattr(model,
table.get_field(fn.text).name)
for fn in searchtable.findall('.//displayfield/fieldName')
])
filters = [
fltr
for fltr in [t.create_filter(table, f) for f in fields for t in terms]
if fltr is not None
]
if len(filters) > 0:
reduced = reduce(or_, filters)
query = session.query(*q_fields).filter(reduced)
query = filter_by_collection(model, query, collection)
return query.as_scalar() if as_scalar else query.order_by(id_field)
logger.info("no filters for query. model: %s fields: %s terms: %s", table,
fields, terms)
return None
def build_join(query, table, model, join_path, join_cache):
path = deque(join_path)
field = None
while len(path) > 0:
field = path.popleft()
if isinstance(field, str):
field = table.get_field(field, strict=True)
if not field.is_relationship:
break
next_table = datamodel.get_table(field.relatedModelName, strict=True)
logger.debug("joining: %r to %r via %r", table, next_table, field)
if join_cache is not None and (model, field.name) in join_cache:
aliased = join_cache[(model, field.name)]
logger.debug("using join cache for %r.%s", model, field.name)
else:
aliased = orm.aliased(getattr(models, next_table.name))
if join_cache is not None:
join_cache[(model, field.name)] = aliased
logger.debug("adding to join cache %r, %r", (model, field.name), aliased)
query = query.outerjoin(aliased, getattr(model, field.name))
table, model = next_table, aliased
return query, model, table, field
def field_to_elem(field):
related_model = datamodel.get_table(field.relatedModelName)
if field.relatedModelName.lower() == field.name.lower():
return str(related_model.tableId)
else:
return "%d-%s" % (related_model.tableId, field.name.lower())
def field_to_elem(field):
related_model = datamodel.get_table(field.relatedModelName)
if field.relatedModelName.lower() == field.name.lower():
return str(related_model.tableId)
else:
return "%d-%s" % (related_model.tableId, field.name.lower())
from sqlalchemy import orm, inspect
from sqlalchemy.sql.expression import case, func, cast, literal
from sqlalchemy.sql.functions import concat, coalesce, count
from sqlalchemy import types
from specifyweb.context.app_resource import get_app_resource
from specifyweb.specify.models import datamodel, Spappresourcedata, Splocalecontaineritem
from . import models
from .queryfieldspec import build_join
from .group_concat import group_concat
logger = logging.getLogger(__name__)
CollectionObject_model = datamodel.get_table('CollectionObject')
Agent_model = datamodel.get_table('Agent')
class ObjectFormatter(object):
def __init__(self, collection, user):
formattersXML, _ = get_app_resource(collection, user, 'DataObjFormatters')
self.formattersDom = ElementTree.fromstring(formattersXML)
self.date_format = get_date_format()
self.collection = collection
def getFormatterDef(self, specify_model, formatter_name):
def lookup(attr, val):
return self.formattersDom.find('format[@%s=%s]' % (attr, quoteattr(val)))
return (formatter_name and lookup('name', formatter_name)) \
or lookup('class', specify_model.classname)
from sqlalchemy.sql.expression import case, func, cast, literal
from sqlalchemy.sql.functions import concat, count
from sqlalchemy import types
from specifyweb.context.app_resource import get_app_resource
from specifyweb.context.remote_prefs import get_remote_prefs
from specifyweb.specify.models import datamodel, Spappresourcedata, Splocalecontainer, Splocalecontaineritem
from . import models
from .group_concat import group_concat
from .blank_nulls import blank_nulls
from .query_construct import QueryConstruct
logger = logging.getLogger(__name__)
CollectionObject_model = datamodel.get_table('CollectionObject')
Agent_model = datamodel.get_table('Agent')
Spauditlog_model = datamodel.get_table('SpAuditLog')
class ObjectFormatter(object):
def __init__(self, collection, user, replace_nulls):
formattersXML, _ = get_app_resource(collection, user,
'DataObjFormatters')
self.formattersDom = ElementTree.fromstring(formattersXML)
self.date_format = get_date_format()
self.date_format_year = MYSQL_TO_YEAR.get(self.date_format)
self.date_format_month = MYSQL_TO_MONTH.get(self.date_format)
self.collection = collection
self.replace_nulls = replace_nulls
def objformat(self, query, orm_table, formatter_name):
logger.info('formatting %s using %s', orm_table, formatter_name)
specify_model = datamodel.get_table(inspect(orm_table).class_.__name__,
strict=True)
formatterNode = self.getFormatterDef(specify_model, formatter_name)
if formatterNode is None:
logger.warn("no dataobjformatter for %s", specify_model)
return query, literal("<Formatter not defined.>")
logger.debug("using dataobjformatter: %s",
ElementTree.tostring(formatterNode))
def case_value_convert(value):
return value
switchNode = formatterNode.find('switch')
single = switchNode.attrib.get('single', 'true') == 'true'
if not single:
sp_control_field = specify_model.get_field(
switchNode.attrib['field'])
if sp_control_field.type == 'java.lang.Boolean':
def case_value_convert(value):
return value == 'true'
def make_expr(query, fieldNode):
path = fieldNode.text.split('.')
query, table, model, specify_field = query.build_join(
specify_model, orm_table, path)
if specify_field.is_relationship:
formatter_name = fieldNode.attrib.get('formatter', None)
query, expr = self.objformat(query, table, formatter_name)
else:
expr = self._fieldformat(specify_field,
getattr(table, specify_field.name))
if 'format' in fieldNode.attrib:
expr = self.pseudo_sprintf(fieldNode.attrib['format'], expr)
if 'sep' in fieldNode.attrib:
expr = concat(fieldNode.attrib['sep'], expr)
return query, blank_nulls(expr)
def make_case(query, caseNode):
field_exprs = []
for node in caseNode.findall('field'):
query, expr = make_expr(query, node)
field_exprs.append(expr)
expr = concat(
*field_exprs) if len(field_exprs) > 1 else field_exprs[0]
return query, case_value_convert(caseNode.attrib.get(
'value', None)), expr
cases = []
for caseNode in switchNode.findall('fields'):
query, value, expr = make_case(query, caseNode)
cases.append((value, expr))
if single:
value, expr = cases[0]
else:
control_field = getattr(orm_table, switchNode.attrib['field'])
expr = case(cases, control_field)
return query, blank_nulls(expr)
from sqlalchemy import orm, inspect
from sqlalchemy.sql.expression import case, func, cast, literal
from sqlalchemy.sql.functions import concat, coalesce, count
from sqlalchemy import types
from specifyweb.context.app_resource import get_app_resource
from specifyweb.specify.models import datamodel, Spappresourcedata, Splocalecontaineritem
from . import models
from .queryfieldspec import build_join
from .group_concat import group_concat
logger = logging.getLogger(__name__)
CollectionObject_model = datamodel.get_table('CollectionObject')
Agent_model = datamodel.get_table('Agent')
class ObjectFormatter(object):
def __init__(self, collection, user):
formattersXML, _ = get_app_resource(collection, user,
'DataObjFormatters')
self.formattersDom = ElementTree.fromstring(formattersXML)
self.date_format = get_date_format()
self.collection = collection
def getFormatterDef(self, specify_model, formatter_name):
def lookup(attr, val):
return self.formattersDom.find('format[@%s=%s]' %
(attr, quoteattr(val)))
