get_entries() query too complicated

[lemon24]

reader/src/reader/core/storage.py

Line 511 in be336e0

# TODO: This needs some sort of query builder so badly.

[lemon24]

Prototype in this gist (including using another sort key for #122).

The end result looks nice, but using it would add ~150 lines of code (that need to be tested), and it's less useful for simpler queries.

Also, it looks nice now, when I just wrote it; 6 months from now it may be hard to understand.

[lemon24]

Alternative:

Don't "build" the query; always shove everything into it and disable various bits through boolean parameters, and let the optimizer sort it out.

Cons:

• The optimizer may not be able to optimize everything away, especially if the query is optimized before the parameters are passed in (so it doesn't get a change to remove stuff).
• Some things we may not be able to turn off at all.
• Do we still need to provide values for parameters used in the disabled bits? Probably yes.
• Passing more parameters may be more expensive. Would it be more expensive than the string building, though?

[lemon24]

Summarizing the possibilities a bit:

• do nothing
• disable bits of the query with booleans and hope the optimizer sorts it out (above)
  • lots of cons, not doing it
• JinjaSQL
  • seems like overkill
  • I'm not convinced it improves readability
• sqlbuilder.mini
  • seems very extensible while preserving some of the readability
• home rolled query builder (above)
  • ~50 lines base, ~150 with some extensions
  • preserves some readability, but it's likely less extensible than sqlbuilder.mini
  • could be made more extensible, with more code
  • update: v2 here
• SQLAlchemy query builder
  • may be overkill
  • if we are to use an existing query builder, this is likely it (maintained, stable, tested, used, extensible, portable)
  • sqlakeyset for scrolling window
• June 2020 update: https://github.com/9fin/sqlpy looks like a novel way of building queries
• June 2021 update: https://github.com/kayak/pypika looks like it follows the same philosophy as my prototype, but it's way more comprehensive; and it did exist when I first worked on this
• June 2021 update: http://docs.peewee-orm.com/en/latest/peewee/query_builder.html exists as well (IIRC I skipped it because it comes with the whole ORM)

Whatever solution we use, it needs to support:

• the existing query (as of 0.18; uses joins, scrolling window)
• the more index-friendly version of the query (Use indexes #134, needs CTEs)
• the full text search query (Cannot search through entries #122, needs CTEs)
• whatever random sort query we come up with (Allow sorting entries randomly #105)

For now, we'll do nothing; we can re-assess after we implement full text search, use indexes, and maybe implement random sort.

[lemon24]

Version 2 of the query builder, with support for WITH, JOIN, and UNION, and enforcing the correct keyword order.

~110 lines base, ~140 lines with extensions (no type annotations).

There are full examples of the get_entries() and search_entries() queries.

[lemon24]

Counts for _search.py + _storage.py and _sql_utils.py (the query builder, for after):

• statements: 437 before, 409 + 75 = 484 after
• lines: 1411 before, 1304 + 136 = 1440 after (and an additional 182 for _sql_utils.py tests)

It's not much worse. There's less duplication between get_entries() and search_entries(), and it should now be easier to paginate most of the get_...() queries.

---

Also, it should now be much easier to tweak the queries. fb38d91 is a good example of this (the search_entries_read is ~5x faster because 1/5 of the entries are read).

Before:

$ python -u bench.py time 'search_entries_*' -n1 -r100 \
> | grep -e stat -e min -e p50 | grep -v -e ' 32' -e ' 64'
stat number repeat num_entries search_entries_all search_entries_read
 min      1    100         128              0.008               0.003
 p50      1    100         128              0.008               0.003
 min      1    100         256              0.014               0.005
 p50      1    100         256              0.015               0.005
 min      1    100         512              0.030               0.011
 p50      1    100         512              0.030               0.011
 min      1    100        1024              0.062               0.024
 p50      1    100        1024              0.062               0.024
 min      1    100        2048              0.168               0.046
 p50      1    100        2048              0.168               0.046

After:

$ python -u bench.py time 'search_entries_*' -n1 -r100 \
> | grep -e stat -e min -e p50 | grep -v -e ' 32' -e ' 64'
stat number repeat num_entries search_entries_all search_entries_read
 min      1    100         128              0.009               0.001
 p50      1    100         128              0.009               0.001
 min      1    100         256              0.016               0.001
 p50      1    100         256              0.016               0.001
 min      1    100         512              0.031               0.002
 p50      1    100         512              0.032               0.002
 min      1    100        1024              0.064               0.004
 p50      1    100        1024              0.064               0.004
 min      1    100        2048              0.171               0.007
 p50      1    100        2048              0.172               0.007