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