Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
I just thought of what may be a simpler solution.
I'm assuming that there is a certain limit to the length of the books
(positions can be safely assumed to never exceed say, 100,000)
So what can be done is
update page set position=position + 10 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position>10;
This will work around the unique contraint and seems simpler than dropping it
everytime you want to insert a page.
-Original Message-
From: Gwendal Roué
To: General Discussion of SQLite Database
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 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
I just thought of what may be a simpler solution.
I'm assuming that there is a certain limit to the length of the books
(positions can be safely assumed to never exceed say, 100,000)
So what can be done is
update page set position=position + 10 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position>10;
This will work around the unique contraint and seems simpler than dropping it
everytime you want ot insert a page.
-Original Message-
From: James K. Lowden
To: sqlite-users
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 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 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.
It's not *deferred* constraint checking. It's constraint checking.
Best to honor the transaction first.
Rather than adding to the syntax, perhaps a pragma could cause updates
to happen in a transaction:
1. Create a temporary table to hold the after-image of the updated
rows.
2. begin transaction
3 . Delete the rows from the target table.
3. Insert the updated rows from the temporary table.
4. commit
5. drop temporary table.
Of course there are more efficient answers available deeper in the
update logic, affecting only the partcular columns at the time the
constraint is enforced. I guess they all involve deleting the
prior set from the index and inserting the new one.
--jkl
___
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
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
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
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
That should have been "trigger occur before...", pardon. -Original Message- From: Richard Hipp To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries that lead to the unexpected error: > > -- The `books` and `pages` tables implement a book with several pages. > -- Page ordering is implemented via the `position` column in the pages > table. > -- A unique index makes sure two pages do not share the same position. > CREATE TABLE books ( > id INT PRIMARY KEY > ) > CREATE TABLE pages ( > book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON > UPDATE CASCADE, > position INT > ) > CREATE UNIQUE INDEX pagination ON pages(book_id, position) > > -- Let's populate the tables with a single book and three pages: > INSERT INTO books VALUES (0); > INSERT INTO pages VALUES (0,0); > INSERT INTO pages VALUES (0,1); > INSERT INTO pages VALUES (0,2); > > -- We want to insert a page between the pages at positions 0 and 1. So we > have > -- to increment the positions of all pages after page 1. > -- Unfortunately, this query yields an error: "columns book_id, position > are not unique"/ > > UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= > 1; > > The query should run without any error, since it does not break the unique > index. > Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. > > Thank you for considering this issue. > > Cheers, > Gwendal Roué > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Try having your cascade occur before the row is created, updated or deleted. http://www.sqlite.org/lang_createtrigger.html -Original Message- From: Richard Hipp To: General Discussion of SQLite Database Sent: Mon, Dec 8, 2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries that lead to the unexpected error: > > -- The `books` and `pages` tables implement a book with several pages. > -- Page ordering is implemented via the `position` column in the pages > table. > -- A unique index makes sure two pages do not share the same position. > CREATE TABLE books ( > id INT PRIMARY KEY > ) > CREATE TABLE pages ( > book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON > UPDATE CASCADE, > position INT > ) > CREATE UNIQUE INDEX pagination ON pages(book_id, position) > > -- Let's populate the tables with a single book and three pages: > INSERT INTO books VALUES (0); > INSERT INTO pages VALUES (0,0); > INSERT INTO pages VALUES (0,1); > INSERT INTO pages VALUES (0,2); > > -- We want to insert a page between the pages at positions 0 and 1. So we > have > -- to increment the positions of all pages after page 1. > -- Unfortunately, this query yields an error: "columns book_id, position > are not unique"/ > > UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= > 1; > > The query should run without any error, since it does not break the unique > index. > Uniqueness is checked for each row change, not just at the end of the transaction. Hence, uniqueness might fail, depending on the order in which the individual rows are updated. > > Thank you for considering this issue. > > Cheers, > Gwendal Roué > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?
The questions you have to ask is Are the rows I'm returning identifiable by a unique id -- typically the row id, but also unique identifiers, like ISBN for books, Employee ID for employees, etc. If you find duplicates of what should be a unique id in a table then its probably a sign the data is bad. (Two books with the same ISBN, two employees with the same Employee ID.) Of course, the other possibility is that the database wasn't normalized and the standard operations (Create, Read, Update, Delete) weren't used in a logical fashion (for instance leaving out a way to update or delete employees and thus making it impossible to rename an employee who's changed their name.) When I search for this author are the books returned normalized against the author's table? That is, is there an identifier shared between the tables that allows one table to be searched in relation to the other? (The relational part of databases.) The other thing is finding external sources to verify against, or performing tests as mentioned by other members of this list. Create a test author. create test books by the test author. do you get only the books you entered for that author? If not, why not? If so, then can you repeat the results? Another thing to look at are your queries. Select [fields] from [table] where [condition] Insert into [table] ([columns]) values ([value for each column]) Delete from [table] where [condition] Update [table] set [field]=[value], [field2]=[value2] ... where [condition] Select [fields] from [table] where [condition] limit [rows to skip],[rows to return] If you have doubts about the accuracy of the tool you're using there are free SQLite Managers out there. And then there's always the last option which is reading the file manually. This last requires a bit more understanding of the database engine itself as you need to be able to identify what type is supposed to be where by the bytes of the file, and would probably have to write a program to do this programmatically instead of trying to do it manually. -Original Message- From: Dwight Harvey To: sqlite-users Sent: Sun, Dec 7, 2014 9:24 pm Subject: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on? I am a student with no Tech or IT background what so ever. I am new to Databases and IT in general. I am taking an accelerated class in database basics and within the last three weeks I just learned what databases were. I know very little and Databases are complex and intimidating. I figured out how to run queries but I don't know if they are correct/accurate, as in what I requested from the 'RUN' results? How do you 'VERIFY' your query results? My instructor wants me to explain how do I KNOW that the records are accurate. Here is an example of what is expected in the assignment... *VERIFICATION: *What is verification? Each time you retrieve data, you should ask yourself, "How do I know I selected the correct data?". For example, if you were asked to pull all records written by an author named Fred Smith, your query might be based on last name equal to Smith. However, if you might get records for someone with the first name of Fred, Mary and Ginger. What would you do to insure you are pulling only Fred? The person who has requested the data will always want assurance from you that you are 100% positive you pulled the correct records. Look at the records returned and always as yourself, did I pull the correct records? How would I verify it? "Capture each query, number of records returned and *explain your validation of the query.*" Example: /** First query 1. List all employees **/ SELECT dbo.Firstname, dbo.Lastname FROM dbo.employees --records returned: 24 *--Validation: I did a quick listing of top 200 records and 4 were returned*. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search for text in all tables
Dominique,
Why not get a column count (datareader.fieldcount (C#) or
cursor.getColumnCount() (Java/Android))?
>From there you should be able to simply do a
try {
str = getString(columnIndex);
checkValue(str);
}
catch(Exception e) {
// wasn't a string or the check (and replace) failed
// you may want to catch different exceptions
}
-Original Message-
From: Dominique Devienne
To: General Discussion of SQLite Database
Sent: Thu, Dec 4, 2014 6:57 am
Subject: Re: [sqlite] Search for text in all tables
On Thu, Dec 4, 2014 at 11:45 AM, Baruch Burstein
wrote:
> On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <
> > If you are a coder then it is a relatively straight forward process
> > Loop through each table
> >Loop through each column
>
> This is the part I am having trouble with. I can loop through tables using
> sqlite3_master, but how do I loop through columns? Parse the schema?
http://www.sqlite.org/pragma.html#pragma_table_info --DD
___
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
