Our Product Development team has started the architecture and design process of an app for Android phones, which uses SQLite as a critical part of the app architecture. Since this was a new technology for both our architect and myself, I decided do some reading on the Web so that I could help him in the detailed architecture and design activities
Basically SQLite is an RDBMS that fits into a small file and exists as an integral part of a client application (unlike other RDMBS that exist as separate "database servers"). At the same time, it supports terabyte-sized databases and gigabyte-sized strings and blobs. The SQLite website calls it "a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine". It also claims that SQLite transactions "are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures"
The website also claims that "SQLite is the most widely deployed SQL database engine in the world". The reason is not hard to see, as "SQLite is popular choice for the database engine in cellphones, PDAs, MP3 players, set-top boxes, and other electronic gadgets". Also, since "it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites". SQLite is used by major corporations ranging from Adobe to Mozilla and Airbus to Toshiba
Wikipedia tells me that "D. Richard Hipp designed SQLite in the spring of 2000 while working for General Dynamics on contract with the United States Navy" and also that "SQLite implements most of the SQL-92 standard for SQL but it lacks some features. For example it has partial support for triggers, and it can't write to views". SQLite "is weakly typed in some of the same ways that Perl is: one can insert a string into an integer column (although SQLite will try to convert the string to an integer first, if the column's preferred type is integer)"
Writing PHP code for SQLite is simple, as PHP has the SQLite3 library pre-installed but it has to be enabled by uncommenting two lines in the PHP.ini file:
extension=php_pdo_sqlite.dll
and
extension=php_sqlite3.dll
Here are some simple lines of code for manipulating an SQLite database using PHP:
<html>
<head>
<title>PHP with SQLite</title>
</head>
<body>
<h2>Testing PHP with SQLite</h2>
<?php
#Create or open SQLite database
$db = new SQLite3('mysqlitedb.db');
#Create table if it doesn't already exist
$db->exec('CREATE TABLE foo (bar STRING PRIMARY KEY)');
#Insert row in table or replace existing row
$db->exec("INSERT OR REPLACE INTO foo (bar) VALUES ('This is a test')");
#Read data from table and display it
$result = $db->query('SELECT bar FROM foo');
while ($row = $result->fetchArray()) {
print ("Value of <i>bar</i> in table <i>foo</i>: " . $row["bar"] . "<br>");
}
?>
<br>
<a href="index.php">Back</a>
</body>
</html>
