source: trunk/gsdl3/src/java/org/greenstone/gsdl3/util/SQLQuery.java@ 9874

Last change on this file since 9874 was 9874, checked in by kjdon, 19 years ago

merged from branch ant-install-branch: merge 1
  • Property svn:keywords set to Author Date Id Revision
File size: 13.4 KB
Line 
1package org.greenstone.gsdl3.util;
2
3import org.greenstone.gsdl3.gs3build.database.GS3SQLConnectionFactory;
4import org.greenstone.gsdl3.gs3build.database.GS3SQLConnection;
5
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9
10import java.util.ArrayList;
11
12public class SQLQuery {
13 GS3SQLConnection connection = null;
14
15 public SQLQuery() {
16
17 }
18
19 public boolean setDatabase(String db_name) {
20 connection = GS3SQLConnectionFactory.getGS3SQLConnection(db_name);
21 try {
22
23 // test the connection
24 String query = "select * from "+GSSQL.DOCUMENT_TABLE+GSSQL.END;
25 Statement s = connection.createStatement();
26 s.execute(query);
27 s.close();
28 return true;
29 } catch (Exception e) {
30 System.err.println("SQLQuery.setDatabase():"+e);
31 }
32 closeConnection();
33 return false;
34 }
35
36 public void closeConnection() {
37 if (connection != null) {
38 connection.close();
39 connection=null;
40 }
41 }
42
43 public String MGNum2OID(String mg_num) {
44 System.out.println("converting mg num to oid "+mg_num);
45 // get doc id and meta id
46 String query = "select "+
47 GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+
48 GSSQL.COMMA+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+
49 " from "+GSSQL.METADATA_VALUE_TABLE+GSSQL.COMMA+GSSQL.NAMESPACE_TABLE+GSSQL.COMMA+GSSQL.METADATA_TABLE+
50 " where "+
51 GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.LABEL+GSSQL.EQUALS_QUOTE+GSSQL.MG_NUM+GSSQL.QUOTE+
52 " and "+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.VALUE+GSSQL.EQUALS_QUOTE+mg_num+GSSQL.QUOTE+
53 " and "+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+
54 " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+GSSQL.EQUALS+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+
55 GSSQL.END;
56
57 Statement statement = null;
58 ResultSet results = null;
59 String doc_id = null;
60 String meta_id = null;
61 try {
62 statement = connection.createStatement();
63 results = statement.executeQuery(query);
64 if (results.first()) {
65 doc_id = results.getString(GSSQL.DOCUMENT_ID);
66 meta_id = results.getString(GSSQL.METADATA_ID);
67 } else {
68 statement.close();
69 return null;
70 }
71 } catch (java.sql.SQLException e) {
72 System.err.println("SQLQuery.MGNum2OID(): "+e);
73 return null;
74 }
75
76 // now get division label
77 query = "select "+
78 GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.SHORT_LABEL+
79 " from "+GSSQL.DIVISION_TABLE+GSSQL.COMMA+GSSQL.DIVISION_METADATA_TABLE+
80 " where "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+GSSQL.EQUALS_QUOTE+meta_id+GSSQL.QUOTE+
81 " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
82 " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+GSSQL.EQUALS+GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+
83 GSSQL.END;
84
85
86 String short_label = null;
87 try {
88 results = statement.executeQuery(query);
89 if (results.first()) {
90 short_label = results.getString(GSSQL.SHORT_LABEL);
91 } else {
92 statement.close();
93 return null;
94 }
95 statement.close();
96 } catch (SQLException e) {
97
98 System.err.println("SQLQuery.MGNum2OID() Error: "+e.getMessage());
99 return null;
100 }
101
102 return GS3OID.createOID(doc_id, short_label);
103 }
104
105 public String OID2MGNum(String oid) {
106 String id = getDocumentMetadata(oid, "gsdl3.mgseqno");
107 return id;
108
109 }
110
111 // does the document have sections, or is it just a single page document?
112 public boolean isHierarchicalDocument(String oid) {
113 // first just check the OID - it may already have section info in it
114 if (!GS3OID.isDocTop(oid)) {
115 return true;
116 }
117
118 // now we need to check the database
119 String query = "select * "+
120 " from "+GSSQL.STRUCTURE_TABLE +
121 " where "+ GSSQL.DOCUMENT_ID +GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
122 " and "+ GSSQL.STRUCTURE_ID +GSSQL.EQUALS_QUOTE + "Section" + GSSQL.QUOTE +
123 GSSQL.END;
124 boolean is_hierarchical = false;
125 try {
126 Statement statement = connection.createStatement();
127 ResultSet results = statement.executeQuery(query);
128 is_hierarchical = results.first();
129 statement.close();
130 } catch (java.sql.SQLException e) {
131 return false;
132 }
133
134 return is_hierarchical;
135 }
136
137 // classifier metadata is not actually metadata
138 public String getClassifierMetadata(String oid, String full_meta_name) {
139
140 if (!full_meta_name.equals("Title") && !full_meta_name.equals("numleafdocs")) {
141 return null;
142 }
143 // get the description
144 String field_name="";
145 if (full_meta_name.equals("Title")) {
146 field_name = GSSQL.DESCRIPTION;
147 } else {
148 field_name = GSSQL.NUM_LEAF_DOCUMENTS;
149 }
150
151 String query = "select "+ field_name+
152 " from " + GSSQL.CLASSIFIER_TABLE +
153 " where " + GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
154 GSSQL.END;
155
156 String value = null;
157 try {
158 Statement statement = connection.createStatement();
159 ResultSet results = statement.executeQuery(query);
160 if (results.first()) {
161 value = results.getString(field_name);
162 }
163 statement.close();
164 } catch (java.sql.SQLException e) {
165
166 System.err.println("SQLQuery.getClassifierMetadata() Error: "+e.getMessage());
167 return null;
168 }
169 return value;
170
171 }
172
173 public ArrayList getDocumentChildren(String oid) {
174
175 ArrayList children = new ArrayList();
176 String doc_id = oid;
177 String section_id = "All"; // if we are just passed a doc id
178 if (!GS3OID.isDocTop(oid)) {
179 doc_id = GS3OID.getDocID(oid);
180 section_id = GS3OID.getSectionLabel(oid);
181 }
182// int sep_index = oid.indexOf(".");
183// if (sep_index != -1) {
184// doc_id = oid.substring(0, sep_index);
185// section_id = oid.substring(sep_index+1);
186// }
187
188 // get the division ref for this section
189 String query = "select "+GSSQL.DIVISION_REF +
190 " from "+GSSQL.DIVISION_TABLE+
191 " where "+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
192 " and "+GSSQL.SHORT_LABEL+GSSQL.EQUALS_QUOTE+section_id+GSSQL.QUOTE+
193 GSSQL.END;
194
195 ResultSet results = null;
196 Statement statement = null;
197 String div_ref = null;
198 try {
199 statement = connection.createStatement();
200 results = statement.executeQuery(query);
201 if (results.first()) {
202 div_ref = results.getString(GSSQL.DIVISION_REF);
203 } else {
204 statement.close();
205 return null;
206 }
207 } catch (java.sql.SQLException e) {
208
209 System.err.println("SQLQuery.getDocumentChildren() Error1: "+e.getMessage());
210 return null;
211 }
212
213 // now get the list of children
214 query = "select "+GSSQL.SHORT_LABEL +
215 " from "+GSSQL.DIVISION_TABLE+
216 " where "+ GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
217 " and "+GSSQL.PARENT_TYPE+GSSQL.EQUALS_QUOTE+GSSQL.DIVISION+GSSQL.QUOTE+
218 " and "+GSSQL.PARENT_REF+GSSQL.EQUALS_QUOTE+div_ref+GSSQL.QUOTE+
219 GSSQL.END;
220 try {
221 results = statement.executeQuery(query);
222 while (results.next()) {
223 String id = results.getString(GSSQL.SHORT_LABEL);
224 children.add(GS3OID.createOID(doc_id, id));
225 }
226 statement.close();
227 } catch (java.sql.SQLException e) {
228
229 System.err.println("SQLQuery.getDocumentChildren() Error2: "+e.getMessage());
230 return null;
231 }
232
233 return children;
234
235 }
236
237 public boolean documentHasChildren(String oid) {
238 // for now, use getChildren, but can we do this a better way??
239 ArrayList children = getDocumentChildren(oid);
240 if (children==null || children.size()==0) {
241 return false;
242 }
243 return true;
244
245 }
246 public ArrayList getClassifierChildren(String oid) {
247
248 ArrayList children = new ArrayList();
249
250 String query = "select "+ GSSQL.CLASSIFIER_ID +
251 " from " + GSSQL.CLASSIFIER_TABLE +
252 " where " + GSSQL.PARENT_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
253 " and " + GSSQL.NUM_LEAF_DOCUMENTS + GSSQL.NOT+GSSQL.EQUALS_QUOTE+ "0"+GSSQL.QUOTE +
254 GSSQL.END;
255
256 try {
257 Statement statement = connection.createStatement();
258 ResultSet results = statement.executeQuery(query);
259 while (results.next()) {
260 String id = results.getString(GSSQL.CLASSIFIER_ID);
261 children.add(id);
262 }
263 statement.close();
264 } catch (java.sql.SQLException e) {
265
266 System.err.println("SQLQuery.getClassifierChildren Error: "+e.getMessage());
267 return null;
268 }
269 return children;
270 }
271
272 public ArrayList getClassifierDocChildren(String oid) {
273 ArrayList children = new ArrayList();
274
275 String query = "select "+ GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+ GSSQL.DOCUMENT_ID+
276 " from " + GSSQL.CLASSIFIER_TABLE + GSSQL.COMMA+GSSQL.CLASS_DOCUMENT_TABLE+
277 " where " + GSSQL.CLASSIFIER_TABLE +GSSQL.DOT+ GSSQL.CLASSIFIER_ID + GSSQL.EQUALS_QUOTE + oid + GSSQL.QUOTE +
278 " and " + GSSQL.CLASSIFIER_TABLE +GSSQL.DOT+ GSSQL.CLASSIFIER_REF+GSSQL.EQUALS+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+ GSSQL.CLASSIFIER_REF+
279 " order by "+GSSQL.CLASS_DOCUMENT_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ORDER+
280 GSSQL.END;
281 try {
282 Statement statement = connection.createStatement();
283 ResultSet results = statement.executeQuery(query);
284 while (results.next()) {
285 String id = results.getString(GSSQL.DOCUMENT_ID);
286 children.add(id);
287 }
288 } catch (java.sql.SQLException e) {
289
290 System.err.println("SQLQuery.getClassifierDocChildren() Error: "+e.getMessage());
291 return null;
292 }
293
294 return children;
295 }
296
297 public String getDocumentMetadata(String oid, String full_meta_name) {
298
299 String doc_id = oid;
300 String section_id = "All"; // if we are just passed a doc id
301 if (!GS3OID.isDocTop(oid)) {
302 doc_id = GS3OID.getDocID(oid);
303 section_id = GS3OID.getSectionLabel(oid);
304 }
305// int sep_index = oid.indexOf(".");
306// if (sep_index != -1) {
307// doc_id = oid.substring(0, sep_index);
308// section_id = oid.substring(sep_index+1);
309// }
310
311
312 // get the metadata block id
313 String query = "select "+
314 GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID +
315 " from " + GSSQL.DIVISION_METADATA_TABLE+GSSQL.COMMA+GSSQL.DIVISION_TABLE+
316 " where " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+GSSQL.EQUALS+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DIVISION_REF+
317 " and " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.SHORT_LABEL+GSSQL.EQUALS_QUOTE+section_id+GSSQL.QUOTE+
318 " and " + GSSQL.DIVISION_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
319 " and "+GSSQL.DIVISION_METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
320 GSSQL.END;
321
322 Statement statement = null;
323 ResultSet results = null;
324 String meta_id = null;
325 try {
326 statement = connection.createStatement();
327 results = statement.executeQuery(query);
328 if (results.first()) {
329 meta_id = results.getString(GSSQL.METADATA_ID);
330 } else {
331 statement.close();
332 return null;
333 }
334 } catch (java.sql.SQLException e) {
335
336 System.err.println("SQLQuery.getDocumentMetadata() Error: "+e.getMessage());
337 return null;
338 }
339
340 // now get the list of namespace refs for the right namespace
341 int sep_index = full_meta_name.indexOf('.');
342 String meta_ns = "gsdl3";
343 String meta_name = full_meta_name;
344 if (sep_index != -1) {
345
346 meta_ns = full_meta_name.substring(0, sep_index);
347 meta_name = full_meta_name.substring(sep_index+1);
348 }
349
350 query = "select * "+
351 " from "+GSSQL.NAMESPACE_TABLE+GSSQL.COMMA+GSSQL.METADATA_TABLE+GSSQL.COMMA+GSSQL.METADATA_VALUE_TABLE+
352 " where "+GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_ID+GSSQL.EQUALS_QUOTE+meta_id+GSSQL.QUOTE+
353 " and "+ GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.DOCUMENT_ID+GSSQL.EQUALS_QUOTE+doc_id+GSSQL.QUOTE+
354 " and " + GSSQL.METADATA_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+GSSQL.EQUALS+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.METADATA_REF+
355 " and "+ GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_TYPE+GSSQL.EQUALS_QUOTE+meta_ns+GSSQL.QUOTE+
356 " and "+GSSQL.NAMESPACE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+GSSQL.EQUALS+GSSQL.METADATA_VALUE_TABLE+GSSQL.DOT+GSSQL.NAMESPACE_REF+
357 GSSQL.END;
358
359 String meta_value = null;
360 try {
361 statement = connection.createStatement();
362 results = statement.executeQuery(query);
363 while (results.next()) {
364 String m_name = results.getString(GSSQL.LABEL);
365 if (meta_name.equals(m_name)) {
366 meta_value = results.getString(GSSQL.VALUE);
367 break;
368 }
369 }
370 statement.close();
371 } catch (java.sql.SQLException e) {
372
373 System.err.println("SQLQuery.getDocumentMetadata() Error: "+e.getMessage());
374 return null;
375 }
376
377 return meta_value;
378 }
379 public static void main (String [] args) {
380
381 SQLQuery self = new SQLQuery();
382 self.setDatabase("gs3test");
383 System.out.println("getting oid for mg num 3");
384 String oid = self.MGNum2OID("stx.3");
385 System.out.println("oid was "+oid);
386
387 System.out.println("title metadata for mg num 3:");
388 String meta = self.getDocumentMetadata(oid, "gsdl3.title");
389 if (meta != null) {System.out.println(meta);}
390 System.out.println("subject metadata for mg num 3:");
391 meta = self.getDocumentMetadata(oid, "gsdl3.Subject");
392 if (meta != null) {System.out.println(meta);}
393
394 System.out.println("converting back to mg, oid "+oid);
395 String id = self.OID2MGNum(oid);
396 System.out.println(""+id);
397
398 System.out.println("getting oid for mg num 8");
399 oid = self.MGNum2OID("stx.8");
400 System.out.println("oid was "+oid);
401
402 System.out.println("title metadata for mg num 8:");
403 meta = self.getDocumentMetadata(oid, "gsdl3.title");
404 if (meta != null) {System.out.println(meta);}
405 System.out.println("subject metadata for mg num 8:");
406 meta = self.getDocumentMetadata(oid, "gsdl3.Subject");
407 if (meta != null) {System.out.println(meta);}
408
409 System.out.println("converting back to mg, oid "+oid);
410 id = self.OID2MGNum(oid);
411 System.out.println(""+id);
412 }
413
414
415
416
417}
Note: See TracBrowser for help on using the repository browser.