What is a Data Dictionary? In some languages it may go by another name, such as Catalog, Repository or Application Model. The Data Dictionary does not contain any application data, it contains information which describes how that application data is structured. This information may include table names and characteristics, field names and characteristics, relationship information, et cetera. It is therefore data-about-data, or meta-data.

Many of the modern RDBMS products have facilities for holding and viewing meta-data (also known as a dictionary or catalog), so why not use that instead of going to the trouble of re-inventing the wheel? Simply because the RDBMS dictionary is designed for use by the RDBMS and does not have enough information to service other parts of the application. If you consider that an application is divided into 3 Tiers, then the RDBMS dictionary/catalog is limited to the data access layer. It cannot provide information for use in the business layer nor the presentation layer. The purpose of this particular data dictionary is to fill those gaps.

To utilise this data dictionary there are three distinct stages:

IMPORT - Populate the dictionary database using details extracted from the physical database schema. This includes all field names and their attributes, and all primary keys, candidate keys and indices. If any table details are subsequently modified the import function can be used again to synchronise the dictionary. This will deal with new tables and new, changed or deleted columns.
EDIT - Extend and customise the details for use by the application. The data imported from the database cannot be changed, but additional data can be customised as required.
EXPORT - Make the details available to the application in the form of disk files which can be referenced by means of the include() function. Two files will be created for each database table:
- classes/<tablename>.class.inc - this is the initial class file for the database table. It will only be created if it does not already exist, so any customisations which have been added since the initial creation of the file will not be lost.
- classes/<tablename>.dict.inc - this contains the column details, key details and relationship details. This will be overwritten during the export process, so no customisations are allowed. All customisation should be performed in the dictionary and then exported to this file.

Note that the only way to keep the dictionary and the database synchronised is to make changes within the database (using whatever administration tool is available) then import the modified details into the dictionary. It is not possible to make modifications within the dictionary then export them to the database.

Database Design

Dictionary E-R Diagram

The Entity-Relationship (E-R) Diagram for my dictionary database is as shown in figure 1:

Figure 1 - E-R Diagram

E-R Diagram description
DATABASE	This identifies all the different databases which are available in the application.
TABLE	This identifies all the tables that exist within each database.
COLUMN	This identifies the columns that exist within each table.
TABLE-KEY	This identifies all the keys (primary, candidate and non-unique) that have been defined for each table.
RELATIONSHIP	This identifies where two tables are joined in a one-to-many (parent-to-child, senior-to-junior) relationship. This data is defined manually.
RELATED-COLUMN	For each relationship this identifies which field (column) in the primary key of the parent table is related to which foreign key field in the child table. This data is defined manually.

DATABASE table

The structure of this table is as follows:

CREATE TABLE `dict_database` (
  `database_id` varchar(64) NOT NULL default '',
  `database_desc` varchar(255) NOT NULL default '',
  `comment` text,
  `subsys_id` varchar(8) NOT NULL default '',
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) default NULL,
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`database_id`)
) TYPE=MyISAM;

Field	Type	Description
database_id	STRING	The name of the database.
database_desc	STRING	A user-defined description for the database.
comment	TEXT	User-defined comments for the database.
subsys_id	STRING	The identity of a SUBSYSTEM within the MENU database. This provides the name of the directory in which the export files will be written.
The following fields are set automatically by the system:
created_date	DATE+TIME	The date and time on which this record was created.
created_user	STRING	The identity of the user who created this record.
revised_date	DATE+TIME	The date and time on which this record was last changed.
revised_user	STRING	The identity of the user who last changed this record.

TABLE table

The structure of this table is as follows:

CREATE TABLE `dict_table` (
  `database_id` varchar(64) NOT NULL default '',
  `table_id` varchar(64) NOT NULL default '',
  `table_desc` varchar(255) NOT NULL default '',
  `comment` text,
  `audit_logging` char(1) NOT NULL default 'Y',
  `default_orderby` varchar(64) default NULL,
  `alt_language_table` varchar(64) default NULL,
  `alt_language_cols` text,
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) default NULL,
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`database_id`,`table_id`)
) TYPE=MyISAM;

Field	Type	Description
database_id	STRING	The name of the database.
table_id	STRING	The name of the table.
table_desc	STRING	A user-defined description for the table.
comment	TEXT	User-defined comments for the table.
audit_logging	BOOLEAN	A YES/NO switch which identifies if updates to this table should be written to the Audit Log.
default_orderby	STRING	The contents of this string will be used as the ORDER BY clause on an sql SELECT statement in a LIST screen if no other sort sequence is specified.
alt_language_table	STRING	Optional. Identifies the table which contains text in alternative languages for certain columns on this table. Please refer to Internationalisation and the Radicore Development Infrastructure (Part 2) for more details.
alt_language_cols	STRING	Optional. Identifies the columns which have translated text on al_language_table.
The following fields are set automatically by the system:
created_date	DATE+TIME	The date and time on which this record was created.
created_user	STRING	The identity of the user who created this record.
revised_date	DATE+TIME	The date and time on which this record was last changed.
revised_user	STRING	The identity of the user who last changed this record.

COLUMN table

The structure of this table is as follows:

CREATE TABLE `dict_column` (
  `database_id` varchar(64) NOT NULL default '',
  `table_id` varchar(64) NOT NULL default '',
  `column_id` varchar(64) NOT NULL default '',
  `column_seq` smallint(6) unsigned NOT NULL default '0',
  `column_desc` varchar(255) NOT NULL default '',
  `comment` text,
  `col_type` varchar(20) NOT NULL default '',
  `col_type_native` varchar(32) NOT NULL default '',
  `col_array_type` varchar(20) NOT NULL default '',
  `col_values` text,
  `user_size` int(11) unsigned NOT NULL default '0',
  `col_maxsize` int(11) unsigned NOT NULL default '0',
  `col_null` char(3) NOT NULL default 'YES',
  `is_required` char(1) NOT NULL default 'N',
  `col_key` char(3) default NULL,
  `col_default` varchar(40) default NULL,
  `col_auto_increment` char(1) NOT NULL default 'N',
  `col_unsigned` char(1) NOT NULL default 'N',
  `col_zerofill_bwz` char(3) default NULL,
  `col_precision` tinyint(3) unsigned default NULL,
  `col_scale` tinyint(3) unsigned default NULL,
  `col_minvalue` double default NULL,
  `col_maxvalue` double default NULL,
  `user_minvalue` double default NULL,
  `user_maxvalue` double default NULL,
  `noedit_nodisplay` char(3) default NULL,
  `nosearch` char(3) default NULL,
  `noaudit` char(3) default NULL,
  `upper_lowercase` varchar(5) default NULL,
  `is_password` char(1) NOT NULL default 'N',
  `auto_insert` char(1) NOT NULL default 'N',
  `auto_update` char(1) NOT NULL default 'N',
  `infinityisnull` char(1) NOT NULL default 'N',
  `subtype` varchar(10) default NULL,
  `image_width` smallint(6) unsigned default NULL,
  `image_height` smallint(5) unsigned default NULL,
  `is_boolean` char(1) NOT NULL default 'N',
  `boolean_true` varchar(4) default NULL,
  `boolean_false` varchar(4) default NULL,
  `control` varchar(10) default NULL,
  `optionlist` varchar(64) default NULL,
  `checkbox_label` varchar(64) default NULL,
  `task_id` varchar(64) default NULL,
  `foreign_field` varchar(64) default NULL,
  `align_hv` char(1) default NULL,
  `align_lr` char(1) default NULL,
  `multi_cols` tinyint(3) unsigned default NULL,
  `multi_rows` tinyint(3) unsigned default NULL,
  `custom_validation` varchar(255) default NULL,
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) default NULL,
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`database_id`,`table_id`,`column_id`)
) TYPE=MyISAM;

Field	Type	Description
database_id	STRING	The name of the database.
table_id	STRING	The name of the table.
column_id	STRING	The name of the column.
column_seq	NUMERIC	The sequence number of this column within the table's definition.
column_desc	STRING	A user-defined description for the column.
comment	TEXT	User-defined comments for the column.
col_type	STRING	The column type as shown to the user and as used by the application. In some cases COL_TYPE_NATIVE may identify a collection of possible data types, such as "CHAR,BOOLEAN", "TINYINT,BOOLEAN" or "DATE,TIME,DATETIME", in which case the user must choose the one which is desired from a dropdown list.
col_type_native	STRING	The column type as defined in the database. In some cases this may contain a collection of possible choices where a data type in the database may actually be used as a different data type in the application. This situation exists, for example, where the database does not support the BOOLEAN data type, in which case a CHAR(1) or TINYINT(1) is used instead. By defining such a column as BOOLEAN in the dictionary it can be treated as BOOLEAN by the application. This also applies in the Oracle database where the single DATE data type covers the DATE, TIME and DATETIME types which exist in other databases. As it could be inconvenient to always treat such a field as DATETIME when in fact it should be DATE only or TIME only it is presented to the user as "DATE,TIME,DATETIME" so that a specific choice can be made.
col_array_type	STRING	By default each datatype holds a single value, but with the PostgreSQL database it is possible to have a datatype which holds an array of values. In this case COL_TYPE is set to "ARRAY" and COL_ARRAY_TYPE is set to the underlying datatype such as VARCHAR, NUMBER, etc.
col_values	TEXT	This is the array of field values for MySQL fields of type 'ENUM' or 'SET'.
user_size	NUMERIC	The column size as defined by the user. It starts off the same as COL_MAXSIZE, but may be reduced.
col_maxsize	NUMERIC	The column size as defined in the database.
col_null	STRING	Identifies if the column value is allowed to be NULL or not. Possible values are: 'NULL' - value is allowed to be empty. 'NOT NULL' - value is not allowed to be empty.
is_required	BOOLEAN	Indicates if the column value is required or not. This is initially set using the value in COL_NULL. Possible values are: 'REQUIRED' - value is not allowed to be empty. 'No' - value is allowed to be empty. This may be altered to 'REQUIRED' to override the database setting.
col_key	STRING	Indicates if this column is part of a key or index. Possible values are: ' ' - not any type of key. 'PRIMARY' - column is in the primary key. 'UNIQUE' - column is in an additional unique key (candidate key). 'NON-UNIQUE' - column is in a non-unique key (index).
col_default	STRING	The default value for this column as defined within the database. If IS_REQUIRED is TRUE and no value is supplied on an INSERT, this default value will be used.
col_auto_increment	BOOLEAN	Indicates if this column is set to 'auto_increment' within the database. Possible values are: ' ' - no. 'AUTO INCREMENT' - on an INSERT the database will obtain the next sequence number.
col_unsigned	BOOLEAN	For numeric columns this turns off the ability to store values with a plus or minus sign (+/-). Only positive values can be stored. For MySQL this allows the maximum value for be doubled as the sign bit can be used as part of the value.
col_zerofill_bwz	STRING	Optional. Available for numeric/decimal fields only. Possible values are: '' - zeroes and blanks will not be converted in any way. ZEROFILL (ZF) - Causes leading zeros to be shown as '0' instead of ' ' (blank). BLANK WHEN ZERO (BWZ) - Causes '0.00' to be displayed as ' ' (blank).
col_precision	NUMERIC	For numeric values this is the number of significant decimal digits. For example, the value 999.99 has a precision of 5.
col_scale	NUMERIC	For numeric values this is the number of digits that can be stored following the decimal point. For example, the value 999.99 has a scale of 2.
col_minvalue	NUMERIC	For numeric fields this is the minimum value allowed by the database.
col_maxvalue	NUMERIC	For numeric fields this is the maximum value allowed by the database.
user_minvalue	NUMERIC	This starts off with the same value as COL_MINVALUE, but can be customised by the user.
user_maxvalue	NUMERIC	This starts off with the same value as COL_MAXVALUE, but can be customised by the user.
noedit_nodisplay	STRING	This is used by the presentation layer only. Possible values are: ' ' - value may be changed. 'NOEDIT' - the value will be displayed but cannot be changed. 'NODISPLAY' - value will not be displayed.
nosearch	STRING	This is used by the presentation layer only. Possible values are: ' ' - the field will not be excluded from SEARCH screens. 'NOEDIT' - the field will be excluded from SEARCH screens.
noaudit	STRING	This is used by the data access layer only. Possible values are: ' ' - the 'before' and 'after' values for the field will appear in the Audit Log as normal to show that it has changed. 'NOAUDIT' - the field will still appear in the Audit Log, but its value will be replaced with a series of asterisks (*).
upper_lowercase	STRING	For string fields this forces all input to be shifted to upper or lower case before being written to the database.
is_password	BOOLEAN	This is used by the presentation layer only. As characters are input they are masked, typically by a series of asterisks, to protect sensitive information from onlookers. However, they are still transmitted to the server in clear text.
auto_insert	BOOLEAN	This is only used when new records are inserted. Possible values are: ' ' - no action. 'AUTO-INSERT' - a value will be inserted according to the column's type: DATETIME - the current date and time will be inserted. DATE - the current date will be inserted. TIME - the current time will be inserted. STRING - the current user's identity (from the logon screen) will be inserted. OTHER - no action will be taken.
auto_update	BOOLEAN	This is only used when existing records are updated. Possible values are: ' ' - no action. 'AUTO-UPDATE' - a value will be inserted according to the column's type (see AUTO_INSERT for details).
infinityisnull	BOOLEAN	This is valid for columns of type DATE only. It means that a blank date on the screen will be held in the database as '9999-12-31' instead of '0000-00-00'. See Dealing with null End Dates for a detailed explanation.
subtype	STRING	This option is available for string fields only. Possible values are: ' ' - no action. 'FILE NAME' - no action (yet). 'E-MAIL' - will be checked against the pattern for e-mail addresses. 'IMAGE' - will display the file as an image (see also IMAGE_WIDTH and IMAGE_HEIGHT).
custom_validation	STRING	This option is available for any field. It identifies the validation method to be used for this field. The format is 'file/class/method' where: file = the name of the file which contains the validation class. class = the name of the validation class. method = the name of the method within the validation class. Refer to Extending the Validation class for more details.
image_width	NUMERIC	Only valid if SUBTYPE='IMAGE'. Identifies the width of the image in pixels.
image_height	NUMERIC	Only valid if SUBTYPE='IMAGE'. Identifies the height of the image in pixels.
is_boolean	BOOLEAN	The DBMS may not support a column type of BOOLEAN, in which case a 1-character field may be used as a substitute. This column may be set to TRUE to force the application to treat such a field as BOOLEAN so that its values can be limited to either BOOLEAN_TRUE or BOOLEAN_FALSE.
boolean_true	STRING	Only valid if IS_BOOLEAN='TRUE'. Identifies how the value 'TRUE' is stored, typically something like '1' or 'T' or 'Y'.
boolean_false	STRING	Only valid if IS_BOOLEAN='TRUE'. Identifies how the value 'FALSE' is stored, typically something like '0' or 'F' or 'N'.
control	STRING	This identifies the HTML control to be used when the field is built into a screen (and is amendable). Possible values are: ' ' - text box (default). 'DROPDOWN LIST' - options will be displayed in a scrollable list. 'MULTI DROPDOWN' - a dropdown list that allows multiple items to be selected (see also MULTI_ROWS). 'RADIO GROUP' - options will be displayed as a group of radio buttons. 'CHECKBOX' - a box that will be ticked to represent ON/YES/TRUE and unticked to represent OFF/NO/FALSE. 'MULTI-LINE TEXT BOX' - will be displayed as a multi-line text box instead of a single line (see also MULTI_COLS and MULTI_ROWS). 'POPUP' - a picklist that will be displayed as another form/screen instead of a dropdown list due to the large number of available options. 'FILE PICKER' - a picklist that will present a list of file names from a particular directory. 'HYPERLINK' - a string that will be displayed as a hyperlink, but for tasks of type 'input', 'update' and 'search' it will be displayed as an editable text box.
optionlist	STRING	This is valid for dropdown lists and radio groups only. It is the name the list of items that will be used to populate that HTML control. This list should be constructed within the table class and written out to the XML file.
checkbox_label	STRING	This is valid for checkboxes only. It is for an additional piece of text which may appear either to the left or right of the control depending on ALIGN_LR.
task_id	STRING	If the CONTROL type is 'File Picker' or 'Popup' this is the name of the Task that will be activated to provide the picklist of available options.
foreign_field	STRING	If the CONTROL type is 'Popup' this is name of a field on the foreign table that will be merged with the contents of the current table before being output to the XML file.
align_hv	STRING	If the CONTROL type is 'Radio Group' this identifies how the list of options should be aligned. Possible values are: 'V' - Vertical. 'H' - Horizontal (default). This can be used to produce effects such as:
align_lr	STRING	If the CONTROL type is 'Radio Group' or 'Checkbox' this identifies whether the text should be to the left or the right of the control. For checkboxes this only applies if an additional checkbox label has been defined. Possible values are: 'L' - Left. 'R' - Right (default). This can be used to produce effects such as:
multi_cols	NUMERIC	If the CONTROL type is 'Multi-Line' this identifies the width of the text box in columns.
multi_rows	NUMERIC	If the CONTROL type is 'Multi-Line' this identifies the height of the text box in rows (lines). If the CONTROL type is 'Dropdown' or 'Multi-Dropdown' this identifies the height of the scrollable area in rows (lines).
The following fields are set automatically by the system:
created_date	DATE+TIME	The date and time on which this record was created.
created_user	STRING	The identity of the user who created this record.
revised_date	DATE+TIME	The date and time on which this record was last changed.
revised_user	STRING	The identity of the user who last changed this record.

TABLE-KEY table

The structure of this table is as follows:

CREATE TABLE `dict_table_key` (
  `database_id` varchar(64) NOT NULL default '',
  `table_id` varchar(64) NOT NULL default '',
  `key_name` varchar(64) NOT NULL default '',
  `column_id` varchar(64) NOT NULL default '',
  `seq_in_index` tinyint(3) unsigned NOT NULL default '0',
  `is_unique` char(1) NOT NULL default 'N',
  `column_seq` tinyint(3) unsigned default NULL,
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) default NULL,
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`database_id`,`table_id`,`key_name`,`column_id`)
) TYPE=MyISAM;

Field	Type	Description
database_id	STRING	The name of the database.
table_id	STRING	The name of the table.
key_name	STRING	As a table can have more than one key each one requires a unique identifier. The primary key is given the name 'PRIMARY'.
column_id	STRING	This is the name of the column that appears within this key. A column may appear only once in any key, but it may appear in more than key for the same table.
seq_in_index	NUMERIC	A key may be comprised of more than one column, so this is the sequence number of this column within the key.
is_unique	BOOLEAN	Keys may be unique or non-unique. This identifies if the key is unique or not.
column_seq	NUMERIC	This is the sequence number of the entry returned by the SHOW INDEX statement which was issued to the database.
The following fields are set automatically by the system:
created_date	DATE+TIME	The date and time on which this record was created.
created_user	STRING	The identity of the user who created this record.
revised_date	DATE+TIME	The date and time on which this record was last changed.
revised_user	STRING	The identity of the user who last changed this record.

RELATIONSHIP table

The structure of this table is as follows:

CREATE TABLE `dict_relationship` (
  `database_id_snr` varchar(64) NOT NULL default '',
  `table_id_snr` varchar(64) NOT NULL default '',
  `database_id_jnr` varchar(64) NOT NULL default '',
  `table_id_jnr` varchar(64) NOT NULL default '',
  `seq_no` tinyint unsigned NOT NULL default '0',
  `table_alias_snr` varchar(64) default NULL,
  `table_alias_jnr` varchar(64) default NULL,
  `relation_desc` varchar(255) default NULL,
  `comment` text,
  `rel_type` char(3) NOT NULL default '',
  `orderby` varchar(64) default NULL,
  `parent_field` varchar(64) default NULL,
  `calc_field` varchar(255) default NULL,
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) default NULL,
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`database_id_snr`,`table_id_snr`,`database_id_jnr`,`table_id_jnr`,`seq_no`)
) TYPE=MyISAM;

Field	Type	Description
database_id_snr	STRING	The identity of the parent/senior database in the relationship.
table_id_snr	STRING	The identity of the parent/senior table in the relationship.
database_id_jnr	STRING	The identity of the child/junior database in the relationship.
table_id_jnr	STRING	The identity of the child/junior table in the relationship.
seq_no	NUMERIC	If there is more than one relationship between the same tables - in other words, it has duplicates - then this value is a means of making the key unique. The default value is zero for the first entry, and should be incremented for duplicate entries.
table_alias_snr	STRING	Where multiple relationships between the same tables exist this is a means of providing an alias for the parent/senior table.
table_alias_jnr	STRING	Where multiple relationships between the same tables exist this is a means of providing an alias for the child/junior table.
relation_desc	STRING	A user-defined description for the relationship.
comment	TEXT	User-defined comments for the relationship.
rel_type	STRING	This specifies how the relationship is to be treated when deleting entries from the paren/senior table. Possible values are: 'RESTRICTED' - cannot delete the parent entry if any associated entries exist on this child table. 'CASCADE' - when the parent entry is deleted all associated entries on this child table will also be deleted. These entries will be processed in the sequence specified in ORDERBY. 'NULLIFY' - when the parent entry is deleted all associated entries on this child table will have their foreign key set to NULL.
orderby	STRING	This is only used when REL-TYPE = 'CASCADE'. It identifies the sequence in which child entries will be processed when they are deleted.
parent_field	STRING	When dealing with a single occurrence from the child table there is code in the standard table class which will attempt to access the parent (foreign) table and bring back one of its fields for inclusion in the data array for the child. This is used to specify which field to return. Possible values area: ' ' - do not perform any lookup on the parent table. '<column>' - perform a lookup and return the named field. 'CALCULATED' - use the contents of CALC_FIELD as the field name.
calc_field	STRING	This is only used when PARENT-FIELD = 'CALCULATED'. It provides a means of returning a value from the parent table which is not one of the existing field names. For example: 'fieldname AS aliasname' - can be used where multiple relationships exist in order to return a different fieldname from each relationship. 'CONCAT(field1, " ", field2) AS field3' - can be used to return a derived or calculated value.
The following fields are set automatically by the system:
created_date	DATE+TIME	The date and time on which this record was created.
created_user	STRING	The identity of the user who created this record.
revised_date	DATE+TIME	The date and time on which this record was last changed.
revised_user	STRING	The identity of the user who last changed this record.

RELATED-COLUMN table

The structure of this table is as follows:

CREATE TABLE `dict_related_column` (
  `database_id_snr` varchar(64) NOT NULL default '',
  `table_id_snr` varchar(64) NOT NULL default '',
  `column_id_snr` varchar(64) NOT NULL default '',
  `database_id_jnr` varchar(64) NOT NULL default '',
  `table_id_jnr` varchar(64) NOT NULL default '',
  `seq_no` tinyint unsigned NOT NULL default '0',
  `column_id_jnr` varchar(64) default NULL,
  `seq_in_index` tinyint(4) unsigned NOT NULL default '0',
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_user` varchar(16) default NULL,
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`database_id_snr`,`table_id_snr`,`column_id_snr`,`database_id_jnr`,`table_id_jnr`,`seq_no`)
) TYPE=MyISAM;

Field	Type	Description
database_id_snr	STRING	The name of the parent/senior database in the relationship.
table_id_snr	STRING	The name of the parent/senior table in the relationship.
column_id_snr	STRING	The name of a column in the primary key of the parent/senior table.
database_id_jnr	STRING	The name of the child/junior database in the relationship.
table_id_jnr	STRING	The name of the child/junior table in the relationship.
seq_no	NUMERIC	If there is more than one relationship between the same tables - in other words, it has duplicates - then this value is a means of making the key unique. The default value is zero for the first entry, and should be incremented for duplicate entries.
column_id_jnr	STRING	The identity of a column in the child/junior table which corresponds with COLUMN_ID_SNR.
seq_in_index	NUMERIC	The primary key on the parent table may be comprised of more than one column, so this is the sequence number of this column within the key.
The following fields are set automatically by the system:
created_date	DATE+TIME	The date and time on which this record was created.
created_user	STRING	The identity of the user who created this record.
revised_date	DATE+TIME	The date and time on which this record was last changed.
revised_user	STRING	The identity of the user who last changed this record.

Export Files

My whole development methodology is built around the philosophy of having a separate class for each database table. Some OO proponents consider this approach to be pure heresy, but I choose to ignore them.

Files <tablename>.class.inc and <tablename>.dict.inc are created by the Export Table to PHP process.

File <dbname>.dict_export.inc is created by the Export Database process.

<tablename>.class.inc

All the standard functionality for accessing a database table is contained within an abstract superclass, so all that is required for each individual table is a simple script that extends this into a subclass, as shown in the sample below:

<?php
require_once 'std.table.class.inc';

class #tablename# extends Default_Table
{
    // ****************************************************************************
    // class constructor
    // ****************************************************************************
    function #tablename# ()
    {
        // save directory name of current script
        $this->dirname     = dirname(__file__);
        
        $this->dbms_engine = $GLOBALS['dbms'];
        $this->dbname      = '#dbname#';
        $this->tablename   = '#tablename#';
        
        // call this method to get original field specifications
        // (note that they may be modified at runtime)

        $this->fieldspec = $this->getFieldSpec_original();
        
    } // #tablename#
    
// ****************************************************************************
} // end class
// ****************************************************************************
?>

During the export process the strings #dbname# and #tablename# are replaced with the relevant values. As there is usually only one DBMS engine for each installation this is defined in the CONFIG.INC file as a global variable, but this may be manually altered within individual table classes should it be necessary.

The getFieldSpec_original() method is defined within the superclass and will load the contents of <tablename>.dict.inc into the class instance at runtime using the following code:

    function getFieldSpec_original () 
    // set the specifications for this database table.
    {
        $fieldspec                = array();
        $this->primary_key        = array();
        $this->unique_keys        = array();
        $this->child_relations    = array();
        $this->parent_relations   = array();
        $this->audit_logging      = FALSE;
        $this->default_orderby    = '';
        $this->alt_language_table = '';
        $this->alt_language_cols  = '';
		
        if ($this->getTableName() != 'default') {
            // include table specifications generated by Data Dictionary

            require ($this->dirname .'/' .$this->getTableName() .'.dict.inc');
        } // if
        
        return $fieldspec;
        
    } // getFieldSpec_original

This subclass, when combined with the standard code inherited from the superclass, is enough to provide all the code necessary to perform insert, select, update and delete operations on that database table, with all primary validation being performed using the rules provided by the contents of <tablename>.dict.inc.

The default processing for any table can be modified by inserting the required code into the customisable methods (identified by the prefix '_cm_') which have been defined as empty stubs within the superclass. The default processing contains calls to these empty methods at various stages of its processing, as shown in UML diagrams for the Radicore Development Infrastructure. Simply copy the empty method from the superclass to the subclass and whatever code you place within it will be executed at runtime instead of the empty original.

Note that should the table details be re-exported from the data dictionary then this file will NOT be overwritten in order to preserve any manual amendments.

<tablename>.dict.inc

This file contains information that was contained within the Data Dictionary at the time the EXPORT function was processed. Note that should the table details be re-exported from the data dictionary then this file will be overwritten, so any manual amendments will be lost. Changes to any part of a table's structure should instead be defined in the _cm_changeConfig() method of the table's class file.

This file is presented in a format which is readily accessible to PHP scripts. The structure of this file is as follows:

<?php
    // file created on May 30, 2005, 10:45 am
    
    // field specifications for table dbname.tblname
    $fieldspec['fieldname1']      = array('keyword1' => 'value1',
                                          'keyword2' => 'value2');
    $fieldspec['fieldname2']      = array('keyword1' => 'value1',
                                          'keyword2' => 'value2');
    
    // primary key details 
    $this->primary_key            = array('field1','field2');
    
    // unique key details 
    $this->unique_keys[]          = array('field1','field2');
    $this->unique_keys[]          = array('field3','field4');
    
    // child relationship details 
    $this->child_relations[]      = array('child' => 'tblchild',
                                          'dbname' => 'dbchild',
                                          'subsys_dir' => 'dirname',
                                          'alias' => 'tblchild_alias',
                                          'type' => 'RES/CAS/NUL',
                                          'orderby' => 'field1,field2,...',
                                          'fields' => array('fldparent1' => 'fldchild1',
                                                            'fldparent2' => 'fldchild2'));

    $this->child_relations[]      = array(...);
    
    // parent relationship details 
    $this->parent_relations[]     = array('parent' => 'tblparent',
                                          'dbname' => 'dbparent',
                                          'subsys_dir' => 'dirname',
                                          'alias' => 'tblparent_alias',
                                          'parent_field' => 'fieldname',
                                          'alt_language_table' => 'tablename',
                                          'alt_language_cols' => 'fieldname1,fieldname2,...',
                                          'fields' => array('fldchild1' => 'fldparent1',
                                                            'fldchild2' => 'fldparent2'));

    $this->parent_relations[]     = array(...);
    
    // determines if database updates are recorded in an audit log 
    $this->audit_logging          = TRUE/FALSE;
    
    // default sort sequence 
    $this->default_orderby        = 'fieldname1,fieldname2,...';

    // alternative language options 
    $this->alt_language_table     = 'tablename';
    $this->alt_language_cols      = 'fieldname1,fieldname2,...';
    
    // finished

?>

Each individual component of this file is described below.

$fieldspec array

This has the following format:

// field specifications for table dbname.tblname
$fieldspec['fieldname1']  = array('keyword1' => 'value1',
                                  'keyword2' => 'value2');

$fieldspec['fieldname2']  = array('keyword1' => 'value1',
                                  'keyword2' => 'value2');

An entry is created here for each field (column) within the database table. Each entry has an array of keywords and values which identify how the field is to be dealt with as it passes in and out of the application. Please note the following:

Most of these keywords are optional and may not be defined for every field.
Some keywords are dependent on the settings of other keywords.
Where a value is expressed as '=y' this is used just to set the keyword as a key in the array. The value itself is irrelevant. To turn the keyword off it is necessary to remove it from the array completely and not to set the value to something other than 'y'.

The meanings of each entry are:

Keyword	Value
type	The identifies the field type. Possible values are: STRING - this covers CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. INTEGER - this covers TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT. NUMERIC - this covers DECIMAL and YEAR. FLOAT - this covers FLOAT, DOUBLE and REAL. DATE - this covers DATE. TIME - this covers TIME. DATETIME - this covers DATETIME. TIMESTAMP - this covers TIMESTAMP. BOOLEAN - this covers BOOLEAN, CHAR(1) and TINYINT(1). BLOB - this covers TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB and VARBINARY. ENUM - this is for ENUM. SET - this is for SET. ARRAY - this is the PostgreSQL equivalent of SET.
size	The size of the field. This is used to set the MAXLENGTH and SIZE options for the HTML control. During validation of user input any value which exceeds this length will be rejected.
precision	For numeric values this is the number of digits, including decimals. For example, the value 999.99 has a precision of 5.
scale	For numeric values this is the number of digits that can be stored following the decimal point. For example, the value 999.99 has a scale of 2.
zerofill=y	Available for unsigned numeric fields only. Causes leading zeros to be shown as '0' instead of ' ' (blank).
blank_when_zero=y	Available for numeric fields only. Causes a zero value to be displayed as ' ' (blank).
auto_increment=y	For numeric fields only. On an INSERT the database will obtain the next sequence number.
minvalue	During the validation of user input this is the minimum value that can be accepted.
unsigned=y	During the validation of user input this will cause any negative values to be rejected.
maxvalue	During the validation of user input this is the maximum value that can be accepted.
pkey=y	This indicates that this field is part of the primary key and cannot be changed once it has been written to the database.
required=y	This will cause an empty input field to be rejected unless: a DEFAULT value has been specified. AUTO_INCREMENT=Y AUTOINSERT=Y
default	The default value for this column as defined within the database. If IS_REQUIRED is TRUE and no value is supplied on an INSERT, this default value will be used.
password=y	This is used for HTML controls only. It causes user input to be masked.
uppercase=y	This will cause all user input to be shifted into uppercase before being written to the database.
lowercase=y	This will cause all user input to be shifted into lowercase before being written to the database.
autoinsert=y	This is only used when new records are inserted. A value will automatically be inserted according to the field's TYPE: DATETIME - the current date and time will be inserted. DATE - the current date will be inserted. STRING - the current user's identity will be inserted. OTHER - no action will be taken.
autoupdate=y	Same as for AUTOINSERT, but for updates.
infinityisnull=y	This is valid for columns of type DATE only. It means that a blank date on the screen will be held in the database as '9999-12-31' instead of '0000-00-00'. See Dealing with null End Dates for a detailed explanation.
true	For BOOLEAN fields this identifies how a TRUE value will be recorded in the database. It may be '1' or 'T' or 'Y', for example.
false	For BOOLEAN fields this identifies how a FALSE value will be recorded in the database. It may be '0' or 'F' or 'N', for example.
subtype	This optional setting is available only for fields of type STRING. Possible values are: 'FILE NAME' - no action (yet). 'E-MAIL' - will be checked against the pattern for e-mail addresses. 'IMAGE' - will display the file as an image (see also IMAGE_WIDTH and IMAGE_HEIGHT). NOTE: if CONTROL=FILEPICKER and SUBTYPE=IMAGE the HTML output will show the following: A text box for the file name. A popup button to run the file picker task (in input and amendment screens). The file as an image. If in some screens it is required to display the image without the file name, the text can be suppressed by adding `'notext' => 'y'` to the `$fieldspec` array for that field.
custom_validation	This option is available for any field. It identifies the validation method to be used for this field. The format is 'file/class/method' where: file = the name of the file which contains the validation class. class = the name of the validation class. method = the name of the method within the validation class. Refer to Extending the Validation class for more details.
image_width	Only valid if SUBTYPE='IMAGE'. Identifies the width of the image in pixels.
image_height	Only valid if SUBTYPE='IMAGE'. Identifies the height of the image in pixels.
control	This identifies the HTML control to be used when the field is built into a screen (and is amendable). Possible values are: 'DROPDOWN' - options will be displayed in a scrollable list. 'MULTIDROP' - a dropdown list that allows multiple items to be selected (see also MULTI_ROWS). 'RADIO' - options will be displayed as a group of radio buttons. 'CHECKBOX' - a box that will be ticked to represent ON/YES/TRUE and unticked to represent OFF/NO/FALSE. 'MULTILINE' - will be displayed as a multi-line text box instead of a single line (see also COLS and ROWS). 'POPUP' - a picklist that will be displayed as another form/screen instead of a dropdown list due to the large number of available options. 'FILEPICKER' - a picklist that will present a list of file names from a particular directory. 'HYPERLINK' - a string that will be displayed as a hyperlink, but for tasks of type 'input', 'update' and 'search' it will be displayed as an editable text box. If this is not specified the default control will be a standard TEXT BOX.
optionlist	This is only valid when CONTROL='DROPDOWN', 'MULTIDROP' or 'RADIO'. It is the name the list of items that will be used to populate that HTML control. This list should be constructed within the table class and written out to the XML file.
label	This is only valid when CONTROL='CHECKBOX'. It provides an optional piece of text that will be placed either to the left or right of the control depending on ALIGN_LR.
task_id	If CONTROL = 'FILEPICKER' or 'POPUP' this is the name of the TASK that will be activated to provide the picklist of available options.
foreign_field	If the CONTROL = 'POPUP' this is name of a field on the foreign table that will be merged with the contents of the current table before being output to the XML file.
align_hv	If the CONTROL = 'RADIO' this identifies how the list of options should be aligned. Possible values are: 'V' - Vertical. 'H' - Horizontal (default).
align_lr	If the CONTROL = 'RADIO' this identifies whether the text for each button goes on the left or the right. If the CONTROL = 'CHECKBOX' this identifies whether the optional CHECKBOX_LABEL goes on the left or the right. Possible values are: 'L' - Left. 'R' - Right (default).
cols	If the CONTROL = 'MULTILINE' this identifies the width of the text box in columns.
rows	If the CONTROL = 'MULTILINE' this identifies the height of the text box in rows (lines). If the CONTROL = 'DROPDOWN' or 'MULTIDROP' this identifies the height of the scrollable area in rows (lines).
noedit=y	This is used when building the input form for the user. This field will be set to read-only.
nodisplay=y	This is used when building the input form for the user. This field will be left out of the form.
nosearch=y	This is used when building SEARCH screens. It will cause the field to be left out of the form.
noaudit=y	This is used when recording database updates in the Audit Log. If this is set then the actual value will be replaced by a series of asterisks (*).

The $fieldspec array has several uses:

It identifies all the fields which exist on the table, therefore when the contents of the POST array are passed to an object for processing it is able to filter out any non-database fields (such as the SUBMIT button, for example) before preparing the SQL statement.
Because the field(s) which form the primary key are identified it is easy to extract from the POST array the correct WHERE clause for a database UPDATE or DELETE.
Because this array identifies all the fields and their field types it is possible to have a standard function which performs primary validation. This can check that required fields are not empty, date fields contain dates, numeric fields contain numbers, et cetera. Secondary validation, where one field is compared with another, requires custom code to be inserted into the relevant validate() method in the entity class.
Information from this array is also written out to the XML document so that it can be used during the XSL transformation process which constructs the HTML output.
- The size option will be used to set the field size, for example.
- The pkey option means that the field will be editable only on INSERT and SEARCH screens.
- The noedit option will cause the field to be rendered as non-editable.
- The nodisplay option will cause the field to be completely omitted from the HTML output.
- The nosearch option will cause the field to be removed from search screens.
- By default the HTML control type used for editable fields will be 'text', but this can be changed to dropdown list, checkbox, radio group or whatever.
- For radio groups there are options to display the group either vertically or horizontally, and to place the text for each button either on the left or the right.
- For checkboxes there is the option to include an additional label, and to place this text either on the left or the right.
It is possible for the contents of the $fieldspec array to be modified at runtime. Thus it is possible to change a field from editable to non-editable, or even hide it altogether. It is even possible to change the HTML control type - in one application I have a field which may vary between a multi-line text box, a dropdown list or a number depending on different settings. Code in the entity class detects what is required and alters the $fieldspec array accordingly. This information is written out to the XML document so it can be actioned by the XSL transformation.

$primary_key array

This has the following format:

// primary key details 
$this->primary_key        = array('field1','field2');

This is an indexed array containing one or more field names which together form the primary key of this database table.

This information is used just before an INSERT to check that a record with this key does not currently exist, otherwise an error message will be returned to the user. If the primary key contains a field with AUTO-INCREMENT=TRUE then this check will not be performed.

$unique_keys array

This has the following format:

// unique key details 
$this->unique_keys[]      = array('field1','field2');
$this->unique_keys[]      = array('field3','field4');

These are unique keys that are in addition to the primary key, and are sometimes known as candidate keys. As a table may have zero or more additional keys this array may be empty, or it may contain a separate entry for each additional key. Each entry will itself be an array containing one or more field names.

This information is used just before an INSERT or UPDATE to check that a record with each key does not currently exist, otherwise an error message will be returned to the user.

$child_relations array

This has the following format:

// child relationship details 

$this->child_relations[]  = array('child' => 'tblchild',
                                  'dbname' => 'dbchild',
                                  'subsys_dir' => 'dirname',
                                  'alias' => 'tblchild_alias',
                                  'type' => 'RES/CAS/NUL',
                                  'orderby' => 'field1,field2,...',
                                  'fields' => array('fldparent1' => 'fldchild1',
                                                    'fldparent2' => 'fldchild2'));


$this->child_relations[]  = array(...);

An entry is created here for each instance where this table is defined as the parent in a parent-to-child (one-to-many, senior-to-junior) relationship. Note that a table can be the parent in any number of relationships. The meanings of each entry are:

'child' => 'tblchild'	tblchild identifies the name of the child table in this parent-child relationship.
'dbname' => 'dbchild'	Not currently used (for information only).
'subsys_dir' => 'dirname'	If this child table does not reside in the same directory as this parent then dirname provides the directory name from the SUBSYSTEM entry. This is used on an include() statement to identify the location of the class file so that an object of this child table class can be instantiated.
'alias' => 'tblchild_alias'	It is possible for more than one relationship to exist between the same pair of tables, so this is a means of giving each occurrence of the duplicated child table an alias name as an aid to identification. The tblchild_alias value is taken from the TABLE_ALIAS_JNR column of the RELATIONSHIP table.
'type' => 'RES/CAS/NUL'	This identifies the type of processing to be performed when an attempt is made to delete on entry from the parent table. The possible values are: RESTRICTED - do not allow the parent to be deleted if any associated records on this child table exist. CASCADE - when the parent is deleted then also delete all associated records on this child table. NULLIFY - when the parent is deleted then update all associated records on this child table by setting the foreign key field(s) to NULL.
'orderby' => '... , ...'	This is an optional list of field names separated by commas. It is only relevant when 'type' => 'CAS' as it identifies the order in which the occurrences from the child table should be retrieved before they are deleted.
'fields' => array( 'fldparent1' => 'fldchild1', 'fldparent2' => 'fldchild2')	In any parent-child relationship the primary key field(s) in the parent table must be linked to corresponding field(s) in the child table. Each entry in this array identifies which field from the parent table (fldparentN) is associated with which field in the child table (fldchildN). Note that this structure allows for the fact that corresponding fields may not have the same name in the two tables.

$parent_relations array

This has the following format:


// parent relationship details 
$this->parent_relations[] = array('parent' => 'tblparent',
                                  'dbname' => 'dbparent',
                                  'subsys_dir' => 'dirname',
                                  'alias' => 'tblparent_alias',
                                  'parent_field' => 'fieldname',
                                  'alt_language_table' => 'tablename',
                                  'alt_language_cols' => 'fieldname1,fieldname2,...',
                                  'fields' => array('fldchild1' => 'fldparent1',
                                                    'fldchild2' => 'fldparent2'));


$this->parent_relations[] = array(...);

An entry is created here for each instance where this table is defined as the child in a parent-to-child (one-to-many, senior-to-junior) relationship. Note that a table can be the child in any number of relationships. The meanings of each entry are:

'parent' => 'tblparent'	tblparent identifies the name of the parent table in this parent-child relationship.
'dbname' => 'dbparent'	If this parent table does not belong in the same database then dbparent is used as the qualifier in the JOIN clause of the sql SELECT statement which is generated by the system (refer to Using Parent Relations to construct sql JOINs for details).
'subsys_dir' => 'dirname'	If this parent table does not reside in the same directory as this child then dirname provides the directory name from the SUBSYSTEM entry. This is used on an include() statement to identify the location of the class file so that an object of this parent table class can be instantiated.
'alias' => 'tblparent_alias'	It is possible for more than one relationship to exist between the same pair of tables, or for a table to be related to itself, so this is a means of giving each occurrence of the duplicated parent table an alias name as an aid to identification. The tblparent_alias value is taken from the TABLE_ALIAS_SNR column of the RELATIONSHIP table. Refer to Using Parent Relations to construct sql JOINs for examples of specifying alias names.
'parent_field' => '...'	In some cases it is a common requirement that when reading an occurrence of the child table that a lookup be performed on the parent table in order to bring back one or more fields from the parent table (for example, to bring back a COUNTRY_NAME for a given COUNTRY_CODE). This structure provides the means for this to be performed automatically by the standard code (refer to Using Parent Relations to construct sql JOINs).
'fields' => array( 'fldchild1' => 'fldparent1', 'fldchild2' => 'fldparent2')	In any parent-child relationship the primary key field(s) in the parent table must be linked to corresponding field(s) in the child table. Each entry in this array identifies which field from the parent table (fldparentN) is associated with which field in the child table (fldchildN). Note that this structure allows for the fact that corresponding fields may not have the same name in the two tables.
'alt_language_table' => '...' 'alt_language_cols' => '...'	This identifies those columns on this table which are also available on another table in an alternative language.

The information in the $parent_relations array is used to help construct sql SELECT statements, as explained in Using Parent Relations to construct sql JOINs.

$audit_logging switch

This has the following format:

// determines if database updates are recorded in an audit log 
$this->audit_logging      = TRUE/FALSE;

This switch can either be TRUE or FALSE. If TRUE then any INSERT, UPDATE or DELETE operation on this table will cause the Data Access Object (DAO) to pass control to the Audit Logging system so that the changes can be logged in the AUDIT database. These changes will then be available for viewing using standard online enquiry screens.

$default_orderby string

This has the following format:

// default sort sequence 
$this->default_orderby    = 'fieldname1,fieldname2,...';

This is a string containing zero or more field names. When occurrences of this table are retrieved for LIST screens this will be used as the sort sequence unless other sorting criteria has been specified either for the TASK or by the use of the column headings.

$alt_language strings

This has the following format:

// alternative language options 
$this->alt_language_table       = 'tablename';
$this->alt_language_cols        = 'fieldname1,fieldname2,...';

These two strings identify the fact that this table has columns which have translations in alternative languages on another table.

<dbname>.dict_export.inc

It is sometimes necessary to take the details which have been entered into one data dictionary and copy them into another. Typing in the details manually would be too cumbersome, and copying the entire dictionary database may include unwanted details. Another option would be to export the details for selected application databases into an SQL script which can then be processed by whatever utility comes with your database engine.

This facility will create an SQL script containing INSERT/REPLACE statements for each record in the data dictionary which belongs to the selected application database. This script can then be used to import the same details into another dictionary database.

Using this function to provide an export of dictionary data would be a good idea before the erase function is used.

Using Parent Relations to construct sql JOINs

Accessing the database is performed by constructing a query string within the data access layer and sending this to the database via the relevant API. Because this string is constructed from several smaller substrings which are generated in the business layer it is possible to incorporate the details of any parent relationships so that the sql SELECT statement which is constructed will include the relevant JOIN clauses to bring back the specified field(s) from the parent table(s). It is also possible to specify alias names to deal with those situations when a table appears more than once in the same SELECT statement.

The full query string is constructed using the following substrings:

$query = "SELECT $select_str
            FROM $from_str 
                 $where_str 
                 $group_str 
                 $having_str 
                 $sort_str 
                 $limit_str";

These are described in more detail in How to extend the SQL SELECT statement.

Just before the business layer component passes control to the data access layer to retrieve data from the database it will check to see if it needs to include any JOIN clauses for parent relations. The actual sequence of events is as follows:

If $from_str is not empty then skip this processing. This means that the developer has provided a customised SELECT string and does not want the system to automatically add in any extra JOIN clauses.
If the $parent_relations array is empty then there is nothing to process.
If $select_str is empty then insert <tablename>.*.
Insert <tablename> into $from_str.
For each entry in the $parent_relations array do the following:
1. If nothing is defined for parent_field then ignore this entry. If no data is going to be retrieved from this parent table then there is no point in JOINing to it.
2. Append the contents of parent_field to the contents of $select_str.
3. Append 'LEFT JOIN <tblparent>' to the contents of $from_str.
4. If an alias is defined then append ' AS <alias>' to the contents of $from_str.
5. Iterate through the fields array to construct the ' ON (tblparent.fldparent=tblchild.fldchild) clause, and append it to the contents of $from_str.

If all these substrings are still empty by the time they reach the data access layer, then:

Set $select_str to '*'.
Set $from_str to the current table name.

Here are some examples of SELECT strings which can be generated by the system when no customised substrings are supplied:

A standard SELECT without any JOINs:
```
SELECT * FROM x_tree_node WHERE node_id='23'
```
If there are no parent relations then no JOIN clauses can be constructed.

SELECT with a simple join:

SELECT x_tree_node.*, 
       tree_level_seq, tree_level_desc
FROM x_tree_node 
LEFT JOIN x_tree_level ON (x_tree_level.tree_type_id=x_tree_node.tree_type_id 
                      AND x_tree_level.tree_level_id=x_tree_node.tree_level_id)
WHERE x_tree_node.node_id='23'

In this example x_tree_level is the parent and x_tree_node is the child. Note here that more than one field is being retrieved from the parent table. It is also possible to specify a calculated field, as in

CONCAT(first_name, ' ', last_name) AS person_name

There is no need to qualify these field names unless they also appear on other tables in the same SELECT statement.

SELECT with a table joining to itself:
```
SELECT x_tree_node.*, 
       x_tree_node_snr.node_desc AS node_desc_snr 
FROM x_tree_node
LEFT JOIN x_tree_node AS x_tree_node_snr 
       ON (x_tree_node_snr.node_id=x_tree_node.node_id_snr)
WHERE x_tree_node.node_id='23'
```
In this example the table x_tree_node (primary key node_id) is joined to itself by field node_id_snr which contains the identity of the parent node within the hierarchy of senior-to-junior nodes. Because the table name x_tree_node will appear twice in the same SELECT statement the parent entry is given the alias name of x_tree_node_snr. Because the field name node_desc will appear twice in the same SELECT statement the parent entry is given the alias name of node_desc_snr. Note that this also has to be qualified with the table's alias name.

SELECT with multiple JOINs to the same table:

SELECT mnu_nav_button.*, 
       mnu_task_snr.task_desc AS task_desc_snr, 
       mnu_task_jnr.task_desc AS task_desc_jnr

FROM mnu_nav_button 
LEFT JOIN mnu_task AS mnu_task_snr 
       ON (mnu_task_snr.task_id=mnu_nav_button.task_id_snr) 
LEFT JOIN mnu_task AS mnu_task_jnr 
       ON (mnu_task_jnr.task_id=mnu_nav_button.task_id_jnr)  
WHERE mnu_nav_button.task_id_snr='mnu_dialog_type(list)'   

ORDER BY mnu_nav_button.sort_seq asc

In this example the table mnu_nav_button is related to table mnu_task twice, using a field with either a '_snr' or '_jnr' suffix. Each occurrence of the table name is therefore given an alias which, by coincidence, also includes the same suffix. Note also that this requires the field name(s) which are to be retrieved from each parent table to be given the same qualifier as that table's alias.

SELECT where tables have alternative language options

SELECT x_tree_node.node_id, x_tree_node.tree_type_id, x_tree_node.tree_level_id, ...,
       COALESCE(...) AS node_desc, 
       COALESCE(...) AS tree_level_desc, 
       COALESCE(...) AS node_desc_snr, 
       COALESCE(...) AS tree_type_desc 
FROM x_tree_node 
LEFT JOIN x_tree_level ON (x_tree_level.tree_type_id=x_tree_node.tree_type_id 
                       AND x_tree_level.tree_level_id=x_tree_node.tree_level_id) 
LEFT JOIN x_tree_node AS x_tree_node_snr ON (x_tree_node_snr.node_id=x_tree_node.node_id_snr) 
LEFT JOIN x_tree_type ON (x_tree_type.tree_type_id=x_tree_node.tree_type_id) 
WHERE ( x_tree_node.node_id='44' )

where each COALESCE statement is in the format:

COALESCE((SELECT <field> 
          FROM <table_alt> 
          WHERE <table_alt>.<foreign_key>=<table>.<primary_key> 
            AND <table_alt>.language_id='??')
         , <table>.<field>) AS <field>

All of the above is standard SQL, therefore should work in any standards-compliant database engine.

Back to Index Page