SQLite Forum

How to Extension SQLite to support Stored Procedure´╝č
Login
Wow, Second post about Stored Procedures in the same year - Amazing!
:-).

Generally, the SQL scripting is considered excessive and redundant in
the sqlite (i.e. embedding) context by the core team leader and most of
the distinguished members of the forum (and preceding sqlite-users
mailing list). Furthermore, very few of the list participants have some
accountable (like L Carl practice expressed in [1]) experience with SP
codebases in past projects.

What kind is your interest in the SQL scripting - You need to use it
incidentally in your current work; Or you have principal confidence that
sufficient number of sqlite based projects needs such feature for logic
encapsulation?

If you need something this year, perhaps it will be more reasonable to
check some sqlite alternative, e.g. Tarantool, SQL part of which could
be considered as sqlite fork [2]. Tarantool supports engine-side Lua [3]
(and C) which feels more like .Net in MSSQL than Transact SQL (i.e.
project do not have data oriented DSL). Tarantool is well-established
project which powers big cloud services provider (mail.ru).

Another (much lighter and easy embeddable) project in the same vein
(modulo our topic) is jsish [4] which develops own JS engine for SQL/WEB
services scripting [5].

If you have principal interest on the topic, for your convenience I
extracted the list posts from the past 10-ish years, containing the
string "stored procedure" [6], so you can enumerate the argumentation of
the view points from previous threads. In addition to my own humble
opinions in the past (few of which you could find in the archive), now I
think that for future SQL (and XQuery too) developments it would be a
good idea some well thought macro/tree rewriting system to be promoted,
and the CSTs/ASTs of the codebase to be (structurally) persisted in both
unexpanded and expanded form (think app_code_node tree table(s) which
are enough for easy code base mangling implementations (i.e. IDEs
support) and also for unparsing the DDLs stored in sqlite_master)

Kind Regards,
Alek

[1] https://sqlite.org/forum/forumpost/538fbde154
[2] https://sourcegraph.com/github.com/tarantool/tarantool@2.5.0/-/blob/src/box/sql/vdbe.c
[3] https://www.tarantool.io/en/doc/2.4/tutorials/sql_tutorial/#create-a-million-row-table
[4] https://jsish.org/fossil/jsi3/doc/tip/md/DBQuery.md#Javascript
[5] https://jsish.org/fossil/jsi3/doc/ckout/README.md
[6] http://source.declera.com/sqlite-stored-procedure.zip
    Password: i-will-try-fossil