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