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