xref: /plugin/sqlite/SQLiteDB.php (revision aeccf717e96982614e71e0858e8ebf4e370518fa) 
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 pdo()
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        fwrite($fp, 'BEGIN TRANSACTION;' . "\n");
307
308        foreach ($tables as $table) {
309            fwrite($fp, $table['sql'] . ";\n"); // table definition
310
311            // data as INSERT statements
312            $sql = "SELECT * FROM " . $table['name'];
313            $res = $this->query($sql);
314            while ($row = $res->fetch(\PDO::FETCH_ASSOC)) {
315                $line = 'INSERT INTO ' . $table['name'] . ' VALUES(';
316                foreach ($row as $no_entry => $entry) {
317                    if ($no_entry !== 0) {
318                        $line .= ',';
319                    }
320
321                    if (is_null($entry)) {
322                        $line .= 'NULL';
323                    } elseif (!is_numeric($entry)) {
324                        $line .= $this->pdo->quote($entry);
325                    } else {
326                        // TODO depending on locale extra leading zeros
327                        // are truncated e.g 1.300 (thousand three hunderd)-> 1.3
328                        $line .= $entry;
329                    }
330                }
331                $line .= ');' . "\n";
332                fwrite($fp, $line);
333            }
334            $res->closeCursor();
335        }
336
337        // indexes
338        $indexes = $this->queryAll("SELECT name,sql FROM sqlite_master WHERE type='index'");
339        foreach ($indexes as $index) {
340            fwrite($fp, $index['sql'] . ";\n");
341        }
342        fwrite($fp, 'COMMIT;' . "\n");
343        fclose($fp);
344        return $filename;
345    }
346
347    // endregion
348
349    // region migration handling
350
351    /**
352     * Apply all pending migrations
353     *
354     * Each migration is executed in a transaction which is rolled back on failure
355     * Migrations can be files in the schema directory or event handlers
356     *
357     * @throws \Exception
358     */
359    protected function applyMigrations()
360    {
361        $currentVersion = $this->currentDbVersion();
362        $latestVersion = $this->latestDbVersion();
363
364        for ($newVersion = $currentVersion + 1; $newVersion <= $latestVersion; $newVersion++) {
365            $data = [
366                'dbname' => $this->dbname,
367                'from' => $currentVersion,
368                'to' => $newVersion,
369                'file' => $this->getMigrationFile($newVersion),
370                'sqlite' => $this->helper,
371                'adapter' => $this,
372            ];
373            $event = new \Doku_Event('PLUGIN_SQLITE_DATABASE_UPGRADE', $data);
374
375            $this->pdo->beginTransaction();
376            try {
377                if ($event->advise_before()) {
378                    // standard migration file
379                    $sql = file_get_contents($data['file']);
380                    $this->pdo->exec($sql);
381                } else {
382                    if (!$event->result) {
383                        // advise before returned false, but the result was false
384                        throw new \PDOException('Plugin event did not signal success');
385                    }
386                }
387                $this->setOpt('dbversion', $newVersion);
388                $this->pdo->commit();
389                $event->advise_after();
390            } catch(\Exception $e) {
391                // something went wrong, rollback
392                $this->pdo->rollBack();
393                throw $e;
394            }
395        }
396
397        // vacuum the database to free up unused space
398        $this->pdo->exec('VACUUM');
399    }
400
401    /**
402     * Read the current version from the opt table
403     *
404     * The opt table is created here if not found
405     *
406     * @return int
407     * @throws \PDOException
408     */
409    protected function currentDbVersion()
410    {
411        try {
412            $version = $this->getOpt('dbversion', 0);
413            return (int) $version;
414        } catch(\PDOException $ignored) {
415            // add the opt table - if this fails too, let the exception bubble up
416            $sql = "CREATE TABLE IF NOT EXISTS opts (opt TEXT NOT NULL PRIMARY KEY, val NOT NULL DEFAULT '')";
417            $this->exec($sql);
418            $this->setOpt('dbversion', 0);
419            return 0;
420        }
421    }
422
423    /**
424     * Get the version this db should have
425     *
426     * @return int
427     * @throws \PDOException
428     */
429    protected function latestDbVersion()
430    {
431        if (!file_exists($this->schemadir . '/latest.version')) {
432            throw new \PDOException('No latest.version in schema dir');
433        }
434        return (int) trim(file_get_contents($this->schemadir . '/latest.version'));
435    }
436
437    /**
438     * Get the migrartion file for the given version
439     *
440     * @param int $version
441     * @return string
442     */
443    protected function getMigrationFile($version)
444    {
445        return sprintf($this->schemadir . '/update%04d.sql', $version);
446    }
447    // endregion
448}
449