1<?php
2/**
3 * Implements functions for using PDO driver of PHP for sqlite
4 *
5 * Only sqlite3 is (good) supported.
6 * Therefore an upgrade function is supplied, when you have also the
7 * Sqlite Extension running.
8 */
9class helper_plugin_sqlite_adapter_pdosqlite extends helper_plugin_sqlite_adapter {
10
11    protected $fileextension = '.sqlite3';
12    /** @var $db PDO */
13    protected $db;
14
15    /**
16     * return name of adapter
17     *
18     * @return string adapter name
19     */
20    public function getName() {
21        return DOKU_EXT_PDO;
22    }
23
24    /**
25     * Registers a User Defined Function for use in SQL statements
26     *
27     * @param string   $function_name The name of the function used in SQL statements
28     * @param callable $callback      Callback function to handle the defined SQL function
29     * @param int      $num_args      Number of arguments accepted by callback function
30     */
31    public function create_function($function_name, $callback, $num_args) {
32        $this->db->sqliteCreateFunction($function_name, $callback, $num_args);
33    }
34
35    /**
36     * open db connection
37     *
38     * @param bool $init          true if this is a new database to initialize
39     * @param bool $sqliteupgrade when connecting to a new database:
40     *                              false stops connecting to an .sqlite3 db when an .sqlite2 db already exist and warns instead,
41     *                              true let connecting so upgrading is possible
42     * @return bool true if connecting to sqlite3 db succeed
43     */
44    public function opendb($init, $sqliteupgrade = false) {
45        if($init) {
46            $oldDbfile = substr($this->dbfile, 0, -1);
47
48            if(@file_exists($oldDbfile)) {
49
50                $notfound_msg = "SQLite: '".$this->dbname.$this->fileextension."' database not found. In the meta directory is '".$this->dbname.substr($this->fileextension, 0, -1)."' available. ";
51                global $ID;
52                if($this->isSqlite3db($oldDbfile)) {
53                    msg($notfound_msg."PDO sqlite needs a rename of the file extension to '.sqlite3'. For admins more info via Admin > <a href=\"".wl($ID, array('do'=> 'admin', 'page'=> 'sqlite'))."\">Sqlite Interface</a>.", -1);
54                    return false;
55                } else {
56                    //don't block connecting db, when upgrading
57                    if(!$sqliteupgrade) {
58                        msg($notfound_msg."PDO sqlite needs a upgrade of this sqlite2 db to sqlite3 format. For admins more info via Admin > <a href=\"".wl($ID, array('do'=> 'admin', 'page'=> 'sqlite'))."\">Sqlite Interface</a>.", -1);
59                        return false;
60                    }
61                }
62            }
63        } else {
64            if(!$this->isSqlite3db($this->dbfile)) {
65                msg("SQLite: failed to open SQLite '".$this->dbname."' database (DB has not a sqlite3 format.)", -1);
66                return false;
67            }
68        }
69
70        $dsn = 'sqlite:'.$this->dbfile;
71
72        try {
73            $this->db = new PDO($dsn);
74        } catch(PDOException $e) {
75            msg("SQLite: failed to open SQLite '".$this->dbname."' database (".$e->getMessage().")", -1);
76            return false;
77        }
78        $this->db->sqliteCreateAggregate(
79            'group_concat',
80            array($this, '_pdo_group_concat_step'),
81            array($this, '_pdo_group_concat_finalize')
82        );
83        return true;
84    }
85
86    /**
87     * close current db connection
88     */
89    public function closedb() {
90        $this->db = null;
91    }
92
93    /**
94     * Execute a query
95     *
96     * @param string $sql query
97     * @return bool|PDOStatement
98     */
99    public function executeQuery($sql) {
100        $res = $this->db->query($sql);
101
102        $this->data = null;
103
104        if(!$res) {
105            $err = $this->db->errorInfo();
106            if(defined('DOKU_UNITTEST')) {
107                throw new RuntimeException($err[0] . ' ' . $err[1] . ' ' . $err[2] . ":\n" .$sql);
108            }
109            msg($err[0] . ' ' . $err[1] . ' ' . $err[2] . ':<br /><pre>' . hsc($sql) . '</pre>', -1);
110            return false;
111        }
112
113        return $res;
114    }
115
116    /**
117     * Close the result set and it's cursors
118     *
119     * @param bool|PDOStatement $res
120     * @return bool
121     */
122    public function res_close($res) {
123        if(!$res) return false;
124
125        return $res->closeCursor();
126    }
127
128    /**
129     * Returns a complete result set as array
130     *
131     * @param bool|PDOStatement $res
132     * @param bool $assoc
133     * @return array with arrays of the rows
134     */
135    public function res2arr($res, $assoc = true) {
136        if(!$res) return array();
137
138        if(!$this->data) {
139            $mode       = $assoc ? PDO::FETCH_ASSOC : PDO::FETCH_NUM;
140            $this->data = $res->fetchAll($mode);
141        }
142        return $this->data;
143    }
144
145    /**
146     * Return the next row of the given result set as associative array
147     *
148     * @param bool|PDOStatement $res
149     * @return bool|array
150     */
151    public function res2row($res) {
152        if(!$res) return false;
153
154        return $res->fetch(PDO::FETCH_ASSOC);
155    }
156
157    /**
158     * Return the first value from the next row.
159     *
160     * @param bool|PDOStatement $res
161     * @return bool|string
162     */
163    public function res2single($res) {
164        if(!$res) return false;
165
166        $data = $res->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_ABS, 0);
167        if(empty($data)) {
168            return false;
169        }
170        return $data[0];
171    }
172
173    /**
174     * Run sqlite_escape_string() on the given string and surround it
175     * with quotes
176     *
177     * @param string $string
178     * @return string
179     */
180    public function quote_string($string) {
181        return $this->db->quote($string);
182    }
183
184    /**
185     * Escape string for sql
186     *
187     * @param string $str
188     * @return string
189     */
190    public function escape_string($str) {
191        return trim($this->db->quote($str), "'");
192    }
193
194    /**
195     * Aggregation function for SQLite via PDO
196     *
197     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
198     *
199     * @param null|array &$context   (reference) argument where processed data can be stored
200     * @param int         $rownumber current row number
201     * @param string      $string    column value
202     * @param string      $separator separator added between values
203     */
204    public function _pdo_group_concat_step(&$context, $rownumber, $string, $separator = ',') {
205        if(is_null($context)) {
206            $context = array(
207                'sep'  => $separator,
208                'data' => array()
209            );
210        }
211
212        $context['data'][] = $string;
213        return $context;
214    }
215
216    /**
217     * Aggregation function for SQLite via PDO
218     *
219     * @link http://devzone.zend.com/article/863-SQLite-Lean-Mean-DB-Machine
220     *
221     * @param null|array &$context   (reference) data as collected in step callback
222     * @param int         $rownumber number of rows over which the aggregate was performed.
223     * @return null|string
224     */
225    public function _pdo_group_concat_finalize(&$context, $rownumber) {
226        if(!is_array($context)) {
227            return null;
228        }
229        $context['data'] = array_unique($context['data']);
230        if (empty($context['data'][0])) {
231            return null;
232        }
233        return join($context['sep'], $context['data']);
234    }
235
236    /**
237     * fetch the next row as zero indexed array
238     *
239     * @param bool|PDOStatement $res
240     * @return bool|array
241     */
242    public function res_fetch_array($res) {
243        if(!$res) return false;
244
245        return $res->fetch(PDO::FETCH_NUM);
246    }
247
248    /**
249     * fetch the next row as assocative array
250     *
251     * @param bool|PDOStatement $res
252     * @return bool|array
253     */
254    public function res_fetch_assoc($res) {
255        if(!$res) return false;
256
257        return $res->fetch(PDO::FETCH_ASSOC);
258    }
259
260    /**
261     * Count the number of records in result
262     *
263     * This function is really inperformant in PDO and should be avoided!
264     *
265     * @param bool|PDOStatement $res
266     * @return int
267     */
268    public function res2count($res) {
269        if(!$res) return 0;
270
271        if(!$this->data) {
272            $this->data = $this->res2arr($res);
273        }
274
275        return count($this->data);
276    }
277
278    /**
279     * Count the number of records changed last time
280     *
281     * Don't work after a SELECT statement
282     *
283     * @param bool|PDOStatement $res
284     * @return int
285     */
286    public function countChanges($res) {
287        if(!$res) return 0;
288
289        return $res->rowCount();
290    }
291}
292
293// vim:ts=4:sw=4:et:enc=utf-8:
294