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(); |
---|
380 | $indexfields = preg_replace('/^CREATE.+\(/','',$oProc->m_odb->f($num)); |
---|
381 | $indexfields = preg_replace('/\)$/','',$indexfields); |
---|
382 | $aIx = explode(',',$indexfields); |
---|
383 | $i = 0; |
---|
384 | foreach($aIx as $ix) |
---|
385 | { |
---|
386 | $aIx[$i] = trim($ix); |
---|
387 | ++$i; |
---|
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; |
---|
411 | ++$i; |
---|
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 | } |
---|
446 | ++$i; |
---|
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 | ?> |
---|