2 /* vim: set expandtab tabstop=4 shiftwidth=4 foldmethod=marker: */
3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2003 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 // +----------------------------------------------------------------------+
20 // Based on DB 1.5.0RC2 from the pear.php.net repository.
21 // The only modification is the include path.
23 rcs_id('$Id: sqlite.php,v 1.3 2004-04-26 20:44:37 rurban Exp $');
24 rcs_id('From Pear CVS: $Id: sqlite.php,v 1.14 2003/09/11 12:55:23 tuupola Exp');
27 // Database independent query interface definition for the PECL's SQLite
30 // SQLite function set:
31 // sqlite_open, sqlite_popen, sqlite_close, sqlite_query
32 // sqlite_libversion, sqlite_libencoding, sqlite_changes
33 // sqlite_num_rows, sqlite_num_fields, sqlite_field_name, sqlite_seek
34 // sqlite_escape_string, sqlite_busy_timeout, sqlite_last_error
35 // sqlite_error_string, sqlite_unbuffered_query, sqlite_create_aggregate
36 // sqlite_create_function, sqlite_last_insert_rowid, sqlite_fetch_array
39 // # astyle --style=kr < sqlite.php > out.php
49 if (!extension_loaded('sqlite')) {
50 if (!dl(stristr(PHP_OS, "WIN") ? "php_sqlite.dll" : "sqlite.so"))
51 exit("Could not load the SQLite extension.\n");
54 require_once 'DB.php';
55 require_once 'DB/sqlite.php';
58 // TODO: mode should be passed id options array, fix example.
60 'phptype' => "sqlite",
61 'database' => getcwd() . "/test1.db",
64 $db = &new DB_sqlite();
65 $db->connect($dsn, array('persistent'=> true) );
67 // Give a new table name
68 $table = "tbl_" . md5(uniqid(rand()));
69 $table = substr($table, 0, 10);
72 $result = $db->query("CREATE TABLE $table (comment varchar(50),
73 datetime varchar(50));");
74 $result = $db->query("INSERT INTO $table VALUES ('Date and Time', '" .
75 date('Y-m-j H:i:s') . "');");
78 printf("affectedRows:\t\t%s\n", $db->affectedRows() );
79 $result = $db->query("SELECT FROM $table;" );
80 $arr = $db->fetchRow($result );
87 //require_once 'DB.php';
88 require_once 'DB/common.php';
93 * This is a special constant that tells DB the user hasn't specified
94 * any particular get mode, so the default should be used.
97 define('DB_FETCHMODE_BOTH', SQLITE_ASSOC | SQLITE_NUM );
99 /* for compatibility */
101 define('DB_GETMODE_BOTH', DB_FETCHMODE_BOTH);
106 // {{{ class DB_sqlite
108 class DB_sqlite extends DB_common {
112 var $phptype, $dbsyntax;
113 var $prepare_tokens = array();
114 var $prepare_types = array();
116 var $_lasterror = '';
122 * Constructor for this class; Error codes according to sqlite_exec
123 * Error Codes specification (see online manual, http://sqlite.org/c_interface.html.
124 * This errorhandling based on sqlite_exec is not yet implemented.
128 function DB_sqlite() {
131 $this->phptype = 'sqlite';
132 $this->dbsyntax = 'sqlite';
133 $this->features = array (
136 'transactions' => false,
140 // SQLite data types, http://www.sqlite.org/datatypes.html
141 $this->keywords = array (
159 $this->errorcode_map = array(
160 1 => DB_ERROR_SYNTAX,
161 19 => DB_ERROR_CONSTRAINT,
162 20 => DB_ERROR_MISMATCH,
163 23 => DB_ERROR_ACCESS_VIOLATION
171 * Connect to a database represented by a file.
173 * @param $dsn the data source name; the file is taken as
174 * database; "sqlite://root:@host/test.db"
175 * @param $persistent (optional) whether the connection should
178 * @return int DB_OK on success, a DB error on failure
180 function connect($dsninfo, $persistent = false) {
182 $file = $dsninfo['database'];
184 if (!DB::assertExtension('sqlite')) {
185 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
189 if (!file_exists($file)) {
191 chmod($file, (is_numeric($dsninfo['mode']) ? $dsninfo['mode'] : 0644));
192 if (!file_exists($file)) {
193 return $this->sqliteRaiseError(DB_ERROR_NOT_FOUND);
196 if (!is_file($file)) {
197 return $this->sqliteRaiseError(DB_ERROR_INVALID);
199 if (!is_readable($file)) {
200 return $this->sqliteRaiseError(DB_ERROR_ACCESS_VIOLATION);
203 return $this->sqliteRaiseError(DB_ERROR_ACCESS_VIOLATION);
206 $connect_function = $persistent ? 'sqlite_open' : 'sqlite_popen';
207 if (!($conn = @$connect_function($dsninfo['database']) )) {
208 return $this->sqliteRaiseError(DB_ERROR_NODBSELECTED);
210 $this->connection = $conn;
211 $this->dsn = $dsninfo;
220 * Log out and disconnect from the database.
223 * @return bool TRUE on success, FALSE if not connected.
224 * @todo fix return values
226 function disconnect() {
227 $ret = @sqlite_close($this->connection);
228 $this->connection = null;
236 * Send a query to SQLite and returns the results as a SQLite resource
239 * @param the SQL query
241 * @return mixed returns a valid SQLite result for successful SELECT
242 * queries, DB_OK for other successful queries. A DB error is
243 * returned on failure.
245 function simpleQuery($query) {
246 $ismanip = DB::isManip($query);
247 $this->last_query = $query;
248 $query = $this->_modifyQuery($query);
249 ini_set('track_errors', true);
250 $result = @sqlite_query($query, $this->connection);
251 ini_restore('track_errors');
252 $this->_lasterror = isset($php_errormsg) ? $php_errormsg : '';
253 $this->result = $result;
254 if (!$this->result ) {
255 return $this->sqliteRaiseError(null);
258 /* sqlite_query() seems to allways return a resource */
259 /* so cant use that. Using $ismanip instead */
261 $numRows = $this->numRows($result);
263 /* if numRows() returned PEAR_Error */
264 if (is_object($numRows )) {
276 * Move the internal sqlite result pointer to the next available result
278 * @param a valid sqlite result resource
280 * @return true if a result is available otherwise return false
282 function nextResult($result) {
290 * Fetch and return a row of data (it uses fetchInto for that)
292 * @param $result SQLite result identifier
293 * @param $fetchmode format of fetched row array
294 * @param $rownum the absolute row number to fetch
295 * @return array a row of data, or false on error
297 function fetchRow($result, $fetchmode=DB_FETCHMODE_DEFAULT, $rownum=null) {
298 if ($fetchmode == DB_FETCHMODE_DEFAULT) {
299 $fetchmode = $this->fetchmode;
301 $res = $this->fetchInto($this->result, $arr, $fetchmode, $rownum );
303 $errno = sqlite_last_error($this->connection);
307 return $this->raiseError($errno);
309 if ($res !== DB_OK) {
319 * Fetch a row and insert the data into an existing array. Availabe modes
320 * are SQLITE_ASSOC, SQLITE_NUM and SQLITE_BOTH. An object type is not
323 * @param $result SQLite result identifier
324 * @param $arr (reference) array where data from the row is stored
325 * @param $fetchmode how the array data should be indexed
326 * @param $rownum the row number to fetch
329 * @return int DB_OK on success, a DB error on failure
331 function fetchInto($result, &$arr, $fetchmode, $rownum=null) {
332 if ($rownum !== null) {
333 if (!@sqlite_seek($this->result, $rownum)) {
337 if ($fetchmode & DB_FETCHMODE_ASSOC ) {
338 $arr = sqlite_fetch_array($result, SQLITE_ASSOC);
340 $arr = sqlite_fetch_array($result, SQLITE_NUM);
343 /* See: http://bugs.php.net/bug.php?id=22328 */
353 * Free the internal resources associated with $result.
355 * @param $result SQLite result identifier or DB statement identifier
357 * @return bool TRUE on success, FALSE if $result is invalid
359 function freeResult($result) {
360 if(is_resource($result)) {
364 // $result is a prepared query handle
365 $result = (int)$result;
366 if (!isset($this->prepare_tokens[$result])) {
369 $this->prepare_types = array();
370 $this->prepare_tokens = array();
378 * Gets the number of columns in a result set.
380 * @return number of columns in a result set
382 function numCols($result) {
383 $cols = @sqlite_num_fields($result);
385 return $this->sqliteRaiseError();
394 * Gets the number of rows affected by a query.
396 * @return number of rows affected by the last query
398 function numRows($result) {
399 $rows = @sqlite_num_rows($result);
400 if (!is_integer($rows)) {
401 return $this->raiseError();
410 * Gets the number of rows affected by a query.
412 * @return number of rows affected by the last query
414 function affectedRows() {
415 return sqlite_changes($this->connection );
423 * Get the native error string of the last error (if any) that
424 * occured on the current connection. This is used to retrieve
425 * more meaningfull error messages DB_pgsql way since
426 * sqlite_last_error() does not provide adequate info.
428 * @return string native SQLite error message
430 function errorNative()
432 return($this->_lasterror);
435 function errorCode($errormsg)
437 static $error_regexps;
438 if (empty($error_regexps)) {
439 $error_regexps = array(
440 '/^no such table:/' => DB_ERROR_NOSUCHTABLE,
441 '/^table .* already exists$/' => DB_ERROR_ALREADY_EXISTS,
442 '/^no such column:/' => DB_ERROR_NOSUCHFIELD,
443 '/^near ".*": syntax error$/' => DB_ERROR_SYNTAX
446 foreach ($error_regexps as $regexp => $code) {
447 if (preg_match($regexp, $errormsg)) {
451 // Fall back to DB_ERROR if there was no mapping.
457 function dropSequence($seq_name)
459 $seqname = $this->getSequenceName($seq_name);
460 return $this->query("DROP TABLE $seqname");
463 function createSequence($seq_name)
465 $seqname = $this->getSequenceName($seq_name);
466 $query = 'CREATE TABLE ' . $seqname .
467 ' (id INTEGER UNSIGNED PRIMARY KEY) ';
468 $result = $this->query($query);
469 if (DB::isError($result)) {
472 $query = "CREATE TRIGGER ${seqname}_cleanup AFTER INSERT ON $seqname
474 DELETE FROM $seqname WHERE id<LAST_INSERT_ROWID();
476 $result = $this->query($query);
477 if (DB::isError($result)) {
484 * Get the next value in a sequence. We emulate sequences
485 * for SQLite. Will create the sequence if it does not exist.
489 * @param string $seq_name the name of the sequence
491 * @param bool $ondemand whether to create the sequence table on demand
494 * @return mixed a sequence integer, or a DB error
497 function nextId($seq_name, $ondemand = true)
499 $seqname = $this->getSequenceName($seq_name);
503 $this->pushErrorHandling(PEAR_ERROR_RETURN);
504 $result = $this->query("INSERT INTO $seqname VALUES (NULL)");
505 $this->popErrorHandling();
506 if ($result == DB_OK) {
507 $id = sqlite_last_insert_rowid($this->connection);
511 } elseif ($ondemand && DB::isError($result) &&
512 $result->getCode() == DB_ERROR_NOSUCHTABLE) {
513 $result = $this->createSequence($seq_name);
514 if (DB::isError($result)) {
515 return $this->raiseError($result);
522 return $this->raiseError($result);
527 // {{{ getSpecialQuery()
530 * Returns the query needed to get some backend info. Refer to
531 * the online manual at http://sqlite.org/sqlite.html.
533 * @param string $type What kind of info you want to retrieve
534 * @return string The SQL query string
536 function getSpecialQuery($type, $args=array()) {
539 return $this->raiseError('no key specified', null, null, null,
540 'Argument has to be an array.');
541 switch (strtolower($type)) {
543 $query .= "SELECT * FROM sqlite_master;";
546 $query .= "SELECT name FROM sqlite_master WHERE type='table' ";
547 $query .= "UNION ALL SELECT name FROM sqlite_temp_master ";
548 $query .= "WHERE type='table' ORDER BY name;";
551 $query .= "SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL ";
552 $query .= "SELECT * FROM sqlite_temp_master) ";
553 $query .= "WHERE type!='meta' ORDER BY tbl_name, type DESC, name;";
559 * $res = $db->query( $db->getSpecialQuery("schema_x", array("table" => "table3" )) );
561 $query .= "SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL ";
562 $query .= "SELECT * FROM sqlite_temp_master) ";
563 $query .= sprintf("WHERE tbl_name LIKE '%s' AND type!='meta' ", $args['table'] );
564 $query .= "ORDER BY type DESC, name;";
568 * SQLite does not support ALTER TABLE; this is a helper query to handle this. 'table'
569 * represents the table name, 'rows' the news rows to create, 'save' the row(s) to keep
575 * 'rows' => "id INTEGER PRIMARY KEY, firstname TEXT, surname TEXT, datetime TEXT",
576 * 'save' => "NULL, titel, content, datetime"
579 * $res = $db->query( $db->getSpecialQuery("alter", $args ) );
581 $rows = strtr($args['rows'], $this->keywords );
583 $query .= "BEGIN TRANSACTION;";
584 $query .= "CREATE TEMPORARY TABLE {$args['table']}_backup ({$args['rows']});";
585 $query .= "INSERT INTO {$args['table']}_backup SELECT {$args['save']} FROM {$args['table']};";
586 $query .= "DROP TABLE {$args['table']};";
587 $query .= "CREATE TABLE {$args['table']} ({$args['rows']});";
588 $query .= "INSERT INTO {$args['table']} SELECT {$rows} FROM {$args['table']}_backup;";
589 $query .= "DROP TABLE {$args['table']}_backup;";
592 // This is a dirty hack, since the above query will no get executed with a single
593 // query call; so here the query method will be called directly and return a select instead.
594 $q = explode(";", $query );
595 for($i=0; $i<8; $i++)
596 $result = $this->query( $q[$i] );
597 $query = "SELECT * FROM {$args['table']};";
606 // {{{ getDbFileStats()
609 * Get the file stats for the current database. Possible arguments are
610 * dev, ino, mode, nlink, uid, gid, rdev, size, atime, mtime, ctime, blksize, blocks
611 * or a numeric key between 0 and 12.
613 * @param string $arg Array key for stats()
614 * @return mixed array on an unspecified key, integer on a passed arg and
615 * FALSE at a stats error.
617 function getDbFileStats($arg="" ) {
618 $stats = stat($this->dsn['database'] );
619 if ($stats == false )
621 if (is_array($stats)) {
622 if(is_numeric($arg) ) {
623 if(((int)$arg <= 12) & ((int)$arg >= 0))
625 return $stats[$arg ];
627 if (array_key_exists(trim($arg), $stats)) {
628 return $stats[$arg ];
637 function modifyLimitQuery($query, $from, $count)
639 $query = $query . " LIMIT $count OFFSET $from";
644 * "DELETE FROM table" gives 0 affected rows in SQLite. This little hack
645 * lets you know how many rows were deleted.
647 * @param string $query The SQL query string
648 * @return string The SQL query string
650 function _modifyQuery($query ) {
651 if ($this->options['optimize'] == 'portability') {
652 if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
653 $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
654 'DELETE FROM \1 WHERE 1=1', $query);
661 // {{{ sqliteRaiseError()
664 * Handling PEAR Errors
666 * @param int $errno a PEAR error code
667 * @return object a PEAR error object
669 function sqliteRaiseError($errno = null) {
671 if ($errno === null) {
672 $native = $this->errorNative();
673 $errno = $this->errorCode($native);
675 return $this->raiseError($errno, null, null, null,
676 @sqlite_last_error($this->connection) . " ** " .
677 @sqlite_error_string($this->connection));