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
    Definition Classes
    backends
  • GenericSqlDialect
  • MySqlBackend
  • MySqlDialect
  • OracleBackend
  • OracleServiceProvider
  • OracleSqlDialect
  • PostgresBackend
  • PostgresSqlDialect
  • SqLiteBackend
  • SqLiteDialect
  • SqlBackend
  • SqlBackendFactory
  • SqlBackendProvider
  • SqlDialect
  • SqlQueryCache
  • SqlServerBackend
  • SqlServerServiceProvider
  • SqlServerSqlDialect

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.

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

Linear Supertypes
Serializable, Serializable, Product, Equals, AnyRef, Any
Known Subclasses
MySqlBackend, OracleBackend, PostgresBackend, SqLiteBackend, SqlServerBackend
Ordering
  1. Alphabetic
  2. By Inheritance
Inherited
  1. SqlBackend
  2. Serializable
  3. Serializable
  4. Product
  5. Equals
  6. AnyRef
  7. Any
  1. Hide All
  2. Show All
Visibility
  1. Public
  2. All

Instance Constructors

  1. new SqlBackend(connectionProperties: Properties, sqlDialect: SqlDialect, driverClassName: String)

Value Members

  1. final def !=(arg0: Any): Boolean
    Definition Classes
    AnyRef → Any
  2. final def ##(): Int
    Definition Classes
    AnyRef → Any
  3. final def ==(arg0: Any): Boolean
    Definition Classes
    AnyRef → Any
  4. val CACHESIZE: Int
  5. val SUPPORTS_MULT_RS: Boolean
  6. final def asInstanceOf[T0]: T0
    Definition Classes
    Any
  7. def batchInsert(tableName: String, table: DataTable[_], schemaName: Option[String] = None): Int

    Perform a batch insert into a table.

    Perform a batch insert into a table. This is the preferred insertion method for large insert operations

    tableName

    The name of the table to do the insert

    table

    A edu.chop.cbmi.dataExpress.dataModels.DataTable that holds the data for the insert

    schemaName

    The schema where the table is located

  8. def batchInsertRows(tableName: String, rows: Iterator[DataRow[_]], columnNames: List[String], schemaName: Option[String] = None): Int

    Perform a batch insert into a table.

    Perform a batch insert into a table. This is the preferred insertion method for large insert operations

    tableName

    The name of the table to do the insert

    rows

    An interable over edu.chop.cbmi.dataExpress.dataModels.DataRow that holds the data for the insert

    columnNames

    List[String] of the column names

    schemaName

    The schema where the table is located Assumes all rows have the same length and column names

  9. def checkResultSetThenExecute(code: ⇒ Option[ResultSet]): Option[ResultSet]

    Finesses the situation where some databases allow for only one open result set at a time.

    Finesses the situation where some databases allow for only one open result set at a time. This is called whenever a new result set might be created to ensure that any open result set is closed.

    code

    a code block that is likely to return a ResultSet

    Attributes
    protected
  10. def clone(): AnyRef
    Attributes
    protected[java.lang]
    Definition Classes
    AnyRef
    Annotations
    @native() @throws( ... )
  11. def close(): Any

    Closes the java.sql.connection associated with instances of the backend

  12. def close(connection: Connection): Unit

    Closes a java.sql.Connection

    Closes a java.sql.Connection

    connection

    the connection to close

  13. def commit(): Boolean

    Commit any open transactions to the database

  14. def connect(props: Properties): Connection

    Opens a connection to the database via properties defined in a java.util.Properties object

    Opens a connection to the database via properties defined in a java.util.Properties object

    props

    the java.util.Properties object with the connect information

  15. def connect(): Connection

    Opens the connection to the database

  16. var connection: Connection
  17. val connectionProperties: Properties
  18. def createTable(tableName: String, columnNames: List[String], dataTypes: List[DataType], schemaName: Option[String] = None): Boolean

    Create a new table

    Create a new table

    tableName

    the name of the table

    columnNames

    the list of column names to use

    dataTypes

    a list of DataExpress edu.chop.cbmi.dataExpress.dataModels.DataType objects that correspond to the columns

  19. val driverClassName: String
  20. def dropTable(tableName: String, cascade: Boolean = false, schemaName: Option[String] = None): Boolean

    Drops a database table, optionally cascading constraints

    Drops a database table, optionally cascading constraints

    tableName

    the name of the table to drop

    cascade

    when 

    true
  21. def endTransaction(): Boolean

    End the existing transaction

  22. final def eq(arg0: AnyRef): Boolean
    Definition Classes
    AnyRef
  23. def execute(sqlStatement: String, bindVars: Seq[Option[_]] = Seq.empty[Option[_]]): Boolean

    Runs UPDATE, INSERT, or DELETE statement.

    Runs UPDATE, INSERT, or DELETE statement.

    sqlStatement

    the statement to run

    bindVars

    set of bind variables to use

    returns

    true if no errors were encountered

  24. def executeBatch[T](statement: PreparedStatement, values: Iterator[T], batchSize: Int, callback: (T) ⇒ Seq[Option[_]]): Int

    Executes a set of java.sql.PreparedStatements in a batch mode.

    Executes a set of java.sql.PreparedStatements in a batch mode. The primary operation where this makes sense is INSERT operations, but one could imagine a stored procedure used in this way as well

    statement

    The java.sql.PreparedStatement to be used for the insert

    values

    The bind values of any bind variables that might be needed for placeholders in the prepared statement

    batchSize

    The size of the batch to use before executing the batch in the database

    callback

    A function that will be applied to each set of data values before they are added to the prepared statement

    returns

    the number of statements that correctly executed

  25. def executeQuery(sqlStatement: String, bindvars: Seq[Option[_]] = Seq.empty[Option[_]], fetchSize: Int = 20): ResultSet

    Will execute a SQL SELECT statement with a fetch size of 20.

    Will execute a SQL SELECT statement with a fetch size of 20.

    sqlStatement

    the SELECT statement to run

    bindvars

    set of bind variables for substitution in the statement (see java.sql.PreparedStatement)

    returns

    java.sql.ResultSet representing the query

  26. def executeReturningKeys(sqlStatement: String, bindVars: Seq[Option[_]]): DataRow[_]

    Executes a SQL statement where new keys are auto-generated from an auto-incrementing primary key.

    Executes a SQL statement where new keys are auto-generated from an auto-incrementing primary key. Autogenerated keys are returned as a result of the statement. Not all databases implement this, and their individual implementations vary widely

    sqlStatement

    the statement to be run

    bindVars

    set of bind variables to use

  27. def finalize(): Unit
    Attributes
    protected[java.lang]
    Definition Classes
    AnyRef
    Annotations
    @throws( classOf[java.lang.Throwable] )
  28. final def getClass(): Class[_]
    Definition Classes
    AnyRef → Any
    Annotations
    @native()
  29. def get_jdbcUri: String

    returns the URI for the JDBC connection

  30. def insertReturningKeys(tableName: String, row: DataRow[_], schemaName: Option[String] = None): DataRow[_]

    Insert a single edu.chop.cbmi.dataModels.DataRow into a table, returning auto-generated primary keys

    Insert a single edu.chop.cbmi.dataModels.DataRow into a table, returning auto-generated primary keys

    tableName

    The name of the table to do the insert

    row

    The edu.chop.cbmi.dataExpress.dataModels.DataRow to insert

    schemaName

    The schema where the table is located

  31. def insertRow(tableName: String, row: DataRow[_], schemaName: Option[String] = None): Boolean

    Insert a single edu.chop.cbmi.dataExpress.dataModels.DataRow into a table

    Insert a single edu.chop.cbmi.dataExpress.dataModels.DataRow into a table

    tableName

    The name of the table to do the insert

    row

    The edu.chop.cbmi.dataExpress.dataModels.DataRow to insert

    schemaName

    The schema where the table is located

  32. final def isInstanceOf[T0]: Boolean
    Definition Classes
    Any
  33. final def ne(arg0: AnyRef): Boolean
    Definition Classes
    AnyRef
  34. final def notify(): Unit
    Definition Classes
    AnyRef
    Annotations
    @native()
  35. final def notifyAll(): Unit
    Definition Classes
    AnyRef
    Annotations
    @native()
  36. def prepStatement(sqlStatement: PreparedStatement, bindVars: Seq[Option[_]]): Unit

    Generate a JDBC java.sql.PreparedStatement using bindVariables

    Generate a JDBC java.sql.PreparedStatement using bindVariables

    sqlStatement

    A java.sql.PreparedStatement that has place holders for bind variables

    bindVars

    A list of values to be bound to the statement

    Attributes
    protected
  37. def rollback(): Boolean

    Rollback an existing transaction

  38. val sqlDialect: SqlDialect
  39. def startTransaction(): Boolean

    Start a new transaction (potentially closing the old one)

  40. var statementCache: SqlQueryCache
  41. final def synchronized[T0](arg0: ⇒ T0): T0
    Definition Classes
    AnyRef
  42. def truncateTable(tableName: String, schemaName: Option[String] = None): Boolean

    Truncates a SQL table

    Truncates a SQL table

    tableName

    the name of the table to truncate

  43. def updateRow(tableName: String, updated_row: DataRow[_], filter: List[(String, Any)], schemaName: Option[String] = None): Boolean

    Update existing table rows.

    Update existing table rows. In order to allow specificity, the filter parameter is a set of (columnName, value) tuples. These tuples get converted into the WHERE clause. For example:

    List(("id",12345),("type","Luggage Combination"))

    passed in as a filter is converted to the SQL WHERE id = 12345 AND type = 'Luggage Combination' Currently, there is no support for operations other than equality on filters (e.g. WHERE id > 100).

    tableName

    The name of the table to do the insert

    updated_row

    The contents to be used for the update

    filter

    A list of (columnName,value) tuples to be used when constructing the WHERE clause

    schemaName

    The schema where the table is located

  44. final def wait(): Unit
    Definition Classes
    AnyRef
    Annotations
    @throws( ... )
  45. final def wait(arg0: Long, arg1: Int): Unit
    Definition Classes
    AnyRef
    Annotations
    @throws( ... )
  46. final def wait(arg0: Long): Unit
    Definition Classes
    AnyRef
    Annotations
    @native() @throws( ... )

Inherited from Serializable

Inherited from Serializable

Inherited from Product

Inherited from Equals

Inherited from AnyRef

Inherited from Any

Ungrouped