From ADOdb Version 5.21, A new method of managing changes to the schema has been added. This method is called Structured schema management to signify the more formal method of managing elements. The original method, referred to as Simple, remains unchanged.

In Structured schema management, the elements that can be modified through the dictionary management routines such as createIndexSql(),dropSql() and createtableSql() can be created and represented as objects prior to them being passed to the methods that create the SQL necessary to create them in the database. The concept is built on AXMLS and contains some feature overlap.

These objects can then be easily stored through the use of technologies such as JSON, which can easily be manipulated through 3rd party tools and reprocessed.

obj metaObjectStructure(
    object $dataDictionary,
    string $objectName,
    optional string $platform=''
    )

Each table, and element of a table (such as a column or index) is represented by a PHP object called a metaObjectStructure. Each of these objects can store an unlimited number of additional attributes that represent configuration items for the element. The attributes themselves can be defined as platform-specific.

Every structure, no matter what the ultimate reason for its construction is, begins with construction of a table object. In this example, we are going to add a new column 'termination_date' to a table 'employees'.

/*
* Assuming an established database object $db
*/
$dict = newDataDictionary($db);
 
/*
* Create the table object and pass the dictionary object
*/
$t = new metaObjectStructure($dict, 'employees');

The platform option is available to all level of object definitions, and filters the definition based on the database's data provider. For example, if a table definition were given a platform mysql, it would simply be ignored if applied to a PostgreSQL database.

The metaObjectStructure class has 4 public methods available:

obj addAttribute(
    mixed $attribute,
    optional string $platform='',
    optional integer $priority = -1;
    )

This method adds an attribute to the table management. For example, it might be necessary to add an option to tell the DBMS what type of database engine to use. In this case, the platform variable is set to match the ADOdb Data Provider.

The attribute can be provided as a string, a numeric or associative array.

The priority parameter can optionally force the system to process the attributes in a specific order. If not provided, the attributes are processed in the order provided.

$t = new metaObjectStructure($dict,'employees');
 
$t->addAttribute('ENGINE INNODB','mysql');
 
/*
* Add another platform option, for IBM DB2
*/
$t->addAttribute(array('TABLESPACE'=>'LARGE1'),'db2');

Alternatively, we can write the definition as:

$t = new metaObjectStructure($dict,'employees');
 
$t->addAttribute('ENGINE INNODB','mysql')
  ->addAttribute(array('TABLESPACE'=>'LARGE1'),'db2');

obj addColumnObject(
    string $columnName,
    string $columnType,
    optional string $platform=''
    )

This method adds a column object to a previously defined table structure. The method accepts 3 arguments, the column name, the type and an optional platform value. The column type is declared using a metaType.

$t = new metaObjectStructure($dict,'employees');
 
$t->addColumnObject('COL1','I');
$t->addColumnObject('COL2','C(60)');

obj addIndexObject
(
  string $indexName,
  optional string $platform=''
)

This method adds an index object to a previously defined table structure. The method accepts 2 arguments, the index name, and an optional platform value. The index object is a container for Index-Item objects, which are the columns themselves.

$t = new metaObjectStructure($dict,'employees');
 
$t->addColumnObject('COL1','I');
$t->addColumnObject('COL2','C(60)');
 
$t->addIndexObject('COL1-IDX');

obj addIndexItemObject
(
  string $indexName,
  string $columnName,
  optional string $platform=''
)

This method adds an index item object to a previously defined index structure. The method accepts 3 arguments, the index name, the column name and an optional platform value.

$t = new metaObjectStructure($dict,'employees');
 
$t->addColumnObject('COL1','I');
$t->addColumnObject('COL2','C(60)');
 
$i = $t->addIndexObject('COL1-IDX');
$i->addIndexItemObject('COL1');
$i->addIndexItemObject('COL2');

obj setNewName
(
  string $newName
)

In methods that set new names for objects, this method sets the new name to be used. The method is only applicable to the 'table' and 'column' objects.

$t = new metaObjectStructure($dict,'employees');
 
$t->setNewName('new-employees');
 
$sql = $dict->renameTableSql($t);

Portable attributes are comparable to the options available to the original Column Attributes in Simple Schema Management.

• If a feature is portable, no platform designation is generally necessary but can still be used
• Portable options may have a priority automatically assigned, which may control the order of definitions. In particular, the priority will force the NOTNULL and DEFAULT attributes to conform to the ANSI SQL order.

A custom attribute is one that must be defined fully in order to be handled correctly by the DBMS. In principle, any option not all ready defined as portable in the Column Attributes list is a custom attribute.

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

/*
* We define a metaObjectStructure, that represents
* the table 'employees'
*/
 
$dict = NewDataDictionary($db);
$t = new metaObjectStructure($dict,'employees');
 
/*
* We now add an object representing the column, 'somecolumn' of type Character(32)
*/
$c = $t->addColumnObject('somecolumn','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');
 
 
/*
* This object is now passed to the addColumnSql method
*/
$sql = $dict->addColumnSql($t)

If ADOdb was attached to a MySQL database, the sql returned would be:

ALTER TABLE employees ADD somecolumn VARCHAR(32) 
                           DEFAULT 'SOME VALUE' 
                           CHARACTER SET "ascii"

but if attached to a SQL Server database, it would appear as:

ALTER TABLE employees ADD somecolumn VARCHAR(32) 
                          DEFAULT "SOME VALUE" 
                    CHARACTER SET "ascii" 
                           SPARSE

The structure is not automatically sent to createTableSql, it must be deliberately passed on. This is important because it allows the returned object be to re-used or stored, for example by serializing or JSON-encoding it. 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

$dict = newDataDictionary($db);
$t = new metaObjectStructure($dict,'test');
/*
* Make sure the table is transactional in MySQL
*/
$t->addAttribute('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($dict,'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($dict,'employees');
$t->addIndexObject('bd-idx')
    ->addIndexItemObject('birth_date')->addAttribute('ASC');
 
$sqlarray = $dict->createIndexSql($t);

$t = new metaObjectStructure($dict,'employees');
$t->addIndeObject('bd-idx');
 
$sqlarray = $dict->dropIndexSql($t);

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

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

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

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