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

class MySqlBackend extends SqlBackend

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
Linear Supertypes
SqlBackend, Serializable, Serializable, Product, Equals, AnyRef, Any
Ordering
  1. Alphabetic
  2. By Inheritance
Inherited
  1. MySqlBackend
  2. SqlBackend
  3. Serializable
  4. Serializable
  5. Product
  6. Equals
  7. AnyRef
  8. Any
  1. Hide All
  2. Show All
Visibility
  1. Public
  2. All

Instance Constructors

  1. new MySqlBackend(connectionProperties: Properties, _sqlDialect: SqlDialect = null, _driverClassName: String = null)

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
    Definition Classes
    SqlBackend
  5. val SUPPORTS_MULT_RS: Boolean
    Definition Classes
    MySqlBackend → SqlBackend
  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

    Definition Classes
    SqlBackend
  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

    Definition Classes
    SqlBackend
  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
    Definition Classes
    SqlBackend
  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

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

    Definition Classes
    SqlBackend
  12. def close(connection: Connection): Unit

    Closes a java.sql.Connection

    Closes a java.sql.Connection

    connection

    the connection to close

    Definition Classes
    SqlBackend
  13. def commit(): Boolean

    Commit any open transactions to the database

    Commit any open transactions to the database

    Definition Classes
    SqlBackend
  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

    Definition Classes
    SqlBackend
  15. def connect(): Connection

    Opens the connection to the database

    Opens the connection to the database

    Definition Classes
    SqlBackend
  16. var connection: Connection
    Definition Classes
    SqlBackend
  17. val connectionProperties: Properties
    Definition Classes
    MySqlBackend → SqlBackend
  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

    Definition Classes
    SqlBackend
  19. val driverClassName: String
    Definition Classes
    SqlBackend
  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
    Definition Classes
    SqlBackend
  21. def endTransaction(): Boolean

    End the existing transaction

    End the existing transaction

    Definition Classes
    SqlBackend
  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

    Definition Classes
    SqlBackend
  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

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

    In order to avoid loading all results of a query into memory, mySQL requires the fetch size of a a database query to be -2^31 or Integer.MIN_VALUE, all other values are ignored.

    In order to avoid loading all results of a query into memory, mySQL requires the fetch size of a a database query to be -2^31 or Integer.MIN_VALUE, all other values are ignored.

    sqlStatement

    the SELECT statement to run

    bindvars

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

    fetchSize

    any value supplied will be ignored

    returns

    java.sql.ResultSet representing the query

    Definition Classes
    MySqlBackend → SqlBackend
  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

    Definition Classes
    SqlBackend
  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

    returns the URI for the JDBC connection

    Definition Classes
    SqlBackend
  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

    Definition Classes
    SqlBackend
  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

    Definition Classes
    SqlBackend
  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
    Definition Classes
    SqlBackend
  37. def rollback(): Boolean

    Rollback an existing transaction

    Rollback an existing transaction

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

    Start a new transaction (potentially closing the old one)

    Start a new transaction (potentially closing the old one)

    Definition Classes
    SqlBackend
  40. var statementCache: SqlQueryCache
    Definition Classes
    SqlBackend
  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

    Definition Classes
    SqlBackend
  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

    Definition Classes
    SqlBackend
  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 SqlBackend

Inherited from Serializable

Inherited from Serializable

Inherited from Product

Inherited from Equals

Inherited from AnyRef

Inherited from Any

Ungrouped