[2] | 1 | <?php |
---|
| 2 | /* |
---|
| 3 | |
---|
[8222] | 4 | V4.81 3 May 2006 (c) 2000-2012 John Lim (jlim#natsoft.com). All rights reserved. |
---|
[2] | 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 | |
---|
| 13 | error_reporting(E_ALL); |
---|
| 14 | include_once('../adodb.inc.php'); |
---|
| 15 | |
---|
[8222] | 16 | foreach(array('sapdb','sybase','mysql','access','oci8po','odbc_mssql','odbc','db2','firebird','postgres','informix') as $dbType) { |
---|
[2] | 17 | echo "<h3>$dbType</h3><p>"; |
---|
| 18 | $db = NewADOConnection($dbType); |
---|
| 19 | $dict = NewDataDictionary($db); |
---|
| 20 | |
---|
| 21 | if (!$dict) continue; |
---|
| 22 | $dict->debug = 1; |
---|
| 23 | |
---|
[34] | 24 | $opts = array('REPLACE','mysql' => 'ENGINE=INNODB', 'oci8' => 'TABLESPACE USERS'); |
---|
[2] | 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 = " |
---|
| 49 | ID I AUTO KEY, |
---|
[34] | 50 | FIRSTNAME VARCHAR(30) DEFAULT 'Joan' INDEX idx_name, |
---|
[8222] | 51 | LASTNAME VARCHAR(28) DEFAULT 'Chen' key INDEX idx_name INDEX idx_lastname, |
---|
[2] | 52 | averylonglongfieldname X(1024) DEFAULT 'test', |
---|
| 53 | price N(7.2) DEFAULT '0.00', |
---|
[34] | 54 | MYDATE D DEFDATE INDEX idx_date, |
---|
[2] | 55 | BIGFELLOW X NOTNULL, |
---|
[8222] | 56 | TS_SECS T DEFTIMESTAMP, |
---|
| 57 | TS_SUBSEC TS DEFTIMESTAMP |
---|
| 58 | "; |
---|
[2] | 59 | |
---|
| 60 | |
---|
| 61 | $sqla = $dict->CreateDatabase('KUTU',array('postgres'=>"LOCATION='/u01/postdata'")); |
---|
| 62 | $dict->SetSchema('KUTU'); |
---|
| 63 | |
---|
| 64 | $sqli = ($dict->CreateTableSQL('testtable',$flds, $opts)); |
---|
[34] | 65 | $sqla = array_merge($sqla,$sqli); |
---|
[2] | 66 | |
---|
[34] | 67 | $sqli = $dict->CreateIndexSQL('idx','testtable','price,firstname,lastname',array('BITMAP','FULLTEXT','CLUSTERED','HASH')); |
---|
| 68 | $sqla = array_merge($sqla,$sqli); |
---|
[2] | 69 | $sqli = $dict->CreateIndexSQL('idx2','testtable','price,lastname');//,array('BITMAP','FULLTEXT','CLUSTERED')); |
---|
[34] | 70 | $sqla = array_merge($sqla,$sqli); |
---|
[2] | 71 | |
---|
| 72 | $addflds = array(array('height', 'F'),array('weight','F')); |
---|
| 73 | $sqli = $dict->AddColumnSQL('testtable',$addflds); |
---|
[34] | 74 | $sqla = array_merge($sqla,$sqli); |
---|
[2] | 75 | $addflds = array(array('height', 'F','NOTNULL'),array('weight','F','NOTNULL')); |
---|
| 76 | $sqli = $dict->AlterColumnSQL('testtable',$addflds); |
---|
[34] | 77 | $sqla = array_merge($sqla,$sqli); |
---|
[2] | 78 | |
---|
| 79 | |
---|
| 80 | printsqla($dbType,$sqla); |
---|
| 81 | |
---|
| 82 | if (file_exists('d:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php')) |
---|
[34] | 83 | if ($dbType == 'mysqlt') { |
---|
[2] | 84 | $db->Connect('localhost', "root", "", "test"); |
---|
| 85 | $dict->SetSchema(''); |
---|
| 86 | $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds); |
---|
| 87 | if ($sqla2) printsqla($dbType,$sqla2); |
---|
| 88 | } |
---|
[34] | 89 | if ($dbType == 'postgres') { |
---|
| 90 | if (@$db->Connect('localhost', "tester", "test", "test")); |
---|
[2] | 91 | $dict->SetSchema(''); |
---|
| 92 | $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds); |
---|
| 93 | if ($sqla2) printsqla($dbType,$sqla2); |
---|
| 94 | } |
---|
| 95 | |
---|
[34] | 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 | |
---|
[2] | 110 | } |
---|
| 111 | |
---|
| 112 | function 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 | } |
---|
[34] | 121 | print "</pre><hr />"; |
---|
[2] | 122 | } |
---|
| 123 | |
---|
| 124 | /*** |
---|
| 125 | |
---|
| 126 | Generated SQL: |
---|
| 127 | |
---|
| 128 | mysql |
---|
| 129 | |
---|
| 130 | CREATE DATABASE KUTU; |
---|
| 131 | DROP TABLE KUTU.testtable; |
---|
| 132 | CREATE TABLE KUTU.testtable ( |
---|
| 133 | id INTEGER NOT NULL AUTO_INCREMENT, |
---|
| 134 | firstname VARCHAR(30) DEFAULT 'Joan', |
---|
| 135 | lastname VARCHAR(28) NOT NULL DEFAULT 'Chen', |
---|
| 136 | averylonglongfieldname LONGTEXT NOT NULL, |
---|
| 137 | price NUMERIC(7,2) NOT NULL DEFAULT 0.00, |
---|
| 138 | MYDATE DATE DEFAULT CURDATE(), |
---|
| 139 | PRIMARY KEY (id, lastname) |
---|
| 140 | )TYPE=ISAM; |
---|
| 141 | CREATE FULLTEXT INDEX idx ON KUTU.testtable (firstname,lastname); |
---|
| 142 | CREATE INDEX idx2 ON KUTU.testtable (price,lastname); |
---|
| 143 | ALTER TABLE KUTU.testtable ADD height DOUBLE; |
---|
| 144 | ALTER TABLE KUTU.testtable ADD weight DOUBLE; |
---|
| 145 | ALTER TABLE KUTU.testtable MODIFY COLUMN height DOUBLE NOT NULL; |
---|
| 146 | ALTER TABLE KUTU.testtable MODIFY COLUMN weight DOUBLE NOT NULL; |
---|
| 147 | |
---|
| 148 | |
---|
| 149 | -------------------------------------------------------------------------------- |
---|
| 150 | |
---|
| 151 | oci8 |
---|
| 152 | |
---|
| 153 | CREATE USER KUTU IDENTIFIED BY tiger; |
---|
| 154 | / |
---|
| 155 | GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO KUTU; |
---|
| 156 | / |
---|
| 157 | DROP TABLE KUTU.testtable CASCADE CONSTRAINTS; |
---|
| 158 | / |
---|
| 159 | CREATE TABLE KUTU.testtable ( |
---|
| 160 | id NUMBER(16) NOT NULL, |
---|
| 161 | firstname VARCHAR(30) DEFAULT 'Joan', |
---|
| 162 | lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL, |
---|
| 163 | averylonglongfieldname CLOB NOT NULL, |
---|
| 164 | price NUMBER(7,2) DEFAULT 0.00 NOT NULL, |
---|
| 165 | MYDATE DATE DEFAULT TRUNC(SYSDATE), |
---|
| 166 | PRIMARY KEY (id, lastname) |
---|
| 167 | )TABLESPACE USERS; |
---|
| 168 | / |
---|
| 169 | DROP SEQUENCE KUTU.SEQ_testtable; |
---|
| 170 | / |
---|
| 171 | CREATE SEQUENCE KUTU.SEQ_testtable; |
---|
| 172 | / |
---|
| 173 | CREATE 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 | / |
---|
| 179 | CREATE BITMAP INDEX idx ON KUTU.testtable (firstname,lastname); |
---|
| 180 | / |
---|
| 181 | CREATE INDEX idx2 ON KUTU.testtable (price,lastname); |
---|
| 182 | / |
---|
| 183 | ALTER TABLE testtable ADD ( |
---|
| 184 | height NUMBER, |
---|
| 185 | weight NUMBER); |
---|
| 186 | / |
---|
| 187 | ALTER TABLE testtable MODIFY( |
---|
| 188 | height NUMBER NOT NULL, |
---|
| 189 | weight NUMBER NOT NULL); |
---|
| 190 | / |
---|
| 191 | |
---|
| 192 | |
---|
| 193 | -------------------------------------------------------------------------------- |
---|
| 194 | |
---|
| 195 | postgres |
---|
| 196 | AlterColumnSQL not supported for PostgreSQL |
---|
| 197 | |
---|
| 198 | |
---|
| 199 | CREATE DATABASE KUTU LOCATION='/u01/postdata'; |
---|
| 200 | DROP TABLE KUTU.testtable; |
---|
| 201 | CREATE TABLE KUTU.testtable ( |
---|
| 202 | id SERIAL, |
---|
| 203 | firstname VARCHAR(30) DEFAULT 'Joan', |
---|
| 204 | lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL, |
---|
| 205 | averylonglongfieldname TEXT NOT NULL, |
---|
| 206 | price NUMERIC(7,2) DEFAULT 0.00 NOT NULL, |
---|
| 207 | MYDATE DATE DEFAULT CURRENT_DATE, |
---|
| 208 | PRIMARY KEY (id, lastname) |
---|
| 209 | ); |
---|
| 210 | CREATE INDEX idx ON KUTU.testtable USING HASH (firstname,lastname); |
---|
| 211 | CREATE INDEX idx2 ON KUTU.testtable (price,lastname); |
---|
| 212 | ALTER TABLE KUTU.testtable ADD height FLOAT8; |
---|
| 213 | ALTER TABLE KUTU.testtable ADD weight FLOAT8; |
---|
| 214 | |
---|
| 215 | |
---|
| 216 | -------------------------------------------------------------------------------- |
---|
| 217 | |
---|
| 218 | odbc_mssql |
---|
| 219 | |
---|
| 220 | CREATE DATABASE KUTU; |
---|
| 221 | DROP TABLE KUTU.testtable; |
---|
| 222 | CREATE TABLE KUTU.testtable ( |
---|
| 223 | id INT IDENTITY(1,1) NOT NULL, |
---|
| 224 | firstname VARCHAR(30) DEFAULT 'Joan', |
---|
| 225 | lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL, |
---|
| 226 | averylonglongfieldname TEXT NOT NULL, |
---|
| 227 | price NUMERIC(7,2) DEFAULT 0.00 NOT NULL, |
---|
| 228 | MYDATE DATETIME DEFAULT GetDate(), |
---|
| 229 | PRIMARY KEY (id, lastname) |
---|
| 230 | ); |
---|
| 231 | CREATE CLUSTERED INDEX idx ON KUTU.testtable (firstname,lastname); |
---|
| 232 | CREATE INDEX idx2 ON KUTU.testtable (price,lastname); |
---|
| 233 | ALTER TABLE KUTU.testtable ADD |
---|
| 234 | height REAL, |
---|
| 235 | weight REAL; |
---|
| 236 | ALTER TABLE KUTU.testtable ALTER COLUMN height REAL NOT NULL; |
---|
| 237 | ALTER TABLE KUTU.testtable ALTER COLUMN weight REAL NOT NULL; |
---|
| 238 | |
---|
| 239 | |
---|
| 240 | -------------------------------------------------------------------------------- |
---|
| 241 | */ |
---|
| 242 | |
---|
[34] | 243 | |
---|
[2] | 244 | echo "<h1>Test XML Schema</h1>"; |
---|
| 245 | $ff = file('xmlschema.xml'); |
---|
| 246 | echo "<pre>"; |
---|
| 247 | foreach($ff as $xml) echo htmlspecialchars($xml); |
---|
| 248 | echo "</pre>"; |
---|
| 249 | include_once('test-xmlschema.php'); |
---|
| 250 | ?> |
---|