Mercurial > hg > Members > shoshi > webvirt
comparison cake/libs/model/datasources/dbo/dbo_sqlite.php @ 0:261e66bd5a0c
hg init
author | Shoshi TAMAKI <shoshi@cr.ie.u-ryukyu.ac.jp> |
---|---|
date | Sun, 24 Jul 2011 21:08:31 +0900 |
parents | |
children |
comparison
equal
deleted
inserted
replaced
-1:000000000000 | 0:261e66bd5a0c |
---|---|
1 <?php | |
2 /** | |
3 * SQLite layer for DBO | |
4 * | |
5 * PHP versions 4 and 5 | |
6 * | |
7 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org) | |
8 * Copyright 2005-2010, 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-2010, Cake Software Foundation, Inc. (http://cakefoundation.org) | |
14 * @link http://cakephp.org CakePHP(tm) Project | |
15 * @package cake | |
16 * @subpackage cake.cake.libs.model.datasources.dbo | |
17 * @since CakePHP(tm) v 0.9.0 | |
18 * @license MIT License (http://www.opensource.org/licenses/mit-license.php) | |
19 */ | |
20 | |
21 /** | |
22 * DBO implementation for the SQLite DBMS. | |
23 * | |
24 * Long description for class | |
25 * | |
26 * @package cake | |
27 * @subpackage cake.cake.libs.model.datasources.dbo | |
28 */ | |
29 class DboSqlite extends DboSource { | |
30 | |
31 /** | |
32 * Datasource Description | |
33 * | |
34 * @var string | |
35 */ | |
36 var $description = "SQLite DBO Driver"; | |
37 | |
38 /** | |
39 * Opening quote for quoted identifiers | |
40 * | |
41 * @var string | |
42 */ | |
43 var $startQuote = '"'; | |
44 | |
45 /** | |
46 * Closing quote for quoted identifiers | |
47 * | |
48 * @var string | |
49 */ | |
50 var $endQuote = '"'; | |
51 | |
52 /** | |
53 * Keeps the transaction statistics of CREATE/UPDATE/DELETE queries | |
54 * | |
55 * @var array | |
56 * @access protected | |
57 */ | |
58 var $_queryStats = array(); | |
59 | |
60 /** | |
61 * Base configuration settings for SQLite driver | |
62 * | |
63 * @var array | |
64 */ | |
65 var $_baseConfig = array( | |
66 'persistent' => true, | |
67 'database' => null | |
68 ); | |
69 | |
70 /** | |
71 * Index of basic SQL commands | |
72 * | |
73 * @var array | |
74 * @access protected | |
75 */ | |
76 var $_commands = array( | |
77 'begin' => 'BEGIN TRANSACTION', | |
78 'commit' => 'COMMIT TRANSACTION', | |
79 'rollback' => 'ROLLBACK TRANSACTION' | |
80 ); | |
81 | |
82 /** | |
83 * SQLite column definition | |
84 * | |
85 * @var array | |
86 */ | |
87 var $columns = array( | |
88 'primary_key' => array('name' => 'integer primary key'), | |
89 'string' => array('name' => 'varchar', 'limit' => '255'), | |
90 'text' => array('name' => 'text'), | |
91 'integer' => array('name' => 'integer', 'limit' => 11, 'formatter' => 'intval'), | |
92 'float' => array('name' => 'float', 'formatter' => 'floatval'), | |
93 'datetime' => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'), | |
94 'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'), | |
95 'time' => array('name' => 'time', 'format' => 'H:i:s', 'formatter' => 'date'), | |
96 'date' => array('name' => 'date', 'format' => 'Y-m-d', 'formatter' => 'date'), | |
97 'binary' => array('name' => 'blob'), | |
98 'boolean' => array('name' => 'boolean') | |
99 ); | |
100 | |
101 /** | |
102 * List of engine specific additional field parameters used on table creating | |
103 * | |
104 * @var array | |
105 * @access public | |
106 */ | |
107 var $fieldParameters = array( | |
108 'collate' => array( | |
109 'value' => 'COLLATE', | |
110 'quote' => false, | |
111 'join' => ' ', | |
112 'column' => 'Collate', | |
113 'position' => 'afterDefault', | |
114 'options' => array( | |
115 'BINARY', 'NOCASE', 'RTRIM' | |
116 ) | |
117 ), | |
118 ); | |
119 | |
120 /** | |
121 * Connects to the database using config['database'] as a filename. | |
122 * | |
123 * @param array $config Configuration array for connecting | |
124 * @return mixed | |
125 */ | |
126 function connect() { | |
127 $config = $this->config; | |
128 | |
129 if (!$config['persistent']) { | |
130 $this->connection = sqlite_open($config['database']); | |
131 } else { | |
132 $this->connection = sqlite_popen($config['database']); | |
133 } | |
134 $this->connected = is_resource($this->connection); | |
135 | |
136 if ($this->connected) { | |
137 $this->_execute('PRAGMA count_changes = 1;'); | |
138 } | |
139 return $this->connected; | |
140 } | |
141 | |
142 /** | |
143 * Check that SQLite is enabled/installed | |
144 * | |
145 * @return boolean | |
146 */ | |
147 function enabled() { | |
148 return extension_loaded('sqlite'); | |
149 } | |
150 /** | |
151 * Disconnects from database. | |
152 * | |
153 * @return boolean True if the database could be disconnected, else false | |
154 */ | |
155 function disconnect() { | |
156 @sqlite_close($this->connection); | |
157 $this->connected = false; | |
158 return $this->connected; | |
159 } | |
160 | |
161 /** | |
162 * Executes given SQL statement. | |
163 * | |
164 * @param string $sql SQL statement | |
165 * @return resource Result resource identifier | |
166 */ | |
167 function _execute($sql) { | |
168 $result = sqlite_query($this->connection, $sql); | |
169 | |
170 if (preg_match('/^(INSERT|UPDATE|DELETE)/', $sql)) { | |
171 $this->resultSet($result); | |
172 list($this->_queryStats) = $this->fetchResult(); | |
173 } | |
174 return $result; | |
175 } | |
176 | |
177 /** | |
178 * Overrides DboSource::execute() to correctly handle query statistics | |
179 * | |
180 * @param string $sql | |
181 * @return unknown | |
182 */ | |
183 function execute($sql) { | |
184 $result = parent::execute($sql); | |
185 $this->_queryStats = array(); | |
186 return $result; | |
187 } | |
188 | |
189 /** | |
190 * Returns an array of tables in the database. If there are no tables, an error is raised and the application exits. | |
191 * | |
192 * @return array Array of tablenames in the database | |
193 */ | |
194 function listSources() { | |
195 $cache = parent::listSources(); | |
196 | |
197 if ($cache != null) { | |
198 return $cache; | |
199 } | |
200 $result = $this->fetchAll("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;", false); | |
201 | |
202 if (empty($result)) { | |
203 return array(); | |
204 } else { | |
205 $tables = array(); | |
206 foreach ($result as $table) { | |
207 $tables[] = $table[0]['name']; | |
208 } | |
209 parent::listSources($tables); | |
210 return $tables; | |
211 } | |
212 return array(); | |
213 } | |
214 | |
215 /** | |
216 * Returns an array of the fields in given table name. | |
217 * | |
218 * @param string $tableName Name of database table to inspect | |
219 * @return array Fields in table. Keys are name and type | |
220 */ | |
221 function describe(&$model) { | |
222 $cache = parent::describe($model); | |
223 if ($cache != null) { | |
224 return $cache; | |
225 } | |
226 $fields = array(); | |
227 $result = $this->fetchAll('PRAGMA table_info(' . $this->fullTableName($model) . ')'); | |
228 | |
229 foreach ($result as $column) { | |
230 $fields[$column[0]['name']] = array( | |
231 'type' => $this->column($column[0]['type']), | |
232 'null' => !$column[0]['notnull'], | |
233 'default' => $column[0]['dflt_value'], | |
234 'length' => $this->length($column[0]['type']) | |
235 ); | |
236 if ($column[0]['pk'] == 1) { | |
237 $colLength = $this->length($column[0]['type']); | |
238 $fields[$column[0]['name']] = array( | |
239 'type' => $fields[$column[0]['name']]['type'], | |
240 'null' => false, | |
241 'default' => $column[0]['dflt_value'], | |
242 'key' => $this->index['PRI'], | |
243 'length'=> ($colLength != null) ? $colLength : 11 | |
244 ); | |
245 } | |
246 } | |
247 | |
248 $this->__cacheDescription($model->tablePrefix . $model->table, $fields); | |
249 return $fields; | |
250 } | |
251 | |
252 /** | |
253 * Returns a quoted and escaped string of $data for use in an SQL statement. | |
254 * | |
255 * @param string $data String to be prepared for use in an SQL statement | |
256 * @return string Quoted and escaped | |
257 */ | |
258 function value($data, $column = null, $safe = false) { | |
259 $parent = parent::value($data, $column, $safe); | |
260 | |
261 if ($parent != null) { | |
262 return $parent; | |
263 } | |
264 if ($data === null) { | |
265 return 'NULL'; | |
266 } | |
267 if ($data === '' && $column !== 'integer' && $column !== 'float' && $column !== 'boolean') { | |
268 return "''"; | |
269 } | |
270 switch ($column) { | |
271 case 'boolean': | |
272 $data = $this->boolean((bool)$data); | |
273 break; | |
274 case 'integer': | |
275 case 'float': | |
276 if ($data === '') { | |
277 return 'NULL'; | |
278 } | |
279 default: | |
280 $data = sqlite_escape_string($data); | |
281 break; | |
282 } | |
283 return "'" . $data . "'"; | |
284 } | |
285 | |
286 /** | |
287 * Generates and executes an SQL UPDATE statement for given model, fields, and values. | |
288 * | |
289 * @param Model $model | |
290 * @param array $fields | |
291 * @param array $values | |
292 * @param mixed $conditions | |
293 * @return array | |
294 */ | |
295 function update(&$model, $fields = array(), $values = null, $conditions = null) { | |
296 if (empty($values) && !empty($fields)) { | |
297 foreach ($fields as $field => $value) { | |
298 if (strpos($field, $model->alias . '.') !== false) { | |
299 unset($fields[$field]); | |
300 $field = str_replace($model->alias . '.', "", $field); | |
301 $field = str_replace($model->alias . '.', "", $field); | |
302 $fields[$field] = $value; | |
303 } | |
304 } | |
305 } | |
306 $result = parent::update($model, $fields, $values, $conditions); | |
307 return $result; | |
308 } | |
309 | |
310 /** | |
311 * Deletes all the records in a table and resets the count of the auto-incrementing | |
312 * primary key, where applicable. | |
313 * | |
314 * @param mixed $table A string or model class representing the table to be truncated | |
315 * @return boolean SQL TRUNCATE TABLE statement, false if not applicable. | |
316 * @access public | |
317 */ | |
318 function truncate($table) { | |
319 return $this->execute('DELETE From ' . $this->fullTableName($table)); | |
320 } | |
321 | |
322 /** | |
323 * Returns a formatted error message from previous database operation. | |
324 * | |
325 * @return string Error message | |
326 */ | |
327 function lastError() { | |
328 $error = sqlite_last_error($this->connection); | |
329 if ($error) { | |
330 return $error.': '.sqlite_error_string($error); | |
331 } | |
332 return null; | |
333 } | |
334 | |
335 /** | |
336 * Returns number of affected rows in previous database operation. If no previous operation exists, this returns false. | |
337 * | |
338 * @return integer Number of affected rows | |
339 */ | |
340 function lastAffected() { | |
341 if (!empty($this->_queryStats)) { | |
342 foreach (array('rows inserted', 'rows updated', 'rows deleted') as $key) { | |
343 if (array_key_exists($key, $this->_queryStats)) { | |
344 return $this->_queryStats[$key]; | |
345 } | |
346 } | |
347 } | |
348 return false; | |
349 } | |
350 | |
351 /** | |
352 * Returns number of rows in previous resultset. If no previous resultset exists, | |
353 * this returns false. | |
354 * | |
355 * @return integer Number of rows in resultset | |
356 */ | |
357 function lastNumRows() { | |
358 if ($this->hasResult()) { | |
359 sqlite_num_rows($this->_result); | |
360 } | |
361 return false; | |
362 } | |
363 | |
364 /** | |
365 * Returns the ID generated from the previous INSERT operation. | |
366 * | |
367 * @return int | |
368 */ | |
369 function lastInsertId() { | |
370 return sqlite_last_insert_rowid($this->connection); | |
371 } | |
372 | |
373 /** | |
374 * Converts database-layer column types to basic types | |
375 * | |
376 * @param string $real Real database-layer column type (i.e. "varchar(255)") | |
377 * @return string Abstract column type (i.e. "string") | |
378 */ | |
379 function column($real) { | |
380 if (is_array($real)) { | |
381 $col = $real['name']; | |
382 if (isset($real['limit'])) { | |
383 $col .= '('.$real['limit'].')'; | |
384 } | |
385 return $col; | |
386 } | |
387 | |
388 $col = strtolower(str_replace(')', '', $real)); | |
389 $limit = null; | |
390 if (strpos($col, '(') !== false) { | |
391 list($col, $limit) = explode('(', $col); | |
392 } | |
393 | |
394 if (in_array($col, array('text', 'integer', 'float', 'boolean', 'timestamp', 'date', 'datetime', 'time'))) { | |
395 return $col; | |
396 } | |
397 if (strpos($col, 'varchar') !== false) { | |
398 return 'string'; | |
399 } | |
400 if (in_array($col, array('blob', 'clob'))) { | |
401 return 'binary'; | |
402 } | |
403 if (strpos($col, 'numeric') !== false) { | |
404 return 'float'; | |
405 } | |
406 return 'text'; | |
407 } | |
408 | |
409 /** | |
410 * Enter description here... | |
411 * | |
412 * @param unknown_type $results | |
413 */ | |
414 function resultSet(&$results) { | |
415 $this->results =& $results; | |
416 $this->map = array(); | |
417 $fieldCount = sqlite_num_fields($results); | |
418 $index = $j = 0; | |
419 | |
420 while ($j < $fieldCount) { | |
421 $columnName = str_replace('"', '', sqlite_field_name($results, $j)); | |
422 | |
423 if (strpos($columnName, '.')) { | |
424 $parts = explode('.', $columnName); | |
425 $this->map[$index++] = array($parts[0], $parts[1]); | |
426 } else { | |
427 $this->map[$index++] = array(0, $columnName); | |
428 } | |
429 $j++; | |
430 } | |
431 } | |
432 | |
433 /** | |
434 * Fetches the next row from the current result set | |
435 * | |
436 * @return unknown | |
437 */ | |
438 function fetchResult() { | |
439 if ($row = sqlite_fetch_array($this->results, SQLITE_ASSOC)) { | |
440 $resultRow = array(); | |
441 $i = 0; | |
442 | |
443 foreach ($row as $index => $field) { | |
444 if (strpos($index, '.')) { | |
445 list($table, $column) = explode('.', str_replace('"', '', $index)); | |
446 $resultRow[$table][$column] = $row[$index]; | |
447 } else { | |
448 $resultRow[0][str_replace('"', '', $index)] = $row[$index]; | |
449 } | |
450 $i++; | |
451 } | |
452 return $resultRow; | |
453 } else { | |
454 return false; | |
455 } | |
456 } | |
457 | |
458 /** | |
459 * Returns a limit statement in the correct format for the particular database. | |
460 * | |
461 * @param integer $limit Limit of results returned | |
462 * @param integer $offset Offset from which to start results | |
463 * @return string SQL limit/offset statement | |
464 */ | |
465 function limit($limit, $offset = null) { | |
466 if ($limit) { | |
467 $rt = ''; | |
468 if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) { | |
469 $rt = ' LIMIT'; | |
470 } | |
471 $rt .= ' ' . $limit; | |
472 if ($offset) { | |
473 $rt .= ' OFFSET ' . $offset; | |
474 } | |
475 return $rt; | |
476 } | |
477 return null; | |
478 } | |
479 | |
480 /** | |
481 * Generate a database-native column schema string | |
482 * | |
483 * @param array $column An array structured like the following: array('name'=>'value', 'type'=>'value'[, options]), | |
484 * where options can be 'default', 'length', or 'key'. | |
485 * @return string | |
486 */ | |
487 function buildColumn($column) { | |
488 $name = $type = null; | |
489 $column = array_merge(array('null' => true), $column); | |
490 extract($column); | |
491 | |
492 if (empty($name) || empty($type)) { | |
493 trigger_error(__('Column name or type not defined in schema', true), E_USER_WARNING); | |
494 return null; | |
495 } | |
496 | |
497 if (!isset($this->columns[$type])) { | |
498 trigger_error(sprintf(__('Column type %s does not exist', true), $type), E_USER_WARNING); | |
499 return null; | |
500 } | |
501 | |
502 $real = $this->columns[$type]; | |
503 $out = $this->name($name) . ' ' . $real['name']; | |
504 if (isset($column['key']) && $column['key'] == 'primary' && $type == 'integer') { | |
505 return $this->name($name) . ' ' . $this->columns['primary_key']['name']; | |
506 } | |
507 return parent::buildColumn($column); | |
508 } | |
509 | |
510 /** | |
511 * Sets the database encoding | |
512 * | |
513 * @param string $enc Database encoding | |
514 */ | |
515 function setEncoding($enc) { | |
516 if (!in_array($enc, array("UTF-8", "UTF-16", "UTF-16le", "UTF-16be"))) { | |
517 return false; | |
518 } | |
519 return $this->_execute("PRAGMA encoding = \"{$enc}\"") !== false; | |
520 } | |
521 | |
522 /** | |
523 * Gets the database encoding | |
524 * | |
525 * @return string The database encoding | |
526 */ | |
527 function getEncoding() { | |
528 return $this->fetchRow('PRAGMA encoding'); | |
529 } | |
530 | |
531 /** | |
532 * Removes redundant primary key indexes, as they are handled in the column def of the key. | |
533 * | |
534 * @param array $indexes | |
535 * @param string $table | |
536 * @return string | |
537 */ | |
538 function buildIndex($indexes, $table = null) { | |
539 $join = array(); | |
540 | |
541 foreach ($indexes as $name => $value) { | |
542 | |
543 if ($name == 'PRIMARY') { | |
544 continue; | |
545 } | |
546 $out = 'CREATE '; | |
547 | |
548 if (!empty($value['unique'])) { | |
549 $out .= 'UNIQUE '; | |
550 } | |
551 if (is_array($value['column'])) { | |
552 $value['column'] = implode(', ', array_map(array(&$this, 'name'), $value['column'])); | |
553 } else { | |
554 $value['column'] = $this->name($value['column']); | |
555 } | |
556 $out .= "INDEX {$name} ON {$table}({$value['column']});"; | |
557 $join[] = $out; | |
558 } | |
559 return $join; | |
560 } | |
561 | |
562 /** | |
563 * Overrides DboSource::index to handle SQLite indexe introspection | |
564 * Returns an array of the indexes in given table name. | |
565 * | |
566 * @param string $model Name of model to inspect | |
567 * @return array Fields in table. Keys are column and unique | |
568 */ | |
569 function index(&$model) { | |
570 $index = array(); | |
571 $table = $this->fullTableName($model); | |
572 if ($table) { | |
573 $indexes = $this->query('PRAGMA index_list(' . $table . ')'); | |
574 $tableInfo = $this->query('PRAGMA table_info(' . $table . ')'); | |
575 foreach ($indexes as $i => $info) { | |
576 $key = array_pop($info); | |
577 $keyInfo = $this->query('PRAGMA index_info("' . $key['name'] . '")'); | |
578 foreach ($keyInfo as $keyCol) { | |
579 if (!isset($index[$key['name']])) { | |
580 $col = array(); | |
581 if (preg_match('/autoindex/', $key['name'])) { | |
582 $key['name'] = 'PRIMARY'; | |
583 } | |
584 $index[$key['name']]['column'] = $keyCol[0]['name']; | |
585 $index[$key['name']]['unique'] = intval($key['unique'] == 1); | |
586 } else { | |
587 if (!is_array($index[$key['name']]['column'])) { | |
588 $col[] = $index[$key['name']]['column']; | |
589 } | |
590 $col[] = $keyCol[0]['name']; | |
591 $index[$key['name']]['column'] = $col; | |
592 } | |
593 } | |
594 } | |
595 } | |
596 return $index; | |
597 } | |
598 | |
599 /** | |
600 * Overrides DboSource::renderStatement to handle schema generation with SQLite-style indexes | |
601 * | |
602 * @param string $type | |
603 * @param array $data | |
604 * @return string | |
605 */ | |
606 function renderStatement($type, $data) { | |
607 switch (strtolower($type)) { | |
608 case 'schema': | |
609 extract($data); | |
610 | |
611 foreach (array('columns', 'indexes') as $var) { | |
612 if (is_array(${$var})) { | |
613 ${$var} = "\t" . implode(",\n\t", array_filter(${$var})); | |
614 } | |
615 } | |
616 return "CREATE TABLE {$table} (\n{$columns});\n{$indexes}"; | |
617 break; | |
618 default: | |
619 return parent::renderStatement($type, $data); | |
620 break; | |
621 } | |
622 } | |
623 } |