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