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

2017-04-20 Thread Hick Gunter
...@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 trig

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

2017-04-20 Thread 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

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

2017-04-19 Thread 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 peo

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

2017-04-19 Thread 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

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

2017-04-19 Thread 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,

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

2017-04-19 Thread 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 thin

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

2017-04-19 Thread 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...@

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

2017-04-19 Thread 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

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

2017-04-19 Thread 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 permissi

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

2017-04-19 Thread 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 popula

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

2017-04-19 Thread 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

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

2017-04-19 Thread 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 w

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

2017-04-19 Thread 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 thi

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

2017-04-16 Thread 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

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

2017-04-16 Thread 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 earli

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

2017-04-16 Thread 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 in

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

2017-04-16 Thread 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

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

2017-04-15 Thread 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 w

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

2017-04-15 Thread 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

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

2017-04-15 Thread Christian Werner
On 04/15/2017 06:18 PM, Richard Hipp wrote: On 4/15/17, Manoj Sengottuvel 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. I

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

2017-04-15 Thread Richard Hipp
On 4/15/17, Manoj Sengottuvel 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 l

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

2017-04-15 Thread 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/listi