Support for index hints

[zebrapurring]

Motivation

Some queries require manually disabling or specifying indexes, which is currently supported with index hints after the table name in the FROM clause.

See documentation of the support for index hints:

• SQLite with INDEXED BY <index-name> and NOT INDEXED: https://www.sqlite.org/syntax/table-or-subquery.html
• MySQL: https://dev.mysql.com/doc/refman/9.2/en/index-hints.html

For PostgreSQL I haven't found an equivalent alternative.

Proposed Solutions

I would like to either:

1. Implement support for SQLite and MySQL hints (platform specific)
2. Implement a generic way to pass table hints to the tables used in the FROM clause

[Expurple]

Generic support for custom FROM clauses would be useful and consistent with other escape hatches like Expr::cust.

It's a bit weird that methods like SelectStatement::from accept an IntoTableRef parameter, where TableRef is a rigid exhaustive enum that doesn't provide any escape hatches. Because of this rigidity, it looks like we'll need a major version bump to introduce an escape hatch.

If a maintainer reads this thread, please create a milestone for sea_query 0.33 and add this issue to that milestone.

[Expurple]

Hmm, I've just noticed that MySQL index hints are already implemented. It's right there in the search

[zebrapurring]

Right, I'm working with SQLite so I didn't notice the MySQL hints are already supported. Then maybe would make sense to implement the specialized hints for SQLite as well?