Plugin *Kindle Utilities* was no longer working. At least, the **read percentage** column was not updating. Data previous to latest Calibre install were still there.
Asked what I might do in [Calibre forum](
https://www.mobileread.com/forums/sh...postcount=3203) but no effective suggestions.
Noticed that my once *portable* calibre had a some stage become non-portable: settings files had migrated to [AppData](file://c:/Users/tom/AppData/Roaming/calibre).
I did read somewhere that a portable install can be updated by using general installer and pointing it at the portable dir. It will know what to do. Maybe not.
Blitzed the install and did a fresh install. The existing [metadata.db](file://d:/docs/language/ebooks/calibre-library/metadata.db)
contained references to old plugin data. Didn't want this so removed it and got Calibre to crawl through existing [calibre-library](file://d:/docs/language/ebooks/calibre-library) and regenerate database. This took several hours.
I've lost all the settings etc that took many hours to configure, including all plugins. But most of that was just faffing around. However, installed *Kobo Utilities* and it works again.
But, the reason this all started, *Kobo Utitilities* **percentage read** function was useful and I was disappointed to have lost the data.
A day later, browsed the old database backup using [DB Browser for SQLite](
https://sqlitebrowser.org/) and realised all the data was there: what I needed at least. Though book ids had changed from old to new database, the titles would be enough to get at the ids in the new database.
With help from ChatGPT:
### 1. Got **percent read** from old db incl **title** and **author**
```sql
SELECT books.title,books.author_sort,custom_column_3.valu e,custom_column_3.id
FROM books
JOIN custom_column_3 ON books.id = custom_column_3.book;
```
This returned e.g.
```tsv
Kitchen Confidential Deluxe Edition: Adventures in the Culinary Underbelly Bourdain, Anthony 100 4
Big Trouble Barry, Dave 100 6
Black Coffee Christie, Agatha 100 9
The Bottle Factory Outing Bainbridge, Beryl 100 36
Mother Land Theroux, Paul 100 46
Easily Distracted Coogan, Steve 100 48
Marching Powder Young, Rusty 100 50
```
The final value was unnecessary. As was the author.
### 2. Got book id from new db via title from above query. The results also include the **percentage read** from previous results, hard-coded into query.
```
SELECT books.id, books.title, authors.name AS author,
CASE books.title
WHEN 'Kitchen Confidential Deluxe Edition: Adventures in the Culinary Underbelly' THEN 4
WHEN 'Big Trouble' THEN 6
WHEN 'Black Coffee' THEN 9
WHEN 'The Bottle Factory Outing' THEN 36
WHEN 'Mother Land' THEN 46
END AS num
FROM books
JOIN books_authors_link ON books.id = books_authors_link.book
JOIN authors ON books_authors_link.author = authors.id
WHERE books.title IN (
'Kitchen Confidential Deluxe Edition: Adventures in the Culinary Underbelly',
'Big Trouble',
'Black Coffee',
'The Bottle Factory Outing',
'Mother Land'
);
```
Results are like:
```tsv
97 Kitchen Confidential Deluxe Edition: Adventures in the Culinary Underbelly Anthony Bourdain 100
103 The Lord of the Rings 1 - The Fellowship of the Ring J. R. R. Tolkien 100
145 Five Little Pigs Agatha Christie 100
192 The Lord of the Rings 2 - The Two Towers J. R. R. Tolkien 100
```
Again, the author was unnecessary inasmuch not needed in subsequent queries; although it was useful seeing who the author of a particular entry was.
The results contained duplicate entries, that is, duplicate book ids. Seems it returned same book id where book had multiple authors.
### 3. Removed duplicate ids.
First print duplicate entries (i.e. with same ids)
awk -F'\t' '{count[$1]++; lines[$1] = lines[$1] ORS $0} END {for (id in count) if (count[id] > 1) print lines[id]}' yourfile.tsv
Remove duplicate entries (with same ids) retaining first found
awk -F'\t' '!seen[$1]++' yourfile.tsv
### 4. Test that returned ids (from step 2) are actually the book we want
```sql
SELECT books.id, books.title
FROM books
WHERE books.id = 97; -- Replace ? with the actual ID value
```
Result e.g.:
97 Kitchen Confidential Deluxe Edition: Adventures in the Culinary Underbelly
### 5. finnese results from step 2 to just **percentage read** and **book id**, e.g.
```
103 100
145 100
192 100
231 100
```
### 6. Turn this list into SQL statements
```sql
INSERT INTO custom_column_2 (book, value)
SELECT 6441, 100 WHERE NOT EXISTS (SELECT 1 FROM custom_column_2 WHERE book = 6441);
INSERT INTO custom_column_2 (book, value)
SELECT 97, 100 WHERE NOT EXISTS (SELECT 1 FROM custom_column_2 WHERE book = 97);
```
### 7. Make a backup of Calibre db in case something goes wrong.
### 8. Open Calibre db in DB Browser and run SQL statements from step 5.
### 9. Open Calibre and check results.
Everything seems good. Ran Library check. Nothing wrong found. However, old db is still around just in case.