adodb/tests/test-datadict.php

<?php
/*

  V4.81 3 May 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
  Released under both BSD license and Lesser GPL library license. 
  Whenever there is any discrepancy between the two licenses, 
  the BSD license will take precedence.
        
  Set tabs to 4 for best viewing.

*/

error_reporting(E_ALL);
include_once('../adodb.inc.php');

foreach(array('sapdb','sybase','mysql','access','oci8po','postgres','odbc_mssql','odbc','db2','firebird','informix') as $dbType) {
        echo "<h3>$dbType</h3><p>";
        $db = NewADOConnection($dbType);
        $dict = NewDataDictionary($db);

        if (!$dict) continue;
        $dict->debug = 1;
        
        $opts = array('REPLACE','mysql' => 'ENGINE=INNODB', 'oci8' => 'TABLESPACE USERS');
        
/*      $flds = array(
                array('id',     'I',                                                            
                                                        'AUTO','KEY'),
                                                        
                array('name' => 'firstname', 'type' => 'varchar','size' => 30,
                                                        'DEFAULT'=>'Joan'),
                                                        
                array('lastname','varchar',28,
                                                        'DEFAULT'=>'Chen','key'),
                                                        
                array('averylonglongfieldname','X',1024,
                                                        'NOTNULL','default' => 'test'),
                                                        
                array('price','N','7.2',
                                                        'NOTNULL','default' => '0.00'),
                                                        
                array('MYDATE', 'D', 
                                                        'DEFDATE'),
                array('TS','T',
                                                        'DEFTIMESTAMP')
        );*/
        
        $flds = "
ID            I           AUTO KEY,
FIRSTNAME     VARCHAR(30) DEFAULT 'Joan',
LASTNAME      VARCHAR(28) DEFAULT 'Chen' key,
averylonglongfieldname X(1024) DEFAULT 'test',
price         N(7.2)  DEFAULT '0.00',
MYDATE        D      DEFDATE,
BIGFELLOW     X      NOTNULL,
TS            T      DEFTIMESTAMP";


        $sqla = $dict->CreateDatabase('KUTU',array('postgres'=>"LOCATION='/u01/postdata'"));
        $dict->SetSchema('KUTU');
        
        $sqli = ($dict->CreateTableSQL('testtable',$flds, $opts));
        $sqla = array_merge($sqla,$sqli);
        
        $sqli = $dict->CreateIndexSQL('idx','testtable','firstname,lastname',array('BITMAP','FULLTEXT','CLUSTERED','HASH'));
        $sqla = array_merge($sqla,$sqli);
        $sqli = $dict->CreateIndexSQL('idx2','testtable','price,lastname');//,array('BITMAP','FULLTEXT','CLUSTERED'));
        $sqla = array_merge($sqla,$sqli);
        
        $addflds = array(array('height', 'F'),array('weight','F'));
        $sqli = $dict->AddColumnSQL('testtable',$addflds);
        $sqla = array_merge($sqla,$sqli);
        $addflds = array(array('height', 'F','NOTNULL'),array('weight','F','NOTNULL'));
        $sqli = $dict->AlterColumnSQL('testtable',$addflds);
        $sqla = array_merge($sqla,$sqli);
        
        
        printsqla($dbType,$sqla);
        
        if (file_exists('d:\inetpub\wwwroot\php\phplens\adodb\adodb.inc.php'))
        if ($dbType == 'mysqlt') {
                $db->Connect('localhost', "root", "", "test");
                $dict->SetSchema('');
                $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
                if ($sqla2) printsqla($dbType,$sqla2);
        }
        if ($dbType == 'postgres') {
                if (@$db->Connect('localhost', "tester", "test", "test"));
                $dict->SetSchema('');
                $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
                if ($sqla2) printsqla($dbType,$sqla2);
        }
        
        if ($dbType == 'odbc_mssql') {
                $dsn = $dsn = "PROVIDER=MSDASQL;Driver={SQL Server};Server=localhost;Database=northwind;";
                if (@$db->Connect($dsn, "sa", "natsoft", "test"));
                $dict->SetSchema('');
                $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
                if ($sqla2) printsqla($dbType,$sqla2);
        }
        
        
        adodb_pr($dict->databaseType);
        printsqla($dbType, $dict->DropColumnSQL('table',array('my col','`col2_with_Quotes`','A_col3','col3(10)')));
        printsqla($dbType, $dict->ChangeTableSQL('adoxyz','LASTNAME varchar(32)'));
        
}

function printsqla($dbType,$sqla)
{
        print "<pre>";
        //print_r($dict->MetaTables());
        foreach($sqla as $s) {
                $s = htmlspecialchars($s);
                print "$s;\n";
                if ($dbType == 'oci8') print "/\n";
        }
        print "</pre><hr />";
}

/***

Generated SQL:

mysql

CREATE DATABASE KUTU;
DROP TABLE KUTU.testtable;
CREATE TABLE KUTU.testtable (
id               INTEGER NOT NULL AUTO_INCREMENT,
firstname        VARCHAR(30) DEFAULT 'Joan',
lastname         VARCHAR(28) NOT NULL DEFAULT 'Chen',
averylonglongfieldname LONGTEXT NOT NULL,
price            NUMERIC(7,2) NOT NULL DEFAULT 0.00,
MYDATE           DATE DEFAULT CURDATE(),
                 PRIMARY KEY (id, lastname)
)TYPE=ISAM;
CREATE FULLTEXT INDEX idx ON KUTU.testtable (firstname,lastname);
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
ALTER TABLE KUTU.testtable  ADD height           DOUBLE;
ALTER TABLE KUTU.testtable  ADD weight           DOUBLE;
ALTER TABLE KUTU.testtable  MODIFY COLUMN height           DOUBLE NOT NULL;
ALTER TABLE KUTU.testtable  MODIFY COLUMN weight           DOUBLE NOT NULL;


--------------------------------------------------------------------------------

oci8

CREATE USER KUTU IDENTIFIED BY tiger;
/
GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO KUTU;
/
DROP TABLE KUTU.testtable CASCADE CONSTRAINTS;
/
CREATE TABLE KUTU.testtable (
id               NUMBER(16) NOT NULL,
firstname        VARCHAR(30) DEFAULT 'Joan',
lastname         VARCHAR(28) DEFAULT 'Chen' NOT NULL,
averylonglongfieldname CLOB NOT NULL,
price            NUMBER(7,2) DEFAULT 0.00 NOT NULL,
MYDATE           DATE DEFAULT TRUNC(SYSDATE),
                 PRIMARY KEY (id, lastname)
)TABLESPACE USERS;
/
DROP SEQUENCE KUTU.SEQ_testtable;
/
CREATE SEQUENCE KUTU.SEQ_testtable;
/
CREATE OR REPLACE TRIGGER KUTU.TRIG_SEQ_testtable BEFORE insert ON KUTU.testtable 
                FOR EACH ROW
                BEGIN
                  select KUTU.SEQ_testtable.nextval into :new.id from dual;
                END;
/
CREATE BITMAP INDEX idx ON KUTU.testtable (firstname,lastname);
/
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
/
ALTER TABLE testtable ADD (
 height           NUMBER,
 weight           NUMBER);
/
ALTER TABLE testtable MODIFY(
 height           NUMBER NOT NULL,
 weight           NUMBER NOT NULL);
/


--------------------------------------------------------------------------------

postgres
AlterColumnSQL not supported for PostgreSQL


CREATE DATABASE KUTU LOCATION='/u01/postdata';
DROP TABLE KUTU.testtable;
CREATE TABLE KUTU.testtable (
id               SERIAL,
firstname        VARCHAR(30) DEFAULT 'Joan',
lastname         VARCHAR(28) DEFAULT 'Chen' NOT NULL,
averylonglongfieldname TEXT NOT NULL,
price            NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
MYDATE           DATE DEFAULT CURRENT_DATE,
                 PRIMARY KEY (id, lastname)
);
CREATE INDEX idx ON KUTU.testtable USING HASH (firstname,lastname);
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
ALTER TABLE KUTU.testtable  ADD height           FLOAT8;
ALTER TABLE KUTU.testtable  ADD weight           FLOAT8;


--------------------------------------------------------------------------------

odbc_mssql

CREATE DATABASE KUTU;
DROP TABLE KUTU.testtable;
CREATE TABLE KUTU.testtable (
id               INT IDENTITY(1,1) NOT NULL,
firstname        VARCHAR(30) DEFAULT 'Joan',
lastname         VARCHAR(28) DEFAULT 'Chen' NOT NULL,
averylonglongfieldname TEXT NOT NULL,
price            NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
MYDATE           DATETIME DEFAULT GetDate(),
                 PRIMARY KEY (id, lastname)
);
CREATE CLUSTERED INDEX idx ON KUTU.testtable (firstname,lastname);
CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
ALTER TABLE KUTU.testtable  ADD
 height           REAL,
 weight           REAL;
ALTER TABLE KUTU.testtable  ALTER COLUMN height           REAL NOT NULL;
ALTER TABLE KUTU.testtable  ALTER COLUMN weight           REAL NOT NULL;


--------------------------------------------------------------------------------
*/


echo "<h1>Test XML Schema</h1>";
$ff = file('xmlschema.xml');
echo "<pre>";
foreach($ff as $xml) echo htmlspecialchars($xml);
echo "</pre>";
include_once('test-xmlschema.php');
?>
Revision:	1.1
Committed:	Fri Jun 29 07:49:42 2007 UTC (17 years, 10 months ago) by kdziedzi
Branch:	MAIN
CVS Tags:	V01-01-02, V01-01-01, V1_01_00, V01-01-00, V1_00_01, HEAD
Error occurred while calculating annotation data.
Log Message:	Introducing new order in project
#	Content
1	<?php
2	/*
3
4	V4.81 3 May 2006 (c) 2000-2006 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',
51	LASTNAME VARCHAR(28) DEFAULT 'Chen' key,
52	averylonglongfieldname X(1024) DEFAULT 'test',
53	price N(7.2) DEFAULT '0.00',
54	MYDATE D DEFDATE,
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','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	?>