Update to upstream version 4.94
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / tests / test-datadict.php
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 ?>