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