ADOdb Data Dictionary

CMS Made Simple (CMSMS) uses ADOdb for database abstraction. This is a good thing from my point of view as we use ADOdb at work so I don’t have any paradigm shifts to worry about. When writing my modules for CMSMS I discovered ADOdb’s data dictionary system. This allows you to create and edit tables in a cross-database manner. For instance:

		
$db = $this->cms->db; /* @var $db ADOConnection */
$dict = NewDataDictionary($db);  /* @var $dict ADODB_DataDict */
	
$table_options = array('mysql' => 'TYPE=MyISAM');

// create categories table
$fields = "
    category_id I KEY,
    category_name C(255),
    category_order I";

$sql_array = $dict->CreateTableSQL($this->categories_table_name, 
               $fields, $table_options);
$dict->ExecuteSQLArray($sql_array);
$db->CreateSequence($this->categories_table_name . '_seq');

This code creates a simple table for storing categories in any supported database. CMSMS uses it so that a module can create it’s own tables in Install() and then remove them again in Uninstall(). There is also a function Upgrade() which I’d never used before (having got my DB tables right first time!)

Whilst developing the Calendar module, I had a need to alter the database table structure that held an event. A feature request came in asking for “from” and “to” dates for each event rather than just a single date.”Aha!” thought I; a chance to play with some more data dictionary functions. (Actually, I wondered if I could just require users to dump their data as it’s only a 0.2 release, then I relented!)

Having decided to manipulate the table, I worked out that I needed to add a new timestamp field called event_date_end and rename my event_date field to event_date_start. I wandered over to the ADOdb Data Dictionary manual to find out what I could do. As with the main documentation, it’s very well written and easy to understand (with examples!). I quickly discovered RenameColumnSQL() and AddColumnSQL() were what I wanted.

Thus the Upgrade code is:

function Upgrade($oldversion, $newversion)
{
  // 0.3 introduces new event_date_start and event_date_end
  $db = $this->cms->db; /* @var $db ADOConnection */
  $dict = NewDataDictionary($db); /* @var $dict ADODB_DataDict */

  if(version_compare($oldversion, 0.3, "< "))
  {
    // this is version 0.2 or 0.1
    $sqlarray = $dict->RenameColumnSQL($this->events_table_name,
                  "event_date", "event_date_start", 
                  "event_date_start T");
    $dict->ExecuteSQLArray($sqlarray);
    $sqlarray = $dict->AddColumnSQL($this->events_table_name, 
                  "event_date_end T");
    $dict->ExecuteSQLArray($sqlarray);			

    // ensure that the new field has valid data in it.
    $sql = "UPDATE {$this->events_table_name} 
            SET event_date_end = event_date_start";
    $db->Execute($sql);
  }		
}

How easy is that?