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