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