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

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.

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.

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

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();
}

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/

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')

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:

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

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
                       }
              }
}
*/

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
)
*/

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 object can be passed to the following ADOdb methods

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

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

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

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

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

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

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

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