File Neevo/Drivers/sqlite3.php

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

Classes
