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