1<?php 2 /** 3 * Database schema abstraction class for PostgreSQL 4 * @author Tobias Ratschiller <tobias@dnet.it> 5 * @author Dan Wilson <phpPgAdmin@acucore.com> 6 * @author Michael Dean <mdean@users.sourceforge.net> 7 * @author Miles Lott <milosch@phpgroupware.org> 8 * @copyright Copyright (C) 1998-1999 Tobias Ratschiller 9 * @copyright Copyright (C) 1999-2000 Dan Wilson 10 * @copyright Copyright (C) ? Michael Dean, Miles Lott 11 * @copyright Portions Copyright (C) 2003,2004 Free Software Foundation, Inc. http://www.fsf.org/ 12 * @license http://www.fsf.org/licenses/gpl.html GNU General Public License 13 * @package phpgwapi 14 * @subpackage database 15 * @version $Id: class.schema_proc_pgsql.inc.php 16676 2006-04-20 06:47:04Z sigurdne $ 16 * @link http://www.greatbridge.org/project/phppgadmin 17 * @internal SQL for table properties taken from phpPgAdmin Version 2.2.1 18 */ 19 20 /** 21 * Database schema abstraction class for PostgreSQL 22 * 23 * @package phpgwapi 24 * @subpackage database 25 */ 26 class schema_proc_pgsql 27 { 28 var $m_sStatementTerminator; 29 /* Following added to convert sql to array */ 30 var $sCol = array(); 31 var $pk = array(); 32 var $fk = array(); 33 var $ix = array(); 34 var $uc = array(); 35 36 function schema_proc_pgsql() 37 { 38 $this->m_sStatementTerminator = ';'; 39 } 40 41 /* Return a type suitable for DDL */ 42 function TranslateType($sType, $iPrecision = 0, $iScale = 0) 43 { 44 switch($sType) 45 { 46 case 'auto': 47 $sTranslated = 'int4'; 48 break; 49 case 'blob': 50 $sTranslated = 'text'; 51 break; 52 case 'char': 53 if ($iPrecision > 0 && $iPrecision < 256) 54 { 55 $sTranslated = sprintf("char(%d)", $iPrecision); 56 } 57 if ($iPrecision > 255) 58 { 59 $sTranslated = 'text'; 60 } 61 break; 62 case 'date': 63 $sTranslated = 'date'; 64 break; 65 case 'decimal': 66 $sTranslated = sprintf("decimal(%d,%d)", $iPrecision, $iScale); 67 break; 68 case 'float': 69 if ($iPrecision == 4 || $iPrecision == 8) 70 { 71 $sTranslated = sprintf("float%d", $iPrecision); 72 } 73 break; 74 case 'int': 75 if ($iPrecision == 2 || $iPrecision == 4 || $iPrecision == 8) 76 { 77 $sTranslated = sprintf("int%d", $iPrecision); 78 } 79 break; 80 case 'longtext': 81 $sTranslated = 'text'; 82 break; 83 case 'text': 84 $sTranslated = 'text'; 85 break; 86 case 'timestamp': 87 $sTranslated = 'timestamp'; 88 break; 89 case 'varchar': 90 if ($iPrecision > 0 && $iPrecision < 256) 91 { 92 $sTranslated = sprintf("varchar(%d)", $iPrecision); 93 } 94 if ($iPrecision > 255) 95 { 96 $sTranslated = 'text'; 97 } 98 break; 99 } 100 return $sTranslated; 101 } 102 103 function TranslateDefault($sDefault) 104 { 105 switch ($sDefault) 106 { 107 case 'current_date': 108 case 'current_timestamp': 109 return "'now'"; 110 } 111 return "'" . $sDefault . "'"; 112 } 113 114 /* Inverse of above, convert sql column types to array info */ 115 function rTranslateType($sType, $iPrecision = 0, $iScale = 0) 116 { 117 $sTranslated = ''; 118 switch($sType) 119 { 120 case 'serial': 121 $sTranslated = "'type' => 'auto'"; 122 break; 123 case 'int2': 124 $sTranslated = "'type' => 'int', 'precision' => 2"; 125 break; 126 case 'int4': 127 $sTranslated = "'type' => 'int', 'precision' => 4"; 128 break; 129 case 'int8': 130 $sTranslated = "'type' => 'int', 'precision' => 8"; 131 break; 132 case 'bpchar': 133 case 'char': 134 if ($iPrecision > 0 && $iPrecision < 256) 135 { 136 $sTranslated = "'type' => 'char', 'precision' => $iPrecision"; 137 } 138 if ($iPrecision > 255) 139 { 140 $sTranslated = "'type' => 'text'"; 141 } 142 break; 143 case 'numeric': 144 /* Borrowed from phpPgAdmin */ 145 $iPrecision = ($iScale >> 16) & 0xffff; 146 $iScale = ($iScale - 4) & 0xffff; 147 $sTranslated = "'type' => 'decimal', 'precision' => $iPrecision, 'scale' => $iScale"; 148 break; 149 case 'float': 150 case 'float4': 151 case 'float8': 152 case 'double': 153 $sTranslated = "'type' => 'float', 'precision' => $iPrecision"; 154 break; 155 case 'datetime': 156 case 'timestamp': 157 $sTranslated = "'type' => 'timestamp'"; 158 break; 159 case 'varchar': 160 if ($iPrecision > 0 && $iPrecision < 256) 161 { 162 $sTranslated = "'type' => 'varchar', 'precision' => $iPrecision"; 163 } 164 if ($iPrecision > 255) 165 { 166 $sTranslated = "'type' => 'text'"; 167 } 168 break; 169 case 'text': 170 case 'blob': 171 case 'date': 172 $sTranslated = "'type' => '$sType'"; 173 break; 174 } 175 return $sTranslated; 176 } 177 178 function GetPKSQL($sFields) 179 { 180 return "PRIMARY KEY($sFields)"; 181 } 182 183 function GetUCSQL($sFields) 184 { 185 return "UNIQUE($sFields)"; 186 } 187 188 function GetIXSQL($sFields) 189 { 190 $this->indexes_sql[$sFields] = "CREATE INDEX __index_name__ ON __table_name__ USING btree ($sFields)"; 191 return ''; 192 } 193 194 195 function GetFKSQL($sFields) 196 { 197 if (ereg("\((.*)\)", $sFields, $regs)) 198 { 199 $ret = "FOREIGN KEY (".$regs[1].")\n" . 200 " REFERENCES ".$sFields; 201 return $ret; 202 } else 203 return ""; // incorrect FK declaration found 204 } 205 206 function _GetColumns($oProc, $sTableName, &$sColumns, $sDropColumn = '', $sAlteredColumn = '', $sAlteredColumnType = '') 207 { 208 $sdb = $oProc->m_odb; 209 $sdc = $oProc->m_odb; 210 211 $sColumns = ''; 212 $this->pk = array(); 213 $this->fk = array(); 214 $this->ix = array(); 215 $this->uc = array(); 216 217 $query = "SELECT a.attname,a.attnum FROM pg_attribute a,pg_class b WHERE "; 218 $query .= "b.oid=a.attrelid AND a.attnum>0 and b.relname='$sTableName'"; 219 if ($sDropColumn != '') 220 { 221 $query .= " AND a.attname != '$sDropColumn'"; 222 } 223 $query .= ' ORDER BY a.attnum'; 224 225 $oProc->m_odb->query($query); 226 while ($oProc->m_odb->next_record()) 227 { 228 if ($sColumns != '') 229 { 230 $sColumns .= ','; 231 } 232 233 $sFieldName = $oProc->m_odb->f(0); 234 $sColumns .= $sFieldName; 235 if ($sAlteredColumn == $sFieldName && $sAlteredColumnType != '') 236 { 237 $sColumns .= '::' . $sAlteredColumnType; 238 } 239 } 240 //$qdefault = "SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c " 241 // . "WHERE c.relname = $sTableName AND c.oid = d.adrelid AND d.adnum =" . $oProc->m_odb->f(1); 242 $sql_get_fields = " 243 SELECT 244 a.attnum, 245 a.attname AS field, 246 t.typname AS type, 247 a.attlen AS length, 248 a.atttypmod AS lengthvar, 249 a.attnotnull AS notnull 250 FROM 251 pg_class c, 252 pg_attribute a, 253 pg_type t 254 WHERE 255 c.relname = '$sTableName' 256 and a.attnum > 0 257 and a.attrelid = c.oid 258 and a.atttypid = t.oid 259 ORDER BY a.attnum"; 260 /* attnum field type length lengthvar notnull(Yes/No) */ 261 $sdb->query($sql_get_fields); 262 while ($sdb->next_record()) 263 { 264 $colnum = $sdb->f(0); 265 $colname = $sdb->f(1); 266 267 if ($sdb->f(5) == 'Yes') 268 { 269 $null = "'nullable' => True"; 270 } 271 else 272 { 273 $null = "'nullable' => False"; 274 } 275 276 if ($sdb->f(2) == 'numeric') 277 { 278 $prec = $sdb->f(3); 279 $scale = $sdb->f(4); 280 } 281 elseif ($sdb->f(3) > 0) 282 { 283 $prec = $sdb->f(3); 284 $scale = 0; 285 } 286 elseif ($sdb->f(4) > 0) 287 { 288 $prec = $sdb->f(4) - 4; 289 $scale = 0; 290 } 291 else 292 { 293 $prec = 0; 294 $scale = 0; 295 } 296 297 $type = $this->rTranslateType($sdb->f(2), $prec, $scale); 298 299 $sql_get_default = " 300 SELECT d.adsrc AS rowdefault 301 FROM pg_attrdef d, pg_class c 302 WHERE 303 c.relname = '$sTableName' AND 304 c.oid = d.adrelid AND 305 d.adnum = $colnum 306 "; 307 $sdc->query($sql_get_default); 308 $sdc->next_record(); 309 if ($sdc->f(0)) 310 { 311 if (ereg('nextval',$sdc->f(0))) 312 { 313 $default = ''; 314 $nullcomma = ''; 315 } 316 else 317 { 318 $default = "'default' => '".$sdc->f(0)."'"; 319 $nullcomma = ','; 320 } 321 } 322 else 323 { 324 $default = ''; 325 $nullcomma = ''; 326 } 327 $default = ereg_replace("''","'",$default); 328 329 $this->sCol[] = "\t\t\t\t'" . $colname . "' => array(" . $type . ',' . $null . $nullcomma . $default . '),' . "\n"; 330 } 331 $sql_pri_keys = " 332 SELECT 333 ic.relname AS index_name, 334 bc.relname AS tab_name, 335 ta.attname AS column_name, 336 i.indisunique AS unique_key, 337 i.indisprimary AS primary_key 338 FROM 339 pg_class bc, 340 pg_class ic, 341 pg_index i, 342 pg_attribute ta, 343 pg_attribute ia 344 WHERE 345 bc.oid = i.indrelid 346 AND ic.oid = i.indexrelid 347 AND ia.attrelid = i.indexrelid 348 AND ta.attrelid = bc.oid 349 AND bc.relname = '$sTableName' 350 AND ta.attrelid = i.indrelid 351 AND ta.attnum = i.indkey[ia.attnum-1] 352 ORDER BY 353 index_name, tab_name, column_name"; 354 $sdc->query($sql_pri_keys); 355 while ($sdc->next_record()) 356 { 357 //echo '<br /> checking: ' . $sdc->f(4); 358 if ($sdc->f(4) == 't') 359 { 360 $this->pk[] = $sdc->f(2); 361 } 362 if ($sdc->f(3) == 't') 363 { 364 $this->uc[] = $sdc->f(2); 365 } 366 } 367 /* ugly as heck, but is here to chop the trailing comma on the last element (for php3) */ 368 $this->sCol[count($this->sCol) - 1] = substr($this->sCol[count($this->sCol) - 1],0,-2) . "\n"; 369 370 return false; 371 } 372 373 function GetSequenceForTable($oProc,$table,&$sSequenceName) 374 { 375 global $DEBUG; 376 if($DEBUG) { echo '<br />GetSequenceForTable: ' . $table; } 377 378 $oProc->m_odb->query("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE 'seq_$table' AND relkind='S' ORDER BY relname",__LINE__,__FILE__); 379 $oProc->m_odb->next_record(); 380 if ($oProc->m_odb->f('relname')) 381 { 382 $sSequenceName = $oProc->m_odb->f('relname'); 383 } 384 return True; 385 } 386 387 function GetSequenceFieldForTable($oProc,$table,&$sField) 388 { 389 global $DEBUG; 390 if($DEBUG) { echo '<br />GetSequenceFieldForTable: You rang?'; } 391 $oProc->m_odb->query("SELECT a.attname FROM pg_attribute a, pg_class c, pg_attrdef d WHERE c.relname='$table' AND c.oid=d.adrelid AND d.adsrc LIKE '%seq_$table%' AND a.attrelid=c.oid AND d.adnum=a.attnum"); 392 $oProc->m_odb->next_record(); 393 if ($oProc->m_odb->f('attname')) 394 { 395 $sField = $oProc->m_odb->f('attname'); 396 } 397 return True; 398 } 399 400 function DropSequenceForTable($oProc,$table) 401 { 402 global $DEBUG; 403 if($DEBUG) { echo '<br />DropSequenceForTable: ' . $table; } 404 405 $this->GetSequenceForTable($oProc,$table,$sSequenceName); 406 if ($sSequenceName) 407 { 408 $oProc->m_odb->query("DROP SEQUENCE " . $sSequenceName . " CASCADE",__LINE__,__FILE__); 409 } 410 return True; 411 } 412 413 function DropTable($oProc, &$aTables, $sTableName) 414 { 415 $this->DropSequenceForTable($oProc,$sTableName); 416 417 return $oProc->m_odb->query("DROP TABLE " . $sTableName . " CASCADE") && 418 $this->DropSequenceForTable($oProc, $sTableName); 419 } 420 421 function DropColumn($oProc, &$aTables, $sTableName, $aNewTableDef, $sColumnName, $bCopyData = true) 422 { 423 $query = "ALTER TABLE $sTableName DROP COLUMN $sColumnName CASCADE"; 424 $bRet = !!($oProc->m_odb->query($query)); 425 return $bRet; 426 427 } 428 429 function RenameTable($oProc, &$aTables, $sOldTableName, $sNewTableName) 430 { 431 global $DEBUG; 432 if ($DEBUG) { echo '<br>RenameTable():' . $sOldTableName . 'to: '. $sNewTableName; } 433 if ($DEBUG) { echo '<br>RenameTable(): Fetching old sequence for: ' . $sOldTableName; } 434 $this->GetSequenceForTable($oProc,$sOldTableName,$sSequenceName); 435 if ($DEBUG) { echo ' - ' . $sSequenceName; } 436 if ($DEBUG) { echo '<br>RenameTable(): Fetching sequence field for: ' . $sOldTableName; } 437 $this->GetSequenceFieldForTable($oProc,$sOldTableName,$sField); 438 if ($DEBUG) { echo ' - ' . $sField; } 439 440 if ($sSequenceName) 441 { 442 $oProc->m_odb->query("SELECT last_value FROM seq_$sOldTableName",__LINE__,__FILE__); 443 $oProc->m_odb->next_record(); 444 $lastval = $oProc->m_odb->f(0); 445 446 if ($lastval) 447 { 448 $lastval = ' start ' . $lastval; 449 } 450 $this->GetSequenceSQL($sNewTableName,$sSequenceSQL); 451 if ($DEBUG) { echo '<br>RenameTable(): Making new sequence using: ' . $sSequenceSQL . $lastval; } 452 $oProc->m_odb->query($sSequenceSQL . $lastval,__LINE__,__FILE__); 453 if ($DEBUG) { echo '<br>RenameTable(): Altering column default for: ' . $sField; } 454 } 455 456 $oProc->m_odb->query("ALTER TABLE $sOldTableName RENAME TO $sNewTableName"); 457 if ($sSequenceName) 458 { 459 $Ok = !!($oProc->m_odb->query("ALTER TABLE $sNewTableName ALTER $sField SET DEFAULT nextval('seq_" . $sNewTableName . "')")); 460 $this->DropSequenceForTable($oProc,$sOldTableName); 461 } 462 463 return $Ok; 464 465 /* todo - fix index-renaming. 466 $indexnames = $oProc->m_odb->index_names(); 467 while(list($key,$val) = @each($indexnames)) 468 { 469 $indexes[] = $val['index_name']; 470 } 471 if(!in_array($sOldTableName . '_pkey',$indexes)) // no idea how this can happen 472 { 473 $oProc->m_odb->query("DROP INDEX " . $sOldTableName . "_pkey",__LINE__,__FILE__); 474 } 475 else // rename the index 476 { 477 $oProc->m_odb->query('ALTER TABLE '.$sOldTableName.'_pkey RENAME TO '.$sNewTableName.'_pkey'); 478 } 479 480 481 return !!($oProc->m_odb->query("ALTER TABLE $sOldTableName RENAME TO $sNewTableName")); 482 */ 483 } 484 485 function RenameColumn($oProc, &$aTables, $sTableName, $sOldColumnName, $sNewColumnName, $bCopyData = true) 486 { 487 $query = "ALTER TABLE $sTableName RENAME COLUMN $sOldColumnName TO $sNewColumnName"; 488 return !!($oProc->m_odb->query($query)); 489 } 490 491 function AlterColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef, $bCopyData = true) 492 { 493 $sType = ''; 494 $iPrecision = 0; 495 $iScale = 0; 496 $sDefault = ''; 497 $bNullable = true; 498 499 reset($aColumnDef); 500 while(list($sAttr, $vAttrVal) = each($aColumnDef)) 501 { 502 switch ($sAttr) 503 { 504 case 'type': 505 $sType = $vAttrVal; 506 break; 507 case 'precision': 508 $iPrecision = (int)$vAttrVal; 509 break; 510 case 'scale': 511 $iScale = (int)$vAttrVal; 512 break; 513 case 'default': 514 $sDefault = $vAttrVal; 515 break; 516 case 'nullable': 517 $bNullable = $vAttrVal; 518 break; 519 } 520 } 521 522 $sFieldSQL = $this->TranslateType($sType, $iPrecision, $iScale); 523 $query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName TYPE $sFieldSQL"; 524 $Ok = !!($oProc->m_odb->query($query)); 525 526 if($bNullable == False || $bNullable == 'False') 527 { 528 $sFieldSQL = ' NOT NULL'; 529 $query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET $sFieldSQL"; 530 $Ok = !!($oProc->m_odb->query($query)); 531 } 532 533 if($sDefault == '0') 534 { 535 $defaultSQL = " DEFAULT 0"; 536 } 537 elseif(!is_numeric($sDefault) && $sDefault != '') 538 { 539 $sTranslatedDefault = $this->TranslateDefault($sDefault); 540 $defaultSQL = " DEFAULT $sTranslatedDefault"; 541 } 542 elseif($sDefault) 543 { 544 $defaultSQL = " DEFAULT $sDefault"; 545 } 546 547 if($defaultSQL) 548 { 549 $query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET $defaultSQL"; 550 $Ok = !!($oProc->m_odb->query($query)); 551 } 552 553 return $Ok; 554 } 555 556 function AddColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef) 557 { 558 if (isset($aColumnDef['default'])) // pgsql cant add a colum with a default 559 { 560 $default = $aColumnDef['default']; 561 unset($aColumnDef['default']); 562 } 563 if (isset($aColumnDef['nullable']) && !$aColumnDef['nullable']) // pgsql cant add a column not nullable 564 { 565 $notnull = !$aColumnDef['nullable']; 566 unset($aColumnDef['nullable']); 567 } 568 $oProc->_GetFieldSQL($aColumnDef, $sFieldSQL); 569 $query = "ALTER TABLE $sTableName ADD COLUMN $sColumnName $sFieldSQL"; 570 571 if (($Ok = !!($oProc->m_odb->query($query))) && isset($default)) 572 { 573 if($default == '0') 574 { 575 $defaultSQL = " DEFAULT 0"; 576 } 577 elseif(!is_numeric($default) && $default != '') 578 { 579 $sTranslatedDefault = $this->TranslateDefault($default); 580 $defaultSQL = " DEFAULT $sTranslatedDefault"; 581 } 582 elseif($default) 583 { 584 $defaultSQL = " DEFAULT $default"; 585 } 586 587 $query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET $defaultSQL;\n"; 588 589 $query .= "UPDATE $sTableName SET $sColumnName='$default';\n"; 590 591 $Ok = !!($oProc->m_odb->query($query)); 592 593 if ($OK && $notnull) 594 { 595 // unfortunally this is pgSQL >= 7.3 596 //$query .= "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET NOT NULL;\n"; 597 //$Ok = !!($oProc->m_odb->query($query)); 598 // so we do it the slow way 599 AlterColumn($oProc, $aTables, $sTableName, $sColumnName, $aColumnDef); 600 } 601 } 602 return $Ok; 603 } 604 605 function GetSequenceSQL($sTableName, &$sSequenceSQL) 606 { 607 $sSequenceSQL = sprintf("CREATE SEQUENCE seq_%s", $sTableName); 608 return true; 609 } 610 611 function CreateTable($oProc, $aTables, $sTableName, $aTableDef, $bCreateSequence = true) 612 { 613 global $DEBUG; 614 unset($this->indexes_sql); 615 if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL)) 616 { 617 /* create sequence first since it will be needed for default */ 618 if ($bCreateSequence && $sSequenceSQL != '') 619 { 620 if ($DEBUG) { echo '<br />Making sequence using: ' . $sSequenceSQL; } 621 $oProc->m_odb->query($sSequenceSQL); 622 } 623 624 $query = "CREATE TABLE $sTableName ($sTableSQL)"; 625 //echo 'sql' .$query . "\n"; 626 627 $result = !!($oProc->m_odb->query($query)); 628 if($result==True) 629 { 630 if ($DEBUG) 631 { 632 echo '<pre>'; 633 print_r($this->indexes_sql); 634 echo '</pre>'; 635 } 636 637 if(is_array($this->indexes_sql) && count($this->indexes_sql)>0) 638 { 639 foreach($this->indexes_sql as $key => $sIndexSQL) 640 { 641 $ix_name = str_replace(',','_',$key).'_'.$sTableName.'_idx'; 642 $IndexSQL = str_replace(array('__index_name__','__table_name__'), array($ix_name,$sTableName), $sIndexSQL); 643 $oProc->m_odb->query($IndexSQL); 644 } 645 } 646 } 647 return $result; 648 //return !!($oProc->m_odb->query($query)); 649 } 650 651 return false; 652 } 653 } 654?> 655