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