xref: /plugin/sqlite/SQLiteDB.php (revision 0290deae916e3095ecc953614b9aeb5c71dde053) 
1<?php
2
3/**
4 * @noinspection SqlNoDataSourceInspection
5 * @noinspection SqlDialectInspection
6 * @noinspection PhpComposerExtensionStubsInspection
7 */
8
9namespace dokuwiki\plugin\sqlite;
10
11use dokuwiki\Extension\Event;
12use dokuwiki\Logger;
13
14/**
15 * Helpers to access a SQLite Database with automatic schema migration
16 */
17class SQLiteDB
18{
19    const FILE_EXTENSION = '.sqlite3';
20
21    /** @var \PDO */
22    protected $pdo;
23
24    /** @var string */
25    protected $schemadir;
26
27    /** @var string */
28    protected $dbname;
29
30    /** @var \helper_plugin_sqlite */
31    protected $helper;
32
33    /**
34     * Constructor
35     *
36     * @param string $dbname Database name
37     * @param string $schemadir directory with schema migration files
38     * @param \helper_plugin_sqlite $sqlitehelper for backwards compatibility
39     * @throws \Exception
40     */
41    public function __construct($dbname, $schemadir, $sqlitehelper = null)
42    {
43        if (!class_exists('pdo') || !in_array('sqlite', \PDO::getAvailableDrivers())) {
44            throw new \Exception('SQLite PDO driver not available');
45        }
46
47        // backwards compatibility, circular dependency
48        $this->helper = $sqlitehelper;
49        if (!$this->helper) {
50            $this->helper = new \helper_plugin_sqlite();
51        }
52        $this->helper->setAdapter($this);
53
54        $this->schemadir = $schemadir;
55        $this->dbname = $dbname;
56        $file = $this->getDbFile();
57
58        $this->pdo = new \PDO(
59            'sqlite:' . $file,
60            null,
61            null,
62            [
63                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION
64            ]
65        );
66
67        if ($schemadir !== '') {
68            // schema dir is empty, when accessing the DB from Admin interface instead of plugin context
69            $this->applyMigrations();
70        }
71        Functions::register($this->pdo);
72    }
73
74    /**
75     * Do not serialize the DB connection
76     *
77     * @return array
78     */
79    public function __sleep()
80    {
81        $this->pdo = null;
82        return array_keys(get_object_vars($this));
83    }
84
85    /**
86     * On deserialization, reinit database connection
87     */
88    public function __wakeup()
89    {
90        $this->__construct($this->dbname, $this->schemadir, $this->helper);
91    }
92
93    // region public API
94
95    /**
96     * Direct access to the PDO object
97     * @return \PDO
98     */
99    public function getPdo()
100    {
101        return $this->pdo;
102    }
103
104    /**
105     * Execute a statement and return it
106     *
107     * @param string $sql
108     * @param array $parameters
109     * @return \PDOStatement Be sure to close the cursor yourself
110     * @throws \PDOException
111     */
112    public function query($sql, $parameters = [])
113    {
114        $start = microtime(true);
115
116        // Statement preparation sometime throws ValueErrors instead of PDOExceptions, we streamline here
117        try {
118            $stmt = $this->pdo->prepare($sql);
119        } catch (\Throwable $e) {
120            throw new \PDOException($e->getMessage(), (int)$e->getCode(), $e);
121        }
122        $eventData = [
123            'sqlitedb' => $this,
124            'sql' => &$sql,
125            'parameters' => &$parameters,
126            'stmt' => $stmt
127        ];
128        $event = new Event('PLUGIN_SQLITE_QUERY_EXECUTE', $eventData);
129        if ($event->advise_before()) {
130            $stmt->execute($parameters);
131        }
132        $event->advise_after();
133
134        $time = microtime(true) - $start;
135        if ($time > 0.2) {
136            Logger::debug('[sqlite] slow query:  (' . $time . 's)', [
137                'sql' => $sql,
138                'parameters' => $parameters,
139                'backtrace' => explode("\n", dbg_backtrace())
140            ]);
141        }
142
143        return $stmt;
144    }
145
146    /**
147     * Execute a statement and return metadata
148     *
149     * Returns the last insert ID on INSERTs or the number of affected rows
150     *
151     * @param string $sql
152     * @param array $parameters
153     * @return int
154     * @throws \PDOException
155     */
156    public function exec($sql, $parameters = [])
157    {
158        $stmt = $this->query($sql, $parameters);
159
160        $count = $stmt->rowCount();
161        $stmt->closeCursor();
162        if ($count && preg_match('/^INSERT /i', $sql)) {
163            return $this->queryValue('SELECT last_insert_rowid()');
164        }
165
166        return $count;
167    }
168
169    /**
170     * Simple query abstraction
171     *
172     * Returns all data
173     *
174     * @param string $sql
175     * @param array $params
176     * @return array
177     * @throws \PDOException
178     */
179    public function queryAll($sql, $params = [])
180    {
181        $stmt = $this->query($sql, $params);
182        $data = $stmt->fetchAll(\PDO::FETCH_ASSOC);
183        $stmt->closeCursor();
184        return $data;
185    }
186
187    /**
188     * Query one single row
189     *
190     * @param string $sql
191     * @param array $params
192     * @return array|null
193     * @throws \PDOException
194     */
195    public function queryRecord($sql, $params = [])
196    {
197        $stmt = $this->query($sql, $params);
198        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
199        $stmt->closeCursor();
200        if (is_array($row) && count($row)) {
201            return $row;
202        }
203        return null;
204    }
205
206    /**
207     * Insert or replace the given data into the table
208     *
209     * @param string $table
210     * @param array $data
211     * @param bool $replace Conflict resolution, replace or ignore
212     * @return array|null Either the inserted row or null if nothing was inserted
213     * @throws \PDOException
214     */
215    public function saveRecord($table, $data, $replace = true)
216    {
217        $columns = array_map(function ($column) {
218            return '"' . $column . '"';
219        }, array_keys($data));
220        $values = array_values($data);
221        $placeholders = array_pad([], count($columns), '?');
222
223        if ($replace) {
224            $command = 'REPLACE';
225        } else {
226            $command = 'INSERT OR IGNORE';
227        }
228
229        /** @noinspection SqlResolve */
230        $sql = $command . ' INTO "' . $table . '" (' . join(',', $columns) . ') VALUES (' . join(',',
231                $placeholders) . ')';
232        $stm = $this->query($sql, $values);
233        $success = $stm->rowCount();
234        $stm->closeCursor();
235
236        if ($success) {
237            $sql = 'SELECT * FROM "' . $table . '" WHERE rowid = last_insert_rowid()';
238            return $this->queryRecord($sql);
239        }
240        return null;
241    }
242
243    /**
244     * Execute a query that returns a single value
245     *
246     * @param string $sql
247     * @param array $params
248     * @return mixed|null
249     * @throws \PDOException
250     */
251    public function queryValue($sql, $params = [])
252    {
253        $result = $this->queryAll($sql, $params);
254        if (is_array($result) && count($result)) {
255            return array_values($result[0])[0];
256        }
257        return null;
258    }
259
260    /**
261     * Execute a query that returns a list of key-value pairs
262     *
263     * The first column is used as key, the second as value. Any additional colums are ignored.
264     *
265     * @param string $sql
266     * @param array $params
267     * @return array
268     */
269    public function queryKeyValueList($sql, $params = [])
270    {
271        $result = $this->queryAll($sql, $params);
272        if (!$result) return [];
273        if (count(array_keys($result[0])) != 2) {
274            throw new \RuntimeException('queryKeyValueList expects a query that returns exactly two columns');
275        }
276        [$key, $val] = array_keys($result[0]);
277
278        return array_combine(
279            array_column($result, $key),
280            array_column($result, $val)
281        );
282    }
283
284    // endregion
285
286    // region meta handling
287
288    /**
289     * Get a config value from the opt table
290     *
291     * @param string $opt Config name
292     * @param mixed $default What to return if the value isn't set
293     * @return mixed
294     * @throws \PDOException
295     */
296    public function getOpt($opt, $default = null)
297    {
298        $value = $this->queryValue("SELECT val FROM opts WHERE opt = ?", [$opt]);
299        if ($value === null) {
300            return $default;
301        }
302        return $value;
303    }
304
305    /**
306     * Set a config value in the opt table
307     *
308     * @param $opt
309     * @param $value
310     * @throws \PDOException
311     */
312    public function setOpt($opt, $value)
313    {
314        $this->exec('REPLACE INTO opts (opt,val) VALUES (?,?)', [$opt, $value]);
315    }
316
317    /**
318     * @return string
319     */
320    public function getDbName()
321    {
322        return $this->dbname;
323    }
324
325    /**
326     * @return string
327     */
328    public function getDbFile()
329    {
330        global $conf;
331        return $conf['metadir'] . '/' . $this->dbname . self::FILE_EXTENSION;
332    }
333
334    /**
335     * Create a dump of the database and its contents
336     *
337     * @return string
338     * @throws \Exception
339     */
340    public function dumpToFile($filename)
341    {
342        $fp = fopen($filename, 'w');
343        if (!$fp) {
344            throw new \Exception('Could not open file ' . $filename . ' for writing');
345        }
346
347        $tables = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='table'");
348        $indexes = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='index'");
349
350        foreach ($tables as $table) {
351            fwrite($fp, "DROP TABLE IF EXISTS '{$table['name']}';\n");
352        }
353
354        foreach ($tables as $table) {
355            fwrite($fp, $table['sql'] . ";\n");
356        }
357
358        foreach ($tables as $table) {
359            $sql = "SELECT * FROM " . $table['name'];
360            $res = $this->query($sql);
361            while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
362                $values = join(',', array_map(function ($value) {
363                    if ($value === null) return 'NULL';
364                    return $this->pdo->quote($value);
365                }, $row));
366                fwrite($fp, "INSERT INTO '{$table['name']}' VALUES ({$values});\n");
367            }
368            $res->closeCursor();
369        }
370
371        foreach ($indexes as $index) {
372            fwrite($fp, $index['sql'] . ";\n");
373        }
374        fclose($fp);
375        return $filename;
376    }
377
378    // endregion
379
380    // region migration handling
381
382    /**
383     * Apply all pending migrations
384     *
385     * Each migration is executed in a transaction which is rolled back on failure
386     * Migrations can be files in the schema directory or event handlers
387     *
388     * @throws \Exception
389     */
390    protected function applyMigrations()
391    {
392        $currentVersion = $this->currentDbVersion();
393        $latestVersion = $this->latestDbVersion();
394
395        for ($newVersion = $currentVersion + 1; $newVersion <= $latestVersion; $newVersion++) {
396            $data = [
397                'dbname' => $this->dbname,
398                'from' => $currentVersion,
399                'to' => $newVersion,
400                'file' => $this->getMigrationFile($newVersion),
401                'sqlite' => $this->helper,
402                'adapter' => $this,
403            ];
404            $event = new \Doku_Event('PLUGIN_SQLITE_DATABASE_UPGRADE', $data);
405
406            $this->pdo->beginTransaction();
407            try {
408                if ($event->advise_before()) {
409                    // standard migration file
410                    $sql = Tools::SQLstring2array(file_get_contents($data['file']));
411                    foreach ($sql as $query) {
412                        $this->pdo->exec($query);
413                    }
414                } else {
415                    if (!$event->result) {
416                        // advise before returned false, but the result was false
417                        throw new \PDOException('Plugin event did not signal success');
418                    }
419                }
420                $this->setOpt('dbversion', $newVersion);
421                $this->pdo->commit();
422                $event->advise_after();
423            } catch (\Exception $e) {
424                // something went wrong, rollback
425                $this->pdo->rollBack();
426                throw $e;
427            }
428        }
429
430        // vacuum the database to free up unused space
431        $this->pdo->exec('VACUUM');
432    }
433
434    /**
435     * Read the current version from the opt table
436     *
437     * The opt table is created here if not found
438     *
439     * @return int
440     * @throws \PDOException
441     */
442    protected function currentDbVersion()
443    {
444        try {
445            $version = $this->getOpt('dbversion', 0);
446            return (int)$version;
447        } catch (\PDOException $ignored) {
448            // add the opt table - if this fails too, let the exception bubble up
449            $sql = "CREATE TABLE IF NOT EXISTS opts (opt TEXT NOT NULL PRIMARY KEY, val NOT NULL DEFAULT '')";
450            $this->exec($sql);
451            $this->setOpt('dbversion', 0);
452            return 0;
453        }
454    }
455
456    /**
457     * Get the version this db should have
458     *
459     * @return int
460     * @throws \PDOException
461     */
462    protected function latestDbVersion()
463    {
464        if (!file_exists($this->schemadir . '/latest.version')) {
465            throw new \PDOException('No latest.version in schema dir');
466        }
467        return (int)trim(file_get_contents($this->schemadir . '/latest.version'));
468    }
469
470    /**
471     * Get the migrartion file for the given version
472     *
473     * @param int $version
474     * @return string
475     */
476    protected function getMigrationFile($version)
477    {
478        return sprintf($this->schemadir . '/update%04d.sql', $version);
479    }
480    // endregion
481}
482