Database Abstraction, Part 1: PHP


DA_cylinder_phpYou’ve made it past the introduction. Now we can show you how to use the APP(ideas) Database Abstraction layer.

Download + Install

The first thing to do is to get the source code. It is available to get (and use) for free. Here is a link:

https://github.com/appideasDOTcom/DatabaseAbstraction

You can clone it, fork it or download a ZIP file. Whatever works best for you.

  1. After getting the files, copy or move the contents of the “php” directory into a working directory of your choosing.
  2. Open the setup.php file and change the last component of include path value (the part after the last colon on line 21) to point to the “classes” directory of your working copy.
  3. Setup a new database on the server you will be using. The easiest method is probably going to be through phpMyAdmin for MySQL, phpPgAdmin or pgAdmin for PostgreSQL or dbca for Oracle.
  4. Open the file classes/base/AiCommon.php and modify the following variable values to match your setup: $mDbHost, $mDbName, $mDbUser, $mDbPass, $mAutoDbMethod. Each of those variables has a short description, so it should be fairly obvious how to configure them for your installation.
  5. Find the “initialize_xxx.sql” file that is appropriate for your system, and copy and paste the SQL code found therein into your SQL client and execute the script. This is not strictly necessary for the abstraction system to work, but it will make schema update management possible.
  6. Optional: From the command line, change (cd) into the “updates” directory and run: ./run.php This is only necessary if you wish to run the examples or unit tests.
  7. If your php executable is somewhere other than /usr/local/bin: In order to run some of the helper scripts, you will need to modify the path to PHP in docs/run.php, test/run.php, examples/run.php and updates/run.php

You should be setup and ready to go!

Source Code + File Layout

It would be a good idea to first familiarize yourself with the contents of what you have downloaded. Here is a brief explanation of what you will find:

  • root: The setup.php file in the root directory contains the include path for PHP to search. This is necessary since some scripts execute from directories other than those in which the classes it calls reside.
  • classes: This directory contains the code that is used by the database abstraction. To use this system, you only need AiCommon.php, AiDb.php and the connector file (AiMysql.php, AiPgsql.php or AiOracle.php) that is appropriate for your configuration from the “base” directory, and the AiUtil.php file from the “util” directory. The system will work without any other files.
  • docs: This directory contains API documentation for the database abstraction code. Open docs/phpdoc/index.html in any web browser to view the documentation. If you have phpDocumentor installed on your system, you can generate documentation from your own source code by running ./run.php from the command line.
  • examples: We have a few contrived examples of queries that you can run by issuing the command ./run.php from the command line.
  • resources: This directory contains the initialization scripts for each of the supported DBMS’ and a copy of the LGPL license that covers this source code.
  • test: The unit tests that we have written for the database abstraction layer are contained in the php files of this directory. You can view code coverage reports from those tests by opening test/reports/index.html in a web browser. Read below for information about running the tests against your own database system.
  • updates: This directory contains the files needed to execute schema upgrades. More information about that is given below.

One technical note about this system is that the MySQL implementation that is provided uses PHP’s mysqli libraries. That is the method preferred by the PHP team, so that’s the one we have used. If your PHP installation doesn’t include mysqli support, you will need to write a new version of the AiMysql class, but the other methods are considered to be insecure by the folks who make PHP, and using them is not recommended. Rewriting that class to use native MySQL libraries should be trivial.

Before we get into specific examples, let’s start with a very high level overview of what using this code can do for you as the foundation of your data retrieval and management system. This is a very brief and simplistic look at the capabilities of this code.

What It Can Do

  1. It manages connections across your application to help reduce resource utilization, eliminate race conditions, etc.
  2. It takes you one step back from your specific database implementation to give you:
    1. Easier code to read and write. (Saves time and effort)
    2. Greatly improved portability across database servers and server environments.
  3. Although it abstracts database operations, the code is small and easy to understand (not to mention well documented and tested), so it leaves little room for guessing as to how it operates.
  4. It provides easy access to often used features that sometimes take many lines of code for every use. It is easy to extend and expand to suit your specific needs.
  5. It provides a contract by which a database implementation must abide, so changing your code to support a new database system requires adding only a single class file that implements the contract.
  6. It performs and parses queries in a database-agnostic and consistent manner (equalizing boolean values and timestamps across database implementations, for example)
  7. It allows you to easily control the transactional behavior of your system (if your database supports transactions).
  8. It provides an easy to use and manage schema update system, which is often a thorn in the side of data-driven application developers.

Let’s Get To It

As a first introduction, I will show you the examples script. In order for the examples to work, you must upgrade your schema to version 1. To do so, change (cd) into the updates directory on the command line and run this command:

./run.php upgrade

Assuming that completed without any errors, change into the examples directory (cd ../examples) and issue this command:

./run.php

You will be given a list of options. After choosing one of the options, you will see the results of the command, and also the line number of the run.php script from which the query was run. You can look into that file to see how the queries were coded, but the code in that file is a little more complex than what is needed for every day use (largely due to the fact that the examples have to run for all of the supported database systems), so here are some simple utilization examples.

Any place you need to run a query, you will need to include/require the AiCommon class, and get an instance of it. Any script that uses this database abstraction should include/require setup.php so that all of your classes/scripts know where to find the appropriate classes. If this is a web-based app, you can alternately set the include_path via htaccess (see here) or (here) or (here).

Get an instance of the AiCommon class

1
2
3
require_once( "../setup.php" );
require_once( "base/AiCommon.php" );
$common = new AiCommon();

The examples below all use the test_table from the database schema update, version 1. Even if you don’t run that update, take a look at its creation SQL if any of these queries do not make sense. One thing that I would like to point out: “id” is the name of the field used for the unique ID of that table—if your tables use some surrogate key field other than “id” change that string in the queries below.

Insert an empty record into a table and get its ID

1
$id = $common->mDb->insertBlank( "test_table", "id" );

The reason we have a method for inserting a blank record including only its own ID will become more apparent in our next blog series about our Schema Adapter, but for now, I hope that it is sufficient to say that well designed and written code will include a nearly blank “add” function and a more fully-formed “modify” function since “add” and “modify” database methods are typically about 90% the same code.

Create a blank/empty record, then populate it with data

1
2
3
4
5
6
7
$id = $common->mDb->insertBlank( "test_table", "id" );
$updateSql = "
UPDATE	test_table
SET 	int_field = " . $intToSave . ",
	str_field = '" . $common->mDb->escapeString( $stringToSave ) . "'
WHERE	id = " . $id;
$common->mDb->query( $updateSql, __FILE__, __LINE__ );

Before moving on, it is important to note the call to the escapeString() method. It is a good idea to run all strings through this method. Doing so is not forced by the query() method, but failing to do so on a string that may contain apostrophes will cause your database system to error on queries. We enforce the use of escapeString() in the query() method in strongly typed languages, but doing so in in a loosely typed language adds overhead that can equal a significant amount of extra coding and unnecessary resource utilization. All that to say—be sure to escape your strings!

Also note: We commonly use the PHP macros __FILE__ and __LINE__ as inputs to methods so that returned errors can include some information to help us track down problems more quickly.

If you are on a particularly busy database system and your system supports transactions, you can avoid race conditions by wrapping those commands in a transaction like this:

Wrap a query in a transaction

1
2
3
4
5
6
7
8
9
$common->mDb->beginTransaction();
$id = $common->mDb->insertBlank( "test_table", "id" );
$updateSql = "
UPDATE	test_table
SET 	int_field = " . $intToSave . ",
	str_field = '" . $common->mDb->escapeString( $stringToSave ) . "'
WHERE	id = " . $id;
$common->mDb->query( $updateSql, __FILE__, __LINE__ );
$common->mDb->endTransaction();

Oracle users have an extra burden on large string fields in that they are CLOB objects rather than strings.

Insert an Oracle CLOB field (see retrieving a CLOB below)

1
2
$id = $common->mDb->insertBlank( "test_table", "id" );
$common->mDb->updateLob( "test_table", "id", $id, "clob_field", $stringToSave, true );

Insert a PHP boolean value into a database-specific field (int for MySQL and Oracle, bool for PostgreSQL)

1
2
3
4
5
6
7
$id = $common->mDb->insertBlank( "test_table", "id" );
$updateSql = "
UPDATE	test_table
SET 	int_field = " . $intToSave . ",
	bool_field = " . $common->mDb->fixDbBoolean( $boolToSave ) . "
WHERE	id = " . $id;
$common->mDb->query( $updateSql, __FILE__, __LINE__ );

What good is a database system without being able to fetch records?

Fetch records from a table by indexed array

1
2
3
4
5
6
7
8
9
10
11
$sql = "
SELECT	id, int_field, str_field
FROM 	test_table
ORDER BY id";
$result = $common->mDb->query( $updateSql, __FILE__, __LINE__ );
while( $row = $common->mDb->fetchRow( $result ) )
{
	$id = $row[0];
	$int = $row[1];
	$str = $row[2];
}

Some coders prefer addressing elements by associative array, so you can also get values out of the database by field name.

Fetch records from a table by associative array

1
2
3
4
5
6
7
8
9
10
11
$sql = "
SELECT	id, int_field, str_field
FROM 	test_table
ORDER BY id";
$result = $common->mDb->query( $updateSql, __FILE__, __LINE__ );
while( $row = $common->mDb->fetchArray( $result ) )
{
	$id = $row['id'];
	$int = $row['int_field'];
	$str = $row['str_field'];
}

Fetch rows, including a boolean value converted to PHP native boolean

1
2
3
4
5
6
7
8
9
10
11
12
$sql = "
SELECT	id, int_field, str_field, bool_field
FROM 	test_table
ORDER BY id";
$result = $common->mDb->query( $updateSql, __FILE__, __LINE__ );
while( $row = $common->mDb->fetchArray( $result ) )
{
	$id = $row['id'];
	$int = $row['int_field'];
	$str = $row['str_field'];
	$bool = $common->mDb->fixBoolean( $row['bool_field'] );
}

Fetch rows, including an Oracle CLOB

1
2
3
4
5
6
7
8
9
10
11
12
$sql = "
SELECT	id, int_field, str_field, clob_field
FROM 	test_table
ORDER BY id";
$result = $common->mDb->query( $updateSql, __FILE__, __LINE__ );
while( $row = $common->mDb->fetchArray( $result ) )
{
	$id = $row['id'];
	$int = $row['int_field'];
	$str = $row['str_field'];
	$clob = $common->mDb->clobToString( $row['clob_field'] );
}

If you don’t trust your input source (and web-based applications never should), you can guard against injection attacks.

Strip semicolons from possibly untrusted input

1
2
3
4
5
6
$updateSql = "
UPDATE	test_table
SET 	int_field = " . $intToSave . ",
	str_field = '" . $common->mDb->blockInjection( $common->mDb->escapeString( $stringToSave ) ) . "'
WHERE	id = " . $id;
$common->mDb->query( $updateSql, __FILE__, __LINE__ );

The blockInjection() method is likely to throw errors (or more likely to result in truncated input) if someone is attempting an attack, but that’s better than falling victim to an attack!

Get a field name from a query

1
2
3
4
5
6
7
8
9
$sql = "SELECT * FROM test_table";
$result = $common->mDb->query( $sql, __FILE__, __LINE__ );
while( $row = $common->mDb->fetchRow( $result ) )
{
	foreach( $row as $index => $value )
	{
		echo "Field named '" . $common->mDb->fieldName( $result, $index ) . "' has a value of " . $value . "\n";
	}
}

Get the number of rows from the result set

1
2
3
$sql = "SELECT * FROM test_table";
$result = $common->mDb->query( $sql, __FILE__, __LINE__ );
echo "There are " . $common->mDb->numRows( $result ) . " records in your query.\n";

This demonstrates how easy it is to perform the most common function of a database system—perform queries. But there are a few other nifty tricks that this system can do for you too.

Schema Update system

If you develop for database systems, I don’t have to tell you how much of a pain it is to keep schema updates in order. This abstraction greatly simplifies the process for you. In the updates/data directory, there are subdirectories for each of the supported systems. Each of those subdirectories contains (by default) a single file called 1.php. This is the file for a database schema update to version 1. If you want to create a version 2, just copy this file and call it “2.php” and change its contents as such:

  1. Change the class name from “DbUpdater1” to the new version number. “DbUodater2” for version 2, for example.
  2. Add to the array in the upgrade() method any SQL that needs to run to perform the upgrade. The array may contain multiple queries (see oracle/1.php as an example).
  3. Add to the array in the downgrade() method any SQL that needs to be run to “undo” the changes of an upgrade. Again, multiple SQL statements can be included as separate array elements (see oracle/1.php as an example). If a downgrade is inappropriate, make the downgrade() method return an empty array.
  4. On the command line, change (cd) into the updates directory and issue this command:
./run.php upgrade

That’s all there is to it. Your schema will be updated to the latest version available. If there are multiple upgrades to run, they will all run in sequence. To downgrade your schema, change (cd) into the updates directory and issue this command:

./run.php downgrade

The downgrade system will only perform one version downgrade at a time, so if you need to downgrade multiple schema versions, keep issuing the downgrade command until you are at the desired version number.

Running Tests

We have unit tested all of the functionality that doesn’t cause an execution halt, and we have manually tested those hard stops. The test cases that we have written are in the test/reports directory, beginning with index.html. Open that file in any web browser to see our code coverage reports. The test cases we have written are the PHP files in the test directory. To run unit tests against your own system, you must have PHPUnit and xdebug installed and configured. If you do, you can generate tests by following these steps:

  1. Open test/testAiCommon.php and remove from the $dbTypes array any database types not supported by your system. In most cases, you should only have one database system defined here.
  2. On the command line, change (cd) into the test directory and run this command:
./run.php

After doing so, your test results will appear on the command line and code coverage reports will be in test/reports.

Generating Documentation

We are firm believers in self-documenting code, and have produced full API documentation of the classes used by the database abstraction layer. You can view the documentation from the docs/phpdoc directory by opening the index.html file in any web browser. If you have phpDocumentor installed on your system, you can generate your own documentation set based on any modifications you have made to the code by:

  1. On the command line, change (cd) into the docs directory and run this command:
./run.php

After doing that, new documentation will be made in the docs/phpdoc directory.

Further Thoughts

There is more to writing a great data-driven application than abstracting the database layer. However, this is the first, and probably most important step in getting a firm foundation.

If it is certain/likely that your application will need to run on multiple database systems, you probably want to consider abstracting the the data acquisition even one step further and create a class file that contains queries that are likely to change for different database systems. Having to scour your code for query errors really sucks! Although having complete unit tests will help you find these errors quickly. I’ll give one (rather silly) example using Oracle simple math. I know for a fact that this query is specific to Oracle, so it will break on other systems:

1
2
3
$inputA = 3; // This would be some input from the outside world
$inputB = 5;
$common->mDb->query( "SELECT " . $inputA . " + " . $inputB . " FROM DUAL", __FILE__, __LINE__ );

Rather than have an Oracle-specific query like this inline in our code, it would be a good idea to abstract that query to another class so that we only have one file to check/change if our database system ever needs to change. For example, we could change these lines in AiCommon.php:

1
2
require_once( "base/AiOracle.php" );
$this->mDb = new AiOracle( $this->mDbHost, $this->mDbUser, $this->mDbPass, $this->mDbName );

To this:

1
2
require_once( "base/AiOracleExtended.php" );
$this->mDb = new AiOracleExtended( $this->mDbHost, $this->mDbUser, $this->mDbPass, $this->mDbName );

And then have this definition for a new class:

1
class AiOracleExtended extends AiOracle

Add this constructor:

1
2
3
4
function __construct( $dbHost, $dbUser, $dbPass, $dbName )
{
	parent::__construct( $dbHost, $dbUser, $dbPass, $dbName );
}

And this method:

1
2
3
4
function addAandB( $inputA, $inputB )
{
	return "SELECT " . $inputA . " + " . $inputB . " FROM DUAL";
}

And then our query turns into this:

1
$common->mDb->query( $common->mDb->addAandB( $inputA, $inputB ), __FILE__, __LINE__ );

Now if our database system changes, say, to PostgreSQL, instead of having to pour through our code to find possible Oracle-specific queries that now need to be updated, we just need to write one class: Extend the AiPgsql class in the same way we extended the AiOracle class and add this method:

1
2
3
4
function addAandB( $inputA, $inputB )
{
	return "SELECT " . $inputA . " + " . $inputB ;
}

Our functional code, therefore, does not need to change.

This is repeated from the introduction post, in case you didn’t read it there: Another thing that I would like to point out is that this database abstraction lacks something that is present in most of the others that exist in PHP, and that is a “save” wrapper to the query method. It is easier to create an array of key/value pairs and send them into a method for saving in the database than it is to provide a query in SQL, but in the context of a complete application, the database abstraction layer is not the right place to provide that functionality. When you consider your application in its entirety, the objects that represent and implement instances of your data are the correct place to include “save” functionality because it is there that you can include logic for such things as input and sanity checking. Unlike other PHP-based database abstraction layers, our approach to database abstraction is not that your entire application is the data, it is rather that having an easy and stable means of accessing your data is the most important piece of the foundation of a complete application. Your data is your data, and your representation and assignment of meaning to that data should be separate (and separable from) the database abstraction. This is one of the several sources of unnecessary bloat in PHP’s existing database abstraction layers, and our strong opinion is that including such functionality at the data layer is a mistake. It is opposed to the very foundation of the Model-View-Controller method of Object Oriented Programming. You will see a much better approach to “save” functionality when we get to our follow-up series about Schema Adapters.

That may seem like quite a bit to take in, but we cannot overstate the importance of having a good data management layer to any database-driven web site or application. We have provided a lot of information here, and that may seem overwhelming, but it really is simple to use. The best way to learn it is to get your hands dirty and get to coding.

If you find errors or have questions, let us know. You may leave a comment, although it may be better to use our general discussion forum, which is here.

Links to posts in this series:

  1. Introduction
  2. Part 1: PHP (You’re there now)
  3. Part 2: iOS
  4. Part 3: Android (Coming soon)
  5. Follow-up series: Schema Adapter (Coming soon-ish)