speeding up SQLite classes - notes on future work

[ctb]

1808 adds support for SQLite, and is well enough tested (IMO :) that we could start fearlessly refactoring.

see blog post, http://ivory.idyll.org/blog/2022-storing-ulong-in-sqlite-sourmash.html, for some more background.

ideas for future optimization -

• provide different classes for scaled >= 2 and scaled = 1. The current code handles both cases, but there's no reason to do all the conversion stuff when scaled >= 2. (You can see an initial attempt at this in the code base below if max_hash <= MAX_SQLITE_INT: blocks, but lots more could be done.)
• the PRAGMA configuration stuff could potentially be optimized.
• we need to benchmark multiprocess read etc.
• should we restructure the way we allow for duplicate hashvals in sourmash_hashes? we could support one table for unique hashvals for querying etc., and then have another table that links hashvals to sketch_id.

[ctb]

as a side note for @luizirber, for scaled = 2, you actually have precisely one hash that is larger than MAX_SQLITE_INT, which is why I didn't push more on this in #1808!

>>> import sourmash
>>> mh = sourmash.MinHash(0, 31, scaled=2)
>>> mh._max_hash
9223372036854775808
>>> 2**63 -1
9223372036854775807
>>> mh.add_hash(9223372036854775808)
>>> len(mh)
1
>>> mh.add_hash(9223372036854775807)
>>> len(mh)
2