51 lines
1.9 KiB
JavaScript
51 lines
1.9 KiB
JavaScript
'use strict';
|
|
|
|
/**
|
|
* Legacy SRS keys included lesson_id while the course pool used NULL. The same
|
|
* word pair could therefore receive parallel schedules. Keep the most advanced
|
|
* record, preserve its earliest due time, and remove only exact course-wide
|
|
* duplicates.
|
|
*/
|
|
module.exports = {
|
|
async up(queryInterface) {
|
|
await queryInterface.sequelize.query(`
|
|
WITH grouped AS (
|
|
SELECT
|
|
id,
|
|
FIRST_VALUE(id) OVER bucket AS keeper_id,
|
|
MIN(next_due_at) OVER bucket AS earliest_due_at,
|
|
MAX(correct_count) OVER bucket AS max_correct_count,
|
|
MAX(wrong_count) OVER bucket AS max_wrong_count,
|
|
MAX(lapse_count) OVER bucket AS max_lapse_count,
|
|
BOOL_OR(is_hard) OVER bucket AS any_hard,
|
|
ROW_NUMBER() OVER bucket AS row_number
|
|
FROM community.vocab_srs_item
|
|
WINDOW bucket AS (
|
|
PARTITION BY user_id, course_id, UPPER(direction),
|
|
LOWER(REGEXP_REPLACE(learning, '[[:punct:][:space:]]+', ' ', 'g')),
|
|
LOWER(REGEXP_REPLACE(reference, '[[:punct:][:space:]]+', ' ', 'g'))
|
|
ORDER BY correct_count DESC, last_reviewed_at DESC NULLS LAST, id ASC
|
|
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
|
|
)
|
|
), updated AS (
|
|
UPDATE community.vocab_srs_item AS item
|
|
SET next_due_at = grouped.earliest_due_at,
|
|
correct_count = grouped.max_correct_count,
|
|
wrong_count = grouped.max_wrong_count,
|
|
lapse_count = grouped.max_lapse_count,
|
|
is_hard = grouped.any_hard
|
|
FROM grouped
|
|
WHERE item.id = grouped.keeper_id
|
|
RETURNING item.id
|
|
)
|
|
DELETE FROM community.vocab_srs_item AS item
|
|
USING grouped
|
|
WHERE item.id = grouped.id
|
|
AND grouped.row_number > 1;
|
|
`);
|
|
},
|
|
async down() {
|
|
// Duplicate rows are intentionally not recreated.
|
|
}
|
|
};
|