xref: /plugin/sqlite/helper.php (revision 029a3e39a966b7bf7a50e25dc04344388cbe25bc) 
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
18/**
19 * Class helper_plugin_sqlite
20 */
21class helper_plugin_sqlite extends DokuWiki_Plugin {
22    /** @var helper_plugin_sqlite_adapter_pdosqlite|helper_plugin_sqlite_adapter|\helper_plugin_sqlite_adapter_sqlite2|null  */
23    protected $adapter = null;
24
25    /**
26     * @return helper_plugin_sqlite_adapter_pdosqlite|helper_plugin_sqlite_adapter|\helper_plugin_sqlite_adapter_sqlite2|null
27     */
28    public function getAdapter() {
29        return $this->adapter;
30    }
31
32    /**
33     * Keep separate instances for every call to keep database connections
34     */
35    public function isSingleton() {
36        return false;
37    }
38
39    /**
40     * constructor
41     */
42    public function __construct() {
43
44        if(!$this->adapter) {
45            if($this->existsPDOSqlite() && empty($_ENV['SQLITE_SKIP_PDO'])) {
46                require_once(DOKU_PLUGIN.'sqlite/classes/adapter_pdosqlite.php');
47                $this->adapter = new helper_plugin_sqlite_adapter_pdosqlite();
48            }
49        }
50
51        if(!$this->adapter) {
52            if($this->existsSqlite2()) {
53                require_once(DOKU_PLUGIN.'sqlite/classes/adapter_sqlite2.php');
54                $this->adapter = new helper_plugin_sqlite_adapter_sqlite2();
55            }
56        }
57
58        if(!$this->adapter) {
59            msg('SQLite & PDO SQLite support missing in this PHP install - plugin will not work', -1);
60        }
61    }
62
63    /**
64     * check availabilty of PHPs sqlite extension (for sqlite2 support)
65     */
66    public function existsSqlite2() {
67        if(!extension_loaded('sqlite')) {
68            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
69            if(function_exists('dl')) @dl($prefix.'sqlite.'.PHP_SHLIB_SUFFIX);
70        }
71
72        return function_exists('sqlite_open');
73    }
74
75    /**
76     * check availabilty of PHP PDO sqlite3
77     */
78    public function existsPDOSqlite() {
79        if(!extension_loaded('pdo_sqlite')) {
80            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
81            if(function_exists('dl')) @dl($prefix.'pdo_sqlite.'.PHP_SHLIB_SUFFIX);
82        }
83
84        if(class_exists('pdo')) {
85            foreach(PDO::getAvailableDrivers() as $driver) {
86                if($driver == 'sqlite') {
87                    return true;
88                }
89            }
90        }
91        return false;
92    }
93
94    /**
95     * Initializes and opens the database
96     *
97     * Needs to be called right after loading this helper plugin
98     *
99     * @param string $dbname
100     * @param string $updatedir - Database update infos
101     * @return bool
102     */
103    public function init($dbname, $updatedir) {
104        $init = null; // set by initdb()
105        if( !$this->adapter or !$this->adapter->initdb($dbname, $init) ){
106            require_once(DOKU_PLUGIN.'sqlite/classes/adapter_null.php');
107            $this->adapter = new helper_plugin_sqlite_adapter_null();
108            return false;
109        }
110
111        $this->create_function('GETACCESSLEVEL', array($this, '_getAccessLevel'), 1);
112        $this->create_function('PAGEEXISTS', array($this, '_pageexists'), 1);
113        $this->create_function('REGEXP', array($this, '_regexp'), 2);
114
115        return $this->_updatedb($init, $updatedir);
116    }
117
118    /**
119     * Return the current Database Version
120     */
121    private function _currentDBversion() {
122        $sql = "SELECT val FROM opts WHERE opt = 'dbversion';";
123        $res = $this->query($sql);
124        if(!$res) return false;
125        $row = $this->res2row($res, 0);
126        return (int) $row['val'];
127    }
128
129    /**
130     * Update the database if needed
131     *
132     * @param bool   $init      - true if this is a new database to initialize
133     * @param string $updatedir - Database update infos
134     * @return bool
135     */
136    private function _updatedb($init, $updatedir) {
137        if($init) {
138
139            $current = 0;
140        } else {
141            $current = $this->_currentDBversion();
142            if(!$current) {
143                msg("SQLite: no DB version found. '".$this->adapter->getDbname()."' DB probably broken.", -1);
144                return false;
145            }
146        }
147
148        // in case of init, add versioning table
149        if($init) {
150            if(!$this->_runupdatefile(dirname(__FILE__).'/db.sql', 0)) {
151                msg("SQLite: '".$this->adapter->getDbname()."' database upgrade failed for version ", -1);
152                return false;
153            }
154        }
155
156        $latest = (int) trim(io_readFile($updatedir.'/latest.version'));
157
158        // all up to date?
159        if($current >= $latest) return true;
160        for($i = $current + 1; $i <= $latest; $i++) {
161            $file = sprintf($updatedir.'/update%04d.sql', $i);
162            if(file_exists($file)) {
163                if(!$this->_runupdatefile($file, $i)) {
164                    msg("SQLite: '".$this->adapter->getDbname()."' database upgrade failed for version ".$i, -1);
165                    return false;
166                }
167            } else {
168                msg("SQLite: update file $file not found, skipped.", -1);
169            }
170        }
171        return true;
172    }
173
174    /**
175     * Updates the database structure using the given file to
176     * the given version.
177     */
178    private function _runupdatefile($file, $version) {
179        if(!file_exists($file)) {
180            msg("SQLite: Failed to find DB update file $file");
181            return false;
182        }
183        $sql = io_readFile($file, false);
184
185        $sql = $this->SQLstring2array($sql);
186        array_unshift($sql, 'BEGIN TRANSACTION');
187        array_push($sql, "INSERT OR REPLACE INTO opts (val,opt) VALUES ($version,'dbversion')");
188        array_push($sql, "COMMIT TRANSACTION");
189
190        if(!$this->doTransaction($sql)) {
191            return false;
192        }
193        return ($version == $this->_currentDBversion());
194    }
195
196    /**
197     * Callback checks the permissions for the current user
198     *
199     * This function is registered as a SQL function named GETACCESSLEVEL
200     *
201     * @param  string $pageid page ID (needs to be resolved and cleaned)
202     * @return int permission level
203     */
204    public function _getAccessLevel($pageid) {
205        static $aclcache = array();
206
207        if(isset($aclcache[$pageid])) {
208            return $aclcache[$pageid];
209        }
210
211        if(isHiddenPage($pageid)) {
212            $acl = AUTH_NONE;
213        } else {
214            $acl = auth_quickaclcheck($pageid);
215        }
216        $aclcache[$pageid] = $acl;
217        return $acl;
218    }
219
220    /**
221     * Wrapper around page_exists() with static caching
222     *
223     * This function is registered as a SQL function named PAGEEXISTS
224     *
225     * @param string $pageid
226     * @return int 0|1
227     */
228    public function _pageexists($pageid) {
229        static $cache = array();
230        if(!isset($cache[$pageid])) {
231            $cache[$pageid] = page_exists($pageid);
232
233        }
234        return (int) $cache[$pageid];
235    }
236
237    /**
238     * Match a regular expression against a value
239     *
240     * This function is registered as a SQL function named REGEXP
241     *
242     * @param string $regexp
243     * @param string $value
244     * @return bool
245     */
246    public function _regexp($regexp, $value) {
247        $regexp = addcslashes($regexp, '/');
248        return (bool) preg_match('/'.$regexp.'/u', $value);
249    }
250
251    /**
252     * Split sql queries on semicolons, unless when semicolons are quoted
253     *
254     * @param string $sql
255     * @return array sql queries
256     */
257    public function SQLstring2array($sql) {
258        $statements = array();
259        $len = strlen($sql);
260
261        // Simple state machine to "parse" sql into single statements
262        $in_str = false;
263        $in_com = false;
264        $statement = '';
265        for($i=0; $i<$len; $i++){
266            $prev = $i ? $sql{$i-1} : "\n";
267            $char = $sql{$i};
268            $next = $sql{$i+1};
269
270            // in comment? ignore everything until line end
271            if($in_com){
272                if($char == "\n"){
273                    $in_com = false;
274                }
275                continue;
276            }
277
278            // handle strings
279            if($in_str){
280                if($char == "'"){
281                    if($next == "'"){
282                        // current char is an escape for the next
283                        $statement .= $char . $next;
284                        $i++;
285                        continue;
286                    }else{
287                        // end of string
288                        $statement .= $char;
289                        $in_str = false;
290                        continue;
291                    }
292                }
293                // still in string
294                $statement .= $char;
295                continue;
296            }
297
298            // new comment?
299            if($char == '-' && $next == '-' && $prev == "\n"){
300                $in_com = true;
301                continue;
302            }
303
304            // new string?
305            if($char == "'"){
306                $in_str = true;
307                $statement .= $char;
308                continue;
309            }
310
311            // the real delimiter
312            if($char == ';'){
313                $statements[] = trim($statement);
314                $statement = '';
315                continue;
316            }
317
318            // some standard query stuff
319            $statement .= $char;
320        }
321        if($statement) $statements[] = trim($statement);
322
323        return $statements;
324    }
325
326    /**
327     * @param array $sql queries without terminating semicolon
328     * @param bool  $sqlpreparing
329     * @return bool
330     */
331    public function doTransaction($sql, $sqlpreparing = true) {
332        foreach($sql as $s) {
333            $s = preg_replace('!^\s*--.*$!m', '', $s);
334            $s = trim($s);
335            if(!$s) continue;
336
337            if($sqlpreparing) {
338                $res = $this->query("$s;");
339            } else {
340                $res = $this->adapter->executeQuery("$s;");
341            }
342            if($res === false) {
343                //TODO check rollback for sqlite PDO
344                if($this->adapter->getName() == DOKU_EXT_SQLITE) {
345                    $this->query('ROLLBACK TRANSACTION');
346                } else {
347                    $err = $this->adapter->getDb()->errorInfo();
348                    msg($err[0].' '.$err[1].' '.$err[2].':<br /><pre>'.hsc($s).'</pre>', -1);
349                }
350                return false;
351            }
352        }
353        return true;
354    }
355
356    /**
357     * Dump db into a file in meta directory
358     *
359     */
360    public function dumpDatabase($dbname, $from = DOKU_EXT_SQLITE) {
361        global $conf;
362        $adapterDumpDb = null;
363        //connect to desired database
364        if($this->adapter->getName() == $from) {
365            $adapterDumpDb =& $this->adapter;
366        } else {
367            if($from == DOKU_EXT_SQLITE) {
368                //TODO test connecting to sqlite2 database
369                if($this->existsSqlite2()) {
370                    require_once(DOKU_PLUGIN.'sqlite/classes/adapter_sqlite2.php');
371                    $adapterDumpDb = new helper_plugin_sqlite_adapter_sqlite2();
372                } else {
373                    msg('PHP Sqlite Extension(needed for sqlite2) not available, database "'.hsc($dbname).'" is not dumped to file.');
374                    return false;
375                }
376            }
377        }
378        if($adapterDumpDb === null) {
379            msg('No adapter loaded');
380            return false;
381        }
382        $init = false;
383        if(!$adapterDumpDb->initdb($dbname, $init)) {
384            msg('Opening database fails.', -1);
385            return false;
386        }
387
388        $res    = $adapterDumpDb->query(array("SELECT name,sql FROM sqlite_master WHERE type='table'"));
389        $tables = $adapterDumpDb->res2arr($res);
390
391        $filename = $conf['metadir'].'/dumpfile_'.$dbname.'.sql';
392        if($fp = fopen($filename, 'w')) {
393
394            fwrite($fp, 'BEGIN TRANSACTION;'."\n");
395
396            foreach($tables as $table) {
397
398                fwrite($fp, $table['sql'].";\n");
399
400                $sql = "SELECT * FROM ".$table['name'];
401                $res = $adapterDumpDb->query(array($sql));
402
403                while($row = $adapterDumpDb->res_fetch_array($res)) {
404
405                    $line = 'INSERT INTO '.$table['name'].' VALUES(';
406                    foreach($row as $no_entry => $entry) {
407                        if($no_entry !== 0) {
408                            $line .= ',';
409                        }
410
411                        if(is_null($entry)) {
412                            $line .= 'NULL';
413                        } elseif(!is_numeric($entry)) {
414                            $line .= $adapterDumpDb->quote_string($entry);
415                        } else {
416                            //TODO depending on locale extra leading zeros are truncated e.g 1.300 (thousand three hunderd)-> 1.3
417                            $line .= $entry;
418                        }
419                    }
420                    $line .= ');'."\n";
421
422                    fwrite($fp, $line);
423                }
424            }
425
426            $res     = $adapterDumpDb->query(array("SELECT name,sql FROM sqlite_master WHERE type='index'"));
427            $indexes = $adapterDumpDb->res2arr($res);
428            foreach($indexes as $index) {
429                fwrite($fp, $index['sql'].";\n");
430            }
431
432            fwrite($fp, 'COMMIT;'."\n");
433
434            fclose($fp);
435            return $filename;
436        } else {
437            msg('Dumping "'.hsc($dbname).'" has failed. Could not open '.$filename);
438            return false;
439        }
440    }
441
442    /**
443     * Read $dumpfile and try to add it to database.
444     * A existing database is backuped first as e.g. dbname.copy2.sqlite3
445     *
446     * @param string $dbname
447     * @param string $dumpfile
448     * @return bool true on succes
449     */
450    public function fillDatabaseFromDump($dbname, $dumpfile) {
451        global $conf;
452        //backup existing stuff
453        $dbf    = $conf['metadir'].'/'.$dbname;
454        $dbext  = $this->adapter->getFileextension();
455        $dbfile = $dbf.$dbext;
456        if(@file_exists($dbfile)) {
457
458            $i            = 0;
459            $backupdbfile = $dbfile;
460            do {
461                $i++;
462                $backupdbfile = $dbf.".copy$i".$dbext;
463            } while(@file_exists($backupdbfile));
464
465            io_rename($dbfile, $backupdbfile);
466        }
467
468        $init = false;
469        if(!$this->adapter->initdb($dbname, $init, $sqliteupgrade = true)) {
470            msg('Initialize db fails');
471            return false;
472        }
473
474        $sql = io_readFile($dumpfile, false);
475        $sql = $this->SQLstring2array($sql);
476
477        //skip preparing, because it interprets question marks as placeholders.
478        return $this->doTransaction($sql, $sqlpreparing = false);
479    }
480
481    /**
482     * Registers a User Defined Function for use in SQL statements
483     */
484    public function create_function($function_name, $callback, $num_args) {
485        $this->adapter->create_function($function_name, $callback, $num_args);
486    }
487
488    /**
489     * Convenience function to run an INSERT OR REPLACE operation
490     *
491     * The function takes a key-value array with the column names in the key and the actual value in the value,
492     * build the appropriate query and executes it.
493     *
494     * @param string $table the table the entry should be saved to (will not be escaped)
495     * @param array $entry A simple key-value pair array (only values will be escaped)
496     * @return bool|SQLiteResult
497     */
498    public function storeEntry($table, $entry) {
499        $keys = join(',', array_keys($entry));
500        $vals = join(',', array_fill(0,count($entry),'?'));
501
502        $sql = "INSERT INTO $table ($keys) VALUES ($vals)";
503        return $this->query($sql, array_values($entry));
504    }
505
506
507    /**
508     * Execute a query with the given parameters.
509     *
510     * Takes care of escaping
511     *
512     *
513     * @param string ...$args - the arguments of query(), the first is the sql and others are values
514     * @return bool|\SQLiteResult
515     */
516    public function query() {
517        // get function arguments
518        $args = func_get_args();
519
520        return $this->adapter->query($args);
521    }
522
523    /**
524     * Join the given values and quote them for SQL insertion
525     */
526    public function quote_and_join($vals, $sep = ',') {
527        return $this->adapter->quote_and_join($vals, $sep);
528    }
529
530    /**
531     * Run sqlite_escape_string() on the given string and surround it
532     * with quotes
533     */
534    public function quote_string($string) {
535        return $this->adapter->quote_string($string);
536    }
537
538    /**
539     * Escape string for sql
540     */
541    public function escape_string($str) {
542        return $this->adapter->escape_string($str);
543    }
544
545    /**
546     * Closes the result set (and it's cursors)
547     *
548     * If you're doing SELECT queries inside a TRANSACTION, be sure to call this
549     * function on all your results sets, before COMMITing the transaction.
550     *
551     * Also required when not all rows of a result are fetched
552     *
553     * @param $res
554     * @return bool
555     */
556    public function res_close($res){
557        return $this->adapter->res_close($res);
558    }
559
560    /**
561     * Returns a complete result set as array
562     */
563    public function res2arr($res, $assoc = true) {
564        return $this->adapter->res2arr($res, $assoc);
565    }
566
567    /**
568     * Return the wanted row from a given result set as
569     * associative array
570     */
571    public function res2row($res, $rownum = 0) {
572        return $this->adapter->res2row($res, $rownum);
573    }
574
575    /**
576     * Return the first value from the next row.
577     */
578    public function res2single($res) {
579        return $this->adapter->res2single($res);
580    }
581
582    /**
583     * fetch the next row as zero indexed array
584     */
585    public function res_fetch_array($res) {
586        return $this->adapter->res_fetch_array($res);
587    }
588
589    /**
590     * fetch the next row as assocative array
591     */
592    public function res_fetch_assoc($res) {
593        return $this->adapter->res_fetch_assoc($res);
594    }
595
596    /**
597     * Count the number of records in result
598     *
599     * This function is really inperformant in PDO and should be avoided!
600     */
601    public function res2count($res) {
602        return $this->adapter->res2count($res);
603    }
604
605    /**
606     * Count the number of records changed last time
607     */
608    public function countChanges($res) {
609        return $this->adapter->countChanges($res);
610    }
611
612}
613