Jamroom Module Guide: schema.php

If your module is going to work with the Jamroom Quotas, or is going to create a Jamroom database table (or tables) of its own, then you will need to provide a schema file for your module.  The schema file tells Jamroom the layout of your database table, which in turn allows your module table(s) to be validated when a Jamroom Integrity Check is run.

The schema.php file for our jrYouTube module looks like this:

<?php
/**
 * Jamroom jrYouTube module Schema file
 * @copyright 2009 by Talldude Networks LLC
 * @author Brian Johnson - bigguy@jamroom.net
 */
defined('IN_JAMROOM') or exit();

// Create our You Tube videos table
$tbl = "CREATE TABLE {$jamroom_db['jrYouTubeVideos']} (
  youtube_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  youtube_band_id INT(7) UNSIGNED NOT NULL,
  youtube_video_id VARCHAR(25) NOT NULL DEFAULT '',
  youtube_video_time INT(11) UNSIGNED NOT NULL DEFAULT '0',
  youtube_video_category VARCHAR(30) NOT NULL DEFAULT '',
  INDEX youtube_band_id (youtube_band_id),
  INDEX youtube_video_time (youtube_video_time),
  INDEX youtube_video_category (youtube_video_category(15))
)";
dbVerifyTable($jamroom_db['jrYouTubeVideos'],$tbl);

// Jamroom Settings
$tbl = "
  jryoutube_base_url VARCHAR(75) NOT NULL DEFAULT ''
";
dbVerifyTable($jamroom_db['settings'],$tbl);

// Jamroom Quota
$tbl = "
  quota_jryoutube_access CHAR(3) NOT NULL DEFAULT 'no'
";
dbVerifyTable($jamroom_db['quota'],$tbl);
?>

 Again - like in our other PHP scripts we include our comment header and "IN_JAMROOM" check - this is required, so do not forget it.

The next 3 sections of code define a custom Jamroom database table, as well as custom Jamroom Settings and Jamroom Quota config entries.

Our custom Jamroom database table is defined first:

// Create our You Tube videos table
$tbl = "CREATE TABLE {$jamroom_db['jrYouTubeVideos']} (
  youtube_id INT(7) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  youtube_band_id INT(7) UNSIGNED NOT NULL,
  youtube_video_id VARCHAR(25) NOT NULL DEFAULT '',
  youtube_video_time INT(11) UNSIGNED NOT NULL DEFAULT '0',
  youtube_video_category VARCHAR(30) NOT NULL DEFAULT '',
  INDEX youtube_band_id (youtube_band_id),
  INDEX youtube_video_time (youtube_video_time),
  INDEX youtube_video_category (youtube_video_category(15))
)";
dbVerifyTable($jamroom_db['jrYouTubeVideos'],$tbl);

To create our custom database table we:

• Create a text string that is the MySQL database table definition.  This MUST be valid SQL!  Note that for our table name we use the variable we created in our include.php script - this ensures the table is created with the proper table name prefix that was selected when Jamroom was installed.
• each database column definition is on it's OWN LINE.  Do not place multiple column definitions on the same line or your table will not be created properly.
• pass our MySQL database table creation string to the Jamroom dbVerifyTable() function - this function will validate the table is created, and if it is already created, it will validate that each column and index exists.

Next, we want to add a custom Jamroom Settings column into the master jamroom_settings table - this ensures our config option (YouTube Base URL) will be available throughout Jamroom as part of the global $config array (using this field will be covered when creating the config.php module script):

// Jamroom Settings
$tbl = "
  jryoutube_base_url VARCHAR(75) NOT NULL DEFAULT ''
";
dbVerifyTable($jamroom_db['settings'],$tbl);

And finally we create our Jamroom Quota columns that will hold our yes/no setting that we defined in our quota.php script:

// and our option in the Jamroom Quota
$tbl = "
  quota_jryoutube_access CHAR(3) NOT NULL DEFAULT 'no'
";
dbVerifyTable($jamroom_db['quota'],$tbl);

Note how in these last 2 schema definitions we do NOT add the "CREATE TABLE" part of the SQL - we simply define each column as needed.  This is important, and ensures that if something happens to corrupt or delete the Jamroom database (such as a hard drive crash), our module will not attempt to create the Jamroom Settings or Quota tables, and instead the Jamroom Core will handle it.

That's it for the scheme.php script - if we had wanted to we could have created any number of different tables for use by our module, or added additional fields to other (existing) Jamroom tables simply by using the same mechanism that we used to add a field to the Jamroom Quota table.