Is it possible to create the Stored Procedure (SP) in Sqlite?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it possible to create the Stored Procedure (SP) in Sqlite?

Manoj Sengottuvel
Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?


regards
Manoj
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Richard Hipp-3
On 4/15/17, Manoj Sengottuvel <[hidden email]> wrote:
> Hi Richard,
>
> Is it possible to create the Stored Procedure (SP) in Sqlite?
>
> if not , is there any alternate way for SP?

Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency.  Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Christian Werner
On 04/15/2017 06:18 PM, Richard Hipp wrote:

> On 4/15/17, Manoj Sengottuvel <[hidden email]> wrote:
>> Hi Richard,
>>
>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>
>> if not , is there any alternate way for SP?
>
> Short answer:  No.
>
> Longer answer:  With SQLite, your application is the stored procedure.
> In a traditional client/server database like PostgreSQL or Oracle or
> SQL Server, every SQL statement involves a round-trip to the server.
> So there is a lot of latency with each command.  The way applications
> overcome this latency is to put many queries into a stored procedure,
> so that only the stored procedure invocation needs to travel over the
> wire and latency is reduced to a single server round-trip.
>
> But with SQLite, each statement is just a procedure call.  There is no
> network traffic, not IPC, and hence very little latency.  Applications
> that use SQLite can be very "chatty" with the database and that is not
> a problem.  For example, the SQLite website is backed by SQLite (duh!)
> and a typical page request involves 200 to 300 separate queries.  That
> would be a performance killer with a client/server database, but with
> SQLite it is not a problem and the pages render in about 5
> milliseconds.

May I vote this conversation to be included in the SQLite FAQ.

Best,
Christian

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

petern
Yes, please include it in the FAQ along with a description of the SQLite
stored procedure pattern syntax which is never disclosed in these replies:

CREATE TRIGGER  my_sproc INSTEAD OF INSERT on my_sproc_caller_view
BEGIN
--My procedural code to be prepared and stored in the database.
END;
--Called by the following syntax with specific VALUES():
INSERT INTO my_sproc_caller_view VALUES();
--And results returned in the requisite my_sproc_work_table upon which
my_sproc_caller_view is created.

As for the chattiness of client/server DB's, there is a more frequent cause
of that inefficient pattern.  In particular, there is often a ham fisted
design which keeps vital model data in application code or in tables which
are not SQL joined to get the final result.  Often the vendor will
encourage the same incompetent programmers to recast their inefficient
queries within the bodies of stored procedures as proof that the firm needs
to buy much larger and more expensive hardware and requisite software
licenses.  [Filed under vendor contracts for $435 hammers and $600 toilet
seats.]






On Sat, Apr 15, 2017 at 11:33 AM, Christian Werner <
[hidden email]> wrote:

> On 04/15/2017 06:18 PM, Richard Hipp wrote:
>
>> On 4/15/17, Manoj Sengottuvel <[hidden email]> wrote:
>>
>>> Hi Richard,
>>>
>>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>>
>>> if not , is there any alternate way for SP?
>>>
>>
>> Short answer:  No.
>>
>> Longer answer:  With SQLite, your application is the stored procedure.
>> In a traditional client/server database like PostgreSQL or Oracle or
>> SQL Server, every SQL statement involves a round-trip to the server.
>> So there is a lot of latency with each command.  The way applications
>> overcome this latency is to put many queries into a stored procedure,
>> so that only the stored procedure invocation needs to travel over the
>> wire and latency is reduced to a single server round-trip.
>>
>> But with SQLite, each statement is just a procedure call.  There is no
>> network traffic, not IPC, and hence very little latency.  Applications
>> that use SQLite can be very "chatty" with the database and that is not
>> a problem.  For example, the SQLite website is backed by SQLite (duh!)
>> and a typical page request involves 200 to 300 separate queries.  That
>> would be a performance killer with a client/server database, but with
>> SQLite it is not a problem and the pages render in about 5
>> milliseconds.
>>
>
> May I vote this conversation to be included in the SQLite FAQ.
>
> Best,
> Christian
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Simon Slavin-3

On 15 Apr 2017, at 9:14pm, petern <[hidden email]> wrote:

> Yes, please include it in the FAQ

It’s not a FAQ.  Not on this list, at least.  I would argue against it.

I do agree that DRH’s explanation of why it’s not as important in SQLite as in client/server engines is well written.  We can point to it when we need it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Darko Volaric
In reply to this post by Manoj Sengottuvel
If you really wanted to have stored procedures and did not mind calling them using a function syntax, you could write your own stored procedure extension. You'd store them in their own table, write a custom function that evaluates them and call them something like this: sp("name", param1, param2,...). A simple recursive-decent parser would likely do the trick to parse and evaluate the procedures. Depending on your needs, the "stored procedures" could be as simple as a series of SQL statements to execute, with parameter substitutions, which would be almost trivial to write. If you were so inclined.

There are good reasons to have stored procedures other than reducing connection latency - developers like to encapsulate logic that is associated entirely with the database in the database, use them to do extended checking, to populate denormalized or derived data, or to provide a level of abstraction, for instance. Although this code could be put in the client side you may want to be able to maintain the database independently of the application or you may have multiple client applications and want to avoid duplicating code in multiple code bases, for instance.



> On Apr 15, 2017, at 3:57 PM, Manoj Sengottuvel <[hidden email]> wrote:
>
> Hi Richard,
>
> Is it possible to create the Stored Procedure (SP) in Sqlite?
>
> if not , is there any alternate way for SP?
>
>
> regards
> Manoj
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Tim Streater-3
In reply to this post by Manoj Sengottuvel
On 15 Apr 2017 at 22:17, Simon Slavin <[hidden email]> wrote:

> On 15 Apr 2017, at 9:14pm, petern <[hidden email]> wrote:
>
>> Yes, please include it in the FAQ
>
> It’s not a FAQ.  Not on this list, at least.  I would argue against it.

Well he meant on the sqlite website. And if it's not a FAQ then it's nonetheless useful info, so perhaps what the sqlite website might have is an LFAQ page (Less Frequently Asked Questions).

--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Jens Alfke-2
In reply to this post by Simon Slavin-3

> On Apr 15, 2017, at 2:17 PM, Simon Slavin <[hidden email]> wrote:
>
> I do agree that DRH’s explanation of why it’s not as important in SQLite as in client/server engines is well written.  We can point to it when we need it.

Is this list archived anywhere convenient? Last time I tried to look for an earlier post, I had to dig through the Mailman interface, which wanted a password, which I didn’t remember, which I had to ask for by email … not super conducive to knowledge sharing :(

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Simon Slavin-3

On 16 Apr 2017, at 5:27pm, Jens Alfke <[hidden email]> wrote:

> Is this list archived anywhere convenient?

I just google for posts I remember and google usually turns up an archive of this list.

googling "sqlite stored procedure latency" turns up

<https://www.mail-archive.com/sqlite-users@.../msg103190.html>

And it seems that that archive has a search field on its homepage:

<https://www.mail-archive.com>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Philip Warner
In reply to this post by Richard Hipp-3
There is another reason to have stored procedures: encapsulating logic across
apps/clients.

A great deal can be done in triggers, but not much in terms of queries or
complex parameterized updates.

It would be great, imo, if triggers could have durable local storage (ie.
variables) and if this were built upon to allow stored procedures, life would be
much more fun.

Parameterized multi-query SQL statements returning event just a single row set
would be fine.



On 16/04/2017 2:18 AM, Richard Hipp wrote:

> On 4/15/17, Manoj Sengottuvel <[hidden email]> wrote:
>> Hi Richard,
>>
>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>
>> if not , is there any alternate way for SP?
> Short answer:  No.
>
> Longer answer:  With SQLite, your application is the stored procedure.
> In a traditional client/server database like PostgreSQL or Oracle or
> SQL Server, every SQL statement involves a round-trip to the server.
> So there is a lot of latency with each command.  The way applications
> overcome this latency is to put many queries into a stored procedure,
> so that only the stored procedure invocation needs to travel over the
> wire and latency is reduced to a single server round-trip.
>
> But with SQLite, each statement is just a procedure call.  There is no
> network traffic, not IPC, and hence very little latency.  Applications
> that use SQLite can be very "chatty" with the database and that is not
> a problem.  For example, the SQLite website is backed by SQLite (duh!)
> and a typical page request involves 200 to 300 separate queries.  That
> would be a performance killer with a client/server database, but with
> SQLite it is not a problem and the pages render in about 5
> milliseconds.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Domingo Alvarez Duarte
Hello Philip !

There was this attempt https://www.sqliteconcepts.org/PL_index.html and
I tried to adapt to sqlite3 but the change on the sqlite3 vm compared to
sqlite2 made it a lot harder.

The vm of sqlite3 is not well documented and is changing all the time.

But I also agreed with you if we could have "@variables" at connection
level, query level, trigger level and also be able to write triggers in
"C" (or another glue language), simple stored procedures (queries with
parameters at sql level) life would be a bit easier.

Cheers !

On 19/04/17 08:34, Philip Warner wrote:

> There is another reason to have stored procedures: encapsulating logic
> across apps/clients.
>
> A great deal can be done in triggers, but not much in terms of queries
> or complex parameterized updates.
>
> It would be great, imo, if triggers could have durable local storage
> (ie. variables) and if this were built upon to allow stored
> procedures, life would be much more fun.
>
> Parameterized multi-query SQL statements returning event just a single
> row set would be fine.
>
>
>
> On 16/04/2017 2:18 AM, Richard Hipp wrote:
>> On 4/15/17, Manoj Sengottuvel <[hidden email]> wrote:
>>> Hi Richard,
>>>
>>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>>
>>> if not , is there any alternate way for SP?
>> Short answer:  No.
>>
>> Longer answer:  With SQLite, your application is the stored procedure.
>> In a traditional client/server database like PostgreSQL or Oracle or
>> SQL Server, every SQL statement involves a round-trip to the server.
>> So there is a lot of latency with each command.  The way applications
>> overcome this latency is to put many queries into a stored procedure,
>> so that only the stored procedure invocation needs to travel over the
>> wire and latency is reduced to a single server round-trip.
>>
>> But with SQLite, each statement is just a procedure call.  There is no
>> network traffic, not IPC, and hence very little latency. Applications
>> that use SQLite can be very "chatty" with the database and that is not
>> a problem.  For example, the SQLite website is backed by SQLite (duh!)
>> and a typical page request involves 200 to 300 separate queries.  That
>> would be a performance killer with a client/server database, but with
>> SQLite it is not a problem and the pages render in about 5
>> milliseconds.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

petern
My sense from these replies is that nobody bothers to try using triggers to
store their SQLite procedural code within the DB.  I was skeptical when I
first learned of the technique but the trigger syntax is very
computationally permissive.  Frankly, I'm still surprised by what one is
allowed to do in a trigger.

My hope is that more people will use this technique and eventually a good
proposal will emerge for syntactic sugar which condenses the syntax.

Here is a more concrete example without syntax error.  Just paste into a
SQLite shell and see for yourself.

CREATE VIEW my_sproc_caller_view as SELECT (33)a, ('some_param')b, (55)c, *
from my_sproc_worktable;

CREATE TRIGGER my_sproc INSTEAD OF INSERT ON my_sproc_caller_view
BEGIN
--THE STUFF WRITTEN HERE IS STORED IN THE DB.  AKA STORED PROCEDURE.
--What can be done:
--  Do something to insert/update/delete rows from the work table.
--  Using "SELECT fn(p1,p2,p3,...);" call some extension function written
in C etc.
--  Update/insert/delete other tables according to parameters a,b,c and/or
worktable rowset.
--  Call this sproc recursively up to SQLITE_MAX_TRIGGER_DEPTH
--  Have one sproc worktable per connection by using a temp worktable.
--What can't be done? Is there some operation missing here?
--One can introduce as many parameter and variable columns as needed to do
anything whatsoever.
select * from my_sproc_worktable;
END;

CREATE TABLE my_sproc_worktable(var1 TEXT,var2
TEXT,resultCol1,resultCol2,resultColN,etc);

INSERT INTO my_sproc_caller_view(a,b,c,var1) VALUES(1,2,3,4);
INSERT INTO my_sproc_caller_view(a,b,c,var2)
VALUES(7,8,9,"and_some_other_thing");

--SEE: https://sqlite.org/lang_createtrigger.html

On Wed, Apr 19, 2017 at 6:26 AM, Domingo Alvarez Duarte <[hidden email]>
wrote:

> Hello Philip !
>
> There was this attempt https://www.sqliteconcepts.org/PL_index.html and I
> tried to adapt to sqlite3 but the change on the sqlite3 vm compared to
> sqlite2 made it a lot harder.
>
> The vm of sqlite3 is not well documented and is changing all the time.
>
> But I also agreed with you if we could have "@variables" at connection
> level, query level, trigger level and also be able to write triggers in "C"
> (or another glue language), simple stored procedures (queries with
> parameters at sql level) life would be a bit easier.
>
> Cheers !
>
> On 19/04/17 08:34, Philip Warner wrote:
>
>> There is another reason to have stored procedures: encapsulating logic
>> across apps/clients.
>>
>> A great deal can be done in triggers, but not much in terms of queries or
>> complex parameterized updates.
>>
>> It would be great, imo, if triggers could have durable local storage (ie.
>> variables) and if this were built upon to allow stored procedures, life
>> would be much more fun.
>>
>> Parameterized multi-query SQL statements returning event just a single
>> row set would be fine.
>>
>>
>>
>> On 16/04/2017 2:18 AM, Richard Hipp wrote:
>>
>>> On 4/15/17, Manoj Sengottuvel <[hidden email]> wrote:
>>>
>>>> Hi Richard,
>>>>
>>>> Is it possible to create the Stored Procedure (SP) in Sqlite?
>>>>
>>>> if not , is there any alternate way for SP?
>>>>
>>> Short answer:  No.
>>>
>>> Longer answer:  With SQLite, your application is the stored procedure.
>>> In a traditional client/server database like PostgreSQL or Oracle or
>>> SQL Server, every SQL statement involves a round-trip to the server.
>>> So there is a lot of latency with each command.  The way applications
>>> overcome this latency is to put many queries into a stored procedure,
>>> so that only the stored procedure invocation needs to travel over the
>>> wire and latency is reduced to a single server round-trip.
>>>
>>> But with SQLite, each statement is just a procedure call.  There is no
>>> network traffic, not IPC, and hence very little latency. Applications
>>> that use SQLite can be very "chatty" with the database and that is not
>>> a problem.  For example, the SQLite website is backed by SQLite (duh!)
>>> and a typical page request involves 200 to 300 separate queries.  That
>>> would be a performance killer with a client/server database, but with
>>> SQLite it is not a problem and the pages render in about 5
>>> milliseconds.
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

James K. Lowden
In reply to this post by Darko Volaric
On Sun, 16 Apr 2017 12:01:01 +0200
Darko Volaric <[hidden email]> wrote:

> There are good reasons to have stored procedures other than reducing
> connection latency - developers like to encapsulate logic that is
> associated entirely with the database in the database, use them to do
> extended checking, to populate denormalized or derived data, or to
> provide a level of abstraction, for instance.

Exactly so.  A stored procedure can serve the same purpose as a
function in C: to assign a name to a particular body of code.  

But the same effect can be had in SQLite without stored procedures per
se.  In a few projects I've used the build repository to accomplish
much the same thing.  

Choose a directory, say, "sql" for the queries that will be used in
the application.  Each file has a name and contains one query.  In that
way, every query has a name.  A bit of awk transforms that directory
into a C source code module with a contant array of strings.  The
filenames become an enumeration that serves to index the array by name.
(A C++ std::map also works.)  Calling the "stored procedure" is a simple
matter:

        sqlite3_prepare(db, sql[name], ...)

One nice feature of this approach is that testing queries is simple.
It also confines all the SQL to one module, and avoids writing queries
"in line" as C strings.  And, not for nothing, a well chosen query
name renders the code clearer than embedded SQL does.  

--jkl


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

nomad
In reply to this post by petern
On Wed Apr 19, 2017 at 09:53:07AM -0700, petern wrote:
> My sense from these replies is that nobody bothers to try using
> triggers to store their SQLite procedural code within the DB.  I was
> skeptical when I first learned of the technique but the trigger
> syntax is very computationally permissive.  Frankly, I'm still
> surprised by what one is allowed to do in a trigger.

Just to provide at least one data point in the other direction, I use
triggers quite heavily as a kind of stored procedure.

Instead of basing them on views however I use real tables and AFTER
INSERT triggers whose final statement deletes the NEW row just
inserted.

I see two benefits to the use of AFTER INSERT triggers:

    * Constraints are enforced so SQLite catches invalid
    "procedure calls."
    * Default values for columns (or "arguments") can be defined. This
    is very useful if you want to use the incoming value in multiple
    statements - you don't have to hardcode a bunch of
    COALESCE(NEW.col, $DEFAULT) values everywhere.

Because the INSERT/TRIGGER/DELETE happens within a transaction I expect
the data never to hit the disk. I haven't measured it but I guess the
performance would not be too far off the INSTEAD-OF/VIEW trigger.

> CREATE VIEW my_sproc_caller_view as SELECT (33)a, ('some_param')b,
> (55)c, * from my_sproc_worktable;

My own naming convention uses tables like "func_action_name".

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Simon Slavin-3

On 19 Apr 2017, at 7:47pm, [hidden email] wrote:

> I use
> triggers quite heavily as a kind of stored procedure.
>
> Instead of basing them on views however I use real tables and AFTER
> INSERT triggers whose final statement deletes the NEW row just
> inserted.
>
> I see two benefits to the use of AFTER INSERT triggers:
>
>    * Constraints are enforced so SQLite catches invalid
>    "procedure calls."
>    * Default values for columns (or "arguments") can be defined. This
>    is very useful if you want to use the incoming value in multiple
>    statements - you don't have to hardcode a bunch of
>    COALESCE(NEW.col, $DEFAULT) values everywhere.

Had you considered doing UPDATE instead of INSERT ?  Leave one row in the table and issue an UPDATE command when you want to trigger a trigger.  If the column you’re changing isn’t indexed it’s a little faster.  And just like INSERT you can use the value you set, using CASE … END, to set what you want to happen.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

R Smith
In reply to this post by James K. Lowden


On 2017/04/19 6:58 PM, James K. Lowden wrote:

> On Sun, 16 Apr 2017 12:01:01 +0200
> Darko Volaric <[hidden email]> wrote:
>
>> There are good reasons to have stored procedures other than reducing
>> connection latency - developers like to encapsulate logic that is
>> associated entirely with the database in the database, use them to do
>> extended checking, to populate denormalized or derived data, or to
>> provide a level of abstraction, for instance.
> Exactly so.  A stored procedure can serve the same purpose as a
> function in C: to assign a name to a particular body of code.
>
> But the same effect can be had in SQLite without stored procedures per
> se.  In a few projects I've used the build repository to accomplish
> much the same thing.//....

Indeed so, and I've had good success using a similar principle by simply
storing those SQL "files" as simple TEXT column in a table named
"StoredProcs" in any DB with a trivial step in the program to execute it
when needed - thus truly having "Stored Procedures" by virtue of placement.

However, I believe the main motivation of the requests do not intend the
placement of the procedures so much as the character thereof - They do
not care whether it is stored in a Trigger, File or Table, I believe the
real request is for a system of assignable variables ( LET @Name =
'John'; ) which could also be used as parameters in a query, or maybe
assignable datasets ( #TmpResult = Query('...'); ) - perhaps even
traversable datasets ( for each @Row in Query('...') do { ...
DoSomethingWith( @Row.Name ); ... } ) and next will be flow control ( IF
(thisIsTrue) BEGIN doThat(); END ).

Once we start on this road, ALL those will become wanted - all of which
are great, but probably outside the spirit of SQ"Lite".

(Note: I'm not advocating against. I myself am on the fence - using
SQLite so much and never in a size-sensitive anything, so it would be a
boon to have proper procedural execution within, but a "general target
audience" I don't make.)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

petern
1. Forgot to mention. In cases where the WHEN clause is not convenient for
trigger style stored procedure condition branching there is also "SELECT
raise(ignore) WHERE <condition>":

https://sqlite.org/lang_createtrigger.html#raise

2. Here is a question.  It would be helpful to know if TRIGGERs are stored
as prepared SQLite byte code or not.  What does the SQLite engine do
exactly?  Anybody?

If CREATE TRIGGER produces prepared byte code, then TRIGGER programs are
not equivalent to making your own table of stored procedures in TEXT
columns that have to be loaded by external code which repeatedly issues the
prepare statement call.

3. For variables compare, "UPDATE mysproc_worktable SET name='John'" with
"LET @name='John'".   The difference amounts to a lack of imagination.

4. Those requiring loop constructs should consider that TRIGGERs are
re-entrant and can be called recursively.  Any loop can be written as a
recursive call.

5. Recursive CTE's are also available to directly generate/populate the
result columns of the worktable.

In short, SQLite has a fairly complete defacto stored procedure capability
that many could benefit from. But either for lack of a direct CREATE
PROCEDURE statement or a profound lack of imagination, many will never use
it.



On Wed, Apr 19, 2017 at 2:22 PM, R Smith <[hidden email]> wrote:

>
>
> On 2017/04/19 6:58 PM, James K. Lowden wrote:
>
>> On Sun, 16 Apr 2017 12:01:01 +0200
>> Darko Volaric <[hidden email]> wrote:
>>
>> There are good reasons to have stored procedures other than reducing
>>> connection latency - developers like to encapsulate logic that is
>>> associated entirely with the database in the database, use them to do
>>> extended checking, to populate denormalized or derived data, or to
>>> provide a level of abstraction, for instance.
>>>
>> Exactly so.  A stored procedure can serve the same purpose as a
>> function in C: to assign a name to a particular body of code.
>>
>> But the same effect can be had in SQLite without stored procedures per
>> se.  In a few projects I've used the build repository to accomplish
>> much the same thing.//....
>>
>
> Indeed so, and I've had good success using a similar principle by simply
> storing those SQL "files" as simple TEXT column in a table named
> "StoredProcs" in any DB with a trivial step in the program to execute it
> when needed - thus truly having "Stored Procedures" by virtue of placement.
>
> However, I believe the main motivation of the requests do not intend the
> placement of the procedures so much as the character thereof - They do not
> care whether it is stored in a Trigger, File or Table, I believe the real
> request is for a system of assignable variables ( LET @Name = 'John'; )
> which could also be used as parameters in a query, or maybe assignable
> datasets ( #TmpResult = Query('...'); ) - perhaps even traversable datasets
> ( for each @Row in Query('...') do { ... DoSomethingWith( @Row.Name ); ...
> } ) and next will be flow control ( IF (thisIsTrue) BEGIN doThat(); END ).
>
> Once we start on this road, ALL those will become wanted - all of which
> are great, but probably outside the spirit of SQ"Lite".
>
> (Note: I'm not advocating against. I myself am on the fence - using SQLite
> so much and never in a size-sensitive anything, so it would be a boon to
> have proper procedural execution within, but a "general target audience" I
> don't make.)
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Olivier Mascia
> Le 20 avr. 2017 à 01:13, petern <[hidden email]> a écrit :
>
> 2. Here is a question.  It would be helpful to know if TRIGGERs are stored
> as prepared SQLite byte code or not.  What does the SQLite engine do
> exactly?  Anybody?

I'm answering to test my understanding, confronting it to more knowledgeable people here on this list.  As far as I understood, SQLite will parse and compile the trigger text as part of each statement using them.  No bytecode compilation upfront, nor storage of it.  And that is fine by me, well in line with the design goals of SQLite.

Please correct me as needed.

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Clemens Ladisch
Olivier Mascia wrote:
> As far as I understood, SQLite will parse and compile the trigger text
> as part of each statement using them.  No bytecode compilation upfront,
> nor storage of it.

SQLite parses all triggers (and all other schema objects) when it reads
the schema (see "struct Trigger" and "struct TriggerStep" in the source).
However, the bytecode for them is generated only when the actual query
is prepared (see "struct TriggerPrg" and "struct SubProgram").


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to create the Stored Procedure (SP) in Sqlite?

Hick Gunter
Maybe Prakash Premkumar <[hidden email]> or Sairam Gaddam <[hidden email]>, who seemed hell bent on implementing stored procedures (or at least storing generated bytecode) about two years ago, have made progress in the meantime?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Clemens Ladisch
Gesendet: Donnerstag, 20. April 2017 09:38
An: [hidden email]
Betreff: Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

Olivier Mascia wrote:
> As far as I understood, SQLite will parse and compile the trigger text
> as part of each statement using them.  No bytecode compilation
> upfront, nor storage of it.

SQLite parses all triggers (and all other schema objects) when it reads the schema (see "struct Trigger" and "struct TriggerStep" in the source).
However, the bytecode for them is generated only when the actual query is prepared (see "struct TriggerPrg" and "struct SubProgram").


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users