io.permazen.kv.sql.SQLKVDatabase Maven / Gradle / Ivy
Show all versions of permazen-kv-sql Show documentation
/*
* Copyright (C) 2015 Archie L. Cobbs. All rights reserved.
*/
package io.permazen.kv.sql;
import com.google.common.base.Preconditions;
import io.permazen.kv.KVDatabase;
import io.permazen.kv.KVDatabaseException;
import io.permazen.kv.KVTransactionException;
import io.permazen.kv.RetryTransactionException;
import io.permazen.kv.TransactionTimeoutException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLRecoverableException;
import java.sql.SQLTimeoutException;
import java.sql.SQLTransientException;
import java.util.Map;
import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Support superclass for SQL {@link KVDatabase} implementations.
*
*
* {@linkplain io.permazen.kv.KVTransaction#watchKey Key watches} are not supported.
*/
public class SQLKVDatabase implements KVDatabase {
/**
* Default table name ({@value #DEFAULT_TABLE_NAME}).
*/
public static final String DEFAULT_TABLE_NAME = "KV";
/**
* Default key column name ({@value #DEFAULT_KEY_COLUMN_NAME}).
*/
public static final String DEFAULT_KEY_COLUMN_NAME = "kv_key";
/**
* Default value column name ({@value #DEFAULT_VALUE_COLUMN_NAME}).
*/
public static final String DEFAULT_VALUE_COLUMN_NAME = "kv_value";
/**
* Option key for {@link #createTransaction(Map)}. Value should be an {@link IsolationLevel} instance,
* or the {@link IsolationLevel#name name()} thereof.
*/
public static final String OPTION_ISOLATION = "isolation";
protected DataSource dataSource;
/**
* The name of the key/value table. Default value is {@value #DEFAULT_TABLE_NAME}.
*/
protected String tableName = DEFAULT_TABLE_NAME;
/**
* The name of the key column. Default value is {@value #DEFAULT_KEY_COLUMN_NAME}.
*/
protected String keyColumnName = DEFAULT_KEY_COLUMN_NAME;
/**
* The name of the value column. Default value is {@value #DEFAULT_VALUE_COLUMN_NAME}.
*/
protected String valueColumnName = DEFAULT_VALUE_COLUMN_NAME;
/**
* The default transaction isolation level. Default is {@link IsolationLevel#SERIALIZABLE}.
*/
protected IsolationLevel isolationLevel = IsolationLevel.SERIALIZABLE;
protected final Logger log = LoggerFactory.getLogger(this.getClass());
volatile boolean rollbackForReadOnly = true;
/**
* Get the {@link DataSource} used with this instance.
*
* @return the associated {@link DataSource}
*/
public DataSource getDataSource() {
return this.dataSource;
}
/**
* Configure the {@link DataSource} used with this instance.
*
*
* Required property.
*
* @param dataSource access to the underlying database
*/
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* Get the name of the table containing keys and values.
*
*
* Default value is {@value #DEFAULT_TABLE_NAME}.
*
* @return key/value table name
*/
public String getTableName() {
return this.tableName;
}
/**
* Set the name of the key/value table.
*
* @param tableName the name of the key/value table
* @throws IllegalArgumentException if {@code table} is null
*/
public void setTableName(String tableName) {
Preconditions.checkArgument(tableName != null, "null tableName");
this.tableName = tableName;
}
/**
* Get the name of the column containing keys.
*
*
* Default value is {@value #DEFAULT_KEY_COLUMN_NAME}.
*
* @return the name of the key column
*/
public String getKeyColumnName() {
return this.keyColumnName;
}
/**
* Configure the name of the column containing keys.
*
* @param keyColumnName the name of the key column
* @throws IllegalArgumentException if {@code keyColumnName} is null
*/
public void setKeyColumnName(String keyColumnName) {
Preconditions.checkArgument(keyColumnName != null, "null keyColumnName");
this.keyColumnName = keyColumnName;
}
/**
* Get the name of the column containing values.
*
*
* Default value is {@value #DEFAULT_VALUE_COLUMN_NAME}.
*
* @return the name of the value column
*/
public String getValueColumnName() {
return this.valueColumnName;
}
/**
* Configure the name of the column containing values.
*
* @param valueColumnName the name of the value column
* @throws IllegalArgumentException if {@code valueColumnName} is null
*/
public void setValueColumnName(String valueColumnName) {
Preconditions.checkArgument(valueColumnName != null, "null valueColumnName");
this.valueColumnName = valueColumnName;
}
/**
* Get the default transaction isolation level.
*
*
* This may be overridden on a per-transaction basis; see {@link #getIsolationLevel(Map)}.
*
*
* Default value is {@link IsolationLevel#SERIALIZABLE}.
*
* @return isolation level
*/
public IsolationLevel getIsolationLevel() {
return this.isolationLevel;
}
/**
* Configure the default transaction isolation level.
*
* @param isolationLevel isolation level
* @throws IllegalArgumentException if {@code isolationLevel} is null
*/
public void setIsolationLevel(IsolationLevel isolationLevel) {
Preconditions.checkArgument(isolationLevel != null, "null isolationLevel");
this.isolationLevel = isolationLevel;
}
/**
* Get whether to rollback read-only transactions.
*
*
* If true, read-only transactions will be rolled back on commit. If false, mutations during read-only transactions
* will be captured in memory and not written to the database, and the underlying SQL transaction committed on commit.
* Some implementations require the latter in order to guarantee up-to-date reads within the transaction.
*
*
* Default value is true.
*
* @return whether to use rollback when committing a read-only transaction
*/
public boolean isRollbackForReadOnly() {
return this.rollbackForReadOnly;
}
/**
* Configure whether to rollback read-only transactions.
*
* @param rollbackForReadOnly true to use rollback when committing a read-only transaction.
* @see #isRollbackForReadOnly
*/
public void setRollbackForReadOnly(boolean rollbackForReadOnly) {
this.rollbackForReadOnly = rollbackForReadOnly;
}
@Override
@PostConstruct
public void start() {
try (Connection connection = this.createTransactionConnection()) {
this.initializeDatabaseIfNecessary(connection);
} catch (SQLException e) {
throw new KVDatabaseException(this, e);
}
}
@Override
@PreDestroy
public void stop() {
}
/**
* Perform any required database initialization.
*
*
* The implementation in {@link SQLKVDatabase} does nothing. Subclasses will
* typically invoke {@code CREATE TABLE ... IF NOT EXISTS} here, etc.
*
* @param connection open database connection (will be closed by the caller of this method)
* @throws SQLException if an error occurs
*/
protected void initializeDatabaseIfNecessary(Connection connection) throws SQLException {
}
/**
* Create a new transaction.
*
*
* The implementation in {@link SQLKVDatabase} invokes {@link #createTransactionConnection createTransactionConnection()}
* to get a {@link Connection} for the new transaction, then invokes these methods in order:
*
* - {@link Connection#setTransactionIsolation Connection.setTransactionIsolation()}
* - {@link #preBeginTransaction preBeginTransaction()}
* - {@link #beginTransaction beginTransaction()}
* - {@link #postBeginTransaction postBeginTransaction()}
* - {@link #createSQLKVTransaction createSQLKVTransaction()}
*
* and returns the result.
*
* @throws KVDatabaseException if an unexpected error occurs
* @throws IllegalStateException if no {@link DataSource} is {@linkplain #setDataSource configured}
*/
@Override
public SQLKVTransaction createTransaction(Map options) {
// Sanity check
Preconditions.checkState(this.dataSource != null, "no DataSource configured");
// Get isolation level
final IsolationLevel txIsolationLevel = options != null ? this.getIsolationLevel(options) : this.isolationLevel;
// Get connection and transaction
final SQLKVTransaction tx;
try {
final Connection connection = this.createTransactionConnection();
connection.setTransactionIsolation(txIsolationLevel.getConnectionIsolation());
this.preBeginTransaction(connection);
this.beginTransaction(connection);
this.postBeginTransaction(connection);
tx = this.createSQLKVTransaction(connection);
} catch (SQLException e) {
throw new KVDatabaseException(this, e);
}
// Done
return tx;
}
/**
* Create a new transaction.
*
*
* The implementation in {@link SQLKVDatabase} invokes {@link #createTransaction(Map) createTransaction(null)}
* (i.e., with no options) and returns the result.
*
* @throws KVDatabaseException if an unexpected error occurs
* @throws IllegalStateException if no {@link DataSource} is {@linkplain #setDataSource configured}
*/
@Override
public SQLKVTransaction createTransaction() {
return this.createTransaction(null);
}
/**
* Extract the {@link IsolationLevel}, if any, from the transaction options.
*
*
* The implementation in {@link SQLKVDatabase} supports an {@link IsolationLevel} under the key {@link #OPTION_ISOLATION};
* also, the {@link IsolationLevel} may be configured by the Spring
* {@link io.permazen.spring.PermazenTransactionManager}, for example, using the
* {@link org.springframework.transaction.annotation.Transactional @Transactional} annotation.
*
* @param options transaction options
* @return transaction isolation
*/
protected IsolationLevel getIsolationLevel(Map options) {
// Start with the default
IsolationLevel txIsolationLevel = this.isolationLevel;
// Look for isolation option from the PermazenTransactionManager
Object isolation = options.get("org.springframework.transaction.annotation.Isolation");
if (isolation instanceof Enum)
isolation = ((Enum)isolation).name();
if (isolation != null) {
switch (isolation.toString()) {
case "DEFAULT":
break;
default:
txIsolationLevel = IsolationLevel.valueOf(isolation.toString());
break;
}
}
// Look for OPTION_ISOLATION option
try {
final Object value = options.get(OPTION_ISOLATION);
if (value instanceof IsolationLevel)
txIsolationLevel = (IsolationLevel)value;
else if (value instanceof String)
txIsolationLevel = IsolationLevel.valueOf((String)value);
} catch (Exception e) {
// ignore
}
// Done
return txIsolationLevel;
}
/**
* Create a {@link Connection} for a new transaction.
*
*
* The implementation in {@link SQLKVDatabase} invokes {@link DataSource#getConnection()} on the
* {@linkplain #dataSource configured} {@link DataSource}, and then deletates to
* {@link #configureConnection configureConnection()} to perform any required configuration
* of the new {@link Connection}.
*
* @return new transaction {@link Connection}
* @throws SQLException if an error occurs
*/
protected Connection createTransactionConnection() throws SQLException {
final Connection connection = this.dataSource.getConnection();
this.configureConnection(connection);
return connection;
}
/**
* Configure a newly created {@link Connection}.
*
*
* The implementation in {@link SQLKVDatabase} does nothing.
*
* @param connection newly created {@link Connection}
* @throws SQLException if an error occurs
*/
protected void configureConnection(Connection connection) throws SQLException {
}
/**
* Subclass hook invoked just before opening a new SQL transaction.
*
*
* The implementation in {@link SQLKVDatabase} does nothing. Note: subclasses must ensure the transaction is
* configured for the {@link IsolationLevel} configured on this instance.
*
* @param connection the {@link Connection} for a new transaction
* @throws SQLException if an error occurs
* @see #createSQLKVTransaction createSQLKVTransaction()
*/
protected void preBeginTransaction(Connection connection) throws SQLException {
}
/**
* Open a new SQL transaction on the given {@link Connection}.
*
*
* The implementation in {@link SQLKVDatabase} invokes {@link Connection#setAutoCommit Connection.setAutoCommit(false)}.
*
* @param connection the {@link Connection} for a new transaction
* @throws SQLException if an error occurs
* @see #createSQLKVTransaction createSQLKVTransaction()
*/
protected void beginTransaction(Connection connection) throws SQLException {
connection.setAutoCommit(false);
}
/**
* Subclass hook invoked just after opening a new SQL transaction.
*
*
* The implementation in {@link SQLKVDatabase} does nothing. Note: subclasses must ensure the transaction is
* configured for the {@link IsolationLevel} configured on this instance.
*
* @param connection the {@link Connection} for a new transaction
* @throws SQLException if an error occurs
* @see #createSQLKVTransaction createSQLKVTransaction()
*/
protected void postBeginTransaction(Connection connection) throws SQLException {
}
/**
* Create a new {@link SQLKVTransaction} for a new transaction given the specified {@link Connection}.
* There will already be an SQL transaction open on {@code connection}.
*
*
* The implementation in {@link SQLKVDatabase} just invokes
* {@link SQLKVTransaction#SQLKVTransaction new SQLKVTransaction(this, connection)}.
*
* @param connection the {@link Connection} for a new transaction
* @return newly created transaction
* @throws SQLException if an error occurs
*/
protected SQLKVTransaction createSQLKVTransaction(Connection connection) throws SQLException {
return new SQLKVTransaction(this, connection);
}
/**
* Create an SQL statement that reads the value column associated with key ?1.
*
* @return SQL query statement
*/
public String createGetStatement() {
return "SELECT " + this.quote(this.valueColumnName) + " FROM "
+ this.quote(this.tableName) + " WHERE " + this.quote(this.keyColumnName) + " = ?";
}
/**
* Create an SQL statement that reads the key and value columns (in that order) associated
* with the smallest key greater than or equal to ?1, if any.
*
* @param reverse true to return rows in descending key order, false to return rows in ascending key order
* @return SQL query statement
*/
public String createGetAtLeastStatement(boolean reverse) {
return "SELECT " + this.quote(this.keyColumnName) + ", " + this.quote(this.valueColumnName)
+ " FROM " + this.quote(this.tableName) + " WHERE " + this.quote(this.keyColumnName)
+ " >= ? ORDER BY " + this.quote(this.keyColumnName) + (reverse ? " DESC" : " ASC");
}
/**
* Create an SQL statement that reads the key and value columns (in that order)
* associated with the greatest key strictly less than ?1, if any.
*
* @param reverse true to return rows in descending key order, false to return rows in ascending key order
* @return SQL query statement
*/
public String createGetAtMostStatement(boolean reverse) {
return "SELECT " + this.quote(this.keyColumnName) + ", " + this.quote(this.valueColumnName)
+ " FROM " + this.quote(this.tableName) + " WHERE " + this.quote(this.keyColumnName)
+ " < ? ORDER BY " + this.quote(this.keyColumnName) + (reverse ? " DESC" : " ASC");
}
/**
* Create an SQL statement that reads the key and value columns (in that order) associated with all keys
* in the range ?1 (inclusive) to ?2 (exclusive), possibly reversed.
*
* @param reverse true to return rows in descending key order, false to return rows in ascending key order
* @return SQL query statement
*/
public String createGetRangeStatement(boolean reverse) {
return "SELECT " + this.quote(this.keyColumnName) + ", " + this.quote(this.valueColumnName)
+ " FROM " + this.quote(this.tableName) + " WHERE " + this.quote(this.keyColumnName)
+ " >= ? and " + this.quote(this.keyColumnName) + " < ? ORDER BY " + this.quote(this.keyColumnName)
+ (reverse ? " DESC" : " ASC");
}
/**
* Create an SQL statement that reads all of the key and value columns (in that order), possibly reversed.
*
* @param reverse true to return rows in descending key order, false to return rows in ascending key order
* @return SQL query statement
*/
public String createGetAllStatement(boolean reverse) {
return "SELECT " + this.quote(this.keyColumnName) + ", " + this.quote(this.valueColumnName)
+ " FROM " + this.quote(this.tableName) + " ORDER BY " + this.quote(this.keyColumnName) + (reverse ? " DESC" : " ASC");
}
/**
* Create an SQL statement that inserts the key/value pair with key ?1 and value ?2
* A row with key ?1 may already exist; if so, the value should be updated to
* ?2 (if syntax requires it, the value may be updated to ?3 instead;
* ?3, if it exists, will be set to the same value as ?2).
*
* @return SQL insertion statement
*/
public String createPutStatement() {
return "INSERT INTO " + this.quote(this.tableName) + " (" + this.quote(this.keyColumnName)
+ ", " + this.quote(this.valueColumnName) + ") VALUES (?, ?) ON DUPLICATE KEY UPDATE "
+ this.quote(this.valueColumnName) + " = ?";
}
/**
* Create an SQL statement that deletes the row associated with key ?1, if any.
* Note that the key may or may not exist prior to this method being invoked.
*
* @return SQL delete statement
*/
public String createRemoveStatement() {
return "DELETE FROM " + this.quote(this.tableName) + " WHERE " + this.quote(this.keyColumnName) + " = ?";
}
/**
* Create an SQL statement that deletes all rows with keys in the range ?1 (inclusive}
* to ?2 (exclusive).
*
* @return SQL delete statement
*/
public String createRemoveRangeStatement() {
return "DELETE FROM " + this.quote(this.tableName)
+ " WHERE " + this.quote(this.keyColumnName) + " >= ? AND " + this.quote(this.keyColumnName) + " < ?";
}
/**
* Create an SQL statement that deletes all rows with keys greater than or equal to ?1.
*
* @return SQL delete statement
*/
public String createRemoveAtLeastStatement() {
return "DELETE FROM " + this.quote(this.tableName) + " WHERE " + this.quote(this.keyColumnName) + " >= ?";
}
/**
* Create an SQL statement that deletes all rows with keys strictly less than ?1.
*
* @return SQL delete statement
*/
public String createRemoveAtMostStatement() {
return "DELETE FROM " + this.quote(this.tableName) + " WHERE " + this.quote(this.keyColumnName) + " < ?";
}
/**
* Create an SQL statement that deletes all rows.
*
* @return SQL delete statement
*/
public String createRemoveAllStatement() {
return "DELETE FROM " + this.quote(this.tableName);
}
/**
* Modify the given SQL statement so that only one row is returned.
*
*
* This is an optional method; returning {@code statement} unmodified is acceptable, but subclasses may
* be able to improve efficiency by modifying the SQL statement in a vendor-specific manner to only return one row.
*
*
* The implementation in {@link SQLKVDatabase} returns its parameter unchanged.
*
* @param sql SQL statement
* @return SQL statement
*/
public String limitSingleRow(String sql) {
return sql;
}
/**
* Enquote a table or column name as necessary.
*
*
* The implementation in {@link SQLKVDatabase} returns its parameter unchanged.
*
* @param name table or column name
* @return {@code name} enquoted as necessary for this database type
* @throws IllegalArgumentException if {@code name} is null
*/
public String quote(String name) {
return name;
}
/**
* Wrap the given {@link SQLException} in the appropriate {@link KVTransactionException}.
*
* @param transaction the {@link SQLKVTransaction} in which the exception occured
* @param e SQL exception
* @return appropriate {@link KVTransactionException} with chained exception {@code e}
* @throws NullPointerException if {@code e} is null
*/
public KVTransactionException wrapException(SQLKVTransaction transaction, SQLException e) {
if (e instanceof SQLTimeoutException)
return new TransactionTimeoutException(transaction, e);
if (e instanceof SQLRecoverableException)
return new RetryTransactionException(transaction, e);
if (e instanceof SQLTransientException)
return new RetryTransactionException(transaction, e);
return new KVTransactionException(transaction, e);
}
}