def test_select_results(connection: sa.engine.Connection, query_object: QueryObjectDict, expected_results: list[dict]):
""" Typical test: real data, real query, real results """
# Models
Base = sacompat.declarative_base()
class Model(IdManyFieldsMixin, Base):
__tablename__ = 'a'
@property
@loads_attributes_readcode()
def abc(self):
return ' '.join((self.a, self.b, self.c))
@sa.ext.hybrid.hybrid_property
def abch(self):
# dict-evaluation is not used: instead, it's selected as an expression
raise NotImplementedError
@abch.expression
def abch(cls):
return cls.a + ' ' + cls.b + ' ' + cls.c
# Data
with created_tables(connection, Base):
# Insert some rows
insert(connection, Model,
id_manyfields('m', 1),
)
# Test
typical_test_query_results(connection, query_object, Model, expected_results)
def test_joined_sort(connection: sa.engine.Connection, query_object: QueryObjectDict, expected_query_lines: list[str], expected_results: list[dict]):
""" Typical test: JOINs, SQL and results """
# Models
Base = sacompat.declarative_base()
class User(IdManyFieldsMixin, Base):
__tablename__ = 'u'
articles = sa.orm.relationship('Article', back_populates='author')
class Article(IdManyFieldsMixin, Base):
__tablename__ = 'a'
user_id = sa.Column(sa.ForeignKey(User.id))
author = sa.orm.relationship(User, back_populates='articles')
# Data
with created_tables(connection, Base):
# Insert some rows
insert(connection, User,
id_manyfields('u', 1),
)
insert(connection, Article,
id_manyfields('a', 1, user_id=1),
id_manyfields('a', 2, user_id=1),
id_manyfields('a', 3, user_id=1),
)
# Test
typical_test_query_text_and_results(connection, query_object, User, expected_query_lines, expected_results)
def test_sort_results(connection: sa.engine.Connection, query_object: QueryObjectDict, expected_results: list[dict]):
""" Typical test: real data, real query, real results """
# Models
Base = sacompat.declarative_base()
class Model(IdManyFieldsMixin, Base):
__tablename__ = 'a'
# Data
with created_tables(connection, Base):
# Insert some rows
insert(connection, Model,
id_manyfields('m', 1),
id_manyfields('m', 2),
id_manyfields('m', 3),
)
# Test
typical_test_query_results(connection, query_object, Model, expected_results)
def test_joined_select(connection: sa.engine.Connection, model: str, query_object: QueryObjectDict, expected_query_lines: list[str], expected_results: list[dict]):
""" Typical test: JOINs, SQL and results """
# Models
Base = sacompat.declarative_base()
# One-to-Many, FK on remote side:
# User.articles: User -> Article (Article.user_id)
# User.comments: User -> Comment (Comment.user_id)
# Article.comments: Article -> Comment (Comment.article_id)
# Many-to-One, FK on local side:
# Comment.article: Comment -> Article (Comment.article_id)
# Comment.author: Comment -> User (Comment.user_id)
# Article.author: Article -> User (Article.user_id)
# Many-to-Many:
# Article.tags: Article -> (m2m) -> Tag
class User(IdManyFieldsMixin, Base):
__tablename__ = 'u'
articles = sa.orm.relationship('Article', back_populates='author')
comments = sa.orm.relationship('Comment', back_populates='author')
class Tag(IdManyFieldsMixin, Base):
__tablename__ = 't'
class Article(IdManyFieldsMixin, Base):
__tablename__ = 'a'
user_id = sa.Column(sa.ForeignKey(User.id))
author = sa.orm.relationship(User, back_populates='articles')
comments = sa.orm.relationship('Comment', back_populates='article')
tags = sa.orm.relationship(Tag, secondary=lambda: ArticleTagLink.__table__, order_by=Tag.a, lazy='selectin')
@property
@loads_attributes_readcode()
def abc(self):
return ' '.join((self.a, self.b, self.c))
class Comment(IdManyFieldsMixin, Base):
__tablename__ = 'c'
article_id = sa.Column(sa.ForeignKey(Article.id))
article = sa.orm.relationship(Article, back_populates='comments')
user_id = sa.Column(sa.ForeignKey(User.id))
author = sa.orm.relationship(User, back_populates='comments')
class ArticleTagLink(Base): # Many to Many
__tablename__ = 'at'
id = sa.Column(sa.Integer, primary_key=True, nullable=False)
article_id = sa.Column(Article.id.type, sa.ForeignKey(Article.id, ondelete='CASCADE'), nullable=False)
tag_id = sa.Column(Tag.id.type, sa.ForeignKey(Tag.id, ondelete='CASCADE'), nullable=False)
# Data
with created_tables(connection, Base):
# Insert some rows
insert(connection, User,
id_manyfields('u', 1),
id_manyfields('u', 2),
id_manyfields('u', 3),
)
insert(connection, Article,
# 2 articles from User(id=1)
id_manyfields('a', 1, user_id=1),
id_manyfields('a', 2, user_id=1),
# 1 article from User(id=2)
id_manyfields('a', 3, user_id=2),
# 1 article from User(id=3)
id_manyfields('a', 4, user_id=3),
# article with no user
# this is a potential stumbling block for conditions that fail to filter it out
id_manyfields('a', 5, user_id=None),
)
insert(connection, Comment,
# User(id=1), User(id=2), User(id=3) commented on Article(id=1)
id_manyfields('c', 1, user_id=1, article_id=1),
id_manyfields('c', 2, user_id=2, article_id=1),
id_manyfields('c', 3, user_id=3, article_id=1),
# User(id=1) commented on Article(id=2)
id_manyfields('c', 4, user_id=1, article_id=2),
# User(id=1) commented on Article(id=3)
id_manyfields('c', 5, user_id=1, article_id=3),
# comment with no user/article
# this is a potential stumbling block for conditions that fail to filter it out
id_manyfields('c', 6, user_id=None, article_id=None),
)
insert(connection, Tag,
id_manyfields('t', 1),
id_manyfields('t', 2),
id_manyfields('t', 3),
)
insert(connection, ArticleTagLink,
# Article id=1: 3 tags
dict(id=1, article_id=1, tag_id=1),
dict(id=2, article_id=1, tag_id=2),
dict(id=3, article_id=1, tag_id=3),
# Article id=2: one tag
dict(id=4, article_id=2, tag_id=1),
# Article id=3: one tag
dict(id=5, article_id=3, tag_id=1),
)
# Test
Model = locals()[model]
typical_test_query_text_and_results(connection, query_object, Model, expected_query_lines, expected_results)
def test_skiplimit_cursor_pagination(connection: sa.engine.Connection):
""" Test pagination with cursors """
def main():
# ### Test: wrong usage
# Test: cannot get a link before results are fetched
q = jessiql.Query(dict(limit=2), User)
# Not possible to generate links before results are fetched
with pytest.raises(RuntimeError):
q.page_links()
# Fetch results. Now possible.
q.fetchall(connection)
q.page_links() # no error
# ### Test: skip/limit pages
# Test: Page 0
# No prev page, have next page
q, res = load(select=['id'], sort=['a'], limit=2)
assert ids(res) == [1, 2]
assert decode_links(q.page_links()) == (None, dict(skip=2, limit=2))
# Test: next page
# Have both prev & next pages
q, res = load(select=['id'], sort=['a'], after=q.page_links().next)
assert ids(res) == [3, 4]
assert decode_links(q.page_links()) == (dict(skip=0, limit=2),
dict(skip=4, limit=2))
# Test: next page (last page)
q, res = load(select=['id'], sort=['a'], after=q.page_links().next)
assert ids(res) == [5]
assert decode_links(q.page_links()) == (dict(skip=2, limit=2), None)
# Test: prev page
q, res = load(select=['id'], sort=['a'], before=q.page_links().prev)
assert ids(res) == [3, 4]
assert decode_links(q.page_links()) == (dict(skip=0, limit=2),
dict(skip=4, limit=2))
# Test: prev page (first)
q, res = load(select=['id'], sort=['a'], before=q.page_links().prev)
assert ids(res) == [1, 2]
assert decode_links(q.page_links()) == (None, dict(skip=2, limit=2))
# ### Test: approaching the end
# Because this is the end, there should be no next page
# Case 1. Got no rows => No next page.
cursor = SkipCursorData(5, 2).encode()
q, res = load(select=['id'], sort=['a'], after=cursor)
assert ids(res) == []
assert q.page_links().next is None
# Case 2: Got one row, result set incomplete => No next page.
cursor = SkipCursorData(4, 2).encode()
q, res = load(select=['id'], sort=['a'], after=cursor)
assert ids(res) == [5]
assert q.page_links().next is None
# Case 3: Got two rows, but there's nothing beyond that => No next page.
cursor = SkipCursorData(3, 2).encode()
q, res = load(select=['id'], sort=['a'], after=cursor)
assert ids(res) == [4, 5]
assert q.page_links().next is None
# ### Test: keyset
# Test: first page
q, res = load(select=['id', 'a'], sort=['a', 'id'], limit=2)
assert ids(res) == [1, 2]
assert decode_links(q.page_links()) == (None,
dict(cols=['a', 'id'],
limit=2,
op='>',
val=['u-2-a', 2]))
# Test: next page
q, res = load(select=['id', 'a'],
sort=['a', 'id'],
after=q.page_links().next)
assert ids(res) == [3, 4]
assert decode_links(q.page_links()) == (dict(cols=['a', 'id'],
limit=2,
op='<',
val=['u-3-a', 3]),
dict(cols=['a', 'id'],
limit=2,
op='>',
val=['u-4-a', 4]))
# Test: next page
q, res = load(select=['id', 'a'],
sort=['a', 'id'],
after=q.page_links().next)
assert ids(res) == [5]
assert decode_links(q.page_links()) == (dict(cols=['a', 'id'],
limit=2,
op='<',
val=['u-5-a', 5]), None)
# Test: prev page
q, res = load(select=['id', 'a'],
sort=['a', 'id'],
before=q.page_links().prev)
# assert ids(res) == [3, 4] # TODO: backward navigation does not work yet
# assert decode_links(q.page_links()) == (dict(cols=['a', 'id'], limit=2, op='<', val=['u-3-a', 3]),
# dict(cols=['a', 'id'], limit=2, op='>', val=['u-4-a', 4]))
# ### Test: related pagination
q, res = load(select=['id'],
join={
'articles': {
'select': ['id'],
'skip': 1,
'limit': 1,
'sort': ['id+'],
}
},
filter={'id': 1})
assert res == [{
'id':
1,
'articles': [
# {'user_id': 1, 'id': 1},
{
'user_id': 1,
'id': 2
},
# {'user_id': 1, 'id': 3},
],
}]
# Models
Base = sacompat.declarative_base()
class User(IdManyFieldsMixin, Base):
__tablename__ = 'u'
articles = sa.orm.relationship('Article', back_populates='author')
class Article(IdManyFieldsMixin, Base):
__tablename__ = 'a'
user_id = sa.Column(sa.ForeignKey(User.id))
author = sa.orm.relationship(User, back_populates='articles')
# Helpers
def load(**query_object) -> tuple[jessiql.Query, list[dict]]:
""" Given a Query Object, load results, return (Query, result) """
q = jessiql.Query(query_object, User)
res = q.fetchall(connection)
return q, res
def ids(row_dicts: list[dict]) -> list[id]:
""" Convert a list of dicts to ids """
return [row['id'] for row in row_dicts]
# Data
with created_tables(connection, Base):
# Insert some rows
insert(connection, User,
*(id_manyfields('u', id) for id in range(1, 6)))
insert(
connection,
Article,
id_manyfields('a', 1, user_id=1),
id_manyfields('a', 2, user_id=1),
id_manyfields('a', 3, user_id=1),
)
# Test
main()