server/initializers/migrations/0130-video-channel-actor.ts

   1 import * as Sequelize from 'sequelize'
   2 import { DataType } from 'sequelize-typescript'
   3 import { createPrivateAndPublicKeys } from '../../helpers'
   4
   5 async function up (utils: {
   6   transaction: Sequelize.Transaction,
   7   queryInterface: Sequelize.QueryInterface,
   8   sequelize: Sequelize.Sequelize
   9 }): Promise<void> {
  10   // Create actor table
  11   {
  12     const queries = [
  13       `
  14       CREATE TYPE enum_actor_type AS ENUM (
  15         'Group',
  16         'Person',
  17         'Application'
  18       )
  19       `,
  20       `
  21       CREATE TABLE actor (
  22         id integer NOT NULL,
  23         type enum_actor_type NOT NULL,
  24         uuid uuid NOT NULL,
  25         "preferredUsername" character varying(255) NOT NULL,
  26         url character varying(2000) NOT NULL,
  27         "publicKey" character varying(5000),
  28         "privateKey" character varying(5000),
  29         "followersCount" integer NOT NULL,
  30         "followingCount" integer NOT NULL,
  31         "inboxUrl" character varying(2000) NOT NULL,
  32         "outboxUrl" character varying(2000) NOT NULL,
  33         "sharedInboxUrl" character varying(2000) NOT NULL,
  34         "followersUrl" character varying(2000) NOT NULL,
  35         "followingUrl" character varying(2000) NOT NULL,
  36         "avatarId" integer,
  37         "serverId" integer,
  38         "createdAt" timestamp with time zone NOT NULL,
  39         "updatedAt" timestamp with time zone NOT NULL
  40       );`,
  41       `
  42       CREATE SEQUENCE actor_id_seq
  43       AS integer
  44       START WITH 1
  45       INCREMENT BY 1
  46       NO MINVALUE
  47       NO MAXVALUE
  48       CACHE 1
  49       `,
  50       `ALTER SEQUENCE actor_id_seq OWNED BY actor.id`,
  51       `ALTER TABLE ONLY actor ALTER COLUMN id SET DEFAULT nextval('actor_id_seq'::regclass)`,
  52       `ALTER TABLE ONLY actor ADD CONSTRAINT actor_pkey PRIMARY KEY (id);`,
  53       `CREATE UNIQUE INDEX actor_preferred_username_server_id ON actor USING btree ("preferredUsername", "serverId")`,
  54       `ALTER TABLE ONLY actor
  55         ADD CONSTRAINT "actor_avatarId_fkey" FOREIGN KEY ("avatarId") REFERENCES avatar(id) ON UPDATE CASCADE ON DELETE CASCADE`,
  56       `ALTER TABLE ONLY actor
  57         ADD CONSTRAINT "actor_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES server(id) ON UPDATE CASCADE ON DELETE CASCADE;`
  58     ]
  59
  60     for (const query of queries) {
  61       await utils.sequelize.query(query)
  62     }
  63   }
  64
  65   {
  66     // tslint:disable:no-trailing-whitespace
  67     const query1 =
  68       `
  69       INSERT INTO "actor"
  70         (
  71           type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  72           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  73         )
  74         SELECT
  75           'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  76           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  77         FROM account
  78         WHERE "applicationId" IS NOT NULL
  79         `
  80     await utils.sequelize.query(query1)
  81
  82     const query2 =
  83       `
  84       INSERT INTO "actor"
  85         (
  86           type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  87           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  88         )
  89         SELECT
  90           'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  91           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  92         FROM account
  93         WHERE "applicationId" IS NULL
  94         `
  95     await utils.sequelize.query(query2)
  96   }
  97
  98   {
  99     const data = {
 100       type: DataType.INTEGER,
 101       allowNull: true,
 102       defaultValue: null
 103     }
 104     await utils.queryInterface.addColumn('account', 'actorId', data)
 105
 106     const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)`
 107     await utils.sequelize.query(query1)
 108
 109     data.allowNull = false
 110     await utils.queryInterface.changeColumn('account', 'actorId', data)
 111
 112     const query2 = `ALTER TABLE ONLY account
 113     ADD CONSTRAINT "account_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;
 114   `
 115     await utils.sequelize.query(query2)
 116   }
 117
 118   {
 119     const query = `
 120     INSERT INTO actor
 121     (
 122     type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
 123     "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
 124     )
 125     SELECT
 126     'Group', "videoChannel".uuid, "videoChannel".uuid, "videoChannel".url, null, null, 0, 0, "videoChannel".url || '/inbox',
 127     "videoChannel".url || '/outbox', "videoChannel".url || '/inbox', "videoChannel".url || '/followers', "videoChannel".url || '/following',
 128      null, account."serverId", "videoChannel"."createdAt", "videoChannel"."updatedAt"
 129      FROM "videoChannel"
 130      INNER JOIN "account" on "videoChannel"."accountId" = "account".id
 131     `
 132     await utils.sequelize.query(query)
 133   }
 134
 135   {
 136     const data = {
 137       type: DataType.INTEGER,
 138       allowNull: true,
 139       defaultValue: null
 140     }
 141     await utils.queryInterface.addColumn('videoChannel', 'actorId', data)
 142
 143     const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)`
 144     await utils.sequelize.query(query1)
 145
 146     data.allowNull = false
 147     await utils.queryInterface.changeColumn('videoChannel', 'actorId', data)
 148
 149     const query2 = `
 150     ALTER TABLE ONLY "videoChannel"
 151     ADD CONSTRAINT "videoChannel_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;
 152     `
 153     await utils.sequelize.query(query2)
 154   }
 155
 156   {
 157     await utils.queryInterface.renameTable('accountFollow', 'actorFollow')
 158     await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId')
 159     await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId')
 160
 161     try {
 162       await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey')
 163       await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey')
 164     } catch {
 165       await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey')
 166       await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey')
 167     }
 168
 169     const query1 = `UPDATE "actorFollow"
 170       SET "actorId" =
 171       (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")`
 172     await utils.sequelize.query(query1)
 173
 174     const query2 = `UPDATE "actorFollow"
 175       SET "targetActorId" =
 176       (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")`
 177
 178     await utils.sequelize.query(query2)
 179   }
 180
 181   {
 182     await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId')
 183
 184     try {
 185       await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey')
 186     } catch {
 187       await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey')
 188     }
 189
 190     const query = `UPDATE "videoShare"
 191       SET "actorId" =
 192       (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")`
 193     await utils.sequelize.query(query)
 194   }
 195
 196   {
 197     const columnsToDelete = [
 198       'uuid',
 199       'url',
 200       'publicKey',
 201       'privateKey',
 202       'followersCount',
 203       'followingCount',
 204       'inboxUrl',
 205       'outboxUrl',
 206       'sharedInboxUrl',
 207       'followersUrl',
 208       'followingUrl',
 209       'serverId',
 210       'avatarId'
 211     ]
 212     for (const columnToDelete of columnsToDelete) {
 213       await utils.queryInterface.removeColumn('account', columnToDelete)
 214     }
 215   }
 216
 217   {
 218     const columnsToDelete = [
 219       'uuid',
 220       'remote',
 221       'url'
 222     ]
 223     for (const columnToDelete of columnsToDelete) {
 224       await utils.queryInterface.removeColumn('videoChannel', columnToDelete)
 225     }
 226   }
 227
 228   {
 229     const query = 'SELECT * FROM "actor" WHERE "serverId" IS NULL AND "publicKey" IS NULL'
 230     const [ res ] = await utils.sequelize.query(query)
 231
 232     for (const actor of res) {
 233       const { privateKey, publicKey } = await createPrivateAndPublicKeys()
 234
 235       const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}`
 236       await utils.sequelize.query(queryUpdate)
 237     }
 238   }
 239 }
 240
 241 function down (options) {
 242   throw new Error('Not implemented.')
 243 }
 244
 245 export {
 246   up,
 247   down
 248 }