/*******************************************************************************
* Copyright (c) 2010 Arapiki Solutions Inc.
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the Eclipse Public License v1.0
* which accompanies this distribution, and is available at
* http://www.eclipse.org/legal/epl-v10.html
*
* Contributors:
* "Peter Smith <psmith@arapiki.com>" - initial API and
* implementation and/or initial documentation
*******************************************************************************/
package com.buildml.model.impl;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.apache.commons.io.FileUtils;
import com.buildml.model.FatalBuildStoreError;
import com.buildml.model.IPackageMemberMgr;
/**
* A helper class to manage and simplify all the database access performed
* by the BuildStore class and it's managers. This class, and all the methods
* are package-private, meaning that the end user shouldn't be aware of this
* class.
*
* @author "Peter Smith <psmith@arapiki.com>"
*/
/* package private */ class BuildStoreDB {
/*=====================================================================================*
* FIELDS/TYPES
*=====================================================================================*/
/**
* Our connection to the BuildStore's SQL database.
*/
private Connection dbConn = null;
/**
* The current schema version (for newly created BuildStore objects).
* If the database we're reading has a newer schema, we can't handle it. If
* it has an older schema, we need to upgrade it.
*/
public static final int SCHEMA_VERSION = 409;
/** Prepared Statements to make database access faster. */
private PreparedStatement lastRowIDPrepStmt = null;
/** The original name of this database file (user-facing) */
private String databaseFileName;
/** The temporary name of this database file (for unsaved changes) */
private String tempDatabaseFileName;
/**
* true if we've created a temporary working copy of the database.
* That is, databaseFileName != tempDatabaseFileName.
*/
private boolean saveRequired;
/**
* True if the database is in "fast mode" (i.e. auto-commit is disabled). By default,
* this is turned off (auto-commit is one).
*/
private boolean fastAccessMode = false;
/*=====================================================================================*
* CONSTRUCTORS
*=====================================================================================*/
/**
* Create a new BuildStoreDB object.
*
* @param databaseName The name of the database to create. For SQLite databases,
* this is the path to the database file.
* @param saveRequired True if this database must be explicitly "saved" before it's
* closed (otherwise the changes will be discarded).
* @throws FileNotFoundException The database file can't be found, or isn't writable.
* @throws IOException Problem when opening the database, or making a temporary working database.
*/
/* package private */
BuildStoreDB(String databaseName, boolean saveRequired) throws FileNotFoundException, IOException {
/* make sure that the sqlite JDBC connector is available */
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
throw new FatalBuildStoreError("Unable to access the SQLite driver", e);
}
/*
* Ensure that the database name ends with .bml (if it doesn't already)
*/
String fileToOpen;
if (databaseName.endsWith(".bml")) {
fileToOpen = databaseName;
} else {
fileToOpen = databaseName + ".bml";
}
/* save the user-facing name of the database file, for when we need to save */
databaseFileName = new File(fileToOpen).getAbsolutePath();
/*
* If we want save/saveAs functionality, create a temporary database file
* where the live changes will be made. We start by making a copy of the
* user-facing database file into this temporary file.
*/
this.saveRequired = saveRequired;
if (saveRequired) {
try {
tempDatabaseFileName = File.createTempFile("temp", ".tmpbml").toString();
} catch (IOException e) {
throw new IOException("Unable to open " + fileToOpen + ". " + e.getMessage());
}
/*
* If there's an existing database file that we're editing, make a copy of it. If
* not, we simply open a new database with the temporary name.
*/
if (new File(fileToOpen).exists()) {
FileUtils.copyFile(new File(fileToOpen), new File(tempDatabaseFileName));
}
fileToOpen = tempDatabaseFileName;
}
/*
* Open/create the database. The sqlite database will be created as
* a local disk file with a .bml extension.
*/
try {
dbConn = DriverManager.getConnection("jdbc:sqlite:" + fileToOpen);
} catch (SQLException e) {
/* provide a meaningful error message if the file simply can't be opened */
if (e.getMessage().contains("Permission denied")) {
throw new FileNotFoundException("Error: Unable to open database file: " + fileToOpen);
}
/* else provide a more generic message */
throw new FatalBuildStoreError("Unable to access to SQLite database: " +
fileToOpen + "\n" + e.getMessage());
}
/* prepare some statements */
lastRowIDPrepStmt = prepareStatement("select last_insert_rowid()");
/* performance tuning for the database */
long maxMemory = Runtime.getRuntime().maxMemory();
if (maxMemory != Long.MAX_VALUE){
/*
* Set the SQLITE in-memory page cache to 30% of available memory.
* This makes the database really fast, although does tend to take up memory.
*/
int cache_size = (int)(maxMemory / 1024 * 0.3);
if (cache_size < 2000) {
cache_size = 2000;
}
executeUpdate("pragma cache_size=" + cache_size);
}
}
/*=====================================================================================*
* STATIC METHODS
*=====================================================================================*/
/**
* Utility method for converting an SQL exception into a FatalBuildStoreError. Lots of
* parts of the code could trigger an SQL exception, and all exception reporting happens here.
*
* @param sqlException The SQL exception that was thrown.
*/
public static void throwSqlException(SQLException sqlException) {
throw new FatalBuildStoreError("Unable to execute SQL statement", sqlException);
}
/*=====================================================================================*
* PACKAGE METHODS
*=====================================================================================*/
/**
* Retrieve the schema version of this database.
*
* @return The schema version, or -1 if the schema isn't yet initialized.
*/
/* package private */
int getBuildStoreVersion() {
int version = -1;
Statement stat = null;
ResultSet rs = null;
/* make sure the database connection is still open */
checkDatabase();
/*
* Create a new statement. If this fails, it's really bad and
* we can't use the database at all.
*/
try {
stat = dbConn.createStatement();
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to create a SQL statement", e);
}
/*
* Query the schema_version table to retrieve the version number. If this
* fails, it's just because we haven't initialized the schema. Return 0.
*/
try {
rs = stat.executeQuery("select version from schemaVersion");
if (rs.next()) {
version = rs.getInt("version");
}
} catch (SQLException e) {
/* there's no schema in place, return 0 */
version = -1;
}
/* close everything - if this fails, things are really bad */
try {
if (stat != null) {
stat.close();
if (rs != null) {
rs.close();
}
}
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to close SQL statement", e);
}
/* return the schema version */
return version;
}
/*-------------------------------------------------------------------------------------*/
/**
* Initialize the database by adding an entirely new schema. Remove any
* fields that already exist.
*/
/* package private */
void initDatabase() {
/* make sure the database connection is still open */
checkDatabase();
setFastAccessMode(true);
dropDatabase();
try {
Statement stat = dbConn.createStatement();
/*
* Create the "schema_version" table, and insert the current schema version. We can
* use this field to detect older versions of the database.
*/
stat.executeUpdate("create table schemaVersion ( version integer )");
stat.executeUpdate("insert into schemaVersion values ( " + SCHEMA_VERSION + ")");
/* Create the "files" table. */
stat.executeUpdate("create table files ( id integer primary key, parentId integer, trashed integer, " +
"pathType integer, name text not null)");
stat.executeUpdate("insert into files values (0, 0, 0, 1, \"/\")");
stat.executeUpdate("create unique index filesIdx on files (parentId, name)");
/* Create the "fileIncludes" table */
stat.executeUpdate("create table fileIncludes ( fileId1 integer, fileId2 integer, usage integer)");
stat.executeUpdate("create unique index buildFileIncludesIdx1 on fileIncludes (fileId1, fileId2)");
stat.executeUpdate("create index buildFileIncludesIdx2 on fileIncludes (fileId1)");
stat.executeUpdate("create index buildFileIncludesIdx3 on fileIncludes (fileId2)");
/* Create the "buildActions" table. */
stat.executeUpdate("create table buildActions ( actionId integer primary key, " +
"parentActionId integer, trashed integer, actionType integer)");
stat.executeUpdate("insert into buildActions values (0, 0, 0, 0)");
stat.executeUpdate("create index buildActionsIdx on buildActions (parentActionId)");
/* Create the "actionFiles" tables. */
stat.executeUpdate("create table actionFiles ( seqno integer primary key, actionId integer, " +
"fileId integer, operation integer)");
stat.executeUpdate("create index actionFilesIdx1 on actionFiles (fileId)");
stat.executeUpdate("create unique index actionFilesIdx2 on actionFiles (actionId, fileId)");
stat.executeUpdate("create index actionFilesIdx3 on actionFiles (fileId, operation)");
/* Create the "fileRoots" table */
stat.executeUpdate("create table fileRoots (name text primary key, fileId integer)");
stat.executeUpdate("insert into fileRoots values (\"root\", 0)");
/* Create the "workspace" table */
stat.executeUpdate("create table workspace (distance integer)");
stat.executeUpdate("insert into workspace values (0)");
/* Create the fileAttrsName table */
stat.executeUpdate("create table fileAttrsName (id integer primary key, name text)");
/* Create the fileAttrs table */
stat.executeUpdate("create table fileAttrs (pathId integer, attrId integer, value text)");
stat.executeUpdate("create index fileAttrsIdx1 on fileAttrs (pathId)");
stat.executeUpdate("create unique index fileAttrsIdx2 on fileAttrs (pathId, attrId)");
/* Create the packages table */
stat.executeUpdate("create table packages (id integer primary key, isFolder integer, " +
"parent integer, name text)");
stat.executeUpdate("insert into packages values (0, 0, 1, '<import>')");
stat.executeUpdate("insert into packages values (1, 1, 1, 'Root')");
/* Create the file group tables */
stat.executeUpdate("create table fileGroups (id integer primary key, type integer, predId integer)");
stat.executeUpdate("create table fileGroupPaths (groupId integer, pathId integer, " +
"pathString text, pos integer)");
/* Create the slotTypes table */
stat.executeUpdate("create table slotTypes (slotId integer primary key, ownerType integer, " +
"ownerId integer, slotName text, slotDescr text, slotType integer, slotPos integer, " +
"slotCard integer, defaultValue text, enumId integer, trashed integer)");
/* Create the slotValues table */
stat.executeUpdate("create table slotValues (ownerType integer, ownerId integer, " +
"slotId integer, value text)");
/* Create the packageMember table, and add default values */
stat.executeUpdate("create table packageMembers (memberType integer, memberId integer, " +
"pkgId integer, scopeId integer, x integer, y integer)");
stat.executeUpdate("create unique index packageMembersIdx on packageMembers (memberType, memberId)");
stat.executeUpdate("insert into packageMembers values (" + IPackageMemberMgr.TYPE_FILE +
", 0, 0, 0, 0, 0)"); /* the "/" path */
/* Create the subPackage table */
stat.executeUpdate("create table subPackages (subPkgId integer primary key, pkgTypeId integer, " +
"trashed integer)");
/* Create the package exports table */
stat.executeUpdate("create table pkgExports (fileGroupId integer, slotId integer)");
stat.close();
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to initialize database schema", e);
}
setFastAccessMode(false);
}
/*-------------------------------------------------------------------------------------*/
/**
* Delete the content of the database, normally in preparation to recreate it with
* a fresh schema.
*/
/* package private */
void dropDatabase() {
/* make sure the database connection is still open */
checkDatabase();
try {
Statement stat = dbConn.createStatement();
stat.executeUpdate("drop table if exists schemaVersion");
stat.executeUpdate("drop table if exists files");
stat.executeUpdate("drop table if exists fileIncludes");
stat.executeUpdate("drop table if exists buildActions");
stat.executeUpdate("drop table if exists actionFiles");
stat.executeUpdate("drop table if exists fileRoots");
stat.executeUpdate("drop table if exists fileAttrsName");
stat.executeUpdate("drop table if exists fileAttrs");
stat.executeUpdate("drop table if exists packages");
stat.executeUpdate("drop table if exists subPackages");
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to drop database schema", e);
}
}
/*-------------------------------------------------------------------------------------*/
/**
* Specify whether database access should be fast (true) or safe (false). Fast
* access is considerably faster than safe access, but won't ensure that
* changes are written to the disk. Only use fast access for "large write" operations.
*
* @param fast Set to true to enable fast access, or false for safe access.
* @return The previous "fast access" state (before this call was made).
*/
/* package private */
boolean setFastAccessMode(boolean fast){
/* if there's no state change, do nothing */
if (fast == fastAccessMode) {
return fast;
}
/* make sure the database connection is still open */
checkDatabase();
try {
dbConn.setAutoCommit(!fast);
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to setFastAccessMode", e);
}
fastAccessMode = fast;
/* the state has now flipped - return the previous state */
return !fast;
}
/*-------------------------------------------------------------------------------------*/
/**
* Execute a (non-prepared) SQL statement of any update-style command. That is,
* there can't be any results returned from this command.
*
* @param sql The SQL command to executed.
* @return The number of rows changed (or 0 if the command in question didn't change rows).
*/
/* package private */
int executeUpdate(String sql) {
/* make sure the database connection is still open */
checkDatabase();
Statement stmt;
int rowCount = 0;
try {
stmt = dbConn.createStatement();
rowCount = stmt.executeUpdate(sql);
stmt.close();
} catch (SQLException e) {
throw new FatalBuildStoreError("Error executing SQL: " + sql, e);
}
return rowCount;
}
/*-------------------------------------------------------------------------------------*/
/**
* Execute a prepared SQL update-style statement, and return the number of rows that
* were updated. This should only be used for SQL commands that update the database,
* since it can't return the result of a query.
*
* @param stmt The prepared SQL statement to execute
* @return The number of rows updated after executing the statement
*/
/* package private */
int executePrepUpdate(PreparedStatement stmt) {
/* make sure the database connection is still open */
checkDatabase();
int rowCount = 0;
try {
rowCount = stmt.executeUpdate();
} catch (SQLException e) {
throw new FatalBuildStoreError("Error executing SQL: ", e);
}
return rowCount;
}
/*-------------------------------------------------------------------------------------*/
/**
* Execute a (non-prepared) SQL statement, returning a String array of the results
* (one array entry per returned row). This method is simply a helper to make this
* common operation easier to use.
* @param sql The SQL command to be executed
* @return An array of Strings, one per returned row.
*/
/* package private */
String[] executeSelectColumn(String sql) {
/* make sure the database connection is still open */
checkDatabase();
Statement stmt;
ArrayList<String> result;
try {
stmt = dbConn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
result = new ArrayList<String>();
while (rs.next()){
result.add(rs.getString(1));
}
} catch (SQLException e) {
throw new FatalBuildStoreError("Error executing SQL: " + sql, e);
}
return result.toArray(new String[0]);
}
/*-------------------------------------------------------------------------------------*/
/**
* Execute a prepared database statement that performs a query on a String column.
* The query should return a single column of results, and the values will be returned as
* strings. If multiple columns are queried, only the first will be returned.
*
* @param stmt The prepared statement to be executed.
* @return Returns a (possibly empty) array of results.
*/
/* package private */
String[] executePrepSelectStringColumn(PreparedStatement stmt) {
/* make sure the database connection is still open */
checkDatabase();
ArrayList<String> result;
try {
ResultSet rs = stmt.executeQuery();
result = new ArrayList<String>();
while (rs.next()){
result.add(rs.getString(1));
}
rs.close();
} catch (SQLException e) {
throw new FatalBuildStoreError("Error executing SQL:", e);
}
return result.toArray(new String[0]);
}
/*-------------------------------------------------------------------------------------*/
/**
* Execute a prepared database statement that returns Integer values, such as a select
* on a numeric column. The query should only return a single column of Integer results.
* If multiple columns are queried, only the first will be returned.
*
* @param stmt The prepared statement to be executed.
* @return Returns a (possibly empty) array of results.
*/
/* package private */
Integer[] executePrepSelectIntegerColumn(PreparedStatement stmt) {
/* make sure the database connection is still open */
checkDatabase();
ArrayList<Integer> result;
try {
ResultSet rs = stmt.executeQuery();
result = new ArrayList<Integer>();
while (rs.next()){
result.add(rs.getInt(1));
}
rs.close();
} catch (SQLException e) {
throw new FatalBuildStoreError("Error executing SQL:", e);
}
return result.toArray(new Integer[0]);
}
/*-------------------------------------------------------------------------------------*/
/**
* Execute a database query using a prepared statement, and return the full ResultSet
* object. This is purely a convenience function to make it easier to access the
* database and catch Exceptions.
*
* @param stmt The prepared SQL statement to be executed.
* @return The ResultSet from the database query.
*/
/* package private */
ResultSet executePrepSelectResultSet(PreparedStatement stmt) {
/* make sure the database connection is still open */
checkDatabase();
ResultSet rs;
try {
rs = stmt.executeQuery();
} catch (SQLException e) {
throw new FatalBuildStoreError("Error executing SQL:", e);
}
return rs;
}
/*-------------------------------------------------------------------------------------*/
/**
* Execute a database query using a (non-prepared) SQL statement, and return the
* full ResultSet object. This is purely a convenience function to make it easier to
* access the database and catch Exceptions.
*
* @param sql The SQL command to be executed.
* @return The ResultSet from the database query.
*/
/* package private */
ResultSet executeSelectResultSet(String sql) {
/* make sure the database connection is still open */
checkDatabase();
ResultSet rs;
try {
Statement stmt = dbConn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
throw new FatalBuildStoreError("Error executing SQL:", e);
}
return rs;
}
/*-------------------------------------------------------------------------------------*/
/**
* Returns the integer value of the last auto-increment row ID. This is used to
* fetch the last primary key inserted into a table, when the user specified "null"
* to have the database automatically select a suitable unique primary key.
*
* @return The last inserted primary key.
*/
/* package private */
int getLastRowID() {
/* make sure the database connection is still open */
checkDatabase();
Integer lastRowID[] = executePrepSelectIntegerColumn(lastRowIDPrepStmt);
return lastRowID[0];
}
/*-------------------------------------------------------------------------------------*/
/**
* Create a prepared statement from an SQL command string.
*
* @param sql The SQL command to be prepared.
* @return The prepared database statement for executing the SQL command at a later time.
*/
/* package private */
PreparedStatement prepareStatement(String sql) {
/* make sure the database connection is still open */
checkDatabase();
try {
return dbConn.prepareStatement(sql);
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to prepare sql statement: " + sql, e);
}
}
/*-------------------------------------------------------------------------------------*/
/**
* Close a database connection, releasing all resources. From this point on,
* none of the methods in the class may be used (they'll simply throw an
* exception). If there were any unsaved changes in the database, they'll be
* discarded.
*/
/* package private */
void close() {
/* make sure the database connection is still open */
checkDatabase();
/*
* Remove all database rows that are associated with "trashed" files
* and actions. This includes fileAttributes.
*/
emptyTrash();
/* make sure all changes are committed */
setFastAccessMode(false);
try {
dbConn.close();
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to close database connection: " + e);
}
/*
* If this BuildStore was opened with the "saveRequired" flag set, we can now
* delete the temporary file. The caller *should have* saved the database
* if they actually want to keep the content.
*/
if (saveRequired) {
new File(tempDatabaseFileName).delete();
}
/*
* Make the connection variable unusable - this will result in exceptions being
* thrown if somebody try to use it.
*/
dbConn = null;
}
/*-------------------------------------------------------------------------------------*/
/**
* Save the content of this database to disk. This method only has an effect if
* the database was created with savedRequired == true, in which case a temporary
* copy of the original database was used. This method saves the temporary database
* on top of the original (user-facing) file.
* @throws IOException Unable to save the database.
*/
public void save() throws IOException {
if (saveRequired) {
setFastAccessMode(false);
FileUtils.copyFile(new File(tempDatabaseFileName), new File(databaseFileName));
/*
* We must now empty the trash on the file that was saved, so that it no longer
* includes all objects (actions, file groups, etc) that were deleted. However,
* we must NOT empty the trash on the temporary database, since the user can
* still do "undo" the delete.
*/
BuildStoreDB emptyTrashDB = new BuildStoreDB(databaseFileName, false);
emptyTrashDB.emptyTrash();
emptyTrashDB.close();
}
}
/*-------------------------------------------------------------------------------------*/
/**
* Save the content of this database to disk, using the caller-specified file name.
* This method only has an effect if the database was created with savedRequired == true,
* in which case a temporary copy of the original database was used. This method saves
* the temporary database on top of the caller-specified file.
* @param fileToSave New name of the database file. This new name becomes the default
* name for all future "save" operations.
* @throws IOException Unable to save the database.
*/
public void saveAs(String fileToSave) throws IOException {
if (saveRequired) {
databaseFileName = new File(fileToSave).getAbsolutePath();
save();
}
}
/*-------------------------------------------------------------------------------------*/
/**
* Purge the database on any trashed files or actions that may still be present.
*
* @throws FatalBuildStoreError Something went wrong.
*/
public void emptyTrash() throws FatalBuildStoreError {
try {
Statement stat = dbConn.createStatement();
/* delete entries from packageMembers where the files, actions or sub-packages have been trashed */
stat.executeUpdate("delete from packageMembers where memberType = " +
IPackageMemberMgr.TYPE_FILE + " and memberId in " +
"(select id from files where trashed=1)");
stat.executeUpdate("delete from packageMembers where memberType = " +
IPackageMemberMgr.TYPE_ACTION + " and memberId in " +
"(select actionId from buildActions where trashed=1)");
stat.executeUpdate("delete from packageMembers where memberType = " +
IPackageMemberMgr.TYPE_SUB_PACKAGE + " and memberId in " +
"(select subPkgId from subPackages where trashed=1)");
/* delete empty file groups (and their package membership) */
stat.executeUpdate("delete from fileGroups where id not in " +
"(select groupId from fileGroupPaths group by groupId)");
stat.executeUpdate("delete from packageMembers where memberType = " +
IPackageMemberMgr.TYPE_FILE_GROUP +
" and memberId not in " +
"(select groupId from fileGroupPaths group by groupId)");
/* now delete the files and actions themselves */
stat.executeUpdate("delete from fileAttrs where pathId in (select id from files where trashed=1);");
stat.executeUpdate("delete from files where trashed=1");
stat.executeUpdate("delete from buildActions where trashed=1;");
stat.executeUpdate("delete from subPackages where trashed=1;");
stat.executeUpdate("delete from slotTypes where trashed=1;");
} catch (SQLException e) {
throw new FatalBuildStoreError("Unable to remove trashed files and actions", e);
}
}
/*-------------------------------------------------------------------------------------*/
/**
* @return The (native) path to the file containing our database.
*/
public String getDatabaseFileName() {
return databaseFileName;
}
/*=====================================================================================*
* PRIVATE METHODS
*=====================================================================================*/
/**
* Check that our database connection is open, else throw an exception. This stops
* us from getting weird SQL errors when somebody tries to use the database after
* it's closed.
*/
private void checkDatabase() {
if (dbConn == null) {
throw new FatalBuildStoreError("BuildStore has been closed.");
}
}
/*-------------------------------------------------------------------------------------*/
}