001/*
002 * Syncany, www.syncany.org
003 * Copyright (C) 2011-2016 Philipp C. Heckel <philipp.heckel@gmail.com>
004 *
005 * This program is free software: you can redistribute it and/or modify
006 * it under the terms of the GNU General Public License as published by
007 * the Free Software Foundation, either version 3 of the License, or
008 * (at your option) any later version.
009 *
010 * This program is distributed in the hope that it will be useful,
011 * but WITHOUT ANY WARRANTY; without even the implied warranty of
012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
013 * GNU General Public License for more details.
014 *
015 * You should have received a copy of the GNU General Public License
016 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
017 */
018package org.syncany.database;
019
020import java.io.BufferedReader;
021import java.io.File;
022import java.io.IOException;
023import java.io.InputStream;
024import java.io.InputStreamReader;
025import java.sql.Connection;
026import java.sql.DriverManager;
027import java.sql.PreparedStatement;
028import java.sql.ResultSet;
029import java.sql.SQLException;
030import java.util.HashMap;
031import java.util.Map;
032import java.util.logging.Level;
033import java.util.logging.Logger;
034
035import org.syncany.util.FileUtil;
036import org.syncany.util.SqlRunner;
037
038/**
039 * This class is a helper class that provides the connection to the embedded
040 * HSQLDB database. It is mainly used by the data access objects.
041 *
042 * <p>The class provides methods to create {@link Connection} objects, retrieve
043 * SQL statements from the resources, and create the initial tables when the
044 * application is first started.
045 *
046 * @author Philipp C. Heckel (philipp.heckel@gmail.com)
047 */
048public class DatabaseConnectionFactory {
049        private static final Logger logger = Logger.getLogger(DatabaseConnectionFactory.class.getSimpleName());
050
051        public static final String DATABASE_DRIVER = "org.hsqldb.jdbcDriver";
052        public static final String DATABASE_CONNECTION_FILE_STRING = "jdbc:hsqldb:file:%DATABASEFILE%;user=sa;password=;create=true;write_delay=false;hsqldb.write_delay=false";
053        public static final String DATABASE_RESOURCE_PATTERN = "/org/syncany/database/sql/%s";
054        public static final String DATABASE_RESOURCE_CREATE_ALL = "script.create.all.sql";
055
056        public static final Map<String, String> DATABASE_STATEMENTS = new HashMap<String, String>();
057
058        static {
059                try {
060                        logger.log(Level.INFO, "Loading database driver " + DATABASE_DRIVER + " ...");
061                        Class.forName(DATABASE_DRIVER);
062                }
063                catch (Exception e) {
064                        throw new RuntimeException("Cannot load database driver: " + DATABASE_DRIVER, e);
065                }
066        }
067
068        /**
069         * Creates a database connection using the given database file. If the database exists and the
070         * application tables are present, a valid connection is returned. If not, the database is created
071         * and the application tables are created.
072         *
073         * @param databaseFile File at which to create/load the database
074         * @param readOnly True if this connection is only used for reading.
075         * @return Returns a valid database connection
076         */
077        public static Connection createConnection(File databaseFile, boolean readOnly) {
078                String databaseFilePath = FileUtil.getDatabasePath(databaseFile.toString());
079                String connectionString = DATABASE_CONNECTION_FILE_STRING.replaceAll("%DATABASEFILE%", databaseFilePath);
080
081                if (logger.isLoggable(Level.FINEST)) {
082                        connectionString += ";hsqldb.sqllog=3";
083                }
084
085                return createConnection(connectionString, readOnly);
086        }
087
088        /**
089         * Retrieves a SQL statement template from a resource using the given resource identifier. From
090         * this template, a {@link PreparedStatement} can be created.
091         *
092         * <p>The statement is either loaded from the resource (if it is first encountered),
093         * or loaded from the cache if it has been seen before.
094         *
095         * @param resourceIdentifier Path to the resource, e.g. "create.all.sql"
096         * @return Returns the SQL statement read from the resource
097         */
098        public synchronized static String getStatement(String resourceIdentifier) {
099                String fullResourcePath = String.format(DATABASE_RESOURCE_PATTERN, resourceIdentifier);
100                String preparedStatement = DATABASE_STATEMENTS.get(fullResourcePath);
101
102                if (preparedStatement != null) {
103                        return preparedStatement;
104                }
105                else {
106                        InputStream statementInputStream = getStatementInputStream(resourceIdentifier);
107
108                        preparedStatement = readDatabaseStatement(statementInputStream);
109                        DATABASE_STATEMENTS.put(fullResourcePath, preparedStatement);
110
111                        return preparedStatement;
112                }
113        }
114
115        public synchronized static InputStream getStatementInputStream(String resourceIdentifier) {
116                String fullResourcePath = String.format(DATABASE_RESOURCE_PATTERN, resourceIdentifier);
117                InputStream statementInputStream = DatabaseConnectionFactory.class.getResourceAsStream(fullResourcePath);
118
119                if (statementInputStream == null) {
120                        throw new RuntimeException("Unable to load SQL statement '" + fullResourcePath + "'.");
121                }
122
123                return statementInputStream;
124        }
125
126        private static Connection createConnection(String connectionString, boolean readOnly) {
127                try {
128                        Connection connection = DriverManager.getConnection(connectionString);
129                        connection.setAutoCommit(false);
130                        connection.setReadOnly(readOnly);
131
132                        // We use UNCOMMITTED read to enable operations to alter the database and continue
133                        // with those changes, but still roll back the database if something goes wrong later.
134                        connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
135
136                        // Test and create tables
137                        if (!tablesExist(connection)) {
138                                createTables(connection);
139                        }
140
141                        return connection;
142                }
143                catch (Exception e) {
144                        if (e.getCause() != null) {
145                                if (e.getCause().getMessage().contains("Database lock acquisition failure")) {
146                                        throw new RuntimeException("Another process is using the database.", e);
147                                }
148                        }
149                        throw new RuntimeException("Cannot create new connection; database down?", e);
150                }
151        }
152
153        private static boolean tablesExist(Connection connection) throws SQLException {
154                try (ResultSet resultSet = connection.prepareStatement(
155                                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_TYPE='TABLE'")
156                                .executeQuery()) {
157                        resultSet.next();
158                        int numberOfTables = resultSet.getInt(1);
159                        logger.log(Level.INFO, "Found " + numberOfTables + " tables.");
160
161                        // If we have 12 or more tables, we assume the creation scripts has created
162                        // all tables and indices.
163                        return (numberOfTables >= 12);
164                }
165        }
166
167        private static void createTables(Connection connection) throws SQLException, IOException {
168                logger.log(Level.INFO, "Database has no tables. Creating tables from " + DATABASE_RESOURCE_CREATE_ALL);
169
170                String fullResourcePath = String.format(DATABASE_RESOURCE_PATTERN, DATABASE_RESOURCE_CREATE_ALL);
171                InputStream inputStream = DatabaseConnectionFactory.class.getResourceAsStream(fullResourcePath);
172
173                connection.setAutoCommit(true);
174
175                SqlRunner.runScript(connection, inputStream);
176
177                connection.setAutoCommit(false);
178        }
179
180        // TODO [low] Shouldn't the SqlRunner be used here? If so, the SqlRunner also needs refactoring.
181        private static String readDatabaseStatement(InputStream inputStream) {
182                try {
183                        StringBuilder preparedStatementStr = new StringBuilder();
184                        BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
185
186                        String line = null;
187
188                        while (null != (line = reader.readLine())) {
189                                String trimmedLine = line.trim();
190
191                                if (!trimmedLine.startsWith("--")) {
192                                        preparedStatementStr.append(' ');
193                                        preparedStatementStr.append(trimmedLine);
194                                }
195                        }
196
197                        reader.close();
198                        inputStream.close();
199
200                        return preparedStatementStr.toString();
201                }
202                catch (IOException e) {
203                        throw new RuntimeException("Unable to read SQL statement from resource.", e);
204                }
205        }
206}