Metacat

/**

 *    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

 *     '$Date$'

 * '$Revision$'

package edu.ucsb.nceas.metacat;

import java.io.*;

import java.net.MalformedURLException;

import java.sql.*;

import java.util.Stack;

import java.util.Hashtable;

import java.util.Enumeration;

/**

 * 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 WRITE = 2;

  static final int READ = 4;

  /**

   * the main routine used to test the DBQuery utility.

   * Usage: java DBQuery <xmlfile>

  static public void main(String[] args) {

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

     } else {

        try {

          // Open a connection to the database

          Connection dbconn = util.openDBConnection();

          String document = null;

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

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

          while (doclist.hasMoreElements()) {

            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

                         SQLException,

  /**

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

   *

      PreparedStatement pstmt;

      String doctype = null;

      String doctitle = null;

      String updateDate = null;

      String fieldname = null;

      String fielddata = null;

      try {

        ResultSet rs = pstmt.getResultSet();

        boolean tableHasRows = rs.next();

        while (tableHasRows) {

          doctype = rs.getString(3);

          doctitle = rs.getString(4);

          updateDate = rs.getString(6);

          if (docname != null) {

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

          }

          if (doctype != null) {

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

          }

          if (doctitle != null) {

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

          }

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

          }

          if(updateDate != null) {

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

          }

          // Store the document id and the root node id

          // Advance to the next record in the cursor

          tableHasRows = rs.next();

        }

        if(qspec.containsExtendedSQL())

        {

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

          Vector results = new Vector();

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

          pstmt.execute();

          rs = pstmt.getResultSet();

          tableHasRows = rs.next();

          while(tableHasRows)

          {

            docid = rs.getString(1);

            fielddata = rs.getString(3);

            document = new StringBuffer();

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

            }

          }

        }

      } catch (SQLException e) {

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

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

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

  }

  /**

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

  }

  /**

   * 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

   */

    StringBuffer query = new StringBuffer();

    Enumeration keys;

    String casesensitive = null;

    String searchmode = null;

    Object nextelement;

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

    {

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

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

    }

    else

    {

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

    }

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

    }

    {

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

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

      {

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

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

      }

    }

    //an empty returndoctype tag.

        !doctype.equals("ANY") &&

        !doctype.equals("") )

    {

       query.append("<returndoctype>");

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

    }

    {

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

    }

    //allows the dynamic switching of boolean operators

    if(params.containsKey("operator"))

    {

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

    else

    { //the default operator is UNION

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

    }

    {

      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>

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

    if(params.containsKey("anyfield"))

       //allow for more than one value for anyfield

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

         {

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

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

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

  }

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

     //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("</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$

 * '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.

 * '

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

 * '

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

 * '

 * 'added support for the returnfield parameter

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

 * '

 * '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.

 * '

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

 * '

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

 * 'number will be evident in software distributions.

 * '

 * 'added createSQuery

 * '

 * 'no message

 * '

 * '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.

 * '

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

 * ''

 */