ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:dictionary:structure:introduction

Structured Schema Management

Description

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(), dropColumnSql() 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.

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.

Construction Of The Structure

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, a reference is made
* to the ADOdb dictionary class
*/
$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.

Processing The MetaObjectStructure

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

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
)
*/
v5/dictionary/structure/introduction.txt · Last modified: 2021/08/26 10:11 by peterdd