1<?php
2// $Id$
3
4require_once 'simple_include.php';
5require_once 'pager_wrapper_include.php';
6
7class TestOfPagerWrapper extends UnitTestCase
8{
9    function TestOfPagerWrapper($name='Test of Pager_Wrapper') {
10        parent::__construct($name);
11    }
12
13    function setUp() { }
14    function tearDown() { }
15
16    /**
17     * Basic tests for rewriteCountQuery()
18     */
19    function testRewriteCountQuery() {
20        //test LIMIT
21        $query = 'SELECT a, b, c, d FROM mytable WHERE a=1 AND c="g" LIMIT 2';
22        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
23        $this->assertEqual($expected, rewriteCountQuery($query));
24
25        //test ORDER BY and quotes
26        $query = 'SELECT a, b, c, d FROM mytable WHERE a=1 AND c="g" ORDER BY (a, b)';
27        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
28        $this->assertEqual($expected, rewriteCountQuery($query));
29
30        //test CR/LF
31        $query = 'SELECT a, b, c, d FROM mytable
32                   WHERE a=1
33                     AND c="g"
34                ORDER BY (a, b)';
35        $expected = 'SELECT COUNT(*) FROM mytable
36                   WHERE a=1
37                     AND c="g"';
38        $this->assertEqual($expected, rewriteCountQuery($query));
39
40        //test GROUP BY
41        $query = 'SELECT a, b, c, d FROM mytable WHERE a=1 GROUP  BY c';
42        $this->assertFalse(rewriteCountQuery($query));
43
44        //test DISTINCT
45        $query = 'SELECT DISTINCT a, b, c, d FROM  mytable WHERE a=1 GROUP BY c';
46        $this->assertFalse(rewriteCountQuery($query));
47
48        //test UNION
49        $query = 'SELECT a FROM mytable1 UNION SELECT a FROM mytable2';
50        $this->assertFalse(rewriteCountQuery($query));
51
52        //test MiXeD Keyword CaSe
53        $query = 'SELECT a, b, c, d from mytable WHERE a=1 AND c="g"';
54        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
55        $this->assertEqual($expected, rewriteCountQuery($query));
56
57        //test function speed... this query used to be very slow to parse
58        $query = "SELECT  i.item_id,
59                ia.addition,
60                u.username,
61                i.date_created,
62                i.start_date,
63                i.expiry_date
64        FROM    item i, item_addition ia, item_type it, item_type_mapping itm, usr u, category c
65        WHERE   ia.item_type_mapping_id = itm.item_type_mapping_id
66        AND     i.updated_by_id = u.usr_id
67        AND     it.item_type_id  = itm.item_type_id
68        AND     i.item_id = ia.item_id
69        AND     i.item_type_id = it.item_type_id
70        AND     itm.field_name = 'title' AND it.item_type_id = 2 AND i.category_id = 1 AND i.status  = 4
71        AND     i.category_id = c.category_id
72        AND     0 NOT IN (COALESCE(c.perms, '-1'))
73        ORDER BY i.last_updated DESC";
74        $expected = "SELECT COUNT(*) FROM    item i, item_addition ia, item_type it, item_type_mapping itm, usr u, category c
75        WHERE   ia.item_type_mapping_id = itm.item_type_mapping_id
76        AND     i.updated_by_id = u.usr_id
77        AND     it.item_type_id  = itm.item_type_id
78        AND     i.item_id = ia.item_id
79        AND     i.item_type_id = it.item_type_id
80        AND     itm.field_name = 'title' AND it.item_type_id = 2 AND i.category_id = 1 AND i.status  = 4
81        AND     i.category_id = c.category_id
82        AND     0 NOT IN (COALESCE(c.perms, '-1'))";
83        $this->assertEqual($expected, rewriteCountQuery($query));
84    }
85
86    /**
87     * Test rewriteCountQuery() with queries having a subquery in the SELECT clause
88     */
89    function testRewriteCountQuery_SubqueriesInSelectClause() {
90        $query = 'SELECT a, (SELECT a FROM b) AS b, c, d FROM mytable WHERE a=1 AND c="g" LIMIT 2';
91        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
92        $this->assertFalse(rewriteCountQuery($query));
93
94        $query = 'SELECT a, (SELECT a FROM b) AS b, (SELECT c FROM c) AS c, d FROM mytable WHERE a=1 AND c="g" LIMIT 2';
95        //$expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
96        $this->assertFalse(rewriteCountQuery($query));
97
98        $query = 'SELECT `id`, `ip`, (
99SELECT TIMEDIFF(MAX(P.`time`), MIN(P.`time`))
100FROM `przejscia` as P
101WHERE P.`id_wejscia`=W.`id`
102) as `czas`
103FROM `wejscia` as W
104WHERE W.id_domeny=?
105ORDER BY W.czas_wejscia DESC';
106        $expected = 'SELECT COUNT(*)
107FROM `wejscia` as W
108WHERE W.id_domeny=?
109ORDER BY W.czas_wejscia DESC';
110        $this->assertFalse(rewriteCountQuery($query));
111    }
112
113    /**
114     * Test rewriteCountQuery() with queries having a subquery in the FROM clause
115     */
116    function testRewriteCountQuery_SubqueriesInFromClause() {
117        $query = 'SELECT a, b, c, d FROM (SELECT a, b, c, d FROM mytable WHERE a=1) AS tbl_alias WHERE a=1';
118        $expected = 'SELECT COUNT(*) FROM (SELECT a, b, c, d FROM mytable WHERE a=1) AS tbl_alias WHERE a=1';
119        $this->assertEqual($expected, rewriteCountQuery($query));
120    }
121
122    /**
123     * Test rewriteCountQuery() with queries having a subquery in the WHERE clause
124     */
125    function testRewriteCountQuery_SubqueriesInWhereClause() {
126        //this one is not rewritten: subqueries with ORDER BY clauses might get truncated
127        $query = 'SELECT Version.VersionId, Version.Identifier,News.*
128FROM VersionBroker
129JOIN ObjectType ON ObjectType.ObjectTypeId = VersionBroker.ObjectTypeId
130JOIN Version ON VersionBroker.Identifier = Version.Identifier
131JOIN News ON Version.ObjectId = News.NewsId
132WHERE Version.Status = \'Approved\'
133AND ObjectType.Name = \'News\'
134AND Version.ApprovedTS = (
135    SELECT SubV.ApprovedTS
136    FROM Version SubV
137    WHERE SubV.Identifier = VersionBroker.Identifier
138    ORDER BY ApprovedTS DESC
139    LIMIT 1)
140ORDER BY ApprovedTS DESC';
141
142        $expected = 'SELECT COUNT(*)
143FROM VersionBroker
144JOIN ObjectType ON ObjectType.ObjectTypeId = VersionBroker.ObjectTypeId
145JOIN Version ON VersionBroker.Identifier = Version.Identifier
146JOIN News ON Version.ObjectId = News.NewsId
147WHERE Version.Status = \'Approved\'
148AND ObjectType.Name = \'News\'
149AND Version.ApprovedTS = (
150    SELECT SubV.ApprovedTS
151    FROM Version SubV
152    WHERE SubV.Identifier = VersionBroker.Identifier
153    ORDER BY ApprovedTS DESC
154    LIMIT 1)
155ORDER BY ApprovedTS DESC';
156        //$this->assertEqual($expected, rewriteCountQuery($query));
157        $this->assertFalse(rewriteCountQuery($query));
158
159        //this one should pass... subquery without ORDER BY or LIMIT clause
160        $query = 'SELECT Version.VersionId, Version.Identifier,News.* FROM VersionBroker JOIN
161ObjectType ON ObjectType.ObjectTypeId = VersionBroker.ObjectTypeId JOIN
162Version ON VersionBroker.Identifier = Version.Identifier JOIN News ON
163Version.ObjectId = News.NewsId WHERE Version.Status = \'Approved\' AND
164ObjectType.Name = \'News\' AND Version.ApprovedTS = ( SELECT SubV.ApprovedTS
165FROM Version SubV WHERE SubV.Identifier = VersionBroker.Identifier ) ORDER BY ApprovedTS DESC';
166
167        $expected = 'SELECT COUNT(*) FROM VersionBroker JOIN
168ObjectType ON ObjectType.ObjectTypeId = VersionBroker.ObjectTypeId JOIN
169Version ON VersionBroker.Identifier = Version.Identifier JOIN News ON
170Version.ObjectId = News.NewsId WHERE Version.Status = \'Approved\' AND
171ObjectType.Name = \'News\' AND Version.ApprovedTS = ( SELECT SubV.ApprovedTS
172FROM Version SubV WHERE SubV.Identifier = VersionBroker.Identifier )';
173        $this->assertEqual($expected, rewriteCountQuery($query));
174    }
175
176    /**
177     * Test rewriteCountQuery() with queries having keywords embedded in other words
178     */
179    function testRewriteCountQuery_EmbeddedKeywords() {
180        $query = 'SELECT afieldFROM, b, c, d FROM mytable WHERE a=1 AND c="g"';
181        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
182        $this->assertEqual($expected, rewriteCountQuery($query));
183
184        $query = 'SELECT FROMafield, b, c, d FROM mytable WHERE a=1 AND c="g"';
185        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
186        $this->assertEqual($expected, rewriteCountQuery($query));
187
188        $query = 'SELECT afieldFROMaaa, b, c, d FROM mytable WHERE a=1 AND c="gLIMIT"';
189        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="gLIMIT"';
190        $this->assertEqual($expected, rewriteCountQuery($query));
191
192        $query = 'SELECT DISTINCTaaa, b, c, d FROM mytable WHERE a=1 AND c="g"';
193        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g"';
194        $this->assertEqual($expected, rewriteCountQuery($query));
195
196        //this one fails... the regexp should NOT match keywords within quotes.
197        //we need a full blown stack-based parser to catch this...
198        $query = 'SELECT afieldFROMaaa, b, c, d FROM mytable WHERE a=1 AND c="g LIMIT a"';
199        $expected = 'SELECT COUNT(*) FROM mytable WHERE a=1 AND c="g LIMIT a"';
200        $this->assertEqual($expected, rewriteCountQuery($query));
201    }
202}
203?>