1<?php /** @noinspection SqlDialectInspection */
2
3namespace dokuwiki\plugin\sqlite\test;
4
5use dokuwiki\plugin\sqlite\SQLiteDB;
6use DokuWikiTest;
7
8/**
9 * Test the new SQLiteDB class
10 *
11 * @group plugin_sqlite
12 * @group plugins
13 */
14class SQLiteDBTest extends DokuWikiTest
15{
16
17    protected $res;
18
19    /** @inheritdoc */
20    public function setUp(): void
21    {
22        global $conf;
23        $this->pluginsEnabled[] = 'sqlite';
24
25        // reset database before each test
26        if (file_exists($conf['metadir'] . '/testdb.sqlite3')) {
27            unlink($conf['metadir'] . '/testdb.sqlite3');
28        }
29
30        parent::setUp();
31    }
32
33    public function testDB()
34    {
35        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
36        $this->assertInstanceOf(\PDO::class, $db->getPdo());
37    }
38
39    public function testQuery()
40    {
41        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
42        $sql = "SELECT * FROM testdata WHERE keyword=?";
43
44        $stmt = $db->query($sql, ['music']);
45        $this->assertInstanceOf(\PDOStatement::class, $stmt);
46        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
47        $this->assertCount(5, $result);
48        $stmt->closeCursor();
49    }
50
51    public function testParameterHandling()
52    {
53        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
54
55        $sql = "SELECT ? AS first, ? AS second, ? AS third";
56
57        $result = $db->queryRecord($sql, ['one', 'two', 'three']);
58        $this->assertEquals(['first' => 'one', 'second' => 'two', 'third' => 'three'], $result);
59
60        $result = $db->queryRecord($sql, 'one', 'two', 'three');
61        $this->assertEquals(['first' => 'one', 'second' => 'two', 'third' => 'three'], $result);
62
63        $sql = "SELECT :first AS first, :second AS second, :third AS third";
64
65        $result = $db->queryRecord($sql, ['first' => 'one', 'second' => 'two', 'third' => 'three']);
66        $this->assertEquals(['first' => 'one', 'second' => 'two', 'third' => 'three'], $result);
67    }
68
69    public function testExec()
70    {
71        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
72
73        $sql = "INSERT INTO testdata (keyword, value) VALUES (?, ?)";
74        $insid = $db->exec($sql, ['test', 'test']);
75        $this->assertEquals(11, $insid);
76
77        $sql = "UPDATE testdata SET value=? WHERE keyword=?";
78        $affected = $db->exec($sql, ['test2', 'music']);
79        $this->assertEquals(5, $affected);
80
81    }
82
83    public function testQueryAll()
84    {
85        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
86        $sql = "SELECT * FROM testdata WHERE keyword=?";
87
88        $result = $db->queryAll($sql, ['music']);
89        $this->assertCount(5, $result);
90        $this->assertArrayHasKey('keyword', $result[0]);
91        $this->assertArrayHasKey('value', $result[0]);
92    }
93
94    public function testQueryRecord()
95    {
96        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
97        $sql = "SELECT * FROM testdata WHERE tid=?";
98
99        $result = $db->queryRecord($sql, [4]);
100        $this->assertEquals(['tid' => 4, 'keyword' => 'music', 'value' => 'Classic'], $result);
101    }
102
103    public function testSaveRecord()
104    {
105        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
106
107        $record = [
108            'tid' => 4,
109            'keyword' => 'music',
110            'value' => 'New Classic',
111        ];
112
113        $newrecord = $db->saveRecord('testdata', $record, false);
114        $this->assertNull($newrecord);
115
116        $newrecord = $db->saveRecord('testdata', $record, true);
117        $this->assertEquals($record, $newrecord);
118
119        $another = [
120            'keyword' => 'music',
121            'value' => 'Alternative Rock',
122        ];
123        $newrecord = $db->saveRecord('testdata', $another, false);
124        $this->assertEquals(11, $newrecord['tid']);
125    }
126
127    public function testQueryValue()
128    {
129        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
130        $sql = "SELECT value FROM testdata WHERE tid=?";
131
132        $result = $db->queryValue($sql, [4]);
133        $this->assertEquals('Classic', $result);
134    }
135
136    public function testQueryKeyValueList()
137    {
138        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
139
140        // data has not unique keys, last entry should win
141        $sql = "SELECT keyword, value FROM testdata ORDER BY tid";
142        $result = $db->queryKeyValueList($sql);
143        $this->assertArrayHasKey('music', $result);
144        $this->assertEquals('Boring', $result['music']);
145
146        // reverse is actually unique
147        $sql = "SELECT value, keyword FROM testdata";
148        $result = $db->queryKeyValueList($sql);
149        $this->assertArrayHasKey('Boring', $result);
150        $this->assertEquals('music', $result['Classic']);
151    }
152}
153