package ru.bmstu.datalog.storage;
import java.io.File;
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 java.util.HashMap;
import java.util.HashSet;
import ru.bmstu.datalog.data.Argument;
import ru.bmstu.datalog.data.Predicate;
/**
* Realize of onnection to access the database.
* @author art-vybor
*/
class DbAccessor {
private Connection connection;
private static String bdFolder = "sqlDB";
private String bdName;
private boolean inMemory;
public DbAccessor(String _bdName, boolean _inMemory) {
bdName = _bdName;
connection = null;
inMemory = _inMemory;
}
/**
* Opens a new connection.
* @throws SQLException
* @throws ClassNotFoundException
*/
public void open() throws SQLException, ClassNotFoundException {
Class.forName("org.sqlite.JDBC");
if (inMemory) {
connection = DriverManager.getConnection("jdbc:sqlite::memory:");
} else {
File myPath = new File(bdFolder);
myPath.mkdirs();
connection = DriverManager.getConnection("jdbc:sqlite:" + bdFolder + "/" + bdName);
}
}
/**
* Closes the connection.
* @throws SQLException
*/
public void close() throws SQLException {
connection.close();
}
/**
* Clears the database.
* @throws SQLException
* @throws ClassNotFoundException
*/
public void clear() throws SQLException, ClassNotFoundException {
ResultSet resultSet = connection.createStatement().executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%' ");
ArrayList<String> tableList = new ArrayList<String>();
while (resultSet.next()) {
tableList.add(resultSet.getString("name"));
}
//reopen a database connection, for the completion of all operations
close();
open();
for (String name : tableList) {
connection.createStatement().executeUpdate("DROP TABLE " + name);
}
}
/**
* Add a predicate to the database.
* @param predicate
* @throws SQLException
*/
public void insertPredicate(Predicate predicate) throws SQLException {
if (!isTableExist(getTableName(predicate)))
createTable(predicate);
PreparedStatement statement = connection.prepareStatement(getInsertQuery(predicate));
for (int i = 0; i < predicate.size(); ++i) {
statement.setString(i+1, predicate.get(i).toString());
}
statement.executeUpdate();
}
/**
* Checks whether there is a table with the specified name.
* @param tableName
* @return {@link Boolean}
* @throws SQLException
*/
private boolean isTableExist(String tableName) throws SQLException {
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name = '" + tableName + "'");
return resultSet.next();
}
/**
* Creates a table based on the structure of the predicate.
* @param predicate
* @throws SQLException
*/
private void createTable(Predicate predicate) throws SQLException {
Statement statement = connection.createStatement();
statement.executeUpdate(getCreateTableQuery(predicate));
}
/**
* Returns the set of predicate, matching with the input predicate.
* @param predicate
* @return {@link HashSet<Predicate>}
* @throws SQLException
*/
public HashSet<Predicate> selectPredicate(Predicate predicate) throws SQLException {
HashSet<Predicate> answer = new HashSet<Predicate>();
if (!isTableExist(getTableName(predicate))) return answer;
PreparedStatement statement = connection.prepareStatement(getSelectQuery(predicate));
int curArg = 1;
for (int i = 0; i < predicate.size(); ++i) {
if (predicate.get(i).isConstant()) {
statement.setString(curArg++, predicate.get(i).toString());
}
}
ResultSet resultSet = statement.executeQuery();
while(resultSet.next()) {
ArrayList<Argument> args = new ArrayList<Argument>();
for (int i = 0; i < predicate.size(); ++i)
args.add(new Argument(resultSet.getString("f" + i)));
answer.add(new Predicate(predicate.getName(), args));
}
return answer;
}
/**
* Returns the name of the table corresponds to the predicate.
* @param predicate
* @return {@link String}
*/
private String getTableName(Predicate predicate) {
return predicate.getName() + "_" + predicate.size();
}
/**
* Returns the request to insert predicate.
* @param predicate
* @return {@link String}
*/
private String getInsertQuery(Predicate predicate) {
StringBuilder query = new StringBuilder();
query.append("INSERT INTO ");
query.append(getTableName(predicate));
query.append("(");
for (int i = 0; i < predicate.size(); ++i) {
query.append("f");
query.append(i);
if (i != predicate.size() - 1)
query.append(", ");
}
query.append(") ");
query.append("VALUES (");
for (int i = 0; i < predicate.size(); ++i) {
query.append("?");
if (i != predicate.size() - 1)
query.append(", ");
}
query.append(")");
return query.toString();
}
/**
* Returns the request to select by predicate, variables are not ignored.
* @param predicate
* @return {@link String}
*/
private String getSelectQuery(Predicate predicate) {
StringBuilder query = new StringBuilder();
HashMap<Integer, Integer> hashVar = new HashMap<Integer, Integer>();
query.append("SELECT * FROM ");
query.append(getTableName(predicate));
query.append(" WHERE ");
boolean isAndUsed = false;
for (int i = 0; i < predicate.size(); ++i) {
Argument arg = predicate.get(i);
if (arg.isConstant()) {
if (isAndUsed) query.append(" AND ");
else isAndUsed = true;
query.append("f");
query.append(i);
query.append("=?");
} else {
int var = arg.getVariable();
if (hashVar.containsKey(var)) {
if (isAndUsed) query.append(" AND ");
else isAndUsed = true;
query.append("f");
query.append(i);
query.append("=f");
query.append(hashVar.get(var));
} else {
hashVar.put(var, i);
}
}
}
if (!isAndUsed) query.delete(query.length() - 4, query.length());
return query.toString();
}
/**
* Returns the request to create table.
* @param predicate
* @return {@link String}
*/
private String getCreateTableQuery(Predicate predicate) {
StringBuilder query = new StringBuilder();
query.append("CREATE TABLE IF NOT EXISTS ");
query.append(getTableName(predicate));
query.append(" ( ");
for (int i = 0; i < predicate.size(); ++i) {
query.append("f");
query.append(i);
query.append(" varchar(10)");
if (i != predicate.size() - 1)
query.append(", ");
}
query.append(", UNIQUE (");
for (int i = 0; i < predicate.size(); ++i) {
query.append("f");
query.append(i);
if (i != predicate.size() - 1)
query.append(", ");
}
query.append(")");
query.append(")");
return query.toString();
}
public int getNumOfPredicate() {
int res = 0;
try {
ResultSet resultSet = connection.createStatement().executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%' ");
while (resultSet.next()) {
String name = resultSet.getString("name");
ResultSet _resultSet = connection.createStatement().executeQuery("SELECT Count(*) FROM " + name);
while (_resultSet.next()) {
res += _resultSet.getInt(1);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
/**
* Just for debug
*/
@Override
public String toString() {
StringBuilder strBuilder = new StringBuilder();
try {
ResultSet resultSet = connection.createStatement().executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%' ");
while (resultSet.next()) {
String name = resultSet.getString("name");
ResultSet _resultSet = connection.createStatement().executeQuery("SELECT * FROM " + name);
strBuilder.append(name);
strBuilder.append(":\n");
while (_resultSet.next()) {
strBuilder.append(" ");
for (int i = 0; i < Integer.parseInt(name.substring(name.lastIndexOf("_") + 1)); ++i) {
strBuilder.append(_resultSet.getString(i+1));
strBuilder.append("\t");
}
strBuilder.append("\n");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return strBuilder.toString();
}
}