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