Database Structure - Table Definitions

Like most CMS solutions, SiteCommander configures its database when first set up. However, I wanted a solution that allowed database alterations over time, as various features were enhanced and needed related database changes. By creating these structures, I provided a means of comparing the most current structure with the existing structure in an older client deployment. In addition, the system supports hand-written upgrade queries that can be used to quickly update tables.

Arrays are used for these structures. JSON and XML were considered during the design phase but were rejected because both would have been brought into arrays for processing anyway, thus requiring slightly more code.

Here is an example of how the presence of the structure enables quick updates. In this case SiteCommander has detected that the table is already installed but also notes the absence of a field specified by its database structure.

Here is one database structure that provides the definitions for two tables.

/**
 *  +--------------------------------------------------------------------+
 *  | SiteCommander by Will Fastie.                                      |
 *  | Copyright (c) 2009-2017 by Will Fastie. All Rights Reserved.       |
 *  | This source code may not be duplicated, distributed, or transfered |
 *  | by any means, electronic or otherwise, without the express,        |
 *  | written consent of Will Fastie except for the purposes of backup.  |
 *  +--------------------------------------------------------------------+
 *  |              ADMIN - SETUP - TABLE CREATION QUERIES                |
 *  +--------------------------------------------------------------------+
 */

// This module is part of a suite of setup query modules for admin setup.
// Each module is repsonsible for setting up its own categories entries.

$table_set_name = TB_GALLERY2;
$table_set_list = implode(',', array(TB_GALLERY2, TB_GALLERY2_IMAGES));
$table_charset = 'utf8'; // or latin1?
$table_version = '2.80';
$table_date = '27 Feb 2015';

$table_definitions = array(
	'table_set_name' => $table_set_name,
	'tables' => array(
		TB_GALLERY2 => array(
			'name' => TB_GALLERY2,
			'fields' => array(
				'id'                    => array('type' => 'int', 'length' => 10, 'attributes' => 'unsigned', 'extra' => 'auto_increment'),
				'uuid'                  => array('type' => 'varchar', 'length' => 36),
				'category_id'           => array('type' => 'int', 'length' => 10, 'attributes' => 'unsigned'),
				'type'                  => array('type' => 'enum', 
																				 'enum' => "'Album', 'Slider', 'Product', 'Carousel', 'Portfolio'",
																				 'default' => 'Album'
																	 ),
				'visibility'            => array('type' => 'enum', 'enum' => "'Public','Private'", 'default'  => 'Public'),
				'ordering'              => array('type' => 'enum', 'enum' => "'Random','Ordered'", 'default'  => 'Random'),
				'name'                  => array('type' => 'varchar', 'length' => 200),
				'permalink'           	=> array('type' => 'varchar', 'length' => 250, 'comment' => 'same size as title + extra'),
				'legend'                => array('type' => 'varchar', 'length' => 200),
				'description'           => array('type' => 'text'),
				'notes'                 => array('type' => 'text'),
				'date_created'          => array('type' => 'date'),
				'date_start'            => array('type' => 'date'),
				'date_end'              => array('type' => 'date'),
				'creator'               => array('type' => 'varchar', 'length' => 200),
				'copyright'             => array('type' => 'varchar', 'length' => 200),
				'client_name'           => array('type' => 'varchar', 'length' => 200, 'comment' => 'for portfolios'),
				'client_location'       => array('type' => 'varchar', 'length' => 200, 'comment' => 'for portfolios'),
				'code'                  => array('type' => 'varchar', 'length' => 50,  'comment' => 'for portfolios'),
				'slideshow_interval'    => array('type' => 'float'),
				'slideshow_interval'    => array('type' => 'float'),
				'slider_timeout'        => array('type' => 'int', 'length' => 11),
				'slider_speed'          => array('type' => 'int', 'length' => 11),
				'slider_delay'          => array('type' => 'int', 'length' => 11),
				'slider_width'          => array('type' => 'int', 'length' => 11),
				'slider_height'         => array('type' => 'int', 'length' => 11),
				'slider_fx'             => array('type' => 'varchar', 'length' => 50),
				'slider_sync'           => array('type' => 'boolean', 'default' => '1'),
				'slider_ease_in'        => array('type' => 'varchar', 'length' => 50),
				'slider_ease_out'       => array('type' => 'varchar', 'length' => 50),
				'slider_pause_on_hover' => array('type' => 'boolean', 'default' => '0'),
				'slider_show_pager'     => array('type' => 'boolean', 'default' => '0'),
				'slider_pager_location' => array('type' => 'enum', 'enum' =>"'inside','outside'", 'default' => 'outside'),
				'slider_pager_x'        => array('type' => 'int', 'length' => 11),
				'slider_pager_y'        => array('type' => 'int', 'length' => 11),
				'width_full'            => array('type' => 'int', 'length' => 11),
				'height_full'           => array('type' => 'int', 'length' => 11),
				'width_title'           => array('type' => 'int', 'length' => 11),
				'height_title'          => array('type' => 'int', 'length' => 11),
				'width_content'         => array('type' => 'int', 'length' => 11),
				'height_content'        => array('type' => 'int', 'length' => 11),
				'width_thumb'           => array('type' => 'int', 'length' => 11),
				'height_thumb'          => array('type' => 'int', 'length' => 11),
				'width_tiny'            => array('type' => 'int', 'length' => 11),
				'height_tiny'           => array('type' => 'int', 'length' => 11),
				'image_quality'         => array('type' => 'int', 'length' => 10, 'default' => '70'),
				'keep_original'         => array('type' => 'boolean', 'default' =>  '1'),
				'square_thumb'          => array('type' => 'boolean', 'default' =>  '0'),
				'display_order'         => array('type' => 'int', 'length' => 11),
				'protected'             => array('type' => 'boolean', 'default' => '0'),
				'enabled'               => array('type' => 'boolean', 'default' => '1')
			),
			'keys' => array(
				'id' => array('type' => 'primary'),
				'uuid' => array('type' => 'key'),
				'name' => array('type' => 'key')
			),
			'categories' => array(
				'fields' => "`uuid`,`name`,`table_name`,`color1`,`color2`, `display_order`,`immutable`,`enabled`",
				'value_strings' => array(
					"UUID, 'General', TABLE-NAME, 'palegreen', '', 1, false, true",
					"UUID, 'Other', TABLE-NAME, 'silver', '', 1, false, true"
				),
			),
			'charset' => $table_charset,
			'version' => $table_version,
			'date' => $table_date
		),
		TB_GALLERY2_IMAGES => array(
			'name' => TB_GALLERY2_IMAGES,
			'fields' => array(
				'id'              => array('type' => 'int', 'length' => 10, 'attributes' => 'unsigned', 'extra' => 'auto_increment'),
				'uuid'            => array('type' => 'varchar', 'length' => 36),
				'gallery_id'      => array('type' => 'int', 'length' => 10, 'attributes' => 'unsigned'),
				'name'            => array('type' => 'varchar', 'length' => 200),
				'filename'        => array('type' => 'varchar', 'length' => 250),
				'caption'         => array('type' => 'text'),
				'description'     => array('type' => 'text'),
				'notes'           => array('type' => 'text'),
				'url'             => array('type' => 'varchar', 'length' => 250),
				'url_new_page'    => array('type' => 'boolean', 'default' => '0'),
				'creator'         => array('type' => 'varchar', 'length' => 100),
				'copyright'       => array('type' => 'text'),
				'date_taken'      => array('type' => 'date'),
				'date_start'      => array('type' => 'date'),
				'date_end'        => array('type' => 'date'),
				'display_order'   => array('type' => 'int', 'length' => 11),
				'display_name'    => array('type' => 'boolean', 'default' => '0'),
				'display_caption' => array('type' => 'boolean', 'default' => '1'),
				'is_featured'     => array('type' => 'boolean', 'default' => '0'),
				'is_thumbnail'    => array('type' => 'boolean', 'default' => '0'),
				'protected'       => array('type' => 'boolean', 'default' => '0'),
				'enabled'         => array('type' => 'boolean', 'default' => '1')
			),
			'keys' => array(
				'id' => array('type' => 'primary')
			),
			'charset' => $table_charset,
			'version' => $table_version,
			'date' => $table_date
		)
	)
);

$db_builder_setup->add_table_set($table_definitions);

$setup_queries[$table_set_name] = array(
	'name' => 'Gallery 2',
	'description' => 'Install the Gallery 2 System Tables.',
	'date' => date('d M Y', strtotime($table_date)),
	'version' => $table_version,
	'table_name' => TB_GALLERY2,
	'tables' => $table_set_list, // list all tables included
	'test_table' => TB_GALLERY2,
	'show_fields' => $table_set_list,
	'visible' => true, // Do we show this one in the list or is is only used by the initial setup process?
	'builder' => true, // This query package was created by db_builder
	'queries' => $db_builder_setup->get_setup_queries($table_set_name)
);

unset($table_definitions);

/* Update Queries, including updates to old system to enable transition */
$update_queries['20151007-2.62_2.84_rollup_galleries'] = array(
	'table_name' => 'ROLL UP - Gallery 1',
	'name' => 'Update Original Galleries',
	'date' => '07 Oct 2015',
	'version' => '2.62-2.84',
	'description' => 'This updates older galleries so that the current Galleries Panel (v1) works properly.
' . 
				'Run this query set to make the updates, then convert to Gallery 2 when convenient.',
	'show_fields' => 'galleries,gallery_images',
	'check_tables' => 'galleries',
	'queries' => array( 
<<