Database implications when moving, renaming and organizing files...

[emigrating]

I'm about to set out on a reorg of my audiobook and podcast files, but I really, REALLY, need to keep as much of the listened to history intact.

Since there doesn't appear to be a built-in way of doing this, I figured I would have to take down the server and edit the slqite file by a script, however, WHAT exactly do I have to edit?

William Gibson's book Virtual Light for example is listed with filename in at least two tables, books AND libraryItems.

In books the actual filename is referenced in the audioFiles column as
[{"index":1,"ino":"2322","metadata":{"filename":"William Gibson - Bridge 1 - Virtual Light.mp3","ext":".mp3","path":"/others/William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light/William Gibson - Bridge 1 - Virtual Light.mp3","relPath":"William Gibson - Bridge 1 - Virtual Light.mp3","size":130008660,"mtimeMs":1702980338586,"ctimeMs":1731406308325,"birthtimeMs":0},"addedAt":1731406322289,"updatedAt":1731406322289,"trackNumFromMeta":1,"discNumFromMeta":null,"trackNumFromFilename":1,"discNumFromFilename":null,"manuallyVerified":false,"exclude":false,"error":null,"format":"MP2/3 (MPEG audio layer 2/3)","duration":32493.668,"bitRate":32000,"language":null,"codec":"mp3","timeBase":"1/14112000","channels":1,"channelLayout":"mono","chapters":[],"embeddedCoverArt":"mjpeg","metaTags":{"tagAlbum":"William Gibson - Bridge 1 - Virtual Light","tagArtist":"William Gibson","tagGenre":"Audiobook","tagTitle":"Bridge 1 - Virtual Light","tagTrack":"1","tagDate":"1993","tagComment":"A William Gibson trilogy."},"mimeType":"audio/mpeg"}]

Looks simple enough, just do a

UPDATE books
SET audioFiles = REPLACE(audioFiles, '/others/William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light', '/William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light')
WHERE audioFiles LIKE '%/others/William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light%';

But then we also have the row in libraryItems. Where the filename is referenced in path, relPath and libraryItems.

Path
/others/William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light

relPath
William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light

libraryFiles
[{"ino":"2322","metadata":{"filename":"William Gibson - Bridge 1 - Virtual Light.mp3","ext":".mp3","path":"/others/William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light/William Gibson - Bridge 1 - Virtual Light.mp3","relPath":"William Gibson - Bridge 1 - Virtual Light.mp3","size":130008660,"mtimeMs":1702980338586,"ctimeMs":1731406308325,"birthtimeMs":0},"isSupplementary":null,"addedAt":1731406321285,"updatedAt":1731406321285,"fileType":"audio"},{"ino":"2326","metadata":{"filename":"cover - William Gibson - Bridge 1 - Virtual Light.jpg","ext":".jpg","path":"/others/William Gibson/Bridge Trilogy/William Gibson-Bridge-#1-Virtual Light/cover - William Gibson - Bridge 1 - Virtual Light.jpg","relPath":"cover - William Gibson - Bridge 1 - Virtual Light.jpg","size":31541,"mtimeMs":1702979968821,"ctimeMs":1731406308325,"birthtimeMs":0},"isSupplementary":null,"addedAt":1731406321285,"updatedAt":1731406321285,"fileType":"image"}]

I assume I have to change these as well, but how? Notice the path and relPath, how the relPath already looks like how it SHOULD look once I have organized this particular file. The library in question here is named audiobooks, and includes both /audiobooks and /others folders, so the discrepency here may simply be because the current path is in the top-level 'others' folder.

But what about the libraryFiles? Sure, I can run a similar query as with the books table, but is that then it? Do I need to adjust other things elsewhere? and why is it stored like this? Seems like it would have been much better to store the filepath in a single place (say libraryItems) and reference that from books rather than storing a weird json like formatted path in several places... Not to mention it should really remember your existing history against a matched title even if you update the files. If it is read at some point, as a mono book in 64kbps, it should still be listed as read if you replace it for a newly released version in dolby atmos with mood sounds etc.. but that's a different topic I guess.

I've only just started digging into this, so if anyone has done a similar thing earlier or have some ideas please chime in.

Perhaps I'm looking at this all completely wrong, in which case please tell me to get a clue and grow a brain :)

[nichwall]

Does the file watcher work for your setup? It typically doesn't work on network drives or through Docker on Windows for example.

If so, as long as you are moving files/folders to preserve the inode, ABS should detect moves within the same library using the watcher.

I believe those are the only tables you need to update, but not 100% on that without sitting down and going through it.

The existing data structure is due to originally being stored in a flat file system before migrating to SQLite, as well as new things being added like podcasts which made reuse of existing structures more complicated. The database and API updates are ongoing to simplify this, but there is no estimate on when that will be completed.

[nichwall]

Also if the watcher doesn't work, you could also move files and then scan the library. That would reduce the chances of accidentally having the watcher pick up a file if you moved it to a higher directory, causing all books beneath it to be grouped incorrectly.

If you have the ISBN or ASIN set up, you can use something like #2202 to sync progress after moving everything too.

There are some tools that people have written to move books around according to ABS metadata, but I cannot speak to how well they work or if they preserve user-specific data like progress.

For example, BadaBoomBooks, https://github.com/austinsr1/ab_mover, or https://github.com/jeeftor/audiobook-organizer