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
Log Message:	Introducing new order in project
#	User	Rev	Content
1	kdziedzi	1.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			?>