1 <?php /** @noinspection SqlDialectInspection */
2 
3 namespace dokuwiki\plugin\sqlite\test;
4 
5 use DokuWikiTest;
6 
7 /**
8  * Test the helper plugin for backwards compatibility
9  *
10  * @group plugin_sqlite
11  * @group plugins
12  */
13 class HelperTest extends DokuWikiTest
14 {
15 
16     protected $res;
17 
18     /** @inheritdoc */
19     public function setUp(): void
20     {
21         $this->pluginsEnabled[] = 'data';
22         $this->pluginsEnabled[] = 'sqlite';
23         parent::setUp();
24     }
25 
26     /**
27      * @return \helper_plugin_sqlite
28      * @throws \Exception when databsse is not initialized
29      */
30     protected function getSqliteHelper()
31     {
32         /** @var $SqliteHelper \helper_plugin_sqlite */
33         $SqliteHelper = plugin_load('helper', 'sqlite');
34         if (!$SqliteHelper->init("testdb", DOKU_PLUGIN . "sqlite/_test/db")) {
35             throw new \Exception('Initializing Sqlite Helper fails!');
36         }
37         return $SqliteHelper;
38     }
39 
40     /**
41      * @return \helper_plugin_sqlite
42      */
43     protected function getResultSelectquery()
44     {
45         $SqliteHelper = $this->getSqliteHelper();
46 
47         $sql = "SELECT * FROM testdata WHERE keyword='music'";
48         $res = $SqliteHelper->query($sql);
49         $this->res = $res;
50         return $SqliteHelper;
51     }
52 
53     /**
54      * @return \helper_plugin_sqlite
55      */
56     protected function getResultInsertquery()
57     {
58         /** @var $SqliteHelper \helper_plugin_sqlite */
59         $SqliteHelper = $this->getSqliteHelper();
60 
61         $sql = "INSERT INTO testdata VALUES(20,'glass','Purple')";
62         $res = $SqliteHelper->query($sql);
63         $this->res = $res;
64         return $SqliteHelper;
65     }
66 
67     public function testSQLstring2array()
68     {
69         $SqliteHelper = $this->getSqliteHelper();
70 
71         $sqlstring1 = "INSERT INTO data VALUES('text','text ;text')";
72         $sqlarray1 = array("INSERT INTO data VALUES('text','text ;text')");
73 
74         $sqlstring2 = "INSERT INTO data VALUES('text','text ;text');INSERT INTO data VALUES('text','te''xt ;text');";
75         $sqlarray2 = array(
76             "INSERT INTO data VALUES('text','text ;text')",
77             "INSERT INTO data VALUES('text','te''xt ;text')"
78         );
79 
80         $this->assertEquals($sqlarray1, $SqliteHelper->SQLstring2array($sqlstring1));
81         $this->assertEquals($sqlarray2, $SqliteHelper->SQLstring2array($sqlstring2));
82     }
83 
84     public function testSQLstring2array_complex()
85     {
86         $SqliteHelper = $this->getSqliteHelper();
87 
88         $input = <<<EOF
89 -- This is test data for the SQLstring2array function
90 
91 INSERT INTO foo SET bar = '
92 some multi''d line string
93 -- not a comment
94 ';
95 
96 SELECT * FROM bar;
97 SELECT * FROM bax;
98 
99 SELECT * FROM bar; SELECT * FROM bax;
100 ";
101 EOF;
102 
103         $statements = $SqliteHelper->SQLstring2array($input);
104 
105         $this->assertCount(6, $statements, 'number of detected statements');
106 
107         $this->assertStringContainsString('some multi\'\'d line string', $statements[0]);
108         $this->assertStringContainsString('-- not a comment', $statements[0]);
109 
110         $this->assertEquals('SELECT * FROM bar', $statements[1]);
111         $this->assertEquals('SELECT * FROM bax', $statements[2]);
112         $this->assertEquals('SELECT * FROM bar', $statements[3]);
113         $this->assertEquals('SELECT * FROM bax', $statements[4]);
114     }
115 
116     public function testQuoteAndJoin()
117     {
118         /** @var $SqliteHelper \helper_plugin_sqlite */
119         $SqliteHelper = $this->getSqliteHelper();
120 
121         $string = "Co'mpl''ex \"st'\"ring";
122         $vals = array($string, $string);
123         $quotedstring = "'Co''mpl''''ex \"st''\"ring','Co''mpl''''ex \"st''\"ring'";
124         $this->assertEquals($quotedstring, $SqliteHelper->quote_and_join($vals));
125     }
126 
127     public function testQuoteString()
128     {
129         /** @var $SqliteHelper \helper_plugin_sqlite */
130         $SqliteHelper = $this->getSqliteHelper();
131 
132         $string = "Co'mpl''ex \"st'\"ring";
133         $quotedstring = "'Co''mpl''''ex \"st''\"ring'";
134         $this->assertEquals($quotedstring, $SqliteHelper->quote_string($string));
135     }
136 
137     function testEscapeString()
138     {
139         /** @var $SqliteHelper \helper_plugin_sqlite */
140         $SqliteHelper = $this->getSqliteHelper();
141 
142         $string = "Co'mpl''ex \"st'\"ring";
143         $quotedstring = "Co''mpl''''ex \"st''\"ring";
144         $this->assertEquals($quotedstring, $SqliteHelper->escape_string($string));
145     }
146 
147     function testQuerySelect()
148     {
149         $SqliteHelper = $this->getResultSelectquery();
150         $this->assertNotEquals(false, $this->res);
151 
152         //close cursor
153         $SqliteHelper->res_close($this->res);
154     }
155 
156     function testRes2arrAssoc()
157     {
158         $SqliteHelper = $this->getResultSelectquery();
159 
160         $resultassoc = array(
161             0 => array('tid' => 3, 'keyword' => 'music', 'value' => 'happy'),
162             1 => array('tid' => 4, 'keyword' => 'music', 'value' => 'Classic'),
163             2 => array('tid' => 5, 'keyword' => 'music', 'value' => 'Pop'),
164             3 => array('tid' => 8, 'keyword' => 'music', 'value' => 'Pink'),
165             4 => array('tid' => 10, 'keyword' => 'music', 'value' => 'Boring')
166         );
167 
168         $this->assertEquals($resultassoc, $SqliteHelper->res2arr($this->res, $assoc = true));
169         $this->assertEquals(array(), $SqliteHelper->res2arr(false));
170     }
171 
172     function testRes2arrNum()
173     {
174         $SqliteHelper = $this->getResultSelectquery();
175 
176         $resultnum = array(
177             0 => array(0 => 3, 1 => 'music', 2 => 'happy'),
178             1 => array(0 => 4, 1 => 'music', 2 => 'Classic'),
179             2 => array(0 => 5, 1 => 'music', 2 => 'Pop'),
180             3 => array(0 => 8, 1 => 'music', 2 => 'Pink'),
181             4 => array(0 => 10, 1 => 'music', 2 => 'Boring')
182         );
183 
184         $this->assertEquals($resultnum, $SqliteHelper->res2arr($this->res, $assoc = false));
185     }
186 
187     function testRes2row()
188     {
189         $SqliteHelper = $this->getResultSelectquery();
190 
191         $result0 = array('tid' => 3, 'keyword' => 'music', 'value' => 'happy',);
192         $result2 = array('tid' => 5, 'keyword' => 'music', 'value' => 'Pop',);
193 
194         $this->assertEquals(false, $SqliteHelper->res2row(false));
195         $this->assertEquals($result0, $SqliteHelper->res2row($this->res));
196         $SqliteHelper->res2row($this->res); // skip one row
197         $this->assertEquals($result2, $SqliteHelper->res2row($this->res));
198 
199         //close cursor
200         $SqliteHelper->res_close($this->res);
201     }
202 
203     function testRes2single()
204     {
205         $SqliteHelper = $this->getResultSelectquery();
206 
207         $result1 = 3;
208         $result2 = 4;
209 
210         $this->assertEquals(false, $SqliteHelper->res2single(false));
211         $this->assertEquals($result1, $SqliteHelper->res2single($this->res));
212         $this->assertEquals($result2, $SqliteHelper->res2single($this->res)); //next row
213 
214         //close cursor
215         $SqliteHelper->res_close($this->res);
216     }
217 
218     function testResFetchArray()
219     {
220         $SqliteHelper = $this->getResultSelectquery();
221 
222         $result0 = array(0 => 3, 1 => 'music', 2 => 'happy');
223         $result1 = array(0 => 4, 1 => 'music', 2 => 'Classic');
224 
225         $this->assertEquals(false, $SqliteHelper->res_fetch_array(false));
226         $this->assertEquals($result0, $SqliteHelper->res_fetch_array($this->res));
227         $this->assertEquals($result1, $SqliteHelper->res_fetch_array($this->res)); //next row
228 
229         //close cursor
230         $SqliteHelper->res_close($this->res);
231     }
232 
233     function testFetchAssoc()
234     {
235         $SqliteHelper = $this->getResultSelectquery();
236 
237         $result0 = array('tid' => 3, 'keyword' => 'music', 'value' => 'happy',);
238         $result1 = array('tid' => 4, 'keyword' => 'music', 'value' => 'Classic');
239 
240         $this->assertEquals(false, $SqliteHelper->res_fetch_assoc(false));
241         $this->assertEquals($result0, $SqliteHelper->res_fetch_assoc($this->res));
242         $this->assertEquals($result1, $SqliteHelper->res_fetch_assoc($this->res)); //next row
243 
244         //close cursor
245         $SqliteHelper->res_close($this->res);
246     }
247 
248     function testRes2count()
249     {
250         $SqliteHelper = $this->getResultSelectquery();
251 
252         $result = 5;
253 
254         $this->assertSame(0, $SqliteHelper->res2count(false));
255         $this->assertEquals($result, $SqliteHelper->res2count($this->res));
256     }
257 
258     function testCountChanges()
259     {
260         $SqliteHelper = $this->getResultInsertquery();
261 
262         $this->assertSame(0, $SqliteHelper->countChanges(false), 'Empty result');
263         $this->assertEquals(1, $SqliteHelper->countChanges($this->res), 'Insert result');
264     }
265 
266     function testSerialize()
267     {
268         $SqliteHelper = $this->getSqliteHelper();
269 
270         $res = $SqliteHelper->query('SELECT * FROM testdata');
271         $this->assertNotFalse($res);
272         $SqliteHelper->res_close($res);
273 
274         $obj = unserialize(serialize($SqliteHelper));
275 
276         $res = $obj->query('SELECT * FROM testdata');
277         $this->assertNotFalse($res);
278         $obj->res_close($res);
279     }
280 }
281