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