mod_sqlite
A Network Database Server for SQLite

Description

mod_sqlite is an Apache 2.0 module which provides access to SQLite databases over HTTP. SQLite required that clients of the database be on the same machine as the SQLite database itself. mod_sqlite allows you to access your SQLite databases over a network.

Features

  • Works with any existing SQLite database
  • Configuration is familiar Apache style
  • Apache's built in authentication methods can be used
  • mod_sqlite is available over a network
SourceForge.net Logo

Screen Shots

Click for larger image:
Select statement Select statement with bind parameter Select statement with an error

Installation

Make sure to install the following package before attempting to install mod_sqlite:
Then download the source or the RPM from here. Just install the RPM, or follow the instructions in the INSTALL file from the source.

Configuration

Configuration is very simple. Just create a location for your SQLite database, set the handler to sqlite, turn SQLite on, and you're done! Here is a sample configuration that is used while developing:

  <Location /test>
    SetHandler sqlite-handler
    SQLite On
  </Location>

Now access is provided to any SQLite database on the /test location.

Using mod_sqlite

mod_sqlite allows you to access your SQLite database over HTTP. To do this, the Location that you have configured mod_sqlite to use will take query parameters that allow the user to query the database. Here is a list of those parameters, and what they do:

  'db'   -- The 'db' parameter specifies what file the SQLite database resides in. mod_sqlite will attempt to read from the file specified by this parameter.
  'q'   -- The 'q' parameter specifies what SQL query should be executed.
  'p'   -- The 'p' parameter specifies the value of a bind parameter to use.

Usage examples:

To query the database '/tmp/foo.txt' with the SQL 'select * from test', the query would look like this:

  http://localhost/test?db=/tmp/foo.txt&q=select+*+from+test

To query the same database, but use bind parameters with the statement 'select * from test where name = %Q and id = %Q' where name equals 'Aaron' and id equals '1' would look like this:

  http://localhost/test?db=/tmp/foo.txt&q=select+*+from+test+where+name+%3D+%25Q+and+id+%3D+%25Q&p=Aaron&p=1

The query string looks complicated, but it is just the SQL statement URI encoded. Just URI encode the data to send to mod_sqlite. mod_sqlite will support GET's as well as POST's, so your data can be longer than a GET will support.

Return values:

After executing a query, mod_sqlite will return the results URI encoded and separated by semicolons. The first line of the results will always be the column names corresponding to the data. Each row is separated by a newline. A result set might look something like this:

  id;name;password
  1;Aaron;1234
  2;Bill;secret

Error handling:

If any errors occur mod_sqlite will set a special header in the HTTP response called 'X-SQLite-Error'. 'X-SQLite-Error' will contain the error that SQLite threw.

Advanced Configuration

mod_sqlite provides other Apache configuration directives that allow you to tweak the way mod_sqlite performs queries. Here is a list of directives and what they do:

  SQLiteDB /foo/bar.txt

SQLiteDB specifies what database mod_sqlite should read from. mod_sqlite will read for the specified database and ignore the 'db' query parameter if SQLiteDB is set.

  SQLiteQuery "select * from test"

SQLiteQuery specifies what query mod_sqlite should execute. mod_sqlite will ignore the 'q' query parameter if SQLiteQuery is set in the Apache configuration.

  SQLiteBaseDir /home/aaron

SQLiteBaseDir specifies where mod_sqlite will look for databases. So if the 'db' parameter is set to 'foo.txt', then mod_sqlite will try to read '/home/aaron/foo.txt'. If SQLiteBaseDir is not set, mod_sqlite assumes that the'db' parameter is a complete file path to the database.

Example configuration:

The following configuration will only execute "select * from test" on the database "/tmp/foo.txt" no matter what the user passes in:

  <Location /test>
    SetHandler sqlite-handler
    SQLite On
    SQLiteDB /tmp/foo.txt
    SQLiteQuery "select * from test"
  </Location>