Support for partial index

[billy1624]

Discussed in SeaQL/sea-orm#872

^{Originally posted by @onichandame}

Motivation

Sqlite supports a WHERE clause on creating an index. see docs

This feature is useful if only a subset of rows need to be indexed. Without it the index must cover all rows, which unnecessarily consumes more disk space.

Proposed Solutions

Something like sea_query::Index::create().filter(<Condition>) for use with entities, and sea_query::Index::create().filter_raw("<Where Clause>") for more freedom.

Additional Information

[billy1624]

As far as I know, both SQLite and PostgreSQL supported partial index, docs:

• https://www.sqlite.org/partialindex.html
• https://www.postgresql.org/docs/current/indexes-partial.html

But MySQL doesn't support it. I think we could introduce this feature in SeaQuery as 2 out of 3 databases support it.

[kyoto7250]

@billy1624

I have an interest.

Could you leave it to me?

[kyoto7250]

@ikrivosheev

Thank you, I will try it!

[billy1624]

Thanks! @kyoto7250

[kyoto7250]

@billy1624

I created WIP PR. #405
Would you review my implementation once?

I am not in a rush :)