Revision 6954
Added by ben leinfelder almost 13 years ago
| src/edu/ucsb/nceas/metacat/admin/upgrade/UpgradeNodeDataDatetime.java | ||
|---|---|---|
| 59 | 59 |
public boolean upgrade() throws AdminException {
|
| 60 | 60 |
String[] tablesToUpdate = {"xml_nodes", "xml_nodes_revisions", "xml_path_index"};
|
| 61 | 61 |
boolean success = true; |
| 62 |
for (String tableName: tablesToUpdate) {
|
|
| 63 |
success = success && upgradeSingleTable(tableName); |
|
| 64 |
} |
|
| 65 |
return success; |
|
| 62 |
// update each table that needs updating |
|
| 63 |
try {
|
|
| 64 |
for (String tableName: tablesToUpdate) {
|
|
| 65 |
success = success && upgradeSingleTable(tableName); |
|
| 66 |
} |
|
| 67 |
} catch (Exception e) {
|
|
| 68 |
AdminException ae = new AdminException("Error upgrading tables: " + e.getMessage());
|
|
| 69 |
ae.initCause(e); |
|
| 70 |
throw ae; |
|
| 71 |
} |
|
| 72 |
|
|
| 73 |
return success; |
|
| 66 | 74 |
} |
| 67 | 75 |
|
| 76 |
/** |
|
| 77 |
* Utility method to retrieve a connection |
|
| 78 |
* @return db connection based on metacat configuration |
|
| 79 |
* @throws AdminException |
|
| 80 |
*/ |
|
| 68 | 81 |
private Connection getConnection() throws AdminException {
|
| 69 | 82 |
|
| 70 | 83 |
Connection conn = null; |
| ... | ... | |
| 90 | 103 |
|
| 91 | 104 |
} |
| 92 | 105 |
|
| 106 |
/** |
|
| 107 |
* Look up the node data values that need converting to datetime fields |
|
| 108 |
* @param tableName |
|
| 109 |
* @return |
|
| 110 |
* @throws AdminException |
|
| 111 |
*/ |
|
| 93 | 112 |
private Map<String, Timestamp> getDataValues(String tableName) throws AdminException {
|
| 94 | 113 |
|
| 95 | 114 |
Map<String, Timestamp> dataValues = new TreeMap<String, Timestamp>(); |
| ... | ... | |
| 104 | 123 |
String nodedata = null; |
| 105 | 124 |
int count = 0; |
| 106 | 125 |
|
| 107 |
// select the nodes that are not numeric and not null
|
|
| 126 |
// select the nodes that are not null |
|
| 108 | 127 |
sqlStatement = conn .createStatement(); |
| 109 | 128 |
rset = sqlStatement.executeQuery( |
| 110 | 129 |
"SELECT DISTINCT NODEID, NODEDATA " |
| 111 | 130 |
+ "FROM " + tableName + " WHERE " |
| 112 | 131 |
+ "nodedata IS NOT NULL " |
| 113 |
//+ "AND nodedatanumerical IS NULL" |
|
| 114 | 132 |
); |
| 115 | 133 |
|
| 116 | 134 |
count = 0; |
| ... | ... | |
| 124 | 142 |
Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata); |
| 125 | 143 |
Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis()); |
| 126 | 144 |
dataValues.put(nodeid, dataTimestamp); |
| 145 |
log.debug("parsed: " + nodedata);
|
|
| 146 |
count++; |
|
| 147 |
if (count%5 == 0) {
|
|
| 148 |
log.debug(count + "..."); |
|
| 149 |
} |
|
| 127 | 150 |
} catch (Exception e) {
|
| 128 | 151 |
// probably not a valid date |
| 152 |
continue; |
|
| 129 | 153 |
} |
| 130 |
} |
|
| 131 |
|
|
| 132 |
log.debug("parsed: " + nodedata);
|
|
| 133 |
count++; |
|
| 134 |
if (count%5 == 0) {
|
|
| 135 |
log.debug(count + "..."); |
|
| 136 |
} |
|
| 154 |
} |
|
| 137 | 155 |
} |
| 138 |
log.warn("Table: " + tableName + " complete. " + count + " values converted.");
|
|
| 156 |
log.warn("Converting table: " + tableName + " with " + count + " values found.");
|
|
| 139 | 157 |
rset.close(); |
| 140 | 158 |
sqlStatement.close(); |
| 141 | 159 |
conn.close(); |
| ... | ... | |
| 147 | 165 |
return dataValues; |
| 148 | 166 |
} |
| 149 | 167 |
|
| 150 |
private boolean upgradeSingleTable(String tableName) throws AdminException {
|
|
| 168 |
/** |
|
| 169 |
* Upgrade the nodedata fields that have datetime values in them |
|
| 170 |
* @param tableName the table that should be upgraded |
|
| 171 |
* @return |
|
| 172 |
* @throws Exception |
|
| 173 |
*/ |
|
| 174 |
private boolean upgradeSingleTable(String tableName) throws Exception {
|
|
| 151 | 175 |
|
| 152 | 176 |
Map<String, Timestamp> dataValues = getDataValues(tableName); |
| 153 | 177 |
|
| 178 |
// get the connection |
|
| 154 | 179 |
Connection conn = getConnection(); |
| 155 | 180 |
|
| 181 |
// prepare a reusable statement |
|
| 182 |
PreparedStatement pstmt = conn.prepareStatement( |
|
| 183 |
"update " + tableName + |
|
| 184 |
" set nodedatadate = ? " + |
|
| 185 |
" where nodeid = ? "); |
|
| 186 |
|
|
| 187 |
// update each value |
|
| 156 | 188 |
for (String nodeid: dataValues.keySet()) {
|
| 157 |
Timestamp dataTimestamp = dataValues.get(nodeid); |
|
| 158 |
upgradeSingleValue(tableName, nodeid, dataTimestamp , conn); |
|
| 189 |
Timestamp dataTimestamp = dataValues.get(nodeid); |
|
| 190 |
pstmt.setTimestamp(1, dataTimestamp); |
|
| 191 |
pstmt.setInt(2, Integer.valueOf(nodeid)); |
|
| 192 |
log.debug("Updating values: " + pstmt.toString());
|
|
| 193 |
try {
|
|
| 194 |
pstmt.execute(); |
|
| 195 |
} catch (SQLException sqle) {
|
|
| 196 |
// probably too large a date |
|
| 197 |
log.warn("DB error when updating date value, likely can ignore as some errors are to be expected. " + sqle.getMessage());
|
|
| 198 |
continue; |
|
| 199 |
} |
|
| 159 | 200 |
} |
| 160 | 201 |
|
| 202 |
// clean up |
|
| 161 | 203 |
if (conn != null) {
|
| 162 |
try {
|
|
| 163 |
conn.close(); |
|
| 164 |
} catch (SQLException e) {
|
|
| 165 |
throw new AdminException("Error closing connection: " + e.getMessage());
|
|
| 166 |
} |
|
| 204 |
conn.close(); |
|
| 167 | 205 |
} |
| 168 | 206 |
|
| 169 | 207 |
return true; |
| 170 | 208 |
|
| 171 | 209 |
} |
| 172 |
|
|
| 173 |
private boolean upgradeSingleValue(String tableName, String nodeid, Timestamp dataTimestamp, Connection conn) throws AdminException {
|
|
| 174 |
try {
|
|
| 175 |
|
|
| 176 |
PreparedStatement pstmt = null; |
|
| 177 | 210 |
|
| 178 |
try {
|
|
| 179 |
pstmt = conn.prepareStatement( |
|
| 180 |
"update " + tableName + |
|
| 181 |
" set nodedatadate = ? " + |
|
| 182 |
" where nodeid = ? "); |
|
| 183 |
pstmt.setTimestamp(1, dataTimestamp); |
|
| 184 |
pstmt.setString(2, nodeid); |
|
| 185 |
|
|
| 186 |
pstmt.execute(); |
|
| 187 |
|
|
| 188 |
log.debug("processed nodedata: " + dataTimestamp);
|
|
| 189 |
|
|
| 190 |
} catch (Exception e) {
|
|
| 191 |
// do nothing, was not a valid date |
|
| 192 |
//e.printStackTrace(); |
|
| 193 |
} finally {
|
|
| 194 |
if (pstmt != null) {
|
|
| 195 |
pstmt.close(); |
|
| 196 |
} |
|
| 197 |
} |
|
| 198 |
|
|
| 199 |
} catch (Exception e) {
|
|
| 200 |
String msg = "Error updating date time node data: " + e.getMessage(); |
|
| 201 |
log.error(msg, e); |
|
| 202 |
throw new AdminException(msg); |
|
| 203 |
} |
|
| 204 |
return true; |
|
| 205 |
} |
|
| 206 |
|
|
| 207 |
|
|
| 208 | 211 |
public static void main(String [] ags){
|
| 209 | 212 |
|
| 210 | 213 |
try {
|
Also available in: Unified diff
more clean up - reuse prepared statement for data update