001package co.codewizards.cloudstore.local; 002 003import static java.util.Objects.*; 004 005import java.sql.Connection; 006import java.sql.ResultSet; 007import java.sql.SQLException; 008import java.sql.Statement; 009import java.util.ArrayList; 010import java.util.Collection; 011import java.util.HashSet; 012import java.util.Set; 013 014import org.slf4j.Logger; 015import org.slf4j.LoggerFactory; 016 017import co.codewizards.cloudstore.core.oio.File; 018 019public class RepairDatabase implements Runnable { 020 private static final Logger logger = LoggerFactory.getLogger(RepairDatabase.class); 021 022 private final File localRoot; 023 024 private Connection connection; 025 private Statement statement; 026 027 public RepairDatabase(File localRoot) { 028 this.localRoot = requireNonNull(localRoot, "localRoot"); 029 } 030 031 @Override 032 public void run() { 033 try { 034 JdbcConnectionFactory jdbcConnectionFactory = new JdbcConnectionFactory(localRoot); 035 connection = jdbcConnectionFactory.createConnection(); 036 try { 037 statement = connection.createStatement(); 038 try { 039// testInsert(); 040 executeDerbyCheckTable(); 041 dropForeignKeys(); 042 dropIndices(); 043 executeDerbyCheckTable(); 044 } finally { 045 statement.close(); 046 } 047 } finally { 048 connection.close(); 049 } 050 } catch (SQLException x) { 051 throw new RuntimeException(x); 052 } 053 } 054 055 private void executeDerbyCheckTable() throws SQLException { 056 // http://objectmix.com/apache/646586-derby-db-files-get-corrupted-2.html 057 statement.execute( 058 "SELECT schemaname, tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) " 059 + "FROM sys.sysschemas s, sys.systables t " 060 + "WHERE s.schemaid = t.schemaid"); 061 } 062 063// private void testInsert() throws SQLException { 064// connection.setAutoCommit(false); 065// try { 066// long filechunkpayload_id_oid; 067// long histocryptorepofile_id_oid; 068// int length; 069// long offset; 070// Timestamp changed; 071// Timestamp created; 072// 073// try (ResultSet rs = statement.executeQuery("select * from \"histofilechunk\" order by \"id\"")) { 074// if (! rs.next()) { 075// logger.warn("Table \"histofilechunk\" is empty! Cannot obtain test data!"); 076// return; 077// } 078// 079// filechunkpayload_id_oid = rs.getLong("filechunkpayload_id_oid"); 080// histocryptorepofile_id_oid = rs.getLong("histocryptorepofile_id_oid"); 081// length = rs.getInt("length"); 082// offset = rs.getLong("offset"); 083// changed = rs.getTimestamp("changed"); 084// created = rs.getTimestamp("created"); 085// } 086// 087// ++offset; // there is a unique key => must change the offset! 088// 089// logger.info("testInsert: filechunkpayload_id_oid={}, histocryptorepofile_id_oid={}, length={}, offset={}, changed={}, created={}", 090// filechunkpayload_id_oid, histocryptorepofile_id_oid, length, offset, changed, created); 091// 092// try (PreparedStatement ps = connection.prepareStatement( 093// "INSERT INTO \"histofilechunk\"" 094// + " (\"filechunkpayload_id_oid\",\"histocryptorepofile_id_oid\",\"length\",\"offset\",\"changed\",\"created\")" 095// + " VALUES (?,?,?,?,?,?)")) { 096// 097// int paramIdx = 0; 098// ps.setLong(++paramIdx, filechunkpayload_id_oid); 099// ps.setLong(++paramIdx, histocryptorepofile_id_oid); 100// ps.setInt(++paramIdx, length); 101// ps.setLong(++paramIdx, offset); 102// ps.setTimestamp(++paramIdx, changed); 103// ps.setTimestamp(++paramIdx, created); 104// 105// try { 106// ps.execute(); 107// } catch (Exception x) { 108// logger.error("testInsert: " + x, x); 109// return; 110// } 111// } 112// logger.info("testInsert: Success!"); 113// } finally { 114// connection.rollback(); 115// connection.setAutoCommit(true); 116// } 117// } 118 119 private void dropForeignKeys() throws SQLException { // DataNucleus will re-create them. 120 for (String tableName : getTableNames()) { 121 for (String foreignKeyName : getForeignKeyNames(tableName)) { 122 try { 123 statement.execute(String.format("ALTER TABLE \"%s\" DROP CONSTRAINT \"%s\"", tableName, foreignKeyName)); 124 logger.info("dropForeignKeys: Dropped foreign-key '{}' of table '{}'.", foreignKeyName, tableName); 125 } catch (SQLException x) { 126 logger.warn("dropForeignKeys: Could not drop foreign-key '{}' of table '{}': {}", foreignKeyName, tableName, x.toString()); 127 } 128 } 129 } 130 } 131 132 private void dropIndices() throws SQLException { // DataNucleus will re-create them. 133 for (String tableName : getTableNames()) { 134 for (String indexName : getIndexNames(tableName)) { 135 try { 136 statement.execute(String.format("DROP INDEX \"%s\"", indexName)); 137 logger.info("dropIndices: Dropped index '{}'.", indexName); 138 } catch (SQLException x) { 139 logger.warn("dropIndices: Could not drop index '{}': {}", indexName, x.toString()); 140 } 141 } 142 } 143 } 144 145 private Collection<String> getTableNames() throws SQLException 146 { 147 ArrayList<String> res = new ArrayList<String>(); 148 149 final ResultSet rs = connection.getMetaData().getTables(null, null, null, null); 150 while (rs.next()) { 151 final String tableName = rs.getString("TABLE_NAME"); 152 final String tableType = rs.getString("TABLE_TYPE"); 153 154 if ("SEQUENCE".equals(tableType == null ? null : tableType.toUpperCase())) 155 continue; 156 157 if (tableName.toLowerCase().startsWith("sys")) 158 continue; 159 160 res.add(tableName); 161 } 162 rs.close(); 163 164 return res; 165 } 166 167 private Collection<String> getForeignKeyNames(String tableName) throws SQLException { 168 Set<String> tableNameAndForeignKeyNameSet = new HashSet<>(); 169 ArrayList<String> res = new ArrayList<String>(); 170 171 for (String toTableName : getTableNames()) { 172 ResultSet rs = connection.getMetaData().getCrossReference(null, null, toTableName, null, null, tableName); 173 while (rs.next()) { 174// String parentKeyTableName = rs.getString("PKTABLE_NAME"); 175// String foreignKeyTableName = rs.getString("FKTABLE_NAME"); 176 String foreignKeyName = rs.getString("FK_NAME"); 177 if (foreignKeyName == null) 178 continue; 179 180// if (foreignKeyTableName != null && !tableName.equals(foreignKeyTableName)) 181// continue; 182 183 String tableNameAndForeignKeyName = tableName + '.' + foreignKeyName; 184 if (tableNameAndForeignKeyNameSet.add(tableNameAndForeignKeyName)) 185 res.add(foreignKeyName); 186 } 187 rs.close(); 188 } 189 190 return res; 191 } 192 193 private Collection<String> getIndexNames(String tableName) throws SQLException { 194 ArrayList<String> res = new ArrayList<String>(); 195 196 ResultSet rs = connection.getMetaData().getIndexInfo(null, null, tableName, false, true); 197 while (rs.next()) { 198 String indexName = rs.getString("INDEX_NAME"); 199 if (indexName == null) 200 continue; 201 202 res.add(indexName); 203 } 204 rs.close(); 205 206 return res; 207 } 208}