3 ms·

Interesting! We tunnel SQLite over JSON in the Notion iOS/Android/Mac/Windows apps, and the interface presented here looks pretty similar to our second iteratio

by jitl 5y ago

Interesting! We tunnel SQLite over JSON in the Notion iOS/Android/Mac/Windows apps, and the interface presented here looks pretty similar to our second iteration. Here’s the things I think are missing: - Precondition. One or more statements to run to check if the transaction body can proceed. We use this to check pragma user_version which we use to mark schema version level. Prevents old processes from writing to the DB once another process has started a forward migration. You can also use it to assert about transaction state or disk space remaining. - Userspace transaction batching. We found some use-cases (like as part of migrations) where we want to control transactions explicitly. Forcing each batch into a transaction is a good default, but ideally the JSON api doesn’t restrict any DB semantics. - Websockets! I thought “ws” could mean websocket but web server is fine too. Websockets work better for our low latency local-only connection use case, and have a more traditional socket-pool vibe. They also avoid retransmitting headers etc, and can allow the server to better understand client “sessions”. - Extensions. I guess portability is a fine goal, but there’s so much great stuff in the extensions. Full text search, JSON support (although the latest version mainlined it), bulk data load. The main one for us is JSON.

mano78 5y ago
Thanks for the comment. Some great points here. - Precondition: this is a goal, once I find a good balance between allowing to do things and not to reinvent a scripting language. But yes, it's on the roadmap. - Userspace transaction batching: interesting. I don't feel comfortable about letting a transaction "escape" the request, I don't want to introduce timeouts, sessions or such. Could you state an use case? This could be a goal. - Uh, websockets are promising. Thanks! - Extension: basically, the point here is that Go doesn't allow for a "clean" cross-distribution compilation if I enable them, and I cannot chase all the possible glibc configurations. But it's an open point for sure. Isn't JSON included in the "main trunk" now?
- jitl 5y ago
  Here's our precondition, it expects a single row with precondition_result to be 0/1. You could add an error reporting string column to the return type, but that's about all we needed. Very basic: SELECT CASE user_version WHEN ${schema.pragmas.user_version} THEN 1 ELSE 0 END AS precondition_result FROM pragma_user_version() LIMIT 1 On extensions: Yes, JSON is included in trunk. Is the latest trunk included in your build? Or are you linking against the platform's sqlite3? At least for the built-in extensions, you can pre-build an amalgamation .c file that contains all the first-party extensions with `make sqlite3.c` and a few env vars. But maybe you're using a pre-built dependency ¯\_(ツ)_/¯. One extension we like is LIMIT for update/delete. I believe (at least in the version of SQLite we build with), that we need to enable that one when producing the amalgamation. On userspace transaction batching: our usecase is internal inside an app. When we built the second version of our SQLite bridge, we were replacing a safe but very rigid API, so we decided to build something with as few limitations as possible. The main use-case today is batching multiple transactions into a single IPC message/JSON request object. But, for our platforms, there's exactly 1 client of the JSON bridge at a time, and we know out of band if that client dies or resets -- so we can terminate hanging transactions appropriately. You could also add an assertion that a batch never leaves a transaction open. For an HTTP use-case, perhaps not a great idea because of multiple clients, sessions, etc. Maybe something to consider for the web socket API, which would give you a protocol session concept to reason with.
  - mano78 5y ago
    thanks for your comments, really interesting. I'll come back to this post for ideas ;-). I like the precondition, maybe I'd like to structure it so that it passes when 1+ rows are returned and fails if 0 rows... should be equivalent and doesn't make any assumption on rows _and_ columns being there... but I'll think about it. Thank you again!
  - mano78 5y ago
    Sorry, forgot to comment: SQLite is currently 3.38.0, see https://github.com/mattn/go-sqlite3/blob/master/sqlite3-binding.c?raw=true https://github.com/mattn/go-sqlite3/blob/master/sqlite3-bind... I don't package it directly, but via the go bindings by mattn (go-sqlite3).

by jitl 5y ago

mano78 5y ago

Thanks for the comment. Some great points here. - Precondition: this is a goal, once I find a good balance between allowing to do things and not to reinvent a scripting language. But yes, it's on the roadmap. - Userspace transaction batching: interesting. I don't feel comfortable about letting a transaction "escape" the request, I don't want to introduce timeouts, sessions or such. Could you state an use case? This could be a goal. - Uh, websockets are promising. Thanks! - Extension: basically, the point here is that Go doesn't allow for a "clean" cross-distribution compilation if I enable them, and I cannot chase all the possible glibc configurations. But it's an open point for sure. Isn't JSON included in the "main trunk" now?

jitl 5y ago
Here's our precondition, it expects a single row with precondition_result to be 0/1. You could add an error reporting string column to the return type, but that's about all we needed. Very basic: SELECT CASE user_version WHEN ${schema.pragmas.user_version} THEN 1 ELSE 0 END AS precondition_result FROM pragma_user_version() LIMIT 1 On extensions: Yes, JSON is included in trunk. Is the latest trunk included in your build? Or are you linking against the platform's sqlite3? At least for the built-in extensions, you can pre-build an amalgamation .c file that contains all the first-party extensions with `make sqlite3.c` and a few env vars. But maybe you're using a pre-built dependency ¯\_(ツ)_/¯. One extension we like is LIMIT for update/delete. I believe (at least in the version of SQLite we build with), that we need to enable that one when producing the amalgamation. On userspace transaction batching: our usecase is internal inside an app. When we built the second version of our SQLite bridge, we were replacing a safe but very rigid API, so we decided to build something with as few limitations as possible. The main use-case today is batching multiple transactions into a single IPC message/JSON request object. But, for our platforms, there's exactly 1 client of the JSON bridge at a time, and we know out of band if that client dies or resets -- so we can terminate hanging transactions appropriately. You could also add an assertion that a batch never leaves a transaction open. For an HTTP use-case, perhaps not a great idea because of multiple clients, sessions, etc. Maybe something to consider for the web socket API, which would give you a protocol session concept to reason with.
- mano78 5y ago
  thanks for your comments, really interesting. I'll come back to this post for ideas ;-). I like the precondition, maybe I'd like to structure it so that it passes when 1+ rows are returned and fails if 0 rows... should be equivalent and doesn't make any assumption on rows _and_ columns being there... but I'll think about it. Thank you again!
- mano78 5y ago
  Sorry, forgot to comment: SQLite is currently 3.38.0, see https://github.com/mattn/go-sqlite3/blob/master/sqlite3-binding.c?raw=true https://github.com/mattn/go-sqlite3/blob/master/sqlite3-bind... I don't package it directly, but via the go bindings by mattn (go-sqlite3).