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