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}