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