Project

General

Profile

1 301 bojilova
/**
2
 *  '$RCSfile$'
3
 *    Purpose: A Class that implements utility methods like:
4
 *             1/ Reding all doctypes from db connection
5 699 bojilova
 *             2/ Reading DTD or Schema file from Metacat catalog system
6
 *             3/ Reading Lore type Data Guide from db connection
7 301 bojilova
 *  Copyright: 2000 Regents of the University of California and the
8
 *             National Center for Ecological Analysis and Synthesis
9 315 bojilova
 *    Authors: Jivka Bojilova
10 349 jones
 *    Release: @release@
11 301 bojilova
 *
12
 *   '$Author$'
13
 *     '$Date$'
14
 * '$Revision$'
15 669 jones
 *
16
 * This program is free software; you can redistribute it and/or modify
17
 * it under the terms of the GNU General Public License as published by
18
 * the Free Software Foundation; either version 2 of the License, or
19
 * (at your option) any later version.
20
 *
21
 * This program is distributed in the hope that it will be useful,
22
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
23
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
24
 * GNU General Public License for more details.
25
 *
26
 * You should have received a copy of the GNU General Public License
27
 * along with this program; if not, write to the Free Software
28
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
29 301 bojilova
 */
30
31
package edu.ucsb.nceas.metacat;
32
33
import java.sql.Connection;
34
import java.sql.SQLException;
35
import java.sql.PreparedStatement;
36
import java.sql.ResultSet;
37 699 bojilova
38
import java.io.BufferedInputStream;
39
import java.io.InputStream;
40
import java.io.IOException;
41
import java.net.URL;
42
import java.net.URLConnection;
43
import java.net.MalformedURLException;
44
45 301 bojilova
import java.util.Enumeration;
46
import java.util.Vector;
47 374 bojilova
import java.util.Stack;
48 301 bojilova
49
/**
50
 * A suite of utility classes for quering DB
51
 */
52
public class DBUtil {
53
54 1217 tao
  //private Connection	conn = null;
55 301 bojilova
56
  /**
57
   * main routine used for testing.
58
   * <p>
59
   * Usage: java DBUtil <-dt|-dg>
60
   *
61
   * @param -dt for selecting all doctypes
62
   *        -dg for selecting DataGuide
63
   */
64
  static public void main(String[] args) {
65
66
     if (args.length < 1)
67
     {
68
        System.err.println("Wrong number of arguments!!!");
69 699 bojilova
        System.err.println(
70 2067 jones
        "USAGE: java DBUtil <-dt | -ds [doctype] | -dl user>");
71 301 bojilova
        return;
72
     } else {
73
        try {
74
75
          // Open a connection to the database
76
          MetaCatUtil   util = new MetaCatUtil();
77 1217 tao
          //Connection dbconn = util.openDBConnection();
78 301 bojilova
79 1217 tao
          DBUtil dbutil = new DBUtil();
80 301 bojilova
81
          if ( args[0].equals("-dt") ) {
82
            String doctypes = dbutil.readDoctypes();
83
            System.out.println(doctypes);
84 699 bojilova
          } else if ( args[0].equals("-ds") ) {
85
            String doctype = null;
86
            if ( args.length == 2 ) { doctype = args[1]; }
87
            String dtdschema = dbutil.readDTDSchema(doctype);
88
            System.out.println(dtdschema);
89 793 bojilova
          } else if ( args[0].equals("-dl") ) {
90 847 jones
            String scope = "";
91
            if ( args.length == 2 ) { scope = args[1]; }
92
            String docid = dbutil.getMaxDocid(scope);
93 793 bojilova
            System.out.println(docid);
94 301 bojilova
          } else {
95 699 bojilova
            System.err.println(
96
            "USAGE: java DBUtil <-dt | -ds [doctype] | -dg [doctype]>");
97 301 bojilova
          }
98
99
        } catch (Exception e) {
100 699 bojilova
          //System.err.println("error in DBUtil.main");
101
          //System.err.println(e.getMessage());
102 301 bojilova
          e.printStackTrace(System.err);
103
        }
104
     }
105
  }
106
107
  /**
108
   * Construct an instance of the utility class
109
   */
110 1217 tao
  public DBUtil() {
111
    //this.conn = conn;
112 301 bojilova
  }
113
114
  /**
115
   * read all doctypes from db connection in XML format
116
   * select all Public Id from xml_catalog table
117
   */
118
  public String readDoctypes()
119
        throws SQLException  {
120
121
    Vector doctypeList = new Vector();
122 1217 tao
    DBConnection dbConn = null;
123
    int serialNumber = -1;
124
    PreparedStatement pstmt = null;
125 301 bojilova
    try {
126
127 1217 tao
      dbConn=DBConnectionPool.
128
                  getDBConnection("DBUtil.readDoctypes");
129
      serialNumber=dbConn.getCheckOutSerialNumber();
130
      pstmt =
131
        dbConn.prepareStatement("SELECT public_id FROM xml_catalog " +
132 413 bojilova
                              "WHERE entry_type = 'DTD'");
133 301 bojilova
134
      pstmt.execute();
135
      ResultSet rs = pstmt.getResultSet();
136
      boolean tableHasRows = rs.next();
137
      while (tableHasRows) {
138
           doctypeList.addElement(rs.getString(1));
139
           tableHasRows = rs.next();
140
      }
141
142
      pstmt.close();
143
144
    } catch (SQLException e) {
145 699 bojilova
      throw new SQLException("DBUtil.readDoctypes(). " + e.getMessage());
146 301 bojilova
    }
147 1217 tao
    finally
148
    {
149
      try
150
      {
151
        pstmt.close();
152
      }//try
153
      finally
154
      {
155
        DBConnectionPool.returnDBConnection(dbConn, serialNumber);
156
      }//finally
157
    }//finally
158
159 301 bojilova
160
    return formatToXML(doctypeList, "doctype");
161
  }
162
163
  /**
164 699 bojilova
   * read DTD or Schema file from Metacat's XML catalog system
165
   */
166
  public String readDTDSchema(String doctype)
167
        throws SQLException, MalformedURLException, IOException
168
  {
169
    String systemID = null;
170 1217 tao
    PreparedStatement pstmt = null;
171 699 bojilova
    StringBuffer cbuff = new StringBuffer();
172 1217 tao
    DBConnection dbConn = null;
173
    int serialNumber = -1;
174 699 bojilova
    // get doctype's System ID from db catalog
175
    try {
176 1217 tao
177
      dbConn=DBConnectionPool.
178
                  getDBConnection("DBUtil.readDTDSchema");
179
      serialNumber=dbConn.getCheckOutSerialNumber();
180
      pstmt = dbConn.prepareStatement("SELECT system_id " +
181 699 bojilova
                                    "FROM xml_catalog " +
182
                                    "WHERE entry_type in ('DTD','Schema') " +
183
                                    "AND public_id LIKE ?");
184
      pstmt.setString(1, doctype);
185
      pstmt.execute();
186
      ResultSet rs = pstmt.getResultSet();
187
      boolean hasRow = rs.next();
188
      if (hasRow) {
189
        systemID = rs.getString(1);
190
      } else {
191
        throw new SQLException("Non-registered doctype: " + doctype);
192
      }
193
      pstmt.close();
194
195
    } catch (SQLException e) {
196
      throw new SQLException("DBUtil.readDTD(). " + e.getMessage());
197
    }
198 1217 tao
    finally
199
    {
200
      try
201
      {
202
        pstmt.close();
203
      }//try
204
      finally
205
      {
206
        DBConnectionPool.returnDBConnection(dbConn, serialNumber);
207
      }//finally
208
    }//finally
209 699 bojilova
210
    // read from URL stream as specified by the System ID.
211
    try {
212
      // open a connection to this URL and return an InputStream
213
      // for reading from that connection
214
      InputStream istream = new URL(systemID).openStream();
215
      // create a buffering character-input stream
216
      // that uses a default-sized input buffer
217
      BufferedInputStream in = new BufferedInputStream(istream);
218
219
      // read the input and write into the string buffer
220
	    int inputByte;
221
	    while ( (inputByte = in.read()) != -1 ) {
222
        cbuff.append((char)inputByte);
223
	    }
224
225
      // the input stream must be closed
226
	    in.close();
227
228
    } catch (MalformedURLException e) {
229
      throw new MalformedURLException
230
      ("DBUtil.readDTD(). " + e.getMessage());
231
    } catch (IOException e) {
232
      throw new IOException
233
      ("DBUtil.readDTD(). " + e.getMessage());
234
    } catch (SecurityException e) {
235
      throw new IOException
236
      ("DBUtil.readDTD(). " + e.getMessage());
237
    }
238
239
   return cbuff.toString();
240
  }
241
242
  /**
243 374 bojilova
   * format the DataGuide ResultSet to XML
244
   */
245
  private String formatToXML(Vector resultset) {
246
247
    String currPath = null;
248
    String currElement = null;
249
    String prevElement = null;
250
    StringBuffer result = new StringBuffer();
251
    Enumeration rs = resultset.elements();
252
    Stack st = new Stack();
253
    int i = 0;
254
255
    result.append("<?xml version=\"1.0\"?>\n");
256
    result.append("<resultset>\n");
257
258
    while (rs.hasMoreElements()) {
259
        currPath = (String)rs.nextElement();
260
        while ( !In(prevElement, currPath) ) {
261
            currElement = (String)st.pop();
262
            result.append(pad(" ",i--) + "</" + currElement + ">\n");
263
            if ( st.empty() )
264
                prevElement = null;
265
            else
266
                prevElement = (String)st.peek();
267
        }
268
        currElement = getElementFromPath(currPath);
269
        st.push(currElement);
270
        result.append(pad(" ",++i) + "<" + currElement + ">\n");
271
        prevElement = currElement;
272
    }
273
    while ( !st.empty() ) {
274
        prevElement = (String)st.pop();
275
        result.append(pad(" ",i--) + "</" + prevElement + ">\n");
276
    }
277
    result.append("</resultset>\n");
278
279
    return result.toString();
280
  }
281
282
  /**
283
   * check if element is in path like /elem1/elem2/elemn3
284
   */
285
  private boolean In(String element, String path) {
286
287
    if ( element == null ) return true;
288
    return ( path.indexOf(element) != -1 );
289
  }
290
291
  /**
292
   * get last element from path like /elem1/elem2/elemn3
293
   */
294
  private String getElementFromPath(String path) {
295
296
    return ( path.substring(path.lastIndexOf("/")+1) );
297
  }
298
299
  /**
300
   * repeates the str n-times
301
   */
302
  private String pad(String str, int n) {
303
304
    String result = "";
305
    for ( int i = 0; i < n; i++ )
306
        result = result.concat(str);
307
308
    return result;
309
  }
310
311
  /**
312 301 bojilova
   * format the ResultSet to XML
313
   */
314
  private String formatToXML(Vector resultset, String tag) {
315
316
    String val = null;
317
    StringBuffer result = new StringBuffer();
318
    Enumeration rs = resultset.elements();
319
320
    result.append("<?xml version=\"1.0\"?>\n");
321
    result.append("<resultset>\n");
322
    while (rs.hasMoreElements()) {
323
        val = (String)rs.nextElement();
324
        result.append("   <" + tag + ">" + val + "</" + tag + ">\n");
325
    }
326
    result.append("</resultset>\n");
327
328
    return result.toString();
329
  }
330
331 793 bojilova
  /**
332 847 jones
   * get the lastest Accession Number from a particular scope
333 793 bojilova
   */
334 847 jones
  public String getMaxDocid(String scope)
335 793 bojilova
        throws SQLException  {
336 301 bojilova
337 793 bojilova
    String accnum = null;
338
    String sep = MetaCatUtil.getOption("accNumSeparator");
339 1217 tao
    PreparedStatement pstmt = null;
340
    DBConnection dbConn = null;
341
    int serialNumber = -1;
342 793 bojilova
    try {
343 1217 tao
        dbConn=DBConnectionPool.
344
                  getDBConnection("DBUtil.getMaxDocid");
345
        serialNumber=dbConn.getCheckOutSerialNumber();
346
        pstmt =
347
        dbConn.prepareStatement(
348 2560 sgarg
            "SELECT docid, max(rev) FROM " +
349 847 jones
            "( " +
350 2560 sgarg
                "SELECT docid, rev " +
351 847 jones
                "FROM xml_documents " +
352
                "WHERE docid LIKE ? " +
353
            "UNION " +
354 2560 sgarg
                "SELECT docid, rev " +
355 847 jones
                "FROM xml_revisions " +
356
                "WHERE docid LIKE ? " +
357 2560 sgarg
            ") AS subQueryResult" +
358
            " GROUP BY docid"
359 847 jones
            );
360
361
      pstmt.setString(1,scope + sep + "%");
362
      pstmt.setString(2,scope + sep + "%");
363 793 bojilova
      pstmt.execute();
364
      ResultSet rs = pstmt.getResultSet();
365 2560 sgarg
366
      int max = 0;
367
      String temp = null;
368
369
      while(rs.next()){
370
    	  temp = rs.getString(1);
371
    	  if(temp != null){
372
    		  temp = temp.substring(temp.indexOf(scope) + scope.length() + 1);
373
    		  try {
374
    			  int localid = Integer.parseInt(temp);
375
    			  if (localid > max){
376
    				  max = localid;
377
    				  accnum = rs.getString(1) + sep + rs.getString(2);
378
    			  }
379
    		  } catch (NumberFormatException nfe){
380
    			  // ignore the exception as it is possible that the
381
    			  // localid in the identifier is not an integer
382
    		  }
383
    	  }
384 793 bojilova
      }
385
386
      pstmt.close();
387
388
    } catch (SQLException e) {
389 847 jones
      throw new SQLException("DBUtil.getMaxDocid(). " + e.getMessage());
390 793 bojilova
    }
391 1217 tao
    finally
392
    {
393
      try
394
      {
395
        pstmt.close();
396
      }//try
397
      finally
398
      {
399
        DBConnectionPool.returnDBConnection(dbConn, serialNumber);
400
      }//finally
401
    }//finally
402 793 bojilova
403
    return accnum;
404
  }
405 1292 tao
406
  /**
407
   * To a given docid, found a dataset docid which conatains the the given doicd
408
   * This will be done by searching xml_relation table
409
   * If couldn't find, null will be return
410
   * @param givenDocId, the docid which we want to find
411
   */
412
  public static String findDataSetDocIdForGivenDocument(String givenDocId)
413
  {
414
    // Prepared statement for sql
415
    PreparedStatement pStmt = null;
416
    // Result set
417
    ResultSet resultSet = null;
418
    // String to store the data set docid
419
    String dataSetDocId = null;
420
    // DBConnection will be checkout
421
    DBConnection dbConn = null;
422
    int serialNumber = -1;
423
    // String to store the sql command
424
    String sqlCommand = null;
425
    try
426
    {
427
      // Checkout DBConnection from pool
428
      dbConn=DBConnectionPool.
429
                  getDBConnection("DBUtil.findDataSetDocIdForGivenDocument");
430
      serialNumber=dbConn.getCheckOutSerialNumber();
431
      // Sql command to chose a docid from xm_relation table
432
      sqlCommand = "select docid from xml_relation where object like ? or "
433
                                                    + "subject like ?";
434
      // Prepared statement
435
      pStmt = dbConn.prepareStatement(sqlCommand);
436
      // Bind variable
437
      pStmt.setString(1, givenDocId);
438
      pStmt.setString(2, givenDocId);
439
      // Excute prepared statement
440
      pStmt.execute();
441
      // Get result set
442
      resultSet = pStmt.getResultSet();
443
444
      // There has record
445
      if (resultSet.next())
446
      {
447
        // Put the docid into dataSetDocid
448
        dataSetDocId = resultSet.getString(1);
449
        return dataSetDocId;
450
      }//if
451
      else
452
      {
453
        // No record in xml_relation table for given doicd, null returned
454
        return dataSetDocId;
455
      }//else
456
457
    }//try
458
    catch ( SQLException e)
459
    {
460
      // Print out excepition
461
      MetaCatUtil.debugMessage("Error in DBUil.findDataSEtDocIdForGivenDocument"
462
                                +e.getMessage(), 30);
463
      // return null
464
      return dataSetDocId;
465
466
    }//catch
467
    finally
468
    {
469
      try
470
      {
471
        // Close result set
472
        resultSet.close();
473
        // Close preparedStatement
474
        pStmt.close();
475
      }//try
476
      catch ( SQLException e)
477
      {
478
        // Print out excepition
479
        MetaCatUtil.debugMessage("Error in DBUil.findDataSetDocIdForGivenDoc"
480
                                + e.getMessage(), 30);
481
482
      }//catch
483
      finally
484
      {
485
        // Return DBConnection to the pool
486
        DBConnectionPool.returnDBConnection(dbConn, serialNumber);
487
      }//finally
488
    }//finally
489
490
  }//findDataSetDocIdForGivenDocument
491
492
  /**
493
   * Method to get current revision and doctype for a given docid
494
   * The output will look like "rev;doctype"
495
   * @param givenDocId, the docid which we want
496
   */
497
  public String getCurrentRevisionAndDocTypeForGivenDocument(String givenDocId)
498
                                                 throws SQLException
499
  {
500
    // DBConection for JDBC
501
    DBConnection dbConn = null;
502
    int serialNumber = -1;
503
    // Prepared Statement
504
    PreparedStatement pstmt = null;
505
    // String to store a docid without rev
506
    String docIdWithoutRevision = null;
507
    // SQL comand
508
    String sqlCommand = null;
509
    // Resulst set
510
    ResultSet rs = null;
511
    // String to store the revision
512
    String revision = null;
513
    // String to store the doctype
514
    String docType = null;
515
516
    // Get docid without rev
517
    docIdWithoutRevision = MetaCatUtil.getDocIdFromString(givenDocId);
518
    // SQL comand is:
519
    sqlCommand = "select rev, doctype from xml_documents where docid like ?";
520
521
    try
522
    {
523
      // Check out the connection
524
      dbConn=DBConnectionPool.
525
         getDBConnection("DBUtil.getCurrentRevisionAndDocTypeForGivenDocument");
526
      serialNumber=dbConn.getCheckOutSerialNumber();
527
528
      // Prepare the sql command
529
      pstmt = dbConn.prepareStatement(sqlCommand);
530
      // Bin vairable
531
      pstmt.setString(1, docIdWithoutRevision);
532
      // Excute the prepared statement
533
      pstmt.execute();
534
      // Get result set
535
      rs = pstmt.getResultSet();
536
      // If there is some record
537
      if (rs.next())
538
      {
539
        revision = rs.getString(1);
540
        docType = rs.getString(2);
541
      }//if
542
      else
543
      {
544
        // No record, throw a exception
545
        throw new
546
              SQLException("There is not record for given docid:"+givenDocId);
547
      }//else
548
549
    }
550
    finally
551
    {
552
      try
553
      {
554
        // Close result set
555
        rs.close();
556
        // Close preparedStatement
557
        pstmt.close();
558
      }//try
559
      catch ( SQLException e)
560
      {
561
        // Print out excepition
562
        MetaCatUtil.debugMessage("Error in DBUil.getCurrentRevisionAndDocType"
563
                                + e.getMessage(), 30);
564
565
      }//catch
566
      finally
567
      {
568
        // Return DBConnection to the pool
569
        DBConnectionPool.returnDBConnection(dbConn, serialNumber);
570
      }//finally
571
    }
572
    return revision+";"+docType;
573
  }//getCurrentRevisionAndDocTypeForGivenDocument
574
575 301 bojilova
}