4
V4.20 22 Feb 2004 (c) 2000-2004 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.
9
Set tabs to 4 for best viewing.
13
error_reporting(E_ALL);
14
include_once('../adodb.inc.php');
16
foreach(array('sybase','mysql','access','oci8','postgres','odbc_mssql','odbc','sybase','firebird','informix','db2') as $dbType) {
17
echo "<h3>$dbType</h3><p>";
18
$db = NewADOConnection($dbType);
19
$dict = NewDataDictionary($db);
24
$opts = array('REPLACE','mysql' => 'TYPE=ISAM', 'oci8' => 'TABLESPACE USERS');
30
array('name' => 'firstname', 'type' => 'varchar','size' => 30,
33
array('lastname','varchar',28,
34
'DEFAULT'=>'Chen','key'),
36
array('averylonglongfieldname','X',1024,
37
'NOTNULL','default' => 'test'),
39
array('price','N','7.2',
40
'NOTNULL','default' => '0.00'),
50
FIRSTNAME VARCHAR(30) DEFAULT 'Joan',
51
LASTNAME VARCHAR(28) DEFAULT 'Chen' key,
52
averylonglongfieldname X(1024) DEFAULT 'test',
53
price N(7.2) DEFAULT '0.00',
59
$sqla = $dict->CreateDatabase('KUTU',array('postgres'=>"LOCATION='/u01/postdata'"));
60
$dict->SetSchema('KUTU');
62
$sqli = ($dict->CreateTableSQL('testtable',$flds, $opts));
63
$sqla =& array_merge($sqla,$sqli);
65
$sqli = $dict->CreateIndexSQL('idx','testtable','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);
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);
78
printsqla($dbType,$sqla);
80
if (file_exists('d:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php'))
81
if ($dbType == 'mysql') {
82
$db->Connect('localhost', "root", "", "test");
84
$sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
85
if ($sqla2) printsqla($dbType,$sqla2);
87
if ($dbType == 'postgres') {
88
$db->Connect('localhost', "tester", "test", "test");
90
$sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
91
if ($sqla2) printsqla($dbType,$sqla2);
96
function printsqla($dbType,$sqla)
99
//print_r($dict->MetaTables());
100
foreach($sqla as $s) {
101
$s = htmlspecialchars($s);
103
if ($dbType == 'oci8') print "/\n";
114
CREATE DATABASE KUTU;
115
DROP TABLE KUTU.testtable;
116
CREATE TABLE KUTU.testtable (
117
id INTEGER NOT NULL AUTO_INCREMENT,
118
firstname VARCHAR(30) DEFAULT 'Joan',
119
lastname VARCHAR(28) NOT NULL DEFAULT 'Chen',
120
averylonglongfieldname LONGTEXT NOT NULL,
121
price NUMERIC(7,2) NOT NULL DEFAULT 0.00,
122
MYDATE DATE DEFAULT CURDATE(),
123
PRIMARY KEY (id, lastname)
125
CREATE FULLTEXT INDEX idx ON KUTU.testtable (firstname,lastname);
126
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
127
ALTER TABLE KUTU.testtable ADD height DOUBLE;
128
ALTER TABLE KUTU.testtable ADD weight DOUBLE;
129
ALTER TABLE KUTU.testtable MODIFY COLUMN height DOUBLE NOT NULL;
130
ALTER TABLE KUTU.testtable MODIFY COLUMN weight DOUBLE NOT NULL;
133
--------------------------------------------------------------------------------
137
CREATE USER KUTU IDENTIFIED BY tiger;
139
GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO KUTU;
141
DROP TABLE KUTU.testtable CASCADE CONSTRAINTS;
143
CREATE TABLE KUTU.testtable (
144
id NUMBER(16) NOT NULL,
145
firstname VARCHAR(30) DEFAULT 'Joan',
146
lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL,
147
averylonglongfieldname CLOB NOT NULL,
148
price NUMBER(7,2) DEFAULT 0.00 NOT NULL,
149
MYDATE DATE DEFAULT TRUNC(SYSDATE),
150
PRIMARY KEY (id, lastname)
153
DROP SEQUENCE KUTU.SEQ_testtable;
155
CREATE SEQUENCE KUTU.SEQ_testtable;
157
CREATE OR REPLACE TRIGGER KUTU.TRIG_SEQ_testtable BEFORE insert ON KUTU.testtable
160
select KUTU.SEQ_testtable.nextval into :new.id from dual;
163
CREATE BITMAP INDEX idx ON KUTU.testtable (firstname,lastname);
165
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
167
ALTER TABLE testtable ADD (
171
ALTER TABLE testtable MODIFY(
172
height NUMBER NOT NULL,
173
weight NUMBER NOT NULL);
177
--------------------------------------------------------------------------------
180
AlterColumnSQL not supported for PostgreSQL
183
CREATE DATABASE KUTU LOCATION='/u01/postdata';
184
DROP TABLE KUTU.testtable;
185
CREATE TABLE KUTU.testtable (
187
firstname VARCHAR(30) DEFAULT 'Joan',
188
lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL,
189
averylonglongfieldname TEXT NOT NULL,
190
price NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
191
MYDATE DATE DEFAULT CURRENT_DATE,
192
PRIMARY KEY (id, lastname)
194
CREATE INDEX idx ON KUTU.testtable USING HASH (firstname,lastname);
195
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
196
ALTER TABLE KUTU.testtable ADD height FLOAT8;
197
ALTER TABLE KUTU.testtable ADD weight FLOAT8;
200
--------------------------------------------------------------------------------
204
CREATE DATABASE KUTU;
205
DROP TABLE KUTU.testtable;
206
CREATE TABLE KUTU.testtable (
207
id INT IDENTITY(1,1) NOT NULL,
208
firstname VARCHAR(30) DEFAULT 'Joan',
209
lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL,
210
averylonglongfieldname TEXT NOT NULL,
211
price NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
212
MYDATE DATETIME DEFAULT GetDate(),
213
PRIMARY KEY (id, lastname)
215
CREATE CLUSTERED INDEX idx ON KUTU.testtable (firstname,lastname);
216
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
217
ALTER TABLE KUTU.testtable ADD
220
ALTER TABLE KUTU.testtable ALTER COLUMN height REAL NOT NULL;
221
ALTER TABLE KUTU.testtable ALTER COLUMN weight REAL NOT NULL;
224
--------------------------------------------------------------------------------
227
echo "<h1>Test XML Schema</h1>";
228
$ff = file('xmlschema.xml');
230
foreach($ff as $xml) echo htmlspecialchars($xml);
232
include_once('test-xmlschema.php');
4
V4.51 29 July 2004 (c) 2000-2004 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.
9
Set tabs to 4 for best viewing.
13
error_reporting(E_ALL);
14
include_once('../adodb.inc.php');
16
foreach(array('sybase','mysqlt','access','oci8','postgres','odbc_mssql','odbc','sybase','firebird','informix','db2') as $dbType) {
17
echo "<h3>$dbType</h3><p>";
18
$db = NewADOConnection($dbType);
19
$dict = NewDataDictionary($db);
24
$opts = array('REPLACE','mysql' => 'TYPE=INNODB', 'oci8' => 'TABLESPACE USERS');
30
array('name' => 'firstname', 'type' => 'varchar','size' => 30,
33
array('lastname','varchar',28,
34
'DEFAULT'=>'Chen','key'),
36
array('averylonglongfieldname','X',1024,
37
'NOTNULL','default' => 'test'),
39
array('price','N','7.2',
40
'NOTNULL','default' => '0.00'),
50
FIRSTNAME VARCHAR(30) DEFAULT 'Joan',
51
LASTNAME VARCHAR(28) DEFAULT 'Chen' key,
52
averylonglongfieldname X(1024) DEFAULT 'test',
53
price N(7.2) DEFAULT '0.00',
59
$sqla = $dict->CreateDatabase('KUTU',array('postgres'=>"LOCATION='/u01/postdata'"));
60
$dict->SetSchema('KUTU');
62
$sqli = ($dict->CreateTableSQL('testtable',$flds, $opts));
63
$sqla =& array_merge($sqla,$sqli);
65
$sqli = $dict->CreateIndexSQL('idx','testtable','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);
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);
78
printsqla($dbType,$sqla);
80
if (file_exists('d:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php'))
81
if ($dbType == 'mysql') {
82
$db->Connect('localhost', "root", "", "test");
84
$sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
85
if ($sqla2) printsqla($dbType,$sqla2);
87
if ($dbType == 'postgres') {
88
$db->Connect('localhost', "tester", "test", "test");
90
$sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
91
if ($sqla2) printsqla($dbType,$sqla2);
96
function printsqla($dbType,$sqla)
99
//print_r($dict->MetaTables());
100
foreach($sqla as $s) {
101
$s = htmlspecialchars($s);
103
if ($dbType == 'oci8') print "/\n";
114
CREATE DATABASE KUTU;
115
DROP TABLE KUTU.testtable;
116
CREATE TABLE KUTU.testtable (
117
id INTEGER NOT NULL AUTO_INCREMENT,
118
firstname VARCHAR(30) DEFAULT 'Joan',
119
lastname VARCHAR(28) NOT NULL DEFAULT 'Chen',
120
averylonglongfieldname LONGTEXT NOT NULL,
121
price NUMERIC(7,2) NOT NULL DEFAULT 0.00,
122
MYDATE DATE DEFAULT CURDATE(),
123
PRIMARY KEY (id, lastname)
125
CREATE FULLTEXT INDEX idx ON KUTU.testtable (firstname,lastname);
126
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
127
ALTER TABLE KUTU.testtable ADD height DOUBLE;
128
ALTER TABLE KUTU.testtable ADD weight DOUBLE;
129
ALTER TABLE KUTU.testtable MODIFY COLUMN height DOUBLE NOT NULL;
130
ALTER TABLE KUTU.testtable MODIFY COLUMN weight DOUBLE NOT NULL;
133
--------------------------------------------------------------------------------
137
CREATE USER KUTU IDENTIFIED BY tiger;
139
GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO KUTU;
141
DROP TABLE KUTU.testtable CASCADE CONSTRAINTS;
143
CREATE TABLE KUTU.testtable (
144
id NUMBER(16) NOT NULL,
145
firstname VARCHAR(30) DEFAULT 'Joan',
146
lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL,
147
averylonglongfieldname CLOB NOT NULL,
148
price NUMBER(7,2) DEFAULT 0.00 NOT NULL,
149
MYDATE DATE DEFAULT TRUNC(SYSDATE),
150
PRIMARY KEY (id, lastname)
153
DROP SEQUENCE KUTU.SEQ_testtable;
155
CREATE SEQUENCE KUTU.SEQ_testtable;
157
CREATE OR REPLACE TRIGGER KUTU.TRIG_SEQ_testtable BEFORE insert ON KUTU.testtable
160
select KUTU.SEQ_testtable.nextval into :new.id from dual;
163
CREATE BITMAP INDEX idx ON KUTU.testtable (firstname,lastname);
165
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
167
ALTER TABLE testtable ADD (
171
ALTER TABLE testtable MODIFY(
172
height NUMBER NOT NULL,
173
weight NUMBER NOT NULL);
177
--------------------------------------------------------------------------------
180
AlterColumnSQL not supported for PostgreSQL
183
CREATE DATABASE KUTU LOCATION='/u01/postdata';
184
DROP TABLE KUTU.testtable;
185
CREATE TABLE KUTU.testtable (
187
firstname VARCHAR(30) DEFAULT 'Joan',
188
lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL,
189
averylonglongfieldname TEXT NOT NULL,
190
price NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
191
MYDATE DATE DEFAULT CURRENT_DATE,
192
PRIMARY KEY (id, lastname)
194
CREATE INDEX idx ON KUTU.testtable USING HASH (firstname,lastname);
195
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
196
ALTER TABLE KUTU.testtable ADD height FLOAT8;
197
ALTER TABLE KUTU.testtable ADD weight FLOAT8;
200
--------------------------------------------------------------------------------
204
CREATE DATABASE KUTU;
205
DROP TABLE KUTU.testtable;
206
CREATE TABLE KUTU.testtable (
207
id INT IDENTITY(1,1) NOT NULL,
208
firstname VARCHAR(30) DEFAULT 'Joan',
209
lastname VARCHAR(28) DEFAULT 'Chen' NOT NULL,
210
averylonglongfieldname TEXT NOT NULL,
211
price NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
212
MYDATE DATETIME DEFAULT GetDate(),
213
PRIMARY KEY (id, lastname)
215
CREATE CLUSTERED INDEX idx ON KUTU.testtable (firstname,lastname);
216
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
217
ALTER TABLE KUTU.testtable ADD
220
ALTER TABLE KUTU.testtable ALTER COLUMN height REAL NOT NULL;
221
ALTER TABLE KUTU.testtable ALTER COLUMN weight REAL NOT NULL;
224
--------------------------------------------------------------------------------
227
echo "<h1>Test XML Schema</h1>";
228
$ff = file('xmlschema.xml');
230
foreach($ff as $xml) echo htmlspecialchars($xml);
232
include_once('test-xmlschema.php');
b'\\ No newline at end of file'