Re: [sqlite] INTEGER PRIMARY and Triggers
On Nov 16, 2008, at 2:14 AM, [EMAIL PROTECTED] wrote: > Do you mind specifying what are the "odd things" that happen? > > I have used TRIGGERs on tables with INTEGER PRIMARY KEY and have had > no odd things happen at all. Right. I should have put more details up originally. Actually, the problems have all been related to the Objective-C / Cocoa interface that I have been working on. It is a small project with a small database consisting of 4 tables, one of which is a startup table with two integers. Those two integers correspond to primary keys of two other tables with the AUTOINCREMENT, precisely because I do not want recycling of id numbers. Originally, I had written Obj-C code to update the startup table integers whenever an UPDATE or INSERT was performed on the two tables with the AUTOINCREMENT id numbers, then I thought it would be much more efficient to have the triggers do the job, and result in less code in the actual front end. Well, all was working fine until I installed the triggers. My Obj-C code did indeed update the startup table, and then correctly read those integers to present the proper data to the GUI. After installing the triggers, and commenting out the relevant Obj-C code, it did not matter whether I did the INSERT or UPDATE via my GUI or via the command line; the startup table was consistently correct (i.e., the triggers were working), but my Obj-C code kept returning the integer 2, even when the startup table contained a 17. It was even more maddening when the same behavior was exhibited after dropping the triggers and un-commenting the Obj-C code. I ended up having to erase the database file completely and write a new one to get everything to function correctly as the original had. Yes, I am a beginner with both Obj-C and SQLite (6 months or so with both), so it is certainly possible that there is something involved with the timing of my commands within my Cocoa project that is doing this. The weird thing is that even when I shut down the sample app (running inside of XCode), and then re-launched it to read the tables fresh on startup, I would still consistently get a 2, regardless of what integer was actually residing in the startup table. In case you are wondering, I use 3 different Macs, depending on where I am during the month, and I want the startup table so that the database file can move from machine to machine, and my app will always have the same data and setup. The file will eventually reside on a flash drive, although for development purposes, right now I am keeping it on my desktop(s). Thank you again. > Craig Smith [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rowid for my first 2 rows is always the Same.
"LordEricO" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > For some reason, the rowid of the first the 2 rows in my table always > matches. Whether it's one and one, two and two, 20 and 20 etc... No > matter if I delete one of the rows. > Do you know why that would be? With all due respect, I find this hard to believe. As far as I can tell, this is a physical impossibility in SQLite's data structures. Can you show a small sample that demonstrates the problem? How do you create the table, insert rows, and read them back in a way that suggests to you there are duplicate rowids? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
Hi Merv Thanks for your sharing If you can share it through this dissussion forum then that will be counted as a great work from you. Because those who are using Sqlite with windows and visualstudio 6 will face the problem of reporting. They have to depend up on Crystall Reports or some other tools which will require ODBC/ ADO. I have tested OOFile but was not worth enough. So please share what you have with all of us. I am tired of searching such a thing. Rajesh Nair - Original Message - From: "Merv" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Saturday, November 15, 2008 9:03 PM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer >I have a program for Windows that may help you. It is not open-source > but it is free to use and distribute. > It has a simple report function that I am willing to modify based on > input from users. > We can continue to communicate this way or you can contact me directly > at the above address or call 705-646-1076. > Have a super one! > Merv > > Rajesh Nair wrote: >> Thank You.. >> >> Java report builders are available on sourceforge.net and i have gone >> through it long before. What I am looking is the one which any one can >> use >> with VisualStudio 6 or ealier products. It should be handy. Not a >> complecated one. Simple like "just design and port the report file and >> then >> just view and print". Do you have any link which could help all of us. >> >> >> >> - Original Message - >> From: "Timothy A. Sawyer" <[EMAIL PROTECTED]> >> To: "General Discussion of SQLite Database" >> Sent: Saturday, November 15, 2008 10:24 AM >> Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer >> >> >> >>> If you want something that is Windows based like you describe, why not >>> use >>> Java to build a Swing GUI? Delphi and Pascal are a little antiquated so >>> if >>> you need to revise you may have a problem finding a resource. Also >>> consider the platform independence if that is a concern >>> Timothy A. Sawyer, CISSP >>> Managing Director >>> MBD Solutions >>> Phone: (603) 546-7132 >>> Web: http://www.mybowlingdiary.com >>> Email: [EMAIL PROTECTED] >>> >>> >>> -Original Message- >>> From: "Rajesh Nair" <[EMAIL PROTECTED]> >>> >>> Date: Sat, 15 Nov 2008 09:23:46 >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer >>> >>> >>> Thanks >>> >>> So I can't use it... Ok ... But I got some source code of FastReport ( >>> some >>> ealier version ) from the internet and it is in Dephi/Pascal. I don't >>> know >>> both of the tools. Can you use the same code to build a dll which can be >>> used with other windows programing languages? ( So that no one can raise >>> their finger aganist me or YOU ). >>> >>> I don't want a very efficient-high-end report designer like FastReport. >>> My >>> clients require very simple reports, which does not contain much >>> caculations >>> etc If you can help me please. It won't be just for me. Those who >>> are >>> using SqLite with windows will be happy with a simple report deisgner >>> and >>> viewer. Also my company is not willing to puchase any product for just >>> reporting, since our programs are supplied freely along with some of our >>> products. So an extra cost is not feasible. >>> >>> A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS >>> >>> >>> - Original Message - >>> From: "Cariotoglou Mike" <[EMAIL PROTECTED]> >>> To: "General Discussion of SQLite Database" >>> Sent: Friday, November 14, 2008 7:14 PM >>> Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer >>> >>> >>> I am the author of sqlite3Explorer. Sorry I did not answer you previous post, reason is I did not motice it... :( I don't think what you want is feasible. the reason is this: the Dll expects a "dataset" which sqlite3Explorer builds internally, and the report engine takes it from there (Design, run, export etc). the dll itself does not contain data retrieval code, and relies on the host for this. this is ok, but the catch is here: the dataset format that the dll understands is a proprietary format developed by and only used by the company I work for. the dll engine is one the prime design/execute engines for our commercial projects. thus I cannot open-source it. and, since the dataset format in question cannot be produced without having access to the proprietary libraries we use here, what you want cannot be done. sorry... > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair > Sent: Friday, November 14, 2008 2:12 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer > > Hi all > > How can I use the mkFrxEngine.DLL supplied along with the > Sqlite3Explorer. > The dll is
[sqlite] rowid for my first 2 rows is always the Same.
For some reason, the rowid of the first the 2 rows in my table always matches. Whether it's one and one, two and two, 20 and 20 etc... No matter if I delete one of the rows. Do you know why that would be? Eric -- View this message in context: http://www.nabble.com/rowid-for-my-first-2-rows-is-always-the-Same.-tp20522218p20522218.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which TCL distrabution for Windows XP for running SQLite tests?
Brown, Daniel wrote: > Good afternoon list, > > If I would like to use the TCL based tests on my version of SQLite on > Windows XP what would be the recommend TCL distribution to use? Would > ActiveTCL (http://tinyurl.com/5wl6uv) be compatible with the SQLite TCL > scripts on Windows XP? > > Cheers, > > Daniel Brown | Software Engineer > "The best laid schemes o' mice an' men, gang aft agley" > That'd definitely be my recommendation. I use ActiveTcl for all of my Tcl development, which happens to be Windows XP based and often incorporates SQLite. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Which TCL distrabution for Windows XP for running SQLite tests?
Good afternoon list, If I would like to use the TCL based tests on my version of SQLite on Windows XP what would be the recommend TCL distribution to use? Would ActiveTCL (http://tinyurl.com/5wl6uv) be compatible with the SQLite TCL scripts on Windows XP? Cheers, Daniel Brown | Software Engineer "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Jacobi wrote: > (1) Just as an OS abstraction layer is in place for I/O, wouldn't it > be possible to use an OS abstraction layer for L14N? SQLite allows multiple registrations of the same function if they take different number of arguments. Consequently the SQLite core implements upper/lower taking one arg and the ICU extension implements upper/lower taking two args with the second arg being the locale. Why don't you write an extension that does the mapping into the Win32 api and then contribute it - http://sqlite.org/contrib - if it is small and works well then it could become part of the core for Windows. > (2) I'm under the impression, that the problematic cases ICU in SQLite does a lot more than just locale specific upper/lower casing. It also does locale specific sorting (which can't be done with a trivial lookup table) and LIKE/regular expressions. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkfPHYACgkQmOOfHg372QQ25wCgzoswPyHJbHKdw+AZeX/6MV3g XWMAn10xjkcf3NjZWvr+e+BOhyLUErzO =62c0 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRIGGER to disallow updates on condition
I want to create a TRIGGER (or some kind of constraint) that would croak when an update is attempted without a WHERE clause (to prevent accidental updates of the entire table). If a conscious update of the entire table is requested then one could just add a WHERE 0=0 kind of a dummy clause, but if there is no WHERE clause, the UPDATE attempt should just fail. Any hints would be appreciated. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and triggers
On 11/15/08, Craig Smith <[EMAIL PROTECTED]> wrote: > I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id > fields in my SQLite projects, yet I would like to try some triggers as > well. Of course, every time I add a trigger that accesses a table > with these types of id fields, all sorts of odd things happen. Do you mind specifying what are the "odd things" that happen? I have used TRIGGERs on tables with INTEGER PRIMARY KEY and have had no odd things happen at all. > I > read in the documentation about simply using PRIMARY KEY instead of > INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability. > > Does anyone have a solution that both provides AUTOINCREMENT (or a > reasonable facsimile) to a PRIMARY KEY field and allows the usage of > triggers on those tables? INTEGER PRIMARY KEY provides the autoincrement facily... it is just an alias for the internal ROWID which can also be accessed via the keywords ROWID, _ROWID_ or OID. On the other hand, if you add the keyword AUTOINCREMENT to INTEGER PRIMARY KEY, a subtle different takes place -- with just INTEGER PRIMARY KEY, if a row is deleted, its PRIMARY KEY can be reused. On the other hand, with the AUTOINCREMENT keyword, every new PRIMARY KEY is Max(PRIMARY KEY) + 1. Other than that, there is no interference with TRIGGER behaviors. If you have experienced any, why don't you detail them here. > > Thank you very much. > > Craig Smith > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INTEGER PRIMARY KEY and triggers
I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id fields in my SQLite projects, yet I would like to try some triggers as well. Of course, every time I add a trigger that accesses a table with these types of id fields, all sorts of odd things happen. I read in the documentation about simply using PRIMARY KEY instead of INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability. Does anyone have a solution that both provides AUTOINCREMENT (or a reasonable facsimile) to a PRIMARY KEY field and allows the usage of triggers on those tables? Thank you very much. Craig Smith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
I have a program for Windows that may help you. It is not open-source but it is free to use and distribute. It has a simple report function that I am willing to modify based on input from users. We can continue to communicate this way or you can contact me directly at the above address or call 705-646-1076. Have a super one! Merv Rajesh Nair wrote: > Thank You.. > > Java report builders are available on sourceforge.net and i have gone > through it long before. What I am looking is the one which any one can use > with VisualStudio 6 or ealier products. It should be handy. Not a > complecated one. Simple like "just design and port the report file and then > just view and print". Do you have any link which could help all of us. > > > > - Original Message - > From: "Timothy A. Sawyer" <[EMAIL PROTECTED]> > To: "General Discussion of SQLite Database" > Sent: Saturday, November 15, 2008 10:24 AM > Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer > > > >> If you want something that is Windows based like you describe, why not use >> Java to build a Swing GUI? Delphi and Pascal are a little antiquated so if >> you need to revise you may have a problem finding a resource. Also >> consider the platform independence if that is a concern >> Timothy A. Sawyer, CISSP >> Managing Director >> MBD Solutions >> Phone: (603) 546-7132 >> Web: http://www.mybowlingdiary.com >> Email: [EMAIL PROTECTED] >> >> >> -Original Message- >> From: "Rajesh Nair" <[EMAIL PROTECTED]> >> >> Date: Sat, 15 Nov 2008 09:23:46 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer >> >> >> Thanks >> >> So I can't use it... Ok ... But I got some source code of FastReport ( >> some >> ealier version ) from the internet and it is in Dephi/Pascal. I don't know >> both of the tools. Can you use the same code to build a dll which can be >> used with other windows programing languages? ( So that no one can raise >> their finger aganist me or YOU ). >> >> I don't want a very efficient-high-end report designer like FastReport. My >> clients require very simple reports, which does not contain much >> caculations >> etc If you can help me please. It won't be just for me. Those who are >> using SqLite with windows will be happy with a simple report deisgner and >> viewer. Also my company is not willing to puchase any product for just >> reporting, since our programs are supplied freely along with some of our >> products. So an extra cost is not feasible. >> >> A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS >> >> >> - Original Message - >> From: "Cariotoglou Mike" <[EMAIL PROTECTED]> >> To: "General Discussion of SQLite Database" >> Sent: Friday, November 14, 2008 7:14 PM >> Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer >> >> >> >>> I am the author of sqlite3Explorer. Sorry I did not answer you previous >>> post, reason is I did not motice it... :( >>> >>> I don't think what you want is feasible. the reason is this: the Dll >>> expects a "dataset" which sqlite3Explorer builds internally, and the >>> report engine takes it from there (Design, run, export etc). the dll >>> itself does not contain data retrieval code, and relies on the host for >>> this. >>> >>> this is ok, but the catch is here: the dataset format that the dll >>> understands is a proprietary format developed by and only used by the >>> company I work for. the dll engine is one the prime design/execute >>> engines for our commercial projects. >>> thus I cannot open-source it. and, since the dataset format in question >>> cannot be produced without having access to the proprietary libraries >>> we use here, what you want cannot be done. >>> >>> sorry... >>> >>> -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair Sent: Friday, November 14, 2008 2:12 PM To: General Discussion of SQLite Database Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer Hi all How can I use the mkFrxEngine.DLL supplied along with the Sqlite3Explorer. The dll is a report designer and viewer. Please help me to use that dll if any one knows. I asked the same before 10-14 days ago, but didn't get any reply. If even the author of the same is reading this please help me. I want to use it as a report viewer for my sqlite3 database. -- Regards Rajesh Nair ___ 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
Re: [sqlite] bug? like-search with german umlaut is case-sensitive, should not be
I'm aware that ICU is able to provide a very general solution, but I'm wondering about two other options: (1) Just as an OS abstraction layer is in place for I/O, wouldn't it be possible to use an OS abstraction layer for L14N? So that for example uppercasing is forwarded to LCMapString(LCMAP_UPPERCASE) on Win32. That would bring the Sqlite behaviour in line with the handling in the application program itself (provided that it uses OS APIs and not ICU). (2) I'm under the impression, that the problematic cases (german sharp-s, turkic i) are few compared with all the cases where a simple lookup would things make work. If I'm not mistaken, a lookup table of 2048 entries handling all 2 byte UTF-8 characters would already cover all the joint character repertoire of all ISO-8859-* (and their MSFT counterparts). Thai (in ISO 8859-11) is using three byte UTF-8 but doesn't have upper/lower case. Regards, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to remove SQLite profiling
Hello! Start console SQLite $ sqlite3 SQLite version 3.6.3 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .q profiling:/usr/src/sqlite3:Cannot create directory profiling:/usr/src/sqlite3/sqlite-3.6.3/.libs/sqlite3.gcda:Skip What is strings profiling:/usr/src/sqlite3:Cannot create directory profiling:/usr/src/sqlite3/sqlite-3.6.3/.libs/sqlite3.gcda:Skip and how can I remove it? Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
Thank You.. Java report builders are available on sourceforge.net and i have gone through it long before. What I am looking is the one which any one can use with VisualStudio 6 or ealier products. It should be handy. Not a complecated one. Simple like "just design and port the report file and then just view and print". Do you have any link which could help all of us. - Original Message - From: "Timothy A. Sawyer" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Saturday, November 15, 2008 10:24 AM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer > If you want something that is Windows based like you describe, why not use > Java to build a Swing GUI? Delphi and Pascal are a little antiquated so if > you need to revise you may have a problem finding a resource. Also > consider the platform independence if that is a concern > Timothy A. Sawyer, CISSP > Managing Director > MBD Solutions > Phone: (603) 546-7132 > Web: http://www.mybowlingdiary.com > Email: [EMAIL PROTECTED] > > > -Original Message- > From: "Rajesh Nair" <[EMAIL PROTECTED]> > > Date: Sat, 15 Nov 2008 09:23:46 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer > > > Thanks > > So I can't use it... Ok ... But I got some source code of FastReport ( > some > ealier version ) from the internet and it is in Dephi/Pascal. I don't know > both of the tools. Can you use the same code to build a dll which can be > used with other windows programing languages? ( So that no one can raise > their finger aganist me or YOU ). > > I don't want a very efficient-high-end report designer like FastReport. My > clients require very simple reports, which does not contain much > caculations > etc If you can help me please. It won't be just for me. Those who are > using SqLite with windows will be happy with a simple report deisgner and > viewer. Also my company is not willing to puchase any product for just > reporting, since our programs are supplied freely along with some of our > products. So an extra cost is not feasible. > > A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS > > > - Original Message - > From: "Cariotoglou Mike" <[EMAIL PROTECTED]> > To: "General Discussion of SQLite Database" > Sent: Friday, November 14, 2008 7:14 PM > Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer > > >>I am the author of sqlite3Explorer. Sorry I did not answer you previous >> post, reason is I did not motice it... :( >> >> I don't think what you want is feasible. the reason is this: the Dll >> expects a "dataset" which sqlite3Explorer builds internally, and the >> report engine takes it from there (Design, run, export etc). the dll >> itself does not contain data retrieval code, and relies on the host for >> this. >> >> this is ok, but the catch is here: the dataset format that the dll >> understands is a proprietary format developed by and only used by the >> company I work for. the dll engine is one the prime design/execute >> engines for our commercial projects. >> thus I cannot open-source it. and, since the dataset format in question >> cannot be produced without having access to the proprietary libraries >> we use here, what you want cannot be done. >> >> sorry... >> >>> -Original Message- >>> From: [EMAIL PROTECTED] >>> [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair >>> Sent: Friday, November 14, 2008 2:12 PM >>> To: General Discussion of SQLite Database >>> Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer >>> >>> Hi all >>> >>> How can I use the mkFrxEngine.DLL supplied along with the >>> Sqlite3Explorer. >>> The dll is a report designer and viewer. Please help me to >>> use that dll if any one knows. I asked the same before 10-14 >>> days ago, but didn't get any reply. If even the author of the >>> same is reading this please help me. I want to use it as a >>> report viewer for my sqlite3 database. >>> >>> >>> -- >>> Regards >>> Rajesh Nair >>> ___ >>> 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 > ___ > 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] Non-exclusive reserved lock?
On Sat, 15 Nov 2008 02:32:21 -0800, Roger Binns <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > >Kees Nuyt wrote: >> I assume the OP wants to collect updates from more than one >> webpage into one logical transaction, where every webpage >> contains another part of the data to be changed. In that >> case a memory database wouldn't work because it would >> disappear between page requests. > > More accurately the memory database has the same > lifetime of the database connection in that process, > and depending on how PHP is hosted there could be > multiple different processes involved in requests, and > processes could exit after processing a number of > requests. (It is important to clarify that SQLite > doesn't spuriously disappear in memory databases :-) Very true. To clarify some more: In general a database connection (and thus an SQLite memory database) is lost as soon as PHP has processed the request and sent a response page. >> That would work. I would use the main database all the way >> though, > > The OP is trying to prematurely optimize access away from > a potentially busy/locked main database :-) I secretly hope a complicated design pattern scared him away from doing that ;) >> One common design pattern > >It may be worth starting a section of the wiki with >various design patterns for common tasks. Yes, but I don't think the SQLite wiki is the appropriate place, as this is a generic session and transaction management (OLTP) issue, which has more to do with application frameworks than with a specific database engine. The SQLite wiki could contain links to relevant fora, but I wouldn't know one from the top of my head. Searching newsgroup comp.lang.php would be a good start. >Roger -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] One writer, many interruptible readers application design
Hello everybody, I know there are lots of documentation about sqlite and multithreading and yes, I have read all of it! ;) I'm to the point where I think about restructuring my application in order to make it more reactive, and I'd like to ask for your wise comments. The scheme I would like to implement is as follow: - The main thread (which displays the GUI and must therefore not be frozen by some long query) is the only thread that does write accesses to the database. Writing remains very seldom and fast, though. - Around this thread, several other threads may exist that run read-only queries on demand by the main thread (typically, when the user wants to display something in the user interface. I need to use a different thread because I don't want to block the GUI while the request is running). When the request is finished, the result is displayed by the main thread. However, the main thread may also want to abort that request, for instance if the user decides to display something else by the meantime. The problem is that there may be several of these peripheral threads (I estimate up to 5), and they must all be interruptible individually. This is where my problem lies: sqlite has a function to interrupt queries (sqlite3_interrupt), but it stops all the queries of a given connection. Therefore, to ensure all queries are individually interruptible, I must open one sqlite connection per thread. By doing so, and considering that the main thread only writes seldomly, I think I would not experience any GUI is freeze, as all the lengthly queries would be executed by other threads that can be interrupted if the user decides to take some further action before they complete. The only freeze condition that I may have would be when the main thread wants to write to the database while other threads are still reading it. In that case, the GUI may freeze while the main thread tries to acquire its lock. However, I think I can easily workaround that using another thread that is only dedicated to writing and buffers write requests as they arrive. This design will require a major overhaul of my application, so I'd like to make sure it is a good idea. I'd like to avoid using multiple connections, but I feel like I have no choice there. Would anyone have something to say against that design or would suggest a better idea? Thanks, Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Non-exclusive reserved lock?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kees Nuyt wrote: > I assume the OP wants to collect updates from more than one > webpage into one logical transaction, where every webpage > contains another part of the data to be changed. In that > case a memory database wouldn't work because it would > disappear between page requests. More accurately the memory database has the same lifetime of the database connection in that process, and depending on how PHP is hosted there could be multiple different processes involved in requests, and processes could exit after processing a number of requests. (It is important to clarify that SQLite doesn't spuriously disappear in memory databases :-) > That would work. I would use the main database all the way > though, The OP is trying to prematurely optimize access away from a potentially busy/locked main database :-) > One common design pattern It may be worth starting a section of the wiki with various design patterns for common tasks. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkepTEACgkQmOOfHg372QRPDgCg2SmHXsmdyR93ffvR0SWSWMEp zFoAniPt4WzVXIjm/Iq/qrru5FEVHhw2 =r/yF -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Non-exclusive reserved lock?
On Fri, 14 Nov 2008 18:34:04 -0800, Roger Binns <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > >Kyle Cronin wrote: >> In essence what I'm looking for is a way to defer database writes in a >> process so that they can all be done at once. However, while I'm >> accumulating update queries, > >Why not attach a memory database and accumulate your updates in there. >Once ready you do something like: > > INSERT INTO permanent_table SELECT * from memdb.table I assume the OP wants to collect updates from more than one webpage into one logical transaction, where every webpage contains another part of the data to be changed. In that case a memory database wouldn't work because it would disappear between page requests. >You could also attach another disk database and accumulate updates in >there, periodically copying across to the main database if you don't >want to lose the accumulating updates. That would work. I would use the main database all the way though, because it enables triggers that can act over the tables with persistent data and the tables with transient data meant to accumulate updates in. An PHP application like this (see my assumption above) always needs session data that spans over page requests. Session data could be used to store the updates. >Or you could follow the sage advice about premature optimization and >actually wait till you have problem and then address whatever that >problem turns out to be :-) One common design pattern is to flag every row that is "logically opened for update" with the user_id or session_id and a timestamp or transaction_id. In the update phase (when the user commits the logical update), the application should check those to verify the same rows have been grabbed by another user, and report a "Your update is blocked by another user" if they are. In the OP's scenario his users would never get that message: >> All I can say is that in my case there's no >> danger in performing these updates simultaneously. To get back to the original question: >> One solution is that in each process I could accumulate >> the text of the queries I wish to invoke in an array, >> then loop down it once I'm ready to write, but I'm >> wondering if it's possible the SQLite transaction can >> be made to do this for me automatically. There is no immediate support for that. SQLite is a database engine, not a transaction manager. As a side note: SQLite could be used to store session data, one could rewrite the "PHP4 MySQL Session Handler" by Ying Zhang for SQLite. http://coding.derkeiler.com/Archive/PHP/comp.lang.php/2005-06/msg01492.html HTH -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
