function findObject($term, $filter)
{
global $conf;
/*about escaping:
* SET standard_conforming_string is not available before 8.2
* So we must use PostgreSQL specific notation :/
* E'' notation is not available before 8.1
* $$ is available since 8.0
* Nothing specific from 7.4
**/
// Escape search term for ILIKE match
$this->clean($term);
$this->clean($filter);
$term = str_replace('_', '\\_', $term);
$term = str_replace('%', '\\%', $term);
// Exclude system relations if necessary
if (!$conf['show_system']) {
// XXX: The mention of information_schema here is in the wrong place, but
// it's the quickest fix to exclude the info schema from 7.4
$where = " AND pn.nspname NOT LIKE \$_PATERN_\$pg\\_%\$_PATERN_\$ AND pn.nspname != 'information_schema'";
$lan_where = "AND pl.lanispl";
} else {
$where = '';
$lan_where = '';
}
// Apply outer filter
$sql = '';
if ($filter != '') {
$sql = "SELECT * FROM (";
}
$term = "\$_PATERN_\$%{$term}%\$_PATERN_\$";
$sql .= "\n\t\t\tSELECT 'SCHEMA' AS type, oid, NULL AS schemaname, NULL AS relname, nspname AS name\n\t\t\t\tFROM pg_catalog.pg_namespace pn WHERE nspname ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT CASE WHEN relkind='r' THEN 'TABLE' WHEN relkind='v' THEN 'VIEW' WHEN relkind='S' THEN 'SEQUENCE' END, pc.oid,\n\t\t\t\tpn.nspname, NULL, pc.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn\n\t\t\t\tWHERE pc.relnamespace=pn.oid AND relkind IN ('r', 'v', 'S') AND relname ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT CASE WHEN pc.relkind='r' THEN 'COLUMNTABLE' ELSE 'COLUMNVIEW' END, NULL, pn.nspname, pc.relname, pa.attname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn,\n\t\t\t\tpg_catalog.pg_attribute pa WHERE pc.relnamespace=pn.oid AND pc.oid=pa.attrelid\n\t\t\t\tAND pa.attname ILIKE {$term} AND pa.attnum > 0 AND NOT pa.attisdropped AND pc.relkind IN ('r', 'v') {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT 'FUNCTION', pp.oid, pn.nspname, NULL, pp.proname || '(' || pg_catalog.oidvectortypes(pp.proargtypes) || ')' FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pn\n\t\t\t\tWHERE pp.pronamespace=pn.oid AND NOT pp.proisagg AND pp.proname ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT 'INDEX', NULL, pn.nspname, pc.relname, pc2.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn,\n\t\t\t\tpg_catalog.pg_index pi, pg_catalog.pg_class pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pi.indrelid\n\t\t\t\tAND pi.indexrelid=pc2.oid\n\t\t\t\tAND NOT EXISTS (\n\t\t\t\t\tSELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c\n\t\t\t\t\tON (d.refclassid = c.tableoid AND d.refobjid = c.oid)\n\t\t\t\t\tWHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')\n\t\t\t\t)\n\t\t\t\tAND pc2.relname ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT 'CONSTRAINTTABLE', NULL, pn.nspname, pc.relname, pc2.conname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn,\n\t\t\t\tpg_catalog.pg_constraint pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pc2.conrelid AND pc2.conrelid != 0\n\t\t\t\tAND CASE WHEN pc2.contype IN ('f', 'c') THEN TRUE ELSE NOT EXISTS (\n\t\t\t\t\tSELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c\n\t\t\t\t\tON (d.refclassid = c.tableoid AND d.refobjid = c.oid)\n\t\t\t\t\tWHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')\n\t\t\t\t) END\n\t\t\t\tAND pc2.conname ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT 'CONSTRAINTDOMAIN', pt.oid, pn.nspname, pt.typname, pc.conname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn,\n\t\t\t\tpg_catalog.pg_constraint pc WHERE pt.typnamespace=pn.oid AND pt.oid=pc.contypid AND pc.contypid != 0\n\t\t\t\tAND pc.conname ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT 'TRIGGER', NULL, pn.nspname, pc.relname, pt.tgname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn,\n\t\t\t\tpg_catalog.pg_trigger pt WHERE pc.relnamespace=pn.oid AND pc.oid=pt.tgrelid\n\t\t\t\t\tAND ( pt.tgconstraint = 0 OR NOT EXISTS\n\t\t\t\t\t(SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c\n\t\t\t\t\tON (d.refclassid = c.tableoid AND d.refobjid = c.oid)\n\t\t\t\t\tWHERE d.classid = pt.tableoid AND d.objid = pt.oid AND d.deptype = 'i' AND c.contype = 'f'))\n\t\t\t\tAND pt.tgname ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT 'RULETABLE', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r\n\t\t\t\tJOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n\t\t\t\tLEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace\n\t\t\t\tWHERE c.relkind='r' AND r.rulename != '_RETURN' AND r.rulename ILIKE {$term} {$where}\n\t\t\tUNION ALL\n\t\t\tSELECT 'RULEVIEW', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r\n\t\t\t\tJOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n\t\t\t\tLEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace\n\t\t\t\tWHERE c.relkind='v' AND r.rulename != '_RETURN' AND r.rulename ILIKE {$term} {$where}\n\t\t";
// Add advanced objects if show_advanced is set
if ($conf['show_advanced']) {
$sql .= "\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT CASE WHEN pt.typtype='d' THEN 'DOMAIN' ELSE 'TYPE' END, pt.oid, pn.nspname, NULL,\n\t\t\t\t\tpt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn\n\t\t\t\t\tWHERE pt.typnamespace=pn.oid AND typname ILIKE {$term}\n\t\t\t\t\tAND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid))\n\t\t\t\t\t{$where}\n\t\t\t \tUNION ALL\n\t\t\t\tSELECT 'OPERATOR', po.oid, pn.nspname, NULL, po.oprname FROM pg_catalog.pg_operator po, pg_catalog.pg_namespace pn\n\t\t\t\t\tWHERE po.oprnamespace=pn.oid AND oprname ILIKE {$term} {$where}\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT 'CONVERSION', pc.oid, pn.nspname, NULL, pc.conname FROM pg_catalog.pg_conversion pc,\n\t\t\t\t\tpg_catalog.pg_namespace pn WHERE pc.connamespace=pn.oid AND conname ILIKE {$term} {$where}\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT 'LANGUAGE', pl.oid, NULL, NULL, pl.lanname FROM pg_catalog.pg_language pl\n\t\t\t\t\tWHERE lanname ILIKE {$term} {$lan_where}\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT DISTINCT ON (p.proname) 'AGGREGATE', p.oid, pn.nspname, NULL, p.proname FROM pg_catalog.pg_proc p\n\t\t\t\t\tLEFT JOIN pg_catalog.pg_namespace pn ON p.pronamespace=pn.oid\n\t\t\t\t\tWHERE p.proisagg AND p.proname ILIKE {$term} {$where}\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT DISTINCT ON (po.opcname) 'OPCLASS', po.oid, pn.nspname, NULL, po.opcname FROM pg_catalog.pg_opclass po,\n\t\t\t\t\tpg_catalog.pg_namespace pn WHERE po.opcnamespace=pn.oid\n\t\t\t\t\tAND po.opcname ILIKE {$term} {$where}\n\t\t\t";
} else {
$sql .= "\n\t\t\t\tUNION ALL\n\t\t\t\tSELECT 'DOMAIN', pt.oid, pn.nspname, NULL,\n\t\t\t\t\tpt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn\n\t\t\t\t\tWHERE pt.typnamespace=pn.oid AND pt.typtype='d' AND typname ILIKE {$term}\n\t\t\t\t\tAND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid))\n\t\t\t\t\t{$where}\n\t\t\t";
}
if ($filter != '') {
// We use like to make RULE, CONSTRAINT and COLUMN searches work
$sql .= ") AS sub WHERE type LIKE '{$filter}%' ";
}
$sql .= "ORDER BY type, schemaname, relname, name";
return $this->selectSet($sql);
}
