1 <?php /** @noinspection SqlDialectInspection */
2 
3 namespace dokuwiki\plugin\sqlite\test;
4 
5 use dokuwiki\plugin\sqlite\SQLiteDB;
6 use DokuWikiTest;
7 
8 /**
9  * Test the new SQLiteDB class
10  *
11  * @group plugin_sqlite
12  * @group plugins
13  */
14 class 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