HuasoFoundries /
phpPgAdmin6
| @@ 69-210 (lines=142) @@ | ||
| 66 | * |
|
| 67 | * @return A recordset |
|
| 68 | */ |
|
| 69 | public function findObject($term, $filter) |
|
| 70 | { |
|
| 71 | $conf = $this->conf; |
|
| 72 | ||
| 73 | /*about escaping: |
|
| 74 | * SET standard_conforming_string is not available before 8.2 |
|
| 75 | * So we must use PostgreSQL specific notation :/ |
|
| 76 | * E'' notation is not available before 8.1 |
|
| 77 | * $$ is available since 8.0 |
|
| 78 | * Nothing specific from 7.4 |
|
| 79 | */ |
|
| 80 | ||
| 81 | // Escape search term for ILIKE match |
|
| 82 | $this->clean($term); |
|
| 83 | $this->clean($filter); |
|
| 84 | $term = str_replace('_', '\_', $term); |
|
| 85 | $term = str_replace('%', '\%', $term); |
|
| 86 | ||
| 87 | // Exclude system relations if necessary |
|
| 88 | if (!$conf['show_system']) { |
|
| 89 | // XXX: The mention of information_schema here is in the wrong place, but |
|
| 90 | // it's the quickest fix to exclude the info schema from 7.4 |
|
| 91 | $where = " AND pn.nspname NOT LIKE \$_PATERN_\$pg\_%\$_PATERN_\$ AND pn.nspname != 'information_schema'"; |
|
| 92 | $lan_where = 'AND pl.lanispl'; |
|
| 93 | } else { |
|
| 94 | $where = ''; |
|
| 95 | $lan_where = ''; |
|
| 96 | } |
|
| 97 | ||
| 98 | // Apply outer filter |
|
| 99 | $sql = ''; |
|
| 100 | if ($filter != '') { |
|
| 101 | $sql = 'SELECT * FROM ('; |
|
| 102 | } |
|
| 103 | ||
| 104 | $term = "\$_PATERN_\$%{$term}%\$_PATERN_\$"; |
|
| 105 | ||
| 106 | $sql .= " |
|
| 107 | SELECT 'SCHEMA' AS type, oid, NULL AS schemaname, NULL AS relname, nspname AS name |
|
| 108 | FROM pg_catalog.pg_namespace pn WHERE nspname ILIKE {$term} {$where} |
|
| 109 | UNION ALL |
|
| 110 | SELECT CASE WHEN relkind='r' THEN 'TABLE' WHEN relkind='v' THEN 'VIEW' WHEN relkind='S' THEN 'SEQUENCE' END, pc.oid, |
|
| 111 | pn.nspname, NULL, pc.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn |
|
| 112 | WHERE pc.relnamespace=pn.oid AND relkind IN ('r', 'v', 'S') AND relname ILIKE {$term} {$where} |
|
| 113 | UNION ALL |
|
| 114 | SELECT 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, |
|
| 115 | pg_catalog.pg_attribute pa WHERE pc.relnamespace=pn.oid AND pc.oid=pa.attrelid |
|
| 116 | AND pa.attname ILIKE {$term} AND pa.attnum > 0 AND NOT pa.attisdropped AND pc.relkind IN ('r', 'v') {$where} |
|
| 117 | UNION ALL |
|
| 118 | SELECT 'FUNCTION', pp.oid, pn.nspname, NULL, pp.proname || '(' || pg_catalog.oidvectortypes(pp.proargtypes) || ')' FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pn |
|
| 119 | WHERE pp.pronamespace=pn.oid AND NOT pp.proisagg AND pp.proname ILIKE {$term} {$where} |
|
| 120 | UNION ALL |
|
| 121 | SELECT 'INDEX', NULL, pn.nspname, pc.relname, pc2.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 122 | pg_catalog.pg_index pi, pg_catalog.pg_class pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pi.indrelid |
|
| 123 | AND pi.indexrelid=pc2.oid |
|
| 124 | AND NOT EXISTS ( |
|
| 125 | SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 126 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 127 | WHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') |
|
| 128 | ) |
|
| 129 | AND pc2.relname ILIKE {$term} {$where} |
|
| 130 | UNION ALL |
|
| 131 | SELECT 'CONSTRAINTTABLE', NULL, pn.nspname, pc.relname, pc2.conname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 132 | pg_catalog.pg_constraint pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pc2.conrelid AND pc2.conrelid != 0 |
|
| 133 | AND CASE WHEN pc2.contype IN ('f', 'c') THEN TRUE ELSE NOT EXISTS ( |
|
| 134 | SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 135 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 136 | WHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') |
|
| 137 | ) END |
|
| 138 | AND pc2.conname ILIKE {$term} {$where} |
|
| 139 | UNION ALL |
|
| 140 | SELECT 'CONSTRAINTDOMAIN', pt.oid, pn.nspname, pt.typname, pc.conname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn, |
|
| 141 | pg_catalog.pg_constraint pc WHERE pt.typnamespace=pn.oid AND pt.oid=pc.contypid AND pc.contypid != 0 |
|
| 142 | AND pc.conname ILIKE {$term} {$where} |
|
| 143 | UNION ALL |
|
| 144 | SELECT 'TRIGGER', NULL, pn.nspname, pc.relname, pt.tgname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 145 | pg_catalog.pg_trigger pt WHERE pc.relnamespace=pn.oid AND pc.oid=pt.tgrelid |
|
| 146 | AND ( NOT pt.tgisconstraint OR NOT EXISTS |
|
| 147 | (SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 148 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 149 | WHERE d.classid = pt.tableoid AND d.objid = pt.oid AND d.deptype = 'i' AND c.contype = 'f')) |
|
| 150 | AND pt.tgname ILIKE {$term} {$where} |
|
| 151 | UNION ALL |
|
| 152 | SELECT 'RULETABLE', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r |
|
| 153 | JOIN pg_catalog.pg_class c ON c.oid = r.ev_class |
|
| 154 | LEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace |
|
| 155 | WHERE c.relkind='r' AND r.rulename != '_RETURN' AND r.rulename ILIKE {$term} {$where} |
|
| 156 | UNION ALL |
|
| 157 | SELECT 'RULEVIEW', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r |
|
| 158 | JOIN pg_catalog.pg_class c ON c.oid = r.ev_class |
|
| 159 | LEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace |
|
| 160 | WHERE c.relkind='v' AND r.rulename != '_RETURN' AND r.rulename ILIKE {$term} {$where} |
|
| 161 | "; |
|
| 162 | ||
| 163 | // Add advanced objects if show_advanced is set |
|
| 164 | if ($conf['show_advanced']) { |
|
| 165 | $sql .= " |
|
| 166 | UNION ALL |
|
| 167 | SELECT CASE WHEN pt.typtype='d' THEN 'DOMAIN' ELSE 'TYPE' END, pt.oid, pn.nspname, NULL, |
|
| 168 | pt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn |
|
| 169 | WHERE pt.typnamespace=pn.oid AND typname ILIKE {$term} |
|
| 170 | AND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid)) |
|
| 171 | {$where} |
|
| 172 | UNION ALL |
|
| 173 | SELECT 'OPERATOR', po.oid, pn.nspname, NULL, po.oprname FROM pg_catalog.pg_operator po, pg_catalog.pg_namespace pn |
|
| 174 | WHERE po.oprnamespace=pn.oid AND oprname ILIKE {$term} {$where} |
|
| 175 | UNION ALL |
|
| 176 | SELECT 'CONVERSION', pc.oid, pn.nspname, NULL, pc.conname FROM pg_catalog.pg_conversion pc, |
|
| 177 | pg_catalog.pg_namespace pn WHERE pc.connamespace=pn.oid AND conname ILIKE {$term} {$where} |
|
| 178 | UNION ALL |
|
| 179 | SELECT 'LANGUAGE', pl.oid, NULL, NULL, pl.lanname FROM pg_catalog.pg_language pl |
|
| 180 | WHERE lanname ILIKE {$term} {$lan_where} |
|
| 181 | UNION ALL |
|
| 182 | SELECT DISTINCT ON (p.proname) 'AGGREGATE', p.oid, pn.nspname, NULL, p.proname FROM pg_catalog.pg_proc p |
|
| 183 | LEFT JOIN pg_catalog.pg_namespace pn ON p.pronamespace=pn.oid |
|
| 184 | WHERE p.proisagg AND p.proname ILIKE {$term} {$where} |
|
| 185 | UNION ALL |
|
| 186 | SELECT DISTINCT ON (po.opcname) 'OPCLASS', po.oid, pn.nspname, NULL, po.opcname FROM pg_catalog.pg_opclass po, |
|
| 187 | pg_catalog.pg_namespace pn WHERE po.opcnamespace=pn.oid |
|
| 188 | AND po.opcname ILIKE {$term} {$where} |
|
| 189 | "; |
|
| 190 | } // Otherwise just add domains |
|
| 191 | else { |
|
| 192 | $sql .= " |
|
| 193 | UNION ALL |
|
| 194 | SELECT 'DOMAIN', pt.oid, pn.nspname, NULL, |
|
| 195 | pt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn |
|
| 196 | WHERE pt.typnamespace=pn.oid AND pt.typtype='d' AND typname ILIKE {$term} |
|
| 197 | AND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid)) |
|
| 198 | {$where} |
|
| 199 | "; |
|
| 200 | } |
|
| 201 | ||
| 202 | if ($filter != '') { |
|
| 203 | // We use like to make RULE, CONSTRAINT and COLUMN searches work |
|
| 204 | $sql .= ") AS sub WHERE type LIKE '{$filter}%' "; |
|
| 205 | } |
|
| 206 | ||
| 207 | $sql .= 'ORDER BY type, schemaname, relname, name'; |
|
| 208 | ||
| 209 | return $this->selectSet($sql); |
|
| 210 | } |
|
| 211 | ||
| 212 | // Capabilities |
|
| 213 | ||
| @@ 863-1004 (lines=142) @@ | ||
| 860 | * |
|
| 861 | * @return A recordset |
|
| 862 | */ |
|
| 863 | public function findObject($term, $filter) |
|
| 864 | { |
|
| 865 | $conf = $this->conf; |
|
| 866 | ||
| 867 | /*about escaping: |
|
| 868 | * SET standard_conforming_string is not available before 8.2 |
|
| 869 | * So we must use PostgreSQL specific notation :/ |
|
| 870 | * E'' notation is not available before 8.1 |
|
| 871 | * $$ is available since 8.0 |
|
| 872 | * Nothing specific from 7.4 |
|
| 873 | */ |
|
| 874 | ||
| 875 | // Escape search term for ILIKE match |
|
| 876 | $this->clean($term); |
|
| 877 | $this->clean($filter); |
|
| 878 | $term = str_replace('_', '\_', $term); |
|
| 879 | $term = str_replace('%', '\%', $term); |
|
| 880 | ||
| 881 | // Exclude system relations if necessary |
|
| 882 | if (!$conf['show_system']) { |
|
| 883 | // XXX: The mention of information_schema here is in the wrong place, but |
|
| 884 | // it's the quickest fix to exclude the info schema from 7.4 |
|
| 885 | $where = " AND pn.nspname NOT LIKE \$_PATERN_\$pg\_%\$_PATERN_\$ AND pn.nspname != 'information_schema'"; |
|
| 886 | $lan_where = 'AND pl.lanispl'; |
|
| 887 | } else { |
|
| 888 | $where = ''; |
|
| 889 | $lan_where = ''; |
|
| 890 | } |
|
| 891 | ||
| 892 | // Apply outer filter |
|
| 893 | $sql = ''; |
|
| 894 | if ($filter != '') { |
|
| 895 | $sql = 'SELECT * FROM ('; |
|
| 896 | } |
|
| 897 | ||
| 898 | $term = "\$_PATERN_\$%{$term}%\$_PATERN_\$"; |
|
| 899 | ||
| 900 | $sql .= " |
|
| 901 | SELECT 'SCHEMA' AS type, oid, NULL AS schemaname, NULL AS relname, nspname AS name |
|
| 902 | FROM pg_catalog.pg_namespace pn WHERE nspname ILIKE {$term} {$where} |
|
| 903 | UNION ALL |
|
| 904 | SELECT CASE WHEN relkind='r' THEN 'TABLE' WHEN relkind='v' THEN 'VIEW' WHEN relkind='S' THEN 'SEQUENCE' END, pc.oid, |
|
| 905 | pn.nspname, NULL, pc.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn |
|
| 906 | WHERE pc.relnamespace=pn.oid AND relkind IN ('r', 'v', 'S') AND relname ILIKE {$term} {$where} |
|
| 907 | UNION ALL |
|
| 908 | SELECT 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, |
|
| 909 | pg_catalog.pg_attribute pa WHERE pc.relnamespace=pn.oid AND pc.oid=pa.attrelid |
|
| 910 | AND pa.attname ILIKE {$term} AND pa.attnum > 0 AND NOT pa.attisdropped AND pc.relkind IN ('r', 'v') {$where} |
|
| 911 | UNION ALL |
|
| 912 | SELECT 'FUNCTION', pp.oid, pn.nspname, NULL, pp.proname || '(' || pg_catalog.oidvectortypes(pp.proargtypes) || ')' FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pn |
|
| 913 | WHERE pp.pronamespace=pn.oid AND NOT pp.proisagg AND pp.proname ILIKE {$term} {$where} |
|
| 914 | UNION ALL |
|
| 915 | SELECT 'INDEX', NULL, pn.nspname, pc.relname, pc2.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 916 | pg_catalog.pg_index pi, pg_catalog.pg_class pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pi.indrelid |
|
| 917 | AND pi.indexrelid=pc2.oid |
|
| 918 | AND NOT EXISTS ( |
|
| 919 | SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 920 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 921 | WHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') |
|
| 922 | ) |
|
| 923 | AND pc2.relname ILIKE {$term} {$where} |
|
| 924 | UNION ALL |
|
| 925 | SELECT 'CONSTRAINTTABLE', NULL, pn.nspname, pc.relname, pc2.conname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 926 | pg_catalog.pg_constraint pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pc2.conrelid AND pc2.conrelid != 0 |
|
| 927 | AND CASE WHEN pc2.contype IN ('f', 'c') THEN TRUE ELSE NOT EXISTS ( |
|
| 928 | SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 929 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 930 | WHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') |
|
| 931 | ) END |
|
| 932 | AND pc2.conname ILIKE {$term} {$where} |
|
| 933 | UNION ALL |
|
| 934 | SELECT 'CONSTRAINTDOMAIN', pt.oid, pn.nspname, pt.typname, pc.conname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn, |
|
| 935 | pg_catalog.pg_constraint pc WHERE pt.typnamespace=pn.oid AND pt.oid=pc.contypid AND pc.contypid != 0 |
|
| 936 | AND pc.conname ILIKE {$term} {$where} |
|
| 937 | UNION ALL |
|
| 938 | SELECT 'TRIGGER', NULL, pn.nspname, pc.relname, pt.tgname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 939 | pg_catalog.pg_trigger pt WHERE pc.relnamespace=pn.oid AND pc.oid=pt.tgrelid |
|
| 940 | AND ( pt.tgconstraint = 0 OR NOT EXISTS |
|
| 941 | (SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 942 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 943 | WHERE d.classid = pt.tableoid AND d.objid = pt.oid AND d.deptype = 'i' AND c.contype = 'f')) |
|
| 944 | AND pt.tgname ILIKE {$term} {$where} |
|
| 945 | UNION ALL |
|
| 946 | SELECT 'RULETABLE', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r |
|
| 947 | JOIN pg_catalog.pg_class c ON c.oid = r.ev_class |
|
| 948 | LEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace |
|
| 949 | WHERE c.relkind='r' AND r.rulename != '_RETURN' AND r.rulename ILIKE {$term} {$where} |
|
| 950 | UNION ALL |
|
| 951 | SELECT 'RULEVIEW', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r |
|
| 952 | JOIN pg_catalog.pg_class c ON c.oid = r.ev_class |
|
| 953 | LEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace |
|
| 954 | WHERE c.relkind='v' AND r.rulename != '_RETURN' AND r.rulename ILIKE {$term} {$where} |
|
| 955 | "; |
|
| 956 | ||
| 957 | // Add advanced objects if show_advanced is set |
|
| 958 | if ($conf['show_advanced']) { |
|
| 959 | $sql .= " |
|
| 960 | UNION ALL |
|
| 961 | SELECT CASE WHEN pt.typtype='d' THEN 'DOMAIN' ELSE 'TYPE' END, pt.oid, pn.nspname, NULL, |
|
| 962 | pt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn |
|
| 963 | WHERE pt.typnamespace=pn.oid AND typname ILIKE {$term} |
|
| 964 | AND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid)) |
|
| 965 | {$where} |
|
| 966 | UNION ALL |
|
| 967 | SELECT 'OPERATOR', po.oid, pn.nspname, NULL, po.oprname FROM pg_catalog.pg_operator po, pg_catalog.pg_namespace pn |
|
| 968 | WHERE po.oprnamespace=pn.oid AND oprname ILIKE {$term} {$where} |
|
| 969 | UNION ALL |
|
| 970 | SELECT 'CONVERSION', pc.oid, pn.nspname, NULL, pc.conname FROM pg_catalog.pg_conversion pc, |
|
| 971 | pg_catalog.pg_namespace pn WHERE pc.connamespace=pn.oid AND conname ILIKE {$term} {$where} |
|
| 972 | UNION ALL |
|
| 973 | SELECT 'LANGUAGE', pl.oid, NULL, NULL, pl.lanname FROM pg_catalog.pg_language pl |
|
| 974 | WHERE lanname ILIKE {$term} {$lan_where} |
|
| 975 | UNION ALL |
|
| 976 | SELECT DISTINCT ON (p.proname) 'AGGREGATE', p.oid, pn.nspname, NULL, p.proname FROM pg_catalog.pg_proc p |
|
| 977 | LEFT JOIN pg_catalog.pg_namespace pn ON p.pronamespace=pn.oid |
|
| 978 | WHERE p.proisagg AND p.proname ILIKE {$term} {$where} |
|
| 979 | UNION ALL |
|
| 980 | SELECT DISTINCT ON (po.opcname) 'OPCLASS', po.oid, pn.nspname, NULL, po.opcname FROM pg_catalog.pg_opclass po, |
|
| 981 | pg_catalog.pg_namespace pn WHERE po.opcnamespace=pn.oid |
|
| 982 | AND po.opcname ILIKE {$term} {$where} |
|
| 983 | "; |
|
| 984 | } // Otherwise just add domains |
|
| 985 | else { |
|
| 986 | $sql .= " |
|
| 987 | UNION ALL |
|
| 988 | SELECT 'DOMAIN', pt.oid, pn.nspname, NULL, |
|
| 989 | pt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn |
|
| 990 | WHERE pt.typnamespace=pn.oid AND pt.typtype='d' AND typname ILIKE {$term} |
|
| 991 | AND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid)) |
|
| 992 | {$where} |
|
| 993 | "; |
|
| 994 | } |
|
| 995 | ||
| 996 | if ($filter != '') { |
|
| 997 | // We use like to make RULE, CONSTRAINT and COLUMN searches work |
|
| 998 | $sql .= ") AS sub WHERE type LIKE '{$filter}%' "; |
|
| 999 | } |
|
| 1000 | ||
| 1001 | $sql .= 'ORDER BY type, schemaname, relname, name'; |
|
| 1002 | ||
| 1003 | return $this->selectSet($sql); |
|
| 1004 | } |
|
| 1005 | ||
| 1006 | /** |
|
| 1007 | * Returns all available variable information. |
|
| @@ 106-245 (lines=140) @@ | ||
| 103 | * |
|
| 104 | * @return A recordset |
|
| 105 | */ |
|
| 106 | public function findObject($term, $filter) |
|
| 107 | { |
|
| 108 | $conf = $this->conf; |
|
| 109 | ||
| 110 | /*about escaping: |
|
| 111 | * SET standard_conforming_string is not available before 8.2 |
|
| 112 | * So we must use PostgreSQL specific notation :/ |
|
| 113 | * E'' notation is not available before 8.1 |
|
| 114 | * $$ is available since 8.0 |
|
| 115 | * Nothing specific from 7.4 |
|
| 116 | */ |
|
| 117 | ||
| 118 | // Escape search term for ILIKE match |
|
| 119 | $term = str_replace('_', '\\_', $term); |
|
| 120 | $term = str_replace('%', '\\%', $term); |
|
| 121 | $this->clean($term); |
|
| 122 | $this->clean($filter); |
|
| 123 | ||
| 124 | // Exclude system relations if necessary |
|
| 125 | if (!$conf['show_system']) { |
|
| 126 | // XXX: The mention of information_schema here is in the wrong place, but |
|
| 127 | // it's the quickest fix to exclude the info schema from 7.4 |
|
| 128 | $where = " AND pn.nspname NOT LIKE 'pg\\\\_%' AND pn.nspname != 'information_schema'"; |
|
| 129 | $lan_where = 'AND pl.lanispl'; |
|
| 130 | } else { |
|
| 131 | $where = ''; |
|
| 132 | $lan_where = ''; |
|
| 133 | } |
|
| 134 | ||
| 135 | // Apply outer filter |
|
| 136 | $sql = ''; |
|
| 137 | if ($filter != '') { |
|
| 138 | $sql = 'SELECT * FROM ('; |
|
| 139 | } |
|
| 140 | ||
| 141 | $sql .= " |
|
| 142 | SELECT 'SCHEMA' AS type, oid, NULL AS schemaname, NULL AS relname, nspname AS name |
|
| 143 | FROM pg_catalog.pg_namespace pn WHERE nspname ILIKE '%{$term}%' {$where} |
|
| 144 | UNION ALL |
|
| 145 | SELECT CASE WHEN relkind='r' THEN 'TABLE' WHEN relkind='v' THEN 'VIEW' WHEN relkind='S' THEN 'SEQUENCE' END, pc.oid, |
|
| 146 | pn.nspname, NULL, pc.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn |
|
| 147 | WHERE pc.relnamespace=pn.oid AND relkind IN ('r', 'v', 'S') AND relname ILIKE '%{$term}%' {$where} |
|
| 148 | UNION ALL |
|
| 149 | SELECT 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, |
|
| 150 | pg_catalog.pg_attribute pa WHERE pc.relnamespace=pn.oid AND pc.oid=pa.attrelid |
|
| 151 | AND pa.attname ILIKE '%{$term}%' AND pa.attnum > 0 AND NOT pa.attisdropped AND pc.relkind IN ('r', 'v') {$where} |
|
| 152 | UNION ALL |
|
| 153 | SELECT 'FUNCTION', pp.oid, pn.nspname, NULL, pp.proname || '(' || pg_catalog.oidvectortypes(pp.proargtypes) || ')' FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pn |
|
| 154 | WHERE pp.pronamespace=pn.oid AND NOT pp.proisagg AND pp.proname ILIKE '%{$term}%' {$where} |
|
| 155 | UNION ALL |
|
| 156 | SELECT 'INDEX', NULL, pn.nspname, pc.relname, pc2.relname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 157 | pg_catalog.pg_index pi, pg_catalog.pg_class pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pi.indrelid |
|
| 158 | AND pi.indexrelid=pc2.oid |
|
| 159 | AND NOT EXISTS ( |
|
| 160 | SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 161 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 162 | WHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') |
|
| 163 | ) |
|
| 164 | AND pc2.relname ILIKE '%{$term}%' {$where} |
|
| 165 | UNION ALL |
|
| 166 | SELECT 'CONSTRAINTTABLE', NULL, pn.nspname, pc.relname, pc2.conname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 167 | pg_catalog.pg_constraint pc2 WHERE pc.relnamespace=pn.oid AND pc.oid=pc2.conrelid AND pc2.conrelid != 0 |
|
| 168 | AND CASE WHEN pc2.contype IN ('f', 'c') THEN TRUE ELSE NOT EXISTS ( |
|
| 169 | SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 170 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 171 | WHERE d.classid = pc2.tableoid AND d.objid = pc2.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p') |
|
| 172 | ) END |
|
| 173 | AND pc2.conname ILIKE '%{$term}%' {$where} |
|
| 174 | UNION ALL |
|
| 175 | SELECT 'CONSTRAINTDOMAIN', pt.oid, pn.nspname, pt.typname, pc.conname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn, |
|
| 176 | pg_catalog.pg_constraint pc WHERE pt.typnamespace=pn.oid AND pt.oid=pc.contypid AND pc.contypid != 0 |
|
| 177 | AND pc.conname ILIKE '%{$term}%' {$where} |
|
| 178 | UNION ALL |
|
| 179 | SELECT 'TRIGGER', NULL, pn.nspname, pc.relname, pt.tgname FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pn, |
|
| 180 | pg_catalog.pg_trigger pt WHERE pc.relnamespace=pn.oid AND pc.oid=pt.tgrelid |
|
| 181 | AND ( pt.tgisconstraint = 'f' OR NOT EXISTS |
|
| 182 | (SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c |
|
| 183 | ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) |
|
| 184 | WHERE d.classid = pt.tableoid AND d.objid = pt.oid AND d.deptype = 'i' AND c.contype = 'f')) |
|
| 185 | AND pt.tgname ILIKE '%{$term}%' {$where} |
|
| 186 | UNION ALL |
|
| 187 | SELECT 'RULETABLE', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r |
|
| 188 | JOIN pg_catalog.pg_class c ON c.oid = r.ev_class |
|
| 189 | LEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace |
|
| 190 | WHERE c.relkind='r' AND r.rulename != '_RETURN' AND r.rulename ILIKE '%{$term}%' {$where} |
|
| 191 | UNION ALL |
|
| 192 | SELECT 'RULEVIEW', NULL, pn.nspname AS schemaname, c.relname AS tablename, r.rulename FROM pg_catalog.pg_rewrite r |
|
| 193 | JOIN pg_catalog.pg_class c ON c.oid = r.ev_class |
|
| 194 | LEFT JOIN pg_catalog.pg_namespace pn ON pn.oid = c.relnamespace |
|
| 195 | WHERE c.relkind='v' AND r.rulename != '_RETURN' AND r.rulename ILIKE '%{$term}%' {$where} |
|
| 196 | "; |
|
| 197 | ||
| 198 | // Add advanced objects if show_advanced is set |
|
| 199 | if ($conf['show_advanced']) { |
|
| 200 | $sql .= " |
|
| 201 | UNION ALL |
|
| 202 | SELECT CASE WHEN pt.typtype='d' THEN 'DOMAIN' ELSE 'TYPE' END, pt.oid, pn.nspname, NULL, |
|
| 203 | pt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn |
|
| 204 | WHERE pt.typnamespace=pn.oid AND typname ILIKE '%{$term}%' |
|
| 205 | AND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid)) |
|
| 206 | {$where} |
|
| 207 | UNION ALL |
|
| 208 | SELECT 'OPERATOR', po.oid, pn.nspname, NULL, po.oprname FROM pg_catalog.pg_operator po, pg_catalog.pg_namespace pn |
|
| 209 | WHERE po.oprnamespace=pn.oid AND oprname ILIKE '%{$term}%' {$where} |
|
| 210 | UNION ALL |
|
| 211 | SELECT 'CONVERSION', pc.oid, pn.nspname, NULL, pc.conname FROM pg_catalog.pg_conversion pc, |
|
| 212 | pg_catalog.pg_namespace pn WHERE pc.connamespace=pn.oid AND conname ILIKE '%{$term}%' {$where} |
|
| 213 | UNION ALL |
|
| 214 | SELECT 'LANGUAGE', pl.oid, NULL, NULL, pl.lanname FROM pg_catalog.pg_language pl |
|
| 215 | WHERE lanname ILIKE '%{$term}%' {$lan_where} |
|
| 216 | UNION ALL |
|
| 217 | SELECT DISTINCT ON (p.proname) 'AGGREGATE', p.oid, pn.nspname, NULL, p.proname FROM pg_catalog.pg_proc p |
|
| 218 | LEFT JOIN pg_catalog.pg_namespace pn ON p.pronamespace=pn.oid |
|
| 219 | WHERE p.proisagg AND p.proname ILIKE '%{$term}%' {$where} |
|
| 220 | UNION ALL |
|
| 221 | SELECT DISTINCT ON (po.opcname) 'OPCLASS', po.oid, pn.nspname, NULL, po.opcname FROM pg_catalog.pg_opclass po, |
|
| 222 | pg_catalog.pg_namespace pn WHERE po.opcnamespace=pn.oid |
|
| 223 | AND po.opcname ILIKE '%{$term}%' {$where} |
|
| 224 | "; |
|
| 225 | } // Otherwise just add domains |
|
| 226 | else { |
|
| 227 | $sql .= " |
|
| 228 | UNION ALL |
|
| 229 | SELECT 'DOMAIN', pt.oid, pn.nspname, NULL, |
|
| 230 | pt.typname FROM pg_catalog.pg_type pt, pg_catalog.pg_namespace pn |
|
| 231 | WHERE pt.typnamespace=pn.oid AND pt.typtype='d' AND typname ILIKE '%{$term}%' |
|
| 232 | AND (pt.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = pt.typrelid)) |
|
| 233 | {$where} |
|
| 234 | "; |
|
| 235 | } |
|
| 236 | ||
| 237 | if ($filter != '') { |
|
| 238 | // We use like to make RULE, CONSTRAINT and COLUMN searches work |
|
| 239 | $sql .= ") AS sub WHERE type LIKE '{$filter}%' "; |
|
| 240 | } |
|
| 241 | ||
| 242 | $sql .= 'ORDER BY type, schemaname, relname, name'; |
|
| 243 | ||
| 244 | return $this->selectSet($sql); |
|
| 245 | } |
|
| 246 | ||
| 247 | /** |
|
| 248 | * Returns table locks information in the current database. |
|
