Project

General

Profile

Revision 170

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

View differences:

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