1 import * as Sequelize from 'sequelize'
2 import { DataType } from 'sequelize-typescript'
3 import { createPrivateAndPublicKeys } from '../../helpers/peertube-crypto'
5 async function up (utils: {
6 transaction: Sequelize.Transaction
7 queryInterface: Sequelize.QueryInterface
8 sequelize: Sequelize.Sequelize
13 `DROP TYPE IF EXISTS enum_actor_type`,
15 CREATE TYPE enum_actor_type AS ENUM (
24 type enum_actor_type 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,
39 "createdAt" timestamp with time zone NOT NULL,
40 "updatedAt" timestamp with time zone NOT NULL
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;`
53 for (const query of queries) {
54 await utils.sequelize.query(query)
59 // tslint:disable:no-trailing-whitespace
64 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
65 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
68 'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
69 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
71 WHERE "applicationId" IS NOT NULL
73 await utils.sequelize.query(query1)
79 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
80 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
83 'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
84 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
86 WHERE "applicationId" IS NULL
88 await utils.sequelize.query(query2)
93 type: DataType.INTEGER,
101 await utils.queryInterface.addColumn('account', 'actorId', data)
103 const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)`
104 await utils.sequelize.query(query1)
106 data.allowNull = false
107 await utils.queryInterface.changeColumn('account', 'actorId', data)
114 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
115 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
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"
122 INNER JOIN "account" on "videoChannel"."accountId" = "account".id
124 await utils.sequelize.query(query)
129 type: DataType.INTEGER,
137 await utils.queryInterface.addColumn('videoChannel', 'actorId', data)
139 const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)`
140 await utils.sequelize.query(query1)
142 data.allowNull = false
143 await utils.queryInterface.changeColumn('videoChannel', 'actorId', data)
147 await utils.queryInterface.renameTable('accountFollow', 'actorFollow')
148 await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId')
149 await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId')
152 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey')
153 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey')
155 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey')
156 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey')
160 const query1 = `UPDATE "actorFollow"
162 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")`
163 await utils.sequelize.query(query1)
165 const query2 = `UPDATE "actorFollow"
166 SET "targetActorId" =
167 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")`
169 await utils.sequelize.query(query2)
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)
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)
184 await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId')
187 await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey')
189 await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey')
192 const query = `UPDATE "videoShare"
194 (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")`
195 await utils.sequelize.query(query)
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)
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)
209 const columnsToDelete = [
224 for (const columnToDelete of columnsToDelete) {
225 await utils.queryInterface.removeColumn('account', columnToDelete)
230 const columnsToDelete = [
235 for (const columnToDelete of columnsToDelete) {
236 await utils.queryInterface.removeColumn('videoChannel', columnToDelete)
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<any>(query, options)
245 for (const actor of res) {
246 const { privateKey, publicKey } = await createPrivateAndPublicKeys()
248 const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}`
249 await utils.sequelize.query(queryUpdate)
254 function down (options) {
255 throw new Error('Not implemented.')