]>
Commit | Line | Data |
---|---|---|
a7d647c4 C |
1 | import * as Sequelize from 'sequelize' |
2 | import { DataType } from 'sequelize-typescript' | |
da854ddd | 3 | import { createPrivateAndPublicKeys } from '../../helpers/peertube-crypto' |
a7d647c4 C |
4 | |
5 | async 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 | ||
253 | function down (options) { | |
254 | throw new Error('Not implemented.') | |
255 | } | |
256 | ||
257 | export { | |
258 | up, | |
259 | down | |
260 | } |