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.
All the ugly details are hidden behind an interface, aptly named ZMDatabase.
There are currently three different implementations (provider) included in ZenMagick:
This is a wrapper around Zen Cart’s
$dbwith 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
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.
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
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.
$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).
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.
I’ll keep this to a minimun and rather add more to the wiki where formatting is not so much of an issue…