Metacat

/**

 *  '$RCSfile$'

 *    Purpose: A Class that searches a relational DB for elements and 

 *             attributes that have free text matches a query string,

 *             or structured query matches to a path specified node in the 

 *             XML hierarchy.  It returns a result set consisting of the 

 *             document ID for each document that satisfies the query

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

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Matt Jones

 *    Release: @release@

 *

 *   '$Author: bojilova $'

 *     '$Date: 2001-02-23 14:14:30 -0800 (Fri, 23 Feb 2001) $'

 * '$Revision: 706 $'

 *

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

import java.util.Vector;

import java.net.URL;

import java.net.MalformedURLException;

import java.sql.*;

import java.util.Stack;

import java.util.Hashtable;

import java.util.Enumeration;

import java.io.File;

import java.io.FileWriter;

import java.io.BufferedWriter;

/** 

 * A Class that searches a relational DB for elements and 

 * attributes that have free text matches a query string,

 * or structured query matches to a path specified node in the 

 * XML hierarchy.  It returns a result set consisting of the 

 * document ID for each document that satisfies the query

 */

public class DBQuery {

  static final int ALL = 1;

  static final int WRITE = 2;

  static final int READ = 4;

  private Connection  conn = null;

  private String  parserName = null;

  private MetaCatUtil util = new MetaCatUtil();

  /**

   * the main routine used to test the DBQuery utility.

   * <p>

   * Usage: java DBQuery <xmlfile>

   *

   * @param xmlfile the filename of the xml file containing the query

   */

  static public void main(String[] args) {

     if (args.length < 1)

     {

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

        System.err.println("USAGE: java DBQuery [-t] [-index] <xmlfile>");

        return;

     } else {

        try {

          int i = 0;

          boolean showRuntime = false;

          boolean useXMLIndex = false;

          if ( args[i].equals( "-t" ) ) {

            showRuntime = true;

            i++;

          }

          if ( args[i].equals( "-index" ) ) {

            useXMLIndex = true;

            i++;

          } 

          String xmlfile  = args[i];

          // Time the request if asked for

          double startTime = System.currentTimeMillis();

          // Open a connection to the database

          MetaCatUtil   util = new MetaCatUtil();

          Connection dbconn = util.openDBConnection();

          double connTime = System.currentTimeMillis();

          // Execute the query

          DBQuery queryobj = new DBQuery(dbconn, util.getOption("saxparser"));

          FileReader xml = new FileReader(new File(xmlfile));

          Hashtable nodelist = null;

          nodelist = queryobj.findDocuments(xml, null, null, useXMLIndex);

          // Print the reulting document listing

          StringBuffer result = new StringBuffer();

          String document = null;

          String docid = null;

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

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

  // following line removed by Dan Higgins to avoid insertion of query XML inside returned XML doc

  //        result.append("  <query>" + xmlfile + "</query>\n");

          Enumeration doclist = nodelist.keys(); 

          while (doclist.hasMoreElements()) {

            docid = (String)doclist.nextElement();

            document = (String)nodelist.get(docid);

            result.append("  <document>\n    " + document + 

                          "\n  </document>\n");

          }

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

          // Time the request if asked for

          double stopTime = System.currentTimeMillis();

          double dbOpenTime = (connTime - startTime)/1000;

          double readTime = (stopTime - connTime)/1000;

          double executionTime = (stopTime - startTime)/1000;

          if (showRuntime) {

            System.out.println("\n\nTotal Execution time was: " + 

                               executionTime + " seconds.");

            System.out.println("Time to open DB connection was: " + dbOpenTime + 

                               " seconds.");

            System.out.println("Time to read was: " + readTime +

                               " seconds.");

          }

          //System.out.println(result);

          //write into a file "result.txt"

          File f = new File("./result.txt");

          FileWriter fw = new FileWriter(f);

          BufferedWriter out = new BufferedWriter(fw);

          out.write(result.toString());          

    	    out.flush();

	        out.close();

	        fw.close();

        } catch (Exception e) {

          System.err.println("Error in DBQuery.main");

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

          e.printStackTrace(System.err);

        }

     }

  }

  /**

   * construct an instance of the DBQuery class 

   *

   * <p>Generally, one would call the findDocuments() routine after creating 

   * an instance to specify the search query</p>

   *

   * @param conn the JDBC connection that we use for the query

   * @param parserName the fully qualified name of a Java class implementing

   *                   the org.xml.sax.XMLReader interface

   */

  public DBQuery( Connection conn, String parserName ) 

                  throws IOException, 

                         SQLException, 

                         ClassNotFoundException {

    this.conn = conn;

    this.parserName = parserName;

  }

  public Hashtable findDocuments(Reader xmlquery, String user, String group,

                                 boolean useXMLIndex)

  {

    return findDocuments(xmlquery, user, group, null, useXMLIndex);

  }

  public Hashtable findDocuments(Reader xmlquery, String user, String group,

                                 String[] returndoc)

  {

    return findDocuments(xmlquery, user, group, returndoc, true);

  }

  /** 

   * routine to search the elements and attributes looking to match query

   *

   * @param xmlquery the xml serialization of the query (@see pathquery.dtd)

   * @param user the username of the user

   * @param group the group of the user

   * @param returndoc an array of document types to backtrack against.

   */

  public Hashtable findDocuments(Reader xmlquery, String user, String group,

                                 String[] returndoc, boolean useXMLIndex)

  {

    //System.out.println("in finddocuments");

      Hashtable   docListResult = new Hashtable();

      PreparedStatement pstmt = null;

      String docid = null;

      String docname = null;

      String doctype = null;

// DOCTITLE attr cleared from the db

//      String doctitle = null;

      String createDate = null;

      String updateDate = null;

      String fieldname = null;

      String fielddata = null;

      String relation = null;

      Connection dbconn = null;

      int rev = 0;

      StringBuffer document = null; 

      Vector returndocVec = new Vector();

      if(returndoc != null)

      {//add the returndoc elements to a vector for easier manipulation

        for(int i=0; i<returndoc.length; i++)

        {

          returndocVec.add(new String((String)returndoc[i]));

        }

      }

      try {

        dbconn = util.openDBConnection();

        // Get the XML query and covert it into a SQL statment

        QuerySpecification qspec = new QuerySpecification(xmlquery, 

                                   parserName, 

                                   util.getOption("accNumSeparator"));

       // System.out.println(qspec.printSQL());

        pstmt = dbconn.prepareStatement( qspec.printSQL(useXMLIndex) );

        // Execute the SQL query using the JDBC connection

        pstmt.execute();

        ResultSet rs = pstmt.getResultSet();

        boolean tableHasRows = rs.next();

        while (tableHasRows) 

        {

          docid = rs.getString(1);

          if ( !hasPermission(dbconn, user, group, docid) ) {

            // Advance to the next record in the cursor

            tableHasRows = rs.next();

            continue;

          }

          docname = rs.getString(2);

          doctype = rs.getString(3);

// DOCTITLE attr cleared from the db

//          doctitle = rs.getString(4);

          createDate = rs.getString(4);

          updateDate = rs.getString(5);

          rev = rs.getInt(6);

          //System.out.println("vec.size = " + returndocVec.size());

          if(returndocVec.size() != 0 && !returndocVec.contains(doctype))

          { //there are returndocs to match (backtracking can now be performed). 

            //System.out.println("olddoctype: " + doctype);

            StringBuffer btBuf = new StringBuffer();

            btBuf.append("select object from xml_relation where ");

            btBuf.append("objdoctype in (");

            //build the doctype list for the backtracking sql statement

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

            {

              btBuf.append("'").append((String)returndocVec.get(i)).append("'");

              if(i != (returndocVec.size() - 1))

              {

                btBuf.append(", ");

              } 

            }

            btBuf.append(") ");

            btBuf.append("and subject like '");

            //btBuf.append("metacat://").append(util.getOption("server"));

            //btBuf.append("?docid=").append(docid).append("'");

            btBuf.append("%docid=").append(docid).append("'");

            //System.out.println("sql: " + btBuf.toString());

            PreparedStatement npstmt = dbconn.prepareStatement(btBuf.toString());

            npstmt.execute();

            ResultSet btrs = npstmt.getResultSet();

            boolean hasBtRows = btrs.next();

            if(hasBtRows)

            { //there was a backtrackable document found

              DocumentImpl xmldoc = null;

              //System.out.println("document found is: " + btrs.getString(1));

              MetacatURL objURL = new MetacatURL(btrs.getString(1));

              try

              {

                xmldoc = new DocumentImpl(dbconn, objURL.getParam(0)[1]);

              }

              catch(Exception e)

              {

                System.out.println("Error getting document in " + 

                                   "DBQuery.findDocuments: " + e.getMessage());

              }

              docid   = xmldoc.getDocID();

              docname = xmldoc.getDocname();

              doctype = xmldoc.getDoctype();

// DOCTITLE attr cleared from the db

//              doctitle = xmldoc.getDocTitle();

              createDate = xmldoc.getCreateDate();

              updateDate = xmldoc.getUpdateDate();

              //System.out.println("docname: " + docname + " doctype: " + doctype + 

              //                   " doctitle: " + doctitle + " createdate: " +

              //                   createDate + " updatedate: " + updateDate);

            }

            npstmt.close();

            btrs.close();

          }

          document = new StringBuffer();

          //System.out.println("packagdoctype: " + util.getOption("packagedoctype"));

          //if(!doctype.equals(util.getOption("packagedoctype")))

          {

            String completeDocid = docid + util.getOption("accNumSeparator");

            completeDocid += rev;

            document.append("<docid>").append(completeDocid).append("</docid>");

            if (docname != null) {

              document.append("<docname>" + docname + "</docname>");

            }

            if (doctype != null) {

              document.append("<doctype>" + doctype + "</doctype>");

            }

// DOCTITLE attr cleared from the db

//            if (doctitle != null) {

//              document.append("<doctitle>" + doctitle + "</doctitle>");

//            }

            if(createDate != null) {

              document.append("<createdate>" + createDate + "</createdate>");

            }

            if(updateDate != null) {

              document.append("<updatedate>" + updateDate + "</updatedate>");

            }

            // Store the document id and the root node id

            docListResult.put(docid,(String)document.toString());

          }

          // Advance to the next record in the cursor

          tableHasRows = rs.next();

        }

        rs.close();

        //pstmt.close();

        if(qspec.containsExtendedSQL())

        {

          Vector extendedFields = new Vector(qspec.getReturnFieldList());

          Vector results = new Vector();

          Enumeration keylist = docListResult.keys();

          StringBuffer doclist = new StringBuffer();

          while(keylist.hasMoreElements())

          {

            doclist.append("'");

            doclist.append((String)keylist.nextElement());

            doclist.append("',");

          }

          doclist.deleteCharAt(doclist.length()-1); //remove the last comma

          pstmt.close();

          pstmt = dbconn.prepareStatement(qspec.printExtendedSQL(

                                        doclist.toString()));

          pstmt.execute();

          rs = pstmt.getResultSet();

          tableHasRows = rs.next();

          while(tableHasRows) 

          {

            docid = rs.getString(1);

            if ( !hasPermission(dbconn, user, group, docid) ) {

              // Advance to the next record in the cursor

              tableHasRows = rs.next();

              continue;

            }

            fieldname = rs.getString(2);

            fielddata = rs.getString(3);

            document = new StringBuffer();

            document.append("<param name=\"");

            document.append(fieldname);

            document.append("\">");

            document.append(fielddata);

            document.append("</param>");

            tableHasRows = rs.next();

            if(docListResult.containsKey(docid))

            {

              String removedelement = (String)docListResult.remove(docid);

              docListResult.put(docid, removedelement + document.toString());

            }

            else

            {

              docListResult.put(docid, document.toString()); 

            }

          }

          rs.close();

        }

        //this loop adds the relation data to the resultdoc

        //this code might be able to be added to the backtracking code above

        Enumeration docidkeys = docListResult.keys();

        while(docidkeys.hasMoreElements())

        {

          //String connstring = "metacat://"+util.getOption("server")+"?docid=";

          String connstring = "%docid=";

          String docidkey = (String)docidkeys.nextElement();

          //System.out.println("relationsql: " + qspec.printRelationSQL(

          //                                           connstring + docidkey));

          pstmt.close();

          pstmt = dbconn.prepareStatement(

                  qspec.printRelationSQL(connstring + docidkey));

          pstmt.execute();

          rs = pstmt.getResultSet();

          tableHasRows = rs.next();

          while(tableHasRows)

          {

            String sub = rs.getString(1);

            String rel = rs.getString(2);

            String obj = rs.getString(3);

            String subDT = rs.getString(4);

            String objDT = rs.getString(5);

            MetacatURL murl = new MetacatURL(sub);

            if(murl.getProtocol().equals("metacat"))

            {//we only want to process metacat urls here.

              String[] tempparam = murl.getParam(0);

              if(tempparam[0].equals("docid") && tempparam[1].equals(docidkey))

              {

                document = new StringBuffer();

                document.append("<relation>");

                document.append("<relationtype>").append(rel);

                document.append("</relationtype>");

                document.append("<relationdoc>").append(obj);

                document.append("</relationdoc>");

                document.append("<relationdoctype>").append(objDT);

                document.append("</relationdoctype>");

                document.append("</relation>");

                String removedelement = (String)docListResult.remove(docidkey);

                docListResult.put(docidkey, removedelement + document.toString());

              }

            }

            tableHasRows = rs.next();

          }

          rs.close();

          pstmt.close();

        }

      } catch (SQLException e) {

        System.err.println("SQL Error in DBQuery.findDocuments: " + 

                           e.getMessage());

      } catch (IOException ioe) {

        System.err.println("IO error in DBQuery.findDocuments:");

        System.err.println(ioe.getMessage());

      } catch (Exception ee) {

        System.out.println("Exception in DBQuery.findDocuments: " + 

                           ee.getMessage());

      }

      finally {

        try

        {

          dbconn.close();

        }

        catch(SQLException sqle)

        {

          System.out.println("error closing conn in DBQuery.findDocuments");

        }

      }

    //System.out.println("docListResult: ");

    //System.out.println(docListResult.toString());

    return docListResult;

  }

  /**

   * returns a string array of the contents of a particular node. 

   * If the node appears more than once, the contents are returned 

   * in the order in which they appearred in the document.

   * @param nodename the name or path of the particular node.

   * @param docid the docid of the document you want the node from.

   * @param conn a database connection-this allows this method to be static

   */

  public static Object[] getNodeContent(String nodename, String docid, 

                                        Connection conn)

  {

    StringBuffer query = new StringBuffer();

    Vector result = new Vector();

    PreparedStatement pstmt = null;

    query.append("select nodedata from xml_nodes where parentnodeid in ");

    query.append("(select nodeid from xml_index where path like '");

    query.append(nodename);

    query.append("' and docid like '").append(docid).append("')");

    try

    {

      pstmt = conn.prepareStatement(query.toString());

      // Execute the SQL query using the JDBC connection

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tableHasRows = rs.next();

      while (tableHasRows) 

      {

        result.add(rs.getString(1));

        System.out.println(rs.getString(1));

        tableHasRows = rs.next();

      }

    } 

    catch (SQLException e) 

    {

      System.err.println("Error in DBQuery.getNodeContent: " + e.getMessage());

    } finally {

      try

      {

        pstmt.close();

      }

      catch(SQLException sqle) {}

    }

    return result.toArray();

  }

  /**

   * format a structured query as an XML document that conforms

   * to the pathquery.dtd and is appropriate for submission to the DBQuery

   * structured query engine

   *

   * @param params The list of parameters that  should be included in the query

   */

  public static String createSQuery(Hashtable params)

  { 

    StringBuffer query = new StringBuffer();

    Enumeration elements;

    Enumeration keys;

    String doctype = null;

    String casesensitive = null;

    String searchmode = null;

    Object nextkey;

    Object nextelement;

    //add the xml headers

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

    query.append("<pathquery version=\"1.0\"><meta_file_id>");

    if(params.containsKey("meta_file_id"))

    {

      query.append( ((String[])params.get("meta_file_id"))[0]);

      query.append("</meta_file_id>");

    }

    else

    {

      query.append("unspecified</meta_file_id>");

    }

// DOCTITLE attr cleared from the db

//    query.append("<querytitle>");

//    if(params.containsKey("querytitle"))

//    {

//      query.append(((String[])params.get("querytitle"))[0]);

//      query.append("</querytitle>");

//    }

//    else

//    {

//      query.append("unspecified</querytitle>");

//    }

    if(params.containsKey("doctype"))

    {

      doctype = ((String[])params.get("doctype"))[0]; 

    }

    else

    {

      doctype = "ANY";  

    }

    if(params.containsKey("returnfield"))

    {

      String[] returnfield = ((String[])params.get("returnfield"));

      for(int i=0; i<returnfield.length; i++)

      {

        query.append("<returnfield>").append(returnfield[i]);

        query.append("</returnfield>");

      }

    }

    if(params.containsKey("owner"))

    {

      String[] owner = ((String[])params.get("owner"));

      for(int i=0; i<owner.length; i++)

      {

        query.append("<owner>").append(owner[i]);

        query.append("</owner>");

      }

    }

    if(params.containsKey("site"))

    {

      String[] site = ((String[])params.get("site"));

      for(int i=0; i<site.length; i++)

      {

        query.append("<site>").append(site[i]);

        query.append("</site>");

      }

    }

    //if you don't limit the query by doctype, then it just creates

    //an empty returndoctype tag.

    if (!doctype.equals("any") && 

        !doctype.equals("ANY") &&

        !doctype.equals("") ) 

    {

       query.append("<returndoctype>");

       query.append(doctype).append("</returndoctype>");

    }

    else

    { 

      query.append("<returndoctype></returndoctype>");

    }

    //allows the dynamic switching of boolean operators

    if(params.containsKey("operator"))

    {

      query.append("<querygroup operator=\"" + 

                ((String[])params.get("operator"))[0] + "\">");

    }

    else

    { //the default operator is UNION

      query.append("<querygroup operator=\"UNION\">"); 

    }

    if(params.containsKey("casesensitive"))

    {

      casesensitive = ((String[])params.get("casesensitive"))[0]; 

    }

    else

    {

      casesensitive = "false"; 

    }

    if(params.containsKey("searchmode"))

    {

      searchmode = ((String[])params.get("searchmode"))[0]; 

    }

    else

    {

      searchmode = "contains"; 

    }

    //anyfield is a special case because it does a 

    //free text search.  It does not have a <pathexpr>

    //tag.  This allows for a free text search within the structured

    //query.  This is useful if the INTERSECT operator is used.

    if(params.containsKey("anyfield"))

    {

       String[] anyfield = ((String[])params.get("anyfield"));

       //allow for more than one value for anyfield

       for(int i=0; i<anyfield.length; i++)

       {

         if(!anyfield[i].equals(""))

         {

           query.append("<queryterm casesensitive=\"" + casesensitive + 

                        "\" " + "searchmode=\"" + searchmode + "\"><value>" +

                        anyfield[i] +

                        "</value></queryterm>"); 

         }

       }

    }

    //this while loop finds the rest of the parameters

    //and attempts to query for the field specified

    //by the parameter.

    elements = params.elements();

    keys = params.keys();

    while(keys.hasMoreElements() && elements.hasMoreElements())

    {

      nextkey = keys.nextElement();

      nextelement = elements.nextElement();

      //make sure we aren't querying for any of these

      //parameters since the are already in the query

      //in one form or another.

      if(!nextkey.toString().equals("doctype") && 

         !nextkey.toString().equals("action")  &&

         !nextkey.toString().equals("qformat") && 

         !nextkey.toString().equals("anyfield") &&

         !nextkey.toString().equals("returnfield") &&

         !nextkey.toString().equals("owner") &&

         !nextkey.toString().equals("site") &&

         !nextkey.toString().equals("operator") )

      {

        //allow for more than value per field name

        for(int i=0; i<((String[])nextelement).length; i++)

        {

          if(!((String[])nextelement)[i].equals(""))

          {

            query.append("<queryterm casesensitive=\"" + casesensitive +"\" " + 

                         "searchmode=\"" + searchmode + "\">" +

                         "<value>" +

                         //add the query value

                         ((String[])nextelement)[i] +

                         "</value><pathexpr>" +

                         //add the path to query by 

                         nextkey.toString() + 

                         "</pathexpr></queryterm>");

          }

        }

      }

    }

    query.append("</querygroup></pathquery>");

    //append on the end of the xml and return the result as a string

    return query.toString();

  }

  /**

   * format a simple free-text value query as an XML document that conforms

   * to the pathquery.dtd and is appropriate for submission to the DBQuery

   * structured query engine

   *

   * @param value the text string to search for in the xml catalog

   * @param doctype the type of documents to include in the result set -- use

   *        "any" or "ANY" for unfiltered result sets

   */

   public static String createQuery(String value, String doctype) {

     StringBuffer xmlquery = new StringBuffer();

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

     xmlquery.append("<pathquery version=\"1.0\">");

     xmlquery.append("<meta_file_id>Unspecified</meta_file_id>");

// DOCTITLE attr cleared from the db

//     xmlquery.append("<querytitle>Unspecified</querytitle>");

     if (!doctype.equals("any") && !doctype.equals("ANY")) {

       xmlquery.append("<returndoctype>");

       xmlquery.append(doctype).append("</returndoctype>");

     }

     xmlquery.append("<querygroup operator=\"UNION\">");

     //chad added - 8/14

     //the if statement allows a query to gracefully handle a null 

     //query.  Without this if a nullpointerException is thrown.

     if(!value.equals(""))

     {

       xmlquery.append("<queryterm casesensitive=\"false\" ");

       xmlquery.append("searchmode=\"contains\">");

       xmlquery.append("<value>").append(value).append("</value>");

       xmlquery.append("</queryterm>");

     }

     xmlquery.append("</querygroup>");

     xmlquery.append("</pathquery>");

     return (xmlquery.toString());

   }

  /**

   * format a simple free-text value query as an XML document that conforms

   * to the pathquery.dtd and is appropriate for submission to the DBQuery

   * structured query engine

   *

   * @param value the text string to search for in the xml catalog

   */

   public static String createQuery(String value) {

     return createQuery(value, "any");

   }

  /** 

    * Check for "READ" permission on @docid for @user and/or @group 

    * from DB connection 

    */

  private boolean hasPermission ( Connection conn, String user,

                                  String group, String docid ) 

                  throws SQLException

  {

    // b' of the command line invocation

    if ( (user == null) && (group == null) ) {

      return true;

    }

    // Check for READ permission on @docid for @user and/or @group

    AccessControlList aclobj = new AccessControlList(conn);

    boolean hasPermission = aclobj.hasPermission("READ",user,docid);

    if ( !hasPermission && group != null ) {

      hasPermission = aclobj.hasPermission("READ",group,docid);

    }

    return hasPermission;

  }

}