9d16fd48fc
* added query to move custom interiors * Add query for solving this * removed old query * Add filter to query * Bump migration version --------- Co-authored-by: Rikard Bartholf <rikard@bartholf.nu>
130 lines
4.3 KiB
SQL
130 lines
4.3 KiB
SQL
/** PROMPT
|
|
|
|
Table interiors has the following columns:
|
|
- id INTEGER
|
|
- room_id INTEGER (NULLABLE)
|
|
- position INTEGER
|
|
|
|
Create a migration that finds all print groups that matches the following criterias:
|
|
- Has a room_id that is not null ON position 0
|
|
- Has one or more entries where room_id is NULL
|
|
|
|
Here is how the migration should work:
|
|
- Get the entry with the lowest position where room_id is NULL and set its position to 0.
|
|
- Set the position of the entry with room_id not null to the value of the row above.
|
|
- Two or more entries with room_id NULL can have the same position, in that case, use any of those entries.
|
|
- All entries in the group should then increase in a straight series starting from 1, except the entry that got position 0.
|
|
- Solve this by using a cursor
|
|
|
|
Entries with the same print_id are grouped together in a "print group".
|
|
|
|
*/
|
|
|
|
|
|
|
|
-- Migration to reposition interiors entries within print groups
|
|
-- Swaps position 0 entry (room_id NOT NULL) with lowest positioned NULL room_id entry
|
|
-- Then renumbers all remaining entries sequentially starting from 1
|
|
|
|
DO $$
|
|
DECLARE
|
|
cursor_print_groups CURSOR FOR
|
|
SELECT DISTINCT print_id
|
|
FROM interiors
|
|
WHERE print_id IN (
|
|
-- Find print_ids that have both:
|
|
-- 1. An entry with room_id NOT NULL at position 0
|
|
-- 2. One or more entries with room_id NULL
|
|
SELECT print_id
|
|
FROM interiors
|
|
WHERE room_id IS NOT NULL AND position = 0
|
|
INTERSECT
|
|
SELECT print_id
|
|
FROM interiors
|
|
WHERE room_id IS NULL
|
|
)
|
|
AND print_id IN (
|
|
SELECT printid FROM "product-printproducts" WHERE groupid IN (1, 3)
|
|
);
|
|
|
|
current_print_id INTEGER;
|
|
entry_at_position_0_id INTEGER;
|
|
entry_at_position_0_old_pos INTEGER;
|
|
lowest_null_entry_id INTEGER;
|
|
lowest_null_position INTEGER;
|
|
renumber_cursor CURSOR (p_print_id INTEGER, exclude_id INTEGER) FOR
|
|
SELECT id
|
|
FROM interiors
|
|
WHERE print_id = p_print_id
|
|
AND id != exclude_id
|
|
ORDER BY
|
|
CASE WHEN room_id IS NULL THEN position ELSE position END ASC,
|
|
id ASC;
|
|
|
|
counter INTEGER;
|
|
entry_id INTEGER;
|
|
BEGIN
|
|
-- Process each qualifying print group
|
|
FOR print_group IN cursor_print_groups
|
|
LOOP
|
|
current_print_id := print_group.print_id;
|
|
|
|
RAISE NOTICE 'Processing print_id: %', current_print_id;
|
|
|
|
-- Get the entry currently at position 0 (room_id NOT NULL)
|
|
SELECT id, position INTO entry_at_position_0_id, entry_at_position_0_old_pos
|
|
FROM interiors
|
|
WHERE print_id = current_print_id
|
|
AND room_id IS NOT NULL
|
|
AND position = 0
|
|
LIMIT 1;
|
|
|
|
-- Get the entry with lowest position where room_id IS NULL
|
|
SELECT id, position INTO lowest_null_entry_id, lowest_null_position
|
|
FROM interiors
|
|
WHERE print_id = current_print_id
|
|
AND room_id IS NULL
|
|
ORDER BY position ASC, id ASC
|
|
LIMIT 1;
|
|
|
|
RAISE NOTICE 'Found entry at position 0: id=%, position=%',
|
|
entry_at_position_0_id, entry_at_position_0_old_pos;
|
|
RAISE NOTICE 'Found lowest NULL entry: id=%, position=%',
|
|
lowest_null_entry_id, lowest_null_position;
|
|
|
|
-- Step 1: Set the NULL entry to position 0
|
|
UPDATE interiors
|
|
SET position = 0
|
|
WHERE id = lowest_null_entry_id;
|
|
|
|
RAISE NOTICE 'Set entry id=% to position 0', lowest_null_entry_id;
|
|
|
|
-- Step 2: Set the former position-0 entry to the old position of the NULL entry
|
|
UPDATE interiors
|
|
SET position = lowest_null_position
|
|
WHERE id = entry_at_position_0_id;
|
|
|
|
RAISE NOTICE 'Set entry id=% to position %', entry_at_position_0_id, lowest_null_position;
|
|
|
|
-- Step 3: Renumber all other entries sequentially starting from 1
|
|
counter := 1;
|
|
|
|
OPEN renumber_cursor(current_print_id, lowest_null_entry_id);
|
|
LOOP
|
|
FETCH renumber_cursor INTO entry_id;
|
|
EXIT WHEN NOT FOUND;
|
|
|
|
UPDATE interiors
|
|
SET position = counter
|
|
WHERE id = entry_id;
|
|
|
|
RAISE NOTICE 'Renumbered entry id=% to position %', entry_id, counter;
|
|
counter := counter + 1;
|
|
END LOOP;
|
|
CLOSE renumber_cursor;
|
|
|
|
END LOOP;
|
|
|
|
RAISE NOTICE 'Migration completed successfully';
|
|
END $$;
|