Sqlite for beginners

The road to… Sqlite?

I have been developing a web-service over the past year. It’s mostly a hobby and learning experience right now. The service provides a web-site statistics gathering and presentation tool. The original implementation used PHP & MySql which is pretty much the standard combination when it comes to server side scripting with a database back-end. There are numerous tutorials on the web which describe how to develop PHP applications with a MySql database.  About a month ago, I began looking around for a simpler DB that would allow me to easily develop apps without the overhead of the MySql installation and configuration.  What I want is a database that is easy to manage, maintain, backup and will run on a PHP enabled server or on a laptop with another language.

During my search, I found the Sqlite database, which is a relational database that runs “in process”. This means that it is not a server based database like MySql, Postgres, Oracle and many others. An Sqlite database is comprised of a single file. An application accesses the database via an API for the particular language that the application is written in. Language bindings exist for PHP, Java, C, Ruby, Python and many others.

I find Sqlite intriguing because of the simplicity of it’s usage and deployment model. All that is needed to use it in an application is the database file and the library. That’s it, nothing else. There are no heavyweight servers to install, no firewall settings to modify and no complex configurations to manage. Sqlite supports only a subset of the SQL 92 standard whereas most of the major server based relational databases support the full SQL 92 standard.

Sqlite is in the public domain.  According to the license, Sqlite can be used for any purpose including commercially.  To some, this may be a drawback, as they incorrectly think that open source projects lack support.  To the contrary, Sqlite has the support of many very successful companies in software and finance including:  Adobe, Bloomberg and Mozilla (the organization that develops the Firefox browser).

A simple PHP and Sqlite example

Getting Sqlite running with PHP is pretty straight forward.  If you are using a hosting company, many will already have the Sqlite module installed or compiled into their servers PHP implementation.

I use 1and1.com as my hosting provider and have been with them for about 3 years.  There appears to be some confusion on various forums and websites regarding 1and1.com’s support for Sqlite.  Contrary to several forum entries that I have run across, 1and1.com DOES support Sqlite versions 2 and 3 in the Linux hosting packages.  Overall, I have been very happy with the level of features and service provided by 1and1.com.  If you are looking for a good, low cost web-host, then my recommendation would be to go with 1and1.com.

First, you should verify that your PHP implementation has Sqlite included.  To do this simply create a php file called phpinfo.php and upload it to your webhost.  The file should contain the following:

<?php
  phpinfo();
?>

Execute the file by browsing to it in your web-browser.  If Sqlite is installed and available in your PHP instance, you should see numerous entries regarding Sqlite in the information returned by your server.  Once you have validated your Sqlite status with PHP, be sure to delete the phpinfo.php file from your server as it’s availability may provide too much information to nefarious evildoers.

Now that you have determined that your webhost’s PHP implementation supports Sqlite, you can start coding.  This next example is a completely self contained PHP file that performs the following functions:

  1. Creates the Sqlite database
  2. Creates a table on the new database
  3. Inserts several records into the table
  4. Displays all of the records in an HTML table

If you are using 1and1.com as your hosting provider, you should add a .htaccess file to the directory containing the php scripts in order for these examples to work.  The .htaccess file should contain the following 2 lines:

AddType x-mapp-php5 .php
AddHandler x-mapp-php5 .php

<?php
  try
  {
    //create the database.
    //this will generate the database file in the directory in which this script exists.
    //If this file already exists, the database will be opened on this file.
    $database = new SQLiteDatabase('dogsDb.sqlite', 0666, $error);
  }
  catch(Exception $e)
  {
    die($error);
  }

  //Add a new table to the database called Dogs
  $query = 'CREATE TABLE Dogs (Id INTEGER, Breed TEXT, Name TEXT, Age INTEGER)';
  if(!$database->queryExec($query, $error))
  {
    die($error);
  }

  //Insert several Dog records  into the Dog table
  $query = "INSERT INTO Dogs (Id, Breed, Name, Age) VALUES (1, 'Labrador', 'Tank', 2); " .
           "INSERT INTO Dogs (Id, Breed, Name, Age) VALUES (2, 'Husky', 'Glacier', 7); " .
           "INSERT INTO Dogs (Id, Breed, Name, Age) VALUES (3, 'Golden-Doodle', 'Ellie', 4)";
  if(!$database->queryExec($query, $error))
  {
    die($error);
  }

  //Read all of the data from the Dogs table and print it in an HTML table
  $query = "SELECT * FROM Dogs";
  if($result = $database->query($query, SQLITE_BOTH, $error))
  {
    print "<table border=1>";
    print "<tr><td>Id</td><td>Breed</td><td>Name</td><td>Age</td></tr>";
    while($row = $result->fetch())
    {
      print "<tr><td>{$row['Id']}</td><td>{$row['Breed']}</td><td>{$row['Name']}</td><td>{$row['Age']}</td></tr>";
    }
    print "</table>";
  }
  else
  {
    die($error);
  }
?>

Now, simply place this code in a file called dogsqlite.php and upload it to your web server. Then execute the file by opening it in your browser. The file will generate the database, add the table and the records, then display the contents. You should see something like this returned from your server:

Id Breed Name Age
1 Labrador Tank 2
2 Husky Glacier 7
3 Golden-Doodle Ellie 4

There you have it. A simple integration of PHP and Sqlite. We’ll explore Sqlite 3, PDO and Sqlite Manager in the next article.


Leave a Reply

Your email address will not be published. Required fields are marked *

*