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