xref: /plugin/sqlite/helper.php (revision aa81d78132800d11a55b7cccbad5662632e4bf36) 
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
19require_once(DOKU_PLUGIN.'sqlite/classes/adapter.php');
20
21class helper_plugin_sqlite extends DokuWiki_Plugin {
22    var $adapter = null;
23
24
25    public function getInfo() {
26        return confToHash(dirname(__FILE__).'plugin.info.txt');
27    }
28
29    /**
30     * Keep separate instances for every call to keep database connections
31     */
32    public function isSingleton() {
33        return false;
34    }
35
36    /**
37     * constructor
38     */
39    public function helper_plugin_sqlite(){
40
41      if(!$this->adapter)
42      {
43        if($this->existsPDOSqlite()){
44            require_once(DOKU_PLUGIN.'sqlite/classes/adapter_pdosqlite.php');
45            $this->adapter = new helper_plugin_sqlite_adapter_pdosqlite();
46        }
47      }
48
49      if(!$this->adapter)
50      {
51        if($this->existsSqlite2()){
52            require_once(DOKU_PLUGIN.'sqlite/classes/adapter_sqlite2.php');
53            $this->adapter = new helper_plugin_sqlite_adapter_sqlite2();
54        }
55      }
56
57      if(!$this->adapter)
58      {
59        msg('SQLite & PDO SQLite support missing in this PHP install - plugin will not work',-1);
60      }
61    }
62
63
64    /**
65     * check availabilty of PHPs sqlite extension (for sqlite2 support)
66     */
67    public function existsSqlite2(){
68        if (!extension_loaded('sqlite')) {
69            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
70            if(function_exists('dl')) @dl($prefix . 'sqlite.' . PHP_SHLIB_SUFFIX);
71        }
72
73        return function_exists('sqlite_open');
74    }
75
76    /**
77     * check availabilty of PHP PDO sqlite3
78     */
79    public function existsPDOSqlite(){
80        if (!extension_loaded('pdo_sqlite')) {
81            $prefix = (PHP_SHLIB_SUFFIX === 'dll') ? 'php_' : '';
82            if(function_exists('dl')) @dl($prefix . 'pdo_sqlite.' . PHP_SHLIB_SUFFIX);
83        }
84
85        if(class_exists('pdo')){
86            foreach(PDO::getAvailableDrivers() as $driver){
87                if($driver=='sqlite'){
88                    return true;
89                }
90            }
91        }
92        return false;
93    }
94
95    /**
96     * Initializes and opens the database
97     *
98     * Needs to be called right after loading this helper plugin
99     *
100     * @param string $dbname
101     * @param string $updatedir - Database update infos
102     * @return bool
103     */
104    public function init($dbname,$updatedir){
105
106        $init = null; // set by initdb()
107        if(!$this->adapter->initdb($dbname,$init)) return false;
108
109        return $this->_updatedb($init,$updatedir);
110    }
111
112    /**
113     * Return the current Database Version
114     */
115    private function _currentDBversion(){
116        $sql = "SELECT val FROM opts WHERE opt = 'dbversion';";
117        $res = $this->query($sql);
118        if(!$res) return false;
119        $row = $this->res2row($res,0);
120        return (int) $row['val'];
121    }
122
123    /**
124     * Update the database if needed
125     *
126     * @param bool   $init      - true if this is a new database to initialize
127     * @param string $updatedir - Database update infos
128     * @return bool
129     */
130    private function _updatedb($init,$updatedir)
131    {
132        if($init){
133
134            $current = 0;
135        }else{
136            $current = $this->_currentDBversion();
137            if(!$current){
138                msg("SQLite: no DB version found. '".$this->adapter->getDbname()."' DB probably broken.",-1);
139                return false;
140            }
141        }
142
143        // in case of init, add versioning table
144        if($init){
145            if(!$this->_runupdatefile(dirname(__FILE__).'/db.sql',0)){
146                  msg("SQLite: '".$this->adapter->getDbname()."' database upgrade failed for version ", -1);
147                return false;
148            }
149        }
150
151        $latest  = (int) trim(io_readFile($updatedir.'/latest.version'));
152
153        // all up to date?
154        if($current >= $latest) return true;
155        for($i=$current+1; $i<=$latest; $i++){
156            $file = sprintf($updatedir.'/update%04d.sql',$i);
157            if(file_exists($file)){
158                if(!$this->_runupdatefile($file,$i)){
159                    msg("SQLite: '".$this->adapter->getDbname()."' database upgrade failed for version ".$i, -1);
160                    return false;
161                }
162            }
163        }
164        return true;
165    }
166
167    /**
168     * Updates the database structure using the given file to
169     * the given version.
170     */
171    private function _runupdatefile($file,$version){
172        $sql  = io_readFile($file,false);
173
174        $sql = explode(";",$sql);
175        array_unshift($sql,'BEGIN TRANSACTION');
176        array_push($sql,"INSERT OR REPLACE INTO opts (val,opt) VALUES ($version,'dbversion')");
177        array_push($sql,"COMMIT TRANSACTION");
178
179        foreach($sql as $s){
180            $s = preg_replace('!^\s*--.*$!m', '', $s);
181            $s = trim($s);
182            if(!$s) continue;
183
184
185            $res = $this->query("$s;");
186            if ($res === false) {
187                if($this->adapter->getName() == DOKU_EXT_SQLITE)
188                {
189                    sqlite_query($this->db, 'ROLLBACK TRANSACTION');
190                }
191                return false;
192            }
193        }
194
195        return ($version == $this->_currentDBversion());
196    }
197
198    /**
199     * Dump db into a file in meta directory
200     *
201     */
202     public function dumpDatabase($dbname){
203        global $conf;
204
205        $res = $this->query("SELECT name,sql FROM sqlite_master WHERE type='table'");
206        $tables = $this->res2arr($res);
207
208        $filename = 'dumpfile_'.$dbname.'.sql';
209        if($fp = fopen($conf['metadir'].'/'.$filename, 'w')){
210
211            fwrite($fp, 'BEGIN TRANSACTION;'."\n");
212
213            foreach($tables as $table){
214
215                fwrite($fp, $table['sql'].";\n");
216
217                $sql = "SELECT * FROM ".$table['name'];
218                $res = $this->query($sql);
219
220                while($row = $this->res_fetch_array($res)){
221
222                    $line = 'INSERT INTO '.$table['name'].' VALUES(';
223                    foreach($row as $no_entry => $entry){
224                        if($no_entry!==0){
225                            $line .= ',';
226                        }
227
228                        if(is_null($entry)){
229                            $line .= 'NULL';
230                        }elseif(!is_numeric($entry)){
231                            $line .= $this->quote_string($entry);
232                        }else{
233                            //FIXME extra leading zeros are truncated e.g 1.300 (thousand three hunderd)-> 1.3
234                            $line .= $entry;
235                        }
236                    }
237                    $line .= ');'."\n";
238
239                    fwrite($fp, $line);
240                }
241            }
242
243            $res = $this->query("SELECT name,sql FROM sqlite_master WHERE type='index'");
244            $indexes = $this->res2arr($res);
245            foreach($indexes as $index){
246                fwrite($fp, $index['sql'].";\n");
247            }
248
249            fwrite($fp, 'COMMIT;'."\n");
250
251            fclose($fp);
252            return true;
253        }else{
254            msg('Dumping "'.hsc($dbname).'" has failed. Could not open '.$filename);
255            return false;
256        }
257    }
258
259    /**
260     * Registers a User Defined Function for use in SQL statements
261     */
262    public function create_function($function_name,$callback,$num_args){
263        $this->adapter->create_function($function_name,$callback,$num_args);
264    }
265
266    /**
267     * Execute a query with the given parameters.
268     *
269     * Takes care of escaping
270     *
271     * @internal param string $sql - the statement
272     * @internal param $arguments ...
273     * @return bool|\SQLiteResult
274     */
275    public function query(){
276        // get function arguments
277        $args = func_get_args();
278
279        return $this->adapter->query($args);
280    }
281
282    /**
283     * Join the given values and quote them for SQL insertion
284     */
285    public function quote_and_join($vals,$sep=',') {
286        return $this->adapter->quote_and_join($vals,$sep);
287    }
288
289    /**
290     * Run sqlite_escape_string() on the given string and surround it
291     * with quotes
292     */
293    public function quote_string($string){
294        return $this->adapter->quote_string($string);
295    }
296
297    /**
298     * Escape string for sql
299     */
300    public function escape_string($str){
301        return $this->adapter->escape_string($str);
302    }
303
304    /**
305     * Returns a complete result set as array
306     */
307    public function res2arr($res, $assoc = true){
308        return $this->adapter->res2arr($res, $assoc);
309    }
310
311    /**
312     * Return the wanted row from a given result set as
313     * associative array
314     */
315    public function res2row($res,$rownum=0){
316        return $this->adapter->res2row($res,$rownum);
317    }
318
319    /**
320     * Return the first value from the first row.
321     */
322    public function res2single($res){
323        return $this->adapter->res2single($res);
324    }
325
326     /**
327     * fetch the next row as zero indexed array
328     */
329    public function res_fetch_array($res){
330        return $this->adapter->res_fetch_array($res);
331    }
332
333
334    /**
335     * fetch the next row as assocative array
336     */
337    public function res_fetch_assoc($res){
338        return $this->adapter->res_fetch_assoc($res);
339    }
340
341    /**
342     * Count the number of records in result
343     *
344     * This function is really inperformant in PDO and should be avoided!
345     */
346    public function res2count($res) {
347        return $this->adapter->res2count($res);
348    }
349
350    /**
351    * Count the number of records changed last time
352    */
353    public function countChanges($db, $res){
354        return $this->adapter->countChanges($db, $res);
355    }
356
357}
358