1 <?php
  2 /**
  3  * Neevo - Tiny database layer for PHP. (http://neevo.smasty.net)
  4  *
  5  * This source file is subject to the MIT license that is bundled
  6  * with this package in the file license.txt.
  7  *
  8  * Copyright (c) 2011 Martin Srank (http://smasty.net)
  9  *
 10  */
 11 
 12 
 13 /**
 14  * Neevo SQLite 3 driver (PHP extension 'sqlite3')
 15  *
 16  * Driver configuration:
 17  *  - database (or file)
 18  *  - memory (bool) => use an in-memory database (overrides 'database')
 19  *  - charset => Character encoding to set (defaults to utf-8)
 20  *  - dbcharset => Database character encoding (will be converted to 'charset')
 21  *
 22  *  - updateLimit (bool) => Set TRUE if SQLite driver was compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT
 23  *  - resource (instance of SQLite3) => Existing SQLite 3 link
 24  *  - lazy, table_prefix => see NeevoConnection
 25  *
 26  * Since SQLite 3 only allows unbuffered queries, number of result rows and seeking
 27  * is not supported for this driver.
 28  *
 29  * @author Martin Srank
 30  * @package Neevo\Drivers
 31  */
 32 class NeevoDriverSQLite3 extends NeevoParser implements INeevoDriver {
 33 
 34 
 35     /** @var string */
 36     private $dbCharset;
 37 
 38     /** @var string */
 39     private $charset;
 40 
 41     /** @var bool */
 42     private $updateLimit;
 43 
 44     /** @var SQLite3Result */
 45     private $resource;
 46 
 47     /** @var int */
 48     private $affectedRows;
 49 
 50     /** @var array */
 51     private $tblData = array();
 52 
 53 
 54     /**
 55      * Check for required PHP extension.
 56      * @return void
 57      * @throws NeevoDriverException
 58      */
 59     public function __construct(NeevoBaseStmt $statement = null){
 60         if(!extension_loaded("sqlite3"))
 61             throw new NeevoDriverException("Cannot instantiate Neevo SQLite 3 driver - PHP extension 'sqlite3' not loaded.");
 62         if($statement instanceof NeevoBaseStmt)
 63             parent::__construct($statement);
 64     }
 65 
 66 
 67     /**
 68      * Create connection to database.
 69      * @param array $config Configuration options
 70      * @return void
 71      * @throws NeevoException
 72      */
 73     public function connect(array $config){
 74         //NeevoConnection::alias($config, 'database', 'file');
 75         NeevoConnection::alias($config, 'updateLimit', 'update_limit');
 76 
 77         $defaults = array(
 78             'memory' => false,
 79             'resource' => null,
 80             'updateLimit' => false,
 81             'charset' => 'UTF-8',
 82             'dbcharset' => 'UTF-8'
 83         );
 84 
 85         $config += $defaults;
 86 
 87         if($config['memory'])
 88             $config['database'] = ':memory:';
 89 
 90         // Connect
 91         if($config['resource'] instanceof SQLite3)
 92             $connection = $config['resource'];
 93         else{
 94             try{
 95                 $connection = new SQLite3($config['database']);
 96             } catch(Exception $e){
 97                     throw new NeevoException($e->getMessage(), $e->getCode());
 98             }
 99         }
100 
101         if(!($connection instanceof SQLite3))
102             throw new NeevoException("Opening database file '$config[database]' failed.");
103 
104         $this->resource = $connection;
105         $this->updateLimit = (bool) $config['updateLimit'];
106 
107         // Set charset
108         $this->dbCharset = $config['dbcharset'];
109         $this->charset = $config['charset'];
110         if(strcasecmp($this->dbCharset, $this->charset) === 0)
111             $this->dbCharset = $this->charset = null;
112     }
113 
114 
115     /**
116      * Close the connection.
117      * @return void
118      */
119     public function closeConnection(){
120         $this->resource->close();
121     }
122 
123 
124 
125     /**
126      * Free memory used by given result.
127      *
128      * NeevoResult automatically NULLs the resource, so this is not necessary.
129      * @param SQLite3Result $resultSet
130      * @return bool
131      */
132     public function freeResultSet($resultSet){
133         return true;
134     }
135 
136 
137     /**
138      * Execute given SQL statement.
139      * @param string $queryString
140      * @return SQLite3Result|bool
141      * @throws NeevoException
142      */
143     public function runQuery($queryString){
144 
145         $this->affectedRows = false;
146         if($this->dbCharset !== null)
147             $queryString = iconv($this->charset, $this->dbCharset . '//IGNORE', $queryString);
148 
149         $result = $this->resource->query($queryString);
150 
151         if($result === false)
152             throw new NeevoException($this->resource->lastErrorMsg(), $this->resource->lastErrorCode(), $queryString);
153 
154         $this->affectedRows = $this->resource->changes();
155         return $result;
156     }
157 
158 
159     /**
160      * Begin a transaction if supported.
161      * @param string $savepoint
162      * @return void
163      */
164     public function beginTransaction($savepoint = null){
165         $this->runQuery($savepoint ? "SAVEPOINT $savepoint" : 'BEGIN');
166     }
167 
168 
169     /**
170      * Commit statements in a transaction.
171      * @param string $savepoint
172      * @return void
173      */
174     public function commit($savepoint = null){
175         $this->runQuery($savepoint ? "RELEASE SAVEPOINT $savepoint" : 'COMMIT');
176     }
177 
178 
179     /**
180      * Rollback changes in a transaction.
181      * @param string $savepoint
182      * @return void
183      */
184     public function rollback($savepoint = null){
185         $this->runQuery($savepoint ? "ROLLBACK TO SAVEPOINT $savepoint" : 'ROLLBACK');
186     }
187 
188 
189     /**
190      * Fetch row from given result set as an associative array.
191      * @param SQLite3Result $resultSet
192      * @return array
193      */
194     public function fetch($resultSet){
195         $row = $resultSet->fetchArray(SQLITE3_ASSOC);
196         $charset = $this->charset === null ? null : $this->charset.'//TRANSLIT';
197 
198         if($row){
199             $fields = array();
200             foreach($row as $key=>$val){
201                 if($charset !== null && is_string($val))
202                     $val = iconv($this->dbcharset, $charset, $val);
203                 $fields[str_replace(array('[', ']'), '', $key)] = $val;
204             }
205             return $fields;
206         }
207         return $row;
208     }
209 
210 
211     /**
212      * Move internal result pointer.
213      *
214      * Not supported because of unbuffered queries.
215      * @param SQLite3Result $resultSet
216      * @param int $offset
217      * @return bool
218      * @throws NeevoDriverException
219      */
220     public function seek($resultSet, $offset){
221         throw new NeevoDriverException('Cannot seek on unbuffered result.');
222     }
223 
224 
225     /**
226      * Get the ID generated in the INSERT statement.
227      * @return int
228      */
229     public function getInsertId(){
230         return $this->resource->lastInsertRowID();
231     }
232 
233 
234     /**
235      * Randomize result order.
236      * @param NeevoBaseStmt $tatement
237      * @return void
238      */
239     public function randomizeOrder(NeevoBaseStmt $statement){
240         $statement->order('RANDOM()');
241     }
242 
243 
244     /**
245      * Get the number of rows in the given result set.
246      *
247      * Not supported because of unbuffered queries.
248      * @param SQLite3Result $resultSet
249      * @return int|FALSE
250      * @throws NeevoDriverException
251      */
252     public function getNumRows($resultSet){
253         throw new NeevoDriverException('Cannot count rows on unbuffered result.');
254     }
255 
256 
257     /**
258      * Get the umber of affected rows in previous operation.
259      * @return int
260      */
261     public function getAffectedRows(){
262         return $this->affectedRows;
263     }
264 
265 
266     /**
267      * Escape given value.
268      * @param mixed $value
269      * @param string $type
270      * @throws InvalidArgumentException
271      * @return mixed
272      */
273     public function escape($value, $type){
274         switch($type){
275             case Neevo::BOOL:
276                 return $value ? 1 :0;
277 
278             case Neevo::TEXT:
279                 return "'". $this->resource->escapeString($value) ."'";
280 
281             case Neevo::IDENTIFIER:
282                 return str_replace('[*]', '*', '[' . str_replace('.', '].[', $value) . ']');
283 
284             case Neevo::BINARY:
285                 return "X'" . bin2hex((string) $value) . "'";
286 
287             case Neevo::DATETIME:
288                 return ($value instanceof DateTime) ? $value->format("'Y-m-d H:i:s'") : date("'Y-m-d H:i:s'", $value);
289 
290             default:
291                 throw new InvalidArgumentException('Unsupported data type');
292                 break;
293         }
294     }
295 
296 
297     /**
298      * Decode given value.
299      * @param mixed $value
300      * @param string $type
301      * @return mixed
302      */
303     public function unescape($value, $type){
304         if($type === Neevo::BINARY)
305             return $value;
306         throw new InvalidArgumentException('Unsupported data type.');
307     }
308 
309 
310     /**
311      * Get the PRIMARY KEY column for given table.
312      * @param string $table
313      * @return string
314      */
315     public function getPrimaryKey($table){
316         $key = '';
317         $pos = strpos($table, '.');
318         if($pos !== false)
319             $table = substr($table, $pos + 1);
320         if(isset($this->tblData[$table]))
321             $sql = $this->tblData[$table];
322         else{
323             $q = $this->runQuery("SELECT sql FROM sqlite_master WHERE tbl_name='$table'");
324             $r = $this->fetch($q);
325             if($r === false)
326                 return '';
327             $this->tblData[$table] = $sql = $r['sql'];
328         }
329 
330         $sql = explode("\n", $sql);
331         foreach($sql as $field){
332             $field = trim($field);
333             if(stripos($field, 'PRIMARY KEY') !== false && $key === '')
334                 $key = preg_replace('~^"(\w+)".*$~i', '$1', $field);
335         }
336         return $key;
337     }
338 
339 
340     /**
341      * Get types of columns in given result set.
342      * @param SQLite3Result $resultSet
343      * @param string $table
344      * @return array
345      */
346     public function getColumnTypes($resultSet, $table){
347         if($table === null)
348             return array();
349         if(isset($this->tblData[$table]))
350             $sql = $this->tblData[$table];
351         else{
352             $q = $this->runQuery("SELECT sql FROM sqlite_master WHERE tbl_name='$table'");
353             $r = $this->fetch($q);
354             if($r === false){
355                 return array();
356             }
357             $this->tblData[$table] = $sql = $r['sql'];
358         }
359         $sql = explode("\n", $sql);
360 
361         $cols = array();
362         foreach($sql as $field){
363             $field = trim($field);
364             preg_match('~^"(\w+)"\s+(integer|real|numeric|text|blob).+$~i', $field, $m);
365             if(isset($m[1], $m[2]))
366                 $cols[$m[1]] = $m[2];
367         }
368         return $cols;
369     }
370 
371 
372     /*  ************  NeevoParser overrides  ************  */
373 
374 
375     /**
376      * Parse UPDATE statement.
377      * @return string
378      */
379     protected function parseUpdateStmt(){
380         $sql = parent::parseUpdateStmt();
381         return $this->updateLimit ? $this->applyLimit($sql . $this->clauses[3]) : $sql;
382     }
383 
384 
385     /**
386      * Parse DELETE statement.
387      * @return string
388      */
389     protected function parseDeleteStmt(){
390         $sql = parent::parseDeleteStmt();
391         return $this->updateLimit ? $this->applyLimit($sql . $this->clauses[3]) : $sql;
392     }
393 
394 
395 }
396 

Neevo Public API API documentation generated by ApiGen 2.8.0