1 import * as Sequelize from 'sequelize'
2 import { DataType } from 'sequelize-typescript'
3 import { createPrivateAndPublicKeys } from '../../helpers'
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
43 CREATE SEQUENCE actor_id_seq
51 `ALTER SEQUENCE actor_id_seq OWNED BY actor.id`,
52 `ALTER TABLE ONLY actor ALTER COLUMN id SET DEFAULT nextval('actor_id_seq'::regclass)`,
53 `ALTER TABLE ONLY actor ADD CONSTRAINT actor_pkey PRIMARY KEY (id);`,
54 `CREATE UNIQUE INDEX actor_preferred_username_server_id ON actor USING btree ("preferredUsername", "serverId")`,
55 `ALTER TABLE ONLY actor
56 ADD CONSTRAINT "actor_avatarId_fkey" FOREIGN KEY ("avatarId") REFERENCES avatar(id) ON UPDATE CASCADE ON DELETE CASCADE`,
57 `ALTER TABLE ONLY actor
58 ADD CONSTRAINT "actor_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES server(id) ON UPDATE CASCADE ON DELETE CASCADE;`
61 for (const query of queries) {
62 await utils.sequelize.query(query)
67 // tslint:disable:no-trailing-whitespace
72 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
73 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
76 'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
77 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
79 WHERE "applicationId" IS NOT NULL
81 await utils.sequelize.query(query1)
87 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
88 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
91 'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
92 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
94 WHERE "applicationId" IS NULL
96 await utils.sequelize.query(query2)
101 type: DataType.INTEGER,
109 await utils.queryInterface.addColumn('account', 'actorId', data)
111 const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)`
112 await utils.sequelize.query(query1)
114 data.allowNull = false
115 await utils.queryInterface.changeColumn('account', 'actorId', data)
122 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
123 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
126 'Group', "videoChannel".uuid, "videoChannel".uuid, "videoChannel".url, null, null, 0, 0, "videoChannel".url || '/inbox',
127 "videoChannel".url || '/outbox', "videoChannel".url || '/inbox', "videoChannel".url || '/followers', "videoChannel".url || '/following',
128 null, account."serverId", "videoChannel"."createdAt", "videoChannel"."updatedAt"
130 INNER JOIN "account" on "videoChannel"."accountId" = "account".id
132 await utils.sequelize.query(query)
137 type: DataType.INTEGER,
145 await utils.queryInterface.addColumn('videoChannel', 'actorId', data)
147 const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)`
148 await utils.sequelize.query(query1)
150 data.allowNull = false
151 await utils.queryInterface.changeColumn('videoChannel', 'actorId', data)
155 await utils.queryInterface.renameTable('accountFollow', 'actorFollow')
156 await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId')
157 await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId')
160 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey')
161 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey')
163 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey')
164 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey')
168 const query1 = `UPDATE "actorFollow"
170 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")`
171 await utils.sequelize.query(query1)
173 const query2 = `UPDATE "actorFollow"
174 SET "targetActorId" =
175 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")`
177 await utils.sequelize.query(query2)
181 const query1 = `ALTER TABLE ONLY "actorFollow"
182 ADD CONSTRAINT "actorFollow_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
183 await utils.sequelize.query(query1)
185 const query2 = `ALTER TABLE ONLY "actorFollow"
186 ADD CONSTRAINT "actorFollow_targetActorId_fkey" FOREIGN KEY ("targetActorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
187 await utils.sequelize.query(query2)
192 await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId')
195 await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey')
197 await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey')
200 const query = `UPDATE "videoShare"
202 (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")`
203 await utils.sequelize.query(query)
206 const query1 = `ALTER TABLE ONLY "videoShare"
207 ADD CONSTRAINT "videoShare_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
208 await utils.sequelize.query(query1)
210 const query2 = `ALTER TABLE ONLY "videoShare"
211 ADD CONSTRAINT "videoShare_videoId_fkey" FOREIGN KEY ("videoId") REFERENCES video(id) ON UPDATE CASCADE ON DELETE CASCADE;`
212 await utils.sequelize.query(query2)
217 const columnsToDelete = [
232 for (const columnToDelete of columnsToDelete) {
233 await utils.queryInterface.removeColumn('account', columnToDelete)
238 const columnsToDelete = [
243 for (const columnToDelete of columnsToDelete) {
244 await utils.queryInterface.removeColumn('videoChannel', columnToDelete)
249 const query = 'SELECT * FROM "actor" WHERE "serverId" IS NULL AND "publicKey" IS NULL'
250 const [ res ] = await utils.sequelize.query(query)
252 for (const actor of res) {
253 const { privateKey, publicKey } = await createPrivateAndPublicKeys()
255 const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}`
256 await utils.sequelize.query(queryUpdate)
261 function down (options) {
262 throw new Error('Not implemented.')