Query storage in separate file

[rogerbinns]

Consider a module that stores queries in a separate file. It would be APSW and SQLite specific and be similar to this but with differences:

• Multiple statements per name, so eg it can be schema upgrades like in user_version example
• Loading via paths but also importlib.resources so easily found from a module
• Introspection via apsw.ext.query_info although that requires the statements to be valid at query time (eg all tables and columns must exist)
• Only named bindings allowed
• executemany vs execute
• default values for bindings, so only one copy of fractal but could supply large iteration value
• automagically work in both sync and async
• t/f-string style templating available that quotes names like in Feature request: allow using template strings as query statements #598
• Section at the top providing default flags, default template values etc
• Can the fractal take width and height params which requires arithmetic inside the expression done at python formating
• Make fts5 triggers be done via that templating, plus investigate the other SQL composed in fts5
• Sample queries like the fractal and Sudoku for testing and consuming cpu
• Other places in apsw where queries are done like shell (dump) and ext.analyze_pages
• auto generate type stubs so auto sphinx do able and ide integration. Needs to know data classes somehow.. Per query rtype option?
• gather sqlite statistics (per query, per file, authorizer to show tables/columns accessed)

Also have a look at mayim. The general gist is you can define a dataclass and reference a query and a method automagically combines them to produce the dataclass as a result of the query with provided parameters. The method type signature can be inspected to figure out the return, and we have a way of referencing the query above.

Look at this because it should be possible to replace all those hard coded SQL and be way more ergonomic

Some code thoughts:

# assuming example.sql contains queries in the following

# an import hook?
from . import example
example.query1.....

# usage of queries - auto async if db is async?
example.query1.execute(db, bindings)
example.query1.executemany(db, iterable)
# ?db as contextvar so
example.query1.execute(bindings)

# derive from str so
db.execute(example.query1, bindings)