1 | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> |
---|
2 | <html> |
---|
3 | <head> |
---|
4 | <title>ADODB Data Dictionary Manual</title> |
---|
5 | <meta http-equiv="Content-Type" |
---|
6 | content="text/html; charset=iso-8859-1"> |
---|
7 | <style type="text/css"> |
---|
8 | body, td { |
---|
9 | /*font-family: Arial, Helvetica, sans-serif;*/ |
---|
10 | font-size: 11pt; |
---|
11 | } |
---|
12 | pre { |
---|
13 | font-size: 9pt; |
---|
14 | background-color: #EEEEEE; padding: .5em; margin: 0px; |
---|
15 | } |
---|
16 | .toplink { |
---|
17 | font-size: 8pt; |
---|
18 | } |
---|
19 | </style> |
---|
20 | </head> |
---|
21 | <body style="background-color: rgb(255, 255, 255);"> |
---|
22 | <h2>ADOdb Data Dictionary Library for PHP</h2> |
---|
23 | <p>V4.50 6 July 2004 (c) 2000-2004 John Lim (<a |
---|
24 | href="mailto:jlim#natsoft.com.my">jlim#natsoft.com.my</a>).<br> |
---|
25 | AXMLS (c) 2004 ars Cognita, Inc</p> |
---|
26 | <p><font size="1">This software is dual licensed using BSD-Style and |
---|
27 | LGPL. This means you can use it in compiled proprietary and commercial |
---|
28 | products.</font></p> |
---|
29 | <table border="1"> |
---|
30 | <tbody> |
---|
31 | <tr> |
---|
32 | <td><font color="red">Kindly note that the ADOdb home page has |
---|
33 | moved to <a href="http://adodb.sourceforge.net/">http://adodb.sourceforge.net/</a> |
---|
34 | because of the persistent unreliability of http://php.weblogs.com. <b>Please |
---|
35 | change your links</b>!</font></td> |
---|
36 | </tr> |
---|
37 | </tbody> |
---|
38 | </table> |
---|
39 | <p>Useful ADOdb links: <a href="http://adodb.sourceforge.net/#download">Download</a> |
---|
40 | <a href="http://adodb.sourceforge.net/#docs">Other Docs</a> |
---|
41 | </p> |
---|
42 | <p>This documentation describes a PHP class library to automate the |
---|
43 | creation of tables, indexes and foreign key constraints portably for |
---|
44 | multiple databases. Richard Tango-Lowy and Dan Cech have been kind |
---|
45 | enough to contribute <a href="#xmlschema">AXMLS</a>, an XML schema |
---|
46 | system for defining databases. You can contact them at |
---|
47 | dcech#phpwerx.net and richtl#arscognita.com.</p> |
---|
48 | <p>Currently the following databases are supported:</p> |
---|
49 | <p> <b>Well-tested:</b> PostgreSQL, MySQL, Oracle, MSSQL.<br> |
---|
50 | <b>Beta-quality:</b> DB2, Informix, Sybase, Interbase, Firebird.<br> |
---|
51 | <b>Alpha-quality:</b> MS Access (does not support DEFAULT values) and |
---|
52 | generic ODBC. |
---|
53 | </p> |
---|
54 | <h3>Example Usage</h3> |
---|
55 | <pre> include_once('adodb.inc.php');<br> <font color="#006600"># First create a normal connection</font><br> $db->NewADOConnection('mysql');<br> $db->Connect(...);<br><br> <font |
---|
56 | color="#006600"># Then create a data dictionary object, using this connection</font><br> $dict = <strong>NewDataDictionary</strong>($db);<br><br> <font |
---|
57 | color="#006600"># We have a portable declarative data dictionary format in ADOdb, similar to SQL.<br> # Field types use 1 character codes, and fields are separated by commas.<br> # The following example creates three fields: "col1", "col2" and "col3":</font><br> $flds = " <br> <font |
---|
58 | color="#663300"><strong> col1 C(32) NOTNULL DEFAULT 'abc',<br> col2 I DEFAULT 0,<br> col3 N(12.2)</strong></font><br> ";<br><br> <font |
---|
59 | color="#006600"># We demonstrate creating tables and indexes</font><br> $sqlarray = $dict-><strong>CreateTableSQL</strong>($tabname, $flds, $taboptarray);<br> $dict-><strong>ExecuteSQLArray</strong>($sqlarray);<br><br> $idxflds = 'co11, col2';<br> $sqlarray = $dict-><strong>CreateIndexSQL</strong>($idxname, $tabname, $idxflds);<br> $dict-><strong>ExecuteSQLArray</strong>($sqlarray);<br></pre> |
---|
60 | <h3>Functions</h3> |
---|
61 | <h4>function CreateDatabase($dbname, $optionsarray=false)</h4> |
---|
62 | <p>Create a database with the name $dbname;</p> |
---|
63 | <h4>function CreateTableSQL($tabname, $fldarray, $taboptarray=false)</h4> |
---|
64 | <pre> RETURNS: an array of strings, the sql to be executed, or false<br> $tabname: name of table<br> $fldarray: string (or array) containing field info<br> $taboptarray: array containing table options<br></pre> |
---|
65 | <p>The new format of $fldarray uses a free text format, where each |
---|
66 | field is comma-delimited. |
---|
67 | The first token for each field is the field name, followed by the type |
---|
68 | and optional |
---|
69 | field size. Then optional keywords in $otheroptions:</p> |
---|
70 | <pre> "$fieldname $type $colsize $otheroptions"</pre> |
---|
71 | <p>The older (and still supported) format of $fldarray is a |
---|
72 | 2-dimensional array, where each row in the 1st dimension represents one |
---|
73 | field. Each row has this format:</p> |
---|
74 | <pre> array($fieldname, $type, [,$colsize] [,$otheroptions]*)</pre> |
---|
75 | <p>The first 2 fields must be the field name and the field type. The |
---|
76 | field type can be a portable type codes or the actual type for that |
---|
77 | database.</p> |
---|
78 | <p>Legal portable type codes include:</p> |
---|
79 | <pre> C: varchar<br> X: Largest varchar size <br> XL: For Oracle, returns CLOB, otherwise same as 'X' above<br><br> C2: Multibyte varchar<br> X2: Multibyte varchar (largest size)<br><br> B: BLOB (binary large object)<br><br> D: Date (some databases do not support this, and we return a datetime type)<br> T: Datetime or Timestamp<br> L: Integer field suitable for storing booleans (0 or 1)<br> I: Integer (mapped to I4)<br> I1: 1-byte integer<br> I2: 2-byte integer<br> I4: 4-byte integer<br> I8: 8-byte integer<br> F: Floating point number<br> N: Numeric or decimal number<br></pre> |
---|
80 | <p>The $colsize field represents the size of the field. If a decimal |
---|
81 | number is used, then it is assumed that the number following the dot is |
---|
82 | the precision, so 6.2 means a number of size 6 digits and 2 decimal |
---|
83 | places. It is recommended that the default for number types be |
---|
84 | represented as a string to avoid any rounding errors.</p> |
---|
85 | <p>The $otheroptions include the following keywords (case-insensitive):</p> |
---|
86 | <pre> AUTO For autoincrement number. Emulated with triggers if not available.<br> Sets NOTNULL also.<br> AUTOINCREMENT Same as auto.<br> KEY Primary key field. Sets NOTNULL also. Compound keys are supported.<br> PRIMARY Same as KEY.<br> DEF Synonym for DEFAULT for lazy typists.<br> DEFAULT The default value. Character strings are auto-quoted unless<br> the string begins and ends with spaces, eg ' SYSDATE '.<br> NOTNULL If field is not null.<br> DEFDATE Set default value to call function to get today's date.<br> DEFTIMESTAMP Set default to call function to get today's datetime.<br> NOQUOTE Prevents autoquoting of default string values.<br> CONSTRAINTS Additional constraints defined at the end of the field<br> definition.<br></pre> |
---|
87 | <p>The Data Dictonary accepts two formats, the older array |
---|
88 | specification:</p> |
---|
89 | <pre> $flds = array(<br> array('COLNAME', 'DECIMAL', '8.4', 'DEFAULT' => 0, 'NOTNULL'),<br> array('id', 'I' , 'AUTO'),<br> array('`MY DATE`', 'D' , 'DEFDATE'),<br> array('NAME', 'C' , '32', 'CONSTRAINTS' => 'FOREIGN KEY REFERENCES reftable')<br> );<br></pre> |
---|
90 | <p>Or the simpler declarative format:</p> |
---|
91 | <pre> $flds = "<font color="#660000"><strong><br> COLNAME DECIMAL(8.4) DEFAULT 0 NOTNULL,<br> id I AUTO,<br> `MY DATE` D DEFDATE,<br> NAME C(32) CONSTRAINTS 'FOREIGN KEY REFERENCES reftable'</strong></font><br> ";<br></pre> |
---|
92 | <p>Note that if you have special characters in the field name (e.g. My |
---|
93 | Date), you should enclose it in back-quotes. Normally field names are |
---|
94 | not case-sensitive, but if you enclose it in back-quotes, some |
---|
95 | databases will treat the names as case-sensitive (eg. Oracle) , and |
---|
96 | others won't. So be careful.</p> |
---|
97 | <p>The $taboptarray is the 3rd parameter of the CreateTableSQL |
---|
98 | function. This contains table specific settings. Legal keywords include:</p> |
---|
99 | <ul> |
---|
100 | <li><b>REPLACE</b><br> |
---|
101 | Indicates that the previous table definition should be removed |
---|
102 | (dropped)together with ALL data. See first example below. </li> |
---|
103 | <li><b>DROP</b><br> |
---|
104 | Drop table. Useful for removing unused tables. </li> |
---|
105 | <li><b>CONSTRAINTS</b><br> |
---|
106 | Define this as the key, with the constraint as the value. See the |
---|
107 | postgresql example below. Additional constraints defined for the whole |
---|
108 | table. You will probably need to prefix this with a comma. </li> |
---|
109 | </ul> |
---|
110 | <p>Database specific table options can be defined also using the name |
---|
111 | of the database type as the array key. In the following example, <em>create |
---|
112 | the table as ISAM with MySQL, and store the table in the "users" |
---|
113 | tablespace if using Oracle</em>. And because we specified REPLACE, drop |
---|
114 | the table first.</p> |
---|
115 | <pre> $taboptarray = array('mysql' => 'TYPE=ISAM', 'oci8' => 'tablespace users', 'REPLACE');</pre> |
---|
116 | <p>You can also define foreignkey constraints. The following is syntax |
---|
117 | for postgresql: |
---|
118 | </p> |
---|
119 | <pre> $taboptarray = array('constraints' => ', FOREIGN KEY (col1) REFERENCES reftable (refcol)');</pre> |
---|
120 | <h4>function DropTableSQL($tabname)</h4> |
---|
121 | <p>Returns the SQL to drop the specified table.</p> |
---|
122 | <h4>function ChangeTableSQL($tabname, $flds)</h4> |
---|
123 | <p>Checks to see if table exists, if table does not exist, behaves like |
---|
124 | CreateTableSQL. If table exists, generates appropriate ALTER TABLE |
---|
125 | MODIFY COLUMN commands if field already exists, or ALTER TABLE ADD |
---|
126 | $column if field does not exist.</p> |
---|
127 | <p>The class must be connected to the database for ChangeTableSQL to |
---|
128 | detect the existence of the table. Idea and code contributed by Florian |
---|
129 | Buzin.</p> |
---|
130 | <h4>function CreateIndexSQL($idxname, $tabname, $flds, |
---|
131 | $idxoptarray=false)</h4> |
---|
132 | <pre> RETURNS: an array of strings, the sql to be executed, or false<br> $idxname: name of index<br> $tabname: name of table<br> $flds: list of fields as a comma delimited string or an array of strings<br> $idxoptarray: array of index creation options<br></pre> |
---|
133 | <p>$idxoptarray is similar to $taboptarray in that index specific |
---|
134 | information can be embedded in the array. Other options include:</p> |
---|
135 | <pre> CLUSTERED Create clustered index (only mssql)<br> BITMAP Create bitmap index (only oci8)<br> UNIQUE Make unique index<br> FULLTEXT Make fulltext index (only mysql)<br> HASH Create hash index (only postgres)<br> DROP Drop legacy index<br></pre> |
---|
136 | <h4>function DropIndexSQL ($idxname, $tabname = NULL)</h4> |
---|
137 | <p>Returns the SQL to drop the specified index.</p> |
---|
138 | <h4>function AddColumnSQL($tabname, $flds)</h4> |
---|
139 | <p>Add one or more columns. Not guaranteed to work under all situations.</p> |
---|
140 | <h4>function AlterColumnSQL($tabname, $flds)</h4> |
---|
141 | <p>Warning, not all databases support this feature.</p> |
---|
142 | <h4>function DropColumnSQL($tabname, $flds)</h4> |
---|
143 | <p>Drop 1 or more columns.</p> |
---|
144 | <h4>function SetSchema($schema)</h4> |
---|
145 | <p>Set the schema.</p> |
---|
146 | <h4>function &MetaTables()</h4> |
---|
147 | <h4>function &MetaColumns($tab, $upper=true, $schema=false)</h4> |
---|
148 | <h4>function &MetaPrimaryKeys($tab,$owner=false,$intkey=false)</h4> |
---|
149 | <h4>function &MetaIndexes($table, $primary = false, $owner = false)</h4> |
---|
150 | <p>These functions are wrappers for the corresponding functions in the |
---|
151 | connection object. However, the table names will be autoquoted by the |
---|
152 | TableName function (see below) before being passed to the connection |
---|
153 | object.</p> |
---|
154 | <h4>function NameQuote($name = NULL)</h4> |
---|
155 | <p>If the provided name is quoted with backquotes (`) or contains |
---|
156 | special characters, returns the name quoted with the appropriate quote |
---|
157 | character, otherwise the name is returned unchanged.</p> |
---|
158 | <h4>function TableName($name)</h4> |
---|
159 | <p>The same as NameQuote, but will prepend the current schema if |
---|
160 | specified</p> |
---|
161 | <h4>function MetaType($t,$len=-1,$fieldobj=false)</h4> |
---|
162 | <h4>function ActualType($meta)</h4> |
---|
163 | <p>Convert between database-independent 'Meta' and database-specific |
---|
164 | 'Actual' type codes.</p> |
---|
165 | <h4>function ExecuteSQLArray($sqlarray, $contOnError = true)</h4> |
---|
166 | <pre> RETURNS: 0 if failed, 1 if executed all but with errors, 2 if executed successfully<br> $sqlarray: an array of strings with sql code (no semicolon at the end of string)<br> $contOnError: if true, then continue executing even if error occurs<br></pre> |
---|
167 | <p>Executes an array of SQL strings returned by CreateTableSQL or |
---|
168 | CreateIndexSQL.</p> |
---|
169 | <hr> |
---|
170 | <a name="xmlschema"></a> |
---|
171 | <h2>ADOdb XML Schema (AXMLS)</h2> |
---|
172 | <p>This is a class contributed by Richard Tango-Lowy and Dan Cech that |
---|
173 | allows the user to quickly |
---|
174 | and easily build a database using the excellent ADODB database library |
---|
175 | and a simple XML formatted file. |
---|
176 | You can <a href="http://sourceforge.net/projects/adodb-xmlschema/">download |
---|
177 | the latest version of AXMLS here</a>.</p> |
---|
178 | <h3>Quick Start</h3> |
---|
179 | <p>Adodb-xmlschema, or AXMLS, is a set of classes that allow the user |
---|
180 | to quickly and easily build or upgrade a database on almost any RDBMS |
---|
181 | using the excellent ADOdb database library and a simple XML formatted |
---|
182 | schema file. Our goal is to give developers a tool that's simple to |
---|
183 | use, but that will allow them to create a single file that can build, |
---|
184 | upgrade, and manipulate databases on most RDBMS platforms.</p> |
---|
185 | <span style="font-weight: bold;"> Installing axmls</span> |
---|
186 | <p>The easiest way to install AXMLS to download and install any recent |
---|
187 | version of the ADOdb database abstraction library. To install AXMLS |
---|
188 | manually, simply copy the adodb-xmlschema.inc.php file and the xsl |
---|
189 | directory into your adodb directory.</p> |
---|
190 | <span style="font-weight: bold;"> Using AXMLS in Your Application</span> |
---|
191 | <p>There are two steps involved in using AXMLS in your application: |
---|
192 | first, you must create a schema, or XML representation of your |
---|
193 | database, and second, you must create the PHP code that will parse and |
---|
194 | execute the schema.</p> |
---|
195 | <p>Let's begin with a schema that describes a typical, if simplistic |
---|
196 | user management table for an application.</p> |
---|
197 | <pre class="listing"><pre><?xml version="1.0"?><br><schema version="0.2"><br><br> <table name="users"><br> <desc>A typical users table for our application.</desc><br> <field name="userId" type="I"><br> <descr>A unique ID assigned to each user.</descr><br><br> <KEY/><br> <AUTOINCREMENT/><br> </field><br> <br> <field name="userName" type="C" size="16"><NOTNULL/></field><br><br> <br> <index name="userName"><br> <descr>Put a unique index on the user name</descr><br> <col>userName</col><br> <UNIQUE/><br><br> </index><br> </table><br> <br> <sql><br> <descr>Insert some data into the users table.</descr><br> <query>insert into users (userName) values ( 'admin' )</query><br><br> <query>insert into users (userName) values ( 'Joe' )</query><br> </sql><br></schema> <br></pre></pre> |
---|
198 | <p>Let's take a detailed look at this schema.</p> |
---|
199 | <p>The opening <?xml version="1.0"?> tag is required by XML. The |
---|
200 | <schema> tag tells the parser that the enclosed markup defines an |
---|
201 | XML schema. The version="0.2" attribute sets <em>the version of the |
---|
202 | AXMLS DTD used by the XML schema.</em> </p> |
---|
203 | <p>All versions of AXMLS prior to version 1.0 have a schema version of |
---|
204 | "0.1". The current schema version is "0.2".</p> |
---|
205 | <pre class="listing"><pre><?xml version="1.0"?><br><schema version="0.2"><br> ...<br></schema><br></pre></pre> |
---|
206 | <p>Next we define one or more tables. A table consists of a fields (and |
---|
207 | other objects) enclosed by <table> tags. The name="" attribute |
---|
208 | specifies the name of the table that will be created in the database.</p> |
---|
209 | <pre class="listing"><pre><table name="users"><br><br> <desc>A typical users table for our application.</desc><br> <field name="userId" type="I"><br><br> <descr>A unique ID assigned to each user.</descr><br> <KEY/><br> <AUTOINCREMENT/><br> </field><br> <br> <field name="userName" type="C" size="16"><NOTNULL/></field><br><br> <br></table><br></pre></pre> |
---|
210 | <p>This table is called "users" and has a description and two fields. |
---|
211 | The description is optional, and is currently only for your own |
---|
212 | information; it is not applied to the database.</p> |
---|
213 | <p>The first <field> tag will create a field named "userId" of |
---|
214 | type "I", or integer. (See the ADOdb Data Dictionary documentation for |
---|
215 | a list of valid types.) This <field> tag encloses two special |
---|
216 | field options: <KEY/>, which specifies this field as a primary |
---|
217 | key, and <AUTOINCREMENT/>, which specifies that the database |
---|
218 | engine should automatically fill this field with the next available |
---|
219 | value when a new row is inserted.</p> |
---|
220 | <p>The second <field> tag will create a field named "userName" of |
---|
221 | type "C", or character, and of length 16 characters. The |
---|
222 | <NOTNULL/> option specifies that this field does not allow NULLs.</p> |
---|
223 | <p>There are two ways to add indexes to a table. The simplest is to |
---|
224 | mark a field with the <KEY/> option as described above; a primary |
---|
225 | key is a unique index. The second and more powerful method uses the |
---|
226 | <index> tags.</p> |
---|
227 | <pre class="listing"><pre><table name="users"><br> ...<br> <br> <index name="userName"><br> <descr>Put a unique index on the user name</descr><br> <col>userName</col><br><br> <UNIQUE/><br> </index><br> <br></table><br></pre></pre> |
---|
228 | <p>The <index> tag specifies that an index should be created on |
---|
229 | the enclosing table. The name="" attribute provides the name of the |
---|
230 | index that will be created in the database. The description, as above, |
---|
231 | is for your information only. The <col> tags list each column |
---|
232 | that will be included in the index. Finally, the <UNIQUE/> tag |
---|
233 | specifies that this will be created as a unique index.</p> |
---|
234 | <p>Finally, AXMLS allows you to include arbitrary SQL that will be |
---|
235 | applied to the database when the schema is executed.</p> |
---|
236 | <pre class="listing"><pre><sql><br> <descr>Insert some data into the users table.</descr><br> <query>insert into users (userName) values ( 'admin' )</query><br><br> <query>insert into users (userName) values ( 'Joe' )</query><br></sql><br></pre></pre> |
---|
237 | <p>The <sql> tag encloses any number of SQL queries that you |
---|
238 | define for your own use.</p> |
---|
239 | <p>Now that we've defined an XML schema, you need to know how to apply |
---|
240 | it to your database. Here's a simple PHP script that shows how to load |
---|
241 | the schema.</p> |
---|
242 | <pre class="listing"><pre><?PHP<br>/* You must tell the script where to find the ADOdb and<br> * the AXMLS libraries.<br> */<br>require( "path_to_adodb/adodb.inc.php");<br>require( "path_to_adodb/adodb-xmlschema.inc.php" );<br><br>/* Configuration information. Define the schema filename,<br> * RDBMS platform (see the ADODB documentation for valid<br> * platform names), and database connection information here.<br> */<br>$schemaFile = 'example.xml';<br>$platform = 'mysql';<br>$dbHost = 'localhost';<br>$dbName = 'database';<br>$dbUser = 'username';<br>$dbPassword = 'password';<br><br>/* Start by creating a normal ADODB connection.<br> */<br>$db = ADONewConnection( $platform );<br>$db->Connect( $dbHost, $dbUser, $dbPassword, $dbName );<br><br>/* Use the database connection to create a new adoSchema object.<br> */<br>$schema = new adoSchema( $db );<br><br>/* Call ParseSchema() to build SQL from the XML schema file.<br> * Then call ExecuteSchema() to apply the resulting SQL to <br> * the database.<br> */<br>$sql = $schema->ParseSchema( $schemaFile );<br>$result = $schema->ExecuteSchema();<br>?><br></pre></pre> |
---|
243 | <p>Let's look at each part of the example in turn. After you manually |
---|
244 | create the database, there are three steps required to load (or |
---|
245 | upgrade) your schema.</p> |
---|
246 | <p>First, create a normal ADOdb connection. The variables and values |
---|
247 | here should be those required to connect to your database.</p> |
---|
248 | <pre class="listing"><pre>$db = ADONewConnection( 'mysql' );<br>$db->Connect( 'host', 'user', 'password', 'database' );<br></pre></pre> |
---|
249 | <p>Second, create the adoSchema object that load and manipulate your |
---|
250 | schema. You must pass an ADOdb database connection object in order to |
---|
251 | create the adoSchema object.</p> |
---|
252 | <pre class="listing"><pre>$schema = new adoSchema( $db );<br></pre></pre> |
---|
253 | <p>Third, call ParseSchema() to parse the schema and then |
---|
254 | ExecuteSchema() to apply it to the database. You must pass |
---|
255 | ParseSchema() the path and filename of your schema file.</p> |
---|
256 | <pre class="listing"><pre><br>$schema->ParseSchema( $schemaFile ); <br>$schema->ExecuteSchema(); <br></pre></pre> |
---|
257 | <p>Execute the above code and then log into your database. If you've |
---|
258 | done all this right, you should see your tables, indexes, and SQL.</p> |
---|
259 | <p>You can find the source files for this tutorial in the examples |
---|
260 | directory as tutorial_shema.xml and tutorial.php. See the class |
---|
261 | documentation for a more detailed description of the adoSchema methods, |
---|
262 | including methods and schema elements that are not described in this |
---|
263 | tutorial.</p> |
---|
264 | <h3>Upgrading</h3> |
---|
265 | If your schema version is older, than XSLT is used to transform the |
---|
266 | schema to the newest version. This means that if you are using an older |
---|
267 | XML schema format, you need to have the XSLT extension installed. |
---|
268 | If you do not want to require your users to have the XSLT extension |
---|
269 | installed, make sure you modify your XML schema to conform to the |
---|
270 | latest version. |
---|
271 | <hr> |
---|
272 | <address>If you have any questions or comments, please email them to |
---|
273 | Richard at richtl#arscognita.com. |
---|
274 | </address> |
---|
275 | </body> |
---|
276 | </html> |
---|