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,
Alek
(*) 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
