muken
09bbe0a441
- Photos now stored exclusively in images table (no duplication) - rooms.photos array cleared, freed 17 MB storage - Updated room APIs to fetch images by room_id - Added PUT endpoint for room updates - Updated RoomEditor to load/delete images from images table - Fixed images API to return 'data' key consistently
76 lines
2.3 KiB
PL/PgSQL
76 lines
2.3 KiB
PL/PgSQL
-- Migration: Move room photos from rooms.photos to images table
|
|
-- Run with: psql -U lahuasca -d lahuasca -f migrate-room-photos.sql
|
|
|
|
BEGIN;
|
|
|
|
-- First, check which rooms have photos in rooms.photos but not in images
|
|
-- For rooms 7 and 8, we need to copy their photos to images table
|
|
|
|
DO $$
|
|
DECLARE
|
|
rec RECORD;
|
|
photo_data TEXT;
|
|
room_id_val INTEGER;
|
|
photo_index INTEGER;
|
|
BEGIN
|
|
-- Loop through rooms that have photos in the array but not in images table
|
|
FOR rec IN
|
|
SELECT r.id, r.name, r.photos
|
|
FROM rooms r
|
|
WHERE array_length(r.photos, 1) > 0
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM images i WHERE i.room_id = r.id
|
|
)
|
|
LOOP
|
|
room_id_val := rec.id;
|
|
|
|
-- Insert each photo from the array into images
|
|
FOR photo_index IN 1..array_length(rec.photos, 1) LOOP
|
|
photo_data := rec.photos[photo_index];
|
|
|
|
INSERT INTO images (filename, data, thumbnail, medium, category, room_id, location_target)
|
|
VALUES (
|
|
CONCAT('room-', room_id_val, '-photo-', photo_index, '.webp'),
|
|
photo_data,
|
|
photo_data, -- thumbnail (will be regenerated by WebP migration if different)
|
|
NULL,
|
|
'rooms',
|
|
room_id_val,
|
|
CONCAT('room-', room_id_val)
|
|
);
|
|
END LOOP;
|
|
|
|
RAISE NOTICE 'Migrated % photos for room % (%)', array_length(rec.photos, 1), rec.name, room_id_val;
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- Now clear the photos arrays in rooms table (images table is the source of truth)
|
|
UPDATE rooms SET photos = '{}' WHERE array_length(photos, 1) > 0;
|
|
|
|
-- For rooms that already have images, sync the photo count
|
|
-- This is informational only
|
|
DO $$
|
|
DECLARE
|
|
rec RECORD;
|
|
BEGIN
|
|
FOR rec IN
|
|
SELECT r.id, r.name,
|
|
array_length(r.photos, 1) as old_count,
|
|
(SELECT COUNT(*) FROM images i WHERE i.room_id = r.id) as new_count
|
|
FROM rooms r
|
|
LOOP
|
|
RAISE NOTICE 'Room % (%): photos array=%, images table=%',
|
|
rec.name, rec.id, rec.old_count, rec.new_count;
|
|
END LOOP;
|
|
END $$;
|
|
|
|
COMMIT;
|
|
|
|
-- Verify the migration
|
|
SELECT
|
|
r.id,
|
|
r.name,
|
|
array_length(r.photos, 1) as room_photos_count,
|
|
(SELECT COUNT(*) FROM images i WHERE i.room_id = r.id) as images_count
|
|
FROM rooms r
|
|
ORDER BY r.id; |