To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, Dec 8, 2014 12:07 pm
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
> Le 8 déc. 2014 à 17:21, Simon Slavin <slav...@bigfraud.org> a écrit :
>
>> Why n
mania.org>
To: sqlite-users <sqlite-users@sqlite.org>
Sent: Tue, Dec 9, 2014 10:38 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
On Mon, 08 Dec 2014 22:01:15 +0700
Dan Kennedy <danielk1...@gmail.com> wrote:
> On 12/08/2014 09:55 PM, Nico
On Mon, 08 Dec 2014 15:48:41 +0200
RSmith wrote:
> > 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
>
On Mon, 08 Dec 2014 22:01:15 +0700
Dan Kennedy wrote:
> 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
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
> 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.
>
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
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
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.
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
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
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
...@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
> 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.
>>
Cancel that, apparently that only updates the last record...
-Original Message-
From: John McKown <john.archie.mck...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, Dec 8, 2014 9:18 am
Subject: Re: [sqlite] sqlite bugreport :
: 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 <mlal...@outsitenetworks.com>
wrote:
> I am like you, Gwendal, in that I d
-
From: RSmith <rsm...@rsweb.co.za>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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 mail
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
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,
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
> 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
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`
eneral Discussion of SQLite Database <sqlite-users@sqlite.org>
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
> 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
ion of SQLite Database <sqlite-users@sqlite.org>
> 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é <g...@pierlis.com> wrote:
>
>> Hi,
>
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
Original Message-
> From: Richard Hipp <d...@sqlite.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Mon, Dec 8, 2014 8:14 am
> Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
> fail
>
>
>
> Le 8 déc. 2014 à 14:14, Richard Hipp a écrit :
>
> On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote:
>
>> Hi,
>>
>> Unique indexes make some valid update queries fail.
>>
>> Please find below the SQL queries that lead to the unexpected error:
>>
>> --
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é <g...@pierlis.com> wrote:
> Hi,
>
> Unique indexes make some valid update queries fail.
>
> Please find below the SQL queries
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
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
31 matches
Mail list logo