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     // tslint:disable:no-trailing-whitespace
  60     const query1 =
  61       `
  62       INSERT INTO "actor"
  63         (
  64           type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  65           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  66         )
  67         SELECT
  68           'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  69           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  70         FROM account
  71         WHERE "applicationId" IS NOT NULL
  72         `
  73     await utils.sequelize.query(query1)
  74
  75     const query2 =
  76       `
  77       INSERT INTO "actor"
  78         (
  79           type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  80           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  81         )
  82         SELECT
  83           'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
  84           "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
  85         FROM account
  86         WHERE "applicationId" IS NULL
  87         `
  88     await utils.sequelize.query(query2)
  89   }
  90
  91   {
  92     const data = {
  93       type: DataType.INTEGER,
  94       allowNull: true,
  95       references: {
  96         model: 'actor',
  97         key: 'id'
  98       },
  99       onDelete: 'CASCADE'
 100     }
 101     await utils.queryInterface.addColumn('account', 'actorId', data)
 102
 103     const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)`
 104     await utils.sequelize.query(query1)
 105
 106     data.allowNull = false
 107     await utils.queryInterface.changeColumn('account', 'actorId', data)
 108   }
 109
 110   {
 111     const query = `
 112     INSERT INTO actor
 113     (
 114     type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
 115     "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
 116     )
 117     SELECT
 118     'Group', "videoChannel".uuid, "videoChannel".uuid, "videoChannel".url, null, null, 0, 0, "videoChannel".url || '/inbox',
 119     "videoChannel".url || '/outbox', "videoChannel".url || '/inbox', "videoChannel".url || '/followers', "videoChannel".url || '/following',
 120      null, account."serverId", "videoChannel"."createdAt", "videoChannel"."updatedAt"
 121      FROM "videoChannel"
 122      INNER JOIN "account" on "videoChannel"."accountId" = "account".id
 123     `
 124     await utils.sequelize.query(query)
 125   }
 126
 127   {
 128     const data = {
 129       type: DataType.INTEGER,
 130       allowNull: true,
 131       references: {
 132         model: 'actor',
 133         key: 'id'
 134       },
 135       onDelete: 'CASCADE'
 136     }
 137     await utils.queryInterface.addColumn('videoChannel', 'actorId', data)
 138
 139     const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)`
 140     await utils.sequelize.query(query1)
 141
 142     data.allowNull = false
 143     await utils.queryInterface.changeColumn('videoChannel', 'actorId', data)
 144   }
 145
 146   {
 147     await utils.queryInterface.renameTable('accountFollow', 'actorFollow')
 148     await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId')
 149     await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId')
 150
 151     try {
 152       await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey')
 153       await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey')
 154     } catch {
 155       await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey')
 156       await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey')
 157     }
 158
 159     {
 160       const query1 = `UPDATE "actorFollow"
 161       SET "actorId" =
 162       (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")`
 163       await utils.sequelize.query(query1)
 164
 165       const query2 = `UPDATE "actorFollow"
 166       SET "targetActorId" =
 167       (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")`
 168
 169       await utils.sequelize.query(query2)
 170     }
 171
 172     {
 173       const query1 = `ALTER TABLE ONLY "actorFollow"
 174     ADD CONSTRAINT "actorFollow_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
 175       await utils.sequelize.query(query1)
 176
 177       const query2 = `ALTER TABLE ONLY "actorFollow"
 178     ADD CONSTRAINT "actorFollow_targetActorId_fkey" FOREIGN KEY ("targetActorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
 179       await utils.sequelize.query(query2)
 180     }
 181   }
 182
 183   {
 184     await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId')
 185
 186     try {
 187       await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey')
 188     } catch {
 189       await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey')
 190     }
 191
 192     const query = `UPDATE "videoShare"
 193       SET "actorId" =
 194       (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")`
 195     await utils.sequelize.query(query)
 196
 197     {
 198       const query1 = `ALTER TABLE ONLY "videoShare"
 199     ADD CONSTRAINT "videoShare_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
 200       await utils.sequelize.query(query1)
 201
 202       const query2 = `ALTER TABLE ONLY "videoShare"
 203     ADD CONSTRAINT "videoShare_videoId_fkey" FOREIGN KEY ("videoId") REFERENCES video(id) ON UPDATE CASCADE ON DELETE CASCADE;`
 204       await utils.sequelize.query(query2)
 205     }
 206   }
 207
 208   {
 209     const columnsToDelete = [
 210       'uuid',
 211       'url',
 212       'publicKey',
 213       'privateKey',
 214       'followersCount',
 215       'followingCount',
 216       'inboxUrl',
 217       'outboxUrl',
 218       'sharedInboxUrl',
 219       'followersUrl',
 220       'followingUrl',
 221       'serverId',
 222       'avatarId'
 223     ]
 224     for (const columnToDelete of columnsToDelete) {
 225       await utils.queryInterface.removeColumn('account', columnToDelete)
 226     }
 227   }
 228
 229   {
 230     const columnsToDelete = [
 231       'uuid',
 232       'remote',
 233       'url'
 234     ]
 235     for (const columnToDelete of columnsToDelete) {
 236       await utils.queryInterface.removeColumn('videoChannel', columnToDelete)
 237     }
 238   }
 239
 240   {
 241     const query = 'SELECT * FROM "actor" WHERE "serverId" IS NULL AND "publicKey" IS NULL'
 242     const options = { type: Sequelize.QueryTypes.SELECT as Sequelize.QueryTypes.SELECT }
 243     const [ res ] = await utils.sequelize.query(query, options)
 244
 245     for (const actor of res) {
 246       const { privateKey, publicKey } = await createPrivateAndPublicKeys()
 247
 248       const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}`
 249       await utils.sequelize.query(queryUpdate)
 250     }
 251   }
 252 }
 253
 254 function down (options) {
 255   throw new Error('Not implemented.')
 256 }
 257
 258 export {
 259   up,
 260   down
 261 }