Where an application requires certain values at runtime, and where these values may be changed at irregular intervals, it is common practice to hold these on a database record rather than having them hard-coded into any program. This means that should any of these values ever change it is a simple matter of updating the database rather than changing, compiling and releasing individual program modules. This can be a problem, of course, if one of the modules is missed out.
The traditional approach is to have a single control record containing a separate field for each item of data. However, this has the following disadvantages:
After encountering those disadvantages on more than one occasion I decided on a different and more flexible approach. Instead of a single record containing many values I have each value on its own record, which therefore results in many records. The trick is to store these values in separate records in the database, but to present them to the user in a single screen as if they were separate values on a single record. How is this possible? Read on and learn.
First, let us start with the database structure:
CREATE TABLE `mnu_control` ( `record_id` varchar(16) NOT NULL default '', `field_id` varchar(32) NOT NULL default '', `field_value` varchar(255) default NULL, PRIMARY KEY (`record_id`,`field_id`) );
| Field ID | Description |
|---|---|
| RECORD_ID | is used to group various records into logical sets. The same table could be used by multiple applications, so by setting RECORD_ID to the application name each application's data can be kept separate from the other, even if any FIELD_IDs are the same. |
| FIELD_ID | is the name of the field, unqualified, in upper case. |
| FIELD_VALUE | is a string field as its holds any value in display format regardless of the interface definition (number, date, time, boolean, etc) of the source field on the screen. |
Second, build a screen where these records can be displayed and modified:
As you can see this looks like a completely normal screen, which is the whole idea.
Although I have previous implemented this design in a different language this article shows my latest implementation in PHP using my own development framework.
In this example I am creating the class to deal with the control data for the 'foobar' subsystem. I am inheriting from the standard MNU_CONTROL class in order to reuse the implementation for the following methods:
<?php require_once 'classes/mnu_control.class.inc'; class foobar_control extends mnu_control { function __construct () { // perform parent constructor parent::__construct(); if (!empty($this->dirname)) { // this is to be used in the loadFieldSpec() method $this->dirname_dict = $this->dirname; } // if // this is to be used in _getCustomProcessingObject() method $this->dirname = dirname(__file__); // provide overrides for this subsystem $this->record_id = 'FOOBAR'; } // __construct // **************************************************************************** function _cm_changeConfig ($where, $fieldarray) { // see Changing the table structure return $fieldarray; } // _cm_changeConfig // **************************************************************************** function _cm_commonValidation ($fieldarray, $originaldata) { // custom code goes here return $fieldarray; } // _cm_commonValidation // **************************************************************************** function _cm_getExtraData ($where, $fieldarray) { // custom code goes here return $fieldarray; } // _cm_getExtraData // **************************************************************************** function _cm_post_updateSelection ($selection, $replace) { // custom code goes here return $selection; } // _cm_post_updateSelection // **************************************************************************** function _cm_pre_updateSelection ($selection, $replace) { // custom code goes here return $selection; } // _cm_pre_updateSelection } // foobar_control ?>
The first step is to update the internal table definition by replacing the physical database structure with the theoretical structure. The "physical" structure, as exported from the Data Dictionary, is as follows:
$fieldspec['record_id'] = array('type' => 'string',
'size' => 16,
'pkey' => 'y',
'required' => 'y',
'uppercase' => 'y');
$fieldspec['field_id'] = array('type' => 'string',
'size' => 32,
'pkey' => 'y',
'required' => 'y',
'uppercase' => 'y');
$fieldspec['field_value'] = array('type' => 'string',
'size' => 255);
This structure can be replaced at runtime with the following code:
function _cm_changeConfig ($where, $fieldarray)
// Change the table configuration for the duration of this instance.
{
// default language code
$fieldspec['default_language'] = array('type' => 'string',
'size' => 5,
'required' => 'y',
'lowercase' => 'y',
'control' => 'dropdown',
'optionlist' => 'language_code');
// how often must the user change his password?
$fieldspec['pswd_change'] = array('type' => 'string',
'size' => 2,
'required' => 'y',
'uppercase' => 'y',
'control' => 'radiogroup',
'optionlist' => 'pswd_change',
'align_hv' => 'vertical');
// change password after 'n' logons
$fieldspec['pswd_count'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y');
// change password after 'n' days
$fieldspec['pswd_days'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y');
// an invalid password can be tried 'n' times after which the user_id will be disabled
$fieldspec['pswd_retries'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y');
// issue a "password will expire in N days/logons" warning
$fieldspec['pswd_warning'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y');
// specify the format of user passwords
$fieldspec['pswd_format_minlen'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y',
'required' => 'y',
'minvalue' => 1);
$fieldspec['pswd_format_upper'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y');
$fieldspec['pswd_format_lower'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y');
$fieldspec['pswd_format_digits'] = array('type' => 'integer',
'size' => 3,
'unsigned' => 'y');
// are passwords to be encrypted on the database?
$fieldspec['pswd_encrypt'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N');
// are passwords to be visible in the update/enquiry screens?
$fieldspec['pswd_hidden'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N');
// define lockout times between which system is unavailable
$fieldspec['shutdown_start'] = array('type' => 'time',
'size' => 5);
$fieldspec['shutdown_end'] = array('type' => 'time',
'size' => 5);
$fieldspec['shutdown_warning'] = array('type' => 'time',
'size' => 5);
$day_names = getLanguageArray('day_names_short');
$fieldspec['shutdown_monday'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N',
'control' => 'checkbox',
'label' => $day_names['mon'],
'align_lr' => 'left');
$fieldspec['shutdown_tuesday'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N',
'control' => 'checkbox',
'label' => $day_names['tue'],
'align_lr' => 'left');
$fieldspec['shutdown_wednesday'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N',
'control' => 'checkbox',
'label' => $day_names['wed'],
'align_lr' => 'left');
$fieldspec['shutdown_thursday'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N',
'control' => 'checkbox',
'label' => $day_names['thu'],
'align_lr' => 'left');
$fieldspec['shutdown_friday'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N',
'control' => 'checkbox',
'label' => $day_names['fri'],
'align_lr' => 'left');
$fieldspec['shutdown_saturday'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N',
'control' => 'checkbox',
'label' => $day_names['sat'],
'align_lr' => 'left');
$fieldspec['shutdown_sunday'] = array('type' => 'boolean',
'true' => 'Y',
'false' => 'N',
'control' => 'checkbox',
'label' => $day_names['sun'],
'align_lr' => 'left');
$this->fieldspec = $fieldspec;
return $fieldarray;
} // _cm_changeConfig
This amended structure identifies the following:
The second step is to read multiple records from the database into an array using the following code:
$fieldarray = $object->getData("record_id='SYSTEM'");
At this point $fieldarray is a nested array - the first level is indexed by row number, and each row contains an associative array of name=value pairs. This looks like the following:
$rowdata => Array
(
[0] => Array
(
[record_id] => SYSTEM
[field_id] => DEFAULT_LANGUAGE
[field_value] => en
)
[1] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_CHANGE
[field_value] => AR
)
[2] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_COUNT
[field_value] =>
)
[3] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_DAYS
[field_value] =>
)
[4] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_ENCRYPT
[field_value] => Y
)
[5] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_FORMAT_DIGITS
[field_value] =>
)
[6] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_FORMAT_LOWER
[field_value] =>
)
[7] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_FORMAT_MINLEN
[field_value] => 4
)
[8] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_FORMAT_UPPER
[field_value] =>
)
[9] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_HIDDEN
[field_value] => Y
)
[10] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_RETRIES
[field_value] => 3
)
[11] => Array
(
[record_id] => SYSTEM
[field_id] => PSWD_WARNING
[field_value] => 5
)
[12] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_END
[field_value] =>
)
[13] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_FRIDAY
[field_value] =>
)
[14] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_MONDAY
[field_value] =>
)
[15] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_SATURDAY
[field_value] =>
)
[16] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_START
[field_value] =>
)
[17] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_SUNDAY
[field_value] =>
)
[18] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_THURSDAY
[field_value] =>
)
[19] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_TUESDAY
[field_value] =>
)
[20] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_WARNING
[field_value] =>
)
[21] => Array
(
[record_id] => SYSTEM
[field_id] => SHUTDOWN_WEDNESDAY
[field_value] =>
)
)
This can be changed into a single row of data using the following code:
function _cm_post_getData ($rowdata, &$where)
// perform custom processing after database record(s) are retrieved.
// NOTE: $where is passed BY REFERENCE so that it may be modified.
{
// turn multiple rows into a single associative array
foreach ($rowdata as $row => $data) {
$fieldarray[0][strtolower($data['field_id'])] = $data['field_value'];
} // foreach
// get list of (virtual) fields in this table
$fieldspec = $this->getFieldSpec();
// insert any missing fields from $fieldspec
foreach ($fieldspec as $fieldname => $spec) {
if (!array_key_exists(strtolower($fieldname), $fieldarray[0])) {
$fieldarray[0][$fieldname] = null;
} // if
} // foreach
return $fieldarray;
} // _cm_post_getData
Note that you can inherit the necessary code from the _cm_pre_getData() and _cm_post_getData() methods in the MNU_CONTROL class.
The new array looks like the following:
$fieldarray => Array
(
[0] => Array
(
[default_language] => en
[pswd_change] => AR
[pswd_count] =>
[pswd_days] =>
[pswd_encrypt] => Y
[pswd_format_digits] =>
[pswd_format_lower] =>
[pswd_format_minlen] => 4
[pswd_format_upper] =>
[pswd_hidden] => Y
[pswd_retries] => 3
[pswd_warning] => 5
[shutdown_end] =>
[shutdown_friday] =>
[shutdown_monday] =>
[shutdown_saturday] =>
[shutdown_start] =>
[shutdown_sunday] =>
[shutdown_thursday] =>
[shutdown_tuesday] =>
[shutdown_warning] =>
[shutdown_wednesday] =>
)
)
This data is transferred to an XML document which is transformed into HTML by an XSL stylesheet. The information in the modified structure tells the stylesheet which HTML control to use for each field.
After the user has changed any values he presses the "submit" button to send those changes to the server for processing. Everyone knows that user input should never be trusted, and should be "cleansed" or "filtered" before being written to the database, and this common task can be performed automatically by the framework using the information contained with the modified structure. This will ensure that:
After this validation has been performed the data can be written to the database using the following code:
function _cm_updateSelection($fieldarray, $replace)
// update multiple rows in a single operation.
{
$errors = array();
// set $fieldspec to the database view
$this->fieldspec = $this->getFieldSpec_original();
// get array of fieldnames in the primary key
$pkeynames = $this->getPkeyNames();
// now turn the array of columns into an array of rows
$rowdata = array();
$rownum = 0;
foreach ($updatearray as $fieldname => $fieldvalue) {
$rowdata[$rownum]['record_id'] = 'system';
$rowdata[$rownum]['field_id'] = $fieldname;
$rowdata[$rownum]['field_value'] = $fieldvalue;
// construct 'where' clause from primary key
$where = array2where($rowdata[$rownum], $pkeynames);
// find out if this record currently exists or not
$count = $this->getCount($where);
if ($count == 0) {
// record does not exist, so create it
$rowdata[$rownum] = $this->insertRecord($rowdata[$rownum]);
} else {
// record already exists, so update it
$rowdata[$rownum] = $this->updateRecord($rowdata[$rownum]);
} // if
if (!empty($this->errors)) {
// ignore 'name' and extract 'value' from $this->errors
// as 'name' may not be the same as $fieldname
$errors[$fieldname] = array_shift($this->errors);
} // if
$rownum = $rownum + 1;
} // foreach
$this->errors = $errors;
return $fieldarray;
} // _cm_updateSelection
The loadFieldSpec() method is used to replace the modified structure with the original structure. It then steps through the input array and extracts each field which it then treats as a separate database row. This row is then inserted or updated, as appropriate.
Note that you can inherit the necessary code from the _cm_updateSelection() method in the MNU_CONTROL class.
This design has the following advantages:
record_id it is possible to use the same table for different sets of control data, such as for an individual application instead of the whole system.