Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?
On 9 Dec 2014, at 1:36am, David Barrett wrote: > *Re: Why VACUUM.* We vacuum weekly. This particular database is a > "rolling journal" -- we are constantly adding new rows to the end of the > table, and every week we truncate off the head of the journal to only keep > 3M rows at the "tail". Given that we're truncating the "head", without > vacuuming we'd be inserting the new rows at the "front" of the database > with the old rows at the "end" -- and then each truncation would leave the > database more and more fragmented. Granted, this is on SSDs so the > fragmentation doesn't matter a *ton*, but it just adds up and gets worse > over time. Anyway, agreed it's not the most important thing to do, but all > things being equal I'd like to do it if I can to keep things clean and > snappy. Okay. I have some great news for you. You can completely ignore VACUUMing without any time or space drawbacks. You're wasting your time and using up the life of your SSD for no advantage. Fragmentation ceases to become a problem when you move from rotating disks to SSD. SSD is a truly random access medium. It's no faster to access block b then block b+1 than it is block b then block b+1000. Two contiguous blocks used to be faster in rotating disks only because there is a physical read/write head and it will already be in the right place. SSDs have no read/write head. It's all solid state and accessing one block is no faster than another. Delete old rows and you'll release space. Insert new rows and they'll take up the space released. Don't worry about the internal 'neatness' of the file. Over a long series of operations you might see an extra block used from time to time. But it will be either zero or one extra block per table/index. No more than that. A messy internal file structure might niggle the OCD side of your nature but that's the only disadvantage. Also, SSD drives wear out fast. We don't have good figures yet for mass-produced drives (manufacturers introduce new models faster than the old ones wear out, so it's hard to gather stats) but typical figures show a drive failing in from 2,000 to 3,000 write cycles of each single block. Your drive does something called 'wear levelling' and it has a certain number of blocks spare and will automatically swap them in when the first blocks fail, but after that your drive is smoke. And VACUUM /thrashes/ a drive, doing huge amounts of reading and writing as it rebuilds tables and indexes. You don't want to do something like that on an SSD without a good reason. So maybe once every few years, or perhaps if you have another more complicated maintenance routine which already takes up lots of time, do a VACUUM then. But it doesn't really matter if you never VACUUM. (Which is entirely unlike my home, dammit.) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
On 12/8/2014 8:43 PM, Keith Medcalf wrote: That is not a problem -- and I only fixed the where clause, not the set clause. The data will be "hot" on the second access, so the overhead of the additional access is negligible since CPU usage is negligible (even if it goes through all the motions of cold access) compared to I/O usage -- given a large enough page cache in RAM (so no I/O is required) to hold the pages involved in the tree traversals, of course. It's not really about efficiency - it's about verbosity. Imagine that you need to update not one but 10 fields in temp_table from the corresponding row in some_table: now you need to repeat the same condition 11 times. Imagine further that the condition is more complicated, involving joins on several tables. Pretty soon, we are talking one really long and convoluted query. Yes, there are workarounds (a view; or REPLACE INTO may sometimes be pressed into service). But I, for one, kinda miss UPDATE ... FROM. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
That is not a problem -- and I only fixed the where clause, not the set clause. The data will be "hot" on the second access, so the overhead of the additional access is negligible since CPU usage is negligible (even if it goes through all the motions of cold access) compared to I/O usage -- given a large enough page cache in RAM (so no I/O is required) to hold the pages involved in the tree traversals, of course. It is also possible to construct a view which may be used with an instead-of trigger to achieve indirectly the exact behaviour implemented by executing an update query of the form: UPDATE a SET x=b.b FROM a, b WHERE a.a = b.a AND ... You simply need to create a view which outputs the rowid's needing updating, and the values that should be updated. Then the instead of update trigger on the view merely applies the updates to the underlying real table. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Igor Tandetnik >Sent: Monday, 8 December, 2014 18:32 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] How do I update multiple rows in a single sql >statement > >On 12/8/2014 8:20 PM, Keith Medcalf wrote: >> >> update temp_table >> set id=(select id from some_table where c=42), >> operation='UPDATE' >> where exists (select 1 >> from some_table s >> where s.a=temp_table.a and s.b=temp_table.b and s.c=42); >> >> is the proper way of phrasing of a correlated subquery ... > >Now the problem is that (select id from some_table where c=42) takes an >id from some row of some_table - not necessarily the row with matching a >and b. > >Without some form of UPDATE...FROM (supported by some SQL engines, but >not SQLite), I can't think of a way to avoid repeating the whole >three-conjuncts condition twice - once in SET id=, and again in WHERE. >-- >Igor Tandetnik > >___ >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] Artificially slow VACUUM by injecting a sleep() somewhere?
Hi all, great questions: *Re: Why VACUUM.* We vacuum weekly. This particular database is a "rolling journal" -- we are constantly adding new rows to the end of the table, and every week we truncate off the head of the journal to only keep 3M rows at the "tail". Given that we're truncating the "head", without vacuuming we'd be inserting the new rows at the "front" of the database with the old rows at the "end" -- and then each truncation would leave the database more and more fragmented. Granted, this is on SSDs so the fragmentation doesn't matter a *ton*, but it just adds up and gets worse over time. Anyway, agreed it's not the most important thing to do, but all things being equal I'd like to do it if I can to keep things clean and snappy. *Re: "a simple way is to sleep in the progress callback"* -- Can you tell me more about this? Are you referring to the callback provided to sqlite3_exec(), or something else? Thanks! -david On Mon, Dec 8, 2014 at 3:16 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 12/08/2014 01:35 PM, Max Vlasov wrote: > > I wonder whether I/O "sleeping" possible in the first place. > > In this particular case the OP wants to vacuum while the machine is > doing other I/O activity unrelated to the vacuum. Having more > sleeping during the vacuum will allow the other I/O a greater share. > > Roger > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlSGMTgACgkQmOOfHg372QRxMACgz3qZHBGcUrOyf4DkFR5Km1a4 > jm4AoL49txXLfzPQefbjlnGg9UZ4GtcP > =9gAV > -END PGP SIGNATURE- > ___ > 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] Artificially slow VACUUM by injecting a sleep() somewhere?
Wanting is not needing. If a highly I/O bound process interferes with the I/O performed by other (not I/O bound) processes, then the OS is broken and the proper solution is to get a better O/S. These sorts of problems were solved back in the 60's (okay, maybe 70's). Therefore, unless a Redmond Operating System is in use (for which the only repair is choosing a better OS), then the problem is most likely bad or ill-suited hardware choices (insufficient cache, no data phase disconnect, excessive queueing, etc) or OS configuration deliberately set to ill-suited values (such as to use a "user" scheduler rather than a "server" scheduler, or a lack of pre-emption). --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Roger Binns >Sent: Monday, 8 December, 2014 16:16 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Artificially slow VACUUM by injecting a sleep() >somewhere? > >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > >On 12/08/2014 01:35 PM, Max Vlasov wrote: >> I wonder whether I/O "sleeping" possible in the first place. > >In this particular case the OP wants to vacuum while the machine is >doing other I/O activity unrelated to the vacuum. Having more >sleeping during the vacuum will allow the other I/O a greater share. > >Roger > >-BEGIN PGP SIGNATURE- >Version: GnuPG v1 > >iEYEARECAAYFAlSGMTgACgkQmOOfHg372QRxMACgz3qZHBGcUrOyf4DkFR5Km1a4 >jm4AoL49txXLfzPQefbjlnGg9UZ4GtcP >=9gAV >-END PGP SIGNATURE- >___ >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] How do I update multiple rows in a single sql statement
On 12/8/2014 8:20 PM, Keith Medcalf wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s where s.a=temp_table.a and s.b=temp_table.b and s.c=42); is the proper way of phrasing of a correlated subquery ... Now the problem is that (select id from some_table where c=42) takes an id from some row of some_table - not necessarily the row with matching a and b. Without some form of UPDATE...FROM (supported by some SQL engines, but not SQLite), I can't think of a way to avoid repeating the whole three-conjuncts condition twice - once in SET id=, and again in WHERE. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s where s.a=temp_table.a and s.b=temp_table.b and s.c=42); is the proper way of phrasing of a correlated subquery ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Luuk >Sent: Monday, 8 December, 2014 13:36 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] How do I update multiple rows in a single sql >statement > >On 8-12-2014 21:17, Igor Tandetnik wrote: >> On 12/8/2014 3:08 PM, Luuk wrote: >>> i hope this does it: >>> >>> update temp_table >>> set id=(select id from some_table where c=42), >>> operation='UPDATE' >>> where exists (select 1 >>> from some_table s, temp_table t >>> where s.a=t.a and s.b=t.b); >> >> This updates all rows in temp_table, as long as at least one row in >> temp_table matches one row in some_table. In other words, it updates no >> rows, or all rows - never just some. > >you are right > >update temp_table >set id=(select id from some_table where c=42), >operation='UPDATE' >where exists (select 1 > from some_table s, temp_table t > where s.a=t.a and s.b=t.b and s.c=42); >___ >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] Table names length and content
You get a palladium star for avoiding the most obvious source of errors (and wasted keystrokes) ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Drago, William @ CSG - NARDAEAST >Sent: Monday, 8 December, 2014 11:38 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Table names length and content > >> -Original Message- >> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >> boun...@sqlite.org] On Behalf Of Richard Hipp >> Sent: Monday, December 08, 2014 1:28 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Table names length and content >> >> On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera >> > > wrote: >> >> > >> > "Stephan Beal" wrote... >> > >> > On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera < >> >> jic...@cinops.xerox.com >> >> >> >>> wrote: >> >>> >> >> >> >> When creating table names, are there any constraints that one must >> >> look >> >>> for? ie. Length? Characters? etc.? I quickly browse through this >> >>> spot, >> >>> >> >>> https://www.sqlite.org/lang_createtable.html >> >>> >> >> >> >> >> >> https://www.sqlite.org/limits.html >> >> >> >> might have what you're looking for. >> >> >> > >> > Hmmm... what I am looking for it is not there. If the "string >> length" >> > defined there is what defines the length of the name of a table, I am >> > in business. :-) However, there is nothing about problematic >> characters. ie. >> > <>!@#$%^&*()_+=-{}\|[]'";:?/.,, etc., etc. in the table name. >> > >> > >> Put the table name in double-quotes. > >Uh oh! I never use quotes when I create my tables. What are the >implications of not quoting? > >-Bill >CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any >attachments are solely for the use of the addressee and may contain >information that is privileged or confidential. Any disclosure, use or >distribution of the information contained herein is prohibited. In the >event this e-mail contains technical data within the definition of the >International Traffic in Arms Regulations or Export Administration >Regulations, it is subject to the export control laws of the >U.S.Government. The recipient should check this e-mail and any >attachments for the presence of viruses as L-3 does not accept any >liability associated with the transmission of this e-mail. If you have >received this communication in error, please notify the sender by reply >e-mail and immediately delete this message and any attachments. >___ >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] Artificially slow VACUUM by injecting a sleep() somewhere?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 01:35 PM, Max Vlasov wrote: > I wonder whether I/O "sleeping" possible in the first place. In this particular case the OP wants to vacuum while the machine is doing other I/O activity unrelated to the vacuum. Having more sleeping during the vacuum will allow the other I/O a greater share. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSGMTgACgkQmOOfHg372QRxMACgz3qZHBGcUrOyf4DkFR5Km1a4 jm4AoL49txXLfzPQefbjlnGg9UZ4GtcP =9gAV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
Thank you everyone! I'll try to solve this case with your replies! Thanks, 2014年12月9日火曜日、Richard Hippさんは書きました: > On Mon, Dec 8, 2014 at 12:20 PM, Scott Robison > > wrote: > > > On Dec 8, 2014 2:10 AM, "Shinichiro Yoshioka" > > > wrote: > > > > > > Hi, > > > > > > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > > > But although the compiling was successfully finished, even if I set > break > > > point > > > on the source code, I can't trace the working line in sqlite3.c > > correctly. > > > > As someone else said, the problem is that the Visual C++ debugger doesn't > > play nice with files in excess of 64KiB lines. When I had this need a > > couple years ago, I carefully split the sqlite3.c file into several > pieces > > and compiled them separately. > > > > > From the canonical SQLite source code you can type "make sqlite3-all.c" and > it will generate a version of the amalgamation that #includes a handful of > separate files (named sqlite3-N.c for N=1,2,3,), each less than 32K > lines in size. > > drh@bella:~/sqlite/bld$ make sqlite3-all.c > tclsh /home/drh/sqlite/sqlite/tool/split-sqlite3c.tcl > drh@bella:~/sqlite/bld$ wc sqlite3-*.c > 32314 165952 1228350 sqlite3-1.c > 30892 145495 1098859 sqlite3-2.c > 32729 144742 1091870 sqlite3-3.c > 32481 150359 1198841 sqlite3-4.c > 23259 100070 768733 sqlite3-5.c > 32 2371518 sqlite3-all.c > 151707 706855 5388171 total > > Include all these files in your project, but compile against just > sqlite3-all.c. > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Artificially slow VACUUM by injecting a sleep() somewhere?
On Mon, Dec 8, 2014 at 5:56 PM, Roger Binns wrote: > On 12/07/2014 04:43 PM, David Barrett wrote: >> so I'm curious if you can think of a way using the API (or any >> other way) to essentially "nice" the process by inserting a short >> "sleep" into whatever loop runs inside the VACUUM command. > > Using OS provided functionality will be the most reliable. Other than > that, a simple way is to sleep in the progress callback, although that > will make I/O lumpy. I wonder whether I/O "sleeping" possible in the first place. Correct me, but what we usually call "sleeping" is about CPU that already sleeps during most I/O operations waiting for rotating media finishing its slow tasks. As a consequence, the more fragmented the data on disk, the less relative cpu time will be spent trying to read and write data. In this case the strategy might be to measure cpu consumption value for consecutive blocks of data and if it's 100% (or other heuristically adequate value) then never sleep (this probably means either cached data or non-fragmented data on a fast disk). But when the cpu consumption drops significantly (much time spent waiting for I/O), the sleeping indeed might be needed. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
On 12/8/2014 3:35 PM, Luuk wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b and s.c=42); Same thing. Your WHERE clause doesn't depend on the values in the current row of temp_table - it's either always true, or always false. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
On 8-12-2014 21:17, Igor Tandetnik wrote: On 12/8/2014 3:08 PM, Luuk wrote: i hope this does it: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b); This updates all rows in temp_table, as long as at least one row in temp_table matches one row in some_table. In other words, it updates no rows, or all rows - never just some. you are right update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b and s.c=42); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
The visibility of table created in with clause is only in the SET part. The following is a valid sql statement in sqlite3. with ds as (select id, a , b, c from some_table where c = 43) update temp_table set id = (select ds.id from ds where ds.a = temp_table.a AND ds.b = temp_table.b), operation = 'UPDATE'; Have to take care using case statement when a match is not found. Fiberlink Disclaimer: 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
"Richard Hipp" wrote... On Mon, Dec 8, 2014 at 1:36 PM, Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 10:30 AM, jose isaias cabrera wrote: >>> Hmmm... what I am looking for it is not there. If the "string >>> length" defined there is what defines the length of the name of >>> a table, I am in business. :-) However, there is nothing about >>> problematic characters. ie. <>!@#$%^&*()_+=-{}\|[]'";:?/.,, >>> etc., etc. in the table name. SQLite supports all those, as well as zero length table names, column types and names. But just because SQLite supports all of that does not mean that you *should* use it. I'm worried about what you are contemplating, Jose. I think you would be better off to use a short and simple lower-case ASCII table name. Most of the time, I use single and lowercase names. And, I will take your advice and continue with such. I was trying to do an easy hack, but, you're right. It's a long story, so I will save it for another day... Thanks all. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
On 12/8/2014 3:08 PM, Luuk wrote: i hope this does it: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b); This updates all rows in temp_table, as long as at least one row in temp_table matches one row in some_table. In other words, it updates no rows, or all rows - never just some. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
On 8-12-2014 20:50, Venkat Murty wrote: How do I update multiple rows in a single sql statement. Two tables: create table some_table(id, a, b, c); create table temp_table (id, operation, a, b, c); Operation: Updating id, operation fields in temp_table if the record exists in some_table. with ds as (select id, a , b, c from some_table where c = 42) update temp_table set id = ds.id, operation = 'UPDATE' WHERE ds.a = temp_table.a AND ds.b = temp_table.b; I get the error " no such column: ds.id" i hope this does it: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do I update multiple rows in a single sql statement
How do I update multiple rows in a single sql statement. Two tables: create table some_table(id, a, b, c); create table temp_table (id, operation, a, b, c); Operation: Updating id, operation fields in temp_table if the record exists in some_table. with ds as (select id, a , b, c from some_table where c = 42) update temp_table set id = ds.id, operation = 'UPDATE' WHERE ds.a = temp_table.a AND ds.b = temp_table.b; I get the error " no such column: ds.id" Thanks, Venkat Murty Fiberlink Disclaimer: 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
That's a relief. I thought I was doing something wrong. Thanks, -- Bill Drago Senior Engineer L3 Communications / Narda Microwave East 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / william.dr...@l-3com.com > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Stephan Beal > Sent: Monday, December 08, 2014 1:41 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Table names length and content > > On Mon, Dec 8, 2014 at 7:37 PM, Drago, William @ CSG - NARDAEAST < > william.dr...@l-3com.com> wrote: > > > Uh oh! I never use quotes when I create my tables. What are the > > implications of not quoting? > > > > If you restrict yourself to C-style identifiers, the primary > implication is better ease of use and readability! ;) > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > http://gplus.to/sgbeal > "Freedom is sloppy. But since tyranny's the only guaranteed byproduct > of those who insist on a perfect world, freedom will have to do." -- > Bigby Wolf ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
On Mon, Dec 8, 2014 at 1:36 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 12/08/2014 10:30 AM, jose isaias cabrera wrote: > >>> Hmmm... what I am looking for it is not there. If the "string > >>> length" defined there is what defines the length of the name of > >>> a table, I am in business. :-) However, there is nothing about > >>> problematic characters. ie. <>!@#$%^&*()_+=-{}\|[]'";:?/.,, > >>> etc., etc. in the table name. > > SQLite supports all those, as well as zero length table names, column > types and names. > But just because SQLite supports all of that does not mean that you *should* use it. I'm worried about what you are contemplating, Jose. I think you would be better off to use a short and simple lower-case ASCII table name. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
On Mon, Dec 8, 2014 at 7:37 PM, Drago, William @ CSG - NARDAEAST < william.dr...@l-3com.com> wrote: > Uh oh! I never use quotes when I create my tables. What are the > implications of not quoting? > If you restrict yourself to C-style identifiers, the primary implication is better ease of use and readability! ;) -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
On Mon, Dec 8, 2014 at 7:36 PM, Roger Binns wrote: > SQLite supports all those, as well as zero length table names, column > types and names. > > > sqlite> create table " <>!@#$%^&*()_+=-{}\|[]'"";:?/.,, etc., etc. in > the table name"("" ""); > To whichl add: sqlite allows it. Your fellow colleagues, on the other hand, will hopefully not let such things through code review ;). (Empty strings? Really?) -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Monday, December 08, 2014 1:28 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Table names length and content > > On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera > > wrote: > > > > > "Stephan Beal" wrote... > > > > On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera < > >> jic...@cinops.xerox.com > >> > >>> wrote: > >>> > >> > >> When creating table names, are there any constraints that one must > >> look > >>> for? ie. Length? Characters? etc.? I quickly browse through this > >>> spot, > >>> > >>> https://www.sqlite.org/lang_createtable.html > >>> > >> > >> > >> https://www.sqlite.org/limits.html > >> > >> might have what you're looking for. > >> > > > > Hmmm... what I am looking for it is not there. If the "string > length" > > defined there is what defines the length of the name of a table, I am > > in business. :-) However, there is nothing about problematic > characters. ie. > > <>!@#$%^&*()_+=-{}\|[]'";:?/.,, etc., etc. in the table name. > > > > > Put the table name in double-quotes. Uh oh! I never use quotes when I create my tables. What are the implications of not quoting? -Bill CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/2014 10:30 AM, jose isaias cabrera wrote: >>> Hmmm... what I am looking for it is not there. If the "string >>> length" defined there is what defines the length of the name of >>> a table, I am in business. :-) However, there is nothing about >>> problematic characters. ie. <>!@#$%^&*()_+=-{}\|[]'";:?/.,, >>> etc., etc. in the table name. SQLite supports all those, as well as zero length table names, column types and names. sqlite> create table " <>!@#$%^&*()_+=-{}\|[]'"";:?/.,, etc., etc. in the table name"("" ""); sqlite> .header on sqlite> pragma table_info(" <>!@#$%^&*()_+=-{}\|[]'"";:?/.,, etc., etc. in the table name"); cid|name|type|notnull|dflt_value|pk 0|||0||0 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSF764ACgkQmOOfHg372QRorQCcDbpSsjwclDLcKAiRQlFOC73M Sc8AnirtIkzx1v/5LWrAc1VYAGJ53MnS =bj14 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
"Richard Hipp" wrote... On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera wrote: "Stephan Beal" wrote... On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera < jic...@cinops.xerox.com wrote: When creating table names, are there any constraints that one must look for? ie. Length? Characters? etc.? I quickly browse through this spot, https://www.sqlite.org/lang_createtable.html https://www.sqlite.org/limits.html might have what you're looking for. Hmmm... what I am looking for it is not there. If the "string length" defined there is what defines the length of the name of a table, I am in business. :-) However, there is nothing about problematic characters. ie. <>!@#$%^&*()_+=-{}\|[]'";:?/.,, etc., etc. in the table name. Put the table name in double-quotes. If the table name contains a double-quote mark (ascii 0x22) then double it. The name of the table cannot start with "sqlite" but otherwise, there are no other restrictions that I recall. Thanks, Dr. Hipp. That is what I was looking for. I will let you know if something does not work. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera wrote: > > "Stephan Beal" wrote... > > On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera < >> jic...@cinops.xerox.com >> >>> wrote: >>> >> >> When creating table names, are there any constraints that one must look >>> for? ie. Length? Characters? etc.? I quickly browse through this spot, >>> >>> https://www.sqlite.org/lang_createtable.html >>> >> >> >> https://www.sqlite.org/limits.html >> >> might have what you're looking for. >> > > Hmmm... what I am looking for it is not there. If the "string length" > defined there is what defines the length of the name of a table, I am in > business. :-) However, there is nothing about problematic characters. ie. > <>!@#$%^&*()_+=-{}\|[]'";:?/.,, etc., etc. in the table name. > > Put the table name in double-quotes. If the table name contains a double-quote mark (ascii 0x22) then double it. The name of the table cannot start with "sqlite" but otherwise, there are no other restrictions that I recall. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
"Stephan Beal" wrote... On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera wrote: When creating table names, are there any constraints that one must look for? ie. Length? Characters? etc.? I quickly browse through this spot, https://www.sqlite.org/lang_createtable.html https://www.sqlite.org/limits.html might have what you're looking for. Hmmm... what I am looking for it is not there. If the "string length" defined there is what defines the length of the name of a table, I am in business. :-) However, there is nothing about problematic characters. ie. <>!@#$%^&*()_+=-{}\|[]'";:?/.,, etc., etc. in the table name. Thoughts? Thanks. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table names length and content
On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera wrote: > When creating table names, are there any constraints that one must look > for? ie. Length? Characters? etc.? I quickly browse through this spot, > > https://www.sqlite.org/lang_createtable.html https://www.sqlite.org/limits.html might have what you're looking for. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table names length and content
Greetings! When creating table names, are there any constraints that one must look for? ie. Length? Characters? etc.? I quickly browse through this spot, https://www.sqlite.org/lang_createtable.html but could not find any information regarding this. Would someone please bring light for this subject for me? Thanks. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
On Mon, Dec 8, 2014 at 12:20 PM, Scott Robison wrote: > On Dec 8, 2014 2:10 AM, "Shinichiro Yoshioka" > wrote: > > > > Hi, > > > > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > > But although the compiling was successfully finished, even if I set break > > point > > on the source code, I can't trace the working line in sqlite3.c > correctly. > > As someone else said, the problem is that the Visual C++ debugger doesn't > play nice with files in excess of 64KiB lines. When I had this need a > couple years ago, I carefully split the sqlite3.c file into several pieces > and compiled them separately. > > >From the canonical SQLite source code you can type "make sqlite3-all.c" and it will generate a version of the amalgamation that #includes a handful of separate files (named sqlite3-N.c for N=1,2,3,), each less than 32K lines in size. drh@bella:~/sqlite/bld$ make sqlite3-all.c tclsh /home/drh/sqlite/sqlite/tool/split-sqlite3c.tcl drh@bella:~/sqlite/bld$ wc sqlite3-*.c 32314 165952 1228350 sqlite3-1.c 30892 145495 1098859 sqlite3-2.c 32729 144742 1091870 sqlite3-3.c 32481 150359 1198841 sqlite3-4.c 23259 100070 768733 sqlite3-5.c 32 2371518 sqlite3-all.c 151707 706855 5388171 total Include all these files in your project, but compile against just sqlite3-all.c. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
On Dec 8, 2014 2:10 AM, "Shinichiro Yoshioka" wrote: > > Hi, > > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > But although the compiling was successfully finished, even if I set break > point > on the source code, I can't trace the working line in sqlite3.c correctly. As someone else said, the problem is that the Visual C++ debugger doesn't play nice with files in excess of 64KiB lines. When I had this need a couple years ago, I carefully split the sqlite3.c file into several pieces and compiled them separately. SDR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 9:01 AM, Dan Kennedy wrote: > You could hack SQLite to do enforce unique constraints the same way as FKs. > When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If > one exists, increment a counter. Do the opposite when removing entries - > decrement the counter if there are two or more duplicates of the entry you > are removing. If your counter is greater than zero at commit time, a UNIQUE > constraint has failed. > > I suspect there would be a non-trivial increase in the CPU use of UPDATE > statements though. Well, it'd be an option which, when not used, ought to cost very few additional unlikely branches. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
> Le 8 déc. 2014 à 17:21, Simon Slavin a écrit : > >> Why not an opt-in way to ask for deferred constraint checking. The key here >> is only to allow perfectly legit requests to run. With all the due respect >> to sqlite implementors and the wonderful design of sqlite. > > SQL-99 includes a syntax for deferred checking. We don't need to invent our > own syntax with a PRAGMA. However, it is done when the constraint is defined > rather than being something one can turn on or off. So you would need to > think out whether you wanted row- or transaction-based checking when you > define each constraint in the first place. Hi Simon, This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled upon this page which shows how deferred index maintenance affects Oracle query plan, and performance : https://alexanderanokhin.wordpress.com/deferred-index-maintenance/. I now understand that the strategy for checking index constraints is tied to their maintenance. The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1` query we are talking about has indeed to perform both. Such an innocuous-looking request, and it sends us right into the very guts of relational constraints :-) Gwendal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 8 Dec 2014, at 3:05pm, Gwendal Roué wrote: > Why not an opt-in way to ask for deferred constraint checking. The key here > is only to allow perfectly legit requests to run. With all the due respect to > sqlite implementors and the wonderful design of sqlite. SQL-99 includes a syntax for deferred checking. We don't need to invent our own syntax with a PRAGMA. However, it is done when the constraint is defined rather than being something one can turn on or off. So you would need to think out whether you wanted row- or transaction-based checking when you define each constraint in the first place. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Yes, that would be nice. For example, sqlite already needs explicit opt-in for some of the relational toolkit. I think about "PRAGMA foreign_keys = ON". Why not an opt-in way to ask for deferred constraint checking. The key here is only to allow perfectly legit requests to run. With all the due respect to sqlite implementors and the wonderful design of sqlite. > Le 8 déc. 2014 à 15:55, Nico Williams a écrit : > > Ideally there would be something like DEFERRED foreign key checking > for uniqueness constraints... You can get something like that by > using non-unique indexes (but there would also go your primary keys) > and then check that there are no duplicates before you COMMIT. (Doing > this reliably would require something like transaction triggers, which > IIRC exists in a "sessions" branch.) > > Nico > -- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> Le 8 déc. 2014 à 10:55, Gwendal Roué a écrit : >> >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables implement a book with several pages. >> -- Page ordering is implemented via the `position` column in the pages table. >> -- A unique index makes sure two pages do not share the same position. >> CREATE TABLE books ( >>id INT PRIMARY KEY >>) >> CREATE TABLE pages ( >>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE >> CASCADE, >>position INT >> ) >> CREATE UNIQUE INDEX pagination ON pages(book_id, position) >> >> -- Let's populate the tables with a single book and three pages: >> INSERT INTO books VALUES (0); >> INSERT INTO pages VALUES (0,0); >> INSERT INTO pages VALUES (0,1); >> INSERT INTO pages VALUES (0,2); >> >> -- We want to insert a page between the pages at positions 0 and 1. So we >> have >> -- to increment the positions of all pages after page 1. >> -- Unfortunately, this query yields an error: "columns book_id, position are >> not unique"/ >> >> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1; >> >> The query should run without any error, since it does not break the unique >> index. >> >> Thank you for considering this issue. >> >> Cheers, >> Gwendal Roué >> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 12/08/2014 09:55 PM, Nico Williams wrote: Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You could hack SQLite to do enforce unique constraints the same way as FKs. When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If one exists, increment a counter. Do the opposite when removing entries - decrement the counter if there are two or more duplicates of the entry you are removing. If your counter is greater than zero at commit time, a UNIQUE constraint has failed. I suspect there would be a non-trivial increase in the CPU use of UPDATE statements though. You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would require something like transaction triggers, which IIRC exists in a "sessions" branch.) Nico -- ___ 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] Artificially slow VACUUM by injecting a sleep() somewhere?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/07/2014 04:43 PM, David Barrett wrote: > so I'm curious if you can think of a way using the API (or any > other way) to essentially "nice" the process by inserting a short > "sleep" into whatever loop runs inside the VACUUM command. Using OS provided functionality will be the most reliable. Other than that, a simple way is to sleep in the progress callback, although that will make I/O lumpy. If you want finer grained control then you can copy the pointers for the default VFS into your own VFS, and override the read/write methods to rate limit themselves. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlSFvBsACgkQmOOfHg372QRv9wCfYrybsVowHx6QTpbw/WjMoSZh AJIAoNc4HyP1pUU/AvTGkdjJeQm93I7Y =IKzd -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would require something like transaction triggers, which IIRC exists in a "sessions" branch.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 8:23 AM, Marc L. Allen wrote: > I'm not sure I'd even consider it broken. > Well, to some on that forum: "If it doesn't work the way that _I_ want, then it is ipso-facto broken". And I forgot the in my message. Sorry. > > SQLite is wonderful. Simply wonderful. Code size and amount of features > forced into it impresses me no end. But, it was never intended to run with > the big dogs. The fact that, quite often, it can is a tribute to the > people that work on it. > I completely agree. I took the source code and copied to my z/OS mainframe operating system. This system is a UNIX branded system. But is very weird. Mainly in that it does not use ASCII or Unicode, but another coding sequence called EBCDIC. Dr. Hipp already had the EBCDIC code in SQLite. And, despite not having access to a z/OS system (as I understand it), the code compiled and ran cleanly on z/OS "out of the box". Amazing! > > When making a 'lite' version of something, it's normal to eliminate > difficult or intensive features that can be lived without. I think this is > one of them. > Again, I agree. The only other RDMS which I have used on the aforementioned system, which was not especially designed for it (DB2), is Derby (pure Java implementation). SQLite is, IMO, much nicer. And it is definitely much less of a "hog". > > Marc > > -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 8-12-2014 14:58, Gwendal Roué wrote: Le 8 déc. 2014 à 14:48, RSmith a écrit : On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: "columns book_id, position are not unique"/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1; NOT a bug... the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like (0,2) and there is of course at this point in time already an entry like (0,2). Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a non-deferred checking. Now that we have established it isn't a bug, I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué It's not a bug, it's in the manual that SQLite behave this way (https://www.sqlite.org/lang_update.html) Optional LIMIT and ORDER BY Clauses If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses as follows: . The ORDER BY clause on an UPDATE statement is used only to determine which rows fall within the LIMIT. The order in which rows are modified is arbitrary and is *not* influenced by the ORDER BY clause. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Doesn't that code risk being broken in a later version that doesn't update in the order provided by the sub-query? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J T Sent: Monday, December 08, 2014 9:23 AM To: rsm...@rsweb.co.za; sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your page. Please see if that'll work. I tested it, but your results may differ. -Original Message- From: RSmith To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 9:15 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On 2014/12/08 15:58, Gwendal Roué wrote: > I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed > without introducing any regression (since fixing it would cause > failing code to suddenly run, and this has never been a > compatibility issue). Thank you all for your support and explanations. > The root cause has been found, and lies in the constraint > checking algorithm of sqlite. I have been able to find a work around > that is good enough for me. Now the subject deserves a rest, > until, maybe, someday, one sqlite maintainer who his not attached to > the constraint-checking algorithm fixes it. Have a nice day, > Gwendal Roué Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where backward-compatibility is not an issue. The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not make it a "bug" as long as it works as described. I hope the work-around you found works great! ___ 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
> Le 8 déc. 2014 à 15:18, John McKown a écrit : > > On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen > wrote: > >> I am like you, Gwendal, in that I don't like that behavior in SQLite; >> however, not liking it doesn't make it a bug. >> > > On another of my forums, this is called a BAD - Broken, As Designed. As > opposed to the normal WAD - Working As Designed. Thanks RSmith, Marc and John. I can live with this :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Cancel that, apparently that only updates the last record... -Original Message- From: John McKown To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 9:18 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen wrote: > I am like you, Gwendal, in that I don't like that behavior in SQLite; > however, not liking it doesn't make it a bug. > On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! <>< John McKown ___ 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] sqlite bugreport : unique index causes valid updates to fail
I'm not sure I'd even consider it broken. SQLite is wonderful. Simply wonderful. Code size and amount of features forced into it impresses me no end. But, it was never intended to run with the big dogs. The fact that, quite often, it can is a tribute to the people that work on it. When making a 'lite' version of something, it's normal to eliminate difficult or intensive features that can be lived without. I think this is one of them. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John McKown Sent: Monday, December 08, 2014 9:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen wrote: > I am like you, Gwendal, in that I don't like that behavior in SQLite; > however, not liking it doesn't make it a bug. > On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your page. Please see if that'll work. I tested it, but your results may differ. -Original Message- From: RSmith To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 9:15 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On 2014/12/08 15:58, Gwendal Roué wrote: > I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed > without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a > compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint > checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, > until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, > Gwendal Roué Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where backward-compatibility is not an issue. The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not make it a "bug" as long as it works as described. I hope the work-around you found works great! ___ 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] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen wrote: > I am like you, Gwendal, in that I don't like that behavior in SQLite; > however, not liking it doesn't make it a bug. > On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. The constraint-checking algorithm was defined to work exactly the way it's working. When designed, the fact that your type of insert would fail was known and understood. Hence, it cannot be considered a bug. Changing it at this date might be a problem. While unlikely, there is a possibility that code exists out there that takes advantage of that particular design attribute. Then you get into pragmas and options and the like. I don't do any of the development, but I suspect that's a serious pain when there are other features that are more useful to work on. So, in short... not a bug, but a design feature that you don't care for. I'm sure there's a way to make suggestions or requests to change the design. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where backward-compatibility is not an issue. The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not make it a "bug" as long as it works as described. I hope the work-around you found works great! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
> Le 8 déc. 2014 à 14:48, RSmith a écrit : > > > On 2014/12/08 11:55, Gwendal Roué wrote: >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables implement a book with several pages. >> -- Page ordering is implemented via the `position` column in the pages table. >> -- A unique index makes sure two pages do not share the same position. >> CREATE TABLE books ( >> id INT PRIMARY KEY >> ) >> CREATE TABLE pages ( >> book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON >> UPDATE CASCADE, >> position INT >> ) >> CREATE UNIQUE INDEX pagination ON pages(book_id, position) >> >> -- Let's populate the tables with a single book and three pages: >> INSERT INTO books VALUES (0); >> INSERT INTO pages VALUES (0,0); >> INSERT INTO pages VALUES (0,1); >> INSERT INTO pages VALUES (0,2); >> >> -- We want to insert a page between the pages at positions 0 and 1. So we >> have >> -- to increment the positions of all pages after page 1. >> -- Unfortunately, this query yields an error: "columns book_id, position are >> not unique"/ >> >> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1; > > NOT a bug... the moment you SET position to position +1 for the first > iteration of the query, it tries to make that entry look like (0,2) and there > is of course at this point in time already an entry like (0,2). > > Some engines allow you to defer the constraint checking until the end of the > transaction (and you can do this for References, though you are cascading > which is fine). In SQLite the check is immediate and will fail for the > duplication attempted on the first iteration. The fact that the other record > will eventually be changed to no longer cause a fail is irrelevant to the > engine in a non-deferred checking. > > Now that we have established it isn't a bug, I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: "columns book_id, position are not unique"/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1; NOT a bug... the moment you SET position to position +1 for the first iteration of the query, it tries to make that entry look like (0,2) and there is of course at this point in time already an entry like (0,2). Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a non-deferred checking. Now that we have established it isn't a bug, some methods of working round this exist, like Updating in the reverse order (though this has to be done in code as the UPDATE function cannot be ordered). Also creating a temp table then substituting it after an update (but then you have to recreate the index anyway, so dropping the index and re-making it is better though this can take a long time on really large tables). My favourite is simply running the query twice, once making the values negative, and once more fixing them, like this: UPDATE pages SET position = ((position + 1) * -1) WHERE book_id = 0 AND position >= 1; UPDATE pages SET position = abs(position) WHERE book_id = 0 AND position < 0; No mess, no fuss, no Unique constraint problem. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Sorry, wasn't focused on what I was looking at. Though, you said you already tried the order by without success which would have been my next suggestion or clarification of my first. As, you should be able to update the rows from the end down to the page that would be after your insertion (update pages set position=position + 1 where book=0 order by position desc.) and then inserting the new page at the desired position. But if that's not working, I have to agree with your opinion of it being a bug. -Original Message- From: Gwendal Roué To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 8:40 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail J T, I did provide a sequence of queries that reliably reproduce the issue (see below, from the first CREATE to the last UPDATE). There is no trigger involved, as far as I know. Forgive me but I don't see how I could use your advice. My work around has been to destroy the unique index, and then re-create it after the update. This solution is good enough as my table is not that big, and the "pure" code path remains intact, with only two inserted statements that are easily described and commented. Gwendal Roué > Le 8 déc. 2014 à 14:24, J T a écrit : > > Try having your cascade occur before the row is created, updated or deleted. > > http://www.sqlite.org/lang_createtrigger.html > > > > > > > > -Original Message- > From: Richard Hipp > To: General Discussion of SQLite Database > Sent: Mon, Dec 8, 2014 8:14 am > Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail > > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables implement a book with several pages. >> -- Page ordering is implemented via the `position` column in the pages >> table. >> -- A unique index makes sure two pages do not share the same position. >> CREATE TABLE books ( >>id INT PRIMARY KEY >>) >> CREATE TABLE pages ( >>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON >> UPDATE CASCADE, >>position INT >> ) >> CREATE UNIQUE INDEX pagination ON pages(book_id, position) >> >> -- Let's populate the tables with a single book and three pages: >> INSERT INTO books VALUES (0); >> INSERT INTO pages VALUES (0,0); >> INSERT INTO pages VALUES (0,1); >> INSERT INTO pages VALUES (0,2); >> >> -- We want to insert a page between the pages at positions 0 and 1. So we >> have >> -- to increment the positions of all pages after page 1. >> -- Unfortunately, this query yields an error: "columns book_id, position >> are not unique"/ >> >> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= >> 1; >> >> The query should run without any error, since it does not break the unique >> index. >> > > Uniqueness is checked for each row change, not just at the end of the > transaction. Hence, uniqueness might fail, depending on the order in which > the individual rows are updated. > > >> >> Thank you for considering this issue. >> >> Cheers, >> Gwendal Roué >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] sqlite bugreport : unique index causes valid updates to fail
> Le 8 déc. 2014 à 14:39, Simon Slavin a écrit : > > On 8 Dec 2014, at 1:31pm, Gwendal Roué wrote: > >> We share the same conclusion. I even tried to decorate the update query with >> "ORDER" clauses, in a foolish attempt to reverse the ordering of row >> updates, and circumvent the issue. > > A way to solve this is to use REAL for page numbers instead of INTEGER. To > insert a page between two existing ones, give it a number which is the mean > of the two pages you're inserting it between. Every so often you can run a > maintenance routine which renumbers all pages to integers. > > Alternatively, store your pages as a linked list. Polluting my database schema around such a bug is not an option for me, as long as I can find a work around that is good enough and leaves my intent intact. The one I chose involves destroying the unique index before running the failing update query, and then recreating it. All I look for is this issue to enter the ticket list of sqlite at http://www.sqlite.org/src/reportlist, so that this fantastic embeddable database gets better. Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
J T, I did provide a sequence of queries that reliably reproduce the issue (see below, from the first CREATE to the last UPDATE). There is no trigger involved, as far as I know. Forgive me but I don't see how I could use your advice. My work around has been to destroy the unique index, and then re-create it after the update. This solution is good enough as my table is not that big, and the "pure" code path remains intact, with only two inserted statements that are easily described and commented. Gwendal Roué > Le 8 déc. 2014 à 14:24, J T a écrit : > > Try having your cascade occur before the row is created, updated or deleted. > > http://www.sqlite.org/lang_createtrigger.html > > > > > > > > -Original Message- > From: Richard Hipp > To: General Discussion of SQLite Database > Sent: Mon, Dec 8, 2014 8:14 am > Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to > fail > > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables implement a book with several pages. >> -- Page ordering is implemented via the `position` column in the pages >> table. >> -- A unique index makes sure two pages do not share the same position. >> CREATE TABLE books ( >>id INT PRIMARY KEY >>) >> CREATE TABLE pages ( >>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON >> UPDATE CASCADE, >>position INT >> ) >> CREATE UNIQUE INDEX pagination ON pages(book_id, position) >> >> -- Let's populate the tables with a single book and three pages: >> INSERT INTO books VALUES (0); >> INSERT INTO pages VALUES (0,0); >> INSERT INTO pages VALUES (0,1); >> INSERT INTO pages VALUES (0,2); >> >> -- We want to insert a page between the pages at positions 0 and 1. So we >> have >> -- to increment the positions of all pages after page 1. >> -- Unfortunately, this query yields an error: "columns book_id, position >> are not unique"/ >> >> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= >> 1; >> >> The query should run without any error, since it does not break the unique >> index. >> > > Uniqueness is checked for each row change, not just at the end of the > transaction. Hence, uniqueness might fail, depending on the order in which > the individual rows are updated. > > >> >> Thank you for considering this issue. >> >> Cheers, >> Gwendal Roué >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] sqlite bugreport : unique index causes valid updates to fail
On 8 Dec 2014, at 1:31pm, Gwendal Roué wrote: > We share the same conclusion. I even tried to decorate the update query with > "ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, > and circumvent the issue. A way to solve this is to use REAL for page numbers instead of INTEGER. To insert a page between two existing ones, give it a number which is the mean of the two pages you're inserting it between. Every so often you can run a maintenance routine which renumbers all pages to integers. Alternatively, store your pages as a linked list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Thanks J T. Let's give a look. > Le 8 déc. 2014 à 14:24, J T a écrit : > > Try having your cascade occur before the row is created, updated or deleted. > > http://www.sqlite.org/lang_createtrigger.html > > > > > > > > -Original Message- > From: Richard Hipp > To: General Discussion of SQLite Database > Sent: Mon, Dec 8, 2014 8:14 am > Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to > fail > > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables implement a book with several pages. >> -- Page ordering is implemented via the `position` column in the pages >> table. >> -- A unique index makes sure two pages do not share the same position. >> CREATE TABLE books ( >>id INT PRIMARY KEY >>) >> CREATE TABLE pages ( >>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON >> UPDATE CASCADE, >>position INT >> ) >> CREATE UNIQUE INDEX pagination ON pages(book_id, position) >> >> -- Let's populate the tables with a single book and three pages: >> INSERT INTO books VALUES (0); >> INSERT INTO pages VALUES (0,0); >> INSERT INTO pages VALUES (0,1); >> INSERT INTO pages VALUES (0,2); >> >> -- We want to insert a page between the pages at positions 0 and 1. So we >> have >> -- to increment the positions of all pages after page 1. >> -- Unfortunately, this query yields an error: "columns book_id, position >> are not unique"/ >> >> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= >> 1; >> >> The query should run without any error, since it does not break the unique >> index. >> > > Uniqueness is checked for each row change, not just at the end of the > transaction. Hence, uniqueness might fail, depending on the order in which > the individual rows are updated. > > >> >> Thank you for considering this issue. >> >> Cheers, >> Gwendal Roué >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] sqlite bugreport : unique index causes valid updates to fail
> Le 8 déc. 2014 à 14:14, Richard Hipp a écrit : > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables implement a book with several pages. >> -- Page ordering is implemented via the `position` column in the pages >> table. >> -- A unique index makes sure two pages do not share the same position. >> CREATE TABLE books ( >>id INT PRIMARY KEY >>) >> CREATE TABLE pages ( >>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON >> UPDATE CASCADE, >>position INT >> ) >> CREATE UNIQUE INDEX pagination ON pages(book_id, position) >> >> -- Let's populate the tables with a single book and three pages: >> INSERT INTO books VALUES (0); >> INSERT INTO pages VALUES (0,0); >> INSERT INTO pages VALUES (0,1); >> INSERT INTO pages VALUES (0,2); >> >> -- We want to insert a page between the pages at positions 0 and 1. So we >> have >> -- to increment the positions of all pages after page 1. >> -- Unfortunately, this query yields an error: "columns book_id, position >> are not unique"/ >> >> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= >> 1; >> >> The query should run without any error, since it does not break the unique >> index. >> > > Uniqueness is checked for each row change, not just at the end of the > transaction. Hence, uniqueness might fail, depending on the order in which > the individual rows are updated. Thank you Richard for your answer. We share the same conclusion. I even tried to decorate the update query with "ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, and circumvent the issue. Our analysis describes an implementation detail. Still, this behavior can not be considered as normal, and closed as "behaves as expected". I still believe that my initial mail is an actual bug report and should be treated as such. I hope it will find an interested ear. I'm unfortunately not familiar enough with the sqlite guts to fix it myself - especially considering the root cause. Messing with relational constraints validation is not an easy task. Regards, Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
That should have been "trigger occur before...", pardon. -Original Message- From: Richard Hipp To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries that lead to the unexpected error: > > -- The `books` and `pages` tables implement a book with several pages. > -- Page ordering is implemented via the `position` column in the pages > table. > -- A unique index makes sure two pages do not share the same position. > CREATE TABLE books ( > id INT PRIMARY KEY > ) > CREATE TABLE pages ( > book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON > UPDATE CASCADE, > position INT > ) > CREATE UNIQUE INDEX pagination ON pages(book_id, position) > > -- Let's populate the tables with a single book and three pages: > INSERT INTO books VALUES (0); > INSERT INTO pages VALUES (0,0); > INSERT INTO pages VALUES (0,1); > INSERT INTO pages VALUES (0,2); > > -- We want to insert a page between the pages at positions 0 and 1. So we > have > -- to increment the positions of all pages after page 1. > -- Unfortunately, this query yields an error: "columns book_id, position > are not unique"/ > > UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= > 1; > > The query should run without any error, since it does not break the unique > index. > Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. > > Thank you for considering this issue. > > Cheers, > Gwendal Roué > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ 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] sqlite bugreport : unique index causes valid updates to fail
Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries that lead to the unexpected error: > > -- The `books` and `pages` tables implement a book with several pages. > -- Page ordering is implemented via the `position` column in the pages > table. > -- A unique index makes sure two pages do not share the same position. > CREATE TABLE books ( > id INT PRIMARY KEY > ) > CREATE TABLE pages ( > book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON > UPDATE CASCADE, > position INT > ) > CREATE UNIQUE INDEX pagination ON pages(book_id, position) > > -- Let's populate the tables with a single book and three pages: > INSERT INTO books VALUES (0); > INSERT INTO pages VALUES (0,0); > INSERT INTO pages VALUES (0,1); > INSERT INTO pages VALUES (0,2); > > -- We want to insert a page between the pages at positions 0 and 1. So we > have > -- to increment the positions of all pages after page 1. > -- Unfortunately, this query yields an error: "columns book_id, position > are not unique"/ > > UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= > 1; > > The query should run without any error, since it does not break the unique > index. > Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. > > Thank you for considering this issue. > > Cheers, > Gwendal Roué > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ 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] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?
The questions you have to ask is Are the rows I'm returning identifiable by a unique id -- typically the row id, but also unique identifiers, like ISBN for books, Employee ID for employees, etc. If you find duplicates of what should be a unique id in a table then its probably a sign the data is bad. (Two books with the same ISBN, two employees with the same Employee ID.) Of course, the other possibility is that the database wasn't normalized and the standard operations (Create, Read, Update, Delete) weren't used in a logical fashion (for instance leaving out a way to update or delete employees and thus making it impossible to rename an employee who's changed their name.) When I search for this author are the books returned normalized against the author's table? That is, is there an identifier shared between the tables that allows one table to be searched in relation to the other? (The relational part of databases.) The other thing is finding external sources to verify against, or performing tests as mentioned by other members of this list. Create a test author. create test books by the test author. do you get only the books you entered for that author? If not, why not? If so, then can you repeat the results? Another thing to look at are your queries. Select [fields] from [table] where [condition] Insert into [table] ([columns]) values ([value for each column]) Delete from [table] where [condition] Update [table] set [field]=[value], [field2]=[value2] ... where [condition] Select [fields] from [table] where [condition] limit [rows to skip],[rows to return] If you have doubts about the accuracy of the tool you're using there are free SQLite Managers out there. And then there's always the last option which is reading the file manually. This last requires a bit more understanding of the database engine itself as you need to be able to identify what type is supposed to be where by the bytes of the file, and would probably have to write a program to do this programmatically instead of trying to do it manually. -Original Message- From: Dwight Harvey To: sqlite-users Sent: Sun, Dec 7, 2014 9:24 pm Subject: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on? I am a student with no Tech or IT background what so ever. I am new to Databases and IT in general. I am taking an accelerated class in database basics and within the last three weeks I just learned what databases were. I know very little and Databases are complex and intimidating. I figured out how to run queries but I don't know if they are correct/accurate, as in what I requested from the 'RUN' results? How do you 'VERIFY' your query results? My instructor wants me to explain how do I KNOW that the records are accurate. Here is an example of what is expected in the assignment... *VERIFICATION: *What is verification? Each time you retrieve data, you should ask yourself, "How do I know I selected the correct data?". For example, if you were asked to pull all records written by an author named Fred Smith, your query might be based on last name equal to Smith. However, if you might get records for someone with the first name of Fred, Mary and Ginger. What would you do to insure you are pulling only Fred? The person who has requested the data will always want assurance from you that you are 100% positive you pulled the correct records. Look at the records returned and always as yourself, did I pull the correct records? How would I verify it? "Capture each query, number of records returned and *explain your validation of the query.*" Example: /** First query 1. List all employees **/ SELECT dbo.Firstname, dbo.Lastname FROM dbo.employees --records returned: 24 *--Validation: I did a quick listing of top 200 records and 4 were returned*. ___ 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] sqlite bugreport : unique index causes valid updates to fail
On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries that lead to the unexpected error: > > -- The `books` and `pages` tables implement a book with several pages. > -- Page ordering is implemented via the `position` column in the pages > table. > -- A unique index makes sure two pages do not share the same position. > CREATE TABLE books ( > id INT PRIMARY KEY > ) > CREATE TABLE pages ( > book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON > UPDATE CASCADE, > position INT > ) > CREATE UNIQUE INDEX pagination ON pages(book_id, position) > > -- Let's populate the tables with a single book and three pages: > INSERT INTO books VALUES (0); > INSERT INTO pages VALUES (0,0); > INSERT INTO pages VALUES (0,1); > INSERT INTO pages VALUES (0,2); > > -- We want to insert a page between the pages at positions 0 and 1. So we > have > -- to increment the positions of all pages after page 1. > -- Unfortunately, this query yields an error: "columns book_id, position > are not unique"/ > > UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= > 1; > > The query should run without any error, since it does not break the unique > index. > Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. > > Thank you for considering this issue. > > Cheers, > Gwendal Roué > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite bugreport : unique index causes valid updates to fail
Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index makes sure two pages do not share the same position. CREATE TABLE books ( id INT PRIMARY KEY ) CREATE TABLE pages ( book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE, position INT ) CREATE UNIQUE INDEX pagination ON pages(book_id, position) -- Let's populate the tables with a single book and three pages: INSERT INTO books VALUES (0); INSERT INTO pages VALUES (0,0); INSERT INTO pages VALUES (0,1); INSERT INTO pages VALUES (0,2); -- We want to insert a page between the pages at positions 0 and 1. So we have -- to increment the positions of all pages after page 1. -- Unfortunately, this query yields an error: "columns book_id, position are not unique"/ UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1; The query should run without any error, since it does not break the unique index. Thank you for considering this issue. Cheers, Gwendal Roué ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite]Basic query
Hi Shinichiro, If opening the database failed with sqlite3_open() != SQLITE_OK, it is probably best to check the return code and error message using sqlite3_errmsg(). It will give a strong hint. Debugging into the sqlite3 code itself never worked for me. Make sure that - the directory the database file redsides in exists - the the user executing the program has the permission to read/write in the directory and the database file This is not really the answer to your question, but you can probably solve your problem without debugging into sqlite. Martin. Am 08.12.2014 10:55, schrieb Shinichiro Yoshioka: Hi, Clemens and Martin, Thank you for your prompt responses. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, I'm using sqlite APIs for calling from C source code directly. which Version of VC++ do you use? I'm using Visual studio express 2010. why do you want to debug into the sqlite.c file? Since opening database API is failed, I'm trying to figure out the cause. The file sqlite.c is just another source file for your compiler; optimizations would occcur only if you set them in your compiler options. Yeah.. I completely agree with you, but I can't find such setting in VC++ property, So I'm asking this query... Thanks, 2014-12-08 18:14 GMT+09:00 Clemens Ladisch : Shinichiro Yoshioka wrote: I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, it is likely that there is a second copy of the SQLite library inside that other database driver. Regards, Clemens ___ 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] [SQLite]Basic query
Hi, Clemens and Martin, Thank you for your prompt responses. >How exactly are you using SQLite in your program? If you are not using >the SQLite C API (sqlite3_* functions) directly, I'm using sqlite APIs for calling from C source code directly. >which Version of VC++ do you use? I'm using Visual studio express 2010. >why do you want to debug into the sqlite.c file? Since opening database API is failed, I'm trying to figure out the cause. >The file sqlite.c is just another source file for your compiler; optimizations would occcur > only if you set them in your compiler options. Yeah.. I completely agree with you, but I can't find such setting in VC++ property, So I'm asking this query... Thanks, 2014-12-08 18:14 GMT+09:00 Clemens Ladisch : > Shinichiro Yoshioka wrote: > > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > > But although the compiling was successfully finished, even if I set break > > point > > on the source code, I can't trace the working line in sqlite3.c > correctly. > > How exactly are you using SQLite in your program? If you are not using > the SQLite C API (sqlite3_* functions) directly, it is likely that there > is a second copy of the SQLite library inside that other database driver. > > > Regards, > Clemens > ___ > 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] [SQLite]Basic query
Hi Shinichiro, which Version of VC++ do you use? As far as I know, older versions do not support debugging source files with more than 65535 lines. Also, why do you want to debug into the sqlite.c file? The file sqlite.c is just another source file for your compiler; optimizations would occcur only if you set them in your compiler options. HTH Martin Am 08.12.2014 10:10, schrieb Shinichiro Yoshioka: Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. I suspected that the sqlite3.c was optimized, but there is no opiton like that. Always the active line doesn't match to source code in debug mode. Is the code optimized automatically? if so, how can I deactivate it? If anyone knows above phenomenon, then could you please answer me?? Thanks, ___ 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] [SQLite]Basic query
Shinichiro Yoshioka wrote: > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > But although the compiling was successfully finished, even if I set break > point > on the source code, I can't trace the working line in sqlite3.c correctly. How exactly are you using SQLite in your program? If you are not using the SQLite C API (sqlite3_* functions) directly, it is likely that there is a second copy of the SQLite library inside that other database driver. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SQLite]Basic query
Hi, I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. But although the compiling was successfully finished, even if I set break point on the source code, I can't trace the working line in sqlite3.c correctly. I suspected that the sqlite3.c was optimized, but there is no opiton like that. Always the active line doesn't match to source code in debug mode. Is the code optimized automatically? if so, how can I deactivate it? If anyone knows above phenomenon, then could you please answer me?? Thanks, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users