[info] Migration SQL queries for manual intervention after a botched migration

[tsmethurst]

Going to start keeping a thread here of SQL queries between versions, so that folks who run into issues when migrating can do manual interventions if they want to. The provided commands are for info only, it is always better to allow GoToSocial to handle the migration, not do it yourself. Nevertheless, there are cases where manually running stuff can be helpful, so here we are.

[tsmethurst]

v0.17.4 -> v0.18.0

SQLite

ALTER TABLE "instances" ADD COLUMN "custom_css" TEXT;
CREATE TABLE IF NOT EXISTS "domain_permission_drafts" ("id" CHAR(26) NOT NULL, "created_at" timestamptz NOT NULL DEFAULT current_timestamp, "updated_at" timestamptz NOT NULL DEFAULT current_timestamp, "permission_type" INTEGER NOT NULL, "domain" VARCHAR NOT NULL, "created_by_account_id" CHAR(26) NOT NULL, "private_comment" VARCHAR, "public_comment" VARCHAR, "obfuscate" BOOLEAN NOT NULL DEFAULT false, "subscription_id" CHAR(26), PRIMARY KEY ("id"), UNIQUE ("id"), CONSTRAINT "domain_permission_drafts_permission_type_domain_subscription_id_uniq" UNIQUE ("permission_type", "domain", "subscription_id"));
CREATE TABLE IF NOT EXISTS "domain_permission_excludes" ("id" CHAR(26) NOT NULL, "created_at" timestamptz NOT NULL DEFAULT current_timestamp, "updated_at" timestamptz NOT NULL DEFAULT current_timestamp, "domain" VARCHAR NOT NULL, "created_by_account_id" CHAR(26) NOT NULL, "private_comment" VARCHAR, PRIMARY KEY ("id"), UNIQUE ("id"), UNIQUE ("domain"));
CREATE INDEX IF NOT EXISTS "domain_permission_drafts_domain_idx" ON "domain_permission_drafts" ("domain");
CREATE INDEX IF NOT EXISTS "domain_permission_drafts_subscription_id_idx" ON "domain_permission_drafts" ("subscription_id");
ALTER TABLE "mentions" DROP COLUMN "updated_at";
ALTER TABLE "statuses" ADD "edits" VARCHAR(0);
CREATE TABLE IF NOT EXISTS "status_edits" ("id" CHAR(26) NOT NULL, "content" VARCHAR, "content_warning" VARCHAR, "text" VARCHAR, "language" VARCHAR, "sensitive" BOOLEAN NOT NULL DEFAULT false, "attachments" VARCHAR, "attachment_descriptions" VARCHAR, "poll_options" VARCHAR, "poll_votes" VARCHAR, "status_id" CHAR(26) NOT NULL, "created_at" timestamptz NOT NULL DEFAULT current_timestamp, PRIMARY KEY ("id"), UNIQUE ("id"));
ALTER TABLE "statuses" ADD "visibility_new" SMALLINT NOT NULL DEFAULT 0;
UPDATE "statuses" SET "visibility_new" = (CASE "visibility" WHEN 'none' THEN 1 WHEN 'public' THEN 2 WHEN 'unlocked' THEN 3 WHEN 'followers_only' THEN 4 WHEN 'mutuals_only' THEN 5 WHEN 'direct' THEN 6 ELSE 0 END);
DROP INDEX IF EXISTS statuses_visibility_idx;
DROP INDEX IF EXISTS statuses_profile_web_view_idx;
DROP INDEX IF EXISTS statuses_public_timeline_idx;
ALTER TABLE "statuses" DROP COLUMN "visibility";
ALTER TABLE "statuses" RENAME COLUMN "visibility_new" TO "visibility";
ALTER TABLE "sin_bin_statuses" ADD "visibility_new" SMALLINT NOT NULL DEFAULT 0;
UPDATE "sin_bin_statuses" SET "visibility_new" = (CASE "visibility" WHEN 'mutuals_only' THEN 5 WHEN 'direct' THEN 6 WHEN 'none' THEN 1 WHEN 'public' THEN 2 WHEN 'unlocked' THEN 3 WHEN 'followers_only' THEN 4 ELSE 0 END);
ALTER TABLE "sin_bin_statuses" DROP COLUMN "visibility";
ALTER TABLE "sin_bin_statuses" RENAME COLUMN "visibility_new" TO "visibility";
ALTER TABLE "account_settings" ADD "privacy_new" SMALLINT NOT NULL DEFAULT 3;
UPDATE "account_settings" SET "privacy_new" = (CASE "privacy" WHEN 'direct' THEN 6 WHEN 'none' THEN 1 WHEN 'public' THEN 2 WHEN 'unlocked' THEN 3 WHEN 'followers_only' THEN 4 WHEN 'mutuals_only' THEN 5 ELSE 3 END);
ALTER TABLE "account_settings" DROP COLUMN "privacy";
ALTER TABLE "account_settings" RENAME COLUMN "privacy_new" TO "privacy";
ALTER TABLE "account_settings" ADD "web_visibility_new" SMALLINT NOT NULL DEFAULT 3;
UPDATE "account_settings" SET "web_visibility_new" = (CASE "web_visibility" WHEN 'followers_only' THEN 4 WHEN 'mutuals_only' THEN 5 WHEN 'direct' THEN 6 WHEN 'none' THEN 1 WHEN 'public' THEN 2 WHEN 'unlocked' THEN 3 ELSE 3 END);
ALTER TABLE "account_settings" DROP COLUMN "web_visibility";
ALTER TABLE "account_settings" RENAME COLUMN "web_visibility_new" TO "web_visibility";
CREATE INDEX "statuses_visibility_idx" ON "statuses" ("visibility");
CREATE INDEX "statuses_profile_web_view_idx" ON "statuses" ("account_id", "visibility", id DESC);
CREATE INDEX "statuses_public_timeline_idx" ON "statuses" ("visibility", id DESC);
ALTER TABLE "notifications" ADD "notification_type_new" SMALLINT NOT NULL DEFAULT 0;
UPDATE "notifications" SET "notification_type_new" = (CASE "notification_type" WHEN 'follow' THEN 1 WHEN 'reblog' THEN 4 WHEN 'poll' THEN 6 WHEN 'status' THEN 7 WHEN 'admin.sign_up' THEN 8 WHEN 'follow_request' THEN 2 WHEN 'mention' THEN 3 WHEN 'favourite' THEN 5 WHEN 'pending.favourite' THEN 9 WHEN 'pending.reply' THEN 10 WHEN 'pending.reblog' THEN 11 ELSE 0 END);
ALTER TABLE "notifications" DROP COLUMN "notification_type";
ALTER TABLE "notifications" RENAME COLUMN "notification_type_new" TO "notification_type";
CREATE TABLE IF NOT EXISTS "vapid_key_pairs" ("id" INTEGER NOT NULL, "public" VARCHAR NOT NULL, "private" VARCHAR NOT NULL, PRIMARY KEY ("id"));
CREATE TABLE IF NOT EXISTS "web_push_subscriptions" ("id" CHAR(26) NOT NULL, "account_id" CHAR(26) NOT NULL, "token_id" CHAR(26) NOT NULL, "endpoint" VARCHAR NOT NULL, "auth" VARCHAR NOT NULL, "p256dh" VARCHAR NOT NULL, "notification_flags" INTEGER NOT NULL, "policy" INTEGER NOT NULL DEFAULT 1, PRIMARY KEY ("id"), UNIQUE ("token_id"));
CREATE INDEX IF NOT EXISTS "web_push_subscriptions_account_id_idx" ON "web_push_subscriptions" ("account_id");
ALTER TABLE "media_attachments" DROP COLUMN "updated_at";
ALTER TABLE "statuses" ADD "edited_at" timestamptz;
UPDATE "statuses" AS "status" SET "edited_at" = "updated_at" WHERE (NOT (json_array_length("edits") = 0 OR "edits" IS NULL));
ALTER TABLE "statuses" DROP COLUMN "updated_at";
CREATE TABLE IF NOT EXISTS "domain_permission_subscriptions" ("id" CHAR(26) NOT NULL, "priority" INTEGER, "title" VARCHAR, "permission_type" INTEGER NOT NULL, "as_draft" BOOLEAN NOT NULL DEFAULT true, "adopt_orphans" BOOLEAN NOT NULL DEFAULT false, "created_by_account_id" CHAR(26) NOT NULL, "uri" VARCHAR NOT NULL, "content_type" INTEGER NOT NULL, "fetch_username" VARCHAR, "fetch_password" VARCHAR, "fetched_at" timestamptz, "successfully_fetched_at" timestamptz, "last_modified" timestamptz, "etag" VARCHAR, "error" VARCHAR, PRIMARY KEY ("id"), UNIQUE ("id"), UNIQUE ("title"), UNIQUE ("uri"));
CREATE INDEX IF NOT EXISTS "domain_permission_subscriptions_permission_type_idx" ON "domain_permission_subscriptions" ("permission_type");
CREATE INDEX IF NOT EXISTS "domain_permission_subscriptions_priority_order_idx" ON "domain_permission_subscriptions" ("priority" DESC);
DROP INDEX IF EXISTS statuses_account_view_idx;
CREATE INDEX IF NOT EXISTS "statuses_account_view_idx" ON "statuses" ("account_id", "in_reply_to_uri", "in_reply_to_account_id", (mentions IS NULL OR json_array_length(mentions) = 0), id DESC);
ALTER TABLE "web_push_subscriptions" ADD "policy" INTEGER NOT NULL DEFAULT 1;

Postgres

ALTER TABLE "instances" ADD COLUMN "custom_css" TEXT;
CREATE TABLE IF NOT EXISTS "domain_permission_drafts" ("id" CHAR(26) NOT NULL, "created_at" timestamptz NOT NULL DEFAULT current_timestamp, "updated_at" timestamptz NOT NULL DEFAULT current_timestamp, "permission_type" SMALLINT NOT NULL, "domain" VARCHAR NOT NULL, "created_by_account_id" CHAR(26) NOT NULL, "private_comment" VARCHAR, "public_comment" VARCHAR, "obfuscate" BOOLEAN NOT NULL DEFAULT false, "subscription_id" CHAR(26), PRIMARY KEY ("id"), UNIQUE ("id"), CONSTRAINT "domain_permission_drafts_permission_type_domain_subscription_id_uniq" UNIQUE ("permission_type", "domain", "subscription_id"));
CREATE TABLE IF NOT EXISTS "domain_permission_excludes" ("id" CHAR(26) NOT NULL, "created_at" timestamptz NOT NULL DEFAULT current_timestamp, "updated_at" timestamptz NOT NULL DEFAULT current_timestamp, "domain" VARCHAR NOT NULL, "created_by_account_id" CHAR(26) NOT NULL, "private_comment" VARCHAR, PRIMARY KEY ("id"), UNIQUE ("id"), UNIQUE ("domain"));
CREATE INDEX IF NOT EXISTS "domain_permission_drafts_domain_idx" ON "domain_permission_drafts" ("domain");
CREATE INDEX IF NOT EXISTS "domain_permission_drafts_subscription_id_idx" ON "domain_permission_drafts" ("subscription_id");
ALTER TABLE "mentions" DROP COLUMN "updated_at";
ALTER TABLE "statuses" ADD "edits" VARCHAR[];
CREATE TABLE IF NOT EXISTS "status_edits" ("id" CHAR(26) NOT NULL, "content" VARCHAR, "content_warning" VARCHAR, "text" VARCHAR, "language" VARCHAR, "sensitive" BOOLEAN NOT NULL DEFAULT false, "attachments" VARCHAR[], "attachment_descriptions" VARCHAR[], "poll_options" VARCHAR[], "poll_votes" BIGINT[], "status_id" CHAR(26) NOT NULL, "created_at" timestamptz NOT NULL DEFAULT current_timestamp, PRIMARY KEY ("id"), UNIQUE ("id"));
ALTER TABLE "statuses" ADD "visibility_new" SMALLINT NOT NULL DEFAULT 0;
UPDATE "statuses" SET "visibility_new" = (CASE "visibility" WHEN 'followers_only' THEN 4 WHEN 'mutuals_only' THEN 5 WHEN 'direct' THEN 6 WHEN 'none' THEN 1 WHEN 'public' THEN 2 WHEN 'unlocked' THEN 3 ELSE 0 END);
DROP INDEX statuses_visibility_idx;
DROP INDEX statuses_profile_web_view_idx;
DROP INDEX statuses_public_timeline_idx;
ALTER TABLE "statuses" DROP COLUMN "visibility";
ALTER TABLE "statuses" RENAME COLUMN "visibility_new" TO "visibility";
ALTER TABLE "sin_bin_statuses" ADD "visibility_new" SMALLINT NOT NULL DEFAULT 0;
UPDATE "sin_bin_statuses" SET "visibility_new" = (CASE "visibility" WHEN 'unlocked' THEN 3 WHEN 'followers_only' THEN 4 WHEN 'mutuals_only' THEN 5 WHEN 'direct' THEN 6 WHEN 'none' THEN 1 WHEN 'public' THEN 2 ELSE 0 END);
ALTER TABLE "sin_bin_statuses" DROP COLUMN "visibility";
ALTER TABLE "sin_bin_statuses" RENAME COLUMN "visibility_new" TO "visibility";
ALTER TABLE "account_settings" ADD "privacy_new" SMALLINT NOT NULL DEFAULT 3;
UPDATE "account_settings" SET "privacy_new" = (CASE "privacy" WHEN 'mutuals_only' THEN 5 WHEN 'direct' THEN 6 WHEN 'none' THEN 1 WHEN 'public' THEN 2 WHEN 'unlocked' THEN 3 WHEN 'followers_only' THEN 4 ELSE 3 END);
ALTER TABLE "account_settings" DROP COLUMN "privacy";
ALTER TABLE "account_settings" RENAME COLUMN "privacy_new" TO "privacy";
ALTER TABLE "account_settings" ADD "web_visibility_new" SMALLINT NOT NULL DEFAULT 3;
UPDATE "account_settings" SET "web_visibility_new" = (CASE "web_visibility" WHEN 'mutuals_only' THEN 5 WHEN 'direct' THEN 6 WHEN 'none' THEN 1 WHEN 'public' THEN 2 WHEN 'unlocked' THEN 3 WHEN 'followers_only' THEN 4 ELSE 3 END);
ALTER TABLE "account_settings" DROP COLUMN "web_visibility";
ALTER TABLE "account_settings" RENAME COLUMN "web_visibility_new" TO "web_visibility";
CREATE INDEX "statuses_visibility_idx" ON "statuses" ("visibility");
CREATE INDEX "statuses_profile_web_view_idx" ON "statuses" ("account_id", "visibility", id DESC);
CREATE INDEX "statuses_public_timeline_idx" ON "statuses" ("visibility", id DESC);
ALTER TABLE "notifications" ADD "notification_type_new" SMALLINT NOT NULL DEFAULT 0;
UPDATE "notifications" SET "notification_type_new" = (CASE "notification_type" WHEN 'follow_request' THEN 2 WHEN 'reblog' THEN 4 WHEN 'favourite' THEN 5 WHEN 'status' THEN 7 WHEN 'pending.favourite' THEN 9 WHEN 'pending.reply' THEN 10 WHEN 'pending.reblog' THEN 11 WHEN 'follow' THEN 1 WHEN 'mention' THEN 3 WHEN 'poll' THEN 6 WHEN 'admin.sign_up' THEN 8 ELSE 0 END);
ALTER TABLE "notifications" DROP COLUMN "notification_type";
ALTER TABLE "notifications" RENAME COLUMN "notification_type_new" TO "notification_type";
CREATE TABLE IF NOT EXISTS "vapid_key_pairs" ("id" BIGINT NOT NULL, "public" VARCHAR NOT NULL, "private" VARCHAR NOT NULL, PRIMARY KEY ("id"));
CREATE TABLE IF NOT EXISTS "web_push_subscriptions" ("id" CHAR(26) NOT NULL, "account_id" CHAR(26) NOT NULL, "token_id" CHAR(26) NOT NULL, "endpoint" VARCHAR NOT NULL, "auth" VARCHAR NOT NULL, "p256dh" VARCHAR NOT NULL, "notification_flags" BIGINT NOT NULL, "policy" SMALLINT NOT NULL DEFAULT 1, PRIMARY KEY ("id"), UNIQUE ("token_id"));
CREATE INDEX IF NOT EXISTS "web_push_subscriptions_account_id_idx" ON "web_push_subscriptions" ("account_id");
ALTER TABLE "media_attachments" DROP COLUMN "updated_at";
ALTER TABLE "statuses" ADD "edited_at" timestamptz;
UPDATE "statuses" AS "status" SET "edited_at" = "updated_at" WHERE (NOT (CARDINALITY("edits") = 0 OR "edits" IS NULL));
ALTER TABLE "statuses" DROP COLUMN "updated_at";
CREATE TABLE IF NOT EXISTS "domain_permission_subscriptions" ("id" CHAR(26) NOT NULL, "priority" SMALLINT, "title" VARCHAR, "permission_type" SMALLINT NOT NULL, "as_draft" BOOLEAN NOT NULL DEFAULT true, "adopt_orphans" BOOLEAN NOT NULL DEFAULT false, "created_by_account_id" CHAR(26) NOT NULL, "uri" VARCHAR NOT NULL, "content_type" SMALLINT NOT NULL, "fetch_username" VARCHAR, "fetch_password" VARCHAR, "fetched_at" timestamptz, "successfully_fetched_at" timestamptz, "last_modified" timestamptz, "etag" VARCHAR, "error" VARCHAR, PRIMARY KEY ("id"), UNIQUE ("id"), UNIQUE ("title"), UNIQUE ("uri"));
CREATE INDEX IF NOT EXISTS "domain_permission_subscriptions_permission_type_idx" ON "domain_permission_subscriptions" ("permission_type");
CREATE INDEX IF NOT EXISTS "domain_permission_subscriptions_priority_order_idx" ON "domain_permission_subscriptions" ("priority" DESC);
DROP INDEX IF EXISTS statuses_account_view_idx;
CREATE INDEX IF NOT EXISTS "statuses_account_view_idx" ON "statuses" ("account_id", "in_reply_to_uri", "in_reply_to_account_id", (mentions IS NULL OR CARDINALITY(mentions) = 0), id DESC);
ALTER TABLE "web_push_subscriptions" ADD IF NOT EXISTS "policy" SMALLINT NOT NULL DEFAULT 1;