Multiple authors with same authorid

[wizzy99]

To help with identifying and fixing issues, please include as much information as possible, including:

LazyLibrarian version number (at the bottom of config page)

71990e1b336a81f587f03a69631dfb94e3bdd3fd

Operating system used (windows, mac, linux, NAS type)

linux docker

Interface in use (default, bookstrap)

bookstrap

I've noticed a couple of authors with more than one entry with the same ID in the authors list. Shouldn't authorID be a unique entry, and suggestions for cleaning it up? I found this when seeing the same author listed twice on the authors tab with identical information except for the name.

sqlite> select authorname,count() from authors group by authorid having count() > 1; Henry James|2 Henry, 1843-1916 James|2

[philborman]

Authorid is unique or at least has been since a couple of years ago, table definition is CREATE TABLE authors (AuthorID TEXT UNIQUE, AuthorName TEXT UNIQUE, AuthorImg TEXT, AuthorLink TEXT, DateAdded TEXT, Status TEXT, LastBook TEXT, LastBookImg TEXT, LastLink Text, LastDate TEXT, HaveBooks INTEGER, TotalBooks INTEGER, AuthorBorn TEXT, AuthorDeath TEXT, UnignoredBooks INTEGER, Manual TEXT, GRfollow TEXT, LastBookID TEXT);

Not sure how this can occur, should throw a "unique constraint failed" error, but it should be fixable just by deleting one of the entries... sqlite3> delete from authors where authorname='Henry, 1843-1916 James'; Is your original lazylibrarian database older than Jujy 2016 by any chance, or is your table definition not correct for some reason? sqlite3> .schema authors

[wizzy99]

Looks like the schema isn't correct, no unique constraint. I can't remember when I first set up LL.

sqlite> .schema authors CREATE TABLE authors (AuthorID TEXT, AuthorName TEXT UNIQUE, AuthorImg TEXT, AuthorLink TEXT, DateAdded TEXT, Status TEXT, LastBook TEXT, LastLink Text, LastDate TEXT, HaveBooks INTEGER, TotalBooks INTEGER, AuthorBorn TEXT, AuthorDeath TEXT, UnignoredBooks INTEGER, LastBookImg TEXT, Manual TEXT, GRfollow TEXT, LastBookID TEXT); CREATE INDEX authors_index_status ON authors(Status);

[philborman]

That's a bummer. sqlite3 doesn't allow you to add constraints after the table is created. The only way is to create a new table, copy the old data over, rename the table. Remove all the duplicates first as we discussed before, then do something like this ... (should be fairly quick)

DROP TABLE IF EXISTS temp_table ALTER TABLE authors RENAME TO temp_table

CREATE TABLE authors (AuthorID TEXT UNIQUE, AuthorName TEXT UNIQUE, AuthorImg TEXT, AuthorLink TEXT, DateAdded TEXT, Status TEXT, LastBook TEXT, LastBookImg TEXT, LastLink Text, LastDate TEXT, HaveBooks INTEGER, TotalBooks INTEGER, AuthorBorn TEXT, AuthorDeath TEXT, UnignoredBooks INTEGER, Manual TEXT, GRfollow TEXT, LastBookID TEXT)

INSERT INTO authors SELECT AuthorID, AuthorName, AuthorImg, AuthorLink, DateAdded, Status, LastBook, LastBookImg, LastLink, LastDate, HaveBooks, TotalBooks, AuthorBorn, AuthorDeath, UnignoredBooks, Manual, GRfollow, LastBookID FROM temp_table

DROP TABLE temp_table CREATE INDEX authors_index_status ON authors(Status)

[wizzy99]

thanks, ran that and it seems to have moved the data around just fine.

[philborman]

Good. Do you mind doing it again though, just noticed a missing bit. The "CREATE" should be

CREATE TABLE authors (AuthorID TEXT UNIQUE, AuthorName TEXT UNIQUE, AuthorImg TEXT, AuthorLink TEXT, DateAdded TEXT, Status TEXT, LastBook TEXT, LastBookImg TEXT, LastLink TEXT, LastDate TEXT, HaveBooks INTEGER DEFAULT 0, TotalBooks INTEGER DEFAULT 0, AuthorBorn TEXT, AuthorDeath TEXT, UnignoredBooks INTEGER DEFAULT 0, Manual TEXT, GRfollow TEXT, LastBookID TEXT)

The first version missed the DEFAULT 0 for the integers. Sorry about that.

[wizzy99]

reran. thanks for the help.