| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
1 <?php 2 3 /** 4 @version v5.20.3 01-Jan-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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |