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