/* PDFDB - Embedded data package */
package pdfdb.data.db;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import pdfdb.settings.UserSettingsManager;
/** Lowest level access to the JDBC objects. This class is only accessible to
* the database layer.
* @author ug22cmg*/
public class DatabaseConnection
{
//<editor-fold defaultstate="collapsed" desc="Auto install DML">
private static final String CREATE_SQL_FILES = "CREATE TABLE Files(" +
"FilePath varchar(1500) NOT NULL PRIMARY KEY, " +
"LastIndexed date NOT NULL)";
private static final String CREATE_SQL_WORDS = "CREATE TABLE Words(" +
"WordId int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " +
"Tag varchar(150) NOT NULL" +
")";
private static final String CREATE_SQL_REGIONS = "CREATE TABLE Regions(" +
"RegionId int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " +
"FileId varchar(1500) NOT NULL, " +
"RegionType int NOT NULL," +
"CONSTRAINT T1C1 FOREIGN KEY(FileId) REFERENCES Files(FilePath) ON DELETE CASCADE)";
private static final String CREATE_SQL_INDEXES = "CREATE TABLE Indexes(" +
"IndexId int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " +
"RegionId int NOT NULL, " +
"WordPosition int NOT NULL," +
"WordId int NOT NULL," +
"CONSTRAINT T2C1 FOREIGN KEY(RegionId) REFERENCES Regions(RegionId) ON DELETE CASCADE," +
"CONSTRAINT T2C2 CHECK(WordPosition >= 0)," +
"CONSTRAINT T2C3 FOREIGN KEY(WordId) REFERENCES Words(WordId))";
private static final String CREATE_SQL_PROPERTIES = "CREATE TABLE Properties(" +
"PropertyId varchar(1500) NOT NULL, " +
"FriendlyName varchar(1500) NOT NULL, " +
"PropertyValue varchar(5000) NOT NULL, " +
"FileId varchar(1500) NOT NULL, " +
"CONSTRAINT T3C1 PRIMARY KEY(PropertyId, FileId)," +
"CONSTRAINT T3C2 FOREIGN KEY(FileId) REFERENCES Files(FilePath) ON DELETE CASCADE)";
//</editor-fold>
//<editor-fold defaultstate="collapsed" desc="Connection strings / driver names">
private static final String DB_CONNSTRING_CREATE = "jdbc:hsqldb:file:db";
private static final String DB_CONNSTRING_OPEN = "jdbc:hsqldb:file:db;ifexists=true";
private static final String DB_DRIVER_NAME = "org.hsqldb.jdbcDriver";
//</editor-fold>
/** Creates a new database.
* @return The database connection.
* @throws java.sql.SQLException If there is an error during creation. */
private static Connection createDatabase() throws SQLException
{
return DriverManager.getConnection(DB_CONNSTRING_CREATE);
}
/** Creates a new database and returns the connection.
* @return A connection to the new database. */
private static Connection fromNewDatabase()
{
Connection rtnConn = null;
try
{
rtnConn = createDatabase();
installTables(rtnConn);
return rtnConn;
}
catch (SQLException se)
{
return null;
}
}
/** Gets a list of all the DML statements to create the database.
* @return A list containing DML statements. */
private static List<String> getDML()
{
List<String> l = new ArrayList<String>();
l.add(CREATE_SQL_FILES);
l.add(CREATE_SQL_WORDS);
l.add(CREATE_SQL_REGIONS);
l.add(CREATE_SQL_INDEXES);
l.add(CREATE_SQL_PROPERTIES);
return l;
}
/** Installs the tables into the database.
* @param c A connection to a brand new database.
* @throws java.sql.SQLException If an error occurs. */
private static void installTables(Connection c) throws SQLException
{
Statement statement = null;
try
{
statement = c.createStatement();
for (String dml : getDML())
{
statement.execute(dml);
}
}
finally
{
close(statement);
}
}
/** External accessor method, throws an uncatchable runtime exception
* as we don't want to continue operating if we can't get access to the
* database.
* @return A new connection. */
static Connection getNewConnection()
{
try
{
return getConnection();
}
catch (SQLException ex)
{
throw new RuntimeException("The database was inaccessible.");
}
}
/** Gets the suitable connection object for the current context of the
* application. This may mean creating a new embedded database if it
* doesn't exist.
* @return An instance of the Connection object
* @throws java.sql.SQLException If there is a problem communicating
* with the database. */
static Connection getConnection() throws SQLException
{
try
{
int isolation = Connection.TRANSACTION_READ_UNCOMMITTED;
Connection currConn = null;
Class.forName(DB_DRIVER_NAME).newInstance();
currConn =
DriverManager.getConnection(DB_CONNSTRING_OPEN, "sa", "");
currConn.setTransactionIsolation(isolation);
return currConn;
}
catch (SQLException ex)
{
return fromNewDatabase();
}
catch (Exception ex)
{
return null;
}
}
/** Closes both a statement and result set and ignores exceptions.
* @param s The statement to close.
* @param resultSet The result set to close. */
static void close(Statement s, ResultSet resultSet)
{
try
{
if (resultSet != null) resultSet.close();
}
catch (SQLException ex)
{
}
close(s);
}
/** Closes a statement.
* @param statement Statement to close. */
static void close(Statement statement)
{
try
{
if (statement != null) statement.close();
}
catch (Exception e)
{
}
}
/** Sets the connection's auto-commit state. Wraps exceptions.
* @param conn The connection to set.
* @param autoCommit The new auto-commit state. */
static void setAutoCommit(Connection conn, boolean autoCommit)
{
try
{
conn.setAutoCommit(autoCommit);
}
catch (SQLException ex)
{
}
}
/** Closes connection.
* @param conn Connection to close. */
static void close(Connection conn)
{
try
{
if (conn != null) conn.close();
}
catch (SQLException se)
{
}
}
/** Registers a shutdown and return's the shutdown query. If this is
* the fifth shutdown, then the database is compacted.
* @return The shutdown SQL. */
private static String registerShutdown()
{
UserSettingsManager settings = UserSettingsManager.getInstance();
try
{
String command = "SHUTDOWN";
String compactCommand = " COMPACT";
String curr = settings.get("SHUTDOWN");
boolean fifthShutdown = curr == null ? false : curr.equals("5");
return fifthShutdown ? command + compactCommand : command;
}
finally
{
saveShutdown(settings);
}
}
/** Saves the current shutdown number to the configuration file.
* @param settings configuration file to use. */
private static void saveShutdown(UserSettingsManager settings)
{
try
{
String tmp = settings.get("SHUTDOWN");
int shutdown = tmp == null ? 0 : Integer.parseInt(tmp);
settings.set("SHUTDOWN", String.valueOf(shutdown + 1));
settings.save();
}
catch (IOException ex)
{
}
}
/** Performs a shutdown of the database. Once this is done, no further
* access to the database should be attempted. */
public static void shutdown()
{
Connection conn = null;
Statement statement = null;
try
{
conn = getConnection();
statement = conn.createStatement();
statement.execute(registerShutdown());
}
catch (SQLException e)
{
}
finally
{
close(statement);
close(conn);
}
}
}