Using PHP Objects to Access Your Database Tables (Part 2)
By Tony Marston2005-04-07
Adding the features of a Data Dictionary/Repository
|
|
A Data Dictionary or Data Repository is a collection of descriptions of the data objects or items in a data model which can be used by both the programmers who are writing the code and the language in which they are coding. It describes all the tables in the database, the columns in each table, it identifies which columns form primary keys, candidate keys and indexes, it identifies which tables are related to one another, on which columns they are related, and what action to take when a row with existing relations is deleted.
As the information within a data dictionary is not actual application data (that which passes from the user to the database and back again) but is instead information about the application data, it is sometimes referred to as meta-data, or data-about-data.
Background
I first encountered a language which used a Data Dictionary in the early 1980s after using COBOL, a well known 3rd generation language (3GL) for several years. It made programming a lot easier because the language "knew" things about the data it was asked to manipulate and could do certain things automatically. If a field was a date, for example, it would only accept a valid date as input, and would automatically format it for display according to the specification in the dictionary. Similarly for numbers it would reject non-numeric input and automatically display it with the specified number of decimal places, currency symbol, and thousands separator. For integers it would only accept positive whole numbers, and so on and so on.
While teaching myself PHP I read a lot of books and looked at lots of sample code, and I was surprised to see a great deal of repetition when it came to validating user input before sending it to the database. As a self-confessed lazy programmer I do not like the idea of repeating the same or similar code over and over again so I sought a method of simplifying the whole data validation process. What I wanted was the features of a data dictionary so I could define the characteristics of my data and get the language to do what was necessary without further intervention. As you are aware (or should be) PHP does not have any sort of data dictionary, so I had to design and implement one myself.
My first decision was where to store this data dictionary. One thought that crossed my mind was to use a totally separate object, but then I reasoned that the best place to store the definitions of a database table was inside the object I was already building to communicate with that table. This actually fits in with the OO concept of 'encapsulation' which puts all the knowledge, properties and methods of an object in a single place.
My next decision was how to store this data. This was actually a no-brainer as the obvious choice is to use a set of multi-dimensional arrays. In Part 1 of this document I talked about the Class Constructor and introduced the concept of using $fieldlist to hold a list a fields contained within that particular table. I have actually enhanced this to hold much more than a simple list of fields. In fact it holds complete field specifications, so I have renamed it $fieldspec.
The $fieldspec Array
This is a class variable that is built within the class constructor. It is a multi-dimensional array which identifies all the fields (columns) which exist in that database table. For each field it also identifies the type (string, number, date, time), size and any other important characteristics. It is built according to the following format:
$this->fieldspec['fieldname'] = array('keyword' => 'value',
'keyword' => 'value', ...);As you can see this array is able to hold any number of fields, and for each field it is able to hold any number of keyword/value specifications. The list of field specifications includes the following:
array('type' => 'string');
array('size' => 16);
array('required' => 'y');
array('uppercase' => 'y');
array('lowercase' => 'y');This particular one identifies the field as being part of the primary key. Note that a primary key may consist of more than one field.
array('pkey' => 'y');
This next one tells the system to display this as an HTML password field so the user's input is not echoed back:
array('password' => 'y');
If it is a multiline text field this will specify the size of the text box in columns and rows:
array('type' => 'multiline', 'cols' => 50, 'rows' => 5);
Integers can be signed or unsigned, and leading zeros may have to be filled. Be aware that all sizes of integer need to be catered for, from 'int1' all the way up to 'int8'.
array('type' => 'integer', 'unsigned' => 'y', 'zerofill' => 'y');
For numeric fields you may want to specify a minimum and/or maximum value:
array('minvalue' => 0, 'maxvalue' => 999);
Boolean fields may be stored as 'Y/N', 'T/F' or '0/1', so here you can identify which combination is being used:
array('type' => 'boolean', 'true' => 'Y', 'false' => 'N');
If a field needs to be encrypted you may choose between a built-in or custom encryption algorithm:
array('hash' => 'md5|sha1|custom');
For MySQL databases fields of type 'enum' need special handling:
array('type' => 'enum');
Then there are data and time fields. The 'infinityisnull' setting is for end dates when a blank date to the user means 'an unspecified date in the future' and should be stored in the database as '9999-12-31' and not '0000-00-00'. This makes date comparisons very much easier, and it is a technique that I have used for several years.
array('type' => 'time');
array('type' => 'date', 'infinityisnull' => 'y');
The following options can either be built into the $fieldspec array within the class constructor, or added at a later time during execution of particular scripts if particular conditions are met. One makes the field read-only and will not accept any changes while the other will prevent the field from being output to the client's browser, even as a hidden field:
array('noedit' => 'y');
array('nodisplay' => 'y');
The $unique_keys Array
This is a class variable that is built within the class constructor. It is a multi-dimensional array built according to the following format:
$this->unique_keys[] = array('fieldname1', 'fieldname2', ...);
$this->unique_keys[] = array('fieldname5', 'fieldname6', ...);
It is only used if the table contains unique keys which are in additional to the standard primary key. These are known as candidate keys.
A table may contain any number of candidate keys, and each key may be comprised of one or more fields. When a record is inserted or updated the contents of this array is used to check that these keys do not already exist.
The $relationship Array
This is a class variable that is built within the class constructor. It is a multi-dimensional array built according to the following format:
$this->relationship[] = array('many' => 'tablename',
'fields' => array('one_id' => 'many_id',
'one_id' => 'many_id', ...),
'type' => 'nullify|delete|restricted');In a one-to-many or parent-child relationship between two tables these details are defined for the 'one' or 'parent' table to idetify its 'many' or 'child' tables. There can be any number of relationships, so for each it is necessary to first identify the name of the 'many' or 'child' table.
For each relationship you must identify which field(s) on the 'parent' table are related to which field(s) on the 'child' table. Again it is worth pointing out that each relationship may involve more than one field.
The final step is to identify the delete constraint for the relationship.
For this there are 3 options:-
• Nullify - the foreign key field in all related rows in this 'child' table will be set to NULL.
• Delete - any related rows in this 'child' table will also be deleted.
• Restricted - the existence of any related rows in this 'child' table will prevent the entry from the 'parent' table from being deleted.
Tutorial Pages:
» Intended Audience
» Extending the MySQL SELECT statement
» Adding the features of a Data Dictionary/Repository
» Using the Data Dictionary/Repository
» Changing Candidate Keys
» Deleting Rows
» Custom Processing
» Virtual Database Tables
» Summary
|
