3 ms·
That assumes your database cells aren't themselves revision-controlled (e.g. Dolt: https://www.dolthub.com/ https://www.dolthub.com/) But also, this is the sam
by derefr 3y ago
That assumes your database cells aren't themselves revision-controlled (e.g. Dolt: https://www.dolthub.com/ https://www.dolthub.com/)
But also, this is the same argument people generally give against using DB stored procedures. And, despite using sprocs often myself, I generally agree with them — the tooling around sprocs really sucks. Sprocs are "DB objects as state" (like rows in a table are state); but they could be a lot more than that.
My secret dream, as an infrastructure engineer and DBA, is that, on boot, each version of each application that uses an RDBMS could "register" or "zero-install" a client schema with said RDBMS — i.e. a namespace of persistent virtual DB objects, unique to that build of that application — but where the underlying physical DB objects are not necessarily unique, instead being shared as needed between builds and even between applications. (Think: what would happen if you put the Plan9 designers in charge of the evolution of the SQL standard.)
Such a "client schema" would specify, among other things, the full source code of any sprocs the application expects to be able to call. These would be able to be internally deduplicated — e.g. registered by content-hash into an sproc store on the DB (compare/contrast: Redis EVALSHA), and then "symbolically linked" to a particular name in the client schema.
Ideally, in applications that use client schemas, every SQL query that's a static literal at app build time, would be compiled into a boot-time registration of, and runtime call to, such a registered virtual sproc definition. Think SQL "prepared statements" — but "prepared" at compile time on the client and ensure-bound to RDBMS-side equivalents at app boot time.
(Why? Well, think about what RDBMSes could do to dynamically compile / optimize / JIT queries — and dynamically create/optimize indices to serve queries — if they could know the bounded set of in-use queries at any given time, and do long-term statistics collection on the behavior of those queries. No existing RDBMS is designed to do this, despite sprocs existing, only because they're so little used. Instead, almost all "app queries" to RDBMSes are just regular DML statements — which read to an RDBMS as one-offs needing to be freshly compiled and planned. RDBMSes could do so much better — if apps simply had the language to communicate their needs clearly!)