support for conditional index

[carmi2214]

Hi, I think it would be very useful to add in Index.java a property called condition / where, to use in DB types that support the feature.

For example:

@Target({})
@Retention(RUNTIME)
public @interface Index {

    /**
     * (Optional) The name of the index; defaults to a provider-generated name.
     */
    String name() default "";

    /**
     * (Required) The names of the columns to be included in the index, 
     * in order.
     */
    String columnList();

    /**
     * (Optional) Whether the index is unique.
     */
    boolean unique() default false;

    /**
     * (Optional) The contents of the where clause while creating the index.
     */
    String where() default "";

}

Useful for indicies on tables with logical deletion, or many other cases

[gavinking]

How many databases support this? Just Postgres and SQL Server?

[carmi2214]

How many databases support this? Just Postgres and SQL Server?

Yep, those two and SQLite

[beikov]

It can be easily and safely emulated by transforming the index to a unique one I.e. index on tbl1(col1) where col2 = 1 to unique index on tbl1(col1, case when col2 = 2 then primaryKey else null end) because nulls in unique indexes are ignored by default.

[gavinking]

Mmmmm. So look, every database has a bunch of fancy proprietary options for creating indexes. We can't possibly expand the @Index annotation to accommodate all these things individually.

Now, perhaps it could make sense to add something like indexDefinition in analogy to columnDefinition in @Column, so you could write:

@Index(name="orders_unbilled_index ", 
       columnList="order_nr", 
       indexDefinition ="where billed is not true")

To produce this DDL:

create index orders_unbilled_index on orders (order_nr)
       where billed is not true;

And of course you could also use it for lots of other things.

I guess that's kinda intellectually consistent at least.

On the other hand, you could just put that create index statement in a DDL script, and the JPA provider will happily execute the script for you, so the need is not really that acute, IMO.

Dunno.

[Note: I wrote this before seeing @beikov's comment.]

[gavinking]

It can be easily and safely emulated by transforming the index to a unique one

Ah, that's interesting.

[carmi2214]

It can be easily and safely emulated by transforming the index to a unique one I.e. index on tbl1(col1) where col2 = 1 to unique index on tbl1(col1, case when col2 = 2 then primaryKey else null end) because nulls in unique indexes are ignored by default.

Thank you for the suggestion, though still trying to add that option since it seems more like an elegant solution, for the relevant databases, IMO.

[carmi2214]

Mmmmm. So look, every database has a bunch of fancy proprietary options for creating indexes. We can't possibly expand the @Index annotation to accommodate all these things individually.

Looks awsome. Would using it in ORM libraries be acceptable?

[gavinking]

@carmi2214 We need to pick just one of those alternatives. You can't have both 😅

[carmi2214]

Of course - excuse me, by "awsome" I meant to respond on the indexDefinition idea 😄

[gavinking]

Actually, since the @ForeignKey annotation defines a foreignKeyDefinition member which is supposed to specify the whole DDL definition of the FK, instead of just some text to be appended at the end, we had better stay away from the use of "definition" here.

So perhaps what we should do is call it options:

Index(name = "orders_unbilled_index ", 
      columnList = "order_nr", 
      options = "where billed is not true")

We could even add options members to other annotations like @UniqueConstraint, @ForeignKey, and even @Table and @Column.

I think this would be a quite useful enhancement.

[carmi2214]

@gavinking Great! So, what's next?

[gavinking]

So your nagging worked: #485.

But now lets see what other people think.