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