xref: /plugin/sqlite/helper.php (revision a34ef33354f4df1030568693cc77a63fbac0f190) 
1<?php
2/**
3 * DokuWiki Plugin sqlite (Helper Component)
4 *
5 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
6 * @author  Andreas Gohr <gohr@cosmocode.de>
7 */
8
9// must be run within Dokuwiki
10if(!defined('DOKU_INC')) die();
11
12if(!defined('DOKU_LF')) define('DOKU_LF', "\n");
13if(!defined('DOKU_TAB')) define('DOKU_TAB', "\t");
14if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN', DOKU_INC.'lib/plugins/');
15
16if(!defined('DOKU_EXT_SQLITE')) define('DOKU_EXT_SQLITE', 'sqlite');
17if(!defined('DOKU_EXT_PDO')) define('DOKU_EXT_PDO', 'pdo');
18
19require_once(DOKU_PLUGIN.'sqlite/classes/adapter.php');
20
21class helper_plugin_sqlite extends DokuWiki_Plugin {
22    var $adapter = null;
23
24    public function getInfo() {
25        return confToHash(dirname(__FILE__).'plugin.info.txt');
26    }
27
28    /**
29     * Keep separate instances for every call to keep database connections
30     */
31    public function isSingleton() {
32        return false;
33    }
34
35    /**
36     * constructor
37     */
38    public function helper_plugin_sqlite() {
39
40        if(!$this->adapter) {
41            if($this->existsPDOSqlite()) {
42                require_once(DOKU_PLUGIN.'sqlite/classes/adapter_pdosqlite.php');
43                $this->adapter = new helper_plugin_sqlite_adapter_pdosqlite();
44            }
45        }
46
47        if(!$this->adapter) {
48            if($this->existsSqlite2()) {
49                require_once(DOKU_PLUGIN.'sqlite/classes/adapter_sqlite2.php');
50                $this->adapter = new helper_plugin_sqlite_adapter_sqlite2();
51            }
52        }
53
54        if(!$this->adapter) {
55            msg('SQLite & PDO SQLite support missing in this PHP install - plugin will not work', -1);
56        }
57    }
58
59    /**
60     * check availabilty of PHPs sqlite extension (for sqlite2 support)
61     */
62    public function existsSqlite2() {
63        if(!extension_loaded('sqlite')) {
64            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
65            if(function_exists('dl')) @dl($prefix.'sqlite.'.PHP_SHLIB_SUFFIX);
66        }
67
68        return function_exists('sqlite_open');
69    }
70
71    /**
72     * check availabilty of PHP PDO sqlite3
73     */
74    public function existsPDOSqlite() {
75        if(!extension_loaded('pdo_sqlite')) {
76            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
77            if(function_exists('dl')) @dl($prefix.'pdo_sqlite.'.PHP_SHLIB_SUFFIX);
78        }
79
80        if(class_exists('pdo')) {
81            foreach(PDO::getAvailableDrivers() as $driver) {
82                if($driver == 'sqlite') {
83                    return true;
84                }
85            }
86        }
87        return false;
88    }
89
90    /**
91     * Initializes and opens the database
92     *
93     * Needs to be called right after loading this helper plugin
94     *
95     * @param string $dbname
96     * @param string $updatedir - Database update infos
97     * @return bool
98     */
99    public function init($dbname, $updatedir) {
100
101        $init = null; // set by initdb()
102        if(!$this->adapter OR !$this->adapter->initdb($dbname, $init)) return false;
103
104        return $this->_updatedb($init, $updatedir);
105    }
106
107    /**
108     * Return the current Database Version
109     */
110    private function _currentDBversion() {
111        $sql = "SELECT val FROM opts WHERE opt = 'dbversion';";
112        $res = $this->query($sql);
113        if(!$res) return false;
114        $row = $this->res2row($res, 0);
115        return (int) $row['val'];
116    }
117
118    /**
119     * Update the database if needed
120     *
121     * @param bool   $init      - true if this is a new database to initialize
122     * @param string $updatedir - Database update infos
123     * @return bool
124     */
125    private function _updatedb($init, $updatedir) {
126        if($init) {
127
128            $current = 0;
129        } else {
130            $current = $this->_currentDBversion();
131            if(!$current) {
132                msg("SQLite: no DB version found. '".$this->adapter->getDbname()."' DB probably broken.", -1);
133                return false;
134            }
135        }
136
137        // in case of init, add versioning table
138        if($init) {
139            if(!$this->_runupdatefile(dirname(__FILE__).'/db.sql', 0)) {
140                msg("SQLite: '".$this->adapter->getDbname()."' database upgrade failed for version ", -1);
141                return false;
142            }
143        }
144
145        $latest = (int) trim(io_readFile($updatedir.'/latest.version'));
146
147        // all up to date?
148        if($current >= $latest) return true;
149        for($i = $current + 1; $i <= $latest; $i++) {
150            $file = sprintf($updatedir.'/update%04d.sql', $i);
151            if(file_exists($file)) {
152                if(!$this->_runupdatefile($file, $i)) {
153                    msg("SQLite: '".$this->adapter->getDbname()."' database upgrade failed for version ".$i, -1);
154                    return false;
155                }
156            }
157        }
158        return true;
159    }
160
161    /**
162     * Updates the database structure using the given file to
163     * the given version.
164     */
165    private function _runupdatefile($file, $version) {
166        $sql = io_readFile($file, false);
167
168        $sql = $this->SQLstring2array($sql);
169        array_unshift($sql, 'BEGIN TRANSACTION');
170        array_push($sql, "INSERT OR REPLACE INTO opts (val,opt) VALUES ($version,'dbversion')");
171        array_push($sql, "COMMIT TRANSACTION");
172
173        if(!$this->doTransaction($sql)) {
174            return false;
175        }
176        return ($version == $this->_currentDBversion());
177    }
178
179    /**
180     * Split sql queries on semicolons, unless when semicolons are quoted
181     *
182     * @param string $sql
183     * @return array sql queries
184     */
185    public function SQLstring2array($sql) {
186        return preg_split("/;(?=([^']*'[^']*')*[^']*$)/", $sql);
187    }
188
189    /**
190     * @param array $sql queries without terminating semicolon
191     * @param bool  $sqlpreparing
192     * @return bool
193     */
194    public function doTransaction($sql, $sqlpreparing = true) {
195        foreach($sql as $s) {
196            $s = preg_replace('!^\s*--.*$!m', '', $s);
197            $s = trim($s);
198            if(!$s) continue;
199
200            if($sqlpreparing) {
201                $res = $this->query("$s;");
202            } else {
203                $res = $this->adapter->executeQuery("$s;");
204            }
205            if($res === false) {
206                //TODO check rollback for sqlite PDO
207                if($this->adapter->getName() == DOKU_EXT_SQLITE) {
208                    $this->query('ROLLBACK TRANSACTION');
209                }
210                return false;
211            }
212        }
213        return true;
214    }
215
216    /**
217     * Dump db into a file in meta directory
218     *
219     */
220    public function dumpDatabase($dbname, $from = DOKU_EXT_SQLITE) {
221        global $conf;
222        $adapterDumpDb = null;
223        //connect to desired database
224        if($this->adapter->getName() == $from) {
225            $adapterDumpDb =& $this->adapter;
226        } else {
227            if($from == DOKU_EXT_SQLITE) {
228                //TODO test connecting to sqlite2 database
229                if($this->existsSqlite2()) {
230                    require_once(DOKU_PLUGIN.'sqlite/classes/adapter_sqlite2.php');
231                    $adapterDumpDb = new helper_plugin_sqlite_adapter_sqlite2();
232                } else {
233                    msg('PHP Sqlite Extension(needed for sqlite2) not available, database "'.hsc($dbname).'" is not dumped to file.');
234                    return false;
235                }
236            }
237        }
238        if($adapterDumpDb === null) {
239            msg('No adapter loaded');
240            return false;
241        }
242        $init = false;
243        if(!$adapterDumpDb->initdb($dbname, $init)) {
244            msg('Opening database fails.', -1);
245            return false;
246        }
247
248        $res    = $adapterDumpDb->query(array("SELECT name,sql FROM sqlite_master WHERE type='table'"));
249        $tables = $adapterDumpDb->res2arr($res);
250
251        $filename = $conf['metadir'].'/dumpfile_'.$dbname.'.sql';
252        if($fp = fopen($filename, 'w')) {
253
254            fwrite($fp, 'BEGIN TRANSACTION;'."\n");
255
256            foreach($tables as $table) {
257
258                fwrite($fp, $table['sql'].";\n");
259
260                $sql = "SELECT * FROM ".$table['name'];
261                $res = $adapterDumpDb->query(array($sql));
262
263                while($row = $adapterDumpDb->res_fetch_array($res)) {
264
265                    $line = 'INSERT INTO '.$table['name'].' VALUES(';
266                    foreach($row as $no_entry => $entry) {
267                        if($no_entry !== 0) {
268                            $line .= ',';
269                        }
270
271                        if(is_null($entry)) {
272                            $line .= 'NULL';
273                        } elseif(!is_numeric($entry)) {
274                            $line .= $adapterDumpDb->quote_string($entry);
275                        } else {
276                            //TODO depending on locale extra leading zeros are truncated e.g 1.300 (thousand three hunderd)-> 1.3
277                            $line .= $entry;
278                        }
279                    }
280                    $line .= ');'."\n";
281
282                    fwrite($fp, $line);
283                }
284            }
285
286            $res     = $adapterDumpDb->query(array("SELECT name,sql FROM sqlite_master WHERE type='index'"));
287            $indexes = $adapterDumpDb->res2arr($res);
288            foreach($indexes as $index) {
289                fwrite($fp, $index['sql'].";\n");
290            }
291
292            fwrite($fp, 'COMMIT;'."\n");
293
294            fclose($fp);
295            return $filename;
296        } else {
297            msg('Dumping "'.hsc($dbname).'" has failed. Could not open '.$filename);
298            return false;
299        }
300    }
301
302    /**
303     * Read $dumpfile and try to add it to database.
304     * A existing database is backuped first as e.g. dbname.copy2.sqlite3
305     *
306     * @param string $dbname
307     * @param string $dumpfile
308     * @return bool true on succes
309     */
310    public function fillDatabaseFromDump($dbname, $dumpfile) {
311        global $conf;
312        //backup existing stuff
313        $dbf    = $conf['metadir'].'/'.$dbname;
314        $dbext  = $this->adapter->getFileextension();
315        $dbfile = $dbf.$dbext;
316        if(@file_exists($dbfile)) {
317
318            $i            = 0;
319            $backupdbfile = $dbfile;
320            do {
321                $i++;
322                $backupdbfile = $dbf.".copy$i".$dbext;
323            } while(@file_exists($backupdbfile));
324
325            io_rename($dbfile, $backupdbfile);
326        }
327
328        $init = false;
329        if(!$this->adapter->initdb($dbname, $init, $sqliteupgrade = true)) {
330            msg('Initialize db fails');
331            return false;
332        }
333
334        $sql = io_readFile($dumpfile, false);
335        $sql = $this->SQLstring2array($sql);
336
337        //skip preparing, because it interprets question marks as placeholders.
338        return $this->doTransaction($sql, $sqlpreparing = false);
339    }
340
341    /**
342     * Registers a User Defined Function for use in SQL statements
343     */
344    public function create_function($function_name, $callback, $num_args) {
345        $this->adapter->create_function($function_name, $callback, $num_args);
346    }
347
348    /**
349     * Execute a query with the given parameters.
350     *
351     * Takes care of escaping
352     *
353     * @internal param string $sql - the statement
354     * @internal param $arguments ...
355     * @return bool|\SQLiteResult
356     */
357    public function query() {
358        // get function arguments
359        $args = func_get_args();
360
361        return $this->adapter->query($args);
362    }
363
364    /**
365     * Join the given values and quote them for SQL insertion
366     */
367    public function quote_and_join($vals, $sep = ',') {
368        return $this->adapter->quote_and_join($vals, $sep);
369    }
370
371    /**
372     * Run sqlite_escape_string() on the given string and surround it
373     * with quotes
374     */
375    public function quote_string($string) {
376        return $this->adapter->quote_string($string);
377    }
378
379    /**
380     * Escape string for sql
381     */
382    public function escape_string($str) {
383        return $this->adapter->escape_string($str);
384    }
385
386    /**
387     * Returns a complete result set as array
388     */
389    public function res2arr($res, $assoc = true) {
390        return $this->adapter->res2arr($res, $assoc);
391    }
392
393    /**
394     * Return the wanted row from a given result set as
395     * associative array
396     */
397    public function res2row($res, $rownum = 0) {
398        return $this->adapter->res2row($res, $rownum);
399    }
400
401    /**
402     * Return the first value from the first row.
403     */
404    public function res2single($res) {
405        return $this->adapter->res2single($res);
406    }
407
408    /**
409     * fetch the next row as zero indexed array
410     */
411    public function res_fetch_array($res) {
412        return $this->adapter->res_fetch_array($res);
413    }
414
415    /**
416     * fetch the next row as assocative array
417     */
418    public function res_fetch_assoc($res) {
419        return $this->adapter->res_fetch_assoc($res);
420    }
421
422    /**
423     * Count the number of records in result
424     *
425     * This function is really inperformant in PDO and should be avoided!
426     */
427    public function res2count($res) {
428        return $this->adapter->res2count($res);
429    }
430
431    /**
432     * Count the number of records changed last time
433     */
434    public function countChanges($db, $res) {
435        return $this->adapter->countChanges($db, $res);
436    }
437
438}
439