@Override
public int size(final DatabaseTable table) throws DatabaseException {
preOperationCheck();
final StringBuilder sb = new StringBuilder();
sb.append("SELECT COUNT(" + KEY_COLUMN + ") FROM ").append(table.toString());
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sb.toString());
resultSet = statement.executeQuery();
if (resultSet.next()) {
return resultSet.getInt(1);
}
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "size operation failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
} finally {
close(statement);
close(resultSet);
}
updateStats(true, false);
return 0;
}
/**
* Adds a feature to the PrimaryKey attribute of the DatabaseSchemaUtils class
*
* @param db The feature to be added to the PrimaryKey attribute
* @param primaryKeys The feature to be added to the PrimaryKey attribute
* @exception SQLException Description of the Exception
*/
public static void addPrimaryKey(Connection db, PropertyMapList mappings, HashMap primaryKeys)
throws SQLException {
Iterator i = primaryKeys.keySet().iterator();
while (i.hasNext()) {
String elementName = (String) i.next();
PropertyMap thisMap = (PropertyMap) mappings.getMap(elementName);
if (thisMap != null) {
System.out.println("\nElement name: " + elementName);
DatabaseTable dbTable = new DatabaseTable(thisMap.getTable(), thisMap.getSequence());
dbTable.setDbType(DatabaseUtils.getType(db));
Iterator properties = thisMap.iterator();
while (properties.hasNext()) {
Property thisProperty = (Property) properties.next();
if (!thisProperty.getField().equals(thisMap.getUniqueField())) {
DatabaseColumn column =
new DatabaseColumn(thisProperty.getField(), thisProperty.getSqlType());
if (thisProperty.hasLookupValue()) {
PropertyMap lookupMap = (PropertyMap) mappings.getMap(thisProperty.getLookupValue());
column.setReferenceTable(lookupMap.getTable());
column.setReferenceColumn(lookupMap.getUniqueField());
}
column.setAllowsNull(thisProperty.allowsNull());
if (thisProperty.getSize() != null) {
column.setSize(thisProperty.getSize());
}
if (thisProperty.hasDefaultValue()) {
column.setDefaultValue(thisProperty.getDefaultValue());
}
// print(column.toString());
dbTable.addColumn(column);
}
}
System.out.println("Adding primary key...");
DatabaseSchemaUtils.addPrimaryKey(db, dbTable, (String) primaryKeys.get(elementName));
}
}
}
private static void checkIfTableExists(final Connection connection, final DatabaseTable table)
throws SQLException {
final StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + " = '0'");
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery(sb.toString());
} finally {
close(statement);
close(resultSet);
}
}
private ResultSet init() throws DatabaseException {
final StringBuilder sb = new StringBuilder();
sb.append("SELECT " + KEY_COLUMN + " FROM ").append(table.toString());
try {
final PreparedStatement statement = connection.prepareStatement(sb.toString());
return statement.executeQuery();
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "get iterator failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
}
}
@Override
public String get(final DatabaseTable table, final String key) throws DatabaseException {
if (traceLogging) {
LOGGER.trace("attempting get operation for table=" + table + ", key=" + key);
}
preOperationCheck();
final StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + " = ?");
PreparedStatement statement = null;
ResultSet resultSet = null;
String returnValue = null;
try {
statement = connection.prepareStatement(sb.toString());
statement.setString(1, key);
statement.setMaxRows(1);
resultSet = statement.executeQuery();
if (resultSet.next()) {
returnValue = resultSet.getString(VALUE_COLUMN);
}
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "get operation failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
} finally {
close(statement);
close(resultSet);
}
if (traceLogging) {
final LinkedHashMap<String, Object> debugOutput = new LinkedHashMap<>();
debugOutput.put("table", table);
debugOutput.put("key", key);
debugOutput.put("result", returnValue);
LOGGER.trace(
"get operation result: " + JsonUtil.serializeMap(debugOutput, JsonUtil.Flag.PrettyPrint));
}
updateStats(true, false);
return returnValue;
}
@Override
public boolean remove(final DatabaseTable table, final String key) throws DatabaseException {
if (traceLogging) {
LOGGER.trace("attempting remove operation for table=" + table + ", key=" + key);
}
boolean result = contains(table, key);
if (result) {
final StringBuilder sqlText = new StringBuilder();
sqlText.append("DELETE FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + "=?");
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sqlText.toString());
statement.setString(1, key);
statement.executeUpdate();
LOGGER.trace("remove operation succeeded for table=" + table + ", key=" + key);
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "remove operation failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
} finally {
close(statement);
}
}
if (traceLogging) {
final Map<String, Object> debugOutput = new LinkedHashMap<>();
debugOutput.put("table", table);
debugOutput.put("key", key);
debugOutput.put("result", result);
LOGGER.trace(
"remove operation result: "
+ JsonUtil.serializeMap(debugOutput, JsonUtil.Flag.PrettyPrint));
}
updateStats(true, false);
return result;
}
private synchronized void init() throws DatabaseException {
status = PwmService.STATUS.OPENING;
LOGGER.debug("opening connection to database " + this.dbConfiguration.getConnectionString());
connection = openDB(dbConfiguration);
for (final DatabaseTable table : DatabaseTable.values()) {
initTable(connection, table, dbConfiguration);
}
status = PwmService.STATUS.OPEN;
try {
put(
DatabaseTable.PWM_META,
KEY_ENGINE_START_PREFIX + instanceID,
PwmConstants.DEFAULT_DATETIME_FORMAT.format(new java.util.Date()));
} catch (DatabaseException e) {
final String errorMsg = "error writing engine start time value: " + e.getMessage();
throw new DatabaseException(new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg));
}
}
@Override
public boolean put(final DatabaseTable table, final String key, final String value)
throws DatabaseException {
preOperationCheck();
if (traceLogging) {
LOGGER.trace("attempting put operation for table=" + table + ", key=" + key);
}
if (!contains(table, key)) {
final String sqlText =
"INSERT INTO "
+ table.toString()
+ "("
+ KEY_COLUMN
+ ", "
+ VALUE_COLUMN
+ ") VALUES(?,?)";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sqlText);
statement.setString(1, key);
statement.setString(2, value);
statement.executeUpdate();
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "put operation failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
} finally {
close(statement);
}
return false;
}
final String sqlText =
"UPDATE " + table.toString() + " SET " + VALUE_COLUMN + "=? WHERE " + KEY_COLUMN + "=?";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sqlText);
statement.setString(1, value);
statement.setString(2, key);
statement.executeUpdate();
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "put operation failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
} finally {
close(statement);
}
if (traceLogging) {
final Map<String, Object> debugOutput = new LinkedHashMap<>();
debugOutput.put("table", table);
debugOutput.put("key", key);
debugOutput.put("value", value);
LOGGER.trace(
"put operation result: " + JsonUtil.serializeMap(debugOutput, JsonUtil.Flag.PrettyPrint));
}
updateStats(false, true);
return true;
}
private static void initTable(
final Connection connection, final DatabaseTable table, final DBConfiguration dbConfiguration)
throws DatabaseException {
try {
checkIfTableExists(connection, table);
LOGGER.trace("table " + table + " appears to exist");
} catch (SQLException e) { // assume error was due to table missing;
{
final StringBuilder sqlString = new StringBuilder();
sqlString.append("CREATE table ").append(table.toString()).append(" (").append("\n");
sqlString
.append(" " + KEY_COLUMN + " ")
.append(dbConfiguration.getColumnTypeKey())
.append("(")
.append(KEY_COLUMN_LENGTH)
.append(") NOT NULL PRIMARY KEY,")
.append("\n");
sqlString
.append(" " + VALUE_COLUMN + " ")
.append(dbConfiguration.getColumnTypeValue())
.append(" ");
sqlString.append("\n");
sqlString.append(")").append("\n");
LOGGER.trace(
"attempting to execute the following sql statement:\n " + sqlString.toString());
Statement statement = null;
try {
statement = connection.createStatement();
statement.execute(sqlString.toString());
LOGGER.debug("created table " + table.toString());
} catch (SQLException ex) {
LOGGER.error("error creating new table " + table.toString() + ": " + ex.getMessage());
} finally {
close(statement);
}
}
{
final String indexName = table.toString() + "_IDX";
final StringBuilder sqlString = new StringBuilder();
sqlString.append("CREATE index ").append(indexName);
sqlString.append(" ON ").append(table.toString());
sqlString.append(" (").append(KEY_COLUMN).append(")");
Statement statement = null;
LOGGER.trace(
"attempting to execute the following sql statement:\n " + sqlString.toString());
try {
statement = connection.createStatement();
statement.execute(sqlString.toString());
LOGGER.debug("created index " + indexName);
} catch (SQLException ex) {
LOGGER.error("error creating new index " + indexName + ": " + ex.getMessage());
} finally {
close(statement);
}
}
}
}
/**
* Adds a feature to the PrimaryKey attribute of the DatabaseSchemaUtils object
*
* @param db The feature to be added to the PrimaryKey attribute
* @param primaryKey The feature to be added to the PrimaryKey attribute
* @param dbTable The feature to be added to the PrimaryKey attribute
* @exception SQLException Description of the Exception
*/
public static void addPrimaryKey(Connection db, DatabaseTable dbTable, String primaryKey)
throws SQLException {
boolean commit = db.getAutoCommit();
try {
if (commit) {
db.setAutoCommit(false);
}
// Create a clone 'y' of the table 'x' specified
DatabaseTable clone = new DatabaseTable(dbTable);
clone.setTableName(dbTable.getTableName() + "_clone");
clone.setSequenceName("");
clone.create(db);
System.out.println("Inserting into clone");
// Move data from 'x' into its clone 'y'
dbTable.selectInto(db, clone);
// Delete data from 'x' and drop table 'x'. No need to drop the sequence since the table has
// no primary key
System.out.println("Dropping dbTable");
dbTable.drop(db, false);
System.out.println("Creating dbTable");
// Create a new 'x' with a primary key column
DatabaseColumn column = new DatabaseColumn(primaryKey, java.sql.Types.INTEGER);
column.setIsPrimaryKey(true);
dbTable.addColumn(0, column);
dbTable.create(db);
System.out.println("Inserting back into dbTable");
// Move data from 'y' into the new 'x'
clone.selectInto(db, dbTable);
// Delete data from 'y' and drop clone 'y'. No need to drop the sequence since the clone has
// no primary key
System.out.println("Dropping clone");
clone.drop(db, false);
if (commit) {
db.commit();
}
} catch (SQLException e) {
e.printStackTrace(System.out);
if (commit) {
db.rollback();
}
throw new SQLException(e.getMessage());
} finally {
if (commit) {
db.setAutoCommit(true);
}
}
}
