Most open source software forum have database setup scripts that are simply a set of SQL instructions that you can inject directly into your database (typically mysql) to get going. Vanilla 1 was no exception to this rule. The benefit of this approach is that you can quickly and easily get your app up and running. The downside to this approach is that when it is time to upgrade the application to a newer version, you have to write extremely convoluted scripts to figure out what application version the user currently user has, or write scripts to examine database table structure to see what fields need to be removed, or what fields need to be added. Hell, it’s even difficult to understand what I just wrote, let alone the code that would go along with it.
The Database Structure Class
Garden does not have any SQL install or update scripts. Instead, every application in garden has a structure.php file which can be used by either the Garden installer, or by the application management screen when enabling that application. The structure.php file contains a set of instructions on how to build the structure of the database. This file uses the DatabaseStructure class to define the structure of the database.
The class itself is extremely simple to use. Like the database class, the DatabaseStructure class uses method chaining, and it contains methods to create/drop/rename tables, create/drop/rename table columns, and create/modify views.
Tables & Columns
Each application’s structure.php file resides in that application’s “settings” folder (the same folder that contains the appliation’s about.php file). So, the structure file for Garden resides in /path/to/root/applications/garden/settings/structure.php. Let’s take a look at an example of how I create the user table in Garden:
// User Table
$Construct->Table('User')
->Column('UserID', 'int', 10, FALSE, NULL, 'primary', TRUE)
->Column('PhotoID', 'int', 8, TRUE, NULL, 'key')
->Column('Name', 'varchar', 20, FALSE, NULL, 'key')
->Column('Password', 'varchar', 32)
->Column('About', 'text', '', TRUE)
->Column('Email', 'varchar', 200)
->Column('ShowEmail', array('1', '0'), '', FALSE, '0')
->Column('Gender', array('m', 'f'), '', FALSE, 'm')
->Column('CountVisits', 'int', 8, FALSE, '0')
->Column('CountInvitations', 'int', 2, FALSE, '0')
->Column('InviteUserID', 'int', 10, TRUE)
->Column('DiscoveryText', 'text', '', TRUE)
->Column('Preferences', 'text', '', TRUE)
->Column('Permissions', 'text', '', TRUE)
->Column('Attributes', 'text', '', TRUE)
->Column('DateSetInvitations', 'datetime', '', TRUE)
->Column('DateOfBirth', 'datetime', '', TRUE)
->Column('DateFirstVisit', 'datetime', '', TRUE)
->Column('DateLastActive', 'datetime', '', TRUE)
->Column('DateInserted', 'datetime')
->Column('DateUpdated', 'datetime', '', TRUE)
->Column('HourOffset', 'int', 2, FALSE, '0')
->Set(TRUE, FALSE);
The hardest part of understanding this SQL is knowing what each of the arguments in the “Column” method are. I’ve done my best to keep these arguments similar to the way they appear when writing a column definition in SQL. Let’s take a look at all of the arguments:
public function Column(
$Name,
$Type,
$Length = '',
$Null = FALSE,
$Default = NULL,
$KeyType = FALSE,
$AutoIncrement = FALSE
) { }
Name (string):
The name of the column to create.
Type (mixed):
The data type of the column to be created. If an array of values is provided, the type will be set as “enum” and the array will be assigned as the column’s Enum property.
Length (mixed, not required, default “empty”):
The length of the column.
Null (bool, not required, default FALSE):
Does the column allow null values.
Default (mixed, not required, default NULL):
The default value of the column. If NULL is provided (default), there will be no default value.
KeyType (string, not required, default FALSE):
What type of key is this column on the table. Options are primary, key, and FALSE (not a key).
AutoIncrement (bool, not required, default FALSE):
A boolean value indicating if this column auto-increments.
Once you master the arguments for the column method, building and altering tables is a breeze.
The Set() method (used above to execute the creation of the user table) also has some important arguments:
public function Set(
$Explicit = FALSE,
$Drop = FALSE
) {}
The Set method creates the table and columns specified with $this->Table() and $this->Column(). If no table or columns have been specified, this method will throw a fatal error.
Explicit (bool, not required, default FALSE):
If TRUE, and the table specified with $this->Table() already exists, this method will remove any columns from the table that were not defined with $this->Column().
Drop (bool, not required, default FALSE):
If TRUE, and the table specified with $this->Table() already exists, this method will drop the table before attempting to re-create it.
As you can imagine, these are some pretty powerful commands that you must pay close attention to. In 90% of my structure scripts, Explicit is TRUE, and Drop is FALSE. Despite this fact, I have kept both arguments FALSE by default so that tables are not dropped and columns are not removed if a user does not know what they are doing.
As I mentioned, there are other methods available to you if you don’t want to redefine an entire table, but you do want to add or remove columns (or drop entire tables). Those are: Drop (allows you to drop a table), RenameTable, DropColumn, and RenameColumn.
Views
Using the database class, you can also quickly and easily build a query and turn it into a view. Here is an example of building a view:
$View = $Database->Select('rp.*')
->Select('p.Name', '', 'Permission')
->From('RolePermission rp')
->Join('Permission p', 'rp.PermissionID = p.PermissionID')
->GetSelect();
$Construct->View('vw_RolePermission', $View);
Benefits
Why did I bother building this class? I originally got the idea because I found that I was keeping a SQL script that I would constantly make changes to, and re-run on the database, essentially wiping out my test data every time. It got me thinking about how upgrades are difficult because of database structure changes. I ended up writing this class in a very short amount of time, and then spending a lot of time turning my rather large SQL script into a structure.php file. I was delighted to discover that changes made to my structure.php file would quickly and easily apply to the database with a quick run of the file. So, I could easily add a column to a table definition, run my structure script, and go back to happily using the application, and making use of my new column, while all of my testing data remained intact.
This means that when you release new versions of your applications, all you need to do is update your structure file (which you will likely be doing as you add features and fix bugs), and all database structure changes apply when the application is re-enabled by the user.
This class can also be used by plugins to quickly and easily add (or remove) columns from tables (or even create tables). The sky is the limit.
This class has gone far beyond my initial hopes. At this point I don’t anticipate ever writing an upgrade script for any application in Garden. Everything should be handled by the application management screen, or the initial install script that comes prepackaged with Garden.
