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