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}