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