[HN Gopher] Build your own SQLite, Part 1: Listing tables
___________________________________________________________________
Build your own SQLite, Part 1: Listing tables
Author : upmind
Score : 154 points
Date : 2024-08-17 23:13 UTC (23 hours ago)
(HTM) web link (blog.sylver.dev)
(TXT) w3m dump (blog.sylver.dev)
| bluejekyll wrote:
| An interesting idea just struck me. If this is all in native Rust
| then you could do something interesting with macro defined SQL
| queries, where at compile time, you could output direct bindings
| from the SQL to the internal DB api. This would skip parsing and
| building query plans at compile time (for static queries).
|
| Anyway, cool project.
| maxbond wrote:
| That's an interesting idea. One problem I see is that
| conventional query planners use statistics to choose the
| optimal plan, however the data presumably wouldn't be available
| at compile time. But if you built the database with this
| approach in mind you might find a different approach to
| planning.
| runevault wrote:
| Pretty sure this is why sql engines normally jit compile to
| either bytecode or straight to machine code, so that they can
| easily recompile and replace the query if the statistics or
| other factors change. So long as you don't regularly
| recompile the query you pay the cost once then reuse the
| results possibly thousands or even millions of times, but
| without being forced to keep using the exact same plan until
| you rebuild the application.
| anacrolix wrote:
| Pretty sure Sqlite's bytecode is generated after choosing
| the optimal plan. So you can't change it after that point.
| I had never considered this but you probably do want to
| prepare or expire statements after a while if your DB
| changes significantly
| runevault wrote:
| It never reconsiders? The one I have the most experience
| with (SQL server) certainly will, sometimes at weird
| times in ways that are non-obvious leading to performance
| degradation heh.
| maxbond wrote:
| Poking at the documentation, they're only recompiled when
| the schema changes or ANALYZE has been run[1]. So, if you
| want them recompiled, you can use the optimize PRAGMA[2].
|
| They raise a good point in the docs that in the contexts
| SQLite works in, if there's a regression caused by
| changing the plan, there won't be a DBA around to fix it.
|
| [1] https://sqlite.org/queryplanner-ng.html
|
| [2] https://sqlite.org/lang_analyze.html#req
| khimaros wrote:
| SQLx seems to do some form of this. though what you're
| suggesting may remove the build time dependency on "connecting"
| to a SQLite database.
|
| "SQLx supports compile-time checked queries. It does not,
| however, do this by providing a Rust API or DSL (domain-
| specific language) for building queries. Instead, it provides
| macros that take regular SQL as input and ensure that it is
| valid for your database. The way this works is that SQLx
| connects to your development DB at compile time to have the
| database itself verify (and return some info on) your SQL
| queries."
|
| https://github.com/launchbadge/sqlx
| dymk wrote:
| Query plans may depend on runtime information about a table's
| statistics, though
| chipdart wrote:
| > If this is all in native Rust then you could do something
| interesting with macro defined SQL queries, where at compile
| time, you could output direct bindings from the SQL to the
| internal DB api.
|
| Aren't table references resolved at runtime?
| mo_42 wrote:
| I came across this blog as well because I got interested in
| writing a compiler that converts SQL into source code of a type-
| checked language.
|
| Are there any blog series about the details of query planning,
| optimization etc?
| jessekv wrote:
| Not sure I understand, but SQLite itself sort of does that. You
| can take a look at the architecture here:
| https://www.sqlite.org/arch.html
___________________________________________________________________
(page generated 2024-08-18 23:02 UTC)