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