Metacat

/**

 *  '$RCSfile$'

 *    Purpose: A Class that implements utility methods like:

 *             1/ Reding all doctypes from db connection

 *             2/ Reading DTD or Schema file from Metacat catalog system

 *             3/ Reading Lore type Data Guide from db connection

 *  Copyright: 2000 Regents of the University of California and the

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Jivka Bojilova

 *    Release: @release@

 * 

 *   '$Author: jones $'

 *     '$Date: 2001-10-19 14:50:57 -0700 (Fri, 19 Oct 2001) $'

 * '$Revision: 847 $'

 *

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

package edu.ucsb.nceas.metacat;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.io.BufferedInputStream;

import java.io.InputStream;

import java.io.IOException;

import java.net.URL;

import java.net.URLConnection;

import java.net.MalformedURLException;

import java.util.Enumeration;

import java.util.Vector;

import java.util.Stack;

/**

 * A suite of utility classes for quering DB

 */

public class DBUtil {

  private Connection	conn = null;

  /**

   * main routine used for testing.

   * <p>

   * Usage: java DBUtil <-dt|-dg>

   *

   * @param -dt for selecting all doctypes

   *        -dg for selecting DataGuide

   */

  static public void main(String[] args) {

     if (args.length < 1)

     {

        System.err.println("Wrong number of arguments!!!");

        System.err.println(

        "USAGE: java DBUtil <-dt | -ds [doctype] | -dg [doctype] | -dl user>");

        return;

     } else {

        try {

          // Open a connection to the database

          MetaCatUtil   util = new MetaCatUtil();

          Connection dbconn = util.openDBConnection();

          DBUtil dbutil = new DBUtil( dbconn );

          if ( args[0].equals("-dt") ) {

            String doctypes = dbutil.readDoctypes();

            System.out.println(doctypes);

          } else if ( args[0].equals("-dg") ) {

            String doctype = null;

            if ( args.length == 2 ) { doctype = args[1]; }

            String dataguide = dbutil.readDataGuide(doctype);

            System.out.println(dataguide);

          } else if ( args[0].equals("-ds") ) {

            String doctype = null;

            if ( args.length == 2 ) { doctype = args[1]; }

            String dtdschema = dbutil.readDTDSchema(doctype);

            System.out.println(dtdschema);

          } else if ( args[0].equals("-dl") ) {

            String scope = "";

            if ( args.length == 2 ) { scope = args[1]; }

            String docid = dbutil.getMaxDocid(scope);

            System.out.println(docid);

          } else {

            System.err.println(

            "USAGE: java DBUtil <-dt | -ds [doctype] | -dg [doctype]>");

          }  

        } catch (Exception e) {

          //System.err.println("error in DBUtil.main");

          //System.err.println(e.getMessage());

          e.printStackTrace(System.err);

        }

     }

  }

  /**

   * Construct an instance of the utility class

   */

  public DBUtil( Connection conn ) {

    this.conn = conn;

  }

  /**

   * read all doctypes from db connection in XML format

   * select all Public Id from xml_catalog table

   */

  public String readDoctypes()

        throws SQLException  {

    Vector doctypeList = new Vector();

    try {

      PreparedStatement pstmt =

        conn.prepareStatement("SELECT public_id FROM xml_catalog " +

                              "WHERE entry_type = 'DTD'");

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tableHasRows = rs.next();

      while (tableHasRows) {

           doctypeList.addElement(rs.getString(1));

           tableHasRows = rs.next();

      }

      pstmt.close();

    } catch (SQLException e) {

      throw new SQLException("DBUtil.readDoctypes(). " + e.getMessage());

    }

    return formatToXML(doctypeList, "doctype");

  }

  /**

   * read DTD or Schema file from Metacat's XML catalog system

   */

  public String readDTDSchema(String doctype)

        throws SQLException, MalformedURLException, IOException

  {

    String systemID = null;

    PreparedStatement pstmt;

    StringBuffer cbuff = new StringBuffer();

    // get doctype's System ID from db catalog

    try {

      pstmt = conn.prepareStatement("SELECT system_id " + 

                                    "FROM xml_catalog " +

                                    "WHERE entry_type in ('DTD','Schema') " +

                                    "AND public_id LIKE ?");

      pstmt.setString(1, doctype);

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean hasRow = rs.next();

      if (hasRow) {

        systemID = rs.getString(1);

      } else {

        throw new SQLException("Non-registered doctype: " + doctype);

      }

      pstmt.close();

    } catch (SQLException e) {

      throw new SQLException("DBUtil.readDTD(). " + e.getMessage());

    }

    // read from URL stream as specified by the System ID.

    try {

      // open a connection to this URL and return an InputStream

      // for reading from that connection

      InputStream istream = new URL(systemID).openStream();

      // create a buffering character-input stream

      // that uses a default-sized input buffer

      BufferedInputStream in = new BufferedInputStream(istream);

      // read the input and write into the string buffer

	    int inputByte;

	    while ( (inputByte = in.read()) != -1 ) {

        cbuff.append((char)inputByte);

	    }

      // the input stream must be closed

	    in.close();

    } catch (MalformedURLException e) {

      throw new MalformedURLException

      ("DBUtil.readDTD(). " + e.getMessage());

    } catch (IOException e) {

      throw new IOException

      ("DBUtil.readDTD(). " + e.getMessage());

    } catch (SecurityException e) {

      throw new IOException

      ("DBUtil.readDTD(). " + e.getMessage());

    }

   return cbuff.toString();

  }

  /**

   * read Data Guide for a given doctype from db connection in XML format

   * select all distinct absolute paths from xml_index table

   */

  public String readDataGuide( String doctype )

        throws SQLException  {

    Vector dataguide = new Vector();

    String path;

    PreparedStatement pstmt;

    try {

        if ( doctype != null ) {

            pstmt = conn.prepareStatement("SELECT distinct path, nodeid " + 

                                          "FROM xml_index " +

                                          "WHERE path LIKE '/%' " + 

                                          "AND doctype LIKE ? " +

                                          "ORDER BY nodeid");

            pstmt.setString(1, doctype);

        } else {

            pstmt = conn.prepareStatement("SELECT distinct path, doctype, nodeid " + 

                                          "FROM xml_index " +

                                          "WHERE path LIKE '/%' " + 

                                          "ORDER BY doctype, nodeid");

        }

        pstmt.execute();

        ResultSet rs = pstmt.getResultSet();

        boolean tableHasRows = rs.next();

        while (tableHasRows) {

            path = rs.getString(1);

            if ( dataguide.indexOf(path) == -1 ) {

                dataguide.addElement(path);

            }    

            tableHasRows = rs.next();

        }

        pstmt.close();

    } catch (SQLException e) {

      throw new SQLException("DBUtil.readDataGuide(). " + e.getMessage());

    }

    return formatToXML(dataguide);

  }

  /**

   * format the DataGuide ResultSet to XML

   */

  private String formatToXML(Vector resultset) {

    String currPath = null;

    String currElement = null;

    String prevElement = null;

    StringBuffer result = new StringBuffer();

    Enumeration rs = resultset.elements(); 

    Stack st = new Stack();

    int i = 0;

    result.append("<?xml version=\"1.0\"?>\n");

    result.append("<resultset>\n"); 

    while (rs.hasMoreElements()) {

        currPath = (String)rs.nextElement();

        while ( !In(prevElement, currPath) ) {

            currElement = (String)st.pop();

            result.append(pad(" ",i--) + "</" + currElement + ">\n");

            if ( st.empty() ) 

                prevElement = null;

            else    

                prevElement = (String)st.peek();

        }    

        currElement = getElementFromPath(currPath);

        st.push(currElement);

        result.append(pad(" ",++i) + "<" + currElement + ">\n");

        prevElement = currElement;

    }

    while ( !st.empty() ) {

        prevElement = (String)st.pop();

        result.append(pad(" ",i--) + "</" + prevElement + ">\n");

    }    

    result.append("</resultset>\n"); 

    return result.toString();

  }

  /**

   * check if element is in path like /elem1/elem2/elemn3

   */

  private boolean In(String element, String path) {

    if ( element == null ) return true;

    return ( path.indexOf(element) != -1 );

  }

  /**

   * get last element from path like /elem1/elem2/elemn3

   */

  private String getElementFromPath(String path) {

    return ( path.substring(path.lastIndexOf("/")+1) );

  }

  /**

   * repeates the str n-times

   */

  private String pad(String str, int n) {

    String result = "";

    for ( int i = 0; i < n; i++ )

        result = result.concat(str);

    return result;    

  }

  /**

   * format the ResultSet to XML

   */

  private String formatToXML(Vector resultset, String tag) {

    String val = null;

    StringBuffer result = new StringBuffer();

    Enumeration rs = resultset.elements(); 

    result.append("<?xml version=\"1.0\"?>\n");

    result.append("<resultset>\n"); 

    while (rs.hasMoreElements()) {

        val = (String)rs.nextElement();

        result.append("   <" + tag + ">" + val + "</" + tag + ">\n");

    }

    result.append("</resultset>\n"); 

    return result.toString();

  }

  /**

   * get the lastest Accession Number from a particular scope

   */

  public String getMaxDocid(String scope)

        throws SQLException  {

    String accnum = null;

    String sep = MetaCatUtil.getOption("accNumSeparator");

    try {

      PreparedStatement pstmt =

        conn.prepareStatement(

            "SELECT docid, rev, acc FROM " +

            "( " +

            "SELECT docid, rev, acc FROM " +

                "(" +

                "SELECT docid, rev, " + 

                "SUBSTR(docid, INSTR(docid, '" + sep + "', 1)+1)+0 acc " +

                "FROM xml_documents " +

                "WHERE docid LIKE ? " +

                "ORDER BY acc DESC " +

                ") " +

            "WHERE rownum = 1 " +

            "UNION " + 

            "SELECT docid, rev, acc FROM " +

                "(" +

                "SELECT docid, rev, " + 

                "SUBSTR(docid, INSTR(docid, '" + sep + "', 1)+1)+0 acc " +

                "FROM xml_revisions " +

                "WHERE docid LIKE ? " +

                "ORDER BY acc DESC " +

                ") " +

            "WHERE rownum = 1 " +

            ") " +

            "ORDER BY acc DESC"

            );

      pstmt.setString(1,scope + sep + "%");

      pstmt.setString(2,scope + sep + "%");

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tableHasRows = rs.next(); 

      // 0, 1 or 2 possible num of rows

      // get the first one which is the max accnum

      if (tableHasRows) {

        accnum = rs.getString(1) + sep + rs.getString(2);

        //tableHasRows = rs.next();

      }

      pstmt.close();

    } catch (SQLException e) {

      throw new SQLException("DBUtil.getMaxDocid(). " + e.getMessage());

    }

    return accnum;

  }

}