Software: Apache. PHP/5.5.15 uname -a: Windows NT SVR-DMZ 6.1 build 7600 (Windows Server 2008 R2 Enterprise Edition) i586 SYSTEM Safe-mode: OFF (not secure) C:\Intranet\C\xampp\php\PEAR\adodb\docs\ drwxrwxrwx |
Viewing file: docs-datadict.htm (23.32 KB) -rw-rw-rw- Select action/file-type: (+) | (+) | (+) | Code (+) | Session (+) | (+) | SDB (+) | (+) | (+) | (+) | (+) | (+) | ADOdb Data Dictionary Library for PHPV4.96 24 Sept 2007 (c) 2000-2007 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 PHP class library to automate the creation of tables, indexes and foreign key constraints portably for multiple databases. Richard Tango-Lowy and Dan Cech have been kind enough to contribute AXMLS, an XML schema system for defining databases. You can contact them at dcech#phpwerx.net and richtl#arscognita.com. Currently the following databases are supported: Well-tested: PostgreSQL, MySQL, Oracle, MSSQL. Example Usageinclude_once('adodb.inc.php'); More Complex Table SampleThe following string will create a table with a primary key event_id and multiple indexes, including one compound index idx_ev1. The ability to define indexes using the INDEX keyword was added in ADOdb 4.94 by Gaetano Giunta. $flds = " event_id I(11) NOTNULL AUTOINCREMENT PRIMARY, event_type I(4) NOTNULL INDEX idx_evt, event_start_date T DEFAULT NULL INDEX id_esd, event_end_date T DEFAULT '0000-00-00 00:00:00' INDEX id_eted, event_parent I(11) UNSIGNED NOTNULL DEFAULT 0 INDEX id_evp, event_owner I(11) DEFAULT 0 INDEX idx_ev1, event_project I(11) DEFAULT 0 INDEX idx_ev1, event_times_recuring I(11) UNSIGNED NOTNULL DEFAULT 0, event_icon C(20) DEFAULT 'obj/event', event_description X "; $sqlarray = $db->CreateTableSQL($tablename, $flds); $dict->ExecuteSQLArray($sqlarray); Class FactoryNewDataDictionary($connection, $drivername=false)Creates a new data dictionary object. You pass a database connection object in $connection. The $connection does not have to be actually connected to the database. Some database connection objects are generic (eg. odbtp and odbc). Since 4.53, you can tell ADOdb the actual database with $drivername. E.g. $db =& NewADOConnection('odbtp'); $datadict = NewDataDictionary($db, 'mssql'); # force mssql Class Functionsfunction 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 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, capped to 255 characters. 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. The Data Dictonary accepts two formats, the older array specification: $flds = array( Or the simpler declarative format: $flds = " Note that if you have special characters in the field name (e.g. My Date), you should enclose it in back-quotes. Normally field names are not case-sensitive, but if you enclose it in back-quotes, some databases will treat the names as case-sensitive (eg. Oracle) , and others won't. So be careful. 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 because we specified REPLACE, drop the table first. $taboptarray = array('mysql' => 'TYPE=ISAM', 'oci8' => 'tablespace users', 'REPLACE'); You can also define foreign key constraints. The following is syntax for postgresql: $taboptarray = array('constraints' => ', FOREIGN KEY (col1) REFERENCES reftable (refcol)'); function DropTableSQL($tabname)Returns the SQL to drop the specified table. 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 existence of the table. Idea and code contributed by Florian Buzin. function RenameTableSQL($tabname,$newname)Rename a table. Returns the an array of strings, which is the SQL required to rename a table. Since ADOdb 4.53. Contributed by Ralf Becker. function RenameColumnSQL($tabname,$oldcolumn,$newcolumn,$flds='')Rename a table field. Returns the an array of strings, which is the SQL required to rename a column. The optional $flds is a complete column-defintion-string like for AddColumnSQL, only used by mysql at the moment. Since ADOdb 4.53. Contributed by Ralf Becker. function CreateIndexSQL($idxname, $tabname, $flds, $idxoptarray=false)RETURNS: an array of strings, the sql to be executed, or false $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) function DropIndexSQL ($idxname, $tabname = NULL)Returns the SQL to drop the specified index. 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 SetSchema($schema)Set the schema. function &MetaTables()function &MetaColumns($tab, $upper=true, $schema=false)function &MetaPrimaryKeys($tab,$owner=false,$intkey=false)function &MetaIndexes($table, $primary = false, $owner = false)These functions are wrappers for the corresponding functions in the connection object. However, the table names will be autoquoted by the TableName function (see below) before being passed to the connection object. function NameQuote($name = NULL)If the provided name is quoted with backquotes (`) or contains special characters, returns the name quoted with the appropriate quote character, otherwise the name is returned unchanged. function TableName($name)The same as NameQuote, but will prepend the current schema if specified function MetaType($t,$len=-1,$fieldobj=false)function ActualType($meta)Convert between database-independent 'Meta' and database-specific 'Actual' type codes. function ExecuteSQLArray($sqlarray, $contOnError = true)RETURNS: 0 if failed, 1 if executed all but with errors, 2 if executed successfully Executes an array of SQL strings returned by CreateTableSQL or CreateIndexSQL. ADOdb XML Schema (AXMLS)This is a class contributed by Richard Tango-Lowy and Dan Cech that allows the user to quickly and easily build a database using the excellent ADODB database library and a simple XML formatted file. You can download the latest version of AXMLS here. Quick StartAdodb-xmlschema, or AXMLS, is a set of classes that allow the user to quickly and easily build or upgrade a database on almost any RDBMS using the excellent ADOdb database library and a simple XML formatted schema file. Our goal is to give developers a tool that's simple to use, but that will allow them to create a single file that can build, upgrade, and manipulate databases on most RDBMS platforms. Installing axmlsThe easiest way to install AXMLS to download and install any recent version of the ADOdb database abstraction library. To install AXMLS manually, simply copy the adodb-xmlschema.inc.php file and the xsl directory into your adodb directory. Using AXMLS in Your ApplicationThere are two steps involved in using AXMLS in your application: first, you must create a schema, or XML representation of your database, and second, you must create the PHP code that will parse and execute the schema. Let's begin with a schema that describes a typical, if simplistic user management table for an application. <?xml version="1.0"?> Let's take a detailed look at this schema. The opening <?xml version="1.0"?> tag is required by XML. The <schema> tag tells the parser that the enclosed markup defines an XML schema. The version="0.2" attribute sets the version of the AXMLS DTD used by the XML schema. All versions of AXMLS prior to version 1.0 have a schema version of "0.1". The current schema version is "0.2". <?xml version="1.0"?> Next we define one or more tables. A table consists of a fields (and other objects) enclosed by <table> tags. The name="" attribute specifies the name of the table that will be created in the database. <table name="users"> This table is called "users" and has a description and two fields. The description is optional, and is currently only for your own information; it is not applied to the database. The first <field> tag will create a field named "userId" of type "I", or integer. (See the ADOdb Data Dictionary documentation for a list of valid types.) This <field> tag encloses two special field options: <KEY/>, which specifies this field as a primary key, and <AUTOINCREMENT/>, which specifies that the database engine should automatically fill this field with the next available value when a new row is inserted. The second <field> tag will create a field named "userName" of type "C", or character, and of length 16 characters. The <NOTNULL/> option specifies that this field does not allow NULLs. There are two ways to add indexes to a table. The simplest is to mark a field with the <KEY/> option as described above; a primary key is a unique index. The second and more powerful method uses the <index> tags. <table name="users"> The <index> tag specifies that an index should be created on the enclosing table. The name="" attribute provides the name of the index that will be created in the database. The description, as above, is for your information only. The <col> tags list each column that will be included in the index. Finally, the <UNIQUE/> tag specifies that this will be created as a unique index. Finally, AXMLS allows you to include arbitrary SQL that will be applied to the database when the schema is executed. <sql> The <sql> tag encloses any number of SQL queries that you define for your own use. Now that we've defined an XML schema, you need to know how to apply it to your database. Here's a simple PHP script that shows how to load the schema. <?PHP Let's look at each part of the example in turn. After you manually create the database, there are three steps required to load (or upgrade) your schema. First, create a normal ADOdb connection. The variables and values here should be those required to connect to your database. $db = ADONewConnection( 'mysql' ); Second, create the adoSchema object that load and manipulate your schema. You must pass an ADOdb database connection object in order to create the adoSchema object. $schema = new adoSchema( $db ); Third, call ParseSchema() to parse the schema and then ExecuteSchema() to apply it to the database. You must pass ParseSchema() the path and filename of your schema file. $schema->ParseSchema( $schemaFile ); Execute the above code and then log into your database. If you've done all this right, you should see your tables, indexes, and SQL. You can find the source files for this tutorial in the examples directory as tutorial_shema.xml and tutorial.php. See the class documentation for a more detailed description of the adoSchema methods, including methods and schema elements that are not described in this tutorial. XML Schema Version 3In March 2006, we added adodb-xmlschema03.inc.php to the release, which supports version 3 of XML Schema. The adodb-xmlschema.inc.php remains the same as previous releases, and supports version 2 of XML Schema. Version 3 provides some enhancements:
Example usage: <?xml version="1.0"?> <schema version="0.3"> <table name="ats_kb"> <descr>ATS KnowledgeBase</descr> <opt platform="mysql">TYPE=INNODB</opt> <field name="recid" type="I"/> <field name="organization_code" type="I4"/> <field name="sub_code" type="C" size="20"/> etc... To use it, change your code to include adodb-xmlschema03.inc.php. UpgradingIf your schema version is older, than XSLT is used to transform the schema to the newest version. This means that if you are using an older XML schema format, you need to have the XSLT extension installed. If you do not want to require your users to have the XSLT extension installed, make sure you modify your XML schema to conform to the latest version. If you have any questions or comments, please email them to Richard at richtl#arscognita.com. |
:: Command execute :: | |
--[ c99shell v. 1.0 pre-release build #13 powered by Captain Crunch Security Team | http://ccteam.ru | Generation time: 0.0312 ]-- |