2 /* vim: set expandtab tabstop=4 shiftwidth=4 foldmethod=marker: */
3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2004 The PHP Group |
7 // +----------------------------------------------------------------------+
8 // | This source file is subject to version 2.02 of the PHP license, |
9 // | that is bundled with this package in the file LICENSE, and is |
10 // | available at through the world-wide-web at |
11 // | http://www.php.net/license/2_02.txt. |
12 // | If you did not receive a copy of the PHP license and are unable to |
13 // | obtain it through the world-wide-web, please send a note to |
14 // | license@php.net so we can mail you a copy immediately. |
15 // +----------------------------------------------------------------------+
16 // | Authors: Urs Gehrig <urs@circle.ch> |
17 // | Mika Tuupola <tuupola@appelsiini.net> |
18 // | Maintainer: Daniel Convissor <danielc@php.net> |
19 // +----------------------------------------------------------------------+
23 require_once 'DB/common.php';
26 * Database independent query interface definition for the SQLite
32 * @author Urs Gehrig <urs@circle.ch>
33 * @author Mika Tuupola <tuupola@appelsiini.net>
35 class DB_sqlite extends DB_common
40 var $phptype, $dbsyntax;
41 var $prepare_tokens = array();
42 var $prepare_types = array();
49 * Constructor for this class.
51 * Error codes according to sqlite_exec. Error Codes specification is
52 * in the {@link http://sqlite.org/c_interface.html online manual}.
54 * This errorhandling based on sqlite_exec is not yet implemented.
62 $this->phptype = 'sqlite';
63 $this->dbsyntax = 'sqlite';
64 $this->features = array (
67 'transactions' => false,
71 // SQLite data types, http://www.sqlite.org/datatypes.html
72 $this->keywords = array (
90 $this->errorcode_map = array(
98 * Connect to a database represented by a file.
100 * @param $dsn the data source name; the file is taken as
101 * database; "sqlite://root:@host/test.db?mode=0644"
102 * @param $persistent (optional) whether the connection should
105 * @return int DB_OK on success, a DB error on failure
107 function connect($dsninfo, $persistent = false)
109 if (!DB::assertExtension('sqlite')) {
110 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
113 $this->dsn = $dsninfo;
115 if ($dsninfo['database']) {
116 if (!file_exists($dsninfo['database'])) {
117 if (!touch($dsninfo['database'])) {
118 return $this->sqliteRaiseError(DB_ERROR_NOT_FOUND);
120 if (!isset($dsninfo['mode']) ||
121 !is_numeric($dsninfo['mode']))
125 $mode = octdec($dsninfo['mode']);
127 if (!chmod($dsninfo['database'], $mode)) {
128 return $this->sqliteRaiseError(DB_ERROR_NOT_FOUND);
130 if (!file_exists($dsninfo['database'])) {
131 return $this->sqliteRaiseError(DB_ERROR_NOT_FOUND);
134 if (!is_file($dsninfo['database'])) {
135 return $this->sqliteRaiseError(DB_ERROR_INVALID);
137 if (!is_readable($dsninfo['database'])) {
138 return $this->sqliteRaiseError(DB_ERROR_ACCESS_VIOLATION);
141 return $this->sqliteRaiseError(DB_ERROR_ACCESS_VIOLATION);
144 $connect_function = $persistent ? 'sqlite_popen' : 'sqlite_open';
145 if (!($conn = @$connect_function($dsninfo['database']))) {
146 return $this->sqliteRaiseError(DB_ERROR_NODBSELECTED);
148 $this->connection = $conn;
157 * Log out and disconnect from the database.
160 * @return bool true on success, false if not connected.
161 * @todo fix return values
163 function disconnect()
165 $ret = @sqlite_close($this->connection);
166 $this->connection = null;
174 * Send a query to SQLite and returns the results as a SQLite resource
177 * @param the SQL query
179 * @return mixed returns a valid SQLite result for successful SELECT
180 * queries, DB_OK for other successful queries. A DB error is
181 * returned on failure.
183 function simpleQuery($query)
185 $ismanip = DB::isManip($query);
186 $this->last_query = $query;
187 $query = $this->_modifyQuery($query);
188 @ini_set('track_errors', true);
189 $result = @sqlite_query($query, $this->connection);
190 ini_restore('track_errors');
191 $this->_lasterror = isset($php_errormsg) ? $php_errormsg : '';
192 $this->result = $result;
193 if (!$this->result) {
194 return $this->sqliteRaiseError(null);
197 /* sqlite_query() seems to allways return a resource */
198 /* so cant use that. Using $ismanip instead */
200 $numRows = $this->numRows($result);
202 /* if numRows() returned PEAR_Error */
203 if (is_object($numRows)) {
215 * Move the internal sqlite result pointer to the next available result.
217 * @param a valid sqlite result resource
219 * @return true if a result is available otherwise return false
221 function nextResult($result)
230 * Fetch a row and insert the data into an existing array.
232 * Formating of the array and the data therein are configurable.
233 * See DB_result::fetchInto() for more information.
235 * @param resource $result query result identifier
236 * @param array $arr (reference) array where data from the row
238 * @param int $fetchmode how the resulting array should be indexed
239 * @param int $rownum the row number to fetch
241 * @return mixed DB_OK on success, null when end of result set is
242 * reached or on failure
244 * @see DB_result::fetchInto()
247 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
249 if ($rownum !== null) {
250 if (!@sqlite_seek($this->result, $rownum)) {
254 if ($fetchmode & DB_FETCHMODE_ASSOC) {
255 $arr = @sqlite_fetch_array($result, SQLITE_ASSOC);
256 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
257 $arr = array_change_key_case($arr, CASE_LOWER);
260 $arr = @sqlite_fetch_array($result, SQLITE_NUM);
263 /* See: http://bugs.php.net/bug.php?id=22328 */
266 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
268 * Even though this DBMS already trims output, we do this because
269 * a field might have intentional whitespace at the end that
270 * gets removed by DB_PORTABILITY_RTRIM under another driver.
272 $this->_rtrimArrayValues($arr);
274 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
275 $this->_convertNullArrayValuesToEmpty($arr);
284 * Free the internal resources associated with $result.
286 * @param $result SQLite result identifier
288 * @return bool true on success, false if $result is invalid
290 function freeResult(&$result)
292 // XXX No native free?
293 if (!is_resource($result)) {
304 * Gets the number of columns in a result set.
306 * @return number of columns in a result set
308 function numCols($result)
310 $cols = @sqlite_num_fields($result);
312 return $this->sqliteRaiseError();
321 * Gets the number of rows affected by a query.
323 * @return number of rows affected by the last query
325 function numRows($result)
327 $rows = @sqlite_num_rows($result);
328 if (!is_integer($rows)) {
329 return $this->raiseError();
338 * Gets the number of rows affected by a query.
340 * @return number of rows affected by the last query
342 function affectedRows()
344 return @sqlite_changes($this->connection);
351 * Get the native error string of the last error (if any) that
352 * occured on the current connection.
354 * This is used to retrieve more meaningfull error messages DB_pgsql
355 * way since sqlite_last_error() does not provide adequate info.
357 * @return string native SQLite error message
359 function errorNative()
361 return($this->_lasterror);
368 * Determine PEAR::DB error code from the database's text error message.
370 * @param string $errormsg error message returned from the database
371 * @return integer an error number from a DB error constant
373 function errorCode($errormsg)
375 static $error_regexps;
376 if (!isset($error_regexps)) {
377 $error_regexps = array(
378 '/^no such table:/' => DB_ERROR_NOSUCHTABLE,
379 '/^table .* already exists$/' => DB_ERROR_ALREADY_EXISTS,
380 '/PRIMARY KEY must be unique/i' => DB_ERROR_CONSTRAINT,
381 '/is not unique/' => DB_ERROR_CONSTRAINT,
382 '/uniqueness constraint failed/' => DB_ERROR_CONSTRAINT,
383 '/may not be NULL/' => DB_ERROR_CONSTRAINT_NOT_NULL,
384 '/^no such column:/' => DB_ERROR_NOSUCHFIELD,
385 '/^near ".*": syntax error$/' => DB_ERROR_SYNTAX
388 foreach ($error_regexps as $regexp => $code) {
389 if (preg_match($regexp, $errormsg)) {
393 // Fall back to DB_ERROR if there was no mapping.
398 // {{{ dropSequence()
403 * @param string $seq_name name of the sequence to be deleted
405 * @return int DB_OK on success. DB_Error if problems.
408 * @see DB_common::dropSequence()
411 function dropSequence($seq_name)
413 $seqname = $this->getSequenceName($seq_name);
414 return $this->query("DROP TABLE $seqname");
418 * Creates a new sequence
420 * @param string $seq_name name of the new sequence
422 * @return int DB_OK on success. A DB_Error object is returned if
426 * @see DB_common::createSequence()
429 function createSequence($seq_name)
431 $seqname = $this->getSequenceName($seq_name);
432 $query = 'CREATE TABLE ' . $seqname .
433 ' (id INTEGER UNSIGNED PRIMARY KEY) ';
434 $result = $this->query($query);
435 if (DB::isError($result)) {
438 $query = "CREATE TRIGGER ${seqname}_cleanup AFTER INSERT ON $seqname
440 DELETE FROM $seqname WHERE id<LAST_INSERT_ROWID();
442 $result = $this->query($query);
443 if (DB::isError($result)) {
452 * Returns the next free id in a sequence
454 * @param string $seq_name name of the sequence
455 * @param boolean $ondemand when true, the seqence is automatically
456 * created if it does not exist
458 * @return int the next id number in the sequence. DB_Error if problem.
461 * @see DB_common::nextID()
464 function nextId($seq_name, $ondemand = true)
466 $seqname = $this->getSequenceName($seq_name);
470 $this->pushErrorHandling(PEAR_ERROR_RETURN);
471 $result = $this->query("INSERT INTO $seqname VALUES (NULL)");
472 $this->popErrorHandling();
473 if ($result == DB_OK) {
474 $id = @sqlite_last_insert_rowid($this->connection);
478 } elseif ($ondemand && DB::isError($result) &&
479 $result->getCode() == DB_ERROR_NOSUCHTABLE)
481 $result = $this->createSequence($seq_name);
482 if (DB::isError($result)) {
483 return $this->raiseError($result);
490 return $this->raiseError($result);
494 // {{{ getSpecialQuery()
497 * Returns the query needed to get some backend info.
499 * Refer to the online manual at http://sqlite.org/sqlite.html.
501 * @param string $type What kind of info you want to retrieve
502 * @return string The SQL query string
504 function getSpecialQuery($type, $args=array())
506 if (!is_array($args))
507 return $this->raiseError('no key specified', null, null, null,
508 'Argument has to be an array.');
509 switch (strtolower($type)) {
511 return 'SELECT * FROM sqlite_master;';
513 return "SELECT name FROM sqlite_master WHERE type='table' "
514 . 'UNION ALL SELECT name FROM sqlite_temp_master '
515 . "WHERE type='table' ORDER BY name;";
517 return 'SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL '
518 . 'SELECT * FROM sqlite_temp_master) '
519 . "WHERE type!='meta' ORDER BY tbl_name, type DESC, name;";
524 * $res = $db->query($db->getSpecialQuery('schema_x', array('table' => 'table3')));
526 return 'SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL '
527 . 'SELECT * FROM sqlite_temp_master) '
528 . "WHERE tbl_name LIKE '{$args['table']}' AND type!='meta' "
529 . 'ORDER BY type DESC, name;';
532 * SQLite does not support ALTER TABLE; this is a helper query
533 * to handle this. 'table' represents the table name, 'rows'
534 * the news rows to create, 'save' the row(s) to keep _with_
540 * 'rows' => "id INTEGER PRIMARY KEY, firstname TEXT, surname TEXT, datetime TEXT",
541 * 'save' => "NULL, titel, content, datetime"
543 * $res = $db->query( $db->getSpecialQuery('alter', $args));
545 $rows = strtr($args['rows'], $this->keywords);
549 "CREATE TEMPORARY TABLE {$args['table']}_backup ({$args['rows']})",
550 "INSERT INTO {$args['table']}_backup SELECT {$args['save']} FROM {$args['table']}",
551 "DROP TABLE {$args['table']}",
552 "CREATE TABLE {$args['table']} ({$args['rows']})",
553 "INSERT INTO {$args['table']} SELECT {$rows} FROM {$args['table']}_backup",
554 "DROP TABLE {$args['table']}_backup",
558 // This is a dirty hack, since the above query will no get executed with a single
559 // query call; so here the query method will be called directly and return a select instead.
560 foreach ($q as $query) {
561 $this->query($query);
563 return "SELECT * FROM {$args['table']};";
570 // {{{ getDbFileStats()
573 * Get the file stats for the current database.
575 * Possible arguments are dev, ino, mode, nlink, uid, gid, rdev, size,
576 * atime, mtime, ctime, blksize, blocks or a numeric key between
579 * @param string $arg Array key for stats()
580 * @return mixed array on an unspecified key, integer on a passed arg and
581 * false at a stats error.
583 function getDbFileStats($arg = '')
585 $stats = stat($this->dsn['database']);
586 if ($stats == false) {
589 if (is_array($stats)) {
590 if (is_numeric($arg)) {
591 if (((int)$arg <= 12) & ((int)$arg >= 0)) {
594 return $stats[$arg ];
596 if (array_key_exists(trim($arg), $stats)) {
597 return $stats[$arg ];
604 // {{{ escapeSimple()
607 * Escape a string according to the current DBMS's standards
609 * In SQLite, this makes things safe for inserts/updates, but may
610 * cause problems when performing text comparisons against columns
611 * containing binary data. See the
612 * {@link http://php.net/sqlite_escape_string PHP manual} for more info.
614 * @param string $str the string to be escaped
616 * @return string the escaped string
619 * @see DB_common::escapeSimple()
622 function escapeSimple($str) {
623 return @sqlite_escape_string($str);
627 // {{{ modifyLimitQuery()
629 function modifyLimitQuery($query, $from, $count)
631 $query = $query . " LIMIT $count OFFSET $from";
639 * "DELETE FROM table" gives 0 affected rows in SQLite.
641 * This little hack lets you know how many rows were deleted.
643 * @param string $query The SQL query string
644 * @return string The SQL query string
646 function _modifyQuery($query)
648 if ($this->options['portability'] & DB_PORTABILITY_DELETE_COUNT) {
649 if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
650 $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
651 'DELETE FROM \1 WHERE 1=1', $query);
658 // {{{ sqliteRaiseError()
661 * Gather information about an error, then use that info to create a
662 * DB error object and finally return that object.
664 * @param integer $errno PEAR error number (usually a DB constant) if
665 * manually raising an error
666 * @return object DB error object
669 * @see DB_common::raiseError()
671 function sqliteRaiseError($errno = null)
674 $native = $this->errorNative();
675 if ($errno === null) {
676 $errno = $this->errorCode($native);
679 $errorcode = @sqlite_last_error($this->connection);
680 $userinfo = "$errorcode ** $this->last_query";
682 return $this->raiseError($errno, null, null, $userinfo, $native);