Metacat

/**

 *  '$RCSfile$'

 *    Purpose: A class to asyncronously do delta-T replication checking

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

 *             National Center for Ecological Analysis and Synthesis

 *    Authors: Chad Berkley

 *    Release: @release@

 *

 *   '$Author: tao $'

 *     '$Date: 2002-06-13 11:54:51 -0700 (Thu, 13 Jun 2002) $'

 * '$Revision: 1217 $'

 *

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

import java.util.*;

import java.lang.Thread; 

import java.io.*;

import java.net.*;

import java.text.*;

import org.xml.sax.AttributeList;

import org.xml.sax.ContentHandler;

import org.xml.sax.DTDHandler;

import org.xml.sax.EntityResolver;

import org.xml.sax.ErrorHandler;

import org.xml.sax.InputSource;

import org.xml.sax.XMLReader;

import org.xml.sax.SAXException;

import org.xml.sax.SAXParseException;

import org.xml.sax.helpers.XMLReaderFactory;

import org.xml.sax.helpers.DefaultHandler;

/**

 * This class handles deltaT replication checking.  Whenever this TimerTask

 * is fired it checks each server in xml_replication for updates and updates

 * the local db as needed.

 */

public class ReplicationHandler extends TimerTask

{

  int serverCheckCode = 1;

  MetaCatUtil util = new MetaCatUtil();

  Hashtable serverList = new Hashtable(); 

  //Connection conn;

  PrintWriter out;

  public ReplicationHandler(PrintWriter o)

  {

    this.out = o;

  }

  public ReplicationHandler(PrintWriter o, int serverCheckCode)

  {

    this.out = o;

    this.serverCheckCode = serverCheckCode;

  }

  /**

   * Method that implements TimerTask.run().  It runs whenever the timer is 

   * fired.

   */

  public void run()

  {

    //find out the last_checked time of each server in the server list and

    //send a query to each server to see if there are any documents in 

    //xml_documents with an update_date > last_checked

    try

    {

      //try

      //{ //this connection is prone to error for some reason so we 

        //try to connect it three times before quiting.

        //conn = util.openDBConnection();

      //}

      //catch(SQLException sqle)

      //{

        /*try

        {

          conn = util.openDBConnection();

        }

        catch(SQLException sqlee)

        {

          try

          {

            conn = util.openDBConnection();

          }

          catch(SQLException sqleee)

          {*/

            //System.out.println("error getting db connection in " + 

                               //"ReplicationHandler.run: " +

                               //sqle.getMessage());

          //}

        //}

      //}

      serverList = buildServerList();

      //if serverList is null, metacat don't need to replication

      if (serverList==null||serverList.isEmpty())

      {

        return;

      }

      updateCatalog(serverList);

      update(serverList);

      //conn.close();

    }//try

    catch (Exception e)

    {

      MetaCatUtil.debugMessage("Error in replicationHandler.run():"

                                                    +e.getMessage(), 30);

    }//catch

  }

  /**

   * Method that uses revision taging for replication instead of update_date.

   */

  private void update(Hashtable serverList)

  {

    /*

     Pseudo-algorithm

     - request a doc list from each server in xml_replication

     - check the rev number of each of those documents agains the 

       documents in the local database

     - pull any documents that have a lesser rev number on the local server

       from the remote server

     - delete any documents that still exist in the local xml_documents but

       are in the deletedDocuments tag of the remote host response.

     - update last_checked to keep track of the last time it was checked.

       (this info is theoretically not needed using this system but probably 

       should be kept anyway)

    */

    DBConnection dbConn = null;

    int serialNumber = -1;

    /*try

    {

      conn = util.getConnection();

    }

    catch (Exception e)

    {

      util.debugMessage("There is problem to get conncetion: "+e.getMessage());

    }*/

    Enumeration keys;

    String server;

    String update;

    ReplMessageHandler message = new ReplMessageHandler();

    Vector responses = new Vector();

    PreparedStatement pstmt = null;

    ResultSet rs;

    boolean tablehasrows;

    boolean flag=false;

    boolean dataFile=false;

    String action = new String();

    XMLReader parser;

    URL u;

    try

    {

      dbConn=DBConnectionPool.

                  getDBConnection("ReplicationHandler.update");

      serialNumber=dbConn.getCheckOutSerialNumber();

      MetaCatUtil.debugMessage("init parser");

      parser = initParser(message);

      keys = serverList.keys();

      while(keys.hasMoreElements())

      {

        server = (String)(keys.nextElement());

        MetacatReplication.replLog("full update started to: " + server);

        u = new URL("https://" + server + "?server="

        +util.getLocalReplicationServerName()+"&action=update");

        //System.out.println("Sending Message: " + u.toString());

        String result = MetacatReplication.getURLContent(u);

        //check if result have error or not

        if (result.indexOf("error")!=-1)

        {

          continue;

        }

        responses.add(result);

      }

      //make sure that there is updated file list

      //If response is null, mecat don't need do anything

       if (responses==null||responses.isEmpty())

      {

        return;

      }

      //String srvr = util.getOption("servletpath");

      //System.out.println("responses (from srvr): " + responses.toString());

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

      { //check each server for updated files

        //System.out.println("parsing responses");

        parser.parse(new InputSource(

                     new StringReader(

                     (String)(responses.elementAt(i)))));

        //v is the list of updated documents

        Vector v = new Vector(message.getUpdatesVect());

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

        //d is the list of deleted documents

        Vector d = new Vector(message.getDeletesVect());

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

        //check the revs in u to see if there are any newer ones than

        //in the local DB.

        MetaCatUtil.debugMessage("Update vector size: "+ v.size(), 20);

        MetaCatUtil.debugMessage("Delete vector size: "+ d.size(),20);

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

        {

          //initial dataFile is false

          dataFile=false;

          //w is information for one document, information contain

          //docid, rev, server or datafile.

          Vector w = new Vector((Vector)(v.elementAt(j)));

          //Check if the vector w contain "datafile"

          //If it has, this document is data file

          if (w.contains((String)util.getOption("datafileflag")))

          {

            dataFile=true;

          }

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

          String docid = (String)w.elementAt(0);

          MetaCatUtil.debugMessage("docid: " + docid, 40);

          int rev = Integer.parseInt((String)w.elementAt(1));

          MetaCatUtil.debugMessage("rev: " + rev, 40);

          String docServer = (String)w.elementAt(2);

         pstmt = dbConn.prepareStatement("select rev from xml_documents where "+

                                        "docid like '" + docid + "'");

          pstmt.execute();

          rs = pstmt.getResultSet();

          tablehasrows = rs.next();

          if(tablehasrows)

          { //check the revs for an update because this document is in the

            //local DB, it just might be out of date.

            int localrev = rs.getInt(1);

            if(localrev == rev)

            {

              flag = false;

            }

            else if(localrev < rev)

            {//this document needs to be updated so send an read request

              action = "UPDATE";

              flag = true;

            }

          }

          else

          { //insert this document as new because it is not in the local DB

            action = "INSERT";

            flag = true;

          }

          // this is non-data file

          if(flag && !dataFile)

          { //if the document needs to be updated or inserted, this is executed

            u = new URL("https://" + docServer + "?server="+

              util.getLocalReplicationServerName()+"&action=read&docid="+docid);

            //System.out.println("Sending message: " + u.toString());

            String newxmldoc = MetacatReplication.getURLContent(u);

            if ( newxmldoc.indexOf("error")!= -1)

            {

              continue;

            }

            MetaCatUtil.debugMessage("xml documnet:", 50);

            MetaCatUtil.debugMessage(newxmldoc, 50);

            DocInfoHandler dih = new DocInfoHandler();

            XMLReader docinfoParser = initParser(dih);

            URL docinfoUrl = new URL("https://" + docServer + 

                  "?server="+util.getLocalReplicationServerName()+

                  "&action=getdocumentinfo&docid="+docid);

            //System.out.println("Sending message: " + docinfoUrl.toString());

            String docInfoStr = MetacatReplication.getURLContent(docinfoUrl);

            docinfoParser.parse(new InputSource(new StringReader(docInfoStr)));

            Hashtable docinfoHash = dih.getDocInfo();

            String docHomeServer = (String)docinfoHash.get("home_server");

            //int serverCode = MetacatReplication.getServerCode(docServer);

            //System.out.println("updating doc: " + docid +" action: "+ action);

            //docid should include rev number too

            String accnum=docid+util.getOption("accNumSeparator")+

                                              (String)docinfoHash.get("rev");

            //System.out.println("accnum: "+accnum);

            try

            {

              String newDocid = DocumentImpl.writeReplication(dbConn, 

                              new StringReader(newxmldoc),

                              (String)docinfoHash.get("public_access"),

                              null,  /* the dtd text */

                              action, 

                              accnum, 

                              (String)docinfoHash.get("user_owner"),

                              null, /* null for groups[] */

                              docHomeServer, 

                              false /* validate */);

              //increase usage

              dbConn.increaseUsageCount(1);

              MetacatReplication.replLog("wrote doc " + docid + " from " + 

                                         docServer);

              /*System.out.println("wrote doc " + docid + " from " + 

                                 docServer);*/

            }

            catch(Exception e)

            {

              System.out.println("error writing document in " + 

                                 "ReplicationHandler.update: " + docid +

                                 ": " + e.getMessage());

            }

          }//if for non-data file

           // this is for data file and metacat is configured to accetp datafile

          if(flag && dataFile 

                   && (util.getOption("replicationacceptdata")).equals("on"))

          { 

            //if the document needs to be updated or inserted, this is executed

            //String newxmldoc = MetacatReplication.getURLContent(u);

            DocInfoHandler dih = new DocInfoHandler();

            XMLReader docinfoParser = initParser(dih);

            URL docinfoUrl = new URL("https://" + docServer + 

                  "?server="+util.getLocalReplicationServerName()+

                  "&action=getdocumentinfo&docid="+docid);

            //System.out.println("Sending message: " + docinfoUrl.toString());

            String docInfoStr = MetacatReplication.getURLContent(docinfoUrl);

            docinfoParser.parse(new InputSource(new StringReader(docInfoStr)));

            Hashtable docinfoHash = dih.getDocInfo();

            String user = (String)docinfoHash.get("user_owner");

            String docName = (String)docinfoHash.get("docname");

            String docType = (String)docinfoHash.get("doctype");

            String docHomeServer = (String)docinfoHash.get("home_server");

            //int serverCode = MetacatReplication.getServerCode(docServer);

            //System.out.println("updating doc: " + docid +" action: "+ action);

            //docid should include rev number too

            String accnum=docid+util.getOption("accNumSeparator")+

                                              (String)docinfoHash.get("rev");

            String datafilePath = util.getOption("datafilepath");

            u = new URL("https://" + docServer + "?server="+

                                        util.getLocalReplicationServerName()+

                                            "&action=readdata&docid="+accnum);

            //System.out.println("accnum: "+accnum);

            try

            {

              //register data file into xml_documents table and wite data file

              //into file system

              if (u.openStream() != null)

              {

              DocumentImpl.writeDataFile(u.openStream(), datafilePath, 

                                  docName,docType, accnum, user,docHomeServer);

              MetacatReplication.replLog("wrote doc " + docid + " from " + 

                                         docServer);

              }

              else

              {

                continue;

              }

              /*System.out.println("wrote doc " + docid + " from " + 

                                 docServer);*/

            }

            catch(Exception e)

            {

              System.out.println("error writing document in " + 

                                 "ReplicationHandler.update: " + docid +

                                 ": " + e.getMessage());

            }

          }//for datafile

        }//for update docs

        //handle deleted docs

        for(int k=0; k<d.size(); k++)

        { //delete the deleted documents;

          Vector w = new Vector((Vector)d.elementAt(k));

          String docid = (String)w.elementAt(0);

          if(!alreadyDeleted(docid))

          {

            //because delete method docid should have rev number

            //so we just add one for it. This rev number is no sence.

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

            //System.out.println("accnum: "+accnum);

            DocumentImpl.delete(accnum, null, null);

            MetacatReplication.replLog("doc " + docid + " deleted");

          }

        }//for delete docs

        //updated last_checked

        keys = serverList.keys();

        while(keys.hasMoreElements())

        {

          server = (String)(keys.nextElement()); 

          URL dateurl = new URL("https://" + server + "?server="+

          util.getLocalReplicationServerName()+"&action=gettime");

          String datexml = MetacatReplication.getURLContent(dateurl);

          MetaCatUtil.debugMessage("datexml: "+datexml, 40);

          if (datexml!=null && !datexml.equals(""))

          {

            String datestr = datexml.substring(11, datexml.indexOf('<', 11));

            StringBuffer sql = new StringBuffer();

            sql.append("update xml_replication set last_checked = to_date('");

            sql.append(datestr).append("', 'YY-MM-DD HH24:MI:SS') where ");

            sql.append("server like '").append(server).append("'");

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

            pstmt.close();

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

            //increase usage count

            dbConn.increaseUsageCount(1);

            pstmt.executeUpdate();

            dbConn.commit();

            System.out.println("last_checked updated to " + datestr + " on " +

                            server);

          }

        }//wile

      }//for

    }//try

    catch(Exception e)

    {

      /*System.out.println("error in ReplicationHandler.update: " + 

                          e.getMessage());*/

      e.printStackTrace(System.out);

    }

    finally

    {

      try

      {

        pstmt.close();

      }//try

      catch(Exception ee) 

      {

        MetaCatUtil.debugMessage("Error in ReplicationHandler.update to close"+

                                " pstmt: "+ee.getMessage(), 30);

      }//catch

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

  }//update

  /**

   * updates xml_catalog with entries from other servers.

   */

  private void updateCatalog(Hashtable serverList)

  {

    DBConnection dbConn = null;

    int serialNumber = -1;

    PreparedStatement pstmt = null;

    try

    {

      /*try

      { 

        conn = util.openDBConnection();

      }

      catch(SQLException sqle)

      {

        try

        {

          conn = util.openDBConnection();

        }

        catch(SQLException sqlee)

        {

          try

          {

            conn = util.openDBConnection();

          }

          catch(SQLException sqleee)

          {

            System.out.println("error getting db connection in " + 

                               "ReplicationHandler.update: " +

                               sqleee.getMessage());

          }

        }

      }*/

      dbConn=DBConnectionPool.

                  getDBConnection("ReplicationHandler.updateCatalog");

      serialNumber=dbConn.getCheckOutSerialNumber();

      String server;

      Enumeration keys = serverList.keys();

      while(keys.hasMoreElements())

      { //go through each server

        server = (String)(keys.nextElement());

        URL u = new URL("https://" + server + "?server="+

        util.getLocalReplicationServerName()+"&action=getcatalog");

        //System.out.println("sending message " + u.toString());

        String catxml = MetacatReplication.getURLContent(u);

        //System.out.println("catxml: " + catxml);

        CatalogMessageHandler cmh = new CatalogMessageHandler();

        XMLReader catparser = initParser(cmh);

        catparser.parse(new InputSource(new StringReader(catxml)));

        //parse the returned catalog xml and put it into a vector

        Vector remoteCatalog = cmh.getCatalogVect();

        String localcatxml = MetacatReplication.getCatalogXML();

        //System.out.println("localcatxml: " + localcatxml);

        cmh = new CatalogMessageHandler();

        catparser = initParser(cmh);

        catparser.parse(new InputSource(new StringReader(localcatxml)));

        Vector localCatalog = cmh.getCatalogVect();

        //now we have the catalog from the remote server and this local server

        //we now need to compare the two and merge the differences.

        //the comparison is base on the public_id fields which is the 4th

        //entry in each row vector.

        Vector publicId = new Vector();

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

        {

          Vector v = new Vector((Vector)localCatalog.elementAt(i));

          //System.out.println("v1: " + v.toString());

          publicId.add(new String((String)v.elementAt(3)));

          //System.out.println("adding " + (String)v.elementAt(3));

        }

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

        {

          Vector v = (Vector)remoteCatalog.elementAt(i);

          //System.out.println("v2: " + v.toString());

          //System.out.println("i: " + i);

          //System.out.println("remoteCatalog.size(): " + remoteCatalog.size());

          //System.out.println("publicID: " + publicId.toString());

          //System.out.println("v.elementAt(3): " + (String)v.elementAt(3));

          if(!publicId.contains(v.elementAt(3)))

          { //so we don't have this public id in our local table so we need to

            //add it.

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

            StringBuffer sql = new StringBuffer();

            sql.append("insert into xml_catalog (entry_type, source_doctype, ");

            sql.append("target_doctype, public_id, system_id) values (?,?,?,");

            sql.append("?,?)");

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

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

            //increase usage count 1

            dbConn.increaseUsageCount(1);

            pstmt.setString(1, (String)v.elementAt(0));

            pstmt.setString(2, (String)v.elementAt(1));

            pstmt.setString(3, (String)v.elementAt(2));

            pstmt.setString(4, (String)v.elementAt(3));

            pstmt.setString(5, (String)v.elementAt(4));

            pstmt.execute();

            pstmt.close();

          }

        }

      }

      //conn.close();

    }

    catch(Exception e)

    {

      System.out.println("error in ReplicationHandler.updateCatalog: " + 

                          e.getMessage());

      e.printStackTrace(System.out);

    }//catch

    finally

    {

      try

      {

        pstmt.close();

      }//try

      catch (SQLException ee)

      {

        MetaCatUtil.debugMessage("Error in ReplicationHandler.updateCatalog "+

                      "to close pstmt: "+ee.getMessage(), 30);

      }//catch

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finall

  }

  /**

   * Method that returns true if docid has already been "deleted" from metacat.

   * This method really implements a truth table for deleted documents

   * The table is (a docid in one of the tables is represented by the X):

   * xml_docs      xml_revs      deleted?

   * ------------------------------------

   *   X             X             FALSE

   *   X             _             FALSE

   *   _             X             TRUE

   *   _             _             TRUE

   */

  private static boolean alreadyDeleted(String docid)

  {

    DBConnection dbConn = null;

    int serialNumber = -1;

    PreparedStatement pstmt = null;

    try

    {

      dbConn=DBConnectionPool.

                  getDBConnection("ReplicationHandler.alreadyDeleted");

      serialNumber=dbConn.getCheckOutSerialNumber();

      boolean xml_docs = false;

      boolean xml_revs = false;

      StringBuffer sb = new StringBuffer();

      sb.append("select docid from xml_revisions where docid like '");

      sb.append(docid).append("'");

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

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tablehasrows = rs.next();

      if(tablehasrows)

      {

        xml_revs = true;

      }

      sb = new StringBuffer();

      sb.append("select docid from xml_documents where docid like '");

      sb.append(docid).append("'");

      pstmt.close();

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

      //increase usage count

      dbConn.increaseUsageCount(1);

      pstmt.execute();

      rs = pstmt.getResultSet();

      tablehasrows = rs.next();

      pstmt.close();

      if(tablehasrows)

      {

        xml_docs = true;

      }

      if(xml_docs && xml_revs)

      {

        return false;

      }

      else if(xml_docs && !xml_revs)

      {

        return false;

      }

      else if(!xml_docs && xml_revs)

      {

        return true;

      }

      else if(!xml_docs && !xml_revs)

      {

        return true;

      }

    }

    catch(Exception e)

    {

      System.out.println("error in ReplicationHandler.alreadyDeleted: " + 

                          e.getMessage());

      e.printStackTrace(System.out);

    }

    finally

    {

      try

      {

        pstmt.close();

      }//try

      catch (SQLException ee)

      {

        MetaCatUtil.debugMessage("Error in replicationHandler.alreadyDeleted "+

                          "to close pstmt: "+ee.getMessage(), 30);

      }//catch

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

    return false;

  }

  /**

   * Checks to see if a document is already in the DB.  Returns

   * "UPDATE" if it is, "INSERT" if it isn't

   */

  private static String getAction(String docid)

  {

    DBConnection dbConn = null;

    int serialNumber = -1;

    PreparedStatement pstmt = null;

    try

    {

      dbConn=DBConnectionPool.

                  getDBConnection("ReplicationHandler.getAction");

      serialNumber=dbConn.getCheckOutSerialNumber();

      //MetaCatUtil util = new MetaCatUtil();

      StringBuffer sql = new StringBuffer();

      sql.append("select docid from xml_documents where docid like '");

      sql.append(docid).append("'");

      //Connection conn = util.openDBConnection();

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

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      if(rs.next())

      {

        pstmt.close();

        //conn.close();

        return "UPDATE";

      }

      else

      {

        pstmt.close();

        //conn.close();

        return "INSERT";

      }

    }

    catch(Exception e)

    {

      System.out.println("error in replicationHandler.getAction: " + 

                          e.getMessage());

    }

    finally

    {

      try

      {

        pstmt.close();

      }

      catch (SQLException sqlE)

      {

        MetaCatUtil.debugMessage("error in replicationHandler.getAction: " + 

                     "to close pstmt: "+ sqlE.getMessage(), 30);

      }

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }

    }//finally

    return "";

  }

  /**

   * Method to query xml_replication and build a hashtable of each server

   * and it's last update time.

   * @param conn a connection to the database

   */

  public static Hashtable buildServerList()

  {

    DBConnection dbConn = null;

    int serialNumber = -1;

    Hashtable sl = new Hashtable();

    PreparedStatement pstmt = null;   

    try

    {

      dbConn=DBConnectionPool.

                  getDBConnection("ReplicationHandler.buildServerList");

      serialNumber=dbConn.getCheckOutSerialNumber();

      pstmt = dbConn.prepareStatement("select server, last_checked, replicate "+

                                    "from xml_replication");

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tableHasRows = rs.next();

      while(tableHasRows)

      {

        if(rs.getInt(3) == 1)

        {//only put the server in the list if the replicate flag is true

          String server = rs.getString(1);

          String last_checked = rs.getString(2);

          if(!server.equals("localhost"))

          {

            sl.put(server, last_checked);

          }

        }

        tableHasRows = rs.next();   

      }

      pstmt.close();

    }

    catch(Exception e)

    {

      System.out.println("error in replicationHandler.buildServerList(): " +

                         e.getMessage());

    }

    finally

    {

      try

      {

        pstmt.close();

      }//try

      catch (SQLException sqlE)

      {

        MetaCatUtil.debugMessage("Error in ReplicationHandler.buildServerList: "

                                +sqlE.getMessage());

      }//catch

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }//finally

    }//finally

    return sl;

  }

   /**

   * Method to get a host server information for given docid

   * @param conn a connection to the database

   */

  public static Hashtable getHomeServer(String docId)

  {

    Hashtable sl = new Hashtable();

    DBConnection dbConn = null;

    int serialNumber = -1;

    //MetaCatUtil ut=new MetaCatUtil();

    docId=MetaCatUtil.getDocIdFromString(docId);

    PreparedStatement pstmt=null;

    int serverLocation;

    try

    {

      //get conection

      dbConn=DBConnectionPool.

                  getDBConnection("ReplicationHandler.getHomeServer");

      serialNumber=dbConn.getCheckOutSerialNumber();

      //get a server location from xml_document table

      pstmt=dbConn.prepareStatement("select server_location from xml_documents "

                                            +"where docid = ?");

      pstmt.setString(1, docId);

      pstmt.execute();

      ResultSet serverName = pstmt.getResultSet();

      //get a server location

      if(serverName.next())

      {

        serverLocation=serverName.getInt(1);

        pstmt.close();

      }

      else

      {

        pstmt.close();

        //ut.returnConnection(conn);

        return null;

      }

      pstmt=dbConn.prepareStatement("select server, last_checked, replicate " +

                        "from xml_replication where serverid = ?");

      //increase usage count

      dbConn.increaseUsageCount(1);

      pstmt.setInt(1, serverLocation);

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tableHasRows = rs.next();

      if (tableHasRows)

      {

          String server = rs.getString(1);

          String last_checked = rs.getString(2);

          if(!server.equals("localhost"))

          {

            sl.put(server, last_checked);

          }

      }

      else

      {

        pstmt.close();

        //ut.returnConnection(conn);

        return null;

      }

      pstmt.close();

    }

    catch(Exception e)

    {

      System.out.println("error in replicationHandler.getHomeServer(): " +

                         e.getMessage());

    }

    finally

    {

      try

      {

        pstmt.close();

        //ut.returnConnection(conn);

      }

      catch (Exception ee)

      {

        MetaCatUtil.debugMessage("Eror irn rplicationHandler.getHomeServer() "+

                          "to close pstmt: "+ee.getMessage(), 30);

      }

      finally

      {

        DBConnectionPool.returnDBConnection(dbConn, serialNumber);

      }

    }//finally

    return sl;

  }

  /**

   * Returns a server location  given a accnum

   * @param accNum , given accNum for a document

   * 

   */

  public static int getServerLocation(String accNum) throws Exception

  {

    DBConnection dbConn = null;

    int serialNumber = -1;

    PreparedStatement pstmt = null;

    int serverCode = 1;

    //MetaCatUtil ut = new MetaCatUtil();

    String docId=MetaCatUtil.getDocIdFromString(accNum);

    try 

    {

      //conn = ut.openDBConnection();

      //get conection

      dbConn=DBConnectionPool.

                  getDBConnection("ReplicationHandler.getServerLocation");

      serialNumber=dbConn.getCheckOutSerialNumber();

      pstmt=dbConn.prepareStatement("SELECT server_location FROM xml_documents " 

                              + "WHERE docid LIKE '" + docId + "'");

      pstmt.execute();

      ResultSet rs = pstmt.getResultSet();

      boolean tablehasrows = rs.next();

      //If a document is find, return the server location for it

      if ( tablehasrows ) 

      {  

        serverCode = rs.getInt(1);

        pstmt.close();

        //conn.close();

        return serverCode;

      }

      //if couldn't find in xml_documents table, we think server code is 1

      //(this is new document)

      else

      {