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)
63 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
64 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
67 'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
68 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
70 WHERE "applicationId" IS NOT NULL
72 await utils.sequelize.query(query1)
78 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
79 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
82 'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
83 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
85 WHERE "applicationId" IS NULL
87 await utils.sequelize.query(query2)
92 type: DataType.INTEGER,
100 await utils.queryInterface.addColumn('account', 'actorId', data)
102 const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)`
103 await utils.sequelize.query(query1)
105 data.allowNull = false
106 await utils.queryInterface.changeColumn('account', 'actorId', data)
113 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
114 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
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"
121 INNER JOIN "account" on "videoChannel"."accountId" = "account".id
123 await utils.sequelize.query(query)
128 type: DataType.INTEGER,
136 await utils.queryInterface.addColumn('videoChannel', 'actorId', data)
138 const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)`
139 await utils.sequelize.query(query1)
141 data.allowNull = false
142 await utils.queryInterface.changeColumn('videoChannel', 'actorId', data)
146 await utils.queryInterface.renameTable('accountFollow', 'actorFollow')
147 await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId')
148 await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId')
151 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey')
152 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey')
154 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey')
155 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey')
159 const query1 = `UPDATE "actorFollow"
161 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")`
162 await utils.sequelize.query(query1)
164 const query2 = `UPDATE "actorFollow"
165 SET "targetActorId" =
166 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")`
168 await utils.sequelize.query(query2)
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)
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)
183 await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId')
186 await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey')
188 await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey')
191 const query = `UPDATE "videoShare"
193 (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")`
194 await utils.sequelize.query(query)
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)
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)
208 const columnsToDelete = [
223 for (const columnToDelete of columnsToDelete) {
224 await utils.queryInterface.removeColumn('account', columnToDelete)
229 const columnsToDelete = [
234 for (const columnToDelete of columnsToDelete) {
235 await utils.queryInterface.removeColumn('videoChannel', columnToDelete)
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)
244 for (const actor of res) {
245 const { privateKey, publicKey } = await createPrivateAndPublicKeys()
247 const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}`
248 await utils.sequelize.query(queryUpdate)
253 function down (options) {
254 throw new Error('Not implemented.')