Revision 6953
Added by ben leinfelder almost 13 years ago
| src/edu/ucsb/nceas/metacat/admin/upgrade/UpgradeNodeDataDatetime.java | ||
|---|---|---|
| 35 | 35 |
import java.sql.DriverManager; |
| 36 | 36 |
import java.sql.Timestamp; |
| 37 | 37 |
import java.util.Calendar; |
| 38 |
import java.util.Map; |
|
| 39 |
import java.util.TreeMap; |
|
| 38 | 40 |
|
| 39 | 41 |
import javax.xml.bind.DatatypeConverter; |
| 40 | 42 |
|
| ... | ... | |
| 63 | 65 |
return success; |
| 64 | 66 |
} |
| 65 | 67 |
|
| 66 |
private boolean upgradeSingleTable(String tableName) throws AdminException {
|
|
| 68 |
private Connection getConnection() throws AdminException {
|
|
| 69 |
|
|
| 70 |
Connection conn = null; |
|
| 67 | 71 |
try {
|
| 68 |
// get the properties
|
|
| 69 |
driver = PropertyService.getProperty("database.driver");
|
|
| 70 |
url = PropertyService.getProperty("database.connectionURI");
|
|
| 71 |
user = PropertyService.getProperty("database.user");
|
|
| 72 |
password = PropertyService.getProperty("database.password");
|
|
| 73 |
|
|
| 72 |
// get the properties
|
|
| 73 |
driver = PropertyService.getProperty("database.driver");
|
|
| 74 |
url = PropertyService.getProperty("database.connectionURI");
|
|
| 75 |
user = PropertyService.getProperty("database.user");
|
|
| 76 |
password = PropertyService.getProperty("database.password");
|
|
| 77 |
|
|
| 74 | 78 |
// Create a JDBC connection to the database |
| 75 |
Connection sqlca = null; |
|
| 76 | 79 |
Driver d = (Driver) Class.forName(driver).newInstance(); |
| 77 | 80 |
DriverManager.registerDriver(d); |
| 78 |
sqlca = DriverManager.getConnection(url, user, password); |
|
| 79 |
|
|
| 81 |
conn = DriverManager.getConnection(url, user, password); |
|
| 82 |
} catch (Exception e) {
|
|
| 83 |
String msg = "Could not get connection: " + e.getMessage(); |
|
| 84 |
log.error(msg); |
|
| 85 |
AdminException ae = new AdminException(msg); |
|
| 86 |
ae.initCause(e); |
|
| 87 |
throw ae; |
|
| 88 |
} |
|
| 89 |
return conn; |
|
| 90 |
|
|
| 91 |
} |
|
| 92 |
|
|
| 93 |
private Map<String, Timestamp> getDataValues(String tableName) throws AdminException {
|
|
| 94 |
|
|
| 95 |
Map<String, Timestamp> dataValues = new TreeMap<String, Timestamp>(); |
|
| 96 |
|
|
| 97 |
try {
|
|
| 98 |
|
|
| 99 |
Connection conn = getConnection(); |
|
| 80 | 100 |
|
| 81 | 101 |
Statement sqlStatement = null; |
| 82 |
PreparedStatement pstmt = null; |
|
| 83 | 102 |
ResultSet rset = null; |
| 84 | 103 |
String nodeid = null; |
| 85 | 104 |
String nodedata = null; |
| 86 | 105 |
int count = 0; |
| 87 | 106 |
|
| 88 |
// select the nodes that are not numeric and not null |
|
| 89 |
sqlStatement = sqlca.createStatement(); |
|
| 90 |
rset = sqlStatement.executeQuery( |
|
| 91 |
"SELECT DISTINCT NODEID, NODEDATA " |
|
| 92 |
+ "FROM " + tableName + " WHERE " |
|
| 93 |
+ "nodedata IS NOT NULL " |
|
| 94 |
//+ "AND nodedatanumerical IS NULL" |
|
| 95 |
); |
|
| 96 |
|
|
| 97 |
count = 0; |
|
| 98 |
while (rset.next()) {
|
|
| 99 |
|
|
| 100 |
nodeid = rset.getString(1); |
|
| 101 |
nodedata = rset.getString(2); |
|
| 102 |
|
|
| 103 |
try {
|
|
| 104 |
if (!nodedata.trim().equals("")) {
|
|
| 105 |
|
|
| 106 |
|
|
| 107 |
Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata); |
|
| 108 |
Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis()); |
|
| 109 |
|
|
| 110 |
pstmt = sqlca.prepareStatement( |
|
| 111 |
"update " + tableName + |
|
| 112 |
" set nodedatadate = ?" + |
|
| 113 |
" where nodeid = " + nodeid); |
|
| 114 |
pstmt.setTimestamp(1, dataTimestamp); |
|
| 115 |
pstmt.execute(); |
|
| 116 |
//pstmt.close(); |
|
| 107 |
// select the nodes that are not numeric and not null |
|
| 108 |
sqlStatement = conn .createStatement(); |
|
| 109 |
rset = sqlStatement.executeQuery( |
|
| 110 |
"SELECT DISTINCT NODEID, NODEDATA " |
|
| 111 |
+ "FROM " + tableName + " WHERE " |
|
| 112 |
+ "nodedata IS NOT NULL " |
|
| 113 |
//+ "AND nodedatanumerical IS NULL" |
|
| 114 |
); |
|
| 115 |
|
|
| 116 |
count = 0; |
|
| 117 |
while (rset.next()) {
|
|
| 118 |
|
|
| 119 |
nodeid = rset.getString(1); |
|
| 120 |
nodedata = rset.getString(2); |
|
| 121 |
|
|
| 122 |
if (!nodedata.trim().equals("")) {
|
|
| 123 |
try {
|
|
| 124 |
Calendar dataDateValue = DatatypeConverter.parseDateTime(nodedata); |
|
| 125 |
Timestamp dataTimestamp = new Timestamp(dataDateValue.getTimeInMillis()); |
|
| 126 |
dataValues.put(nodeid, dataTimestamp); |
|
| 127 |
} catch (Exception e) {
|
|
| 128 |
// probably not a valid date |
|
| 129 |
} |
|
| 130 |
} |
|
| 131 |
|
|
| 132 |
log.debug("parsed: " + nodedata);
|
|
| 133 |
count++; |
|
| 134 |
if (count%5 == 0) {
|
|
| 135 |
log.debug(count + "..."); |
|
| 136 |
} |
|
| 137 |
} |
|
| 138 |
log.warn("Table: " + tableName + " complete. " + count + " values converted.");
|
|
| 139 |
rset.close(); |
|
| 140 |
sqlStatement.close(); |
|
| 141 |
conn.close(); |
|
| 142 |
} catch (Exception e) {
|
|
| 143 |
String msg = "Error parsing date time node data: " + e.getMessage(); |
|
| 144 |
log.error(msg, e); |
|
| 145 |
throw new AdminException(msg); |
|
| 146 |
} |
|
| 147 |
return dataValues; |
|
| 148 |
} |
|
| 149 |
|
|
| 150 |
private boolean upgradeSingleTable(String tableName) throws AdminException {
|
|
| 151 |
|
|
| 152 |
Map<String, Timestamp> dataValues = getDataValues(tableName); |
|
| 153 |
|
|
| 154 |
Connection conn = getConnection(); |
|
| 155 |
|
|
| 156 |
for (String nodeid: dataValues.keySet()) {
|
|
| 157 |
Timestamp dataTimestamp = dataValues.get(nodeid); |
|
| 158 |
upgradeSingleValue(tableName, nodeid, dataTimestamp , conn); |
|
| 159 |
} |
|
| 160 |
|
|
| 161 |
if (conn != null) {
|
|
| 162 |
try {
|
|
| 163 |
conn.close(); |
|
| 164 |
} catch (SQLException e) {
|
|
| 165 |
throw new AdminException("Error closing connection: " + e.getMessage());
|
|
| 166 |
} |
|
| 167 |
} |
|
| 168 |
|
|
| 169 |
return true; |
|
| 170 |
|
|
| 171 |
} |
|
| 172 |
|
|
| 173 |
private boolean upgradeSingleValue(String tableName, String nodeid, Timestamp dataTimestamp, Connection conn) throws AdminException {
|
|
| 174 |
try {
|
|
| 175 |
|
|
| 176 |
PreparedStatement pstmt = null; |
|
| 117 | 177 |
|
| 118 |
log.debug("processed: " + nodedata);
|
|
| 119 |
count++;
|
|
| 120 |
if (count%5 == 0) {
|
|
| 121 |
log.debug(count + "...");
|
|
| 122 |
}
|
|
| 123 |
|
|
| 124 |
}
|
|
| 125 |
} catch (Exception e) {
|
|
| 126 |
// do nothing, was not a valid date
|
|
| 127 |
//e.printStackTrace(); |
|
| 128 |
} finally {
|
|
| 129 |
if (pstmt != null) {
|
|
| 130 |
pstmt.close();
|
|
| 131 |
}
|
|
| 132 |
}
|
|
| 133 |
}
|
|
| 134 |
log.warn("Table: " + tableName + " complete. " + count + " values converted.");
|
|
| 135 |
|
|
| 136 |
rset.close();
|
|
| 137 |
sqlStatement.close();
|
|
| 138 |
sqlca.close(); |
|
| 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 |
|
|
| 139 | 199 |
} catch (Exception e) {
|
| 140 | 200 |
String msg = "Error updating date time node data: " + e.getMessage(); |
| 141 | 201 |
log.error(msg, e); |
Also available in: Unified diff
look up nodedata values first, then update each one - trying to avoid out of memory exception.