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…

Last night ZenMagick 0.9.5 was finally released! I am quite excited about this particular release, because it includes a couple things that make ZenMagick a lot more attractive to existing Zen Cart installations.

For one thing, there is a new patch that allows plugins to access the final HTML content of Zen Cart templates. This has been around in ZenMagick (for themes) for quite a while and proves to be very powerful.

A lot of the storefront plugins (page stats, page caching, google analytics) depend on this feature and now they can be used with Zen Cart templates too!
If you head over to the demo store you’ll see that the HTML generated by the default Zen Cart template is caches like any other page, following the same rules!

Another quite surprising thing (at least  for me!) is the new PDO based ZMDatabase implementation. It seems to be the fastest implementation so far and the demo store seems to do fine using it, so I might make it the default implementation for the next release.

There are also improvements to existing plugins and, of course, a couple new ones. The most interesting one is probably the phpBB3 plugin. It provides the same features as Zen Cart’s phpBB support, just for phpBB3.

I also really like the new theme toggle plugin that allows to switch between Zen Cart and ZenMagick. Probably not very useful for a production environment, but good to compare features, displayed values, etc.

Of course, this is not the end, and I’ve already started working on the next version. In that context a reminder that with 0.9.6 I’ll be removing a lot of the deprecated stuff. Also, the deprecated API support will be removed before a 1.0.0 release, but most likely not before 0.9.8.

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.

With the number of things on my todo list for the next release shringking fast its time to have a look at what is about to happen in the next ZenMagick release.
I’ve already written about a few things, but I think it is also nice to have it all in one place. (Also, these posts are usually good starting points for writing the release notes!)

So, here’s a list of things to look out for int he next release:

• Banner Management
  So far, banners where identified by index (integer) and needed a setting each in order to work. As of 0.9.5 this is going to be simplified and there is actually already some documentation about how things are going to work.
• Database API
  A number of things changed here, in particular the ability to create type mappings on-the-fly and support for array model data in all xxxModel() methods should make life simpler in a few cases
• define refactoring
  A lot more defines have been converted into consts in the appropriate classes. Defines are fine, however, I feel that consts are a lot more readable and also it’s easier to find where things actually are in the source code.
• ZMLayout refactoring
  The service class ZMLayout has been renamed ZMTemplateManager in reflect the newly added methods to manage CSS and JavaScript resources. Short story is that it allows to add CSS to a page even after the <head> section has been created by the template.
• Support for large result lists
  I’ve explained this already, so I’ll just say that the new solution is SQL based and pretty close (at the bottom) to what Zen Cart currently does.
• Plugins
  As usual there will be new plugins. First, an initial version of a plugin to manage custom ZenMagick settings in the database. It uses some new widgets and I expect that with the number of widgets growing there will be a lot of improvements in the future.
  Secondly, support for phpBB3 at the same level as currently exists for phpBB2.
  There are also improvements and fixes for a number of other plugins that are, of course, listed in the changelog.
• Open
  A couple things are still open, but should be included, time permitting: a new search page based on the already existing ZMProductFinder class, make all unit tests pass!

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.