/ - Diff - Metacat - Ecoinformatics Redmine

     /**
      *  '$RCSfile$'
      *    Purpose: A Class for upgrading the database to version 1.5
      *  Copyright: 2000 Regents of the University of California and the
      *             National Center for Ecological Analysis and Synthesis
      *    Authors: Saurabh Garg
+     *
      *   '$Author$'
      *     '$Date$'
      * '$Revision$'
+     *
      * 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
      */
     import java.io.*;
     import java.sql.Connection;
     import java.sql.ResultSet;
     import java.sql.PreparedStatement;
     import java.sql.Statement;
     import java.sql.SQLException;
     import java.sql.DriverManager;
     import edu.ucsb.nceas.metacat.properties.PropertyService;
     import edu.ucsb.nceas.utilities.PropertyNotFoundException;
     public class upgrade_db_to_1_5{
         static String database = null;
         static String url = null;
         static String user = null;
         static String password = null;
         static {
         	try {
         	    database = PropertyService.getProperty("database.type");
         	    url = PropertyService.getProperty("database.connectionURI");
         	    user = PropertyService.getProperty("database.user");
         	    password = PropertyService.getProperty("database.password");
         	} catch (PropertyNotFoundException pnfe) {
                 System.err.println("Error in upgrade_db_to_1_5 static block:"
                         + pnfe.getMessage());
                 pnfe.printStackTrace();
+        	}
+         }
         public static void upgrade_xml_nodes(Connection sqlca) throws SQLException{
             Statement sqlStatement = null;
             PreparedStatement pstmt = null;
             ResultSet rset = null;
             // Delete old nodedatanumerical column from xml_nodes if one exsists
             try {
                 System.out.println(
                     "Deleting old nodedatanumerical column from xml_nodes...");
                 pstmt = sqlca.prepareStatement(
                     "ALTER TABLE xml_nodes DROP COLUMN nodedatanumerical");
                 pstmt.execute();
                 pstmt.close();
                 System.out.println("Done.");
             } catch (Exception e) {
                 System.out.println(" column not found.");
+            }
             // Create nodedatanumerical column in xml_nodes
             System.out.println(
                 "Creating new nodedatanumerical column in xml_nodes...");
             if (database.equals("oracle")) {
                 pstmt = sqlca.prepareStatement(
                     "ALTER TABLE xml_nodes ADD nodedatanumerical NUMBER");
+            }
             else {
                 pstmt = sqlca.prepareStatement(
                     "ALTER TABLE xml_nodes ADD nodedatanumerical FLOAT8");
+            }
             pstmt.execute();
             pstmt.close();
             System.out.println("Done.");
             // Copy numerical values from nodedata to
             // nodedatanumerical column in xml_nodes
             System.out.println(
                 "Please be patient as the next upgrade step can be extremely time consuming.");
             System.out.println(
                 "Copy numerical values from nodedata to nodedatanumerical "
                 + "in xml_nodes...");
             sqlStatement = sqlca.createStatement();
             rset = sqlStatement.executeQuery(
                 "SELECT DISTINCT NODEID, NODEDATA "
                 + "FROM xml_nodes WHERE "
                 + "nodedata IS NOT NULL AND "
                 + "UPPER(nodedata) = LOWER(nodedata)");
             int count = 0;
             while (rset.next()) {
                 String nodeid = rset.getString(1);
                 String nodedata = rset.getString(2);
                 try {
                     if (!nodedata.trim().equals("")) {
                         double s = Double.parseDouble(nodedata);
                         pstmt = sqlca.prepareStatement(
                             "update xml_nodes set nodedatanumerical = " + s +
                             " where nodeid=" + nodeid);
                         pstmt.execute();
                         pstmt.close();
                         count++;
                         if (count%5 == 0) {
                             System.out.println(count + "...");
+                        }
+                    }
                 } catch (NumberFormatException nfe) {
                 } catch (Exception e) {
                     System.out.println("Exception:" + e.getMessage());
+                }
+            }
             System.out.println("\nDone. " + count + " values copied.");
             rset.close();
             sqlStatement.close();
+        }
         public static void main(String [] ags){
             try {
                 Connection sqlca = null;
                 // Create a JDBC connection to the database
                 if (database.equals("oracle")) {
                 	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
+                }
                 else if (database.equals("postgresql")) {
                 	DriverManager.registerDriver(new org.postgresql.Driver());
+                }
                 else if (database.equals("sqlserver")) {
                 	DriverManager.registerDriver(new com.microsoft.jdbc.sqlserver.SQLServerDriver());
+                }
                 sqlca = DriverManager.getConnection(url, user, password);
                 // Upgrade the xml_nodes table only if it oracle
     	    if (database.equals("oracle")) {
                 	upgrade_xml_nodes(sqlca);
+    	    }
                 // Close the connection...
                 sqlca.close();
             } catch (SQLException ex) {
                 System.out.println("SQLException:" + ex.getMessage());
+            }
+        }
+    }

     database.upgradeVersion.1.9.2=upgrade-db-to-1.9.2
     database.upgradeVersion.1.9.3=upgrade-db-to-1.9.3
     database.upgradeVersion.1.10.0=upgrade-db-to-1.10.0
     ## for running java-based utilities
     database.upgradeUtility.1.5.0=edu.ucsb.nceas.metacat.admin.upgrade.Upgrade1_5_0
     database.upgradeUtility.1.10.0=edu.ucsb.nceas.metacat.admin.upgrade.Upgrade1_10_0
     database.initialConnections=5
     database.incrementConnections=5
     database.maximumConnections=200

     	docid		VARCHAR2(250),	-- index to the document id
     	date_created	DATE,
     	date_updated	DATE,
     	nodedatanumerical NUMBER,       -- the data for this node if
     					-- it is a number
     	nodedatanumerical NUMBER,       -- the data for this node if it is a number
     	nodedatadate TIMESTAMP,       -- the data for this node if it is a date
        CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
        CONSTRAINT xml_nodes_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
-...
             docid           VARCHAR2(250),  -- index to the document id
             date_created    DATE,
             date_updated    DATE,
             nodedatanumerical NUMBER,       -- the data for this node if
                                             -- it is a number
             nodedatanumerical NUMBER,       -- the data for this node if it is a number
             nodedatadate TIMESTAMP,       -- the data for this node if it is a date
        CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
        CONSTRAINT xml_nodes_revisions_root_fk
                     FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
-...
             path            VARCHAR2(1000), -- precomputed path through tree
     	    nodedata        VARCHAR2(4000), -- the data for this node e.g.,
             nodedatanumerical NUMBER(20),   -- the data for this node if
             parentnodeid    NUMBER(20),     -- index of the parent of this node
     		nodedatadate TIMESTAMP,       -- the data for this node if it is a date
     		parentnodeid    NUMBER(20),     -- index of the parent of this node
             CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
             CONSTRAINT xml_path_index_docid_fk FOREIGN KEY (docid) REFERENCES xml_documents
      );
-...
     CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
     CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
     CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
     CREATE INDEX xml_path_index_idx4 ON xml_path_index (nodedatadate);
     CREATE TABLE xml_relation (
     	relationid    NUMBER(20) PRIMARY KEY, -- unique id
     	docid         VARCHAR2(250),          -- the docid of the package file

     );
     /*
      * add the nodedatadate column to the tables that need it
      */
     ALTER TABLE xml_nodes ADD COLUMN nodedatadate TIMESTAMP;
     ALTER TABLE xml_nodes_revisions ADD COLUMN nodedatadate TIMESTAMP;
     ALTER TABLE xml_path_index ADD COLUMN nodedatadate TIMESTAMP;
     CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate);
     /*
      * Register the DataONE schemas
      */

     	docid VARCHAR(250),	-- index to the document id
     	date_created DATE,
     	date_updated DATE,
             nodedatanumerical FLOAT8, -- the data for this node if
     				  -- if it is a number
         nodedatanumerical FLOAT8, -- the data for this node if it is a number
         nodedatadate TIMESTAMP, -- the data for this node if it is a date
        CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
        CONSTRAINT xml_nodes_root_fk
     		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
-...
             docid VARCHAR(250),     -- index to the document id
             date_created DATE,
             date_updated DATE,
             nodedatanumerical FLOAT8, -- the data for this node if
                                       -- if it is a number
             nodedatanumerical FLOAT8, -- the data for this node if it is a number
             nodedatadate TIMESTAMP, -- the data for this node if it is a date
        CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
        CONSTRAINT xml_nodes_revisions_root_fk
                     FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
-...
             path VARCHAR(1000),     -- precomputed path through tree
             nodedata TEXT, -- the data for this node (e.g.,
                                     -- for TEXT it is the content)
             nodedatanumerical FLOAT8, -- the data for this node if
                                       -- if it is a number
             nodedatanumerical FLOAT8, -- the data for this node if it is a number
             nodedatadate TIMESTAMP, -- the data for this node if it is a date
             parentnodeid INT8,      -- id of the parent of the node represented
                                     -- by this row
        CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
-...
     CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
     CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
     CREATE INDEX xml_path_index_idx4 ON xml_path_index (upper(nodedata));
     CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate);
     /*
      * harvest_site_schedule -- table to store harvest sites and schedule info

     );
     /*
      * add the nodedatadate column to xml_nodes
      * TODO: load the data into it (java?)
      */
     ALTER TABLE xml_nodes ADD COLUMN nodedatadate TIMESTAMP;
     ALTER TABLE xml_nodes_revisions ADD COLUMN nodedatadate TIMESTAMP;
     ALTER TABLE xml_path_index ADD COLUMN nodedatadate TIMESTAMP;
     CREATE INDEX xml_path_index_idx4 ON xml_path_index (nodedatadate);
     /*
      * Register the DataONE schemas
      */
     INSERT INTO xml_catalog (entry_type, public_id, system_id)

     import java.sql.PreparedStatement;
     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.sql.Timestamp;
     import java.util.Enumeration;
     import java.util.Hashtable;
     import java.util.Timer;
-...
                                     + pathIndex);
                             if(pathIndex.indexOf("@")<0){
                                 pstmt = conn.prepareStatement("SELECT DISTINCT n.docid, "
                                         + "n.nodedata, n.nodedatanumerical, n.parentnodeid"
                                         + "n.nodedata, n.nodedatanumerical, n.nodedatadate, n.parentnodeid"
                                         + " FROM xml_nodes n, xml_index i WHERE"
                                         + " i.path = ? and n.parentnodeid=i.nodeid and"
                                         + " n.nodetype LIKE 'TEXT' order by n.parentnodeid");
                             } else {
                                 pstmt = conn.prepareStatement("SELECT DISTINCT n.docid, "
                                         + "n.nodedata, n.nodedatanumerical, n.parentnodeid"
                                         + "n.nodedata, n.nodedatanumerical, n.nodedatadate, n.parentnodeid"
                                         + " FROM xml_nodes n, xml_index i WHERE"
                                         + " i.path = ? and n.nodeid=i.nodeid and"
                                         + " n.nodetype LIKE 'ATTRIBUTE' order by n.parentnodeid");
-...
                                     String docid = rs.getString(1);
                                     String nodedata = rs.getString(2);
                                     float nodedatanumerical = rs.getFloat(3);
                                     int parentnodeid = rs.getInt(4);
                                     Timestamp nodedatadate = rs.getTimestamp(4);
                                     int parentnodeid = rs.getInt(5);
                                     if (!nodedata.trim().equals("")) {
                                         pstmt1 = conn.prepareStatement(
                                                 "INSERT INTO xml_path_index"
                                                 + " (docid, path, nodedata, "
                                                 + "nodedatanumerical, parentnodeid)"
                                                 + " VALUES (?, ?, ?, ?, ?)");
                                                 + "nodedatanumerical, nodedatadate, parentnodeid)"
                                                 + " VALUES (?, ?, ?, ?, ?, ?)");
                                         pstmt1.setString(1, docid);
                                         pstmt1.setString(2, pathIndex);
                                         pstmt1.setString(3, nodedata);
                                         pstmt1.setFloat(4, nodedatanumerical);
                                         pstmt1.setInt(5, parentnodeid);
                                         pstmt1.setTimestamp(5, nodedatadate);
                                         pstmt1.setInt(6, parentnodeid);
                                         pstmt1.execute();
                                         pstmt1.close();

     import java.sql.ResultSet;
     import java.sql.SQLException;
     import java.sql.Statement;
     import java.sql.Timestamp;
     import java.util.Hashtable;
     import java.util.HashMap;
     import java.util.Iterator;
-...
             String leafData = leafRecord.getNodeData();
             long leafParentId = leafRecord.getParentNodeId();
             float leafDataNumerical = leafRecord.getNodeDataNumerical();
             Timestamp leafDataDate = leafRecord.getNodeDataDate();
             if ( current.getNodeType().equals("ELEMENT") ||
                 current.getNodeType().equals("ATTRIBUTE") ) {
-...
     					logMetacat.debug("DocumentImpl.traverseParents - paths found for indexing: " + currentName);
     					pathsFoundForIndexing.put(currentName, new PathIndexEntry(
     							leafNodeId, currentName, docid, leafParentId, leafData,
     							leafDataNumerical));
     							leafDataNumerical, leafDataDate));
+    				}
+                }
-...
     						&& leafData.trim().length() != 0) {
     					logMetacat.debug("DocumentImpl.traverseParents - paths found for indexing: " + currentName);
     					pathsFoundForIndexing.put(path, new PathIndexEntry(leafNodeId,
     							path, docid, leafParentId, leafData, leafDataNumerical));
     							path, docid, leafParentId, leafData, leafDataNumerical, leafDataDate));
+    				}
+                }
                 // process absolute xpaths
-...
     					logMetacat.debug("DocumentImpl.traverseParents - paths found for indexing: " + currentName);
     					pathsFoundForIndexing.put(fullPath, new PathIndexEntry(
     							leafNodeId, fullPath, docid, leafParentId, leafData,
     							leafDataNumerical));
     							leafDataNumerical, leafDataDate));
+    				}
+                }
+            }
-...
             // insertions
             PreparedStatement pstmt = conn.prepareStatement("INSERT INTO "
                     + "xml_path_index (docid, path, nodedata, "
                     + "nodedatanumerical, parentnodeid)"
                     + " VALUES (?, ?, ?, ?, ?)");
                     + "nodedatanumerical, nodedatadate, parentnodeid)"
                     + " VALUES (?, ?, ?, ?, ?, ?)");
             // Step through the hashtable and insert each of the path values
             Iterator<PathIndexEntry> it = pathsFound.values().iterator();
-...
                  pstmt.setString(2, entry.path);
                  pstmt.setString(3, entry.nodeData);
                  pstmt.setFloat(4, entry.nodeDataNumerical);
                  pstmt.setLong(5, entry.parentId);
                  pstmt.setTimestamp(5, entry.nodeDataDate);
                  pstmt.setLong(6, entry.parentId);
                  logMetacat.debug("DocumentImpl.updatePathIndex - executing SQL: " + pstmt.toString());
                  pstmt.execute();
+            }
-...
             String nodeprefix = null;
             String nodedata = null;
             float nodedatanumerical = -1;
             Timestamp nodedatadate = null;
     //        String quotechar = DatabaseService.getDBAdapter().getStringDelimiter();
             String table = "xml_nodes";
             //System.out.println("in getNodeREcorelist !!!!!!!!!!!for root node id "+rootnodeid);
-...
                 serialNumber = dbconn.getCheckOutSerialNumber();
                 pstmt = dbconn
                         .prepareStatement("SELECT nodeid,parentnodeid,nodeindex, "
                                 + "nodetype,nodename,nodeprefix,nodedata, nodedatanumerical "
                                 + "nodetype,nodename,nodeprefix,nodedata, nodedatanumerical, nodedatadate "
                                 + "FROM " + table + " WHERE rootnodeid = ?");
                 // Bind the values to the query
-...
                     	logMetacat.warn("DocumentImpl.getNodeRecordList - StringIndexOutOfBoundsException in normalize() while reading the document");
+                    }
                     nodedatanumerical = rs.getFloat(8);
                     nodedatadate = rs.getTimestamp(9);
                     // add the data to the node record list hashtable
                     NodeRecord currentRecord = new NodeRecord(nodeid, parentnodeid,
                             nodeindex, nodetype, nodename, nodeprefix, nodedata, nodedatanumerical);
                             nodeindex, nodetype, nodename, nodeprefix, nodedata, nodedatanumerical, nodedatadate);
                     nodeRecordList.add(currentRecord);
                     // Advance to the next node
-...
             pstmt = dbconn.prepareStatement("INSERT INTO xml_nodes_revisions "
                     + "(nodeid, nodeindex, nodetype, nodename, nodeprefix, "
                     + "nodedata, parentnodeid, rootnodeid, docid, date_created,"
                     + " date_updated, nodedatanumerical) "
                     + " date_updated, nodedatanumerical, nodedatadate) "
                     + "SELECT nodeid, nodeindex, nodetype, nodename, nodeprefix, "
                     + "nodedata, parentnodeid, rootnodeid, docid, date_created,"
                     + " date_updated, nodedatanumerical "
                     + " date_updated, nodedatanumerical, nodedatadate "
                     + "FROM xml_nodes WHERE rootnodeid = ?");
             // Increase dbconnection usage count

     package edu.ucsb.nceas.metacat;
     import java.sql.*;
     import java.text.ParseException;
     import java.text.SimpleDateFormat;
     import java.util.Date;
     import java.util.Hashtable;
     import java.util.Enumeration;
     import org.apache.log4j.Logger;
-...
+        {
           PreparedStatement pstmt;
           Timestamp timestampData = null;
           if (nodetype == "DOCUMENT") {
             pstmt = connection.prepareStatement(
                 "INSERT INTO xml_nodes " +
-...
           } else {
               if(data != null && !data.trim().equals("")
                  && !data.trim().equals("NaN") && !data.trim().equalsIgnoreCase("Infinity")){
                   try{
                       double numberData = Double.parseDouble(data);
             	  try{
             		  // try some common ISO 8601 formats
             		  SimpleDateFormat sdf = null;
             		  if (data.length() == 10) {
             			  sdf = new SimpleDateFormat("yyyy-MM-dd");
             		  } else if (data.length() == 19) {
             			  sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
             		  } else {
             			  // throw this so we continue with parsing as numeric or string.
             			  throw new ParseException("String length will not match date/dateTime patterns", -1);
+            		  }
             		  Date dateData = sdf.parse(data);
             		  timestampData = new Timestamp(dateData.getTime());
                       pstmt = connection.prepareStatement(
                           "INSERT INTO xml_nodes " +
                           "(nodetype, nodename, nodeprefix, docid, " +
                           "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatanumerical) " +
                           "VALUES (?, ?, ?, ?, ?, ?, ?, ?, "+ numberData +")");
                   } catch (NumberFormatException nfe) {
                       pstmt = connection.prepareStatement(
                           "INSERT INTO xml_nodes " +
                           "(nodetype, nodename, nodeprefix, docid, " +
                           "rootnodeid, parentnodeid, nodedata, nodeindex) " +
                           "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
                           "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatadate) " +
                           "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                   } catch (ParseException pe) {
     	        	  try{
     	                  double numberData = Double.parseDouble(data);
     	                  pstmt = connection.prepareStatement(
     	                      "INSERT INTO xml_nodes " +
     	                      "(nodetype, nodename, nodeprefix, docid, " +
     	                      "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatanumerical) " +
     	                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, "+ numberData +")");
     	              } catch (NumberFormatException nfe) {
     	                  pstmt = connection.prepareStatement(
     	                      "INSERT INTO xml_nodes " +
     	                      "(nodetype, nodename, nodeprefix, docid, " +
     	                      "rootnodeid, parentnodeid, nodedata, nodeindex) " +
     	                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
+    	              }
+                  }
               } else {
                   pstmt = connection.prepareStatement(
-...
               pstmt.setInt(8, incChildNum());
+            }
+          }
           if (timestampData != null) {
         	  pstmt.setTimestamp(9, timestampData);
+          }
           // Do the insertion
           logMetacat.debug("DBSAXNode.writeChildNodeToDBDataLimited - SQL insert: " + pstmt.toString());
           pstmt.execute();
-...
+        {
           PreparedStatement pstmt;
           Timestamp timestampData = null;
           logMetacat.info("DBSAXNode.writeDTDNodeToDB - Insert dtd into db: "+nodename +" "+data);
           if(data != null && !data.trim().equals("")){
                 try{
         	  try{
         		  // try some common ISO 8601 formats
         		  SimpleDateFormat sdf = null;
         		  if (data.length() == 10) {
         			  sdf = new SimpleDateFormat("yyyy-MM-dd");
         		  } else if (data.length() == 19) {
         			  sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
+        		  }
         		  Date dateData = sdf.parse(data);
         		  timestampData = new Timestamp(dateData.getTime());
                   pstmt = connection.prepareStatement(
                       "INSERT INTO xml_nodes " +
                       "(nodetype, nodename, docid, " +
                       "rootnodeid, parentnodeid, nodedata, nodeindex, nodedatadate) " +
                       "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
               } catch (ParseException pe) {
         	  	try{
                     double numberData = Double.parseDouble(data);
                     pstmt = connection.prepareStatement(
                         "INSERT INTO xml_nodes " +
-...
                         "rootnodeid, parentnodeid, nodedata, nodeindex) " +
                         "VALUES (?, ?, ?, ?, ?, ?, ?)");
+                }
+              }
             } else {
                 pstmt = connection.prepareStatement(
                       "INSERT INTO xml_nodes " +
-...
           pstmt.setLong(5, getParentID());
           pstmt.setString(6, data);
           pstmt.setInt(7, getNodeIndex());
           if (timestampData != null) {
         	  pstmt.setTimestamp(8, timestampData);
+          }
           // Do the insertion
           pstmt.execute();

     package edu.ucsb.nceas.metacat;
     import java.text.ParseException;
     import java.text.SimpleDateFormat;
     import java.util.Date;
     import java.util.Vector;
     import org.apache.log4j.Logger;
-...
                                     + searchmode);
                     return null;
+                }
                 try {
                     // it is number; numeric comparison
                     // but we need to make sure there is no string in node data
                     searchexpr = nodedataterm + " " + oper + " "
                             + new Double(casevalue) + " ";
                 } catch (NumberFormatException nfe) {
                     // these are characters; character comparison
                     searchexpr = nodedataterm + " " + oper + " '" + casevalue
                             + "' ";
+                }
                 	// try some common ISO 8601 formats
             		SimpleDateFormat sdf = null;
             		if (casevalue.length() == 10) {
             			sdf = new SimpleDateFormat("yyyy-MM-dd");
             		} else if (casevalue.length() == 19) {
             			sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
+            		}
             		Date dataDateValue = sdf.parse(casevalue);
                     nodedataterm = "nodedatadate";
             		searchexpr =
             			nodedataterm + " " + oper + " '"
             			+ casevalue + "' ";
             	} catch (ParseException pe) {
     	            try {
     	                // it is number; numeric comparison
     	                // but we need to make sure there is no string in node data
     	                searchexpr = nodedataterm + " " + oper + " "
     	                        + new Double(casevalue) + " ";
     	            } catch (NumberFormatException nfe) {
     	                // these are characters; character comparison
     	                searchexpr = nodedataterm + " " + oper + " '" + casevalue
     	                        + "' ";
+    	            }
+            	}
+            }

      */
     package edu.ucsb.nceas.metacat;
     import java.sql.Timestamp;
     /**
      * PathIndexEntry contains all of the data fields needed to insert an path into
      * the xml_index table or the xml_path_index table, depending on which
-...
         protected long parentId;
         protected String nodeData;
         protected float nodeDataNumerical;
         protected Timestamp nodeDataDate;
         /**
          * Construct a new PathIndexEntry for the xml_index table.
+         *
-...
          * @param nodeDataNumerical the node value as a double precision number
          */
         public PathIndexEntry(long nodeId, String path, String docid,
                 long parentId, String nodeData, float nodeDataNumerical )
                 long parentId, String nodeData, float nodeDataNumerical, Timestamp nodeDataDate )
+        {
             this.nodeId = nodeId;
             this.path = path;
             this.docid = docid;
             this.nodeData = nodeData;
             this.nodeDataNumerical = nodeDataNumerical;
             this.nodeDataDate = nodeDataDate;
             this.parentId = parentId;
+        }
+    }

     package edu.ucsb.nceas.metacat.admin.upgrade;
     /**
      *  '$RCSfile$'
      *    Purpose: A Class for upgrading the database to version 1.5
      *  Copyright: 2000 Regents of the University of California and the
      *             National Center for Ecological Analysis and Synthesis
      *    Authors: Saurabh Garg
+     *
      *   '$Author$'
      *     '$Date$'
      * '$Revision$'
+     *
      * 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
      */
     import java.sql.Connection;
     import java.sql.Driver;
     import java.sql.ResultSet;
     import java.sql.PreparedStatement;
     import java.sql.Statement;
     import java.sql.DriverManager;
     import java.sql.Timestamp;
     import java.text.ParseException;
     import java.text.SimpleDateFormat;
     import java.util.Date;
     import edu.ucsb.nceas.metacat.admin.AdminException;
     import edu.ucsb.nceas.metacat.properties.PropertyService;
     import edu.ucsb.nceas.utilities.SortedProperties;
     public class Upgrade1_10_0 implements UpgradeUtilityInterface {
         private String driver = null;
         private String url = null;
         private String user = null;
         private String password = null;
         public boolean upgrade() throws AdminException {
         	try {
         		// get the properties
         		driver = PropertyService.getProperty("database.driver");
         	    url = PropertyService.getProperty("database.connectionURI");
         	    user = PropertyService.getProperty("database.user");
         	    password = PropertyService.getProperty("database.password");
     	        // Create a JDBC connection to the database
     	    	Connection sqlca = null;
     	        Driver d = (Driver) Class.forName(driver).newInstance();
     	        DriverManager.registerDriver(d);
     	        sqlca = DriverManager.getConnection(url, user, password);
     	        // three tables need to be updated here
     	        String[] tablesToUpdate = {"xml_nodes", "xml_nodes_revisions", "xml_path_index"};
     	        for (String tableName: tablesToUpdate) {
     		        Statement sqlStatement = null;
     		        PreparedStatement pstmt = null;
     		        ResultSet rset = null;
     		        sqlStatement = sqlca.createStatement();
     		        rset = sqlStatement.executeQuery(
     		            "SELECT DISTINCT NODEID, NODEDATA "
     		            + "FROM " + tableName + " WHERE "
     		            + "nodedata IS NOT NULL AND "
     		            + "UPPER(nodedata) = LOWER(nodedata)");
     		        int count = 0;
     		        while (rset.next()) {
     		            String nodeid = rset.getString(1);
     		            String nodedata = rset.getString(2);
     		            try {
     		                if (!nodedata.trim().equals("")) {
     		                	boolean skip = false;
     		                	SimpleDateFormat sdf = null;
     		            		if (nodedata.length() == 10) {
     		            			sdf = new SimpleDateFormat("yyyy-MM-dd");
     		            		} else if (nodedata.length() == 19) {
     		            			sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
     		            		} else {
     		            			skip = true;
+    		            		}
     		            		if (!skip) {
     			            		Date dataDateValue = sdf.parse(nodedata);
     			                    Timestamp dataTimestamp = new Timestamp(dataDateValue.getTime());
     			                    pstmt = sqlca.prepareStatement(
     			                        "update " + tableName +
     			                        " set nodedatadate = ?" +
     			                        " where nodeid = " + nodeid);
     			                    pstmt.setTimestamp(1, dataTimestamp);
     			                    pstmt.execute();
     			                    pstmt.close();
     			                    count++;
     			                    if (count%5 == 0) {
     			                        System.out.println(count + "...");
+    			                    }
+    		            		}
+    		                }
     		            } catch (ParseException pe) {
     		            	// do nothing, was not a date
+    		            }
+    		        }
     		        System.out.println("Table: " + tableName + " complete. " + count + " values converted.");
     		        rset.close();
     		        sqlStatement.close();
+    	        }
     	        sqlca.close();
         	} catch (Exception e) {
         		e.printStackTrace();
                 throw new AdminException("Error upgrading system to 1.10.0: " + e.getMessage());
+            }
         	return true;
+        }
         public static void main(String [] ags){
             try {
             	// set up the properties based on the test/deployed configuration of the workspace
             	SortedProperties testProperties =
     				new SortedProperties("test/test.properties");
     			testProperties.load();
     			String metacatContextDir = testProperties.getProperty("metacat.contextDir");
     			PropertyService.getInstance(metacatContextDir + "/WEB-INF");
     			// now run it
                 Upgrade1_10_0 upgrader = new Upgrade1_10_0();
                 upgrader.upgrade();
             } catch (Exception ex) {
                 System.out.println("Exception:" + ex.getMessage());
                 ex.printStackTrace();
+            }
+        }
+    }

     package edu.ucsb.nceas.metacat.admin.upgrade;
     /**
      *  '$RCSfile$'
      *    Purpose: A Class for upgrading the database to version 1.5
      *  Copyright: 2000 Regents of the University of California and the
      *             National Center for Ecological Analysis and Synthesis
      *    Authors: Saurabh Garg
+     *
      *   '$Author$'
      *     '$Date$'
      * '$Revision$'
+     *
      * 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
      */
     import java.sql.Connection;
     import java.sql.Driver;
     import java.sql.ResultSet;
     import java.sql.PreparedStatement;
     import java.sql.Statement;
     import java.sql.DriverManager;
     import edu.ucsb.nceas.metacat.admin.AdminException;
     import edu.ucsb.nceas.metacat.properties.PropertyService;
     public class Upgrade1_5_0 implements UpgradeUtilityInterface {
         private String driver = null;
         private String database = null;
         private String url = null;
         private String user = null;
         private String password = null;
         public boolean upgrade() throws AdminException {
         	try {
     	    	// get the properties
     			driver = PropertyService.getProperty("database.driver");
     			database = PropertyService.getProperty("database.type");
     		    url = PropertyService.getProperty("database.connectionURI");
     		    user = PropertyService.getProperty("database.user");
     		    password = PropertyService.getProperty("database.password");
     		    // only do oracle
     		    if (!database.equals("oracle")) {
     		    	return true;
+    		    }
     	        // Create a JDBC connection to the database
     	    	Connection sqlca = null;
     	        Driver d = (Driver) Class.forName(driver).newInstance();
     	        DriverManager.registerDriver(d);
     	        sqlca = DriverManager.getConnection(url, user, password);
     	        Statement sqlStatement = null;
     	        PreparedStatement pstmt = null;
     	        ResultSet rset = null;
     	        // Delete old nodedatanumerical column from xml_nodes if one exsists
     	        try {
     	            System.out.println(
     	                "Deleting old nodedatanumerical column from xml_nodes...");
     	            pstmt = sqlca.prepareStatement(
     	                "ALTER TABLE xml_nodes DROP COLUMN nodedatanumerical");
     	            pstmt.execute();
     	            pstmt.close();
     	            System.out.println("Done.");
     	        } catch (Exception e) {
     	            System.out.println(" column not found.");
+    	        }
     	        // Create nodedatanumerical column in xml_nodes
     	        System.out.println(
     	            "Creating new nodedatanumerical column in xml_nodes...");
     	        if (database.equals("oracle")) {
     	            pstmt = sqlca.prepareStatement(
     	                "ALTER TABLE xml_nodes ADD nodedatanumerical NUMBER");
+    	        }
     	        else {
     	            pstmt = sqlca.prepareStatement(
     	                "ALTER TABLE xml_nodes ADD nodedatanumerical FLOAT8");
+    	        }
     	        pstmt.execute();
     	        pstmt.close();
     	        System.out.println("Done.");
     	        // Copy numerical values from nodedata to
     	        // nodedatanumerical column in xml_nodes
     	        System.out.println(
     	            "Please be patient as the next upgrade step can be extremely time consuming.");
     	        System.out.println(
     	            "Copy numerical values from nodedata to nodedatanumerical "
     	            + "in xml_nodes...");
     	        sqlStatement = sqlca.createStatement();
     	        rset = sqlStatement.executeQuery(
     	            "SELECT DISTINCT NODEID, NODEDATA "
     	            + "FROM xml_nodes WHERE "
     	            + "nodedata IS NOT NULL AND "
     	            + "UPPER(nodedata) = LOWER(nodedata)");
     	        int count = 0;
     	        while (rset.next()) {
     	            String nodeid = rset.getString(1);
     	            String nodedata = rset.getString(2);
     	            try {
     	                if (!nodedata.trim().equals("")) {
     	                    double s = Double.parseDouble(nodedata);
     	                    pstmt = sqlca.prepareStatement(
     	                        "update xml_nodes set nodedatanumerical = " + s +
     	                        " where nodeid=" + nodeid);
     	                    pstmt.execute();
     	                    pstmt.close();
     	                    count++;
     	                    if (count%5 == 0) {
     	                        System.out.println(count + "...");
+    	                    }
+    	                }
     	            } catch (NumberFormatException nfe) {
     	            } catch (Exception e) {
     	                System.out.println("Exception:" + e.getMessage());
+    	            }
+    	        }
     	        System.out.println("\nDone. " + count + " values copied.");
     	        rset.close();
     	        sqlStatement.close();
     	        sqlca.close();
         	} catch (Exception e) {
                 throw new AdminException("Error upgrading system to 1.5: " + e.getMessage());
+            }
         	return true;
+        }
         public static void main(String [] ags){
             try {
                 Upgrade1_5_0 upgrader = new Upgrade1_5_0();
                 upgrader.upgrade();
             } catch (Exception ex) {
                 System.out.println("Exception:" + ex.getMessage());
+            }
+        }
+    }

     package edu.ucsb.nceas.metacat.admin.upgrade;
     import edu.ucsb.nceas.metacat.admin.AdminException;
     public interface UpgradeUtilityInterface {
     	public boolean upgrade() throws AdminException;
+    }

     import javax.servlet.http.HttpSession;
     import edu.ucsb.nceas.metacat.MetacatVersion;
     import edu.ucsb.nceas.metacat.admin.upgrade.UpgradeUtilityInterface;
     import edu.ucsb.nceas.metacat.database.DBConnection;
     import edu.ucsb.nceas.metacat.database.DBConnectionPool;
     import edu.ucsb.nceas.metacat.database.DBVersion;
-...
     		return updateScriptList;
+    	}
     	public Vector<String> getUpdateClasses() throws AdminException {
     		Vector<String> updateClassList = new Vector<String>();
     		MetacatVersion metaCatVersion = null;
     		// get the location of sql scripts
     		try {
     			metaCatVersion = SystemUtil.getMetacatVersion();
     		} catch (PropertyNotFoundException pnfe) {
     			throw new AdminException("DBAdmin.getUpdateScripts - Could not get property while trying "
     					+ "to retrieve update utilities: " + pnfe.getMessage());
+    		}
     		// if either of these is null, we don't want to do anything.  Just
     		// return an empty list.
     		if (metaCatVersion == null || databaseVersion == null) {
     			return updateClassList;
+    		}
     		// go through all the versions that the the software went through and
     		// figure out which ones need to be applied to the database
     		for (DBVersion nextVersion : versionSet) {
     			// add every update script that is > than the db version
     			// but <= to the metacat version to the update list.
     			if (nextVersion.compareTo(databaseVersion) > 0
     					&& nextVersion.compareTo(metaCatVersion) <= 0) {
     				String key = "database.upgradeUtility." + nextVersion.getVersionString();
     				String className = null;
     				try {
     					className = PropertyService.getProperty(key);
     				} catch (PropertyNotFoundException pnfe) {
     					// there probably isn't a utility needed for this version
     					logMetacat.warn("No utility defined for version: " + key);
     					continue;
+    				}
     				updateClassList.add(className);
+    			}
+    		}
     		// this should now hold all the script names that need to be run
     		// to bring the database up to date with this version of metacat
     		return updateClassList;
+    	}
     	/**
     	 * Iterates through the list of scripts that need to be run to upgrade
     	 * the database and calls runSQLFile on each.
-...
     			for (String updateScript : updateScriptList) {
     				runSQLFile(updateScript);
+    			}
     			// get the classes we need to execute in order to bring DB to current version
     			Vector<String> updateClassList = getUpdateClasses();
     			for (String className : updateClassList) {
     				UpgradeUtilityInterface utility = null;
     				try {
     					utility = (UpgradeUtilityInterface) Class.forName(className).newInstance();
     					utility.upgrade();
     				} catch (Exception e) {
     					throw new AdminException("DBAdmin.upgradeDatabase - error getting utility class: "
     							+ className + ". Error message: "
     							+ e.getMessage());
+    				}
+    			}
     			// update the db version to be the metacat version
     			databaseVersion = new DBVersion(SystemUtil.getMetacatVersion().getVersionString());

     package edu.ucsb.nceas.metacat;
     import java.sql.Timestamp;
     import org.apache.log4j.Logger;
     /**
-...
       private String _nodetype = null;
       private String _nodedata = null;
       private float _nodedatanumerical = -1;
       private Timestamp _nodedatadate = null;
       private Logger logMetacat = Logger.getLogger(NodeRecord.class);
       /**
-...
+      }
       public NodeRecord(long nodeid, long parentnodeid, long nodeindex, String nodetype,
     			String nodename, String nodeprefix, String nodedata, float nodedatanumerical) {
     			String nodename, String nodeprefix, String nodedata, float nodedatanumerical, Timestamp nodedatadate) {
     		setNodeId(nodeid);
     		setParentNodeId(parentnodeid);
     		setNodeIndex(nodeindex);
-...
     		setNodeType(nodetype);
     		setNodeData(nodedata);
     		setNodeDataNumerical(nodedatanumerical);
     		setNodeDataDate(nodedatadate);
+    	}
       /** Get functions */
-...
         return _nodedatanumerical;
+      }
       public Timestamp getNodeDataDate()
+      {
         return _nodedatadate;
+      }
       /** Setter methods **/
       /**
-...
         _nodedatanumerical = datanumerical;
+      }
       public void setNodeDataDate(Timestamp datadate){
     	    _nodedatadate = datadate;
+    	  }
       /** Method compare two records */
       public boolean contentEquals(NodeRecord record)
+      {

       [docid]		[varchar] (250) NULL ,
       [date_created]	[datetime] NULL ,
       [date_updated]	[datetime] NULL,
       [nodedatanumerical]   [float] NULL
       [nodedatanumerical]   [float] NULL,
       [nodedatadate]   [datetime] NULL
     ) ON [PRIMARY]
     GO

Project

General

Profile

Metacat

Revision 6012

Added by ben leinfelder almost 14 years ago