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
// $Id: sqlite.php 6184 2008-08-22 10:33:41Z vargenau $
23
require_once 'DB/common.php';
26
* Database independent query interface definition for the SQLite
30
* @version $Id: sqlite.php 6184 2008-08-22 10:33:41Z vargenau $
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);
added
removed
plugins/wiki/www/lib/pear/DB/sqlite.php
