• Installation
• Creating a Database
• Accessing a Database
• Accessing a Database With SQL Relay

I've successfully installed SQLite on Linux, FreeBSD, NetBSD, OpenBSD, SCO OpenServer and Solaris. On all platforms, I've had to compile it from source. SQLite is available from the SQLite home page. I usually give the configure script the --prefix=/usr/local/sqlite parameter so that SQLite will be installed entirely under /usr/local/sqlite and add /usr/local/sqlite/bin to my PATH environment variable and /usr/local/sqlite/lib to my LD_LIBRARY_PATH environment variable.

On OpenBSD and NetBSD sqlite will build cleanly but will crash at run time. To get it working, you have to edit the Makefile that the configure script generates, search for a line like:

LIBREADLINE = -lreadline

And add -lcurses to the end of it as such:

LIBREADLINE = -lreadline -lcurses

Compiling sqlite with this modification will make it work.

In version 1.0.x of SQLite, a database is just a directory with table files in it. In versions 2.x, the database is a single file. Unlike other relation database systems, there is no daemon managing the database, the SQLite API interprets queries and runs them against the file(s) directly. I usually create a directory /usr/local/sqlite/var and create database directories or files there as follows. SQLite database users are equivalent to unix users. Database directory and file permissions dictate what permissions a user has on the database.

mkdir /usr/local/sqlite/var
chown testuser /usr/local/sqlite/var
chmod 755 /usr/local/sqlite/var

For SQLite versions 2.x, it is only necessary to create the directory, the database file will get created the first time a user tries to access it.

For SQLite version 1.0.x, the following commands are necessary to create a database called testdb owned by the user testuser.

mkdir /usr/local/sqlite/var/testdb
chown testuser /usr/local/sqlite/var/testdb
chmod 755 /usr/local/sqlite/var/testdb

To access an SQLite database, su to the appropriate user and run the sqlite command line utility.

su testuser
sqlite /usr/local/sqlite/var/testdb

Once you're connected to the database, the sqlite client prompts you to enter a query. Queries may be split across multiple lines. To run a query, end it with a semicolon. To exit, type .exit

A sample sqlite session follows.

[testuser@localhost testuser]$ sqlite /usr/local/sqlite/var/testdb
SQLite version 2.2.0
Enter ".help" for instructions
sqlite> create table testtable (
   ...> col1 char(40),
   ...> col2 integer
   ...> );
sqlite> .tables
testtable
sqlite> insert into testtable values ('hello',50);
sqlite> insert into testtable values ('hi',60);
sqlite> insert into testtable values ('bye',70);
sqlite> select * from testtable;
hi|60
hello|50
bye|70
sqlite> update testtable set col2=0 where col1='hi';
sqlite> select * from testtable;
hi|0
hello|50
bye|70
sqlite> delete from testtable where col2=50;
sqlite> select * from testtable;
hi|0
bye|70
sqlite> drop table testtable;
sqlite> .exit

Accessing SQLite from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called sqlitetest. This instance connects to the /usr/local/sqlite/var/testdb database. Note how the runasuser and runasgroup attributes of the instance tag are set to the owner of the database.

<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>

        <instance id="sqlitetest" port="9000" socket="/tmp/sqlitetest.socket" dbase="sqlite" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="testuser" runasgroup="testuser" cursors="5" authtier="listener" handoff="pass">
                <users>
                        <user user="sqlitetest" password="sqlitetest"/>
                </users>
                <connections>
                        <connection connectionid="sqlitetest" string="db=/usr/local/sqlite/var/testdb" metric="1"/>
                </connections>
        </instance>

</instances>

Now you can start up this instance with the following command.

sqlr-start -id sqlitetest

To connect to the instance and run queries, use the following command.

sqlrsh -id sqlitetest

The following command shuts down the SQL Relay instance.

sqlr-stop sqlitetest