xref: /plugin/sqlite/helper.php (revision 01be6b278e3652c448e852fbf47db28cd96aba89) 
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_EXT_SQLITE')) define('DOKU_EXT_SQLITE', 'sqlite');
13if(!defined('DOKU_EXT_PDO')) define('DOKU_EXT_PDO', 'pdo');
14if(!defined('DOKU_EXT_NULL')) define('DOKU_EXT_NULL', 'null');
15
16require_once(DOKU_PLUGIN.'sqlite/classes/adapter.php');
17
18class helper_plugin_sqlite extends DokuWiki_Plugin {
19    var $adapter = null;
20
21    public function getAdapter() {
22        return $this->adapter;
23    }
24
25    /**
26     * Keep separate instances for every call to keep database connections
27     */
28    public function isSingleton() {
29        return false;
30    }
31
32    /**
33     * constructor
34     */
35    public function helper_plugin_sqlite() {
36
37        if(!$this->adapter) {
38            if($this->existsPDOSqlite() && empty($_ENV['SQLITE_SKIP_PDO'])) {
39                require_once(DOKU_PLUGIN.'sqlite/classes/adapter_pdosqlite.php');
40                $this->adapter = new helper_plugin_sqlite_adapter_pdosqlite();
41            }
42        }
43
44        if(!$this->adapter) {
45            if($this->existsSqlite2()) {
46                require_once(DOKU_PLUGIN.'sqlite/classes/adapter_sqlite2.php');
47                $this->adapter = new helper_plugin_sqlite_adapter_sqlite2();
48            }
49        }
50
51        if(!$this->adapter) {
52            msg('SQLite & PDO SQLite support missing in this PHP install - plugin will not work', -1);
53        }
54    }
55
56    /**
57     * check availabilty of PHPs sqlite extension (for sqlite2 support)
58     */
59    public function existsSqlite2() {
60        if(!extension_loaded('sqlite')) {
61            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
62            if(function_exists('dl')) @dl($prefix.'sqlite.'.PHP_SHLIB_SUFFIX);
63        }
64
65        return function_exists('sqlite_open');
66    }
67
68    /**
69     * check availabilty of PHP PDO sqlite3
70     */
71    public function existsPDOSqlite() {
72        if(!extension_loaded('pdo_sqlite')) {
73            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
74            if(function_exists('dl')) @dl($prefix.'pdo_sqlite.'.PHP_SHLIB_SUFFIX);
75        }
76
77        if(class_exists('pdo')) {
78            foreach(PDO::getAvailableDrivers() as $driver) {
79                if($driver == 'sqlite') {
80                    return true;
81                }
82            }
83        }
84        return false;
85    }
86
87    /**
88     * Initializes and opens the database
89     *
90     * Needs to be called right after loading this helper plugin
91     *
92     * @param string $dbname
93     * @param string $updatedir - Database update infos
94     * @return bool
95     */
96    public function init($dbname, $updatedir) {
97        $init = null; // set by initdb()
98        if( !$this->adapter or !$this->adapter->initdb($dbname, $init) ){
99            require_once(DOKU_PLUGIN.'sqlite/classes/adapter_null.php');
100            $this->adapter = new helper_plugin_sqlite_adapter_null();
101            return false;
102        }
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        $statements = array();
187        $len = strlen($sql);
188
189        // Simple state machine to "parse" sql into single statements
190        $in_str = false;
191        $in_com = false;
192        $statement = '';
193        for($i=0; $i<$len; $i++){
194            $prev = $i ? $sql{$i-1} : "\n";
195            $char = $sql{$i};
196            $next = $sql{$i+1};
197
198            // in comment? ignore everything until line end
199            if($in_com){
200                if($char == "\n"){
201                    $in_com = false;
202                }
203                continue;
204            }
205
206            // handle strings
207            if($in_str){
208                if($char == "'"){
209                    if($next == "'"){
210                        // current char is an escape for the next
211                        $statement .= $char . $next;
212                        $i++;
213                        continue;
214                    }else{
215                        // end of string
216                        $statement .= $char;
217                        $in_str = false;
218                        continue;
219                    }
220                }
221                // still in string
222                $statement .= $char;
223                continue;
224            }
225
226            // new comment?
227            if($char == '-' && $next == '-' && $prev == "\n"){
228                $in_com = true;
229                continue;
230            }
231
232            // new string?
233            if($char == "'"){
234                $in_str = true;
235                $statement .= $char;
236                continue;
237            }
238
239            // the real delimiter
240            if($char == ';'){
241                $statements[] = trim($statement);
242                $statement = '';
243                continue;
244            }
245
246            // some standard query stuff
247            $statement .= $char;
248        }
249        if($statement) $statements[] = trim($statement);
250
251        return $statements;
252    }
253
254    /**
255     * @param array $sql queries without terminating semicolon
256     * @param bool  $sqlpreparing
257     * @return bool
258     */
259    public function doTransaction($sql, $sqlpreparing = true) {
260        foreach($sql as $s) {
261            $s = preg_replace('!^\s*--.*$!m', '', $s);
262            $s = trim($s);
263            if(!$s) continue;
264
265            if($sqlpreparing) {
266                $res = $this->query("$s;");
267            } else {
268                $res = $this->adapter->executeQuery("$s;");
269            }
270            if($res === false) {
271                //TODO check rollback for sqlite PDO
272                if($this->adapter->getName() == DOKU_EXT_SQLITE) {
273                    $this->query('ROLLBACK TRANSACTION');
274                }
275                return false;
276            }
277        }
278        return true;
279    }
280
281    /**
282     * Dump db into a file in meta directory
283     *
284     */
285    public function dumpDatabase($dbname, $from = DOKU_EXT_SQLITE) {
286        global $conf;
287        $adapterDumpDb = null;
288        //connect to desired database
289        if($this->adapter->getName() == $from) {
290            $adapterDumpDb =& $this->adapter;
291        } else {
292            if($from == DOKU_EXT_SQLITE) {
293                //TODO test connecting to sqlite2 database
294                if($this->existsSqlite2()) {
295                    require_once(DOKU_PLUGIN.'sqlite/classes/adapter_sqlite2.php');
296                    $adapterDumpDb = new helper_plugin_sqlite_adapter_sqlite2();
297                } else {
298                    msg('PHP Sqlite Extension(needed for sqlite2) not available, database "'.hsc($dbname).'" is not dumped to file.');
299                    return false;
300                }
301            }
302        }
303        if($adapterDumpDb === null) {
304            msg('No adapter loaded');
305            return false;
306        }
307        $init = false;
308        if(!$adapterDumpDb->initdb($dbname, $init)) {
309            msg('Opening database fails.', -1);
310            return false;
311        }
312
313        $res    = $adapterDumpDb->query(array("SELECT name,sql FROM sqlite_master WHERE type='table'"));
314        $tables = $adapterDumpDb->res2arr($res);
315
316        $filename = $conf['metadir'].'/dumpfile_'.$dbname.'.sql';
317        if($fp = fopen($filename, 'w')) {
318
319            fwrite($fp, 'BEGIN TRANSACTION;'."\n");
320
321            foreach($tables as $table) {
322
323                fwrite($fp, $table['sql'].";\n");
324
325                $sql = "SELECT * FROM ".$table['name'];
326                $res = $adapterDumpDb->query(array($sql));
327
328                while($row = $adapterDumpDb->res_fetch_array($res)) {
329
330                    $line = 'INSERT INTO '.$table['name'].' VALUES(';
331                    foreach($row as $no_entry => $entry) {
332                        if($no_entry !== 0) {
333                            $line .= ',';
334                        }
335
336                        if(is_null($entry)) {
337                            $line .= 'NULL';
338                        } elseif(!is_numeric($entry)) {
339                            $line .= $adapterDumpDb->quote_string($entry);
340                        } else {
341                            //TODO depending on locale extra leading zeros are truncated e.g 1.300 (thousand three hunderd)-> 1.3
342                            $line .= $entry;
343                        }
344                    }
345                    $line .= ');'."\n";
346
347                    fwrite($fp, $line);
348                }
349            }
350
351            $res     = $adapterDumpDb->query(array("SELECT name,sql FROM sqlite_master WHERE type='index'"));
352            $indexes = $adapterDumpDb->res2arr($res);
353            foreach($indexes as $index) {
354                fwrite($fp, $index['sql'].";\n");
355            }
356
357            fwrite($fp, 'COMMIT;'."\n");
358
359            fclose($fp);
360            return $filename;
361        } else {
362            msg('Dumping "'.hsc($dbname).'" has failed. Could not open '.$filename);
363            return false;
364        }
365    }
366
367    /**
368     * Read $dumpfile and try to add it to database.
369     * A existing database is backuped first as e.g. dbname.copy2.sqlite3
370     *
371     * @param string $dbname
372     * @param string $dumpfile
373     * @return bool true on succes
374     */
375    public function fillDatabaseFromDump($dbname, $dumpfile) {
376        global $conf;
377        //backup existing stuff
378        $dbf    = $conf['metadir'].'/'.$dbname;
379        $dbext  = $this->adapter->getFileextension();
380        $dbfile = $dbf.$dbext;
381        if(@file_exists($dbfile)) {
382
383            $i            = 0;
384            $backupdbfile = $dbfile;
385            do {
386                $i++;
387                $backupdbfile = $dbf.".copy$i".$dbext;
388            } while(@file_exists($backupdbfile));
389
390            io_rename($dbfile, $backupdbfile);
391        }
392
393        $init = false;
394        if(!$this->adapter->initdb($dbname, $init, $sqliteupgrade = true)) {
395            msg('Initialize db fails');
396            return false;
397        }
398
399        $sql = io_readFile($dumpfile, false);
400        $sql = $this->SQLstring2array($sql);
401
402        //skip preparing, because it interprets question marks as placeholders.
403        return $this->doTransaction($sql, $sqlpreparing = false);
404    }
405
406    /**
407     * Registers a User Defined Function for use in SQL statements
408     */
409    public function create_function($function_name, $callback, $num_args) {
410        $this->adapter->create_function($function_name, $callback, $num_args);
411    }
412
413    /**
414     * Execute a query with the given parameters.
415     *
416     * Takes care of escaping
417     *
418     * @internal param string $sql - the statement
419     * @internal param $arguments ...
420     * @return bool|\SQLiteResult
421     */
422    public function query() {
423        // get function arguments
424        $args = func_get_args();
425
426        return $this->adapter->query($args);
427    }
428
429    /**
430     * Join the given values and quote them for SQL insertion
431     */
432    public function quote_and_join($vals, $sep = ',') {
433        return $this->adapter->quote_and_join($vals, $sep);
434    }
435
436    /**
437     * Run sqlite_escape_string() on the given string and surround it
438     * with quotes
439     */
440    public function quote_string($string) {
441        return $this->adapter->quote_string($string);
442    }
443
444    /**
445     * Escape string for sql
446     */
447    public function escape_string($str) {
448        return $this->adapter->escape_string($str);
449    }
450
451    /**
452     * Closes the result set (and it's cursors)
453     *
454     * If you're doing SELECT queries inside a TRANSACTION, be sure to call this
455     * function on all your results sets, before COMMITing the transaction.
456     *
457     * @param $res
458     * @return bool
459     */
460    public function res_close($res){
461        return $this->adapter->res_close($res);
462    }
463
464    /**
465     * Returns a complete result set as array
466     */
467    public function res2arr($res, $assoc = true) {
468        return $this->adapter->res2arr($res, $assoc);
469    }
470
471    /**
472     * Return the wanted row from a given result set as
473     * associative array
474     */
475    public function res2row($res, $rownum = 0) {
476        return $this->adapter->res2row($res, $rownum);
477    }
478
479    /**
480     * Return the first value from the next row.
481     */
482    public function res2single($res) {
483        return $this->adapter->res2single($res);
484    }
485
486    /**
487     * fetch the next row as zero indexed array
488     */
489    public function res_fetch_array($res) {
490        return $this->adapter->res_fetch_array($res);
491    }
492
493    /**
494     * fetch the next row as assocative array
495     */
496    public function res_fetch_assoc($res) {
497        return $this->adapter->res_fetch_assoc($res);
498    }
499
500    /**
501     * Count the number of records in result
502     *
503     * This function is really inperformant in PDO and should be avoided!
504     */
505    public function res2count($res) {
506        return $this->adapter->res2count($res);
507    }
508
509    /**
510     * Count the number of records changed last time
511     */
512    public function countChanges($res) {
513        return $this->adapter->countChanges($res);
514    }
515
516}
517