edu.isi.pegasus.planner.catalog.work

## Class Database

- java.lang.Object
- - edu.isi.pegasus.planner.catalog.work.Database

All Implemented Interfaces:
  Catalog, WorkCatalog

    public class Database
    extends java.lang.Object
    implements WorkCatalog

This class implements a work catalog on top of a simple table in a JDBC database. This enables a variety of replica catalog implementations in a transactionally safe, concurrent environment. The table must be defined using the statements appropriate for your database - they are part of the setup in $PEGASUS_HOME/sql/create-my-wf.sql for MYSQL database and in $PEGASUS_HOME/sql/create-pg-wf.sql. If you chose to use an unsupported database, please check, if your database either supports sequence number, or if it supports auto increment columns. If your database supports sequences (e.g. PostGreSQL), you can use a setup similar to the following (for Oracle, the autoinc can be implemented via a trigger).

     CREATE TABLE wf_work (
           id         BIGSERIAL PRIMARY KEY,
           basedir    TEXT,
           vogroup    VARCHAR(255),
           workflow   VARCHAR(255),
           run        VARCHAR(255),
           creator    VARCHAR(32),
           ctime      TIMESTAMP WITH TIME ZONE NOT NULL,
           state      INTEGER NOT NULL,
           mtime      TIMESTAMP WITH TIME ZONE NOT NULL,

            CONSTRAINT sk_wf_work UNIQUE(basedir,vogroup,workflow,run)
     );

     CREATE TABLE wf_jobstate (
           wfid       BIGINT REFERENCES wf_work(id) ON DELETE CASCADE,
           jobid      VARCHAR(64),
           state      VARCHAR(24) NOT NULL,
           mtime      TIMESTAMP WITH TIME ZONE NOT NULL,
           site       VARCHAR(64),

            CONSTRAINT pk_wf_jobstate PRIMARY KEY (wfid,jobid)
     );
     CREATE INDEX ix_wf_jobstate ON wf_jobstate(jobid);

     CREATE TABLE wf_siteinfo (
           id         BIGSERIAL PRIMARY KEY,
           handle     VARCHAR(48) NOT NULL,
           mtime      TIMESTAMP WITH TIME ZONE,
           -- gauges
           other      INTEGER DEFAULT 0,
           pending    INTEGER DEFAULT 0,
           running    INTEGER DEFAULT 0,
           -- counters
           success    INTEGER DEFAULT 0,
           smtime     TIMESTAMP WITH TIME ZONE,
           failure    INTEGER DEFAULT 0,
           fmtime     TIMESTAMP WITH TIME ZONE,

            CONSTRAINT sk_wf_siteinfo UNIQUE(handle)
     );

In case of databases that do not support sequences (e.g. MySQL), do not specify the `create sequence`, and use an auto-increment column for the primary key instead, e.g.:

     CREATE TABLE wf_work (
           id         BIGINT AUTO_INCREMENT PRIMARY KEY,
           basedir    TEXT,
           vogroup    VARCHAR(255),
           workflow   VARCHAR(255),
           run        VARCHAR(255),
           creator    VARCHAR(32),
           ctime      DATETIME NOT NULL,
           state      INTEGER NOT NULL,
           mtime      DATETIME NOT NULL,

            CONSTRAINT sk_wf_work UNIQUE(basedir(255),vogroup,workflow,run)
     ) type=InnoDB;

     CREATE TABLE wf_jobstate (
           wfid       BIGINT REFERENCES wf_work(id) ON DELETE CASCADE,
            jobid      VARCHAR(64),
            state      VARCHAR(24) NOT NULL,
            mtime      DATETIME NOT NULL,
            site       VARCHAR(64),

            CONSTRAINT pk_wf_jobstate PRIMARY KEY (wfid,jobid)
     ) type=InnoDB;
     CREATE INDEX ix_wf_jobstate ON wf_jobstate(jobid);

     CREATE TABLE wf_siteinfo (
           id         BIGINT AUTO_INCREMENT PRIMARY KEY,
           handle     VARCHAR(48) NOT NULL,
           mtime      DATETIME,
           -- gauges
           other      INTEGER DEFAULT 0,
           pending    INTEGER DEFAULT 0,
           running    INTEGER DEFAULT 0,
           -- counters
           success    INTEGER DEFAULT 0,
           smtime     DATETIME,
           failure    INTEGER DEFAULT 0,
           fmtime     DATETIME,

            CONSTRAINT sk_wf_siteinfo UNIQUE(handle)
     ) type=InnoDB;

The site attribute should be specified whenever possible. For the shell planner, it will always be of value "local".

Version:
  $Revision$
Author:
  Karan Vahi

### Field Summary

Fields
`private boolean` `m_autoinc` Remembers if obtaining generated keys will work or not.
`protected java.sql.Connection` `mConnection` Maintains the connection to the database over the lifetime of this instance.
`private static java.lang.String` `mConnectionError` This message is sent whenever one of the member function is executed which relies on an established database context.
`private static java.lang.String[]` `mCStatements` The statement to prepare to slurp attributes.
`protected LogManager` `mLogger` The handle to the logging object.
`protected java.sql.PreparedStatement[]` `mStatements` Maintains an essential set of prepared statement, ready to use.

### Fields inherited from interface edu.isi.pegasus.planner.catalog.WorkCatalog

`c_prefix, DB_PREFIX, VERSION`

### Fields inherited from interface edu.isi.pegasus.planner.catalog.Catalog

`DB_ALL_PREFIX`

### Constructor Summary

Constructors
Constructor and Description
`Database()` Default empty constructor creates an object that is not yet connected to any database.
`Database(java.lang.String jdbc, java.lang.String url, java.lang.String username, java.lang.String password)` Convenience c'tor: Establishes the connection to the work catalog database.

### Method Summary

All Methods Instance Methods Concrete Methods
`void` `close()` Explicitely free resources before the garbage collection hits.
`boolean` `connect(java.util.Properties props)` Establishes a connection to the database from the properties.
`void` `connect(java.lang.String url, java.lang.String username, java.lang.String password)` Connects to the database.
`int` `delete(java.lang.String basedir, java.lang.String vogroup, java.lang.String label, java.lang.String run)` Deletes a mapping from the work catalog.
`protected java.sql.PreparedStatement` `getStatement(int i)` Singleton manager for prepared statements.
`int` `insert(java.lang.String basedir, java.lang.String vogroup, java.lang.String label, java.lang.String run, java.lang.String creator, java.util.Date cTime, java.util.Date mTime, int state)` Inserts a new mapping into the work catalog.
`boolean` `isClosed()` Predicate to check, if the connection with the catalog's implementation is still active.

### Methods inherited from class java.lang.Object

`clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

### Field Detail

#### mConnectionError

    private static final java.lang.String mConnectionError

This message is sent whenever one of the member function is executed which relies on an established database context.

See Also:
  Constant Field Values

#### mConnection

    protected java.sql.Connection mConnection

Maintains the connection to the database over the lifetime of this instance.

#### mStatements

    protected java.sql.PreparedStatement[] mStatements

Maintains an essential set of prepared statement, ready to use.

#### mLogger

    protected LogManager mLogger

The handle to the logging object.

#### mCStatements

    private static final java.lang.String[] mCStatements

The statement to prepare to slurp attributes.

#### m_autoinc

    private boolean m_autoinc

Remembers if obtaining generated keys will work or not.

### Constructor Detail

#### Database

    public Database(java.lang.String jdbc,
                    java.lang.String url,
                    java.lang.String username,
                    java.lang.String password)
             throws java.lang.LinkageError,
                    java.lang.ExceptionInInitializerError,
                    java.lang.ClassNotFoundException,
                    java.sql.SQLException

Convenience c'tor: Establishes the connection to the work catalog database. The usual suspects for the class name include:

     org.postgresql.Driver
     com.mysql.jdbc.Driver
     com.microsoft.jdbc.sqlserver.SQLServerDriver
     SQLite.JDBCDriver
     sun.jdbc.odbc.JdbcOdbcDriver

Parameters:
  `jdbc` - is a string containing the full name of the java class that must be dynamically loaded. This is usually an external jar file which contains the Java database driver.
  `url` - is the database driving URL. This string is database specific, and tell the JDBC driver, at which host and port the database listens, permits additional arguments, and selects the database inside the rDBMS to connect to. Please refer to your JDBC driver documentation for the format and permitted values.
  `username` - is the database user account name to connect with.
  `password` - is the database account password to use.
Throws:
  `java.lang.LinkageError` - if linking the dynamically loaded driver fails. This is a run-time error, and does not need to be caught.
  `java.lang.ExceptionInInitializerError` - if the initialization function of the driver's instantiation threw an exception itself. This is a run-time error, and does not need to be caught.
  `java.lang.ClassNotFoundException` - if the class in your jdbc parameter cannot be found in your given CLASSPATH environment. Callers must catch this exception.
  `java.sql.SQLException` - if something goes awry with the database. Callers must catch this exception.

#### Database

    public Database()

Default empty constructor creates an object that is not yet connected to any database. You must use support methods to connect before this instance becomes usable.

See Also:
  `connect( String, String, String )`

### Method Detail

#### connect

    public void connect(java.lang.String url,
                        java.lang.String username,
                        java.lang.String password)
                 throws java.sql.SQLException

Connects to the database. This is effectively an accessor to initialize the internal connection instance variable. Warning! You must call `Class.forName( String )` yourself to load the database JDBC driver jar!

Parameters:
  `url` - is the database driving URL. This string is database specific, and tell the JDBC driver, at which host and port the database listens, permits additional arguments, and selects the database inside the rDBMS to connect to. Please refer to your JDBC driver documentation for the format and permitted values.
  `username` - is the database user account name to connect with.
  `password` - is the database account password to use.
Throws:
  `java.sql.SQLException` - if something goes awry with the database. Callers must catch this exception.
See Also:
  `DriverManager.getConnection( String, String, String )`

#### connect

    public boolean connect(java.util.Properties props)

Establishes a connection to the database from the properties. You can specify a `driver` property to contain the class name of the JDBC driver for your database. This property will be removed before attempting to connect. You must speficy a `url` property to describe the connection. It will be removed before attempting to connect.

Specified by:
  `connect` in interface `Catalog`
Parameters:
  `props` - is the property table with sufficient settings to establish a link with the database. The minimum key required key is "url", and possibly "driver". Any other keys depend on the database driver.
Returns:
  true if connected, false if failed to connect.
Throws:
  `java.lang.Error` - subclasses for runtime errors in the class loader.
See Also:
  `DriverManager.getConnection( String, Properties )`

#### close

    public void close()

Explicitely free resources before the garbage collection hits.

Specified by:
  `close` in interface `Catalog`

#### isClosed

    public boolean isClosed()

Predicate to check, if the connection with the catalog's implementation is still active. This helps determining, if it makes sense to call `close()`.

Specified by:
  `isClosed` in interface `Catalog`
Returns:
  true, if the implementation is disassociated, false otherwise.
See Also:
  `close()`

#### getStatement

    protected java.sql.PreparedStatement getStatement(int i)
                                               throws java.sql.SQLException

Singleton manager for prepared statements. This instruction checks that a prepared statement is ready to use, and will create an instance of the prepared statement, if it was unused previously.

Parameters:
  `i` - is the index which prepared statement to check.
Returns:
  a handle to the prepared statement.
Throws:
  `java.sql.SQLException` - in case of unable to delete entry.

#### insert

    public int insert(java.lang.String basedir,
                      java.lang.String vogroup,
                      java.lang.String label,
                      java.lang.String run,
                      java.lang.String creator,
                      java.util.Date cTime,
                      java.util.Date mTime,
                      int state)
               throws WorkCatalogException

Inserts a new mapping into the work catalog.

Specified by:
  `insert` in interface `WorkCatalog`
Parameters:
  `basedir` - the base directory
  `vogroup` - the vo to which the user belongs to.
  `label` - the label in the DAX
  `run` - the run number.
  `creator` - the user who is running.
  `cTime` - the creation time of the DAX
  `mTime` - the modification time.
  `state` - the state of the workflow
Returns:
  number of insertions, should always be 1. On failure, throw an exception, don't use zero.
Throws:
  `WorkCatalogException` - in case of unable to delete entry.

#### delete

    public int delete(java.lang.String basedir,
                      java.lang.String vogroup,
                      java.lang.String label,
                      java.lang.String run)
               throws WorkCatalogException

Deletes a mapping from the work catalog.

Specified by:
  `delete` in interface `WorkCatalog`
Parameters:
  `basedir` - the base directory
  `vogroup` - the vo to which the user belongs to.
  `label` - the label in the DAX
  `run` - the run number.
Returns:
  number of insertions, should always be 1. On failure, throw an exception, don't use zero.
Throws:
  `WorkCatalogException` - in case of unable to delete entry.