|
|
 |
CIII. SQLite
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.
Pozn�mka:
For further information see the SQLite Website
().
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:
().
In PHP 5, the SQLite extension and the engine itself are bundled and
compilled by default.
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 functions sqlite_fetch_array() and
sqlite_current() use a constant for
the different types of result arrays. The following constants are
defined:
Tabulka 1. SQLite fetch constants | constant | meaning |
|---|
| SQLITE_ASSOC |
Columns are returned into the array having the fieldname as the array
index.
| | SQLITE_BOTH |
Columns are returned into the array having both a numerical index
and the fieldname as the array index.
| | SQLITE_NUM |
Columns are returned into the array having a numerical index to the
fields. This index starts with 0, the first field in the result.
|
Chov�n� t�chto funkc� je ovlivn�no nastaven�m parametr� v php.ini.
Tabulka 2. SQLite Configuration Options | Name | Default | Changeable |
|---|
| sqlite.assoc_case | 0 | PHP_INI_ALL |
For further details and definition of the PHP_INI_* constants see
ini_set().
Zde je stru�n� vysv�tlen�
konfigura�n�ch direktiv.
- 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.
alexs at alphacomolex dot info
28-Oct-2004 10: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
22-Oct-2004 01: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 10: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 06:39
I've written a short article about the (lack of) use of SQLite in-memory databases: . Hopefully it will help somebody out there ;)
jon at jenseng dot com
20-Jul-2004 09: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:
Source:
david at acz dot org
15-Jul-2004 01: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.
13-May-2004 02:46
PHP5 compile time option --enable-sqlite-utf8
See the note about buggy utf-8 support
Adam Desaulniers
11-May-2004 12:54
OO implementation changed as of 5.0.0RC2 -- converted to studlyCaps (helly)
<?
$objDB = new sqlite_db('foo.sqlite');
$arrRows = $objDB->array_query('select foo from bar');
$objDB = new SQLiteDatabase('foo.sqlite');
$arrRows = $objDB->arrayQuery('select foo from bar');
?>
and so forth:
csaba at alum dot mit dot edu
14-Apr-2004 03:16
If you want to get the list of all the columns in a table (and associated information), PRAGMA is helpful (see 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 05: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
14-Feb-2004 02: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.
05-Feb-2004 04:11
Since this extension is rather new and few tutorials/articles talk about it... here's one worthy of a link:
vpupkin at comcast dot net
01-Dec-2003 04: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>
22-Nov-2003 02: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
# 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 03:19
Very nice MS PowerPoint presentation titled "SQLite and PHP" (author: Wez Furlong) can be downloaded at [134 KB]
HTH,
R. Rajesh Jeba Anbiah
| |
is_soap_fault
