/ - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 457

Added by bojilova over 23 years ago

changes related to decrease the time of INSERT of document.
With these changes I inserted 200KB file for 3 minutes, 50KB for 50sec.
This is mainly simplifing the DBSAXNode class and
using batching feature of Oracle JDBC driver.

          MetaCatUtil.debugMessage("start Document");
          // Create the document node representation as root
          rootNode = new DBSAXNode(conn);
          rootNode = new DBSAXNode(conn, this.docid);
          // Add the node to the stack, so that any text data can be
          // added as it is encountered
          nodeStack.push(rootNode);
-...
        public void endDocument() throws SAXException {
          currentDocument.setTitleFromChildElement();
          MetaCatUtil.debugMessage("end Document");
          if ((docid != null) && (!docid.equals(currentDocument.getDocID()))) {
            throw (new SAXException("New document ID generated:",
                new AccessionNumberGeneratedException(currentDocument.getDocID())));
          } else {
            throw (new SAXException("New document ID generated:",
                new AccessionNumberGeneratedException(currentDocument.getDocID())));
+         }
     //     if ((docid != null) && (!docid.equals(currentDocument.getDocID()))) {
     //       throw (new SAXException("New document ID generated:",
     //           new AccessionNumberGeneratedException(currentDocument.getDocID())));
     //     } else {
     //       throw (new SAXException("New document ID generated:",
     //           new AccessionNumberGeneratedException(currentDocument.getDocID())));
     //     }
+       }
        /** SAX Handler that is called at the start of each XML element */
-...
            try {
              currentDocument = new DocumentImpl(conn, rootNode.getNodeID(),
                                            docname, doctype, docid, action, user);
            } catch (AccessionNumberException ane) {
            } catch (Exception ane) {
              throw (new SAXException("Error with " + action, ane));
+           }
            rootNode.writeDocID(currentDocument.getDocID());
            // not needed any more
            //rootNode.writeDocID(currentDocument.getDocID());
+         }
          // Create the current node representation
          currentNode = new DBSAXNode(conn, localName, parentNode, currentDocument);
          currentNode = new DBSAXNode(conn, localName, parentNode,
                                      currentDocument.getRootNodeID(),docid,
                                      currentDocument.getDoctype());
          // Add all of the attributes
          for (int i=0; i<atts.getLength(); i++) {
            currentNode.setAttribute(atts.getLocalName(i), atts.getValue(i));
            currentNode.setAttribute(atts.getLocalName(i), atts.getValue(i), docid);
+         }
          // Add the node to the stack, so that any text data can be
-...
+           }
            // Write the content of the node to the database
            currentNode.writeChildNodeToDB("TEXT", null, data);
            currentNode.writeChildNodeToDB("TEXT", null, data, docid);
+         }
+       }
-...
               throws SAXException {
          MetaCatUtil.debugMessage("PI");
          DBSAXNode currentNode = (DBSAXNode)nodeStack.peek();
          currentNode.writeChildNodeToDB("PI", target, data);
          currentNode.writeChildNodeToDB("PI", target, data, docid);
+       }
        /** SAX Handler that is called at the end of each XML element */
-...
        public void comment(char[] ch, int start, int length) throws SAXException {
          MetaCatUtil.debugMessage("COMMENT");
          DBSAXNode currentNode = (DBSAXNode)nodeStack.peek();
          currentNode.writeChildNodeToDB("COMMENT", null, new String(ch));
          currentNode.writeChildNodeToDB("COMMENT", null, new String(ch), docid);
+       }
        /**

        */
       public DocumentImpl(Connection conn, long rootnodeid, String docname,
                           String doctype, String docid, String action, String user)
                           throws AccessionNumberException
                           throws SQLException, Exception
+      {
         this.conn = conn;
         this.rootnodeid = rootnodeid;
-...
        */
       public static String write( Connection conn, String filename, String action,
                                   String docid, String user, String group )
                     throws IOException, SQLException, ClassNotFoundException,
                            SAXException, SAXParseException, Exception {
                     throws Exception {
         return write(conn, new FileReader(new File(filename).toString()),
                       action, docid, user, group);
-...
        */
       public static String write( Connection conn, Reader xml, String action,
                                   String docid, String user, String group )
                     throws IOException, SQLException, ClassNotFoundException,
                            SAXException, SAXParseException, Exception {
                     throws Exception {
         // Determine if the docid is OK for INSERT or UPDATE
         AccessionNumber ac = new AccessionNumber();
         String newdocid = ac.generate(docid, action);
         if ( action.equals("UPDATE") ) {
           // Determine if the docid is OK for UPDATE
           AccessionNumber ac = new AccessionNumber();
           String newdocid = ac.generate(docid, "UPDATE");
           // check for 'write' permission for 'user' to update this document
           if ( !hasWritePermission(conn, docid, user, group) ) {
             throw new Exception("User " + user +
-...
+        }
         try {
             XMLReader parser = initializeParser(conn, action, docid, user);
             XMLReader parser = initializeParser(conn, action, newdocid, user);
             conn.setAutoCommit(false);
             parser.parse(new InputSource(xml));
             conn.commit();
             conn.setAutoCommit(true);
             return docid;
             //return newdocid;
           } catch (SAXParseException e) {
             conn.rollback();
             conn.setAutoCommit(true);
             throw e;
           } catch (SAXException e) {
             conn.rollback();
             conn.setAutoCommit(true);
             throw e;
     /*
             // If its a problem with the accession number its ok, just the
             // accession number was regenerated
             AccessionNumberGeneratedException ang = null;
-...
               conn.rollback();
               throw e;
+            }
     */
           } catch (Exception e) {
             conn.rollback();
             conn.setAutoCommit(true);
             throw e;
+          }
           if ( (docid != null) && !(newdocid.equals(docid)) ) {
             return new String("New document ID generated:" + newdocid);
           } else {
             return newdocid;
+          }
+      }
       /**
-...
        */
       public static void delete( Connection conn, String docid,
                                      String user, String group )
                     throws IOException, SQLException, ClassNotFoundException,
                            AccessionNumberException, Exception {
                     throws Exception {
         // Determine if the docid is OK for DELETE
         AccessionNumber ac = new AccessionNumber();
-...
         stmt.execute("DELETE FROM xml_documents WHERE docid = '" + docid + "'");
         stmt.close();
         conn.commit();
         conn.setAutoCommit(true);
+      }
       /** Check for "write" permissions from DB connection */
-...
       /** creates SQL code and inserts new document into DB connection */
       private void writeDocumentToDB(String action, String user)
                    throws AccessionNumberException {
                    throws SQLException, Exception {
         try {
           PreparedStatement pstmt = null;
           if (action.equals("INSERT")) {
             AccessionNumber ac = new AccessionNumber();
             this.docid = ac.generate(docid, "INSERT");
             //AccessionNumber ac = new AccessionNumber();
             //this.docid = ac.generate(docid, "INSERT");
             pstmt = conn.prepareStatement(
                 "INSERT INTO xml_documents " +
                 "(docid, rootnodeid, docname, doctype, " +
-...
           pstmt.execute();
           pstmt.close();
         } catch (SQLException e) {
           System.out.println(e.getMessage());
         } catch (AccessionNumberException ane) {
           MetaCatUtil.debugMessage("Invalid accession number.");
           MetaCatUtil.debugMessage(ane.getMessage());
           throw ane;
         } catch (SQLException sqle) {
           throw sqle;
     //    } catch (AccessionNumberException ane) {
     //      MetaCatUtil.debugMessage("Invalid accession number.");
     //      MetaCatUtil.debugMessage(ane.getMessage());
     //      throw ane;
         } catch (Exception e) {
           System.out.println(e.getMessage());
           throw e;
+        }
+      }
-...
+      }
       /** Save a document entry in the xml_revisions table */
     /*
       private static void archiveDocRevision(Connection conn, String docid,
                                              String user) throws SQLException {
         // First get all of the values we need
         long rnodeid = -1;
         String docname = null;
         String doctype = null;
         String doctitle = null;
         String user_owner = null;
         Date date_created = null;
         PreparedStatement pstmt = conn.prepareStatement(
           "SELECT rootnodeid,docname,doctype,doctitle,user_owner,date_created " +
           "FROM xml_documents " +
           "WHERE docid = ?");
         // Bind the values to the query and execute it
         pstmt.setString(1, docid);
         pstmt.execute();
         ResultSet rs = pstmt.getResultSet();
         boolean tableHasRows = rs.next();
         if (tableHasRows) {
           rnodeid      = rs.getLong(1);
           docname      = rs.getString(2);
           doctype      = rs.getString(3);
           doctitle     = rs.getString(4);
           user_owner   = rs.getString(5);
           date_created = rs.getDate(6);
+        }
         pstmt.close();
         MetaCatUtil.debugMessage(new Long(rnodeid).toString());
         MetaCatUtil.debugMessage(docname);
         MetaCatUtil.debugMessage(doctitle);
         //MetaCatUtil.debugMessage(date_created.toString());
         // Next create the new record in the other table using the values selected
         pstmt = conn.prepareStatement(
            "INSERT INTO xml_revisions " +
            "(revisionid, docid, rootnodeid, docname, doctype, doctitle, " +
            "user_owner, user_updated, date_created, date_updated) " +
            "VALUES (null, ?, ?, ?, ?, ?, ?, ?, sysdate, sysdate)");
         // Bind the values to the query and execute it
         pstmt.setString(1, docid);
         pstmt.setLong(2, rnodeid);
         pstmt.setString(3, docname);
         pstmt.setString(4, doctype);
         pstmt.setString(5, doctitle);
         pstmt.setString(6, user_owner);
         pstmt.setString(7, user);
         //pstmt.setDate(6, date_created);
         pstmt.execute();
         pstmt.close();
+      }
     */
       /**
        * Set up the parser handlers for writing the document to the database
        */
       private static XMLReader initializeParser(Connection conn,
                                String action, String docid, String user) {
                                String action, String docid, String user)
                                throws Exception {
         XMLReader parser = null;
         //
         // Set up the SAX document handlers for parsing
-...
           parser.setErrorHandler((ErrorHandler)chandler);
         } catch (Exception e) {
            System.err.println(e.toString());
           throw e;
+        }
         return parser;

     import java.io.IOException;
     import java.util.Hashtable;
     import java.util.Enumeration;
     import oracle.jdbc.driver.*;
     /**
      * A Class that represents an XML node and its contents and
-...
+       *
        * @param conn the JDBC Connection to which all information is written
        */
       public DBSAXNode (Connection conn) {
       public DBSAXNode (Connection conn, String docid) {
         super();
         this.conn = conn;
         this.parentNode = null;
         writeChildNodeToDB("DOCUMENT", null, null);
         writeChildNodeToDB("DOCUMENT", null, null, docid);
+      }
       /**
-...
        * @param parentNode the parent node for this node being created
        */
       public DBSAXNode (Connection conn, String tagname, DBSAXNode parentNode,
                         DocumentImpl currentDocument) {
                         long rootnodeid, String docid, String doctype) {
         super(tagname);
         setParentID(parentNode.getNodeID());
         setRootNodeID(currentDocument.getRootNodeID());
         setDocID(currentDocument.getDocID());
         setRootNodeID(rootnodeid);
         setDocID(docid);
         setNodeIndex(parentNode.incChildNum());
         this.conn = conn;
         this.parentNode = parentNode;
         writeChildNodeToDB("ELEMENT", getTagName(), null);
         updateNodeIndex(currentDocument.getDocID(), currentDocument.getDoctype());
         writeChildNodeToDB("ELEMENT", getTagName(), null, docid);
         updateNodeIndex(docid, doctype);
+      }
       /** creates SQL code and inserts new node into DB connection */
       public void writeChildNodeToDB(String nodetype, String nodename,
                                      String data) {
                                      String data, String docid) {
         try {
           PreparedStatement pstmt;
           if (nodetype == "DOCUMENT") {
             pstmt = conn.prepareStatement(
                 "INSERT INTO xml_nodes " +
                 "(nodeid, nodetype, nodename, rootnodeid) " +
                 "VALUES (?, ?, ?, ?)");
                 "(nodeid, nodetype, nodename, docid, rootnodeid) " +
                 "VALUES (?, ?, ?, ?, ?)");
             MetaCatUtil.debugMessage("INSERTING DOCNAME: " + nodename);
           } else {
             pstmt = conn.prepareStatement(
                 "INSERT INTO xml_nodes " +
                 "(nodeid, nodetype, nodename, " +
                 "rootnodeid, parentnodeid, docid, nodedata, nodeindex) " +
                 "(nodeid, nodetype, nodename, docid, " +
                 "rootnodeid, parentnodeid, nodedata, nodeindex) " +
                 "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
+          }
-...
           pstmt.setLong(1, nid);
           pstmt.setString(2, nodetype);
           pstmt.setString(3, nodename);
           pstmt.setString(4, docid);
           if (nodetype == "DOCUMENT") {
             pstmt.setLong(4, nid);
             pstmt.setLong(5, nid);
           } else {
             if (nodetype == "ELEMENT") {
               pstmt.setLong(4, getRootNodeID());
               pstmt.setLong(5, getParentID());
               pstmt.setString(6, getDocID());
               pstmt.setLong(5, getRootNodeID());
               pstmt.setLong(6, getParentID());
               pstmt.setString(7, data);
               pstmt.setInt(8, getNodeIndex());
             } else {
               pstmt.setLong(4, getRootNodeID());
               pstmt.setLong(5, getNodeID());
               pstmt.setString(6, getDocID());
               pstmt.setLong(5, getRootNodeID());
               pstmt.setLong(6, getNodeID());
               if ( nodetype == "TEXT" && getTagName().equals("meta_file_id") ) {
                 pstmt.setString(7, getDocID());
               } else {
-...
+      }
       /** Add a new attribute to this node, or set its value */
       public void setAttribute(String attName, String attValue) {
       public void setAttribute(String attName, String attValue, String docid) {
         if (attName != null) {
           // Enter the attribute in the hash table
           super.setAttribute(attName, attValue);
           // And enter the attribute in the database
           writeChildNodeToDB("ATTRIBUTE", attName, attValue);
           writeChildNodeToDB("ATTRIBUTE", attName, attValue, docid);
         } else {
           System.err.println("Attribute name must not be null!");
+        }
-...
         boolean atRootDocumentNode = false;
         DBSAXNode nodePointer = this;
         StringBuffer currentPath = new StringBuffer();
         int counter = 0;
         // Create a Hashtable of all of the paths to reach this node
         // including absolute paths and relative paths
-...
           if (atStartingNode) {
             currentPath.insert(0, nodePointer.getTagName());
             pathlist.put(currentPath.toString(), new Long(getNodeID()));
             counter++;
             atStartingNode = false;
           } else {
             currentPath.insert(0, "/");
             currentPath.insert(0, nodePointer.getTagName());
             pathlist.put(currentPath.toString(), new Long(getNodeID()));
             counter++;
+          }
           // advance to the next parent node
-...
           if (nodePointer.getNodeType().equals("DOCUMENT")) {
             currentPath.insert(0, "/");
             pathlist.put(currentPath.toString(), new Long(getNodeID()));
             counter++;
             atRootDocumentNode = true;
+          }
+        }
-...
                   "INSERT INTO xml_index (nodeid, path, docid, doctype, " +
                    "parentnodeid) " +
                   "VALUES (?, ?, ?, ?, ?)");
           ((OraclePreparedStatement)pstmt).setExecuteBatch(counter);
           pstmt.setString(3, docid);
           pstmt.setString(4, doctype);
-...
             Long nodeid = (Long)pathlist.get(path);
             pstmt.setLong(1, nodeid.longValue());
             pstmt.setString(2, path);
             pstmt.execute();
             pstmt.executeUpdate();
             //System.out.println(nodeid + " ==> " + path);
+          }

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 457

Added by bojilova over 23 years ago