I struggled a lot to find a method to install a simple database script in Magento for creating a table or upgrading field or column. In the end I thought why not write one for reference. This post is mainly for my reference 😉
Initially you need to create a small extension to install the db script. You can also use the existing one if you want. But i have written a small extension just for the purpose of adding and updating database tables.
Below is the step by step procedure for executing Database queries
1) I have created a small extension named database. Create a file in “app/etc/modules” named “Greeting_Database.xml”.
The content of the file will be.
<?xml version="1.0"?> <config> <modules> <Greeting_Database> <active>true</active> <codePool>local</codePool> </Greeting_Database> </modules> </config>
This is just a basic file to let Magento know you have a module named Database. Greeting is the Company name of the module.
2) Create the “config.xml” file in “app/code/local/Greeting/Database/etc/config.xml”. The content of the file will be as follows
<?xml version="1.0"?> <config> <modules> <Greeting_Database> <version>0.1.0</version> <!-- This is an important section to note. It is the version of the module --> </Greeting_Database> </modules> <global> <models> <database> <class>Greeting_Database_Model</class> <!-- It defines a location of Model folder. Please create an empty folder name Model at app/code/local/Greeting/Database/Model --> <resourceModel>database_mysql4</resourceModel> <!-- It defines our resourceModel used to execute db queries --> </database> <database_mysql4> <!-- This is the name of the resourceModel defined above --> <class>Greeting_Database_mysql4_Model_Mysql4</class> </database_mysql4> </models> <resources> <!-- declare all database operation over here --> <database_setup> <!-- Database setup operation is done over here --> <setup> <module>Greeting_Database</module> </setup> <connection> <use>core_setup</use> </connection> </database_setup> <database_write> <!-- It gives the write permission to db --> <connection> <use>core_write</use> </connection> </database_write> <database_read> <!-- It gives the read permission from db --> <connection> <use>core_read</use> </connection> </database_read> </resources> </global> </config>
I am considering, you have a basic knowledge of module creation. I have added the required comment along with the code. You can understand what is does from there.
Please Note : There might be some mistake in comments so while trying it please remove all the comments
3) I am assuming, you have already created “Model” folder as stated in above comment. Next you need to create a folder named “sql” in your module at “app/code/local/Greeting/Database”.
4) Inside it create another folder named “database_setup”. This should be of the exact name of the one defined in config.xml
5) Now create a file named “mysql4-install-0.1.0.php” in the folder database_setup. The name of the file should be exact of what I stated. It should not change else the script wont execute.
6) You can now put your database query in there. The sample code is as below:
$installer = $this; $installer->startSetup(); $installer->run(" CREATE TABLE IF NOT EXISTS sampletable2 ( recid int(11) NOT NULL auto_increment, name varchar(100), surname varchar(100), date datetime default NULL, timestamp timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`recid`)) "); $installer->endSetup();
7) Thats it, Goto frontend of the website and reload it. Now, check your database and see the table will be created.
8) Now, suppose you need to upgrade the database table or say add a new one using the same module. So what we can do is create an upgrade script. To create an upgrade script you need to follow below steps.
a) Considering our install script name was “mysql4-install-0.1.0.php” the ugrade script will be named as “upgrade-0.1.0-0.1.1”. Here we have increment the version number in upgrade script. Thus database will be considering 0.1.1 as a new script. Below is an example of upgrade script file for you to test.
$installer = $this; $installer->startSetup(); $installer->run(" alter table sampletable2 drop column name; "); $installer->endSetup();
b) Next after creating an upgrade script, you need to goto your config.xml file and change the version of the module same as the upgrade script ie: in our case it should be
Now reload you website and your upgrade script should run. To check if your module has ran, you can check the entry of your module with version number in database table “core_resource”
Please Note: Your script will not run if you make any mistake in the upgrade script name and version number. the upgrade script version and version in config.xml should be same.
You can create as many upgrade script as you want. you just need to create new file and name it accordingly.
Thats it, Please let me know your views on it by commenting.