Add a way to define custom SQL DDL for Index

[lukasj]

The spec should allow user to define his own, custom DDL for Index definition to support usage of DB specific features, ie MySQL CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX ... or Oracle DB CREATE [BITMAP | MULTIVALUE | VECTOR ] INDEX ... We already have @Column.columnDefinition, so we could add something like @Index.indexDefinition

[loiclefevre]

@lukasj, there is also CREATE SEARCH INDEX ... for Oracle DB:

See JSON Developer Guide
See XML Developer Guide

[gavinking]

So at one stage a did spend a tiny amount of time thinking about this, the only issue is:

1. Just adding options (which was have done in 3.2) wasn't quite enough because as you say we need a way to specify some arbitrary qualifiers that go before the index keyword.
2. Just adding indexDefinition didn't seem especially useful to me because if I'm going to write a whole create index statement, I might as well just add such things in the script specified by jakarta.persistence.schema-generation.create-source. Why use an @Index annotation at all for that?

What we could do though is add a type member to some of these annotations, so you could write stuff like:

@Table(name="TheTable", 
       indexes=@Index(name="TheIndex", 
                      type="fulltext", 
                      columnList="column1, column2",
                      options="with parser MyParser"))

which would result in:

create table TheTable ( ..... fulltext index TheIndex (column1, column2) with parser MyParser)

I think that gives you everything you need for indexes, and the type element also makes sense for @Table and I guess @UniqueConstraint and @ForeignKey as well.

[loiclefevre]

The type for @Index looks exactly like what we need. This is also needed for @Table; I'm not sure about the others though.

I also wanted to double-check if the columnList value is parsed at some point and validated according to the BNF grammar mentioned in the Javadoc?

[gavinking]

I also wanted to double-check if the columnList value is parsed at some point and validated according to the BNF grammar mentioned in the Javadoc?

Good point. In our implementation we do actually parse it, yes. But if we did this, I think we could allow the columnList to be freeform text. It's not really clear at all what value that grammar has.

[loiclefevre]

I can see 3 types of index where validating the grammar could be problematic:

• function based indexes
• JSON Path expressions to be indexed (a special case for first point but with a grammar far more complex)
• multivalue index defined over JSON_Table()