SQLite is an open source embeddable database engine written in C by D. Richard Hipp. It is entirely self-contained with no external dependencies. It was introduced as an option in PHP V4.3 and is built into PHP V5. SQLite supports much of the SQL92 standard, runs on all major operating systems, and has support for the major computer languages. SQLite is also surprisingly robust. Its creator conservatively estimates that it can handle a Web site with a load of up to 100,00 hits a day, and there have been cases where SQLite has handled a load 10 times that.
SQLite's support of the SQL92 standard includes indices, limitations, triggers, and views. SQLite does not support foreign key constraints, but supports Atomic, Consistent, Isolated, and Durable (ACID) transactions.
This means that transactions are Atomic in that they are either fully executed or not at all. Transactions are also Consistent, with the database never being left in an inconsistent state. Transactions are Isolated, so if you have two transactions performing operations on the same database at the same time, they will not interfere with each other. And transactions are Durable, so that the database is able to survive crashes and power failures without data loss or corruption.
SQLite implements isolated transactions through exclusive and shared locks at the database level. This means that while multiple processes and threads can read from the same database at the same time, only one is able to write. A process or thread must acquire an exclusive lock before performing a write to a database. Once an exclusive lock has been issued, no other read or write operations can take place.
The complete SQLite locking semantics are documented at SQLite.org.
Internally, SQLite consists of several components: an SQL compiler, a core, a back end, and accessories. SQLite's utilization of a virtual machine, the Virtual Database Engine (VDBE), makes it easy to debug, modify, and extend SQLite's core. All SQL statements are compiled into easy-to-read assembly executed in the SQLite virtual machine.
Figure 1. Internal architecture of SQLite
SQLite supports databases of up to 2 TB, with each database stored entirely in a single disk file. These disk files are portable across different byte-ordered machines. The data is stored on disk in a B+tree data structure. SQLite depends on the file system for its database permissions.
SQLite does not support static data typing and, instead, uses column affinity. This means that instead of a datatype being a property of a table column, it is a property of the data itself. When a value is inserted into the database, SQLite will examine its type. If the type doesn't match that of the associated column, an attempt will be made to convert the value to the column type. If this not possible, the value will be stored as its native type.
SQLite supports the NULL, INTEGER, REAL, TEXT, and BLOB data types.
SQLite comes with a downloadable command-line interface for database administration. The command-line program is invoked with the name of the database, and a new database and table can be created as follows:
Listing 1. Creating a new database and table
C:\minblogg>sqlite3 c:\minblogg\www\db\alf.db
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table mytable(name varchar(40), age smallint);
sqlite> insert into mytable values('Nils-Erik',23);
sqlite> select * from mytable;
Nils-Erik|23
sqlite> |
We can then open the database again, list its tables and schema, and continue to insert and delete values.
Listing 2. Listing tables and the schema
C:\minblogg>sqlite3 c:\minblogg\www\db\alf.db SQLite version 3.2.1 Enter ".help" for instructions sqlite> .tables mytable sqlite> select * from mytable; Nils-Erik|23 sqlite> .schema CREATE TABLE mytable(name varchar(40), age smallint); sqlite> |
SQLite also comes with a command-line database analyzer, which allows you to show detailed information about the current state of any SQLite database.
Listing 3. The SQLite analyzer
C:\minblogg>sqlite3_analyzer www\db\alf.db Analyzing table mytable... Analyzing table sqlite_master... /** Disk-Space Utilization Report For www\db\alf.db *** As of 2005-Apr-24 18:56:40 Page size in bytes.................... 1024 Pages in the whole file (measured).... 2 Pages in the whole file (calculated).. 2 Pages that store data................. 2 100.0% Pages on the freelist (per header).... 0 0.0% Pages on the freelist (calculated).... 0 0.0% Pages of auto-vacuum overhead......... 0 0.0% Number of tables in the database...... 2 Number of indices..................... 0 Number of named indices............... 0 Automatically generated indices....... 0 Size of the file in bytes............. 2048 Bytes of user payload stored.......... 13 0.63% *** Page counts for all tables with their indices ******************** MYTABLE............................... 1 50.0% SQLITE_MASTER......................... 1 50.0% *** All tables ******************************************************* Percentage of total database.......... 100.0% Number of entries..................... 2 Bytes of storage consumed............. 2048 Bytes of payload...................... 91 4.4% Average payload per entry............. 45.50 Average unused bytes per entry........ 916.50 Maximum payload per entry............. 78 Entries that use overflow............. 0 0.0% Primary pages used.................... 2 Overflow pages used................... 0 Total pages used...................... 2 Unused bytes on primary pages......... 1833 89.5% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 1833 89.5% *** Table MYTABLE **************************************************** Percentage of total database.......... 50.0% Number of entries..................... 1 Bytes of storage consumed............. 1024 Bytes of payload...................... 13 1.3% Average payload per entry............. 13.00 Average unused bytes per entry........ 999.00 Maximum payload per entry............. 13 Entries that use overflow............. 0 0.0% Primary pages used.................... 1 Overflow pages used................... 0 Total pages used...................... 1 Unused bytes on primary pages......... 999 97.6% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 999 97.6% |
While it is possible to administer the database fully using the command-line interface, a database manager can be more convenient. There are a number of good Web-based SQLite database management systems. One of these is the PHP-based SQLiteManager.
Figure 2. Managing a database with SQLiteManager
There are two ways to back up an SQLite database. If the database is in use, the .dump command from the command-line interface should be used. This creates a file that contains the necessary commands and data to recreate the database. The .dump command can also be used to back up a database table.
Listing 4. The
.dump command
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE mytable(name varchar(40), age smallint);
INSERT INTO "mytable" VALUES('Nils-Erik', 23);
COMMIT;
sqlite>
|
If the database is not in use, one can simply copy the database file to a secure location.
It is good practice to separate SQLite databases from PHP code. An easy way to do this is to create a www directory. Inside that directory, create a db directory for the SQLite database, a dbscripts directory for the database and table creation scripts, and a backups directory for database backups.
Listing 5. Organizing SQLite databases with PHP V5
2004-12-06 15:43 DIR . 2004-12-06 15:43 DIR .. 2005-04-23 19:55 DIR db 2005-01-02 11:46 DIR dbscripts 2005-01-02 11:46 DIR backups |
Creating an SQLite database in PHP V5 is done just like in the command-line interface. If the database does not exist, an empty database is created.
$db = sqlite_open('../db/ac.db'); |
Creating a table is equally straightforward:
Listing 6. Creating a table
$db = sqlite_open('../db/ac.db');
sqlite_query($db, 'DROP TABLE post');
sqlite_query($db, 'CREATE TABLE post \
(id INTEGER PRIMARY KEY, kategori VARCHAR(20) NOT NULL,
titel VARCHAR(75) NOT NULL, referens VARCHAR(75), status VARCHAR(20) not null,
date varchar(10)not null, synopsis VARCHAR(120), inlaegg varchar(8192))');
|
As is inserting a record:
$sqldb = sqlite_open("../db/ac.db");
sqlite_query($sqldb, "INSERT INTO isvs VALUES ('$isvurl' , '$isvname', '$comment')");
|
And selecting data:
Listing 7. Selecting data from an SQLite database
$sqldb = sqlite_open("www/db/ac.db");
$results = sqlite_query($sqldb, "SELECT * FROM isvs order by isvurl asc ");
while (list($isvurl, $isvname) = sqlite_fetch_array($results)) {
sqlite_close($sqldb);
|
Using SQLite with a database abstraction layer
Two modern open source database abstraction layers offer support for SQLite: PEAR::DB, included in PHP V5, and the considerably more lightweight ezSQL. By making use of PHP Extension and Application Repository (PEAR) or ezSQL up front, SQLite can be used for rapid prototyping of applications that, should they later need to, be ported seamlessly to a more industrial-strength database.
Listing 8. Using ezSQL with SQLite
$users = $db->get_results("SELECT name, age FROM table1");
foreach ( $users as $user )
{
echo $user->name;
echo $user->age;
} |
There are several things that should be taken into consideration before deciding whether to use SQLite in an application.
- There is currently no network server available for SQLite. The only way to run SQLite on a different machine from the application is to run it from a network share. This can lead to problems, as both UNIX® and Windows® network shares have bugs regarding file locking. There is also performance degradation due to the latency associated with accessing a network share.
- SQLite offers database-level locks only. There are little tricks one can do to increase concurrency, but if your applications need table-level or row-level locks, a DBMS will better suit your needs.
- As mentioned earlier, SQLite can support a Web site of roughly 100,00 hits a day -- and, in certain cases, has handled 10 times the traffic. For a Web site with higher traffic or one that needs to stand up to a "slashdotting," you should consider a DBMS.
- SQLite has no concept of user accounts, and instead relies on the file system for all database permissions. This makes enforcing storage quotas difficult and enforcing user permissions impossible.
- SQLite supports most, but not all, of the SQL92 standard. Some features left out are complete trigger support and writable views. See the unimplemented SQL92 features.
If you feel any of these limitations will affect your application, you should look into a full-blown DBMS. However, if you can do without these limitations and are interested in an open source, fast, flexible, and embeddable database engine, you should give SQLite some serious consideration.
Some areas where SQLite really shines are Web sites, managing application data, rapid application prototyping, and educational tools.
Because of their small footprint, fast performance, and zero administration costs, embeddable databases are finding important niches and becoming cost-efficient for applications that previously could not afford a database as a back end for persistent data. There is simply no reason today to use flat files for persistent storage. The ease of use of embeddable databases such as SQLite is fueling application development and allowing small applications the luxury of full-fledged SQL support. This is especially important for applications that target the small-device space.
Embeddable databases are also important for rapid application development, especially if used with a database abstraction layer, such as PEAR::DB or ezSQL. Finally, SQLite is under active development and is sure to have new features in the future, making it even more useful to the open source community.
Learn
-
See the developerWorks article "Auditing PHP, Part 1" for an outline of the basic issues developers need to keep in mind when creating PHP applications.
-
"PHP by example, Part 1" is first in a series offering the basics of PHP. It features a Webzine that includes an author's page, where content providers can enter the text of articles, as well as a front end for presenting this content to the world.
-
"Learning PHP, Part 1" is first in of a three-part series that takes you from the most basic PHP script to working with databases and streaming from the file system by documenting the building of a document workflow system.
-
Check out SQLite, by Chris Newman, a great book on the open source embeddable database.
-
Check out SQLite, by Chris Newman, a great book on the open source embeddable database.
-
Learn more about Apache Derby and Cloudscape.
-
Visit IBM developerWorks' PHP project resources to learn more about PHP.
-
Stay current with developerWorks technical events and webcasts.
-
Check out upcoming conferences, trade shows, webcasts, and other Events around the world that are of interest to IBM open source developers.
-
Visit the developerWorks Open source zone for extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM's products.
-
To listen to interesting interviews and discussions for software developers, be sure to check out developerWorks podcasts.
Get products and technologies
-
Visit SQLite to download the latest version of SQLite and the command-line interface, as well as documentation and the latest news.
-
Download SQLiteManager for easy SQLite database management and administration.
-
Download PHP V5, which includes SQLite.
-
Get the PEAR::DB database abstraction layer.
-
Visit jvmultimedia to download the ezSQL database abstraction layer.
-
Download a combined PHP V5 binary distribution that includes Cloudscape.
-
Innovate your next open source development project with IBM trial software, available for download or on DVD.
Discuss
-
Get involved in the developerWorks community by participating in developerWorks blogs.
Nils-Erik Frantzell is a senior at the University of California, Santa Cruz. He is interested in databases (particularly their internals), management of information, Web technologies, and fiddling with computer hardware. He spends his time away from the computer tending to tropical fish while listening to electronic music.
Comments (Undergoing maintenance)
Table of contents
Local resources
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
