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