1<?php
2
3
4/**
5 * abstract for the adapter that gives access to different sqlite backends
6 */
7abstract class helper_plugin_sqlite_adapter {
8    protected $dbname = '';
9    protected $fileextension;
10    protected $dbfile;
11    protected $db = null;
12    protected $data = array();
13    protected $nativealter = false;
14
15    /**
16     * return name of adapter
17     *
18     * @return string backend name as defined in helper.php
19     */
20    public abstract function getName();
21
22    /**
23     * Should the nativ ALTER TABLE implementation be used instead of workaround?
24     *
25     * @param bool $set
26     */
27    public function setUseNativeAlter($set) {
28        $this->nativealter = $set;
29    }
30
31    /**
32     * The file extension used by the adapter
33     *
34     * @return string
35     */
36    public function getFileextension() {
37        return $this->fileextension;
38    }
39
40    /**
41     * @return string database name when set, otherwise an empty string
42     */
43    public function getDbname() {
44        return $this->dbname;
45    }
46
47    /**
48     * Gives direct access to the database
49     *
50     * This is only usefull for the PDO Adapter as this gives direct access to the PDO object
51     * nontheless it should generally not be used
52     *
53     * @return null|PDO|resource
54     */
55    public function getDb() {
56       return $this->db;
57    }
58
59    /**
60     * Returns the path to the database file (if initialized)
61     *
62     * @return string
63     */
64    public function getDbFile() {
65        return $this->dbfile;
66    }
67
68    /**
69     * Registers a User Defined Function for use in SQL statements
70     *
71     * @param string   $function_name The name of the function used in SQL statements
72     * @param callable $callback      Callback function to handle the defined SQL function
73     * @param int      $num_args      Number of arguments accepted by callback function
74     */
75    public abstract function create_function($function_name, $callback, $num_args);
76
77    /**
78     * Initializes and opens the database
79     * Needs to be called right after loading this helper plugin
80     *
81     * @param string $dbname    - name of database
82     * @param bool   $init      - true if this is a new database to initialize
83     * @param bool   $sqliteupgrade
84     * @return bool
85     */
86    public function initdb($dbname, &$init, $sqliteupgrade = false) {
87        global $conf;
88
89        // check for already open DB
90        if($this->db) {
91            if($this->dbname == $dbname) {
92                // db already open
93                return true;
94            }
95            // close other db
96            $this->closedb();
97
98            $this->db     = null;
99            $this->dbname = '';
100        }
101
102        $this->dbname = $dbname;
103        $this->dbfile = $conf['metadir'].'/'.$dbname.$this->fileextension;
104
105        $init = (!@file_exists($this->dbfile) || ((int) @filesize($this->dbfile)) < 3);
106        return $this->opendb($init, $sqliteupgrade);
107    }
108
109    /**
110     * Checks of given dbfile has Sqlite format 3
111     *
112     * first line tell the format of db file http://marc.info/?l=sqlite-users&m=109383875408202
113     */
114    public static function isSqlite3db($dbfile) {
115        $firstline = @file_get_contents($dbfile, false, null, 0, 15);
116        return $firstline == 'SQLite format 3';
117    }
118
119    /**
120     * open db connection
121     *
122     * @param bool $init          true if this is a new database to initialize
123     * @param bool $sqliteupgrade when connecting to a new database:
124     *                              false stops connecting to an .sqlite3 db when an .sqlite2 db already exist and warns instead,
125     *                              true let connecting so upgrading is possible
126     * @return bool true if connecting to sqlite3 db succeed
127     */
128    protected abstract function opendb($init, $sqliteupgrade = false);
129
130    /**
131     * close current db connection
132     */
133    protected abstract function closedb();
134
135    /**
136     * Execute a query with the given parameters.
137     *
138     * Takes care of escaping
139     *
140     * @param array $args Array with sql string and parameters
141     * @return bool|\PDOStatement|\SQLiteResult
142     */
143    public function query($args) {
144        if(!$this->db) return false;
145
146        //reset previous result
147        $this->data = array();
148
149        $sql = $this->prepareSql($args);
150        if(!$sql) return false;
151
152        // intercept ALTER TABLE statements
153        if(!$this->nativealter) {
154            $match = null;
155            if(preg_match('/^ALTER\s+TABLE\s+([\w\.]+)\s+(.*)/i', $sql, $match)) {
156                return $this->_altertable($match[1], $match[2]);
157            }
158        }
159
160        // execute query
161        return $this->executeQuery($sql);
162    }
163
164    /**
165     * Execute a raw query
166     *
167     * @param $sql..
168     */
169    public abstract function executeQuery($sql);
170
171    /**
172     * Prepare a query with the given arguments.
173     *
174     * Takes care of escaping
175     *
176     * @param array $args
177     *    array of arguments:
178     *      - string $sql - the statement
179     *      - arguments...
180     * @return bool|string
181     */
182    public function prepareSql($args) {
183
184        $sql = trim(array_shift($args));
185        $sql = rtrim($sql, ';');
186
187        if(!$sql) {
188            if(!defined('SIMPLE_TEST')) msg('No SQL statement given', -1);
189            return false;
190        }
191
192        $argc = count($args);
193        if($argc > 0 && is_array($args[0])) {
194            $args = $args[0];
195            $argc = count($args);
196        }
197
198        // check number of arguments
199        $qmc = substr_count($sql, '?');
200        if($argc < $qmc) {
201            if(!defined('SIMPLE_TEST')) msg(
202                'Not enough arguments passed for statement. '.
203                    'Expected '.$qmc.' got '.
204                    $argc.' - '.hsc($sql), -1
205            );
206            return false;
207        }elseif($argc > $qmc){
208            if(!defined('SIMPLE_TEST')) msg(
209                'Too much arguments passed for statement. '.
210                    'Expected '.$qmc.' got '.
211                    $argc.' - '.hsc($sql), -1
212            );
213            return false;
214        }
215
216        // explode at wildcard, then join again
217        $parts = explode('?', $sql, $argc + 1);
218        $args  = array_map(array($this, 'quote_string'), $args); // TODO
219        $sql   = '';
220
221        while(($part = array_shift($parts)) !== null) {
222            $sql .= $part;
223            $sql .= array_shift($args);
224        }
225
226        return $sql;
227    }
228
229    /**
230     * Emulate ALTER TABLE
231     *
232     * The ALTER TABLE syntax is parsed and then emulated using a
233     * temporary table
234     *
235     * @author <jon@jenseng.com>
236     * @link   http://code.jenseng.com/db/
237     * @author Andreas Gohr <gohr@cosmocode.de>
238     */
239    protected function _altertable($table, $alterdefs) {
240
241        // load original table definition SQL
242        $result = $this->query(
243            array(
244                 "SELECT sql,name,type
245                                  FROM sqlite_master
246                                 WHERE tbl_name = '$table'
247                                   AND type = 'table'"
248            )
249        );
250
251        if(($result === false) || ($this->getName() == DOKU_EXT_SQLITE && $this->res2count($result) <= 0)) {
252            msg("ALTER TABLE failed, no such table '".hsc($table)."'", -1);
253            return false;
254        }
255
256        $row = $this->res_fetch_assoc($result);
257
258        if($row === false) {
259            msg("ALTER TABLE failed, table '".hsc($table)."' had no master data", -1);
260            return false;
261        }
262
263        // prepare temporary table SQL
264        $tmpname            = 't'.time();
265        $origsql            = trim(
266            preg_replace(
267                "/[\s]+/", " ",
268                str_replace(
269                    ",", ", ",
270                    preg_replace(
271                        '/\)$/', ' )',
272                        preg_replace("/[\(]/", "( ", $row['sql'], 1)
273                    )
274                )
275            )
276        );
277        $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'", $tmpname, $origsql, 1)), 6);
278
279        // load indexes to reapply later
280        $result = $this->query(
281            array(
282                 "SELECT sql,name,type
283                                  FROM sqlite_master
284                                 WHERE tbl_name = '$table'
285                                   AND type = 'index'"
286            )
287        );
288        if(!$result) {
289            $indexes = array();
290        } else {
291            $indexes = $this->res2arr($result);
292        }
293
294        $defs     = preg_split("/[,]+/", $alterdefs, -1, PREG_SPLIT_NO_EMPTY);
295        $prevword = $table;
296        $oldcols  = preg_split("/[,]+/", substr(trim($createtemptableSQL), strpos(trim($createtemptableSQL), '(') + 1), -1, PREG_SPLIT_NO_EMPTY);
297        $newcols  = array();
298
299        for($i = 0; $i < count($oldcols); $i++) {
300            $colparts              = preg_split("/[\s]+/", $oldcols[$i], -1, PREG_SPLIT_NO_EMPTY);
301            $oldcols[$i]           = $colparts[0];
302            $newcols[$colparts[0]] = $colparts[0];
303        }
304        $newcolumns = '';
305        $oldcolumns = '';
306        foreach ($newcols as $key => $val) {
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(array($createtesttableSQL));
382        if($res === false) return false;
383
384        $droptempsql = 'DROP TABLE '.$tmpname;
385        $res         = $this->query(array($droptempsql));
386        if($res === false) return false;
387
388        $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'".$tmpname."'", $table, $createtesttableSQL, 1)), 17);
389        $newcolumns        = '';
390        $oldcolumns        = '';
391        foreach($newcols as $key => $val) {
392            $newcolumns .= ($newcolumns ? ', ' : '').$val;
393            $oldcolumns .= ($oldcolumns ? ', ' : '').$key;
394        }
395
396        $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname;
397
398        $res = $this->query(array($createtemptableSQL)); //create temp table
399        if($res === false) return false;
400        $res = $this->query(array($copytotempsql)); //copy to table
401        if($res === false) return false;
402        $res = $this->query(array($dropoldsql)); //drop old table
403        if($res === false) return false;
404
405        $res = $this->query(array($createnewtableSQL)); //recreate original table
406        if($res === false) return false;
407        $res = $this->query(array($copytonewsql)); //copy back to original table
408        if($res === false) return false;
409
410        foreach($indexes as $index) { // readd indexes
411            $res = $this->query(array($index['sql']));
412            if($res === false) return false;
413        }
414
415        $res = $this->query(array($droptempsql)); //drop temp table
416        if($res === false) return false;
417
418        return $res; // return a valid resource
419    }
420
421    /**
422     * Join the given values and quote them for SQL insertion
423     */
424    public function quote_and_join($vals, $sep = ',') {
425        $vals = array_map(array($this, 'quote_string'), $vals);
426        return join($sep, $vals);
427    }
428
429    /**
430     * Run sqlite_escape_string() on the given string and surround it
431     * with quotes
432     */
433    public abstract function quote_string($string);
434
435    /**
436     * Escape string for sql
437     */
438    public abstract function escape_string($str);
439
440    /**
441     * Close the result set and it's cursors
442     *
443     * @param $res
444     */
445    public abstract function res_close($res);
446
447    /**
448     * Returns a complete result set as array
449     */
450    public abstract function res2arr($res, $assoc = true);
451
452    /**
453     * Return the next row of the given result set as associative array
454     */
455    public abstract function res2row($res);
456
457    /**
458     * Return the first value from the next row.
459     */
460    public abstract function res2single($res);
461
462    /**
463     * fetch the next row as zero indexed array
464     */
465    public abstract function res_fetch_array($res);
466
467    /**
468     * fetch the next row as assocative array
469     */
470    public abstract function res_fetch_assoc($res);
471
472    /**
473     * Count the number of records in result
474     *
475     * This function is really inperformant in PDO and should be avoided!
476     */
477    public abstract function res2count($res);
478
479    /**
480     * Count the number of records changed last time
481     *
482     * Don't work after a SELECT statement in PDO
483     */
484    public abstract function countChanges($res);
485
486    /**
487     * Do not serialize the DB connection
488     *
489     * @return array
490     */
491    public function __sleep() {
492        $this->db = null;
493        return array_keys(get_object_vars($this));
494    }
495
496    /**
497     * On deserialization, reinit database connection
498     */
499    public function __wakeup() {
500        $init = false;
501        $this->initdb($this->dbname, $init);
502    }
503
504}
505
506// vim:ts=4:sw=4:et:enc=utf-8:
507