MetaObject Structure Prototype

Principal prototype rationale:	Enhance createTableSql to provide a method of adding additional attributes to columns.
Secondary prototype rationale:	Provide improved structure to the table creation process.

All features are stored in loadable classes, located in ADODB_BASE_DIR/metaoptions

Concept

Define table and all its child elements and attributes as a consistent structure. The idea is built on the AXMLS concept, and certainly contains feature overlap.

Naming

To distinguish between the original table creation method and this prototype, The original method is designated as 'Simple' to suggest that it is suitable for quick table design, this new method is called 'Structured', to signify its more formal approach to element creation.

Missing From The Prototype

Ability to create foreign keys
Ability to create constraints
Change of table/column functionality
Attribute validation

Definitions

Element

The element represents each item in the object. Each item can be one of a table,column,index,foreign key,constraint.

class metaElementStructure
{
	public $type;
	public $name;
	public $value;
	public $platform;
        public $action;
	public $attributes = array();
}

Name	Description
$type	The type of element
$name	The name of element
$value	The optional value of the element
$platform	The optional platform indicator
$action	Action to perform on the element (add/change/delete)
$attributes	An array of optional attributes of the elements. Each attribute is also a metaElement

Object

The metaObjectStructure represents the elements and directions necessary to manage a table,column or index.

class metaObjectStructure
{
        public $type;
	public $name;
	public $value;
	public $platform;
	public $attributes = array();
 
}

Each object contains a series of the same objects as children, that represent the elements the define the parent/

Child

A Child is one of the following types, COLUMN,INDEX,INDEX-ITEM,FOREIGNKEY,CONSTRAINT. The child can be added to the metaObjectStructure object using the command:

metaObjectStructure::addColumnObject(column-name,platform)
metaObjectStructure::addIndexObject(index-name,platform)
metaObjectStructure::addIndexItemObject(index,column-name,platform)

t = new metaObjectStructure('test');
/*
* Add the column
*/
$t->addColumnObject('COL1','I');
 
/*
* Create an index, COL1-IDX
*/
$t->addIndexObject('COL1-IDX')
 
/*
* add column COL1 to the index COL1-IDX
*/
$t->addIndexItemObject('COL1-IDX','COL1')

Attribute

Each MetaObjectStructure object can have an unlimited number of *attributes* that can represent anything appropriate to be added to the definition of the object. The attribute defined can be platform-specific. Samples for a MySQL database could be:

Assigned To	Attribute
TABLE	ENGINE
TABLE	DISABLE KEYS
COLUMN	NOT NULL (PORTABLE ATTRIBUTE)
COLUMN	COLLATE “ascii”
INDEX	USING BTREE
INDEX-ITEM	DESC

Portable vs Non-Portable Attributes

Each metaElement can be assigned attributes. The attributes are processed and attached to the statement to create the necessary creation code. The attribute can be assigned to one of the following: TABLE,COLUMN,INDEX,INDEX-ITEM,CONSTRAINT,FOREIGNKEY and is assigned in the following way:

metaObjectStructure::addAttribute(name, value, platform)

The value can be a string, numeric or associative array.
The platform can be null or indicated a provider match

 $column->addAttribute('AUTO');
 $column->addAttribute(array('CHARACTER SET'=>'"ascii"'));

Portable Attributes

Portable attributes are comparable to the options available to the original createTableSql. Each option is controlled by a loadable class, the metaOption. The name of the class to handle a portable option is constructed from metaOption_ + the value to check, e.g.

metaOption_AUTO, metaOption_DEFDATE etc

Loading of the classes is controlled by an autoloader list. search sequence:

ADODB_BASE_DIR/metaoptions/driver
ADODB_BASE_DIR/metaoptions/dataprovider
ADODB_BASE_DIR/metaoptions

If there is no class match, the class ADODB_BASE_DIR/metaoptions/metaOption_CUSTOM is loaded.

This design allows the following feature:

If an attribute is designated as Portable, the only action required is to create a class to represent the attribute, containing the default behavior of the attribute and placing it in the metaoptions directory. Any driver or provider extensions can be created as standalone overrides, without being concerned about conflicting behavior. If a feature is portable no platform designation is generally necessary but can still be used.
Any other attribute can be passed, and will be treated as non-portable. Platform considerations can be made on an as-needed basis

In the following example, we create a column, and add 3 attributes to it. 1 attribute is portable and 2 are custom.

/*
* We have defined a metaObjectStructure, that represents
* a table $t
*/
 
/*
* We now add an object representing the column, COL1 of type Character(32)
*/
$c = $t->addColumnObject('COL1','C(32)');
 
/*
* now add a portable attribute DEFAULT
* This could also be passed as a string "DEFAULT 'SOMEVALUE'")
*/
$c->addAttribute(array('DEFAULT'=>'SOMEVALUE');
 
/*
* now add a custom attribute to column, applicable to all platforms
*/
$c->addAttribute(array('CHARACTER SET'=>'"ascii"'));
 
/*
* Now add another attribute, which is only used if the database type is SQL Server
*/
$c->addAttribute('SPARSE','mssqlnative');

Processing The MetaObjectStructure

The structure is not automatically sent to createTableSql. The object must be deliberately passed to createTableSql. This is important because it allows the returned object to re-used or stored, for example by serializing or JSON encoding the resulting object. Conceivably, a JSON encoded object could be passed in by a 3rd party application and used.

In this example, we create a table, 'TEST' with a single column 'COL1' and extract the structure

$t = new metaObjectStructure('test');
/*
* Make sure the table is transactional in MySQL
*/
$t->addAttribute('table','','ENGINE INNODB','mysql');
 
/*
* Add the column
*/
$t->addColumnObject('COL1','I');
 
/*
* Now get the structure
*/
print_r($t);
 
/*
* The following object is returned
*/
metaObjectStructure Object
(
    [type] => table
    [value] => 
    [platform] => 
    [options] => Array
        (
        )
 
    [attributes] => Array
        (
            [0] => metaElementStructure Object
                (
                    [type] => table
                    [name] => test
                    [value] => ENGINE INNODB
                    [platform] => mysql
                    [action] => 0
                    [attributes] => Array
                        (
                        )
 
                )
 
        )
 
    [name] => test
    [action] => 0
    [columns] => Array
        (
            [COL1] => metaObjectStructure Object
                (
                    [type] => column
                    [value] => I
                    [platform] => 
                    [options] => Array
                        (
                        )
 
                    [attributes] => Array
                        (
                        )
 
                    [name] => COL1
                    [action] => 0
                )
 
        )
 
)
 
 
We could json_encode it here and store it
*/
$j = json_encode($def)
/*
{"type":"table",
 "value":"",
 "platform":"",
 "options":[],
 "attributes":[{
    "type":"table",
    "name":"test",
    "value":"ENGINE INNODB",
    "platform":"mysql",
    "action":0,
    "attributes":[]
    }],
    "name":"test",
    "action":0,
    "columns":{"COL1":{"type":"column",
                       "value":"I",
                       "platform":"",
                       "options":[],
                       "attributes":[],
                       "name":"COL1",
                       "action":0
                       }
              }
}
*/

Processing The Structure

A change to createTableSql() that allows the object to be passed as the first argument is all that is necessary to process the result

$sql = $dict->createTableSql($tabledef);
 
print_r($sql);
/*
* Returns:
Array
(
    [0] => CREATE TABLE test (
           COL1 I
          )ENGINE INNODB
)
*/

Complex Example

The following complex example shows the additional functionality of updating indexes, as well as chaining methods

$dict = NewDataDictionary($db);
 
$t = new metaObjectStructure('test');
$t->addAttribute('ENGINE INNODB','mysql');
$t->addColumnObject('COL1','I');
$t->addColumnObject('COL1','I')->addAttribute('NOTNULL');
 
$c = $t->addColumnObject('COL2','C(64)');
$c->addAttribute('NOTNULL');
 
$i = $t->addIndexObject('CIDX');
$ii = $i->addIndexItemObject('COL1');
 
$t->addColumnObject('COL1','C(32)')->addAttribute('NOTNULL')->addAttribute(array('DEFAULT'=>'abc'));
 
$t->addColumnObject('COL2','I')->addAttribute('NOTNULL')->addAttribute('PRIMARY')->addAttribute('AUTO');
$t->addColumnObject('COL3','N(12.2)');
$t->addColumnObject('COL4','C(64)')->addAttribute(array('CHARACTER SET'=>'"ascii"'))->addAttribute(array('COLLATE'=>'"latin1_swedish_ci"'),'mysql');
 
$t->addColumnObject('COL5',"ENUM('cats','dogs','fish')");
$t->addColumnObject('COL6','T')->addAttribute('DEFTIMESTAMP');
 
$t->addColumnObject('COL7','D')->addAttribute('DEFDATE');
 
$t->addIndexObject('COL4-INDEX')->addIndexItem('COL4')->addAttribute('ASC');
$t->addIndexObject('COL4-INDEX')->addIndexItem('COL5');
 
$sql = $dict->createTableSql($tabledef);
 
print_r($sql);
/*
* Returns:
*/
Array
(
[0] => CREATE TABLE test (
        COL1 C(32)  NOT NULL  DEFAULT 'abc',
        COL2 I  NOT NULL    AUTO-INCREMENT,
        COL3 N(12.2),
        COL4 C(64)  CHARACTER SET "ascii"  COLLATE "latin1_swedish_ci",
        COL5 ENUM('cats','dogs','fish'),
        COL6 T  DEFAULT NOW(),
        COL7 D  DEFAULT CURDATE(),
        PRIMARY KEY (COL2)
        ) ENGINE INNODB
[1] => ALTER TABLE test ADD  INDEX CIDX  (COL1)
[2] => ALTER TABLE test ADD  INDEX `COL4-INDEX`  (`COL4  ASC`, COL5)
)

The code above identifies a bug in addIndexSql where the column is incorrectly quoted if an attribute is added

Data Dictionary Objects

The object can be passed to the following ADOdb methods

createIndexSql

Note that multiple indexes can be created in the same command when used this way

Usage

$t = new metaObjectStructure('table','employees');
$t->addIndex('bd-idx')->addIndexItem('birth_date')->addAttribute('ASC');
 
$sqlarray = $dict->createIndexSql($t);

dropIndexSql

Usage

$t = new metaObjectStructure('employees'); $t→addIndeObject('bd-idx');

$sqlarray = $dict→dropIndexSql($t); </code>

addColumnSql

$t = new metaObjectStructure('employees');
$t->addColumnObject('COL','C(32)');
$sqlarray = $dict->addColumnSql($t);

alterColumnSql

Usage

$t = new metaObjectStructure('employees');
$t->addColumnObject('COL','C(32)');
$sqlarray = $dict->alterColumnSql($t);

dropColumnSql

Usage

$t = new metaObjectStructure('employees');
$t->addColumnObject('COL','C(32)');
$sqlarray = $dict->dropColumnSql($t);

dropTableSql

Usage

$t = new metaObjectStructure('employees');
$sqlarray = $dict->dropTableSql($t);

Add support for constraints, foreign keys
Maybe the metaobjectstructure should be called dataDictionaryStructure?

Table of Contents

MetaObject Structure Prototype

Concept

Naming

Missing From The Prototype

Definitions

Element

Object

Child

Attribute

Portable vs Non-Portable Attributes

Portable Attributes

Processing The MetaObjectStructure

Processing The Structure

Complex Example

Data Dictionary Objects

createIndexSql

Usage

dropIndexSql

Usage

addColumnSql

alterColumnSql

Usage

dropColumnSql

Usage

dropTableSql

Usage