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