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

Classes
