Revision 2378
Added by sgarg over 19 years ago
| upgrade_db_to_1_5.java | ||
|---|---|---|
| 27 | 27 |
|
| 28 | 28 |
|
| 29 | 29 |
import java.io.*; |
| 30 |
import java.sql.*; |
|
| 30 |
import java.sql.Connection; |
|
| 31 |
import java.sql.ResultSet; |
|
| 32 |
import java.sql.PreparedStatement; |
|
| 33 |
import java.sql.Statement; |
|
| 34 |
import java.sql.SQLException; |
|
| 35 |
import java.sql.DriverManager; |
|
| 31 | 36 |
|
| 32 | 37 |
public class upgrade_db_to_1_5{
|
| 33 |
public static void main(String [] ags) throws IOException{
|
|
| 34 |
Connection sqlca = null; |
|
| 38 |
|
|
| 39 |
static String database = "@database@"; |
|
| 40 |
static String url = "@jdbc-connect@"; |
|
| 41 |
static String user = "@user@"; |
|
| 42 |
static String password = "@password@"; |
|
| 43 |
|
|
| 44 |
public static void upgrade_xml_nodes(Connection sqlca) throws SQLException{
|
|
| 35 | 45 |
Statement sqlStatement = null; |
| 36 | 46 |
PreparedStatement pstmt = null; |
| 37 | 47 |
ResultSet rset = null; |
| 38 |
String database = "@database@"; |
|
| 39 |
String url = "@jdbc-connect@"; |
|
| 40 |
String user = "@user@"; |
|
| 41 |
String password = "@password@"; |
|
| 42 | 48 |
|
| 43 |
try{
|
|
| 44 |
// Create a JDBC connection to the database
|
|
| 45 |
DriverManager.registerDriver(new @dbDriver@());
|
|
| 46 |
sqlca= DriverManager.getConnection (url, user, password);
|
|
| 49 |
// Delete old nodedatanumerical column from xml_nodes if one exsists
|
|
| 50 |
try {
|
|
| 51 |
System.out.println(
|
|
| 52 |
"Deleting old nodedatanumerical column from xml_nodes...");
|
|
| 47 | 53 |
|
| 48 |
// Delete old nodedatanumerical column from xml_nodes if one exsists |
|
| 54 |
pstmt = sqlca.prepareStatement( |
|
| 55 |
"ALTER TABLE xml_nodes DROP COLUMN nodedatanumerical"); |
|
| 56 |
pstmt.execute(); |
|
| 57 |
pstmt.close(); |
|
| 58 |
|
|
| 59 |
System.out.println("Done.");
|
|
| 60 |
} catch (Exception e) {
|
|
| 61 |
System.out.println(" column not found.");
|
|
| 62 |
} |
|
| 63 |
|
|
| 64 |
// Create nodedatanumerical column in xml_nodes |
|
| 65 |
System.out.println( |
|
| 66 |
"Creating new nodedatanumerical column in xml_nodes..."); |
|
| 67 |
|
|
| 68 |
if (database.equals("oracle")) {
|
|
| 69 |
pstmt = sqlca.prepareStatement( |
|
| 70 |
"ALTER TABLE xml_nodes ADD nodedatanumerical NUMBER"); |
|
| 71 |
} |
|
| 72 |
else {
|
|
| 73 |
pstmt = sqlca.prepareStatement( |
|
| 74 |
"ALTER TABLE xml_nodes ADD nodedatanumerical FLOAT8"); |
|
| 75 |
} |
|
| 76 |
pstmt.execute(); |
|
| 77 |
pstmt.close(); |
|
| 78 |
|
|
| 79 |
System.out.println("Done.");
|
|
| 80 |
|
|
| 81 |
// Copy numerical values from nodedata to |
|
| 82 |
// nodedatanumerical column in xml_nodes |
|
| 83 |
System.out.println( |
|
| 84 |
"Copy numerical values from nodedata to nodedatanumerical " |
|
| 85 |
+ "in xml_nodes..."); |
|
| 86 |
|
|
| 87 |
sqlStatement = sqlca.createStatement(); |
|
| 88 |
rset = sqlStatement.executeQuery( |
|
| 89 |
"SELECT DISTINCT NODEID, NODEDATA " |
|
| 90 |
+ "FROM xml_nodes WHERE " |
|
| 91 |
+ "nodedata IS NOT NULL AND " |
|
| 92 |
+ "UPPER(nodedata) = LOWER(nodedata)"); |
|
| 93 |
|
|
| 94 |
int count = 0; |
|
| 95 |
while (rset.next()) {
|
|
| 96 |
|
|
| 97 |
String nodeid = rset.getString(1); |
|
| 98 |
String nodedata = rset.getString(2); |
|
| 99 |
|
|
| 49 | 100 |
try {
|
| 50 |
System.out.println(
|
|
| 51 |
"Deleting old nodedatanumerical column from xml_nodes...");
|
|
| 101 |
if (!nodedata.trim().equals("")) {
|
|
| 102 |
double s = Double.parseDouble(nodedata);
|
|
| 52 | 103 |
|
| 53 |
pstmt = sqlca.prepareStatement( |
|
| 54 |
"ALTER TABLE xml_nodes DROP COLUMN nodedatanumerical"); |
|
| 55 |
pstmt.execute(); |
|
| 56 |
pstmt.close(); |
|
| 104 |
pstmt = sqlca.prepareStatement( |
|
| 105 |
"update xml_nodes set nodedatanumerical = " + s + |
|
| 106 |
" where nodeid=" + nodeid); |
|
| 107 |
pstmt.execute(); |
|
| 108 |
pstmt.close(); |
|
| 57 | 109 |
|
| 58 |
System.out.println("Done.");
|
|
| 110 |
count++; |
|
| 111 |
} |
|
| 112 |
} catch (NumberFormatException nfe) {
|
|
| 113 |
|
|
| 114 |
} catch (Exception e) {
|
|
| 115 |
System.out.println("Exception:" + e.getMessage());
|
|
| 59 | 116 |
} |
| 60 |
catch (Exception e) {
|
|
| 61 |
e.printStackTrace(); |
|
| 62 |
System.out.println(" column not found.");
|
|
| 63 |
} |
|
| 117 |
} |
|
| 118 |
System.out.println("Done. " + count + " values copied.");
|
|
| 64 | 119 |
|
| 65 |
// Create nodedatanumerical column in xml_nodes |
|
| 120 |
rset.close(); |
|
| 121 |
sqlStatement.close(); |
|
| 122 |
} |
|
| 123 |
|
|
| 124 |
|
|
| 125 |
public static void upgrade_xml_index(Connection sqlca) throws SQLException{
|
|
| 126 |
Statement sqlStatement, sqlStatement1 = null; |
|
| 127 |
PreparedStatement pstmt = null; |
|
| 128 |
ResultSet rset, rset1 = null; |
|
| 129 |
|
|
| 130 |
// Delete old nodedata column from xml_index if one exsists |
|
| 131 |
try {
|
|
| 66 | 132 |
System.out.println( |
| 67 |
"Creating new nodedatanumerical column in xml_nodes...");
|
|
| 133 |
"Deleting old nodedata column from xml_index...");
|
|
| 68 | 134 |
|
| 69 |
if(database.equals("oracle")){
|
|
| 70 |
pstmt =sqlca.prepareStatement( |
|
| 71 |
"ALTER TABLE xml_nodes ADD nodedatanumerical NUMBER"); |
|
| 72 |
} else {
|
|
| 73 |
pstmt =sqlca.prepareStatement( |
|
| 74 |
"ALTER TABLE xml_nodes ADD nodedatanumerical FLOAT8"); |
|
| 75 |
} |
|
| 135 |
pstmt = sqlca.prepareStatement( |
|
| 136 |
"ALTER TABLE xml_index DROP COLUMN nodedata"); |
|
| 76 | 137 |
pstmt.execute(); |
| 77 | 138 |
pstmt.close(); |
| 78 | 139 |
|
| 79 | 140 |
System.out.println("Done.");
|
| 141 |
} catch (Exception e) {
|
|
| 142 |
System.out.println(" column not found.");
|
|
| 143 |
} |
|
| 80 | 144 |
|
| 81 |
// Copy numerical values from nodedata to |
|
| 82 |
// nodedatanumerical column in xml_nodes |
|
| 83 |
System.out.println( |
|
| 84 |
"Copy numerical values from nodedata to nodedatanumerical " |
|
| 85 |
+ "in xml_nodes..."); |
|
| 145 |
// Create nodedata column in xml_index |
|
| 146 |
System.out.println( |
|
| 147 |
"Creating new nodedata column in xml_index..."); |
|
| 86 | 148 |
|
| 87 |
sqlStatement = sqlca.createStatement(); |
|
| 88 |
rset = sqlStatement.executeQuery( |
|
| 89 |
"SELECT DISTINCT NODEID, NODEDATA " |
|
| 90 |
+ "FROM xml_nodes WHERE " |
|
| 91 |
+ "nodedata IS NOT NULL AND " |
|
| 92 |
+ "UPPER(nodedata) = LOWER(nodedata)"); |
|
| 149 |
if (database.equals("oracle")) {
|
|
| 150 |
pstmt = sqlca.prepareStatement( |
|
| 151 |
"ALTER TABLE xml_index ADD nodedata VARCHAR2(4000)"); |
|
| 152 |
} else {
|
|
| 153 |
pstmt = sqlca.prepareStatement( |
|
| 154 |
"ALTER TABLE xml_index ADD nodedata VARCHAR(4000)"); |
|
| 155 |
} |
|
| 156 |
pstmt.execute(); |
|
| 157 |
pstmt.close(); |
|
| 93 | 158 |
|
| 94 |
int count = 0; |
|
| 95 |
while (rset.next()) {
|
|
| 159 |
System.out.println("Done.");
|
|
| 96 | 160 |
|
| 97 |
String nodeid = rset.getString(1); |
|
| 98 |
String nodedata = rset.getString(2); |
|
| 161 |
// Copy numerical values from nodedata to |
|
| 162 |
// nodedatanumerical column in xml_index |
|
| 163 |
System.out.println( |
|
| 164 |
"Copy nodedata from xml_nodes to nodedata in xml_index..."); |
|
| 99 | 165 |
|
| 100 |
try {
|
|
| 101 |
if(!nodedata.trim().equals("") && !nodedata.trim().equals("NaN")){
|
|
| 102 |
double s = Double.parseDouble(nodedata); |
|
| 166 |
sqlStatement = sqlca.createStatement(); |
|
| 167 |
rset = sqlStatement.executeQuery( |
|
| 168 |
"select docid, nodeid, path from xml_index"); |
|
| 169 |
int j = 0; |
|
| 103 | 170 |
|
| 104 |
pstmt =sqlca.prepareStatement( |
|
| 105 |
"update xml_nodes set nodedatanumerical = " + s + |
|
| 106 |
" where nodeid=" + nodeid); |
|
| 107 |
pstmt.execute(); |
|
| 108 |
pstmt.close(); |
|
| 171 |
while (rset.next()) {
|
|
| 172 |
String docid = rset.getString(1); |
|
| 173 |
String nodeid = rset.getString(2); |
|
| 174 |
String path = rset.getString(3); |
|
| 109 | 175 |
|
| 110 |
count++; |
|
| 176 |
sqlStatement1 = sqlca.createStatement(); |
|
| 177 |
rset1 = sqlStatement1.executeQuery( |
|
| 178 |
"select nodedata from xml_nodes where parentnodeid = " + nodeid |
|
| 179 |
+ " and docid = '" + docid |
|
| 180 |
+ "' and nodetype = 'TEXT'"); |
|
| 181 |
|
|
| 182 |
while (rset1.next()) {
|
|
| 183 |
String nodedata = rset1.getString(1); |
|
| 184 |
if (nodedata != null && !nodedata.trim().equals("")) {
|
|
| 185 |
j++; |
|
| 186 |
if (j % 100 == 0) {
|
|
| 187 |
System.out.println("Number of records modified:" + j);
|
|
| 111 | 188 |
} |
| 112 |
} catch (NumberFormatException nfe) {
|
|
| 113 | 189 |
|
| 114 |
} catch (Exception e) {
|
|
| 115 |
System.out.println("Exception:" + e.getMessage());
|
|
| 190 |
pstmt = sqlca.prepareStatement( |
|
| 191 |
"update xml_index set nodedata = ? where nodeid = " + |
|
| 192 |
nodeid + " and path = ? and docid = ? and nodedata is null"); |
|
| 193 |
pstmt.setString(1, nodedata); |
|
| 194 |
pstmt.setString(2, path); |
|
| 195 |
pstmt.setString(3, docid); |
|
| 196 |
pstmt.execute(); |
|
| 197 |
pstmt.close(); |
|
| 198 |
|
|
| 199 |
continue; |
|
| 116 | 200 |
} |
| 117 | 201 |
} |
| 118 |
System.out.println("Done. " + count + " values copied.");
|
|
| 202 |
rset1.close(); |
|
| 203 |
sqlStatement1.close(); |
|
| 119 | 204 |
|
| 120 |
rset.close(); |
|
| 121 |
sqlStatement.close(); |
|
| 205 |
} |
|
| 206 |
rset.close(); |
|
| 207 |
sqlStatement.close(); |
|
| 122 | 208 |
|
| 209 |
// Create index on xml_index.docid |
|
| 210 |
System.out.println("Creating index on docid column in xml_index...");
|
|
| 211 |
pstmt = sqlca.prepareStatement( |
|
| 212 |
"CREATE INDEX xml_index_idx2 ON xml_index (docid)"); |
|
| 213 |
|
|
| 214 |
pstmt.execute(); |
|
| 215 |
pstmt.close(); |
|
| 216 |
} |
|
| 217 |
|
|
| 218 |
|
|
| 219 |
public static void main(String [] ags){
|
|
| 220 |
|
|
| 221 |
try {
|
|
| 222 |
Connection sqlca = null; |
|
| 223 |
|
|
| 224 |
// Create a JDBC connection to the database |
|
| 225 |
DriverManager.registerDriver(new @dbDriver@()); |
|
| 226 |
sqlca = DriverManager.getConnection(url, user, password); |
|
| 227 |
|
|
| 228 |
// Upgrade the xml_nodes table |
|
| 229 |
upgrade_xml_nodes(sqlca); |
|
| 230 |
|
|
| 231 |
// Upgrade the xml_index table |
|
| 232 |
upgrade_xml_index(sqlca); |
|
| 233 |
|
|
| 234 |
// Close the connection... |
|
| 123 | 235 |
sqlca.close(); |
| 124 |
} |
|
| 125 |
|
|
| 126 |
catch (SQLException ex) |
|
| 127 |
{
|
|
| 236 |
} catch (SQLException ex) {
|
|
| 128 | 237 |
System.out.println("SQLException:" + ex.getMessage());
|
| 129 |
ex.printStackTrace(); |
|
| 130 | 238 |
} |
| 131 | 239 |
} |
| 132 | 240 |
} |
Also available in: Unified diff
Modified code to add nodedata column to xml_index and enter data into it from xml_nodes table