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