Re: [sqlite] Stored Procedures
On May 14, 2018, at 9:44 PM, Alek Paunovwrote: > > Practical example of the benefit is that e.g. same complex turnover report > which implementation includes bunch of intermediate calculations, could be > used with same SQL call from Python desktop application, directly as Excel > Data Source or on partial replica through SQL.js [1] in the browser, without > coding the same thing 3 times: on Python, VBA and JS. How common is that for a DBMS that should never be used over a network? That is, all three applications would have to be running on the same machine for proper data safety. I get why a client-server DBMS would have a mix of strange applications in many languages accessing it, but SQLite? Don’t you tend to just pick one implementation language appropriate to the delivery platform? > How many list readers really missed the Stored procedures feature? I’m with drh: stored procedures are a nasty hack needed only to minimize RTT in the high-latency LAN (or WAN!) environment. The cost of this hack is proprietary lock-in, since AFAIK no DBMS supports another’s stored procedure dialect. I’m basing that on a skim of https://en.wikipedia.org/wiki/Stored_procedure#Implementation Apparently SQL/PSM is a “standard,” but poorly supported in the market, according to that article, with all other DBMSes going off in their own directions. If you have multiple applications accessing a given SQLite DB on a single computer, chances are that they’re all coded in the same programming language, so you can just write your shared procedures in a library for that language. If it happens that you really are using multiple languages on a single computer or the same DB has to be cross-compatible among disparate machines with different programs (e.g. desktop and mobile) then you can probably write your shared procedure library in a statically-compiled language that can export a C-compatible ABI, then link to it from any language you like, since most practical non-C family languages have C-compatible FFI mechanisms. You need a way to link to SQLite in the first place, right? In fact, why not just use C to extend SQLite, adding the functions you need? > My humble opinion is that for us, the minority, the best move is to look for > approaches and collaboration on "incubating" valuable advanced features > initially outside of the SQLite core, then eventually apply result solutions > in our own projects and finally propose for inclusion in the core library > only small key components (which lead to inefficiencies or maintenance burden > when living outside). That path leads to a non-“Lite” implementation. I think you can add some flavor of stored procedures as an extension, allowing it to be compiled out of binaries that don’t require it, but it still feels like mission creep to me. The only option I can think of that fits the philosophy of SQLite would be to allow writing stored procedures in Jim Tcl or TH1. The code for both is already in the SQLite tree, though both are used for other things, so neither is currently built into a SQLite binary. This document maps a pre-trodden path: https://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html Scroll down to "SQLite Extensions Written In Tcl.” All I’m proposing is that that be made more of a first-class citizen, so that you can choose to enable it from the SQLite source tree, not by starting first with Tcl and extending it with SQLite. > That way, at some stage, SQLite could even have several procedural dialect > implementations - TSQL, MySQL, etc. :-) Are any of those actually good languages? Every time I’ve seen stored procedures, the syntax is horrid, but I lack the experience to discern among these alternatives. All I know is that I’ve managed to avoid every one of these languages for my entire career, and I’d like to get through the rest of it while still avoiding stored procedures, if possible. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
I missed stored procedures till i coded my workaround. I had a table named sprocs with the sql statements coded into a text blob in each row with each row representing a different sproc. A linked table named params had a column with a tag representing a parameter for the associated sproc. Pull the sproc text and the list of the parameters pass that to a prep routine that replaces the tag text with the relevant values and then execute the sql. Works like a champ. Sent from my T-Mobile 4G LTE Device Original message From: Alek Paunov <a...@declera.com> Date: 5/14/18 8:44 PM (GMT-08:00) To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Stored Procedures On 2018-05-09 03:56, Richard Hipp wrote: ... > > The other benefit of stored procedures is that it provides a way to > code up a common operation once (correctly!) and store it in the > database, rather than having multiple clients all have to work out the > operating themselves (some of them perhaps incorrectly). The usual > way of handling that in SQLite is to store a script in a text column > someplace, then execute them as needed. SQLite began life as a TCL > extension, and so naturally TCL scripts work very well for this kind > of thing. > To amplify the remark above, for me, the Stored procedures are mostly method to add methods :-) to the "cold" data objects encoded in tables, just like the OO style SQLite C API defines interfaces and behavior on top of "cold" C structures. That way, schemes and non-trivial calculations on top of them can be encapsulated and reused as modules (or packages as named in some design tools). Practical example of the benefit is that e.g. same complex turnover report which implementation includes bunch of intermediate calculations, could be used with same SQL call from Python desktop application, directly as Excel Data Source or on partial replica through SQL.js [1] in the browser, without coding the same thing 3 times: on Python, VBA and JS. Having said that, Let's take the survey: How many list readers really missed the Stored procedures feature? If there are enough interest, I would be happy to discuss an (possibly naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level procedures) mostly in pure SQL with just tiny C runner as UDF TValued Function [2]. Kind Regards, Alek (*) On the sqlite mailing list, we see permanent conflict of interests: On the one side are 99% of the users, for which sqlite is just better (than e.g. compressed json) storage option (usually for small as size or simple as structure data). On the other, are the advanced users - these with complex applications who [especially after the great advancements from the last years (CTEs, JSON and so on)] try to encapsulate as much as possible business logic into the DB. My humble opinion is that for us, the minority, the best move is to look for approaches and collaboration on "incubating" valuable advanced features initially outside of the SQLite core, then eventually apply result solutions in our own projects and finally propose for inclusion in the core library only small key components (which lead to inefficiencies or maintenance burden when living outside). For the Stored procedures, borrowing from the SQLite design, I think that it is possible the non SQL, procedural part of the code to be translated to very high level VM with just few instructions, which uses attached :memory: DB for stack/registers/variables and (tree) table for the code. [This can be seen as step further above the approach cited by Dr. Hipp SQLs kept in table and used by executed by TCL script.] That way, at some stage, SQLite could even have several procedural dialect implementations - TSQL, MySQL, etc. :-) [1] https://github.com/kripken/sql.js/ [2] https://www.sqlite.org/vtab.html#tabfunc2 ___ 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] Stored Procedures
On 2018-05-09 03:56, Richard Hipp wrote: ... The other benefit of stored procedures is that it provides a way to code up a common operation once (correctly!) and store it in the database, rather than having multiple clients all have to work out the operating themselves (some of them perhaps incorrectly). The usual way of handling that in SQLite is to store a script in a text column someplace, then execute them as needed. SQLite began life as a TCL extension, and so naturally TCL scripts work very well for this kind of thing. To amplify the remark above, for me, the Stored procedures are mostly method to add methods :-) to the "cold" data objects encoded in tables, just like the OO style SQLite C API defines interfaces and behavior on top of "cold" C structures. That way, schemes and non-trivial calculations on top of them can be encapsulated and reused as modules (or packages as named in some design tools). Practical example of the benefit is that e.g. same complex turnover report which implementation includes bunch of intermediate calculations, could be used with same SQL call from Python desktop application, directly as Excel Data Source or on partial replica through SQL.js [1] in the browser, without coding the same thing 3 times: on Python, VBA and JS. Having said that, Let's take the survey: How many list readers really missed the Stored procedures feature? If there are enough interest, I would be happy to discuss an (possibly naive) idea (*) for implementation (of simple e.g. Sybase 12.5 level procedures) mostly in pure SQL with just tiny C runner as UDF TValued Function [2]. Kind Regards, Alek (*) On the sqlite mailing list, we see permanent conflict of interests: On the one side are 99% of the users, for which sqlite is just better (than e.g. compressed json) storage option (usually for small as size or simple as structure data). On the other, are the advanced users - these with complex applications who [especially after the great advancements from the last years (CTEs, JSON and so on)] try to encapsulate as much as possible business logic into the DB. My humble opinion is that for us, the minority, the best move is to look for approaches and collaboration on "incubating" valuable advanced features initially outside of the SQLite core, then eventually apply result solutions in our own projects and finally propose for inclusion in the core library only small key components (which lead to inefficiencies or maintenance burden when living outside). For the Stored procedures, borrowing from the SQLite design, I think that it is possible the non SQL, procedural part of the code to be translated to very high level VM with just few instructions, which uses attached :memory: DB for stack/registers/variables and (tree) table for the code. [This can be seen as step further above the approach cited by Dr. Hipp SQLs kept in table and used by executed by TCL script.] That way, at some stage, SQLite could even have several procedural dialect implementations - TSQL, MySQL, etc. :-) [1] https://github.com/kripken/sql.js/ [2] https://www.sqlite.org/vtab.html#tabfunc2 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On 5/14/18, Warren Youngwrote: > > Your benchmark doesn’t address the primary problems pointed out in the > Mozilla article: The point of my article is that many people assume *without measuring* that reading and writing directly to the filesystem will be faster than using a database. That assumption is incorrect. The Mozilla article does not state this assumption explicitly, but it is there just beneath the surface. There are indeed many issue to consider when designing a storage system for an application. My point is to show that a relational database is often a much better choice than many people (including the authors of the Mozilla article) believe. Prejudices against relational databases that were acquired due to bad experiences in the previous millennium are still quite pervasive. I aim to combat those prejudices with actual measurements. Where there any actual measurements in the Mozilla article? It is quite old and I haven't read it in years, but I don't recall there being any. -- 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
Re: [sqlite] Stored Procedures
On May 14, 2018, at 1:19 PM, Richard Hippwrote: > > On 5/14/18, Warren Young wrote: >> >> https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature > > That's an older article. These days, it is generally faster to use > SQLite than fopen(). See, for example, > https://www.sqlite.org/fasterthanfs.html Your benchmark doesn’t address the primary problems pointed out in the Mozilla article: 1. Filesystem fragmentation. Mozilla is talking about real-world pathologies, whereas if you run your paper’s benchmark on top of a modern filesystem, it won’t have much fragmentation, if any. Mozilla’s point here is that if you rewrite the data file each time, it’s always in a defragmented state with modern filesystems. 2. DB fragmentation. Your benchmark doesn’t simulate months of semi-random mixed DELETEs, INSERTs, UPDATEs, etc. You don’t have to VACUUM a flat file loaded into RAM on app start and rewritten on app exit. 3. Stale data expiration policies. When INSERT is all but free, you tend not to think as much about how much your data footprint is ballooning as when you’ve chosen a simpler persistence mechanism. 4. Journals have consequences, particularly on mobile platforms. You don’t need to pay the cost for some applications. 5. “Just index it” doesn’t work when dealing with data that doesn’t index well, like a column of URLs, particularly on the modern web where you run into many arm-length pseudorandom URLs that you will only see once. 6. A naive reliance on SQLite’s good data structures breaks down when you misdesign your schema, indexes, etc. so that you create pathological behavior. All the indexing and log(n) and btrees in the world can’t help you when your simplistic query forces a table scan. Understand, I’m not repeating the title of that article as unqualified advice. I offer it as contrast. You can’t know whether SQLite is the correct answer unless you know what your alternatives are and how they respectively stack up. The Mozilla article is reacting to the “If all you have is a hammer…” syndrome. You can just as well flip it around: “If all you have is fopen(), you won’t know when you need SQLite.” (Or PostgreSQL, or Hadoop, or…) Very little of what this Mozilla article discusses is a result of old technology. These are perennial topics. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On 5/14/18, Warren Youngwrote: > > https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature > That's an older article. These days, it is generally faster to use SQLite than fopen(). See, for example, https://www.sqlite.org/fasterthanfs.html -- 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
Re: [sqlite] Stored Procedures
On May 13, 2018, at 10:15 PM, Rowan Worthwrote: > > ...I wouldn't call the extra i/o imposed by sqlite "very very > cheap" either - it doubles writes (once to the rollback journal, once to > the DB), forces syncs, and likely results in a more seek heavy i/o pattern > (this depends a bit on schema design and whether the app requires/fully > takes advantage of relational operations). fopen() can be simpler than SQLite when you’ve got a problem that isn’t taking advantage of SQLite’s power. If you ignore the sizes of SQLite and libc, so that you only consider the size of end-user-written code, the same up-front cost gets you a fully ACID-compliant RDBMS on the one hand vs. flat C structures written linearly to disk on the other. If you can express most or all of your operations on the data in terms of linear table scans or linearly-indexed accesses — e.g. data[i] is always at sizeof(data[0]) * i bytes into the file — then the flat file is probably faster and cheaper. On top of that, the binary size of stdio is probably a lot smaller than that of SQLite. The interesting cases happen when you write your “simple” C code using fopen() and then find yourself writing O(N) algorithms where faster algorithms would work if only you had indexed access or JOIN abilities, or… Then you have to do a classic tradeoff evaluation: will the complexity of SQLite pay for itself? I recommend reading “Avoid SQLite in Your Next Firefox Feature,” an article written by the Mozilla developers after they found many features creating SQLite DBs for tiny little things better handled another way: https://wiki.mozilla.org/Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature Note that their recommendation to use plain-text JSON or lz4-compressed JSON instead of small amounts of data comes out of the fact that Mozilla had to have those mechanisms in Firefox anyway, so they’re “free,” and they already had to be heavily-optimized. It isn’t great as general-purpose advice. > My point is simply that it's unwise to think of any DB query as having "no > latency" even when dealing with an sqlite DB. Yes. ObWarStory: I once wrote an application using SQLite that tripped a 20-second watchdog timer by having an unexpectedly high amount of real-world traffic: a mere hundreds of DB updates per second. How? It was running on a system with a 5400 RPM hard disk, which translates to a theoretical maximum of 45 transactions per second: 5400 RPM / 60 seconds per minute / 2 disk rotations per transaction = 45. Since each DB update was done independently, as long as the update rate was over 45 TPS, the application would fall further and further behind real time until that watchdog timer tripped. Immediate solution: wrap a couple of key “for” loops in SQLite transactions to batch the disk updates. Future solution: stop using spinning rust. :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
> That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very > cheap" either - it doubles writes (once to the rollback journal, once to > the DB), forces syncs, and likely results in a more seek heavy i/o pattern > (this depends a bit on schema design and whether the app requires/fully > takes advantage of relational operations). Use explicit transaction maybe? 2018-05-14 6:15 GMT+02:00, Rowan Worth : > On 14 May 2018 at 01:08, Richard Damon wrote: > >> On 5/13/18 12:55 PM, Rowan Worth wrote: >> > On 9 May 2018 at 08:56, Richard Hipp wrote: >> > >> >> But with >> >> SQLite, there is no round-trip latency. A "round-trip" to and >> >> database is just a function call, and is very very cheap. >> >> >> > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes >> the >> > latency of _communication_ between the app and database in this >> statement, >> > and excludes any processing time required by the database. >> > >> > If you were to interpret "round-trip" from an app-centric perspective >> > (as >> > in "the time taken to retrieve/commit data") then the statement becomes >> > misleading because handling the data involves i/o, possibly even >> > synchronous i/o, which is not "very very cheap" by any standard I'm >> > aware >> > of :) >> > >> > -Rowan >> > Yes, if the request requires I/O, then that costs time, but then the >> operation would likely use similar I/O in whatever way the application >> needed to get that information, so that I/O shouldn't really be charged >> to the use of a database, but to the information requested. One thing to >> remember is SQLite is often compared as a better way to get information >> then using simple disk i/o, so the 'cost' of using SQLite (compared to >> the alternative) shouldn't include the base time to read the file, but >> only any extra i/o above that. >> > > That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very > cheap" either - it doubles writes (once to the rollback journal, once to > the DB), forces syncs, and likely results in a more seek heavy i/o pattern > (this depends a bit on schema design and whether the app requires/fully > takes advantage of relational operations). > > To be clear, this is not a criticism of sqlite. These costs are paid for a > reason (eg. durability) and I think sqlite does its job very efficiently. > You're also right that an app implementing similar features without sqlite > will have to pay similar costs. > > My point is simply that it's unwise to think of any DB query as having "no > latency" even when dealing with an sqlite DB. > -Rowan > ___ > 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] Stored Procedures
On 14 May 2018 at 01:08, Richard Damonwrote: > On 5/13/18 12:55 PM, Rowan Worth wrote: > > On 9 May 2018 at 08:56, Richard Hipp wrote: > > > >> But with > >> SQLite, there is no round-trip latency. A "round-trip" to and > >> database is just a function call, and is very very cheap. > >> > > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes > the > > latency of _communication_ between the app and database in this > statement, > > and excludes any processing time required by the database. > > > > If you were to interpret "round-trip" from an app-centric perspective (as > > in "the time taken to retrieve/commit data") then the statement becomes > > misleading because handling the data involves i/o, possibly even > > synchronous i/o, which is not "very very cheap" by any standard I'm aware > > of :) > > > > -Rowan > Yes, if the request requires I/O, then that costs time, but then the > operation would likely use similar I/O in whatever way the application > needed to get that information, so that I/O shouldn't really be charged > to the use of a database, but to the information requested. One thing to > remember is SQLite is often compared as a better way to get information > then using simple disk i/o, so the 'cost' of using SQLite (compared to > the alternative) shouldn't include the base time to read the file, but > only any extra i/o above that. > That's fair, but I wouldn't call the extra i/o imposed by sqlite "very very cheap" either - it doubles writes (once to the rollback journal, once to the DB), forces syncs, and likely results in a more seek heavy i/o pattern (this depends a bit on schema design and whether the app requires/fully takes advantage of relational operations). To be clear, this is not a criticism of sqlite. These costs are paid for a reason (eg. durability) and I think sqlite does its job very efficiently. You're also right that an app implementing similar features without sqlite will have to pay similar costs. My point is simply that it's unwise to think of any DB query as having "no latency" even when dealing with an sqlite DB. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On 5/13/18 12:55 PM, Rowan Worth wrote: > On 9 May 2018 at 08:56, Richard Hippwrote: > >> But with >> SQLite, there is no round-trip latency. A "round-trip" to and >> database is just a function call, and is very very cheap. >> > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the > latency of _communication_ between the app and database in this statement, > and excludes any processing time required by the database. > > If you were to interpret "round-trip" from an app-centric perspective (as > in "the time taken to retrieve/commit data") then the statement becomes > misleading because handling the data involves i/o, possibly even > synchronous i/o, which is not "very very cheap" by any standard I'm aware > of :) > > -Rowan Yes, if the request requires I/O, then that costs time, but then the operation would likely use similar I/O in whatever way the application needed to get that information, so that I/O shouldn't really be charged to the use of a database, but to the information requested. One thing to remember is SQLite is often compared as a better way to get information then using simple disk i/o, so the 'cost' of using SQLite (compared to the alternative) shouldn't include the base time to read the file, but only any extra i/o above that. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On 9 May 2018 at 08:56, Richard Hippwrote: > But with > SQLite, there is no round-trip latency. A "round-trip" to and > database is just a function call, and is very very cheap. > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the latency of _communication_ between the app and database in this statement, and excludes any processing time required by the database. If you were to interpret "round-trip" from an app-centric perspective (as in "the time taken to retrieve/commit data") then the statement becomes misleading because handling the data involves i/o, possibly even synchronous i/o, which is not "very very cheap" by any standard I'm aware of :) -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
Very useful comments in this thread. I recommend adding this to the SQLite FAQ, if it exists. -- Craig H Maynard Rhode Island, USA 401-413-2376 > Date: Tue, 8 May 2018 20:56:45 -0400 > From: Richard Hipp <d...@sqlite.org> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Stored Procedures > Message-ID: >
Re: [sqlite] Stored Procedures
> The usual way of handling that in SQLite is to store a script in a text > column someplace, then execute them as needed. Is there a simple way to do this from SQLite shell? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On 5/8/18, Jens Alfkewrote: > > >> On May 8, 2018, at 1:02 PM, Mike Clark wrote: >> >> Has there been any thought of implementing some kind of stored procedure >> feature for SQLite? > > That's more of a server thing. In an embedded database, a stored procedure > is literally a function in your programming language, which runs a SQLite > query. > Right. To amplify Jens' remark: In a traditional client/server database, there is high latency in the round-trip message from client to server and back again. To work around this, client/server systems provide stored procedures in which lots of little SQL statements can be embedded, and then all run together in a single round-trip. But with SQLite, there is no round-trip latency. A "round-trip" to and database is just a function call, and is very very cheap. This one fact removes a lot of the motivation behind having stored procedures. The other benefit of stored procedures is that it provides a way to code up a common operation once (correctly!) and store it in the database, rather than having multiple clients all have to work out the operating themselves (some of them perhaps incorrectly). The usual way of handling that in SQLite is to store a script in a text column someplace, then execute them as needed. SQLite began life as a TCL extension, and so naturally TCL scripts work very well for this kind of thing. -- 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
Re: [sqlite] Stored Procedures
> On May 8, 2018, at 1:02 PM, Mike Clarkwrote: > > Has there been any thought of implementing some kind of stored procedure > feature for SQLite? That's more of a server thing. In an embedded database, a stored procedure is literally a function in your programming language, which runs a SQLite query. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
maybe Virtual tables? can register functions of various types... mostly since the typical usage of sqlite is as a tightly coupled library, a function in your application is a 'stored procedure'. http://www.sqlite.org/c3ref/update_hook.html There are hooks which would trigger callbacks like triggers... to fake an exec you'd have to use like 'select sp_myProc(...)' instead of 'execute' On Tue, May 8, 2018 at 1:58 PM, Igor Tandetnikwrote: > On 5/8/2018 4:02 PM, Mike Clark wrote: > >> Has there been any thought of implementing some kind of stored procedure >> feature for SQLite? >> >> Or does this feature exist in some other form, with another name? >> > > Triggers are kind of like stored procedures. You can create a dedicated > view and put INSTEAD OF trigger on it. To "call" the trigger, insert a row > into the view - the trigger could use column values, available via > new.columnName, as its parameters. > -- > Igor Tandetnik > > > > ___ > 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] Stored Procedures
On 5/8/2018 4:02 PM, Mike Clark wrote: Has there been any thought of implementing some kind of stored procedure feature for SQLite? Or does this feature exist in some other form, with another name? Triggers are kind of like stored procedures. You can create a dedicated view and put INSTEAD OF trigger on it. To "call" the trigger, insert a row into the view - the trigger could use column values, available via new.columnName, as its parameters. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
You can't change the NEW "row" in trigger bodies. Since you can't make "SELECT"s (or virtual tables) this way, all your "stored procedure" can do is INSERT/UPDATE/DELETE anyways. Using coalesce(NEW.foo, "default value") works fine (and it's how you'd default "SP arguments"). Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On Thu Oct 09, 2014 at 05:38:57PM -0500, Nico Williams wrote: > I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs. > The values of the columns of the rows to be "inserted" are the > "stored procedure's" arguments. I would like to able to do this too, but INSTEAD OF INSERT on a view does not support default values for arguments the same way that BEFORE INSERT on a regular table does. What would really be nice is if one could run the following inside a BEFORE or INSTEAD OF trigger: UPDATE NEW SET NEW.name = COALESCE(NEW.name, new_value) ; Could the SQLite team perhaps comment on how difficult this would be to implement? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
I use triggers, particularly INSTEAD OF INSERT triggers on VIEWs. The values of the columns of the rows to be "inserted" are the "stored procedure's" arguments. I use WHERE clauses judiciously to make up for the lack of IFs. It works well enough. I've used this in combination with recursive triggers to implement recursive CTEs before SQLite3 added support for them. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
Thanks ALL. This has been very helpful Om Kolluri On Thu, Oct 9, 2014 at 1:19 PM, big stonewrote: > Hi, > > Here is an example of stored procedure made in Python for SQLite. > > https://pypi.python.org/pypi/sqlite_bro/0.8.7.4 > > I Hope it will help you figure out quickly if SQLite is ok enough for your > use-case. > > Sheers, > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
Hi, Here is an example of stored procedure made in Python for SQLite. https://pypi.python.org/pypi/sqlite_bro/0.8.7.4 I Hope it will help you figure out quickly if SQLite is ok enough for your use-case. Sheers, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On Thu Oct 09, 2014 at 11:29:49AM -0700, J Decker wrote: > they can be implemented through registered extensions..(well no probably > not how you're thinking).. > > but apparently can't add syntax like 'EXEC" ... but could make them be > like "select * from (stored_proc)" as an alias for "exec (stored proc)" I use BEFORE INSERT triggers that end with a SELECT RAISE(IGNORE). That way I can use the following syntax to perform multiple operations within a single statement: INSERT INTO my_function_name( arg1, arg2, arg3 ) VALUES ( val1, val2, val3 ) Obviously you can't get a result from that, but it would of course be possible to let the insert succeed or to insert a "result row" somewhere. Unfortunately CTEs don't work inside triggers so complicated logic is somewhat limited and/or must be spread across multiple triggers. > For lack of portability I haven't used them; what's a good use case > for stored procedures? I generally use them for storing entities that must be entered in many tables, where it nicely presents an API to the caller that closely maps to the single action they want to take. This provides: Simplicity: a single statement can replace many individual statements, putting more of your application inside the database and can often remove the need for an explicit transaction. Efficiency: for non-C languages there is less translation between the language/SQLite boundary. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
they can be implemented through registered extensions..(well no probably not how you're thinking).. I have a few functions to mimic MySQL functions like now(), curdate() pretty simple learned you can sqlite3_create_module() which is a virtual recordset... but apparently can't add syntax like 'EXEC" ... but could make them be like "select * from (stored_proc)" as an alias for "exec (stored proc)" but it wouldn't be SQL defining the procedure well it could be, but it would be a C routine that then issued the sql statements (maybe make loadable modules like shaders?) For lack of portability I haven't used them; what's a good use case for stored procedures? On Thu, Oct 9, 2014 at 10:12 AM, RSmithwrote: > > On 2014/10/09 19:04, Omprakash Kolluri wrote: > >> Hi, >> >> I am new to SQLite. I am working on an app that I am developing and plan >> to >> use SQLite as an embedded database. My Question - Does SQLite support >> stored procedures similar to those in MS SQL Server etc. Any suggestions >> OR >> pointers to information links woill be greatly appreciated. Thank you >> > > Hi Om, > > Not directly since it is at the API level very integrated and integratable > with any C or other common compiler languages, so doing anything procedural > is usually an easy task, and as the name implies it is "Lite" which means > some higher-CPU-Cycle-and-Memory-consumption additions are foregone > specifically so it could work well on embedded systems, as you seem to be > implementing. > > A good read in this regard would be here: > http://www.sqlite.org/whentouse.html > > Check out the recent CTE additions which does allow a level of procedural > querying here: > http://www.sqlite.org/lang_with.html#rcex2 > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
On 2014/10/09 19:04, Omprakash Kolluri wrote: Hi, I am new to SQLite. I am working on an app that I am developing and plan to use SQLite as an embedded database. My Question - Does SQLite support stored procedures similar to those in MS SQL Server etc. Any suggestions OR pointers to information links woill be greatly appreciated. Thank you Hi Om, Not directly since it is at the API level very integrated and integratable with any C or other common compiler languages, so doing anything procedural is usually an easy task, and as the name implies it is "Lite" which means some higher-CPU-Cycle-and-Memory-consumption additions are foregone specifically so it could work well on embedded systems, as you seem to be implementing. A good read in this regard would be here: http://www.sqlite.org/whentouse.html Check out the recent CTE additions which does allow a level of procedural querying here: http://www.sqlite.org/lang_with.html#rcex2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored Procedures
Views, yes. Stored Procedures, no. On Thu, Oct 9, 2014 at 1:04 PM, Omprakash Kolluriwrote: > Hi, > > I am new to SQLite. I am working on an app that I am developing and plan to > use SQLite as an embedded database. My Question - Does SQLite support > stored procedures similar to those in MS SQL Server etc. Any suggestions OR > pointers to information links woill be greatly appreciated. Thank you > > Om Kolluri > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] stored procedures implementation for SQLite
On Wednesday, January 26, 2011 5:38 PM, Chris Wolf wrote: > but if anyone is interested, I checked in my work on GitHub, including > pre-compiled > binaries for MacOS and Linux. > > http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended > Yes, very interesting! You approached the problem from a different angle to the way I did it. I did it the other way round: created a procedural grammar that could be used like a standard statement, then found a way of storing them and then a way to call them. As a result, I stored the procedures just inside sqlite_master, rather than having multiple additional tables. I simply used wrappers around the sqlite3_bind_* functions for handling parameters to the stored procedures when they were called, rather than holding the parameter names in a table. But I think your way is just as appropriate, and possibly better, in the long run. Additional to calling a stored procedure through a standalone statement ("EXEC fn(param)"), I also implemented calling through an SQL function (e.g. "SELECT exec(fn, param)"), which meant that the stored procedure could return a value that would be fed back into another statement/procedure. Of course, that led to the possibility of recursive calls and other nightmares... Anyway, I'll be interested to see how you progress. Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] stored procedures implementation for SQLite
Thanks for bringing that to my attention - that sample was left over from when I was trying to use APSW rather then sqlite2. The actual test program, sqlite-3.7.3/src/createproc_test.c, is correct. I updated the blog page to reflect the proper code. -Chris On Jan 26, 2011, at 12:55 PM, Jim Wilcoxson wrote: > It looks interesting. Should your except stmt reference apsw? -Jim > -- > HashBackup: easy onsite and offsite Unix backup > http://www.hashbackup.com > > > On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolfwrote: > >> >> >> I know this is an old thread, but shortly after I read it, I attempted to >> implement >> stored procedures in SQLite! I only did it to see if I could, not because >> I >> necessarily think it's a good idea... It's very experimental and not fully >> implemented, >> but if anyone is interested, I checked in my work on GitHub, including >> pre-compiled >> binaries for MacOS and Linux. >> >> >> http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended >> >> Regards, >> >> Chris Wolf >> >> BareFeetWare wrote: >>> On 13/11/2010, at 11:14 AM, Scott Hess wrote: >>> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare < >> list@barefeetware.com> wrote: > IMO, if you're implementing database logic (ie constraints and >> triggers) in application code, then you're reinventing the wheel, making >> your package unnecessarily complex and grossly inefficient. If you're just >> using SQLite to store your data but doing all the logic outside of SQLite, >> then you might as well just be saving your data to a CSV file or XML. See my >> previous post for reasoning behind this. From http://www.sqlite.org/about.html : > Think of SQLite not as a replacement for Oracle but as a replacement >> for fopen() >>> >>> The full paragraph from that page reads: >>> > SQLite is an embedded SQL database engine. Unlike most other SQL >> databases, SQLite does not have a separate server process. SQLite reads and >> writes directly to ordinary disk files. A complete SQL database with >> multiple tables, indices, triggers, and views, is contained in a single disk >> file. The database file format is cross-platform - you can freely copy a >> database between 32-bit and 64-bit systems or between big-endian and >> little-endian architectures. These features make SQLite a popular choice as >> an Application File Format. Think of SQLite not as a replacement for Oracle >> but as a replacement for fopen() >>> >>> So I think it's referring to how SQLite stores its data in a local file, >> rather than on a remote server with which it communicates indirectly. ie >> "SQLite does not have a separate server process". In that way, SQLite is >> like fopen rather than Oracle. The same paragraphs mentions SQLite >> "triggers, and views", freely copying a [self contained] SQLite database >> between architectures, which allude to my point about putting the logic in >> the database itself so you can move the whole database between >> architectures. >>> So, yes, you might as well just be saving your data to a CSV or XML >> file. And I'm sure if you had a package to do that, someone would be >> arguing about whether your XML should allow for embedded transforms. >>> >>> What do you gain by implementing database logic in the application layer, >> when it could be done far more efficiently and reliably in the SQL schema? >> The only thing I can think of is avoiding the (shallow) learning curve. Why >> re-invent and roll your own integrity checking etc when it's already >> available and in a way much closer to the data than your application code >> can get? >>> >>> See my previous post for the contrary argument: >>> >> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html >>> >>> Basically, database logic in the database itself is much faster, causes >> less error, optimises queries, makes the database portable between >> application environments or GUI apps. What's not to love? >>> >>> Thanks, >>> Tom >>> BareFeetWare >>> >>> -- >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] stored procedures implementation for SQLite
It looks interesting. Should your except stmt reference apsw? -Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolfwrote: > > > I know this is an old thread, but shortly after I read it, I attempted to > implement > stored procedures in SQLite! I only did it to see if I could, not because > I > necessarily think it's a good idea... It's very experimental and not fully > implemented, > but if anyone is interested, I checked in my work on GitHub, including > pre-compiled > binaries for MacOS and Linux. > > > http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended > > Regards, > > Chris Wolf > > BareFeetWare wrote: > > On 13/11/2010, at 11:14 AM, Scott Hess wrote: > > > >> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare < > list@barefeetware.com> wrote: > >>> IMO, if you're implementing database logic (ie constraints and > triggers) in application code, then you're reinventing the wheel, making > your package unnecessarily complex and grossly inefficient. If you're just > using SQLite to store your data but doing all the logic outside of SQLite, > then you might as well just be saving your data to a CSV file or XML. See my > previous post for reasoning behind this. > >> From http://www.sqlite.org/about.html : > >>> Think of SQLite not as a replacement for Oracle but as a replacement > for fopen() > > > > The full paragraph from that page reads: > > > >>> SQLite is an embedded SQL database engine. Unlike most other SQL > databases, SQLite does not have a separate server process. SQLite reads and > writes directly to ordinary disk files. A complete SQL database with > multiple tables, indices, triggers, and views, is contained in a single disk > file. The database file format is cross-platform - you can freely copy a > database between 32-bit and 64-bit systems or between big-endian and > little-endian architectures. These features make SQLite a popular choice as > an Application File Format. Think of SQLite not as a replacement for Oracle > but as a replacement for fopen() > > > > So I think it's referring to how SQLite stores its data in a local file, > rather than on a remote server with which it communicates indirectly. ie > "SQLite does not have a separate server process". In that way, SQLite is > like fopen rather than Oracle. The same paragraphs mentions SQLite > "triggers, and views", freely copying a [self contained] SQLite database > between architectures, which allude to my point about putting the logic in > the database itself so you can move the whole database between > architectures. > > > >> So, yes, you might as well just be saving your data to a CSV or XML > file. And I'm sure if you had a package to do that, someone would be > arguing about whether your XML should allow for embedded transforms. > > > > What do you gain by implementing database logic in the application layer, > when it could be done far more efficiently and reliably in the SQL schema? > The only thing I can think of is avoiding the (shallow) learning curve. Why > re-invent and roll your own integrity checking etc when it's already > available and in a way much closer to the data than your application code > can get? > > > > See my previous post for the contrary argument: > > > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html > > > > Basically, database logic in the database itself is much faster, causes > less error, optimises queries, makes the database portable between > application environments or GUI apps. What's not to love? > > > > Thanks, > > Tom > > BareFeetWare > > > > -- > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] stored procedures implementation for SQLite
I know this is an old thread, but shortly after I read it, I attempted to implement stored procedures in SQLite! I only did it to see if I could, not because I necessarily think it's a good idea... It's very experimental and not fully implemented, but if anyone is interested, I checked in my work on GitHub, including pre-compiled binaries for MacOS and Linux. http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended Regards, Chris Wolf BareFeetWare wrote: > On 13/11/2010, at 11:14 AM, Scott Hess wrote: > >> On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare>> wrote: >>> IMO, if you're implementing database logic (ie constraints and triggers) in >>> application code, then you're reinventing the wheel, making your package >>> unnecessarily complex and grossly inefficient. If you're just using SQLite >>> to store your data but doing all the logic outside of SQLite, then you >>> might as well just be saving your data to a CSV file or XML. See my >>> previous post for reasoning behind this. >> From http://www.sqlite.org/about.html : >>> Think of SQLite not as a replacement for Oracle but as a replacement for >>> fopen() > > The full paragraph from that page reads: > >>> SQLite is an embedded SQL database engine. Unlike most other SQL databases, >>> SQLite does not have a separate server process. SQLite reads and writes >>> directly to ordinary disk files. A complete SQL database with multiple >>> tables, indices, triggers, and views, is contained in a single disk file. >>> The database file format is cross-platform - you can freely copy a database >>> between 32-bit and 64-bit systems or between big-endian and little-endian >>> architectures. These features make SQLite a popular choice as an >>> Application File Format. Think of SQLite not as a replacement for Oracle >>> but as a replacement for fopen() > > So I think it's referring to how SQLite stores its data in a local file, > rather than on a remote server with which it communicates indirectly. ie > "SQLite does not have a separate server process". In that way, SQLite is like > fopen rather than Oracle. The same paragraphs mentions SQLite "triggers, and > views", freely copying a [self contained] SQLite database between > architectures, which allude to my point about putting the logic in the > database itself so you can move the whole database between architectures. > >> So, yes, you might as well just be saving your data to a CSV or XML file. >> And I'm sure if you had a package to do that, someone would be arguing about >> whether your XML should allow for embedded transforms. > > What do you gain by implementing database logic in the application layer, > when it could be done far more efficiently and reliably in the SQL schema? > The only thing I can think of is avoiding the (shallow) learning curve. Why > re-invent and roll your own integrity checking etc when it's already > available and in a way much closer to the data than your application code can > get? > > See my previous post for the contrary argument: > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html > > Basically, database logic in the database itself is much faster, causes less > error, optimises queries, makes the database portable between application > environments or GUI apps. What's not to love? > > Thanks, > Tom > BareFeetWare > > -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored procedures
"BareFeetWare" schrieb > On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote: > >> If you have code (in either environment) that is > >> looping or grabbing one result and sticking it in a > >> second query, then your approach is probably flawed. > > > > As you say: "probably" ... because "it depends" ... > > (maybe your application wants to show "details" > > in a delayed fashion - e.g. when a Node in a > > Treeview gets "expanded"). > > Good example. At the point where the user requests expanded > details (eg by clicking on a button), that obviously kicks > off application code. As far as its interaction with the > database, simplistically, it would have to do something > like get the key values in the current row, check whether > it exists in the details set, get the detail data. What I'm > saying is that this should be done as one SQL call, which > most likely doesn't require any branching logic within that call. > > So it should look something like this, with just one SQL call: > > array filteredDetailResults = execQuery( > select "Product Code", "Product Name", Quantity, Price > from "Invoice Items" > join "Invoice List" on "Invoice Items"."Invoice Number" = "Invoice List"."Invoice Number" > left join "Item List" on "Invoice Items"."Product Code" = "Item List"."Product Code" > where "GUI Selection Flag" = 1 > ) > show filteredDetailResults Nah ... come on - Joins are common things - don't know which DB-Application-developer (who worth its salt) is not making use of it (instead of falling back to loops). And in my App I would have put the above SQL into a View-Definition beforehand (that's another common thing, most DB-Engines share and which is well-portable). Ending up with something like that... SQL = "Select * From InvoiceDetailView Where CustomerID = " & _ CurrentNode.ID ' get a new Recordset-Instance, containing the set, defined above Set Rs = Cnn.OpenRecordset(SQL) ' GUI-Refresh over DataBinding against the Rs-Instance Set MyGUIWidget.DataSource = Rs -- And in case I'd have put the above (already View-based) SQL-Code into a CommandObject first, then I would only need to set the Command-Parameter (typed, and without fiddling around with String-Concats). InvoicesCmd.SetInteger !CustomerID, CurrentNode.ID Set MyGUIWidget.DataSource = InvoicesCmd.Execute Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored procedures
On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote: From: "Olaf Schmidt" Wednesday, November 10, 2010 9:07:19 AM >>> There was a somewhat similar sounding post (from BareFeetWare, >>> sent on 20.Oct to this list) who also encouraged, to include >>> "more logic" into the SQLite-Files itself, to reach more "portability". >> >> That post of mine is archived here: >> > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html > > Yep, that was it. > >> For the record, I (for BareFeetWare) was advocating implementing database >> logic in constraints and triggers, rather than in application code. I was >> not actually talking about stored procedures. > After reading your posting again, it seems I had a "wrong image" in the back > of my mind, regarding your post, sorry. > > Since you are talking about *database-logic* - our opinions do not differ > that much I'd say... > ...use "DB-internal mechanisms" only in a range, which is common among > different DB-engines (Triggers for example, to ensure referential integrity > at least)." > > And constraints, which I left out, belong into the same category, since they > are "common enough" too (among different DB-engines). > Nothing wrong with Triggers and Constraints (the "database logic", as you > call it). Things which ensure (force) consistency on your Data belong into > the DB. They are usually well portable between different Engines (good > DB-Import-Tools can recognize and understand most of these - where importing > vendor-specific Stored-Procedures into another Backend can be adventurous at > least, if we talk about larger or complex procedures). OK, we'll agree to... agree ;-) Database internal logic (integrity checks etc) are best handled in the database code (SQL), such as in constraints and triggers. I didn't know what you meant by "range". > "business-logic" or "business layer", then that's something, what in my > opinion belongs into a shareable Component (usually a Dll), written > in a normal language - but I think I already made my points, let's not repeat > them again - and it's after all only a personal opinion. I guess we both agree that for business logic (as opposed to database logic, discussed above), a procedure of some kind is obviously required. In this situation, I advocate that as much as possible is done in SQL, by using "where" clauses and manipulating sets rather than using "if/then" branching structures and iterating through loops, respectively. This is more efficient and exploits SQL data storage optimisations. In fact I think that the availability of if/then and repeat loops in a database often entices people into bad habits of procedural programming where set programming should be used instead. Do you have procedures that require branching structures? Can it not be better achieved by set logic? If there's no other way than to have branching structures, then I think I see what you're saying: that it's better to take care of the branching structures in an external code layer than in an SQL procedural language (which SQLite doesn't have, currently). I think you're saying that SQL procedure branching structures aren't standard enough across SQL flavours to make it portable. In short, if you're more likely to change the choice of SQL flavor (eg SQLite to MySQL) than application code (eg perl vs python vs PHP vs Objective-C) then you're better off putting the branching structures in the application code. Correct? This choice would boil down to the likelihood of changing each (ie SQL flavor vs application code environment). >> Why would we want to perform stored procedures? > > Ehhmm, because a "bunch of well-organized and consistent data-records" is not > (yet) an Application? ;-) LOL, yes, true. The application code has to drive the GUI or html input/output or whatever, but when the application code gets to the point of getting or setting data in the database, then it should ideally be done each time as one SQL call, rather than having a pile of application branches that makes several SQL calls. >> If you have code (in either environment) that is looping or grabbing one >> result and sticking it in a second query, then your approach is probably >> flawed. > As you say: "probably" ... because "it depends" ... (maybe your application > wants to show "details" in a delayed fashion - e.g. when a Node in a Treeview > gets "expanded"). Good example. At the point where the user requests expanded details (eg by clicking on a button), that obviously kicks off application code. As far as its interaction with the database, simplistically, it would have to do something like get the key values in the current row, check whether it exists in the details set, get the detail data. What I'm saying is that this should be done as one SQL call, which most likely doesn't require any branching logic within that call. So it should look something like this,
Re: [sqlite] Stored procedures (was: Question about SQLite features.)
On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWarewrote: > IMO, if you're implementing database logic (ie constraints and triggers) in > application code, then you're reinventing the wheel, making your package > unnecessarily complex and grossly inefficient. If you're just using SQLite > to store your data but doing all the logic outside of SQLite, then you might > as well just be saving your data to a CSV file or XML. See my previous > post for reasoning behind this. >From http://www.sqlite.org/about.html : > Think of SQLite not as a replacement for Oracle but as a replacement > for fopen() So, yes, you might as well just be saving your data to a CSV or XML file. And I'm sure if you had a package to do that, someone would be arguing about whether your XML should allow for embedded transforms. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored procedures (was: Question about SQLite features.)
"BareFeetWare" schrieb > On 12/11/2010, at 6:30 AM, Olaf Schmidt wrote: > > > "jeff archer" schrieb > >> From: "Olaf Schmidt" > >> Wednesday, November 10, 2010 9:07:19 AM > >> > >>> [Stored procedures in SQLite] > >>> > >>> IMO stored procedure-support only makes > >>> sense in "Server-Instances" which run on their own... > > > >> I disagree. The overall design and structure of applications > >> using SQLite and therefor SQLite itself would benefit > >> from SQLite supporting stored procedures. > >> This would allow all code necessary for enforcing > >> the business rules of the data to be stored in the > >> database itself. This is just a good basic design principal. > > > > There was a somewhat similar sounding post (from BareFeetWare, > > sent on 20.Oct to this list) who also encouraged, to include > > "more logic" into the SQLite-Files itself, to reach more "portability". > > That post of mine is archived here: > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html Yep, that was it. > For the record, I (for BareFeetWare) was advocating implementing > database logic in constraints and triggers, rather than in > application code. I was not actually talking about stored > procedures. > In order of preference, I would implement logic in: > > 1. Constraints > 2. Triggers > 9. Pure SQL (eg stored procedures) > 10. Application code (ie an external language that > calls SQL snippets) After reading your posting again, it seems I had a "wrong image" in the back of my mind, regarding your post, sorry. Since you are talking about *database-logic* - our opinions do not differ that much I'd say... >From my posting you were replying to ... I wrote: "...use "DB-internal mechanisms" only in a range, which is common among different DB-engines (Triggers for example, to ensure referential integrity at least)." And constraints, which I left out, belong into the same category, since they are "common enough" too (among different DB-engines). Maybe I also misunderstood Jeff Archer in this regard, but I think he meant a different thing with: "...code necessary for enforcing the business rules..." If he meant "business-logic" or "business layer", then that's something, what in my opinion belongs into a shareable Component (usually a Dll), written in a normal language - but I think I already made my points, let's not repeat them again - and it's after all only a personal opinion. > IMO, if you're implementing database logic (ie constraints > and triggers) in application code, then you're reinventing > the wheel, ... As said, that was not what I was "complaining about". [layout of a handmade "stored procedure mechanism for the poor" in SQLite ;-)] > But my question is: why? > > Why would we want to perform stored procedures? > Or why would we want to perform application code, > if you're on that side of the "war"? ;-) Ehhmm, because a "bunch of well-organized and consistent data-records" is not (yet) an Application? ;-) > In most cases, I suggest that you should be implementing your > database logic in constraints and triggers, not in procedural > code (ie not in SQL store procedures and not in application code). Here you go again... ;-) Nothing wrong with Triggers and Constraints (the "database logic", as you call it). Things which ensure (force) consistency on your Data belong into the DB. They are usually well portable between different Engines (good DB-Import-Tools can recognize and understand most of these - where importing vendor- specific Stored-Procedures into another Backend can be adventurous at least, if we talk about larger or complex procedures). > If you have code (in either environment) that is looping or > grabbing one result and sticking it in a second query, > then your approach is probably flawed. As you say: "probably" ... because "it depends" ... (maybe your application wants to show "details" in a delayed fashion - e.g. when a Node in a Treeview gets "expanded"). Anyways, hope my position is more clear to you now, and - (since you made this request) - you feel "enlightened enough" in the meantime . Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Stored procedures" performance issue
Darren Duncan wrote: > Igor Tandetnik wrote: >> Kristoffer Danielsson >>wrote: >>> When I create my own "stored procedures" using >>> sqlite3_create_function, I get horrible performance (which I >>> expected) even though the column of interest is INDEXED. >>> >>> Consider this sample (it's stupid, but it shows my problem): >>> >>> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); >>> >>> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside >>> this function, if I encounter a date greater than my birthday, then >>> I want to tell SQLite to stop searching, since the date is indexed. >> >> I'm not sure what you mean by "a date greater than my birthday". >> Presumably, your birthday comes every year, so every date is either >> your birthday or falls between two birthdays (except dates before >> the date >> you were actually born on, but those can't be greater than any of >> your >> birthday dates). > > Another meaning for birthday is the the day in history where one was > born, and there is just one of these per person. More often this is > what people are > talking about when they are dealing with dates having a year part. With this interpretation, having a function IS_MY_BIRTHDAY(IndexedDate) doesn't make much sense. In any case, one part of my response deals with this possibility. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Stored procedures" performance issue
Igor Tandetnik wrote: > Kristoffer Danielssonwrote: >> When I create my own "stored procedures" using >> sqlite3_create_function, I get horrible performance (which I >> expected) even though the column of interest is INDEXED. >> >> Consider this sample (it's stupid, but it shows my problem): >> >> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); >> >> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside >> this function, if I encounter a date greater than my birthday, then I >> want to tell SQLite to stop searching, since the date is indexed. > > I'm not sure what you mean by "a date greater than my birthday". > Presumably, your birthday comes every year, so every date is either your > birthday or falls between two birthdays (except dates before the date > you were actually born on, but those can't be greater than any of your > birthday dates). Another meaning for birthday is the the day in history where one was born, and there is just one of these per person. More often this is what people are talking about when they are dealing with dates having a year part. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Stored procedures" performance issue
On Mon, 5 Oct 2009 18:01:46 +0200, Kristoffer Danielssonwrote: > This makes sense. Though, I think the > documentation should cover this. Much of this is implicitly or explicitly covered in http://www.sqlite.org/optoverview.html . And what Scott Hess said. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Stored procedures" performance issue
Not to be flippant, but how could SQLite optimize for the output of a custom function you define outside of SQLite? It can't possibly know anything about how IS_MY_BIRTHDAY() works. Your function could return 1 for every single row, or for no rows at all, without regard to whether you're passing the function an indexed column. -scott On Mon, Oct 5, 2009 at 9:01 AM, Kristoffer Danielsson <kristoffer.daniels...@live.se> wrote: > > This makes sense. Though, I think the documentation should cover this. > > > > Thanks for your response. > >> From: paiva...@gmail.com >> Date: Mon, 5 Oct 2009 09:31:10 -0400 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] "Stored procedures" performance issue >> >> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this >> > function, if I encounter a date greater than my birthday, then I want to >> > tell SQLite to stop searching, since the date is indexed. >> > >> > Is this possible? If so, how? >> >> Even if this was possible it would be useless because you cannot say >> if SQLite traverses table via index or makes just full table scan. And >> in your particular case SQLite will see that there's no benefit in >> using any index and will just scan the whole table. >> The general advice in this case is to add column to the table which >> will store the result of your function, create an index on that >> additional column and then use that column in the query. In this case >> SQLite will understand that it's better to use index and will use it. >> >> Pavel >> >> On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson >> <kristoffer.daniels...@live.se> wrote: >> > >> > When I create my own "stored procedures" using sqlite3_create_function, I >> > get horrible performance (which I expected) even though the column of >> > interest is INDEXED. >> > >> > >> > Consider this sample (it's stupid, but it shows my problem): >> > >> > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); >> > >> > >> > >> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this >> > function, if I encounter a date greater than my birthday, then I want to >> > tell SQLite to stop searching, since the date is indexed. >> > >> > >> > >> > Is this possible? If so, how? >> > >> > >> > Thanks. >> > >> > _ >> > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och >> > Digg när de skickar e-post till dig. >> > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _ > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg > när de skickar e-post till dig. > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Stored procedures" performance issue
This makes sense. Though, I think the documentation should cover this. Thanks for your response. > From: paiva...@gmail.com > Date: Mon, 5 Oct 2009 09:31:10 -0400 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] "Stored procedures" performance issue > > > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this > > function, if I encounter a date greater than my birthday, then I want to > > tell SQLite to stop searching, since the date is indexed. > > > > Is this possible? If so, how? > > Even if this was possible it would be useless because you cannot say > if SQLite traverses table via index or makes just full table scan. And > in your particular case SQLite will see that there's no benefit in > using any index and will just scan the whole table. > The general advice in this case is to add column to the table which > will store the result of your function, create an index on that > additional column and then use that column in the query. In this case > SQLite will understand that it's better to use index and will use it. > > Pavel > > On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson > <kristoffer.daniels...@live.se> wrote: > > > > When I create my own "stored procedures" using sqlite3_create_function, I > > get horrible performance (which I expected) even though the column of > > interest is INDEXED. > > > > > > Consider this sample (it's stupid, but it shows my problem): > > > > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); > > > > > > > > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this > > function, if I encounter a date greater than my birthday, then I want to > > tell SQLite to stop searching, since the date is indexed. > > > > > > > > Is this possible? If so, how? > > > > > > Thanks. > > > > _ > > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg > > när de skickar e-post till dig. > > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när de skickar e-post till dig. http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Stored procedures" performance issue
> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this > function, if I encounter a date greater than my birthday, then I want to tell > SQLite to stop searching, since the date is indexed. > > Is this possible? If so, how? Even if this was possible it would be useless because you cannot say if SQLite traverses table via index or makes just full table scan. And in your particular case SQLite will see that there's no benefit in using any index and will just scan the whole table. The general advice in this case is to add column to the table which will store the result of your function, create an index on that additional column and then use that column in the query. In this case SQLite will understand that it's better to use index and will use it. Pavel On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielssonwrote: > > When I create my own "stored procedures" using sqlite3_create_function, I get > horrible performance (which I expected) even though the column of interest is > INDEXED. > > > Consider this sample (it's stupid, but it shows my problem): > > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); > > > > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this > function, if I encounter a date greater than my birthday, then I want to tell > SQLite to stop searching, since the date is indexed. > > > > Is this possible? If so, how? > > > Thanks. > > _ > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg > när de skickar e-post till dig. > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Stored procedures
Sqlite supports user loaded functions and triggers but not stored procedures. You can probably achieve the functionality you need with functions and/or triggers. VIGNY Cecilia wrote: Hi, Does SQLite supports stored procedures ? If it does, what is the appropriated syntax ? Thanks. Ce message est protégé par les règles relatives au secret des correspondances. Il est donc établi à destination exclusive de son destinataire. Celui-ci peut donc contenir des informations confidentielles. La divulgation de ces informations est à ce titre rigoureusement interdite. Si vous avez reçu ce message par erreur, merci de le renvoyer à l'expéditeur dont l'adresse e-mail figure ci-dessus et de détruire le message ainsi que toute pièce jointe. This message is protected by the secrecy of correspondence rules. Therefore, this message is intended solely for the attention of the addressee. This message may contain privileged or confidential information, as such the disclosure of these informations is strictly forbidden. If, by mistake, you have received this message, please return this message to the addressser whose e-mail address is written above and destroy this message and all files attached. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Stored procedures in triggers
>Would it be useful to have recursive DELETE triggers >even without recursive INSERT or UPDATE triggers? Recursive DELETE triggers would certainly be usefull and have in fact already been asked for on this list occasionally. They would allow to move referential integrity of hierarchical data out of the application and into the database. My vote is a strong "Yes!" in favour of recursive DELETE triggers! Ralf Btw: Does the SQL standard say anything about recursive triggers? Is there a reserved word to make a trigger recursive or not? Could there be an (application defined) limit on INSERT and UPDATE recursions for to solve the endless loop / stack/memory overflow problem?
RE: [sqlite] Stored procedures in triggers
> > Thoughts? Would making recursive triggers an error rather > than just silently ignoring them break anybody's code? even if it does, it should. otherwise, people may assume that the functionality exists,and rely on it. > I'm also looking at making DELETE triggers recursive. I can > do that because recursive DELETE triggers are guaranteed to > terminate (you will eventually run out of rows to delete.) > But INSERT or UPDATE triggers might go on forever. There are > also technical issues that make recursive INSERT and UPDATE > triggers more difficult so that I would prefer to delay > implementing them. > > Comments? Would it be useful to have recursive DELETE > triggers even without recursive INSERT or UPDATE triggers? not much IMHO
Re: [sqlite] Stored procedures in triggers
"Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > Cascading triggers are supported [in SQLite], recursive triggers are not. > That is, if you have an insert trigger on table A which, say, inserts > into table B, and there's an insert trigger on table B, it will run. But > if this latter trigger turns around and inserts into table A, the A > trigger won't run again. > I've been looking into this. Right now, if you have a recursive trigger, it just doesn't run. There is no error. I'm thinking of perhaps changing that so that you do at least get an error message. Thoughts? Would making recursive triggers an error rather than just silently ignoring them break anybody's code? I'm also looking at making DELETE triggers recursive. I can do that because recursive DELETE triggers are guaranteed to terminate (you will eventually run out of rows to delete.) But INSERT or UPDATE triggers might go on forever. There are also technical issues that make recursive INSERT and UPDATE triggers more difficult so that I would prefer to delay implementing them. Comments? Would it be useful to have recursive DELETE triggers even without recursive INSERT or UPDATE triggers? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Stored procedures in triggers
See the email of Igor Tandetnik from 18-Dec-2005: "Vishal Kashyap" wrote > Is their any way we can write simple stored procedures or functions > in sqlite. If yes please do guide me I need this functionality in one > of my open source project. Not in the usual sense, meaning some language that gets stored in the database itself together with the data. The only thing that comes somewhat close is a trigger. It is possible to create a poor man's stored procedure like this: create table sp_dosomething (param1 int, param2 char); create trigger sp_dosomething_impl instead of insert on sp_dosomething begin -- one or more sql statements possibly referring to -- new.param1 and new.param2 end; -- To invoke: insert into sp_dosomething values(1, 'hello'); Note that triggers are rather limited in what they can do. They are just a bunch of SQL statements, there is no control flow (loops, if then else, goto) beyond what little you can implement in pure SQL. They cannot return values, except indirectly by inserting or updating some table. SQLite does not support cascading triggers, so if your "stored procedure" manipulates some table to which regular triggers are attached (perhaps ensuring data integrity), those triggers won't run. SQLite supports custom functions - see sqlite3_create_function[16]. You write them in C (or any other language that has bindings to SQLite API) and you have to install them every time you open a DB handle with sqlite3_open, before you can refer to them in your SQL statements. They are not stored in the database file itself. Finally, SQLite prepared statements (sqlite_prepare) can be thought of as simple stored procedures defined in your program. Similar to custom functions, you can prepare a statement right after opening the database, then keep it around. Igor Tandetnik Ran On 3/24/06, Chethana, Rao (IE10) <[EMAIL PROTECTED]> wrote: > > Hi, > > Can you tell me how to create a stored procedure in an sqlite3 database > and use the same in a trigger? Please provide an example (as complete as > possible). In the stored procedure I need to execute few queries on some > tables. Can you tell me how to do that also? > > Any help is deeply appreciated. > > Best Regards, > > Chethana >
Re: [sqlite] stored procedures
One reason might be that SQLite does not usually include anything that can be just as well implemented externally. I could be missing something, because I'm not really sure of the advantages of stored procedures, but it seems to me that an implementation could be created without modifying SQLite itself. --- Lloyd Dupont <[EMAIL PROTECTED]> wrote: > I'm not sure it's a real justification > I believe that stored procedure are more than convenience to do avoid > multiple client-serveur call > > For exemple lately I wanted to created to related table (kind of > MASTER_TABLE, PROPERTY_TABLE) > property should be destroyed/created with master record. > I used trigger for that. > But I read once that TRIGGER are evil. And I do feel it, when I write my > INSERT in MASTER_TABLE, it's quite easy to forget that that a record is > created as well in PROPERTY_TABLE (with some link ID updated in both table). > Whereas a stored Procedure would have enable me to clearly look at the whole > procedure as one single operation. > > - Original Message - > From: "Jay Sprenkle" <[EMAIL PROTECTED]> > To: <sqlite-users@sqlite.org> > Sent: Wednesday, June 15, 2005 12:40 AM > Subject: Re: [sqlite] stored procedures > > > >> BTW I wonder why SQLite doesn't support Stored Procedure. > >> Through Trigger it does already support some similar functionality. > >> Certainly, while writing trigger code it won't have been that much code > >> to > >> write stored procedure code as well. > >> That kind of puzzle me.. is there any rationale for the lack of stored > >> procedure? > > > > Stored procedures are code run by the server. There isn't a server with > > SQLite. > > SQLite is a database file structure that uses SQL to access it. > > > > __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail