Despatch Date Module – part 4: Setting up the database

This is the fourth post of a series to build a module for a Despatch Date field on the Order View page in Magento Admin. In this post we’ll add a column to a table in the database where we can store the despatch date for the customer’s order. In the next post, we’ll put data in it with our controller class, and in subsequent posts, we’ll see how we can get the data out. 

We’re going to be extending our module, creating two new files and updating config.xml, all shown in red. We already have Blessthemoon_Despatchdate.xml shown in green.

This post will be shorter than the last (I think). We will add a column to an existing table for our despatch date field. We don’t have to create model or a resource model. We’ll instantiate an existing model.

As an aside all models are instantiated thus: $model = Mage::getModel(‘modulename/modelname’) where ‘modulename/modelname’ indicates the class that we are instantiating. For example, in this post we’ll instantiate an existing model to get our data. Our call will look like this: $orderModel = Mage::getModel(‘sales/order’). This will instantiate the class Mage_Sales_Model_Order which lives in the file app/code/core/Mage/Sales/Model/Order.php. Then with setters, save and getters and a host of other methods we can interact with our data. Before moving on with this exercise, let’s take a look at what would be involved if we wanted a new table rather than a new column on an existing table. If you’d rather not, skip the next heading ‘How Magento adds a table’ and move to ‘Adding a column to a table in the database’.

How Magento adds a table

In another scenario we might want to create a new table. In which case, we’d need as a minimum to update our module’s config.xml to identify our model and resource model classes, and identify the table entity that the resource model will use. And we’d need to set up these classes in php files in the model directory of our module. It’s interesting to see how this is done for the existing Sales Order Model that our controller will access (at least interesting to me). Take a look at app/code/core/Mage/Sales/etc/config.xml.

This xml illustrates the xml we’d need to code ourselves if we were creating a new table in the database. Between the <sales></sales> tags, Magento identifies the model’s classes with the model class prefix in line 908: Mage_Sales_Model. (By the way, the model we’ll need to access with our controller is Mage_Sales_Model_Order as mentioned earlier.)

On line 909, the resource model is identified that Mage_Sales_Model should use: ‘sales_resource’, and the class for sales_resource is identified in the <sales_resource></sales_resource> tags on line 938: Mage_Sales_Model_Resource. Between the <entities></entities> tags, the required tables are identified. Were we specifying a new table in our database, then we’d specify it in our module’s config.xml like we see here. In fact the table that we will add our column to for the despatch date field is the sales_flat_order table as identified in the <order></order> tags on line 963 (for the Mage_Sales_Model_Order class). For a good explanation of why we have a resource model as well as a model see Alan Storm’s Magento for Developers: Part 4 – Magento Layouts, Blocks and Templates. Briefly though, for the most part, we talk to the model, the model talks to the resource model and the resource model talks to the database.

Don’t worry about the <resources></resources> tags, we’ll come back to those.

If we wanted to to create a new table then, in addition to the xml for our module’s config, we would need to set up the classes. Again, we can take a look at the classes used with ‘sales/order’ to see what would be involved. We can see in app/code/core/Mage/Sales/Model/Order.php what we’d need as a bare minimum for our model class Mage_Sales_Model_Order:

Note that the class Mage_Sales_Model_Order extends Mage_Sales_Model_Abstract. In turn, Mage_Sales_Model_Abstract extends Mage_Core_Model_Abstract. It’s Mage_Core_Model_Abstract that ensures that ‘_construct’ with its ‘_init’ method in this class is called automatically. (Note that ‘_construct’ is not to be confused with the PHP constructor ‘__construct’. However, it’s the constructor ‘__construct’ that will ensure that ‘_construct’ is called. I found this explanation from Benesch helpful.)

OK if we wanted to create a table in the database, we’d also need a resource class. Again, we’ll see how this is achieved for the Sales Order Model. We can see from config.xml (line 938) that the file we’re looking for is in the core Sales module in the app/core/code/Mage/Sales/Model/Resource directory. It’s Order.php. Here’s the code:

Again it’s the init method that automatically runs. The first parameter identifies the model and the second the primary key for the table sales_flat_order we identified in line 963 of config.xml. (There’s more than a hundred and thirty columns in the sales_flat_order table. As I mentioned earlier we’re going to be adding another in this post.)

Well that’s the config and classes set up and the model almost ready for use. Of course Magento had to actually create the table in the database, and it did this using an install SQL script that it ran the first time it found the script. Install had to be configured, and we can see how in config.xml above, between the <resources></resources> tags, lines 1172 to 1179. There a couple of important things to note here. There’s the <sales_setup> tag, which identifies the location of the install script in the Sales module (and later the location of upgrade scripts if supplied): /app/code/core/Mage/Sales/sql/sales_setup/. And there’s the name of the setup class Mage_Sales_Model_Resource_Setup which exists in /app/code/core/Mage/Sales/Model/Resource/Setup.php. We won’t take a look at the SQL script and the Setup class in this post for the table that Magento needs for this module (sales_flat_order), but please look in these files if interested. We’ll see our own setup class and SQL script in action next, as we install a new column in the sales_flat_order table.

Adding a column to a table in the database

Firstly, we’ll need to add some xml to our module’s config shown in red:

This xml identifies the setup class and the location of our SQL install script. Here’s the setup class we need in our module in Setup.php the ‘Model/Resource’ directory:

Not much to it! The important thing here is that we’ve extended the Mage_Sales_Model_Resource_Setup class which provides us with the methods we need in our install script including startSetup() and endSetup().

OK we also need to create the SQL script in our module’s ‘sql/despatchdate-Setup/’ directory with the name install-0.1.0.php. We get the version 0.1.0 from the version number of the module in the config file. They must be the same. And finally, here’s the script we need:

This short script does all the work. The $this references the object instantiated from
Blessthemoon_Despatchdate_Resource_Setup. By way of convention, we set $installer = $this, and use $installer in subsequent code. The addAttribute method takes 3 arguments: the attribute set (or table), the attribute to be added, and an array which takes values describing the attribute such as its type and whether it is required. In our case, we’ve supplied ‘order’ as the attribute set, ‘estdate’ (estimated despatch date) as the attribute name, and within the array, ‘text’ as the attribute type.

With config updated and with our Setup class and script in place, we can load any page of our site and behind the scenes the new column will be created in the sales_flat_order table. Go into phpMyAdmin and you should see the newly created column.

The script will be run only once. As you can see in the image below, Magento has stored a record of the install in the core_resource table (marked with ‘x’) and will check here before running any install (or upgrade) scripts. Of course if we want to do a rerun, we could delete the record in core_resource and delete the column in sales_flat_order.

Upgrade scripts

To run an upgrade script for our module, we increment the module version in config.xml from 0.1.0 to 0.2.0 and name our new script ‘upgrade-0.1.0-0.2.0.php’. The new script is stored in the despatchdate_setup directory along with the old one. Refresh any page and the script is run, and again, a record of the upgrade is stored in the core_setup table. For more information on upgrades, I’d refer you to Magento for Developers: Part 6 – Magento Setup Resources by Alan Storm.

And from here?

We have a column where we can store despatch date, and we’re now ready to build our controller. We’ll do this in the next post.