xref: /plugin/sqlite/helper.php (revision d9cff31c8c5e434c28529aebb79d38edc75927ce) 
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
16class helper_plugin_sqlite extends DokuWiki_Plugin {
17    var $db     = null;
18    var $dbname = '';
19
20    function getInfo() {
21        return confToHash(dirname(__FILE__).'plugin.info.txt');
22    }
23
24    /**
25     * constructor
26     */
27    function helper_plugin_sqlite(){
28        if (!extension_loaded('sqlite')) {
29            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
30            if(function_exists('dl')) @dl($prefix . 'sqlite.' . PHP_SHLIB_SUFFIX);
31        }
32
33        if(!function_exists('sqlite_open')){
34            msg('SQLite support missing in this PHP install - plugin will not work',-1);
35        }
36    }
37
38    /**
39     * Initializes and opens the database
40     *
41     * Needs to be called right after loading this helper plugin
42     */
43    function init($dbname,$updatedir){
44        global $conf;
45
46        // check for already open DB
47        if($this->db){
48            if($this->dbname == $dbname){
49                // db already open
50                return true;
51            }
52            // close other db
53            sqlite_close($this->db);
54            $this->db     = null;
55            $this->dbname = '';
56        }
57
58        $this->dbname = $dbname;
59        $dbfile = $conf['metadir'].'/'.$dbname.'.sqlite';
60        $init   = (!@file_exists($dbfile) || ((int) @filesize($dbfile)) < 3);
61
62        $error='';
63        $this->db = sqlite_open($dbfile, 0666, $error);
64        if(!$this->db){
65            msg("SQLite: failed to open SQLite ".$this->dbname." database ($error)",-1);
66            return false;
67        }
68
69        // register our custom aggregate function
70        sqlite_create_aggregate($this->db,'group_concat',
71                                array($this,'_sqlite_group_concat_step'),
72                                array($this,'_sqlite_group_concat_finalize'), 2);
73
74        $this->_updatedb($init,$updatedir);
75        return true;
76    }
77
78    /**
79     * Return the current Database Version
80     */
81    function _currentDBversion(){
82        $sql = "SELECT val FROM opts WHERE opt = 'dbversion';";
83        $res = $this->query($sql);
84        if(!$res) return false;
85        $row = $this->res2row($res,0);
86        return (int) $row['val'];
87    }
88    /**
89     * Update the database if needed
90     *
91     * @param bool   $init      - true if this is a new database to initialize
92     * @param string $updatedir - Database update infos
93     */
94    function _updatedb($init,$updatedir){
95        if($init){
96            $current = 0;
97        }else{
98            $current = $this->_currentDBversion();
99            if(!$current){
100                msg('SQLite: no DB version found. '.$this->dbname.' DB probably broken.',-1);
101                return false;
102            }
103        }
104
105        // in case of init, add versioning table
106        if($init){
107            if(!$this->_runupdatefile(dirname(__FILE__).'/db.sql',0)){
108                msg('SQLite: '.$this->dbname.' database upgrade failed for version '.$i, -1);
109                return false;
110            }
111        }
112
113        $latest  = (int) trim(io_readFile($updatedir.'/latest.version'));
114
115        // all up to date?
116        if($current >= $latest) return true;
117        for($i=$current+1; $i<=$latest; $i++){
118            $file = sprintf($updatedir.'/update%04d.sql',$i);
119            if(file_exists($file)){
120                if(!$this->_runupdatefile($file,$i)){
121                    msg('SQLite: '.$this->dbname.' database upgrade failed for version '.$i, -1);
122
123
124                    return false;
125                }
126            }
127        }
128        return true;
129    }
130
131    /**
132     * Updates the database structure using the given file to
133     * the given version.
134     */
135    function _runupdatefile($file,$version){
136        $sql  = io_readFile($file,false);
137
138        $sql = explode(";",$sql);
139        array_unshift($sql,'BEGIN TRANSACTION');
140        array_push($sql,"INSERT OR REPLACE INTO opts (val,opt) VALUES ($version,'dbversion')");
141        array_push($sql,"COMMIT TRANSACTION");
142
143        foreach($sql as $s){
144            $s = preg_replace('!^\s*--.*$!m', '', $s);
145            $s = trim($s);
146            if(!$s) continue;
147            $res = $this->query("$s;");
148            if ($res === false) {
149                sqlite_query($this->db, 'ROLLBACK TRANSACTION');
150                return false;
151            }
152        }
153
154        return ($version == $this->_currentDBversion());
155    }
156
157    /**
158     * Execute a query with the given parameters.
159     *
160     * Takes care of escaping
161     *
162     * @param string $sql - the statement
163     * @param arguments...
164     */
165    function query(){
166        if(!$this->db) return false;
167
168        // get function arguments
169        $args = func_get_args();
170        $sql  = trim(array_shift($args));
171
172        if(!$sql){
173            msg('No SQL statement given',-1);
174            return false;
175        }
176
177        if(is_array($args[0])) $args = $args[0];
178        $argc = count($args);
179
180        // check number of arguments
181        if($argc < substr_count($sql,'?')){
182            msg('Not enough arguments passed for statement. '.
183                'Expected '.substr_count($sql,'?').' got '.
184                $argc.' - '.hsc($sql),-1);
185            return false;
186        }
187
188        // explode at wildcard, then join again
189        $parts = explode('?',$sql,$argc+1);
190        $args  = array_map(array($this,'quote_string'),$args);
191        $sql   = '';
192
193        while( ($part = array_shift($parts)) !== null ){
194            $sql .= $part;
195            $sql .= array_shift($args);
196        }
197
198        // execute query
199        $err = '';
200        $res = @sqlite_query($this->db,$sql,SQLITE_ASSOC,$err);
201        if($err){
202            msg($err.':<br /><pre>'.hsc($sql).'</pre>',-1);
203            return false;
204        }elseif(!$res){
205            msg(sqlite_error_string(sqlite_last_error($this->db)).
206                ':<br /><pre>'.hsc($sql).'</pre>',-1);
207            return false;
208        }
209
210        return $res;
211    }
212
213    /**
214     * Returns a complete result set as array
215     */
216    function res2arr($res){
217        $data = array();
218        if(!sqlite_num_rows($res)) return $data;
219        sqlite_rewind($res);
220        while(($row = sqlite_fetch_array($res)) !== false){
221            $data[] = $row;
222        }
223        return $data;
224    }
225
226    /**
227     * Return the wanted row from a given result set as
228     * associative array
229     */
230    function res2row($res,$rownum=0){
231        if(!@sqlite_seek($res,$rownum)){
232            return false;
233        }
234        return sqlite_fetch_array($res);
235    }
236
237
238    /**
239     * Join the given values and quote them for SQL insertion
240     */
241    function quote_and_join($vals,$sep=',') {
242        $vals = array_map(array('helper_plugin_sqlite','quote_string'),$vals);
243        return join($sep,$vals);
244    }
245
246    /**
247     * Run sqlite_escape_string() on the given string and surround it
248     * with quotes
249     */
250    function quote_string($string){
251        return "'".sqlite_escape_string($string)."'";
252    }
253
254
255    /**
256     * Aggregation function for SQLite
257     *
258     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
259     */
260    function _sqlite_group_concat_step(&$context, $string, $separator = ',') {
261         $context['sep']    = $separator;
262         $context['data'][] = $string;
263    }
264
265    /**
266     * Aggregation function for SQLite
267     *
268     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
269     */
270    function _sqlite_group_concat_finalize(&$context) {
271         $context['data'] = array_unique($context['data']);
272         return join($context['sep'],$context['data']);
273    }
274
275
276}
277
278// vim:ts=4:sw=4:et:enc=utf-8:
279