A Flexible Method of Storing Control Data

By
admin
– November 15, 2011Posted in: Shortcuts


Introduction

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:
  • It can result in a very large record structure, which could be a problem as most database engines have a limit to the number of columns (fields) in a table.
  • Adding or removing items on the control record requires a change to the physical database structure. All components which reference this structure would then have to be recompiled.
  • Any component which updates any control data will have to lock the entire record. This may cause delays to other components which try to update the same record at the same time.

A flexible approach

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`));

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.

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.

Implementation

Although I have previous implemented this design in a different language this article shows my latest implementation in PHP using my own development framework.

Changing the table structure

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:

  • What fields need to be displayed on the screen.
  • How each field should be displayed (i.e. which HTML control to use).
  • How the user input for each field should be validated.

From database to screen

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

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.

From screen to database

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:

  • Any field marked as "required" is not empty.
  • All string fields contain strings which do not exceed their maximum size.
  • All numeric fields contain numbers which do not exceed their maximum size, or fall below their minimum value.
  • All boolean fields contain a value which is either TRUE or FALSE.

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 getFieldSpec_original() 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.

Summary

This design has the following advantages:

  • Values can be added to or removed from the control table without having to

    • alter the structure of the table. The structure of the table remains static

    while it is only the contents which are varied.

  • Because individual values are held on totally separate records, individual

    values can be locked without affecting other values.

  • Because each value is held on a separate record it is easier for the table

    to hold 1000′s of records than it is for the table to contain 1000′s of fields.

  • By using a different value for 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.

Field ID Description
RECORD_ID is used to group various records into logical sets. The same table could be
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.

Tags: , , , ,