stash-box
stash-box copied to clipboard
Date accuracy on older edits is incorrect
The date accuracy migration added in #810 (42_date_columns.up.sql) does not migrate the edit data.
Example from StashDB: https://stashdb.org/edits/140cf443-3985-4375-a20b-965a9dbbb82b (the input was '1996')
Examples from my old dev database. I assume NULL date accuracy is DAY here.
SELECT /* e.id, */
target_type,
data['new_data'][d.field] AS old_date, data['old_data'][d.field||'_accuracy'] AS old_da,
data['new_data'][d.field] AS new_date, data['new_data'][d.field||'_accuracy'] AS new_da
FROM edits e
JOIN (
SELECT id, (CASE WHEN target_type = 'SCENE' THEN 'date' WHEN target_type = 'PERFORMER' THEN 'birthdate' END) field
FROM edits
WHERE target_type in ('SCENE', 'PERFORMER')
) d ON d.id = e.id
WHERE data['new_data'] ? d.field OR data['old_data'] ? d.field
ORDER BY e.created_at DESC, e.id ASC;
| target_type | old_date | old_da | new_date | new_da |
|---|---|---|---|---|
| SCENE | "2022-01-01" | "DAY" | "2022-01-01" | "DAY" |
| PERFORMER | "1995-06-13" | "1995-06-13" | "DAY" | |
| SCENE | "2022-01-01" | "DAY" | "2022-01-01" | "YEAR" |
| SCENE | "2022-01-01" | "2022-01-01" | "YEAR" | |
| PERFORMER | "1986-01-01" | "1986-01-01" | "YEAR" | |
| PERFORMER | "1985-12-01" | "MONTH" | "1985-12-01" | "DAY" |
| PERFORMER | "1985-12-01" | "MONTH" | "1985-12-01" | "DAY" |
| PERFORMER | "1992-03-01" | "DAY" | "1992-03-01" | "MONTH" |
| PERFORMER | "1992-03-15" | "1992-03-15" | "DAY" | |
| PERFORMER | "1985-12-01" | "1985-12-01" | "MONTH" | |
| SCENE | "2021-10-09" | "2021-10-09" | ||
| PERFORMER | "1990-01-01" | "1990-01-01" | "YEAR" |