1<?php /** @noinspection SqlDialectInspection */
2
3namespace dokuwiki\plugin\sqlite\test;
4
5use DokuWikiTest;
6
7/**
8 * Test the helper plugin for backwards compatibility
9 *
10 * @group plugin_sqlite
11 * @group plugins
12 */
13class 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
91INSERT INTO foo SET bar = '
92some multi''d line string
93-- not a comment
94';
95
96SELECT * FROM bar;
97SELECT * FROM bax;
98
99SELECT * FROM bar; SELECT * FROM bax;
100";
101EOF;
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