Resources and Database Connections

The role of a resource in Magento is to manage database connections. Resources are defined under the global XML tag of any config.xml file. To make a new database connection you would add XML like the following to any config.xml file. Each resource has a name of the form module/name and each connection has a name. Connection names are generally of the pattern

• module_read,
• module_write,
• or module_setup.

<resources>
<default_setup>
<connection>
<host>localhost</host>
<username></username>
<password></password>

<dbname>magento</dbname>
<model>mysql4</model>
<initStatements>SET NAMES utf8</initStatements>
<type>pdo_mysql</type>
<active>1</active>
</connection>
</default_setup>
….
The format should be mostly self explanatory, most of these values end up being passed to the Zend_Db_Adapter_Abstract class. The initStatements tag is executed upon every connection to the database server. The model tag seems unnecessary, perhaps it was part of an idea that didn’t pan-out completely. The type tag refers to one of two connection types defined in the global app/etc/config.xml file. Only pdo_mysql and mysqli are implemented at the moment.

To retrieve a Magento database connection we must first get a resource. If you require a generic database handle, you can use the name core/resource for the resource  name, and core_write for the connection name. The Mage::getSingleton method keeps track of all classes loaded through it as singletons and returns a previously initialized object if the name matches. Each module may have its own database settings – usernames and passwords – or it might even be connecting to a separate database server, so be sure to use the most appropriate resource name when you can. In a default setup, all of Magento’s connections use the settings of default_setup, default_write, or default_read.

$write = Mage::getSingleton(’core/resource’)
->getConnection(’core_write’);
if ($write instanceof Zend_Db_Adapter_Abstract) {
echo get_class($write);
}
//outputs: Varien_Db_Adapter_Pdo_Mysql

Database Design

Magento’s database design is one of its most controversial aspects. Key data aremodeled using the Entity Attribute Value method (EAV). Utilizing an EAVmodeling pattern allows for unlimited attributes on any product, category, customer, or order, but EAV also depletes a programmer’s ability to write ad-hoc queries against the data. Before we delve deep intoMagento’s database design, we will look at the basic way of communicating with the database – the resource.