From c4bc85003fd0dd430f68c775f5f0a032538600e2 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Tue, 19 Dec 2017 11:24:13 +0100 Subject: Fix migration scripts --- .../migrations/0130-video-channel-actor.ts | 267 -------------------- .../migrations/0135-video-channel-actor.ts | 268 +++++++++++++++++++++ 2 files changed, 268 insertions(+), 267 deletions(-) delete mode 100644 server/initializers/migrations/0130-video-channel-actor.ts create mode 100644 server/initializers/migrations/0135-video-channel-actor.ts (limited to 'server/initializers/migrations') diff --git a/server/initializers/migrations/0130-video-channel-actor.ts b/server/initializers/migrations/0130-video-channel-actor.ts deleted file mode 100644 index fab77cf4a..000000000 --- a/server/initializers/migrations/0130-video-channel-actor.ts +++ /dev/null @@ -1,267 +0,0 @@ -import * as Sequelize from 'sequelize' -import { DataType } from 'sequelize-typescript' -import { createPrivateAndPublicKeys } from '../../helpers' - -async function up (utils: { - transaction: Sequelize.Transaction, - queryInterface: Sequelize.QueryInterface, - sequelize: Sequelize.Sequelize -}): Promise { - // Create actor table - { - const queries = [ - ` - CREATE TYPE enum_actor_type AS ENUM ( - 'Group', - 'Person', - 'Application' - ) - `, - ` - CREATE TABLE actor ( - id integer NOT NULL, - type enum_actor_type NOT NULL, - uuid uuid NOT NULL, - "preferredUsername" character varying(255) NOT NULL, - url character varying(2000) NOT NULL, - "publicKey" character varying(5000), - "privateKey" character varying(5000), - "followersCount" integer NOT NULL, - "followingCount" integer NOT NULL, - "inboxUrl" character varying(2000) NOT NULL, - "outboxUrl" character varying(2000) NOT NULL, - "sharedInboxUrl" character varying(2000) NOT NULL, - "followersUrl" character varying(2000) NOT NULL, - "followingUrl" character varying(2000) NOT NULL, - "avatarId" integer, - "serverId" integer, - "createdAt" timestamp with time zone NOT NULL, - "updatedAt" timestamp with time zone NOT NULL - );`, - ` - CREATE SEQUENCE actor_id_seq - AS integer - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1 - `, - `ALTER SEQUENCE actor_id_seq OWNED BY actor.id`, - `ALTER TABLE ONLY actor ALTER COLUMN id SET DEFAULT nextval('actor_id_seq'::regclass)`, - `ALTER TABLE ONLY actor ADD CONSTRAINT actor_pkey PRIMARY KEY (id);`, - `CREATE UNIQUE INDEX actor_preferred_username_server_id ON actor USING btree ("preferredUsername", "serverId")`, - `ALTER TABLE ONLY actor - ADD CONSTRAINT "actor_avatarId_fkey" FOREIGN KEY ("avatarId") REFERENCES avatar(id) ON UPDATE CASCADE ON DELETE CASCADE`, - `ALTER TABLE ONLY actor - ADD CONSTRAINT "actor_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES server(id) ON UPDATE CASCADE ON DELETE CASCADE;` - ] - - for (const query of queries) { - await utils.sequelize.query(query) - } - } - - { - // tslint:disable:no-trailing-whitespace - const query1 = - ` - INSERT INTO "actor" - ( - type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", - "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" - ) - SELECT - 'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", - "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" - FROM account - WHERE "applicationId" IS NOT NULL - ` - await utils.sequelize.query(query1) - - const query2 = - ` - INSERT INTO "actor" - ( - type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", - "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" - ) - SELECT - 'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", - "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" - FROM account - WHERE "applicationId" IS NULL - ` - await utils.sequelize.query(query2) - } - - { - const data = { - type: DataType.INTEGER, - allowNull: true, - references: { - model: 'actor', - key: 'id' - }, - onDelete: 'CASCADE' - } - await utils.queryInterface.addColumn('account', 'actorId', data) - - const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)` - await utils.sequelize.query(query1) - - data.allowNull = false - await utils.queryInterface.changeColumn('account', 'actorId', data) - } - - { - const query = ` - INSERT INTO actor - ( - type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", - "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" - ) - SELECT - 'Group', "videoChannel".uuid, "videoChannel".uuid, "videoChannel".url, null, null, 0, 0, "videoChannel".url || '/inbox', - "videoChannel".url || '/outbox', "videoChannel".url || '/inbox', "videoChannel".url || '/followers', "videoChannel".url || '/following', - null, account."serverId", "videoChannel"."createdAt", "videoChannel"."updatedAt" - FROM "videoChannel" - INNER JOIN "account" on "videoChannel"."accountId" = "account".id - ` - await utils.sequelize.query(query) - } - - { - const data = { - type: DataType.INTEGER, - allowNull: true, - references: { - model: 'actor', - key: 'id' - }, - onDelete: 'CASCADE' - } - await utils.queryInterface.addColumn('videoChannel', 'actorId', data) - - const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)` - await utils.sequelize.query(query1) - - data.allowNull = false - await utils.queryInterface.changeColumn('videoChannel', 'actorId', data) - } - - { - await utils.queryInterface.renameTable('accountFollow', 'actorFollow') - await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId') - await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId') - - try { - await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey') - await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey') - } catch { - await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey') - await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey') - } - - { - const query1 = `UPDATE "actorFollow" - SET "actorId" = - (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")` - await utils.sequelize.query(query1) - - const query2 = `UPDATE "actorFollow" - SET "targetActorId" = - (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")` - - await utils.sequelize.query(query2) - } - - { - const query1 = `ALTER TABLE ONLY "actorFollow" - ADD CONSTRAINT "actorFollow_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;` - await utils.sequelize.query(query1) - - const query2 = `ALTER TABLE ONLY "actorFollow" - ADD CONSTRAINT "actorFollow_targetActorId_fkey" FOREIGN KEY ("targetActorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;` - await utils.sequelize.query(query2) - } - } - - { - await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId') - - try { - await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey') - } catch { - await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey') - } - - const query = `UPDATE "videoShare" - SET "actorId" = - (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")` - await utils.sequelize.query(query) - - { - const query1 = `ALTER TABLE ONLY "videoShare" - ADD CONSTRAINT "videoShare_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;` - await utils.sequelize.query(query1) - - const query2 = `ALTER TABLE ONLY "videoShare" - ADD CONSTRAINT "videoShare_videoId_fkey" FOREIGN KEY ("videoId") REFERENCES video(id) ON UPDATE CASCADE ON DELETE CASCADE;` - await utils.sequelize.query(query2) - } - } - - { - const columnsToDelete = [ - 'uuid', - 'url', - 'publicKey', - 'privateKey', - 'followersCount', - 'followingCount', - 'inboxUrl', - 'outboxUrl', - 'sharedInboxUrl', - 'followersUrl', - 'followingUrl', - 'serverId', - 'avatarId' - ] - for (const columnToDelete of columnsToDelete) { - await utils.queryInterface.removeColumn('account', columnToDelete) - } - } - - { - const columnsToDelete = [ - 'uuid', - 'remote', - 'url' - ] - for (const columnToDelete of columnsToDelete) { - await utils.queryInterface.removeColumn('videoChannel', columnToDelete) - } - } - - { - const query = 'SELECT * FROM "actor" WHERE "serverId" IS NULL AND "publicKey" IS NULL' - const [ res ] = await utils.sequelize.query(query) - - for (const actor of res) { - const { privateKey, publicKey } = await createPrivateAndPublicKeys() - - const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}` - await utils.sequelize.query(queryUpdate) - } - } -} - -function down (options) { - throw new Error('Not implemented.') -} - -export { - up, - down -} diff --git a/server/initializers/migrations/0135-video-channel-actor.ts b/server/initializers/migrations/0135-video-channel-actor.ts new file mode 100644 index 000000000..3c5c10ad6 --- /dev/null +++ b/server/initializers/migrations/0135-video-channel-actor.ts @@ -0,0 +1,268 @@ +import * as Sequelize from 'sequelize' +import { DataType } from 'sequelize-typescript' +import { createPrivateAndPublicKeys } from '../../helpers' + +async function up (utils: { + transaction: Sequelize.Transaction, + queryInterface: Sequelize.QueryInterface, + sequelize: Sequelize.Sequelize +}): Promise { + // Create actor table + { + const queries = [ + `DROP TYPE IF EXISTS enum_actor_type`, + ` + CREATE TYPE enum_actor_type AS ENUM ( + 'Group', + 'Person', + 'Application' + ) + `, + ` + CREATE TABLE actor ( + id integer NOT NULL, + type enum_actor_type NOT NULL, + uuid uuid NOT NULL, + "preferredUsername" character varying(255) NOT NULL, + url character varying(2000) NOT NULL, + "publicKey" character varying(5000), + "privateKey" character varying(5000), + "followersCount" integer NOT NULL, + "followingCount" integer NOT NULL, + "inboxUrl" character varying(2000) NOT NULL, + "outboxUrl" character varying(2000) NOT NULL, + "sharedInboxUrl" character varying(2000) NOT NULL, + "followersUrl" character varying(2000) NOT NULL, + "followingUrl" character varying(2000) NOT NULL, + "avatarId" integer, + "serverId" integer, + "createdAt" timestamp with time zone NOT NULL, + "updatedAt" timestamp with time zone NOT NULL + );`, + ` + CREATE SEQUENCE actor_id_seq + AS integer + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1 + `, + `ALTER SEQUENCE actor_id_seq OWNED BY actor.id`, + `ALTER TABLE ONLY actor ALTER COLUMN id SET DEFAULT nextval('actor_id_seq'::regclass)`, + `ALTER TABLE ONLY actor ADD CONSTRAINT actor_pkey PRIMARY KEY (id);`, + `CREATE UNIQUE INDEX actor_preferred_username_server_id ON actor USING btree ("preferredUsername", "serverId")`, + `ALTER TABLE ONLY actor + ADD CONSTRAINT "actor_avatarId_fkey" FOREIGN KEY ("avatarId") REFERENCES avatar(id) ON UPDATE CASCADE ON DELETE CASCADE`, + `ALTER TABLE ONLY actor + ADD CONSTRAINT "actor_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES server(id) ON UPDATE CASCADE ON DELETE CASCADE;` + ] + + for (const query of queries) { + await utils.sequelize.query(query) + } + } + + { + // tslint:disable:no-trailing-whitespace + const query1 = + ` + INSERT INTO "actor" + ( + type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", + "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" + ) + SELECT + 'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", + "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" + FROM account + WHERE "applicationId" IS NOT NULL + ` + await utils.sequelize.query(query1) + + const query2 = + ` + INSERT INTO "actor" + ( + type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", + "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" + ) + SELECT + 'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", + "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" + FROM account + WHERE "applicationId" IS NULL + ` + await utils.sequelize.query(query2) + } + + { + const data = { + type: DataType.INTEGER, + allowNull: true, + references: { + model: 'actor', + key: 'id' + }, + onDelete: 'CASCADE' + } + await utils.queryInterface.addColumn('account', 'actorId', data) + + const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)` + await utils.sequelize.query(query1) + + data.allowNull = false + await utils.queryInterface.changeColumn('account', 'actorId', data) + } + + { + const query = ` + INSERT INTO actor + ( + type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl", + "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt" + ) + SELECT + 'Group', "videoChannel".uuid, "videoChannel".uuid, "videoChannel".url, null, null, 0, 0, "videoChannel".url || '/inbox', + "videoChannel".url || '/outbox', "videoChannel".url || '/inbox', "videoChannel".url || '/followers', "videoChannel".url || '/following', + null, account."serverId", "videoChannel"."createdAt", "videoChannel"."updatedAt" + FROM "videoChannel" + INNER JOIN "account" on "videoChannel"."accountId" = "account".id + ` + await utils.sequelize.query(query) + } + + { + const data = { + type: DataType.INTEGER, + allowNull: true, + references: { + model: 'actor', + key: 'id' + }, + onDelete: 'CASCADE' + } + await utils.queryInterface.addColumn('videoChannel', 'actorId', data) + + const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)` + await utils.sequelize.query(query1) + + data.allowNull = false + await utils.queryInterface.changeColumn('videoChannel', 'actorId', data) + } + + { + await utils.queryInterface.renameTable('accountFollow', 'actorFollow') + await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId') + await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId') + + try { + await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey') + await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey') + } catch { + await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey') + await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey') + } + + { + const query1 = `UPDATE "actorFollow" + SET "actorId" = + (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")` + await utils.sequelize.query(query1) + + const query2 = `UPDATE "actorFollow" + SET "targetActorId" = + (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")` + + await utils.sequelize.query(query2) + } + + { + const query1 = `ALTER TABLE ONLY "actorFollow" + ADD CONSTRAINT "actorFollow_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;` + await utils.sequelize.query(query1) + + const query2 = `ALTER TABLE ONLY "actorFollow" + ADD CONSTRAINT "actorFollow_targetActorId_fkey" FOREIGN KEY ("targetActorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;` + await utils.sequelize.query(query2) + } + } + + { + await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId') + + try { + await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey') + } catch { + await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey') + } + + const query = `UPDATE "videoShare" + SET "actorId" = + (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")` + await utils.sequelize.query(query) + + { + const query1 = `ALTER TABLE ONLY "videoShare" + ADD CONSTRAINT "videoShare_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;` + await utils.sequelize.query(query1) + + const query2 = `ALTER TABLE ONLY "videoShare" + ADD CONSTRAINT "videoShare_videoId_fkey" FOREIGN KEY ("videoId") REFERENCES video(id) ON UPDATE CASCADE ON DELETE CASCADE;` + await utils.sequelize.query(query2) + } + } + + { + const columnsToDelete = [ + 'uuid', + 'url', + 'publicKey', + 'privateKey', + 'followersCount', + 'followingCount', + 'inboxUrl', + 'outboxUrl', + 'sharedInboxUrl', + 'followersUrl', + 'followingUrl', + 'serverId', + 'avatarId' + ] + for (const columnToDelete of columnsToDelete) { + await utils.queryInterface.removeColumn('account', columnToDelete) + } + } + + { + const columnsToDelete = [ + 'uuid', + 'remote', + 'url' + ] + for (const columnToDelete of columnsToDelete) { + await utils.queryInterface.removeColumn('videoChannel', columnToDelete) + } + } + + { + const query = 'SELECT * FROM "actor" WHERE "serverId" IS NULL AND "publicKey" IS NULL' + const [ res ] = await utils.sequelize.query(query) + + for (const actor of res) { + const { privateKey, publicKey } = await createPrivateAndPublicKeys() + + const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}` + await utils.sequelize.query(queryUpdate) + } + } +} + +function down (options) { + throw new Error('Not implemented.') +} + +export { + up, + down +} -- cgit v1.2.3