65 lines
2.0 KiB
SQL
65 lines
2.0 KiB
SQL
-- DMs
|
|
CREATE TABLE dms_temp_old(
|
|
"id" varchar(255) NOT NULL,
|
|
"user" varchar(255) NOT NULL,
|
|
"service" varchar(20) NOT NULL,
|
|
"content" text NOT NULL DEFAULT '',
|
|
"embed" jsonb NOT NULL DEFAULT '{}',
|
|
"added" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"published" timestamp,
|
|
"file" jsonb NOT NULL,
|
|
PRIMARY KEY (id, service)
|
|
);
|
|
|
|
CREATE TABLE unapproved_dms_temp_old(
|
|
"import_id" varchar(255) NOT NULL,
|
|
contributor_id varchar(255),
|
|
"id" varchar(255) NOT NULL,
|
|
"user" varchar(255) NOT NULL,
|
|
"service" varchar(20) NOT NULL,
|
|
"content" text NOT NULL DEFAULT '',
|
|
"embed" jsonb NOT NULL DEFAULT '{}',
|
|
"added" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"published" timestamp,
|
|
"file" jsonb NOT NULL,
|
|
PRIMARY KEY (id, service)
|
|
);
|
|
|
|
-- the old tables weren't dropped, apparently
|
|
|
|
CREATE TABLE dms(
|
|
"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_pkey PRIMARY KEY ("hash", "user", service)
|
|
);
|
|
|
|
CREATE TABLE unapproved_dms(
|
|
"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_pkey PRIMARY KEY ("hash", "user", service, contributor_id)
|
|
);
|
|
|
|
CREATE INDEX dm_idx ON dms_temp_old USING btree("user");
|
|
|
|
CREATE INDEX dms_user_idx ON dms_temp_old("user");
|
|
|
|
CREATE INDEX unapproved_dm_idx ON unapproved_dms USING btree("import_id");
|
|
|
|
CREATE INDEX unapproved_dms_contributor_id_user_idx ON unapproved_dms(contributor_id, "user");
|