Magento: Upgrading mysql setup of module

Suppose, you are working on a module, and you need to:

* Add a new table, or
* Modify current tables, or
* Insert some default data in existing table, or
* Add/ modify some attributes

In every case, either you will
1. goto core_resource table
2. delete module's entry
3. and delete current tables of module

which is a poor solution, because sometimes, you don't have direct access to database, so you can't delete/ modify data in db in that case.
There is a simple solution for this. You can do it by simply upgrading you module version and write a new mysql upgrade file.


Suppose, you have a module named Mymodule. Its version is 0.1.1. You have the mysql setup file (mysql install file) mysql4-install-0.1.1.php in Mymodule/sql/mymodule_setup folder of your module.

You don’t need to make direct changes to database. To fulfill any of the above tasks in your module,


1) First create a new mysql upgrade file inside Mymodule/sql/mymodule_setup folder.

Let us suppose that you are going to change the version of your module from 0.1.1 to 0.1.2.

The name of mysql upgrade file should be mysql4-upgrade-0.1.1-0.1.2.php

2) Write necessary sql statements in this mysql upgrade file.

3) Now, you have to change the version in config.xml in Mymodule/etc folder as well.

Change the version like 0.1.2. Which is the new version for our module.

4) Reload your site. And you are done!


Note: The upgrade format is like mysql4-upgrade-CURRENT_VERSION-UPGRADED_VERSION.php


Enjoy.. :)