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