[Bug]: Relinking with "Fix Unlinked Entries" breaks if new entry exists (SQL)

[CyanVoxel]

Checklist

• I am using an up-to-date version.
• I have read the documentation.
• I have searched existing issues.

TagStudio Version

main branch (v9.5.0)

Operating System & Version

macOS 15.1.1

Description

This is an extension of the issue in #652 and its respective fix in #653.

Relinking unlinked files does not function if a new file entry with the new path already exists.

In v9.4 (JSON) there wasn't a hard unique constraint for file paths. While that caused issues of its own, it allowed for the relinking system to occur in-place inside the library in all circumstances. This includes the case where a file is unlinked, rescanned and added as a new entry in its new location, while having the old entry attempt to be relinked. As the old entry updates to the new path this hits the unique constraint inside the SQL database as it doesn't allow two entries to share the same path. If there isn't a duplicate entry in the new path, the old entry seems to relink correctly.

As a side note I'd like to mention that the "Fix Unlinked Entries" is still technically behind the 9.4 version, as that was updated in #204 in order to fix #179. This change was centered around duplicates entries however, which I would rather continue to avoid in the SQL version. I feel the unique constraint is necessary and duplicate entries (e.g. entries pointing to the same file) should be avoided at all costs.

Expected Behavior

Under the following circumstances,

1. A file entry points to "library/old_location/my_file.ext"
2. "my_file.ext" is moved to "library/new_location/my_file.ext"
3. TagStudio refreshes/rescans the library folder, creating a new file entry for "library/new_location/my_file.ext"

When relinking the original "library/old_location/my_file.ext" entry to point to "library/new_location/my_file.ext", both entries should be consolidated (aka "mirrored") into a single combined entry referencing the new location. This should be the current behavior in v9.4.

Steps to Reproduce

Have a TagStudio library with a setup similar to the following:

1. Have a file entry that points to "library/old_location/my_file.ext"
2. Move "my_file.ext" to "library/new_location/my_file.ext"
3. Select "File -> Refresh Directories" (a new file entry for "library/new_location/my_file.ext" will be created)
4. Attempt to "Search & Relink" from "Tools -> Fix Unlinked Entries" and observe it failing (error, crash, hang, etc.)

Logs

Context: "prune_sidecar_files.py" was the name of the file inside the library that was unlinked, not an internal TagStudio file. It seems like it was already re-added by the library due to refreshing, but can not relink the old entry while a new entry with that same path exists due to the unique constraint. "Scripts/" is also the new directory.

Exception has occurred: IntegrityError (note: full exception trace is shown but execution is paused at: run)

(sqlite3.IntegrityError) UNIQUE constraint failed: entries.path
[SQL: UPDATE entries SET path=? WHERE entries.id = ?]
[parameters: ('Scripts/prune_sidecar_files.py', 100852)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
sqlite3.IntegrityError: UNIQUE constraint failed: entries.path

The above exception was the direct cause of the following exception:

  File "/Users/cyanvoxel/Files/GitHub/TagStudio/tagstudio/src/core/library/alchemy/library.py", line 657, in update_entry_path
    session.execute(update_stmt)
  File "/Users/cyanvoxel/Files/GitHub/TagStudio/tagstudio/src/core/utils/missing_files.py", line 57, in fix_missing_files
    self.library.update_entry_path(entry.id, item_matches[0])
  File "/Users/cyanvoxel/Files/GitHub/TagStudio/tagstudio/src/qt/helpers/function_iterator.py", line 19, in run
    for i in self.iterable():
             ^^^^^^^^^^^^^^^
  File "/Users/cyanvoxel/Files/GitHub/TagStudio/tagstudio/src/qt/widgets/progress.py", line 61, in <lambda>
    r = CustomRunnable(lambda: iterator.run())
                               ^^^^^^^^^^^^^^
  File "/Users/cyanvoxel/Files/GitHub/TagStudio/tagstudio/src/qt/helpers/custom_runnable.py", line 18, in run (Current frame)
    self.function()
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: entries.path
[SQL: UPDATE entries SET path=? WHERE entries.id = ?]
[parameters: ('Scripts/prune_sidecar_files.py', 100852)]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

[mashed5894]

I can start working on this one.

[CyanVoxel]

Fixed in #720