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())
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)))