Synching read progress between multiple copies of the same book

[WarDrake]

if you find that you have multiple copies of a book for some reason, be it changed paths, partial restores or whatever
you can copy the progress from the old books to the new ones as long as the new one doesn't have any progress information (you can remove it)

you'll need to open the ABS database, the file is called absdatabase.sqlite in there you'll execute 2 queries

INSERT INTO mediaProgresses (id, mediaItemId, mediaItemType, duration, currentTime, isFinished, hideFromContinueListening, ebookLocation, ebookProgress, finishedAt, extraData, createdAt, updatedAt, userId)
SELECT
substr(hex(randomblob(4)), 1, 8) || '-' ||
substr(hex(randomblob(2)), 1, 4) || '-' ||
'4' || substr(hex(randomblob(2)), 1, 3) || '-' ||
'a' || substr(hex(randomblob(2)), 1, 3) || '-' ||
substr(hex(randomblob(6)), 1, 12) AS id,
b2.id AS mediaItemId, mp.mediaItemType, mp.duration, mp.currentTime, mp.isFinished, mp.hideFromContinueListening, mp.ebookLocation, mp.ebookProgress, mp.finishedAt, mp.extraData, mp.createdAt, mp.updatedAt, mp.userId
FROM books AS b1
JOIN books AS b2 ON b1.asin = b2.asin AND b1.id <> b2.id
JOIN mediaProgresses AS mp ON b1.id = mp.mediaItemId
WHERE b2.id NOT IN (SELECT mediaItemId FROM mediaProgresses);

This query will create copies of reading progress between books as long as they have the same "asin" value, you can edit that to be "iban" if you prefer

this will create the necesary records for progress in the db, however it won't tie them to the proper items yet

UPDATE mediaProgresses
SET extraData = json_set(extraData, '$.libraryItemId', (SELECT libraryItems.id FROM libraryItems WHERE libraryItems.mediaId = mediaProgresses.mediaItemId))
WHERE extraData IS NOT NULL;

This query will update the library item data with the correct value in the json in extraData, once you run this query, all those books with the same 'asin' will have synced their progress

This is retroactive only, if you listen to something some more. the progress won't stay synched.

in my particular case this was needed because I lost all my content in a drive failure, and am reimporting with different paths for better organization, the new books are recognized as different books and don't have any progress attached to them.