Documentation for SQLite Class written by Sascha 'SieGeL' Pfalz
Last updated on 07-Aug-2010
-----------------------------------------------------------------------------
1. INTRODUCTION
~~~~~~~~~~~~~~~
For several of my projects I needed a good logging backend to store various
log messages. As I also wanted to have the possibility to log errors when
the main database has problems I decided to give SQLite a go. I was really
impressed about the speed and ease of use, so to make it even more easy to
use SQLite this class was written based on my MySQL database class.
The API is mainly the same, some methods (like ConvertMySQLDate()) are not
implemented but the main functionality is exactly the same.
This doc gives you an overview of all available methods and their usage.
Examples are provided in the "examples" directory.
2. REQUIREMENTS
~~~~~~~~~~~~~~~
To use this class you have to met the following requirements:
- PHP 5.x
- "sqlite" extension. In PHP 5 this is included.
3. INSTALLATION AND USAGE
~~~~~~~~~~~~~~~~~~~~~~~~~
Copy the supplied db_sqlite.class.php to a directory of your choice, a good
place would be the inc/ directory of your project. Also copy the file
dbdefs.inc.php to the same directory you have copied the db_sqlite.class.php
file.
The file "dbdefs.inc.php" serves as the configuration file for the class.
You may give an alternate path to this file in the constructor of this class.
The file dbdefs.inc.php is automatically require()d by the class once you
instantiate it the first time.
The following defines can be set to use the class inside dbdefs.inc.php:
SQLITEDB_APPNAME - Enter here the name of the application using this
class. This define is mandatory, if you do not
set it the class will terminate with an error.
SQLITEDB_DEFAULT_DB - Full path and filename for the default database
to use. You can give an alternate database file
in the Connect() method, however it makes life
much easier to define here your default db and
call Connect() without any parameter.
SQLITEDB_ERRORMODE - How errors should be handled. Default is to show
only limited informations for safety reasons. See
description of SetErrorHandling() for further
details.
SQLITEDB_ADMINEMAIL - Specify here an e-mail address which should be
used to send error messages whenever an error
occures. This address is also shown whenever the
class shows an error message. Note that you have
to set SQLITEDB_SENTMAILONERROR to 1 to actually
retrieve any error messages.
SQLITEDB_SENTMAILONERROR - Flag indicating if the class should auto-send
emails to the defined EMail address whenever
an error occures. Set it to 1 to enable auto-
sending, and set it to 0 to disable.
SQLITEDB_MAIL_EXTRAARGS - Use this define to pass additional parameter to the
mail() command in SendmailOnError(). Some servers
might need to set the -f parameter when using PHP's
mail() command, and to allow this also here in the
class you can use this define. Default is unset.
SQLITEDB_USE_PCONNECT - If set to 1 persistant connections are used,
else standard connects are used. This can be set
also on a script-by-script basis via the method
setPConnect().
To use the class you have to require() the class code, the rest is done
automatically when you first instantiate the class. Normally you may have one
PHP script which includes several others, here would be the ideal place to put
the require() statement for the class, i.e.:
---[SNIP]---
// ...Your require() statements
require("path/to/db_sqlite.class.php");
// ..Rest of your code here
---[SNIP]---
Once this is done and you have added the proper values in dbdefs.inc.php you
can now start using the class, this would look like this for example:
---[SNIP]---
<?php
require("db_sqlite.class.php");
$db = new db_SQLite;
$db->Connect();
$ver = $db->Version();
$db->Disconnect();
echo("Your SQLite library version is v".$ver);
?>
---[SNAP]---
As you can see in this example the dbdefs.inc.php file is automatically
loaded when you create the first instance of the DB object.
You can also use a different configfile by specifying a different path to
your config inside the constructor, like this:
---[SNIP]---
<?php
require("db_sqlite.class.php");
$db = new db_SQLite('/path/to/my/own/config.inc.php');
$db->Connect();
$ver = $db->Version();
$db->Disconnect();
echo("Your SQLite library version is v".$ver);
?>
---[SNAP]---
4. METHOD OVERVIEW
~~~~~~~~~~~~~~~~~~
I've provided an auto-generated method overview inside the docs subfolder of
the distribution archive generated by phpDocumentor.
The class provides the following methods:
-----------------------------------------------------------------------------
static float GetMicrotime ()
-----------------------------------------------------------------------------
Returns current time in microseconds. Format is s.mmmmmm . Used to determine
SQL execution time.
-----------------------------------------------------------------------------
db_SQLite __construct ([string $ext_config = ''])
-----------------------------------------------------------------------------
The constructor of this class. If no parameter passed to it, the class tries
to require() the file "dbdefs.inc.php" and aborts if the file does not exist.
You can optionally pass a filename to the constructor where your own config
file is located. See "3. INSTALLATION AND USAGE" for a description of the
possible configuration defines.
-----------------------------------------------------------------------------
integer AffectedRows ([mixed $extsock = -1])
-----------------------------------------------------------------------------
Returns the amount of affected rows based on previous DML operation. Note
the word DML (Data Manipulation Language) which implies that this method
only returns values for INSERT, UPDATE, DELETE or REPLACE commands! If no
external connection handle is given the internal database handle is used.
-----------------------------------------------------------------------------
void Commit ()
-----------------------------------------------------------------------------
Commits a transaction. Note that this will only working if you have started
first a transaction with the Command "BEGIN TRANSACTION"! If you just have
fired some DML commands to the database, then autocommit is in effect and
Commit() may return an error! So correct way would be:
<?php
$db->Query("BEGIN TRANSACTION");
...do some DML
...
$db->Commit();
?>
-----------------------------------------------------------------------------
mixed Connect ([string $database = ''], [string $mode = ''])
-----------------------------------------------------------------------------
Connects to a database. In SQLite, a database is a flat file on disc. When
connecting to a SQLite database, an existing database file will be opened
or, if no file exists yet, a new database file will be created.
Normally you do not have to pass here any parameters, as the defaults from
dbdefs.inc.php are taken if you omit here all parameters.
Mode describes the filemask for the database file, this defaults to 0666.
Returns either a SQLite resource or 0 in case of an error.
-----------------------------------------------------------------------------
void Disconnect ([mixed $other_sock = -1])
-----------------------------------------------------------------------------
Disconnects from database. If you did not pass here an SQLite resource,
the internal one will be disconnected.
-----------------------------------------------------------------------------
string EscapeString (string $str)
-----------------------------------------------------------------------------
Backquotes all "dangerous" characters in the given string so that the string
can be safely added to a query string afterwards.
-----------------------------------------------------------------------------
array FetchResult (mixed $result, [integer $resflag = SQLITE_ASSOC])
-----------------------------------------------------------------------------
Returns the next row from the given result set or NULL if no more data is
available. You can choose how data should be returned, if $resflag is not
given data is returned as an associative array. You can also pass here
SQLITE_NUM or SQLITE_BOTH to get a numbered array or a mixed array with numeric
and associative elements.
-----------------------------------------------------------------------------
void FreeResult (mixed $result)
-----------------------------------------------------------------------------
Frees the given result set. Please always free the resultset after you've
read your data with FetchResult().
-----------------------------------------------------------------------------
string GetClassVersion ()
-----------------------------------------------------------------------------
Returns the class version in format major.minor, i.e. "0.20".
-----------------------------------------------------------------------------
integer GetDebug ()
-----------------------------------------------------------------------------
Returns the current bitmask for debug handling. See SetDebug() for further
details about debugging with this class.
-----------------------------------------------------------------------------
integer GetErrno ([mixed $other_sock = -1])
-----------------------------------------------------------------------------
Returns the error code from the last SQL operation. You can pass your own
connection handle here if you want.
-----------------------------------------------------------------------------
integer GetErrorHandling ()
-----------------------------------------------------------------------------
Returns the current internal error handling setting of the class.
This value can be set at run-time via the "SetDebug()" method or globally
via the dbdefs.inc.php define DB_ERRORMODE.
-----------------------------------------------------------------------------
string GetErrorText ([mixed $other_sock = -1])
-----------------------------------------------------------------------------
Returns the error description of the last SQL operation as pointed by the
$other_sock. If no value is given, the internal socket is used to read out
the error message.
-----------------------------------------------------------------------------
integer GetQueryCount ()
-----------------------------------------------------------------------------
Returns the current query counter. Whenever the class performs a query
against the database server an internal counter is incremented. This is
useful to track errors, as the Print_Error() function dumps out this value,
making it more easy to find the errornous query inside your scripts by simply
counting the queries down to the one where the error occures.
See examples/functions.inc.php in function DBFooter() for an example.
-----------------------------------------------------------------------------
float GetQueryTime ()
-----------------------------------------------------------------------------
Returns amount of time spend on queries executed by this class.
The format is "seconds.microseconds".
See examples/functions.inc.php in function DBFooter() for an example.
-----------------------------------------------------------------------------
integer LastInsertId ([mixed $extsock = -1])
-----------------------------------------------------------------------------
Returns last used auto_increment id. Whenever you INSERT a row with an
auto_increment field defined in the underlying table, SQLite auto-increments
this field. With this method you can retrieve the newly updated value.
If no external connection handle is given the internal handle is used.
-----------------------------------------------------------------------------
integer NumRows()
-----------------------------------------------------------------------------
Returns the number of rows in the result set.
Use this after a SELECT command has been executed.
For DML operations like INSERT, UPDATE, DELETE the method AffectedRows()
has to be used.
-----------------------------------------------------------------------------
void PrintDebug (string $msg)
-----------------------------------------------------------------------------
Depending on the current DEBUG setting the class dumps out debugging
informations either on screen, to the error.log of PHP or to both. If debug
is not enabled this function does nothing. This is extremly useful when
tracking errors, you can simply call SetDebug() with an debug level of your
choice before the query in question is executed and the class dumps out the
queries, so you can track easily what happens behind the scene.
Example:
---[SNIP]---
..
$db->SetDebug(db_SQLite::DBOF_DEBUGSCREEN);
$db->Query('SELECT FOO FROM BAR WHERE DUMMY=1');
..
..
---[SNAP]---
Would result in dumping out the Query on screen.
-----------------------------------------------------------------------------
void Print_Error ([string $ustr = ""], [mixed $var2dump = ""])
-----------------------------------------------------------------------------
This method serves as the general error handling method inside the class.
Normally this method dumps out the error occured together with additional
informations like used Variables and current query etc. After displaying
these informations this method calls exit() and terminates execution.
However you can modify this behavour with SetErrorHandling(). If you have
defined DB_ERRORMODE = db_SQLite:: DBOF_RETURN_ALL_ERRORS no error message
is shown, instead the class returns the error code to you, and you have to
handle the error conditions on your own.
If you have set db_SQLite::DBOF_SHOW_NO_ERRORS the class still displays an
error message, however the informations shown are limited so that an possible
attacker does not have all required informations in place to hack your site.
This is also default behavour.
In development environments it may useful to use the third flag
db_SQLite::DBOF_SHOW_ALL_ERRORS, in this mode all possible informations are shown
including the query that produces the error and a dump of all passed variables.
-----------------------------------------------------------------------------
mixed Query (string $querystring, [integer $resflag = SQLITE_ASSOC],
[integer $no_exit = 0])
-----------------------------------------------------------------------------
Performs a single-row query and returns result, either as numeric or as
associative array, depending on the $resflag setting.
With the $no_exit flag you can selectively instruct the class NOT to exit
in case of an error (set to 1), even if your master define DB_ERRORMODE has
a different setting.
This method returns the result of the call as array if sqlite_has_more()
returns TRUE, else the return result of sqlite_unbuffered_query().
-----------------------------------------------------------------------------
mixed QueryResult (string $querystring, [integer $no_exit = 0])
-----------------------------------------------------------------------------
Performs a multi-row query and returns a statement handle ready to pass to
FetchResult()/FreeResult().
With the $no_exit flag you can selectively instruct the class NOT to exit
in case of an error (set to 1), even if your master define DB_ERRORMODE has
a different setting.
-----------------------------------------------------------------------------
void Rollback ()
-----------------------------------------------------------------------------
Rolls back current transaction.
Note that you have to start first a transaction with "BEGIN TRANSACTION" !
See Commit() for an example!
-----------------------------------------------------------------------------
void SetDebug (integer $state)
-----------------------------------------------------------------------------
Function allows debugging of SQL Queries inside your scripts.
$state can have these values:
- db_SQLite::DBOF_DEBUGOFF = Turn off debugging
- db_SQLite::DBOF_DEBUGSCREEN = Turn on debugging on screen
(every Query will be dumped on screen)
- db_SQLite::DBOF_DEBUGFILE = Turn on debugging on PHP errorlog
You can mix the debug levels by adding the according defines. Also you can
retrieve the current debug level setting by calling the method "GetDebug()".
-----------------------------------------------------------------------------
void SetErrorHandling (integer $val)
-----------------------------------------------------------------------------
Allows to set the class handling of errors.
- db_SQLite::DBOF_SHOW_NO_ERRORS = Show no security-relevant informations
- db_SQLite::DBOF_SHOW_ALL_ERRORS = Show all errors (useful for develop)
- db_SQLite::DBOF_RETURN_ALL_ERRORS = No error/autoexit, just return the
sqlite_error code.
-----------------------------------------------------------------------------
string Version ()
-----------------------------------------------------------------------------
Returns Database Versionstring.
5. FINAL WORDS AND CONTACT ADDRESSES
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I'm using this class now in some projects and never encountered any
problems so far. However we all know that no software is 100% bugfree,
so if you have found a bug or have suggestions or feature requests feel free
to contact me under one of the following addresses:
WWW: http://www.saschapfalz.de/contact.php
EMAIL: php <at> saschapfalz <dot> de
ICQ: 9691810
AIM: SieGeL2k2
MSN: sapf@live.de
You can contact me whenever you are:
- missing some functionality
- found a bug
- have usage questions
Feel free to get in touch with me.
Happy coding!
-----------------------------------------------------------------------[EOF]-
$Id: README,v 1.3 2010/08/07 17:57:37 siegel Exp $
Click to download