Metacat

/**

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

 *    Release: @release@

 *

 *   '$Author: sgarg $'

 *     '$Date: 2004-12-30 14:33:01 -0800 (Thu, 30 Dec 2004) $'

 * '$Revision: 2355 $'

 *

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

public class upgrade_db_to_1_5{

    public static void main(String [] ags) throws IOException{

        Connection sqlca  = null;

        Statement sqlStatement = null;

        PreparedStatement pstmt = null;

        ResultSet rset = null;

        String database = "@database@";

        String url = "@jdbc-connect@";

        String user = "@user@";

        String password = "@password@";

        try{

            // Create a JDBC connection to the database

            DriverManager.registerDriver(new @dbDriver@());

            sqlca= DriverManager.getConnection (url, user, password);

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

                e.printStackTrace();

                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(

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

                    }

                } catch (NumberFormatException nfe) {

                } catch (Exception e) {

                    System.out.println("Exception:" + e.getMessage());

                }

            }

            System.out.println("Done. " + count + " values copied.");

            rset.close();

            sqlStatement.close();

            sqlca.close();

        }

        catch (SQLException ex)

        {

            System.out.println("SQLException:" + ex.getMessage());

            ex.printStackTrace();

        }

    }

}