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