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