Differences

This shows you the differences between two versions of the page.

dev:dbschema_convention [2009/07/06 20:36] (current)
Line 1: Line 1:
 +====== Creating AXMLS (AdoDB XML Schema) ======
 +  * Namespace is schema :-) . The version="0.2" attribute sets the version of the AXMLS DTD used by the XML schema.
 +<code xml>
 +<?xml version="1.0"?>
 +<schema version="0.2">
 +   ....
 +   ....
 +</schema>
 +</code>
 +  * Mainly you can include the Tables and SQL statements.
 +  * The schema is closely related with the ADODB Data dictionary.
 +  * Please have a look at [[http://phplens.com/lens/adodb/docs-datadict.htm]]
 +  * Following are useful when creating the schema
 +
 +^ Type Prefix ^ Description ^
 +| 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|
 +
 +
 +^ Keywords ^ Description ^
 +| 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. |
 +
 +  * Here is an example
 +<code xml>
 +<?xml version="1.0"?>
 +<schema version="0.2">
 +
 +  <table name="person_uuid">
 +    <desc>The central table on a person, with their associated names.</desc>
 +
 +    <field name="p_uuid" type="I">
 +      <descr>A unique ID assigned to each person.</descr>
 +      <KEY/>
 +      <AUTOINCREMENT/>
 +    </field>
 +    
 +    <field name="full_name" type="C" size="100" />
 +    <field name="family_name" type="C" size="50" />
 +    <field name="l10n_name" type="C" size="100" />
 +    <field name="custom_name" type="C" size="50" />
 +
 +    <index name="full_name">
 +      <descr>Put an unique index on the full name</descr>
 +      <col>full_name</col>
 +      <UNIQUE/>
 +    </index>
 +
 +  </table>
 +
 +</schema>
 +</code>
 +
 +====== USING AXMLS ======
 +  * You need to include the sahana db handler and include the adodb xmlschema library
 +<code php>
 +require_once( $global['approot']."/inc/db_handler.inc");
 +require_once( $global['approot']."/3rd/adodb/adodb-xmlschema.inc.php" );
 +</code>
 +  * Use the database connection to create a new adoSchema object.
 +<code php>
 +$schema = new adoSchema( $global['db'] );
 +</code>
 +  * Call ParseSchema() to build SQL from the XML schema file. Then call the ExecuteSchema() to apply the resulting SQL to database.
 +<code php>
 +$result = $schema->ExecuteSchema();
 +</code>
 +
 +====== References ======
 +  * [[http://adodb-xmlschema.sourceforge.net/docs/axmls/tutorial_getting_started.pkg.html]]
 +  * [[http://phplens.com/lens/adodb/docs-datadict.htm]]

Navigation
  • Navigate