xref: /plugin/sqlite/_test/SQLiteDBTest.php (revision 33e488b3fffebc85f02333885d5d0ead1dd65c8b) 
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 testExec()
52    {
53        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
54
55        $sql = "INSERT INTO testdata (keyword, value) VALUES (?, ?)";
56        $insid = $db->exec($sql, ['test', 'test']);
57        $this->assertEquals(11, $insid);
58
59        $sql = "UPDATE testdata SET value=? WHERE keyword=?";
60        $affected = $db->exec($sql, ['test2', 'music']);
61        $this->assertEquals(5, $affected);
62
63    }
64
65    public function testQueryAll()
66    {
67        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
68        $sql = "SELECT * FROM testdata WHERE keyword=?";
69
70        $result = $db->queryAll($sql, ['music']);
71        $this->assertCount(5, $result);
72        $this->assertArrayHasKey('keyword', $result[0]);
73        $this->assertArrayHasKey('value', $result[0]);
74    }
75
76    public function testQueryRecord()
77    {
78        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
79        $sql = "SELECT * FROM testdata WHERE tid=?";
80
81        $result = $db->queryRecord($sql, [4]);
82        $this->assertEquals(['tid' => 4, 'keyword' => 'music', 'value' => 'Classic'], $result);
83    }
84
85    public function testSaveRecord()
86    {
87        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
88
89        $record = [
90            'tid' => 4,
91            'keyword' => 'music',
92            'value' => 'New Classic',
93        ];
94
95        $newrecord = $db->saveRecord('testdata', $record, false);
96        $this->assertNull($newrecord);
97
98        $newrecord = $db->saveRecord('testdata', $record, true);
99        $this->assertEquals($record, $newrecord);
100
101        $another = [
102            'keyword' => 'music',
103            'value' => 'Alternative Rock',
104        ];
105        $newrecord = $db->saveRecord('testdata', $another, false);
106        $this->assertEquals(11, $newrecord['tid']);
107    }
108
109    public function testQueryValue()
110    {
111        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
112        $sql = "SELECT value FROM testdata WHERE tid=?";
113
114        $result = $db->queryValue($sql, [4]);
115        $this->assertEquals('Classic', $result);
116    }
117
118    public function testQueryKeyValueList()
119    {
120        $db = new SQLiteDB('testdb', DOKU_PLUGIN . "sqlite/_test/db");
121
122        // data has not unique keys, last entry should win
123        $sql = "SELECT keyword, value FROM testdata ORDER BY tid";
124        $result = $db->queryKeyValueList($sql);
125        $this->assertArrayHasKey('music', $result);
126        $this->assertEquals('Boring', $result['music']);
127
128        // reverse is actually unique
129        $sql = "SELECT value, keyword FROM testdata";
130        $result = $db->queryKeyValueList($sql);
131        $this->assertArrayHasKey('Boring', $result);
132        $this->assertEquals('music', $result['Classic']);
133    }
134}
135