Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

[Simon Slavin]

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

[Igor Tandetnik]

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

[Keith Medcalf]

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?

[David Barrett]

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?

[Keith Medcalf]

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

[Igor Tandetnik]

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

[Keith Medcalf]

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

[Keith Medcalf]

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?

[Roger Binns]

-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]Ba​sic query

[Shinichiro Yoshioka]

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?

[Max Vlasov]

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

[Igor Tandetnik]

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

[Luuk]

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

[Venkat Murty]

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

[jose isaias cabrera]

"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

[Igor Tandetnik]

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

[Luuk]

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

[Venkat Murty]

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

[Drago, William @ CSG - NARDAEAST]

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

[Richard Hipp]

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

[Stephan Beal]

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

[Stephan Beal]

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

[Drago, William @ CSG - NARDAEAST]

> -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

[Roger Binns]

-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

[jose isaias cabrera]

"Richard Hipp" wrote...

On Mon, Dec 8, 2014 at 1:22 PM, jose isaias cabrera 
!@#$%^&*()_+=-{}\|[]'";:?/.,, 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

[Richard Hipp]

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

[jose isaias cabrera]

"Stephan Beal" wrote...

On Mon, Dec 8, 2014 at 6:32 PM, jose isaias cabrera 
!@#$%^&*()_+=-{}\|[]'";:?/.,, 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

[Stephan Beal]

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

[jose isaias cabrera]

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]Ba​sic query

[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

Re: [sqlite] [SQLite]Ba​sic query

[Scott Robison]

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

[Nico Williams]

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

[Gwendal Roué]

> 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

[Simon Slavin]

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

[Gwendal Roué]

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

[Dan Kennedy]

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?

[Roger Binns]

-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

[Nico Williams]

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

[John McKown]

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

[Luuk]

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

[Marc L. Allen]

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

[Gwendal Roué]

> 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

[J T]

 

 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

[Marc L. Allen]

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

[J T]

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

[John McKown]

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

[Marc L. Allen]

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

[RSmith]

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

[Gwendal Roué]

> 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

[RSmith]

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

[J T]

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

[Gwendal Roué]

> 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

[Gwendal Roué]

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

[Simon Slavin]

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

[Gwendal Roué]

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

[Gwendal Roué]

> 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

[J T]

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?

[J T]

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

[Richard Hipp]

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

[Gwendal Roué]

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]Ba​sic query

[Martin Engelschalk]

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]Ba​sic query

[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

Re: [sqlite] [SQLite]Ba​sic query

[Martin Engelschalk]

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]Ba​sic query

[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] [SQLite]Ba​sic query

[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