Managing Table Structure

Database tables and records can be managed easily using a built-in feature which allows modules to create, update or delete database objects. These updates are applied along with the corresponding version information found in a module version info file (version.dat). You can read more about version information in the Creating a Module article.

When PHPR reads the version info file it will execute any logic found in a file name that matches the version number inside the updates directory. This is optional, if no corresponding file is found, PHPR will move to the next file until it reaches the most recent version.

For example the module updates directory may look like this:

  • updates
    • 1.sql
    • 2.php
    • version.dat

The contents of version.dat:

#1 First version
#2 Removes posts from Charles

The execution order is determined by the version number, meaning the first version will execute the file named 1.sql, then the second version will execute the file named 2.php and so on. As you can see above, update files can have the php or sql extension, this gives you the option of explicit SQL or using PHP logic to update the database.

If PHPR finds both extensions for the same version number (eg: 2.php and 2.sql) it will execute the SQL first, then the PHP file execute second.

SQL Update Files

Inside files with the sql extension, you can you as many SQL queries as you need. The queries must be separated with a semicolon and a newline character.

As an example of an SQL file, let's take a look at the contents of 1.sql from the above file structure:

CREATE TABLE `bossblog_posts` (
  `id` int(11) NOT NULL auto_increment,
  `author_id` int(11) default NULL,
  `title` varchar(255) default NULL,
  `url_title` varchar(255) default NULL,
  `description` text,
  `content` text,
  PRIMARY KEY  (`id`),
  KEY `author_id` (`author_id`),
  KEY `url_title` (`url_title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

This example will create a table for storing blog posts, it is the most common way to update and initialize the database.

PHP Update Files

Update files with the php extension can contain any PHP code. They are useful when you have to perform an update which is not possible using a standard SQL query. You may need to check a condition, like if a user exists, before running the update.

An example can be found in the contents of 2.php following on from the above example:

// Find user with first name: Charles
$user = User::create()->where('first_name=?', 'Charles')->find();

// Unable to find Charles, abort
if (!$user)
	return;

// Find posts belonging to Charles
$posts = BossBlog_Post::create()->where('author_id=?', $user->id)->find_all();

foreach ($posts as $post)
{
	// Delete each post
	$post->delete();
}

This update script looks for a user with the first name "Charlie" and removes any blog posts that belong to him.

Naming Standard

As described in the Module Basics article, every table in the database should follow a naming convention where each table name has a prefix of the module name. For example the blog posts table has the prefix bossblog_ because it belongs to the bossblog module.

In addition to this you should always use auto-increment identifiers with the name id for your tables.