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

 * 

 *   '$Author: daigle $'

 *     '$Date: 2009-12-18 13:37:26 -0800 (Fri, 18 Dec 2009) $'

 * '$Revision: 5167 $'

 *

 * 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.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.MalformedURLException;

import java.util.Enumeration;

import java.util.Vector;

import org.apache.log4j.Logger;

import edu.ucsb.nceas.metacat.database.DBConnection;

import edu.ucsb.nceas.metacat.database.DBConnectionPool;

import edu.ucsb.nceas.metacat.properties.PropertyService;

import edu.ucsb.nceas.metacat.util.DocumentUtil;

import edu.ucsb.nceas.metacat.util.SystemUtil;

import edu.ucsb.nceas.utilities.PropertyNotFoundException;

/**

 * A suite of utility classes for querying DB

 */

public class DBUtil {

  //private Connection	conn = null;

  private static Logger logMetacat = Logger.getLogger(DBUtil.class);

  /**

   * 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] | -dl user>");

        return;

     } else {

        try {

          // Open a connection to the database

          //Connection dbconn = util.openDBConnection();

          DBUtil dbutil = new DBUtil();

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

            String doctypes = dbutil.readDoctypes();

            System.out.println(doctypes);

          } 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() {

    //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<String> doctypeList = new Vector<String>();

    DBConnection dbConn = null;

    int serialNumber = -1;

    PreparedStatement pstmt = null;

    try {

      dbConn=DBConnectionPool.

                  getDBConnection("DBUtil.readDoctypes");

      serialNumber=dbConn.getCheckOutSerialNumber();

      pstmt =

        dbConn.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 - SQL error: " + e.getMessage());

    }

    finally

    {

      try

      {

        pstmt.close();

      }//try

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

    return formatToXML(doctypeList, "doctype");

  }

  /**

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

   */

  public String readDTDSchema(String doctype)

        throws SQLException, MalformedURLException, IOException, PropertyNotFoundException

  {

    String systemID = null;

    PreparedStatement pstmt = null;

    StringBuffer cbuff = new StringBuffer();

    DBConnection dbConn = null;

    int serialNumber = -1;

    // get doctype's System ID from db catalog

    try {

      dbConn=DBConnectionPool.

                  getDBConnection("DBUtil.readDTDSchema");

      serialNumber=dbConn.getCheckOutSerialNumber();

      pstmt = dbConn.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);

        // system id may not have server url on front.  Add it if not.

        if (!systemID.startsWith("http://")) {

        	systemID = SystemUtil.getContextURL() + systemID;

        }

      } else {

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

      }

      pstmt.close();

    } catch (SQLException e) {

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

    }

    finally

    {

      try

      {

        pstmt.close();

      }//try

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

    // 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.readDTDSchema - Malformed URL Error: " + e.getMessage());

    } catch (IOException e) {

    	throw new IOException("DBUtil.readDTDSchema - I/O error: " + e.getMessage());

    } catch (SecurityException e) {

    	throw new IOException("DBUtil.readDTDSchema - Security error: " + e.getMessage());

    }

   return cbuff.toString();

  }

//  /**

//   * 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<String> 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<String> resultset, String tag) {

    String val = null;

    StringBuffer result = new StringBuffer();

    Enumeration<String> rs = resultset.elements(); 

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

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

    while (rs.hasMoreElements()) {

        val = rs.nextElement();

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

    }

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

    return result.toString();

  }

  /**

   * get the latest Accession Number from a particular scope

   */

  public String getMaxDocid(String scope)

        throws SQLException  {

    String accnum = null;

    String sep = ".";

    try {

    	PropertyService.getProperty("document.accNumSeparator");

    } catch (PropertyNotFoundException pnfe) {

    	logMetacat.error("DBUtil.getMaxDocid - could not get property " + 

    			"'accNumSeparator'.  setting to '.': " + pnfe.getMessage());  	

    }

    PreparedStatement pstmt = null;

    DBConnection dbConn = null;

    int serialNumber = -1;

    try {

        dbConn=DBConnectionPool.

                  getDBConnection("DBUtil.getMaxDocid");

        serialNumber=dbConn.getCheckOutSerialNumber();

        pstmt =

        dbConn.prepareStatement(

            "SELECT docid, max(rev) FROM " +

            "( " +

                "SELECT docid, rev " + 

                "FROM xml_documents " +

                "WHERE docid LIKE ? " +

            "UNION " + 

                "SELECT docid, rev " + 

                "FROM xml_revisions " +

                "WHERE docid LIKE ?" +

            ") subquery GROUP BY docid"

            );

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

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

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      long max = 0;

      String temp = null;

      while(rs.next()){

    	  temp = rs.getString(1);

    	  if(temp != null){

    		  temp = temp.substring(temp.indexOf(scope) + scope.length() + 1);

    		  try {

    			  long localid = Long.parseLong(temp);

    			  if (localid > max) {

    				  max = localid;

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

    			  }

    		  } catch (NumberFormatException nfe){

    			  // ignore the exception as it is possible that the  

    			  // localid in the identifier is not an integer 

    		  }

    	  }

      }

      pstmt.close();

    } catch (SQLException e) {

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

    }

    finally

    {

      try

      {

        pstmt.close();

      }//try

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

    return accnum;

  }

  /**

   * return true if the given docid is registered in either the xml_documents

   * or xml_revisions table

   */

  public boolean idExists(String docid)

    throws SQLException

  {

    Vector<String> v = getAllDocids(null);

    for(int i=0; i<v.size(); i++)

    {

      String id = (String)v.elementAt(i);

      if(id.trim().equals(docid.trim()))

      {

        return true;

      }

    }

    return false;

  }

  /**

   * return all docids with a given doctype

   */

  public Vector<String> getAllDocidsByType(String doctype, boolean includeRevs) throws SQLException {

		Vector<String> resultVector = new Vector<String>();

		String sep = ".";

	    try {

	    	PropertyService.getProperty("document.accNumSeparator");

	    } catch (PropertyNotFoundException pnfe) {

	    	logMetacat.error("DBUtil.getAllDocidsByType - could not get property " + 

	    			"'accNumSeparator'.  setting to '.': " + pnfe.getMessage());  	

	    }

		PreparedStatement pstmt = null;

		DBConnection dbConn = null;

		int serialNumber = -1;

		try {

			dbConn = DBConnectionPool.getDBConnection("DBUtil.getAllDocidsByType");

			serialNumber = dbConn.getCheckOutSerialNumber();

			StringBuffer sb = new StringBuffer();

			sb.append("SELECT docid, rev FROM " + "( " + "SELECT docid, rev "

					+ "FROM xml_documents ");

			if (doctype != null) {

				sb.append("WHERE doctype LIKE ? ");

			}

			if (includeRevs) {

				sb.append("UNION " + "SELECT docid, rev " + "FROM xml_revisions ");

				if (doctype != null) {

					sb.append("WHERE doctype LIKE ?");

				}

			}

			sb.append(") subquery GROUP BY docid, rev");

			pstmt = dbConn.prepareStatement(sb.toString());

			if (doctype != null) {

				pstmt.setString(1, doctype);

				if (includeRevs) {

					pstmt.setString(2, doctype);

				}

			}

			pstmt.execute();

			ResultSet rs = pstmt.getResultSet();

			String id = null;

			String rev = null;

			while (rs.next()) {

				id = rs.getString(1);

				rev = rs.getString(2);

				if (id != null) {

					resultVector.addElement(id + sep + rev);

				}

			}

			pstmt.close();

		} catch (SQLException e) {

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

		} finally {

			try {

				pstmt.close();

			}// try

			finally {

				DBConnectionPool.returnDBConnection(dbConn, serialNumber);

			}// finally

		}// finally

		return resultVector;

	}

  /**

   * get the latest Accession Number from a particular scope

   */

  public Vector<String> getAllDocids(String scope)

        throws SQLException  {

    Vector<String> resultVector = new Vector<String>();

//    String accnum = null;

    String sep = ".";

    try {

    	PropertyService.getProperty("document.accNumSeparator");

    } catch (PropertyNotFoundException pnfe) {

    	logMetacat.error("could not get property 'accNumSeparator'.  setting to '.': " 

    			+ pnfe.getMessage());  	

    }

    PreparedStatement pstmt = null;

    DBConnection dbConn = null;

    int serialNumber = -1;

    try 

    {

      dbConn=DBConnectionPool.

                getDBConnection("DBUtil.getAllDocids");

      serialNumber=dbConn.getCheckOutSerialNumber();

      StringBuffer sb = new StringBuffer();

      sb.append("SELECT docid, rev FROM " +

                "( " +

                "SELECT docid, rev " + 

                "FROM xml_documents ");

      if(scope != null)

      {

        sb.append("WHERE docid LIKE ? ");

      }

      sb.append("UNION " + 

                "SELECT docid, rev " + 

                "FROM xml_revisions ");

      if(scope != null)

      {

        sb.append("WHERE docid LIKE ?");

      }

      sb.append(") subquery GROUP BY docid, rev");

      pstmt = dbConn.prepareStatement(sb.toString());

      if(scope != null)

      {

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

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

      }

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

//      long max = 0;

      String id = null;

      String rev = null;

      while(rs.next()){

    	  id = rs.getString(1);

        rev = rs.getString(2);

    	  if(id != null){

    		  //temp = temp.substring(id.indexOf(scope) + scope.length() + 1);

          resultVector.addElement(id + sep + rev);

        }

      }

      pstmt.close();

    } catch (SQLException e) {

      throw new SQLException("DBUtil.getAllDocids - SQL error:  " + e.getMessage());

    }

    finally

    {

      try

      {

        pstmt.close();

      }//try

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

    return resultVector;

  }

  /**

   * To a given docid, found a dataset docid which contains the the given docid

   * This will be done by searching xml_relation table

   * If couldn't find, null will be return

   * @param givenDocId, the docid which we want to find

   */

  public static String findDataSetDocIdForGivenDocument(String givenDocId)

  {

    // Prepared statement for sql

    PreparedStatement pStmt = null;

    // Result set

    ResultSet resultSet = null;

    // String to store the data set docid

    String dataSetDocId = null;

    // DBConnection will be checkout

    DBConnection dbConn = null;

    int serialNumber = -1;

    // String to store the sql command

    String sqlCommand = null;

    try

    {

      // Checkout DBConnection from pool

      dbConn=DBConnectionPool.

                  getDBConnection("DBUtil.findDataSetDocIdForGivenDocument");

      serialNumber=dbConn.getCheckOutSerialNumber();

      // SQL command to chose a docid from xm_relation table

      sqlCommand = "select docid from xml_relation where object like ? or " 

                                                    + "subject like ?";

      // Prepared statement

      pStmt = dbConn.prepareStatement(sqlCommand);

      // Bind variable

      pStmt.setString(1, givenDocId);

      pStmt.setString(2, givenDocId);

      // Execute prepared statement

      pStmt.execute();

      // Get result set

      resultSet = pStmt.getResultSet();

      // There has record

      if (resultSet.next())

      {

        // Put the docid into dataSetDocid

        dataSetDocId = resultSet.getString(1);

        return dataSetDocId;

      }//if

      else

      {

        // No record in xml_relation table for given doicd, null returned

        return dataSetDocId;

      }//else

    }//try

    catch ( SQLException e)

    {

      // Print out exception

      logMetacat.error("DBUtil.findDataSetDocIdForGivenDocument - " +

    		  "SQL error: " + e.getMessage());

      // return null

      return dataSetDocId;

    }//catch

    finally

    {

      try

      {

        // Close result set

        resultSet.close();

        // Close preparedStatement

        pStmt.close();

      }//try

      catch ( SQLException e)

      {

        // Print out exception

    	  logMetacat.error("DBUtil.findDataSetDocIdForGivenDocument - " +

    			  "error closing db resources: "  + e.getMessage());

      }//catch

      finally

      {

        // Return DBConnection to the pool

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

  }//findDataSetDocIdForGivenDocument

  /**

   * Method to get current revision and doctype for a given docid

   * The output will look like "rev;doctype"

   * @param givenDocId, the docid which we want 

   */

  public String getCurrentRevisionAndDocTypeForGivenDocument(String givenDocId)

                                                 throws SQLException

  {

    // DBConection for JDBC

    DBConnection dbConn = null;

    int serialNumber = -1;

    // Prepared Statement

    PreparedStatement pstmt = null;

    // String to store a docid without rev

    String docIdWithoutRevision = null;

    // SQL command

    String sqlCommand = null;

    // Result set

    ResultSet rs = null;

    // String to store the revision

    String revision = null;

    // String to store the doctype

    String docType = null;

    // Get docid without rev

    docIdWithoutRevision = DocumentUtil.getDocIdFromString(givenDocId);

    // SQL command is:

    sqlCommand = "select rev, doctype from xml_documents where docid like ?";

    try

    {

      // Check out the connection

      dbConn=DBConnectionPool.

         getDBConnection("DBUtil.getCurrentRevisionAndDocTypeForGivenDocument");

      serialNumber=dbConn.getCheckOutSerialNumber();

      // Prepare the sql command

      pstmt = dbConn.prepareStatement(sqlCommand);

      // Bin variable

      pstmt.setString(1, docIdWithoutRevision);

      // Execute the prepared statement

      pstmt.execute();

      // Get result set

      rs = pstmt.getResultSet();

      // If there is some record

      if (rs.next())

      {

        revision = rs.getString(1);

        docType = rs.getString(2);

      }//if

      else

      {

        // No record, throw a exception

        throw new 

              SQLException("DBUtil.getCurrentRevisionAndDocTypeForGivenDocument - " + 

            		  "There is no record for given docid: " + givenDocId);

      }//else

    }

    finally

    {

      try

      {

        // Close result set

        rs.close();

        // Close preparedStatement

        pstmt.close();

      }//try

      catch ( SQLException e)

      {

        // Print out exception

    	  logMetacat.error("DBUtil.getCurrentRevisionAndDocTypeForGivenDocument - " + 

    			  "Error closing db resources: " + e.getMessage());

      }//catch

      finally

      {

        // Return DBConnection to the pool

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }

    return revision+";"+docType;

  }//getCurrentRevisionAndDocTypeForGivenDocument

  /**

   * Method to return a rev list in xml_revision for given docid.

   * @param docId

   * @return is a vector which contains Integer object

   * @throws SQLException

   */

  public static Vector<Integer> getRevListFromRevisionTable(String docIdWithoutRev) throws SQLException

  {

      Vector<Integer> list = new Vector<Integer>();

      int rev = 1;

      PreparedStatement pStmt = null;

      DBConnection dbConn = null;

      int serialNumber = -1;

      // get rid of rev

      //docId = MetacatUtil.getDocIdFromString(docId);

      try {

          //check out DBConnection

          dbConn = DBConnectionPool

                  .getDBConnection("getRevListFromRevisionTable");

          serialNumber = dbConn.getCheckOutSerialNumber();

          pStmt = dbConn

                  .prepareStatement("SELECT rev FROM xml_revisions WHERE docid='"

                          + docIdWithoutRev + "'");

          pStmt.execute();

          ResultSet rs = pStmt.getResultSet();

          boolean hasRow = rs.next();

          while (hasRow) {

              rev = rs.getInt(1);

              logMetacat.info("DBUtil.getRevListFromRevisionTable - rev: " + rev + 

            		  " is added to list for docid: " + docIdWithoutRev);

              list.add(new Integer(rev));

              hasRow = rs.next();

          }

          pStmt.close();

      }//try

      finally {

          try {

              pStmt.close();

          } catch (Exception ee) {

        	  logMetacat.error("DBUtil.getRevListFromRevisionTable - Error closing " + 

        			  "prepared statement: " + ee.getMessage());

          } finally {

              DBConnectionPool.returnDBConnection(dbConn, serialNumber);

          }

      }//finally

      return list;

  }//getLatestRevisionNumber

  /**

   * Get last revision number from database for a docid If couldn't find an

   * entry, -1 will return The return value is integer because we want compare

   * it to there new one

   *

   * @param docid

   *            <sitecode>. <uniqueid>part of Accession Number

   */

  public static int getLatestRevisionInDocumentTable(String docIdWithoutRev) throws SQLException

  {

      int rev = 1;

      PreparedStatement pStmt = null;

      DBConnection dbConn = null;

      int serialNumber = -1;

      try {

          //check out DBConnection

          dbConn = DBConnectionPool

                  .getDBConnection("DBUtil.getLatestRevisionInDocumentTable");

          serialNumber = dbConn.getCheckOutSerialNumber();

          pStmt = dbConn

                  .prepareStatement("SELECT rev FROM xml_documents WHERE docid='"

                          + docIdWithoutRev + "'");

          pStmt.execute();

          ResultSet rs = pStmt.getResultSet();

          boolean hasRow = rs.next();

          if (hasRow) {

              rev = rs.getInt(1);

              pStmt.close();

          } else {

              rev = -1;

              pStmt.close();

          }

      }//try

      finally {

          try {

              pStmt.close();

          } catch (Exception ee) {

        	  logMetacat.error("DBUtil.getLatestRevisionInDocumentTable - Error closing " + 

        			  " prepared statement: " + ee.getMessage());

          } finally {

              DBConnectionPool.returnDBConnection(dbConn, serialNumber);

          }

      }//finally

      return rev;

  }

}