Database Abstraction Layer and Sql Cache
The abstraction layer has been re-factored to include a new factory based driver layer. The driver layer (the part that actually talks to the database) has been kept as light as possible. This will mean developing driver layers for other databases should be extremely easy. To highlight the new driver layer we will be including preliminary support for mysql innodb tables. Using innodb tables will also allow us to include support for transactions. Transactions are a way of ensuring that changes to the database that affect more than 1 table, do not leave the database in a corrupt state, if those changes fail at any point.
The sql caching layer has also been re-factored, to make it easier to add custom caching solutions. Again, to highlight this, we will be adding support for caching using the extremely popular Memcache system. Memcache is a memory based caching system that has the potential to provide blistering performance.
To further aid those that might wish to use other database types, postgres, oracle mssql etc., we have also created a new database layer called the DAO (for database access objects) These are simply php classes tasked to build and execute sql queries related to a specific table. The DAO's can be partially or fully overridden so that SQL can be customised easily for other database types.
Database Performance Enhancements.
Zen Cart provides huge flexibility in the options it allows shop owners. Extremely flexible product attributes, differing product types and a plethora of product marketing options. In the past this flexibility has had a downside in the number of sql queries that were used to support all of these options.
We have spent a lot of time analysing those queries, the internal database structure, and the infrastructure that supports the execution of those queries, and as a result have implemented a number of changes that seriously improve the database performance.
The first is the use of MPTT (modified pre-order tree traversal or sometimes called nested set) to describe the category structure. The original system relied on recursive functions to generate a category tree and as such could generate a very large number of queries for all but the simplest of category structures. MPTT allows us to build a category tree with a fixed number of queries no matter the size or complexity of the category structure. MPTT does have some downsides, most important of which is the complexity of the sql required to administer the category structure (adding/deleting/moving categories). However we believe that the performance gains, where it matters, to the users/shoppers experience are well worth it.
Secondly, in previous versions of Zen Cart using all of those wonderful options, like attributes, product types, product pricing options etc, took its toll in the number of queries needed to get information about the products being displayed. Each feature would run queries, without regard to the fact that some other feature may have already gathered that information.
To address this we have created new classes specifically tasked to getting all of the information relating to a product using the fewest queries possible. As mentioned elsewhere using these new classes can result in decreasing the number of queries enormously (typically 700+ fewer queries on an out-of-the-box Zen Cart install homepage, and that is with product counts turned on).
We have also increased the usage of the sql caching subsystem, so that subsequent page loads will use even fewer queries.
Finally, much use has been made of mysql optimisation techniques to improve indexing/structure to further improve performance.
To summarise
New Database Driver Layer
Extremely light/flexible drivers make it easier to support other Database Types
Preliminary support for innodb and mysql transactions
Sql Caching system rewritten
Much easier to add new caching types
Preliminary Support for Memcache
Use of MPTT for category structure
Reduces number of queries needed to 'describe' the category structure
Improves user experience thru reduced page load times
Supporting Classes to reduce query load
Hugely reduces queries needed
Reuses queries using Cache to further improve performance
Front Controller.
The front controller in Zen Cart is index.php. In a live shop it has always been responsible for loading all of the code required to display a page, based on the URL parameters and configuration settings.
The new index.php consists of just a few lines of code, the majority of which are just configuration settings. It then loads the required code using an Object Oriented framework.
The configuration settings are there to set up the environment in which the front controller runs, and although they are not meant to be changed for production use, what they allow you to do, if you enable this option, is create cloned copies of index.php that can operate in different environments.
In practice this could mean you have different controllers for Development/Testing/Production.
(note there are security settings in place to ensure that you don't accidentally enable a development front controller on your production system)
Where this ability to have different front controllers really becomes useful is when you are initially testing your site, especially in conjunction with some shiny new debugging & logging tools.
You can, for example, set up a development front controller (by copying and renaming index.php; I usually call mine index_dev.php) and by changing some settings in the new front controller, have access to a JavaScript based tool bar that will display things like:
SQL queries executed for that page
Server variables
PHP superglobals (GET/POST/SESSION)
Named timers – to find the bit of code that is slowing you down.
At the same time you can also change the logging parameters to log as much or as little detail as you want.
Summary
- Multiple Front Controllers allow for different configurations in testing and deployment
- Enhanced support for debugging and error logging including in situ display of debugging information.
