def y(cls):
s = database.Session.object_session(cls)
arrunnest = func.unnest(cls.indices)
xarr = (func.unnest(cls.indices) / cls.size).label('xarrind')
arrind = (arrunnest - xarr * cls.size).label('yarrind')
y = s.query(arrind).select_from(cls).subquery('yarr')
yagg = s.query(func.array_agg(y.c.yarrind))
return yagg.as_scalar()
def y(cls):
#arrind = func.unnest(cls.indices).label('arrind')
#x = arrind / cls.size
#y = func.array_agg(arrind - x*cls.size).label('y')
#return y
s = db.Session()
arrunnest = func.unnest(cls.indices)
xarr = (func.unnest(cls.indices) / cls.size).label('xarrind')
arrind = (arrunnest - xarr * cls.size).label('yarrind')
#n.arrind-(n.arrind/n.size)*n.size
y = s.query(arrind).select_from(cls).subquery('yarr')
yagg = s.query(func.array_agg(y.c.yarrind))
return yagg.as_scalar()
def get_all_countries():
query = (db.session.query(
func.unnest(Project.country).label("country")).distinct().order_by(
"country").all())
tags_dto = TagsDTO()
tags_dto.tags = [r[0] for r in query]
return tags_dto
def _get_most_played_with(id_: str, session):
p = func.unnest(Game.players).label('player')
players_in_common = []
result = session.query(
p,
func.count(Game.players).label('count')).filter(
Game.players.contains(cast(
[id_],
postgresql.ARRAY(String)))).group_by('player').order_by(
desc('count'))
result = result[1:4]
for p in result:
player = session.query(Player).filter(
Player.platformid == p[0]).first()
if player is None or player.platformname == "":
print("unknown player")
players_in_common.append(
PlayerInCommonStats(name="Unknown",
count=p[1],
id=p[0],
avatar=player.avatar))
else:
players_in_common.append(
PlayerInCommonStats(name=player.platformname,
count=p[1],
id=p[0],
avatar=player.avatar))
return players_in_common
def get_mapped_projects(user_id: int,
preferred_locale: str) -> UserMappedProjectsDTO:
""" Get all projects a user has mapped on """
from backend.models.postgis.task import Task
from backend.models.postgis.project import Project
query = db.session.query(func.unnest(
User.projects_mapped)).filter_by(id=user_id)
query_validated = (db.session.query(
Task.project_id.label("project_id"),
func.count(Task.validated_by).label("validated"),
).filter(Task.project_id.in_(query)).filter_by(
validated_by=user_id).group_by(Task.project_id,
Task.validated_by).subquery())
query_mapped = (db.session.query(
Task.project_id.label("project_id"),
func.count(Task.mapped_by).label("mapped"),
).filter(Task.project_id.in_(query)).filter_by(
mapped_by=user_id).group_by(Task.project_id,
Task.mapped_by).subquery())
query_union = (db.session.query(
func.coalesce(query_validated.c.project_id,
query_mapped.c.project_id).label("project_id"),
func.coalesce(query_validated.c.validated, 0).label("validated"),
func.coalesce(query_mapped.c.mapped, 0).label("mapped"),
).join(
query_mapped,
query_validated.c.project_id == query_mapped.c.project_id,
full=True,
).subquery())
results = (db.session.query(
Project.id,
Project.status,
Project.default_locale,
query_union.c.mapped,
query_union.c.validated,
functions.ST_AsGeoJSON(Project.centroid),
).filter(Project.id == query_union.c.project_id).order_by(
desc(Project.id)).all())
mapped_projects_dto = UserMappedProjectsDTO()
for row in results:
mapped_project = MappedProject()
mapped_project.project_id = row[0]
mapped_project.status = ProjectStatus(row[1]).name
mapped_project.tasks_mapped = row[3]
mapped_project.tasks_validated = row[4]
mapped_project.centroid = geojson.loads(row[5])
project_info = ProjectInfo.get_dto_for_locale(
row[0], preferred_locale, row[2])
mapped_project.name = project_info.name
mapped_projects_dto.mapped_projects.append(mapped_project)
return mapped_projects_dto
def _get_most_played_with(id_: str, session):
p = func.unnest(Game.players).label('player')
players_in_common = []
result = session.query(
p,
func.count(Game.players).label('count')).filter(
Game.players.contains(cast(
[id_],
postgresql.ARRAY(String)))).group_by('player').order_by(
desc('count')).subquery('t')
result = session.query(result, Player.platformname).join(
Player, Player.platformid == result.c.player).filter(
Player.platformid != id_).filter(
literal_column('count') > 1)[:3]
for p in result:
player = session.query(Player).filter(
Player.platformid == p[0]).first()
if player is None or player.platformname == "":
players_in_common.append(
PlayerInCommonStats(name=p[2],
count=p[1],
id=p[0],
avatar=player.avatar))
else:
players_in_common.append(
PlayerInCommonStats(name=player.platformname,
count=p[1],
id=p[0],
avatar=player.avatar))
return players_in_common
def test_unnest(engine, bigquery_dataset):
from sqlalchemy import select, func, String
from sqlalchemy_bigquery import ARRAY
conn = engine.connect()
metadata = MetaData()
table = Table(
f"{bigquery_dataset}.test_unnest",
metadata,
Column("objects", ARRAY(String)),
)
metadata.create_all(engine)
conn.execute(table.insert(),
[dict(objects=["a", "b", "c"]),
dict(objects=["x", "y"])])
query = select([func.unnest(table.c.objects).alias("foo_objects").column])
compiled = str(query.compile(engine))
assert " ".join(compiled.strip().split()) == (
f"SELECT `foo_objects`"
f" FROM"
f" `{bigquery_dataset}.test_unnest` `{bigquery_dataset}.test_unnest_1`,"
f" unnest(`{bigquery_dataset}.test_unnest_1`.`objects`) AS `foo_objects`"
)
assert sorted(r[0]
for r in conn.execute(query)) == ["a", "b", "c", "x", "y"]
def update_common_topic_tags(config_path: str) -> None:
"""Update the list of common topic tags for all groups."""
db_session = get_session_from_config(config_path)
all_groups = db_session.query(Group).all()
for group in all_groups:
# create a subquery for all tags from topics in that group - UNNEST() converts
# the arrays of tags into rows so that we can easily group and count, and
# created_time will be used to determine when a particular tag was last used
group_tags = (db_session.query(
func.unnest(Topic.tags).label("tag"),
Topic.created_time).filter(Topic.group == group).subquery())
# get the list of the most common tags, based on frequency and breaking ties
# with which was used most recently
common_tags = (db_session.query(
group_tags.columns["tag"],
func.count().label("frequency"),
func.max(group_tags.columns["created_time"]).label("last_used"),
).group_by("tag").order_by(
desc("frequency"),
desc("last_used")).limit(MAX_NUM_COMMON_TAGS).all())
group.common_topic_tags = [common_tag[0] for common_tag in common_tags]
db_session.add(group)
db_session.commit()
def __init__(self):
super().__init__()
self.db = Db()
self.session = self.db.get_session()
products = self.session.query(Product).all()
self.products = {
ProductService.build_url(product): product
for product in products
}
if not self.products:
return
self.start_urls = self.products.keys()
brands = self.session.query(Brand).filter(
Brand.id.in_([product.brand_id for product in products])).all()
self.brands = {brand.title: brand for brand in brands}
category_ids = self.session.query(func.unnest(Product.category_ids)) \
.filter(Product.id.in_([product.id for product in products])).distinct()
categories = self.session.query(Category) \
.filter(Category.id.in_(category_ids)).all()
self.categories = {
catalog_category.title: catalog_category
for catalog_category in categories
}
def x(cls):
#arrind = func.unnest(cls.indices).label('arrind')
#x = func.array_agg(arrind / cls.size).label('x')
s = db.Session()
arrind = (func.unnest(cls.indices) / cls.size).label('xarrind')
#x = s.query(arrind).select_from(cls).subquery('xarr')
#xagg = s.query(func.array_agg(x.c.xarrind))
return arrind
def test_unnest_and_struct_access_233(engine, bigquery_dataset, metadata):
# https://github.com/googleapis/python-bigquery-sqlalchemy/issues/233
from sqlalchemy import Table, select, Column, ARRAY, String, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy_bigquery import STRUCT
conn = engine.connect()
mock_table = Table(f"{bigquery_dataset}.Mock", metadata, Column("mock_id", String))
another_mock_table = Table(
f"{bigquery_dataset}.AnotherMock",
metadata,
Column("objects", ARRAY(STRUCT(object_id=String))),
)
metadata.create_all(engine)
conn.execute(
mock_table.insert(), dict(mock_id="x"), dict(mock_id="y"), dict(mock_id="z"),
)
conn.execute(
another_mock_table.insert(),
dict(objects=[dict(object_id="x"), dict(object_id="y"), dict(object_id="q")]),
)
subquery = select(
func.unnest(another_mock_table.c.objects).alias("another_mock_objects").column
).subquery()
join = mock_table.join(
subquery, subquery.c.another_mock_objects["object_id"] == mock_table.c.mock_id,
)
query = select(mock_table).select_from(join)
got = str(query.compile(engine))
want = (
f"SELECT `{bigquery_dataset}.Mock`.`mock_id` \n"
f"FROM `{bigquery_dataset}.Mock` "
f"JOIN ("
f"SELECT `another_mock_objects` \n"
f"FROM "
f"`{bigquery_dataset}.AnotherMock` `{bigquery_dataset}.AnotherMock_1`, "
f"unnest(`{bigquery_dataset}.AnotherMock_1`.`objects`)"
f" AS `another_mock_objects`"
f") AS `anon_1` "
f"ON "
f"(`anon_1`.`another_mock_objects`.object_id) = "
f"`{bigquery_dataset}.Mock`.`mock_id`"
)
assert got == want
Session = sessionmaker(bind=engine)
session = Session()
results = sorted(session.execute(query))
assert results == [("x",), ("y",)]
def _sip_query_option(cls, option, regex_filter=None):
attr = EndpointSIPOptionsView.get_option_value(option)
if regex_filter:
attr = func.unnest(
func.regexp_matches(
attr, bindparam('regexp', regex_filter, unique=True)))
return (select([attr]).where(
EndpointSIPOptionsView.root == cls.endpoint_sip_uuid).as_scalar())
def import_traits(self, amplicon_filter):
q = self._session.query(func.unnest(
OTUSampleOTU.traits)).distinct().group_by(OTUSampleOTU.traits)
q = apply_op_and_val_filter(OTUSampleOTU.amplicon_id, q,
amplicon_filter)
# log_query(q)
vals = self._q_all_cached('import_traits', q)
vals.sort(key=lambda v: v[0])
return vals
def facet_by(cls, q, field, filter_null=False, mapping={}):
if isinstance(field.property.columns[0].type, ARRAY):
field = func.unnest(field)
cnt = func.count(field)
q = q.from_self(field, cnt)
q = q.group_by(field)
q = q.order_by(cnt.desc())
return [{'id': v, 'label': mapping.get(v, v), 'count': c}
for v, c in q if v is not None]
def add_outcomes(
self,
feature_query,
):
# The events table holds all the events, not just conversion ones
relevant_events = self.bq_session.query(
self.events.c['time'].cast(DATE).label('date'),
self.events.c['type'].label('outcome'),
self.events.c['user_id'].label('user_id')).filter(
and_(
self.events.c['type'].in_(list(LABELS.keys())),
cast(self.events.c['time'], DATE) > cast(
self.aggregation_time, DATE),
cast(self.events.c['time'], DATE) <= cast(
self.aggregation_time +
timedelta(days=EVENT_LOOKAHEAD), DATE))).subquery()
# TODO: Remove deduplication, once the event table doesn't contain any
relevant_events_deduplicated = self.bq_session.query(
relevant_events.c['date'],
relevant_events.c['user_id'],
# This case when provides logic for dealing with multiple outcomes during the same time period
# an example is user_id 195379 during the 4/2020 where the user renews, but then cancels and gets
# a refund (the current pipeline provides both labels)
case(
[
# If there is at least one churn event, we identify the user as churned
(literal(self.negative_label()).in_(
func.unnest(
func.array_agg(relevant_events.c['outcome']))),
self.negative_label())
],
# In case of any number of any positive only events we consider the event as a renewal
else_=self.positive_label()).label('outcome')).group_by(
relevant_events.c['date'].label('date'),
relevant_events.c['user_id'].label('user_id')).subquery()
feature_query_w_outcome = self.bq_session.query(
*[
column.label(column.name) for column in feature_query.columns
if column.name not in ['user_id', 'date', 'outcome_date']
],
func.coalesce(
feature_query.c['user_id'],
relevant_events_deduplicated.c['user_id']).label('user_id'),
func.coalesce(feature_query.c['date'],
self.aggregation_time.date()).label('date'),
relevant_events_deduplicated.c['outcome'].label('outcome'),
func.coalesce(relevant_events_deduplicated.c['date'],
feature_query.c['outcome_date'].cast(DATE)).
label('outcome_date')).outerjoin(
relevant_events_deduplicated,
feature_query.c['user_id'] == relevant_events_deduplicated.
c['user_id']).subquery('feature_query_w_outcome')
return feature_query_w_outcome
def invalid_query(self, session, **kw):
vs_rid = select([
ValueSet.pk,
func.unnest(func.regexp_matches(
ValueSet.description, '\*\*(\d+)\*\*', 'g')).label('ref_id')]).alias()
return session.query(ValueSet)\
.filter(ValueSet.pk.in_(
session.query(vs_rid.c.pk)
.filter(~session.query(Ref).filter_by(id=vs_rid.c.ref_id).exists())))\
.order_by(ValueSet.id)
def test_plain_old_unnest(self, connection):
fn = func.unnest(postgresql.array(["one", "two", "three",
"four"])).column_valued()
stmt = select(fn)
eq_(
connection.execute(stmt).all(),
[("one", ), ("two", ), ("three", ), ("four", )],
)
def get(self):
sub = db.session.query(func.unnest(Post.tags).label('tag')).subquery()
q = select([sub.c.tag, func.count(sub.c.tag)
]).select_from(sub).group_by(sub.c.tag).order_by(
func.count(sub.c.tag).desc(), sub.c.tag)
tags = db.session.execute(q)
return [tag for tag, c in tags]
def search():
keywords = request.args.get('keywords')
sort = request.args.get('sort')
client = SphinxClient()
client.SetServer(SEARCH_HOST, SEARCH_PORT)
# Sorting mode
if sort == 'newest':
client.SetSortMode(SPH_SORT_ATTR_DESC, 'date_added')
elif sort == 'oldest':
client.SetSortMode(SPH_SORT_ATTR_ASC, 'date_added')
elif sort == 'highest_cost':
client.SetSortMode(SPH_SORT_ATTR_DESC, 'cost')
elif sort == 'lowest_cost':
client.SetSortMode(SPH_SORT_ATTR_ASC, 'cost')
# Filter by category
category = request.args.get('category')
try:
category = int(category)
except (ValueError, TypeError):
category = None
if category:
client.SetFilter('category', [category])
# Paging
try:
per_page = int(per_page)
except ValueError:
per_page = 20
page = request.args.get('page', default=1)
try:
page = int(page)
except ValueError:
page = 1
# Use our SphinxSearch query to construct our page
client.SetLimits(per_page*(page-1), per_page)
# Handle the query
q = client.Query(keywords)
if not q:
return 'Could not complete search', 400
ids = []
for res in q['matches']:
ids.append(res['id'])
if not ids:
return jsonify(data=[], num_pages=0), 200
# First construct the subquery
s_ids = db.session.query(func.unnest(array(ids)).label('id')).subquery('s_ids')
query = Postings.query.join(s_ids, Postings.id == s_ids.c.id)
# Return the JSON
return jsonify(data=[to_dict(r) for r in page.items], num_pages=page.pages), 200
def get_countries_contributed(user_id: int):
query = (
TaskHistory.query.with_entities(
func.unnest(Project.country).label("country"),
TaskHistory.action_text,
func.count(TaskHistory.action_text).label("count"),
)
.filter(TaskHistory.user_id == user_id)
.filter(
TaskHistory.action_text.in_(
[
TaskStatus.MAPPED.name,
TaskStatus.BADIMAGERY.name,
TaskStatus.VALIDATED.name,
]
)
)
.group_by("country", TaskHistory.action_text)
.outerjoin(Project, Project.id == TaskHistory.project_id)
.all()
)
countries = list(set([q.country for q in query]))
result = []
for country in countries:
values = [q for q in query if q.country == country]
# Filter element to sum mapped values.
mapped = sum(
[
v.count
for v in values
if v.action_text
in [TaskStatus.MAPPED.name, TaskStatus.BADIMAGERY.name]
]
)
validated = sum(
[v.count for v in values if v.action_text == TaskStatus.VALIDATED.name]
)
dto = UserCountryContributed(
dict(
name=country,
mapped=mapped,
validated=validated,
total=mapped + validated,
)
)
result.append(dto)
# Order by total
result = sorted(result, reverse=True, key=lambda i: i.total)
countries_dto = UserCountriesContributed()
countries_dto.countries_contributed = result
countries_dto.total = len(result)
return countries_dto
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
def all_tags(cls, org, user):
dashboards = cls.all(org, user.group_ids, user.id)
tag_column = func.unnest(cls.tags).label('tag')
usage_count = func.count(1).label('usage_count')
query = (db.session.query(
tag_column, usage_count).group_by(tag_column).filter(
Dashboard.id.in_(dashboards.options(
load_only('id')))).order_by(usage_count.desc()))
return query
def test_unnest_with_ordinality(self, connection):
array_val = postgresql.array(
[postgresql.array([14, 41, 7]),
postgresql.array([54, 9, 49])])
stmt = select("*").select_from(
func.unnest(array_val).table_valued(
"elts", with_ordinality="num").render_derived().alias("t"))
eq_(
connection.execute(stmt).all(),
[(14, 1), (41, 2), (7, 3), (54, 4), (9, 5), (49, 6)],
)
def get_chars():
session = db.session()
query = select([
func.unnest(Works.characters).label('chara'),
func.count().label('value')
]).select_from(Works).group_by('chara').order_by(desc('value'))
charas = session.execute(query).fetchall()
data = list(map(lambda x: {'character': x[0], 'value': x[1]}, charas))
with open('./data/haikyuu/chars.json', 'w') as outfile:
json.dump(data, outfile)
return jsonify(data)
def all_tags(cls, user, include_drafts=False):
queries = cls.all_queries(group_ids=user.group_ids,
user_id=user.id,
include_drafts=include_drafts)
tag_column = func.unnest(cls.tags).label("tag")
usage_count = func.count(1).label("usage_count")
query = (db.session.query(
tag_column, usage_count).group_by(tag_column).filter(
Query.id.in_(queries.options(load_only("id")))).order_by(
usage_count.desc()))
return query
def facet_array(cls, field, start_collection, start_entity_id=None,
end_collection_id=[], labels=[], types=[]):
"""Facet over an array-typed column."""
# SELECT unnest(labels), COUNT(unnest(labels))
# FROM path GROUP BY unnest(labels);
field = func.unnest(field)
field_cnt = func.count(field)
q = db.session.query(field, field_cnt)
q = cls.filters(q, start_collection, start_entity_id=start_entity_id,
end_collection_id=end_collection_id, types=types)
q = q.group_by(field)
q = q.order_by(field_cnt.desc())
return [{'value': v, 'count': c} for v, c in q]
def add_outcomes(
feature_query,
start_time: datetime,
positive_event_lookahead: int = 1,
):
# The events table holds all the events, not just conversion ones
relevant_events = bq_session.query(
events.c['time'].cast(DATE).label('date'),
events.c['type'].label('outcome'),
events.c['user_id'].label('user_id')).filter(
events.c['type'].in_(list(LABELS.keys())),
cast(events.c['time'], DATE) > cast(start_time, DATE),
cast(events.c['time'], DATE) <= cast(
start_time + timedelta(days=positive_event_lookahead),
DATE)).subquery()
# TODO: Remove deduplication, once the event table doesn't contain any
relevant_events_deduplicated = bq_session.query(
relevant_events.c['date'],
relevant_events.c['user_id'],
# This case when provides logic for dealing with multiple outcomes during the same time period
# an example is user_id 195379 during the 4/2020 where the user renews, but then cancels and gets
# a refund (the current pipeline provides both labels)
case(
[
# If there is at least one churn event, we identify the user as churned
(literal(negative_label()).in_(
func.unnest(func.array_agg(
relevant_events.c['outcome']))), negative_label())
],
# In case of any number of any positive only events we consider the event as a renewal
else_=positive_labels()).label('outcome')).group_by(
relevant_events.c['date'].label('date'),
relevant_events.c['user_id'].label('user_id')).subquery()
feature_query_w_outcome = bq_session.query(
feature_query,
relevant_events_deduplicated.c['outcome'].label('outcome'),
relevant_events_deduplicated.c['date'].label('outcome_date')
).outerjoin(
relevant_events_deduplicated,
and_(
feature_query.c['user_id'] ==
relevant_events_deduplicated.c['user_id'],
feature_query.c['date'] >= func.date_sub(
relevant_events_deduplicated.c['date'],
text(f'interval {positive_event_lookahead} day')),
feature_query.c['date'] <= relevant_events_deduplicated.c['date'])
).subquery('feature_query_w_outcome')
return feature_query_w_outcome
def all_tags(cls, org, user):
dashboards = cls.all(org, user.group_ids, user.id)
tag_column = func.unnest(cls.tags).label('tag')
usage_count = func.count(1).label('usage_count')
query = (
db.session
.query(tag_column, usage_count)
.group_by(tag_column)
.filter(Dashboard.id.in_(dashboards.options(load_only('id'))))
.order_by(usage_count.desc())
)
return query
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 enabled_schemas(clz) -> Iterable[str]:
"""Returns all schemas that have at least one ACL defined on them."""
schemas = set()
if db.engine.dialect.name == 'postgresql':
# postgresql has array field, so return it from the array
for acl_schemas in db.session.query(func.unnest(
ACL.schemas)).distinct().all():
schemas.update(acl_schemas)
else:
# otherwise iterate all the ACLs, let's hope there is not too many of them
for acl in ACL.query.all():
for schema in acl.schemas:
schemas.add(schema)
return schemas
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 __init__(self, meta, name, osmdata, subset=None):
self.data = Table(name, meta, Column('parent', BigInteger, index=True),
Column('child', BigInteger, index=True),
Column('depth', Integer))
if subset is None:
m = osmdata.member.data.alias()
self.subset = select([func.unnest(array([m.c.relation_id,
m.c.member_id])).label('id')],
distinct=True)\
.where(m.c.member_type == 'R')
else:
self.subset = subset
self.osmdata = osmdata
def __init__(self, meta, name, osmdata, subset=None):
self.data = Table(name, meta,
Column('parent', BigInteger, index=True),
Column('child', BigInteger, index=True),
Column('depth', Integer)
)
if subset is None:
m = osmdata.member.data.alias()
self.subset = select([func.unnest(array([m.c.relation_id,
m.c.member_id])).label('id')],
distinct=True)\
.where(m.c.member_type == 'R')
else:
self.subset = subset
self.osmdata = osmdata
def all_tags(cls, user, include_drafts=False):
queries = cls.all_queries(
group_ids=user.group_ids,
user_id=user.id,
include_drafts=include_drafts,
)
tag_column = func.unnest(cls.tags).label('tag')
usage_count = func.count(1).label('usage_count')
query = (
db.session
.query(tag_column, usage_count)
.group_by(tag_column)
.filter(Query.id.in_(queries.options(load_only('id'))))
.order_by(usage_count.desc())
)
return query
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 glottocode(name, conn, codes=None):
letters = slug(name)[:4].ljust(4, 'a')
active = select([cast(func.substring(Languoid.id, 5), Integer).label('number')])\
.where(Languoid.id.startswith(letters))
legacy = select([cast(func.substring(LegacyCode.id, 5), Integer).label('number')])\
.where(LegacyCode.id.startswith(letters))
if not codes:
known = union_all(active, legacy)
else:
dirty = select([cast(func.substring(literal_column('dirty'), 5), Integer).label('number')])\
.select_from(func.unnest(list(codes)).alias('dirty'))\
.where(literal_column('dirty').startswith(letters))
known = union_all(active, legacy, dirty)
number = conn.execute(select([func.coalesce(func.max(literal_column('number') + 1), 1234)])\
.select_from(known.alias())).scalar()
number = str(number)
assert len(number) == 4
res = letters + number
assert GLOTTOCODE_PATTERN.match(res)
if codes is not None:
codes[res] = True
return res
def chat_list(request):
current_page = int(request.GET.get("page", 1))
if request.matched_route.name.startswith("chat_list_unanswered"):
current_status = "unanswered"
elif request.matched_route.name.startswith("chat_list_ongoing"):
current_status = "ongoing"
elif request.matched_route.name.startswith("chat_list_ended"):
current_status = "ended"
else:
current_status = None
if request.matched_route.name.startswith("chat_list_label"):
current_label = request.matchdict["label"].lower().strip().replace(" ", "_")
if current_label != request.matchdict["label"]:
raise HTTPFound(request.route_path("chat_list_label", label=current_label))
else:
current_label = None
chats = (
Session.query(ChatUser, Chat, Message)
.join(Chat)
.outerjoin(
Message,
Message.id == Session.query(func.min(Message.id)).filter(Message.chat_id == Chat.id).correlate(Chat),
)
.filter(ChatUser.user_id == request.user.id)
)
chat_count = Session.query(func.count("*")).select_from(ChatUser).filter(ChatUser.user_id == request.user.id)
if current_status == "unanswered":
chats = chats.filter(and_(Chat.last_user_id is not None, Chat.last_user_id != request.user.id))
chat_count = chat_count.join(Chat).filter(
and_(Chat.last_user_id is not None, Chat.last_user_id != request.user.id)
)
elif current_status is not None:
chats = chats.filter(Chat.status == current_status)
chat_count = chat_count.join(Chat).filter(Chat.status == current_status)
if current_label is not None:
label_array = cast([current_label], ARRAY(Unicode(500)))
chats = chats.filter(ChatUser.labels.contains(label_array))
chat_count = chat_count.filter(ChatUser.labels.contains(label_array))
chats = chats.order_by(Chat.updated.desc()).limit(25).offset((current_page - 1) * 25).all()
# 404 on empty pages, unless it's the first page.
if current_page != 1 and len(chats) == 0:
raise HTTPNotFound
chat_count = chat_count.scalar()
if request.matchdict.get("fmt") == "json":
return render_to_response(
"json",
{
"chats": [
{"chat_user": chat_user, "chat": chat, "prompt": prompt} for chat_user, chat, prompt in chats
],
"chat_count": chat_count,
},
request=request,
)
paginator = paginate.Page(
[],
page=current_page,
items_per_page=25,
item_count=chat_count,
url=paginate.PageURL(
request.route_path(request.matched_route.name, label=current_label), {"page": current_page}
),
)
labels = (
Session.query(func.unnest(ChatUser.labels), func.count("*"))
.filter(ChatUser.user_id == request.user.id)
.group_by(func.unnest(ChatUser.labels))
.order_by(func.count("*").desc(), func.unnest(ChatUser.labels).asc())
.all()
)
template = "layout2/chat_list.mako" if request.user.layout_version == 2 else "chat_list.mako"
return render_to_response(
template,
{
"chats": chats,
"paginator": paginator,
"labels": labels,
"current_status": current_status,
"current_label": current_label,
"symbols": symbols,
},
request=request,
)
from sqlalchemy import Column, func, Integer, select, String, Unicode
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import backref, relationship
from sqlalchemy.sql.expression import cast
from pokr.database import Base
from .person import Person
class School(Base):
__tablename__ = 'school'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(40), index=True)
person_school = select([func.unnest(Person.education_id).label('school_id'),
Person.id.label('person_id')]).alias()
School.alumni = relationship("Person",
secondary=person_school,
primaryjoin=cast(School.id, String) == person_school.c.school_id,
secondaryjoin=person_school.c.person_id == Person.id,
viewonly=True,
backref='schools')
@property
def representative_people(self):
return [cosponsor
for cosponsor in self.cosponsors
if cosponsor.name in self.sponsor]
def _to_dict_light(self):
d = self._columns_to_dict()
d['status'] = self.status
# TODO: add relation data
return d
bill_and_status = select([func.row_number().over().label('status_order'),
func.unnest(Bill.status_ids).label('bill_status_id'),
Bill.id.label('bill_id')]).alias()
Bill.statuses = relationship("BillStatus",
secondary=bill_and_status,
primaryjoin=Bill.id == bill_and_status.c.bill_id,
secondaryjoin=bill_and_status.c.bill_status_id == BillStatus.id,
order_by=bill_and_status.c.status_order,
viewonly=True,
backref='bills')
def assembly_id_by_bill_id(bill_id):
return int(bill_id.lstrip('Z')[:2])
def sequence_pdf(sequence_id):
"""PDF view of a sequence and its seances, etapes..."""
seq = Sequence.query.get_or_404(sequence_id)
materiel_pe = (db.session.query(
func.unnest(Etape.materiel_pe).label('materiel'))
.select_from(Sequence)
.join(Seance)
.join(Etape)
.filter(Sequence.id == seq.id)
.distinct()
.all())
materiel_eleve = (db.session.query(
func.unnest(Etape.materiel_eleve).label('materiel'))
.select_from(Sequence)
.join(Seance)
.join(Etape)
.filter(Sequence.id == seq.id)
.distinct()
.all())
materiel_pe = set([m.materiel for m in materiel_pe] + seq.materiel_pe)
materiel_eleve = set([m.materiel for m in materiel_eleve] +
seq.materiel_eleve)
html = render_template('print/sequence.jinja2', sequence=seq,
materiel_pe=materiel_pe,
materiel_eleve=materiel_eleve)
seq_page = StringIO()
HTML(string=html,
base_url=current_app.static_folder).write_pdf(target=seq_page)
html = render_template('print/seances_summary.jinja2', sequence=seq)
seance_page = StringIO()
HTML(string=html,
base_url=current_app.static_folder).write_pdf(target=seance_page)
seances_docs = []
for seance in seq.seances:
if seance.etapes:
seance_doc = StringIO()
seances_docs.append(seance_doc)
css = CSS(string=render_template('print/seances.css.jinja2',
title=seance.title, idx=seance.ordinal))
html = render_template('print/seances.jinja2', seance=seance)
doc_html = HTML(string=html, base_url=current_app.static_folder)
doc_html.write_pdf(target=seance_doc,
stylesheets=[css])
out = PdfFileWriter()
input = PdfFileReader(seq_page)
out.addPage(input.getPage(0))
input = PdfFileReader(seance_page)
for page in input.pages:
out.addPage(page)
for seance in seances_docs:
input = PdfFileReader(seance)
for page in input.pages:
out.addPage(page)
outstream = StringIO()
out.write(outstream)
response = make_response(outstream.getvalue())
response.headers['Content-Type'] = 'application/pdf'
response.headers['Content-Disposition'] = \
"attachment; filename=%s.pdf" % (seq.title.encode('utf8')
.replace(' ', '_'))
return response
def chat_list(request):
try:
current_page = int(request.GET.get("page", 1))
except ValueError:
raise HTTPNotFound
if "status" in request.matchdict:
current_status = request.matchdict["status"]
else:
current_status = None
if "label" in request.matchdict:
current_label = request.matchdict["label"].lower().strip().replace(" ", "_")
if current_label != request.matchdict["label"]:
raise HTTPFound(request.route_path("chat_list_label", label=current_label))
else:
current_label = None
db = request.find_service(name="db")
chats = db.query(ChatUser, Chat, Message).select_from(ChatUser).join(Chat).outerjoin(
Message,
Message.id == db.query(
func.min(Message.id),
).filter(
Message.chat_id == Chat.id,
).correlate(Chat),
).filter(
ChatUser.user_id == request.user.id,
ChatUser.status == ChatUserStatus.active,
)
chat_count = db.query(func.count('*')).select_from(ChatUser).filter(
ChatUser.user_id == request.user.id,
ChatUser.status == ChatUserStatus.active,
)
if current_status == "unanswered":
chats = chats.filter(and_(
Chat.last_user_id != None,
Chat.last_user_id != request.user.id,
))
chat_count = chat_count.join(Chat).filter(and_(
Chat.last_user_id != None,
Chat.last_user_id != request.user.id,
))
elif current_status is not None:
chats = chats.filter(Chat.status == current_status)
chat_count = chat_count.join(Chat).filter(Chat.status == current_status)
if current_label is not None:
label_array = cast([current_label], ARRAY(Unicode(500)))
chats = chats.filter(ChatUser.labels.contains(label_array))
chat_count = chat_count.filter(ChatUser.labels.contains(label_array))
chats = (
chats.options(joinedload(Chat.request))
.order_by(Chat.updated.desc())
.limit(25).offset((current_page-1)*25).all()
)
# 404 on empty pages, unless it's the first page.
if current_page != 1 and len(chats) == 0:
raise HTTPNotFound
chat_count = chat_count.scalar()
if request.matched_route.name.endswith("_ext"):
return {
"chats": [{
"chat_user": chat_user,
"chat": chat,
"prompt": prompt,
} for chat_user, chat, prompt in chats],
"chat_count": chat_count,
}
labels = (
db.query(func.unnest(ChatUser.labels), func.count("*"))
.filter(and_(
ChatUser.user_id == request.user.id,
ChatUser.status == ChatUserStatus.active,
))
.group_by(func.unnest(ChatUser.labels))
.order_by(func.count("*").desc(), func.unnest(ChatUser.labels).asc()).all()
)
template = "layout2/chat_list.mako" if request.user.layout_version == 2 else "chat_list.mako"
return render_to_response(template, {
"chats": chats,
"chat_count": chat_count,
"current_page": current_page,
"labels": labels,
"current_status": current_status,
"current_label": current_label,
}, request=request)
def argoQuery(dbcon, geoWKT=None, tspan=None, withinDmeter=None, tsort=None):
tbl = dbcon.getTable('argo2', 'oceanobs')
#first create a subquery to quickly discard argo profiles
subqry = select([tbl])
if tspan:
subqry = subqry.where(
func.overlaps(tbl.c.tstart, tbl.c.tend, tspan[0], tspan[1]))
# Apply initial geospatial constraints
if geoWKT:
if withinDmeter:
#only base initial constraints ont he bounding box
subqry = subqry.where(
func.ST_DWithin(
literal_column('ST_Envelope(geom::geometry)::geography'),
func.ST_GeogFromText(geoWKT), withinDmeter))
else:
subqry = subqry.where(
func.ST_Intersects(literal_column('geom::geometry'),
func.ST_GeomFromText(geoWKT, 4326)))
#we need to assign an alias to this subquery in order to work with it
subqry = subqry.alias("ar")
#expand the arrays and points int he subquery
qry = select([
subqry.c.wmoid, subqry.c.uri, subqry.c.datacenter,
func.unnest(subqry.c.mode).label('mode'),
func.unnest(subqry.c.ascend).label('ascend'),
func.unnest(subqry.c.tlocation).label('tlocation'),
func.unnest(subqry.c.cycle).label('cycle'),
func.unnest(subqry.c.iprof).label('iprof'),
ST_Dump(literal_column("ar.geom::geometry")).geom.label('geom')
])
#additional spatial constraints
finalqry = qry
qry = qry.alias("arex")
if tspan:
finalqry = select([qry]).where(
between(qry.c.tlocation, tspan[0], tspan[1]))
if geoWKT:
if withinDmeter:
#only base initial constraints ont he bounding box
finalqry = finalqry.where(
func.ST_DWithin(qry.c.geom, func.ST_GeogFromText(geoWKT),
withinDmeter))
else:
finalqry = finalqry.where(
func.ST_Within(literal_column("arex.geom"),
func.ST_GeomFromText(geoWKT, 4326)))
if tsort:
finalqry = finalqry.order_by(qry.c.tlocation)
return dbcon.dbeng.execute(finalqry)
