The update script is certainly the problem.
When it creates the new FileLookup table and inserts the entries from the File table, it does not copy over the ID column, causing all entries to get new IDs which is why they do not match.
The code is:
/* Build the path lookup table */
INSERT INTO "FileLookup" ("Path", "PrefixID", "BlocksetID", "MetadataID")
SELECT
SUBSTR("Path", LENGTH("ParentFolder") + 1) AS "Path",
"ID" AS "PrefixID",
"BlocksetID",
"MetadataID"
FROM
(SELECT "Path", "BlocksetID", "MetadataID",
CASE SUBSTR("Path", LENGTH("Path")) WHEN '/' THEN
rtrim(SUBSTR("Path", 1, LENGTH("Path")-1), replace(replace(SUBSTR("Path", 1, LENGTH("Path")-1), "\", "/"), '/', ''))
ELSE
rtrim("Path", replace(replace("Path", "\", "/"), '/', ''))
END AS "ParentFolder"
FROM "File") "A" INNER JOIN "PathPrefix" "B" ON "A"."ParentFolder" = "B"."Prefix";
I have tested this query instead on my dataset and it correctly updates the ID’s and seems to pass all tests.
/* Build the path lookup table */
INSERT INTO "FileLookup" ("ID", "Path", "PrefixID", "BlocksetID", "MetadataID")
SELECT
"A"."ID",
SUBSTR("Path", LENGTH("ParentFolder") + 1) AS "Path",
"B"."ID" AS "PrefixID",
"BlocksetID",
"MetadataID"
FROM
(SELECT "ID", "Path", "BlocksetID", "MetadataID",
CASE SUBSTR("Path", LENGTH("Path")) WHEN '/' THEN
rtrim(SUBSTR("Path", 1, LENGTH("Path")-1), replace(replace(SUBSTR("Path", 1, LENGTH("Path")-1), "\", "/"), '/', ''))
ELSE
rtrim("Path", replace(replace("Path", "\", "/"), '/', ''))
END AS "ParentFolder"
FROM "File") "A" INNER JOIN "PathPrefix" "B" ON "A"."ParentFolder" = "B"."Prefix";
My suggestion is that we push out an update with this change ASAP to avoid hurting more users, and then deal with those who have already updated.
From my database, it seems the mapping ID’s are lost during the update and cannot be recovered. However, the previous database should exist as a backup, so it should be possible to do:
- Install version 2.0.4.14
- Remove the current (broken) database, and copy in the backup
- Run a backup (which will update correctly)
Only (2) should cause problems. As pointed out elsewhere, the naming of the backups does not indicate what it is a backup of, so some guessing / handholding is required here.
