A very difficult part of maintaining an application is managing the database changes. Some of the more modern frameworks offer a way to automatically handle schema and data changes within the application. Magento is one of these frameworks. Many developers are used to adding columns or rows to tables and then 2 weeks later forgetting what they did. I know this has bitten me before and I’m glad I don’t have to worry about it with Magento.
To start, let me say, “ANY DATABASE CHANGE YOU MAKE SHOULD BE SCRIPTED; THIS INCLUDES SCHEMA AND/OR DATA CHANGES”. Hopefully you could hear me when I said that. I put it in CAPS just in case you were hard of hearing.
So the first example is that you need a new table and would like that table populated with some sample data. When you first install the module into your Magento instance. There are some config.xml configurations that need to be configured, as well as the actual script to create the tables.
More than likely, you will have already created the modules section in the config.xml and given your module a version. For some reason, the default is usually 0.1.0 in our modules.
|
1
2
3
4
5
|
<modules> <Super_Awesome> <version>0.1.0</version> </Super_Awesome> </modules> |
Next, in the config.xml we need to define the resources. The one we really care about here is the “setup” resource. Remember the name “awesome_setup” and notice the module element inside of it. The module is the “driver” for the setup scripts. If the module’s version number changes, it will look for scripts to run.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
<global> ... <resources> <awesome_setup> <setup> <module>Super_Awesome</module> </setup> <connection> <use>core_setup</use> </connection> </awesome_setup> <awesome_write> <connection> <use>core_write</use> </connection> </awesome_write> <awesome_read> <connection> <use>core_read</use> </connection> </awesome_read> </resources></global> |
Now, in our example, we have a module at version 0.1.0, and a resource for “awesome_setup” for the Super_Awesome module.
I’m going to stop right here to explain how/when the setup scripts are run:
The setup script mechanism is run the FIRST time a request is made after the cache is cleared.
- Magento will get a collection of all the “setup resources” and their corresponding Module version numbers from the config.xml files.
- Magento will then go to the core_resource table and get a collection of all the “setup resources” and their version numbers stored in the table.
- Magento will then do 1 of 3 things:
- If the setup resource doesn’t exist in the core_resource table it will run an install script.
- If the resource version in the database is less than the one in the config.xml file, it will run one to many update scripts.
- If the resource version in the database is greater than the one in the config.xml file, it will run a rollback script.
Here is the code in the Mage_Core_Model_Resource_Setup class that figures out which script to run:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
/** * Apply module resource install, upgrade and data scripts */ public function applyUpdates() { $dbVer = $this->_getResource()->getDbVersion($this->_resourceName); $configVer = (string)$this->_moduleConfig->version; // Module is installed if ($dbVer!==false) { $status = version_compare($configVer, $dbVer); switch ($status) { case self::VERSION_COMPARE_LOWER: $this->_rollbackResourceDb($configVer, $dbVer); break; case self::VERSION_COMPARE_GREATER: $this->_upgradeResourceDb($dbVer, $configVer); break; default: return true; break; } } elseif ($configVer) { $this->_installResourceDb($configVer); } } |
Because in our example, we have not yet run any setup scripts and the resource does not exist in the core_resource table, we have created a file called local/Super/Awesome/sql/awesome_setup/mysql4-install-0.1.0.php:
<?php$installer = $this;$installer->startSetup();$installer->run("-- DROP TABLE IF EXISTS {$this->getTable('super_awesome_example')};CREATE TABLE {$this->getTable('super_awesome_example')} ( `id` int(11) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL, `description` varchar(100) NOT NULL, `other` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;INSERT INTO {$this->getTable('super_awesome_example')} (name, description, other) values ('Example 1', 'Example One Description', 'This first example is reall awesome.');INSERT INTO {$this->getTable('super_awesome_example')} (name, description, other) values ('Example 2', 'Example Two Description', 'This second example is reall awesome.');INSERT INTO {$this->getTable('super_awesome_example')} (name, description, other) values ('Example 3', 'Example Three Description', 'This third example is reall awesome.');INSERT INTO {$this->getTable('super_awesome_example')} (name, description, other) values ('Example 4', 'Example Four Description', 'This fourth example is reall awesome.');");$installer->endSetup(); |
Now, if we clear cache and make a request to the application, Magento will run the install script and update the core_resource table to version 0.1.0 for the awesome_setup resource, so next time the versions will be the same and no scripts will be run.
Now, what if you need to clear out the sample data you put in there in the next version of the module?
First, change the module’s version number in the config.xml:
|
1
2
3
4
5
|
<modules> <Super_Awesome> <version>0.1.1</version> </Super_Awesome> </modules> |
Then create a file called local/Super/Awesome/sql/awesome_setup/mysql4-upgrade-0.1.0-0.1.1.php:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
<?php$installer = $this;$installer->startSetup();$installer->run("DELETE FROM {$this->getTable('super_awesome_example')};");$installer->endSetup(); |
When you clear the cache and make a request from Magento, it will see that you are at version 0.1.0 in the core_resource table, but 0.1.1 in the module. So it runs the upgrade script from 0.1.0 to 0.1.1.
Some things to note:
- You can put anything into the install scripts. You can run SQL or any kind of php code. Therefore, you can use the setup scripts to move data around; not just make schema changes.
- There are other ways to manipulate databases rather than using SQL: http://stackoverflow.com/questions/4315660/alter-table-in-magento-setup-script-without-using-sql
- If you have a high traffic site, there is a possibility that the setup scripts will be run multiple times. For instance, if you clear the cache and Request 1 comes in and kicks off the setup script (that for example’s purposes takes 5 seconds), and a second later Request 2 comes in, it will see that the core_resource is still at the old version number and try to run the setup scripts again. Magento’s official recommendation is to shut down the frontend when you release a new module version and run the scripts manually by clearing cache and hitting the backend.
Alrighty, go change your database schema now, and do it through scripts!
Post from codegento.com