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