[feat] migration should support partial index

[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]

Hey @onichandame, thanks for the proposal!

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.

Thoughts? @tyt2y3 @ikrivosheev

[tyt2y3]

Agreed

[billy1624]

Alright, I take that as a go! I open a tracking issue on SeaQL/sea-query#396

[tyt2y3]

Reason to close: this issue has been moved to SeaQuery