Revision 6025
Added by ben leinfelder about 13 years ago
| src/edu/ucsb/nceas/metacat/admin/upgrade/Upgrade1_10_0.java | ||
|---|---|---|
| 30 | 30 |
import java.sql.Driver; |
| 31 | 31 |
import java.sql.ResultSet; |
| 32 | 32 |
import java.sql.PreparedStatement; |
| 33 |
import java.sql.SQLException; |
|
| 33 | 34 |
import java.sql.Statement; |
| 34 | 35 |
import java.sql.DriverManager; |
| 35 | 36 |
import java.sql.Timestamp; |
| ... | ... | |
| 49 | 50 |
private String password = null; |
| 50 | 51 |
|
| 51 | 52 |
public boolean upgrade() throws AdminException {
|
| 53 |
String[] tablesToUpdate = {"xml_nodes", "xml_nodes_revisions", "xml_path_index"};
|
|
| 54 |
boolean success = true; |
|
| 55 |
for (String tableName: tablesToUpdate) {
|
|
| 56 |
success = success && upgradeSingleTable(tableName); |
|
| 57 |
} |
|
| 58 |
return success; |
|
| 59 |
} |
|
| 60 |
|
|
| 61 |
private boolean upgradeSingleTable(String tableName) throws AdminException {
|
|
| 52 | 62 |
try {
|
| 53 | 63 |
// get the properties |
| 54 | 64 |
driver = PropertyService.getProperty("database.driver");
|
| ... | ... | |
| 62 | 72 |
DriverManager.registerDriver(d); |
| 63 | 73 |
sqlca = DriverManager.getConnection(url, user, password); |
| 64 | 74 |
|
| 65 |
// three tables need to be updated here |
|
| 66 |
String[] tablesToUpdate = {"xml_nodes", "xml_nodes_revisions", "xml_path_index"};
|
|
| 67 |
for (String tableName: tablesToUpdate) {
|
|
| 68 |
Statement sqlStatement = null; |
|
| 69 |
PreparedStatement pstmt = null; |
|
| 70 |
ResultSet rset = null; |
|
| 71 |
|
|
| 75 |
|
|
| 76 |
Statement sqlStatement = null; |
|
| 77 |
PreparedStatement pstmt = null; |
|
| 78 |
ResultSet rset = null; |
|
| 79 |
String nodeid = null; |
|
| 80 |
String nodedata = null; |
|
| 81 |
int count = 0; |
|
| 82 |
|
|
| 83 |
// select the nodes that are not numeric and not null |
|
| 72 | 84 |
sqlStatement = sqlca.createStatement(); |
| 73 | 85 |
rset = sqlStatement.executeQuery( |
| 74 | 86 |
"SELECT DISTINCT NODEID, NODEDATA " |
| 75 | 87 |
+ "FROM " + tableName + " WHERE " |
| 76 |
+ "nodedata IS NOT NULL AND " |
|
| 77 |
+ "UPPER(nodedata) = LOWER(nodedata)"); |
|
| 88 |
+ "nodedata IS NOT NULL " |
|
| 89 |
//+ "AND nodedatanumerical IS NULL" |
|
| 90 |
); |
|
| 78 | 91 |
|
| 79 |
int count = 0;
|
|
| 92 |
count = 0; |
|
| 80 | 93 |
while (rset.next()) {
|
| 81 | 94 |
|
| 82 |
String nodeid = rset.getString(1);
|
|
| 83 |
String nodedata = rset.getString(2);
|
|
| 95 |
nodeid = rset.getString(1); |
|
| 96 |
nodedata = rset.getString(2); |
|
| 84 | 97 |
|
| 85 | 98 |
try {
|
| 86 | 99 |
if (!nodedata.trim().equals("")) {
|
| 87 | 100 |
|
| 88 |
System.out.println(nodedata); |
|
| 89 | 101 |
|
| 90 | 102 |
Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata); |
| 91 | 103 |
Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis()); |
| ... | ... | |
| 97 | 109 |
pstmt.setTimestamp(1, dataTimestamp); |
| 98 | 110 |
pstmt.execute(); |
| 99 | 111 |
pstmt.close(); |
| 100 |
|
|
| 112 |
|
|
| 113 |
System.out.println("processed: " + nodedata);
|
|
| 101 | 114 |
count++; |
| 102 | 115 |
if (count%5 == 0) {
|
| 103 | 116 |
System.out.println(count + "..."); |
| 104 | 117 |
} |
| 105 | 118 |
|
| 106 | 119 |
} |
| 107 |
} catch (Exception e) {
|
|
| 120 |
} catch (SQLException sqle) {
|
|
| 121 |
pstmt.close(); |
|
| 122 |
} catch (Exception e) {
|
|
| 108 | 123 |
// do nothing, was not a valid date |
| 109 |
e.printStackTrace(); |
|
| 124 |
//e.printStackTrace();
|
|
| 110 | 125 |
} |
| 111 | 126 |
} |
| 112 | 127 |
System.out.println("Table: " + tableName + " complete. " + count + " values converted.");
|
| 113 | 128 |
|
| 114 | 129 |
rset.close(); |
| 115 | 130 |
sqlStatement.close(); |
| 116 |
} |
|
| 117 | 131 |
sqlca.close(); |
| 118 | 132 |
} catch (Exception e) {
|
| 119 | 133 |
e.printStackTrace(); |
| ... | ... | |
| 134 | 148 |
PropertyService.getInstance(metacatContextDir + "/WEB-INF"); |
| 135 | 149 |
// now run it |
| 136 | 150 |
Upgrade1_10_0 upgrader = new Upgrade1_10_0(); |
| 137 |
upgrader.upgrade(); |
|
| 151 |
upgrader.upgrade(); |
|
| 152 |
|
|
| 153 |
// test the converter |
|
| 154 |
//Calendar date = DatatypeConverter.parseDate("2011-03-17");
|
|
| 155 |
//System.out.println("date:" + DatatypeConverter.printDate(date));
|
|
| 138 | 156 |
|
| 139 |
Calendar date = DatatypeConverter.parseDate("2011-03-17");
|
|
| 140 |
System.out.println("date:" + DatatypeConverter.printDate(date));
|
|
| 141 |
|
|
| 142 | 157 |
} catch (Exception ex) {
|
| 143 | 158 |
System.out.println("Exception:" + ex.getMessage());
|
| 144 | 159 |
ex.printStackTrace(); |
Also available in: Unified diff
do each table separately with it's own connection - running into memory issues on dev.nceas running this.