| blob | aa795c0e76d13d923c53dc27b0e8fdad01fd0a47 |
1 <html>
2 <head>
7 /*font-family: Arial, Helvetica, sans-serif;*/
9 }
10 pre {
12 }
15 }
16 </style>
17 </head>
21 <p>V4.20 22 Feb 2004 (c) 2000-2004 John Lim (<a href="mailto:jlim#natsoft.com.my">jlim#natsoft.com.my</a>).<br> AXMLS (c) 2004 ars Cognita, Inc</p>
22 <p><font size="1">This software is dual licensed using BSD-Style and LGPL. This means you can use it in compiled proprietary and commercial products.</font></p>
23 <p>Useful ADOdb links: <a href=http://php.weblogs.com/adodb?dd=1>Download</a> <a href=http://php.weblogs.com/adodb_manual?dd=1>Other Docs</a></p>
25 <p>This documentation describes a PHP class library to automate the creation of tables,
26 indexes and foreign key constraints portably for multiple databases. Richard Tango-Lowy and Dan Cech
27 have been kind enough to contribute <a href=#xmlschema>AXMLS</a>, an XML schema system for defining databases.</p>
30 <p>
34 </p>
37 <pre>
38 include_once('adodb.inc.php');
40 $db->NewADOConnection('mysql');
41 $db->Connect(...);
46 <font color="#006600"># We have a portable declarative data dictionary format in ADOdb, similar to SQL.
47 # Field types use 1 character codes, and fields are separated by commas.
49 $flds = "
51 col2 I DEFAULT 0,
52 col3 N(12.2)</strong></font>
53 ";
58 $idxflds = 'co11, col2';
61 </pre>
69 <pre>
70 RETURNS: an array of strings, the sql to be executed, or false
71 $tabname: name of table
72 $fldarray: string (or array) containing field info
73 $taboptarray: array containing table options
74 </pre>
75 <p>The new format of $fldarray uses a free text format, where each field is comma-delimited.
76 The first token for each field is the field name, followed by the type and optional
77 field size. Then optional keywords in $otheroptions:</p>
79 <p>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:</p>
82 <p>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.</p>
84 <pre>
85 C: varchar
86 X: Largest varchar size
87 XL: For Oracle, returns CLOB, otherwise same as 'X' above
89 C2: Multibyte varchar
90 X2: Multibyte varchar (largest size)
92 B: BLOB (binary large object)
94 D: Date (some databases do not support this, and we return a datetime type)
95 T: Datetime or Timestamp
97 I: Integer (mapped to I4)
98 I1: 1-byte integer
99 I2: 2-byte integer
100 I4: 4-byte integer
101 I8: 8-byte integer
102 F: Floating point number
103 N: Numeric or decimal number
104 </pre>
105 <p>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.</p>
107 <pre>
108 AUTO For autoincrement number. Emulated with triggers if not available.
109 Sets NOTNULL also.
110 AUTOINCREMENT Same as auto.
111 KEY Primary key field. Sets NOTNULL also. Compound keys are supported.
112 PRIMARY Same as KEY.
113 DEF Synonym for DEFAULT for lazy typists.
114 DEFAULT The default value. Character strings are auto-quoted unless
115 the string begins and ends with spaces, eg ' SYSDATE '.
116 NOTNULL If field is not null.
117 DEFDATE Set default value to call function to get today's date.
118 DEFTIMESTAMP Set default to call function to get today's datetime.
119 NOQUOTE Prevents autoquoting of default string values.
120 CONSTRAINTS Additional constraints defined at the end of the field
121 definition.
122 </pre>
124 <pre>
125 $flds = array(
127 array('id', 'I' , 'AUTO'),
128 array('`MY DATE`', 'D' , 'DEFDATE'),
130 );
131 </pre>
133 <pre>
135 COLNAME DECIMAL(8.4) DEFAULT 0 NOTNULL,
136 id I AUTO,
137 `MY DATE` D DEFDATE,
138 NAME C(32) CONSTRAINTS 'FOREIGN KEY REFERENCES reftable'</strong></font>
139 ";
140 </pre>
141 <p>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 treat the names as case-sensitive, and some don't. So be careful.</p>
143 <p>The $taboptarray is the 3rd parameter of the CreateTableSQL function. This contains table specific settings. Legal keywords include:</p>
144 <ul>
146 Indicates that the previous table definition should be removed (dropped)together with ALL data. See first example below.
147 </li>
149 Drop table. Useful for removing unused tables.
150 </li>
152 Define this as the key, with the constraint as the value. See the postgresql example below.
153 Additional constraints defined for the whole table. You will probably need to prefix this with a comma.
154 </li>
155 </ul>
157 <p>Database specific table options can be defined also using the name of the database type as the array key. In the following example, <em>create the table as ISAM with MySQL, and store the table in the "users" tablespace if using Oracle</em>. And because we specified REPLACE, drop the table first.</p>
158 <pre> $taboptarray = array('mysql' => 'TYPE=ISAM', 'oci8' => 'tablespace users', 'REPLACE');</pre>
160 <p>You can also define foreignkey constraints. The following is syntax for postgresql:
161 <pre> $taboptarray = array('constraints' => ', FOREIGN KEY (col1) REFERENCES reftable (refcol)');</pre>
167 <p>Checks to see if table exists, if table does not exist, behaves like CreateTableSQL.
168 If table exists, generates appropriate ALTER TABLE MODIFY COLUMN commands if
169 field already exists, or ALTER TABLE ADD $column if field does not exist.</p>
170 <p>The class must be connected to the database for ChangeTableSQL to detect the
171 existence of the table. Idea and code contributed by Florian Buzin.</p>
174 <pre>
175 RETURNS: an array of strings, the sql to be executed, or false
176 $idxname: name of index
177 $tabname: name of table
178 $flds: list of fields as a comma delimited string or an array of strings
179 $idxoptarray: array of index creation options
180 </pre>
181 <p>$idxoptarray is similar to $taboptarray in that index specific information can be embedded in the array. Other options include:</p>
182 <pre>
183 CLUSTERED Create clustered index (only mssql)
184 BITMAP Create bitmap index (only oci8)
185 UNIQUE Make unique index
186 FULLTEXT Make fulltext index (only mysql)
187 HASH Create hash index (only postgres)
188 DROP Drop legacy index
189 </pre>
210 <p>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.</p>
213 <p>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.</p>
223 <pre>
225 $sqlarray: an array of strings with sql code (no semicolon at the end of string)
226 $contOnError: if true, then continue executing even if error occurs
227 </pre>
230 <hr />
234 <p>This is a class contributed by Richard Tango-Lowy and Dan Cech that allows the user to quickly
235 and easily build a database using the excellent ADODB database library and a simple XML formatted file.
236 You can <a href=http://sourceforge.net/projects/adodb-xmlschema/>download the latest version of AXMLS here</a>.</p>
240 <pre>
261 insert into mytable ( row1, row2 ) values ( 12, 'stuff' )
264 insert into mytable ( row1, row2 ) values ( 12, 'different stuff' )
268 </pre>
272 in the database and insert one row into <i>mytable</i> if the platform is postgres or mysql.</p>
274 <pre>
275 include_once('/path/to/adodb.inc.php');
276 include_once('/path/to/adodb-xmlschema.inc.php');
278 <font color="#006600">// To build the schema, start by creating a normal ADOdb connection:</font>
279 $db->NewADOConnection( 'mysql' );
280 $db->Connect( ... );
296 // (PHP won't do this for you!)</font>
298 </pre>
301 <h3>Using AXMLS in Your
302 Application</h3>
304 <p>
305 There are two steps involved in using
306 AXMLS in your application: first,
307 you must create a schema, or XML representation of your
308 database, and second, you must create the PHP code that will
309 parse and execute the schema.</p>
310 <p>Let's begin with a schema that describes a typical, if simplistic
311 user management table for an application.</p>
324 <field name="userName" type="C" size="16"><NOTNULL/></field>
339 </pre></pre>
343 tells the parser that the enclosed markup defines an XML
345 <em>the version of the AXMLS DTD used by the XML
346 schema.</em> <p>All versions of AXMLS prior
352 ...
354 </pre></pre>
355 <p>Next we define one or more tables. A table consists of a
356 fields (and other objects) enclosed by
358 name="" attribute specifies the name of
359 the table that will be created in the database.</p>
370 <field name="userName" type="C" size="16"><NOTNULL/></field>
373 </pre></pre>
375 two fields. The description is optional, and is currently
376 only for your own information; it is not applied to the
377 database.</p>
380 ADOdb Data Dictionary
381 documentation for a list of valid types.) This
384 specifies this field as a primary key, and
386 that the database engine should automatically fill this
387 field with the next available value when a new row is
388 inserted.</p>
392 option specifies that this field does not allow
393 NULLs.</p>
394 <p>There are two ways to add indexes to a table. The
395 simplest is to mark a field with the
397 primary key is a unique index. The second and more powerful
401 ...
410 </pre></pre>
412 index should be created on the enclosing table. The
413 name="" attribute provides the name of the
414 index that will be created in the database. The
415 description, as above, is for your information only. The
417 will be included in the index. Finally, the
419 will be created as a unique index.</p>
420 <p>Finally, AXMLS allows you to include arbitrary SQL that
421 will be applied to the database when the schema is
422 executed.</p>
429 </pre></pre>
431 of SQL queries that you define for your own use.</p>
432 <p>Now that we've defined an XML schema, you need to know how to
433 apply it to your database. Here's a simple PHP script that shows
434 how to load the schema.</p>
436 <?PHP
437 /* You must tell the script where to find the ADOdb and
438 * the AXMLS libraries.
439 */
443 /* Configuration information. Define the schema filename,
444 * RDBMS platform (see the ADODB documentation for valid
445 * platform names), and database connection information here.
446 */
447 $schemaFile = 'example.xml';
448 $platform = 'mysql';
449 $dbHost = 'localhost';
450 $dbName = 'database';
451 $dbUser = 'username';
452 $dbPassword = 'password';
454 /* Start by creating a normal ADODB connection.
455 */
456 $db = ADONewConnection( $platform );
457 $db->Connect( $dbHost, $dbUser, $dbPassword, $dbName );
459 /* Use the database connection to create a new adoSchema object.
460 */
461 $schema = new adoSchema( $db );
463 /* Call ParseSchema() to build SQL from the XML schema file.
464 * Then call ExecuteSchema() to apply the resulting SQL to
465 * the database.
466 */
467 $sql = $schema->ParseSchema( $schemaFile );
468 $result = $schema->ExecuteSchema();
469 ?>
470 </pre></pre></p>
471 <p>Let's look at each part of the example in turn. After you
472 manually create the database, there are three steps required to
473 load (or upgrade) your schema.</p>
474 <p>First, create a normal ADOdb connection. The variables
475 and values here should be those required to connect to your
476 database.</p>
478 $db = ADONewConnection( 'mysql' );
479 $db->Connect( 'host', 'user', 'password', 'database' );
480 </pre></pre>
481 <p>Second, create the adoSchema object that load and
482 manipulate your schema. You must pass an ADOdb database
483 connection object in order to create the adoSchema
484 object.</p>
486 $schema = new adoSchema( $db );
487 </pre></pre>
488 <p>Third, call ParseSchema() to parse the
489 schema and then ExecuteSchema() to apply
490 it to the database. You must pass
491 ParseSchema() the path and filename of
492 your schema file.</p>
494 $schema->ParseSchema( $schemaFile );
495 $schema->ExecuteSchema();
496 </pre></pre>
497 <p>Execute the above code and then log into your database. If you've
498 done all this right, you should see your tables, indexes, and
499 SQL.</p>
500 <p>You can find the source files for this tutorial in the
501 examples directory as
502 tutorial_shema.xml and
503 tutorial.php. See the class documentation for
504 a more detailed description of the adoSchema methods, including
505 methods and schema elements that are not described in this
506 tutorial.</p>
510 <PRE>
538 </pre>
541 If your schema version is older, than XSLT is used to transform the schema to the newest version.
542 This means that if you are using an older XML schema format, you need to have the XSLT extension installed.
543 If you do not want to require your users to have the XSLT extension installed, make sure you
544 modify your XML schema to conform to the latest version.
545 <hr />
547 <address>If you have any questions or comments, please email them to Richard at richtl#arscognita.com.
548 </address>
550 </body>
551 </html>