Revision 2068
Added by Matt Jones about 20 years ago
| QueryTerm.java | ||
|---|---|---|
| 30 | 30 |
|
| 31 | 31 |
package edu.ucsb.nceas.metacat; |
| 32 | 32 |
|
| 33 |
import edu.ucsb.nceas.dbadapter.*; |
|
| 34 |
|
|
| 35 |
import java.io.*; |
|
| 36 |
import java.util.Hashtable; |
|
| 37 |
import java.util.Stack; |
|
| 38 | 33 |
import java.util.Vector; |
| 39 |
import java.util.Enumeration; |
|
| 40 | 34 |
|
| 41 | 35 |
/** a utility class that represents a single term in a query */ |
| 42 |
public class QueryTerm {
|
|
| 36 |
public class QueryTerm |
|
| 37 |
{
|
|
| 38 |
|
|
| 43 | 39 |
private boolean casesensitive = false; |
| 40 |
|
|
| 44 | 41 |
private String searchmode = null; |
| 42 |
|
|
| 45 | 43 |
private String value = null; |
| 44 |
|
|
| 46 | 45 |
private String pathexpr = null; |
| 46 |
|
|
| 47 | 47 |
private boolean percentageSymbol = false; |
| 48 |
|
|
| 48 | 49 |
private int countPercentageSearchItem = 0; |
| 49 |
|
|
| 50 | 50 |
|
| 51 | 51 |
/** |
| 52 |
* Construct a new instance of a query term for a free text search |
|
| 53 |
* (using the value only) |
|
| 54 |
* |
|
| 55 |
* @param casesensitive flag indicating whether case is used to match |
|
| 56 |
* @param searchmode determines what kind of substring match is performed |
|
| 57 |
* (one of starts-with|ends-with|contains|matches-exactly) |
|
| 58 |
* @param value the text value to match |
|
| 52 |
* Construct a new instance of a query term for a free text search (using |
|
| 53 |
* the value only) |
|
| 54 |
* |
|
| 55 |
* @param casesensitive |
|
| 56 |
* flag indicating whether case is used to match |
|
| 57 |
* @param searchmode |
|
| 58 |
* determines what kind of substring match is performed (one of |
|
| 59 |
* starts-with|ends-with|contains|matches-exactly) |
|
| 60 |
* @param value |
|
| 61 |
* the text value to match |
|
| 59 | 62 |
*/ |
| 60 |
public QueryTerm(boolean casesensitive, String searchmode, |
|
| 61 |
String value) {
|
|
| 62 |
this.casesensitive = casesensitive; |
|
| 63 |
this.searchmode = searchmode; |
|
| 64 |
this.value = value; |
|
| 63 |
public QueryTerm(boolean casesensitive, String searchmode, String value)
|
|
| 64 |
{
|
|
| 65 |
this.casesensitive = casesensitive;
|
|
| 66 |
this.searchmode = searchmode;
|
|
| 67 |
this.value = value;
|
|
| 65 | 68 |
} |
| 66 | 69 |
|
| 67 | 70 |
/** |
| 68 | 71 |
* Construct a new instance of a query term for a structured search |
| 69 | 72 |
* (matching the value only for those nodes in the pathexpr) |
| 70 |
* |
|
| 71 |
* @param casesensitive flag indicating whether case is used to match |
|
| 72 |
* @param searchmode determines what kind of substring match is performed |
|
| 73 |
* (one of starts-with|ends-with|contains|matches-exactly) |
|
| 74 |
* @param value the text value to match |
|
| 75 |
* @param pathexpr the hierarchical path to the nodes to be searched |
|
| 73 |
* |
|
| 74 |
* @param casesensitive |
|
| 75 |
* flag indicating whether case is used to match |
|
| 76 |
* @param searchmode |
|
| 77 |
* determines what kind of substring match is performed (one of |
|
| 78 |
* starts-with|ends-with|contains|matches-exactly) |
|
| 79 |
* @param value |
|
| 80 |
* the text value to match |
|
| 81 |
* @param pathexpr |
|
| 82 |
* the hierarchical path to the nodes to be searched |
|
| 76 | 83 |
*/ |
| 77 |
public QueryTerm(boolean casesensitive, String searchmode, |
|
| 78 |
String value, String pathexpr) {
|
|
| 79 |
this(casesensitive, searchmode, value); |
|
| 80 |
this.pathexpr = pathexpr; |
|
| 84 |
public QueryTerm(boolean casesensitive, String searchmode, String value, |
|
| 85 |
String pathexpr) |
|
| 86 |
{
|
|
| 87 |
this(casesensitive, searchmode, value); |
|
| 88 |
this.pathexpr = pathexpr; |
|
| 81 | 89 |
} |
| 82 | 90 |
|
| 83 | 91 |
/** determine if the QueryTerm is case sensitive */ |
| 84 |
public boolean isCaseSensitive() {
|
|
| 85 |
return casesensitive; |
|
| 92 |
public boolean isCaseSensitive() |
|
| 93 |
{
|
|
| 94 |
return casesensitive; |
|
| 86 | 95 |
} |
| 87 | 96 |
|
| 88 | 97 |
/** get the searchmode parameter */ |
| 89 |
public String getSearchMode() {
|
|
| 90 |
return searchmode; |
|
| 98 |
public String getSearchMode() |
|
| 99 |
{
|
|
| 100 |
return searchmode; |
|
| 91 | 101 |
} |
| 92 |
|
|
| 102 |
|
|
| 93 | 103 |
/** get the Value parameter */ |
| 94 |
public String getValue() {
|
|
| 95 |
return value; |
|
| 104 |
public String getValue() |
|
| 105 |
{
|
|
| 106 |
return value; |
|
| 96 | 107 |
} |
| 97 | 108 |
|
| 98 | 109 |
/** get the path expression parameter */ |
| 99 |
public String getPathExpression() {
|
|
| 100 |
return pathexpr; |
|
| 110 |
public String getPathExpression() |
|
| 111 |
{
|
|
| 112 |
return pathexpr; |
|
| 101 | 113 |
} |
| 102 |
|
|
| 103 |
/** get the percentage count for one query term*/ |
|
| 114 |
|
|
| 115 |
/** get the percentage count for one query term */
|
|
| 104 | 116 |
public int getPercentageSymbolCount() |
| 105 | 117 |
{
|
| 106 |
return countPercentageSearchItem; |
|
| 118 |
return countPercentageSearchItem;
|
|
| 107 | 119 |
} |
| 108 | 120 |
|
| 109 | 121 |
/** |
| 110 | 122 |
* create a SQL serialization of the query that this instance represents |
| 111 | 123 |
*/ |
| 112 |
public String printSQL(boolean useXMLIndex) {
|
|
| 113 |
StringBuffer self = new StringBuffer(); |
|
| 124 |
public String printSQL(boolean useXMLIndex) |
|
| 125 |
{
|
|
| 126 |
StringBuffer self = new StringBuffer(); |
|
| 114 | 127 |
|
| 115 |
// Uppercase the search string if case match is not important |
|
| 116 |
String casevalue = null; |
|
| 117 |
String nodedataterm = null; |
|
| 128 |
// Uppercase the search string if case match is not important
|
|
| 129 |
String casevalue = null;
|
|
| 130 |
String nodedataterm = null;
|
|
| 118 | 131 |
|
| 119 |
if (casesensitive) {
|
|
| 120 |
nodedataterm = "nodedata"; |
|
| 121 |
casevalue = value; |
|
| 122 |
} else {
|
|
| 123 |
nodedataterm = "UPPER(nodedata)"; |
|
| 124 |
casevalue = value.toUpperCase(); |
|
| 125 |
} |
|
| 132 |
if (casesensitive) {
|
|
| 133 |
nodedataterm = "nodedata";
|
|
| 134 |
casevalue = value;
|
|
| 135 |
} else {
|
|
| 136 |
nodedataterm = "UPPER(nodedata)";
|
|
| 137 |
casevalue = value.toUpperCase();
|
|
| 138 |
}
|
|
| 126 | 139 |
|
| 127 |
// Add appropriate wildcards to search string |
|
| 128 |
//String searchvalue = null; |
|
| 129 |
String searchexpr = null; |
|
| 130 |
if (searchmode.equals("starts-with")) {
|
|
| 131 |
//searchvalue = casevalue + "%"; |
|
| 132 |
searchexpr = nodedataterm + " LIKE '" + casevalue + "%' "; |
|
| 133 |
} else if (searchmode.equals("ends-with")) {
|
|
| 134 |
//searchvalue = "%" + casevalue; |
|
| 135 |
searchexpr = nodedataterm + " LIKE '%" + casevalue + "' "; |
|
| 136 |
} else if (searchmode.equals("contains")) {
|
|
| 137 |
//searchvalue = "%" + casevalue + "%"; |
|
| 138 |
if (!casevalue.equals("%"))
|
|
| 139 |
{
|
|
| 140 |
searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' "; |
|
| 141 |
} |
|
| 142 |
else |
|
| 143 |
{
|
|
| 144 |
searchexpr = nodedataterm + " LIKE '" + casevalue + "' "; |
|
| 145 |
// find percentage symbol |
|
| 146 |
percentageSymbol = true; |
|
| 147 |
} |
|
| 148 |
} else if (searchmode.equals("not-contains")) {
|
|
| 149 |
//searchvalue = "%" + casevalue; |
|
| 150 |
searchexpr = nodedataterm + " NOT LIKE '%" + casevalue + "%' "; |
|
| 151 |
} else if (searchmode.equals("equals")) {
|
|
| 152 |
//searchvalue = casevalue; |
|
| 153 |
searchexpr = nodedataterm + " = '" + casevalue + "' "; |
|
| 154 |
} else if (searchmode.equals("isnot-equal")) {
|
|
| 155 |
//searchvalue = casevalue; |
|
| 156 |
searchexpr = nodedataterm + " != '" + casevalue + "' "; |
|
| 157 |
} else {
|
|
| 158 |
//searchvalue = casevalue; |
|
| 159 |
String oper = null; |
|
| 160 |
if (searchmode.equals("greater-than")) {
|
|
| 161 |
oper = ">"; |
|
| 162 |
nodedataterm = "nodedata"; |
|
| 163 |
} else if (searchmode.equals("greater-than-equals")) {
|
|
| 164 |
oper = ">="; |
|
| 165 |
nodedataterm = "nodedata"; |
|
| 166 |
} else if (searchmode.equals("less-than")) {
|
|
| 167 |
oper = "<"; |
|
| 168 |
nodedataterm = "nodedata"; |
|
| 169 |
} else if (searchmode.equals("less-than-equals")) {
|
|
| 170 |
oper = "<="; |
|
| 171 |
nodedataterm = "nodedata"; |
|
| 140 |
// Add appropriate wildcards to search string |
|
| 141 |
String searchexpr = null; |
|
| 142 |
if (searchmode.equals("starts-with")) {
|
|
| 143 |
searchexpr = nodedataterm + " LIKE '" + casevalue + "%' "; |
|
| 144 |
} else if (searchmode.equals("ends-with")) {
|
|
| 145 |
searchexpr = nodedataterm + " LIKE '%" + casevalue + "' "; |
|
| 146 |
} else if (searchmode.equals("contains")) {
|
|
| 147 |
if (!casevalue.equals("%")) {
|
|
| 148 |
searchexpr = nodedataterm + " LIKE '%" + casevalue + "%' "; |
|
| 149 |
} else {
|
|
| 150 |
searchexpr = nodedataterm + " LIKE '" + casevalue + "' "; |
|
| 151 |
// find percentage symbol |
|
| 152 |
percentageSymbol = true; |
|
| 153 |
} |
|
| 154 |
} else if (searchmode.equals("not-contains")) {
|
|
| 155 |
searchexpr = nodedataterm + " NOT LIKE '%" + casevalue + "%' "; |
|
| 156 |
} else if (searchmode.equals("equals")) {
|
|
| 157 |
searchexpr = nodedataterm + " = '" + casevalue + "' "; |
|
| 158 |
} else if (searchmode.equals("isnot-equal")) {
|
|
| 159 |
searchexpr = nodedataterm + " != '" + casevalue + "' "; |
|
| 172 | 160 |
} else {
|
| 173 |
System.out.println("NOT expected case. NOT recognized operator: " +
|
|
| 174 |
searchmode); |
|
| 175 |
return null; |
|
| 161 |
String oper = null; |
|
| 162 |
if (searchmode.equals("greater-than")) {
|
|
| 163 |
oper = ">"; |
|
| 164 |
nodedataterm = "nodedata"; |
|
| 165 |
} else if (searchmode.equals("greater-than-equals")) {
|
|
| 166 |
oper = ">="; |
|
| 167 |
nodedataterm = "nodedata"; |
|
| 168 |
} else if (searchmode.equals("less-than")) {
|
|
| 169 |
oper = "<"; |
|
| 170 |
nodedataterm = "nodedata"; |
|
| 171 |
} else if (searchmode.equals("less-than-equals")) {
|
|
| 172 |
oper = "<="; |
|
| 173 |
nodedataterm = "nodedata"; |
|
| 174 |
} else {
|
|
| 175 |
System.out |
|
| 176 |
.println("NOT expected case. NOT recognized operator: "
|
|
| 177 |
+ searchmode); |
|
| 178 |
return null; |
|
| 179 |
} |
|
| 180 |
try {
|
|
| 181 |
// it is number; numeric comparison |
|
| 182 |
// but we need to make sure there is no string in node data |
|
| 183 |
String getRidOfString = " AND UPPER(nodedata) = LOWER(nodedata)" |
|
| 184 |
+ " AND LTRIM(nodedata) != ' ' " |
|
| 185 |
+ " AND nodedata IS NOT NULL "; |
|
| 186 |
searchexpr = nodedataterm + " " + oper + " " |
|
| 187 |
+ new Double(casevalue) + " " + getRidOfString; |
|
| 188 |
} catch (NumberFormatException nfe) {
|
|
| 189 |
// these are characters; character comparison |
|
| 190 |
searchexpr = nodedataterm + " " + oper + " '" + casevalue |
|
| 191 |
+ "' "; |
|
| 192 |
} |
|
| 176 | 193 |
} |
| 177 |
try {
|
|
| 178 |
// it is number; numeric comparison |
|
| 179 |
// but we need to make sure there is no string in node data |
|
| 180 |
String getRidOfString = " AND UPPER(nodedata) = LOWER(nodedata)" + |
|
| 181 |
" AND LTRIM(nodedata) != ' ' " + |
|
| 182 |
" AND nodedata IS NOT NULL "; |
|
| 183 |
searchexpr = nodedataterm + " " + oper + " " + |
|
| 184 |
new Double(casevalue) + " "+getRidOfString; |
|
| 185 |
} catch (NumberFormatException nfe) {
|
|
| 186 |
// these are characters; character comparison |
|
| 187 |
searchexpr = nodedataterm + " " + oper + " '" + casevalue + "' "; |
|
| 188 |
} |
|
| 189 |
} |
|
| 190 | 194 |
|
| 191 |
self.append("SELECT DISTINCT docid FROM xml_nodes WHERE \n");
|
|
| 192 |
//self.append(nodedataterm + " LIKE " + "'" + searchvalue + "' ");
|
|
| 193 |
self.append(searchexpr);
|
|
| 194 |
if (pathexpr != null) |
|
| 195 |
{
|
|
| 196 |
|
|
| 197 |
// use XML Index
|
|
| 198 |
if ( useXMLIndex )
|
|
| 199 |
{
|
|
| 200 |
if (!hasAttributeInPath(pathexpr))
|
|
| 201 |
{
|
|
| 202 |
// without attributes in path
|
|
| 203 |
self.append("AND parentnodeid IN ");
|
|
| 204 |
}
|
|
| 205 |
else
|
|
| 206 |
{
|
|
| 207 |
// has a attribute in path
|
|
| 208 |
String attributeName = QuerySpecification.getAttributeName(pathexpr);
|
|
| 209 |
self.append("AND nodetype LIKE 'ATTRIBUTE' AND nodename LIKE '"+
|
|
| 210 |
attributeName + "' ");
|
|
| 211 |
self.append("AND parentnodeid IN ");
|
|
| 212 |
pathexpr =
|
|
| 213 |
QuerySpecification.newPathExpressionWithOutAttribute(pathexpr);
|
|
| 214 |
|
|
| 215 |
}
|
|
| 216 |
self.append("(SELECT nodeid FROM xml_index WHERE path LIKE " +
|
|
| 217 |
"'" + pathexpr + "') " );
|
|
| 218 |
} |
|
| 219 |
else
|
|
| 220 |
{
|
|
| 221 |
// without using XML Index; using nested statements instead
|
|
| 222 |
self.append("AND parentnodeid IN ");
|
|
| 223 |
self.append(useNestedStatements(pathexpr)); |
|
| 195 |
self.append("SELECT DISTINCT docid FROM xml_nodes WHERE \n");
|
|
| 196 |
self.append(searchexpr);
|
|
| 197 |
if (pathexpr != null) {
|
|
| 198 |
|
|
| 199 |
// use XML Index
|
|
| 200 |
if (useXMLIndex) {
|
|
| 201 |
if (!hasAttributeInPath(pathexpr)) {
|
|
| 202 |
// without attributes in path
|
|
| 203 |
self.append("AND parentnodeid IN ");
|
|
| 204 |
} else {
|
|
| 205 |
// has a attribute in path
|
|
| 206 |
String attributeName = QuerySpecification
|
|
| 207 |
.getAttributeName(pathexpr);
|
|
| 208 |
self.append("AND nodetype LIKE 'ATTRIBUTE' AND nodename LIKE '"
|
|
| 209 |
+ attributeName + "' ");
|
|
| 210 |
self.append("AND parentnodeid IN ");
|
|
| 211 |
pathexpr = QuerySpecification
|
|
| 212 |
.newPathExpressionWithOutAttribute(pathexpr);
|
|
| 213 |
|
|
| 214 |
}
|
|
| 215 |
self.append("(SELECT nodeid FROM xml_index WHERE path LIKE "
|
|
| 216 |
+ "'" + pathexpr + "') ");
|
|
| 217 |
} else {
|
|
| 218 |
// without using XML Index; using nested statements instead |
|
| 219 |
self.append("AND parentnodeid IN ");
|
|
| 220 |
self.append(useNestedStatements(pathexpr));
|
|
| 221 |
}
|
|
| 222 |
} else if ((value.trim()).equals("%")) {
|
|
| 223 |
//if pathexpr is null and search value is %, is a
|
|
| 224 |
// percentageSearchItem
|
|
| 225 |
// the count number will be increase one
|
|
| 226 |
countPercentageSearchItem++;
|
|
| 227 |
|
|
| 224 | 228 |
} |
| 225 |
} |
|
| 226 |
else if ((value.trim()).equals("%"))
|
|
| 227 |
{
|
|
| 228 |
//if pathexpr is null and search value is %, is a percentageSearchItem |
|
| 229 |
// the count number will be increase one |
|
| 230 |
countPercentageSearchItem++; |
|
| 231 |
|
|
| 232 |
} |
|
| 233 | 229 |
|
| 234 |
return self.toString(); |
|
| 230 |
return self.toString();
|
|
| 235 | 231 |
} |
| 236 |
|
|
| 237 |
/* A method to judge if a path have attribute */ |
|
| 232 |
|
|
| 233 |
/** A method to judge if a path have attribute */
|
|
| 238 | 234 |
private boolean hasAttributeInPath(String path) |
| 239 | 235 |
{
|
| 240 |
if (path.indexOf(QuerySpecification.ATTRIBUTESYMBOL)!=-1) |
|
| 241 |
{
|
|
| 242 |
return true; |
|
| 243 |
} |
|
| 244 |
else |
|
| 245 |
{
|
|
| 246 |
return false; |
|
| 247 |
} |
|
| 236 |
if (path.indexOf(QuerySpecification.ATTRIBUTESYMBOL) != -1) {
|
|
| 237 |
return true; |
|
| 238 |
} else {
|
|
| 239 |
return false; |
|
| 240 |
} |
|
| 248 | 241 |
} |
| 249 |
|
|
| 250 |
|
|
| 251 |
|
|
| 252 |
/* |
|
| 253 |
* Constraint the query with @pathexp without using the XML Index, |
|
| 254 |
* but nested SQL statements instead. The query migth be slower. |
|
| 242 |
|
|
| 243 |
/* |
|
| 244 |
* Constraint the query with @pathexp without using the XML Index, but |
|
| 245 |
* nested SQL statements instead. The query migth be slower. |
|
| 255 | 246 |
*/ |
| 256 | 247 |
private String useNestedStatements(String pathexpr) |
| 257 | 248 |
{
|
| 258 |
StringBuffer nestedStmts = new StringBuffer(); |
|
| 259 |
Vector nodes = new Vector(); |
|
| 260 |
String path = pathexpr; |
|
| 261 |
int inx = 0; |
|
| 249 |
StringBuffer nestedStmts = new StringBuffer();
|
|
| 250 |
Vector nodes = new Vector();
|
|
| 251 |
String path = pathexpr;
|
|
| 252 |
int inx = 0;
|
|
| 262 | 253 |
|
| 263 |
do {
|
|
| 264 |
inx = path.lastIndexOf("/");
|
|
| 254 |
do {
|
|
| 255 |
inx = path.lastIndexOf("/");
|
|
| 265 | 256 |
|
| 266 |
nodes.addElement(path.substring(inx+1)); |
|
| 267 |
path = path.substring(0, Math.abs(inx)); |
|
| 268 |
} while ( inx > 0 ); |
|
| 269 |
|
|
| 270 |
// nested statements |
|
| 271 |
int i = 0; |
|
| 272 |
for (i = 0; i < nodes.size()-1; i++) {
|
|
| 273 |
nestedStmts.append("(SELECT nodeid FROM xml_nodes" +
|
|
| 274 |
" WHERE nodename LIKE '" + |
|
| 275 |
(String)nodes.elementAt(i) + "'" + |
|
| 276 |
" AND parentnodeid IN "); |
|
| 277 |
} |
|
| 278 |
// for the last statement: it is without " AND parentnodeid IN " |
|
| 279 |
nestedStmts.append("(SELECT nodeid FROM xml_nodes" +
|
|
| 280 |
" WHERE nodename LIKE '" + |
|
| 281 |
(String)nodes.elementAt(i) + "'" ); |
|
| 282 |
// node.size() number of closing brackets |
|
| 283 |
for (i = 0; i < nodes.size(); i++) {
|
|
| 284 |
nestedStmts.append(")");
|
|
| 285 |
} |
|
| 257 |
nodes.addElement(path.substring(inx + 1)); |
|
| 258 |
path = path.substring(0, Math.abs(inx)); |
|
| 259 |
} while (inx > 0); |
|
| 286 | 260 |
|
| 261 |
// nested statements |
|
| 262 |
int i = 0; |
|
| 263 |
for (i = 0; i < nodes.size() - 1; i++) {
|
|
| 264 |
nestedStmts.append("(SELECT nodeid FROM xml_nodes"
|
|
| 265 |
+ " WHERE nodename LIKE '" + (String) nodes.elementAt(i) |
|
| 266 |
+ "'" + " AND parentnodeid IN "); |
|
| 267 |
} |
|
| 268 |
// for the last statement: it is without " AND parentnodeid IN " |
|
| 269 |
nestedStmts.append("(SELECT nodeid FROM xml_nodes"
|
|
| 270 |
+ " WHERE nodename LIKE '" + (String) nodes.elementAt(i) + "'"); |
|
| 271 |
// node.size() number of closing brackets |
|
| 272 |
for (i = 0; i < nodes.size(); i++) {
|
|
| 273 |
nestedStmts.append(")");
|
|
| 274 |
} |
|
| 287 | 275 |
|
| 288 |
|
|
| 289 |
return nestedStmts.toString(); |
|
| 276 |
return nestedStmts.toString(); |
|
| 290 | 277 |
} |
| 291 | 278 |
|
| 292 | 279 |
/** |
| 293 | 280 |
* create a String description of the query that this instance represents. |
| 294 | 281 |
* This should become a way to get the XML serialization of the query. |
| 295 | 282 |
*/ |
| 296 |
public String toString() {
|
|
| 283 |
public String toString() |
|
| 284 |
{
|
|
| 297 | 285 |
|
| 298 |
return this.printSQL(true); |
|
| 286 |
return this.printSQL(true);
|
|
| 299 | 287 |
} |
| 300 |
} |
|
| 288 |
} |
|
Also available in: Unified diff
Created test class QuerySpecificationTest and started process of removing the xml_index from the QuerySpecification code. Reformatted some classes for readability.