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
14 CREATE TYPE enum_actor_type AS ENUM (
23 type enum_actor_type NOT NULL,
25 "preferredUsername" character varying(255) NOT NULL,
26 url character varying(2000) NOT NULL,
27 "publicKey" character varying(5000),
28 "privateKey" character varying(5000),
29 "followersCount" integer NOT NULL,
30 "followingCount" integer NOT NULL,
31 "inboxUrl" character varying(2000) NOT NULL,
32 "outboxUrl" character varying(2000) NOT NULL,
33 "sharedInboxUrl" character varying(2000) NOT NULL,
34 "followersUrl" character varying(2000) NOT NULL,
35 "followingUrl" character varying(2000) NOT NULL,
38 "createdAt" timestamp with time zone NOT NULL,
39 "updatedAt" timestamp with time zone NOT NULL
42 CREATE SEQUENCE actor_id_seq
50 `ALTER SEQUENCE actor_id_seq OWNED BY actor.id`,
51 `ALTER TABLE ONLY actor ALTER COLUMN id SET DEFAULT nextval('actor_id_seq'::regclass)`,
52 `ALTER TABLE ONLY actor ADD CONSTRAINT actor_pkey PRIMARY KEY (id);`,
53 `CREATE UNIQUE INDEX actor_preferred_username_server_id ON actor USING btree ("preferredUsername", "serverId")`,
54 `ALTER TABLE ONLY actor
55 ADD CONSTRAINT "actor_avatarId_fkey" FOREIGN KEY ("avatarId") REFERENCES avatar(id) ON UPDATE CASCADE ON DELETE CASCADE`,
56 `ALTER TABLE ONLY actor
57 ADD CONSTRAINT "actor_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES server(id) ON UPDATE CASCADE ON DELETE CASCADE;`
60 for (const query of queries) {
61 await utils.sequelize.query(query)
66 // tslint:disable:no-trailing-whitespace
71 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
72 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
75 'Application', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
76 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
78 WHERE "applicationId" IS NOT NULL
80 await utils.sequelize.query(query1)
86 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
87 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
90 'Person', uuid, name, url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
91 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
93 WHERE "applicationId" IS NULL
95 await utils.sequelize.query(query2)
100 type: DataType.INTEGER,
108 await utils.queryInterface.addColumn('account', 'actorId', data)
110 const query1 = `UPDATE account SET "actorId" = (SELECT id FROM actor WHERE actor.url = account.url)`
111 await utils.sequelize.query(query1)
113 data.allowNull = false
114 await utils.queryInterface.changeColumn('account', 'actorId', data)
121 type, uuid, "preferredUsername", url, "publicKey", "privateKey", "followersCount", "followingCount", "inboxUrl", "outboxUrl",
122 "sharedInboxUrl", "followersUrl", "followingUrl", "avatarId", "serverId", "createdAt", "updatedAt"
125 'Group', "videoChannel".uuid, "videoChannel".uuid, "videoChannel".url, null, null, 0, 0, "videoChannel".url || '/inbox',
126 "videoChannel".url || '/outbox', "videoChannel".url || '/inbox', "videoChannel".url || '/followers', "videoChannel".url || '/following',
127 null, account."serverId", "videoChannel"."createdAt", "videoChannel"."updatedAt"
129 INNER JOIN "account" on "videoChannel"."accountId" = "account".id
131 await utils.sequelize.query(query)
136 type: DataType.INTEGER,
144 await utils.queryInterface.addColumn('videoChannel', 'actorId', data)
146 const query1 = `UPDATE "videoChannel" SET "actorId" = (SELECT id FROM actor WHERE actor.url = "videoChannel".url)`
147 await utils.sequelize.query(query1)
149 data.allowNull = false
150 await utils.queryInterface.changeColumn('videoChannel', 'actorId', data)
154 await utils.queryInterface.renameTable('accountFollow', 'actorFollow')
155 await utils.queryInterface.renameColumn('actorFollow', 'accountId', 'actorId')
156 await utils.queryInterface.renameColumn('actorFollow', 'targetAccountId', 'targetActorId')
159 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_accountId_fkey')
160 await utils.queryInterface.removeConstraint('actorFollow', 'AccountFollows_targetAccountId_fkey')
162 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_accountId_fkey')
163 await utils.queryInterface.removeConstraint('actorFollow', 'accountFollow_targetAccountId_fkey')
167 const query1 = `UPDATE "actorFollow"
169 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."actorId")`
170 await utils.sequelize.query(query1)
172 const query2 = `UPDATE "actorFollow"
173 SET "targetActorId" =
174 (SELECT "account"."actorId" FROM account WHERE "account"."id" = "actorFollow"."targetActorId")`
176 await utils.sequelize.query(query2)
180 const query1 = `ALTER TABLE ONLY "actorFollow"
181 ADD CONSTRAINT "actorFollow_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
182 await utils.sequelize.query(query1)
184 const query2 = `ALTER TABLE ONLY "actorFollow"
185 ADD CONSTRAINT "actorFollow_targetActorId_fkey" FOREIGN KEY ("targetActorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
186 await utils.sequelize.query(query2)
191 await utils.queryInterface.renameColumn('videoShare', 'accountId', 'actorId')
194 await utils.queryInterface.removeConstraint('videoShare', 'VideoShares_accountId_fkey')
196 await utils.queryInterface.removeConstraint('videoShare', 'videoShare_accountId_fkey')
199 const query = `UPDATE "videoShare"
201 (SELECT "actorId" FROM account WHERE id = "videoShare"."actorId")`
202 await utils.sequelize.query(query)
205 const query1 = `ALTER TABLE ONLY "videoShare"
206 ADD CONSTRAINT "videoShare_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES actor(id) ON UPDATE CASCADE ON DELETE CASCADE;`
207 await utils.sequelize.query(query1)
209 const query2 = `ALTER TABLE ONLY "videoShare"
210 ADD CONSTRAINT "videoShare_videoId_fkey" FOREIGN KEY ("videoId") REFERENCES video(id) ON UPDATE CASCADE ON DELETE CASCADE;`
211 await utils.sequelize.query(query2)
216 const columnsToDelete = [
231 for (const columnToDelete of columnsToDelete) {
232 await utils.queryInterface.removeColumn('account', columnToDelete)
237 const columnsToDelete = [
242 for (const columnToDelete of columnsToDelete) {
243 await utils.queryInterface.removeColumn('videoChannel', columnToDelete)
248 const query = 'SELECT * FROM "actor" WHERE "serverId" IS NULL AND "publicKey" IS NULL'
249 const [ res ] = await utils.sequelize.query(query)
251 for (const actor of res) {
252 const { privateKey, publicKey } = await createPrivateAndPublicKeys()
254 const queryUpdate = `UPDATE "actor" SET "publicKey" = '${publicKey}', "privateKey" = '${privateKey}' WHERE id = ${actor.id}`
255 await utils.sequelize.query(queryUpdate)
260 function down (options) {
261 throw new Error('Not implemented.')