Performance Zone is brought to you in partnership with:

Leigh has been in the technology industry for over 15 years, writing marketing and technical documentation for Sun Microsystems, Wells Fargo, and more. She currently works at New Relic as a Marketing Manager. Leigh is a DZone MVB and is not an employee of DZone and has posted 106 posts at DZone. You can read more from them at their website. View Full User Profile

GladiusDB and Firebird: Databases for Your PHP Apps

08.09.2012
| 5125 views |
  • submit to reddit

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.

// 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(),
    ))
);

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.

<?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>";

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.

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

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:

gsec -user sysdba -password masterkeyGSEC> modify sysdba -pw f1r3b1rDGSEC> quit

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:

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';

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.

<?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++;
    }
}

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.

Published at DZone with permission of Leigh Shevchik, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)