Articles / Generating PHP Database Acc…

Generating PHP Database Access Layers

The productivity benefits of the PHP platform are well known. Its easy syntax, latent typing, high level of abstraction, and support for objects simplify and accelerate the production of large applications. In this article, I propose using code generation tools to build even higher-quality and more easily maintained PHP code.

The foundation of any Web application is the database. In a well-factored application, the database is protected by a set of objects contained within the database access layer. On top of this layer is the business object layer, which implements the business rules. During execution, the user interface layer communicates with the business object layer. These three code layers form the structure of a three-tier Web application server. Two-tier servers merge the business logic into either the database layer or the user interface.

In either topology, the database access layer is a focal point for the application because it provides a level of abstraction between the customer view of the data and its implementation within the database. Because of this, the robustness of the application as a whole is partially dependent upon the robustness of the database access layer at the bottom of the technology stack.

What is the best approach to producing a solid database access layer? Code generation. Thankfully, there are a number of tools available to build database layers for PHP automatically. Throughout the remainder of this article, I will concentrate on the use of code generation techniques and tools upon the database access layer solely. However, when applied against the entire code base, these techniques will enhance the reliability and robustness of a Web application tremendously.

Introduction to code generation

Code generation is the technique of using a special program which builds code to match a set of user-defined requirements. This special program is commonly referred to as a code generator. In the case of PHP database access layers, the code generator will read a definition of the database and create PHP files which contain the database access layer code.

There are two basic types of code generators, and it is important to note the difference between them:

Passive generators
generate the code once. Engineers maintain the generated code in the long term. If additional fields are required, they are added manually, including all the necessary code to manipulate the new fields. Should the new code need to be introduced into several different areas of the existing code base, this is done manually as well.
Active generators
build and maintain code in the long term. When additional fields are required, they are added to the input of the generator. The generator is re-executed to produce new database access code, which includes support for the new fields.

I strongly advocate using an active generator to build your code. If you want to use an off-the-shelf generator, make sure it follows the active generation model.

Active generation has strong advantages when compared with writing the same code by hand:

Quality
Code generators use text templates to produce code. The quality of the code that is produced relates directly to the quality of the templates used to produce it. As problems are found and addressed in the database access layer, the templates are modified accordingly. The generator is re-executed to propagate the changes throughout the database access layer code.
Consistency
If the API of the access layer changes, an active generator will make the change consistently across newly-generated code.
Productivity
Inevitably, during the course of development, tables and fields are added, changed, or removed multiple times. Active generation supports re-building the database layer to match a changing schema almost instantly. This is the kind of development agility that is tough to live without, once experienced.

Obviously, there are compelling reasons to use generators to build code, but why should we apply them to the database access layer in particular?

Why code generation for database access?

The primary reason to generate database access layers is to use the quality and consistency benefits of generation to make a strong foundation for your application. The secondary reason is that database access code is particularly amenable to generation.

Let's take the example of an INSERT statement. PHP code for an insert statement using PEAR might look like this:


function add_person($first,$last)
{
  $sql = "insert into names(first,last) values(?,?)";
  $result = $this->db->query($sql,array($last,$first));
  if(DB::isError($result))...;
}

This is pretty simple stuff. The function takes the arguments and marshals them into an SQL statement, then checks to see whether an error occurred. Even so, this small function has an error in the ordering of the arguments, which is often where errors occur in this type of code.

Note the repetitive nature of the code. The field names are repeated in both arguments lists, the SQL statement and the query statement. The structure of the function itself is repeated for every SQL statement to be executed against the database.

Repetitive coding is the nature of database access work. It's easy to mess it up and difficult to excel at it. When you have code that requires clerical work to create and maintain, it is a warning sign that you could, and probably should, be generating the code.

PHP Code Generators

There are a number of generators available for PHP code generation, both commercial and Open Source. Some just build database code; others build entire applications.

phpGen
This is a Free generator which looks at the table definition of a table on a database server and builds code for inserting, updating, and deleting records from the table.
JaneBUILDER
This is a commercial application builder for PHP. Database specifications are provided to JaneBUILDER using GUI tools, and it builds PHP database access layer code to match those specifications.
phpCodeGenie
This is an Open Source generator which builds code and pages for database tables.

This is just a sampling. There are a number of smaller Open Source projects which can be obtained and tweaked to meet requirements, or you can build a generator from scratch, if this is warranted.

Starting out with code generation can be a daunting experience without help. The field is fairly new and undergoing rapid changes. I've included some tips which will help ease your way into making the most of code generation.

Using off-the-shelf generators

Understand the generator.

You should fully understand what the generator requires and what it delivers, particularly the requirements on the form and format of its input and the form its output will take. Determine early in the process whether you can deliver the data in the form the generator requires and whether the templates which generate code can be modified to format the code to meet local coding guidelines. Believe me, time spent vetting the generator is inconsequential in comparison to the time savings that result as the generator builds code for you.

Remember who is boss.

The generator is just a tool. You should be dictating your database design and requirements to the tool, not vice versa. Ideally, you should have a database and API design in mind before you choose the generator, and you should select the generator most suited to implementing that design for you.

Maintain a high level of abstraction in the generator.

A core value of generation is keeping the design separate from the implementation. When a design is codified into SQL and PHP, it is difficult to migrate it to other platforms or newer technologies. The design becomes lost in the implementation minutiae of managing database connections and marshaling data. When a generator is used, the design is maintained in an XML file or some other abstract form which can be reused to generate code for a different platform or technology.

Conclusion

Engineers tout the productivity benefits of generation, but the real advantage comes from how quickly an existing generated code base can be modified to meet changing requirements. Code generation is an invaluable tool whose time has come. Combining code generation with PHP creates a potent mix for agile application development.

Recent comments

28 Jul 2006 08:46 Avatar shannah

Re: *Examples* of code generation, please


> While pointers to some code-generation

> software are great, some examples would

> be good too.

Here is an example using the Dataface API (http://

fas.sfu.ca/dataface).

$student = df_get_record('students', array

('studentid'=>10));

// loads record from students table with id 10

echo "Name: ". $student->strval('name').'

Email: ". $student->strval('email');

$student->setValue('favcolor', 'blue');

// sets student's favorite color to blue

$student->save();

// saves student to database

// How about adding a 'courses' relationship to the students

table

$student->_table->addRelationship('courses',

array(

'student_courses.courseid'=>'courses.courseid',

'student_courses.studentid'=>'$studentid'

)

);

// Note: this relationship could have been defined in an

INI file for better separation of code and config.

$courses = $student->getRelatedRecordObjects('courses');

// gets the courses that this student is enrolled in.

foreach ($courses as $course){

echo "Course Title: ".$course->strval('title')."

Course Description: ".$course->strval('description')."

";

}

This is a simple example, but the rabbit hole goes much

deeper.

28 Jul 2006 08:34 Avatar shannah

Re: Code generators and applications models


> Code generators are time consuming

> applications, but very benefic to create

> demo, we can generate demo code in about

> 5-10 min for any PHP application, of

> course from scratch ! :)

I have found that code generators are great for generating

prototypes or proofs of concept but they are not good for

developing production systems, because it is difficult to

modify and extend the application after the initial code

generation. A good framework with a good data access

model is much preferred to a code generator IMHO.

-Steve Hannah

07 Mar 2006 16:13 Avatar shannah

Re: My php/MySQL Generator Search
I have recently released a project named Dataface ( http://

fas.sfu.ca/dataface ) which seems to fit the description of

what you are looking for. I agree with you that there

doesn't seem to be anything out there that quite does the

trick. That's why I wrote Dataface.

Hope this helps.

> I'm a developer but new to php. For a

> quick personal project, I have spent the

> last couple weeks searching for a free

> or low cost solution for providing a

> generic php/MySQL interface. I've come

> to the conclusion that there currently

> are none that meet my needs. All I want

> is to be able to generate a spreadsheet

> type interface to an existing table, and

> allow updates, inserts, and deletions to

> the records. Thus far I have looked at

> AppGini, Asap, phpGen, phpJaneBuilder,

> phpMyEdit, and Soloman. In all the

> apps that I've looked at they were bug

> ridden, difficult to implement, or were

> lacking in the necessary features. And

> I'm also amazed that some of these

> products are asking for $ but they

> appear to have been designed by a 13

> year old web developer with a "cool

> idea".

>

> Needless to say, I am currently

> disappointed. I can't afford to spend

> time developing the php source for a

> database interface for a

> "hobby" application. If

> anyone is aware of anything that fill

> this needs then please let me know.

22 Nov 2005 02:19 Avatar pbrosset

Re: My php/MySQL Generator Search
Hi there,
have a look at this PHP/MySQL code generator: MyDBO
http://project.zoe.co.nz/patrick/mydbo/
It is a free generator anyone may use personally or commercially that can create a set of class files corresponding to your database structure.
The generation is done via a wizard that make it possible to select tables, set foreign keys ...
Moreover, code is generated from templates, which make it possible to create your own templates as well.

08 Nov 2005 07:08 Avatar beltundsund

Re: Php Object Generator
Thanx, this one looks like a nice service

I'll try it, writing every php class from scratch could really be annoying.

>

> % I recently wrote a simple but useful

> Php

> % Object Generator.

> %

> % Try it out.

>

>

> sorry. forgot to give the actual url:

> http://

> www.phpobjectgenerator.com.

>

> -joel

Screenshot

Project Spotlight

Kigo Video Converter Ultimate for Mac

A tool for converting and editing videos.

Screenshot

Project Spotlight

Kid3

An efficient tagger for MP3, Ogg/Vorbis, and FLAC files.