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 // +----------------------------------------------------------------------+
21 require_once 'DB/common.php';
24 * Database independent query interface definition for the SQLite
30 * @author Urs Gehrig <urs@circle.ch>
31 * @author Mika Tuupola <tuupola@appelsiini.net>
33 class DB_sqlite extends DB_common
38 var $phptype, $dbsyntax;
39 var $prepare_tokens = array();
40 var $prepare_types = array();
47 * Constructor for this class.
49 * Error codes according to sqlite_exec. Error Codes specification is
50 * in the {@link http://sqlite.org/c_interface.html online manual}.
52 * This errorhandling based on sqlite_exec is not yet implemented.
60 $this->phptype = 'sqlite';
61 $this->dbsyntax = 'sqlite';
62 $this->features = array (
65 'transactions' => false,
69 // SQLite data types, http://www.sqlite.org/datatypes.html
70 $this->keywords = array (
88 $this->errorcode_map = array(
96 * Connect to a database represented by a file.
98 * @param $dsn the data source name; the file is taken as
99 * database; "sqlite://root:@host/test.db?mode=0644"
100 * @param $persistent (optional) whether the connection should
103 * @return int DB_OK on success, a DB error on failure
105 function connect($dsninfo, $persistent = false)
107 if (!DB::assertExtension('sqlite')) {
108 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
111 $this->dsn = $dsninfo;
113 if ($dsninfo['database']) {
114 if (!file_exists($dsninfo['database'])) {
115 if (!touch($dsninfo['database'])) {
116 return $this->sqliteRaiseError(DB_ERROR_NOT_FOUND);
118 if (!isset($dsninfo['mode']) ||
119 !is_numeric($dsninfo['mode']))
123 $mode = octdec($dsninfo['mode']);
125 if (!chmod($dsninfo['database'], $mode)) {
126 return $this->sqliteRaiseError(DB_ERROR_NOT_FOUND);
128 if (!file_exists($dsninfo['database'])) {
129 return $this->sqliteRaiseError(DB_ERROR_NOT_FOUND);
132 if (!is_file($dsninfo['database'])) {
133 return $this->sqliteRaiseError(DB_ERROR_INVALID);
135 if (!is_readable($dsninfo['database'])) {
136 return $this->sqliteRaiseError(DB_ERROR_ACCESS_VIOLATION);
139 return $this->sqliteRaiseError(DB_ERROR_ACCESS_VIOLATION);
142 $connect_function = $persistent ? 'sqlite_popen' : 'sqlite_open';
143 if (!($conn = @$connect_function($dsninfo['database']))) {
144 return $this->sqliteRaiseError(DB_ERROR_NODBSELECTED);
146 $this->connection = $conn;
155 * Log out and disconnect from the database.
158 * @return bool true on success, false if not connected.
159 * @todo fix return values
161 function disconnect()
163 $ret = @sqlite_close($this->connection);
164 $this->connection = null;
172 * Send a query to SQLite and returns the results as a SQLite resource
175 * @param the SQL query
177 * @return mixed returns a valid SQLite result for successful SELECT
178 * queries, DB_OK for other successful queries. A DB error is
179 * returned on failure.
181 function simpleQuery($query)
183 $ismanip = DB::isManip($query);
184 $this->last_query = $query;
185 $query = $this->_modifyQuery($query);
186 @ini_set('track_errors', true);
187 $result = @sqlite_query($query, $this->connection);
188 ini_restore('track_errors');
189 $this->_lasterror = isset($php_errormsg) ? $php_errormsg : '';
190 $this->result = $result;
191 if (!$this->result) {
192 return $this->sqliteRaiseError(null);
195 /* sqlite_query() seems to allways return a resource */
196 /* so cant use that. Using $ismanip instead */
198 $numRows = $this->numRows($result);
200 /* if numRows() returned PEAR_Error */
201 if (is_object($numRows)) {
213 * Move the internal sqlite result pointer to the next available result.
215 * @param a valid sqlite result resource
217 * @return true if a result is available otherwise return false
219 function nextResult($result)
228 * Fetch a row and insert the data into an existing array.
230 * Formating of the array and the data therein are configurable.
231 * See DB_result::fetchInto() for more information.
233 * @param resource $result query result identifier
234 * @param array $arr (reference) array where data from the row
236 * @param int $fetchmode how the resulting array should be indexed
237 * @param int $rownum the row number to fetch
239 * @return mixed DB_OK on success, null when end of result set is
240 * reached or on failure
242 * @see DB_result::fetchInto()
245 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
247 if ($rownum !== null) {
248 if (!@sqlite_seek($this->result, $rownum)) {
252 if ($fetchmode & DB_FETCHMODE_ASSOC) {
253 $arr = @sqlite_fetch_array($result, SQLITE_ASSOC);
254 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
255 $arr = array_change_key_case($arr, CASE_LOWER);
258 $arr = @sqlite_fetch_array($result, SQLITE_NUM);
261 /* See: http://bugs.php.net/bug.php?id=22328 */
264 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
266 * Even though this DBMS already trims output, we do this because
267 * a field might have intentional whitespace at the end that
268 * gets removed by DB_PORTABILITY_RTRIM under another driver.
270 $this->_rtrimArrayValues($arr);
272 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
273 $this->_convertNullArrayValuesToEmpty($arr);
282 * Free the internal resources associated with $result.
284 * @param $result SQLite result identifier
286 * @return bool true on success, false if $result is invalid
288 function freeResult(&$result)
290 // XXX No native free?
291 if (!is_resource($result)) {
302 * Gets the number of columns in a result set.
304 * @return number of columns in a result set
306 function numCols($result)
308 $cols = @sqlite_num_fields($result);
310 return $this->sqliteRaiseError();
319 * Gets the number of rows affected by a query.
321 * @return number of rows affected by the last query
323 function numRows($result)
325 $rows = @sqlite_num_rows($result);
326 if (!is_integer($rows)) {
327 return $this->raiseError();
336 * Gets the number of rows affected by a query.
338 * @return number of rows affected by the last query
340 function affectedRows()
342 return @sqlite_changes($this->connection);
349 * Get the native error string of the last error (if any) that
350 * occured on the current connection.
352 * This is used to retrieve more meaningfull error messages DB_pgsql
353 * way since sqlite_last_error() does not provide adequate info.
355 * @return string native SQLite error message
357 function errorNative()
359 return($this->_lasterror);
366 * Determine PEAR::DB error code from the database's text error message.
368 * @param string $errormsg error message returned from the database
369 * @return integer an error number from a DB error constant
371 function errorCode($errormsg)
373 static $error_regexps;
374 if (!isset($error_regexps)) {
375 $error_regexps = array(
376 '/^no such table:/' => DB_ERROR_NOSUCHTABLE,
377 '/^table .* already exists$/' => DB_ERROR_ALREADY_EXISTS,
378 '/PRIMARY KEY must be unique/i' => DB_ERROR_CONSTRAINT,
379 '/is not unique/' => DB_ERROR_CONSTRAINT,
380 '/uniqueness constraint failed/' => DB_ERROR_CONSTRAINT,
381 '/may not be NULL/' => DB_ERROR_CONSTRAINT_NOT_NULL,
382 '/^no such column:/' => DB_ERROR_NOSUCHFIELD,
383 '/^near ".*": syntax error$/' => DB_ERROR_SYNTAX
386 foreach ($error_regexps as $regexp => $code) {
387 if (preg_match($regexp, $errormsg)) {
391 // Fall back to DB_ERROR if there was no mapping.
396 // {{{ dropSequence()
401 * @param string $seq_name name of the sequence to be deleted
403 * @return int DB_OK on success. DB_Error if problems.
406 * @see DB_common::dropSequence()
409 function dropSequence($seq_name)
411 $seqname = $this->getSequenceName($seq_name);
412 return $this->query("DROP TABLE $seqname");
416 * Creates a new sequence
418 * @param string $seq_name name of the new sequence
420 * @return int DB_OK on success. A DB_Error object is returned if
424 * @see DB_common::createSequence()
427 function createSequence($seq_name)
429 $seqname = $this->getSequenceName($seq_name);
430 $query = 'CREATE TABLE ' . $seqname .
431 ' (id INTEGER UNSIGNED PRIMARY KEY) ';
432 $result = $this->query($query);
433 if (DB::isError($result)) {
436 $query = "CREATE TRIGGER ${seqname}_cleanup AFTER INSERT ON $seqname
438 DELETE FROM $seqname WHERE id<LAST_INSERT_ROWID();
440 $result = $this->query($query);
441 if (DB::isError($result)) {
450 * Returns the next free id in a sequence
452 * @param string $seq_name name of the sequence
453 * @param boolean $ondemand when true, the seqence is automatically
454 * created if it does not exist
456 * @return int the next id number in the sequence. DB_Error if problem.
459 * @see DB_common::nextID()
462 function nextId($seq_name, $ondemand = true)
464 $seqname = $this->getSequenceName($seq_name);
468 $this->pushErrorHandling(PEAR_ERROR_RETURN);
469 $result = $this->query("INSERT INTO $seqname VALUES (NULL)");
470 $this->popErrorHandling();
471 if ($result == DB_OK) {
472 $id = @sqlite_last_insert_rowid($this->connection);
476 } elseif ($ondemand && DB::isError($result) &&
477 $result->getCode() == DB_ERROR_NOSUCHTABLE)
479 $result = $this->createSequence($seq_name);
480 if (DB::isError($result)) {
481 return $this->raiseError($result);
488 return $this->raiseError($result);
492 // {{{ getSpecialQuery()
495 * Returns the query needed to get some backend info.
497 * Refer to the online manual at http://sqlite.org/sqlite.html.
499 * @param string $type What kind of info you want to retrieve
500 * @return string The SQL query string
502 function getSpecialQuery($type, $args=array())
504 if (!is_array($args))
505 return $this->raiseError('no key specified', null, null, null,
506 'Argument has to be an array.');
507 switch (strtolower($type)) {
509 return 'SELECT * FROM sqlite_master;';
511 return "SELECT name FROM sqlite_master WHERE type='table' "
512 . 'UNION ALL SELECT name FROM sqlite_temp_master '
513 . "WHERE type='table' ORDER BY name;";
515 return 'SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL '
516 . 'SELECT * FROM sqlite_temp_master) '
517 . "WHERE type!='meta' ORDER BY tbl_name, type DESC, name;";
522 * $res = $db->query($db->getSpecialQuery('schema_x', array('table' => 'table3')));
524 return 'SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL '
525 . 'SELECT * FROM sqlite_temp_master) '
526 . "WHERE tbl_name LIKE '{$args['table']}' AND type!='meta' "
527 . 'ORDER BY type DESC, name;';
530 * SQLite does not support ALTER TABLE; this is a helper query
531 * to handle this. 'table' represents the table name, 'rows'
532 * the news rows to create, 'save' the row(s) to keep _with_
538 * 'rows' => "id INTEGER PRIMARY KEY, firstname TEXT, surname TEXT, datetime TEXT",
539 * 'save' => "NULL, titel, content, datetime"
541 * $res = $db->query( $db->getSpecialQuery('alter', $args));
543 $rows = strtr($args['rows'], $this->keywords);
547 "CREATE TEMPORARY TABLE {$args['table']}_backup ({$args['rows']})",
548 "INSERT INTO {$args['table']}_backup SELECT {$args['save']} FROM {$args['table']}",
549 "DROP TABLE {$args['table']}",
550 "CREATE TABLE {$args['table']} ({$args['rows']})",
551 "INSERT INTO {$args['table']} SELECT {$rows} FROM {$args['table']}_backup",
552 "DROP TABLE {$args['table']}_backup",
556 // This is a dirty hack, since the above query will no get executed with a single
557 // query call; so here the query method will be called directly and return a select instead.
558 foreach ($q as $query) {
559 $this->query($query);
561 return "SELECT * FROM {$args['table']};";
568 // {{{ getDbFileStats()
571 * Get the file stats for the current database.
573 * Possible arguments are dev, ino, mode, nlink, uid, gid, rdev, size,
574 * atime, mtime, ctime, blksize, blocks or a numeric key between
577 * @param string $arg Array key for stats()
578 * @return mixed array on an unspecified key, integer on a passed arg and
579 * false at a stats error.
581 function getDbFileStats($arg = '')
583 $stats = stat($this->dsn['database']);
584 if ($stats == false) {
587 if (is_array($stats)) {
588 if (is_numeric($arg)) {
589 if (((int)$arg <= 12) & ((int)$arg >= 0)) {
592 return $stats[$arg ];
594 if (array_key_exists(trim($arg), $stats)) {
595 return $stats[$arg ];
602 // {{{ escapeSimple()
605 * Escape a string according to the current DBMS's standards
607 * In SQLite, this makes things safe for inserts/updates, but may
608 * cause problems when performing text comparisons against columns
609 * containing binary data. See the
610 * {@link http://php.net/sqlite_escape_string PHP manual} for more info.
612 * @param string $str the string to be escaped
614 * @return string the escaped string
617 * @see DB_common::escapeSimple()
620 function escapeSimple($str) {
621 return @sqlite_escape_string($str);
625 // {{{ modifyLimitQuery()
627 function modifyLimitQuery($query, $from, $count)
629 $query = $query . " LIMIT $count OFFSET $from";
637 * "DELETE FROM table" gives 0 affected rows in SQLite.
639 * This little hack lets you know how many rows were deleted.
641 * @param string $query The SQL query string
642 * @return string The SQL query string
644 function _modifyQuery($query)
646 if ($this->options['portability'] & DB_PORTABILITY_DELETE_COUNT) {
647 if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
648 $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
649 'DELETE FROM \1 WHERE 1=1', $query);
656 // {{{ sqliteRaiseError()
659 * Gather information about an error, then use that info to create a
660 * DB error object and finally return that object.
662 * @param integer $errno PEAR error number (usually a DB constant) if
663 * manually raising an error
664 * @return object DB error object
667 * @see DB_common::raiseError()
669 function sqliteRaiseError($errno = null)
672 $native = $this->errorNative();
673 if ($errno === null) {
674 $errno = $this->errorCode($native);
677 $errorcode = @sqlite_last_error($this->connection);
678 $userinfo = "$errorcode ** $this->last_query";
680 return $this->raiseError($errno, null, null, $userinfo, $native);