Generating PHP Database Access Layers
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.
PEAR DB_DataObjects library
This library does essentially the same thing as you've described in this article. I've used it and although it's not perfect (i.e. bugs exist), it is a step in the right direction.
This is clearly the right way to develop web applications.
Code generators and applications models
Thank you for this great article, in our company we have worked a lot on code generators, and specially PHP application models, and this have a very high influence on the importance of any code generator we made.
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 ! :) and today we can add also translation for many langage also in the same time, and then help ... etc.
As we have seen, there is none of the available code generators (commercial or free) that can fit our needs ... maybe we'll see more important one soon ;-)
Regards,
Hatem
Dynamix Solutions
*Examples* of code generation, please
While pointers to some code-generation software are great, some examples would be good too. For instance, in Python, you might write a simple code generator as string substitution:
def make_insert_query(table, fields, onerror):
qmarks = ",".join(["?"] * len(fields))
fields = ",".join(fields)
code = """if 1:
def query(cursor, *args):
try:
cursor.execute("insert into %(table)s(%(fields)s"
"values (%(qmarks)s)", args)
except *, detail:
%(onerror)s
""" % locals()
ns = {}
exec code in ns
return ns['query']
add_user = make_insert_query("names", ["first", "last"], "...")
However, in an object-oriented language you'd be unlikely to adopt this approach. Instead, you'd use a class-based approach:
class InsertQuery:
def __init__(self, table, fields):
qmarks = ",".join(["?"] * len(fields))
fields = ",".join(fields)
self.query = ("insert into %(table)s(%fields)s"
"values (%(qmarks)s)") % locals()
def __call__(self, cursor, *args):
try:
return cursor.execute(self.query, args)
except *, detail: self.onerror(detail)
def onerror(self, detail): pass # or override in subclass
add_user = InsertQuery("names", ["first", "last"])
Of course, since I use Python for all my web programming I have no idea if PHP offers these kinds of capabilities.
Re: PEAR DB_DataObjects library
Do you have a link to the home page for DB_DataObjects? I looked around Google and the best I could find was a cached page that summarized a pretty cool generator. I looked on the PEAR site and there was no mention of it.
Re: PEAR DB_DataObjects library
> Do you have a link to the home page for
> DB_DataObjects? I looked around Google
> and the best I could find was a cached
> page that summarized a pretty cool
> generator. I looked on the PEAR site and
> there was no mention of it.
>
>
http://pear.php.net/package-info.php?pacid=80
Look into the documentation for an good introduction to dataobjects.
Re: PEAR DB_DataObjects library
Thanks. The documentation is pretty scarce. From what I understand it reads the table definitions from the database and builds code to match. Is that correct?
Other php generating code projet for database structure.
Hi.
I only want to tell you there's another projet which generate php class from the sql structure:
PHP Generator of Object SQL Database ( pgosd ).
http://vidalcharles.free.fr/pgosd/
Re: *Examples* of code generation, please
yeah, PHP is great, but not for SQL - those tools don't look so cool to me... is
there any way to NOT write SQL in PHP for some general application-oriented use cases?!?
I use ZOPE (http://www.zope.org/) (fm)... it has SQL generation tools, and reuses
everything – the APIs and objects are accessible via HTTP,
XML-RPC, WebDAV & FTP (although I only use the first two regularly or on
public networks). ZOPE (http://www.zope.org/) is written with
Python (http://www.python.org/)... there are constructs built
into ZOPE for accessing relational databases (ZSQL, see Relational Databases Connectivity seciton of the ZOPE Book (http://www.zope.org/Members/michel/ZB)), as well as add-ons (products
for ZOPE (http://www.zope.org/) users) like SQL Forms, SQL Blender (http://www.zope.org/Members/adustman/Products/SQLBlender/) , and
Znolk SQL Wizard (http://www.zope.org/Members/zwork/Znolk_SQL_Wizard/). So using ZOPE it is very rare to write SQL, only for templates in OO-engines, and there is built in an object-oriented persistence layer which is way more flexible than a 2-dimensional relational DB. ;-)
For PHP there are probably some better options than what's here so far,
but all that comes to my mind is phpMyAdmin (fm). Of course it doesn't write
all your SQL queries... you should code your code generators, anyway, not
expect to be able to have a write-all-SQL-to-specific-purposes engine...
phpMyAdmin (http://www.phpmyadmin.net/) does many SQL queries
for database administration purposes (create, import/export, search,
insert, delete, sort). There is a phpPgAdmin (fm) project for PostrgreSQL as well.
as an application server ZOPE (http://www.zope.org/) is a
gem... persistence & logic combined. Python is an excellent high-level
scripting language with better inheritance/acquisition (especially in ZOPE, since its data not just runtime
variables!) than PHP (http://www.php.net/) in my opinion...
there is even a section on generators in the current Python
tutorial! (9.10)
Re: *Examples* of code generation, please
> Python is an excellent high-level scripting language
oooh, here's a nice one: Modeling Framework (http://modeling.sourceforge.net/)
from the freshmeat project description (/projects/modeling/): "main features include generation of database schema, generation of Python code templates" . . . and it gets better, python objects persist in the RDB!
Re: Other php generating code projet for database structure.
other RDB code generators in PHP include :
PHP GEN (http://www.sebi.com.ar/projects/php/php_gen/) v.0.5 released today and announced on FreshMeat (/projects/php_gen/)
Yet another one
http://sourceforge.net/projects/phdwait
not data access layers
I think we should differentiate between "data access layers" and "application frameworks" - some of the tools you guys have posted generate pages and forms and things that more go under the rubrik of "application framework" or something, and not data acess layers. The data access layers is limited to a more thin API that has nothing to do with presentation.
My php/MySQL Generator Search
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.
Re: My php/MySQL Generator Search
> 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.
Check out
(ADODB (http://php.weblogs.com/ADODB)) . It offers a very abstract interface with decent support for MySQL and other database servers.
--Noel
Re: PEAR DB_DataObjects library
yes. it's correct.
manu (http://e88.de)
Layer over layer over layers
Interesting article, I've watching sometime now some database access layers who also make use of another app layer and sometimes over more than one or two..
For instances I saw a class who make use of "quicker" functions which also used Pear to interact with databases, i think this should cause some overhead in the app..
I think those examples are better for rapid development for non-critical applications but for faster PHP is better to just work right with the core PHP functions. I haven't done any benchmarks nor I'm an benchmark expert... But I've always found this true in every programming language I've use like ASP, VB or Java.
What I'm trying to say in fact is: the use of objects and inheritance with other objects is good. But implementing objects over objects that use other objects could not be the perfect approch, not that is the absule purpose of the article. Maybe I'm wrong but I'd like to get a bigger picture.
Php Object Generator
I recently wrote a simple but useful Php Object Generator.
Try it out.
Re: Php Object Generator
> 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
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
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.
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.
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
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.