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