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?>