Hello,
In the last issue, we have looked into part 1 of Lesson 5 in Magento Tutorial series and know how to use Module Creator to create Magento modules.
Next, we’ll want to create our installer Magento script. This is the script that will contain any CREATE TABLE or other SQL code that needs to be run to initialize our module.
This Magento Open Course lesson will help you create the database for module, update database for module by…
Creating our Installer Magento Script
First, take a look at your config.xml file
Eg: app/code/local/Magestore/Lesson05/etc/config.xml
<modules> <Magestore_Lesson05> <version>0.1.0</version> </Magestore_Lesson05> </modules>
and
<global> <resources> <lesson05_setup> <setup> <module>Magestore_Lesson05</module> </setup> <connection> <use>core_setup</use> </connection> </lesson05_setup> <lesson05_write> <connection> <use>core_write</use> </connection> </lesson05_write> <lesson05_read> <connection> <use>core_read</use> </connection> </lesson05_read> </resources> </global>
This section is required in all config.xml files, and identifies the module as well as the its version number. Your installer magento script’s name will be based on this version number. The following assumes the current version of your module is 0.1.0. Define the resource setup for module, connect to database with read/write database.
Create the following file at the following location
File: app/code/local/Magestore/Lesson05/sql/lesson05_setup/mysql4-install-0.1.0.php
Add the following code to your setup script.
$installer = $this;
$installer->startSetup();
/**
* create lesson05 table
*/
$installer->run("
DROP TABLE IF EXISTS {$this->getTable('lesson05')};
CREATE TABLE {$this->getTable('lesson05')} (
`lesson05_id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`content` text NOT NULL default '',
`status` smallint(6) NOT NULL default '0',
PRIMARY KEY (`lesson05_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `{$this->getTable('lesson05')}` VALUES (1,'Magento Course','Hello, I am Michael from Magestore.com',1);
");
$installer->endSetup();
Also, you can use some functions that Magento support to work with database:
- addColumn($name, $type, $size = null, $options = array(), $comment = null)
- $name: the name of a field (column)
- $type: the type of data. For instance: TYPE_BOOLEAN, TYPE_SMALLINT, TYPE_INTEGER…
- $size: the size of a field (0, 255, 2M,…)
- $option: identity (true/false), nullable(true/false), primary (true/false), default (value)
- $comment: add comments to the field
- addForeignKey($fkName, $column, $refTable, $refColumn, $onDelete = null, $onUpdate = null)
- $fkName: the name of the foreign key
- $column: the name of the field (column) set into the foreign key
- $ refTable: the name of the reference table
- $ refColumn: the name of the column table
- $onDelete: identify an action needs to be implemented when the data of the reference table is deleted (ACTION_CASCADE, ACTION_SET_NULL, ACTION_NO_ACTION,ACTION_RESTRICT, CTION_SET_DEFAULT)
- $onUpdate: : identify an action needs to be implemented when the data of the reference table is updated (ACTION_CASCADE,ACTION_SET_NULL, ACTION_NO_ACTION, ACTION_RESTRICT, ACTION_SET_DEFAULT)
- addIndex($indexName, $fields, $options = array())
- $ indexName: the name of index
- $fields: the name/ array of field which is set into index (array or string)
Example:
$installer = $this;
/**
* create lesson05 table
*/
$table = $installer->getConnection()
->newTable($installer->getTable('lesson05'))
->addColumn('lesson05_id', Varien_Db_Ddl_Table::TYPE_INT, 11, array(
'identity' => true,
'nullable' => false,
'primary' => true,
), 'Lesson05 ID')
->addColumn('title', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(
'nullable' => false,
), 'Lesson05 Title')
->addColumn('content', Varien_Db_Ddl_Table::TYPE_TEXT, '2M', array(
), 'Lesson05 Content')
->addColumn('status', Varien_Db_Ddl_Table::TYPE_SMALLINT, 6, array(
'nullable' => false,
'default' => '0',
), 'Status')
->setComment('Lesson05 Table');
$installer->getConnection()->createTable($table);
The lesson05_setup portion of the path should match the tag you created in your config.xml file (<lesson05_setup />). The 0.1.0 portion of the filename should match the starting version of your module.
Resource Versions
Magento’s Setup Resources allow you to simply drop your install scripts (and upgrade scripts, which we’ll get to in a bit) onto the server, and have the system automatically run them. This allows you to have all your database migrations scripts stored in the system in a consistent format.
Using your favorite database client, take a look at the the core_setup table
mysql> select * from core_resource; +-------------------------+------------+--------------+ | code | version | data_version | +-------------------------+------------+--------------+ | adminnotification_setup | 1.6.0.0 | 1.6.0.0 | | admin_setup | 1.6.1.0 | 1.6.1.0 | | api2_setup | 1.0.0.0 | 1.0.0.0 | | api_setup | 1.6.0.0 | 1.6.0.0 | | backup_setup | 1.6.0.0 | 1.6.0.0 | | bundle_setup | 1.6.0.0.1 | 1.6.0.0.1 | | captcha_setup | 1.7.0.0.0 | 1.7.0.0.0 | | catalogindex_setup | 1.6.0.0 | 1.6.0.0 | | cataloginventory_setup | 1.6.0.0.2 | 1.6.0.0.2 | | catalogrule_setup | 1.6.0.3 | 1.6.0.3 | | catalogsearch_setup | 1.6.0.0 | 1.6.0.0 | | catalog_setup | 1.6.0.0.14 | 1.6.0.0.14 | | checkout_setup | 1.6.0.0 | 1.6.0.0 | | cms_setup | 1.6.0.0.1 | 1.6.0.0.1 | | compiler_setup | 1.6.0.0 | 1.6.0.0 | | contacts_setup | 1.6.0.0 | 1.6.0.0 | | core_setup | 1.6.0.2 | 1.6.0.2 | | cron_setup | 1.6.0.0 | 1.6.0.0 | | customer_setup | 1.6.2.0.1 | 1.6.2.0.1 | | dataflow_setup | 1.6.0.0 | 1.6.0.0 | | directory_setup | 1.6.0.1 | 1.6.0.1 | | downloadable_setup | 1.6.0.0.2 | 1.6.0.0.2 | | eav_setup | 1.6.0.0 | 1.6.0.0 | | giftmessage_setup | 1.6.0.0 | 1.6.0.0 | | googleanalytics_setup | 0.1.0 | 0.1.0 | | googlecheckout_setup | 1.6.0.1 | 1.6.0.1 | | importexport_setup | 1.6.0.2 | 1.6.0.2 | | index_setup | 1.6.0.0 | 1.6.0.0 | | lesson05_setup | 0.1.0 | 0.1.0 | | log_setup | 1.6.0.0 | 1.6.0.0 | | moneybookers_setup | 1.6.0.0 | 1.6.0.0 | | newsletter_setup | 1.6.0.1 | 1.6.0.1 | | oauth_setup | 1.0.0.0 | 1.0.0.0 | | paygate_setup | 1.6.0.0 | 1.6.0.0 | | payment_setup | 1.6.0.0 | 1.6.0.0 | | paypaluk_setup | 1.6.0.0 | 1.6.0.0 | | paypal_setup | 1.6.0.2 | 1.6.0.2 | | persistent_setup | 1.0.0.0 | 1.0.0.0 | | poll_setup | 1.6.0.0 | 1.6.0.0 | | productalert_setup | 1.6.0.0 | 1.6.0.0 | | rating_setup | 1.6.0.0 | 1.6.0.0 | | reports_setup | 1.6.0.0.1 | 1.6.0.0.1 | | review_setup | 1.6.0.0 | 1.6.0.0 | | salesrule_setup | 1.6.0.3 | 1.6.0.3 | | sales_setup | 1.6.0.7 | 1.6.0.7 | | sendfriend_setup | 1.6.0.0 | 1.6.0.0 | | shipping_setup | 1.6.0.0 | 1.6.0.0 | | sitemap_setup | 1.6.0.0 | 1.6.0.0 | | tag_setup | 1.6.0.0 | 1.6.0.0 | | tax_setup | 1.6.0.3 | 1.6.0.3 | | usa_setup | 1.6.0.1 | 1.6.0.1 | | weee_setup | 1.6.0.0 | 1.6.0.0 | | widget_setup | 1.6.0.0 | 1.6.0.0 | | wishlist_setup | 1.6.0.0 | 1.6.0.0 | | xmlconnect_setup | 1.6.0.0 | 1.6.0.0 | +-------------------------+------------+--------------+ 55 rows in set (0.00 sec)
This table contains a list of all the installed modules, along with the installed version number. You can see our module near the end
| lesson05_setup | 0.1.0 | 0.1.0 |
This is how Magento knows not to re-run your script on the second, and on all successive, page loads. The lesson05_setup is already installed, so it won’t be updated. If you want to re-run your installer script (useful when you’re developing), just delete the row for your module from this table. Let’s do that now, and actually add the SQL to create our table. So first, run the following SQL.
Anatomy of a Setup Script
So, let’s go over the script line-by-line. First, there’s this (or is that $this?)
$installer = $this;
Each installer script is run from the context of a Setup Resource class, the class you created above. That means any reference to $this from within the script will be a reference to an object instantiated from this class. While not necessary, most setup scripts in the core modules will alias $this to a variable called installer, which is what we’ve done here. While not necessary, it is the convention and it’s always best to follow the convention unless you have a good reason for breaking it.
Next, you’ll see our queries are bookended by the following two method calls.
$installer->startSetup(); //... $installer->endSetup();
If you take a look at the Mage_Core_Model_Resource_Setup class in app/code/core/Mage/Core/Model/Resource/Setup.php (which your setup class inherits from) you can see that these methods do some basic SQL setup
public function startSetup()
{
$this->getConnection()->startSetup()
return $this;
}
public function endSetup()
{
$this->getConnection()->endSetup();
return $this;
}
Look can into Varien_Db_Adapter_Pdo_Mysql to find the real SQL setup executed for MySQL connections in the startSetup() and endSetup() methods.
Finally, there’s the call to the run method
$installer->run(...);
which accepts a string containing the SQL needed to setup your database table(s). You may specify any number of queries, separated by a semi-colon. You also probably noticed the following
$this->getTable('lesson05')
The getTable method allows you to pass in a Magento Model URI and get its table name. While not necessary, using this method ensures that your script will continue to run, even if someone changes the name of their table in the config file. The Mage_Core_Model_Resource_Setup class contains many useful helper methods like this. The best way to become familiar with everything that’s possible is to study the installer scripts used by the core Magento modules.
Upgrade Module
So, that’s how you create a script that will setup your initial database tables, but what if you want to alter the structure of an existing module? Magento’s Setup Resources support a simple versioning scheme that will let you automatically run scripts to upgrade modules.
Once Magento runs an installer script for a module, it will never run another installer for that module again (short of manually deleting the reference in the core_resource table). Instead, you’ll need to create an upgrade script. Upgrade scripts are very similar to installer scripts, with a few key differences.
To get started, we’ll create a script at the following location, with the following contents
File: app/code/local/Magestore/Lesson05/sql/lesson05_setup/upgrade-0.1.0-0.2.0.php:
DROP TABLE IF EXISTS {$this->getTable('lesson05_upgrade')};
CREATE TABLE {$this->getTable('lesson05_upgrade'')} (
`lesson05_upgrade_id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`content` text NOT NULL default '',
`status` smallint(6) NOT NULL default '0',
PRIMARY KEY (`lesson05_upgrade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Upgrade scripts are placed in the same folder as your installer script, but named slightly differently. First, and most obviously, the file name contains the word upgrade. Secondly, you’ll notice there are two version numbers, separated by a “-“. The first (0.1.0) is the module version that we’re upgrading from. The second (0.2.0) is the module version we’re upgrading to.
Notice: the naming for sql file when update database: if you want to update database from version 0.1.0 to version 0.2.0, the naming file is:
“upgrade” + ”-” + “0.1.0” + “-” + “0.2.0” = upgrade-0.1.0-0.2.0
If we cleared our Magento cache and reloaded a page, our script wouldn’t run. We need to update the the version number in our module’s config.xml file to trigger the upgrade
<modules> <Magestore_Lesson05> <version>0.2.0</version> </Magestore_Lesson05> </modules>
With the new version number in place, we’ll need to clear our Magento cache and load any page in our Magento site. You should now see output from your upgrade script.
By the way, we also could have names our upgrade script mysql4-upgrade-0.1.0-0.2.0.php. This would indicate our upgrade would contain MySQL specific SQL.
mysql> select * from core_resource where code = lesson05_setup'; +--------------+---------+--------------+ | code | version | data_version | +--------------+---------+--------------+ |lesson05_setup| 0.2.0 | 0.2.0 | +--------------+---------+--------------+ 1 row in set (0.00 sec)
Here’s what happened
- The lesson05_setup resource was at version 0.1.0
- We upgraded our module to version 0.2.0
- Magento saw the upgraded module, and saw there were two upgrade scripts to run; 0.1.0 to 0.2.0
- Magento queued up both scripts to run
- Magento ran the 0.1.0 to 0.2.0 script
- The lesson05_setup resource is now at version 0.2.0
The correct way to achieve what we wanted would have been to name our scripts as follows
upgrade-0.1.0-0.2.0.php #This goes 0.1.0 to 0.2.0
To summary, after learn this lesson, we can answer 2 questions:
– How to create a new Magento Modules by using Module Creator.
– How to create Installer Magento Script:
- Create database for module
- Upgrade database for module
Let’s take a look at previous issues of Magestore’s Magento Open Course:
– Lesson 1: Installing Magento with Sample Data on Localhost (Part 1 & Part 2)
– Lesson 2: Magento Architecture
– Lesson 3: Folder Structure and Database System in Magento
– Lesson 4: Configuration and naming in Magento
– Lesson 5.1: Create a Magento Module
Don’t hesitate to let us know how you have keep up with the series.
How far do you follow up our lessons? Is there any lesson that is hard for you? Any question?
Bye for now and see you again in the next lessons of Magento Open Course series.
2 Comments
I created magento script then what we have to do?
run that php script?
I cant see my core_setup/core-resource on my magento folder.
I’m stuck at that procedure.Can u please give me more detailed explaination?
Im using magento 2.2.2 now and im new to magento
Hi Deva,
Sorry for my late reply, just follow these steps: https://www.magestore.com/magento-2-tutorial/how-to-create-and-upgrade-database-in-magento-2/
Cheers,