Database Service

The Database Service DSL is a wrapper for the Database Service module provided by CSW. You can refer to detailed documentation of the Database Service provided by CSW here.

This DSL provides APIs to create the connection to a database. It uses a Jooq library underneath for creating database connections and queries. APIs for creating the database connection expose a DSLContext object. All database-related functionality from the Database Service is available using the Java APIs exposed by DSLContext.

Note

Jooq is a Java library that provides a higher level API for accessing data i.e. DDL support, DML support, fetch, batch execution, prepared statements, safety against SQL injection, connection pooling, etc. To know more about Jooq and its features, please refer to this link.

Create a Database Connection (Read Access)

This API allows creating a connection to a database with default read access. The username and password for read access is picked from environment variables set on individual’s machine i.e. DB_READ_USERNAME and DB_READ_PASSWORD. It is expected that developers set these variables before calling this method. It returns a Jooq DSLContext or fails with a DatabaseException. The DSLContext provides methods like fetchAsync, executeAsync, executeBatch, etc. Additionally, the CSW JooqHelper and its wrapper methods can be used. For methods returning a Future, script will need to explicitly await for the future to complete to achieve sequential flow.

THe following example shows the creation of a database connection and a query to the database.

Kotlin
sourceval readDslContext = makeDatabaseService(dbName = "IRIS_db")

onSetup("setup-iris") {
    val query = readDslContext
            .resultQuery("SELECT filter_key FROM filter_table")

    //await to get result of query to achieve sequential flow of execution
    val filterKeys = JooqHelper.fetchAsync(query, String::class.java).await()

    // do something with filter keys
}

Create a Database Connection (Read/Write Access)

This API allows creating a connection to a database with read/write access. Username and password credentials should be stored in environment variables, and the names of these environment variables are passed as method parameters.
If the correct write access credentials can be obtained from these environment variables, then a database connection will be created with write access. It returns a Jooq DSLContext or fails with a DatabaseException.

The following example shows the creation of a database connection with write access and alternative way of using the DSLContext to do queries.

Kotlin
sourceval context = makeDatabaseService("IRIS_db", "db_write_username", "db_write_password")

onObserve("command-1") {
    val result = context
            .select(field("event_name"))
            .from(name("table_1")).fetch()

    // do something with result
}

Source code for examples

0.4.0