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