How to Extension SQLite to support Stored Procedure？

(1) By miechal on 2020-06-16 08:18:06 [link] [source]

(2) By Stephan Beal (stephan) on 2020-06-16 08:23:38 in reply to 1 [link] [source]

How to Extension SQLite to support Stored Procedure？

The short version is "you can't because sqlite extensions cannot extend the syntax of SQL," but you probably want to read this recent thread on the topic to get a better idea of how to approximate stored procedures in sqlite (as well as reasons for why you might really not want to).

(3) By Gunter Hick (gunter_hick) on 2020-06-16 10:07:26 in reply to 1 [link] [source]

I recall some guy on the mailing list (IIRC Prakash Premkumar <prakash.prax@gmail.com>) who was hell bent on saving SQL Bytecode programs (the internal info generated by PREPAREing a statement) and runnning them again later .

That was in 2014, and I have not heard if he has succeeded or not.

(4) By Keith Medcalf (kmedcalf) on 2020-06-16 15:21:50 in reply to 1 [link] [source]

That depends on your definition of Stored Procedure.

(5) By Alek Paunov (decalek) on 2020-06-18 21:24:28 in reply to 1 [source]

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