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

[Hick Gunter]

Maybe Prakash Premkumar <prakash.p...@gmail.com> or Sairam Gaddam 
<gaddamsai...@gmail.com>, 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:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Clemens Ladisch
Gesendet: Donnerstag, 20. April 2017 09:38
An: sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
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: h...@scigames.at

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

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Olivier Mascia]

> Le 20 avr. 2017 à 01:13, petern  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 ":

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  wrote:

>
>
> On 2017/04/19 6:58 PM, James K. Lowden wrote:
>
>> On Sun, 16 Apr 2017 12:01:01 +0200
>> Darko Volaric  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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

On Sun, 16 Apr 2017 12:01:01 +0200
Darko Volaric  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 19 Apr 2017, at 8:59pm, Domingo Alvarez Duarte  wrote:
> 
> What I understood looking at the sqlite3 sources is that an update is always 
> 3 operations:
> 
> 1- Read old row
> 
> 2- Delete old row
> 
> 3- Insert updated row
> 
> So I seems that using "insert" would be less work.

I didn’t think of that.  Your way is probably faster.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Domingo Alvarez Duarte]

Hello !

What I understood looking at the sqlite3 sources is that an update is 
always 3 operations:


1- Read old row

2- Delete old row

3- Insert updated row

So I seems that using "insert" would be less work.

Cheers !


On 19/04/17 16:27, Simon Slavin wrote:

On 19 Apr 2017, at 7:47pm, no...@null.net 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 19 Apr 2017, at 7:47pm, no...@null.net 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[nomad]

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

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

[James K. Lowden]

On Sun, 16 Apr 2017 12:01:01 +0200
Darko Volaric  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <mingo...@gmail.com>
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 <smanoj...@gmail.com> 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
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <smanoj...@gmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Philip Warner]

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 <smanoj...@gmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 16 Apr 2017, at 5:27pm, Jens Alfke  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



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



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jens Alfke]

> On Apr 15, 2017, at 2:17 PM, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Tim Streater]

On 15 Apr 2017 at 22:17, Simon Slavin  wrote: 

> On 15 Apr 2017, at 9:14pm, petern  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Darko Volaric]

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 <smanoj...@gmail.com> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 15 Apr 2017, at 9:14pm, petern  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <
christian.wer...@t-online.de> wrote:

> On 04/15/2017 06:18 PM, Richard Hipp wrote:
>
>> On 4/15/17, Manoj Sengottuvel <smanoj...@gmail.com> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <smanoj...@gmail.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 4/15/17, Manoj Sengottuvel <smanoj...@gmail.com> 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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users