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