ADODB Data Dictionary Manual

## ADOdb Data Dictionary Library for PHP

V3.60 16 June 2003 (c) 2000-2003 John Lim (jlim#natsoft.com.my)

This software is dual licensed using BSD-Style and LGPL. Where there is any discrepancy, the BSD-Style license will take precedence. This means you can use it in proprietary and commercial products.

This documentation describes a class library to automate the creation of tables, indexes and foreign key constraints portably for multiple databases. Download from http://php.weblogs.com/adodb

Currently the following databases are supported:

Well-tested: PostgreSQL, MySQL, Oracle, MSSQL.
Beta-quality: DB2, Informix, Sybase, Interbase, Firebird.
Alpha-quality: MS Access (does not support DEFAULT values) and generic ODBC.

### Example Usage

    include_once('adodb.inc.php');

    # First create a normal connection
    $db->NewADOConnection('mysql');
    $db->Connect(...);

    # Then create a data dictionary object, using this connection
    $dict = NewDataDictionary($db);

    # We have a portable declarative data dictionary format in ADOdb 3.50, similar to SQL.
    # Field types use 1 character codes, and fields are separated by commas.
    # The following example creates three fields: "col1", "col2" and "col3":
    $flds = "
     col1 C(32) NOTNULL DEFAULT 'abc',
     col2 I  DEFAULT 0,
     col3 N(12.2)
    ";

    # We demonstrate creating tables and indexes
    $sqlarray = $dict->CreateTableSQL($tabname, $flds, $taboptarray);
    $dict->ExecuteSQLArray($sqlarray);

    $idxflds = 'co11, col2';
    $sqlarray = $dict->CreateIndexSQL($idxname, $tabname, $idxflds);
    $dict->ExecuteSQLArray($sqlarray);

### Functions

function CreateDatabase($dbname, $optionsarray=false)

Create a database with the name $dbname;

function CreateTableSQL($tabname, $fldarray, $taboptarray=false)

    	RETURNS:	an array of strings, the sql to be executed, or false
    	$tabname: 	name of table
    	$fldarray:	string (or array) containing field info
    	$taboptarray:	array containing table options

The new format of $fldarray uses a free text format, where each field is comma-delimited. The first token for each field is the field name, followed by the type and optional field size. Then optional keywords in $otheroptions:

    	"$fieldname $type $colsize $otheroptions"

The older (and still supported) format of $fldarray is a 2-dimensional array, where each row in the 1st dimension represents one field. Each row has this format:

    	array($fieldname, $type, [,$colsize] [,$otheroptions]*)

The first 2 fields must be the field name and the field type. The field type can be a portable type codes or the actual type for that database.

Legal portable type codes include:

    C:  varchar
    X:  Largest varchar size
    XL: For Oracle, returns CLOB, otherwise same as 'X' above

    C2: Multibyte varchar
    X2: Multibyte varchar (largest size)

    B:  BLOB (binary large object)

    D:  Date (some databases do not support this, and we return a datetime type)
    T:  Datetime or Timestamp
    L:  Integer field suitable for storing booleans (0 or 1)
    I:  Integer (mapped to I4)
    I1: 1-byte integer
    I2: 2-byte integer
    I4: 4-byte integer
    I8: 8-byte integer
    F:  Floating point number
    N:  Numeric or decimal number

The $colsize field represents the size of the field. If a decimal number is used, then it is assumed that the number following the dot is the precision, so 6.2 means a number of size 6 digits and 2 decimal places. It is recommended that the default for number types be represented as a string to avoid any rounding errors.

The $otheroptions include the following keywords (case-insensitive):

    AUTO			For autoincrement number. Emulated with triggers if not available.
    				Sets NOTNULL also.
    AUTOINCREMENT	Same as auto.
    KEY			Primary key field. Sets NOTNULL also. Compound keys are supported.
    PRIMARY 		Same as KEY.
    DEF			Synonym for DEFAULT for lazy typists.
    DEFAULT		The default value. Character strings are auto-quoted unless
    				the string begins and ends with spaces, eg ' SYSDATE '.
    NOTNULL		If field is not null.
    DEFDATE		Set default value to call function to get today's date.
    DEFTIMESTAMP	Set default to call function to get today's datetime.
    NOQUOTE		Prevents autoquoting of default string values.
    CONSTRAINTS	Additional constraints defined at the end of the field
    				definition.

The Data Dictonary accepts two formats, the older array specification:

    $flds = array(
    	array('COLNAME', 'DECIMAL', '8.4', 'DEFAULT' => 0, 'NotNull'),
    	array('ID',      'I'      , 'AUTO'),
    	array('MYDATE',  'D'      , 'DEFDATE'),
    	array('NAME',    'C'      ,'32',
    		  'CONSTRAINTS' => 'FOREIGN KEY REFERENCES reftable')
    );

Or the simpler declarative format:

     $flds = "
     COLNAME DECIMAL(8.4) DEFAULT 0 NotNull,
     ID I AUTO,
     MYDATE D DEFDATE,
     NAME C(32) CONSTRAINTS 'FOREIGN KEY REFERENCES reftable'
    ";

The $taboptarray is the 3rd parameter of the CreateTableSQL function. This contains table specific settings. Legal keywords include:

- REPLACE
  Indicates that the previous table definition should be removed (dropped)together with ALL data. See first example below.
- CONSTRAINTS
  Define this as the key, with the constraint as the value. See the postgresql example below. Additional constraints defined for the whole table. You will probably need to prefix this with a comma.

Database specific table options can be defined also using the name of the database type as the array key. In the following example, create the table as ISAM with MySQL, and store the table in the "users" tablespace if using Oracle. And if the table already exists, drop the table first.

     	$taboptarray = array('mysql' => 'TYPE=ISAM', 'oci8' => 'tablespace users', 'REPLACE');

You can also define foreignkey constraints. The following is syntax for postgresql:

    	$taboptarray = array('constraints' =>
    					', FOREIGN KEY (col1) REFERENCES reftable (refcol)');

function ChangeTableSQL($tabname, $flds)

Checks to see if table exists, if table does not exist, behaves like CreateTableSQL. If table exists, generates appropriate ALTER TABLE MODIFY COLUMN commands if field already exists, or ALTER TABLE ADD $column if field does not exist.

The class must be connected to the database for ChangeTableSQL to detect the existance of the table. Idea and code contributed by Florian Buzin.

function CreateIndexSQL($idxname, $tabname, $flds, $idxoptarray=false)

RETURNS: an array of strings, the sql to be executed, or false

    	$idxname:		name of index
    	$tabname: 		name of table
    	$flds:  		list of fields as a comma delimited string or an array of strings
    	$idxoptarray:	array of index creation options

$idxoptarray is similar to $taboptarray in that index specific information can be embedded in the array. Other options include:

    	CLUSTERED		Create clustered index (only mssql)
    	BITMAP			Create bitmap index (only oci8)
    	UNIQUE			Make unique index
    	FULLTEXT		Make fulltext index (only mysql)
    	HASH			Create hash index (only postgres)

function AddColumnSQL($tabname, $flds)

Add one or more columns. Not guaranteed to work under all situations.

function AlterColumnSQL($tabname, $flds)

Warning, not all databases support this feature.

function DropColumnSQL($tabname, $flds)

Drop 1 or more columns.

function ExecuteSQLArray($sqlarray, $contOnError = true)

    	RETURNS:		0 if failed, 1 if executed all but with errors, 2 if executed successfully
    	$sqlarray:		an array of strings with sql code (no semicolon at the end of string)
    	$contOnError:	if true, then continue executing even if error occurs

Executes an array of SQL strings returned by CreateTableSQL or CreateIndexSQL.

## XML Schema

This is a class contributed by Richard Tango-Lowy that allows the user to quickly and easily build a database using the excellent ADODB database library and a simple XML formatted file.

### Quick Start

First, create an XML database schema. Let's call it "schema.xml:"

    <?xml version="1.0"?>
    <schema>
      <table name="mytable">
        <field name="row1" type="I">
          <descr>An integer row that's a primary key and autoincrements</descr>
          <KEY/>
          <AUTOINCREMENT/>
        </field>
        <field name="row2" type="C" size="16">
          <descr>A 16 character varchar row that can't be null</descr>
          <NOTNULL/>
        </field>
      </table>
      <index name="myindex" table="mytable">
        <col>row1</col>
        <col>row2</col>
      </index>
      <sql>
        <descr>SQL to be executed only on specific platforms</descr>
        <query platform="postgres|postgres7">
          insert into mytable ( row1, row2 ) values ( 12, 'stuff' )
        </query>
        <query platform="mysql">
          insert into mytable ( row1, row2 ) values ( 12, 'different stuff' )
        </query>
      </sql>
    </schema>

Create a new database using the appropriate tool for your platform. Executing the following PHP code will create the a mytable and myindex in the database and insert one row into mytable if the platform is postgres or mysql.

    include_once('/path/to/adodb.inc.php');
    include_once('/path/to/adodb-xmlschema.inc.php');

    // To build the schema, start by creating a normal ADOdb connection:
    $db->NewADOConnection( 'mysql' );
    $db->Connect( ... );

    // Create the schema object and build the query array.
    $schema = new adoSchema( $db );

    // Build the SQL array
    $sql = $schema->ParseSchema( "schema.xml" );

    // Execute the SQL on the database
    $result = $schema->ExecuteSchema( $sql );

    // Finally, clean up after the XML parser
    // (PHP won't do this for you!)
    $schema->Destroy();

### XML Schema Format:

(See ADOdb_schema.dtd for the full specification)

    <?xml version="1.0"?>
    <schema>
      <table name="tablename" platform="platform1|platform2|...">
        <descr>Optional description</descr>
        <field name="fieldname" type="datadict_type" size="size">
          <KEY/>
          <NOTNULL/>
          <AUTOINCREMENT/>
          <DEFAULT value="value"/>
        </field>
    	... more fields
      </table>
      ... more tables

      <index name="indexname" platform="platform1|platform2|...">
        <descr>Optional description</descr>
        <col>fieldname</col>
        ... more columns
      </index>
      ... more indices

      <sql platform="platform1|platform2|...">
        <descr>Optional description</descr>
        <query platform="platform1|platform2|...">SQL query</query>
        ... more queries
      </sql>
      ... more SQL
      </schema>