Revision 3052
Added by Matt Jones over 17 years ago
| src/upgrade-db-to-1.7-postgres.sql | ||
|---|---|---|
| 1 |
/* |
|
| 2 |
* New indexes to make queries avoid full table scans. |
|
| 3 |
*/ |
|
| 4 |
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner)); |
|
| 5 |
CREATE INDEX xml_access_idx1 ON xml_access (lower(principal_name)); |
|
| 6 |
CREATE INDEX xml_access_idx2 ON xml_access (permission); |
|
| 7 |
CREATE INDEX xml_access_idx3 ON xml_access (perm_type); |
|
| 8 |
CREATE INDEX xml_access_idx4 ON xml_access (perm_order); |
|
| 9 |
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid); |
|
| 10 |
CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata)); |
|
| 11 |
|
|
| 12 |
/** done */ |
|
| 0 | 13 | |
| src/xmltables-postgres.sql | ||
|---|---|---|
| 166 | 166 |
* Index of <docid,doctype> in xml_document |
| 167 | 167 |
*/ |
| 168 | 168 |
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype); |
| 169 |
CREATE INDEX xml_documents_idx2 ON xml_documents (lower(user_owner)); |
|
| 169 | 170 |
|
| 170 | 171 |
/* |
| 171 | 172 |
* Revised Documents -- table to store XML documents saved after an UPDATE |
| ... | ... | |
| 217 | 218 |
CONSTRAINT xml_access_accessfileid_fk |
| 218 | 219 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents |
| 219 | 220 |
); |
| 221 |
CREATE INDEX xml_access_idx1 ON xml_access (lower(principal_name)); |
|
| 222 |
CREATE INDEX xml_access_idx2 ON xml_access (permission); |
|
| 223 |
CREATE INDEX xml_access_idx3 ON xml_access (perm_type); |
|
| 224 |
CREATE INDEX xml_access_idx4 ON xml_access (perm_order); |
|
| 225 |
CREATE INDEX xml_access_idx5 ON xml_access (subtreeid); |
|
| 220 | 226 |
|
| 221 | 227 |
/* |
| 222 | 228 |
* Index of Nodes -- table to store precomputed paths through tree for |
| ... | ... | |
| 357 | 363 |
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path); |
| 358 | 364 |
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata); |
| 359 | 365 |
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical); |
| 366 |
CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata)); |
|
| 360 | 367 |
|
| 361 | 368 |
/* |
| 362 | 369 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
| src/edu/ucsb/nceas/metacat/QueryTerm.java | ||
|---|---|---|
| 140 | 140 |
// Add appropriate wildcards to search string |
| 141 | 141 |
String searchexpr = null; |
| 142 | 142 |
if (searchmode.equals("starts-with")) {
|
| 143 |
searchexpr = nodedataterm + " LIKE '" + casevalue + "%' ";
|
|
| 143 |
searchexpr = nodedataterm + " = '" + casevalue + "%' ";
|
|
| 144 | 144 |
} else if (searchmode.equals("ends-with")) {
|
| 145 |
searchexpr = nodedataterm + " LIKE '%" + casevalue + "' ";
|
|
| 145 |
searchexpr = nodedataterm + " = '%" + casevalue + "' ";
|
|
| 146 | 146 |
} else if (searchmode.equals("contains")) {
|
| 147 | 147 |
if (!casevalue.equals("%")) {
|
| 148 |
searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
|
|
| 148 |
searchexpr = nodedataterm + " = '%" + casevalue + "%' ";
|
|
| 149 | 149 |
} else {
|
| 150 |
searchexpr = nodedataterm + " LIKE '" + casevalue + "' ";
|
|
| 150 |
searchexpr = nodedataterm + " = '" + casevalue + "' ";
|
|
| 151 | 151 |
// find percentage symbol |
| 152 | 152 |
percentageSymbol = true; |
| 153 | 153 |
} |
| 154 | 154 |
} else if (searchmode.equals("not-contains")) {
|
| 155 | 155 |
notEqual = true; |
| 156 |
searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' ";
|
|
| 156 |
searchexpr = nodedataterm + " = '%" + casevalue + "%' ";
|
|
| 157 | 157 |
} else if (searchmode.equals("equals")) {
|
| 158 | 158 |
searchexpr = nodedataterm + " = '" + casevalue + "' "; |
| 159 | 159 |
} else if (searchmode.equals("isnot-equal")) {
|
| ... | ... | |
| 208 | 208 |
self.append("SELECT DISTINCT docid from xml_path_index WHERE");
|
| 209 | 209 |
self.append(" docid NOT IN (Select docid FROM xml_path_index WHERE ");
|
| 210 | 210 |
self.append(searchexpr); |
| 211 |
self.append("AND path LIKE '" + pathexpr + "') ");
|
|
| 211 |
self.append("AND path = '" + pathexpr + "') ");
|
|
| 212 | 212 |
} else {
|
| 213 | 213 |
self.append("SELECT DISTINCT docid FROM xml_path_index WHERE ");
|
| 214 | 214 |
self.append(searchexpr); |
| 215 |
self.append("AND path LIKE '" + pathexpr + "' ");
|
|
| 215 |
self.append("AND path = '" + pathexpr + "' ");
|
|
| 216 | 216 |
} |
| 217 | 217 |
|
| 218 | 218 |
} else {
|
Also available in: Unified diff
Modifications to the table definitions to add indices that should be used
when searching the database. Added an upgrade script to create these
indices as well. Modified QueryTerm.java to use the '=' comparator rather
than 'LIKE' so that we can do index scans rather than table scans. This needs
to be tested.