/*
CloudTrail Viewer, is a Java desktop application for reading AWS CloudTrail logs
files.
Copyright (C) 2017 Mark P. Haskins
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 (at your option) 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 io.haskins.java.cloudtrailviewer.service;
import io.haskins.java.cloudtrailviewer.model.dao.CurrentDbVersion;
import io.haskins.java.cloudtrailviewer.model.dao.ResultSetRow;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
* Service that provides DAO functionality
*
* Created by markhaskins on 05/01/2017.
*/
@Service
public class DatabaseService {
// this is not done in the constructor to handle circular dependency
@Autowired private MigrationService migrationService;
private final static Logger LOGGER = Logger.getLogger("CloudTrail");
/**
* Checks if there any any updates to the database required and applies them.
*/
public void sync() {
CurrentDbVersion currentVersion = new CurrentDbVersion();
Connection dbTest = getDbConnection();
if (dbTest == null) {
System.out.println("Creating Database");
String url = getDbUrl();
Properties properties = new Properties();
properties.put("create", "true");
try {
DriverManager.getConnection(url, properties);
}
catch (SQLException ex1) {
LOGGER.log(Level.WARNING, "Unable to get connection to database", ex1);
}
} else {
currentVersion.setDbVersion(getCurrentDbVersion());
}
Connection conn = getDbConnection();
if (conn != null) {
migrationService.createVersion1(conn, currentVersion);
migrationService.createVersion2(conn, currentVersion);
migrationService.createVersion3(conn, currentVersion);
migrationService.createVersion4(conn, currentVersion);
migrationService.createVersion5(conn, currentVersion);
migrationService.createVersion6(conn, currentVersion);
migrationService.createVersion7(conn, currentVersion);
migrationService.createVersion8(conn, currentVersion);
}
}
/**
* Executes the query and then returns the results as a List of ResultSetRow
* objects. All SQL objects (Connect, Statement and ResultSet) will be closed
* before the result is returned.
* @param query SQL statement to run.
* @return a List of results. If there were no results, or there was a problem
* then the list will be empty.
*/
public List<ResultSetRow> executeCursorStatement(String query) {
List<ResultSetRow> rows = new ArrayList<>();
Connection conn = getDbConnection();
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
while (rs.next()) {
HashMap<String, Object> row = new HashMap<>(columns);
for (int i = 1; i <= columns; ++i) {
row.put(md.getColumnName(i), rs.getObject(i));
}
rows.add(new ResultSetRow(row));
}
}
catch (SQLException e) {
LOGGER.log(Level.WARNING, "Couldn't execute statement", e);
}
return rows;
}
/**
* Executes the passed statement and returns a single integer as the result.
* @param query SQL statement to execute
* @param columnName Column name that contains the result
* @return The result of the query or -1 if no result was found or there was
* a problem.
*/
private int executeIntStatement(String query, String columnName) {
int retVal = -1;
Connection conn = getDbConnection();
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
if (rs.next()) {
retVal = rs.getInt(columnName);
}
}
catch (SQLException e) {
LOGGER.log(Level.WARNING, "Couldn't execute statement", e);
}
return retVal;
}
/**
* Executes the passed statement and returns a single String as the result.
* @param query SQL statement to execute
* @param columnName Column name that contains the result
* @return The result of the query or -NULL if no result was found or there was
* a problem.
*/
public String executeStringStatement(String query, String columnName) {
String retVal = null;
Connection conn = getDbConnection();
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
if (rs.next()) {
retVal = rs.getString(columnName);
}
}
catch (SQLException e) {
LOGGER.log(Level.WARNING, "Couldn't execute statement", e);
}
return retVal;
}
/**
* Executes an UPDATE or INSERT statement.
* @param query SQL statement to run
* @return returns either the number of rows affected, or in the case of an UPDATE any autogenerated value
*/
public int doInsertUpdate(String query) {
int updated = -1;
Connection conn = getDbConnection();
try (Statement stmt = conn.createStatement()) {
int affectedRows = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
if (affectedRows == 0) {
updated = affectedRows;
} else {
try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
if (generatedKeys != null && generatedKeys.next()) {
updated = generatedKeys.getInt(1);
}
}
}
}
catch (SQLException e) {
LOGGER.log(Level.WARNING, "Couldn't execute statement", e);
}
return updated;
}
/**
* performs an Execute command
* @param query SQL statement to run
*/
void doExecute(String query) {
Connection conn = getDbConnection();
try (Statement stmt = conn.createStatement()) {
stmt.execute(query);
}
catch (SQLException e) {
LOGGER.log(Level.WARNING, "Couldn't execute statement", e);
}
}
/**
* get a valid DB Connection
* @return A DB Connection or NULL if there was a problem
*/
private Connection getDbConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(getDbUrl(), new Properties());
}
catch (SQLException e) {
LOGGER.log(Level.WARNING, "Couldn't execute statement", e);
}
return conn;
}
/**
* Returns the URL of that database
* @return DB connection URL
*/
private String getDbUrl() {
String userHomeDir = System.getProperty("user.home", ".");
String systemDir = userHomeDir + "/.cloudtrailviewer/prefs.db";
StringBuilder url = new StringBuilder();
url.append("jdbc:derby:");
url.append(systemDir);
return url.toString();
}
/**
* Returns the current version of the database
* @return version number of database
*/
public int getCurrentDbVersion() {
int currentVersion = 0;
String query = "SELECT db_version FROM db_properties WHERE id = 1";
int retVal = executeIntStatement(query, "db_version");
if (retVal != -1) {
currentVersion = retVal;
}
return currentVersion;
}
}