Package io.permazen.kv.sql

Class SQLKVDatabase

java.lang.Object
io.permazen.kv.sql.SQLKVDatabase
All Implemented Interfaces:
KVDatabase
Direct Known Subclasses:
CockroachKVDatabase, MSSQLKVDatabase, MySQLKVDatabase, SQLiteKVDatabase
public class SQLKVDatabase extends Object implements KVDatabase
Support superclass for SQL KVDatabase implementations.

Key watches are not supported.

  • Field Details

  • Constructor Details

    • SQLKVDatabase

      public SQLKVDatabase()

  • Method Details

    • getDataSource

      public DataSource getDataSource()
      Get the DataSource used with this instance.
      Returns:
      the associated DataSource

    • setDataSource

      public void setDataSource(DataSource dataSource)
      Configure the DataSource used with this instance.

      Required property.

      Parameters:
      dataSource - access to the underlying database

    • getTableName

      public String getTableName()
      Get the name of the table containing keys and values.

      Default value is "KV".

      Returns:
      key/value table name

    • setTableName

      public void setTableName(String tableName)
      Set the name of the key/value table.
      Parameters:
      tableName - the name of the key/value table
      Throws:
      IllegalArgumentException - if table is null

    • getKeyColumnName

      public String getKeyColumnName()
      Get the name of the column containing keys.

      Default value is "kv_key".

      Returns:
      the name of the key column

    • setKeyColumnName

      public void setKeyColumnName(String keyColumnName)
      Configure the name of the column containing keys.
      Parameters:
      keyColumnName - the name of the key column
      Throws:
      IllegalArgumentException - if keyColumnName is null

    • getValueColumnName

      public String getValueColumnName()
      Get the name of the column containing values.

      Default value is "kv_value".

      Returns:
      the name of the value column

    • setValueColumnName

      public void setValueColumnName(String valueColumnName)
      Configure the name of the column containing values.
      Parameters:
      valueColumnName - the name of the value column
      Throws:
      IllegalArgumentException - if valueColumnName is null

    • getIsolationLevel

      public IsolationLevel getIsolationLevel()
      Get the default transaction isolation level.

      This may be overridden on a per-transaction basis; see getIsolationLevel(Map).

      Default value is IsolationLevel.SERIALIZABLE.

      Returns:
      isolation level

    • setIsolationLevel

      public void setIsolationLevel(IsolationLevel isolationLevel)
      Configure the default transaction isolation level.
      Parameters:
      isolationLevel - isolation level
      Throws:
      IllegalArgumentException - if isolationLevel is null

    • isRollbackForReadOnly

      public boolean isRollbackForReadOnly()
      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.

      Returns:
      whether to use rollback when committing a read-only transaction

    • setRollbackForReadOnly

      public void setRollbackForReadOnly(boolean rollbackForReadOnly)
      Configure whether to rollback read-only transactions.
      Parameters:
      rollbackForReadOnly - true to use rollback when committing a read-only transaction.
      See Also:

    • start

      @PostConstruct public void start()
      Description copied from interface: KVDatabase
      Start this instance. This method must be called prior to creating any transactions.

      This method is idempotent: if this instance is already started, nothing happens.

      Whether an instance that has been started and stopped can be restarted is implementation-dependent.

      Specified by:
      start in interface KVDatabase

    • stop

      @PreDestroy public void stop()
      Description copied from interface: KVDatabase
      Stop this instance.

      This method is idempotent: if this instance has not been started, or is already stopped, nothing happens.

      Specified by:
      stop in interface KVDatabase

    • initializeDatabaseIfNecessary

      protected void initializeDatabaseIfNecessary(Connection connection) throws SQLException
      Perform any required database initialization.

      The implementation in SQLKVDatabase does nothing. Subclasses will typically invoke CREATE TABLE ... IF NOT EXISTS here, etc.

      Parameters:
      connection - open database connection (will be closed by the caller of this method)
      Throws:
      SQLException - if an error occurs

    • createTransaction

      public SQLKVTransaction createTransaction(Map<String,?> options)
      Create a new transaction.

      The implementation in SQLKVDatabase invokes createTransactionConnection() to get a Connection for the new transaction, then invokes these methods in order:

      1. Connection.setTransactionIsolation()
      2. preBeginTransaction()
      3. beginTransaction()
      4. postBeginTransaction()
      5. createSQLKVTransaction()
      and returns the result.
      Specified by:
      createTransaction in interface KVDatabase
      Parameters:
      options - optional transaction options; may be null
      Returns:
      newly created transaction
      Throws:
      KVDatabaseException - if an unexpected error occurs
      IllegalStateException - if no DataSource is configured

    • createTransaction

      public SQLKVTransaction createTransaction()
      Create a new transaction.

      The implementation in SQLKVDatabase invokes createTransaction(null) (i.e., with no options) and returns the result.

      Specified by:
      createTransaction in interface KVDatabase
      Returns:
      newly created transaction
      Throws:
      KVDatabaseException - if an unexpected error occurs
      IllegalStateException - if no DataSource is configured

    • getIsolationLevel

      protected IsolationLevel getIsolationLevel(Map<String,?> options)
      Extract the IsolationLevel, if any, from the transaction options.

      The implementation in SQLKVDatabase supports an IsolationLevel under the key OPTION_ISOLATION; also, the IsolationLevel may be configured by the Spring PermazenTransactionManager, for example, using the @Transactional annotation.

      Parameters:
      options - transaction options
      Returns:
      transaction isolation

    • createTransactionConnection

      protected Connection createTransactionConnection() throws SQLException
      Create a Connection for a new transaction.

      The implementation in SQLKVDatabase invokes DataSource.getConnection() on the configured DataSource, and then deletates to configureConnection() to perform any required configuration of the new Connection.

      Returns:
      new transaction Connection
      Throws:
      SQLException - if an error occurs

    • configureConnection

      protected void configureConnection(Connection connection) throws SQLException
      Configure a newly created Connection.

      The implementation in SQLKVDatabase does nothing.

      Parameters:
      connection - newly created Connection
      Throws:
      SQLException - if an error occurs

    • preBeginTransaction

      protected void preBeginTransaction(Connection connection) throws SQLException
      Subclass hook invoked just before opening a new SQL transaction.

      The implementation in SQLKVDatabase does nothing. Note: subclasses must ensure the transaction is configured for the IsolationLevel configured on this instance.

      Parameters:
      connection - the Connection for a new transaction
      Throws:
      SQLException - if an error occurs
      See Also:

    • beginTransaction

      protected void beginTransaction(Connection connection) throws SQLException
      Open a new SQL transaction on the given Connection.

      The implementation in SQLKVDatabase invokes Connection.setAutoCommit(false).

      Parameters:
      connection - the Connection for a new transaction
      Throws:
      SQLException - if an error occurs
      See Also:

    • postBeginTransaction

      protected void postBeginTransaction(Connection connection) throws SQLException
      Subclass hook invoked just after opening a new SQL transaction.

      The implementation in SQLKVDatabase does nothing. Note: subclasses must ensure the transaction is configured for the IsolationLevel configured on this instance.

      Parameters:
      connection - the Connection for a new transaction
      Throws:
      SQLException - if an error occurs
      See Also:

    • createSQLKVTransaction

      protected SQLKVTransaction createSQLKVTransaction(Connection connection) throws SQLException
      Create a new SQLKVTransaction for a new transaction given the specified Connection. There will already be an SQL transaction open on connection.

      The implementation in SQLKVDatabase just invokes new SQLKVTransaction(this, connection).

      Parameters:
      connection - the Connection for a new transaction
      Returns:
      newly created transaction
      Throws:
      SQLException - if an error occurs

    • createGetStatement

      public String createGetStatement()
      Create an SQL statement that reads the value column associated with key ?1.
      Returns:
      SQL query statement

    • createGetAtLeastStatement

      public String createGetAtLeastStatement(boolean reverse)
      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.
      Parameters:
      reverse - true to return rows in descending key order, false to return rows in ascending key order
      Returns:
      SQL query statement

    • createGetAtMostStatement

      public String createGetAtMostStatement(boolean reverse)
      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.
      Parameters:
      reverse - true to return rows in descending key order, false to return rows in ascending key order
      Returns:
      SQL query statement

    • createGetRangeStatement

      public String createGetRangeStatement(boolean reverse)
      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.
      Parameters:
      reverse - true to return rows in descending key order, false to return rows in ascending key order
      Returns:
      SQL query statement

    • createGetAllStatement

      public String createGetAllStatement(boolean reverse)
      Create an SQL statement that reads all of the key and value columns (in that order), possibly reversed.
      Parameters:
      reverse - true to return rows in descending key order, false to return rows in ascending key order
      Returns:
      SQL query statement

    • createPutStatement

      public String createPutStatement()
      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).
      Returns:
      SQL insertion statement

    • createRemoveStatement

      public String createRemoveStatement()
      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.
      Returns:
      SQL delete statement

    • createRemoveRangeStatement

      public String createRemoveRangeStatement()
      Create an SQL statement that deletes all rows with keys in the range ?1 (inclusive} to ?2 (exclusive).
      Returns:
      SQL delete statement

    • createRemoveAtLeastStatement

      public String createRemoveAtLeastStatement()
      Create an SQL statement that deletes all rows with keys greater than or equal to ?1.
      Returns:
      SQL delete statement

    • createRemoveAtMostStatement

      public String createRemoveAtMostStatement()
      Create an SQL statement that deletes all rows with keys strictly less than ?1.
      Returns:
      SQL delete statement

    • createRemoveAllStatement

      public String createRemoveAllStatement()
      Create an SQL statement that deletes all rows.
      Returns:
      SQL delete statement

    • limitSingleRow

      public String limitSingleRow(String sql)
      Modify the given SQL statement so that only one row is returned.

      This is an optional method; returning 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 SQLKVDatabase returns its parameter unchanged.

      Parameters:
      sql - SQL statement
      Returns:
      SQL statement

    • quote

      public String quote(String name)
      Enquote a table or column name as necessary.

      The implementation in SQLKVDatabase returns its parameter unchanged.

      Parameters:
      name - table or column name
      Returns:
      name enquoted as necessary for this database type
      Throws:
      IllegalArgumentException - if name is null

    • wrapException

      public KVTransactionException wrapException(SQLKVTransaction transaction, SQLException e)
      Wrap the given SQLException in the appropriate KVTransactionException.
      Parameters:
      transaction - the SQLKVTransaction in which the exception occured
      e - SQL exception
      Returns:
      appropriate KVTransactionException with chained exception e
      Throws:
      NullPointerException - if e is null