HuasoFoundries /
phpPgAdmin6
| @@ 4546-4601 (lines=56) @@ | ||
| 4543 | * @param $table the table where we are looking for fk |
|
| 4544 | * @return a recordset |
|
| 4545 | */ |
|
| 4546 | public function getConstraintsWithFields($table) |
|
| 4547 | { |
|
| 4548 | $c_schema = $this->_schema; |
|
| 4549 | $this->clean($c_schema); |
|
| 4550 | $this->clean($table); |
|
| 4551 | ||
| 4552 | // get the max number of col used in a constraint for the table |
|
| 4553 | $sql = "SELECT DISTINCT |
|
| 4554 | max(SUBSTRING(array_dims(c.conkey) FROM \$patern\$^\\[.*:(.*)\\]$\$patern\$)) as nb |
|
| 4555 | FROM pg_catalog.pg_constraint AS c |
|
| 4556 | JOIN pg_catalog.pg_class AS r ON (c.conrelid=r.oid) |
|
| 4557 | JOIN pg_catalog.pg_namespace AS ns ON (r.relnamespace=ns.oid) |
|
| 4558 | WHERE |
|
| 4559 | r.relname = '{$table}' AND ns.nspname='{$c_schema}'"; |
|
| 4560 | ||
| 4561 | $rs = $this->selectSet($sql); |
|
| 4562 | ||
| 4563 | if ($rs->EOF) { |
|
| 4564 | $max_col = 0; |
|
| 4565 | } else { |
|
| 4566 | $max_col = $rs->fields['nb']; |
|
| 4567 | } |
|
| 4568 | ||
| 4569 | $sql = ' |
|
| 4570 | SELECT |
|
| 4571 | c.oid AS conid, c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc, |
|
| 4572 | ns1.nspname as p_schema, r1.relname as p_table, ns2.nspname as f_schema, |
|
| 4573 | r2.relname as f_table, f1.attname as p_field, f1.attnum AS p_attnum, f2.attname as f_field, |
|
| 4574 | f2.attnum AS f_attnum, pg_catalog.obj_description(c.oid, \'pg_constraint\') AS constcomment, |
|
| 4575 | c.conrelid, c.confrelid |
|
| 4576 | FROM |
|
| 4577 | pg_catalog.pg_constraint AS c |
|
| 4578 | JOIN pg_catalog.pg_class AS r1 ON (c.conrelid=r1.oid) |
|
| 4579 | JOIN pg_catalog.pg_attribute AS f1 ON (f1.attrelid=r1.oid AND (f1.attnum=c.conkey[1]'; |
|
| 4580 | for ($i = 2; $i <= $rs->fields['nb']; $i++) { |
|
| 4581 | $sql .= " OR f1.attnum=c.conkey[$i]"; |
|
| 4582 | } |
|
| 4583 | $sql .= ')) |
|
| 4584 | JOIN pg_catalog.pg_namespace AS ns1 ON r1.relnamespace=ns1.oid |
|
| 4585 | LEFT JOIN ( |
|
| 4586 | pg_catalog.pg_class AS r2 JOIN pg_catalog.pg_namespace AS ns2 ON (r2.relnamespace=ns2.oid) |
|
| 4587 | ) ON (c.confrelid=r2.oid) |
|
| 4588 | LEFT JOIN pg_catalog.pg_attribute AS f2 ON |
|
| 4589 | (f2.attrelid=r2.oid AND ((c.confkey[1]=f2.attnum AND c.conkey[1]=f1.attnum)'; |
|
| 4590 | for ($i = 2; $i <= $rs->fields['nb']; $i++) { |
|
| 4591 | $sql .= " OR (c.confkey[$i]=f2.attnum AND c.conkey[$i]=f1.attnum)"; |
|
| 4592 | } |
|
| 4593 | ||
| 4594 | $sql .= sprintf(")) |
|
| 4595 | WHERE |
|
| 4596 | r1.relname = '%s' AND ns1.nspname='%s' |
|
| 4597 | ORDER BY 1", $table, $c_schema); |
|
| 4598 | ||
| 4599 | return $this->selectSet($sql); |
|
| 4600 | } |
|
| 4601 | ||
| 4602 | /** |
|
| 4603 | * Adds a primary key constraint to a table |
|
| 4604 | * |
|
| @@ 443-498 (lines=56) @@ | ||
| 440 | * @param $table the table where we are looking for fk |
|
| 441 | * @return a recordset |
|
| 442 | */ |
|
| 443 | public function getConstraintsWithFields($table) |
|
| 444 | { |
|
| 445 | $c_schema = $this->_schema; |
|
| 446 | $this->clean($c_schema); |
|
| 447 | $this->clean($table); |
|
| 448 | ||
| 449 | // get the max number of col used in a constraint for the table |
|
| 450 | $sql = "SELECT DISTINCT |
|
| 451 | max(SUBSTRING(array_dims(c.conkey) FROM '^\\\\[.*:(.*)\\\\]$')) as nb |
|
| 452 | FROM pg_catalog.pg_constraint AS c |
|
| 453 | JOIN pg_catalog.pg_class AS r ON (c.conrelid=r.oid) |
|
| 454 | JOIN pg_catalog.pg_namespace AS ns ON (r.relnamespace=ns.oid) |
|
| 455 | WHERE |
|
| 456 | r.relname = '{$table}' AND ns.nspname='{$c_schema}'"; |
|
| 457 | ||
| 458 | $rs = $this->selectSet($sql); |
|
| 459 | ||
| 460 | if ($rs->EOF) { |
|
| 461 | $max_col = 0; |
|
| 462 | } else { |
|
| 463 | $max_col = $rs->fields['nb']; |
|
| 464 | } |
|
| 465 | ||
| 466 | $sql = ' |
|
| 467 | SELECT |
|
| 468 | c.oid AS conid, c.contype, c.conname, pg_catalog.pg_get_constraintdef(c.oid, true) AS consrc, |
|
| 469 | ns1.nspname as p_schema, r1.relname as p_table, ns2.nspname as f_schema, |
|
| 470 | r2.relname as f_table, f1.attname as p_field, f1.attnum AS p_attnum, f2.attname as f_field, |
|
| 471 | f2.attnum AS f_attnum, pg_catalog.obj_description(c.oid, \'pg_constraint\') AS constcomment, |
|
| 472 | c.conrelid, c.confrelid |
|
| 473 | FROM |
|
| 474 | pg_catalog.pg_constraint AS c |
|
| 475 | JOIN pg_catalog.pg_class AS r1 ON (c.conrelid=r1.oid) |
|
| 476 | JOIN pg_catalog.pg_attribute AS f1 ON (f1.attrelid=r1.oid AND (f1.attnum=c.conkey[1]'; |
|
| 477 | for ($i = 2; $i <= $rs->fields['nb']; $i++) { |
|
| 478 | $sql .= " OR f1.attnum=c.conkey[$i]"; |
|
| 479 | } |
|
| 480 | $sql .= ')) |
|
| 481 | JOIN pg_catalog.pg_namespace AS ns1 ON r1.relnamespace=ns1.oid |
|
| 482 | LEFT JOIN ( |
|
| 483 | pg_catalog.pg_class AS r2 JOIN pg_catalog.pg_namespace AS ns2 ON (r2.relnamespace=ns2.oid) |
|
| 484 | ) ON (c.confrelid=r2.oid) |
|
| 485 | LEFT JOIN pg_catalog.pg_attribute AS f2 ON |
|
| 486 | (f2.attrelid=r2.oid AND ((c.confkey[1]=f2.attnum AND c.conkey[1]=f1.attnum)'; |
|
| 487 | for ($i = 2; $i <= $rs->fields['nb']; $i++) { |
|
| 488 | $sql .= " OR (c.confkey[$i]=f2.attnum AND c.conkey[$i]=f1.attnum)"; |
|
| 489 | } |
|
| 490 | ||
| 491 | $sql .= sprintf(")) |
|
| 492 | WHERE |
|
| 493 | r1.relname = '%s' AND ns1.nspname='%s' |
|
| 494 | ORDER BY 1", $table, $c_schema); |
|
| 495 | ||
| 496 | return $this->selectSet($sql); |
|
| 497 | } |
|
| 498 | ||
| 499 | // Constraint functions |
|
| 500 | ||
| 501 | /** |
|
