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

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