My considerations about Db models and RESTful application

I’ve worked on more than one project using complicate DB layers and HTTP client/server systems (e.g. RESTful apps) to interface with the DB server.

My opinion: when working on a project (especially a team project), easy solutions are better then clever ones. OK, OK, a design pattern is an elegant solution to a common problem, but before doing that, keep in mind two basic design principles: KISS (Keep It Simple, Stupid) and YAGNI (You Ain’t Gonna Need It).

A DB layer is the ground of an application, and it must be simple, solid and extensible.

My idea: Simple, maintainable and extensible Model classes for DB access

Make a class for each DB table, all of them extending a common abstract one (for future extensions). Each class has its own methods to insert/retrieve/delete/update the data. Of course do not make a method for each operations, group them using multi-purpose methods and class constants in arrays. Methods accepts params as array and return data using arrays (so easy to deal with). Common insert/delete/update return data in the same format (e.g. numbers of records updated, or exception, no different values for each method). All the input and return params must be documented with PHPDoc comments including all the possible values of an input array key . You can write a constant for each column in the db, so you do not have to look at the DB to know what’s the column name, just use the IDE autocompletition for everything.
e.g.

$newsModel = News::getInstance();
/* get news of the category id=2, limited by 5 and ordered by date.
Join with parent table and select with columns are needed. */
$rows = $newModel->getRecords(array(
  News::FILTER_CATEGORY = 2,
  News::ORDER_CREATED_DESC,
  News::LIMIT => 5,
  News::JOIN => News::JOIN_AUTHORS,
  News::COLUMNS => array(News::FIELD_TITLE, Authors::FIELD_NAME)
));
foreach ($rows as $row) {
   //echo $row[News::FIELD_TITLE]; //piece of news title
   //echo $row[Authors::FIELD_NAME]; //author name (joined)
];

}

Semplicity and Maintainability

One db table => only one class. All the logic inside the models. If I alter the db table, easy to change the centralized logic and costants.
From the controller, I write the code to access the database using IDE autocompletition in a few seconds.
If I want to add another condition to the query, just add to the array. To add a support to something new to the class (new filter, new join, new column), just add the constants, the logic and update the PHPDoc comments.

Caching advantage

This approach make very easy to implement a cache system with automatic tagging and cache invalidation by using wrappers and magic methods [see post about it ].

Extensibility, e.g. move to a client/server via HTTP (web service)

– You want to switch from Zend_Db to Doctrine / NoSQL or optimize the queries ? no problems, just replace the inside logic. the methods still have same input/output. Much easier if you have written unit testing. You sure don’t have to write another layer to dynamically switch between database (that requires a huge effort, in contrast with YAGNI ).

– You want to move the database to a different server and use HTTP calls to get the data (like RESTful apps) ? I have a elegant, don’t-repeat-yourself and easy solution with PHP 5.3. Make the getInstance() of the super class returning a wrapper (you can do that, or alternatively, use a external factory class) that through magic methods, makes the HTTP calls to the server passing [class name, method name, array of params] and decode the response into an array (just one class for all models !). The server will use the same db models classes (shared libs) and implement the other side of the logic with a correspondent one class ($model = new $modelName(); return $model->$methodName($params);). So, basically you code as you are using a local db, but the request is actually going through HTTP to another server. You can switch back to the local db approeach by just changing the abstract DB model class to return the db instance instead of the wrapper.

Drawbacks ?

Most of the application can use this approach and do not need over-engineering of the DB layer. Please follow with your comments with any problems you see in this approach.

ZFDebug panel quick tutorial

In this post I’ll just write some notes to install the ZFDebug panel for Zend Framework. That’s basically a front controller plugin that displays at the bottom of the layout (slides from the bottom), contains “debug panels” (cache, variables, zend db, time info etc…) and it’s easy to extend.

Copy libraries and images

Grab the last stable zip from the Downloads page, or just export the SVN (just my preference as it’s a debug panel, unstable features are OK).

#install library
cd <zendProjectRoot>/library
svn export http://zfdebug.googlecode.com/svn/trunk/library/ZFDebug/
# [not necessary] copy images (if images dir already exists, go inside and export a level less)
cd <zendProjectRoot>/public
svn export http://zfdebug.googlecode.com/svn/trunk/web/images/

Activate using a boostrap _init() method
Now, the plugin can be activated using a Bootstrap _init method (some options cna be moved to application.ini, but some others don’t, so better to keep everything together in a _init method IMO).

copy the whole method from here
http://zfdebug.googlecode.com/svn/trunk/demos/Zend_Application_Bootstrap.php
to your Boostrap. To add plugins, read the comments inside that file. Better to create plugin classes to make easy updating ZFDebug from SVN.

The minimum code to load should be the following (not tested). Of course add the logic to show only on local environment or under some conditions (auth / cookie / env)

protected function _initZFDebug()
{
    // register namespace
    $autoloader = Zend_Loader_Autoloader::getInstance()
                  ->registerNamespace('ZFDebug');

    // Create ZFDebug instance
    $zfdebug = new ZFDebug_Controller_Plugin_Debug(array(
        'plugins' => array(
            'Variables',
            'Html',
            #'Database' => array('adapter' => array('standard' => Zend_Db_Table_Abstract::getDefaultAdapter())),
            #'File' => array('basePath' => 'path/to/application/root'),
            #'Memory',
            #'Time',
            #'Registry',
            #'Cache' => array('backend' => Zend_Registry::get('cache')->getBackend()),
            'Exception'
        )
    ));

    // Register ZFDebug with the front controller
    $front = $this->getResource('FrontController');
    $front->registerPlugin($zfdebug);
}

BSD licence

Display full query in the Exception of Zend_Db_Statement_Pdo

When working with Zend Framework 1.x and Zend_Db in particular, you might bump into an SQL problem like this

Syntax error or access violation: 
1064 You have an error in your SQL
syntax; check the manual that corresponds 
to your MySQL server version for 
the right syntax to use near ') 
ORDER BY `order` ASC' at line 1

OK, thanks a lot Zend, but what’s the query ?

the statement is kept inside $_stmt property of Zend_Db_Statement, and to display it, you need to acess the queryString property

$_stmt->queryString

Depending on the Zend version, the Zend_Db_Statement class might have a getter for it, so you can debug with

var_dump($zfDbStmt->getDriverStatement()->queryString);

You can also – of course – enable the MySQL general log and monitor the log file

[mysqld]
general_log_file = /path/to/logfile
general_log = 1