HuasoFoundries /
phpPgAdmin6
| @@ 199-253 (lines=55) @@ | ||
| 196 | * |
|
| 197 | * @return \PHPPgAdmin\ADORecordSet A recordset |
|
| 198 | */ |
|
| 199 | public function getConstraintsWithFields($table) |
|
| 200 | { |
|
| 201 | $c_schema = $this->_schema; |
|
| 202 | $this->clean($c_schema); |
|
| 203 | $this->clean($table); |
|
| 204 | ||
| 205 | // get the max number of col used in a constraint for the table |
|
| 206 | $sql = "SELECT DISTINCT |
|
| 207 | max(SUBSTRING(array_dims(c.conkey) FROM \$patern\$^\\[.*:(.*)\\]$\$patern\$)) as nb |
|
| 208 | FROM pg_catalog.pg_constraint AS c |
|
| 209 | JOIN pg_catalog.pg_class AS r ON (c.conrelid=r.oid) |
|
| 210 | JOIN pg_catalog.pg_namespace AS ns ON (r.relnamespace=ns.oid) |
|
| 211 | WHERE |
|
| 212 | r.relname = '{$table}' AND ns.nspname='{$c_schema}'"; |
|
| 213 | ||
| 214 | $rs = $this->selectSet($sql); |
|
| 215 | ||
| 216 | if ($rs->EOF) { |
|
| 217 | $max_col = 0; |
|
| 218 | } else { |
|
| 219 | $max_col = $rs->fields['nb']; |
|
| 220 | } |
|
| 221 | ||
| 222 | $sql = ' |
|
| 223 | SELECT |
|
| 224 | c.oid AS conid, c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc, |
|
| 225 | ns1.nspname as p_schema, r1.relname as p_table, ns2.nspname as f_schema, |
|
| 226 | r2.relname as f_table, f1.attname as p_field, f1.attnum AS p_attnum, f2.attname as f_field, |
|
| 227 | f2.attnum AS f_attnum, pg_catalog.obj_description(c.oid, \'pg_constraint\') AS constcomment, |
|
| 228 | c.conrelid, c.confrelid |
|
| 229 | FROM |
|
| 230 | pg_catalog.pg_constraint AS c |
|
| 231 | JOIN pg_catalog.pg_class AS r1 ON (c.conrelid=r1.oid) |
|
| 232 | JOIN pg_catalog.pg_attribute AS f1 ON (f1.attrelid=r1.oid AND (f1.attnum=c.conkey[1]'; |
|
| 233 | for ($i = 2; $i <= $rs->fields['nb']; ++$i) { |
|
| 234 | $sql .= " OR f1.attnum=c.conkey[${i}]"; |
|
| 235 | } |
|
| 236 | $sql .= ')) |
|
| 237 | JOIN pg_catalog.pg_namespace AS ns1 ON r1.relnamespace=ns1.oid |
|
| 238 | LEFT JOIN ( |
|
| 239 | pg_catalog.pg_class AS r2 JOIN pg_catalog.pg_namespace AS ns2 ON (r2.relnamespace=ns2.oid) |
|
| 240 | ) ON (c.confrelid=r2.oid) |
|
| 241 | LEFT JOIN pg_catalog.pg_attribute AS f2 ON |
|
| 242 | (f2.attrelid=r2.oid AND ((c.confkey[1]=f2.attnum AND c.conkey[1]=f1.attnum)'; |
|
| 243 | for ($i = 2; $i <= $rs->fields['nb']; ++$i) { |
|
| 244 | $sql .= " OR (c.confkey[${i}]=f2.attnum AND c.conkey[${i}]=f1.attnum)"; |
|
| 245 | } |
|
| 246 | ||
| 247 | $sql .= sprintf(")) |
|
| 248 | WHERE |
|
| 249 | r1.relname = '%s' AND ns1.nspname='%s' |
|
| 250 | ORDER BY 1", $table, $c_schema); |
|
| 251 | ||
| 252 | return $this->selectSet($sql); |
|
| 253 | } |
|
| 254 | ||
| 255 | /** |
|
| 256 | * Adds a primary key constraint to a table. |
|
| @@ 428-482 (lines=55) @@ | ||
| 425 | * |
|
| 426 | * @return \PHPPgAdmin\ADORecordSet A recordset |
|
| 427 | */ |
|
| 428 | public function getConstraintsWithFields($table) |
|
| 429 | { |
|
| 430 | $c_schema = $this->_schema; |
|
| 431 | $this->clean($c_schema); |
|
| 432 | $this->clean($table); |
|
| 433 | ||
| 434 | // get the max number of col used in a constraint for the table |
|
| 435 | $sql = "SELECT DISTINCT |
|
| 436 | max(SUBSTRING(array_dims(c.conkey) FROM '^\\\\[.*:(.*)\\\\]$')) as nb |
|
| 437 | FROM pg_catalog.pg_constraint AS c |
|
| 438 | JOIN pg_catalog.pg_class AS r ON (c.conrelid=r.oid) |
|
| 439 | JOIN pg_catalog.pg_namespace AS ns ON (r.relnamespace=ns.oid) |
|
| 440 | WHERE |
|
| 441 | r.relname = '{$table}' AND ns.nspname='{$c_schema}'"; |
|
| 442 | ||
| 443 | $rs = $this->selectSet($sql); |
|
| 444 | ||
| 445 | if ($rs->EOF) { |
|
| 446 | $max_col = 0; |
|
| 447 | } else { |
|
| 448 | $max_col = $rs->fields['nb']; |
|
| 449 | } |
|
| 450 | ||
| 451 | $sql = ' |
|
| 452 | SELECT |
|
| 453 | c.oid AS conid, c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc, |
|
| 454 | ns1.nspname as p_schema, r1.relname as p_table, ns2.nspname as f_schema, |
|
| 455 | r2.relname as f_table, f1.attname as p_field, f1.attnum AS p_attnum, f2.attname as f_field, |
|
| 456 | f2.attnum AS f_attnum, pg_catalog.obj_description(c.oid, \'pg_constraint\') AS constcomment, |
|
| 457 | c.conrelid, c.confrelid |
|
| 458 | FROM |
|
| 459 | pg_catalog.pg_constraint AS c |
|
| 460 | JOIN pg_catalog.pg_class AS r1 ON (c.conrelid=r1.oid) |
|
| 461 | JOIN pg_catalog.pg_attribute AS f1 ON (f1.attrelid=r1.oid AND (f1.attnum=c.conkey[1]'; |
|
| 462 | for ($i = 2; $i <= $rs->fields['nb']; ++$i) { |
|
| 463 | $sql .= " OR f1.attnum=c.conkey[${i}]"; |
|
| 464 | } |
|
| 465 | $sql .= ')) |
|
| 466 | JOIN pg_catalog.pg_namespace AS ns1 ON r1.relnamespace=ns1.oid |
|
| 467 | LEFT JOIN ( |
|
| 468 | pg_catalog.pg_class AS r2 JOIN pg_catalog.pg_namespace AS ns2 ON (r2.relnamespace=ns2.oid) |
|
| 469 | ) ON (c.confrelid=r2.oid) |
|
| 470 | LEFT JOIN pg_catalog.pg_attribute AS f2 ON |
|
| 471 | (f2.attrelid=r2.oid AND ((c.confkey[1]=f2.attnum AND c.conkey[1]=f1.attnum)'; |
|
| 472 | for ($i = 2; $i <= $rs->fields['nb']; ++$i) { |
|
| 473 | $sql .= " OR (c.confkey[${i}]=f2.attnum AND c.conkey[${i}]=f1.attnum)"; |
|
| 474 | } |
|
| 475 | ||
| 476 | $sql .= sprintf(")) |
|
| 477 | WHERE |
|
| 478 | r1.relname = '%s' AND ns1.nspname='%s' |
|
| 479 | ORDER BY 1", $table, $c_schema); |
|
| 480 | ||
| 481 | return $this->selectSet($sql); |
|
| 482 | } |
|
| 483 | ||
| 484 | // Constraint functions |
|
| 485 | ||
