|
 ADOdb Lite Data Dictionary
The ADOdb Lite Data Dictionary is a modified version of the data dictionary from ADOdb. The data dictionary has been tested on MySql/i/t. I would appreciate feedback on the other supported databases.
Important: You must include both the meta and pear modules when you create your new ADOdb Connection when using the Data Dictionary. If you do not include these two modules the Data Dictionary will fail to function.
Data Dictionary Example
include_once('adodb.inc.php');
# Create a new Connection
$db->NewADOConnection('mysql');
$db->Connect(...);
# Then create a data dictionary object, using this connection
$dict = NewDataDictionary($db);
# Now create Field Array with all of the database tables field entries
$flds = array(array('id', 'I', 'AUTO','KEY'),
array('name' => 'firstname', 'type' => 'varchar','size' => 30, 'DEFAULT'=>'Joan'),
array('lastname','varchar',28, 'DEFAULT'=>'Chen','key'),
array('averylonglongfieldname','X',1024, 'NOTNULL','default' => 'test'),
array('price','N','7.2', 'NOTNULL','default' => '0.00'),
array('MYDATE', 'D', 'DEFDATE'),
array('TS','T', 'DEFTIMESTAMP')
);
# Alternative non-array method
$flds = "
ID I AUTO KEY,
FIRSTNAME VARCHAR(30) DEFAULT 'Joan',
LASTNAME VARCHAR(28) DEFAULT 'Chen' key,
averylonglongfieldname X(1024) DEFAULT 'test',
price N(7.2) DEFAULT '0.00',
MYDATE D DEFDATE,
BIGFELLOW X NOTNULL,
TS T DEFTIMESTAMP";
# Create the new table
$sqlarray = $dict->CreateTableSQL($tabname, $flds, $taboptarray);
$dict->ExecuteSQLArray($sqlarray);
# Create the table indexes
$idxflds = 'co11, col2';
$sqlarray = $dict->CreateIndexSQL($idxname, $tabname, $idxflds);
$dict->ExecuteSQLArray($sqlarray);
|
Data Dictionary Command Set
CreateTableSQL($tablename, $fieldarray, $tableoptionarray=false)
RETURNS: an array of strings, the sql to be executed, or false
$tabname: name of table
$fldarray: string (or array) containing field info
Field Array Format: "$fieldname $type $colsize $otheroptions"
$fieldname = Field Name
$type = Type Code (valid codes in Cross-Database Type Code List
$columnsize = size of the field based upon the type
$otheroptions = Options listed in the Other Field Options
$taboptarray: array containing table options listed in Other Table Options
|
Cross-Database Type Codes |
C: Varchar, capped to 255 characters.
X: Larger varchar, capped to 4000 characters (to be compatible with Oracle).
XL: For Oracle, returns CLOB, otherwise the largest varchar size.
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
|
|
Other Field Options |
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.
|
|
Other Table Options |
REPLACE Indicates that the previous table definition should be removed (dropped)together with ALL data. See first example below.
DROP Drop table. Useful for removing unused tables.
CONSTRAINTS Define this as the key, with the constraint as the value. Additional constraints defined for the whole table. You will probably need to prefix this with a comma.
Example:
$tableoptionarray = array('mysql' => 'TYPE=INNODB, 'oci8' => 'tablespace users', 'REPLACE');
$tableoptionarray = array('constraints' => ', FOREIGN KEY (col1) REFERENCES reftable (refcol)'); |
|
DropTableSQL($tablename)
RETURNS: SQL Query for dropping a table |
ChangeTableSQL($tablename, $fieldarray)
RETURNS: SQL Query for altering the table if the table exists.
If the table doesn't exist the table is created using CreateTableSQL. |
RenameTableSQL($tablename, $newname)
RETURNS: SQL Query Array for renaming a table |
RenameColumnSQL($tablename, $oldcolumn, $newcolumn, $flds='')
RETURNS: SQL Query Array for renaming a field in the designated table. |
CreateIndexSQL($indexname, $tablename, $flds, $indexoptionarray=false)
RETURNS: SQL Query Array for creating indexes on the designated table
$indexname: name of index
$tablename: name of table
$flds: list of fields as a comma delimited string or an array of strings
$indexoptionarray: array of index creation options
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)
DROP Drop legacy index
|
DropIndexSQL($indexname, $tablename = NULL)
RETURNS: SQL Query for dropping designated index |
AddColumnSQL($tablename, $flds)
RETURNS: SQL Query for adding a column to designated table |
AlterColumnSQL($tablename, $flds)
RETURNS: SQL Query for altering columns in designated table |
DropColumnSQL($tablename, $flds)
RETURNS: SQL Query for dropping a column in designated table |
SetSchema($schemaname)
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
|
ADOdb XML Schema (AXMLS)
You can use an XML Schema file to easily create your database and import tables. For more information you should download the AXMLS Documentation.
<?
/* Load Adodb Lite first and then load XML Schema
*/
require( "path_to_adodb/adodb.inc.php");
require( "path_to_adodb/adodb-xmlschema.inc.php" );
$schemaFile = 'example.xml';
$platform = 'mysql';
$dbHost = 'localhost';
$dbName = 'database';
$dbUser = 'username';
$dbPassword = 'password';
/* You MUST include the Pear Module in the connection either
* in the ADONewConnection or the adodb.config.php file.
*/
$db = ADONewConnection( $platform, "pear");
$db->Connect( $dbHost, $dbUser, $dbPassword, $dbName );
/* Use the database connection to create a new adoSchema object.
*/
$schema = new adoSchema( $db );
/* Call ParseSchema() to build SQL from the XML schema file.
* Then call ExecuteSchema() to apply the resulting SQL to
* the database.
*/
$sql = $schema->ParseSchema( $schemaFile );
$result = $schema->ExecuteSchema();
?>
|
|
