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?>