library/adodb/tests/test-datadict.php

   1 <?php
   2 /*
   3
   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.
   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('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);
  20
  21         if (!$dict) continue;
  22         $dict->debug = 1;
  23
  24         $opts = array('REPLACE','mysql' => 'TYPE=ISAM', '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 == 'mysql') {
  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                 $db->Connect('localhost', "tester", "test", "test");
  89                 $dict->SetSchema('');
  90                 $sqla2 = $dict->ChangeTableSQL('adoxyz',$flds);
  91                 if ($sqla2) printsqla($dbType,$sqla2);
  92         }
  93
  94 }
  95
  96 function printsqla($dbType,$sqla)
  97 {
  98         print "<pre>";
  99         //print_r($dict->MetaTables());
 100         foreach($sqla as $s) {
 101                 $s = htmlspecialchars($s);
 102                 print "$s;\n";
 103                 if ($dbType == 'oci8') print "/\n";
 104         }
 105         print "</pre><hr>";
 106 }
 107
 108 /***
 109
 110 Generated SQL:
 111
 112 mysql
 113
 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)
 124 )TYPE=ISAM;
 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;
 131
 132
 133 --------------------------------------------------------------------------------
 134
 135 oci8
 136
 137 CREATE USER KUTU IDENTIFIED BY tiger;
 138 /
 139 GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO KUTU;
 140 /
 141 DROP TABLE KUTU.testtable CASCADE CONSTRAINTS;
 142 /
 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)
 151 )TABLESPACE USERS;
 152 /
 153 DROP SEQUENCE KUTU.SEQ_testtable;
 154 /
 155 CREATE SEQUENCE KUTU.SEQ_testtable;
 156 /
 157 CREATE OR REPLACE TRIGGER KUTU.TRIG_SEQ_testtable BEFORE insert ON KUTU.testtable
 158                 FOR EACH ROW
 159                 BEGIN
 160                   select KUTU.SEQ_testtable.nextval into :new.id from dual;
 161                 END;
 162 /
 163 CREATE BITMAP INDEX idx ON KUTU.testtable (firstname,lastname);
 164 /
 165 CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
 166 /
 167 ALTER TABLE testtable ADD (
 168  height           NUMBER,
 169  weight           NUMBER);
 170 /
 171 ALTER TABLE testtable MODIFY(
 172  height           NUMBER NOT NULL,
 173  weight           NUMBER NOT NULL);
 174 /
 175
 176
 177 --------------------------------------------------------------------------------
 178
 179 postgres
 180 AlterColumnSQL not supported for PostgreSQL
 181
 182
 183 CREATE DATABASE KUTU LOCATION='/u01/postdata';
 184 DROP TABLE KUTU.testtable;
 185 CREATE TABLE KUTU.testtable (
 186 id               SERIAL,
 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)
 193 );
 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;
 198
 199
 200 --------------------------------------------------------------------------------
 201
 202 odbc_mssql
 203
 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)
 214 );
 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
 218  height           REAL,
 219  weight           REAL;
 220 ALTER TABLE KUTU.testtable  ALTER COLUMN height           REAL NOT NULL;
 221 ALTER TABLE KUTU.testtable  ALTER COLUMN weight           REAL NOT NULL;
 222
 223
 224 --------------------------------------------------------------------------------
 225 */
 226
 227 echo "<h1>Test XML Schema</h1>";
 228 $ff = file('xmlschema.xml');
 229 echo "<pre>";
 230 foreach($ff as $xml) echo htmlspecialchars($xml);
 231 echo "</pre>";
 232 include_once('test-xmlschema.php');
 233 ?>