Packages

  • package root
    Definition Classes
    root
  • package edu
    Definition Classes
    root
  • package chop
    Definition Classes
    edu
  • package cbmi
    Definition Classes
    chop
  • package dataExpress
    Definition Classes
    cbmi
  • package backends

    Provides database wrappers and connectivity for MySQL, Postgres, Oracle, and SQLite JDBC drivers.

    Provides database wrappers and connectivity for MySQL, Postgres, Oracle, and SQLite JDBC drivers.

    Overview

    The support for individual databases is broken out into several classes:

    The SqlBackend for the corresponding database is the primary means of establishing a datsbase connection when using DataExpress. However, It is important to note that it is generally easier, and safer to use the edu.chop.cbmi.dataExpress.backends.SqlBackendFactory to establish the necessary backend and set up the connection

    In general the SQLDialect and BackendProvider are used as part of a SQLBackend, and not directly.

    Usage

    The edu.chop.cbmi.dataExpress.backends.SqlBackend requires properties to fully configure itself. Typically these are loaded from a file, but can also be supplied through a standard java.util.Properties object. The SQL backend has very simple requirements for properties files. They must contain the following keys:

    • driverClassName: The class name for the actual database driver
    • jdbcUri: This is the URI required by the database driver. It is passed through directly to JDBC so you are free to specify schemas and other information here
    • username: The username to authenticate against the database (not required for non-authenticated RDBMS systems such as SQLite) -password: The password for accessing the database

    DataExpress will pass along any other properties it finds directly to JDBC, so in cases where a database requires specific connection configuration (e.g. to enable SSL for Postgres), those settings can be passed along.

    In some cases, DataExpress will set specific properties on the connection to ensure predictable behavior. Most of these have been empirically determined by our testing given the narrow use cases for DataExpress. Where possible, we have made it possible for you to override these defaults in your own configuration file, however this is generally not recommended.

    Definition Classes
    dataExpress
    Example: 

    1. scala> import edu.chop.cbmi.dataExpress.backends._
import edu.chop.cbmi.dataExpress.backends._

scala> val backend = SqlBackendFactory "src/test/resources/sqlite_test.properties"
backend: edu.chop.cbmi.dataExpress.backends.SqlBackend = SqlBackend({jdbcUri=jdbc:sqlite:target/source.sqlite},SqLiteDialect,org.sqlite.JDBC)

scala> backend connect
res0: java.sql.Connection = org.sqlite.Conn@7168f171

scala> backend execute """CREATE TABLE de_test(id INTEGER, first_name VARCHAR, last_name VARCHAR)"""
res1: Boolean = false

scala> backend commit
res3: Boolean = false

scala> backend executeQuery "select count(*) from de_test"
res4: java.sql.ResultSet = org.sqlite.RS@3b070e76

scala> res4.getInt(1)
res5: Int = 0
    Note

    The SQL backends return jdbc ResultSets which are typically a little more "low-level" than what you would probably want for ETL purporses. The edu.chop.cbmi.dataExpress.dataModels.sql.SqlRelation is a much more convenient object to use for ETL.

  • package file
  • GenericSqlDialect
  • MySqlBackend
  • MySqlDialect
  • OracleBackend
  • OracleServiceProvider
  • OracleSqlDialect
  • PostgresBackend
  • PostgresSqlDialect
  • SqLiteBackend
  • SqLiteDialect
  • SqlBackend
  • SqlBackendFactory
  • SqlBackendProvider
  • SqlDialect
  • SqlQueryCache
  • SqlServerBackend
  • SqlServerServiceProvider
  • SqlServerSqlDialect
  • package dataModels
    Definition Classes
    dataExpress
  • package dataWriters
    Definition Classes
    dataExpress
  • package dsl
    Definition Classes
    dataExpress
  • package exceptions
    Definition Classes
    dataExpress
  • package logging
    Definition Classes
    dataExpress

package backends

Provides database wrappers and connectivity for MySQL, Postgres, Oracle, and SQLite JDBC drivers.

Overview

The support for individual databases is broken out into several classes:

The SqlBackend for the corresponding database is the primary means of establishing a datsbase connection when using DataExpress. However, It is important to note that it is generally easier, and safer to use the edu.chop.cbmi.dataExpress.backends.SqlBackendFactory to establish the necessary backend and set up the connection

In general the SQLDialect and BackendProvider are used as part of a SQLBackend, and not directly.

Usage

The edu.chop.cbmi.dataExpress.backends.SqlBackend requires properties to fully configure itself. Typically these are loaded from a file, but can also be supplied through a standard java.util.Properties object. The SQL backend has very simple requirements for properties files. They must contain the following keys:

  • driverClassName: The class name for the actual database driver
  • jdbcUri: This is the URI required by the database driver. It is passed through directly to JDBC so you are free to specify schemas and other information here
  • username: The username to authenticate against the database (not required for non-authenticated RDBMS systems such as SQLite) -password: The password for accessing the database

DataExpress will pass along any other properties it finds directly to JDBC, so in cases where a database requires specific connection configuration (e.g. to enable SSL for Postgres), those settings can be passed along.

In some cases, DataExpress will set specific properties on the connection to ensure predictable behavior. Most of these have been empirically determined by our testing given the narrow use cases for DataExpress. Where possible, we have made it possible for you to override these defaults in your own configuration file, however this is generally not recommended.

Example: 

  1. scala> import edu.chop.cbmi.dataExpress.backends._
import edu.chop.cbmi.dataExpress.backends._

scala> val backend = SqlBackendFactory "src/test/resources/sqlite_test.properties"
backend: edu.chop.cbmi.dataExpress.backends.SqlBackend = SqlBackend({jdbcUri=jdbc:sqlite:target/source.sqlite},SqLiteDialect,org.sqlite.JDBC)

scala> backend connect
res0: java.sql.Connection = org.sqlite.Conn@7168f171

scala> backend execute """CREATE TABLE de_test(id INTEGER, first_name VARCHAR, last_name VARCHAR)"""
res1: Boolean = false

scala> backend commit
res3: Boolean = false

scala> backend executeQuery "select count(*) from de_test"
res4: java.sql.ResultSet = org.sqlite.RS@3b070e76

scala> res4.getInt(1)
res5: Int = 0
Note

The SQL backends return jdbc ResultSets which are typically a little more "low-level" than what you would probably want for ETL purporses. The edu.chop.cbmi.dataExpress.dataModels.sql.SqlRelation is a much more convenient object to use for ETL.

Linear Supertypes
AnyRef, Any
Ordering
  1. Alphabetic
  2. By Inheritance
Inherited
  1. backends
  2. AnyRef
  3. Any
  1. Hide All
  2. Show All
Visibility
  1. Public
  2. All

Type Members

  1. class MySqlBackend extends SqlBackend

    Backend for accessing MySQL databases.

    Backend for accessing MySQL databases. This will use a connection properties file that should look something like the following:

    driverClassName=com.mysql.jdbc.Driver
jdbcUri=jdbc:mysql://server_address:server_port/schema_name
user=username
password=password
jdbcCompliantTruncation=false
  2. class OracleBackend extends SqlBackend

    Backend for accessing Oracle databases.

    Backend for accessing Oracle databases. This will use a connection properties file that should look something like the following:

    driverClassName=jdbc.driver.OracleDriver
jdbcUri=jdbc:oracle:thin:@//server_address:server_port
user=username
schema=schema_name
password=password

    If you must use an SID, the following is the format for the URI line:

    jdbc:oracle:thin:@host:port:SID}}
  3. class OracleServiceProvider extends SqlBackendProvider

    Service provider class for Oracle that allows runtime class loading of the Oracle driver

  4. class PostgresBackend extends SqlBackend

    Backend for accessing Postgres databases.

    Backend for accessing Postgres databases. This will use a connection properties file that should look something like the following:

    driverClassName=org.postgresql.Driver
jdbcUri=dbc:postgresql://server_address:server_port/schema_name
user=username
schema=schemaname
password=password

    Note that if you are using a self-signed cert for Postgres SSL connection, you may also need

    ssl=true
sslfactory=org.postgresql.ssl.NonValidatingFactory

    The above is less secure, so it's not recommended unless you are operating on your own trusted network.

  5. class SqLiteBackend extends SqlBackend

    Backend for accessing Sqlite databases.

    Backend for accessing Sqlite databases. This will use a connection properties file that should look something like the following:

    jdbcUri=jdbc:sqlite:filename_of_the_sqlite_database
  6. case class SqlBackend(connectionProperties: Properties, sqlDialect: SqlDialect, driverClassName: String) extends Product with Serializable

    Wrapper around JDBC that simplifies the mechanics of interacting with databases in an RDBMS-neutral way.

    Wrapper around JDBC that simplifies the mechanics of interacting with databases in an RDBMS-neutral way.

    Instances of SqlBacked should normally be instantiated via edu.chop.cbmi.dataExpress.backends.SqlBackendFactory

  7. trait SqlBackendProvider extends AnyRef

    Provides a mechanism to get a SqlBackend by supplying the necessary parameters at runtime

  8. trait SqlDialect extends AnyRef

    This trait is to be used when constructing new SQL backends for DataExpress.

    This trait is to be used when constructing new SQL backends for DataExpress. It provides basic SQL syntax for supported databases. This trait allows for customization of the SQL Syntax used in each RDBMS, while keeping the DataExpress API as generic as possible. More importantly, this trait provides baseline JDBC -> DataExpress type mappings which can be used across most database implementations.

  9. class SqlQueryCache extends AnyRef

    While the JDBC spec encourages individual drivers to handle their own query cache, this behavior is not guaranteed.

    While the JDBC spec encourages individual drivers to handle their own query cache, this behavior is not guaranteed. This class exists to provide consistent cache functionality across all drivers in DataExpress

  10. class SqlServerBackend extends SqlBackend

    Backend for accessing SqlServer databases.

    Backend for accessing SqlServer databases. This will use a connection properties file that should look something like the following:

    driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbcUri=jdbc:sqlserver://server_address:server_port
database=database_name
user=user_name
password=password
  11. class SqlServerServiceProvider extends SqlBackendProvider

Value Members

  1. object GenericSqlDialect extends SqlDialect with Product with Serializable

    This is a "generic" implementation of a edu.chop.cbmi.dataExpress.backends.SqlDialect.

    This is a "generic" implementation of a edu.chop.cbmi.dataExpress.backends.SqlDialect. It is designed to work reasonably well in databases that conform to the SQL standard. In general, this object should only be used as a last resort when attempting to connect to an unsupported database.

    See also

    edu.chop.cbmi.dataExpress.dataModels

  2. object MySqlDialect extends SqlDialect with Product with Serializable

    SQL dialect for MySQL

    SQL dialect for MySQL

    See also

    edu.chop.cbmi.dataExpress.backends.SqlDialect

  3. object OracleSqlDialect extends SqlDialect with Product with Serializable

    SQL dialect for Oracle

    SQL dialect for Oracle

    See also

    edu.chop.cbmi.dataExpress.backends.SqlDialect

  4. object PostgresSqlDialect extends SqlDialect with Product with Serializable

    SQL dialect for Postgres

    SQL dialect for Postgres

    See also

    edu.chop.cbmi.dataExpress.backends.SqlDialect

  5. object SqLiteDialect extends SqlDialect with Product with Serializable

    SQL dialect for SQLite

    SQL dialect for SQLite

    See also

    edu.chop.cbmi.dataExpress.backends.SqlDialect

  6. object SqlBackendFactory

    Factory for edu.chop.cbmi.dataExpress.backends.SqlBackendFactory

  7. object SqlQueryCache

    Factory for edu.chop.cbmi.dataExpress.backends.SqlQueryCache instances

  8. object SqlServerSqlDialect extends SqlDialect with Product with Serializable

Inherited from AnyRef

Inherited from Any

Ungrouped