Is there any way to batch modify the domain of all images?

Questions and Answers
1
System Information
  • Strapi Version: 4.16.2
  • Operating System: windows11 x64
  • Database: postgres
  • Node Version: v18.18.2
  • PNPM Version: 8.14.3

All of my images are stored in a cloud servicer (like Amazon S3).
Because the old domain needs to be suspended for a period of time, the image domain needs to be migrated.
I have set up a new CDN domain in the cloud to the same storage of old domain.
How should I batch modify the image domain name of the program?

Below is my try.

UPDATE public.files
SET url = REPLACE(url, 'example.com', 'example.cn');

UPDATE public.files
SET formats = jsonb_set(
    formats,
    '{large}',
    CASE 
        WHEN formats->'large' IS NOT NULL AND formats->'large'->>'url' LIKE '%example.com%'
        THEN jsonb_set(formats->'large', '{url}', ('"' || replace(formats->'large'->>'url', 'example.com', 'example.cn') || '"')::jsonb)
        ELSE formats->'large'
    END
) 
WHERE formats->'large' IS NOT NULL AND formats->'large'->>'url' LIKE '%example.com%';

UPDATE public.files
SET formats = jsonb_set(
    formats,
    '{thumbnail}',
    CASE 
        WHEN formats->'thumbnail' IS NOT NULL AND formats->'thumbnail'->>'url' LIKE '%example.com%'
        THEN jsonb_set(formats->'thumbnail', '{url}', ('"' || replace(formats->'thumbnail'->>'url', 'example.com', 'example.cn') || '"')::jsonb)
        ELSE formats->'thumbnail'
    END
) 
WHERE formats->'thumbnail' IS NOT NULL AND formats->'thumbnail'->>'url' LIKE '%example.com%';

UPDATE public.files
SET formats = jsonb_set(
    formats,
    '{medium}',
    CASE 
        WHEN formats->'medium' IS NOT NULL AND formats->'medium'->>'url' LIKE '%example.com%'
        THEN jsonb_set(formats->'medium', '{url}', ('"' || replace(formats->'medium'->>'url', 'example.com', 'example.cn') || '"')::jsonb)
        ELSE formats->'medium'
    END
) 
WHERE formats->'medium' IS NOT NULL AND formats->'medium'->>'url' LIKE '%example.com%';

UPDATE public.files
SET formats = jsonb_set(
    formats,
    '{small}',
    CASE 
        WHEN formats->'small' IS NOT NULL AND formats->'small'->>'url' LIKE '%example.com%'
        THEN jsonb_set(formats->'small', '{url}', ('"' || replace(formats->'small'->>'url', 'example.com', 'example.cn') || '"')::jsonb)
        ELSE formats->'small'
    END
) 
WHERE formats->'small' IS NOT NULL AND formats->'small'->>'url' LIKE '%example.com%';

When I restarted the program, the management panel pictures displayed errors. I checked the cloud storage and found that the pictures had been deleted.
I stopped the program urgently, and eventually 1/2 of pictures were deleted.
I don’t know what happened. :melting_face: