1<?php
2
3namespace dokuwiki\plugin\struct\test;
4
5use dokuwiki\plugin\struct\meta;
6
7/**
8 * Tests for the building of SQL-Queries for the struct plugin
9 *
10 * @group plugin_struct
11 * @group plugins
12 *
13 */
14class Search_struct_test extends StructTest
15{
16
17    public function setUp(): void
18    {
19        parent::setUp();
20
21        $this->loadSchemaJSON('schema1');
22        $this->loadSchemaJSON('schema2');
23        $_SERVER['REMOTE_USER'] = 'testuser';
24
25        $as = mock\Assignments::getInstance();
26        $page = 'page01';
27        $as->assignPageSchema($page, 'schema1');
28        $as->assignPageSchema($page, 'schema2');
29        saveWikiText($page, "===== TestTitle =====\nabc", "Summary");
30        p_get_metadata($page);
31        $now = time();
32        $this->saveData(
33            $page,
34            'schema1',
35            array(
36                'first' => 'first data',
37                'second' => array('second data', 'more data', 'even more'),
38                'third' => 'third data',
39                'fourth' => 'fourth data'
40            ),
41            $now
42        );
43        $this->saveData(
44            $page,
45            'schema2',
46            array(
47                'afirst' => 'first data',
48                'asecond' => array('second data', 'more data', 'even more'),
49                'athird' => 'third data',
50                'afourth' => 'fourth data'
51            ),
52            $now
53        );
54
55        $as->assignPageSchema('test:document', 'schema1');
56        $as->assignPageSchema('test:document', 'schema2');
57        $this->saveData(
58            'test:document',
59            'schema1',
60            array(
61                'first' => 'document first data',
62                'second' => array('second', 'more'),
63                'third' => '',
64                'fourth' => 'fourth data'
65            ),
66            $now
67        );
68        $this->saveData(
69            'test:document',
70            'schema2',
71            array(
72                'afirst' => 'first data',
73                'asecond' => array('second data', 'more data', 'even more'),
74                'athird' => 'third data',
75                'afourth' => 'fourth data'
76            ),
77            $now
78        );
79
80        for ($i = 10; $i <= 20; $i++) {
81            $this->saveData(
82                "page$i",
83                'schema2',
84                array(
85                    'afirst' => "page$i first data",
86                    'asecond' => array("page$i second data"),
87                    'athird' => "page$i third data",
88                    'afourth' => "page$i fourth data"
89                ),
90                $now
91            );
92            $as->assignPageSchema("page$i", 'schema2');
93        }
94    }
95
96    public function test_simple()
97    {
98        $search = new mock\Search();
99
100        $search->addSchema('schema1');
101        $search->addColumn('%pageid%');
102        $search->addColumn('first');
103        $search->addColumn('second');
104
105        /** @var meta\Value[][] $result */
106        $result = $search->execute();
107
108        $this->assertEquals(2, count($result), 'result rows');
109        $this->assertEquals(3, count($result[0]), 'result columns');
110        $this->assertEquals('page01', $result[0][0]->getValue());
111        $this->assertEquals('first data', $result[0][1]->getValue());
112        $this->assertEquals(array('second data', 'more data', 'even more'), $result[0][2]->getValue());
113    }
114
115    public function test_simple_title()
116    {
117        $search = new mock\Search();
118
119        $search->addSchema('schema1');
120        $search->addColumn('%title%');
121        $search->addColumn('first');
122        $search->addColumn('second');
123
124        /** @var meta\Value[][] $result */
125        $result = $search->execute();
126
127        $this->assertEquals(2, count($result), 'result rows');
128        $this->assertEquals(3, count($result[0]), 'result columns');
129        $this->assertEquals('["page01","TestTitle"]', $result[0][0]->getValue());
130        $this->assertEquals('first data', $result[0][1]->getValue());
131        $this->assertEquals(array('second data', 'more data', 'even more'), $result[0][2]->getValue());
132    }
133
134    public function test_search_published()
135    {
136        $search = new mock\Search();
137        $search->isNotPublisher();
138
139        $search->addSchema('schema1');
140        $search->addColumn('%pageid%');
141        $search->addColumn('first');
142        $search->addColumn('second');
143
144        /** @var meta\Value[][] $result */
145        $result = $search->execute();
146
147        $this->assertEquals(0, count($result), 'result rows');
148    }
149
150    public function test_search_lasteditor()
151    {
152        $search = new mock\Search();
153
154        $search->addSchema('schema1');
155        $search->addColumn('%title%');
156        $search->addColumn('%lasteditor%');
157        $search->addColumn('first');
158        $search->addColumn('second');
159
160        /** @var meta\Value[][] $result */
161        $result = $search->execute();
162
163        $this->assertEquals(2, count($result), 'result rows');
164        $this->assertEquals(4, count($result[0]), 'result columns');
165        $this->assertEquals('testuser', $result[0][1]->getValue());
166        $this->assertEquals(array('second data', 'more data', 'even more'), $result[0][3]->getValue());
167    }
168
169
170    /**
171     * @group slow
172     */
173    public function test_search_lastupdate()
174    {
175        sleep(1);
176        saveWikiText('page01', "===== TestTitle =====\nabcd", "Summary");
177        p_get_metadata('page01');
178
179        $search = new mock\Search();
180
181        $search->addSchema('schema1');
182        $search->addColumn('%pageid%');
183        $search->addColumn('%lastupdate%');
184        $search->addColumn('first');
185        $search->addColumn('second');
186
187        /** @var meta\Value[][] $result */
188        $result = $search->execute();
189
190        $expected_time = dformat(filemtime(wikiFN('page01')), '%Y-%m-%d %H:%M:%S');
191
192        $this->assertEquals(2, count($result), 'result rows');
193        $this->assertEquals(4, count($result[0]), 'result columns');
194        $this->assertEquals($expected_time, $result[0][1]->getValue());
195        $this->assertEquals(array('second data', 'more data', 'even more'), $result[0][3]->getValue());
196    }
197
198    /**
199     * @group slow
200     */
201    public function test_search_lastsummary()
202    {
203        sleep(1);
204        $summary = 'Summary';
205        saveWikiText('page01', "===== TestTitle =====\nabcd", $summary);
206        p_get_metadata('page01');
207
208        $search = new mock\Search();
209
210        $search->addSchema('schema1');
211        $search->addColumn('%pageid%');
212        $search->addColumn('%lastsummary%');
213        $search->addColumn('first');
214        $search->addColumn('second');
215
216        /** @var meta\Value[][] $result */
217        $result = $search->execute();
218
219        $this->assertEquals(2, count($result), 'result rows');
220        $this->assertEquals(4, count($result[0]), 'result columns');
221        $this->assertEquals($summary, $result[0][1]->getValue());
222        $this->assertEquals(array('second data', 'more data', 'even more'), $result[0][3]->getValue());
223    }
224
225    public function test_search()
226    {
227        $search = new mock\Search();
228
229        $search->addSchema('schema1');
230        $search->addSchema('schema2', 'foo');
231        $this->assertEquals(2, count($search->schemas));
232
233        $search->addColumn('first');
234        $this->assertEquals('schema1', $search->columns[0]->getTable());
235        $this->assertEquals(1, $search->columns[0]->getColref());
236
237        $search->addColumn('afirst');
238        $this->assertEquals('schema2', $search->columns[1]->getTable());
239        $this->assertEquals(1, $search->columns[1]->getColref());
240
241        $search->addColumn('schema1.third');
242        $this->assertEquals('schema1', $search->columns[2]->getTable());
243        $this->assertEquals(3, $search->columns[2]->getColref());
244
245        $search->addColumn('foo.athird');
246        $this->assertEquals('schema2', $search->columns[3]->getTable());
247        $this->assertEquals(3, $search->columns[3]->getColref());
248
249        $search->addColumn('asecond');
250        $this->assertEquals('schema2', $search->columns[4]->getTable());
251        $this->assertEquals(2, $search->columns[4]->getColref());
252
253        $search->addColumn('doesntexist');
254        $this->assertEquals(5, count($search->columns));
255
256        $search->addColumn('%pageid%');
257        $this->assertEquals('schema1', $search->columns[5]->getTable());
258        $exception = false;
259        try {
260            $search->columns[5]->getColref();
261        } catch (meta\StructException $e) {
262            $exception = true;
263        }
264        $this->assertTrue($exception, "Struct exception expected for accesing colref of PageColumn");
265
266        $search->addSort('first', false);
267        $this->assertEquals(1, count($search->sortby));
268
269        $search->addFilter('%pageid%', '%ag%', '~', 'AND');
270        $search->addFilter('second', '%sec%', '~', 'AND');
271        $search->addFilter('first', '%rst%', '~', 'AND');
272
273        $result = $search->execute();
274        $count = $search->getCount();
275
276        $this->assertEquals(1, $count, 'result count');
277        $this->assertEquals(1, count($result), 'result rows');
278        $this->assertEquals(6, count($result[0]), 'result columns');
279
280        // sort by multi-column
281        $search->addSort('second');
282        $this->assertEquals(2, count($search->sortby));
283        $result = $search->execute();
284        $count = $search->getCount();
285        $this->assertEquals(1, $count, 'result count');
286        $this->assertEquals(1, count($result), 'result rows');
287        $this->assertEquals(6, count($result[0]), 'result columns');
288
289        /*
290        {#debugging
291            list($sql, $opts) = $search->getSQL();
292            print "\n";
293            print_r($sql);
294            print "\n";
295            print_r($opts);
296            print "\n";
297            #print_r($result);
298        }
299        */
300    }
301
302    public function test_ranges()
303    {
304        $search = new mock\Search();
305        $search->addSchema('schema2');
306
307        $search->addColumn('%pageid%');
308        $search->addColumn('afirst');
309        $search->addColumn('asecond');
310
311        $search->addFilter('%pageid%', '%ag%', '~', 'AND');
312
313        $search->addSort('%pageid%', false);
314
315        /** @var meta\Value[][] $result */
316        $result = $search->execute();
317        $count = $search->getCount();
318
319        // check result dimensions
320        $this->assertEquals(12, $count, 'result count');
321        $this->assertEquals(12, count($result), 'result rows');
322        $this->assertEquals(3, count($result[0]), 'result columns');
323
324        // check sorting
325        $this->assertEquals('page20', $result[0][0]->getValue());
326        $this->assertEquals('page19', $result[1][0]->getValue());
327        $this->assertEquals('page18', $result[2][0]->getValue());
328
329        // now add limit
330        $search->setLimit(5);
331        $result = $search->execute();
332        $count = $search->getCount();
333
334        // check result dimensions
335        $this->assertEquals(12, $count, 'result count'); // full result set
336        $this->assertEquals(5, count($result), 'result rows'); // wanted result set
337
338        // check the values
339        $this->assertEquals('page20', $result[0][0]->getValue());
340        $this->assertEquals('page16', $result[4][0]->getValue());
341
342        // now add offset
343        $search->setOffset(5);
344        $result = $search->execute();
345        $count = $search->getCount();
346
347        // check result dimensions
348        $this->assertEquals(12, $count, 'result count'); // full result set
349        $this->assertEquals(5, count($result), 'result rows'); // wanted result set
350
351        // check the values
352        $this->assertEquals('page15', $result[0][0]->getValue());
353        $this->assertEquals('page11', $result[4][0]->getValue());
354    }
355
356    public static function addFilter_testdata()
357    {
358        return array(
359            array('%pageid%', 'val', '<>', 'OR', array(array('%pageid%', 'val', '!=', 'OR')), false, 'replace <> comp'),
360            array('%pageid%', 'val', '*~', 'OR', array(array('%pageid%', '%val%', 'LIKE', 'OR')), false, 'replace *~ comp'),
361            array('%pageid%', 'val*', '~', 'OR', array(array('%pageid%', 'val%', 'LIKE', 'OR')), false, 'replace * in value'),
362            array('%pageid%', 'val.*', '=*', 'OR', array(array('%pageid%', 'val.*', 'REGEXP', 'OR')), false, 'replace * in value'),
363            array('nonexisting', 'val', '~', 'OR', array(), false, 'ignore missing columns'),
364            array('%pageid%', 'val', '?', 'OR', array(), '\dokuwiki\plugin\struct\meta\StructException', 'wrong comperator'),
365            array('%pageid%', 'val', '=', 'NOT', array(), '\dokuwiki\plugin\struct\meta\StructException', 'wrong type')
366        );
367    }
368
369    /**
370     * @dataProvider addFilter_testdata
371     *
372     */
373    public function test_addFilter($colname, $value, $comp, $type, $expected_filter, $expectException, $msg)
374    {
375        $search = new mock\Search();
376        $search->addSchema('schema2');
377        $search->addColumn('%pageid%');
378        if ($expectException !== false) $this->setExpectedException($expectException);
379
380        $search->addFilter($colname, $value, $comp, $type);
381
382        if (count($expected_filter) === 0) {
383            $this->assertEquals(count($search->filter), 0, $msg);
384            return;
385        }
386        $this->assertEquals($expected_filter[0][0], $search->filter[0][0]->getLabel(), $msg);
387        $this->assertEquals($expected_filter[0][1], $search->filter[0][1], $msg);
388        $this->assertEquals($expected_filter[0][2], $search->filter[0][2], $msg);
389        $this->assertEquals($expected_filter[0][3], $search->filter[0][3], $msg);
390    }
391
392    public function test_wildcard()
393    {
394        $search = new mock\Search();
395        $search->addSchema('schema2', 'alias');
396        $search->addColumn('*');
397        $this->assertEquals(4, count($search->getColumns()));
398
399        $search = new mock\Search();
400        $search->addSchema('schema2', 'alias');
401        $search->addColumn('schema2.*');
402        $this->assertEquals(4, count($search->getColumns()));
403
404        $search = new mock\Search();
405        $search->addSchema('schema2', 'alias');
406        $search->addColumn('alias.*');
407        $this->assertEquals(4, count($search->getColumns()));
408
409        $search = new mock\Search();
410        $search->addSchema('schema2', 'alias');
411        $search->addColumn('nope.*');
412        $this->assertEquals(0, count($search->getColumns()));
413    }
414
415    public function test_filterValueList()
416    {
417        $search = new mock\Search();
418
419        //simple - single quote
420        $this->assertEquals(array('test'),
421            $this->callInaccessibleMethod($search, 'parseFilterValueList', array('("test")')));
422
423        //simple - double quote
424        $this->assertEquals(array('test'),
425            $this->callInaccessibleMethod($search, 'parseFilterValueList', array("('test')")));
426
427        //many elements
428        $this->assertEquals(array('test', 'test2', '18'),
429            $this->callInaccessibleMethod($search, 'parseFilterValueList', array('("test", \'test2\', 18)')));
430
431        $str = <<<'EOD'
432("t\"est", 't\'est2', 18)
433EOD;
434        //escape sequences
435        $this->assertEquals(array('t"est', "t'est2", '18'),
436            $this->callInaccessibleMethod($search, 'parseFilterValueList', array($str)));
437
438        //numbers
439        $this->assertEquals(array('18.7', '10e5', '-100'),
440            $this->callInaccessibleMethod($search, 'parseFilterValueList', array('(18.7, 10e5, -100)')));
441
442    }
443}
444