ADOdb Data Dictionary Library for PHP

V4.05 13 Dec 2003 (c) 2000-2003 John Lim (jlim#natsoft.com.my)

This software is dual licensed using BSD-Style and LGPL. This means you can use it in compiled proprietary and commercial products.

Useful ADOdb links: Download   Other Docs

This documentation describes a class library to automate the creation of tables, indexes and foreign key constraints portably for multiple databases.

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:

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

// Optionally, set a prefix for newly-created tables. In this example
// the prefix "myprefix_" will result in a table named "myprefix_tablename".
//$schema->setPrefix( "myprefix_" );
	
// 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>

If you have any questions or comments, please email them to me at richtl#arscognita.com.