async def get_paragraph_order(model: BaseModel) -> Cast: """ Сортировка запроса по числовому параграфовидному префиксу наименования @param model: модель ORM @return: выражение для сортировки по префиксу """ paragraph_order = cast( func.string_to_array( func.string_to_array( model.name, ' ', type_=ARRAY(String) )[1], '.' ), ARRAY(Integer) ) return paragraph_order
def get_profiles(): error, verification_token = get_token(request.headers) if error: return error query = request.args.get('query') verification_email_id = VerificationToken.query.filter( VerificationToken.token == verification_token.token).value( VerificationToken.email_id) return jsonify( profiles_schema.dump( matching_profiles(query).order_by( # Is this the logged-in user's profile? If so, return it first (false) Profile.verification_email_id != verification_email_id, # Get the last word in the name. # Won't work with suffixes. func.split_part( Profile.name, ' ', func.array_length( func.string_to_array( func.regexp_replace( Profile.name, '(,|MD).*', ''), # Remove suffixes after comma and MD ' '), 1 # How many words in the name )))))
def validate_team_name(team_name): s = select([ func.string_to_array(Setting.value, ',').op('@>')([team_name]).\ label('has')]).\ where(Setting.key == 'teams') row = db.session.execute(s).fetchone() return row[0]
def search_by_array(cls, q: str): """ SELECT * FROM devices WHERE 'X' % ANY(STRING_TO_ARRAY(name, ' ')); """ query = cls.query.filter( bindparam('string', q).op('%%')(any_(func.string_to_array(Device.name, ' ')))) return query
def service_info_column(crop): """return optionally cropped service.info column""" if crop: return func.array_to_string( func.string_to_array(Service.info, ' ', type_=postgresql.ARRAY( db.String))[1:int(crop)], ' ') return Service.info
class Node(Base): __tablename__ = "node" id = Column(Integer, primary_key=True, autoincrement=False) path = Column(String(500), nullable=False, index=True) # To find the descendants of this node, we look for nodes whose path # starts with this node's path. descendants = relationship( "Node", viewonly=True, order_by=path, primaryjoin=remote(foreign(path)).like(path.concat(".%")), ) # Finding the ancestors is a little bit trickier. We need to create a fake # secondary table since this behaves like a many-to-many join. secondary = select( id.label("id"), func.unnest( cast( func.string_to_array(func.regexp_replace(path, r"\.?\d+$", ""), "."), ARRAY(Integer), )).label("ancestor_id"), ).alias() ancestors = relationship( "Node", viewonly=True, secondary=secondary, primaryjoin=id == secondary.c.id, secondaryjoin=secondary.c.ancestor_id == id, order_by=path, ) @property def depth(self): return len(self.path.split(".")) - 1 def __repr__(self): return "Node(id={})".format(self.id) def __str__(self): root_depth = self.depth s = [str(self.id)] s.extend(((n.depth - root_depth) * " " + str(n.id)) for n in self.descendants) return "\n".join(s) def move_to(self, new_parent): new_path = new_parent.path + "." + str(self.id) for n in self.descendants: n.path = new_path + n.path[len(self.path):] self.path = new_path
class Node(Base): __tablename__ = 'node' id = Column(Integer, primary_key=True, autoincrement=False) path = Column(String(500), nullable=False, index=True) # 想要找到这个node的后代,我们需要搜索以当前node的path为path前缀的node descendants = relationship('Node', viewonly=True, order_by=path, primaryjoin=remote(foreign(path)).like( path.concat(".%"))) # 想要找到这个node的祖先有点复杂。 # 我们需要创建一个伪secondary表,因为这个行为有些像many-to-many secondary = select([ id.label('id'), func.unnest( cast( func.string_to_array(func.regexp_replace(path, r"\.?\d+$", ""), "."), ARRAY(Integer))).label('ancestor_id') ]).alias() ancestor = relationship("Node", viewonly=True, secondary=secondary, primaryjoin=id == secondary.c.id, secondaryjoin=secondary.c.ancestor_id == id, order_by=path) @property def depth(self): return len(self.path.split(".")) - 1 def __repr__(self): return "Node(id={})".format(self.id) def __str__(self): root_depth = self.depth s = [str(self.id)] s.extend(((n.depth - root_depth) * " " + str(n.id)) for n in self.descendants) return "\n".join(s) def move_to(self, new_parent): new_path = new_parent + "." + str(self.id) for n in self.descendants: n.path = new_path + n.path[len(self.path):] self.path = new_path
def query_enumerate_attribute_values(dbsession, layerinfos, fieldname): attrinfos = layerinfos["attributes"][fieldname] table = attrinfos["table"] layertable = get_table(table, session=dbsession) column = attrinfos.get("column_name", fieldname) attribute = getattr(layertable.columns, column) # For instance if `separator` is a "," we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if "separator" in attrinfos: separator = attrinfos["separator"] attribute = func.unnest(func.string_to_array( func.string_agg(attribute, separator), separator )) return dbsession.query(distinct(attribute)).order_by(attribute).all()
def ts_locs_array( config: ColumnElement, text: ColumnElement, tsquery: ColumnElement, ) -> ColumnElement: options = f"HighlightAll = TRUE, StartSel = {TS_START}, StopSel = {TS_STOP}" delimited = func.ts_headline(config, text, tsquery, options) parts = func.unnest(func.string_to_array(delimited, TS_START)).alias() part = column(parts.name) part_len = func.length(part) - len(TS_STOP) match_pos = func.sum(part_len).over(rows=(None, -1)) + len(TS_STOP) match_len = func.strpos(part, TS_STOP) - 1 return func.array( select([postgresql.array([match_pos, match_len]) ]).select_from(parts).offset(1).as_scalar(), )
def query_enumerate_attribute_values( dbsession: sqlalchemy.orm.Session, layerinfos: Dict[str, Any], fieldname: str) -> Set[Tuple[str, ...]]: attrinfos = layerinfos["attributes"][fieldname] table = attrinfos["table"] layertable = get_table(table, session=dbsession) column = attrinfos.get("column_name", fieldname) attribute = getattr(layertable.columns, column) # For instance if `separator` is a "," we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if "separator" in attrinfos: separator = attrinfos["separator"] attribute = func.unnest( func.string_to_array(func.string_agg(attribute, separator), separator)) return set( cast(List[Tuple[str, ...]], dbsession.query(attribute).order_by(attribute).all()))
def _enumerate_attribute_values(self, general_dbsession_name, layername, fieldname): if layername not in self.layers_enum_config: # pragma: no cover raise HTTPBadRequest('Unknown layer: %s' % layername) layerinfos = self.layers_enum_config[layername] if fieldname not in layerinfos['attributes']: # pragma: no cover raise HTTPBadRequest('Unknown attribute: %s' % fieldname) dbsession = DBSessions.get( layerinfos.get('dbsession', general_dbsession_name), None) if dbsession is None: # pragma: no cover raise HTTPInternalServerError('No dbsession found for layer "%s"' % layername) layer_table = layerinfos.get('table', None) attrinfos = layerinfos['attributes'][fieldname] attrinfos = {} if attrinfos is None else attrinfos table = attrinfos.get('table', layer_table) if table is None: # pragma: no cover raise HTTPInternalServerError( 'No config table found for layer "%s"' % layername) layertable = get_table(table, DBSession=dbsession) column = attrinfos['column_name'] \ if 'column_name' in attrinfos else fieldname attribute = getattr(layertable.columns, column) # For instance if `separator` is a ',' we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if 'separator' in attrinfos: separator = attrinfos['separator'] attribute = func.unnest( func.string_to_array(func.string_agg(attribute, separator), separator)) values = dbsession.query(distinct(attribute)).order_by(attribute).all() enum = { 'items': [{ 'label': value[0], 'value': value[0] } for value in values] } return enum
def get_profiles(): error, verification_token = get_token(request.headers) if error: return error query = request.args.get('query') degrees = request.args.get('degrees', '') affiliations = request.args.get('affiliations', '') page = int(request.args.get('page', 1)) start, end = pagination(page) verification_email_id = VerificationToken.query.filter( VerificationToken.token == verification_token.token).value( VerificationToken.email_id) queryset = ( matching_profiles(query, degrees, affiliations).order_by( # Is this the logged-in user's profile? If so, return it first (false) Profile.verification_email_id != verification_email_id, # Get the last word in the name. # Won't work with suffixes. func.split_part( Profile.name, ' ', func.array_length( func.string_to_array( func.regexp_replace( Profile.name, '(,|MD).*', ''), # Remove suffixes after comma and MD ' ', ), 1, # How many words in the name ), ), ).group_by(Profile.id)) return jsonify({ 'profileCount': queryset.count(), 'profiles': profiles_schema.dump(queryset[start:end]), })
def _enumerate_attribute_values(self, general_dbsession_name, layername, fieldname): if layername not in self.layers_enum_config: # pragma: no cover raise HTTPBadRequest('Unknown layer: %s' % layername) layerinfos = self.layers_enum_config[layername] if fieldname not in layerinfos['attributes']: # pragma: no cover raise HTTPBadRequest('Unknown attribute: %s' % fieldname) dbsession = DBSessions.get( layerinfos.get('dbsession', general_dbsession_name), None ) if dbsession is None: # pragma: no cover raise HTTPInternalServerError( 'No dbsession found for layer "%s"' % layername ) layer_table = layerinfos.get('table', None) attrinfos = layerinfos['attributes'][fieldname] attrinfos = {} if attrinfos is None else attrinfos table = attrinfos.get('table', layer_table) if table is None: # pragma: no cover raise HTTPInternalServerError( 'No config table found for layer "%s"' % layername ) layertable = get_table(table, session=dbsession) column = attrinfos['column_name'] \ if 'column_name' in attrinfos else fieldname attribute = getattr(layertable.columns, column) # For instance if `separator` is a ',' we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if 'separator' in attrinfos: separator = attrinfos['separator'] attribute = func.unnest(func.string_to_array( func.string_agg(attribute, separator), separator )) values = dbsession.query(distinct(attribute)).order_by(attribute).all() enum = { 'items': [{'label': value[0], 'value': value[0]} for value in values] } return enum
def _enumerate_attribute_values(self, general_dbsession_name, layername, fieldname): if layername not in self.layers_enum_config: # pragma: no cover raise HTTPBadRequest("Unknown layer: %s" % layername) layerinfos = self.layers_enum_config[layername] if fieldname not in layerinfos["attributes"]: # pragma: no cover raise HTTPBadRequest("Unknown attribute: %s" % fieldname) dbsession = DBSessions.get( layerinfos.get("dbsession", general_dbsession_name), None ) if dbsession is None: # pragma: no cover raise HTTPInternalServerError( "No dbsession found for layer '%s'" % layername ) layer_table = layerinfos.get("table", None) attrinfos = layerinfos["attributes"][fieldname] attrinfos = {} if attrinfos is None else attrinfos table = attrinfos.get("table", layer_table) if table is None: # pragma: no cover raise HTTPInternalServerError( "No config table found for layer '%s'" % layername ) layertable = get_table(table, session=dbsession) column = attrinfos["column_name"] \ if "column_name" in attrinfos else fieldname attribute = getattr(layertable.columns, column) # For instance if `separator` is a "," we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if "separator" in attrinfos: separator = attrinfos["separator"] attribute = func.unnest(func.string_to_array( func.string_agg(attribute, separator), separator )) values = dbsession.query(distinct(attribute)).order_by(attribute).all() enum = { "items": [{"label": value[0], "value": value[0]} for value in values] } return enum
def _enumerate_attribute_values(self, general_dbsession_name, layername, fieldname): if layername not in self.layers_enum_config: # pragma: no cover raise HTTPBadRequest("Unknown layer: %s" % layername) layerinfos = self.layers_enum_config[layername] if fieldname not in layerinfos["attributes"]: # pragma: no cover raise HTTPBadRequest("Unknown attribute: %s" % fieldname) dbsession = DBSessions.get( layerinfos.get("dbsession", general_dbsession_name), None ) if dbsession is None: # pragma: no cover raise HTTPInternalServerError( "No dbsession found for layer '%s'" % layername ) layer_table = layerinfos.get("table", None) attrinfos = layerinfos["attributes"][fieldname] attrinfos = {} if attrinfos is None else attrinfos table = attrinfos.get("table", layer_table) if table is None: # pragma: no cover raise HTTPInternalServerError( "No config table found for layer '%s'" % layername ) layertable = get_table(table, session=dbsession) column = attrinfos["column_name"] \ if "column_name" in attrinfos else fieldname attribute = getattr(layertable.columns, column) # For instance if `separator` is a "," we consider that the column contains a # comma separate list of values e.g.: "value1,value2". if "separator" in attrinfos: separator = attrinfos["separator"] attribute = func.unnest(func.string_to_array( func.string_agg(attribute, separator), separator )) values = dbsession.query(distinct(attribute)).order_by(attribute).all() enum = { "items": [{"label": value[0], "value": value[0]} for value in values] } return enum
def get_ordering(sorting): last_name_sorting = func.split_part( profile_class.name, " ", func.array_length( func.string_to_array(profile_class.name, " "), 1, # Length in the 1st dimension ), ) sort_options = { "starred": [ desc(text("profile_star_count")), desc(profile_class.date_updated), ], "last_name_alphabetical": [asc(last_name_sorting)], "last_name_reverse_alphabetical": [desc(last_name_sorting)], "date_updated": [desc(profile_class.date_updated)], } if sorting not in sort_options: raise InvalidPayloadError({"sorting": ["invalid"]}) return sort_options[sorting]
def bind_expression(self, value): return func.string_to_array(value, "\v")