1import pytest 2import sqlalchemy as sa 3 4from sqlalchemy_utils.functions.sort_query import make_order_by_deterministic 5 6from .. import assert_contains 7 8 9@pytest.fixture 10def Article(Base): 11 class Article(Base): 12 __tablename__ = 'article' 13 id = sa.Column(sa.Integer, primary_key=True) 14 author_id = sa.Column(sa.Integer, sa.ForeignKey('user.id')) 15 author = sa.orm.relationship('User') 16 return Article 17 18 19@pytest.fixture 20def User(Base, Article): 21 class User(Base): 22 __tablename__ = 'user' 23 id = sa.Column(sa.Integer, primary_key=True) 24 name = sa.Column(sa.Unicode) 25 email = sa.Column(sa.Unicode, unique=True) 26 27 email_lower = sa.orm.column_property( 28 sa.func.lower(name) 29 ) 30 31 User.article_count = sa.orm.column_property( 32 sa.select([sa.func.count()], from_obj=Article) 33 .where(Article.author_id == User.id) 34 .label('article_count') 35 ) 36 return User 37 38 39@pytest.fixture 40def init_models(Article, User): 41 pass 42 43 44class TestMakeOrderByDeterministic(object): 45 46 def test_column_property(self, session, User): 47 query = session.query(User).order_by(User.email_lower) 48 query = make_order_by_deterministic(query) 49 assert_contains('lower(user.name) AS lower_1', query) 50 assert_contains('lower_1, user.id ASC', query) 51 52 def test_unique_column(self, session, User): 53 query = session.query(User).order_by(User.email) 54 query = make_order_by_deterministic(query) 55 56 assert str(query).endswith('ORDER BY user.email') 57 58 def test_non_unique_column(self, session, User): 59 query = session.query(User).order_by(User.name) 60 query = make_order_by_deterministic(query) 61 assert_contains('ORDER BY user.name, user.id ASC', query) 62 63 def test_descending_order_by(self, session, User): 64 query = session.query(User).order_by( 65 sa.desc(User.name) 66 ) 67 query = make_order_by_deterministic(query) 68 assert_contains('ORDER BY user.name DESC, user.id DESC', query) 69 70 def test_ascending_order_by(self, session, User): 71 query = session.query(User).order_by( 72 sa.asc(User.name) 73 ) 74 query = make_order_by_deterministic(query) 75 assert_contains('ORDER BY user.name ASC, user.id ASC', query) 76 77 def test_string_order_by(self, session, User): 78 query = session.query(User).order_by('name') 79 query = make_order_by_deterministic(query) 80 assert_contains('ORDER BY user.name, user.id ASC', query) 81 82 def test_annotated_label(self, session, User): 83 query = session.query(User).order_by(User.article_count) 84 query = make_order_by_deterministic(query) 85 assert_contains('article_count, user.id ASC', query) 86 87 def test_annotated_label_with_descending_order(self, session, User): 88 query = session.query(User).order_by( 89 sa.desc(User.article_count) 90 ) 91 query = make_order_by_deterministic(query) 92 assert_contains('ORDER BY article_count DESC, user.id DESC', query) 93 94 def test_query_without_order_by(self, session, User): 95 query = session.query(User) 96 query = make_order_by_deterministic(query) 97 assert 'ORDER BY user.id' in str(query) 98 99 def test_alias(self, session, User): 100 alias = sa.orm.aliased(User.__table__) 101 query = session.query(alias).order_by(alias.c.name) 102 query = make_order_by_deterministic(query) 103 assert str(query).endswith('ORDER BY user_1.name, user.id ASC') 104