On 2018-05-09 03:56, Richard Hipp wrote:


The other benefit of stored procedures is that it provides a way to
code up a common operation once (correctly!) and store it in the
database, rather than having multiple clients all have to work out the
operating themselves (some of them perhaps incorrectly).  The usual
way of handling that in SQLite is to store a script in a text column
someplace, then execute them as needed.  SQLite began life as a TCL
extension, and so naturally TCL scripts work very well for this kind
of thing.

To amplify the remark above, for me, the Stored procedures are mostly method to add methods :-) to the "cold" data objects encoded in tables, just like the OO style SQLite C API defines interfaces and behavior on top of "cold" C structures.

That way, schemes and non-trivial calculations on top of them can be encapsulated and reused as modules (or packages as named in some design tools).

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.

Having said that, Let's take the survey: How many list readers really missed the Stored procedures feature?

If there are enough interest, I would be happy to discuss an (possibly naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level procedures) mostly in pure SQL with just tiny C runner as UDF TValued Function [2].

Kind Regards,

(*) On the sqlite mailing list, we see permanent conflict of interests: On the one side are 99% of the users, for which sqlite is just better (than e.g. compressed json) storage option (usually for small as size or simple as structure data).

On the other, are the advanced users - these with complex applications who [especially after the great advancements from the last years (CTEs, JSON and so on)] try to encapsulate as much as possible business logic into the DB.

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).

For the Stored procedures, borrowing from the SQLite design, I think that it is possible the non SQL, procedural part of the code to be translated to very high level VM with just few instructions, which uses attached :memory: DB for stack/registers/variables and (tree) table for the code. [This can be seen as step further above the approach cited by Dr. Hipp SQLs kept in table and used by executed by TCL script.]

That way, at some stage, SQLite could even have several procedural dialect implementations - TSQL, MySQL, etc. :-)

[1] https://github.com/kripken/sql.js/
[2] https://www.sqlite.org/vtab.html#tabfunc2
sqlite-users mailing list

Reply via email to