JDBC Plugin

Provides JDBCSource, JDBCCache, PostgreSQLSource, and PostgreSQLCache.

JDBCSource & PostgreSQLSource

JDBCSource maps a URL identifier to a BLOB field in a relational database. It does not require a custom schema and can adapt to any schema, but some delegate methods must be implemented in order to obtain the information needed to run the SQL queries. It uses only standard JDBC API, so it should work with most JDBC drivers.

PostgreSQLSource works similarly to JDBCSource, with the main difference being that it uses PostgreSQL's Large Object feature instead of a BLOB column.

If you are using PostgreSQL, and are able to use its Large Object functionality instead of BLOBs, you will probably be better off with PostgreSQLSource, which enables efficient random access to source images.

The plugin does not include any JDBC drivers, so a driver JAR for the desired database must be obtained separately and saved somewhere on the classpath.

Delegate Methods

These sources require several delegate methods to be implemented.

Database Identifier Retrieval Method

This method takes in an unencoded URL identifier and returns the corresponding database value of the identifier.

class CustomDelegate
  # (or postgresqlsource_database_identifier)
  def jdbcsource_database_identifier(options = {})
    # If URL identifiers map directly to values in the database, simply
    # return the identifier from the request context. Otherwise, you
    # could transform it, perform a service request to look it up, etc.
    context['identifier']
  end
end

Media Type Retrieval Method

This method returns a media (MIME) type corresponding to the value returned by the database identifier method. If the media type is stored in the database, this example will return an SQL statement to retrieve it.

class CustomDelegate
  # (or postgresqlsource_media_type)
  def jdbcsource_media_type(options = {})
    'SELECT media_type ' +
        'FROM some_table ' +
        'WHERE some_identifier = ?'
  end
end

This method may return nil; see Format Inference.

Last Modified Retrieval Method

This method returns a last-modified time corresponding to the value returned by the database identifier method. If this time is stored in the database, this example will return an SQL statement to retrieve it.

class CustomDelegate
  # (or postgresqlsource_last_modified)
  def jdbcsource_last_modified(options = {})
    'SELECT last_modified ' +
        'FROM some_table ' +
        'WHERE some_identifier = ?'
  end
end

This method may return nil.

BLOB Retrieval SQL Method

This method returns an SQL statement that selects the BLOB value corresponding to the value returned by the database identifier method.

class CustomDelegate
  # (or postgresqlsource_lookup_sql)
  def jdbcsource_lookup_sql(options = {})
    'SELECT image_blob_column '
        'FROM some_table '
        'WHERE some_identifier = ?'
  end
end

Format Inference

Like all sources, JDBCSource needs to be able to figure out the format of a source image before it can be served. It uses the following strategy to do this:

  1. If the media type retrieval method returns either a recognized media type, or an SQL query that can be invoked to obtain a recognized media type, the corresponding format is used.
  2. If the source image's URI identifier has a recognized filename extension, the format is inferred from that.
  3. Otherwise, the blob retrieval SQL is executed to obtain a small range of data from the beginning of the resource, and an attempt is made to infer a format from its magic bytes.

JDBCCache & PostgreSQLCache

These caches cache variant images and metadata in relational database tables. JDBCCache uses only standard JDBC API, so it should work with most JDBC drivers. PostgreSQLCache, like PostgreSQLSource, uses PostgreSQL's Large Object feature to store variant images more efficiently.

Table Schemas

These caches require a schema to be created manually—they will not create one themselves.

JDBCCache

CREATE TABLE {JDBCCache.variant_image_table} (
    operations VARCHAR(4096) NOT NULL,
    image BLOB,
    last_modified TIMESTAMP,
    last_accessed TIMESTAMP
);

CREATE UNIQUE INDEX variant_operations_idx ON {JDBCCache.variant_image_table} (operations);
CREATE INDEX variant_last_modified_idx ON {JDBCCache.variant_image_table} (last_modified);
CREATE INDEX variant_last_accessed_idx ON {JDBCCache.variant_image_table} (last_accessed);

CREATE TABLE {JDBCCache.info_table} (
    identifier VARCHAR(4096) NOT NULL,
    info_json VARCHAR(8192) NOT NULL,
    last_modified TIMESTAMP,
    last_accessed TIMESTAMP
);

CREATE UNIQUE INDEX info_operations_idx ON {JDBCCache.info_table} (identifier);
CREATE INDEX info_last_modified_idx ON {JDBCCache.info_table} (last_modified);
CREATE INDEX info_last_accessed_idx ON {JDBCCache.info_table} (last_accessed);

PostgreSQLCache

CREATE TABLE {PostgreSQLCache.variant_image_table} (
    operations VARCHAR(4096) NOT NULL,
    image_oid OID NOT NULL,
    last_modified TIMESTAMP,
    last_accessed TIMESTAMP
);

CREATE UNIQUE INDEX variant_operations_idx ON {PostgreSQLCache.variant_image_table} (operations);
CREATE INDEX variant_last_modified_idx ON {PostgreSQLCache.variant_image_table} (last_modified);
CREATE INDEX variant_last_accessed_idx ON {PostgreSQLCache.variant_image_table} (last_accessed);

CREATE TABLE {PostgreSQLCache.info_table} (
    identifier VARCHAR(4096) NOT NULL,
    info_json JSON NOT NULL,
    last_modified TIMESTAMP,
    last_accessed TIMESTAMP
);

CREATE UNIQUE INDEX info_operations_idx ON {PostgreSQLCache.info_table} (identifier);
CREATE INDEX info_last_modified_idx ON {PostgreSQLCache.info_table} (last_modified);
CREATE INDEX info_last_accessed_idx ON {PostgreSQLCache.info_table} (last_accessed);

CREATE TRIGGER t_galia_cache
    BEFORE UPDATE OR DELETE ON {PostgreSQLCache.variant_image_table}
    FOR EACH ROW EXECUTE FUNCTION lo_manage(image_oid);

Installation

Step 1: Install the plugin

Use the plugin installer:

bin/install_plugin.sh galia-plugin-jdbc

Alternatively, download the plugin directly and extract it into Galia's plugins directory.

Step 2: Install a JDBC driver

There must be a JDBC driver JAR specific to your database present on the classpath.

Note that each implementation uses a distinct JDBC connection pool. If you are are using more than one simultaneously, ensure that your DBMS is configured to allow at least enough connections to support the sum of their max pool sizes. If you have not explicitly set any max pool sizes in the configuration, the default sizes will be logged at DEBUG level at startup.

Step 3: Install the lo module (PostgreSQLCache only)

PostgreSQLCache depends on PostgreSQL's lo module. Without it, cached images will never be deleted. If you have installed PostgreSQL via a package manager, this may already be installed, or you may need to install a PostgreSQL "contrib" package separately. If you compiled PostgreSQL from source, compiling these modules may require an extra step.

Once the lo module has been installed, it must be enabled:

CREATE EXTENSION lo;

Step 4: Create the table schema (JDBCCache & PostgreSQLCache only)

See Table Schemas.

Step 5: Configure the plugin

Copy the keys in config.yml.sample into your main application configuration and fill them in.

Connection URLs must use your JDBC driver's URL syntax, such as:

jdbc:postgresql://localhost:5432/my_database
jdbc:mysql://localhost:3306/my_database
jdbc:microsoft:sqlserver://example.org:1433;DatabaseName=MY_DATABASE

Consult the driver's documentation for details.

Step 6: Implement delegate methods

Copy the delegate methods in delegates.rb.sample into your main delegate script and implement whichever are relevant.