A flexible method of storing control data

By Tony Marston

29th August 2006

Introduction
A flexible approach
Implementation
- Changing the table structure
- From database to screen
- From screen to database
Summary

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:

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

control-data-001 (10K)

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:

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:

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:


© Tony Marston
29th August 2006

http://www.tonymarston.net
http://www.radicore.org

counter