[2] | 1 | <?php |
---|
| 2 | /**************************************************************************\ |
---|
| 3 | * eGroupWare - Setup * |
---|
| 4 | * http://www.egroupware.org * |
---|
| 5 | * SQL for table properties taken from phpPgAdmin Version 2.2.1 * |
---|
| 6 | * http://www.greatbridge.org/project/phppgadmin * |
---|
| 7 | * Copyright (C) 1999-2000 Dan Wilson <phpPgAdmin@acucore.com> * |
---|
| 8 | * Copyright (C) 1998-1999 Tobias Ratschiller <tobias@dnet.it> * |
---|
| 9 | * -------------------------------------------- * |
---|
| 10 | * This file written by Michael Dean<mdean@users.sourceforge.net> * |
---|
| 11 | * and Miles Lott<milosch@groupwhere.org> * |
---|
| 12 | * -------------------------------------------- * |
---|
| 13 | * This program is free software; you can redistribute it and/or modify it * |
---|
| 14 | * under the terms of the GNU General Public License as published by the * |
---|
| 15 | * Free Software Foundation; either version 2 of the License, or (at your * |
---|
| 16 | * option) any later version. * |
---|
| 17 | \**************************************************************************/ |
---|
| 18 | |
---|
| 19 | |
---|
| 20 | class schema_proc_pgsql |
---|
| 21 | { |
---|
| 22 | var $m_sStatementTerminator; |
---|
| 23 | /* Following added to convert sql to array */ |
---|
| 24 | var $sCol = array(); |
---|
| 25 | var $pk = array(); |
---|
| 26 | var $fk = array(); |
---|
| 27 | var $ix = array(); |
---|
| 28 | var $uc = array(); |
---|
| 29 | |
---|
| 30 | function schema_proc_pgsql() |
---|
| 31 | { |
---|
| 32 | $this->m_sStatementTerminator = ';'; |
---|
| 33 | } |
---|
| 34 | |
---|
| 35 | /* Return a type suitable for DDL */ |
---|
| 36 | function TranslateType($sType, $iPrecision = 0, $iScale = 0) |
---|
| 37 | { |
---|
| 38 | $sTranslated = $sType; |
---|
| 39 | switch($sType) |
---|
| 40 | { |
---|
| 41 | case 'auto': |
---|
| 42 | $sTranslated = 'int4'; |
---|
| 43 | break; |
---|
| 44 | case 'blob': |
---|
| 45 | $sTranslated = 'bytea'; |
---|
| 46 | break; |
---|
| 47 | case 'char': |
---|
| 48 | if($iPrecision > 0 && $iPrecision < 256) |
---|
| 49 | { |
---|
| 50 | $sTranslated = sprintf("char(%d)", $iPrecision); |
---|
| 51 | } |
---|
| 52 | if($iPrecision > 255) |
---|
| 53 | { |
---|
| 54 | $sTranslated = 'text'; |
---|
| 55 | } |
---|
| 56 | break; |
---|
| 57 | case 'decimal': |
---|
| 58 | $sTranslated = sprintf("decimal(%d,%d)", $iPrecision, $iScale); |
---|
| 59 | break; |
---|
| 60 | case 'float': |
---|
| 61 | if($iPrecision == 4 || $iPrecision == 8) |
---|
| 62 | { |
---|
| 63 | $sTranslated = sprintf("float%d", $iPrecision); |
---|
| 64 | } |
---|
| 65 | break; |
---|
| 66 | case 'int': |
---|
| 67 | if($iPrecision == 2 || $iPrecision == 4 || $iPrecision == 8) |
---|
| 68 | { |
---|
| 69 | $sTranslated = sprintf("int%d", $iPrecision); |
---|
| 70 | } |
---|
| 71 | break; |
---|
| 72 | case 'longtext': |
---|
| 73 | $sTranslated = 'text'; |
---|
| 74 | break; |
---|
| 75 | case 'varchar': |
---|
| 76 | if($iPrecision > 0 && $iPrecision < 256) |
---|
| 77 | { |
---|
| 78 | $sTranslated = sprintf("varchar(%d)", $iPrecision); |
---|
| 79 | } |
---|
| 80 | if($iPrecision > 255) |
---|
| 81 | { |
---|
| 82 | $sTranslated = 'text'; |
---|
| 83 | } |
---|
| 84 | break; |
---|
| 85 | } |
---|
| 86 | return $sTranslated; |
---|
| 87 | } |
---|
| 88 | |
---|
| 89 | function TranslateDefault($sDefault) |
---|
| 90 | { |
---|
| 91 | switch($sDefault) |
---|
| 92 | { |
---|
| 93 | case 'current_date': |
---|
| 94 | case 'current_timestamp': |
---|
| 95 | $sDefault = 'now'; |
---|
| 96 | } |
---|
| 97 | return "'$sDefault'"; |
---|
| 98 | } |
---|
| 99 | |
---|
| 100 | /* Inverse of above, convert sql column types to array info */ |
---|
| 101 | function rTranslateType($sType, $iPrecision = 0, $iScale = 0) |
---|
| 102 | { |
---|
| 103 | $sTranslated = ''; |
---|
| 104 | switch($sType) |
---|
| 105 | { |
---|
| 106 | case 'serial': |
---|
| 107 | $sTranslated = "'type' => 'auto'"; |
---|
| 108 | break; |
---|
| 109 | case 'int2': |
---|
| 110 | $sTranslated = "'type' => 'int', 'precision' => 2"; |
---|
| 111 | break; |
---|
| 112 | case 'int4': |
---|
| 113 | $sTranslated = "'type' => 'int', 'precision' => 4"; |
---|
| 114 | break; |
---|
| 115 | case 'int8': |
---|
| 116 | $sTranslated = "'type' => 'int', 'precision' => 8"; |
---|
| 117 | break; |
---|
| 118 | case 'bpchar': |
---|
| 119 | case 'char': |
---|
| 120 | if($iPrecision > 0 && $iPrecision < 256) |
---|
| 121 | { |
---|
| 122 | $sTranslated = "'type' => 'char', 'precision' => $iPrecision"; |
---|
| 123 | } |
---|
| 124 | if($iPrecision > 255) |
---|
| 125 | { |
---|
| 126 | $sTranslated = "'type' => 'text'"; |
---|
| 127 | } |
---|
| 128 | break; |
---|
| 129 | case 'numeric': |
---|
| 130 | /* Borrowed from phpPgAdmin */ |
---|
| 131 | $iPrecision = ($iScale >> 16) & 0xffff; |
---|
| 132 | $iScale = ($iScale - 4) & 0xffff; |
---|
| 133 | $sTranslated = "'type' => 'decimal', 'precision' => $iPrecision, 'scale' => $iScale"; |
---|
| 134 | break; |
---|
| 135 | case 'float': |
---|
| 136 | case 'float4': |
---|
| 137 | case 'float8': |
---|
| 138 | case 'double': |
---|
| 139 | $sTranslated = "'type' => 'float', 'precision' => $iPrecision"; |
---|
| 140 | break; |
---|
| 141 | case 'datetime': |
---|
| 142 | case 'timestamp': |
---|
| 143 | $sTranslated = "'type' => 'timestamp'"; |
---|
| 144 | break; |
---|
| 145 | case 'varchar': |
---|
| 146 | if($iPrecision > 0 && $iPrecision < 256) |
---|
| 147 | { |
---|
| 148 | $sTranslated = "'type' => 'varchar', 'precision' => $iPrecision"; |
---|
| 149 | } |
---|
| 150 | if($iPrecision > 255) |
---|
| 151 | { |
---|
| 152 | $sTranslated = "'type' => 'text'"; |
---|
| 153 | } |
---|
| 154 | break; |
---|
| 155 | case 'text': |
---|
| 156 | case 'blob': |
---|
| 157 | case 'date': |
---|
| 158 | case 'bool': |
---|
| 159 | $sTranslated = "'type' => '$sType'"; |
---|
| 160 | break; |
---|
| 161 | } |
---|
| 162 | return $sTranslated; |
---|
| 163 | } |
---|
| 164 | |
---|
| 165 | function GetPKSQL($sFields) |
---|
| 166 | { |
---|
| 167 | return "PRIMARY KEY($sFields)"; |
---|
| 168 | } |
---|
| 169 | |
---|
| 170 | function GetUCSQL($sFields) |
---|
| 171 | { |
---|
| 172 | return "UNIQUE($sFields)"; |
---|
| 173 | } |
---|
| 174 | |
---|
| 175 | function GetIXSQL($sFields,&$append,$options,$sTableName) |
---|
| 176 | { |
---|
| 177 | $append = True; |
---|
| 178 | $ixsql = ''; |
---|
| 179 | $ixFields = str_replace(',','_',$sFields); |
---|
| 180 | $index = $sTableName . '_' . $ixFields . '_idx'; |
---|
| 181 | return "CREATE INDEX $index ON $sTableName ($sFields);\n"; |
---|
| 182 | } |
---|
| 183 | |
---|
| 184 | function _GetColumns($oProc, $sTableName, &$sColumns, $sDropColumn='', $sAlteredColumn='', $sAlteredColumnType='') |
---|
| 185 | { |
---|
| 186 | $sdb = $oProc->m_odb; |
---|
| 187 | $sdc = $oProc->m_odb; |
---|
| 188 | |
---|
| 189 | $sColumns = ''; |
---|
| 190 | $this->pk = array(); |
---|
| 191 | $this->fk = array(); |
---|
| 192 | $this->ix = array(); |
---|
| 193 | $this->uc = array(); |
---|
| 194 | |
---|
| 195 | $query = "SELECT a.attname,a.attnum FROM pg_attribute a,pg_class b WHERE "; |
---|
| 196 | $query .= "b.oid=a.attrelid AND a.attnum>0 and b.relname='$sTableName'"; |
---|
| 197 | if($sDropColumn != '') |
---|
| 198 | { |
---|
| 199 | $query .= " AND a.attname != '$sDropColumn'"; |
---|
| 200 | } |
---|
| 201 | $query .= ' ORDER BY a.attnum'; |
---|
| 202 | |
---|
| 203 | // echo '_GetColumns: ' . $query; |
---|
| 204 | |
---|
| 205 | $oProc->m_odb->query($query); |
---|
| 206 | while($oProc->m_odb->next_record()) |
---|
| 207 | { |
---|
| 208 | if($sColumns != '') |
---|
| 209 | { |
---|
| 210 | $sColumns .= ','; |
---|
| 211 | } |
---|
| 212 | |
---|
| 213 | $sFieldName = $oProc->m_odb->f(0); |
---|
| 214 | /* Failsafe in case the query still includes the column to be dropped */ |
---|
| 215 | if($sFieldName != $sDropColumn) |
---|
| 216 | { |
---|
| 217 | $sColumns .= $sFieldName; |
---|
| 218 | } |
---|
| 219 | if($sAlteredColumn == $sFieldName && $sAlteredColumnType != '') |
---|
| 220 | { |
---|
| 221 | $sColumns .= '::' . $sAlteredColumnType; |
---|
| 222 | } |
---|
| 223 | } |
---|
| 224 | //$qdefault = "SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c " |
---|
| 225 | // . "WHERE c.relname = $sTableName AND c.oid = d.adrelid AND d.adnum =" . $oProc->m_odb->f(1); |
---|
| 226 | $sql_get_fields = " |
---|
| 227 | SELECT |
---|
| 228 | a.attnum, |
---|
| 229 | a.attname AS field, |
---|
| 230 | t.typname AS type, |
---|
| 231 | a.attlen AS length, |
---|
| 232 | a.atttypmod AS lengthvar, |
---|
| 233 | a.attnotnull AS notnull |
---|
| 234 | FROM |
---|
| 235 | pg_class c, |
---|
| 236 | pg_attribute a, |
---|
| 237 | pg_type t |
---|
| 238 | WHERE |
---|
| 239 | c.relname = '$sTableName' |
---|
| 240 | and a.attnum > 0 |
---|
| 241 | and a.attrelid = c.oid |
---|
| 242 | and a.atttypid = t.oid |
---|
| 243 | ORDER BY a.attnum"; |
---|
| 244 | /* attnum field type length lengthvar notnull(Yes/No) */ |
---|
| 245 | $sdb->query($sql_get_fields); |
---|
| 246 | while($sdb->next_record()) |
---|
| 247 | { |
---|
| 248 | $colnum = $sdb->f(0); |
---|
| 249 | $colname = $sdb->f(1); |
---|
| 250 | |
---|
| 251 | if($sdb->f(5) == 'Yes') |
---|
| 252 | { |
---|
| 253 | $null = "'nullable' => True"; |
---|
| 254 | } |
---|
| 255 | else |
---|
| 256 | { |
---|
| 257 | $null = "'nullable' => False"; |
---|
| 258 | } |
---|
| 259 | |
---|
| 260 | if($sdb->f(2) == 'numeric') |
---|
| 261 | { |
---|
| 262 | $prec = $sdb->f(3); |
---|
| 263 | $scale = $sdb->f(4); |
---|
| 264 | } |
---|
| 265 | elseif($sdb->f(3) > 0) |
---|
| 266 | { |
---|
| 267 | $prec = $sdb->f(3); |
---|
| 268 | $scale = 0; |
---|
| 269 | } |
---|
| 270 | elseif($sdb->f(4) > 0) |
---|
| 271 | { |
---|
| 272 | $prec = $sdb->f(4) - 4; |
---|
| 273 | $scale = 0; |
---|
| 274 | } |
---|
| 275 | else |
---|
| 276 | { |
---|
| 277 | $prec = 0; |
---|
| 278 | $scale = 0; |
---|
| 279 | } |
---|
| 280 | |
---|
| 281 | $type = $this->rTranslateType($sdb->f(2), $prec, $scale); |
---|
| 282 | |
---|
| 283 | $sql_get_default = " |
---|
| 284 | SELECT d.adsrc AS rowdefault |
---|
| 285 | FROM pg_attrdef d, pg_class c |
---|
| 286 | WHERE |
---|
| 287 | c.relname = '$sTableName' AND |
---|
| 288 | c.oid = d.adrelid AND |
---|
| 289 | d.adnum = $colnum |
---|
| 290 | "; |
---|
| 291 | $sdc->query($sql_get_default); |
---|
| 292 | $sdc->next_record(); |
---|
| 293 | if($sdc->f(0)) |
---|
| 294 | { |
---|
| 295 | if(strstr($sdc->f(0),'nextval')) |
---|
| 296 | { |
---|
| 297 | $default = ''; |
---|
| 298 | $nullcomma = ''; |
---|
| 299 | } |
---|
| 300 | else |
---|
| 301 | { |
---|
| 302 | $default = "'default' => '".$sdc->f(0)."'"; |
---|
| 303 | $nullcomma = ','; |
---|
| 304 | } |
---|
| 305 | } |
---|
| 306 | else |
---|
| 307 | { |
---|
| 308 | $default = ''; |
---|
| 309 | $nullcomma = ''; |
---|
| 310 | } |
---|
| 311 | $default = str_replace("''","'",$default); |
---|
| 312 | |
---|
| 313 | $this->sCol[] = "\t\t\t\t'" . $colname . "' => array(" . $type . ',' . $null . $nullcomma . $default . '),' . "\n"; |
---|
| 314 | } |
---|
| 315 | $sql_pri_keys = " |
---|
| 316 | SELECT |
---|
| 317 | ic.relname AS index_name, |
---|
| 318 | bc.relname AS tab_name, |
---|
| 319 | ta.attname AS column_name, |
---|
| 320 | i.indisunique AS unique_key, |
---|
| 321 | i.indisprimary AS primary_key |
---|
| 322 | FROM |
---|
| 323 | pg_class bc, |
---|
| 324 | pg_class ic, |
---|
| 325 | pg_index i, |
---|
| 326 | pg_attribute ta, |
---|
| 327 | pg_attribute ia |
---|
| 328 | WHERE |
---|
| 329 | bc.oid = i.indrelid |
---|
| 330 | AND ic.oid = i.indexrelid |
---|
| 331 | AND ia.attrelid = i.indexrelid |
---|
| 332 | AND ta.attrelid = bc.oid |
---|
| 333 | AND bc.relname = '$sTableName' |
---|
| 334 | AND ta.attrelid = i.indrelid |
---|
| 335 | AND ta.attnum = i.indkey[ia.attnum-1] |
---|
| 336 | ORDER BY |
---|
| 337 | index_name, tab_name, column_name"; |
---|
| 338 | $sdc->query($sql_pri_keys); |
---|
| 339 | while($sdc->next_record()) |
---|
| 340 | { |
---|
| 341 | //echo '<br> checking: ' . $sdc->f(4); |
---|
| 342 | if($sdc->f(4) == 't') |
---|
| 343 | { |
---|
| 344 | $this->pk[] = $sdc->f(2); |
---|
| 345 | } |
---|
| 346 | if($sdc->f(3) == 't') |
---|
| 347 | { |
---|
| 348 | $this->uc[] = $sdc->f(2); |
---|
| 349 | } |
---|
| 350 | } |
---|
| 351 | $this->_GetIndices($oProc,$sTableName,$this->pk,$this->ix,$this->uc,$this->fk); |
---|
| 352 | |
---|
| 353 | /* ugly as heck, but is here to chop the trailing comma on the last element (for php3) */ |
---|
| 354 | $this->sCol[count($this->sCol) - 1] = substr($this->sCol[count($this->sCol) - 1],0,-2) . "\n"; |
---|
| 355 | |
---|
| 356 | return False; |
---|
| 357 | } |
---|
| 358 | |
---|
| 359 | function _GetIndices($oProc,$sTableName,&$aPk,&$aIx,&$aUc,&$aFk) |
---|
| 360 | { |
---|
| 361 | /* Try not to die on errors with the query */ |
---|
| 362 | $tmp = $oProc->Halt_On_Error; |
---|
| 363 | $oProc->Halt_On_Error = 'no'; |
---|
| 364 | $aIx = array(); |
---|
| 365 | /* This select excludes any indexes that are just base indexes for constraints. */ |
---|
| 366 | |
---|
| 367 | if(@$oProc->m_odb->db_version >= 7.3) |
---|
| 368 | { |
---|
| 369 | $sql = "SELECT pg_catalog.pg_get_indexdef(i.indexrelid) as pg_get_indexdef FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.relname = '$sTableName' AND pg_catalog.pg_table_is_visible(c.oid) AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND NOT EXISTS ( SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) WHERE d.classid = c2.tableoid AND d.objid = c2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') ) ORDER BY c2.relname"; |
---|
| 370 | $num = 0; |
---|
| 371 | } |
---|
| 372 | else |
---|
| 373 | { |
---|
| 374 | $sql = "SELECT c2.relname, i.indisprimary, i.indisunique, pg_get_indexdef(i.indexrelid) FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = '$sTableName' AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND NOT i.indisprimary AND NOT i.indisunique ORDER BY c2.relname"; |
---|
| 375 | $num = 3; |
---|
| 376 | } |
---|
| 377 | |
---|
| 378 | @$oProc->m_odb->query($sql); |
---|
| 379 | $oProc->m_odb->next_record(); |
---|
[5934] | 380 | $indexfields = preg_replace('/^CREATE.+\(/','',$oProc->m_odb->f($num)); |
---|
| 381 | $indexfields = preg_replace('/\)$/','',$indexfields); |
---|
[2] | 382 | $aIx = explode(',',$indexfields); |
---|
| 383 | $i = 0; |
---|
| 384 | foreach($aIx as $ix) |
---|
| 385 | { |
---|
| 386 | $aIx[$i] = trim($ix); |
---|
[7655] | 387 | ++$i; |
---|
[2] | 388 | } |
---|
| 389 | /* Restore original value */ |
---|
| 390 | $oProc->Halt_On_Error = $tmp; |
---|
| 391 | #echo "Indices from $sTableName<pre>pk=".print_r($aPk,True)."\nix=".print_r($aIx,True)."\nuc=".print_r($aUc,True)."</pre>\n"; |
---|
| 392 | } |
---|
| 393 | |
---|
| 394 | function _CopyAlteredTable($oProc, &$aTables, $sSource, $sDest) |
---|
| 395 | { |
---|
| 396 | $oDB = $oProc->m_odb; |
---|
| 397 | $oProc->m_odb->query("SELECT * FROM $sSource"); |
---|
| 398 | while($oProc->m_odb->next_record()) |
---|
| 399 | { |
---|
| 400 | $sSQL = "INSERT INTO $sDest ("; |
---|
| 401 | $i=0; |
---|
| 402 | @reset($aTables[$sDest]['fd']); |
---|
| 403 | while(list($name,$arraydef) = each($aTables[$sDest]['fd'])) |
---|
| 404 | { |
---|
| 405 | if($i > 0) |
---|
| 406 | { |
---|
| 407 | $sSQL .= ','; |
---|
| 408 | } |
---|
| 409 | |
---|
| 410 | $sSQL .= $name; |
---|
[7655] | 411 | ++$i; |
---|
[2] | 412 | } |
---|
| 413 | |
---|
| 414 | $sSQL .= ') VALUES ('; |
---|
| 415 | @reset($aTables[$sDest]['fd']); |
---|
| 416 | $i = 0; |
---|
| 417 | while(list($name,$arraydef) = each($aTables[$sDest]['fd'])) |
---|
| 418 | { |
---|
| 419 | if($i > 0) |
---|
| 420 | { |
---|
| 421 | $sSQL .= ','; |
---|
| 422 | } |
---|
| 423 | |
---|
| 424 | // !isset($arraydef['nullable']) means nullable !!! |
---|
| 425 | if($oProc->m_odb->f($name) == NULL && (!isset($arraydef['nullable']) || $arraydef['nullable'])) |
---|
| 426 | { |
---|
| 427 | $sSQL .= 'NULL'; |
---|
| 428 | } |
---|
| 429 | else |
---|
| 430 | { |
---|
| 431 | $value = $oProc->m_odb->f($name) != NULL ? $oProc->m_odb->f($name) : @$arraydef['default']; |
---|
| 432 | switch($arraydef['type']) |
---|
| 433 | { |
---|
| 434 | case 'blob': |
---|
| 435 | case 'char': |
---|
| 436 | case 'date': |
---|
| 437 | case 'text': |
---|
| 438 | case 'timestamp': |
---|
| 439 | case 'varchar': |
---|
| 440 | $sSQL .= "'" . $oProc->m_odb->db_addslashes($oProc->m_odb->f($name)) . "'"; |
---|
| 441 | break; |
---|
| 442 | default: |
---|
| 443 | $sSQL .= (int)$oProc->m_odb->f($name); |
---|
| 444 | } |
---|
| 445 | } |
---|
[7655] | 446 | ++$i; |
---|
[2] | 447 | } |
---|
| 448 | $sSQL .= ')'; |
---|
| 449 | |
---|
| 450 | $oDB->query($sSQL); |
---|
| 451 | } |
---|
| 452 | |
---|
| 453 | return true; |
---|
| 454 | } |
---|
| 455 | |
---|
| 456 | function GetSequenceForTable($oProc,$table,&$sSequenceName) |
---|
| 457 | { |
---|
| 458 | if($GLOBALS['DEBUG']) { echo '<br>GetSequenceForTable: ' . $table; } |
---|
| 459 | |
---|
| 460 | $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__); |
---|
| 461 | $oProc->m_odb->next_record(); |
---|
| 462 | if($oProc->m_odb->f('relname')) |
---|
| 463 | { |
---|
| 464 | $sSequenceName = $oProc->m_odb->f('relname'); |
---|
| 465 | } |
---|
| 466 | return True; |
---|
| 467 | } |
---|
| 468 | |
---|
| 469 | function GetSequenceFieldForTable($oProc,$table,&$sField) |
---|
| 470 | { |
---|
| 471 | if($GLOBALS['DEBUG']) { echo '<br>GetSequenceFieldForTable: You rang?'; } |
---|
| 472 | |
---|
| 473 | $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"); |
---|
| 474 | $oProc->m_odb->next_record(); |
---|
| 475 | if($oProc->m_odb->f('attname')) |
---|
| 476 | { |
---|
| 477 | $sField = $oProc->m_odb->f('attname'); |
---|
| 478 | } |
---|
| 479 | return True; |
---|
| 480 | } |
---|
| 481 | |
---|
| 482 | function GetIndexesForTable($oProc,$table,&$sIndexNames) |
---|
| 483 | { |
---|
| 484 | $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 '%$table%idx' AND a.attrelid=c.oid AND d.adnum=a.attnum"); |
---|
| 485 | while($oProc->m_odb->next_record()) |
---|
| 486 | { |
---|
| 487 | $sIndexNames[] = $oProc->m_odb->f('attname'); |
---|
| 488 | } |
---|
| 489 | return True; |
---|
| 490 | } |
---|
| 491 | |
---|
| 492 | function DropSequenceForTable($oProc,$table) |
---|
| 493 | { |
---|
| 494 | if($GLOBALS['DEBUG']) { echo '<br>DropSequenceForTable: ' . $table; } |
---|
| 495 | |
---|
| 496 | $this->GetSequenceForTable($oProc,$table,$sSequenceName); |
---|
| 497 | if($sSequenceName) |
---|
| 498 | { |
---|
| 499 | $oProc->m_odb->query("DROP SEQUENCE " . $sSequenceName,__LINE__,__FILE__); |
---|
| 500 | } |
---|
| 501 | return True; |
---|
| 502 | } |
---|
| 503 | |
---|
| 504 | function DropIndexesForTable($oProc,$table) |
---|
| 505 | { |
---|
| 506 | if($GLOBALS['DEBUG']) { echo '<br>DropSequenceForTable: ' . $table; } |
---|
| 507 | |
---|
| 508 | $this->GetIndexesForTable($oProc,$table,$sIndexNames); |
---|
| 509 | if(@is_array($sIndexNames)) |
---|
| 510 | { |
---|
| 511 | foreach($sIndexNames as $null => $index) |
---|
| 512 | { |
---|
| 513 | $oProc->m_odb->query("DROP INDEX $index",__LINE__,__FILE__); |
---|
| 514 | } |
---|
| 515 | } |
---|
| 516 | return True; |
---|
| 517 | } |
---|
| 518 | |
---|
| 519 | function DropTable($oProc, &$aTables, $sTableName) |
---|
| 520 | { |
---|
| 521 | return $oProc->m_odb->query("DROP TABLE " . $sTableName) && |
---|
| 522 | $this->DropSequenceForTable($oProc, $sTableName) && |
---|
| 523 | $this->DropIndexesForTable($oProc, $sTableName); |
---|
| 524 | } |
---|
| 525 | |
---|
| 526 | function DropColumn($oProc, &$aTables, $sTableName, $aNewTableDef, $sColumnName, $bCopyData = true) |
---|
| 527 | { |
---|
| 528 | if($GLOBALS['DEBUG']) |
---|
| 529 | { |
---|
| 530 | echo '<br>DropColumn: table=' . $sTableName . ', column=' . $sColumnName; |
---|
| 531 | } |
---|
| 532 | |
---|
| 533 | if($bCopyData) |
---|
| 534 | { |
---|
| 535 | $oProc->m_odb->query("SELECT * INTO $sTableName" . "_tmp FROM $sTableName"); |
---|
| 536 | } |
---|
| 537 | |
---|
| 538 | $this->DropTable($oProc, $aTables, $sTableName); |
---|
| 539 | |
---|
| 540 | $oProc->_GetTableSQL($sTableName, $aNewTableDef, $sTableSQL, $sSequenceSQL,$append_ix); |
---|
| 541 | if($sSequenceSQL) |
---|
| 542 | { |
---|
| 543 | $oProc->m_odb->query($sSequenceSQL); |
---|
| 544 | } |
---|
| 545 | if($append_ix) |
---|
| 546 | { |
---|
| 547 | $query = "CREATE TABLE $sTableName ($sTableSQL"; |
---|
| 548 | } |
---|
| 549 | else |
---|
| 550 | { |
---|
| 551 | $query = "CREATE TABLE $sTableName ($sTableSQL)"; |
---|
| 552 | } |
---|
| 553 | if(!$bCopyData) |
---|
| 554 | { |
---|
| 555 | return !!($oProc->m_odb->query($query)); |
---|
| 556 | } |
---|
| 557 | |
---|
| 558 | $oProc->m_odb->query($query); |
---|
| 559 | $this->_GetColumns($oProc, $sTableName . '_tmp', $sColumns, $sColumnName); |
---|
| 560 | $query = "INSERT INTO $sTableName($sColumns) SELECT $sColumns FROM $sTableName" . '_tmp'; |
---|
| 561 | $bRet = !!($oProc->m_odb->query($query)); |
---|
| 562 | return ($bRet && $this->DropTable($oProc, $aTables, $sTableName . '_tmp')); |
---|
| 563 | } |
---|
| 564 | |
---|
| 565 | function RenameTable($oProc, &$aTables, $sOldTableName, $sNewTableName) |
---|
| 566 | { |
---|
| 567 | if($GLOBALS['DEBUG']) { echo '<br>RenameTable(): Fetching old sequence for: ' . $sOldTableName; } |
---|
| 568 | $this->GetSequenceForTable($oProc,$sOldTableName,$sSequenceName); |
---|
| 569 | |
---|
| 570 | if($GLOBALS['DEBUG']) { echo ' - ' . $sSequenceName; } |
---|
| 571 | |
---|
| 572 | if($GLOBALS['DEBUG']) { echo '<br>RenameTable(): Fetching sequence field for: ' . $sOldTableName; } |
---|
| 573 | $this->GetSequenceFieldForTable($oProc,$sOldTableName,$sField); |
---|
| 574 | |
---|
| 575 | if($GLOBALS['DEBUG']) { echo ' - ' . $sField; } |
---|
| 576 | |
---|
| 577 | if($sSequenceName) |
---|
| 578 | { |
---|
| 579 | $oProc->m_odb->query("SELECT last_value FROM seq_$sOldTableName",__LINE__,__FILE__); |
---|
| 580 | $oProc->m_odb->next_record(); |
---|
| 581 | $lastval = $oProc->m_odb->f(0); |
---|
| 582 | |
---|
| 583 | if($GLOBALS['DEBUG']) { echo '<br>RenameTable(): dropping old sequence: ' . $sSequenceName . ' used on field: ' . $sField; } |
---|
| 584 | $this->DropSequenceForTable($oProc,$sOldTableName); |
---|
| 585 | |
---|
| 586 | if($lastval) |
---|
| 587 | { |
---|
| 588 | $lastval = ' start ' . $lastval; |
---|
| 589 | } |
---|
| 590 | $this->GetSequenceSQL($sNewTableName,$sSequenceSQL); |
---|
| 591 | if($GLOBALS['DEBUG']) { echo '<br>RenameTable(): Making new sequence using: ' . $sSequenceSQL . $lastval; } |
---|
| 592 | $oProc->m_odb->query($sSequenceSQL . $lastval,__LINE__,__FILE__); |
---|
| 593 | if($GLOBALS['DEBUG']) { echo '<br>RenameTable(): Altering column default for: ' . $sField; } |
---|
| 594 | $oProc->m_odb->query("ALTER TABLE $sOldTableName ALTER $sField SET DEFAULT nextval('seq_" . $sNewTableName . "')",__LINE__,__FILE__); |
---|
| 595 | } |
---|
| 596 | // renameing existing indexes and primary keys |
---|
| 597 | $indexes = $oProc->m_odb->Link_ID->MetaIndexes($sOldTableName,True); |
---|
| 598 | if($GLOBALS['DEBUG']) { echo '<br>RenameTable(): Fetching indexes: '; _debug_array($indexes); } |
---|
| 599 | foreach($indexes as $name => $data) |
---|
| 600 | { |
---|
| 601 | $new_name = str_replace($sOldTableName,$sNewTableName,$name); |
---|
| 602 | $sql = "ALTER TABLE $name RENAME TO $new_name"; |
---|
| 603 | if($GLOBALS['DEBUG']) { echo "<br>RenameTable(): Renaming the index '$name': $sql"; } |
---|
| 604 | $oProc->m_odb->query($sql); |
---|
| 605 | } |
---|
| 606 | return !!($oProc->m_odb->query("ALTER TABLE $sOldTableName RENAME TO $sNewTableName")); |
---|
| 607 | } |
---|
| 608 | |
---|
| 609 | function RenameColumn($oProc, &$aTables, $sTableName, $sOldColumnName, $sNewColumnName, $bCopyData = true) |
---|
| 610 | { |
---|
| 611 | /* |
---|
| 612 | This really needs testing - it can affect primary keys, and other table-related objects |
---|
| 613 | like sequences and such |
---|
| 614 | */ |
---|
| 615 | if($bCopyData) |
---|
| 616 | { |
---|
| 617 | $oProc->m_odb->query("SELECT * INTO $sTableName" . "_tmp FROM $sTableName"); |
---|
| 618 | } |
---|
| 619 | |
---|
| 620 | $this->DropTable($oProc, $aTables, $sTableName); |
---|
| 621 | |
---|
| 622 | if(!$bCopyData) |
---|
| 623 | { |
---|
| 624 | return $this->CreateTable($oProc, $aTables, $sTableName, $oProc->m_aTables[$sTableName], false); |
---|
| 625 | } |
---|
| 626 | |
---|
| 627 | $this->CreateTable($oProc, $aTables, $sTableName, $aTables[$sTableName], True); |
---|
| 628 | $this->_GetColumns($oProc, $sTableName . "_tmp", $sColumns); |
---|
| 629 | $query = "INSERT INTO $sTableName SELECT $sColumns FROM $sTableName" . "_tmp"; |
---|
| 630 | |
---|
| 631 | $bRet = !!($oProc->m_odb->query($query)); |
---|
| 632 | return ($bRet && $this->DropTable($oProc, $aTables, $sTableName . "_tmp")); |
---|
| 633 | } |
---|
| 634 | |
---|
| 635 | function AlterColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef, $bCopyData = true) |
---|
| 636 | { |
---|
| 637 | if($bCopyData) |
---|
| 638 | { |
---|
| 639 | $oProc->m_odb->query("SELECT * INTO $sTableName" . "_tmp FROM $sTableName"); |
---|
| 640 | } |
---|
| 641 | |
---|
| 642 | $this->DropTable($oProc, $aTables, $sTableName); |
---|
| 643 | |
---|
| 644 | if(!$bCopyData) |
---|
| 645 | { |
---|
| 646 | return $this->CreateTable($oProc, $aTables, $sTableName, $aTables[$sTableName], True); |
---|
| 647 | } |
---|
| 648 | |
---|
| 649 | $this->CreateTable($oProc, $aTables, $sTableName, $aTables[$sTableName], True); |
---|
| 650 | $this->_GetColumns($oProc, $sTableName . "_tmp", $sColumns, '', $sColumnName, $aColumnDef['type'] == 'auto' ? 'int4' : $aColumnDef['type']); |
---|
| 651 | |
---|
| 652 | /* |
---|
| 653 | TODO: analyze the type of change and determine if this is used or _CopyAlteredTable |
---|
| 654 | this is a performance consideration only, _CopyAlteredTable should be safe |
---|
| 655 | $query = "INSERT INTO $sTableName SELECT $sColumns FROM $sTableName" . "_tmp"; |
---|
| 656 | $bRet = !!($oProc->m_odb->query($query)); |
---|
| 657 | */ |
---|
| 658 | |
---|
| 659 | $bRet = $this->_CopyAlteredTable($oProc, $aTables, $sTableName . '_tmp', $sTableName); |
---|
| 660 | |
---|
| 661 | return ($bRet && $this->DropTable($oProc, $aTables, $sTableName . "_tmp")); |
---|
| 662 | } |
---|
| 663 | |
---|
| 664 | function AddColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef) |
---|
| 665 | { |
---|
| 666 | if(isset($aColumnDef['default'])) // pgsql cant add a colum with a default |
---|
| 667 | { |
---|
| 668 | $default = $aColumnDef['default']; |
---|
| 669 | unset($aColumnDef['default']); |
---|
| 670 | } |
---|
| 671 | if(isset($aColumnDef['nullable']) && !$aColumnDef['nullable']) // pgsql cant add a column not nullable |
---|
| 672 | { |
---|
| 673 | $notnull = !$aColumnDef['nullable']; |
---|
| 674 | unset($aColumnDef['nullable']); |
---|
| 675 | } |
---|
| 676 | $oProc->_GetFieldSQL($aColumnDef, $sFieldSQL); |
---|
| 677 | $query = "ALTER TABLE $sTableName ADD COLUMN $sColumnName $sFieldSQL"; |
---|
| 678 | |
---|
| 679 | if(($Ok = !!($oProc->m_odb->query($query))) && isset($default)) |
---|
| 680 | { |
---|
| 681 | $query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET DEFAULT '$default';\n"; |
---|
| 682 | |
---|
| 683 | $query .= "UPDATE $sTableName SET $sColumnName='$default';\n"; |
---|
| 684 | |
---|
| 685 | $Ok = !!($oProc->m_odb->query($query)); |
---|
| 686 | |
---|
| 687 | if($OK && $notnull) |
---|
| 688 | { |
---|
| 689 | // unfortunally this is pgSQL >= 7.3 |
---|
| 690 | //$query .= "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET NOT NULL;\n"; |
---|
| 691 | //$Ok = !!($oProc->m_odb->query($query)); |
---|
| 692 | // so we do it the slow way |
---|
| 693 | AlterColumn($oProc, $aTables, $sTableName, $sColumnName, $aColumnDef); |
---|
| 694 | } |
---|
| 695 | } |
---|
| 696 | return $Ok; |
---|
| 697 | } |
---|
| 698 | |
---|
| 699 | function UpdateSequence($oDb,$sTableName,$sColName) |
---|
| 700 | { |
---|
| 701 | $sql = "SELECT MAX($sColName) FROM $sTableName"; |
---|
| 702 | |
---|
| 703 | $oDb->query($sql,__LINE__,__FILE__); |
---|
| 704 | if ($oDb->next_record() && $oDb->f(0)) |
---|
| 705 | { |
---|
| 706 | $sql = "SELECT setval('seq_$sTableName',".(1 + $oDb->f(0)).")"; |
---|
| 707 | if($GLOBALS['DEBUG']) { echo "<br>Updating sequence 'seq_$sTableName' using: $sql"; } |
---|
| 708 | return $oDb->query($sql,__LINE__,__FILE__); |
---|
| 709 | } |
---|
| 710 | return True; |
---|
| 711 | } |
---|
| 712 | |
---|
| 713 | function GetSequenceSQL($sTableName, &$sSequenceSQL) |
---|
| 714 | { |
---|
| 715 | $sSequenceSQL = sprintf("CREATE SEQUENCE seq_%s", $sTableName); |
---|
| 716 | return true; |
---|
| 717 | } |
---|
| 718 | |
---|
| 719 | function CreateTable($oProc, $aTables, $sTableName, $aTableDef, $bCreateSequence = true) |
---|
| 720 | { |
---|
| 721 | if($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL,$append_ix)) |
---|
| 722 | { |
---|
| 723 | /* create sequence first since it will be needed for default */ |
---|
| 724 | if($bCreateSequence && $sSequenceSQL != '') |
---|
| 725 | { |
---|
| 726 | if($GLOBALS['DEBUG']) { echo '<br>Making sequence using: ' . $sSequenceSQL; } |
---|
| 727 | $oProc->m_odb->query($sSequenceSQL); |
---|
| 728 | } |
---|
| 729 | |
---|
| 730 | if($append_ix) |
---|
| 731 | { |
---|
| 732 | $query = "CREATE TABLE $sTableName ($sTableSQL"; |
---|
| 733 | } |
---|
| 734 | else |
---|
| 735 | { |
---|
| 736 | $query = "CREATE TABLE $sTableName ($sTableSQL)"; |
---|
| 737 | } |
---|
| 738 | |
---|
| 739 | return !!($oProc->m_odb->query($query)); |
---|
| 740 | } |
---|
| 741 | |
---|
| 742 | return false; |
---|
| 743 | } |
---|
| 744 | } |
---|
| 745 | ?> |
---|