ARRAY type as understood by H2

[Lukas Eder]

Hello,

Many databases that support ARRAY types actually provide typed access
to arrays. By that, I mean not just ARRAYs or Object[], but any of
these:

- Oracle: VARRAY(n) of VARCHAR(m)
- HSQLDB: VARCHAR(n) ARRAY
- Postgres: VARCHAR(n)[]

In H2, I only seem to be able to create ARRAY types without any
information about the contained elements:
http://www.h2database.com/html/datatypes.html#array_type

1. Is that true or am I missing something?
2. Will that stay that way or is there a SQL syntax enhancement on the
roadmap for more type-safety?

Cheers
Lukas

[Thomas Mueller]

Hi,

> In H2, I only seem to be able to create ARRAY types without any
> information about the contained elements

Yes, that's true.

> 1. Is that true or am I missing something?

No.

> 2. Will that stay that way or is there a SQL syntax enhancement on the
> roadmap for more type-safety?

I just added this to the roadmap. Patches are always welcome of course!

What exactly is your use case? Is it just a compatibility problem for
you? Or do you mainly need some way to ensure the data type is
correct? In that case you could use a domain with a check constraint.
But as H2 can convert all data types to VARCHAR, it wouldn't help
much.

Regards,
Thomas

[Lukas Eder]

> > 2. Will that stay that way or is there a SQL syntax enhancement on the
> > roadmap for more type-safety?
>
> I just added this to the roadmap.

Great news! Thank you.

> Patches are always welcome of course!

I don't know the inner workings of H2 well enough to easily implement
that correctly... In any case, it can also turn out to be a very
complex requirement, see below

> What exactly is your use case? Is it just a compatibility problem for
> you?

My use case is to provide correct support for array types in many SQL
dialects to include that in my database abstraction library jOOQ:
http://jooq.sourceforge.net

In jOOQ, apart from the functionality you have in JaQu as well, I'm
trying to standardise access to vendor-specific functionality like
stored procedures / functions, UDT's, ARRAY types, etc. One reason why
I think these advanced features are used so little by the Java folks
is because of a lack of standardisation even through JDBC. But with
modern databases, these features become more and more useful. The best
example of how powerful ARRAY types can be is the Postgres RDBMS, in
my opinion. ARRAY types can be converted to tables by functions, and
then used in regular relational logic. But also with plain stored
procedures (no relational logic involved), advanced data types
including arrays are very useful. Hence, so far, I have implemented
ARRAY support in Oracle, Postgres, and HSQLDB. And H2 is the next in
line.

So for my use case, I think I can regard today's H2 ARRAY
implementation as compatible to ARRAY of OTHER, which maps well to
Object[] anyway. I'll be looking forward to seeing more specialised
types in the future!

Cheers
Lukas

[Thomas Mueller]

Hi,

> My use case is to provide correct support for array types in many SQL
> dialects to include that in my database abstraction library jOOQ:
> http://jooq.sourceforge.net

I'm afraid this will be quite hard to achieve...

> used so little by the Java folks
> is because of a lack of standardisation even through JDBC.

I think your are right.

Regards,
Thomas

[Lukas Eder]

Hello,

> > My use case is to provide correct support for array types in many SQL
> > dialects to include that in my database abstraction library jOOQ:
> >http://jooq.sourceforge.net
>
> I'm afraid this will be quite hard to achieve...

It works out quite nicely with your current ARRAY implementation. I am
running some more integration tests this week (including your recent
improvements about the "inline ARRAY syntax") before I can release the
next version also with H2's support for Object[]. Let's see how it is
adopted.

Anyway,
Thanks for your help!

Lukas