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

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

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