xref: /plugin/sqlite/SQLiteDB.php (revision 9ac2cb9b115760cc073da49cbd0528e9b762f6a3) 
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    public 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 . '" (' . implode(',', $columns) . ') VALUES (' . implode(
258            ',',
259            $placeholders
260        ) . ')';
261        $stm = $this->query($sql, $values);
262        $success = $stm->rowCount();
263        $stm->closeCursor();
264
265        if ($success) {
266            $sql = 'SELECT * FROM "' . $table . '" WHERE rowid = last_insert_rowid()';
267            return $this->queryRecord($sql);
268        }
269        return null;
270    }
271
272    /**
273     * Execute a query that returns a single value
274     *
275     * @param string $sql
276     * @param ...mixed|array $params
277     * @return mixed|null
278     * @throws \PDOException
279     */
280    public function queryValue($sql, ...$params)
281    {
282        $result = $this->queryAll($sql, ...$params);
283        if (is_array($result) && count($result)) {
284            return array_values($result[0])[0];
285        }
286        return null;
287    }
288
289    /**
290     * Execute a query that returns a list of key-value pairs
291     *
292     * The first column is used as key, the second as value. Any additional colums are ignored.
293     *
294     * @param string $sql
295     * @param ...mixed|array $params
296     * @return array
297     */
298    public function queryKeyValueList($sql, ...$params)
299    {
300        $result = $this->queryAll($sql, ...$params);
301        if (!$result) return [];
302        if (count(array_keys($result[0])) != 2) {
303            throw new \RuntimeException('queryKeyValueList expects a query that returns exactly two columns');
304        }
305        [$key, $val] = array_keys($result[0]);
306
307        return array_combine(
308            array_column($result, $key),
309            array_column($result, $val)
310        );
311    }
312
313    // endregion
314
315    // region meta handling
316
317    /**
318     * Get a config value from the opt table
319     *
320     * @param string $opt Config name
321     * @param mixed $default What to return if the value isn't set
322     * @return mixed
323     * @throws \PDOException
324     */
325    public function getOpt($opt, $default = null)
326    {
327        $value = $this->queryValue("SELECT val FROM opts WHERE opt = ?", [$opt]);
328        if ($value === null) {
329            return $default;
330        }
331        return $value;
332    }
333
334    /**
335     * Set a config value in the opt table
336     *
337     * @param $opt
338     * @param $value
339     * @throws \PDOException
340     */
341    public function setOpt($opt, $value)
342    {
343        $this->exec('REPLACE INTO opts (opt,val) VALUES (?,?)', [$opt, $value]);
344    }
345
346    /**
347     * @return string
348     */
349    public function getDbName()
350    {
351        return $this->dbname;
352    }
353
354    /**
355     * @return string
356     */
357    public function getDbFile()
358    {
359        global $conf;
360        return $conf['metadir'] . '/' . $this->dbname . self::FILE_EXTENSION;
361    }
362
363    /**
364     * Create a dump of the database and its contents
365     *
366     * @return string
367     * @throws \Exception
368     */
369    public function dumpToFile($filename)
370    {
371        $fp = fopen($filename, 'w');
372        if (!$fp) {
373            throw new \Exception('Could not open file ' . $filename . ' for writing');
374        }
375
376        $tables = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='table'");
377        $indexes = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='index'");
378
379        foreach ($tables as $table) {
380            fwrite($fp, "DROP TABLE IF EXISTS '{$table['name']}';\n");
381        }
382
383        foreach ($tables as $table) {
384            fwrite($fp, $table['sql'] . ";\n");
385        }
386
387        foreach ($tables as $table) {
388            $sql = "SELECT * FROM " . $table['name'];
389            $res = $this->query($sql);
390            while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
391                $values = implode(',', array_map(function ($value) {
392                    if ($value === null) return 'NULL';
393                    return $this->pdo->quote($value);
394                }, $row));
395                fwrite($fp, "INSERT INTO '{$table['name']}' VALUES ({$values});\n");
396            }
397            $res->closeCursor();
398        }
399
400        foreach ($indexes as $index) {
401            fwrite($fp, $index['sql'] . ";\n");
402        }
403        fclose($fp);
404        return $filename;
405    }
406
407    // endregion
408
409    // region migration handling
410
411    /**
412     * Apply all pending migrations
413     *
414     * Each migration is executed in a transaction which is rolled back on failure
415     * Migrations can be files in the schema directory or event handlers
416     *
417     * @throws \Exception
418     */
419    protected function applyMigrations()
420    {
421        $currentVersion = $this->currentDbVersion();
422        $latestVersion = $this->latestDbVersion();
423
424        if ($currentVersion === $latestVersion) return;
425
426        for ($newVersion = $currentVersion + 1; $newVersion <= $latestVersion; $newVersion++) {
427            $data = [
428                'dbname' => $this->dbname,
429                'from' => $currentVersion,
430                'to' => $newVersion,
431                'file' => $this->getMigrationFile($newVersion),
432                'sqlite' => $this->helper,
433                'adapter' => $this,
434            ];
435            $event = new Event('PLUGIN_SQLITE_DATABASE_UPGRADE', $data);
436
437            $this->pdo->beginTransaction();
438            try {
439                if ($event->advise_before()) {
440                    // standard migration file
441                    $sql = Tools::SQLstring2array(file_get_contents($data['file']));
442                    foreach ($sql as $query) {
443                        $this->pdo->exec($query);
444                    }
445                } elseif (!$event->result) {
446                    // advise before returned false, but the result was false
447                    throw new \PDOException('Plugin event did not signal success');
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