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