Revision 170
Added by Matt Jones over 24 years ago
| src/edu/ucsb/nceas/metacat/QuerySpecification.java | ||
|---|---|---|
| 28 | 28 |
|
| 29 | 29 |
|
| 30 | 30 |
/** |
| 31 |
* A Class that represents a structuredd query,and can be constructed from an
|
|
| 32 |
* XML serialization |
|
| 31 |
* A Class that represents a structured query,and can be constructed from an |
|
| 32 |
* XML serialization conforming to "pathquery.dtd"
|
|
| 33 | 33 |
*/ |
| 34 | 34 |
public class QuerySpecification extends HandlerBase {
|
| 35 | 35 |
|
| ... | ... | |
| 93 | 93 |
try {
|
| 94 | 94 |
FileReader xml = new FileReader(new File(xmlfile)); |
| 95 | 95 |
QuerySpecification qspec = new QuerySpecification(xml); |
| 96 |
System.out.println(qspec); |
|
| 96 |
System.out.println(qspec.toSQL());
|
|
| 97 | 97 |
} catch (IOException e) {
|
| 98 | 98 |
System.err.println(e.getMessage()); |
| 99 | 99 |
} |
| ... | ... | |
| 189 | 189 |
} |
| 190 | 190 |
} |
| 191 | 191 |
|
| 192 |
public String toSQL() {
|
|
| 193 |
StringBuffer self = new StringBuffer(); |
|
| 194 |
|
|
| 195 |
self.append("SELECT DISTINCT docid FROM xml_nodes WHERE \n");
|
|
| 196 |
self.append(query.toSQL()); |
|
| 197 |
|
|
| 198 |
return self.toString(); |
|
| 199 |
} |
|
| 200 |
|
|
| 192 | 201 |
public String toString() {
|
| 193 | 202 |
return "meta_file_id=" + meta_file_id + "\n" + |
| 194 | 203 |
"querytitle=" + querytitle + "\n" + query; |
| ... | ... | |
| 216 | 225 |
return children.elements(); |
| 217 | 226 |
} |
| 218 | 227 |
|
| 228 |
public String toSQL() {
|
|
| 229 |
StringBuffer self = new StringBuffer(); |
|
| 230 |
boolean first = true; |
|
| 231 |
|
|
| 232 |
self.append("(");
|
|
| 233 |
|
|
| 234 |
Enumeration en= getChildren(); |
|
| 235 |
while (en.hasMoreElements()) {
|
|
| 236 |
Object qobject = en.nextElement(); |
|
| 237 |
if (first) {
|
|
| 238 |
first = false; |
|
| 239 |
} else {
|
|
| 240 |
self.append(" " + booleantype + " ");
|
|
| 241 |
} |
|
| 242 |
if (qobject instanceof QueryGroup) {
|
|
| 243 |
QueryGroup qg = (QueryGroup)qobject; |
|
| 244 |
self.append(qg.toSQL()); |
|
| 245 |
} else if (qobject instanceof QueryTerm) {
|
|
| 246 |
QueryTerm qt = (QueryTerm)qobject; |
|
| 247 |
self.append(qt.toSQL()); |
|
| 248 |
} else {
|
|
| 249 |
System.err.println("qobject wrong type: fatal error");
|
|
| 250 |
} |
|
| 251 |
} |
|
| 252 |
self.append(") \n");
|
|
| 253 |
return self.toString(); |
|
| 254 |
} |
|
| 255 |
|
|
| 219 | 256 |
public String toString() {
|
| 220 | 257 |
StringBuffer self = new StringBuffer(); |
| 221 | 258 |
|
| ... | ... | |
| 266 | 303 |
return pathexpr; |
| 267 | 304 |
} |
| 268 | 305 |
|
| 306 |
public String toSQL() {
|
|
| 307 |
StringBuffer self = new StringBuffer(); |
|
| 308 |
|
|
| 309 |
//self.append(" Query Term iscasesensitive=" + casesensitive + "\n");
|
|
| 310 |
|
|
| 311 |
// Uppercase the search string if case match is not important |
|
| 312 |
String casevalue = null; |
|
| 313 |
String nodedataterm = null; |
|
| 314 |
|
|
| 315 |
if (casesensitive) {
|
|
| 316 |
nodedataterm = "nodedata"; |
|
| 317 |
casevalue = value; |
|
| 318 |
} else {
|
|
| 319 |
nodedataterm = "UPPER(nodedata)"; |
|
| 320 |
casevalue = value.toUpperCase(); |
|
| 321 |
} |
|
| 322 |
|
|
| 323 |
// Add appropriate wildcards to search string |
|
| 324 |
String searchvalue = null; |
|
| 325 |
if (searchmode.equals("starts-with")) {
|
|
| 326 |
searchvalue = casevalue + "%"; |
|
| 327 |
} else if (searchmode.equals("ends-with")) {
|
|
| 328 |
searchvalue = "%" + casevalue; |
|
| 329 |
} else if (searchmode.equals("contains")) {
|
|
| 330 |
searchvalue = "%" + casevalue + "%"; |
|
| 331 |
} else {
|
|
| 332 |
searchvalue = casevalue; |
|
| 333 |
} |
|
| 334 |
|
|
| 335 |
self.append("(");
|
|
| 336 |
|
|
| 337 |
if (pathexpr != null) {
|
|
| 338 |
self.append(nodedataterm + " LIKE " + "'" + searchvalue + "' "); |
|
| 339 |
self.append("AND parentnodeid IN ");
|
|
| 340 |
self.append("(SELECT nodeid FROM xml_index WHERE path LIKE " +
|
|
| 341 |
"'" + pathexpr + "') " ); |
|
| 342 |
} else {
|
|
| 343 |
self.append(nodedataterm + " LIKE " + "'" + searchvalue + "' "); |
|
| 344 |
} |
|
| 345 |
|
|
| 346 |
self.append(") \n");
|
|
| 347 |
|
|
| 348 |
return self.toString(); |
|
| 349 |
} |
|
| 350 |
|
|
| 269 | 351 |
public String toString() {
|
| 270 | 352 |
StringBuffer self = new StringBuffer(); |
| 271 | 353 |
|
| src/edu/ucsb/nceas/metacat/DBQuery.java | ||
|---|---|---|
| 34 | 34 |
/** |
| 35 | 35 |
* the main routine used to test the DBQuery utility. |
| 36 | 36 |
* |
| 37 |
* Usage: java DBQuery <query> <user> <password> [dbstring]
|
|
| 37 |
* Usage: java DBQuery <xmlfile> <user> <password> [dbstring]
|
|
| 38 | 38 |
* |
| 39 |
* @param query the text to search for in the element and attribute content
|
|
| 39 |
* @param xmlfile the filename of the xml file containing the query
|
|
| 40 | 40 |
* @param user the username to use for the database connection |
| 41 | 41 |
* @param password the password to use for the database connection |
| 42 | 42 |
* @param dbstring the connection info to use for the database connection |
| 43 | 43 |
*/ |
| 44 | 44 |
static public void main(String[] args) {
|
| 45 | 45 |
|
| 46 |
if (args.length < 4)
|
|
| 46 |
if (args.length < 3)
|
|
| 47 | 47 |
{
|
| 48 | 48 |
System.err.println("Wrong number of arguments!!!");
|
| 49 | 49 |
System.err.println("USAGE: java DBQuery " +
|
| 50 |
"<query> <doctype> <user> <password> [dbstring]");
|
|
| 50 |
"<xmlfile> <user> <password> [dbstring]");
|
|
| 51 | 51 |
return; |
| 52 | 52 |
} else {
|
| 53 | 53 |
try {
|
| 54 | 54 |
|
| 55 |
String query = args[0]; |
|
| 56 |
String doctype = args[1]; |
|
| 57 |
String user = args[2]; |
|
| 58 |
String password = args[3]; |
|
| 55 |
String xmlfile = args[0]; |
|
| 56 |
String user = args[1]; |
|
| 57 |
String password = args[2]; |
|
| 59 | 58 |
String dbstring = null; |
| 60 | 59 |
|
| 61 |
if (args.length <= 4) {
|
|
| 60 |
if (args.length <= 3) {
|
|
| 62 | 61 |
dbstring = defaultDB; |
| 63 | 62 |
} else {
|
| 64 |
dbstring = args[4];
|
|
| 63 |
dbstring = args[3];
|
|
| 65 | 64 |
} |
| 66 | 65 |
|
| 67 | 66 |
// Open a connection to the database |
| 68 | 67 |
Connection dbconn = MetaCatUtil.openDBConnection( |
| 69 | 68 |
"oracle.jdbc.driver.OracleDriver", |
| 70 | 69 |
dbstring, user, password); |
| 71 |
// Execute the simple query
|
|
| 70 |
// Execute the query |
|
| 72 | 71 |
DBQuery rd = new DBQuery(dbconn); |
| 72 |
|
|
| 73 |
FileReader xml = new FileReader(new File(xmlfile)); |
|
| 74 |
//QuerySpecification qspec = new QuerySpecification(xml); |
|
| 75 |
//System.out.println(qspec.toSQL()); |
|
| 76 |
|
|
| 73 | 77 |
Hashtable nodelist = null; |
| 74 |
nodelist = rd.findDocuments(query);
|
|
| 78 |
nodelist = rd.findDocuments(xml);
|
|
| 75 | 79 |
|
| 76 | 80 |
// Print the reulting root nodes |
| 77 | 81 |
StringBuffer result = new StringBuffer(); |
| 78 | 82 |
String document = null; |
| 79 |
Long docid = null;
|
|
| 83 |
String docid = null;
|
|
| 80 | 84 |
result.append("<?xml version=\"1.0\"?>\n");
|
| 81 | 85 |
result.append("<resultset>\n");
|
| 82 |
result.append(" <query>" + query + "</query>\n");
|
|
| 86 |
result.append(" <query>" + xmlfile + "</query>\n");
|
|
| 83 | 87 |
Enumeration doclist = nodelist.keys(); |
| 84 | 88 |
while (doclist.hasMoreElements()) {
|
| 85 |
docid = (Long)doclist.nextElement();
|
|
| 89 |
docid = (String)doclist.nextElement();
|
|
| 86 | 90 |
document = (String)nodelist.get(docid); |
| 87 | 91 |
result.append(" <document>\n " + document +
|
| 88 | 92 |
"\n </document>\n"); |
| ... | ... | |
| 118 | 122 |
/** |
| 119 | 123 |
* routine to search the elements and attributes looking to match query |
| 120 | 124 |
* |
| 121 |
* @param queryspec the xml serialization of the query |
|
| 122 |
* @param requestedDoctype the type of documents to return from the query |
|
| 125 |
* @param queryspec the xml serialization of the query (pathquery.dtd) |
|
| 123 | 126 |
*/ |
| 124 |
public Hashtable findDocuments(String queryspec) {
|
|
| 127 |
public Hashtable findDocuments(Reader queryspec) {
|
|
| 125 | 128 |
Hashtable docListResult = new Hashtable(); |
| 126 | 129 |
|
| 127 |
|
|
| 128 |
//QuerySpecification qspec = new QuerySpecification(queryspec); |
|
| 129 |
|
|
| 130 |
|
|
| 131 |
/* |
|
| 132 | 130 |
PreparedStatement pstmt; |
| 133 | 131 |
|
| 134 | 132 |
// Now look up the document id |
| 135 |
long docid = 0;
|
|
| 133 |
String docid = null;
|
|
| 136 | 134 |
String docname = null; |
| 137 | 135 |
String doctype = null; |
| 138 | 136 |
String doctitle = null; |
| 139 | 137 |
StringBuffer document = null; |
| 140 | 138 |
|
| 141 | 139 |
try {
|
| 142 |
if (requestedDoctype == null || |
|
| 143 |
requestedDoctype.equals("any") ||
|
|
| 144 |
requestedDoctype.equals("ANY")) {
|
|
| 145 |
pstmt = conn.prepareStatement( |
|
| 146 |
"SELECT docid,docname,doctype,doctitle " + |
|
| 147 |
"FROM xml_documents " + |
|
| 148 |
"WHERE docid IN " + |
|
| 149 |
"(SELECT docid " + |
|
| 150 |
"FROM xml_nodes WHERE nodedata LIKE ? )"); |
|
| 140 |
QuerySpecification qspec = new QuerySpecification(queryspec); |
|
| 141 |
|
|
| 142 |
pstmt = conn.prepareStatement( |
|
| 143 |
"SELECT docid,docname,doctype,doctitle " + |
|
| 144 |
"FROM xml_documents WHERE docid IN (" +
|
|
| 145 |
qspec.toSQL() + ")"); |
|
| 151 | 146 |
|
| 152 |
// Bind the values to the query |
|
| 153 |
pstmt.setString(1, query); |
|
| 154 |
} else {
|
|
| 155 |
pstmt = conn.prepareStatement( |
|
| 156 |
"SELECT docid,docname,doctype,doctitle " + |
|
| 157 |
"FROM xml_documents " + |
|
| 158 |
"WHERE docid IN " + |
|
| 159 |
"(SELECT docid " + |
|
| 160 |
"FROM xml_nodes WHERE nodedata LIKE ? ) " + |
|
| 161 |
"AND doctype = ?"); |
|
| 162 |
|
|
| 163 |
// Bind the values to the query |
|
| 164 |
pstmt.setString(1, query); |
|
| 165 |
pstmt.setString(2, requestedDoctype); |
|
| 166 |
} |
|
| 167 |
|
|
| 168 | 147 |
pstmt.execute(); |
| 169 | 148 |
ResultSet rs = pstmt.getResultSet(); |
| 170 | 149 |
boolean tableHasRows = rs.next(); |
| 171 | 150 |
while (tableHasRows) {
|
| 172 |
docid = rs.getLong(1);
|
|
| 151 |
docid = rs.getString(1);
|
|
| 173 | 152 |
docname = rs.getString(2); |
| 174 | 153 |
doctype = rs.getString(3); |
| 175 | 154 |
doctitle = rs.getString(4); |
| ... | ... | |
| 187 | 166 |
} |
| 188 | 167 |
|
| 189 | 168 |
// Store the document id and the root node id |
| 190 |
docListResult.put(new Long(docid),(String)document.toString());
|
|
| 169 |
docListResult.put(docid,(String)document.toString());
|
|
| 191 | 170 |
|
| 192 | 171 |
// Advance to the next record in the cursor |
| 193 | 172 |
tableHasRows = rs.next(); |
| ... | ... | |
| 195 | 174 |
pstmt.close(); |
| 196 | 175 |
} catch (SQLException e) {
|
| 197 | 176 |
System.out.println("Error getting id: " + e.getMessage());
|
| 177 |
} catch (IOException ioe) {
|
|
| 178 |
System.err.println("Error printing qspec:");
|
|
| 179 |
System.err.println(ioe.getMessage()); |
|
| 198 | 180 |
} |
| 199 |
*/ |
|
| 200 | 181 |
return docListResult; |
| 201 | 182 |
} |
| 202 | 183 |
} |
Also available in: Unified diff
continued work on structured query. Now QuerySpecification generates SQL for a given xml input, and DBQuery uses that SQL to send the query to the db