source: trunk/greenstone3-extensions/gs3build/src/org/greenstone/gsdl3/util/SQLQuery.java@ 12188

Last change on this file since 12188 was 12188, checked in by kjdon, 18 years ago

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