Matthew Setter is a professional technical writer and passionate web application developer. He’s also the founder of Malt Blue, the community for PHP web application development professionals and PHP Cloud Development Casts – learn Cloud Development through the lens of PHP. You can connect with him on Twitter, Facebook, LinkedIn or Google+ anytime.

Welcome to Part 2 of my series on databases to use with PHP that you may not of heard of. In Part 1, we set the scene for the series and started by looking at Berkeley DB, one of the veteran databases in the open source world. We looked at where it came from, its key features and strengths, and what kinds of applications that it’s best used for, finishing up with some simple code sample.

In this next installment, we’ll be looking at two (arguably very contrasting) databases. The first is Gladius DB, which is a flat file database written in pure PHP. The second is Firebird, another database veteran, which has features similar to some of the better-known databases, such as Microsoft SQLServer.

So with that, let’s get going.

Gladius DB

Gladius DB is written purely in PHP, uses flat files to store its data files and is compatible with a subset of SQL92. It doesn’t need any external libraries or drives to get started, and can also be integrated with ADODB.

What’s It Good For?

As we’ll see a bit further below, Gladius DB is good for a variety of application, regardless of whether you want a lightweight and flexible database, one with low resource overhead or if you need to do a bit of embedded programming.

Since it uses flat files, there is the potential to use it in either a shared host or multi-server/cloud environment. You can store the files on the local filesystem, in APC, memcache or something similar, allowing you to give it a good performance boost as well.

How to Use It

First, download the library from its home on sourceforge.net. Then makes sure it’s in your PHP include path or add a simple set_include_path statement, as shown below, to ensure its there.

[sourcecode language=”php”]

// Define path to application directory

defined(‘APPLICATION_PATH’)

|| define(‘APPLICATION_PATH’, realpath(dirname(__FILE__)));

// Ensure library/ is on include_path

set_include_path(implode(PATH_SEPARATOR,

array(

realpath(APPLICATION_PATH . ‘/gladius-0.8.1’),

get_include_path(),

))

);

[/sourcecode]

As you can see, we’ve defined a base APPLICATION_PATH to base the include from and then added Gladius afterwards. I’ve set it to gladius-0.8.1, the latest at the time of the writing, but there’s no need to be so specific. Really, that’s all you need to do.

A Simple Example

In keeping with the tradition started in Part 1 of the series, I’ve included a simple example to help you get started using it. First, take a look at the code below and then let’s work through it.

[sourcecode language=”php”]

<?php

error_reporting(E_ALL);

ini_set(‘display_errors’, ‘on’);

define(‘DATABASE_DIRECTORIES’, ‘databases’);

$GLADIUS_DB_ROOT = DATABASE_DIRECTORIES;

// Define path to application directory

defined(‘APPLICATION_PATH’)

|| define(‘APPLICATION_PATH’, realpath(dirname(__FILE__)));

// Ensure library/ is on include_path

set_include_path(implode(PATH_SEPARATOR,

array(

realpath(APPLICATION_PATH . ‘/gladius-0.8.1’),

get_include_path(),

))

);

include (‘gladius-0.8.1/gladius.php’);

$G = new Gladius();

$G->SetDBRoot(DATABASE_DIRECTORIES);

// call the database selection method

$G->SelectDB(‘myshop’);

$sql = "INSERT INTO phonebook VALUES(‘Gabriele’, ‘D,Annunzio’, ‘1000-0000’)";

$G->Query($sql);

// show the result

echo $G->errstr;

$query = ‘SELECT * FROM phonebook’;

$rs = $G->Query($query);

// execute a SELECT statement and fetch all the rows

$rsa = $rs->GetArray();

print "<table cellpadding=5 cellspacing=2 border=1>";

// iterate through all rows

foreach ($rsa as $row) {

$row = (object)$row;

printf("<tr><td>Name: %s</td><td>Surname: %s</td><td>Phone: %s</td></tr>",

$row->name,

$row->surname,

$row->phone

);

}

print "</table>";

[/sourcecode]

We first set a constant, DATABASE_DIRECTORIES, to the directory in which Gladius will store its database files, then we use that to set the Gladius constant, GLADIUS_DB_ROOT.

After that, we include the core Gladius file, instantiate a Gladius object and specify the database we’re going to use. Next, we insert a record into the table phonebook and check that the record was inserted correctly by looking at the value assigned to errstr after the query’s executed. If anything has gone wrong, we’ll see it output here.

Now that we have a record in the database, we retrieve a copy of it by running a SELECT * on the phonebook table, then we iterate through the results in the foreach loop. After that, we output the information we retrieved in a simple HTML table structure so that’s it’s easy to read.

As you can see, Gladius is pretty easy to work with – just as easy or easier to use than SQLite. Since its file-based and written in pure PHP, there’s very little that you need to do to get is up and running, and almost nothing is required to debug it.

When you are deploying your application, you should make sure that the database location has the appropriate read/write permissions so that the web server user or commandline user (if its being used to run shell scripts,) can manage the files.

Optimizations and Customizations

But what if you want to make it faster or there are some filesystem limitations? What are you going to do if you’re unable to save the filesystem? There’s always another location to store the files, such as RAM, made easily accessible via TMPFS or RAMFS.

Using TMPFS, you can allocate a section of physical RAM or memory to be used as a filesystem partition. The Geek Stuff has a superb simple example of how to set one up. I’ll leave it to the to explain the advantages and disadvantages of using one, but here’s the skinny on how to implement it.

[sourcecode language=”php”]

mkdir -p /mnt/tmp

mount -t tmpfs -o size=20m tmpfs /mnt/tmp

[/sourcecode]

With this done, you can tell Gladius to store the database files in the TMPFS partition and watch as you (potentially) get an order of magnitude speed increase. Alternatively, you could write a custom PHP stream output wrapper to store the files in either APC or memcache.

So, though Gladius starts out seeming quite simplistic, you can see the power as it quickly becomes apparent.

Firebird

According to Wikipedia, Firebird originated from Borland’s open source edition of InterBase in 2000. Firebird is an open source relational database management system replete with the features listed below and many more. It complies with a large percentage of the SQL:2003 standard. Firebird is available for distribution under a variant of the Mozilla Public License, called the Initial Developer’s Public License (IDPL).

Features

Just because you may not have heard of it, doesn’t mean it’s not worth your attention. Let’s look at a select set of features Firebird provides and see just how good it is.

• OS Support: Available on all major platforms including Linux, Windows, Mac, FreeBSD and HP-US

• Multi-Generational Architecture: Multiple versions of records are kept in the database as long as at least one transaction needs them

• Logging and Monitoring Real time monitoring, SQL debugging and a clear audit trail

• Extensive Security: Whether you’re on a *NIX or Windows server, you’re covered

• Wide Developer Support: There are bindings for all the major languages, including PHP, Java, Python, Rails, and MS Visual Studio

• High Performance: It’s multi-CPU and multi-core SMP ready

• High Scalability: It supports databases up to 20TB in size

What’s It Good For?

Well, to say the least, Firebird is suitable for just about anything that most of the other databases are. Now no, it doesn’t have exactly the same feature set, but if you look at this rather detailed comparison you’ll get a good idea of how they all stack up. (You can also look at these slides on slideshare.net.)

In a nutshell, here are some great uses:

• You’re doing OLTP or OLAP work

• You’re doing work requiring advanced features such as Triggers, Stored Procedures and User Defined Functions (UDFs)

• You need to interact with database events

• You’re doing standard or embedded database work

• You’re building apps that need to scale over time

• You have developers using a range of languages and platforms

How To Use It

Depending on your platform, installing Firebird is fairly straight forward. Go to the Firebird download page and grab a copy of the installation kit for your platform. Or if you’re using a Linux distribution, you should be able to install it from one of the package managers, such as APT or RPM.

After you’ve installed it, the database should already be running. You can see this in either the Windows service manager or by using a shell command such as netstat or the following command: top -b -n1 | grep ib

Then change the default username and password to something you prefer with the following commands:

[sourcecode language=”php”]

gsec -user sysdba -password masterkey

GSEC> modify sysdba -pw f1r3b1rD

GSEC> quit

[/sourcecode]

This will change the default password to f1r3b1rD. But set this as it suits you. Firebird does not come with GUI tools, so you either has to use the commandline ISQL tool or a third-party tool such as Flame Robin.

Install Example Database (Linux)

By default, an example HR/employee database is available when you install Firebird. But you need to do a few things to get it ready to use. Following the follow commands will do that for us:

[sourcecode language=”php”]

cd /usr/share/doc/firebird2.5-examples/examples/empbuild/

# set the ownership correctly

sudo chown firebird.firebird employee.fdb

# put a copy of the database in the firebird data directory

sudo cp -rv employee.fdb /var/lib/firebird/2.5/data/

# connect to it to ensure that it’s working

isql-fb "/var/lib/firebird/2.5/data/employee.fdb" -u ‘SYSDBA’ -p ‘masterkey’;

[/sourcecode]

With that, you’ve got a sample database in place and you’re ready to go. Consult the official installation documentation for further information.

A Simple Example

The code below is a simple, yet effective, example for interacting with Firebase using PHP. Have a look and we’ll go through it after that.

[sourcecode language=”php”]

<?php

$db = ‘localhost:/var/lib/firebird/2.5/data/employee.fdb’;

$dbuser = ‘SYSDBA’;

$dbpass = ‘masterkey’;

$dbcharset = ‘UTF8’;

$dbbuffers = 20;

if (($dbh = ibase_connect($db, $dbuser, $dbpass, $dbcharset, $dbbuffers)) == FALSE) {

print "unable to connect to database";

} else {

print "connected to database";

$stmt = "SELECT * FROM EMPLOYEE";

$result = ibase_query($dbh, $stmt);

$count = 0;

echo "<br />";

while ($row = ibase_fetch_assoc($result)) {

$row = (object)$row;

printf ("Name: %s <br />", $row->FIRST_NAME . ‘ ‘ . $row->LAST_NAME);

$count++;

}

}

[/sourcecode]

In the example above, we’ve connected to the sample employee database we installed earlier. We set a number of variables for use in the code and used the ibase functions in PHP to connect to the database.

After that, we ran a simple SELECT * query on the employee table and iterated over the results, which were retrieved as an associative array. As you can see, all the column names are referred to in uppercase. But that aside, it’s arguably as simple as any of the other databases to interact with.

If you’re a PDO user (and I really encourage you to be if you’re not,) there’s an experimental extension for Firebird/Interbase available. I haven’t tested it myself, so I can’t say what it’s like, but feel free to try it out and let us know your feedback in the comments.

Winding Up

So as you can see, there’s an increasing array of options available outside of the ‘mainstream’ you can use. We now have a database library, a flat-file PHP database and an open source relational database near on par with SQLServer and Oracle. The choices just keep growing with every part of the series.

Have you used either Gladius or Firebird? Tell us about your experiences in the comments.