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