xref: /plugin/sqlite/helper.php (revision 24a03f6c940cb4ce9290fad2b3041a793b5ac769) 
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
16class helper_plugin_sqlite extends DokuWiki_Plugin {
17    var $db     = null;
18    var $dbname = '';
19
20    function getInfo() {
21        return confToHash(dirname(__FILE__).'plugin.info.txt');
22    }
23
24    /**
25     * constructor
26     */
27    function helper_plugin_sqlite(){
28        if (!extension_loaded('sqlite')) {
29            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
30            if(function_exists('dl')) @dl($prefix . 'sqlite.' . PHP_SHLIB_SUFFIX);
31        }
32
33        if(!function_exists('sqlite_open')){
34            msg('SQLite support missing in this PHP install - plugin will not work',-1);
35        }
36    }
37
38    /**
39     * Initializes and opens the database
40     *
41     * Needs to be called right after loading this helper plugin
42     */
43    function init($dbname,$updatedir){
44        global $conf;
45
46        // check for already open DB
47        if($this->db){
48            if($this->dbname == $dbname){
49                // db already open
50                return true;
51            }
52            // close other db
53            sqlite_close($this->db);
54            $this->db     = null;
55            $this->dbname = '';
56        }
57
58        $this->dbname = $dbname;
59        $dbfile = $conf['metadir'].'/'.$dbname.'.sqlite';
60        $init   = (!@file_exists($dbfile) || ((int) @filesize($dbfile)) < 3);
61
62        $error='';
63        $this->db = sqlite_open($dbfile, 0666, $error);
64        if(!$this->db){
65            msg("SQLite: failed to open SQLite ".$this->dbname." database ($error)",-1);
66            return false;
67        }
68
69        // register our custom aggregate function
70        sqlite_create_aggregate($this->db,'group_concat',
71                                array($this,'_sqlite_group_concat_step'),
72                                array($this,'_sqlite_group_concat_finalize'), 2);
73
74        $this->_updatedb($init,$updatedir);
75        return true;
76    }
77
78    /**
79     * Return the current Database Version
80     */
81    function _currentDBversion(){
82        $sql = "SELECT val FROM opts WHERE opt = 'dbversion';";
83        $res = $this->query($sql);
84        if(!$res) return false;
85        $row = $this->res2row($res,0);
86        return (int) $row['val'];
87    }
88    /**
89     * Update the database if needed
90     *
91     * @param bool   $init      - true if this is a new database to initialize
92     * @param string $updatedir - Database update infos
93     */
94    function _updatedb($init,$updatedir){
95        if($init){
96            $current = 0;
97        }else{
98            $current = $this->_currentDBversion();
99            if(!$current){
100                msg('SQLite: no DB version found. '.$this->dbname.' DB probably broken.',-1);
101                return false;
102            }
103        }
104
105        // in case of init, add versioning table
106        if($init){
107            if(!$this->_runupdatefile(dirname(__FILE__).'/db.sql',0)){
108                msg('SQLite: '.$this->dbname.' database initialization failed', -1);
109                return false;
110            }
111        }
112
113        $latest  = (int) trim(io_readFile($updatedir.'/latest.version'));
114
115        // all up to date?
116        if($current >= $latest) return true;
117        for($i=$current+1; $i<=$latest; $i++){
118            $file = sprintf($updatedir.'/update%04d.sql',$i);
119            if(file_exists($file)){
120                if(!$this->_runupdatefile($file,$i)){
121                    msg('SQLite: '.$this->dbname.' database upgrade failed for version '.$i, -1);
122
123
124                    return false;
125                }
126            }
127        }
128        return true;
129    }
130
131    /**
132     * Updates the database structure using the given file to
133     * the given version.
134     */
135    function _runupdatefile($file,$version){
136        $sql  = io_readFile($file,false);
137
138        $sql = explode(";",$sql);
139        array_unshift($sql,'BEGIN TRANSACTION');
140        array_push($sql,"INSERT OR REPLACE INTO opts (val,opt) VALUES ($version,'dbversion')");
141        array_push($sql,"COMMIT TRANSACTION");
142
143        foreach($sql as $s){
144            $s = preg_replace('!^\s*--.*$!m', '', $s);
145            $s = trim($s);
146            if(!$s) continue;
147
148
149            $res = $this->query("$s;");
150            if ($res === false) {
151                sqlite_query($this->db, 'ROLLBACK TRANSACTION');
152                return false;
153            }
154        }
155
156        return ($version == $this->_currentDBversion());
157    }
158
159    /**
160     * Emulate ALTER TABLE
161     *
162     * The ALTER TABLE syntax is parsed and then emulated using a
163     * temporary table
164     *
165     * @author <jon@jenseng.com>
166     * @link   http://code.jenseng.com/db/
167     * @author Andreas Gohr <gohr@cosmocode.de>
168     */
169    function _altertable($table,$alterdefs){
170
171        // load original table definition SQL
172        $result = $this->query("SELECT sql,name,type
173                                  FROM sqlite_master
174                                 WHERE tbl_name = '$table'
175                                   AND type = 'table'");
176
177        if(($result === false) || (sqlite_num_rows($result)<=0)){
178            msg("ALTER TABLE failed, no such table '".hsc($table)."'",-1);
179            return false;
180        }
181        $row = sqlite_fetch_array($result);
182
183        // prepare temporary table SQL
184        $tmpname = 't'.time();
185        $origsql = trim(preg_replace("/[\s]+/"," ",
186                        str_replace(",",", ",
187                        preg_replace('/\)$/',' )',
188                        preg_replace("/[\(]/","( ",$row['sql'],1)))));
189        $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'",$tmpname,$origsql,1)),6);
190        $createindexsql = array();
191
192        // load indexes to reapply later
193        $result = $this->query("SELECT sql,name,type
194                                  FROM sqlite_master
195                                 WHERE tbl_name = '$table'
196                                   AND type = 'index'");
197        if(!$result){
198            $indexes = array();
199        }else{
200            $indexes = $this->res2arr($result);
201        }
202
203
204        $i = 0;
205        $defs = preg_split("/[,]+/",$alterdefs,-1,PREG_SPLIT_NO_EMPTY);
206        $prevword = $table;
207        $oldcols = preg_split("/[,]+/",substr(trim($createtemptableSQL),strpos(trim($createtemptableSQL),'(')+1),-1,PREG_SPLIT_NO_EMPTY);
208        $newcols = array();
209
210        for($i=0;$i<count($oldcols);$i++){
211            $colparts = preg_split("/[\s]+/",$oldcols[$i],-1,PREG_SPLIT_NO_EMPTY);
212            $oldcols[$i] = $colparts[0];
213            $newcols[$colparts[0]] = $colparts[0];
214        }
215        $newcolumns = '';
216        $oldcolumns = '';
217        reset($newcols);
218        while(list($key,$val) = each($newcols)){
219            $newcolumns .= ($newcolumns?', ':'').$val;
220            $oldcolumns .= ($oldcolumns?', ':'').$key;
221        }
222        $copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table;
223        $dropoldsql = 'DROP TABLE '.$table;
224        $createtesttableSQL = $createtemptableSQL;
225
226        foreach($defs as $def){
227            $defparts = preg_split("/[\s]+/",$def,-1,PREG_SPLIT_NO_EMPTY);
228            $action = strtolower($defparts[0]);
229            switch($action){
230                case 'add':
231                    if(count($defparts) < 2){
232                        msg('ALTER TABLE: not enough arguments for ADD statement',-1);
233                        return false;
234                    }
235                    $createtesttableSQL = substr($createtesttableSQL,0,strlen($createtesttableSQL)-1).',';
236                    for($i=1;$i<count($defparts);$i++)
237                        $createtesttableSQL.=' '.$defparts[$i];
238                    $createtesttableSQL.=')';
239                    break;
240
241                case 'change':
242                    if(count($defparts) <= 3){
243                        msg('ALTER TABLE: near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').($defparts[2]?' '.$defparts[2]:'').'": syntax error',-1);
244                        return false;
245                    }
246
247                    if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
248                        if($newcols[$defparts[1]] != $defparts[1]){
249                            msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1);
250                            return false;
251                        }
252                        $newcols[$defparts[1]] = $defparts[2];
253                        $nextcommapos = strpos($createtesttableSQL,',',$severpos);
254                        $insertval = '';
255                        for($i=2;$i<count($defparts);$i++)
256                            $insertval.=' '.$defparts[$i];
257                        if($nextcommapos)
258                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos).$insertval.substr($createtesttableSQL,$nextcommapos);
259                        else
260                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1)).$insertval.')';
261                    } else {
262                        msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1);
263                        return false;
264                    }
265                    break;
266                case 'drop':
267                    if(count($defparts) < 2){
268                        msg('ALTER TABLE: near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').'": syntax error',-1);
269                        return false;
270                    }
271                    if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
272                        $nextcommapos = strpos($createtesttableSQL,',',$severpos);
273                        if($nextcommapos)
274                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos).substr($createtesttableSQL,$nextcommapos + 1);
275                        else
276                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1) - 1).')';
277                        unset($newcols[$defparts[1]]);
278                    }else{
279                        msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1);
280                        return false;
281                    }
282                    break;
283                default:
284                    msg('ALTER TABLE: near "'.$prevword.'": syntax error',-1);
285                    return false;
286            }
287            $prevword = $defparts[count($defparts)-1];
288        }
289
290        // this block of code generates a test table simply to verify that the
291        // columns specifed are valid in an sql statement
292        // this ensures that no reserved words are used as columns, for example
293        $res = $this->query($createtesttableSQL);
294        if($res === false) return false;
295
296        $droptempsql = 'DROP TABLE '.$tmpname;
297        $res = $this->query($droptempsql);
298        if($res === false) return false;
299
300
301        $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'".$tmpname."'",$table,$createtesttableSQL,1)),17);
302        $newcolumns = '';
303        $oldcolumns = '';
304        reset($newcols);
305        while(list($key,$val) = each($newcols)){
306            $newcolumns .= ($newcolumns?', ':'').$val;
307            $oldcolumns .= ($oldcolumns?', ':'').$key;
308        }
309
310        $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname;
311
312        $res = $this->query($createtemptableSQL); //create temp table
313        if($res === false) return false;
314        $res = $this->query($copytotempsql); //copy to table
315        if($res === false) return false;
316        $res = $this->query($dropoldsql); //drop old table
317        if($res === false) return false;
318
319        $res = $this->query($createnewtableSQL); //recreate original table
320        if($res === false) return false;
321        $res = $this->query($copytonewsql); //copy back to original table
322        if($res === false) return false;
323
324        foreach($indexes as $index){ // readd indexes
325            $res = $this->query($index['sql']);
326            if($res === false) return false;
327        }
328
329        $res = $this->query($droptempsql); //drop temp table
330        if($res === false) return false;
331
332        return $res; // return a valid resource
333    }
334
335    /**
336     * Execute a query with the given parameters.
337     *
338     * Takes care of escaping
339     *
340     * @param string $sql - the statement
341     * @param arguments...
342     */
343    function query(){
344        if(!$this->db) return false;
345
346        // get function arguments
347        $args = func_get_args();
348        $sql  = trim(array_shift($args));
349        $sql  = rtrim($sql,';');
350
351        if(!$sql){
352            msg('No SQL statement given',-1);
353            return false;
354        }
355
356        $argc = count($args);
357        if($argc > 0 && is_array($args[0])) {
358            $args = $args[0];
359            $argc = count($args);
360        }
361
362        // check number of arguments
363        if($argc < substr_count($sql,'?')){
364            msg('Not enough arguments passed for statement. '.
365                'Expected '.substr_count($sql,'?').' got '.
366                $argc.' - '.hsc($sql),-1);
367            return false;
368        }
369
370        // explode at wildcard, then join again
371        $parts = explode('?',$sql,$argc+1);
372        $args  = array_map(array($this,'quote_string'),$args);
373        $sql   = '';
374
375        while( ($part = array_shift($parts)) !== null ){
376            $sql .= $part;
377            $sql .= array_shift($args);
378        }
379
380        // intercept ALTER TABLE statements
381        if(preg_match('/^ALTER\s+TABLE\s+([\w\.]+)\s+(.*)/i',$sql,$match)){
382            return $this->_altertable($match[1],$match[2]);
383        }
384
385        // execute query
386        $err = '';
387        $res = @sqlite_query($this->db,$sql,SQLITE_ASSOC,$err);
388        if($err){
389            msg($err.':<br /><pre>'.hsc($sql).'</pre>',-1);
390            return false;
391        }elseif(!$res){
392            msg(sqlite_error_string(sqlite_last_error($this->db)).
393                ':<br /><pre>'.hsc($sql).'</pre>',-1);
394            return false;
395        }
396
397        return $res;
398    }
399
400    /**
401     * Returns a complete result set as array
402     */
403    function res2arr($res){
404        $data = array();
405        if(!sqlite_num_rows($res)) return $data;
406        sqlite_rewind($res);
407        while(($row = sqlite_fetch_array($res)) !== false){
408            $data[] = $row;
409        }
410        return $data;
411    }
412
413    /**
414     * Return the wanted row from a given result set as
415     * associative array
416     */
417    function res2row($res,$rownum=0){
418        if(!@sqlite_seek($res,$rownum)){
419            return false;
420        }
421        return sqlite_fetch_array($res);
422    }
423
424    /**
425     * Return the first value from the first row.
426     */
427    function res2single($res)
428    {
429      return sqlite_fetch_single($res);
430    }
431
432    /**
433     * Join the given values and quote them for SQL insertion
434     */
435    function quote_and_join($vals,$sep=',') {
436        $vals = array_map(array('helper_plugin_sqlite','quote_string'),$vals);
437        return join($sep,$vals);
438    }
439
440    /**
441     * Run sqlite_escape_string() on the given string and surround it
442     * with quotes
443     */
444    function quote_string($string){
445        return "'".sqlite_escape_string($string)."'";
446    }
447
448
449    /**
450     * Escape string for sql
451     */
452    function escape_string($str)
453    {
454        return sqlite_escape_string($str);
455    }
456
457
458    /**
459     * Aggregation function for SQLite
460     *
461     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
462     */
463    function _sqlite_group_concat_step(&$context, $string, $separator = ',') {
464         $context['sep']    = $separator;
465         $context['data'][] = $string;
466    }
467
468    /**
469     * Aggregation function for SQLite
470     *
471     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
472     */
473    function _sqlite_group_concat_finalize(&$context) {
474         $context['data'] = array_unique($context['data']);
475         return join($context['sep'],$context['data']);
476    }
477
478    /**
479     * Keep separate instances for every call to keep database connections
480     */
481    function isSingleton() {
482         return false;
483    }
484
485     /**
486     * fetch the next row as zero indexed array
487     */
488    function res_fetch_array($res)
489    {
490      return sqlite_fetch_array($res, SQLITE_NUM);
491    }
492
493
494    /**
495     * fetch the next row as assocative array
496     */
497    function res_fetch_assoc($res)
498    {
499      return sqlite_fetch_array($res, SQLITE_ASSOC);
500    }
501
502
503    /**
504    * Count the number of records in rsult
505    */
506    function res2count($res) {
507        return sqlite_num_rows($res);
508    }
509
510
511    /**
512    * Count the number of records changed last time
513    */
514    function countChanges($db, $res)
515    {
516      return sqlite_changes($db);
517    }
518}
519
520// vim:ts=4:sw=4:et:enc=utf-8:
521