]> git.immae.eu Git - github/Chocobozzz/PeerTube.git/blame - server/initializers/migrations/0135-video-channel-actor.ts
Update translations
[github/Chocobozzz/PeerTube.git] / server / initializers / migrations / 0135-video-channel-actor.ts
CommitLineData
a7d647c4
C
1import * as Sequelize from 'sequelize'
2import { DataType } from 'sequelize-typescript'
da854ddd 3import { createPrivateAndPublicKeys } from '../../helpers/peertube-crypto'
a7d647c4
C
4
5async function up (utils: {
a1587156
C
6 transaction: Sequelize.Transaction
7 queryInterface: Sequelize.QueryInterface
a7d647c4
C
8 sequelize: Sequelize.Sequelize
9}): Promise<void> {
10 // Create actor table
11 {
12 const queries = [
c4bc8500 13 `DROP TYPE IF EXISTS enum_actor_type`,
a7d647c4
C
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,
e12a0092 26 "preferredUsername" character varying(255) NOT NULL,
a7d647c4
C
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 );`,
f9fcb010 42 `CREATE SEQUENCE actor_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1`,
a7d647c4
C
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);`,
e12a0092 46 `CREATE UNIQUE INDEX actor_preferred_username_server_id ON actor USING btree ("preferredUsername", "serverId")`,
a7d647c4
C
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 {
a7d647c4
C
59 const query1 =
60 `
61 INSERT INTO "actor"
62 (
e12a0092 63 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
a7d647c4
C
64 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
65 )
a1587156 66 SELECT
a7d647c4 67 'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
a1587156
C
68 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
69 FROM account
a7d647c4
C
70 WHERE "applicationId" IS NOT NULL
71 `
72 await utils.sequelize.query(query1)
73
74 const query2 =
75 `
76 INSERT INTO "actor"
77 (
e12a0092 78 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
a7d647c4
C
79 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
80 )
a1587156 81 SELECT
a7d647c4 82 'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
a1587156
C
83 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
84 FROM account
a7d647c4
C
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,
c6bf1bb6
C
94 references: {
95 model: 'actor',
96 key: 'id'
97 },
98 onDelete: 'CASCADE'
a7d647c4
C
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)
a7d647c4
C
107 }
108
109 {
a1587156
C
110 const query = `
111 INSERT INTO actor
a7d647c4 112 (
a1587156 113 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
a7d647c4
C
114 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
115 )
a1587156
C
116 SELECT
117 'Group', "videoChannel".uuid, "videoChannel".uuid, "videoChannel".url, null, null, 0, 0, "videoChannel".url || '/inbox',
a7d647c4 118 "videoChannel".url || '/outbox', "videoChannel".url || '/inbox', "videoChannel".url || '/followers', "videoChannel".url || '/following',
a1587156
C
119 null, account."serverId", "videoChannel"."createdAt", "videoChannel"."updatedAt"
120 FROM "videoChannel"
a7d647c4
C
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,
c6bf1bb6
C
130 references: {
131 model: 'actor',
132 key: 'id'
133 },
134 onDelete: 'CASCADE'
a7d647c4
C
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)
a7d647c4
C
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
c6bf1bb6 158 {
a1587156
C
159 const query1 = `UPDATE "actorFollow"
160 SET "actorId" =
39fdb3c0 161 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")`
c6bf1bb6 162 await utils.sequelize.query(query1)
a7d647c4 163
a1587156
C
164 const query2 = `UPDATE "actorFollow"
165 SET "targetActorId" =
39fdb3c0 166 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")`
a7d647c4 167
c6bf1bb6
C
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 }
a7d647c4
C
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
a1587156
C
191 const query = `UPDATE "videoShare"
192 SET "actorId" =
a7d647c4
C
193 (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")`
194 await utils.sequelize.query(query)
c6bf1bb6
C
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 }
a7d647c4
C
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 }
555846c9
C
238
239 {
240 const query = 'SELECT * FROM "actor" WHERE "serverId" IS NULL AND "publicKey" IS NULL'
1735c825 241 const options = { type: Sequelize.QueryTypes.SELECT as Sequelize.QueryTypes.SELECT }
366caf8b 242 const [ res ] = await utils.sequelize.query<any>(query, options)
555846c9
C
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 }
a7d647c4
C
251}
252
253function down (options) {
254 throw new Error('Not implemented.')
255}
256
257export {
258 up,
259 down
260}