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