The first goal for the Sensor Data Access for Rasdaman project was to implement a JDBC driver for Rasdaman and to create a Hibernate dialect, which uses the JDBC driver to connect Rasdaman as a data storage backend to the 52°North Sensor Observation Service (SOS). This was the solution we came up with for the first part and we think this was a good choice, because now, at the mid term evaluation, we have a JDBC driver and a Hibernate dialect – both of which support multidimensional array queries.
Even though I will summarize the project’s status, you can find a more detailed description about the project’s evolution on the Sensor Data Access for Rasdaman wiki page.
Rasdaman JDBC driver
The choice to implement a JDBC driver for Rasdaman instead of using the Rasdaman Java API came naturally, because one of the mentors was already working on a project trying to adapt HSQLDB to support Rasdaman as a backend for array queries. The new project is called ASQLDB. The HSQLDB project already had a JDBC driver, so our task was to adapt it to support multidimensional array queries. This solution is an elegant one and cut off a lot of work which would have slowed down the evolution of the project.
In Fig. 1 you can see how the ASQLDB works with Rasdaman.
The HSQLDB project already had a Hibernate dialect – making our work easier again. Until now we didn’t have to change anything from the dialect and we’ve worked only on testing the dialect by making multiple select queries. To accomplish this task, two new Hibernate user types were implemented. We’ve also defined the classes which are mapped to the results returned by Hibernate. These tests prove that the Hibernate dialect works correctly with the ASQLDB’s JDBC driver. More than this, the test’s code is going to be a good usage example for the ASQLDB’s future users. That’s why we are going to add some of the test queries to the ASQLDB’s documenation.
After adapting the JDBC driver to return multidimensional array results, the ASQLDB’s GUI was also affected, because now a huge result would have to be displayed in the graphic interface. To deal with this problem in an elegant way, we’ve decided to write the rasdaman result to a file and return the name of the file instead of the multidimensional array result. This way, the GUI returns an easy to read result and it provides a file from where the data can be read and used for further processing. In Fig. 2 you can see an example of this behavior.
We have four query types:
Array queries: select id, coll + 5 as coll from rastest; Constant queries: select id, 24c as coll from rastest; Interval queries: select id, sdom(coll) as coll from rastest; Byte queries: select id, coll[ 100, 150 ] as coll from rastest;
A “plain language” description of the queries would be:
Array queries: select the id and all the values from the multidimensional array, but I want each value from the array to be increased by 5 units. You can see a screenshot for this query in Fig. 3 Constant queries: select the id and the value 24. It means that for each entry, the 24 value will be displayed - doesn't mater what value is stored in the table. You can see a screenshot for this query in Fig. 4 Interval queries: select the id and the dimensions of the array. For example, for a 2D array with the dimensions 0-250, 0-250, this query would return [0:250,0:250]. You can see a screenshot of this query in Fig. 5 Byte queries: select the id and the value located at position (100, 150). The position is represented like a point in space - depending on how many dimensions the array has. Here, we have a two dimensional array. You can see a screenshot of this query in Fig. 6
The ASQLDB rastest table was created like this:
CREATE TABLE RASTEST ( id INTEGER NOT NULL, coll varchar(40) ARRAY NOT NULL, PRIMARY KEY(ID))
The ASQLDB doesn’t yet automatically insert data to Rasdaman. Therefore, in order to populate the rastest table, you have to first create and optionally populate the Rasdaman collection using rasql or using the Rasdaman Java API. After creating the collection in Rasdaman, you can associate it with a table cell from ASQLDB using the collection’s OID. Below is an example of how you can do this:
INSERT INTO RASTEST VALUES( 0, ARRAY['rastest:collection_oid']);
The result type returned by the JDBC driver for the column “coll”, which is an array column, will differ depending on the type of query that is sent to Rasdaman. This is why we have four types of queries. The return types are especially important for the Hibernate tests, because the classes in which the received result must be stored have to declare a correct type for the array column associated with the Rasdaman result.
- For the array queries, the user will receive an instance of the RasGMArray class for the column coll. This object provides information about the element’s size and it can give a byte array version of the result – see Fig. 3
- The constant queries don’t make a Rasdaman query transformation. This is not needed. So, the result will be exactly the requested constant value – see Fig. 4
- The interval queries return the interval in which the resulted values can be enclosed. This result has a rasj type – RasMInterval – and it provides all the usual mathematical operations that can be made using intervals (e.g. intersection, difference, union) – see Fig. 5
- The byte queries return a single cell from the multidimensional array. Because all the values in the test array are equal to 1, it will return the value 1, as you can see in Fig. 6.
For more query examples, check the select tests from the hibernate tests repository.
Having a JDBC Driver for ASQLDB and a Hibernate dialect, we can now concentrate on creating Rasdaman-Hibernate mappings for the 52° North SOS and on the other objectives presented in the first blog post.