lighttpd & SQLite: A Lightweight Web Server and Database Platform

The combination of Apache and MySQL has become the defacto standard in Web and database servers on GNU/Linux systems — so much so that the acronym LAMP (Linux, Apache, MySQL and PHP) is commonly used to refer to this particular set of tools in the context of Web development. The Apache Web server is rightly regarded as one of the best, and it has proved its reliability on countless websites hosted all over the globe. It has accumulated a lot of useful features over the years, enhancing its professional value even more.

There is a tendency among users, however, to underutilise computer hardware and software. Although the Apache Web server provides much functionality, there are many cases where much of its plethora of features simply remains unused. Also, often there are not enough computational resources available to run a large software application. For example, having just a low-end computer or a small mobile computing device should not be an obstacle to it hosting a tiny website. So instead of using a sledgehammer to crack a nut, what you need to do is look for an alternative.

Among the several lightweight Web servers available is lighttpd, pronounced “lighty”. lighttpd has all the features required of a modern Web server, including FastCGI, authentication and URL rewriting, but makes do with modest resources, and is suitable for use on relatively resource-constrained systems. This article demonstrates how to install and configure Lighttpd on a Fedora system; the procedure for other distributions is similar.

Using your package-management system, install the lighttpd package. On Fedora systems, you also need to install the lighttpd-fastcgi package to be able to run PHP. Later, we will look at how to set up the server to run PHP.

Configuring lighttpd

lighttpd uses a single primary configuration file named lighttpd.conf, which is stored in /etc/lighttpd/. Open this file in a text editor with root privileges. The configuration parameters in this file are well-commented, and also have self-explanatory names. The server.modules parameter is an array of modules to be loaded by lighttpd when it starts. At the very least, mod_access and mod_accesslog should be enabled.

For our purposes, we also need to enable mod_fastcgi. To get a more comprehensive feel for lighttpd configuration, also enable mod_userdir, which will enable you to use a subdirectory of your home directory to serve Web pages. In the server.document-root parameter, provide the path to a default document root for your server (not the subdirectory that is to be used through mod_userdir). In the index-file.names array, include index.php or default.php or whatever file name(s) you use for your default PHP pages, if not already present.

lighttpd allows you to selectively prevent or allow access to specific files or directories. For example, to prevent access to directories with _private in the name, use the following configuration parameter:

$HTTP["url"] =~ "_private" {
  url.access-deny = ("")
}

Next, make sure that the static-file.exclude-extensions array contains the .php, .pl and .fcgi extensions, to enable running PHP, Perl and other FastCGI programs. If you use other extensions for your programs, add them here. If you want to run lighttpd along with another Web server, specify the TCP port that lighttpd should listen on for incoming requests, in the server.port parameter (it is commented out by default).

The major part of the configuration to run PHP is the fastcgi.server parameter. This should look like what follows:

fastcgi.server = ( ".php" =>
                   ( "localhost" =>
                     (
                       "socket" => "/var/run/lighttpd/php-fastcgi.socket",
                       "bin-path" => "/usr/bin/php-cgi"
                     )
                   )
                 )

Finally, ensure that there is an include_shell command near the end of the file including files within /etc/lighttpd/conf.d/. Don’t try to run PHP yet.

Since we had enabled mod_userdir, it is time to provide a userdir configuration. In the /etc/lighttpd/conf.d/ directory, create a text file named userdir.conf with root ownership, if it does not exist. In that file, provide just a single parameter, userdir.path, which specifies the subdirectory of your home directory from which you want to serve files. The path is relative to your home directory — for example, userdir.path = "public_html" or userdir.path = "Projects/www".

The configuration of lighttpd is now complete. The next step is to set up PHP to work with it.

Setting up PHP to work with lighttpd

PHP is usually called as a module in the Apache Web server. However, with lighttpd, it needs to be called as a FastCGI program. To enable this, PHP has a version of the interpreter that runs as a standalone program. On Fedora, install the php-cli package for it; there may be other packages for other systems. (Note: the package named php on Fedora provides the Apache module for PHP, which is not what you want.)

After the PHP standalone program is installed, the cgi.fix_pathinfo PHP configuration parameter needs to be set for PHP to work correctly with lighttpd. On Fedora, you should have a file named lighttpd.ini in /etc/php.d/ after the php-cli installation. If not, create it with root ownership. It should just have: cgi.fix_pathinfo = 1. Alternatively, or on other systems, if you do not want or have lighttpd.ini in /etc/php.d/, you can set this parameter within the main php.ini file itself.

At this point, you can run lighttpd and also run PHP programs through it. To start lighttpd on Fedora, use the command su -c 'service lighttpd start' and enter your root password when prompted. To stop lighttpd, just use the word stop instead of start in above command; use status to get the service’s current status. The sudo command can also be used for the purpose, if you have sudo privileges.

Accessing files using mod_userdir is similar to the way it is done in Apache — construct the file’s URL using ~username. For example, if I specify Projects/www in the userdir.path parameter and want to run test.php stored in the Projects/www subdirectory of my home directory, I need to enter the URL http://localhost/~saurav/test.php, where saurav is my username. Similarly, if test.php is in a subdirectory of Projects/www (for example, Projects/www/test/test.php), I need to enter http://localhost/~saurav/test/test.php.

Apart from this, you need to ensure that the user account under which lighttpd runs (usually named lighttpd) has read access to the directory from which you want to serve Web pages, and read-and-write access to your SQLite databases and the directory containing them.

SQLite

Relational database management systems, being based on a clean and well-implemented design founded on mathematical principles, offer a lot of convenience for managing data in a standard manner, while allowing adhoc queries on the database. Most relational database management systems are implemented as database servers, which require the user (administrator) to monitor and tune the server for performance.

A server is required when multiple simultaneous client connections need to be served — but that doesn’t mean that relational databases can only be used with database servers. You may prefer a server-less system in many situations — for example, using a database like an ordinary file for a single user or application, especially on a system with low resources.

Being able to use a relational database and run SQL queries on it in such situations can eliminate a lot of file-handling and information-parsing headache, allowing you to make use of a medium that you are already familiar with. SQLite is one tool that enables this.

SQLite is a tool that stores a relational database in a single file. You can create, populate and query database tables using SQL within that file. You can copy the file and use it anywhere SQLite is installed. This article demonstrates how to install and use SQLite on Fedora; the procedure is similar for other systems.

Installing SQLite and allied packages

There are two versions of SQLite currently in use, versions 2 and 3, one or both of which are sometimes already installed on GNU/Linux systems. We will use version 3 here, because it is the latest one. On Fedora, using your package-management system, install the sqlite package if it is not already installed. The sqlite2 package provides SQLite version 2, and you can install both side by side. You may also want to install the sqlite-doc package to get offline access to the SQLite documentation.

To use SQLite 3 with PHP, you need to access it through PDO. Install PDO if it is not already installed; the Fedora package is php-pdo. You can check whether the SQLite PDO driver is available by running the PHP statement print_r(PDO::getAvailableDrivers());. This is all that is required to use SQLite.

SQLite is very simple to use through the text-mode program provided with it, but if you prefer a graphical interface, install the sqliteman package. We will use the text-mode program for the purposes of this article.

The quirks of SQLite

Though SQLite provides an SQL interface to manage data, it is one of the quirkiest database management systems. It mostly adheres to SQL92, but there are some areas where it deviates, and there are a few features it does not provide. For example, the ALTER TABLE statement implemented in it provides a facility to add and rename columns, but not to drop or alter them. Also, there is no concept of GRANT and REVOKE because the database is stored in a file, and is intended to be used by a single process at a time — so only the filesystem privileges for the database file govern its use.

The part of SQLite that differs the most from other systems is its handling of data types. SQLite does not associate data types with columns or fields. Rather, it associates the type with the data values, similar to dynamically typed programming languages. One of the consequences of this is that you can store any type of data in any type of field. Column types are specified in the CREATE TABLE statement just as in other, more traditional systems, but the type only serves as a hint to the type of data expected in the field, rather than as a constraint. When you try to enter a value in a field, SQLite tries to convert it to the field’s specified type — but even if it cannot perform the conversion, it will still store the value.

There are actually very few data types available in SQLite: NULL, INTEGER, REAL, TEXT and BLOB. Significantly, there is no data type for date and time values. Dates and times can be stored in the international format as TEXT, a UNIX timestamp as INTEGER, or as a number of Julian days as REAL. To work with dates and times, SQLite provides a few functions, which you can read about in the documentation.

Also of note is how SQLite handles sizes or ranges of data values. SQLite only has fixed ranges for data values. For example, even if you specify a number of characters for VARCHAR, the maximum number of characters allowed is actually the maximum allowed by TEXT. The INTEGER type allows a maximum of 8-byte integers, and the REAL type is the IEEE 8-byte floating-point number type.

The strangest part of SQLite, however, is that you can specify literally anything for a column’s data type! For example, you can use the following CREATE TABLE statement:

CREATE TABLE strange_table (x strange_type);

This will succeed because SQLite uses a fixed set of rules to determine the affinity of a column, based on sub-strings of what is specified for the column’s type. The affinity of a column is the hint for the type of data to be stored in the column, specified as the column’s “data type” in the CREATE TABLE statement. If the type name has int in it, the affinity will be INTEGER. For example, INT, INTEGER, MY_INTEGRAL_TYPE, and FLOATING POINT are all INTEGER!

Similarly, type names in which char, clob or text occur as a sub-string are treated as TEXT. If no type name is specified, or if the sub-string blob occurs in the type name, the special affinity NONE is used. The sub-strings real, floa and doub create the REAL affinity. All other type names cause the column to have NUMERIC affinity, which can be used to store both integral and floating-point values. For example, the type name STRING will create the NUMERIC affinity. These rules are applied in this order, which is the reason that FLOATING POINT has INTEGER affinity, not REAL.

A final quirk to be aware of is that SQLite stores a unique row ID for every row in every table, and any column declared with a type of INTEGER PRIMARY KEY becomes an alias for the row ID. This requires the exact name INTEGER, and not a sub-string like INT. You may use the AUTOINCREMENT parameter for an INTEGER PRIMARY KEY, but not any other. Using other constraints, such as ASC or DESC, requires splitting the constraint into a separate constraint section, like PRIMARY KEY(<column_name> <other constraints>). If you do not use an INTEGER PRIMARY KEY column, you can retrieve the row ID using the column name ROWID, OID or _ROWID_.

All of this is very likely to surprise users familiar with other, more traditional SQL-based database management systems, but it does not cause problems in the intended use cases of SQLite. Besides, the usual data types allowed by SQL are covered correctly under the rules. You just need to decide on a type for date and time values.

An example

We will now consider a small example of how SQLite can be used with PHP via the PDO library, which is the recommended method of using SQLite 3 with PHP. We will create a simple version of the traditional products table used to illustrate many database concepts. Only a single table will be used to illustrate working with SQLite. To be able to run the example successfully, ensure that you have enabled proper privileges (see the warning above).

The database

To run the text-mode program for SQLite 3, named sqlite3, open a terminal. Since the SQLite database will be stored in a single portable file, either change the working directory to the one in which you want to create the file, and then run sqlite3 with just the file-name as an argument; or you can run sqlite3 and specify the path to the file as the argument. For example, sqlite3 Projects/products.sqlite or sqlite3 Projects/products.db. The extension you use for the file name is immaterial, but use the name products for this example, or substitute it everywhere with any other name you choose.

The SQLite program should now be awaiting input at the sqlite> prompt. You can enter .help to see the meta-commands that can be used with sqlite3, or enter SQL statements terminated by the semicolon. (Note the dot in .help. All meta-commands begin with a dot.)

Create the products table as follows:

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    price NUMERIC
);

Statements can be split across multiple lines. The prompt will accept the input as part of the statement until a semicolon is entered. You can use the .schema meta-command to look at the structure of tables. For example, .schema products.

Enter a few records after this, then issue a SELECT * query on products. The output may be in the form of values separated by a bar symbol (|). You can change the separator symbol to something else, or you can change the output to a more traditional form. There are several modes of output. To get the traditional format, issue the meta-command .mode column. This may still not display the column names. To get those, use .headers. You can store meta-commands in a file named .sqliterc in your home directory, to automatically use them whenever you run sqlite3. To terminate the sqlite3 program, use the .quit or .exit meta-command.

The program

The recommended method of accessing SQLite 3 through PHP is using the PHP Data Objects (PDO) library. PDO is a set of classes that enables you to use different relational database management systems interchangeably through a single interface. Covering PDO is outside the scope of this article, but the procedure for using it is similar to most others: connect to the database, execute queries and commands, and close the connection.

PDO uses Data Source Names (DSNs) to connect to various database systems. Each DSN has a prefix that specifies which driver is to be used to establish the connection. The general format of a DSN string is <driver>:<data source><other parameters>. For example, the driver for SQLite 3 is sqlite. To use a database, we must specify the DSN as sqlite:<database file name>. Since there is no concept of users in SQLite, we only need to specify the file-name. For other, server-based systems, you can also specify the username and password to be used for the connection.

Assuming that you store the SQLite database products.sqlite in a subdirectory named _private of the directory where you will store the PHP program, use the following PHP code to open the database:

$db = new PDO("sqlite:_private/products.sqlite");

This causes $db to refer to a connection to the products.sqlite database.

To query the database, you can use the query method of the PDO class, as shown below:

$query = "SELECT name, price FROM products ORDER BY name";
$result = $db->query($query);
foreach ($result as $row) {
    $name = $row['name'];
    $price = $row['price'];
    $html_row = <<<HTML_ROW
    <tr>
        <td>$name</td>
        <td>$price</td>
    </tr>
HTML_ROW;

    echo $html_row;
}

$db = null;        // close database connection

As you may have noticed, to close the database connection, you can simply set the connection variable to null.

To insert or update data, we can use the exec method of the PDO class.

$cmd = "INSERT INTO products(name, price) VALUES('$prod_name', $prod_price)";
if ($db->exec($cmd) != 1)
    $err_msg = "There was an error while trying to enter the product details";

The exec method returns the number of records affected.

The PDO system may throw exceptions of type PDOException. Apart from this, PDO also allows you to create and execute prepared statements, which is useful if you want to execute the same statement repeatedly with varying parameters — but this feature is not used in this article, due to lack of space. You can refer to the PDO documentation, available as part of the PHP documentation, for details.

The complete source of the program example.php follows:

<html>
  <head>
    <title>Products</title>
  </head>

<?php
$err_msg = "";	// error message, if any

try {
  $db = new PDO("sqlite:_private/products.sqlite");

  if (isset($_POST['submit'])) {
    $prod_name = $_POST['prod_name'];
    $prod_price = $_POST['prod_price'];
    $cmd = "INSERT INTO products(name, price) VALUES('$prod_name', $prod_price)";
    if ($db->exec($cmd) != 1)
      $err_msg = "There was an error while trying to enter the product details";
  }
}
catch (PDOException $e) {
  $err_msg = $e->getMessage();
}
?>

<body>

<?php
if (strlen($err_msg) > 0) {
  echo "<div>$err_msg</div>";
  $err_msg = "";
}
?>

<h1>Products</h1>
<table>
  <tr>
    <th>Name</th>
    <th>Price</th>
  </tr>

<?php
try {
  $query = "SELECT name, price FROM products ORDER BY name";
  $result = $db->query($query);
  foreach ($result as $row) {
    $name = $row['name'];
    $price = $row['price'];
    $html_row = <<<HTML_ROW
  <tr>
    <td>$name</td>
    <td>$price</td>
  </tr>
HTML_ROW;
  echo $html_row;
}

  $db = null;		// close database connection
}
catch (PDOException $e) {
  $err_msg = $e->getMessage();
}
?>

</table>

<br />

<?php
if (strlen($err_msg) > 0) echo "<div>$err_msg</div><br />";
?>

<div>
<h5>Enter a new product</h5>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<table>
  <tr>
    <td><label for="prod_name">Name: </label></td>
    <td><input id="prod_name" name="prod_name" type="text" /></td>
  </tr>
  <tr>
    <td><label for="prod_price">Price: </label></td>
    <td><input id="prod_price" name="prod_price" type="text" /></td>
  </tr>
  <tr>
    <td colspan="2"><input name="submit" type="submit" value="Enter" /></td>
  </tr>
</table>
</form>
</div>
</body>
</html>

Store the products.sqlite database file in a subdirectory named _private of the directory where you save this program, then run the program. The program is simplistic; there is no proper error handling, and it does not prevent you from erroneously adding the same product repeatedly by refreshing the page after adding a product — but it is only meant to illustrate how to work with SQLite.

Where next?

This concludes our tour of Lighttpd and SQLite — but this was just an introduction! You can explore this combo further, to discover more uses for SQLite, and to enhance the performance and modularisation of your PHP applications. SQLite also provides a C interface, which you can utilise to enjoy other benefits of SQLite apart from Web applications.