/**
* NanoDoA - File based document archive
*
* Copyright (C) 2011-2012 Christian Packenius, christian.packenius@googlemail.com
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package de.chris_soft.nanoarchive;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import de.chris_soft.utilities.IdUtils;
import de.chris_soft.utilities.Pair;
/**
* Database base class for NanoDoA.
* @author Christian Packenius.
*/
public class DB {
/**
* Path to database. This path leads to a directory.
*/
public final String dbPath;
/**
* Connection to database.
*/
private Connection conn;
/**
* Constructor.
* @param path Path to the directory of the database.
* @throws SQLException
* @throws ClassNotFoundException
*/
public DB(String path) throws ClassNotFoundException, SQLException {
dbPath = path;
openDatabase(path);
}
private void openDatabase(String path) throws ClassNotFoundException, SQLException {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
String url = "jdbc:derby:" + path;
boolean newDB = false;
if (!new File(path).exists()) {
System.out.println("Path does not exist, creating new database: " + path);
url += ";create=true";
newDB = true;
}
conn = DriverManager.getConnection(url);
if (newDB) {
createTablesAndIndexes();
}
}
private void createTablesAndIndexes() throws SQLException {
createDocumentsTable();
createDocumentPropertiesTable();
createPathsTable();
createLabelsTable();
createLabelingTable();
}
private void createDocumentsTable() throws SQLException {
createTable("documents", "documentID BIGINT NOT NULL PRIMARY KEY, pathID BIGINT NOT NULL, content BLOB NOT NULL");
createIndex("documentsIndexPathID", false, "documents", "pathID ASC");
}
/**
* Insert a document into database.
* @param documentID ID of the document (will be created if zero or -1).
* @param pathID Path ID (zero or -1 for root path).
* @param documentFile File of the document. Must exist and be readable.
* @return DocumentID.
* @throws SQLException
* @throws IOException
*/
public long addDocument(long documentID, long pathID, File documentFile) throws SQLException, IOException {
if (documentID == 0 || documentID == -1) {
documentID = IdUtils.getUniqueID();
}
if (pathID == -1) {
pathID = 0;
}
PreparedStatement ps = conn.prepareStatement("INSERT INTO documents (documentID, pathID, content) VALUES (?, ?, ?)");
ps.setLong(1, documentID);
ps.setLong(2, pathID);
long length = documentFile.length();
InputStream in = new FileInputStream(documentFile);
ps.setBinaryStream(3, in, length);
ps.execute();
in.close();
ps.close();
conn.commit();
return documentID;
}
/**
* Retrieves a document from database.
* @param documentID ID of the document.
* @return Byte array with document content or <i>null</i>, if document not found.
* @throws SQLException
* @throws IOException
*/
public byte[] readDocument(long documentID) throws SQLException, IOException {
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM documents WHERE documentID = " + documentID);
byte[] ba = null;
if (rs.next()) {
Blob blob = rs.getBlob("content");
int length = (int) blob.length();
InputStream in = blob.getBinaryStream();
ba = blob.getBytes(1, length);
in.close();
blob.free();
}
rs.close();
s.close();
return ba;
}
/**
* Retrieves the pathID of a document.
* @param documentID ID of the document.
* @return Path ID.
* @throws SQLException
*/
public long getPathFromDocument(long documentID) throws SQLException {
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM documents WHERE documentID = " + documentID);
long pathID = 0;
if (rs.next()) {
pathID = rs.getLong("pathID");
}
rs.close();
s.close();
return pathID;
}
/**
* Returns a list of documents at the given path ID.
* @param pathID ID of the path.
* @return List of documents. May be empty, is never <i>null</i>.
* @throws SQLException
*/
public List<Long> getDocumentsFromPath(long pathID) throws SQLException {
if (pathID == -1) {
pathID = 0;
}
List<Long> list = new ArrayList<Long>();
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM documents WHERE pathID = " + pathID);
while (rs.next()) {
list.add(rs.getLong("documentID"));
}
rs.close();
s.close();
return list;
}
/**
* Delete a document from database.
* @param documentID Document ID.
* @throws SQLException
*/
public void deleteDocument(long documentID) throws SQLException {
Statement s = conn.createStatement();
String sql = "DELETE FROM documents WHERE documentID = " + documentID;
s.executeUpdate(sql);
s.close();
conn.commit();
deleteDocumentProperties(documentID);
}
/**
* Changes the path of a document.
* @param documentID Document ID.
* @param newPathID New path ID for the document.
* @throws SQLException
*/
public void changeDocumentPath(long documentID, long newPathID) throws SQLException {
String sql = "UPDATE documents SET pathID = " + newPathID + " WHERE documentID = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setLong(1, documentID);
s.executeUpdate();
s.close();
conn.commit();
}
/**
* Returns the number of documents in the database.
* @return Document count.
* @throws SQLException
*/
public long getDocumentCount() throws SQLException {
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("select count(*) from documents");
rs.next();
long count = rs.getLong(1);
rs.close();
s.close();
return count;
}
private void createDocumentPropertiesTable() throws SQLException {
createTable("documentProperties",
"documentID BIGINT NOT NULL, xkey VARCHAR (200) NOT NULL, xvalue LONG VARCHAR NOT NULL, PRIMARY KEY (documentID, xkey)");
createIndex("documentPropertiesIndexDocumentID", false, "documentProperties", "documentID");
}
/**
* Returns the value of a document property.
* @param documentID Document ID.
* @param key Key of the document property.
* @return Value of the document property or <i>null</i> if not found.
* @throws SQLException
*/
public String getDocumentProperty(long documentID, String key) throws SQLException {
String sql = "select * from documentProperties where documentID = ? AND xkey = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, documentID);
ps.setString(2, key);
ResultSet rs = ps.executeQuery();
String value = null;
if (rs.next()) {
value = rs.getString("xvalue");
}
rs.close();
ps.close();
return value;
}
/**
* Returns all document properties.
* @param documentID Document ID.
* @return Document properties.
* @throws SQLException
*/
public Properties getDocumentProperties(long documentID) throws SQLException {
Properties props = new Properties();
String sql = "select * from documentProperties where documentID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, documentID);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String key = rs.getString("xkey");
String value = rs.getString("xvalue");
props.setProperty(key, value);
}
rs.close();
ps.close();
return props;
}
/**
* Set a document property value.
* @param documentID Document ID.
* @param key Key of the property.
* @param value Value of the property.
* @throws SQLException
*/
public void setDocumentProperty(long documentID, String key, String value) throws SQLException {
if (value == null) {
deleteDocumentProperty(documentID, key);
}
else if (getDocumentProperty(documentID, key) == null) {
createNewDocumentProperty(documentID, key, value);
}
else {
updateDocumentProperty(documentID, key, value);
}
}
/**
* Removes a document property value.
* @param documentID Document ID.
* @param key Key of the property.
* @throws SQLException
*/
public void deleteDocumentProperty(long documentID, String key) throws SQLException {
String sql = "DELETE FROM documentProperties WHERE documentID = ? and xkey = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setLong(1, documentID);
s.setString(2, key);
s.execute();
s.close();
conn.commit();
}
/**
* Removes all property values.
* @param documentID Document ID.
* @throws SQLException
*/
public void deleteDocumentProperties(long documentID) throws SQLException {
String sql = "DELETE FROM documentProperties WHERE documentID = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setLong(1, documentID);
s.execute();
s.close();
conn.commit();
}
private void createNewDocumentProperty(long documentID, String key, String value) throws SQLException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO documentProperties (documentID, xkey, xvalue) VALUES (?, ?, ?)");
ps.setLong(1, documentID);
ps.setString(2, key);
ps.setString(3, value);
ps.execute();
ps.close();
conn.commit();
}
private void updateDocumentProperty(long documentID, String key, String value) throws SQLException {
String sql = "UPDATE documentProperties SET xvalue = ? WHERE documentID = ? AND xkey = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setString(1, value);
s.setLong(2, documentID);
s.setString(3, key);
s.executeUpdate(sql);
s.close();
conn.commit();
}
private void createPathsTable() throws SQLException {
createTable("paths", "pathID BIGINT NOT NULL PRIMARY KEY, parentPathID BIGINT NOT NULL, name VARCHAR(1024) NOT NULL");
createIndex("pathsIndexParentID", false, "paths", "parentPathID ASC");
}
/**
* Creates a new path.
* @param pathName Name of the new path.
* @param parentPathID Parent path ID.
* @return ID of the new path.
* @throws SQLException
*/
public long addPath(String pathName, long parentPathID) throws SQLException {
if (parentPathID == -1) {
parentPathID = 0;
}
if (parentPathID != 0 && getPathName(parentPathID) == null) {
throw new IllegalArgumentException("There does not exist a parent path with ID " + parentPathID + "!");
}
long pathID = IdUtils.getUniqueID();
PreparedStatement ps = conn.prepareStatement("INSERT INTO paths (pathID, parentPathID, name) VALUES (?, ?, ?)");
ps.setLong(1, pathID);
ps.setLong(2, parentPathID);
ps.setString(3, pathName);
ps.execute();
ps.close();
conn.commit();
return pathID;
}
/**
* Returns the name of a path.
* @param pathID ID of the path.
* @return Name of the path or <i>null</i> if it does not exist.
* @throws SQLException
*/
public String getPathName(long pathID) throws SQLException {
String sql = "select * from paths where pathID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, pathID);
ResultSet rs = ps.executeQuery();
String name = null;
if (rs.next()) {
name = rs.getString("name");
}
rs.close();
ps.close();
return name;
}
/**
* Returns the parent path ID of a path.
* @param pathID ID of the path.
* @return Parent path ID.
* @throws SQLException
*/
public long getParentPath(long pathID) throws SQLException {
String sql = "select * from paths where pathID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, pathID);
ResultSet rs = ps.executeQuery();
long parentPathID = 0;
if (rs.next()) {
parentPathID = rs.getLong("parentPathID");
}
rs.close();
ps.close();
return parentPathID;
}
/**
* Returns a list of all child paths of a parent path.
* @param parentPathID Parent path ID.
* @return List of child paths.
* @throws SQLException
*/
public List<Pair<Long, String>> getChildPaths(long parentPathID) throws SQLException {
List<Pair<Long, String>> list = new ArrayList<Pair<Long, String>>();
String sql = "select * from paths where parentPathID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, parentPathID);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
long pathID = rs.getLong("pathID");
String name = rs.getString("name");
list.add(new Pair<Long, String>(pathID, name));
}
rs.close();
ps.close();
return list;
}
/**
* Deletes a document path. All documents within this path or the child paths are moved to the parent path.
* @param pathID Path ID.
* @throws SQLException
*/
public void deletePath(long pathID) throws SQLException {
checkSubPath(pathID);
long parentPathID = getParentPath(pathID);
deleteChildPaths(pathID);
moveDocumentsToOtherPath(pathID, parentPathID);
checkEmptyPath(pathID);
deletePathImmediately(pathID);
}
private void deletePathImmediately(long pathID) throws SQLException {
String sql = "DELETE FROM paths WHERE pathID = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setLong(1, pathID);
s.execute();
s.close();
conn.commit();
}
private void checkSubPath(long pathID) throws SQLException {
if (pathID == 0 || pathID == -1) {
throw new IllegalArgumentException("Can't delete root path!");
}
if (getPathName(pathID) == null) {
throw new IllegalArgumentException("Path with ID " + pathID + " does not exist!");
}
}
private void deleteChildPaths(long pathID) throws SQLException {
List<Pair<Long, String>> children = getChildPaths(pathID);
for (Pair<Long, String> childPath : children) {
deletePath(childPath.obj1);
}
}
private void moveDocumentsToOtherPath(long pathID, long parentPathID) throws SQLException {
List<Long> docs = getDocumentsFromPath(pathID);
for (long documentID : docs) {
changeDocumentPath(documentID, parentPathID);
}
}
private void checkEmptyPath(long pathID) throws SQLException {
if (!getDocumentsFromPath(pathID).isEmpty()) {
throw new IllegalArgumentException("Path with ID " + pathID + " is not empty (documents exist)!");
}
if (!getChildPaths(pathID).isEmpty()) {
throw new IllegalArgumentException("Path with ID " + pathID + " is not empty (child paths exist)!");
}
}
private void createLabelsTable() throws SQLException {
createTable("labels", "labelID BIGINT NOT NULL PRIMARY KEY, name VARCHAR(1024) NOT NULL");
createIndex("labelsIndexName", true, "labels", "name ASC");
}
/**
* Creates a new label.
* @param labelName Name of the new label.
* @return ID of the new label.
* @throws SQLException
*/
public long createLabel(String labelName) throws SQLException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO labels (labelID, name) VALUES (?, ?)");
long labelID = IdUtils.getUniqueID();
ps.setLong(1, labelID);
ps.setString(2, labelName);
ps.execute();
ps.close();
conn.commit();
return labelID;
}
/**
* Changes the name of an existing label.
* @param labelID Label ID.
* @param newLabelName New label name.
* @throws SQLException
*/
public void changeLabelName(long labelID, String newLabelName) throws SQLException {
String sql = "UPDATE labels SET name = ? WHERE labelID = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setString(1, newLabelName);
s.setLong(2, labelID);
s.executeUpdate();
s.close();
conn.commit();
}
/**
* Returns a list of all labels.
* @return List of all label IDs.
* @throws SQLException
*/
public List<Long> getAllLabels() throws SQLException {
List<Long> list = new ArrayList<Long>();
String sql = "SELECT * FROM labels ORDER BY name";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
list.add(rs.getLong("labelID"));
}
rs.close();
ps.close();
return list;
}
/**
* Returns the name of a label.
* @param labelID Label ID.
* @return Name of the label or <i>null</i>, if the label does not exist.
* @throws SQLException
*/
public String getLabelName(long labelID) throws SQLException {
String sql = "SELECT * FROM labels WHERE labelID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, labelID);
ResultSet rs = ps.executeQuery();
String name = null;
if (rs.next()) {
name = rs.getString("name");
}
rs.close();
ps.close();
return name;
}
/**
* Removes a label (a) from all documents, (b) from the label table.
* @param labelID Label ID.
* @throws SQLException
*/
public void deleteLabel(long labelID) throws SQLException {
deleteLabelFromLabeling(labelID);
deleteLabelFromLabels(labelID);
}
private void deleteLabelFromLabeling(long labelID) throws SQLException {
String sql = "DELETE FROM labeling WHERE labelID = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setLong(1, labelID);
s.execute();
s.close();
conn.commit();
}
private void deleteLabelFromLabels(long labelID) throws SQLException {
String sql = "DELETE FROM labels WHERE labelID = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setLong(1, labelID);
s.execute();
s.close();
conn.commit();
}
private void createLabelingTable() throws SQLException {
createTable("labeling", "documentID BIGINT NOT NULL, labelID BIGINT NOT NULL");
createIndex("labelingIndexFull", true, "labeling", "documentID ASC, labelID ASC");
createIndex("labelingIndexDocuments", false, "labeling", "documentID ASC");
createIndex("labelingIndexLabels", false, "labeling", "labelID ASC");
}
/**
* Adds a label to a document.
* @param documentID Document ID.
* @param labelID Label ID.
* @throws SQLException
*/
public void addLabelToDocument(long documentID, long labelID) throws SQLException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO labeling (documentID, labelID) VALUES (?, ?)");
ps.setLong(1, documentID);
ps.setLong(2, labelID);
ps.execute();
ps.close();
conn.commit();
}
/**
* Returns all documents of a label.
* @param labelID Label ID.
* @return List of document IDs.
* @throws SQLException
*/
public List<Long> getDocumentsFromLabel(long labelID) throws SQLException {
List<Long> list = new ArrayList<Long>();
String sql = "SELECT * FROM labeling WHERE labelID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, labelID);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
long documentID = rs.getLong("documentID");
list.add(documentID);
}
rs.close();
ps.close();
return list;
}
/**
* Returns all labels of a document.
* @param documentID Document ID.
* @return List of label IDs.
* @throws SQLException
*/
public List<Long> getLabelsFromDocument(long documentID) throws SQLException {
List<Long> list = new ArrayList<Long>();
String sql = "SELECT * FROM labeling WHERE documentID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, documentID);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
long labelID = rs.getLong("labelID");
list.add(labelID);
}
rs.close();
ps.close();
return list;
}
/**
* Removes a label from a document.
* @param documentID Document ID.
* @param labelID Label ID.
* @throws SQLException
*/
public void removeLabelFromDocument(long documentID, long labelID) throws SQLException {
String sql = "DELETE FROM labeling WHERE labelID = ? and documentID = ?";
PreparedStatement s = conn.prepareStatement(sql);
s.setLong(1, labelID);
s.setLong(2, documentID);
s.execute();
s.close();
conn.commit();
}
/**
* Shutdown database.
*/
public void close() {
try {
if (conn != null) {
conn.close();
}
} catch (Exception exc) {
// LogUtils.log(exc);
}
try {
DriverManager.getConnection("jdbc:derby:" + dbPath + ";shutdown=true");
} catch (Exception exc) {
// LogUtils.log(exc);
}
}
/**
* Create a new table with column definition.
* @param tableName Table name.
* @param columnDefinitions Column definitions.
* @throws SQLException
*/
private void createTable(String tableName, String columnDefinitions) throws SQLException {
if (!doesTableExist(tableName)) {
String createTable = "CREATE TABLE " + tableName + " ( " + columnDefinitions + " )";
System.out.println(createTable);
Statement s = conn.createStatement();
s.executeUpdate(createTable);
s.close();
conn.commit();
}
}
/**
* Tests if the given table exists.
* @param tableName Table name to check.
* @return <i>true</i> if the table exists.
* @throws SQLException
*/
private boolean doesTableExist(String tableName) throws SQLException {
DatabaseMetaData dmd = conn.getMetaData();
ResultSet rs = dmd.getTables(null, null, tableName.toUpperCase(), null);
boolean b = rs.next();
rs.close();
return b;
}
/**
* Prints the given selection on the System output pipe.
* @param selection
* @throws SQLException
*/
public void printSelection(String selection) throws SQLException {
System.out.println("SELECTION: " + selection);
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery(selection);
int id = 0;
while (rs.next()) {
ResultSetMetaData metadata = rs.getMetaData();
int count = metadata.getColumnCount();
System.out.println(" " + ++id + ". with " + count + " columns:");
for (int i = 1; i <= count; i++) {
System.out.println(" [" + i + "] " + metadata.getColumnName(i) + " => " + rs.getObject(i));
}
}
rs.close();
s.close();
}
/**
* Creates a new index for a table.
* @param indexName Name of the index.
* @param unique <i>true</i> for a unique index.
* @param tableName Name of the table.
* @param columnDefinitions Column definitions.
* @throws SQLException
*/
private void createIndex(String indexName, boolean unique, String tableName, String columnDefinitions) throws SQLException {
if (!doesIndexExist(indexName)) {
String sUnique = unique ? "UNIQUE " : "";
String createIndex = "CREATE " + sUnique + "INDEX " + indexName + " ON " + tableName + " ( " + columnDefinitions + " )";
System.out.println(createIndex);
Statement s = conn.createStatement();
s.executeUpdate(createIndex);
s.close();
conn.commit();
}
}
/**
* Check if an index exists in the database.
* @param indexName Name of the index.
* @return <i>true</i> if the index has been found.
* @throws SQLException
*/
private boolean doesIndexExist(String indexName) throws SQLException {
String sql = "select * from SYS.SYSCONGLOMERATES where CONGLOMERATENAME = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, indexName.toUpperCase());
ResultSet rs = ps.executeQuery();
boolean b = rs.next();
rs.close();
ps.close();
return b;
}
/**
* Returns a list of all document IDs which have no label.
* @return List of document IDs.
* @throws SQLException
*/
public List<Long> getDocumentsWithoutLabel() throws SQLException {
List<Long> list = new ArrayList<Long>();
String sql = "SELECT * FROM documents WHERE documentID NOT IN (SELECT DISTINCT(documentID) FROM labeling)";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
long docID = rs.getLong("documentID");
list.add(docID);
}
rs.close();
ps.close();
return list;
}
}