def deduplicate_items(q: sqla.sql.Select, ) -> sqla.sql.Select:
q = q.group_by(
orm_items.Item.Title,
orm_items.Item.Published,
)
q = q.having(orm_feeds.Feed.Title == sqla.func.min(orm_feeds.Feed.Title), )
return q
def apply_to_statement(self, query: QueryObject, target_Model: SAModelOrAlias, stmt: sa.sql.Select) -> sa.sql.Select:
# Prepare the filter expression
cursor = self.cursor_value
if cursor is None:
filter_expression = True
limit = self.limit
else:
# Make sure the columns are still the same
if set(cursor.cols) != query.sort.names:
raise exc.QueryObjectError('You cannot adjust "sort" fields while using cursor-based pagination.')
# Filter
op = {'>': operator.gt, '<': operator.lt}[cursor.op]
filter_expression = op(
sa.tuple_(*(
resolve_column_by_name(field.name, target_Model, where='skip')
for field in query.sort.fields
)),
cursor.val
)
limit = cursor.limit
if limit is None:
return stmt
# Paginate
# We will always load one more row to check if there's a next page
if SA_14:
return stmt.filter(filter_expression).limit(limit + 1)
else:
return stmt.where(filter_expression).limit(limit + 1)
def add_order_by(select_languoid: sa.sql.Select,
*, order_by: str, column_for_path_order) -> sa.sql.Select:
if order_by in (True, None, 'id'):
return select_languoid.order_by(Languoid.id)
elif order_by == 'path':
return select_languoid.order_by(column_for_path_order)
elif order_by is False: # pragma: no cover
return select_languoid
raise ValueError(f'order_by={order_by!r} not implemented') # pragma: no cover
def _apply_simple_skiplimit_pagination(self, stmt: sa.sql.Select):
""" Pagination for the SKIP/LIMIT mode: add SKIP/LIMIT clauses """
if self.skip:
stmt = stmt.offset(self.skip)
if self.limit:
stmt = stmt.limit(self.limit)
# Done
return stmt
def add_columns(
stmt: sa.sql.Select, columns: abc.Iterable[Union[sa.Column,
sa.sql.ColumnElement]]
) -> sa.sql.Select:
""" Add columns to an SQL Select statement """
if SA_13:
for col in columns:
stmt.append_column(col)
else:
stmt = stmt.add_columns(*columns)
return stmt
def filter_dates(
q: sqla.sql.Select,
start: datetime.datetime = None,
finish: datetime.datetime = None,
) -> sqla.sql.Select:
if start:
q = q.where(orm_items.Item.Published >= start, )
if finish:
q = q.where(orm_items.Item.Published < finish, )
return q
def filter_like(
q: sqla.sql.Select,
score: schema_items.Like,
user: orm_users.User,
) -> sqla.sql.Select:
item_likes = orm_items.Item.likes.and_(
orm_items.Like.UserID == user.UserID, )
q = q.join(item_likes)
q = q.where(orm_items.Like.Score == score.name)
return q
def security(q: Query, stmt: sa.sql.Select) -> sa.sql.Select:
""" Security: make sure that the user can only access their own data """
ALLOWED_USER_ID = 1
path = q.load_path
if path == (Article, ):
return stmt.where(q.Model.user_id == ALLOWED_USER_ID)
elif path == (Article, 'author', User):
return stmt.where(q.Model.id == ALLOWED_USER_ID)
elif path == (Article, 'comments', Comment):
return stmt.where(q.Model.user_id == ALLOWED_USER_ID)
else:
raise NotImplementedError
def apply_to_statement(self, stmt: sa.sql.Select) -> sa.sql.Select:
""" Modify the Select statement: add the WHERE clause """
# Compile the conditions
conditions = (self._compile_condition(condition)
for condition in self.query.filter.conditions)
# Add the WHERE clause
if SA_13:
stmt = stmt.where(sa.and_(*conditions))
else:
stmt = stmt.filter(*conditions)
# Done
return stmt
def add_model_columns(select_languoid: sa.sql.Select, model,
*, add_outerjoin=None, label: str = '{name}',
ignore: str = 'id') -> sa.sql.Select:
columns = model.__table__.columns
if ignore:
ignore_suffix = f'_{ignore}'
columns = [c for c in columns
if c.name != ignore and not c.name.endswith(ignore_suffix)]
columns = [c.label(label.format(name=c.name)) for c in columns]
select_languoid = select_languoid.add_columns(*columns)
if add_outerjoin is not None:
select_languoid = select_languoid.outerjoin(add_outerjoin)
return select_languoid
def apply_to_statement(self, stmt: sa.sql.Select) -> sa.sql.Select:
""" Modify the Select statement: add ORDER BY clause """
# Sort fields
stmt = stmt.order_by(*self.compile_columns())
# Done
return stmt
def filter_magic(
q: sqla.sql.Select,
user: orm_users.User,
unscored: bool = True,
) -> sqla.sql.Select:
item_magic = orm_items.Item.magic.and_(
orm_items.Magic.UserID == user.UserID, )
q = q.join(item_magic, isouter=True)
q_where = orm_items.Item.magic.any(orm_items.Magic.UserID == user.UserID)
if unscored:
q_where = ~q_where
q = q.where(q_where)
return q
def filter_tags(
q: sqla.sql.Select,
tags: typing.List[orm_feeds.Tag],
user: orm_users.User,
) -> sqla.sql.Select:
feed_tags = orm_feeds.Feed.tags.and_(
orm_feeds.Tag.UserID == user.UserID,
)
q = q.join(feed_tags)
q = q.where(
orm_feeds.Tag.TagID.in_(
[tag_it.TagID for tag_it in tags]
)
)
return q
def filter_languages(
q: sqla.sql.Select,
langs: typing.List[schema_feeds.Language],
) -> sqla.sql.Select:
langs_name = [e.name for e in langs]
q = q.where(orm_feeds.Feed.Language.in_(langs_name))
return q
def order_magic(
q: sqla.sql.Select,
user: orm_users.User,
desc: bool = True,
) -> sqla.sql.Select:
item_magic = orm_items.Item.magic.and_(
orm_items.Magic.UserID == user.UserID,
)
q = q.join(item_magic, isouter=True)
magic_score = orm_items.Magic.Score
if desc:
magic_score = sqla.desc(magic_score)
q = q.order_by(magic_score)
return q
def add_classification_comment(select_languoid: sa.sql.Select, kind: str,
*, label: str,
bib_suffix: str = '_cr') -> sa.sql.Select:
comment = aliased(ClassificationComment, name=f'cc_{kind}')
label = label.format(kind=kind)
return (select_languoid.add_columns(comment.comment.label(label))
.outerjoin(comment, sa.and_(comment.kind == kind,
comment.languoid_id == Languoid.id)))
def load_tags(
q: sqla.sql.Select,
user: orm_users.User,
tags_joined: bool = False,
) -> sqla.sql.Select:
load_tags = load_tags_option(user, tags_joined)
q = q.options(load_tags)
return q
def filter_display(
q: sqla.sql.Select,
user: orm_users.User,
) -> sqla.sql.Select:
item_feed = orm_items.Item.feed
q = q.join(item_feed)
q = feeds_filter.filter_display(q, user)
return q
def filter_display(
q: sqla.sql.Select,
user: orm_users.User,
) -> sqla.sql.Select:
feed_users = orm_feeds.Feed.users.and_(
orm_users.User.UserID == user.UserID,
)
q = q.join(feed_users)
return q
def add_identifier(select_languoid: sa.sql.Select, site_name: str,
*, label: str) -> sa.sql.Select:
identifier = aliased(Identifier, name=f'ident_{site_name}')
site = aliased(IdentifierSite, name=f'ident_{site_name}_site')
label = label.format(site_name=site_name)
return (select_languoid.add_columns(identifier.identifier.label(label))
.outerjoin(sa.join(identifier, site, identifier.site_id == site.id),
sa.and_(site.name == site_name,
identifier.languoid_id == Languoid.id)))
def filter_lang(
q: sqla.sql.Select,
lang: schema_feeds.Language,
) -> sqla.sql.Select:
item_feed = orm_items.Item.feed
q = q.join(item_feed)
q = feeds_filter.filter_languages(q, [lang])
return q
def order_date(
q: sqla.sql.Select,
desc: bool = True,
) -> sqla.sql.Select:
item_published = orm_items.Item.Published
if desc:
item_published = sqla.desc(item_published)
q = q.order_by(item_published)
return q
def add_endangermentsource(select_languoid: sa.sql.Select,
*, label: str,
bib_suffix: str = '_e') -> sa.sql.Select:
bibfile = aliased(Bibfile, name=f'bibfile{bib_suffix}')
bibitem = aliased(Bibitem, name=f'bibitem{bib_suffix}')
endangermentsource = (EndangermentSource.printf(bibfile, bibitem)
.label(label))
return (select_languoid.add_columns(endangermentsource)
.outerjoin(sa.join(Endangerment, EndangermentSource))
.outerjoin(sa.join(bibitem, bibfile)))
def join_records(
select: sqlalchemy.sql.Select,
location_table: sqlalchemy.schema.Table
) -> sqlalchemy.sql.FromClause:
# mypy needs to be sure
assert isinstance(records_table, ByNameOpaqueTableStorage)
return select.select_from(
records_table._table.join(
location_table,
onclause=records_table._table.columns.dataset_id ==
location_table.columns.dataset_id,
)).where(location_table.columns.datastore_name ==
self.datastoreName)
def load_feed(
q: sqla.sql.Select,
feed_joined: bool = False,
) -> sqla.sql.Select:
item_feed = orm_items.Item.feed
if feed_joined:
load_feed = sqla.orm.contains_eager(item_feed)
else:
load_feed = sqla.orm.selectinload(item_feed)
q = q.options(load_feed)
return q
def load_like(
q: sqla.sql.Select,
user: orm_users.User,
like_joined: bool = False,
) -> sqla.sql.Select:
item_likes = orm_items.Item.likes.and_(
orm_items.Like.UserID == user.UserID,
)
if like_joined:
load_like = sqla.orm.contains_eager(item_likes)
else:
load_like = sqla.orm.selectinload(item_likes)
q = q.options(load_like)
return q
def load_magic(
q: sqla.sql.Select,
user: orm_users.User,
magic_joined: bool = False,
) -> sqla.sql.Select:
item_magic = orm_items.Item.magic.and_(
orm_items.Magic.UserID == user.UserID,
)
if magic_joined:
load_magic = sqla.orm.contains_eager(item_magic)
else:
load_magic = sqla.orm.selectinload(item_magic)
q = q.options(load_magic)
return q
def add_classification_refs(select_languoid: sa.sql.Select, kind: str,
*, label: str,
bib_suffix: str = '_cr') -> sa.sql.Select:
ref = aliased(ClassificationRef, name=f'cr_{kind}')
bibfile = aliased(Bibfile, name=f'bibfile{bib_suffix}_{kind}')
bibitem = aliased(Bibitem, name=f'bibitem{bib_suffix}_{kind}')
label = label.format(kind=kind)
ref = (select(ref.printf(bibfile, bibitem))
.select_from(ref)
.filter_by(languoid_id=Languoid.id)
.correlate(Languoid)
.filter_by(kind=kind)
.join(ref.bibitem.of_type(bibitem))
.join(bibitem.bibfile.of_type(bibfile))
.order_by(ref.ord)
.alias(f'lang_cref_{kind}'))
refs = select(group_concat(ref.c.printf).label(label)).label(label)
return select_languoid.add_columns(refs)
def order_title(q: sqla.sql.Select, ) -> sqla.sql.Select:
q = q.order_by(sqla.collate(orm_feeds.Feed.Title, "NOCASE"), )
return q
def apply_to_statement(self, query: QueryObject, target_Model: SAModelOrAlias, stmt: sa.sql.Select) -> sa.sql.Select:
# We will always load one more row to check if there's a next page
skip = self.cursor_value.skip if self.cursor_value else 0
limit = self.limit + 1 if self.limit is not None else None
return stmt.offset(skip).limit(limit)
