BASE Development information Dynamic API overview Querying the dynamic tables

Dynamic API - Querying the dynamic tables

1. Diagram of classes and methods

A dynamic query always originates from a BioAssaySet or an ExtraValue. All query objects inherit from the DynamicQuery class (not show in the diagram) and it is possible to use all regular methods defined by the Query interface. See the Query API overview

The getSpotData method generates a query that returns all spot data, ie. layer, position and intensities, in the bioassayset. The getPositionData method generates a query that returns position data, ie. position and reporterId. In both cases it is possible to join data from more tables, ie. reporters, raw data and/or extra values. The ExtraValue.getValues method generates a query that returns all extra values, in this case it is not possible to join other tables.

// BioAssaySet, cube = 2, layer = 3, filter = 0
DynamicSpotQuery spots = BioAssaySet.getSpotData();

// Generated SQL
SELECT 
   spt.layer AS layer, 
   spt.position AS position, 
   spt.ch1 AS ch1, spt.ch2 ...
FROM Dynamic#PerSpot spt
WHERE spt.cube = 2 AND spt.layer = 3

DynamicPositionQuery positions = BioAssaySet.getPositionData();
// Generated SQL
SELECT
   pos.position AS position
   pos.reporterId AS reporterId
FROM Dynamic#PerPosition pos
WHERE cube = 2

If the bioassayset is filtered the filter is automatically joined in the query.

// Filtered bioassayset, cube = 2, layer = 3, filter = 5
DynamicSpotQuery spots = BioAssaySet.getSpotData();

// Generated SQL
SELECT 
   spt.layer AS layer, 
   spt.position AS position, 
   spt.ch1 AS ch1, spt.ch2 ...
FROM Dynamic#PerSpot AS spt
INNER JOIN Dynamic#Filter flt
   ON flt.cube = spt.cube 
   AND flt.column = spt.column 
   AND flt.position = spt.position
WHERE spt.cube = 2 AND spt.layer = 3 AND flt.filter = 5

It is possible to modify the query to select other data as well. Use the methods in the Dynamic class to create new Select query elements. If selections are added to the query the default selections are not included. If you want to include those as well, you must manually add them as well. Here is an example were we join the reporters and selects the column and position coordinate and the external id for the reporter:

// BioAssaySet, cube = 2, layer = 3, filter = 0
DynamicSpotQuery spots = BioAssaySet.getSpotData();
spots.joinReporters(JoinType.LEFT);
spots.select(Dynamic.select(VirtualColumn.COLUMN));
spots.select(Dynamic.select(VirtualColumn.POSITION));
spots.select(Dynamic.selectReporter("externalId"));

// Generated SQL
SELECT 
   spt.column AS column, 
   spt.position AS position, 
   rpt.external_id AS externalId
FROM Dynamic#PerSpot AS spt
LEFT JOIN Dynamic#PerPosition AS pos 
   ON spt.cube = pos.cube AND spt.position = pos.position
LEFT JOIN Reporters AS rpt 
   ON rpt.id = pos.reporter_id
WHERE spt.cube = 2 AND spt.layer = 3

A dynamic query also supports grouping and aggregations. For example we can find the number of positions that use each reporter:

// BioAssaySet, cube = 2, layer = 3, filter = 0
DynamicPositionQuery query = BioAssaySet.getPositionData();
Expression reporterId = Dynamic.reporter("id");
Expression positions = Aggregations.count(
   Dynamic.column(VirtualColumn.POSITION)
);
query.select(Select.expression(reporterId, "reporterId"));
query.select(Select.expression(positions, "times"));
query.group(reporterId);

// Generated SQL
SELECT
   pos.reporter_id AS reporterId
   COUNT(pos.position) AS times
FROM Dynamic#PerPosition pos
WHERE pos.cube = 2

It is also possible to join and select extra values: TODO

The result of a dynamic query is always returned as a DynamicResultIterator object. Internally it holds a reference to a JDBC ResultSet object. Since it is an iterator we use the next() and hasNext() methods to move forward in the result set.

The data is accessed through a SqlResult object, which provides some get methods for accessing the data. The index parameter is 1 for the first selected column, 2 for the second and so on. It is possible to use the alias names used in the query by converting the name to an index with the SqlResultIterator.getIndex() method.

Once the end of the results is reached, the resources to the database are automatically released. If you want to abort earlier the close() method should always be called.