001package co.codewizards.cloudstore.local;
002
003import static co.codewizards.cloudstore.core.util.Util.*;
004
005import java.io.File;
006import java.sql.Connection;
007import java.sql.ResultSet;
008import java.sql.SQLException;
009import java.sql.Statement;
010import java.util.ArrayList;
011import java.util.Collection;
012import java.util.HashSet;
013import java.util.Set;
014
015import org.slf4j.Logger;
016import org.slf4j.LoggerFactory;
017
018public class RepairDatabase implements Runnable {
019        private static final Logger logger = LoggerFactory.getLogger(RepairDatabase.class);
020
021        private final File localRoot;
022
023        private Connection connection;
024        private Statement statement;
025
026        public RepairDatabase(File localRoot) {
027                this.localRoot = assertNotNull("localRoot", localRoot);
028        }
029
030        @Override
031        public void run() {
032                try {
033                        JdbcConnectionFactory jdbcConnectionFactory = new JdbcConnectionFactory(localRoot);
034                        connection = jdbcConnectionFactory.createConnection();
035                        try {
036                                statement = connection.createStatement();
037                                try {
038                                        executeDerbyCheckTable();
039                                        dropForeignKeys();
040                                        dropIndices();
041                                        executeDerbyCheckTable();
042                                } finally {
043                                        statement.close();
044                                }
045                        } finally {
046                                connection.close();
047                        }
048                } catch (SQLException x) {
049                        throw new RuntimeException(x);
050                }
051        }
052
053        private void executeDerbyCheckTable() throws SQLException {
054                // http://objectmix.com/apache/646586-derby-db-files-get-corrupted-2.html
055                statement.execute(
056                                "SELECT schemaname, tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) "
057                                + "FROM sys.sysschemas s, sys.systables t "
058                                + "WHERE s.schemaid = t.schemaid");
059        }
060
061        private void dropForeignKeys() throws SQLException { // DataNucleus will re-create them.
062                for (String tableName : getTableNames()) {
063                        for (String foreignKeyName : getForeignKeyNames(tableName)) {
064                                try {
065                                        statement.execute(String.format("ALTER TABLE \"%s\" DROP CONSTRAINT \"%s\"", tableName, foreignKeyName));
066                                        logger.info("dropForeignKeys: Dropped foreign-key '{}' of table '{}'.", foreignKeyName, tableName);
067                                } catch (SQLException x) {
068                                        logger.warn("dropForeignKeys: Could not drop foreign-key '{}' of table '{}': {}", foreignKeyName, tableName, x.toString());
069                                }
070                        }
071                }
072        }
073
074        private void dropIndices() throws SQLException { // DataNucleus will re-create them.
075                for (String tableName : getTableNames()) {
076                        for (String indexName : getIndexNames(tableName)) {
077                                try {
078                                        statement.execute(String.format("DROP INDEX \"%s\"", indexName));
079                                        logger.info("dropIndices: Dropped index '{}'.", indexName);
080                                } catch (SQLException x) {
081                                        logger.warn("dropIndices: Could not drop index '{}': {}", indexName, x.toString());
082                                }
083                        }
084                }
085        }
086
087        private Collection<String> getTableNames() throws SQLException
088        {
089                ArrayList<String> res = new ArrayList<String>();
090
091                final ResultSet rs = connection.getMetaData().getTables(null, null, null, null);
092                while (rs.next()) {
093                        final String tableName = rs.getString("TABLE_NAME");
094                        final String tableType = rs.getString("TABLE_TYPE");
095
096                        if ("SEQUENCE".equals(tableType == null ? null : tableType.toUpperCase()))
097                                continue;
098
099                        if (tableName.toLowerCase().startsWith("sys"))
100                                continue;
101
102                        res.add(tableName);
103                }
104                rs.close();
105
106                return res;
107        }
108
109        private Collection<String> getForeignKeyNames(String tableName) throws SQLException {
110                Set<String> tableNameAndForeignKeyNameSet = new HashSet<>();
111                ArrayList<String> res = new ArrayList<String>();
112
113                for (String toTableName : getTableNames()) {
114                        ResultSet rs = connection.getMetaData().getCrossReference(null, null, toTableName, null, null, tableName);
115                        while (rs.next()) {
116//                              String parentKeyTableName = rs.getString("PKTABLE_NAME");
117//                              String foreignKeyTableName = rs.getString("FKTABLE_NAME");
118                                String foreignKeyName = rs.getString("FK_NAME");
119                                if (foreignKeyName == null)
120                                        continue;
121
122//                              if (foreignKeyTableName != null && !tableName.equals(foreignKeyTableName))
123//                                      continue;
124
125                                String tableNameAndForeignKeyName = tableName + '.' + foreignKeyName;
126                                if (tableNameAndForeignKeyNameSet.add(tableNameAndForeignKeyName))
127                                        res.add(foreignKeyName);
128                        }
129                        rs.close();
130                }
131
132                return res;
133        }
134
135        private Collection<String> getIndexNames(String tableName) throws SQLException {
136                ArrayList<String> res = new ArrayList<String>();
137
138                ResultSet rs = connection.getMetaData().getIndexInfo(null, null, tableName, false, true);
139                while (rs.next()) {
140                        String indexName = rs.getString("INDEX_NAME");
141                        if (indexName == null)
142                                continue;
143
144                        res.add(indexName);
145                }
146                rs.close();
147
148                return res;
149        }
150}