83 lines
2.8 KiB
Python
83 lines
2.8 KiB
Python
|
"""
|
||
|
|
Change completely the structure of DMs
|
||
|
|
"""
|
||
|
|
|
||
|
|
from yoyo import step
|
||
|
|
|
||
|
|
__depends__ = {'20231119_00_ASHrR6-fix-revisions-table-to-match-tags-type'}
|
||
|
|
|
||
|
|
steps = [
|
||
|
|
step("""
|
||
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
||
|
|
|
||
|
|
CREATE TABLE public.dms_temp_new (
|
||
|
|
"hash" varchar NOT NULL,
|
||
|
|
"user" varchar(255) NOT NULL,
|
||
|
|
service varchar(20) NOT NULL,
|
||
|
|
"content" text NOT NULL DEFAULT ''::text,
|
||
|
|
embed jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
published timestamp NULL,
|
||
|
|
file jsonb NOT null,
|
||
|
|
CONSTRAINT dms_temp_new_pkey PRIMARY KEY ("hash","user", service)
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO public.dms_temp_new ("hash", "user", service, "content", embed, added, published, file)
|
||
|
|
select ENCODE(DIGEST("content", 'sha256'), 'hex'), "user", service, "content", embed, MIN(added), MIN(published), file
|
||
|
|
FROM public.dms
|
||
|
|
GROUP BY "content", "user", service, embed, file;
|
||
|
|
|
||
|
|
ALTER TABLE dms RENAME TO dms_temp_old;
|
||
|
|
ALTER TABLE dms_temp_new RENAME TO dms;
|
||
|
|
|
||
|
|
ALTER TABLE dms_temp_old RENAME CONSTRAINT dms_pkey TO dms_temp_old_pkey;
|
||
|
|
ALTER TABLE dms RENAME CONSTRAINT dms_temp_new_pkey TO dms_pkey;
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF EXISTS(SELECT 1 FROM pg_indexes WHERE indexname = 'pgroonga_dms_idx') THEN
|
||
|
|
ALTER INDEX pgroonga_dms_idx RENAME TO pgroonga_dms_temp_old_idx;
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE INDEX dms_user_idx ON public.dms ("user");
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.unapproved_dms_temp_new (
|
||
|
|
"hash" varchar NOT NULL,
|
||
|
|
"user" varchar(255) NOT NULL,
|
||
|
|
service varchar(20) NOT NULL,
|
||
|
|
contributor_id varchar(255) NOT NULL,
|
||
|
|
"content" text NOT NULL DEFAULT ''::text,
|
||
|
|
embed jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
published timestamp NULL,
|
||
|
|
file jsonb NOT NULL,
|
||
|
|
import_id varchar(255) NOT NULL,
|
||
|
|
remote_user_id_hash varchar NULL,
|
||
|
|
deleted_at timestamp NULL,
|
||
|
|
CONSTRAINT unapproved_dms_temp_new_pkey PRIMARY KEY ("hash", "user", service, contributor_id)
|
||
|
|
);
|
||
|
|
INSERT INTO public.unapproved_dms_temp_new ("hash", "user", service, contributor_id, "content", embed, added, published, file, import_id)
|
||
|
|
select ENCODE(DIGEST("content", 'sha256'), 'hex'), "user", service, COALESCE( contributor_id, '0' ), "content", embed, MIN(added), MIN(published), file, MIN(import_id)
|
||
|
|
FROM public.unapproved_dms
|
||
|
|
GROUP BY "user", service, "content", embed, file, contributor_id;
|
||
|
|
|
||
|
|
ALTER TABLE unapproved_dms RENAME TO unapproved_dms_temp_old;
|
||
|
|
ALTER TABLE unapproved_dms_temp_new RENAME TO unapproved_dms;
|
||
|
|
|
||
|
|
ALTER TABLE unapproved_dms_temp_old RENAME CONSTRAINT unapproved_dms_pkey TO unapproved_dms_temp_old_pkey;
|
||
|
|
ALTER TABLE unapproved_dms RENAME CONSTRAINT unapproved_dms_temp_new_pkey TO unapproved_dms_pkey;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE INDEX unapproved_dms_contributor_id_user_idx ON public.unapproved_dms (contributor_id,"user");
|
||
|
|
|
||
|
|
|
||
|
|
DELETE FROM public.unapproved_dms
|
||
|
|
USING public.dms
|
||
|
|
WHERE public.unapproved_dms.hash = public.dms.hash;
|
||
|
|
"""),
|
||
|
|
]
|