1: <?php
  2: /**
  3:  * SQLite layer for DBO
  4:  *
  5:  * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
  6:  * Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
  7:  *
  8:  * Licensed under The MIT License
  9:  * For full copyright and license information, please see the LICENSE.txt
 10:  * Redistributions of files must retain the above copyright notice.
 11:  *
 12:  * @copyright     Copyright (c) Cake Software Foundation, Inc. (http://cakefoundation.org)
 13:  * @link          http://cakephp.org CakePHP(tm) Project
 14:  * @package       Cake.Model.Datasource.Database
 15:  * @since         CakePHP(tm) v 0.9.0
 16:  * @license       http://www.opensource.org/licenses/mit-license.php MIT License
 17:  */
 18: 
 19: App::uses('DboSource', 'Model/Datasource');
 20: App::uses('String', 'Utility');
 21: 
 22: /**
 23:  * DBO implementation for the SQLite3 DBMS.
 24:  *
 25:  * A DboSource adapter for SQLite 3 using PDO
 26:  *
 27:  * @package       Cake.Model.Datasource.Database
 28:  */
 29: class Sqlite extends DboSource {
 30: 
 31: /**
 32:  * Datasource Description
 33:  *
 34:  * @var string
 35:  */
 36:     public $description = "SQLite DBO Driver";
 37: 
 38: /**
 39:  * Quote Start
 40:  *
 41:  * @var string
 42:  */
 43:     public $startQuote = '"';
 44: 
 45: /**
 46:  * Quote End
 47:  *
 48:  * @var string
 49:  */
 50:     public $endQuote = '"';
 51: 
 52: /**
 53:  * Base configuration settings for SQLite3 driver
 54:  *
 55:  * @var array
 56:  */
 57:     protected $_baseConfig = array(
 58:         'persistent' => false,
 59:         'database' => null
 60:     );
 61: 
 62: /**
 63:  * SQLite3 column definition
 64:  *
 65:  * @var array
 66:  */
 67:     public $columns = array(
 68:         'primary_key' => array('name' => 'integer primary key autoincrement'),
 69:         'string' => array('name' => 'varchar', 'limit' => '255'),
 70:         'text' => array('name' => 'text'),
 71:         'integer' => array('name' => 'integer', 'limit' => null, 'formatter' => 'intval'),
 72:         'biginteger' => array('name' => 'bigint', 'limit' => 20),
 73:         'float' => array('name' => 'float', 'formatter' => 'floatval'),
 74:         'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
 75:         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
 76:         'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'),
 77:         'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'),
 78:         'binary' => array('name' => 'blob'),
 79:         'boolean' => array('name' => 'boolean')
 80:     );
 81: 
 82: /**
 83:  * List of engine specific additional field parameters used on table creating
 84:  *
 85:  * @var array
 86:  */
 87:     public $fieldParameters = array(
 88:         'collate' => array(
 89:             'value' => 'COLLATE',
 90:             'quote' => false,
 91:             'join' => ' ',
 92:             'column' => 'Collate',
 93:             'position' => 'afterDefault',
 94:             'options' => array(
 95:                 'BINARY', 'NOCASE', 'RTRIM'
 96:             )
 97:         ),
 98:     );
 99: 
100: /**
101:  * Connects to the database using config['database'] as a filename.
102:  *
103:  * @return boolean
104:  * @throws MissingConnectionException
105:  */
106:     public function connect() {
107:         $config = $this->config;
108:         $flags = array(
109:             PDO::ATTR_PERSISTENT => $config['persistent'],
110:             PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
111:         );
112:         try {
113:             $this->_connection = new PDO('sqlite:' . $config['database'], null, null, $flags);
114:             $this->connected = true;
115:         } catch(PDOException $e) {
116:             throw new MissingConnectionException(array(
117:                 'class' => get_class($this),
118:                 'message' => $e->getMessage()
119:             ));
120:         }
121:         return $this->connected;
122:     }
123: 
124: /**
125:  * Check whether the SQLite extension is installed/loaded
126:  *
127:  * @return boolean
128:  */
129:     public function enabled() {
130:         return in_array('sqlite', PDO::getAvailableDrivers());
131:     }
132: 
133: /**
134:  * Returns an array of tables in the database. If there are no tables, an error is raised and the application exits.
135:  *
136:  * @param mixed $data
137:  * @return array Array of table names in the database
138:  */
139:     public function listSources($data = null) {
140:         $cache = parent::listSources();
141:         if ($cache) {
142:             return $cache;
143:         }
144: 
145:         $result = $this->fetchAll("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;", false);
146: 
147:         if (!$result || empty($result)) {
148:             return array();
149:         }
150: 
151:         $tables = array();
152:         foreach ($result as $table) {
153:             $tables[] = $table[0]['name'];
154:         }
155:         parent::listSources($tables);
156:         return $tables;
157:     }
158: 
159: /**
160:  * Returns an array of the fields in given table name.
161:  *
162:  * @param Model|string $model Either the model or table name you want described.
163:  * @return array Fields in table. Keys are name and type
164:  */
165:     public function describe($model) {
166:         $table = $this->fullTableName($model, false, false);
167:         $cache = parent::describe($table);
168:         if ($cache) {
169:             return $cache;
170:         }
171:         $fields = array();
172:         $result = $this->_execute(
173:             'PRAGMA table_info(' . $this->value($table, 'string') . ')'
174:         );
175: 
176:         foreach ($result as $column) {
177:             $column = (array)$column;
178:             $default = ($column['dflt_value'] === 'NULL') ? null : trim($column['dflt_value'], "'");
179: 
180:             $fields[$column['name']] = array(
181:                 'type' => $this->column($column['type']),
182:                 'null' => !$column['notnull'],
183:                 'default' => $default,
184:                 'length' => $this->length($column['type'])
185:             );
186:             if ($column['pk'] == 1) {
187:                 $fields[$column['name']]['key'] = $this->index['PRI'];
188:                 $fields[$column['name']]['null'] = false;
189:                 if (empty($fields[$column['name']]['length'])) {
190:                     $fields[$column['name']]['length'] = 11;
191:                 }
192:             }
193:         }
194: 
195:         $result->closeCursor();
196:         $this->_cacheDescription($table, $fields);
197:         return $fields;
198:     }
199: 
200: /**
201:  * Generates and executes an SQL UPDATE statement for given model, fields, and values.
202:  *
203:  * @param Model $model
204:  * @param array $fields
205:  * @param array $values
206:  * @param mixed $conditions
207:  * @return array
208:  */
209:     public function update(Model $model, $fields = array(), $values = null, $conditions = null) {
210:         if (empty($values) && !empty($fields)) {
211:             foreach ($fields as $field => $value) {
212:                 if (strpos($field, $model->alias . '.') !== false) {
213:                     unset($fields[$field]);
214:                     $field = str_replace($model->alias . '.', "", $field);
215:                     $field = str_replace($model->alias . '.', "", $field);
216:                     $fields[$field] = $value;
217:                 }
218:             }
219:         }
220:         return parent::update($model, $fields, $values, $conditions);
221:     }
222: 
223: /**
224:  * Deletes all the records in a table and resets the count of the auto-incrementing
225:  * primary key, where applicable.
226:  *
227:  * @param string|Model $table A string or model class representing the table to be truncated
228:  * @return boolean SQL TRUNCATE TABLE statement, false if not applicable.
229:  */
230:     public function truncate($table) {
231:         if (in_array('sqlite_sequence', $this->listSources())) {
232:             $this->_execute('DELETE FROM sqlite_sequence where name=' . $this->startQuote . $this->fullTableName($table, false, false) . $this->endQuote);
233:         }
234:         return $this->execute('DELETE FROM ' . $this->fullTableName($table));
235:     }
236: 
237: /**
238:  * Converts database-layer column types to basic types
239:  *
240:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
241:  * @return string Abstract column type (i.e. "string")
242:  */
243:     public function column($real) {
244:         if (is_array($real)) {
245:             $col = $real['name'];
246:             if (isset($real['limit'])) {
247:                 $col .= '(' . $real['limit'] . ')';
248:             }
249:             return $col;
250:         }
251: 
252:         $col = strtolower(str_replace(')', '', $real));
253:         if (strpos($col, '(') !== false) {
254:             list($col) = explode('(', $col);
255:         }
256: 
257:         $standard = array(
258:             'text',
259:             'integer',
260:             'float',
261:             'boolean',
262:             'timestamp',
263:             'date',
264:             'datetime',
265:             'time'
266:         );
267:         if (in_array($col, $standard)) {
268:             return $col;
269:         }
270:         if ($col === 'bigint') {
271:             return 'biginteger';
272:         }
273:         if (strpos($col, 'char') !== false) {
274:             return 'string';
275:         }
276:         if (in_array($col, array('blob', 'clob'))) {
277:             return 'binary';
278:         }
279:         if (strpos($col, 'numeric') !== false || strpos($col, 'decimal') !== false) {
280:             return 'float';
281:         }
282:         return 'text';
283:     }
284: 
285: /**
286:  * Generate ResultSet
287:  *
288:  * @param mixed $results
289:  * @return void
290:  */
291:     public function resultSet($results) {
292:         $this->results = $results;
293:         $this->map = array();
294:         $numFields = $results->columnCount();
295:         $index = 0;
296:         $j = 0;
297: 
298:         //PDO::getColumnMeta is experimental and does not work with sqlite3,
299:         //  so try to figure it out based on the querystring
300:         $querystring = $results->queryString;
301:         if (stripos($querystring, 'SELECT') === 0) {
302:             $last = strripos($querystring, 'FROM');
303:             if ($last !== false) {
304:                 $selectpart = substr($querystring, 7, $last - 8);
305:                 $selects = String::tokenize($selectpart, ',', '(', ')');
306:             }
307:         } elseif (strpos($querystring, 'PRAGMA table_info') === 0) {
308:             $selects = array('cid', 'name', 'type', 'notnull', 'dflt_value', 'pk');
309:         } elseif (strpos($querystring, 'PRAGMA index_list') === 0) {
310:             $selects = array('seq', 'name', 'unique');
311:         } elseif (strpos($querystring, 'PRAGMA index_info') === 0) {
312:             $selects = array('seqno', 'cid', 'name');
313:         }
314:         while ($j < $numFields) {
315:             if (!isset($selects[$j])) {
316:                 $j++;
317:                 continue;
318:             }
319:             if (preg_match('/\bAS\s+(.*)/i', $selects[$j], $matches)) {
320:                 $columnName = trim($matches[1], '"');
321:             } else {
322:                 $columnName = trim(str_replace('"', '', $selects[$j]));
323:             }
324: 
325:             if (strpos($selects[$j], 'DISTINCT') === 0) {
326:                 $columnName = str_ireplace('DISTINCT', '', $columnName);
327:             }
328: 
329:             $metaType = false;
330:             try {
331:                 $metaData = (array)$results->getColumnMeta($j);
332:                 if (!empty($metaData['sqlite:decl_type'])) {
333:                     $metaType = trim($metaData['sqlite:decl_type']);
334:                 }
335:             } catch (Exception $e) {
336:             }
337: 
338:             if (strpos($columnName, '.')) {
339:                 $parts = explode('.', $columnName);
340:                 $this->map[$index++] = array(trim($parts[0]), trim($parts[1]), $metaType);
341:             } else {
342:                 $this->map[$index++] = array(0, $columnName, $metaType);
343:             }
344:             $j++;
345:         }
346:     }
347: 
348: /**
349:  * Fetches the next row from the current result set
350:  *
351:  * @return mixed array with results fetched and mapped to column names or false if there is no results left to fetch
352:  */
353:     public function fetchResult() {
354:         if ($row = $this->_result->fetch(PDO::FETCH_NUM)) {
355:             $resultRow = array();
356:             foreach ($this->map as $col => $meta) {
357:                 list($table, $column, $type) = $meta;
358:                 $resultRow[$table][$column] = $row[$col];
359:                 if ($type === 'boolean' && $row[$col] !== null) {
360:                     $resultRow[$table][$column] = $this->boolean($resultRow[$table][$column]);
361:                 }
362:             }
363:             return $resultRow;
364:         }
365:         $this->_result->closeCursor();
366:         return false;
367:     }
368: 
369: /**
370:  * Returns a limit statement in the correct format for the particular database.
371:  *
372:  * @param integer $limit Limit of results returned
373:  * @param integer $offset Offset from which to start results
374:  * @return string SQL limit/offset statement
375:  */
376:     public function limit($limit, $offset = null) {
377:         if ($limit) {
378:             $rt = sprintf(' LIMIT %u', $limit);
379:             if ($offset) {
380:                 $rt .= sprintf(' OFFSET %u', $offset);
381:             }
382:             return $rt;
383:         }
384:         return null;
385:     }
386: 
387: /**
388:  * Generate a database-native column schema string
389:  *
390:  * @param array $column An array structured like the following: array('name'=>'value', 'type'=>'value'[, options]),
391:  *    where options can be 'default', 'length', or 'key'.
392:  * @return string
393:  */
394:     public function buildColumn($column) {
395:         $name = $type = null;
396:         $column = array_merge(array('null' => true), $column);
397:         extract($column);
398: 
399:         if (empty($name) || empty($type)) {
400:             trigger_error(__d('cake_dev', 'Column name or type not defined in schema'), E_USER_WARNING);
401:             return null;
402:         }
403: 
404:         if (!isset($this->columns[$type])) {
405:             trigger_error(__d('cake_dev', 'Column type %s does not exist', $type), E_USER_WARNING);
406:             return null;
407:         }
408: 
409:         $isPrimary = (isset($column['key']) && $column['key'] === 'primary');
410:         if ($isPrimary && $type === 'integer') {
411:             return $this->name($name) . ' ' . $this->columns['primary_key']['name'];
412:         }
413:         $out = parent::buildColumn($column);
414:         if ($isPrimary && $type === 'biginteger') {
415:             $replacement = 'PRIMARY KEY';
416:             if ($column['null'] === false) {
417:                 $replacement = 'NOT NULL ' . $replacement;
418:             }
419:             return str_replace($this->columns['primary_key']['name'], $replacement, $out);
420:         }
421:         return $out;
422:     }
423: 
424: /**
425:  * Sets the database encoding
426:  *
427:  * @param string $enc Database encoding
428:  * @return boolean
429:  */
430:     public function setEncoding($enc) {
431:         if (!in_array($enc, array("UTF-8", "UTF-16", "UTF-16le", "UTF-16be"))) {
432:             return false;
433:         }
434:         return $this->_execute("PRAGMA encoding = \"{$enc}\"") !== false;
435:     }
436: 
437: /**
438:  * Gets the database encoding
439:  *
440:  * @return string The database encoding
441:  */
442:     public function getEncoding() {
443:         return $this->fetchRow('PRAGMA encoding');
444:     }
445: 
446: /**
447:  * Removes redundant primary key indexes, as they are handled in the column def of the key.
448:  *
449:  * @param array $indexes
450:  * @param string $table
451:  * @return string
452:  */
453:     public function buildIndex($indexes, $table = null) {
454:         $join = array();
455: 
456:         $table = str_replace('"', '', $table);
457:         list($dbname, $table) = explode('.', $table);
458:         $dbname = $this->name($dbname);
459: 
460:         foreach ($indexes as $name => $value) {
461: 
462:             if ($name === 'PRIMARY') {
463:                 continue;
464:             }
465:             $out = 'CREATE ';
466: 
467:             if (!empty($value['unique'])) {
468:                 $out .= 'UNIQUE ';
469:             }
470:             if (is_array($value['column'])) {
471:                 $value['column'] = implode(', ', array_map(array(&$this, 'name'), $value['column']));
472:             } else {
473:                 $value['column'] = $this->name($value['column']);
474:             }
475:             $t = trim($table, '"');
476:             $indexname = $this->name($t . '_' . $name);
477:             $table = $this->name($table);
478:             $out .= "INDEX {$dbname}.{$indexname} ON {$table}({$value['column']});";
479:             $join[] = $out;
480:         }
481:         return $join;
482:     }
483: 
484: /**
485:  * Overrides DboSource::index to handle SQLite index introspection
486:  * Returns an array of the indexes in given table name.
487:  *
488:  * @param string $model Name of model to inspect
489:  * @return array Fields in table. Keys are column and unique
490:  */
491:     public function index($model) {
492:         $index = array();
493:         $table = $this->fullTableName($model, false, false);
494:         if ($table) {
495:             $indexes = $this->query('PRAGMA index_list(' . $table . ')');
496: 
497:             if (is_bool($indexes)) {
498:                 return array();
499:             }
500:             foreach ($indexes as $info) {
501:                 $key = array_pop($info);
502:                 $keyInfo = $this->query('PRAGMA index_info("' . $key['name'] . '")');
503:                 foreach ($keyInfo as $keyCol) {
504:                     if (!isset($index[$key['name']])) {
505:                         $col = array();
506:                         if (preg_match('/autoindex/', $key['name'])) {
507:                             $key['name'] = 'PRIMARY';
508:                         }
509:                         $index[$key['name']]['column'] = $keyCol[0]['name'];
510:                         $index[$key['name']]['unique'] = intval($key['unique'] == 1);
511:                     } else {
512:                         if (!is_array($index[$key['name']]['column'])) {
513:                             $col[] = $index[$key['name']]['column'];
514:                         }
515:                         $col[] = $keyCol[0]['name'];
516:                         $index[$key['name']]['column'] = $col;
517:                     }
518:                 }
519:             }
520:         }
521:         return $index;
522:     }
523: 
524: /**
525:  * Overrides DboSource::renderStatement to handle schema generation with SQLite-style indexes
526:  *
527:  * @param string $type
528:  * @param array $data
529:  * @return string
530:  */
531:     public function renderStatement($type, $data) {
532:         switch (strtolower($type)) {
533:             case 'schema':
534:                 extract($data);
535:                 if (is_array($columns)) {
536:                     $columns = "\t" . implode(",\n\t", array_filter($columns));
537:                 }
538:                 if (is_array($indexes)) {
539:                     $indexes = "\t" . implode("\n\t", array_filter($indexes));
540:                 }
541:                 return "CREATE TABLE {$table} (\n{$columns});\n{$indexes}";
542:             default:
543:                 return parent::renderStatement($type, $data);
544:         }
545:     }
546: 
547: /**
548:  * PDO deals in objects, not resources, so overload accordingly.
549:  *
550:  * @return boolean
551:  */
552:     public function hasResult() {
553:         return is_object($this->_result);
554:     }
555: 
556: /**
557:  * Generate a "drop table" statement for the given table
558:  *
559:  * @param type $table Name of the table to drop
560:  * @return string Drop table SQL statement
561:  */
562:     protected function _dropTable($table) {
563:         return 'DROP TABLE IF EXISTS ' . $this->fullTableName($table) . ";";
564:     }
565: 
566: /**
567:  * Gets the schema name
568:  *
569:  * @return string The schema name
570:  */
571:     public function getSchemaName() {
572:         return "main"; // Sqlite Datasource does not support multidb
573:     }
574: 
575: /**
576:  * Check if the server support nested transactions
577:  *
578:  * @return boolean
579:  */
580:     public function nestedTransactionSupported() {
581:         return $this->useNestedTransactions && version_compare($this->getVersion(), '3.6.8', '>=');
582:     }
583: 
584: }
585: