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