xref: /plugin/sqlite/helper.php (revision be16ec279330c3fc6e36b917a2aea036c0de5fb9) 
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
19
20class helper_plugin_sqlite extends DokuWiki_Plugin {
21    var $db     = null;
22    var $dbname = '';
23    var $extension = null;
24    function getInfo() {
25        return confToHash(dirname(__FILE__).'plugin.info.txt');
26    }
27
28    /**
29     * constructor
30     */
31    function helper_plugin_sqlite(){
32
33      if(!$this->extension)
34      {
35        if (!extension_loaded('pdo_sqlite')) {
36            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
37            if(function_exists('dl')) @dl($prefix . 'pdo_sqlite.' . PHP_SHLIB_SUFFIX);
38        }
39
40        if(class_exists('pdo')){
41            $this->extension = DOKU_EXT_PDO;
42        }
43      }
44
45      if(!$this->extension)
46      {
47        if (!extension_loaded('sqlite')) {
48            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
49            if(function_exists('dl')) @dl($prefix . 'sqlite.' . PHP_SHLIB_SUFFIX);
50        }
51
52        if(function_exists('sqlite_open')){
53           $this->extension = DOKU_EXT_SQLITE;
54        }
55      }
56
57      if(!$this->extension)
58
59      {
60        msg('SQLite & PDO SQLite support missing in this PHP install - plugin will not work',-1);
61      }
62    }
63
64    /**
65     * Initializes and opens the database
66     *
67     * Needs to be called right after loading this helper plugin
68     */
69    function init($dbname,$updatedir){
70        global $conf;
71
72        // check for already open DB
73        if($this->db){
74            if($this->dbname == $dbname){
75                // db already open
76                return true;
77            }
78            // close other db
79            if($this->extension == DOKU_EXT_SQLITE)
80            {
81              sqlite_close($this->db);
82            }
83            else
84            {
85              $this->db->close();
86            }
87            $this->db     = null;
88            $this->dbname = '';
89        }
90
91        $this->dbname = $dbname;
92
93        $fileextension = '.sqlite';
94
95        $this->dbfile = $conf['metadir'].'/'.$dbname.$fileextension;
96
97        $init   = (!@file_exists($this->dbfile) || ((int) @filesize($this->dbfile)) < 3);
98
99        if($this->extension == DOKU_EXT_SQLITE)
100        {
101          $error='';
102          $this->db = sqlite_open($this->dbfile, 0666, $error);
103          if(!$this->db){
104              msg("SQLite: failed to open SQLite '".$this->dbname."' database ($error)",-1);
105              return false;
106          }
107
108          // register our custom aggregate function
109          sqlite_create_aggregate($this->db,'group_concat',
110                                  array($this,'_sqlite_group_concat_step'),
111                                  array($this,'_sqlite_group_concat_finalize'), 2);
112        }
113        else
114        {
115          $dsn = 'sqlite:'.$this->dbfile;
116
117          try {
118              $this->db = new PDO($dsn);
119          } catch (PDOException $e) {
120            msg("SQLite: failed to open SQLite '".$this->dbname."' database (".$e->getMessage().")",-1);
121              return false;
122          }
123          $this->db->sqliteCreateAggregate('group_concat',
124                                  array($this,'_pdo_group_concat_step'),
125                                  array($this,'_pdo_group_concat_finalize'));
126        }
127
128        $this->_updatedb($init,$updatedir);
129        return true;
130    }
131
132    /**
133     * Return the current Database Version
134     */
135    function _currentDBversion(){
136        $sql = "SELECT val FROM opts WHERE opt = 'dbversion';";
137        $res = $this->query($sql);
138        if(!$res) return false;
139        $row = $this->res2row($res,0);
140        return (int) $row['val'];
141    }
142    /**
143     * Update the database if needed
144     *
145     * @param bool   $init      - true if this is a new database to initialize
146     * @param string $updatedir - Database update infos
147     */
148    function _updatedb($init,$updatedir)
149    {
150        if($init){
151
152            $current = 0;
153        }else{
154            $current = $this->_currentDBversion();
155            if(!$current){
156                msg("SQLite: no DB version found. '".$this->dbname."' DB probably broken.",-1);
157                return false;
158            }
159        }
160
161        // in case of init, add versioning table
162        if($init){
163            if(!$this->_runupdatefile(dirname(__FILE__).'/db.sql',0)){
164                  msg("SQLite: '".$this->dbname."' database upgrade failed for version ", -1);
165                return false;
166            }
167        }
168
169        $latest  = (int) trim(io_readFile($updatedir.'/latest.version'));
170
171        // all up to date?
172        if($current >= $latest) return true;
173        for($i=$current+1; $i<=$latest; $i++){
174            $file = sprintf($updatedir.'/update%04d.sql',$i);
175            if(file_exists($file)){
176                if(!$this->_runupdatefile($file,$i)){
177                    msg("SQLite: '".$this->dbname."' database upgrade failed for version ".$i, -1);
178                    return false;
179                }
180            }
181        }
182        return true;
183    }
184
185    /**
186     * Updates the database structure using the given file to
187     * the given version.
188     */
189    function _runupdatefile($file,$version){
190        $sql  = io_readFile($file,false);
191
192        $sql = explode(";",$sql);
193        array_unshift($sql,'BEGIN TRANSACTION');
194        array_push($sql,"INSERT OR REPLACE INTO opts (val,opt) VALUES ($version,'dbversion')");
195        array_push($sql,"COMMIT TRANSACTION");
196
197        foreach($sql as $s){
198            $s = preg_replace('!^\s*--.*$!m', '', $s);
199            $s = trim($s);
200            if(!$s) continue;
201
202
203            $res = $this->query("$s;");
204            if ($res === false) {
205              if($this->extension == DOKU_EXT_SQLITE)
206              {
207                sqlite_query($this->db, 'ROLLBACK TRANSACTION');
208                return false;
209              }
210              else
211              {
212                return false;
213              }
214            }
215        }
216
217        return ($version == $this->_currentDBversion());
218    }
219
220    /**
221     * Emulate ALTER TABLE
222     *
223     * The ALTER TABLE syntax is parsed and then emulated using a
224     * temporary table
225     *
226     * @author <jon@jenseng.com>
227     * @link   http://code.jenseng.com/db/
228     * @author Andreas Gohr <gohr@cosmocode.de>
229     */
230    function _altertable($table,$alterdefs){
231
232        // load original table definition SQL
233        $result = $this->query("SELECT sql,name,type
234                                  FROM sqlite_master
235                                 WHERE tbl_name = '$table'
236                                   AND type = 'table'");
237
238        if(($result === false) || ($this->extension == DOKU_EXT_SQLITE && sqlite_num_rows($result)<=0)){
239            msg("ALTER TABLE failed, no such table '".hsc($table)."'",-1);
240            return false;
241        }
242
243        if($this->extension == DOKU_EXT_SQLITE )
244        {
245          $row = sqlite_fetch_array($result);
246        }
247        else
248        {
249           $row = $result->fetch(PDO::FETCH_ASSOC);
250        }
251
252        if($row === false){
253            msg("ALTER TABLE failed, table '".hsc($table)."' had no master data",-1);
254            return false;
255        }
256
257
258        // prepare temporary table SQL
259        $tmpname = 't'.time();
260        $origsql = trim(preg_replace("/[\s]+/"," ",
261                        str_replace(",",", ",
262                        preg_replace('/\)$/',' )',
263                        preg_replace("/[\(]/","( ",$row['sql'],1)))));
264        $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'",$tmpname,$origsql,1)),6);
265        $createindexsql = array();
266
267        // load indexes to reapply later
268        $result = $this->query("SELECT sql,name,type
269                                  FROM sqlite_master
270                                 WHERE tbl_name = '$table'
271                                   AND type = 'index'");
272        if(!$result){
273            $indexes = array();
274        }else{
275            $indexes = $this->res2arr($result);
276        }
277
278
279        $i = 0;
280        $defs = preg_split("/[,]+/",$alterdefs,-1,PREG_SPLIT_NO_EMPTY);
281        $prevword = $table;
282        $oldcols = preg_split("/[,]+/",substr(trim($createtemptableSQL),strpos(trim($createtemptableSQL),'(')+1),-1,PREG_SPLIT_NO_EMPTY);
283        $newcols = array();
284
285        for($i=0;$i<count($oldcols);$i++){
286            $colparts = preg_split("/[\s]+/",$oldcols[$i],-1,PREG_SPLIT_NO_EMPTY);
287            $oldcols[$i] = $colparts[0];
288            $newcols[$colparts[0]] = $colparts[0];
289        }
290        $newcolumns = '';
291        $oldcolumns = '';
292        reset($newcols);
293        while(list($key,$val) = each($newcols)){
294            $newcolumns .= ($newcolumns?', ':'').$val;
295            $oldcolumns .= ($oldcolumns?', ':'').$key;
296        }
297        $copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table;
298        $dropoldsql = 'DROP TABLE '.$table;
299        $createtesttableSQL = $createtemptableSQL;
300
301        foreach($defs as $def){
302            $defparts = preg_split("/[\s]+/",$def,-1,PREG_SPLIT_NO_EMPTY);
303            $action = strtolower($defparts[0]);
304            switch($action){
305                case 'add':
306                    if(count($defparts) < 2){
307                        msg('ALTER TABLE: not enough arguments for ADD statement',-1);
308                        return false;
309                    }
310                    $createtesttableSQL = substr($createtesttableSQL,0,strlen($createtesttableSQL)-1).',';
311                    for($i=1;$i<count($defparts);$i++)
312                        $createtesttableSQL.=' '.$defparts[$i];
313                    $createtesttableSQL.=')';
314                    break;
315
316                case 'change':
317                    if(count($defparts) <= 3){
318                        msg('ALTER TABLE: near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').($defparts[2]?' '.$defparts[2]:'').'": syntax error',-1);
319                        return false;
320                    }
321
322                    if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
323                        if($newcols[$defparts[1]] != $defparts[1]){
324                            msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1);
325                            return false;
326                        }
327                        $newcols[$defparts[1]] = $defparts[2];
328                        $nextcommapos = strpos($createtesttableSQL,',',$severpos);
329                        $insertval = '';
330                        for($i=2;$i<count($defparts);$i++)
331                            $insertval.=' '.$defparts[$i];
332                        if($nextcommapos)
333                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos).$insertval.substr($createtesttableSQL,$nextcommapos);
334                        else
335                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1)).$insertval.')';
336                    } else {
337                        msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1);
338                        return false;
339                    }
340                    break;
341                case 'drop':
342                    if(count($defparts) < 2){
343                        msg('ALTER TABLE: near "'.$defparts[0].($defparts[1]?' '.$defparts[1]:'').'": syntax error',-1);
344                        return false;
345                    }
346                    if($severpos = strpos($createtesttableSQL,' '.$defparts[1].' ')){
347                        $nextcommapos = strpos($createtesttableSQL,',',$severpos);
348                        if($nextcommapos)
349                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos).substr($createtesttableSQL,$nextcommapos + 1);
350                        else
351                            $createtesttableSQL = substr($createtesttableSQL,0,$severpos-(strpos($createtesttableSQL,',')?0:1) - 1).')';
352                        unset($newcols[$defparts[1]]);
353                    }else{
354                        msg('ALTER TABLE: unknown column "'.$defparts[1].'" in "'.$table.'"',-1);
355                        return false;
356                    }
357                    break;
358                default:
359                    msg('ALTER TABLE: near "'.$prevword.'": syntax error',-1);
360                    return false;
361            }
362            $prevword = $defparts[count($defparts)-1];
363        }
364
365        // this block of code generates a test table simply to verify that the
366        // columns specifed are valid in an sql statement
367        // this ensures that no reserved words are used as columns, for example
368        $res = $this->query($createtesttableSQL);
369        if($res === false) return false;
370
371        $droptempsql = 'DROP TABLE '.$tmpname;
372        $res = $this->query($droptempsql);
373        if($res === false) return false;
374
375
376        $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'".$tmpname."'",$table,$createtesttableSQL,1)),17);
377        $newcolumns = '';
378        $oldcolumns = '';
379        reset($newcols);
380        while(list($key,$val) = each($newcols)){
381            $newcolumns .= ($newcolumns?', ':'').$val;
382            $oldcolumns .= ($oldcolumns?', ':'').$key;
383        }
384
385        $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname;
386
387        $res = $this->query($createtemptableSQL); //create temp table
388        if($res === false) return false;
389        $res = $this->query($copytotempsql); //copy to table
390        if($res === false) return false;
391        $res = $this->query($dropoldsql); //drop old table
392        if($res === false) return false;
393
394        $res = $this->query($createnewtableSQL); //recreate original table
395        if($res === false) return false;
396        $res = $this->query($copytonewsql); //copy back to original table
397        if($res === false) return false;
398
399        foreach($indexes as $index){ // readd indexes
400            $res = $this->query($index['sql']);
401            if($res === false) return false;
402        }
403
404        $res = $this->query($droptempsql); //drop temp table
405        if($res === false) return false;
406
407        return $res; // return a valid resource
408    }
409
410    /**
411     * Registers a User Defined Function for use in SQL statements
412     */
413    function create_function($function_name,$callback,$num_args){
414        if($this->extension == DOKU_EXT_SQLITE )
415        {
416          sqlite_create_function($this->db,$function_name,$callback,$num_args);
417        }
418        else
419        {
420          $this->db->sqliteCreateFunction($this->db,$function_name,$callback,$num_args);
421        }
422    }
423
424    /**
425     * Execute a query with the given parameters.
426     *
427     * Takes care of escaping
428     *
429     * @param string $sql - the statement
430     * @param arguments...
431     */
432    function query(){
433        if(!$this->db) return false;
434
435        // get function arguments
436        $args = func_get_args();
437        $sql  = trim(array_shift($args));
438        $sql  = rtrim($sql,';');
439
440        if(!$sql){
441            msg('No SQL statement given',-1);
442            return false;
443        }
444
445        $argc = count($args);
446        if($argc > 0 && is_array($args[0])) {
447            $args = $args[0];
448            $argc = count($args);
449        }
450
451        // check number of arguments
452        if($argc < substr_count($sql,'?')){
453            msg('Not enough arguments passed for statement. '.
454                'Expected '.substr_count($sql,'?').' got '.
455                $argc.' - '.hsc($sql),-1);
456            return false;
457        }
458
459        // explode at wildcard, then join again
460        $parts = explode('?',$sql,$argc+1);
461        $args  = array_map(array($this,'quote_string'),$args);
462        $sql   = '';
463
464        while( ($part = array_shift($parts)) !== null ){
465            $sql .= $part;
466            $sql .= array_shift($args);
467        }
468
469        // intercept ALTER TABLE statements
470        $match = null;
471        if(preg_match('/^ALTER\s+TABLE\s+([\w\.]+)\s+(.*)/i',$sql,$match)){
472            return $this->_altertable($match[1],$match[2]);
473        }
474
475        // execute query
476        $err = '';
477        if($this->extension == DOKU_EXT_SQLITE )
478        {
479          $res = @sqlite_query($this->db,$sql,SQLITE_ASSOC,$err);
480          if($err){
481              msg($err.':<br /><pre>'.hsc($sql).'</pre>',-1);
482              return false;
483          }elseif(!$res){
484              msg(sqlite_error_string(sqlite_last_error($this->db)).
485                  ':<br /><pre>'.hsc($sql).'</pre>',-1);
486              return false;
487          }
488        }
489        else
490        {
491          $result = false;
492
493          $res = $this->db->query($sql);
494
495          if(!$res){
496            $err = $this->db->errorInfo();
497              msg($err[2].':<br /><pre>'.hsc($sql).'</pre>',-1);
498              return false;
499          }
500        }
501        return $res;
502    }
503
504    /**
505     * Returns a complete result set as array
506     */
507    function res2arr($res){
508        $data = array();
509        if($this->extension == DOKU_EXT_SQLITE )
510        {
511          if(!sqlite_num_rows($res)) return $data;
512          sqlite_rewind($res);
513          while(($row = sqlite_fetch_array($res, SQLITE_ASSOC)) !== false){
514              $data[] = $row;
515          }
516        }
517        else
518        {
519          $data = $res->fetchAll(PDO::FETCH_ASSOC);
520          if(!count(data))
521          {
522            return false;
523          }
524        }
525        return $data;
526    }
527
528    /**
529     * Return the wanted row from a given result set as
530     * associative array
531     */
532    function res2row($res,$rownum=0){
533        if($this->extension == DOKU_EXT_SQLITE )
534        {
535          if(!@sqlite_seek($res,$rownum)){
536              return false;
537          }
538          return sqlite_fetch_array($res, SQLITE_ASSOC);
539        }
540        else
541        {
542          //very dirty replication of the same functionality (really must look at cursors)
543          $data = array();
544          //do we need to rewind?
545          $data = $res->fetchAll(PDO::FETCH_ASSOC);
546          if(!count(data))
547          {
548            return false;
549          }
550
551          if(!isset($data[$rownum]))
552          {
553            return false;
554          }
555          else
556          {
557            return $data[$rownum];
558          }
559        }
560    }
561
562    /**
563     * Return the first value from the first row.
564     */
565    function res2single($res)
566    {
567      if($this->extension == DOKU_EXT_SQLITE )
568      {
569        return sqlite_fetch_single($res);
570      }
571      else
572      {
573        $data = $res->fetchAll(PDO::FETCH_NUM);
574        if(!count(data))
575        {
576          return false;
577        }
578        return $data[0][0];
579      }
580    }
581
582    /**
583     * Join the given values and quote them for SQL insertion
584     */
585    function quote_and_join($vals,$sep=',') {
586        $vals = array_map(array('helper_plugin_sqlite','quote_string'),$vals);
587        return join($sep,$vals);
588    }
589
590    /**
591     * Run sqlite_escape_string() on the given string and surround it
592     * with quotes
593     */
594    function quote_string($string){
595      if($this->extension == DOKU_EXT_SQLITE )
596      {
597        return "'".sqlite_escape_string($string)."'";
598      }
599      else
600      {
601        return $this->db->quote($string);
602      }
603    }
604
605
606    /**
607     * Escape string for sql
608     */
609    function escape_string($str)
610    {
611      if($this->extension == DOKU_EXT_SQLITE )
612      {
613        return sqlite_escape_string($str);
614      }
615      else
616      {
617        return trim($this->db->quote($str), "'");
618      }
619    }
620
621
622
623    /**
624    * Aggregation function for SQLite
625    *
626    * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
627    */
628    function _sqlite_group_concat_step(&$context, $string, $separator = ',') {
629         $context['sep'] = $separator;
630         $context['data'][] = $string;
631    }
632
633    /**
634    * Aggregation function for SQLite
635    *
636    * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
637    */
638    function _sqlite_group_concat_finalize(&$context) {
639         $context['data'] = array_unique($context['data']);
640         return join($context['sep'],$context['data']);
641    }
642
643
644    /**
645     * Aggregation function for SQLite via PDO
646     *
647     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
648     */
649    function _pdo_group_concat_step(&$context, $rownumber, $string, $separator = ',') {
650         if(is_null($context))
651         {
652           $context = array(
653             'sep'  => $separator,
654             'data' => array()
655             );
656         }
657
658         $context['data'][] = $string;
659         return $context;
660    }
661
662     /**
663     * Aggregation function for SQLite via PDO
664     *
665     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
666     */
667    function _pdo_group_concat_finalize(&$context, $rownumber)
668    {
669        if(!is_array($context))
670        {
671          return null;
672        }
673        $context['data'] = array_unique($context['data']);
674        return join($context['sep'],$context['data']);
675    }
676
677    /**
678     * Keep separate instances for every call to keep database connections
679     */
680    function isSingleton() {
681         return false;
682    }
683
684     /**
685     * fetch the next row as zero indexed array
686     */
687    function res_fetch_array($res)
688    {
689      if($this->extension == DOKU_EXT_SQLITE )
690      {
691        return sqlite_fetch_array($res, SQLITE_NUM);
692      }
693      else
694      {
695        return $res->fetch(PDO::FETCH_NUM);
696      }
697    }
698
699
700    /**
701     * fetch the next row as assocative array
702     */
703    function res_fetch_assoc($res)
704    {
705      if($this->extension == DOKU_EXT_SQLITE )
706      {
707        return sqlite_fetch_array($res, SQLITE_ASSOC);
708      }
709      else
710      {
711       return $res->fetch(PDO::FETCH_ASSOC);
712      }
713    }
714
715
716    /**
717    * Count the number of records in rsult
718    */
719    function res2count($res) {
720      if($this->extension == DOKU_EXT_SQLITE )
721      {
722        return sqlite_num_rows($res);
723      }
724      else
725      {
726        $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/i';
727        if (preg_match($regex, $res->queryString, $output) > 0) {
728            $stmt = $this->db->query("SELECT COUNT(*) FROM {$output[1]}", PDO::FETCH_NUM);
729
730            return $stmt->fetchColumn();
731        }
732
733        return false;
734      }
735    }
736
737
738    /**
739    * Count the number of records changed last time
740    */
741    function countChanges($db, $res)
742    {
743      if($this->extension == DOKU_EXT_SQLITE )
744      {
745        return sqlite_changes($db);
746      }
747      else
748      {
749        return $res->rowCount();
750      }
751    }
752}
753
754// vim:ts=4:sw=4:et:enc=utf-8:
755