1 <?php
2 
3 /**
4  * @noinspection SqlNoDataSourceInspection
5  * @noinspection SqlDialectInspection
6  * @noinspection PhpComposerExtensionStubsInspection
7  */
8 
9 namespace dokuwiki\plugin\sqlite;
10 
11 use dokuwiki\Extension\Event;
12 use dokuwiki\Logger;
13 
14 /**
15  * Helpers to access a SQLite Database with automatic schema migration
16  */
17 class 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(static fn($column) => '"' . $column . '"', array_keys($data));
249         $values = array_values($data);
250         $placeholders = array_pad([], count($columns), '?');
251 
252         if ($replace) {
253             $command = 'REPLACE';
254         } else {
255             $command = 'INSERT OR IGNORE';
256         }
257 
258         /** @noinspection SqlResolve */
259         $sql = $command . ' INTO "' . $table . '" (' . implode(',', $columns) . ') VALUES (' . implode(
260             ',',
261             $placeholders
262         ) . ')';
263         $stm = $this->query($sql, $values);
264         $success = $stm->rowCount();
265         $stm->closeCursor();
266 
267         if ($success) {
268             $sql = 'SELECT * FROM "' . $table . '" WHERE rowid = last_insert_rowid()';
269             return $this->queryRecord($sql);
270         }
271         return null;
272     }
273 
274     /**
275      * Execute a query that returns a single value
276      *
277      * @param string $sql
278      * @param ...mixed|array $params
279      * @return mixed|null
280      * @throws \PDOException
281      */
282     public function queryValue($sql, ...$params)
283     {
284         $result = $this->queryAll($sql, ...$params);
285         if (is_array($result) && count($result)) {
286             return array_values($result[0])[0];
287         }
288         return null;
289     }
290 
291     /**
292      * Execute a query that returns a list of key-value pairs
293      *
294      * The first column is used as key, the second as value. Any additional colums are ignored.
295      *
296      * @param string $sql
297      * @param ...mixed|array $params
298      * @return array
299      */
300     public function queryKeyValueList($sql, ...$params)
301     {
302         $result = $this->queryAll($sql, ...$params);
303         if (!$result) return [];
304         if (count(array_keys($result[0])) != 2) {
305             throw new \RuntimeException('queryKeyValueList expects a query that returns exactly two columns');
306         }
307         [$key, $val] = array_keys($result[0]);
308 
309         return array_combine(
310             array_column($result, $key),
311             array_column($result, $val)
312         );
313     }
314 
315     // endregion
316 
317     // region meta handling
318 
319     /**
320      * Get a config value from the opt table
321      *
322      * @param string $opt Config name
323      * @param mixed $default What to return if the value isn't set
324      * @return mixed
325      * @throws \PDOException
326      */
327     public function getOpt($opt, $default = null)
328     {
329         $value = $this->queryValue("SELECT val FROM opts WHERE opt = ?", [$opt]);
330         if ($value === null) {
331             return $default;
332         }
333         return $value;
334     }
335 
336     /**
337      * Set a config value in the opt table
338      *
339      * @param $opt
340      * @param $value
341      * @throws \PDOException
342      */
343     public function setOpt($opt, $value)
344     {
345         $this->exec('REPLACE INTO opts (opt,val) VALUES (?,?)', [$opt, $value]);
346     }
347 
348     /**
349      * @return string
350      */
351     public function getDbName()
352     {
353         return $this->dbname;
354     }
355 
356     /**
357      * @return string
358      */
359     public function getDbFile()
360     {
361         global $conf;
362         return $conf['metadir'] . '/' . $this->dbname . self::FILE_EXTENSION;
363     }
364 
365     /**
366      * Create a dump of the database and its contents
367      *
368      * @return string
369      * @throws \Exception
370      */
371     public function dumpToFile($filename)
372     {
373         $fp = fopen($filename, 'w');
374         if (!$fp) {
375             throw new \Exception('Could not open file ' . $filename . ' for writing');
376         }
377 
378         $tables = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='table'");
379         $indexes = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='index'");
380 
381         foreach ($tables as $table) {
382             fwrite($fp, "DROP TABLE IF EXISTS '{$table['name']}';\n");
383         }
384 
385         foreach ($tables as $table) {
386             fwrite($fp, $table['sql'] . ";\n");
387         }
388 
389         foreach ($tables as $table) {
390             $sql = "SELECT * FROM " . $table['name'];
391             $res = $this->query($sql);
392             while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
393                 $values = implode(',', array_map(function ($value) {
394                     if ($value === null) return 'NULL';
395                     return $this->pdo->quote($value);
396                 }, $row));
397                 fwrite($fp, "INSERT INTO '{$table['name']}' VALUES ({$values});\n");
398             }
399             $res->closeCursor();
400         }
401 
402         foreach ($indexes as $index) {
403             fwrite($fp, $index['sql'] . ";\n");
404         }
405         fclose($fp);
406         return $filename;
407     }
408 
409     // endregion
410 
411     // region migration handling
412 
413     /**
414      * Apply all pending migrations
415      *
416      * Each migration is executed in a transaction which is rolled back on failure
417      * Migrations can be files in the schema directory or event handlers
418      *
419      * @throws \Exception
420      */
421     protected function applyMigrations()
422     {
423         $currentVersion = $this->currentDbVersion();
424         $latestVersion = $this->latestDbVersion();
425 
426         if ($currentVersion === $latestVersion) return;
427 
428         for ($newVersion = $currentVersion + 1; $newVersion <= $latestVersion; $newVersion++) {
429             $data = [
430                 'dbname' => $this->dbname,
431                 'from' => $currentVersion,
432                 'to' => $newVersion,
433                 'file' => $this->getMigrationFile($newVersion),
434                 'sqlite' => $this->helper,
435                 'adapter' => $this,
436             ];
437             $event = new Event('PLUGIN_SQLITE_DATABASE_UPGRADE', $data);
438 
439             $this->pdo->beginTransaction();
440             try {
441                 if ($event->advise_before()) {
442                     // standard migration file
443                     $sql = Tools::SQLstring2array(file_get_contents($data['file']));
444                     foreach ($sql as $query) {
445                         $this->pdo->exec($query);
446                     }
447                 } elseif (!$event->result) {
448                     // advise before returned false, but the result was false
449                     throw new \PDOException('Plugin event did not signal success');
450                 }
451                 $this->setOpt('dbversion', $newVersion);
452                 $this->pdo->commit();
453                 $event->advise_after();
454             } catch (\Exception $e) {
455                 // something went wrong, rollback
456                 $this->pdo->rollBack();
457                 throw $e;
458             }
459         }
460 
461         // vacuum the database to free up unused space
462         $this->pdo->exec('VACUUM');
463     }
464 
465     /**
466      * Read the current version from the opt table
467      *
468      * The opt table is created here if not found
469      *
470      * @return int
471      * @throws \PDOException
472      */
473     protected function currentDbVersion()
474     {
475         try {
476             $version = $this->getOpt('dbversion', 0);
477             return (int)$version;
478         } catch (\PDOException $e) {
479             if (!preg_match('/no such table/', $e->getMessage())) {
480                 // if this is not a "no such table" error, there is something wrong see #80
481                 Logger::error(
482                     'SQLite: Could not read dbversion from opt table due to unexpected error',
483                     [
484                         'dbname' => $this->dbname,
485                         'exception' => get_class($e),
486                         'message' => $e->getMessage(),
487                         'code' => $e->getCode(),
488                     ],
489                     __FILE__,
490                     __LINE__
491                 );
492             }
493 
494             // add the opt table - if this fails too, let the exception bubble up
495             $sql = "CREATE TABLE IF NOT EXISTS opts (opt TEXT NOT NULL PRIMARY KEY, val NOT NULL DEFAULT '')";
496             $this->exec($sql);
497             return 0;
498         }
499     }
500 
501     /**
502      * Get the version this db should have
503      *
504      * @return int
505      * @throws \PDOException
506      */
507     protected function latestDbVersion()
508     {
509         if (!file_exists($this->schemadir . '/latest.version')) {
510             throw new \PDOException('No latest.version in schema dir');
511         }
512         return (int)trim(file_get_contents($this->schemadir . '/latest.version'));
513     }
514 
515     /**
516      * Get the migrartion file for the given version
517      *
518      * @param int $version
519      * @return string
520      */
521     protected function getMigrationFile($version)
522     {
523         return sprintf($this->schemadir . '/update%04d.sql', $version);
524     }
525     // endregion
526 }
527