Find way for accessing SQlite DB programmatically

[jotaen4tinypilot]

In https://github.com/tiny-pilot/tinypilot/issues/1524, we discussed that we should make it easier to use the SQlite / tinypilot.db file in programmatic contexts, e.g. in bash scripts.

Currently, you have to add a CLI endpoint to the Flask app (see, this example, where we read the streaming mode). This has some downsides:

• Unless we come up with a more generic endpoint, we’d have to provide a CLI blueprint for each and every data point that we want to obtain from the SQlite.
• Invoking Flask via the CLI is relatively slow: on device, it takes ~1.5 seconds for each invocation, where the majority of that time is spent initializing Flask.
• Currently, Flask emits unrelated app-level logging to stdout, which makes the results hard to parse. This can probably be configured / disabled somehow, though.

Note that we can’t “naively” read from the tinypilot.db file as is, since the DB file might not be in the latest migration state. We probably don’t want to port our migration logic to a different language.

So we should explore options to access the tinypilot.db file in programmatic contexts. Requirements for the solution:

• Easy to use, with minimal boilerplate
• Safe – e.g., don’t bypass the migration mechanism
• Performant – like, well below 1 second
  • Just for context, as example: the call to db.settings.Settings().get_streaming_mode().value is ~5ms on device (which includes reading the DB file from disk).

Potential solutions / approaches:

(Non-exhaustive, just as starting points)

• We could isolate the app.db module in a way that we can write our own, Flask-less CLI entry point.
• We investigate why Flask is so slow, and see how we can make it faster for this purpose.

[jotaen4tinypilot]

Re the idea of isolating the app.db module and providing our own, Flask-less CLI entry point:

Out of curiosity, I ran a quick-and-dirty POC on this branch. With this, you could do the following on the command line:

$ ./cli settings streaming-mode
MJPEG
$ ./cli settings requires-https
True
$ ./cli users names
['John Doe']

For that, we’d have to refactor the db_connection handling, and effectively make the app.db module to have no upwards dependencies, otherwise we’d unintendedly loop in Flask.

Execution time on device (including activating venv) is ~120ms, which seems reasonable (well, at least more reasonable than 1500ms).