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