1: <?php
  2: 
  3: /**
  4:  * This file is part of the "dibi" - smart database abstraction layer.
  5:  *
  6:  * Copyright (c) 2005 David Grudl (http://davidgrudl.com)
  7:  *
  8:  * For the full copyright and license information, please view
  9:  * the file license.txt that was distributed with this source code.
 10:  */
 11: 
 12: 
 13: require_once dirname(__FILE__) . '/sqlite.reflector.php';
 14: 
 15: 
 16: /**
 17:  * The dibi driver for SQLite3 database.
 18:  *
 19:  * Driver options:
 20:  *   - database (or file) => the filename of the SQLite3 database
 21:  *   - formatDate => how to format date in SQL (@see date)
 22:  *   - formatDateTime => how to format datetime in SQL (@see date)
 23:  *   - dbcharset => database character encoding (will be converted to 'charset')
 24:  *   - charset => character encoding to set (default is UTF-8)
 25:  *   - resource (SQLite3) => existing connection resource
 26:  *   - lazy, profiler, result, substitutes, ... => see DibiConnection options
 27:  *
 28:  * @author     David Grudl
 29:  * @package    dibi\drivers
 30:  */
 31: class DibiSqlite3Driver extends DibiObject implements IDibiDriver, IDibiResultDriver
 32: {
 33:     /** @var SQLite3  Connection resource */
 34:     private $connection;
 35: 
 36:     /** @var SQLite3Result  Resultset resource */
 37:     private $resultSet;
 38: 
 39:     /** @var string  Date and datetime format */
 40:     private $fmtDate, $fmtDateTime;
 41: 
 42:     /** @var string  character encoding */
 43:     private $dbcharset, $charset;
 44: 
 45: 
 46: 
 47:     /**
 48:      * @throws DibiNotSupportedException
 49:      */
 50:     public function __construct()
 51:     {
 52:         if (!extension_loaded('sqlite3')) {
 53:             throw new DibiNotSupportedException("PHP extension 'sqlite3' is not loaded.");
 54:         }
 55:     }
 56: 
 57: 
 58: 
 59:     /**
 60:      * Connects to a database.
 61:      * @return void
 62:      * @throws DibiException
 63:      */
 64:     public function connect(array &$config)
 65:     {
 66:         DibiConnection::alias($config, 'database', 'file');
 67:         $this->fmtDate = isset($config['formatDate']) ? $config['formatDate'] : 'U';
 68:         $this->fmtDateTime = isset($config['formatDateTime']) ? $config['formatDateTime'] : 'U';
 69: 
 70:         if (isset($config['resource']) && $config['resource'] instanceof SQLite3) {
 71:             $this->connection = $config['resource'];
 72:         } else try {
 73:             $this->connection = new SQLite3($config['database']);
 74: 
 75:         } catch (Exception $e) {
 76:             throw new DibiDriverException($e->getMessage(), $e->getCode());
 77:         }
 78: 
 79:         $this->dbcharset = empty($config['dbcharset']) ? 'UTF-8' : $config['dbcharset'];
 80:         $this->charset = empty($config['charset']) ? 'UTF-8' : $config['charset'];
 81:         if (strcasecmp($this->dbcharset, $this->charset) === 0) {
 82:             $this->dbcharset = $this->charset = NULL;
 83:         }
 84: 
 85:         // enable foreign keys support (defaultly disabled; if disabled then foreign key constraints are not enforced)
 86:         $version = SQLite3::version();
 87:         if ($version['versionNumber'] >= '3006019') {
 88:             $this->query("PRAGMA foreign_keys = ON");
 89:         }
 90:     }
 91: 
 92: 
 93: 
 94:     /**
 95:      * Disconnects from a database.
 96:      * @return void
 97:      */
 98:     public function disconnect()
 99:     {
100:         $this->connection->close();
101:     }
102: 
103: 
104: 
105:     /**
106:      * Executes the SQL query.
107:      * @param  string      SQL statement.
108:      * @return IDibiResultDriver|NULL
109:      * @throws DibiDriverException
110:      */
111:     public function query($sql)
112:     {
113:         if ($this->dbcharset !== NULL) {
114:             $sql = iconv($this->charset, $this->dbcharset . '//IGNORE', $sql);
115:         }
116: 
117:         $res = @$this->connection->query($sql); // intentionally @
118:         if ($this->connection->lastErrorCode()) {
119:             throw new DibiDriverException($this->connection->lastErrorMsg(), $this->connection->lastErrorCode(), $sql);
120: 
121:         } elseif ($res instanceof SQLite3Result) {
122:             return $this->createResultDriver($res);
123:         }
124:     }
125: 
126: 
127: 
128:     /**
129:      * Gets the number of affected rows by the last INSERT, UPDATE or DELETE query.
130:      * @return int|FALSE  number of rows or FALSE on error
131:      */
132:     public function getAffectedRows()
133:     {
134:         return $this->connection->changes();
135:     }
136: 
137: 
138: 
139:     /**
140:      * Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
141:      * @return int|FALSE  int on success or FALSE on failure
142:      */
143:     public function getInsertId($sequence)
144:     {
145:         return $this->connection->lastInsertRowID();
146:     }
147: 
148: 
149: 
150:     /**
151:      * Begins a transaction (if supported).
152:      * @param  string  optional savepoint name
153:      * @return void
154:      * @throws DibiDriverException
155:      */
156:     public function begin($savepoint = NULL)
157:     {
158:         $this->query($savepoint ? "SAVEPOINT $savepoint" : 'BEGIN');
159:     }
160: 
161: 
162: 
163:     /**
164:      * Commits statements in a transaction.
165:      * @param  string  optional savepoint name
166:      * @return void
167:      * @throws DibiDriverException
168:      */
169:     public function commit($savepoint = NULL)
170:     {
171:         $this->query($savepoint ? "RELEASE SAVEPOINT $savepoint" : 'COMMIT');
172:     }
173: 
174: 
175: 
176:     /**
177:      * Rollback changes in a transaction.
178:      * @param  string  optional savepoint name
179:      * @return void
180:      * @throws DibiDriverException
181:      */
182:     public function rollback($savepoint = NULL)
183:     {
184:         $this->query($savepoint ? "ROLLBACK TO SAVEPOINT $savepoint" : 'ROLLBACK');
185:     }
186: 
187: 
188: 
189:     /**
190:      * Returns the connection resource.
191:      * @return mixed
192:      */
193:     public function getResource()
194:     {
195:         return $this->connection;
196:     }
197: 
198: 
199: 
200:     /**
201:      * Returns the connection reflector.
202:      * @return IDibiReflector
203:      */
204:     public function getReflector()
205:     {
206:         return new DibiSqliteReflector($this);
207:     }
208: 
209: 
210: 
211:     /**
212:      * Result set driver factory.
213:      * @param  SQLite3Result
214:      * @return IDibiResultDriver
215:      */
216:     public function createResultDriver(SQLite3Result $resource)
217:     {
218:         $res = clone $this;
219:         $res->resultSet = $resource;
220:         return $res;
221:     }
222: 
223: 
224: 
225:     /********************* SQL ****************d*g**/
226: 
227: 
228: 
229:     /**
230:      * Encodes data for use in a SQL statement.
231:      * @param  mixed     value
232:      * @param  string    type (dibi::TEXT, dibi::BOOL, ...)
233:      * @return string    encoded value
234:      * @throws InvalidArgumentException
235:      */
236:     public function escape($value, $type)
237:     {
238:         switch ($type) {
239:         case dibi::TEXT:
240:             return "'" . $this->connection->escapeString($value) . "'";
241: 
242:         case dibi::BINARY:
243:             return "X'" . bin2hex((string) $value) . "'";
244: 
245:         case dibi::IDENTIFIER:
246:             return '[' . strtr($value, '[]', '  ') . ']';
247: 
248:         case dibi::BOOL:
249:             return $value ? 1 : 0;
250: 
251:         case dibi::DATE:
252:             return $value instanceof DateTime ? $value->format($this->fmtDate) : date($this->fmtDate, $value);
253: 
254:         case dibi::DATETIME:
255:             return $value instanceof DateTime ? $value->format($this->fmtDateTime) : date($this->fmtDateTime, $value);
256: 
257:         default:
258:             throw new InvalidArgumentException('Unsupported type.');
259:         }
260:     }
261: 
262: 
263: 
264:     /**
265:      * Encodes string for use in a LIKE statement.
266:      * @param  string
267:      * @param  int
268:      * @return string
269:      */
270:     public function escapeLike($value, $pos)
271:     {
272:         $value = addcslashes($this->connection->escapeString($value), '%_\\');
273:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'") . " ESCAPE '\\'";
274:     }
275: 
276: 
277: 
278:     /**
279:      * Decodes data from result set.
280:      * @param  string    value
281:      * @param  string    type (dibi::BINARY)
282:      * @return string    decoded value
283:      * @throws InvalidArgumentException
284:      */
285:     public function unescape($value, $type)
286:     {
287:         if ($type === dibi::BINARY) {
288:             return $value;
289:         }
290:         throw new InvalidArgumentException('Unsupported type.');
291:     }
292: 
293: 
294: 
295:     /**
296:      * Injects LIMIT/OFFSET to the SQL query.
297:      * @param  string &$sql  The SQL query that will be modified.
298:      * @param  int $limit
299:      * @param  int $offset
300:      * @return void
301:      */
302:     public function applyLimit(&$sql, $limit, $offset)
303:     {
304:         if ($limit < 0 && $offset < 1) return;
305:         $sql .= ' LIMIT ' . $limit . ($offset > 0 ? ' OFFSET ' . (int) $offset : '');
306:     }
307: 
308: 
309: 
310:     /********************* result set ****************d*g**/
311: 
312: 
313: 
314:     /**
315:      * Automatically frees the resources allocated for this result set.
316:      * @return void
317:      */
318:     public function __destruct()
319:     {
320:         $this->resultSet && @$this->free();
321:     }
322: 
323: 
324: 
325:     /**
326:      * Returns the number of rows in a result set.
327:      * @return int
328:      * @throws DibiNotSupportedException
329:      */
330:     public function getRowCount()
331:     {
332:         throw new DibiNotSupportedException('Row count is not available for unbuffered queries.');
333:     }
334: 
335: 
336: 
337:     /**
338:      * Fetches the row at current position and moves the internal cursor to the next position.
339:      * @param  bool     TRUE for associative array, FALSE for numeric
340:      * @return array    array on success, nonarray if no next record
341:      */
342:     public function fetch($assoc)
343:     {
344:         $row = $this->resultSet->fetchArray($assoc ? SQLITE3_ASSOC : SQLITE3_NUM);
345:         $charset = $this->charset === NULL ? NULL : $this->charset . '//TRANSLIT';
346:         if ($row && ($assoc || $charset)) {
347:             $tmp = array();
348:             foreach ($row as $k => $v) {
349:                 if ($charset !== NULL && is_string($v)) {
350:                     $v = iconv($this->dbcharset, $charset, $v);
351:                 }
352:                 $tmp[str_replace(array('[', ']'), '', $k)] = $v;
353:             }
354:             return $tmp;
355:         }
356:         return $row;
357:     }
358: 
359: 
360: 
361:     /**
362:      * Moves cursor position without fetching row.
363:      * @param  int      the 0-based cursor pos to seek to
364:      * @return boolean  TRUE on success, FALSE if unable to seek to specified record
365:      * @throws DibiNotSupportedException
366:      */
367:     public function seek($row)
368:     {
369:         throw new DibiNotSupportedException('Cannot seek an unbuffered result set.');
370:     }
371: 
372: 
373: 
374:     /**
375:      * Frees the resources allocated for this result set.
376:      * @return void
377:      */
378:     public function free()
379:     {
380:         $this->resultSet->finalize();
381:         $this->resultSet = NULL;
382:     }
383: 
384: 
385: 
386:     /**
387:      * Returns metadata for all columns in a result set.
388:      * @return array
389:      */
390:     public function getResultColumns()
391:     {
392:         $count = $this->resultSet->numColumns();
393:         $columns = array();
394:         static $types = array(SQLITE3_INTEGER => 'int', SQLITE3_FLOAT => 'float', SQLITE3_TEXT => 'text', SQLITE3_BLOB => 'blob', SQLITE3_NULL => 'null');
395:         for ($i = 0; $i < $count; $i++) {
396:             $columns[] = array(
397:                 'name'  => $this->resultSet->columnName($i),
398:                 'table' => NULL,
399:                 'fullname' => $this->resultSet->columnName($i),
400:                 'nativetype' => $types[$this->resultSet->columnType($i)],
401:             );
402:         }
403:         return $columns;
404:     }
405: 
406: 
407: 
408:     /**
409:      * Returns the result set resource.
410:      * @return mixed
411:      */
412:     public function getResultResource()
413:     {
414:         return $this->resultSet;
415:     }
416: 
417: 
418: 
419:     /********************* user defined functions ****************d*g**/
420: 
421: 
422: 
423:     /**
424:      * Registers an user defined function for use in SQL statements.
425:      * @param  string  function name
426:      * @param  mixed   callback
427:      * @param  int     num of arguments
428:      * @return void
429:      */
430:     public function registerFunction($name, $callback, $numArgs = -1)
431:     {
432:         $this->connection->createFunction($name, $callback, $numArgs);
433:     }
434: 
435: 
436: 
437:     /**
438:      * Registers an aggregating user defined function for use in SQL statements.
439:      * @param  string  function name
440:      * @param  mixed   callback called for each row of the result set
441:      * @param  mixed   callback called to aggregate the "stepped" data from each row
442:      * @param  int     num of arguments
443:      * @return void
444:      */
445:     public function registerAggregateFunction($name, $rowCallback, $agrCallback, $numArgs = -1)
446:     {
447:         $this->connection->createAggregate($name, $rowCallback, $agrCallback, $numArgs);
448:     }
449: 
450: }
451: