Open Source Software Technical Articles

Want the Best of the Wazi Blogs Delivered Directly to your Inbox?

Subscribe to Wazi by Email

Your email:

Connect with Us!

Current Articles | RSS Feed RSS Feed

An introduction to SQLite for developers and sysadmins

  
  
  

When they need a relational database, software developers and system administrators often choose MySQL or PostgreSQL. For a lighter and simpler solution, however, developers should consider SQLite. It's used in so many open source and commercial products that SQLite is considered the most deployed SQL database engine in the world. This article will help you get started with SQLite and use it in your programs and scripts.

Unlike most SQL databases, SQLite has no separate server process; the database management system is contained in a small C library. A complete database with multiple tables, indices, triggers, and views lives in just a single file. SQLite requires zero configuration and setup, and also little to no maintenance. Even database backups are simple file copies.

System requirements are minimal too; low-powered devices such as smartphones, video games, and portable media players can store large data libraries in SQLite databases. Despite its small footprint the database implements most of the SQL-92 standard, so if you've worked with other SQL databases, you should be able to use SQLite right away. Its simplicity also makes it a good learning tool for SQL novices.

The source code for SQLite is licensed in the public domain and can be used for any purpose. The project maintains a high level of quality and reliability. The biggest part of the source code is devoted to testing and verification. Data integrity is guaranteed even in system or power failure situations since SQLite uses ACID transactions.

For this article we'll use SQLite3, the recommended version for new deployments; SQLite2 is still available for compatibility with old applications. Packages for SQLite are available for all GNU/Linux distributions, BSDs, and Windows, and it is preinstalled in CentOS, Mac OS X, and OpenBSD (since version 5.2). If it's not installed in your favorite distribution, installation from source is trivial. During installation SQLite can be optimized for resource-constrained environments by omitting features.

SQLite offers bindings for most programming languages. The library can be embedded directly into C and C++ programs. Python has been bundled with SQLite since version 2.5, and PHP comes with both SQLite2 and SQLite3 built-in. For Perl you need to install the DBI and DBD::SQLite CPAN modules. A SQLite gem is available for Ruby. Developers should read SQLite's Quick Start guide to get a better understanding of the software's programming interface (API).

Since there is no server process to start, you can create and use a database directly from the command line once you've installed the software by running the command sqlite3 with a filename for the database as an argument:

$ sqlite3 servers.sqlite3
SQLite version 3.7.14.1 2012-10-04 19:37:12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

If the file does not exist, it is created. You can use any file extension for your database; I prefer .sqlite3, but .db is also common. You can enter SQL statements at the sqlite> prompt, as well as meta-commands, which are used to perform administrative operations and adjust database settings. Type .help for a complete list of available meta-commands.

Let's start looking at how SQLite works by creating a small table with five columns to store information about the servers in our network:

sqlite> CREATE TABLE Servers( Hostname TEXT PRIMARY KEY, IPAddress TEXT, Role TEXT, Status TEXT, Timestamp INT );
sqlite> .tables
Servers

That's a fairly standard CREATE SQL statement, terminated with semicolon. SQLite supports several datatypes: NULL for empty, INTEGER and REAL for signed integers and floating point values respectively, TEXT for text strings, and BLOB for binary files. Unlike other SQL databases, SQLite uses dynamic typing – the datatype of a value is associated with the value itself, not with its container. That means you can enter any value in practically any column, regardless of the predefined type.

The .tables meta-command lists the names of available tables in our database. Meta-commands do not need any termination characters.

You can now INSERT some records in the table or, better yet, automate this process with a shell script:

#!/bin/sh

# WARNING: this script is prone to SQL injections.
# you should always validate user input in real-world
# scripts and applications.

# our database
_DB="servers.sqlite3"

# read hostname from stdin
printf "Hostname: "
read _HOSTNAME

# read ip address from stdin
printf "${_HOSTNAME}'s IP Address: "
read _IP

# read server's role from stdin
printf "${_HOSTNAME}'s role: "
read _ROLE

# INSERT data in database
sqlite3 $_DB "INSERT INTO Servers(Hostname, IPAddress, Role)
              VALUES('${_HOSTNAME}', '${_IP}', '${_ROLE}')"

Run this script twice to populate the database with a couple of records:

$ ./server_add.sh
Hostname: mx1
mx1's IP Address: 1.2.3.4
mx1's role: Mail

$ ./server_add.sh
Hostname: www1
www1's IP Address: 2.3.4.5
www1's role: Web

The sqlite3 command can run a single SQL statement on our database by adding it as a third argument after the filename. You can omit terminating the statement with a semicolon when passing it to sqlite3 non-interactively:

$ sqlite3 servers.sqlite3 "SELECT * FROM Servers"
mx1|1.2.3.4|Mail||
www1|2.3.4.5|Web||

You can create shell scripts in this fashion to automate various system administration tasks or even create simple programs and prototypes.

The next example uses the database we created and extends the functionality of the previous script. Run periodically from cron, this Perl script checks our network servers and updates the columns Status and Timestamp of each server in the database.

#!/usr/bin/env perl

use strict;
use warnings;
use DBI;

# our database
my $db = "servers.sqlite3";

# connect to the database
my $dbh = DBI->connect(
  "DBI:SQLite:${db}",
  undef, undef,
  { RaiseError => 1 }
) or die "Can't connect to database: $DBI::errstr";

# server check function
sub server_check {
  my ($hostname, $ip, $role) = @_;

  print "Add check logic based on hostname, ip and server role\n";
  return 1;
}

# fetch all data from the database as an array ref
my $servers = $dbh->selectall_arrayref("SELECT * FROM Servers");

# parse the data and update the database
foreach my $server (@$servers) {
  # save column values for each row as local variables
  my ($hostname, $ip, $role, $status, $timestamp) = @$server;

  # run server check function for current row
  my $rc = server_check($hostname, $ip, $role);

  # evaluate the result and update status
  if($rc > 0) {
    $status = 'good';
  } else {
    $status = 'bad';
  }

  # update the database with server status and the current
  # date (in epoch :)
  my $sth = $dbh->prepare(
    "UPDATE Servers SET Status = ?, Timestamp = ?" );
  $sth->execute($status, time());
}

# disconnect from the database
$dbh->disconnect();

This program lacks the logic for the actual check and always validates true. You can easily add checks in the server_check subroutine with LWP, cURL, or another tool.

To view the table content in a more structured way let's switch to column mode and enable the presentation of table headers:

$ sqlite3 servers.sqlite3
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Servers;
Hostname    IPAddress   Role        Status      Timestamp 
----------  ----------  ----------  ----------  ----------
mx1         1.2.3.4     Mail        good        1354617393
www1        2.3.4.5     Web         good        1354617393

Our next example is a small web application written in PHP using the built-in PDO extension to access the database. PDO operates in a similar way to Perl's DBI module, which we used in the previous example. They both offer database-agnostic interfaces, which means you can switch to a different SQL database in the future with minor code modifications. Served from a PHP-enabled web server, the following program renders our Servers database table as an HTML table.

<?php
  try {
    /* open the database */
    $db = 'servers.sqlite3';
    $dbh = new PDO("sqlite:$db");

    /* fetch the data */
    $servers = $dbh->prepare( "SELECT * FROM Servers" );
    $servers->execute();
  } catch (Exception $e) { die ($e); }
?>
<!DOCTYPE html>
<html>
<head>
  <title>Servers Status</title>
</head>
<body>
  <table>
    <tr>
      <th>Hostname</th>
      <th>IP Address</th>
      <th>Status</th>
      <th>Last Check</th>
    </tr>
<?php 
  /* present database data as a table */
  while ($server = $servers->fetchObject()): ?>
    <tr>
      <td><?php echo $server->Hostname; ?></td>
      <td><?php echo $server->IPAddress; ?></td>
      <td><?php echo $server->Status; ?></td>
      <td><?php echo date('r', $server->Timestamp); ?></td>
    <tr>
<?php
  endwhile;
  
  /* close the database */
  try { $dbh = NULL; } catch(Exception $e) { die ($e); }
?>
  </table>
</body>
</html>

Several GUI and web-based applications are available to help you administer your SQLite databases. My favorite web-based SQL administration tool is Adminer, which supports MySQL, PostgreSQL, Microsoft SQL Server, and Oracle in addition to SQLite.

You can use SQLite in large projects, such as web applications, to save system resources, but you should be aware of its limitations. SQLite supports simultaneous access from multiple processes and offers its own locking mechanism, but traditional client/server database engines, such as PostgreSQL, offer higher level of concurrency. SQLite also lacks user authentication and access controls; it relies solely on filesystem permissions for database access control. Enterprise-level features like database replication are unavailable.

If your project outgrows your current SQLite database or you need to switch to a client/server SQL database, you can convert a SQLite database to a flat text file easily with a command like

$ echo '.dump' | sqlite3 servers.sqlite3 > ServersDB.sql

After editing the output text file and adapting it to a schema compatible with the other server, you can import it in the other database. For example, in MySQL:

$ mysqladmin -u root -p create OurNetwork
$ mysql -u root -p OurNetwork < EditedServersDB.sql

SQLite was designed to be fast, stable, and compact, and it excels at these goals. Feel free to adapt the code examples in this article to experiment with the database and create your own projects.




This work is licensed under a Creative Commons Attribution 3.0 Unported License
Creative Commons License.

Comments

Your T-Sport PRS200 can be rolex replica one of several best-selling designer watches in recent history pertaining to Tissot. It can be common for the strong face (accessible in silver precious metal, orange as well as dark-colored) as well as exact chronograph purpose. Reliable along with easy to get to links choose this enjoy user-friendly in fact it is constructed of merely the optimum resources offered. This is the very reasonably priced breitling replica enjoy which offers brilliance throughout quality along with functionality. T-Sport PRS516 Computerized Chronograph. Which has a 60's build along with design and style that may be in shape for virtually any electric motor game fan, your TSport PRS516 Computerized Chronograph is just about the nearly all exclusive designer watches that will Tissot can give. The planning grew to be common last your patek philippe replica nineteen sixties along with Tissot features managed to renovate this specific enjoy to adjust to present day morning. Their necklace can be eye-catching along with wonderful, presenting a number of pierced divots. Your divots are designed to observe your divots which are common throughout fake breitling prescribing trolley wheels in athletics autos for countless years. A new duplicate of an steering wheel truly graces a corner in the enjoy scenario on an included artistic price. Your TSport PRS516 Computerized Chronographs occur in several 60's hues that will entice different types involving users. The idea fake hublot is often a favourite amid players along with fans whom have to have rate. 
Posted @ Tuesday, September 02, 2014 11:28 PM by swef
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics