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