Creating and Updating Database Tables

You may need to have some tables in the database for storing the module specific information. LemonStand has a built-in feature which allows modules to create or update database objects. You should use this approach instead of direct database modifications (for example using third-party tools like PHP My Admin), especially if you are going to share your modules with other LemonStand users. However you can use third-party tools for preparing SQL queries.

As it is described in the Developing a simple module article, each module should have the updates directory, which must contain a version information file (version.dat). When LemonStand applies module updates (reads the version information file), it looks to the updates directory for SQL and PHP files with names that match the version numbers (module build numbers) declared in the version.dat file. Imagine that you have the following version information file content:

#1 Module initialization
#2 Created some tables

When LemonStand reads this file, it tries to find and execute the 1.sql, 1.php, 2.sql and 2.php files in the updates directory. If there are no any update files for some version number, LemonStand just goes to a next record in the version.dat file. Thus, if you need to create a table in the database, you can create an SQL file and put the SQL create table query into it. 

Important. Never change version numbers (in version.dat file) for versions which have already been published. Failure to follow this rule can confuse LemonStand update manager and result in inability to update the application.
Version number and version description should be delimited with a space. Tabulation symbol is not allowed. 

SQL and PHP update files

Update files can have the sql and php extensions. You can place as many SQL queries as you need into SQL update files. The queries must be separated with the semicolon symbol. Each query should start from a new line. Example of a valid SQL update file:

alter table shop_tax_classes add column is_default tinyint;
update shop_tax_classes set is_default=1 where name='Product';

Usually you will not need to use PHP update files for updating the database structure or content. PHP update files are useful when you need to perform some update which are not possible with plain SQL queries. For example, before executing some query you may need to check some condition, like whether a record with some identifier already exists in the database. PHP update files can contain any PHP code. You can execute SQL queries from PHP update scripts using the Db_DbHelper class, which is described in the Working with the database article. Example of a valid PHP update file:

<?
  $states = array(
    'Northern Territory'=>'AU-NT',
    'Australian Capital Territory'=>'AU-ACT'
  );

  $country_id = Db_DbHelper::scalar('select id from shop_countries where code_3=:code_3', array('code_3'=>'AUS'));
  if ($country_id)
  {
    foreach ($states as $state_name=>$state_code)
    {
      $states_cnt = Db_DbHelper::scalar('select count(*) 
        from shop_states 
        where country_id=:country_id 
        and code=:state_code', 
        array('country_id'=>$country_id, 'state_code'=>$state_code));

      if (!$states_cnt)
      {
        Db_DbHelper::query('insert into shop_states(country_id, code, name) values (:country_id, :code, :name)', array(
          'country_id'=>$country_id,
          'code'=>$state_code,
          'name'=>$state_name
        ));
      }
    }
  }
?>

This is a real update script which we used for adding two Australian territories, which we forgot to add in previous updates, to the database. It finds the Australia country identifier by its ISO code (AUS). After that it checks whether each of two territories has not been added before, because we assume that the territories could be added manually by LemonStand users. Then the script executes the SQl INSERT query.

If LemonStand finds that there are both types of update files - SQL and PHP for a same version number (for example 3.sql and 3.php) it first executes the SQL update file.

If you work on a shared module, you should remember that the database content is not static and it could be changed between your module updates. You should perform required checks in order to prevent any possible data damages.

Creating tables for the blog module

In the Developing a simple module article we started developing a simple blog module. In this article we will add a posts table, which will contain blog posts. First, we need to add a version line the version.dat file. After adding the line the file should have the following content:

#1 Module initialization
#2 Created the blog posts database table

Also we need to create a SQL update file which will add the table. The file name should be 2.sql and the file should be placed to the updates module directory:

Our posts table have the following data columns: title, description, content. Also there are some service fields - the record identifier (id) field and fields for storing the record update information, which LemonStand can update automatically. We prepared the CREATE SQL query in the CocoaMySQL tool, which is a free desktop application for Mac OS. Content of the 2.sql file:

CREATE TABLE `abcblog_posts` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `description` text,
  `content` text,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  `created_user_id` int(11) default NULL,
  `updated_user_id` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Please note that the table name has the abcblog_ prefix. This is required by the LemonStand naming conventions described in the Developing LemonStand modules article. Also you should always use auto-increment identifiers with the name id for your tables.

The primary key column should always be named id. Tables should use the utf8 charset and the MyISAM engine.

After saving the SQL update file, you can log out and log in to the Administration Area in order LemonStand to apply updates. You can check your database and make sure that the table has been added.

Next: Creating Data Relations
Previous: Working with the Database
Return to Working with the Database