Relational Database Handler

In order to provide support for a Sensor Observation Service (SOS) implementation Hyrax will need a handler that allows it to access data in a Relational Database Management System (RDBMS). (This requirement stems from the observation that many of our stake holders store their in situ measurement data in RDBMSs)

It is anticipated RDBMS handler will have a much broader application than just SOS related data sets.

Use Cases

1. Adding the RDH to the BES
2. RDH handles bes:showCatalog request
3. RDH handles a DDX request
4. RDH handles a DDS request
5. RDH handles a DAS request
6. RDH handles a DAP2 data request

Definitions

row set

A row set is an object which encapsulates a set of rows. Database tables are row sets. A database view is a row set. SQL queries return row sets. SQL JOIN operations take row sets as input and produce row sets.

Background

In the past a Java servlet called the DODS Relational Database Server (DRDS) was used to provide DAP access to RDBMS holdings. However this older implementation has a number of shortcomings that preclude it's direct use in our current server architecture:

• No longer supported.
• Uses the Java DAP implementation
• Not a BES module
• Significant memory limitations
• Difficult to configure/localize

Although there has been continuing interest in a DRDS replacement within the OPeNDAP community, no funding has been available to develop a soution until recently. The IOOS project has a need to provide a Sensor Observation Service (SOS) interface for Hyrax. Since much of the sensor data is already held in RDBMSs it is a natural and necessary time to develop a DRDS replacement.

Design

The RDH will be a BES module/plug-in. It will use an implementation of the ODBC (most likely unixODBC) to access the RDMS(s). ODBC Data Sources will be defined at the system level as usual. [1][2] [3] [4] [5]

The RDH will load (and cache) from it's specific configuration section of the BES configuration file a list of ODBC Data Sources that it will serve as DAP data sets.

1. When the RDH receives a request for content (such as a DDS, DDX, DAP2 data etc.) it will identify the DAP dataset from the request, and determine which ODBC Data Source it must interact with to fulfill the request.
2. Then, using ODBC (or SQL) introspection methods in the ODBC API, the RDH will identify the collection of tables and views available in the ODBC Data Source and use them to construct a specialized DDS instance in memory.
3. The RDH will traverse the available tables and views in the ODBC Data Source and it will build a representation of each one as a DAP Sequence object. The columns in each table and view will represented by a DAP variable in the Sequence.
4. Each of these Sequence instances will be added to the DDS instance.
5. When building the Sequences and their variables the RDH will use a custom class factory to build instances of DAP objects that can utilize the ODBC API to extract data from the Result Set of an ODBC brokered database query. Each of the data types generated by the custom factory will have implemented read methods able to read the row set returned through the ODBC API.
6. DAP Constraint Expression
7. Each Sequence requested will be associated with a separate SQL query. As the DDS is serialized, each Sequence will use the ODBC API to send the query to the Data Source and then use the returned row set as the data content for the serialization of it's (the Sequence's) variables.

Details:

• Each simple DAP type implementation will need to be able to:
  • read from the Result Set
  • move the Result Set Cursor to the next Column

(The implementer will have to decide the order of these two operations)

• The Sequence type will have to manage moving the Result Set Cursor from one row to the next.
• Some helper class (ConstraintEvaluator??? ) will have to convert DAP constraint expressions to SQL queries.