Multiple table rows for book in custom_column_x

[Man Eating Duck]

Hi! I'm writing a script which among other thing extracts metadata in custom columns directly from metadata.db. I know there are tools like calibredb that can do this, but I'm rolling my own as a practical excercise in interaction with SQLite from scripts.

I noticed something curious: whenever the value in a single-value custom column is changed (I observed this with integer and date columns), a new row is inserted in addition to the old value, which is not removed. Then you end up with multiple rows for the same book record instead of just one, with only db metadata (rowid) to specify the order of these. When I extract them I have to do an additional subquery for max(rowid) to get the current value. The id column in the custom_column_x table is always null in these cases.

calibre obviously handles this in some manner (I haven't been able to find the relevant code in source), but to me it seems a bit messy designwise to have multiple rows and rely (explicitly or implicitly) on db metadata features like rowid. It might also be a potential source for non-obvious bugs down the road. Maybe it is something which is left after support for multiple values with _link tables were added?

Based on the maturity of calibre I have a suspicion that this is intended behaviour for some reason I haven't been able to discern, in that case I'd be happy to know why

[chaley]

I don't see this behavior.

My experiment:

1. Look at the db with SQLiteSpy. Find a float custom column. Image is below.
   
2. Change a value in calibre, in this case 9.01 becomes 9.02
3. Look at the db again. The value has changed and there is no duplicated row.
   

What are you doing that is different?

I also note that the table has a UNIQUE constraint on the book_id, so the db itself should toss exceptions in the case you describe.

CREATE TABLE custom_column_16(
                    id    INTEGER PRIMARY KEY AUTOINCREMENT,
                    book  INTEGER,
                    value REAL NOT NULL ,
                    UNIQUE(book));

CREATE INDEX custom_column_16_idx ON custom_column_16 (book);

CREATE TRIGGER fkc_insert_custom_column_16
                        BEFORE INSERT ON custom_column_16
                        BEGIN
                            SELECT CASE
                                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
                                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
                            END;
                        END;

CREATE TRIGGER fkc_update_custom_column_16
                        BEFORE UPDATE OF book ON custom_column_16
                        BEGIN
                            SELECT CASE
                                WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
                                THEN RAISE(ABORT, 'Foreign key violation: book not in books')
                            END;
                        END;

[Man Eating Duck]

Originally Posted by chaley

I don't see this behavior.

Hmmm. Turns out that when I create a new library I don't either. It seems that the earliest created custom columns didn't feature such niceties as constraints or even autoincrement, here is the create statement for my first one (Words). It was probably created in 2010, my library is a bit old:

CREATE TABLE custom_column_1 (id INTEGER, book INTEGER, value int (10,10));

When I copy the problem book records to the new library (with the create custom columns option), the custom columns are created similarly to the one you show. The same happens when I copy the structure. So, the solution to my problem is simple; I just create a new library and copy all my books there.

It probably stems from the early versions of the custom column creation code. Since calibre still handles it just fine it is probably nothing to worry about, my case is probably an edge case, to say the least

Incidentally I wasn't aware of SQLiteSpy, it's by far the best free SQLite manager I've seen. Thanks!

[kovidgoyal]

It's been many years since the first version of custom column code was written, but the current code most definitely creates uniqueness constraints, as you can see in lines 885 onwards in db/backend.py

I suggest you create a new custom column, copy over the values using metadata search and replace and delete the old one.

[Man Eating Duck]

Originally Posted by kovidgoyal

It's been many years since the first version of custom column code was written, but the current code most definitely creates uniqueness constraints, as you can see in lines 885 onwards in db/backend.py

I suggest you create a new custom column, copy over the values using metadata search and replace and delete the old one.

That right, and as I said the old library never gave me any problems during regular use either. I have quite a few custom columns which makes manual duplication a tedious task, but luckily my library is not enormous, so copying to a new library is already finished.

Thanks to you both!