vendor/adodb/adodb-php/datadict/datadict-postgres.inc.php

   1 <?php
   2
   3 /**
   4   @version   v5.20.9  21-Dec-2016
   5   @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   6   @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   7   Released under both BSD license and Lesser GPL library license.
   8   Whenever there is any discrepancy between the two licenses,
   9   the BSD license will take precedence.
  10
  11   Set tabs to 4 for best viewing.
  12
  13 */
  14
  15 // security - hide paths
  16 if (!defined('ADODB_DIR')) die();
  17
  18 class ADODB2_postgres extends ADODB_DataDict {
  19
  20         var $databaseType = 'postgres';
  21         var $seqField = false;
  22         var $seqPrefix = 'SEQ_';
  23         var $addCol = ' ADD COLUMN';
  24         var $quote = '"';
  25         var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1
  26         var $dropTable = 'DROP TABLE %s CASCADE';
  27
  28         function MetaType($t,$len=-1,$fieldobj=false)
  29         {
  30                 if (is_object($t)) {
  31                         $fieldobj = $t;
  32                         $t = $fieldobj->type;
  33                         $len = $fieldobj->max_length;
  34                 }
  35                 $is_serial = is_object($fieldobj) && !empty($fieldobj->primary_key) && !empty($fieldobj->unique) &&
  36                         !empty($fieldobj->has_default) && substr($fieldobj->default_value,0,8) == 'nextval(';
  37
  38                 switch (strtoupper($t)) {
  39                         case 'INTERVAL':
  40                         case 'CHAR':
  41                         case 'CHARACTER':
  42                         case 'VARCHAR':
  43                         case 'NAME':
  44                         case 'BPCHAR':
  45                                 if ($len <= $this->blobSize) return 'C';
  46
  47                         case 'TEXT':
  48                                 return 'X';
  49
  50                         case 'IMAGE': // user defined type
  51                         case 'BLOB': // user defined type
  52                         case 'BIT':     // This is a bit string, not a single bit, so don't return 'L'
  53                         case 'VARBIT':
  54                         case 'BYTEA':
  55                                 return 'B';
  56
  57                         case 'BOOL':
  58                         case 'BOOLEAN':
  59                                 return 'L';
  60
  61                         case 'DATE':
  62                                 return 'D';
  63
  64                         case 'TIME':
  65                         case 'DATETIME':
  66                         case 'TIMESTAMP':
  67                         case 'TIMESTAMPTZ':
  68                                 return 'T';
  69
  70                         case 'INTEGER': return !$is_serial ? 'I' : 'R';
  71                         case 'SMALLINT':
  72                         case 'INT2': return !$is_serial ? 'I2' : 'R';
  73                         case 'INT4': return !$is_serial ? 'I4' : 'R';
  74                         case 'BIGINT':
  75                         case 'INT8': return !$is_serial ? 'I8' : 'R';
  76
  77                         case 'OID':
  78                         case 'SERIAL':
  79                                 return 'R';
  80
  81                         case 'FLOAT4':
  82                         case 'FLOAT8':
  83                         case 'DOUBLE PRECISION':
  84                         case 'REAL':
  85                                 return 'F';
  86
  87                          default:
  88                                 return 'N';
  89                 }
  90         }
  91
  92         function ActualType($meta)
  93         {
  94                 switch($meta) {
  95                 case 'C': return 'VARCHAR';
  96                 case 'XL':
  97                 case 'X': return 'TEXT';
  98
  99                 case 'C2': return 'VARCHAR';
 100                 case 'X2': return 'TEXT';
 101
 102                 case 'B': return 'BYTEA';
 103
 104                 case 'D': return 'DATE';
 105                 case 'TS':
 106                 case 'T': return 'TIMESTAMP';
 107
 108                 case 'L': return 'BOOLEAN';
 109                 case 'I': return 'INTEGER';
 110                 case 'I1': return 'SMALLINT';
 111                 case 'I2': return 'INT2';
 112                 case 'I4': return 'INT4';
 113                 case 'I8': return 'INT8';
 114
 115                 case 'F': return 'FLOAT8';
 116                 case 'N': return 'NUMERIC';
 117                 default:
 118                         return $meta;
 119                 }
 120         }
 121
 122         /**
 123          * Adding a new Column
 124          *
 125          * reimplementation of the default function as postgres does NOT allow to set the default in the same statement
 126          *
 127          * @param string $tabname table-name
 128          * @param string $flds column-names and types for the changed columns
 129          * @return array with SQL strings
 130          */
 131         function AddColumnSQL($tabname, $flds)
 132         {
 133                 $tabname = $this->TableName ($tabname);
 134                 $sql = array();
 135                 $not_null = false;
 136                 list($lines,$pkey) = $this->_GenFields($flds);
 137                 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
 138                 foreach($lines as $v) {
 139                         if (($not_null = preg_match('/NOT NULL/i',$v))) {
 140                                 $v = preg_replace('/NOT NULL/i','',$v);
 141                         }
 142                         if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
 143                                 list(,$colname,$default) = $matches;
 144                                 $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
 145                                 $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
 146                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
 147                         } else {
 148                                 $sql[] = $alter . $v;
 149                         }
 150                         if ($not_null) {
 151                                 list($colname) = explode(' ',$v);
 152                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
 153                         }
 154                 }
 155                 return $sql;
 156         }
 157
 158
 159         function DropIndexSQL ($idxname, $tabname = NULL)
 160         {
 161            return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname)));
 162         }
 163
 164         /**
 165          * Change the definition of one column
 166          *
 167          * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
 168          * to allow, recreating the table and copying the content over to the new table
 169          * @param string $tabname table-name
 170          * @param string $flds column-name and type for the changed column
 171          * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
 172          * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
 173          * @return array with SQL strings
 174          */
 175          /*
 176         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 177         {
 178                 if (!$tableflds) {
 179                         if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 180                         return array();
 181                 }
 182                 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 183         }*/
 184
 185         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 186         {
 187                 // Check if alter single column datatype available - works with 8.0+
 188                 $has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];
 189
 190                 if ($has_alter_column) {
 191                         $tabname = $this->TableName($tabname);
 192                         $sql = array();
 193                         list($lines,$pkey) = $this->_GenFields($flds);
 194                         $set_null = false;
 195                         foreach($lines as $v) {
 196                                 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 197                                 if ($not_null = preg_match('/NOT NULL/i',$v)) {
 198                                         $v = preg_replace('/NOT NULL/i','',$v);
 199                                 }
 200                                  // this next block doesn't work - there is no way that I can see to
 201                                  // explicitly ask a column to be null using $flds
 202                                 else if ($set_null = preg_match('/NULL/i',$v)) {
 203                                         // if they didn't specify not null, see if they explicitely asked for null
 204                                         // Lookbehind pattern covers the case 'fieldname NULL datatype DEFAULT NULL'
 205                                         // only the first NULL should be removed, not the one specifying
 206                                         // the default value
 207                                         $v = preg_replace('/(?<!DEFAULT)\sNULL/i','',$v);
 208                                 }
 209
 210                                 if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
 211                                         $existing = $this->MetaColumns($tabname);
 212                                         list(,$colname,$default) = $matches;
 213                                         $alter .= $colname;
 214                                         if ($this->connection) {
 215                                                 $old_coltype = $this->connection->MetaType($existing[strtoupper($colname)]);
 216                                         }
 217                                         else {
 218                                                 $old_coltype = $t;
 219                                         }
 220                                         $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 221                                         $t = trim(str_replace('DEFAULT '.$default,'',$v));
 222
 223                                         // Type change from bool to int
 224                                         if ( $old_coltype == 'L' && $t == 'INTEGER' ) {
 225                                                 $sql[] = $alter . ' DROP DEFAULT';
 226                                                 $sql[] = $alter . " TYPE $t USING ($colname::BOOL)::INT";
 227                                                 $sql[] = $alter . " SET DEFAULT $default";
 228                                         }
 229                                         // Type change from int to bool
 230                                         else if ( $old_coltype == 'I' && $t == 'BOOLEAN' ) {
 231                                                 if( strcasecmp('NULL', trim($default)) != 0 ) {
 232                                                         $default = $this->connection->qstr($default);
 233                                                 }
 234                                                 $sql[] = $alter . ' DROP DEFAULT';
 235                                                 $sql[] = $alter . " TYPE $t USING CASE WHEN $colname = 0 THEN false ELSE true END";
 236                                                 $sql[] = $alter . " SET DEFAULT $default";
 237                                         }
 238                                         // Any other column types conversion
 239                                         else {
 240                                                 $sql[] = $alter . " TYPE $t";
 241                                                 $sql[] = $alter . " SET DEFAULT $default";
 242                                         }
 243
 244                                 }
 245                                 else {
 246                                         // drop default?
 247                                         preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches);
 248                                         list (,$colname,$rest) = $matches;
 249                                         $alter .= $colname;
 250                                         $sql[] = $alter . ' TYPE ' . $rest;
 251                                 }
 252
 253 #                               list($colname) = explode(' ',$v);
 254                                 if ($not_null) {
 255                                         // this does not error out if the column is already not null
 256                                         $sql[] = $alter . ' SET NOT NULL';
 257                                 }
 258                                 if ($set_null) {
 259                                         // this does not error out if the column is already null
 260                                         $sql[] = $alter . ' DROP NOT NULL';
 261                                 }
 262                         }
 263                         return $sql;
 264                 }
 265
 266                 // does not have alter column
 267                 if (!$tableflds) {
 268                         if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 269                         return array();
 270                 }
 271                 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 272         }
 273
 274         /**
 275          * Drop one column
 276          *
 277          * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
 278          * to allow, recreating the table and copying the content over to the new table
 279          * @param string $tabname table-name
 280          * @param string $flds column-name and type for the changed column
 281          * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
 282          * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
 283          * @return array with SQL strings
 284          */
 285         function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 286         {
 287                 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
 288                 if (!$has_drop_column && !$tableflds) {
 289                         if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
 290                 return array();
 291         }
 292                 if ($has_drop_column) {
 293                         return ADODB_DataDict::DropColumnSQL($tabname, $flds);
 294                 }
 295                 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
 296         }
 297
 298         /**
 299          * Save the content into a temp. table, drop and recreate the original table and copy the content back in
 300          *
 301          * We also take care to set the values of the sequenz and recreate the indexes.
 302          * All this is done in a transaction, to not loose the content of the table, if something went wrong!
 303          * @internal
 304          * @param string $tabname table-name
 305          * @param string $dropflds column-names to drop
 306          * @param string $tableflds complete defintion of the new table, eg. for postgres
 307          * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
 308          * @return array with SQL strings
 309          */
 310         function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
 311         {
 312                 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
 313                 $copyflds = array();
 314                 foreach($this->MetaColumns($tabname) as $fld) {
 315                         if (!$dropflds || !in_array($fld->name,$dropflds)) {
 316                                 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
 317                                 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
 318                                         in_array($fld->type,array('varchar','char','text','bytea'))) {
 319                                         $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
 320                                 } else {
 321                                         $copyflds[] = $fld->name;
 322                                 }
 323                                 // identify the sequence name and the fld its on
 324                                 if ($fld->primary_key && $fld->has_default &&
 325                                         preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
 326                                         $seq_name = $matches[1];
 327                                         $seq_fld = $fld->name;
 328                                 }
 329                         }
 330                 }
 331                 $copyflds = implode(', ',$copyflds);
 332
 333                 $tempname = $tabname.'_tmp';
 334                 $aSql[] = 'BEGIN';              // we use a transaction, to make sure not to loose the content of the table
 335                 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
 336                 $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
 337                 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
 338                 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
 339                 if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
 340                         $seq_name = $tabname.'_'.$seq_fld.'_seq';       // has to be the name of the new implicit sequence
 341                         $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
 342                 }
 343                 $aSql[] = "DROP TABLE $tempname";
 344                 // recreate the indexes, if they not contain one of the droped columns
 345                 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
 346                 {
 347                         if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
 348                                 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
 349                                         $idx_data['unique'] ? array('UNIQUE') : False));
 350                         }
 351                 }
 352                 $aSql[] = 'COMMIT';
 353                 return $aSql;
 354         }
 355
 356         function DropTableSQL($tabname)
 357         {
 358                 $sql = ADODB_DataDict::DropTableSQL($tabname);
 359
 360                 $drop_seq = $this->_DropAutoIncrement($tabname);
 361                 if ($drop_seq) $sql[] = $drop_seq;
 362
 363                 return $sql;
 364         }
 365
 366         // return string must begin with space
 367         function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 368         {
 369                 if ($fautoinc) {
 370                         $ftype = 'SERIAL';
 371                         return '';
 372                 }
 373                 $suffix = '';
 374                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 375                 if ($fnotnull) $suffix .= ' NOT NULL';
 376                 if ($fconstraint) $suffix .= ' '.$fconstraint;
 377                 return $suffix;
 378         }
 379
 380         // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
 381         // if yes return sql to drop it
 382         // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
 383         function _DropAutoIncrement($tabname)
 384         {
 385                 $tabname = $this->connection->quote('%'.$tabname.'%');
 386
 387                 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
 388
 389                 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
 390                 if (!$seq || $this->connection->GetOne("SELECT relname FROM pg_class JOIN pg_depend ON pg_class.relfilenode=pg_depend.objid WHERE relname='$seq' AND relkind='S' AND deptype='i'")) {
 391                         return False;
 392                 }
 393                 return "DROP SEQUENCE ".$seq;
 394         }
 395
 396         function RenameTableSQL($tabname,$newname)
 397         {
 398                 if (!empty($this->schema)) {
 399                         $rename_from = $this->TableName($tabname);
 400                         $schema_save = $this->schema;
 401                         $this->schema = false;
 402                         $rename_to = $this->TableName($newname);
 403                         $this->schema = $schema_save;
 404                         return array (sprintf($this->renameTable, $rename_from, $rename_to));
 405                 }
 406
 407                 return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
 408         }
 409
 410         /*
 411         CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
 412         { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
 413         | table_constraint } [, ... ]
 414         )
 415         [ INHERITS ( parent_table [, ... ] ) ]
 416         [ WITH OIDS | WITHOUT OIDS ]
 417         where column_constraint is:
 418         [ CONSTRAINT constraint_name ]
 419         { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
 420         CHECK (expression) |
 421         REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
 422         [ ON DELETE action ] [ ON UPDATE action ] }
 423         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
 424         and table_constraint is:
 425         [ CONSTRAINT constraint_name ]
 426         { UNIQUE ( column_name [, ... ] ) |
 427         PRIMARY KEY ( column_name [, ... ] ) |
 428         CHECK ( expression ) |
 429         FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
 430         [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
 431         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
 432         */
 433
 434
 435         /*
 436         CREATE [ UNIQUE ] INDEX index_name ON table
 437 [ USING acc_method ] ( column [ ops_name ] [, ...] )
 438 [ WHERE predicate ]
 439 CREATE [ UNIQUE ] INDEX index_name ON table
 440 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
 441 [ WHERE predicate ]
 442         */
 443         function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 444         {
 445                 $sql = array();
 446
 447                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 448                         $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 449                         if ( isset($idxoptions['DROP']) )
 450                                 return $sql;
 451                 }
 452
 453                 if ( empty ($flds) ) {
 454                         return $sql;
 455                 }
 456
 457                 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 458
 459                 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
 460
 461                 if (isset($idxoptions['HASH']))
 462                         $s .= 'USING HASH ';
 463
 464                 if ( isset($idxoptions[$this->upperName]) )
 465                         $s .= $idxoptions[$this->upperName];
 466
 467                 if ( is_array($flds) )
 468                         $flds = implode(', ',$flds);
 469                 $s .= '(' . $flds . ')';
 470                 $sql[] = $s;
 471
 472                 return $sql;
 473         }
 474
 475         function _GetSize($ftype, $ty, $fsize, $fprec)
 476         {
 477                 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty  != 'I' && strpos($ftype,'(') === false) {
 478                         $ftype .= "(".$fsize;
 479                         if (strlen($fprec)) $ftype .= ",".$fprec;
 480                         $ftype .= ')';
 481                 }
 482                 return $ftype;
 483         }
 484 }