server/initializers/migrations/0080-video-channels.ts

   1 import { buildUUID } from '@shared/extra-utils'
   2 import * as Sequelize from 'sequelize'
   3
   4 async function up (utils: {
   5   transaction: Sequelize.Transaction
   6   queryInterface: Sequelize.QueryInterface
   7   sequelize: Sequelize.Sequelize
   8   db: any
   9 }): Promise<void> {
  10   const q = utils.queryInterface
  11
  12   // Assert not friends
  13
  14   // Create uuid column for author
  15   const dataAuthorUUID = {
  16     type: Sequelize.UUID,
  17     defaultValue: Sequelize.UUIDV4,
  18     allowNull: true
  19   }
  20   await q.addColumn('Authors', 'uuid', dataAuthorUUID)
  21
  22   // Set UUID to previous authors
  23   {
  24     const authors = await utils.db.Author.findAll()
  25     for (const author of authors) {
  26       author.uuid = buildUUID()
  27       await author.save()
  28     }
  29   }
  30
  31   dataAuthorUUID.allowNull = false
  32   await q.changeColumn('Authors', 'uuid', dataAuthorUUID)
  33
  34   // Create one author per user that does not already exist
  35   const users = await utils.db.User.findAll()
  36   for (const user of users) {
  37     const author = await utils.db.Author.find({ where: { userId: user.id } })
  38     if (!author) {
  39       await utils.db.Author.create({
  40         name: user.username,
  41         podId: null, // It is our pod
  42         userId: user.id
  43       })
  44     }
  45   }
  46
  47   // Create video channels table
  48   await utils.db.VideoChannel.sync()
  49
  50   // For each author, create its default video channel
  51   const authors = await utils.db.Author.findAll()
  52   for (const author of authors) {
  53     await utils.db.VideoChannel.create({
  54       name: `Default ${author.name} channel`,
  55       remote: false,
  56       authorId: author.id
  57     })
  58   }
  59
  60   // Create channelId column for videos
  61   const dataChannelId = {
  62     type: Sequelize.INTEGER,
  63     defaultValue: null,
  64     allowNull: true
  65   }
  66   await q.addColumn('Videos', 'channelId', dataChannelId)
  67
  68   const query = 'SELECT "id", "authorId" FROM "Videos"'
  69   const options = {
  70     type: Sequelize.QueryTypes.SELECT
  71   }
  72   const rawVideos = await utils.sequelize.query(query, options) as any
  73
  74   for (const rawVideo of rawVideos) {
  75     const videoChannel = await utils.db.VideoChannel.findOne({ where: { authorId: rawVideo.authorId } })
  76
  77     const video = await utils.db.Video.findByPk(rawVideo.id)
  78     video.channelId = videoChannel.id
  79     await video.save()
  80   }
  81
  82   dataChannelId.allowNull = false
  83   await q.changeColumn('Videos', 'channelId', dataChannelId)
  84
  85   const constraintName = 'Videos_channelId_fkey'
  86   const queryForeignKey = 'ALTER TABLE "Videos" ' +
  87     ' ADD CONSTRAINT "' + constraintName + '"' +
  88     ' FOREIGN KEY ("channelId") REFERENCES "VideoChannels" ON UPDATE CASCADE ON DELETE CASCADE'
  89
  90   await utils.sequelize.query(queryForeignKey)
  91
  92   await q.removeColumn('Videos', 'authorId')
  93 }
  94
  95 function down (options) {
  96   // update "Applications" SET "migrationVersion" = 75;
  97   // delete from "Authors";
  98   // alter table "Authors" drop column "uuid";
  99   // ALTER SEQUENCE "Authors_id_seq" RESTART WITH 1
 100   // INSERT INTO "Authors" ("name", "createdAt", "updatedAt", "userId") VALUES ('root', NOW(), NOW(), 1);
 101   // alter table "Videos" drop column "channelId";
 102   // drop table "VideoChannels";
 103   // alter table "Videos" add column "authorId" INTEGER DEFAULT 1;
 104   // alter table "Videos" ADD CONSTRAINT "coucou" FOREIGN KEY ("authorId") REFERENCES "Authors"
 105   throw new Error('Not implemented.')
 106 }
 107
 108 export {
 109   up,
 110   down
 111 }