19 package org.sleuthkit.datamodel;
21 import java.sql.PreparedStatement;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.sql.Statement;
25 import java.util.logging.Level;
26 import java.util.logging.Logger;
52 void process(ResultSet resultSet);
82 boolean doesColumnExists =
false;
85 doesColumnExists =
columnExists(tableName, columnName, localTrans);
90 if (null != localTrans) {
94 logger.log(Level.SEVERE,
"Failed to rollback transaction after exception", ex);
99 return doesColumnExists;
115 Statement statement = null;
116 ResultSet resultSet = null;
118 CaseDbConnection connection = transaction.getConnection();
119 statement = connection.createStatement();
121 String tableInfoQuery =
"PRAGMA table_info(%s)";
122 resultSet = statement.executeQuery(String.format(tableInfoQuery, tableName));
123 while (resultSet.next()) {
124 if (resultSet.getString(
"name").equalsIgnoreCase(columnName)) {
131 String tableInfoQueryTemplate =
"SELECT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='%s' AND column_name='%s')";
132 resultSet = statement.executeQuery(String.format(tableInfoQueryTemplate, tableName.toLowerCase(), columnName.toLowerCase()));
133 if (resultSet.next()) {
134 columnExists = resultSet.getBoolean(1);
138 catch (SQLException ex) {
139 throw new TskCoreException(
"Error checking if column " + columnName +
"exists ", ex);
142 if (resultSet != null) {
145 }
catch (SQLException ex2) {
146 logger.log(Level.WARNING,
"Failed to to close resultset after checking column", ex2);
149 closeStatement(statement);
164 boolean doesTableExist =
false;
167 doesTableExist =
tableExists(tableName, localTrans);
172 if (null != localTrans) {
176 logger.log(Level.SEVERE,
"Failed to rollback transaction after exception", ex);
181 return doesTableExist;
196 Statement statement = null;
197 ResultSet resultSet = null;
199 CaseDbConnection connection = transaction.getConnection();
200 statement = connection.createStatement();
202 resultSet = statement.executeQuery(
"SELECT name FROM sqlite_master WHERE type='table'");
203 while (resultSet.next()) {
204 if (resultSet.getString(
"name").equalsIgnoreCase(tableName)) {
211 String tableInfoQueryTemplate =
"SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='%s')";
212 resultSet = statement.executeQuery(String.format(tableInfoQueryTemplate, tableName.toLowerCase()));
213 if (resultSet.next()) {
214 tableExists = resultSet.getBoolean(1);
218 catch (SQLException ex) {
219 throw new TskCoreException(
"Error checking if table " + tableName +
"exists ", ex);
221 if (resultSet != null) {
224 }
catch (SQLException ex2) {
225 logger.log(Level.WARNING,
"Failed to to close resultset after checking table", ex2);
228 closeStatement(statement);
248 validateTableName(tableName);
249 validateSQL(tableSchema);
251 CaseDbConnection connection = tskDB.getConnection();
254 Statement statement = null;
255 String createSQL =
"CREATE TABLE IF NOT EXISTS " + tableName +
" " + tableSchema;
257 statement = connection.createStatement();
258 statement.execute(createSQL);
259 }
catch (SQLException ex) {
262 closeStatement(statement);
285 if (null != localTrans) {
289 logger.log(Level.SEVERE,
"Failed to rollback transaction after exception", ex);
306 validateTableName(tableName);
307 validateSQL(alterSQL);
309 CaseDbConnection connection = transaction.getConnection();
311 Statement statement = null;
312 String sql =
"ALTER TABLE " + tableName +
" " + alterSQL;
315 statement = connection.createStatement();
316 statement.execute(sql);
317 }
catch (SQLException ex) {
322 alterSQL.toLowerCase().contains(
"add column") &&
323 ex.getMessage().toLowerCase().contains(
"duplicate column name")) {
324 logger.log(Level.WARNING, String.format(
"Column being added by SQL = %s already exists in table %s", alterSQL, tableName));
327 throw new TskCoreException(String.format(
"Error altering table %s with SQL = %s", tableName, sql), ex);
329 closeStatement(statement);
349 validateTableName(tableName);
350 validateIndexName(indexName);
351 validateSQL(colsSQL);
353 CaseDbConnection connection = tskDB.getConnection();
356 Statement statement = null;
357 String indexSQL =
"CREATE INDEX IF NOT EXISTS " + indexName +
" ON " + tableName +
" " + colsSQL;
359 statement = connection.createStatement();
360 statement.execute(indexSQL);
361 }
catch (SQLException ex) {
364 closeStatement(statement);
384 long rowId =
insert(tableName, sql, localTrans);
389 if (null != localTrans) {
393 logger.log(Level.SEVERE,
"Failed to rollback transaction after exception", ex);
419 validateTableName(tableName);
422 CaseDbConnection connection = transaction.getConnection();
424 PreparedStatement statement = null;
426 String insertSQL =
"INSERT";
428 insertSQL +=
" OR IGNORE";
431 insertSQL = insertSQL+
" INTO " + tableName +
" " + sql;
433 statement = connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
434 connection.executeUpdate(statement);
436 resultSet = statement.getGeneratedKeys();
437 if (resultSet.next()) {
438 rowId = resultSet.getLong(1);
440 }
catch (SQLException ex) {
441 throw new TskCoreException(
"Error inserting row in table " + tableName +
" with sql = "+ insertSQL, ex);
443 closeStatement(statement);
472 if (null != localTrans) {
476 logger.log(Level.SEVERE,
"Failed to rollback transaction after exception", ex);
502 validateTableName(tableName);
505 CaseDbConnection connection = transaction.getConnection();
507 PreparedStatement statement = null;
509 String insertSQL =
"INSERT";
511 insertSQL +=
" OR REPLACE";
514 insertSQL +=
" INTO " + tableName +
" " + sql;
516 statement = connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
517 connection.executeUpdate(statement);
519 resultSet = statement.getGeneratedKeys();
521 rowId = resultSet.getLong(1);
522 }
catch (SQLException ex) {
523 throw new TskCoreException(
"Error inserting row in table " + tableName +
" with sql = "+ insertSQL, ex);
525 closeStatement(statement);
542 update(tableName, sql, localTrans);
546 if (null != localTrans) {
550 logger.log(Level.SEVERE,
"Failed to rollback transaction after exception", ex);
569 validateTableName(tableName);
572 CaseDbConnection connection = transaction.getConnection();
574 Statement statement = null;
575 String updateSQL =
"UPDATE " + tableName +
" " + sql;
578 statement = connection.createStatement();
579 statement.executeUpdate(updateSQL);
580 }
catch (SQLException ex) {
583 closeStatement(statement);
597 if (queryCallback == null) {
603 CaseDbConnection connection = tskDB.getConnection();
606 Statement statement = null;
608 String selectSQL =
"SELECT " + sql;
610 statement = connection.createStatement();
611 resultSet = statement.executeQuery(selectSQL);
612 queryCallback.process(resultSet);
613 }
catch (SQLException ex) {
616 closeStatement(statement);
631 validateTableName(tableName);
634 CaseDbConnection connection = tskDB.getConnection();
637 Statement statement = null;
638 String deleteSQL =
"DELETE FROM " + tableName +
" " + sql;
640 statement = connection.createStatement();
641 statement.executeUpdate(deleteSQL);
642 }
catch (SQLException ex) {
643 throw new TskCoreException(
"Error deleting row from table " + tableName, ex);
645 closeStatement(statement);
661 if (
SleuthkitCase.getCoreTableNames().contains(tableName.toLowerCase())) {
662 throw new TskCoreException(
"Attempt to modify a core TSK table " + tableName);
664 if (tableName.toLowerCase().startsWith(
"tsk_")) {
665 throw new TskCoreException(
"Modifying tables with tsk_ prefix is not allowed. ");
677 private void validateIndexName(String indexName)
throws TskCoreException {
679 if (indexName.isEmpty()) {
680 throw new TskCoreException(
"Invalid index name " + indexName);
683 if (SleuthkitCase.getCoreIndexNames().contains(indexName.toLowerCase())) {
684 throw new TskCoreException(
"Attempt to modify a core TSK index " + indexName);
695 private void validateSQL(String sql)
throws TskCoreException {
void process(ResultSet resultSet)
boolean tableExists(String tableName, CaseDbTransaction transaction)
long insertOrUpdate(final String tableName, final String sql)
CaseDbTransaction beginTransaction()
void update(final String tableName, final String sql)
long insert(final String tableName, final String sql, final CaseDbTransaction transaction)
void alterTable(final String tableName, final String alterSQL, final CaseDbTransaction transaction)
boolean tableExists(String tableName)
void createIndex(final String indexName, final String tableName, final String colsSQL)
boolean columnExists(String tableName, String columnName)
void releaseSingleUserCaseReadLock()
void createTable(final String tableName, final String tableSchema)
long insertOrUpdate(final String tableName, final String sql, final CaseDbTransaction transaction)
void acquireSingleUserCaseWriteLock()
void releaseSingleUserCaseWriteLock()
void alterTable(final String tableName, final String alterSQL)
void select(final String sql, final CaseDbAccessQueryCallback queryCallback)
void update(final String tableName, final String sql, CaseDbTransaction transaction)
void acquireSingleUserCaseReadLock()
long insert(final String tableName, final String sql)
boolean columnExists(String tableName, String columnName, CaseDbTransaction transaction)