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: berkley $'

 *     '$Date: 2000-09-26 15:06:52 -0700 (Tue, 26 Sep 2000) $'

 * '$Revision: 465 $'

 */

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;

/** 

 * 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 <xmlfile>");

        return;

     } else {

        try {

          String xmlfile  = args[0];

          // Open a connection to the database

          MetaCatUtil   util = new MetaCatUtil();

          Connection dbconn = util.openDBConnection();

          // 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);

          // 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");

          System.out.println(result);

        } catch (Exception e) {

          System.err.println("EXCEPTION HANDLING REQUIRED");

          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)

  {

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

  }

  /** 

   * 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)

  {

      Hashtable	 docListResult = new Hashtable();

      PreparedStatement pstmt;

      String docid = null;

      String docname = null;

      String doctype = null;

      String doctitle = null;

      String createDate = null;

      String updateDate = null;

      String fieldname = null;

      String fielddata = null;

      String relation = null;

      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 {

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

        QuerySpecification qspec = new QuerySpecification(xmlquery, 

                                   parserName);

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

        pstmt = conn.prepareStatement( qspec.printSQL() );

        // 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 ( !hasReadPermission(conn, docid, user, group) ) {continue;}

          docname = rs.getString(2);

          doctype = rs.getString(3);

          doctitle = rs.getString(4);

          createDate = rs.getString(5);

          updateDate = rs.getString(6);

          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://docid#").append(docid).append("'");

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

            pstmt.execute();

            ResultSet btrs = pstmt.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(conn, objURL.getParam(0)[1]);

              }

              catch(Exception e)

              {

                System.out.println("Error getting document: " + e.getMessage());

              }

              docid   = xmldoc.getDocID();

              docname = xmldoc.getDocname();

              doctype = xmldoc.getDoctype();

              doctitle = xmldoc.getDocTitle();

              createDate = xmldoc.getCreateDate();

              updateDate = xmldoc.getUpdateDate();

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

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

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

            }

            btrs.close();

          }

          document = new StringBuffer();

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

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

          {

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

            if (docname != null) {

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

            }

            if (doctype != null) {

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

            }

            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();

        }

        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 = conn.prepareStatement(qspec.printExtendedSQL(

                                        doclist.toString()));

          pstmt.execute();

          rs = pstmt.getResultSet();

          tableHasRows = rs.next();

          while(tableHasRows) 

          {

            docid = rs.getString(1);

            if ( !hasReadPermission(conn, docid, user, group) ) {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()); 

            }

          }

        }

        //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 docidkey = (String)docidkeys.nextElement();

          pstmt = conn.prepareStatement(

                  qspec.printRelationSQL("metacat://docid#" + 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);

            metacatURL murl = new metacatURL(sub);

            if(murl.getURLType().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("</relation>");

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

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

              }

            }

            tableHasRows = rs.next();

          }

        }

        pstmt.close();

      } catch (SQLException e) {

        System.err.println("Error getting id: " + e.getMessage());

      } catch (IOException ioe) {

        System.err.println("Error printing qspec:");

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

      }

    //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;

    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 getting id: " + e.getMessage());

    } 

    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>");

    }

    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 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("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>");

     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" permissions from DB connection */

  private boolean hasReadPermission(Connection conn, String docid, 

                                     String user, String group) 

                                     throws SQLException {

    // b' of the command line invocation

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

      return true;

    }

    PreparedStatement pstmt;

    // checking if user is owner of docid or if docid has public access

    try {

      pstmt = conn.prepareStatement(

                   "SELECT 'x' FROM xml_documents " +

                   "WHERE docid LIKE ? AND user_owner LIKE ? " + 

                   "UNION " +

                   "SELECT 'x' FROM xml_documents " +

                   "WHERE docid LIKE ? AND public_access = 1");

      // Bind the values to the query

      pstmt.setString(1, docid);

      pstmt.setString(2, user);

      pstmt.setString(3, docid);

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean hasRow = rs.next();

      pstmt.close();

      if (hasRow) {

        return true;

      }

    } catch (SQLException e) {

      throw new 

        SQLException("Error checking document's owner or public access: "

                      + e.getMessage());

    }

    // checking if docid has public access at this time

    try {

      pstmt = conn.prepareStatement(

                   "SELECT 'x' FROM xml_access " +

                   "WHERE docid LIKE ? " +

                   "AND principal_name = 'public' " +

                   "AND principal_type = 'user' " +

                   "AND sysdate BETWEEN nvl(begin_time,sysdate) " +

                                   "AND nvl(end_time,sysdate)");

      // Bind the values to the query

      pstmt.setString(1, docid);

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean hasRow = rs.next();

      pstmt.close();

      if (hasRow) {

        return true;

      }

    } catch (SQLException e) {

      throw new 

        SQLException("Error checking doc's public access: " + e.getMessage());

    }

    // checking access type from xml_access table

    int accesstype = 0;

    try {

      pstmt = conn.prepareStatement(

                   "SELECT access_type FROM xml_access " +

                   "WHERE docid LIKE ? " + 

                   "AND principal_name LIKE ? " +

                   "AND principal_type = 'user' " +

                   "AND sysdate BETWEEN nvl(begin_time,sysdate) " +

                                   "AND nvl(end_time,sysdate) " +

                   "UNION " +

                   "SELECT access_type FROM xml_access " +

                   "WHERE docid LIKE ? " + 

                   "AND principal_name LIKE ? " +

                   "AND principal_type = 'group' " +

                   "AND sysdate BETWEEN nvl(begin_time,sysdate) " +

                                   "AND nvl(end_time,sysdate)");

      // Bind the values to the query

      pstmt.setString(1, docid);

      pstmt.setString(2, user);

      pstmt.setString(3, docid);

      pstmt.setString(2, group);

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean hasRows = rs.next();

      while ( hasRows ) {

        accesstype = rs.getInt(1);

        if ( (accesstype & READ) == READ ) {

          pstmt.close();

          return true;

        }

        hasRows = rs.next();

      }

      pstmt.close();

      return false;

    } catch (SQLException e) {

      throw new 

      SQLException("Error getting document's permissions: " + e.getMessage());

    }

  }

}

/**

 * '$Log$

 * 'Revision 1.20  2000/09/15 19:52:11  berkley

 * 'Added functionality for package specifications.  metacatservlet now contains a new action called getrelateddocument that handles retrieving related documents using the metacatURL specification (metacatURL.java).  DBQuery contains new code in runQuery that embeds relation tags in the returned hashtable describing the documents related to each docid.  querySpecification contains a new method which prints the sql that does the relation query.

 * '

 * 'Revision 1.19  2000/09/12 17:37:07  bojilova

 * 'added check from "read" permission on "query" and "squery" actions

 * 'for connected user or for "public" connection

 * '

 * 'Revision 1.18  2000/09/05 20:50:56  berkley

 * 'Added a method called getNodeContent which retrieves the content of a node in a document.  If there are more than one nodes with the same name returned, it returns an array with all of the data.

 * '

 * 'Revision 1.17  2000/08/31 21:20:39  berkley

 * 'changed xslf for new returnfield scheme.  the returnfields are now returned as <param name="<returnfield>"> tags.

 * 'hThe sql for the returnfield query was redone to fix a previous problem with slow queries

 * '

 * 'Revision 1.16  2000/08/23 22:55:25  berkley

 * 'changed the field names to be case-sensitive in the returnfields

 * '

 * 'Revision 1.15  2000/08/23 17:22:07  berkley

 * 'added support for the returnfield parameter

 * '-added the dynamic parameters to the returned hash table of documents

 * '

 * 'Revision 1.14  2000/08/17 16:02:34  berkley

 * 'Made changes to createSQuery to allow for multiple parameters of the same name.  Also changed the param list to include only "Hashtable params" without a "String doctype" since the doctype is already contained in the params.

 * '

 * 'Revision 1.13  2000/08/14 21:26:12  berkley

 * 'Added createSQuery() to handle structured queries of an arbitrary number of parameters.  Also modified createQuery() to handle a null query in a graceful manner.

 * '

 * 'Revision 1.12  2000/08/14 20:53:33  jones

 * 'Added "release" keyword to all metacat source files so that the release

 * 'number will be evident in software distributions.

 * '

 * 'Revision 1.11  2000/08/11 18:26:07  berkley

 * 'added createSQuery

 * '

 * 'Revision 1.10  2000/07/26 20:40:41  higgins

 * 'no message

 * '

 * 'Revision 1.9  2000/06/26 10:35:04  jones

 * 'Merged in substantial changes to DBWriter and associated classes and to

 * 'the MetaCatServlet in order to accomodate the new UPDATE and DELETE

 * 'functions.  The command line tools and the parameters for the

 * 'servlet have changed substantially.

 * '

 * 'Revision 1.8.2.2  2000/06/25 23:38:16  jones

 * 'Added RCSfile keyword

 * '

 * 'Revision 1.8.2.1  2000/06/25 23:34:17  jones

 * 'Changed documentation formatting, added log entries at bottom of source files

 * ''

 */