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