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