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