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