library/adodb/adodb-datadict.inc.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         DOCUMENTATION:
  12
  13                 See adodb/tests/test-datadict.php for docs and examples.
  14 */
  15
  16 /*
  17         Test script for parser
  18 */
  19 function Lens_ParseTest()
  20 {
  21 $str = "`zcol ACOL` NUMBER(32,2) DEFAULT 'The \"cow\" (and Jim''s dog) jumps over the moon' PRIMARY, INTI INT AUTO DEFAULT 0";
  22 print "<p>$str</p>";
  23 $a= Lens_ParseArgs($str);
  24 print "<pre>";
  25 print_r($a);
  26 print "</pre>";
  27 }
  28
  29 if (!function_exists('ctype_alnum')) {
  30         function ctype_alnum($text) {
  31                 return preg_match('/^[a-z0-9]*$/i', $text);
  32         }
  33 }
  34
  35 //Lens_ParseTest();
  36
  37 /**
  38         Parse arguments, treat "text" (text) and 'text' as quotation marks.
  39         To escape, use "" or '' or ))
  40
  41         Will read in "abc def" sans quotes, as: abc def
  42         Same with 'abc def'.
  43         However if `abc def`, then will read in as `abc def`
  44
  45         @param endstmtchar    Character that indicates end of statement
  46         @param tokenchars     Include the following characters in tokens apart from A-Z and 0-9
  47         @returns 2 dimensional array containing parsed tokens.
  48 */
  49 function Lens_ParseArgs($args,$endstmtchar=',',$tokenchars='_.-')
  50 {
  51         $pos = 0;
  52         $intoken = false;
  53         $stmtno = 0;
  54         $endquote = false;
  55         $tokens = array();
  56         $tokens[$stmtno] = array();
  57         $max = strlen($args);
  58         $quoted = false;
  59
  60         while ($pos < $max) {
  61                 $ch = substr($args,$pos,1);
  62                 switch($ch) {
  63                 case ' ':
  64                 case "\t":
  65                 case "\n":
  66                 case "\r":
  67                         if (!$quoted) {
  68                                 if ($intoken) {
  69                                         $intoken = false;
  70                                         $tokens[$stmtno][] = implode('',$tokarr);
  71                                 }
  72                                 break;
  73                         }
  74
  75                         $tokarr[] = $ch;
  76                         break;
  77
  78                 case '`':
  79                         if ($intoken) $tokarr[] = $ch;
  80                 case '(':
  81                 case ')':
  82                 case '"':
  83                 case "'":
  84
  85                         if ($intoken) {
  86                                 if (empty($endquote)) {
  87                                         $tokens[$stmtno][] = implode('',$tokarr);
  88                                         if ($ch == '(') $endquote = ')';
  89                                         else $endquote = $ch;
  90                                         $quoted = true;
  91                                         $intoken = true;
  92                                         $tokarr = array();
  93                                 } else if ($endquote == $ch) {
  94                                         $ch2 = substr($args,$pos+1,1);
  95                                         if ($ch2 == $endquote) {
  96                                                 $pos += 1;
  97                                                 $tokarr[] = $ch2;
  98                                         } else {
  99                                                 $quoted = false;
 100                                                 $intoken = false;
 101                                                 $tokens[$stmtno][] = implode('',$tokarr);
 102                                                 $endquote = '';
 103                                         }
 104                                 } else
 105                                         $tokarr[] = $ch;
 106
 107                         }else {
 108
 109                                 if ($ch == '(') $endquote = ')';
 110                                 else $endquote = $ch;
 111                                 $quoted = true;
 112                                 $intoken = true;
 113                                 $tokarr = array();
 114                                 if ($ch == '`') $tokarr[] = '`';
 115                         }
 116                         break;
 117
 118                 default:
 119
 120                         if (!$intoken) {
 121                                 if ($ch == $endstmtchar) {
 122                                         $stmtno += 1;
 123                                         $tokens[$stmtno] = array();
 124                                         break;
 125                                 }
 126
 127                                 $intoken = true;
 128                                 $quoted = false;
 129                                 $endquote = false;
 130                                 $tokarr = array();
 131
 132                         }
 133
 134                         if ($quoted) $tokarr[] = $ch;
 135                         else if (ctype_alnum($ch) || strpos($tokenchars,$ch) !== false) $tokarr[] = $ch;
 136                         else {
 137                                 if ($ch == $endstmtchar) {
 138                                         $tokens[$stmtno][] = implode('',$tokarr);
 139                                         $stmtno += 1;
 140                                         $tokens[$stmtno] = array();
 141                                         $intoken = false;
 142                                         $tokarr = array();
 143                                         break;
 144                                 }
 145                                 $tokens[$stmtno][] = implode('',$tokarr);
 146                                 $tokens[$stmtno][] = $ch;
 147                                 $intoken = false;
 148                         }
 149                 }
 150                 $pos += 1;
 151         }
 152
 153         return $tokens;
 154 }
 155
 156
 157 class ADODB_DataDict {
 158         var $connection;
 159         var $debug = false;
 160         var $dropTable = 'DROP TABLE %s';
 161         var $dropIndex = 'DROP INDEX %s';
 162         var $addCol = ' ADD';
 163         var $alterCol = ' ALTER COLUMN';
 164         var $dropCol = ' DROP COLUMN';
 165         var $nameRegex = '\w';
 166         var $schema = false;
 167         var $serverInfo = array();
 168         var $autoIncrement = false;
 169         var $dataProvider;
 170         var $blobSize = 100;    /// any varchar/char field this size or greater is treated as a blob
 171                                                         /// in other words, we use a text area for editting.
 172
 173         function GetCommentSQL($table,$col)
 174         {
 175                 return false;
 176         }
 177
 178         function SetCommentSQL($table,$col,$cmt)
 179         {
 180                 return false;
 181         }
 182
 183         function &MetaTables()
 184         {
 185                 return $this->connection->MetaTables();
 186         }
 187
 188         function &MetaColumns($tab, $upper=true, $schema=false)
 189         {
 190                 return $this->connection->MetaColumns($this->TableName($tab), $upper, $schema);
 191         }
 192
 193         function &MetaPrimaryKeys($tab,$owner=false,$intkey=false)
 194         {
 195                 return $this->connection->MetaPrimaryKeys($this->TableName($tab), $owner, $intkey);
 196         }
 197
 198         function &MetaIndexes($table, $primary = false, $owner = false)
 199         {
 200                 return $this->connection->MetaIndexes($this->TableName($table), $primary, $owner);
 201         }
 202
 203         function MetaType($t,$len=-1,$fieldobj=false)
 204         {
 205                 return ADORecordSet::MetaType($t,$len,$fieldobj);
 206         }
 207
 208         function NameQuote($name = NULL)
 209         {
 210                 if (!is_string($name)) {
 211                         return FALSE;
 212                 }
 213
 214                 $name = trim($name);
 215
 216                 if ( !is_object($this->connection) ) {
 217                         return $name;
 218                 }
 219
 220                 $quote = $this->connection->nameQuote;
 221
 222                 // if name is of the form `name`, quote it
 223                 if ( preg_match('/^`(.+)`$/', $name, $matches) ) {
 224                         return $quote . $matches[1] . $quote;
 225                 }
 226
 227                 // if name contains special characters, quote it
 228                 if ( !preg_match('/^[' . $this->nameRegex . ']+$/', $name) ) {
 229                         return $quote . $name . $quote;
 230                 }
 231
 232                 return $name;
 233         }
 234
 235         function TableName($name)
 236         {
 237                 if ( $this->schema ) {
 238                         return $this->NameQuote($this->schema) .'.'. $this->NameQuote($name);
 239                 }
 240                 return $this->NameQuote($name);
 241         }
 242
 243         // Executes the sql array returned by GetTableSQL and GetIndexSQL
 244         function ExecuteSQLArray($sql, $continueOnError = true)
 245         {
 246                 $rez = 2;
 247                 $conn = &$this->connection;
 248                 $saved = $conn->debug;
 249                 foreach($sql as $line) {
 250
 251                         if ($this->debug) $conn->debug = true;
 252                         $ok = $conn->Execute($line);
 253                         $conn->debug = $saved;
 254                         if (!$ok) {
 255                                 if ($this->debug) ADOConnection::outp($conn->ErrorMsg());
 256                                 if (!$continueOnError) return 0;
 257                                 $rez = 1;
 258                         }
 259                 }
 260                 return 2;
 261         }
 262
 263         /*
 264                 Returns the actual type given a character code.
 265
 266                 C:  varchar
 267                 X:  CLOB (character large object) or largest varchar size if CLOB is not supported
 268                 C2: Multibyte varchar
 269                 X2: Multibyte CLOB
 270
 271                 B:  BLOB (binary large object)
 272
 273                 D:  Date
 274                 T:  Date-time
 275                 L:  Integer field suitable for storing booleans (0 or 1)
 276                 I:  Integer
 277                 F:  Floating point number
 278                 N:  Numeric or decimal number
 279         */
 280
 281         function ActualType($meta)
 282         {
 283                 return $meta;
 284         }
 285
 286         function CreateDatabase($dbname,$options=false)
 287         {
 288                 $options = $this->_Options($options);
 289                 $sql = array();
 290
 291                 $s = 'CREATE DATABASE ' . $this->NameQuote($dbname);
 292                 if (isset($options[$this->upperName]))
 293                         $s .= ' '.$options[$this->upperName];
 294
 295                 $sql[] = $s;
 296                 return $sql;
 297         }
 298
 299         /*
 300          Generates the SQL to create index. Returns an array of sql strings.
 301         */
 302         function CreateIndexSQL($idxname, $tabname, $flds, $idxoptions = false)
 303         {
 304                 if (!is_array($flds)) {
 305                         $flds = explode(',',$flds);
 306                 }
 307
 308                 foreach($flds as $key => $fld) {
 309                         $flds[$key] = $this->NameQuote($fld);
 310                 }
 311
 312                 return $this->_IndexSQL($this->NameQuote($idxname), $this->TableName($tabname), $flds, $this->_Options($idxoptions));
 313         }
 314
 315         function DropIndexSQL ($idxname, $tabname = NULL)
 316         {
 317                 return array(sprintf($this->dropIndex, $this->NameQuote($idxname), $this->TableName($tabname)));
 318         }
 319
 320         function SetSchema($schema)
 321         {
 322                 $this->schema = $schema;
 323         }
 324
 325         function AddColumnSQL($tabname, $flds)
 326         {
 327                 $tabname = $this->TableName ($tabname);
 328                 $sql = array();
 329                 list($lines,$pkey) = $this->_GenFields($flds);
 330                 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
 331                 foreach($lines as $v) {
 332                         $sql[] = $alter . $v;
 333                 }
 334                 return $sql;
 335         }
 336
 337         function AlterColumnSQL($tabname, $flds)
 338         {
 339                 $tabname = $this->TableName ($tabname);
 340                 $sql = array();
 341                 list($lines,$pkey) = $this->_GenFields($flds);
 342                 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 343                 foreach($lines as $v) {
 344                         $sql[] = $alter . $v;
 345                 }
 346                 return $sql;
 347         }
 348
 349         function DropColumnSQL($tabname, $flds)
 350         {
 351                 $tabname = $this->TableName ($tabname);
 352                 if (!is_array($flds)) $flds = explode(',',$flds);
 353                 $sql = array();
 354                 $alter = 'ALTER TABLE ' . $tabname . $this->dropCol . ' ';
 355                 foreach($flds as $v) {
 356                         $sql[] = $alter . $this->NameQuote($v);
 357                 }
 358                 return $sql;
 359         }
 360
 361         function DropTableSQL($tabname)
 362         {
 363                 return array (sprintf($this->dropTable, $this->TableName($tabname)));
 364         }
 365
 366         /*
 367          Generate the SQL to create table. Returns an array of sql strings.
 368         */
 369         function CreateTableSQL($tabname, $flds, $tableoptions=false)
 370         {
 371                 if (!$tableoptions) $tableoptions = array();
 372
 373                 list($lines,$pkey) = $this->_GenFields($flds);
 374
 375                 $taboptions = $this->_Options($tableoptions);
 376                 $tabname = $this->TableName ($tabname);
 377                 $sql = $this->_TableSQL($tabname,$lines,$pkey,$taboptions);
 378
 379                 $tsql = $this->_Triggers($tabname,$taboptions);
 380                 foreach($tsql as $s) $sql[] = $s;
 381
 382                 return $sql;
 383         }
 384
 385         function _GenFields($flds)
 386         {
 387                 if (is_string($flds)) {
 388                         $padding = '     ';
 389                         $txt = $flds.$padding;
 390                         $flds = array();
 391                         $flds0 = Lens_ParseArgs($txt,',');
 392                         $hasparam = false;
 393                         foreach($flds0 as $f0) {
 394                                 $f1 = array();
 395                                 foreach($f0 as $token) {
 396                                         switch (strtoupper($token)) {
 397                                         case 'CONSTRAINT':
 398                                         case 'DEFAULT':
 399                                                 $hasparam = $token;
 400                                                 break;
 401                                         default:
 402                                                 if ($hasparam) $f1[$hasparam] = $token;
 403                                                 else $f1[] = $token;
 404                                                 $hasparam = false;
 405                                                 break;
 406                                         }
 407                                 }
 408                                 $flds[] = $f1;
 409
 410                         }
 411                 }
 412                 $this->autoIncrement = false;
 413                 $lines = array();
 414                 $pkey = array();
 415                 foreach($flds as $fld) {
 416                         $fld = _array_change_key_case($fld);
 417
 418                         $fname = false;
 419                         $fdefault = false;
 420                         $fautoinc = false;
 421                         $ftype = false;
 422                         $fsize = false;
 423                         $fprec = false;
 424                         $fprimary = false;
 425                         $fnoquote = false;
 426                         $fdefts = false;
 427                         $fdefdate = false;
 428                         $fconstraint = false;
 429                         $fnotnull = false;
 430                         $funsigned = false;
 431
 432                         //-----------------
 433                         // Parse attributes
 434                         foreach($fld as $attr => $v) {
 435                                 if ($attr == 2 && is_numeric($v)) $attr = 'SIZE';
 436                                 else if (is_numeric($attr) && $attr > 1 && !is_numeric($v)) $attr = strtoupper($v);
 437
 438                                 switch($attr) {
 439                                 case '0':
 440                                 case 'NAME':    $fname = $v; break;
 441                                 case '1':
 442                                 case 'TYPE':    $ty = $v; $ftype = $this->ActualType(strtoupper($v)); break;
 443
 444                                 case 'SIZE':
 445                                                                 $dotat = strpos($v,'.'); if ($dotat === false) $dotat = strpos($v,',');
 446                                                                 if ($dotat === false) $fsize = $v;
 447                                                                 else {
 448                                                                         $fsize = substr($v,0,$dotat);
 449                                                                         $fprec = substr($v,$dotat+1);
 450                                                                 }
 451                                                                 break;
 452                                 case 'UNSIGNED': $funsigned = true; break;
 453                                 case 'AUTOINCREMENT':
 454                                 case 'AUTO':    $fautoinc = true; $fnotnull = true; break;
 455                                 case 'KEY':
 456                                 case 'PRIMARY': $fprimary = $v; $fnotnull = true; break;
 457                                 case 'DEF':
 458                                 case 'DEFAULT': $fdefault = $v; break;
 459                                 case 'NOTNULL': $fnotnull = $v; break;
 460                                 case 'NOQUOTE': $fnoquote = $v; break;
 461                                 case 'DEFDATE': $fdefdate = $v; break;
 462                                 case 'DEFTIMESTAMP': $fdefts = $v; break;
 463                                 case 'CONSTRAINT': $fconstraint = $v; break;
 464                                 } //switch
 465                         } // foreach $fld
 466
 467                         //--------------------
 468                         // VALIDATE FIELD INFO
 469                         if (!strlen($fname)) {
 470                                 if ($this->debug) ADOConnection::outp("Undefined NAME");
 471                                 return false;
 472                         }
 473
 474                         $fid = strtoupper(preg_replace('/^`(.+)`$/', '$1', $fname));
 475                         $fname = $this->NameQuote($fname);
 476
 477                         if (!strlen($ftype)) {
 478                                 if ($this->debug) ADOConnection::outp("Undefined TYPE for field '$fname'");
 479                                 return false;
 480                         } else {
 481                                 $ftype = strtoupper($ftype);
 482                         }
 483
 484                         $ftype = $this->_GetSize($ftype, $ty, $fsize, $fprec);
 485
 486                         if ($ty == 'X' || $ty == 'X2' || $ty == 'B') $fnotnull = false; // some blob types do not accept nulls
 487
 488                         if ($fprimary) $pkey[] = $fname;
 489
 490                         // some databases do not allow blobs to have defaults
 491                         if ($ty == 'X') $fdefault = false;
 492
 493                         //--------------------
 494                         // CONSTRUCT FIELD SQL
 495                         if ($fdefts) {
 496                                 if (substr($this->connection->databaseType,0,5) == 'mysql') {
 497                                         $ftype = 'TIMESTAMP';
 498                                 } else {
 499                                         $fdefault = $this->connection->sysTimeStamp;
 500                                 }
 501                         } else if ($fdefdate) {
 502                                 if (substr($this->connection->databaseType,0,5) == 'mysql') {
 503                                         $ftype = 'TIMESTAMP';
 504                                 } else {
 505                                         $fdefault = $this->connection->sysDate;
 506                                 }
 507                         } else if (strlen($fdefault) && !$fnoquote)
 508                                 if ($ty == 'C' or $ty == 'X' or
 509                                         ( substr($fdefault,0,1) != "'" && !is_numeric($fdefault)))
 510                                         if (strlen($fdefault) != 1 && substr($fdefault,0,1) == ' ' && substr($fdefault,strlen($fdefault)-1) == ' ')
 511                                                 $fdefault = trim($fdefault);
 512                                         else if (strtolower($fdefault) != 'null')
 513                                                 $fdefault = $this->connection->qstr($fdefault);
 514                         $suffix = $this->_CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned);
 515
 516                         $fname = str_pad($fname,16);
 517                         $lines[$fid] = $fname.' '.$ftype.$suffix;
 518
 519                         if ($fautoinc) $this->autoIncrement = true;
 520                 } // foreach $flds
 521
 522                 return array($lines,$pkey);
 523         }
 524         /*
 525                  GENERATE THE SIZE PART OF THE DATATYPE
 526                         $ftype is the actual type
 527                         $ty is the type defined originally in the DDL
 528         */
 529         function _GetSize($ftype, $ty, $fsize, $fprec)
 530         {
 531                 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && strpos($ftype,'(') === false) {
 532                         $ftype .= "(".$fsize;
 533                         if (strlen($fprec)) $ftype .= ",".$fprec;
 534                         $ftype .= ')';
 535                 }
 536                 return $ftype;
 537         }
 538
 539
 540         // return string must begin with space
 541         function _CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint)
 542         {
 543                 $suffix = '';
 544                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 545                 if ($fnotnull) $suffix .= ' NOT NULL';
 546                 if ($fconstraint) $suffix .= ' '.$fconstraint;
 547                 return $suffix;
 548         }
 549
 550         function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 551         {
 552                 $sql = array();
 553
 554                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 555                         $sql[] = sprintf ($this->dropIndex, $idxname);
 556                         if ( isset($idxoptions['DROP']) )
 557                                 return $sql;
 558                 }
 559
 560                 if ( empty ($flds) ) {
 561                         return $sql;
 562                 }
 563
 564                 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 565
 566                 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
 567
 568                 if ( isset($idxoptions[$this->upperName]) )
 569                         $s .= $idxoptions[$this->upperName];
 570
 571                 if ( is_array($flds) )
 572                         $flds = implode(', ',$flds);
 573                 $s .= '(' . $flds . ')';
 574                 $sql[] = $s;
 575
 576                 return $sql;
 577         }
 578
 579         function _DropAutoIncrement($tabname)
 580         {
 581                 return false;
 582         }
 583
 584         function _TableSQL($tabname,$lines,$pkey,$tableoptions)
 585         {
 586                 $sql = array();
 587
 588                 if (isset($tableoptions['REPLACE']) || isset ($tableoptions['DROP'])) {
 589                         $sql[] = sprintf($this->dropTable,$tabname);
 590                         if ($this->autoIncrement) {
 591                                 $sInc = $this->_DropAutoIncrement($tabname);
 592                                 if ($sInc) $sql[] = $sInc;
 593                         }
 594                         if ( isset ($tableoptions['DROP']) ) {
 595                                 return $sql;
 596                         }
 597                 }
 598                 $s = "CREATE TABLE $tabname (\n";
 599                 $s .= implode(",\n", $lines);
 600                 if (sizeof($pkey)>0) {
 601                         $s .= ",\n                 PRIMARY KEY (";
 602                         $s .= implode(", ",$pkey).")";
 603                 }
 604                 if (isset($tableoptions['CONSTRAINTS']))
 605                         $s .= "\n".$tableoptions['CONSTRAINTS'];
 606
 607                 if (isset($tableoptions[$this->upperName.'_CONSTRAINTS']))
 608                         $s .= "\n".$tableoptions[$this->upperName.'_CONSTRAINTS'];
 609
 610                 $s .= "\n)";
 611                 if (isset($tableoptions[$this->upperName])) $s .= $tableoptions[$this->upperName];
 612                 $sql[] = $s;
 613
 614                 return $sql;
 615         }
 616
 617         /*
 618                 GENERATE TRIGGERS IF NEEDED
 619                 used when table has auto-incrementing field that is emulated using triggers
 620         */
 621         function _Triggers($tabname,$taboptions)
 622         {
 623                 return array();
 624         }
 625
 626         /*
 627                 Sanitize options, so that array elements with no keys are promoted to keys
 628         */
 629         function _Options($opts)
 630         {
 631                 if (!is_array($opts)) return array();
 632                 $newopts = array();
 633                 foreach($opts as $k => $v) {
 634                         if (is_numeric($k)) $newopts[strtoupper($v)] = $v;
 635                         else $newopts[strtoupper($k)] = $v;
 636                 }
 637                 return $newopts;
 638         }
 639
 640         /*
 641         "Florian Buzin [ easywe ]" <florian.buzin@easywe.de>
 642
 643         This function changes/adds new fields to your table. You don't
 644         have to know if the col is new or not. It will check on its own.
 645         */
 646         function ChangeTableSQL($tablename, $flds, $tableoptions = false)
 647         {
 648                 if ( !is_array($cols = &$this->MetaColumns($tablename)) ) {
 649                         return $this->CreateTableSQL($tablename, $flds, $tableoptions);
 650                 }
 651
 652                 list($lines,$pkey) = $this->_GenFields($flds);
 653                 $alter = 'ALTER TABLE ' . $this->TableName($tablename);
 654                 $sql = array();
 655
 656                 foreach ( $lines as $id => $v ) {
 657                         if ( isset($cols[$id]) && is_object($cols[$id]) ) {
 658                                 $sql[] = $alter . $this->alterCol . ' ' . $v;
 659                         } else {
 660                                 $sql[] = $alter . $this->addCol . ' ' . $v;
 661                         }
 662                 }
 663
 664                 return $sql;
 665         }
 666 } // class
 667 ?>