I have written bits & pieces about the database layer used in ZenMagick, but not really anything that would show how to use it. So here is a quick overview about what it does and can do, plus a few examples.

The interface

All the ugly details are hidden behind an interface, aptly named ZMDatabase.

There are currently three different implementations (provider) included in ZenMagick:

• ZMZenCartDatabase
  This is a wrapper around Zen Cart’s $db with all the pros and cons. This is currently the default, because it works One confusing thing is that using this implementation gets the database stats mixed up, because any query via the API will also show up in the stats for $db.
• ZMCreoleDatabase
  The second implementation around, based on creole. Unfortunately, the project is currently not being maintained, so in the long run this is not a good option.
• ZMPdoDatabase
  The newest addition and the seemingly the fastest, too! Using PDO to do the lover level database access.

So, what sort of services does the interface offer? Access to the database can be done either via model classes (as in Model-View-Controller), or using straight SQL and either arrays or objects as data container. This is true for both read and write/update operations.

SQL parameter handling / binding

Parameter binding in SQL is done using :name syntax. Example: 'SELECT * FROM products WHERE products_id = :productId'. The parameter productId would then be expected either in an associative array, for example array('productId' => 3); or via an object with a method getProductId().

Type conversion

The binding syntax doesn’t include any type information. Information about types are handled separately by defining mappings. This is an optional step, because if no mapping is provided the API will build a default mapping based on table meta data. All type conversions during binding will then be based on that.

One of the advantages of explicitly specifying mappings (as does ZenMagick for all core code) is that it allows to map database column names to property names of objects (a simple form of an object-relational database).

Mappings are set up per table, however a single query might use mappings of several tables (usually all the tables involved in the SQL).

How to obtain a database instance?

An instance of ZMDatabase can be retreived via ZMRuntime::getDatabase(). In this simple form an instance of the currently configured provider configured to talk to the default Zen Cart database is returned.

If you want to connect to a different database/server, then all details may be specified as parameter for getDatabase() in the form of an associative map.
Example:

$dbconf = array( 'host' => 'localhost', 'database' => 'mydb', 'driver' => 'mysqli');
$database = ZMRuntime::getDatabase($dbconf);


Both the creole and PDO implementations should allow to connect to any supported database type, however that hasn’t been tested widely (The phpBB3 plugin will use whatever driver is configured in phpBB3 and that seems to work fine).

Database Driver

As shown in the example above, it is possible to specify the database driver to be used. Both creole and PDO support a range of different database types. Until such time when Zen Cart supports different database types this is of limited use, though.

Examples

I’ll keep this to a minimun and rather add more to the wiki where formatting is not so much of an issue…

I’ve just updated the demo store with a release candidate version of ZenMagick 0.9.5.

There are some interesting visual and other changes that the demo store illustrates:

• Use of PDO based database API
• New plugin to switch between ZenMagick and Zen Cart template
• Improved event support for Zen Cart templates (that’s what the switch is really for!)
  The extended event support now allows ZenMagick plugins to manipulate the final HTML of Zen Cart templates. That means, all ZenMagick plugins that rely on this mechanism can now be used in Zen Cart templates, too! Obvious examples are the page stats and style switch plugin (even though the style switch doesn’t really work for Zen Cart templates, right now).

One thing that is now good to see is the difference in database queries. Even though the Zen Cart default template and the ZenMagick themes do not display exactly the same data, it’s still impressive to see the difference in queries

I somehow lost my release notes so the public release will have to wait until early next week to give me time to get organized again.

Happy demo’ing!

Somehow I overdid it with adding code to purge old cache entries by adding additional code to the memory cache implementation. Turns out that Cache_Lite doesn’t support any modus parameter for memory caching!

So, if you are left wondering where all the additional SQL queries for attributes are coming from, now you know.
Since I plan on releasing this weekend there is probably not much point in posting a bugfix or similar. Anyway, if you want to fix it yourself, just delete the following line in ZMMemocryCache.php:

$this->cache_->clean($this->group_, 'old');

On a related issue here is a nifty small code snippet that allows to display the top queries for the current request (using ZMCreoleDatabase or the new ZMPdoDatabase). Just paste that into your local.php and you’ll see the most often run queries (usually with different parameters):

class DbStats {
public function onZMFinaliseContents($args=null) {
$stats = ZMRuntime::getDatabase()->getStats();
$map = array();
foreach ($stats['details'] as $details) {
if (!array_key_exists($details['sql'], $map)) {
$map[$details['sql']] = 0;
}
++$map[$details['sql']];
}
foreach ($map as $sql => $count) {
if (6 < $count) {
echo '<strong>'.$count.'</strong> '.$sql."<br>";
}
}
}
}
ZMEvents::instance()->attach(new DbStats());

(Reminds me that I am still looking for a good WP plugin to preserve formatting and perhaps even do sytax highlighting!)

A while ago I wrote that I’ve been working on (yet) another implementation of the ZenMagick database API using PDO.

After some back and forth (the code was removed and readded yesterday) I’ve started working on that again. There are a couple good reasons for that.

Firstly, since Creole is not supported any more, having another implementation seems like a good idea. Secondly, it appears that it is quite a bit faster than Creole (and perhaps even Zen Cart’s $db).

I haven’t had a chance to verify this usingpages with lots of database access, since there are still bits missing in the implementation. The good news is, though, that the tests for ZMDatabase have been improved and extended in the process of filling the gaps.

In related news, I’ve decided (more or less) to make another change to the database API. This will only happen after 0.9.5 is released, though. The methods to control transactions are a bit clumsy and I hope that things will be a bit more intuitive in the future.

Form validation rules
Since it is now possible to either add or replace validation rules for a given form id, there is a pitfall in that plugins typically set up their custom validation rules before the global/theme validation rules are loaded. If a theme developer decides to override the default rules for a form those plugin rules might get lost.
For example, a particular site might not require a date or birth, postcode, etc. and it might be easier to replace the default rules with a new (smaller) set of rules.

To avoid overriding plugin rules I’ve started converting plugins to use the onZMInitDone() event callback to register custom validation rules. This event is fired once al bootstrap, init and theme setup is done (right before the controller gets executed).

Database auto mapping

I might have written about this as part of a release announcement, but I can’t really remember, so here goes (again)…

Most All ZenMagick services and other classes that access the database use manually created mappings to map object properties to database columns. These mappings are store in the appropriately named file core/database/db_mappings.txt. The mappings also contain information about data types which are used to properly bind values (ie. cast to int, boolean or quote to name just a few).

Sometimes it is not practical to create mappings in advance. In particular if the exact format of the table is now known. In those cases the ZenMagick database API can generate a generic mapping on the fly to ensure type safety. One good case where this is useful is to access 3rd party databases/tables (the new phpBB3 plugins will make use of this).

Currently this is disabled. To enable just change the value of the setting ‘isEnableDBAutoMapping to true.

One of the new features in the upcoming Zen Cart 2.0 version is going to be a reduced number of database queries for price lookup. Most of those queries are attribute related and therefore do not affect all stores/products.

Due to the current work on making ZenMagick properly available for Zen Cart templates, I’ve had the chance to do some easy comparisons. (more…)

I’ve been pondering whether to have another release before Christmas for a while now and I think it would be a good idea. As far as releases go this is going to be a pretty minor one.

I spend a some time fixing some small things that croped up and als refactored a lot defines away (making them consts in service classes mostly). Some theme related defines about directory names got removed completely as they do not really change a lot (and shouldn’t either).

On the plus side there are going to be two new plugins. Firstly, the already annourced form handler and also the code I’ve been using on the demo store to switch themes.

There is also some new code in the form of new cache implementations and also a new ZMDatabase implementation using PDO.

The only real downside is that I’ve touched every single plugin. That in itself is probably not a bad thing, however the sourceforce file release interface requires to set platform and file type for each separately and that is a real pain.

Yesterday I checked the creole homepage for updates and to my horror I discovered that the project is being abandoned.

While I can understand the reasons behind this step to a degree, I still feel sad on different levels. I’ve picked creole as alternative to the current Zen Cart database code as it seemed mature and, at least to me, rather promising.

Also, the style it is coded in is very close to my ideas about how PHP code should look like. Seeing that the repository is going to exist, maybe there is still hope that someone else will pick up the project.

For ZenMagick, nothing really is going to change. I’ll be trying to convert the current 1.2 beta code into a single file as I’ve done with the 1.1.0 release (I had a go at that in some spare minutes, but there have been changes that make the current import code break, so it’ll take some time).

I am glad that I took the time to write my own (thin) database layer for ZenMagick to avoid depending on a single external project. This is proof that it was time well spend, even though the circumstances are very unfortunate.

I might evaluate another database API for the future, although this is very low priority and will be far in the future. Suggestions are, of course, welcome.