Breadcrumbs

SQLite Overview

Introduction

If you have ever dabbled in PHP (external link), chances are, you would have used a database like MySQL (external link). You might have written a messageboard script, or simply used the database to store parts of your website that you wanted to modify through a script.

Unfortunately, it is not so easy if you want to write a program in C++ that utilizes databases. In PHP4, MySQL support is default, and it is quite easy to find a webhost that provides a MySQL database. For an application program in C++, database support is definitely not part of the language, and users may not have a database server installed.

The good news though, is that SQLite (external link) is a relational database management system written to be embedded in C/C++ programs. Its design paradigm is simplicity, and SQLite is easy to use, reliable, fast, and has a small size overhead. There is no server to install and configure. Each SQLite database is contained in a single file, so transferring the database is as easy as transferring that file. If you're concerned about licensing issues when writing programs that use SQLite, there's no worry as SQLite's source code is in the public domain.

Manifest Typing

Manifest typing is an interesting feature of SQLite. The type of the data is determined by the data itself, rather than as a property of the column used in storing it. This is unusual (and non-standard) in SQL, but does add flexibility. Additionally, there is type affinity in SQLite. An attempt is made by the database to conform the data's type to the declared type of the column. If conformity cannot be achieved, then the data's type is left untouched.

Command Line Interface Program

So we do not have a server to download and install, but we want to work with SQLite databases. What do we do? Well, hop over to the SQLite download page (external link). We shall download the command line program for accessing and modifying SQLite databases. Get the version for SQLite3. For Linux this would be sqlite3-3.2.0.bin.gz and for MS Windows it would be sqlite-3_2_0.zip, but of course the version number might have changed, so just take that into account.

Extract the program (to your system path, if you prefer), and run it from the command line. This would be a shell like bash for Linux, and cmd.exe or command.com for MS Windows (using Start -> Run is one way to access it). To exit from the program, enter .quit or .exit - but you would know that if you entered .help for instructions.

Creating A Database

To actually work with a database, use the database name as a parameter when running the program, e.g.

sqlite3 mytest.db

I like using .db as a database file extension, but you can use any extension, or none at all. You can even work with a database in memory by using :memory: as the database name.

From the command line, enter this SQL statement:

Code: SQL

CREATE TABLE children(
child_id INTEGER PRIMARY KEY,
name VARCHAR(32),
sex CHAR,
age INTEGER
);

As per normal SQL, the query is only run when the terminating semi-colon is encountered, so you can press enter for each line quite safely.

Now enter the command:

.tables

You should see 'children' listed as a table.

Reading SQL From File

At this point, how to manipulate the database is obvious - you just enter the SQL queries using this command line program. However, it is often easier to read off SQL statements from a file. Besides, you can more easily format the statements, so using the .schema command to see what statements were used will display nicer output.

Copy and paste the following SQL statements to children.sql:

Code: SQL

INSERT INTO children (name, sex, age) VALUES('Amanda', 'F', 16);
INSERT INTO children (name, sex, age) VALUES('Charis', 'F', 20);
INSERT INTO children (name, sex, age) VALUES('Desmond', 'M', 15);
INSERT INTO children (name, sex, age) VALUES('Bernadette', 'F', 15);
INSERT INTO children (name, sex, age) VALUES('Charles', 'M', 9);

Now run the command:

.read children.sql

To check that everything went right, run the query:

Code: SQL

SELECT * FROM children;

So we have the data we want, but let's face it, the result looks ugly.

Now we run the command:

.explain ON

Then run our SELECT query again.

Note that we did not specify the child_id column for our INSERT's, but as the child_id column is INTEGER PRIMARY KEY, it becomes an auto-increment column. This a special case where manifest typing is not used. Any column (but only one for each table) with the exact type of INTEGER PRIMARY KEY is taken as auto-increment. This column actually becomes an alias for the rowid column present in every table.

Points To Note

Let's set Charis' age to 21:

Code: SQL

UPDATE children SET age=21 WHERE child_id=2;

Notice that I used Charis' child_id, but I could also use rowid instead of child_id.

To check that the change was made:

Code: SQL

SELECT * FROM children WHERE [sex]='F';

Here the brackets enclosing 'sex' is to ensure that even if 'sex' was an SQL keyword, it would be taken as an identifier. This is just like the way backticks (`) are used in MySQL.

Let's try deleting Charis' entry, and inserting one for Selphie. You could place the next 3 SQL statements into a file to .read them:

Code: SQL

DELETE FROM children WHERE name='Charis';
INSERT INTO children (name, sex, age) VALUES('Selphie', 'F', 17);
SELECT * FROM children;

Notice that Selphie's rowid (i.e. the child_id column) is 6, as expected from auto-increment. If we reached the largest possible rowid, then SQLite will try to re-use rowids of deleted rows. However, if the table was created with a INTEGER PRIMARY KEY AUTOINCREMENT column, then new rowids will always be greater than the largest rowid that ever existed in the table. So if you run out of AUTOINCREMENT rowids, you cannot insert new rows. There's little to worry though, since the maximum rowid is 2**63-1.

SQLite's Virtual Machine Instructions

SQLite uses a virtual machine to manipulate its databases. You only need to know SQL to manipulate SQLite databases, but SQLite itself translates your SQL to its virtual machine code.

The .explain command that you used earlier formats the output from the sqlite program. This related especially to a SQLite-specific SQL keyword - EXPLAIN:

Code: SQL

EXPLAIN SELECT * FROM children;

Running the above query would give you an 'explanation' of what virtual machine code would have been used for the SELECT statement, without actually running the SELECT statement.

Summary

From this article, you should have some idea what SQLite is about, and how to use it by using the command line interface provided. If you have a good knowledge of SQL, you should be able to work with SQLite databases quite easily, especially with the SQL syntax (external link) reference of SQLite.