Re: [sqlite] request for additions to sqlite 3.xx
[13-05-2005 9:31, Chmielewski Andrzej escreveu] comm Wiadomosc ta jest przeznaczona jedynie dla osoby lub podmiotu, ktory jest jej adresatem i moze zawierac poufne i/lub uprzywilejowane informacje. Zakazane jest jakiekolwiek przegladanie, przesylanie, rozpowszechnianie lub inne wykorzystanie tych informacji lub podjecie jakichkolwiek dzialan odnosnie tych informacji przez osoby lub podmioty inne niz zamierzony adresat. Jezeli Panstwo otrzymali przez pomylke te informacje prosimy o poinformowanie o tym nadawcy i usuniecie tej wiadomosci z wszelkich komputerow. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. This is the kind of messages that get into my nerves... Sorry, but I didn't agree on your terms before you sent me the message so (even if it wasn't sent over to a public mailing list) I reserve the right to do whatever I want with it. If you don't agree, then don't send it on the first place. And by the way, a simple link to a public server where the terms are should be more than enough (as it doesn't serve nothing anyway). g
RE: [sqlite] request for additions to sqlite 3.xx
comm Wiadomosc ta jest przeznaczona jedynie dla osoby lub podmiotu, ktory jest jej adresatem i moze zawierac poufne i/lub uprzywilejowane informacje. Zakazane jest jakiekolwiek przegladanie, przesylanie, rozpowszechnianie lub inne wykorzystanie tych informacji lub podjecie jakichkolwiek dzialan odnosnie tych informacji przez osoby lub podmioty inne niz zamierzony adresat. Jezeli Panstwo otrzymali przez pomylke te informacje prosimy o poinformowanie o tym nadawcy i usuniecie tej wiadomosci z wszelkich komputerow. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Re: [sqlite] request for additions to sqlite 3.xx
On Mon, 9 May 2005 22:28:34 -0400, "basil thomas" <[EMAIL PROTECTED]> wrote: > database. SQLite is definitely extremely fast and we have no plans of > abandoning due to lack of stored procedures but definitely would be nice to > have... you could fake it with a view and a instead of trigger /* setup */ create table param_codes (code integer primary key, description varchar (10)); insert into param_codes values (1, 'atest'); /* fake a stored procedure */ create view my_procedure as select 1 as param1,'' as param2; create trigger my_procedure_body instead of insert on my_procedure for each row begin update param_codes set description = new.param2 where code = new.param1; end; /* test */ select * from param_codes; insert into my_procedure values (1, 'atest2'); select * from param_codes; klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] request for additions to sqlite 3.xx
> What's the objection to reading your SQL source out of the database and > preparing it at program startup? I have no objection to reading the sql on startup as that is what we are currently doing. I just want all data access code inside of the database instead of my source code. Does anyone else think that SQLite should have stored procedures?? My assumption is that if you are creating views and triggers, why not also create stored procedures aswell. Nearly every other database that has triggers (embedded library or not) has stored procedures aswell. Anyway, as I have pointed out before, I obviously can do without as we are simulating stored procedures by puting them in resource files from one large xml file that can be edited external to the source code. We do not want out data access layer tied too much to SQLite and would prefer to have all access code in the database itself so our data access layer can accomadate (lesser obviously) databases on the market with little change to the code. We can usually work with SQL Server code independant of the application by using stored procedures as our sole access point into he database. SQLite is definitely extremely fast and we have no plans of abandoning due to lack of stored procedures but definitely would be nice to have...
Re: [sqlite] request for additions to sqlite 3.xx
basil thomas wrote: As for storing queries, I'm not sure how useful this feature is given that the database engine itself is part of your program. For simple queries your best bet is a static sqlite3_stmt object, which you prepare once at program initiation and refer back to each time it is needed. Just remember to reset the statement after each query execution. For more complex logic you can couple this trick with functions that choose which query to execute. Clay Dowling Yes I am already creating static statement objects at program init time. That is not the problem. I just do not want the SQL source in my source code as i would like to control the SQL source seperately inside the database I can use all of the c++ functions and do whatever I want. That is not my point either. I am just asking for a standard way to have stored procedures that do exactly the the same simple queries as I am creating now but just implemented as another database object like triggers. The triggers in SQLite are simpled stored procedures fired by SQLite. I just want my own stored procedures fired by my user code. This is a just a simple request that I think others would find usefull. I am already implementing all the other features in my code that simulates a single process/multi-threaded server database. We are using SQLite just as a storage engine as we are fully implementing an xml database on top of SQLite. What's the objection to reading your SQL source out of the database and preparing it at program startup?
Re: [sqlite] request for additions to sqlite 3.xx
> As for storing queries, I'm not sure how useful this feature is given > that the database engine itself is part of your program. For simple > queries your best bet is a static sqlite3_stmt object, which you prepare > once at program initiation and refer back to each time it is needed. > Just remember to reset the statement after each query execution. For > more complex logic you can couple this trick with functions that choose > which query to execute. > > Clay Dowling Yes I am already creating static statement objects at program init time. That is not the problem. I just do not want the SQL source in my source code as i would like to control the SQL source seperately inside the database I can use all of the c++ functions and do whatever I want. That is not my point either. I am just asking for a standard way to have stored procedures that do exactly the the same simple queries as I am creating now but just implemented as another database object like triggers. The triggers in SQLite are simpled stored procedures fired by SQLite. I just want my own stored procedures fired by my user code. This is a just a simple request that I think others would find usefull. I am already implementing all the other features in my code that simulates a single process/multi-threaded server database. We are using SQLite just as a storage engine as we are fully implementing an xml database on top of SQLite.
Re: [sqlite] request for additions to sqlite 3.xx
On May 7, 2005, at 4:13 PM, Darren Duncan wrote: That said, if this were a large database engine, there wouldn't be any excuse to leave this feature out of the core. Yes, that is a good point.
Re: [sqlite] request for additions to sqlite 3.xx
At 12:02 PM -0700 5/7/05, Will Leshner wrote: I really think locking rows with triggers is the way to go. In fact, even if it were built into SQLite itself, I would think the implementation would be something like a trigger. To be more specific, triggers basically let you insert your own hooks right into the VM, so you are guaranteed that the execution of an UPDATE or DELETE that fails because of a locked record will unwind itself correctly. The other cool thing about using triggers to do record locking is that there is zero overhead if there are no locks. I can see some advantages to this, which is basically making the application implement the locks, while having this done in an elegant fashion. SQLite itself is kept a lot simpler. Also, each application can easily customize the granularity of the locks and other related details, so that they work best for the situation; eg, one can simply mark a parent record as locked and the trigger will enforce that its children are also locked at the same time. That said, if this were a large database engine, there wouldn't be any excuse to leave this feature out of the core. -- Darren Duncan
Re: [sqlite] request for additions to sqlite 3.xx
basil thomas wrote: 2) stored procedures - I know the response will probably be a flat at "NO because SQLite is not a client/server database and if you want that feature use xxx instead". I just would like to save my queries inside the database without having to recompile them again. Very simple as we seem to have triggers already and they look just like the stored procedures that I would like to create. No flow control and access to passed in variables only. You might want to check out http://www.sqlite.org/capi3.html to see the features already built in for this (section 2.3, "User-defined functions"). This takes care of ugly calculations at any rate. As for storing queries, I'm not sure how useful this feature is given that the database engine itself is part of your program. For simple queries your best bet is a static sqlite3_stmt object, which you prepare once at program initiation and refer back to each time it is needed. Just remember to reset the statement after each query execution. For more complex logic you can couple this trick with functions that choose which query to execute. Clay Dowling -- http://www.lazarusid.com/notes/ Lazarus Notes Articles and Commentary on Web Development
Re: [sqlite] request for additions to sqlite 3.xx
On May 7, 2005, at 11:50 AM, Darren Duncan wrote: However, row-level locking is something else; I see this as being too complex to implement in the 3.x series, if SQLite ever implements it; so no vote for this. In fact, the existing support to lock the whole database that 3.x supports now, which allows for multiple concurrent readers, is quite powerful on its own considering the simplicity. That said, if row-level locking is supported later, it should probably be a compile-time option, since it would slow things down for people that don't use it from the overhead. I really think locking rows with triggers is the way to go. In fact, even if it were built into SQLite itself, I would think the implementation would be something like a trigger. To be more specific, triggers basically let you insert your own hooks right into the VM, so you are guaranteed that the execution of an UPDATE or DELETE that fails because of a locked record will unwind itself correctly. The other cool thing about using triggers to do record locking is that there is zero overhead if there are no locks.
Re: [sqlite] request for additions to sqlite 3.xx
I can see stored procedures functions (simple ones at least) as being a natural addition considering the existing support for triggers and views, and I vote for this too. However, row-level locking is something else; I see this as being too complex to implement in the 3.x series, if SQLite ever implements it; so no vote for this. In fact, the existing support to lock the whole database that 3.x supports now, which allows for multiple concurrent readers, is quite powerful on its own considering the simplicity. That said, if row-level locking is supported later, it should probably be a compile-time option, since it would slow things down for people that don't use it from the overhead. -- Darren Duncan At 10:42 AM -0400 5/7/05, basil thomas wrote: We have not currently upgraded to version 3.xx even though there has been many enhancements that have made it an even better SQL library. There are 2 features that I would suggest be added in the near future: 1) row locking - this seems to be a touchy subject as any mention of explicit row locking will take SQLite from a standalone/embedded library to a full-blown client/server type architecture. I do not know all the internals of SQLite locking except that locking the database is severly limiting concurrency if running multiple threads. I have read the concurrency document and would really like to know how hard it would be to add row/page locking to SQLite?? 2) stored procedures - I know the response will probably be a flat at "NO because SQLite is not a client/server database and if you want that feature use xxx instead". I just would like to save my queries inside the database without having to recompile them again. Very simple as we seem to have triggers already and they look just like the stored procedures that I would like to create. No flow control and access to passed in variables only.
Re: [sqlite] request for additions to sqlite 3.xx
On May 7, 2005, at 7:42 AM, basil thomas wrote: 1) row locking - this seems to be a touchy subject as any mention of explicit row locking will take SQLite from a standalone/embedded library to a full-blown client/server type architecture. I do not know all the internals of SQLite locking except that locking the database is severly limiting concurrency if running multiple threads. I have read the concurrency document and would really like to know how hard it would be to add row/page locking to SQLite?? It is possible to lock database records with triggers. Just make triggers for update and delete and raise an error if the record being edited is the record that is locked by the trigger.
