Consideration for converting IndexMethod.hash in sqlite transactions

[Skelmis]

Currently I run various text columns with indexes on them, ideally IndexMethod.hash as this provides good equality matching lookups. I run into an issue with this however during tests which run on sqlite, as Piccolo will exit with an error if this index method is used. This is really not ideal and basically means I cannot use this index method at all as it is not feasible to run postgres just for test cases.

Possible I'm thinking of maybe adding another way to say to Piccolo, hey please map this to a usable sqlite index instead of erroring, either by app level variable of a column argument like sqlite_index_method. I.e. In my use case it is fine for IndexMethod.hash to be mapped to IndexMethod.btree if the DB is sqlite, as long

I also may be overlooking something, perhaps there is a way to do this already?

Thoughts?

[sinisaos]

@Skelmis Yes, Piccolo will raise a ValueError because SQLite does not supports hash indexes. That came from this section

piccolo/piccolo/query/methods/create_index.py

Lines 66 to 68 in 11ada0b

	method_name = self.method.value
	if method_name != "btree":
	raise ValueError("SQLite only support btree indexes.")

If we replace ValueError by assigning a method name to the given index method (in SQLite it is btree) like this

method_name = self.method.value
if method_name != "btree":
    method_name = self.method.value
    colored_warning(
        message=(
            "=> A btree index was not used when creating the table. "
            "Piccolo converts the index method to a btree because "
            "it is only method supported by SQLite."
        ),
        level=Level.high,
    )

the engine will replace the index name of the created table with the default one. Should we do this or not?

[Skelmis]

I would quite like it, but it should probably raise a warning so that people do actually know that it's happening as well as being documented behaviour

[sinisaos]

@dantownsend what do you think about this change?