Best practices when using Magento setup scripts

As I mentionned before, Magento setup scripts enable you to make your software setup more reliable. But working with setup require to use some best practices. If not, it could become a nightmare…

Here’s a list of points you should take care of when writing setup scripts

Do not update a setup script already under revision control

A script already under version control has perhaps already been played in some locations, and so core_resource record for this setup has already been updated

In this case, in order to play a second time this script, you must restore the initial state before it:

  • At least, downgrade the core_resource related record
  • Perhaps destroy all that has been run within the script

It’s very problematic because we lost all the automation within the setup. So you should really be careful before sharing a script to ensure that all the script’s content is well written

Be independant from database structure

This is why I wrote previously to not use the SQL files and prefer using the PHP scripts: Magento allow you to prefix your table names for security reasons. If in production you’ve prefixed your tables but not in testing, your script must work fine in each case.

So you must be able to find the table name in currnet context, and this is the role of the methode Mage_Core_Model_Setup::getTableName method

Be independant of the server type which hosts your Magento

For now, this is only in theory that Magento can be run on other database servers than MySQL. But even if it’s a dream, perhaps the module you developed will exist very longer and in 2015, when Magento will be able to use another SGBD, your setup script will also work fine

To do that, it’s really easy since the 1.6CE / 1.11EE because database connexion layer has been updated to be more abstract

Reduce SQL instructions to their minimal to have the more accurate error management

Take a look at the following SQL query we want to run:

SELECT entity_id, sku FROM catalog_product_entity;
ALTER TABLE foo ADD INDEX (entity_id);
<p>The historical way to run it in Magento could something like this:</p>
$this->run("CREATE TABLE {$this->getTableName('module/foor')} AS
SELECT entity_id, sku FROM {$this->getTableName('catalog/product')}
ALTER TABLE  {$this->getTableName('module/foor')} ENGINE=Memory;
ALTER TABLE  {$this->getTableName('module/foor')} ADD UNIQUE (entity_id)");

Fine, but each SQL instruction can have its own errors. So if we play back the script, it's possible to have some instructions run many times and could lead to an inconsistent context, like:

  • Same index defined multiple times
  • Insert run many times
  • ...

To reduce this risk, divide each SQL query to its minimal entity: if an SQL error is encountered, the following code won't be executed and so next time you'll launch the script you won't run the same SQL request. So in this example, use multple run instructions

Take care of existing

It's very important to take care of possible existing resources, and so, for the following reasons:

  • In 1.6 and previous versions, there is no semaphore in Magento setup scripts; in multiple servers, it's possible that you've started your setup while another server was aleady running it (but have not yet updated the core_resource table)
  • If the first time you've run it, there was an error, you'll need to execute it a second time. Testing already existing resources will avoid you to restore initial state before executing it a second time

It can be done in SQL instruction or within PHP code

Don't use constants in Magento setup scripts

Constants are used to declare some reference content. So it's often the case when you write some setups scripts.

But if you reuse the constant definition in multiples scripts, take care of ensuring that you test if the constant already exists: When Magento run some upgrade scripts, it will execute all the required scripts in one time by including all scripts. So it could lead to a case thasituation where the constant is defined multiple times, and so through an error.

Leave Magento use its own database connexion management

It means, do not access connexions objects in setup scripts: this is not a problem when you use only one database server, but if you use different read and write database servers, by using database connexion objects you 'll may change where will be written the data you want to setup

So leave Magento make it's own work

Using Setups to deliver application software with Magento

A long time ago,

in a (not) so far away office,

a Magento project is coming

we could hear something like this

Guys, I have an error when I display product sheet: “table does not exist”, what’s wrong?

The little guy on the openspace corner who talks like a robot answered him: It’s normal, you must execute the SQL script create_table.sql located in var/temp

This is not about the force I’ll talk about, but the power of the dark side, the lazyness: How Magento can help us to solve deployment problems? With the magento setup scripts

Introduction to Magento setup

Main interest of the Magento setup

Have you ever asked how Magento install its own database? This setup process is run against the Magento setup scripts: all database tables, initials configuration values or required records are inserted with these scripts: no need to play any SQL, all is managed with the native setup Magento API

Files involved in Magento setup

This mechanism is strongly linked with Magento modules architecture: if your module requires a dedicated table, it must be able to create the prerequisite to work properly. Another case study is when project is started: you made some modeling choices, and those ones require perhaps a specific configuration. it’ll very interesting that this configuration will be set up automaticly on each location (developpement, testing, prod, …), without any human action.

After reading this post, if you check what is executed by Magento during database setup, you’ll see that each module is responsible for its own setup:

  • Mage_Catalog create the catalog attributes
  • Mage_Checkout create the tables of agreements
  • etc

All modules files involved in setup are located in the module sql subfolder

Let’s see how it works

Main working

How does Magento find the module list that have some setups to run?

As I mentionned before, all is based upon the configuration files located in the app/etc/modules folder. When configuration is merged, Magento knows which are active modules.

From this merged configuration, Magento will find the setup list to run.

But not every module will have some setup. To know which modules have installers, it does not browse the sql folder for each module but looks for some dedicated XML nodes in configuration. These nodes depend on resources and are defined like this:

Without this node, and even if you have defined some sql scripts in your sql subfolder, nothing will happen

Now Magento can iterate through the configuration saved in cache, look for the <config><global><resources> to find the availables resources defined. if this resource has a setup subnode defined, it will be known as “with setup”

In which state are your Magento module sources? the version notion

Your module sources can be in 3 differents states with your Magento:

  • Install mode: you have just installed the module source on your Magento, but for now nothing has already been set up
  • Update mode: you have just made an update of your module sources, and the new setup has not already been played.
  • Downgrade mode: you’ve made a downgrade of your module sources. And now, the state of your database is too far from your module sources

All these states are managed with two version numbers: the current version number of your sources and the latest installed version number of your module in database

Magento current installed module version number

This number is defined in the core_resource table. This table contains for each resource the latest version number installed

Magento current module version number

This number should be defined in the config.xml file located in module etc subfolder with the following node:


Is there a specific format for version number?

No, Magento does not expect a specific format. It’s a versionning problem and often we define the version number with a format like X.Y.Z, where:

  • X: the major version number, and increases when you review the software architecture
  • Y: the minor version number, and increases when you add a new functionnality in module
  • Z: the bug fix version number, and increases when you fix a defect

What’s important is that your version number means something and changes according to your source code update

How does Magento find the list of setup to run?

The Magento resource defines where are located files and if there is some setup defined for your module

How does Magento link resource name with file path to find script list involved in setup?

The resource name is used to find where are located setup scripts:


This is in this folder that Magento will find the script used in setup

Setup state will help Magento to find possible file list to run

Then Magento will check in which state are your modules sources:

  • install: there is no db record for your %resource_name% in core_resource table
  • upgrade mode: core_resource database record is lower than the version defined in the module source
  • downgrade mode: core_resource database record is higher than the version defined in the module source

According to this state, Magento defines in which case you are:

  • install (Mage_Core_Model_Resource_Setup::TYPE_DB_INSTALL)
  • upgrade (Mage_Core_Model_Resource_Setup::TYPE_DB_UPGRADE)
  • rollback (Mage_Core_Model_Resource_Setup::TYPE_DB_ROLLBACK)

Version comparison between already installed version and source version is based upon the version_compare PHP method

Possibles file list to run

Here’s how Magento knows if there are some scripts to run. Let’s see how this file list is built

Can we use subfolders to organise our setups scripts in app/code/%Your_Module_CodePool%/%Your_Module_Namespace%/%Your_Module_Name%/sql/%resource_name%/ folder?

First, Magento iterates on these folders with dir class and so, will iterate only in this subfolder; so you cannot put your scripts in a subfolder, they won’t be played

Which extensions can be used to run setup scripts?

Magento iterates on all files and expects to find .SQL or .PHP files to run.

As a best practice, you must use PHP file to avoid using hard coded table names and field names: For example Magento allows you during install to provide a table name prefix. The easyest way to find this prefix without using PHP setup scripts.

How can Magento take care of the database server type used with your Magento? the database connexion connector type

On paper, Magento can use multiple databases server types. Default one is MySQL.

By default, Magento setups scripts can take care of the setups differences between each database servers with the connector type: Connector type is defined by the configuration node model used by the connection defined to run your module. By default, all connections of your Magento will inherit the connection default_setup, defined in the app/etc/config.xml. As you can see in the following screenshot, this node is by default defined as mysql4

When determining which scripts can be involved in the setup subfolder, Magento will look for the files matching the pattern %connector_type%-%source_state%-.+

How does Magento avoid playing two times the same setup script? the role of version

After finding all files matching the previous pattern, Magento reduces the list according to the current database state to only required versions with the version compare usage

At the end of this step, every script in the available list must be run

Running the scripts: how does Magento increase resource version number in core_resource table?

Plus the setup list to run, Magento will include one other that follows. Version number in core_resource table is not updated at the end of all the scripts, but after a script has completed normally. So if you need to restart the setup process, you’ll restart at the last error setup script

Conclusion about this Magento installer mechanism

This mechanism is really helpfull to deploy and normalize your different projects locations: by setting these scripts, you are sure that every location has the same required configuration values, concepts specified. Deploying a new location for a new developper or for the production server will be more easily because all required configuration values, tables, etc will be already set up.

One other great advantage of these scripts is to allow you to versionate only the scources code: no SQL state versionning requirement, all is managed through sources modules files

Develop and test setups scripts may have a cost to take care, but reduce risks based on misconfiguration or a forgotten SQL script