SQLite Forum

Feature request: Stored Procedures
Login

Feature request: Stored Procedures

(1.1) By MBL (RoboManni) on 2021-06-06 11:22:38 edited from 1.0 [link] [source]

Until now SQLite3 does not support Stored Procedures (at least as far as I am aware)

Are there any plans to introduce also Stored Procedures into SQLite3 ?

Since many more than 10 years I am using therefore my own extension functions to mimic such a feature. I named my function "call" and its first parameter is the name of the procedure and the other parameters are the inputs to that function.

The call function inserts the parameters into a table with name "stack", which fires a trigger which is watching the name, the first parameter of my call.

The trigger itself can work with all the parameters from the stack record, a helper extension function supports this task.

The trigger itself calls a function return, which can be filled into the stack record but more important becomes the return value of the "call" extended function call and therefore is then the reply into the original sql statement, which caused the trigger to fire.

Because all this happens in scope of the same db context the stack leaves only a trace if there was no delete all from stack happening. I am controlling this for debug purposes, too.

Here is an example for the recursive form of the faculty for number 6 ( = 1 * 2 * 3 * 4 * 5 * 6 = 720 ) , 7! = 5040 , 8! = 40320

D:\SQLite3x\sandbox>sqlite3 LoadExTest.SQB
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .load sqlite3.dll
sqlite> .mode box
sqlite> select call('Fakt',6),call('Fakt',7),call('Fakt',8);
┌────────────────┬────────────────┬────────────────┐
│ call('Fakt',6) │ call('Fakt',7) │ call('Fakt',8) │
├────────────────┼────────────────┼────────────────┤
│ 720            │ 5040           │ 40320          │
└────────────────┴────────────────┴────────────────┘
sqlite>

The stored function itself and the stack table looks like the following:

  • Debug() sends output to Windows function OutputDebugString
  • argc() returns the count if non-NULL parameters
  • argv() returns one parameter from the recent stack table record
  • JobLevel() return the recursive depth of calls from the level column
  • result() fills the column result and will be the return value for call
CREATE TABLE stack( job text, -- the function name which will fire the trigger
   level integer, inserted float, returned float,        -- some debug support
   result blob,
   par0 blob, par1 blob, par2 blob, par3 blob, par4 blob,
   par5 blob, par6 blob, par7 blob, par8 blob, par9 blob );

CREATE TRIGGER Fakt after UPDATE OF job ON stack
FOR EACH ROW WHEN new.job=='Fakt' and argc()>=1
BEGIN
  select Debug(concat('JobLevel()=',JobLevel(),': Fakt(',argv(0),')=',
    result( argv(0) * case
      when argv(0)<2 then 1
      else call(new.job,argv(0)-1)
    end   )   )      );
END;

(2) By Decker (d3x0r) on 2021-06-06 14:24:25 in reply to 1.1 [link] [source]

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

But beyond that you can certainly implement user functions which provide stored procedure results; but that's not a thing other databases can do.

Not only that, but Stored procedures would require an additional language implementation to handle variable declaration, assignment and expressions... there's no standard when it comes to writing scripts to be executed by a SQL engine; other than the SQL commends themselves.

(3) By anonymous on 2021-06-07 17:04:54 in reply to 2 [link] [source]

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

This argument applies equally well to all database engines.

There are several advantages to using stored procedures. (I won't list them, Google them). From those, only the client-server over network speedup is irrelevant to SQLite3.

I believe the number of people (including programmers) who use SQLite3 as a stand-alone database tool is vastly underestimated.

SQLite3 has become very powerful over the years due to its relative simplicity, single-file database, and immediacy. Many who would otherwise use a client/server database now find it more than enough to use SQLite3 as it does almost everything the bigger ones do with far less overhead and practically zero setup effort.

Stored procedures would make their life quite a bit easier in many circumstances. A minimal implementation of stored procedures would be a welcome addition to what is already almost a full-fledged database engine.

By 'minimal' I mean at least:

  1. Be able to group several statements together
  2. Allow parameters when calling the proc.
  3. A simple method to rollback and exit after each statement if there are errors or (by default) continue to the next statement.

That's all. No variables, or other perhaps complicated programming structures found in larger database engines.

[1] is already implemented in triggers.

[2] is already setup in the CLI with the .parameter command. It needs to become part of the core using the PROC(PARM1,PARM2,...) syntax.

(4) By Cecil (CecilWesterhof) on 2021-06-08 11:32:53 in reply to 3 [link] [source]

It is also very handy for having to maintain it at one place. When several programs do at some part the same it would be better to maintain stored procedures as to have to maintain al the programs.

(5) By ddevienne on 2021-06-08 11:53:42 in reply to 4 [link] [source]

Exactly. Also agree SQLite is often used stand-alone.

Richard already has a mini-scripting language around, TH1, which is not my cup of tea, being TCL based (and thus entirely string-based), but simplicity matters in this context, and the fact it's already Richard's code helps a lot too. With a few extended commands to gain access to the SQL engine, for dynamic SQL, different from those used by Fossil, and you have most of what's needed for Stored Procedures, which Richard can decide to put in a reserved-name table sqlite_proc for example. Put that in the amalgamation, with an opt-in flag, and you have all the ingredients necessary. Sure a DB with these features would not be properly understood by older clients, but the same is true of many new SQLite features, and over times most clients would support them.

Where things fall down is for Richard to see the need for all that, and given the additional maintenance burden (shared with Fossil though, in part), and the fact his main uses of SQLite are embedded, where you have the full C language at your fingertips. Maybe it will come one day, but I doubt it.

(6) By anonymous on 2021-06-08 19:10:10 in reply to 3 [link] [source]

I also like to have stored procedures, but I also think that the existing capabilities of views and triggers are good enough for many purposes, so we don't need an actual stored procedure capability. (And often you can use extensions if the existing capabilities are not good enough, although there are some things that are not currently a capability of SQLite even with extensions; this could perhaps be improved.)

(7) By anonymous on 2021-06-09 08:45:54 in reply to 6 [link] [source]

I'll give you just one scenario to help you see that views and triggers are not always enough. Consider stand-alone use to clear your mind from thoughts like "the application will do that for you".

A very simple example; two tables: people and phones. (A person may have multiple phone numbers, hence the separate table.)

You want to insert a new person.

INSERT INTO people(pid,name,address) values(1,'First Last','Home');
INSERT INTO phones(pid,phone) values(1,'555-1212');

Each time you want to add a person you have to issue these two statements. You could make a script, and edit the data before each run.

Or, play civilized and do something like:

add_person(1,'First Last','Home','555-1212');

(8) By Decker (d3x0r) on 2021-06-09 10:03:22 in reply to 7 [link] [source]

Easy enough to do with a sqlite library..

import {sack} from "sack.vfs"
const db = sack.Sqlite( "database.db" );

db.function( "add_person", addPerson );
const success = db.do( "select add_person(?,?,?,?) f", 1,'first last','home','555-1212' ) ;
if( !success[0].f ) {
   // failed to insert somehow...
}


function addPerson(pid,name,address,phone)=>{
   try {
      db.do( "insert into people(pid,name,address) values(?,?,?)", pid,name,address));
      db.do( "INSERT INTO phones(pid,phone) values(?,?)", pid,phone ) ;
      return 1;
   } catch(err) {
      return 0;
   }
}

though - that just ends up as part of the library of the app...

(10) By anonymous on 2021-06-09 11:06:18 in reply to 8 [link] [source]

"though - that just ends up as part of the library of the app..."

Exactly, if it was, on the other hand, part of the database itself, it makes it a lot easier to use that database to back multiple applications, written potentially in different languages.

In fact, accessing SQLit from multiple, polyglot applications would be much easier if: 1. There was a pseudo language with which we can define: 1.1. Custom SQL functions 1.2. Stored procedures 2. There was a way to (optionally) provide a default connection configuration file, which can be overriden on a per connection basis, but they all pick up the default first if it exists. Maybe even an option to make the defaults non-modifiable, as part of the defense against dark arts when the db can be accessed by potentially rogue processes.

(9) By anonymous on 2021-06-09 10:06:43 in reply to 7 [link] [source]

Any support for stored procedures (as in MS SQL Server) does not stop with the provision of facilities for storing SQL code by name and then executing it by name.

At the very least, you'd need the ability to edit the stored procedures and, ideally, run the procedures according to a chronological schedule and the it would follow that you would want to know the history (timestamp and outcome) of previous invocations.

Not so simple to provide support for stored procedures.

(11) By David Raymond (dvdraymond) on 2021-06-09 12:14:00 in reply to 7 [link] [source]

I'll give you just one scenario to help you see that views and triggers are not always enough.

How are views and triggers not enough for this?

SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table people (pid, name, address);

sqlite> create table phones (pid, phone);

sqlite> create view add_person as select pid, name, address, phone from people inner join phones using (pid);

sqlite> create trigger add_person_trg instead of insert on add_person begin insert into people values (new.pid, new.name, new.address); insert into phones values (new.pid, new.phone); end;

sqlite> insert into add_person values (1, 'First Last', 'Home', '555-1212');

sqlite> select * from people;
pid|name|address
1|First Last|Home

sqlite> select * from phones;
pid|phone
1|555-1212

sqlite>

(13) By anonymous on 2021-06-09 14:07:35 in reply to 11 [link] [source]

Right. It was an unfortunate example.

What if I had a combination of insert, update, and select?

Can you still get by with just views and triggers? (I think even a single select in the trigger would not work.)

(14) By Kees Nuyt (knu) on 2021-06-09 15:06:01 in reply to 13 [link] [source]

You would have separate INSTEAD OF triggers on

  • INSERT
  • UPDATE
  • DELETE

Any trigger can do INSERT, UPDATE or DELETE on any table in the statements between BEGIN and END.

Triggers can do SELECTs where needed to make decisions of what values to use for INSERT, UPDATE or DELETE.

You can even use multiple INSERT/UPDATE/DELETE triggers with different WHEN clauses to discriminate between cases.

I would implement a "SELECT in a trigger" as a view. No need for a trigger.

(16) By anonymous on 2021-06-09 16:02:49 in reply to 14 [link] [source]

With a proc you can do INSERT/DELETE/UPDATE/SELECT in any conceivable order to get the desired action and/or result. You can't do that with triggers. And even if you manage to figure out a way to do so for some specific case, in general it won't be possible or straight forward/readable.

Why pollute your schema with a whole bunch of funny named view-trigger combinations so you can remember which one to use when a simple concise proc can do the same.

Apparently, it's hard to reach a consensus that stored procedures are a good thing.

I guess there will always be two kinds of people; those who like simplicity and beauty, and those who enjoy programming as if in brainf@ck language :)

(25) By anonymous on 2021-09-29 21:31:30 in reply to 11 [link] [source]

I've been gnawing on a similar problem for the past 3 days, and this solves it beautifully.

Great post, thank you.

(26) By anonymous on 2021-09-30 16:39:47 in reply to 2 [link] [source]

Stored procedures aren't really needed for Sqlite, since the program you write around Sqlite can certainly implement any 'stored' procedures itself.

True. However in a multi-language environment (say Python, C, PHP) that would mean implementing all 'stored' procedures multiple times. And of course it will not be a question IF these implementations will be out of sync one day but WHEN - with potentially fatal results for your data.

The same goes for Triggers - easy to implement in application code. And who needs things like Foreign Keys - it is not as if that could not be implemented in an application. (That was sarcasm - sorry).

There is a lot of functionality in SQLite that not everyone needs or uses. A lot of people will never use FTS (I do), others will never need Geopoly (I don't).

(27) By Ryan Smith (cuz) on 2021-09-30 20:15:45 in reply to 26 [source]

The same goes for Triggers - easy to implement in application code. And who needs things like Foreign Keys - it is not as if that could not be implemented in an application. (That was sarcasm - sorry).

Sarcasm? really? we couldn't tell. The problem here is your sarcasm proves the opposite point than which you are trying to make. Triggers in program code is impossible (rephrasing - exquisitely difficult). So is foreign key maintenance. By contrast, scripts inside Triggers and UDF's are both very easy to add and any other processing in your code is as easy as how good a programmer you are.

There is a lot of functionality in SQLite that not everyone needs or uses. A lot of people will never use FTS (I do), others will never need Geopoly (I don't).

The truth is that very many people use FTS and so it is included in the standard builds (but you can exclude it), as opposed to Geopoly that has to be included if you wish to use it, precisely because not many implementations use it. Both of these however are based on the virtual table mechanism (always included) and infinitely less complex than would be a scripting language for stored procedures.

Furthermore, such an inclusion as stored procedures wouldn't be able to be an add-on or loadable library, it would have to form intricate part of the main engine code and cause a lot of bloat and added processing, something punishing all of us for a thing which very few of us have real use for in SQLite.

I'll say this: There are a couple of requests like this almost every year, so you are not alone, but even tens of requests per year likely won't move the needle.

I'm not saying it won't be "nice" to have, just saying the gain-vs-input efficiency ratio is way too low currently. Don't lose hope though, we've seen lots of other claimed-to-be-bloaty additions making it into SQLite over time, such as Backup tooling, CTE's, Window functions, and the very recent RETURNING clause. That said, they all either had been requested much more (possibly monetary requests too), or took much less complexity/effort to add.

(12) By anonymous on 2021-06-09 12:34:13 in reply to 1.1 [link] [source]

Maybe I'm missing something, but for functions like factorial couldn't you just make an extension? It's simple to load it on each connection, or even just compile it in to sqlite if you don't want to load extensions. It would even have the benefit of better performance.

Also, as pointed out by someone else, creativity with views and triggers can yield some good results.

(15) By MBL (RoboManni) on 2021-06-09 15:15:02 in reply to 12 [link] [source]

My example of UDF (user defined function) was just to show how I mimic stored procedures. My implementation is more like an UDF than stored procedure or function because I still need to use the select statement or an other expression consumer. However, what was important for me -to put this effort into such a small set of extension functions- was that big portions of logic were required to be handled. Let's say: My application get data from various sources (text formatted and blobs), inserted into one table and has to schedule different actions based on them. My application has to run uninterrupted for years. By putting the logic into the database in form of triggers enabled to do that without interrupting the operation; enabled also to improve the logic for some data formats while the scheduler for the others were not affected. "SQL injection as a feature." (I am using a controlling input pipe for this).

It is possible to load extension .so/.dll files - but how can they be unloaded or updated/replaced on a permanently in-use db connection? This is OS dependent if possible at all, I guess. With triggers this is very easy, you can simply drop them and create a new one - even both within one transaction, which makes sure that the rest does not get disturbed and even the next message can use the updated logic without getting lost.

For me the main benefit for UDF or Stored Procedure is the parameterization. If a view could be called with parameters, that would be almost what was in my mind from the beginning.

My implementation cannot generate table results, which a stored procedure (called with parameters) might be able to return. That's the main difference and where I see the benefit.

(17.1) By Max (Maxulite) on 2021-06-10 12:06:29 edited from 17.0 in reply to 15 [link] [source]

As a long time user of sqlite as a library and a data-mining tool, I'm always for parameterization and tried to add it in one or other way. Recently I suggested using views with parameters at the forum (https://sqlite.org/forum/forumpost/8f0351af38), but I doubt this will be implemented. My post also suggested the solution in a form of virtual table and I used it several times since. As I see, you're familiar with user-defined functions so probably with virtual tables too so you would implement something like this.

As a short description, you have a list of named queries in a table and all of them use named or unnamed parameters in the bodies (?1 , :myparameter). The name and the body are stored in the separated fields. The names are registered as eponymous-only modules and when sqlite meets something like

Select * from my_parameterized_query(123, 'foo bar')

the module responses with a schema containing the input field according to the parameters found in the bodies and the names of the output columns, makes the best cursor negotiating low cost when sqlite provides the required parameters.

Not surprisingly, this method also allows using parameterized quering "calls" inside other parameterized queries, so they sometimes can be used as "a library" of inter-calling table-valued functions.

Since new functions are created very easily, they sometimes helps in unexpected ways. I had a cte when I needed a join with the recursive part in a sub-query, which Sqlite currently forbids. But I made a parameterized query for the top-level join accepting the recursive "parameters" and making anything required inside the "body" (for example, group by).

(18) By MBL (RoboManni) on 2021-06-10 18:35:31 in reply to 17.1 [link] [source]

Yes, I also wrote some virtual tables e.g. to read a directory listing or a list of lines from all files which are selected by another SQL statement or simply to use DBase/FoxPro files from within SQLite (long time ago now and not used anymore since about 2010); but this was long before the value enabled virtual tables became invented. I may have to step into this subject myself once more to see if and how our wishes for parameterized functionality can be accomplished with that capabilities. Unfortunately I am very busy and short in time. But thanks for this good hint.

(19) By anonymous on 2021-06-10 19:37:57 in reply to 15 [link] [source]

For me the main benefit for UDF or Stored Procedure is the parameterization. If a view could be called with parameters, that would be almost what was in my mind from the beginning.

Actually, this I agree with, and is something I had wanted too. I would suggest extending the syntax for CREATE VIEW so that the list of column names can include type names and DEFAULT (and maybe also COLLATE); if the type name includes HIDDEN then it is a hidden column which can be constrained by table valued function syntax like virtual tables can.

However, it is then seems not possible to copy the value that is constrained, unless a virtual table extension is used to do that (and I don't know if that will work in a recursive CTE). Host parameter syntax could be another way, maybe.

My implementation cannot generate table results, which a stored procedure (called with parameters) might be able to return. That's the main difference and where I see the benefit.

I believe you that the ability to generate table results can be useful. There is RETURNING, although it is limited:

  • A view/triggers cannot specify what values are returned by RETURNING.

  • You cannot use RETURNING anywhere that SELECT is allowed.

(20) By Bjoern Hoehrmann (bjoern) on 2021-06-10 20:11:51 in reply to 15 [link] [source]

https://github.com/0x09/sqlite-statement-vtab/ gives you essentially parameterized VIEWs (but with an optimization boundary).

(21) By MBL (RoboManni) on 2021-06-13 17:50:00 in reply to 20 [link] [source]

Thanks for the hint .. this is really working great!

I also found another virtual table, which I often were working around for, which is the https://github.com/jakethaw/pivot_vtab to transpose a long table into a wide table. Unfortunately that implementation seems to work only with a single columns solution; there is some space for improvements...

(28) By midijohnny on 2021-11-08 16:07:59 in reply to 20 [link] [source]

That looks like a very nice module: since it glues together existing features to give something very powerful - that is - create table-valued functions using only SQL.

Is there somewhere we can vote to have this included as a 'standard' module?

(22) By anonymous on 2021-06-26 12:24:56 in reply to 1.1 [link] [source]

Does this help? https://cgsql.dev/docs/introduction

(23) By anonymous on 2021-06-26 17:29:16 in reply to 22 [link] [source]

CG/SQL is a code generation system for the popular SQLite library that allows developers to write stored procedures in a variant of Transact-SQL (T-SQL) and compile them into C code that uses SQLite’s C API to do the coded operations.

A problem with this is that unlike standard SQL you cannot read the SQL code at run time, it seems like to me.

(24) By MBL (RoboManni) on 2021-06-27 10:31:10 in reply to 23 [link] [source]

I am quiet happy with my own way of implementation as a user defined function, which works like a wrapper around a table as "call stack" and using normal trigger mechanisms. It would be nice to have a "standard language frame" for this but it is unlikely to come with SQLite in standard.

What I was missing I found in that value enabled virtual table "statement". This works nice and offers probably even more opportunities than what it does now for me.

I also implemented Lua into my list of extensions and with few extension functions I am able to use that for whatever would be difficult to code in SQL otherwise. My applications became script enabled this way.

(30) By doug (doug9forester) on 2021-11-09 14:42:53 in reply to 23 [link] [source]

The code generator could generate a static character string for the C code which contains the text of the SQL. Easy - peasy!

(29) By anonymous on 2021-11-08 21:31:09 in reply to 22 [link] [source]

Is there something like that that will work at run time (parsing the procedural SQL code at run time, and then preparing a procedural SQL object which can be executed later) instead of at compile time?