vollt
vollt copied to clipboard
Array support
I've have been working on adding array support into VOLLT lately and I have partially succeeded:
- What works: Queries using array columns, and UDF which support them as parameters and return values.
- What doesn't work: array element access (
myarray[1]
ormyarray[1:4]
) on ADQL queries.
Some implementation notes:
- Of the DBMS supported by VOLLT, only PostgreSQL and H2 (used in testing) support array columns, although with different syntax and functionality.
- The name of array types can be different: a real array is
REAL ARRAY
in h2 and_real
in PostgreSQL. Thejava.sql.Types
class can be used to obtain the type in an agnostic manner, but it seems to be exclusive (i.e, for a real arraytype == Types.ARRAY
). An extra step is needed, to obtain the base type, with eithergetColumnTypeName
(by replacing the underscore orARRAY
) orgetBaseTypeName
. -
java.sql.Array
fields are returned as Object arrays, but due to the underlying usage of STIL for output they need to be converted into their primitive counterparts, where null values aren't allowed. This has been achieved with Apache Commons Lang3 (ArrayUtils
andStringUtils
). - Both
DBType
andVotType
need an arraysize so the outputs can be managed accordingly. - In the case of UDF, the regexes have been updated to allow a parameters or return type to end with
\[([0-9]+)?\]
, and parsing it so the arraysize is respected in both input and output.
As for the array element access, I am having some difficulties on adding support: looking into the code I think that it is a special (for lack of a better word) case of ADQLColumn
, or perhaps an operation, where you call for the n-th or the n to m-th elements of an existing array database column. The only main difference I see is that the arraysize will differ of the one in the metadata (thus making arraysize
not final
).
It might be solved by adding a new rule such as ARRAY_ELEMENT_ACCESS
, which would take care of these on-the-fly (so to speak) update of the metadata only with array columns... but I'm not sure.