|
|
 |
CXX. SQLite Functions
This is an extension for the SQLite Embeddable SQL Database Engine.
SQLite is a C library that implements an embeddable SQL database engine.
Programs that link with the SQLite library can have SQL database access
without running a separate RDBMS process.
SQLite is not a client library used to connect to a big database server.
SQLite is the server. The SQLite library reads and writes directly to and from
the database files on disk.
Read the INSTALL file, which comes with the package. Or just use the PEAR
installer with "pear install sqlite".
SQLite itself is already included, You do not need to install
any additional software.
Windows users may download the DLL version of the SQLite extension here:
(php_sqlite.dll).
In PHP 5, the SQLite extension and the engine itself are bundled and
compiled by default.
Windows installation for unprivileged accounts:
On Windows operating systems, unprivileged accounts don't have the
TMP environment variable set by default. This will
make sqlite create temporary files in the windows directory, which is
not desirable. So, you should set the TMP environment
variable for the web server or the user account the web server is
running under. If Apache is your web server, you can accomplish this via
a SetEnv directive in your httpd.conf file. For
example:
If you are unable to establish this setting at the server
level, you can implement the setting in your script:
The setting must refer to a directory that the web server
has permission to create files in and subsequently write
to and delete the files it created.
Otherwise, you may receive the following error message:
malformed database schema -
unable to open a temporary database file for storing temporary tables
In order to have these functions available, you must compile PHP with
SQLite support, or load the SQLite extension dynamically from your
php.ini.
There are two resources used in the SQLite Interface. The first one is the
database connection, the second one the result set.
The constants below are defined by this extension, and
will only be available when the extension has either
been compiled into PHP or dynamically loaded at runtime.
The functions sqlite_fetch_array() and
sqlite_current() use a constant for
the different types of result arrays. The following constants are
defined:
SQLite result type constants - SQLITE_ASSOC
(int)
Columns are returned into the array having the field name as the array
index.
- SQLITE_BOTH
(int)
Columns are returned into the array having both a numerical index
and the field name as the array index.
- SQLITE_NUM
(int)
Columns are returned into the array having a numerical index to the
fields. This index starts with 0, the first field in the result.
A number of functions may return status codes. The following constants are
defined:
SQLite status code constants - SQLITE_OK
(int)
Successful result.
- SQLITE_ERROR
(int)
SQL error or missing database.
- SQLITE_INTERNAL
(int)
An internal logic error in SQLite.
- SQLITE_PERM
(int)
Access permission denied.
- SQLITE_ABORT
(int)
Callback routine requested an abort.
- SQLITE_BUSY
(int)
The database file is locked.
- SQLITE_LOCKED
(int)
A table in the database is locked.
- SQLITE_NOMEM
(int)
Memory allocation failed.
- SQLITE_READONLY
(int)
Attempt to write a readonly database.
- SQLITE_INTERRUPT
(int)
Operation terminated internally.
- SQLITE_IOERR
(int)
Disk I/O error occurred.
- SQLITE_CORRUPT
(int)
The database disk image is malformed.
- SQLITE_NOTFOUND
(int)
(Internal) Table or record not found.
- SQLITE_FULL
(int)
Insertion failed because database is full.
- SQLITE_CANTOPEN
(int)
Unable to open the database file.
- SQLITE_PROTOCOL
(int)
Database lock protocol error.
- SQLITE_EMPTY
(int)
(Internal) Database table is empty.
- SQLITE_SCHEMA
(int)
The database schema changed.
- SQLITE_TOOBIG
(int)
Too much data for one row of a table.
- SQLITE_CONSTRAINT
(int)
Abort due to constraint violation.
- SQLITE_MISMATCH
(int)
Data type mismatch.
- SQLITE_MISUSE
(int)
Library used incorrectly.
- SQLITE_NOLFS
(int)
Uses of OS features not supported on host.
- SQLITE_AUTH
(int)
Authorized failed.
- SQLITE_ROW
(int)
Internal process has another row ready.
- SQLITE_DONE
(int)
Internal process has finished executing.
Represents an opened SQLite database.
query - Execute a query queryExec - Execute a result-less query arrayQuery - Execute a query and return the result as an array singleQuery - Execute a query and return either an array for one single column or the value of the first row unbufferedQuery - Execute an unbuffered query lastInsertRowid - Returns the rowid of the most recently inserted row changes - Returns the number of rows changed by the most recent statement createAggregate - Register an aggregating UDF for use in SQL statements createFunction - Register a UDF for use in SQL statements busyTimeout - Sets or disables busy timeout duration lastErorr - Returns the last error code of the most recently encountered error fetchColumnTypes - Return an array of column types from a particular table
Represents a buffered SQLite result set.
fetch - Fetches the next row from the result set as an array fetchObject - Fetches the next row from the result set as an object fetchSingle - Fetches the first column from the result set as a string fetchAll - Fetches all rows from the result set as an array of arrays column - Fetches a column from the current row of the result set numFields - Returns the number of fields in the result set fieldName - Returns the name of a particular field in the result set current - Fetches the current row from the result set as an array key - Return the current row index next - Seek to the next row number valid - Returns whether more rows are available rewind - Seek to the first row number of the result set prev - Seek to the previous row number of the result set hasPrev - Returns whether or not a previous row is available numRows - Returns the number of rows in the result set seek - Seek to a particular row number
Represents an unbuffered SQLite result set. Unbuffered results sets are sequential, forward-seeking only.
fetch - Fetches the next row from the result set as an array fetchObject - Fetches the next row from the result set as an object fetchSingle - Fetches the first column from the result set as a string fetchAll - Fetches all rows from the result set as an array of arrays column - Fetches a column from the current row of the result set numFields - Returns the number of fields in the result set fieldName - Returns the name of a particular field in the result set current - Fetches the current row from the result set as an array next - Seek to the next row number valid - Returns whether more rows are available
The behaviour of these functions is affected by settings in php.ini.
Table 1. SQLite Configure Options | Name | Default | Changeable | Changelog |
|---|
| sqlite.assoc_case | "0" | PHP_INI_ALL | Available since PHP 5.0.0. |
For further details and definitions of the
PHP_INI_* constants, see the Appendix H.
Here's a short explanation of
the configuration directives.
- sqlite.assoc_case
int
Whether to use mixed case (0), upper case
(1) or lower case (2) hash
indexes.
This option is primarily useful when you need compatibility with other
database systems, where the names of the columns are always returned as
uppercase or lowercase, regardless of the case of the actual field names
in the database schema.
The SQLite library returns the column names in their natural case (that
matches the case you used in your schema). When
sqlite.assoc_case is set to 0
the natural case will be preserved. When it is set to
1 or 2, PHP will apply case
folding on the hash keys to upper- or lower-case the keys, respectively.
Use of this option incurs a slight performance penalty, but is MUCH
faster than performing the case folding yourself using PHP script.
User Contributed Notes
SQLite Functions
bart at mediawave dot nl
18-Apr-2005 09:30
SELECT tablename.columnname FROM table;
will cause SQLite to return an array having tablename.field_name as the array index. (e.g. $result['tablename.field_name'])
To let SQLite return an array having only field_name as the array index (e.g. $result['field_name']) you can issue a 'PRAGMA short_column_names = 1' query:
sqlite_query($connection_id, 'PRAGMA short_column_names = 1');
This behaviour is more consistent with the other database extensions.
For a full list of all pragmas visit: http://sqlite.org/pragma.html
dotwho at NOSPAM dot mac dot com
09-Feb-2005 08:03
This may have been obvious to others, but I had a tough time finding the info.
The default location for the actual database file is the same location of the php doc that created the database. You can alter this behavior by specifying the full path in the creation call:
<php
$db = sqlite_open("/absolute/path/my_sqlite.db");
?>
Note that if you used the default location, the db file may be served up by the webserver if it is in a the http document path. This is obviously a security risk that should be avoided.
//Max
hunreal+php at gmail dot com
16-Jan-2005 10:42
Check the db/table if exist
$db_name='db';
$db=new SQLiteDatabase($db_name, 0666, $error);
if ($error) exit($error);
$q=$db->query("PRAGMA table_info(test)");
if ($q->numRows()!=2) {
if (!@$db->queryexec("
CREATE TABLE test (
id INTEGER NOT NULL PRIMARY KEY,
text VARCHAR ( 255 ) NOT NULL
)")
) exit ("Create SQLite Database Error\n");
}
nicolas dot toniazzi at free dot fr
16-Nov-2004 09:24
The same in 3 lines.
<?php
function sqlite_table_exists($db,$mytable) {
$result = sqlite_query($db,"SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='$mytable'");
$count = intval(sqlite_fetch_single($result));
return $count > 0;
}
?>
alexs at alphacomolex dot info
28-Oct-2004 05:41
the OO version.
<?php
function sqlite_table_exists($db,$mytable) {
$result = $db->query("SELECT name FROM sqlite_master WHERE type='table'");
$tables = $result->fetchAll();
if (count($tables) == 0) {
return FALSE ;
}
foreach ($tables as $table) {
if ($table['name'] == $mytable) {
return(TRUE);
}
}
return(FALSE);
}
?>
artooro at gmail dot com
21-Oct-2004 08:26
If you need to check if a table exists, you can use a function like this:
<?php
function sqlite_table_exists($mytable) {
$db = sqlite_open('mydb.sqlite', 0666, $sqliteerror);
$query = sqlite_query($db, "SELECT name FROM sqlite_master WHERE type='table'");
$tables = sqlite_fetch_array($query);
if ($tables != '') {
foreach ($tables as $table) {
if ($table == $mytable) {
return("TRUE");
}
else {
return("FALSE");
}
}
}
else {
return("FALSE");
}
}
?>
A function like this could be used to create the table if it's not already created, etc.
j-pieper at NOSPAM dot web dot de
19-Oct-2004 05:18
If you want to create a table in your database which should have an integer primary key, you cannot declare the row like this:
id int(16) primary key
or
id integer(16) primary key
When you declare it like this it could be that the id isn´t auto increment. You have to declare it like this:
id integer primary key
filip at filipdewaard dot com
10-Sep-2004 01:39
jon at jenseng dot com
20-Jul-2004 04:50
Since SQLite doesn't support ALTER TABLE statements or renaming tables, modifying an existing table is a bit cumbersome. You have to:
1. create a temporary table
2. copy the original table into the temporary table
3. delete the original
4. recreate the original with new column definitions
5. copy the contents back
6. delete the temporary table
As this is rather unwieldy, I've created a wrapper class that allows for ALTER TABLE queries and does the dirty work for you. It has integrated error handling to ensure that queries are completely valid and it allows for complex statements such as:
ALTER TABLE foo ADD bar VARCHAR(27), DROP bar2, CHANGE bar3 foobar INTEGER, ADD bar4 DATE
Documentation:
http://code.jenseng.com/db/
Source:
http://code.jenseng.com/db/sql.txt
david at acz dot org
14-Jul-2004 08:29
You can use the PECL SQLite extension as a static (built into the executable) PHP module with PHP 4. Download the extension tarball and extract it. Move it to ext/sqlite in the PHP source directory. Delete configure and run buildconf.
Example below. Change version numbers as appropriate:
$ tar -xzvf php-4.3.8.tar.gz
$ tar -xzvf SQLite-1.0.2.tgz
$ mv SQLite-1.0.2 php-4.3.8/ext/sqlite
$ cd php-4.3.8
$ rm configure
$ ./buildconf --force
If everything worked, then you should now be able to build PHP with SQLite support:
$ ./configure --help | grep sqlite
--with-sqlite Include sqlite support
I think this method will work for other PECL extensions.
csaba at alum dot mit dot edu
14-Apr-2004 10:16
If you want to get the list of all the columns in a table (and associated information), PRAGMA is helpful (see http://sqlite.org/lang.html#pragma for details):
if (!($db=@sqlite_open("delme.db",0666,$sqliteerror))) die("Can't open database");
@sqlite_query ($db, 'DROP TABLE foo;');
sqlite_query($db, "CREATE TABLE foo (bar INTEGER PRIMARY KEY, baz VARCHAR(5));");
$aTableStruct = sqlite_array_query($db, "PRAGMA table_info('foo');", SQLITE_ASSOC);
for ($i=0,$aNames=array();$i<sizeof($aTableStruct);++$i)
$aNames[]=$aTableStruct[$i]['name'];
var_dump ($aNames); // => ['bar', 'baz']
Note also that if you want to use more than one database in the same connection using "ATTACH DATABASE ..." then you should supply the complete database filename.
Csaba Gabor
cricket at djcricket dot com
31-Mar-2004 11:30
To elaborate on vpupkin at comcast dot net's post about the INSERT query problem, you will be unable to execute any manipulation queries (INSERT/UPDATE/DELETE/ect) on the SQLite database file unless the directory the SQLite database file resides in is writable by the webserver.
The reason for this is because SQLite needs to write a lock file to the hard drive. After a processes finishes writting, it deletes the lock file. Other processes check for the lock file before writting to the SQLite database file and if present, delay writting until the lock file is no longer present.
jlsalinas at spamsucks dot gmx dot net
13-Feb-2004 08:22
For those looking for a function similar to mysql_list_tables, here you have:
if (! function_exists ('sqlite_list_tables')) {
function sqlite_list_tables (&$dblink) {
$tables = array ();
$sql = "SELECT name FROM sqlite_master WHERE (type = 'table')";
if ($res = sqlite_query ($dblink, $sql)) {
while (sqlite_has_more($res)) {
$tables[] = sqlite_fetch_single($res);
}
}
return $tables;
}
}
And a related funtion, to test if a given table exists:
if (! function_exists ('sqlite_table_exists')) {
function sqlite_table_exists (&$dblink, $table) {
$sql = "SELECT count(name) FROM sqlite_master WHERE ((type = 'table') and (name = '$table'))";
if ($res = sqlite_query ($dblink, $sql)) {
return sqlite_fetch_single($res) > 0;
} else {
return false; // or throw exception
}
}
}
Of course it would be preferable to have these functions included in the library, to avoid potential changes internal to SQLite; but we'll have to stick to this method until then.
vpupkin at comcast dot net
30-Nov-2003 10:53
if you are going to send INSERT queries, you will need to make the folder, where you put your "file.db", writable by a web server user, otherwise you'll receive error message - "Unable to open database . . . ". File permissions are not enough (phpinfo - SQLite Lib 2.8.3)
Minots Estichá <minots at D0X dot de>
21-Nov-2003 08:47
If you gone in trouble while/with installation of sqlite,
you can try the installation steps I´ve done at
RedHat9 with PHP4.3.4 and Apache 1.3.28
via the Linux shell:
# wget http://pecl.php.net/get/SQLite-1.0.tgz
# tar xzf SQLite-1.0.tgz
# cd sqlite
# export PHP_PREFIX="/usr"
# $PHP_PREFIX/bin/phpize
# ./configure
# make
# make install
After that add following to php.ini and restart Apache:
[sqlite]
extension="sqlite.so"
ng4rrjanbiah at rediffmail dot com
07-Nov-2003 09:19
| |
spl_classes