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