1<?php 2/* 3 4 V4.81 3 May 2006 (c) 2000-2009 John Lim (jlim#natsoft.com). All rights reserved. 5 Released under both BSD license and Lesser GPL library license. 6 Whenever there is any discrepancy between the two licenses, 7 the BSD license will take precedence. 8 9 Set tabs to 4 for best viewing. 10 11*/ 12 13error_reporting(E_ALL); 14include_once('../adodb.inc.php'); 15 16foreach(array('sapdb','sybase','mysql','access','oci8po','odbc_mssql','odbc','db2','firebird','postgres','informix') as $dbType) { 17 echo "<h3>$dbType</h3><p>"; 18 $db = NewADOConnection($dbType); 19 $dict = NewDataDictionary($db); 20 21 if (!$dict) continue; 22 $dict->debug = 1; 23 24 $opts = array('REPLACE','mysql' => 'ENGINE=INNODB', 'oci8' => 'TABLESPACE USERS'); 25 26/* $flds = array( 27 array('id', 'I', 28 'AUTO','KEY'), 29 30 array('name' => 'firstname', 'type' => 'varchar','size' => 30, 31 'DEFAULT'=>'Joan'), 32 33 array('lastname','varchar',28, 34 'DEFAULT'=>'Chen','key'), 35 36 array('averylonglongfieldname','X',1024, 37 'NOTNULL','default' => 'test'), 38 39 array('price','N','7.2', 40 'NOTNULL','default' => '0.00'), 41 42 array('MYDATE', 'D', 43 'DEFDATE'), 44 array('TS','T', 45 'DEFTIMESTAMP') 46 );*/ 47 48 $flds = " 49ID I AUTO KEY, 50FIRSTNAME VARCHAR(30) DEFAULT 'Joan' INDEX idx_name, 51LASTNAME VARCHAR(28) DEFAULT 'Chen' key INDEX idx_name INDEX idx_lastname, 52averylonglongfieldname X(1024) DEFAULT 'test', 53price N(7.2) DEFAULT '0.00', 54MYDATE D DEFDATE INDEX idx_date, 55BIGFELLOW X NOTNULL, 56TS_SECS T DEFTIMESTAMP, 57TS_SUBSEC TS DEFTIMESTAMP 58"; 59 60 61 $sqla = $dict->CreateDatabase('KUTU',array('postgres'=>"LOCATION='/u01/postdata'")); 62 $dict->SetSchema('KUTU'); 63 64 $sqli = ($dict->CreateTableSQL('testtable',$flds, $opts)); 65 $sqla = array_merge($sqla,$sqli); 66 67 $sqli = $dict->CreateIndexSQL('idx','testtable','price,firstname,lastname',array('BITMAP','FULLTEXT','CLUSTERED','HASH')); 68 $sqla = array_merge($sqla,$sqli); 69 $sqli = $dict->CreateIndexSQL('idx2','testtable','price,lastname');//,array('BITMAP','FULLTEXT','CLUSTERED')); 70 $sqla = array_merge($sqla,$sqli); 71 72 $addflds = array(array('height', 'F'),array('weight','F')); 73 $sqli = $dict->AddColumnSQL('testtable',$addflds); 74 $sqla = array_merge($sqla,$sqli); 75 $addflds = array(array('height', 'F','NOTNULL'),array('weight','F','NOTNULL')); 76 $sqli = $dict->AlterColumnSQL('testtable',$addflds); 77 $sqla = array_merge($sqla,$sqli); 78 79 80 printsqla($dbType,$sqla); 81 82 if (file_exists('d:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php')) 83 if ($dbType == 'mysqlt') { 84 $db->Connect('localhost', "root", "", "test"); 85 $dict->SetSchema(''); 86 $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds); 87 if ($sqla2) printsqla($dbType,$sqla2); 88 } 89 if ($dbType == 'postgres') { 90 if (@$db->Connect('localhost', "tester", "test", "test")); 91 $dict->SetSchema(''); 92 $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds); 93 if ($sqla2) printsqla($dbType,$sqla2); 94 } 95 96 if ($dbType == 'odbc_mssql') { 97 $dsn = $dsn = "PROVIDER=MSDASQL;Driver={SQL Server};Server=localhost;Database=northwind;"; 98 if (@$db->Connect($dsn, "sa", "natsoft", "test")); 99 $dict->SetSchema(''); 100 $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds); 101 if ($sqla2) printsqla($dbType,$sqla2); 102 } 103 104 105 106 adodb_pr($dict->databaseType); 107 printsqla($dbType, $dict->DropColumnSQL('table',array('my col','`col2_with_Quotes`','A_col3','col3(10)'))); 108 printsqla($dbType, $dict->ChangeTableSQL('adoxyz','LASTNAME varchar(32)')); 109 110} 111 112function printsqla($dbType,$sqla) 113{ 114 print "<pre>"; 115 //print_r($dict->MetaTables()); 116 foreach($sqla as $s) { 117 $s = htmlspecialchars($s); 118 print "$s;\n"; 119 if ($dbType == 'oci8') print "/\n"; 120 } 121 print "</pre><hr />"; 122} 123 124/*** 125 126Generated SQL: 127 128mysql 129 130CREATE DATABASE KUTU; 131DROP TABLE KUTU.testtable; 132CREATE TABLE KUTU.testtable ( 133id INTEGER NOT NULL AUTO_INCREMENT, 134firstname VARCHAR(30) DEFAULT 'Joan', 135lastname VARCHAR(28) NOT NULL DEFAULT 'Chen', 136averylonglongfieldname LONGTEXT NOT NULL, 137price NUMERIC(7,2) NOT NULL DEFAULT 0.00, 138MYDATE DATE DEFAULT CURDATE(), 139 PRIMARY KEY (id, lastname) 140)TYPE=ISAM; 141CREATE FULLTEXT INDEX idx ON KUTU.testtable (firstname,lastname); 142CREATE INDEX idx2 ON KUTU.testtable (price,lastname); 143ALTER TABLE KUTU.testtable ADD height DOUBLE; 144ALTER TABLE KUTU.testtable ADD weight DOUBLE; 145ALTER TABLE KUTU.testtable MODIFY COLUMN height DOUBLE NOT NULL; 146ALTER TABLE KUTU.testtable MODIFY COLUMN weight DOUBLE NOT NULL; 147 148 149-------------------------------------------------------------------------------- 150 151oci8 152 153CREATE USER KUTU IDENTIFIED BY tiger; 154/ 155GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO KUTU; 156/ 157DROP TABLE KUTU.testtable CASCADE CONSTRAINTS; 158/ 159CREATE TABLE KUTU.testtable ( 160id NUMBER(16) NOT NULL, 161firstname VARCHAR(30) DEFAULT 'Joan', 162lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL, 163averylonglongfieldname CLOB NOT NULL, 164price NUMBER(7,2) DEFAULT 0.00 NOT NULL, 165MYDATE DATE DEFAULT TRUNC(SYSDATE), 166 PRIMARY KEY (id, lastname) 167)TABLESPACE USERS; 168/ 169DROP SEQUENCE KUTU.SEQ_testtable; 170/ 171CREATE SEQUENCE KUTU.SEQ_testtable; 172/ 173CREATE OR REPLACE TRIGGER KUTU.TRIG_SEQ_testtable BEFORE insert ON KUTU.testtable 174 FOR EACH ROW 175 BEGIN 176 select KUTU.SEQ_testtable.nextval into :new.id from dual; 177 END; 178/ 179CREATE BITMAP INDEX idx ON KUTU.testtable (firstname,lastname); 180/ 181CREATE INDEX idx2 ON KUTU.testtable (price,lastname); 182/ 183ALTER TABLE testtable ADD ( 184 height NUMBER, 185 weight NUMBER); 186/ 187ALTER TABLE testtable MODIFY( 188 height NUMBER NOT NULL, 189 weight NUMBER NOT NULL); 190/ 191 192 193-------------------------------------------------------------------------------- 194 195postgres 196AlterColumnSQL not supported for PostgreSQL 197 198 199CREATE DATABASE KUTU LOCATION='/u01/postdata'; 200DROP TABLE KUTU.testtable; 201CREATE TABLE KUTU.testtable ( 202id SERIAL, 203firstname VARCHAR(30) DEFAULT 'Joan', 204lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL, 205averylonglongfieldname TEXT NOT NULL, 206price NUMERIC(7,2) DEFAULT 0.00 NOT NULL, 207MYDATE DATE DEFAULT CURRENT_DATE, 208 PRIMARY KEY (id, lastname) 209); 210CREATE INDEX idx ON KUTU.testtable USING HASH (firstname,lastname); 211CREATE INDEX idx2 ON KUTU.testtable (price,lastname); 212ALTER TABLE KUTU.testtable ADD height FLOAT8; 213ALTER TABLE KUTU.testtable ADD weight FLOAT8; 214 215 216-------------------------------------------------------------------------------- 217 218odbc_mssql 219 220CREATE DATABASE KUTU; 221DROP TABLE KUTU.testtable; 222CREATE TABLE KUTU.testtable ( 223id INT IDENTITY(1,1) NOT NULL, 224firstname VARCHAR(30) DEFAULT 'Joan', 225lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL, 226averylonglongfieldname TEXT NOT NULL, 227price NUMERIC(7,2) DEFAULT 0.00 NOT NULL, 228MYDATE DATETIME DEFAULT GetDate(), 229 PRIMARY KEY (id, lastname) 230); 231CREATE CLUSTERED INDEX idx ON KUTU.testtable (firstname,lastname); 232CREATE INDEX idx2 ON KUTU.testtable (price,lastname); 233ALTER TABLE KUTU.testtable ADD 234 height REAL, 235 weight REAL; 236ALTER TABLE KUTU.testtable ALTER COLUMN height REAL NOT NULL; 237ALTER TABLE KUTU.testtable ALTER COLUMN weight REAL NOT NULL; 238 239 240-------------------------------------------------------------------------------- 241*/ 242 243 244echo "<h1>Test XML Schema</h1>"; 245$ff = file('xmlschema.xml'); 246echo "<pre>"; 247foreach($ff as $xml) echo htmlspecialchars($xml); 248echo "</pre>"; 249include_once('test-xmlschema.php'); 250?>