Database Configurer v1.1.0

Instructions for Installing on osCommerce Online Merchant v2.2ms2 (all releases)

Abstract
Features
Screen Shots
Support
Contribution Link
Credits
Setup Instructions
Advanced Usage
ChangeLog

Abstract

This contribution is meant to help contribution uploaders manage the database changes. Create tables, add columns to existing tables, and add configuration groups and items as well as complete removal of all installed items.

Features allow you to setup configuration groups and configuration options and create them using the next configuration group id number. It also allows for adding configuration options to existing configuration groups and also avoid removing groups if not all the items were removed from the group.

It also offers an update option that will refresh the titles and descriptions of configuration options, but not the configuration setting itself. It also allows for a "force" install that will reset all the configuration items to the default installed values.

Database Configurer provides a solution so that contributions can access a convenient test function that will return whether any updates are needed. Features are also available that will determine whether any items have already been setup (partial install or updating needed).

Features

Database configuration can be automatic or with user interaction and approval.
Feature allows your end user to view the changes before executing them.
Initial database setup including; create tables, create columns in tables, and add configuration groups and items.
Convenient test functions to determine whether updates are necessary or version has changed.
Configuration groups and items may be installed using next available IDs and sort orders.
Configuration items can be added to existing groups.
Refresh feature that forces configuration items to the default installed values.
Update option to refresh titles and descriptions of configuration options, but not the setting itself.
Full uninstall features for all items and avoid removing configuration groups that are not yet empty.
Versioning features allows authors to setup database changes based on the previous installed version.

Screen Shots

(click to enlarge)

Support

Support Forum: http://forums.oscommerce.com/index.php?showtopic=251893

Contribution Link

Credits

Release: v1.0.0 by: Todd Holforty aka surfalot [forum]
Update: v1.1.0 by: Todd Holforty aka surfalot [forum]

Setup Instructions

These setup instructions will be using the contribution Contribution Tracker from time to time as an example project to integrate Database Configurer into.

Database Configurer was designed so that it may be integrated into many contributions without conflict. The main admin page and the language file are common to all installations. The class file contains both data and code to perform all actions necessary (as is the definition of a class). When finished with the setup in your contribution, the class file will be setup and named unique to your contribution.

The following files from this Database Configurer will need inclusion into your contribution.

catalog/admin/configurer.php

catalog/admin/includes/classes/configurer.php

catalog/admin/includes/language/(your language)/configurer.php

catalog/admin/includes/language/(your language)/images/buttons/button_display_updates_only.gif
catalog/admin/includes/language/(your language)/images/buttons/button_run_configuration_utility.gif
Rename the file in: catalog/admin/includes/classes/configurer.php to be prefixed by the name of the main admin page of your contribution.

By example, if I were integrating this Database Configurer into the contribution "Contribution Tracker", the main file is contrib_tracker.php. So the renamed class file here would be:
catalog/admin/includes/classes/contrib_tracker_configurer.php
Find the following block in your newly renamed class file.

// ////////////////////////////////// // BEGINNING OF USER CONFIGURATION // //////////////////////////////////

As the comment says, this is the start of the user configuration section. It contains the configuration data for your contribution.

Just below that comment block you will find: var $cancel_page = FILENAME_MY_CONTRIBUTION;
1. Change FILENAME_MY_CONTRIBUTION to the similar definition you created for catalog/admin/includes/filenames.php that refers to the main page of your contribution.
Now we will setup your contribution's name and version number.

Just below the last step you will see the following array definition: var $current_version = array( 'name' => 'My Contribution', 'version' => '1.0.0', 'configuration_key' => 'MY_CONTRIBUTION_VERSION' );
1. Update the 'My Contribution' field to the name of your contribution.
2. Update the '1.0.0' field to the current version of your contribution.
3. Update the 'MY_CONTRIBUTION_VERSION' field. Change the MY_CONTRIBUTION portion to refer to your contribution. This will become the configuration key to the version of your contribution that is stored in the database for future lookup.
The next section handles setting Configuration Items in the database. The first group is required to set the Version Number necessary for version control.

osCommerce Configuration Items: // // define Configuration Groups and Configuration Items // ////////////////////////////////////////////////////////////////////////// var $configuration_array = array ( // start: group 1 1 => array ( 'group' => array ( 'id' => 6, // optional, if 0 or excluded, code will select next higher ID 'configuration_group_title' => 'Module Options', 'configuration_group_description' => 'Hidden from configuration', 'sort_order' => 6, // optional, if 0 or excluded, code will select next higher ID 'visible' => 0 ), 'data' => array ( 'MY_CONTRIBUTION_VERSION' => array ( 'configuration_title' => 'My Contribution Version', 'configuration_value' => '0', 'configuration_description' => 'The version of the My Contribution that is installed.', 'sort_order' => 0, // optional, if 0 or excluded, code will select next higher ID 'use_function' => NULL, 'set_function' => NULL ) /* , 'CONFIGURATION_KEY' => array ( 'configuration_title' => 'My Config Item', 'configuration_value' => '0', 'configuration_description' => 'My Config Item description. Yadda, yadda, yadda.', 'sort_order' => 0, // optional, if 0 or excluded, code will select next higher ID 'use_function' => NULL, 'set_function' => NULL ) */ ) ) /* , // end: group 1 // start: group 2 2 => array ( 'group' => array ( 'id' => 0, // optional, if excluded, code will select next higher ID 'configuration_group_title' => 'My Options Group', 'configuration_group_description' => 'General settings for My Contribution', 'sort_order' => 0, // optional, if 0 or excluded, code will select next higher ID 'visible' => 1 ), 'data' => array ( 'CONFIGURATION_KEY' => array ( 'configuration_title' => 'My Config true/false Item', 'configuration_value' => 'false', 'configuration_description' => 'My Config true/false Item description. Yadda, yadda, yadda.', 'sort_order' => 0, // optional, if 0 or excluded, code will select next higher ID 'use_function' => NULL, 'set_function' => 'tep_cfg_select_option(array(\'true\', \'false\'),' ), 'CONFIGURATION_KEY' => array ( 'configuration_title' => 'My Config Item', 'configuration_value' => '0', 'configuration_description' => 'My Config Item description. Yadda, yadda, yadda.', 'sort_order' => 0, // optional, if 0 or excluded, code will select next higher ID 'use_function' => NULL, 'set_function' => NULL ) ) ) // end: group 2 */ ); // end: var $configuration_array
1. Update the 'MY_CONTRIBUTION_VERSION' field in the "group 1" section. Change the MY_CONTRIBUTION portion to refer to your contribution. This is the configuration key of the version number of your contribution.
  ** This key must be the same key name you used in Step 4.3.
  
  The following key is 'CONFIGURATION_KEY' and is an example that was commented. If you would like to add another configuration key to this group, uncomment the following block and make necessary changes. The Module Options configuration group is hidden from users in the osCommerce Admin. This group should only be used for config items that are set and used internally by your contribution that the user will never see.
2. Group 2 is also commented and is an example of how to add additional configuration groups and associated configuration options. You can add as many groups and configuration items are you need and Database Configurer will ensure they are installed or updated in the user's database.
  ** Make sure you are careful about uncommenting the groups. Start comment tags: /* are placed before the comma that separates the array blocks.
The next section handles setting up new tables in the database. You may setup tables in one version and later, as your contribution develops, correct and add features to the table. The Database Configurer will NOT update the existing tables in your user's database based on these table definitions. You should update these new table definitions as needed, and then also add column definitions (described below) in that section to ensure users that upgrade will get the new columns.
- The first table example does not have data to insert after it is created.
- The second table definition, which is commented, adds inserts to the table defined by the 'insert' key. You may use the insert to populate tables with static data or example data for the users of your contribution.
New Table Definitions // // define tables to create // ////////////////////////////////////////////////////////////////////////// var $tables_array = array( // start: table 1 1 => array ( 'tablename' => 'contrib_tracker', 'create' => "CREATE TABLE `new_table` ( `new_table_id` int(11) NOT NULL auto_increment, `new_table_int_field` int( 6 ) NOT NULL, `new_table_varchar_field` varchar(255) NOT NULL default '', KEY `table_id` (new_table_id) ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;", 'remove' => "DROP TABLE IF EXISTS `contrib_tracker`;" ) /* , // end: table 1 // start: table 2 2 => array ( 'tablename' => 'new_table', 'create' => "CREATE TABLE `new_table` ( `new_table_id` int(11) NOT NULL auto_increment, `new_table_int_field` int( 6 ) NOT NULL, `new_table_varchar_field` varchar(255) NOT NULL default '', KEY `table_id` (new_table_id) ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;", 'insert' => array ( "INSERT INTO `new_table` (`new_table_id`, `new_table_int_field`, `new_table_varchar_field`) VALUES ('12','1941','Text Field Data');", "INSERT INTO `new_table` (`new_table_id`, `new_table_int_field`, `new_table_varchar_field`) VALUES ('13','1944','Another Data Field');", "INSERT INTO `new_table` (`new_table_id`, `new_table_int_field`, `new_table_varchar_field`) VALUES ('14','1945','Another Data Field');" ), 'remove' => "DROP TABLE IF EXISTS new_table;" ) // end: table 2 */ ); // end: var $tables_array
The next section handles altering tables to add new columns. They can be additions to pre-existing tables or to tables your contribution installs. You may, and should, change the column definitions here to keep up with changes in your contribution. But as with the tables, if you update these columns in the future, Database Configurer will not make updates to your user's databases based on changes here. If you change the column definitions here in the future, you must add SQL to the Version Changes that will update your user's installations.

Columns to Alter Tables // // this array is used for altering existing tables, or contribution tables that have changed. // ////////////////////////////////////////////////////////////////////////// var $table_columns_array = array( // start: column 1 1 => array ( 'tablename' => 'the_table', 'columnname' => 'the_table_new_column', 'create' => "ALTER TABLE `the_table` ADD `the_table_new_column` VARCHAR(64) default NULL AFTER `the_table_existing_column`;", 'remove' => "ALTER TABLE `the_table` DROP `the_table_new_column`" )/* , // end: column 1 // start: column 2 2 => array ( 'tablename' => 'another_table', 'columnname' => 'another_table_new_column', 'create' => "ALTER TABLE `another_table` ADD `another_table_new_column` VARCHAR(64) default NULL AFTER `another_table_existing_column`;", 'remove' => "ALTER TABLE `another_table` DROP `another_table_new_column`" ) // end: column 2 */ ); // end: var $table_columns_array
The next section handles SQL database update based on versions. Each time you create a new version that needs database changes beyond the usefulness of the Configuration Options, Table Definitions, and Column Additions, you add a new array to this section with your current version number as the key, and add the necessary SQL commands. This section is very useful to move data around, change columns, change column sizes or types, add additional data via inserts, and much more.

Define Additional Version Based Updates // // define version updates // configurer will install all updates that are greater than // the installed version number and less than or equal to the // current version number defined above in $current_version[]. // ////////////////////////////////////////////////////////////////////////// var $version_updates = array( '1.1.12' => array ( // drop table from previous versions that is no longer necessary 1 => "DROP TABLE IF EXISTS `the_table_name`;", // increase the_column_name column size to 255 to support longer support data 2 => "ALTER TABLE `the_table_name` CHANGE `the_column_name` `the_column_name` VARCHAR(255);", // increase contrib_support column size to 255 to support longer support URLs 3 => "ALTER TABLE `the_table_name` CHANGE `another_column_name` `another_column_name` VARCHAR(12);", // copy current reference dates to new column 4 => "UPDATE `the_table_name` SET `the_column_name` = `another_column_name`;", // copy zero another_column_name to new column 5 => "UPDATE `the_table_name` SET `the_column_name` = `another_column_name` where `another_column_name` = '0000-00-00 00:00:00';" ) /* , 'x.x.x' => array ( // do something 1 => ";", // do something else 2 => ";" ) */ ); // end: var $version_updates
Now we will add the code to your contribution's main page that will check the status of the installation and apply updates to the database or redirect the user to the Database Configurer admin page to apply changes themselves.
- If you would like Database Configurer to automatically apply changes as needed, place the following code near the top of your contribution's main page, after the require('includes/application_top.php'); statement.
  
  Include this after require('includes/application_top.php'); // include configurer and check for updates. require(DIR_WS_CLASSES.substr(FILENAME_MY_CONTRIBUTION,0,-4).'_configurer.php'); $conf = new configurer(CONFIGURER_PROCESS_UPDATES);
  
  Be sure to change FILENAME_MY_CONTRIBUTION to the similar definition you created for catalog/admin/includes/filenames.php that refers to the main page of your contribution.
- If you would like the user to view and apply changes as needed, place the following code near the top of your contribution's main page, after the require('includes/application_top.php'); statement. This code will redirect the user to the configuration page when updates are required.
  
  Include this after require('includes/application_top.php'); // include configurer and check for updates. require(DIR_WS_CLASSES.substr(FILENAME_MY_CONTRIBUTION,0,-4).'_configurer.php'); $conf = new configurer(); if ($conf->db_version_outofdate()) { tep_redirect(tep_href_link('configurer.php','conf='.substr(FILENAME_MY_CONTRIBUTION,0,-4))); }
  
  Be sure to change FILENAME_MY_CONTRIBUTION (in two places) to the similar definition you created for catalog/admin/includes/filenames.php that refers to the main page of your contribution.
In this step you will add a button to your contribution's main page that allows your users to access the Database Configurer's Admin Page to update their configuration or remove your contribution's settings.

I would suggest placing this link under the right sidebar typically found in osCommerce admin pages.

Include this anywhere you would like the Database Configurer admin button: echo $conf->db_configurer_button();
You can add this to your installation instructions that will direct the user to the configuration page prior to running your contribution's main page the first time.

This link will show the Database Configurer's admin page.
http://yourdomain.com/admin/configurer.php?conf=(your_contrib)
** Be sure to change (your_contrib) in the link above with page name or your contribution's main page, without the .php extension.

This link will show the Database Configurer's admin page and display all SQL commands that need to be executed to update their database.
http://yourdomain.com/admin/configurer.php?conf=(your_contrib)&action=displayonly
** Be sure to change (your_contrib) in the link above with page name or your contribution's main page, without the .php extension.

Advanced

Here are a few additional options that may help you with integration.

** Remember, in all the following examples, be sure to change FILENAME_MY_CONTRIBUTION to the similar definition you created for catalog/admin/includes/filenames.php that refers to the main page of your contribution.

Start with class code include and class instantiation:

// include configurer. require(DIR_WS_CLASSES.substr(FILENAME_MY_CONTRIBUTION,0,-4).'_configurer.php'); $conf = new configurer();

After instantiation a couple basic properties are available:

The currently stored version is available in this property:
$conf->stored_version

This one stores a boolean value as to whether the Database Version has changed (true) or not (false):
$conf->version_outofdate

After instantiation you can then use the following methods to return info or process all updates:

Check all potential updates and display a report:
echo $conf->db_configurer_update(CONFIGURER_CHECK_STATUS);

Check all potential updates and display the actual SQL command that will be run if processing is executed:
echo $conf->db_configurer_update(CONFIGURER_DISPLAY_UPDATES);

Process all updates and display a report:
echo $conf->db_configurer_update(CONFIGURER_PROCESS_UPDATES);

Process all updates and force Configuration Option defaults on any Configuration Options defined in the Database Configurer that already exists in the user's database:
echo $conf->db_configurer_update(CONFIGURER_REFRESH_CONFIG);
You can get extended details on the database status by instantiating with CONFIGURER_CHECK_STATUS. This option checks for all tables, columns, and configuration items and provides a couple boolean properties for status.

// include configurer. require(DIR_WS_CLASSES.substr(FILENAME_MY_CONTRIBUTION,0,-4).'_configurer.php'); $conf = new configurer(CONFIGURER_CHECK_STATUS);

After that, the following properties are available:

This one contains a boolean value as to whether database needs update (true) or not (false):
$conf->needs_update

This one contains a boolean value as to whether one or more configured items already exist in the database (true) or not (false):
$conf->found_one

The following will display the textual details of what updates are required:
echo $conf->status_messages;
You can tell the class to go ahead and process all updates by instantiating with CONFIGURER_PROCESS_UPDATES

// include configurer. require(DIR_WS_CLASSES.substr(FILENAME_MY_CONTRIBUTION,0,-4).'_configurer.php'); $conf = new configurer(CONFIGURER_PROCESS_UPDATES);

The following will display the textual details of what updates are required:
echo $conf->status_messages;