library/adodb/tests/test-datadict.php

   1 <?php
   2 /*
   3
   4   V4.81 3 May 2006  (c) 2000-2011 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
  13 error_reporting(E_ALL);
  14 include_once('../adodb.inc.php');
  15
  16 foreach(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 = "
  49 ID            I           AUTO KEY,
  50 FIRSTNAME     VARCHAR(30) DEFAULT 'Joan' INDEX idx_name,
  51 LASTNAME      VARCHAR(28) DEFAULT 'Chen' key INDEX idx_name INDEX idx_lastname,
  52 averylonglongfieldname X(1024) DEFAULT 'test',
  53 price         N(7.2)  DEFAULT '0.00',
  54 MYDATE        D      DEFDATE INDEX idx_date,
  55 BIGFELLOW     X      NOTNULL,
  56 TS_SECS            T      DEFTIMESTAMP,
  57 TS_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
 112 function 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
 126 Generated SQL:
 127
 128 mysql
 129
 130 CREATE DATABASE KUTU;
 131 DROP TABLE KUTU.testtable;
 132 CREATE TABLE KUTU.testtable (
 133 id               INTEGER NOT NULL AUTO_INCREMENT,
 134 firstname        VARCHAR(30) DEFAULT 'Joan',
 135 lastname         VARCHAR(28) NOT NULL DEFAULT 'Chen',
 136 averylonglongfieldname LONGTEXT NOT NULL,
 137 price            NUMERIC(7,2) NOT NULL DEFAULT 0.00,
 138 MYDATE           DATE DEFAULT CURDATE(),
 139                  PRIMARY KEY (id, lastname)
 140 )TYPE=ISAM;
 141 CREATE FULLTEXT INDEX idx ON KUTU.testtable (firstname,lastname);
 142 CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
 143 ALTER TABLE KUTU.testtable  ADD height           DOUBLE;
 144 ALTER TABLE KUTU.testtable  ADD weight           DOUBLE;
 145 ALTER TABLE KUTU.testtable  MODIFY COLUMN height           DOUBLE NOT NULL;
 146 ALTER TABLE KUTU.testtable  MODIFY COLUMN weight           DOUBLE NOT NULL;
 147
 148
 149 --------------------------------------------------------------------------------
 150
 151 oci8
 152
 153 CREATE USER KUTU IDENTIFIED BY tiger;
 154 /
 155 GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO KUTU;
 156 /
 157 DROP TABLE KUTU.testtable CASCADE CONSTRAINTS;
 158 /
 159 CREATE TABLE KUTU.testtable (
 160 id               NUMBER(16) NOT NULL,
 161 firstname        VARCHAR(30) DEFAULT 'Joan',
 162 lastname         VARCHAR(28) DEFAULT 'Chen' NOT NULL,
 163 averylonglongfieldname CLOB NOT NULL,
 164 price            NUMBER(7,2) DEFAULT 0.00 NOT NULL,
 165 MYDATE           DATE DEFAULT TRUNC(SYSDATE),
 166                  PRIMARY KEY (id, lastname)
 167 )TABLESPACE USERS;
 168 /
 169 DROP SEQUENCE KUTU.SEQ_testtable;
 170 /
 171 CREATE SEQUENCE KUTU.SEQ_testtable;
 172 /
 173 CREATE 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 /
 179 CREATE BITMAP INDEX idx ON KUTU.testtable (firstname,lastname);
 180 /
 181 CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
 182 /
 183 ALTER TABLE testtable ADD (
 184  height           NUMBER,
 185  weight           NUMBER);
 186 /
 187 ALTER TABLE testtable MODIFY(
 188  height           NUMBER NOT NULL,
 189  weight           NUMBER NOT NULL);
 190 /
 191
 192
 193 --------------------------------------------------------------------------------
 194
 195 postgres
 196 AlterColumnSQL not supported for PostgreSQL
 197
 198
 199 CREATE DATABASE KUTU LOCATION='/u01/postdata';
 200 DROP TABLE KUTU.testtable;
 201 CREATE TABLE KUTU.testtable (
 202 id               SERIAL,
 203 firstname        VARCHAR(30) DEFAULT 'Joan',
 204 lastname         VARCHAR(28) DEFAULT 'Chen' NOT NULL,
 205 averylonglongfieldname TEXT NOT NULL,
 206 price            NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
 207 MYDATE           DATE DEFAULT CURRENT_DATE,
 208                  PRIMARY KEY (id, lastname)
 209 );
 210 CREATE INDEX idx ON KUTU.testtable USING HASH (firstname,lastname);
 211 CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
 212 ALTER TABLE KUTU.testtable  ADD height           FLOAT8;
 213 ALTER TABLE KUTU.testtable  ADD weight           FLOAT8;
 214
 215
 216 --------------------------------------------------------------------------------
 217
 218 odbc_mssql
 219
 220 CREATE DATABASE KUTU;
 221 DROP TABLE KUTU.testtable;
 222 CREATE TABLE KUTU.testtable (
 223 id               INT IDENTITY(1,1) NOT NULL,
 224 firstname        VARCHAR(30) DEFAULT 'Joan',
 225 lastname         VARCHAR(28) DEFAULT 'Chen' NOT NULL,
 226 averylonglongfieldname TEXT NOT NULL,
 227 price            NUMERIC(7,2) DEFAULT 0.00 NOT NULL,
 228 MYDATE           DATETIME DEFAULT GetDate(),
 229                  PRIMARY KEY (id, lastname)
 230 );
 231 CREATE CLUSTERED INDEX idx ON KUTU.testtable (firstname,lastname);
 232 CREATE INDEX idx2 ON KUTU.testtable (price,lastname);
 233 ALTER TABLE KUTU.testtable  ADD
 234  height           REAL,
 235  weight           REAL;
 236 ALTER TABLE KUTU.testtable  ALTER COLUMN height           REAL NOT NULL;
 237 ALTER TABLE KUTU.testtable  ALTER COLUMN weight           REAL NOT NULL;
 238
 239
 240 --------------------------------------------------------------------------------
 241 */
 242
 243
 244 echo "<h1>Test XML Schema</h1>";
 245 $ff = file('xmlschema.xml');
 246 echo "<pre>";
 247 foreach($ff as $xml) echo htmlspecialchars($xml);
 248 echo "</pre>";
 249 include_once('test-xmlschema.php');
 250 ?>