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