81 lines
2.5 KiB
MySQL
81 lines
2.5 KiB
MySQL
|
-- Step 1, fetch data from strapi database and copy it to futureporn database via dblink
|
||
|
|
-- we store the data in futureporn database under public_strapi_old schema
|
||
|
|
-- We did this step manually using pgadmin4 so it's not part of this sql
|
||
|
|
|
||
|
|
|
||
|
|
-- Step 2, transform data from public_strapi_old to match the up-to-date api schema
|
||
|
|
-- There are a lot of deprecated tables in the Strapi db, so what we do is we copy and transform only what we need.
|
||
|
|
SELECT dblink_disconnect('old_db_conn');
|
||
|
|
SELECT dblink_connect(
|
||
|
|
'old_db_conn',
|
||
|
|
'dbname=futureporn_strapi_old user=postgres passfile=/tmp/.pgpass'
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
-- Migrate vods table
|
||
|
|
INSERT INTO api.vods (
|
||
|
|
id,
|
||
|
|
id_old,
|
||
|
|
stream_id,
|
||
|
|
created_at,
|
||
|
|
updated_at,
|
||
|
|
title,
|
||
|
|
date,
|
||
|
|
note,
|
||
|
|
ipfs_cid,
|
||
|
|
s3_file,
|
||
|
|
announce_title,
|
||
|
|
announce_url,
|
||
|
|
status
|
||
|
|
)
|
||
|
|
SELECT
|
||
|
|
gen_random_uuid(),
|
||
|
|
vods.id,
|
||
|
|
NULL,
|
||
|
|
vods.created_at,
|
||
|
|
vods.updated_at,
|
||
|
|
vods.title,
|
||
|
|
vods.date::date,
|
||
|
|
vods.note,
|
||
|
|
vods.video_src_hash,
|
||
|
|
NULL, -- old vods doesn't contain this info-- the join table is needed
|
||
|
|
vods.announce_title,
|
||
|
|
vods.announce_url,
|
||
|
|
'pending_recording'
|
||
|
|
FROM
|
||
|
|
dblink('old_db_conn', 'SELECT * FROM public.vods') AS vods (
|
||
|
|
id integer,
|
||
|
|
video_src_hash character varying,
|
||
|
|
video_720_hash character varying,
|
||
|
|
video_480_hash character varying,
|
||
|
|
video_360_hash character varying,
|
||
|
|
video_240_hash character varying,
|
||
|
|
thin_hash character varying,
|
||
|
|
thicc_hash character varying,
|
||
|
|
announce_title character varying,
|
||
|
|
announce_url character varying,
|
||
|
|
note text,
|
||
|
|
date timestamp(6) without time zone,
|
||
|
|
spoilers text,
|
||
|
|
created_at timestamp(6) without time zone,
|
||
|
|
updated_at timestamp(6) without time zone,
|
||
|
|
published_at timestamp(6) without time zone,
|
||
|
|
created_by_id integer,
|
||
|
|
updated_by_id integer,
|
||
|
|
title character varying,
|
||
|
|
chat_log text,
|
||
|
|
date_2 character varying,
|
||
|
|
cuid character varying,
|
||
|
|
archive_status character varying
|
||
|
|
)
|
||
|
|
LEFT JOIN (
|
||
|
|
-- Fetching vods_vtuber_links from the old database
|
||
|
|
SELECT *
|
||
|
|
FROM dblink('old_db_conn', 'SELECT vod_id, vtuber_id FROM public.vods_vtuber_links') AS links (
|
||
|
|
vod_id integer,
|
||
|
|
vtuber_id integer
|
||
|
|
)
|
||
|
|
) AS links ON vods.id = links.vod_id
|
||
|
|
LEFT JOIN api.vtubers AS vtubers
|
||
|
|
ON links.vtuber_id = vtubers.id_old; -- Map the old `vtuber_id` to the new `uuid` in `vtubers`
|