On May 14, 2018, at 9:44 PM, Alek Paunov <a...@declera.com> wrote: > > Practical example of the benefit is that e.g. same complex turnover report > which implementation includes bunch of intermediate calculations, could be > used with same SQL call from Python desktop application, directly as Excel > Data Source or on partial replica through SQL.js [1] in the browser, without > coding the same thing 3 times: on Python, VBA and JS.
How common is that for a DBMS that should never be used over a network? That is, all three applications would have to be running on the same machine for proper data safety. I get why a client-server DBMS would have a mix of strange applications in many languages accessing it, but SQLite? Don’t you tend to just pick one implementation language appropriate to the delivery platform? > How many list readers really missed the Stored procedures feature? I’m with drh: stored procedures are a nasty hack needed only to minimize RTT in the high-latency LAN (or WAN!) environment. The cost of this hack is proprietary lock-in, since AFAIK no DBMS supports another’s stored procedure dialect. I’m basing that on a skim of https://en.wikipedia.org/wiki/Stored_procedure#Implementation Apparently SQL/PSM is a “standard,” but poorly supported in the market, according to that article, with all other DBMSes going off in their own directions. If you have multiple applications accessing a given SQLite DB on a single computer, chances are that they’re all coded in the same programming language, so you can just write your shared procedures in a library for that language. If it happens that you really are using multiple languages on a single computer or the same DB has to be cross-compatible among disparate machines with different programs (e.g. desktop and mobile) then you can probably write your shared procedure library in a statically-compiled language that can export a C-compatible ABI, then link to it from any language you like, since most practical non-C family languages have C-compatible FFI mechanisms. You need a way to link to SQLite in the first place, right? In fact, why not just use C to extend SQLite, adding the functions you need? > My humble opinion is that for us, the minority, the best move is to look for > approaches and collaboration on "incubating" valuable advanced features > initially outside of the SQLite core, then eventually apply result solutions > in our own projects and finally propose for inclusion in the core library > only small key components (which lead to inefficiencies or maintenance burden > when living outside). That path leads to a non-“Lite” implementation. I think you can add some flavor of stored procedures as an extension, allowing it to be compiled out of binaries that don’t require it, but it still feels like mission creep to me. The only option I can think of that fits the philosophy of SQLite would be to allow writing stored procedures in Jim Tcl or TH1. The code for both is already in the SQLite tree, though both are used for other things, so neither is currently built into a SQLite binary. This document maps a pre-trodden path: https://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html Scroll down to "SQLite Extensions Written In Tcl.” All I’m proposing is that that be made more of a first-class citizen, so that you can choose to enable it from the SQLite source tree, not by starting first with Tcl and extending it with SQLite. > That way, at some stage, SQLite could even have several procedural dialect > implementations - TSQL, MySQL, etc. :-) Are any of those actually good languages? Every time I’ve seen stored procedures, the syntax is horrid, but I lack the experience to discern among these alternatives. All I know is that I’ve managed to avoid every one of these languages for my entire career, and I’d like to get through the rest of it while still avoiding stored procedures, if possible. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
