SQLite

The Current SQLite Logo

SQLite is an ACID-compliant relational database management system contained in a relatively small (~500kB) C programming library.

Unlike client-server database management systems, the SQLite engine is not a standalone process with which the program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the program. The program uses SQLite's functionality through simple function calls, which reduces latency in database access as function calls are more efficient than inter-process communication. The entire database (definitions, tables, indices, and the data itself) is stored as a single cross-platform file on a host machine. This simple design is achieved by locking the entire database file at the beginning of a transaction.

SQLite was created by D. Richard Hipp, who sells training, direct technical support contracts and add-ons such as compression and encryption. The source code for SQLite is in the public domain.

SQLite can be used with a lot of programs to store configuration data or user data. A few examples would be Postfix, Dovecot, Thunderbird, & Firefox.

Using SQLite

• To open a SQLite database.

sqlite3 /path/to/database/file

• Show the tables in a database

.tables

• To Exit SQLite

.quit

PHP & SQLite

Connect to the database

try{
$dbHandle = new PDO('sqlite:/var/www/lighttpd/noc/emailserver_stat.sqlite');
}catch( PDOException $exception ){
echo "Can NOT connect to database";
die($exception->getMessage());
}

Create a table if it doesn't exist

$sqlCreateTable = 'CREATE TABLE status (date date NOT NULL default \'0000-00-00\', item varchar(255) NOT NULL, value varchar(255) NOT NULL)';
$dbHandle->exec($sqlCreateTable); 

List the conent of a table

$search_date = "2009-02-11";
$sqlGetView = 'SELECT * FROM status WHERE date = "'.$search_date.'"';
$result = $dbHandle->query($sqlGetView);
echo "<table border='1'>";
 while ($entry = $result->fetch()) {
    echo "<tr><td> " . $entry['value'] . "</td><td>" . $entry['item']. "</td></tr>";
 } echo "</table>";

Display a single value from a table

$search_date = "2009-02-11";
$sqlGetView = 'SELECT value FROM status WHERE item = \'connections\' AND date = "'.$search_date.'"';
$result = $dbHandle->query($sqlGetView);
$pageView = $result->fetch();
$connections = $pageView['value'];

echo "$search_date
";
echo 'Number Of Connections: '.$connections.'
';