server/initializers/migrations/0760-video-live-replay-setting.ts

   1 import * as Sequelize from 'sequelize'
   2
   3 async function up (utils: {
   4   transaction: Sequelize.Transaction
   5   queryInterface: Sequelize.QueryInterface
   6   sequelize: Sequelize.Sequelize
   7 }): Promise<void> {
   8   {
   9     const query = `
  10     CREATE TABLE IF NOT EXISTS "videoLiveReplaySetting" (
  11       "id"   SERIAL ,
  12       "privacy" INTEGER NOT NULL,
  13       "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  14       "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  15       PRIMARY KEY ("id")
  16       );
  17     `
  18
  19     await utils.sequelize.query(query, { transaction : utils.transaction })
  20   }
  21
  22   {
  23     await utils.queryInterface.addColumn('videoLive', 'replaySettingId', {
  24       type: Sequelize.INTEGER,
  25       defaultValue: null,
  26       allowNull: true,
  27       references: {
  28         model: 'videoLiveReplaySetting',
  29         key: 'id'
  30       },
  31       onDelete: 'SET NULL'
  32     }, { transaction: utils.transaction })
  33   }
  34
  35   {
  36     await utils.queryInterface.addColumn('videoLiveSession', 'replaySettingId', {
  37       type: Sequelize.INTEGER,
  38       defaultValue: null,
  39       allowNull: true,
  40       references: {
  41         model: 'videoLiveReplaySetting',
  42         key: 'id'
  43       },
  44       onDelete: 'SET NULL'
  45     }, { transaction: utils.transaction })
  46   }
  47
  48   {
  49     const query = `
  50     SELECT live."id", v."privacy"
  51     FROM "videoLive" live
  52     INNER JOIN "video" v ON live."videoId" = v."id"
  53     WHERE live."saveReplay" = true
  54     `
  55
  56     const videoLives = await utils.sequelize.query<{ id: number, privacy: number }>(
  57       query,
  58       { type: Sequelize.QueryTypes.SELECT, transaction: utils.transaction }
  59     )
  60
  61     for (const videoLive of videoLives) {
  62       const query = `
  63       WITH new_replay_setting AS (
  64         INSERT INTO "videoLiveReplaySetting" ("privacy", "createdAt", "updatedAt")
  65         VALUES (:privacy, NOW(), NOW())
  66         RETURNING id
  67         )
  68       UPDATE "videoLive" SET "replaySettingId" = (SELECT id FROM new_replay_setting)
  69       WHERE "id" = :id
  70       `
  71
  72       const options = {
  73         replacements: { privacy: videoLive.privacy, id: videoLive.id },
  74         type: Sequelize.QueryTypes.UPDATE,
  75         transaction: utils.transaction
  76       }
  77
  78       await utils.sequelize.query(query, options)
  79     }
  80   }
  81
  82   {
  83     const query = `
  84     SELECT session."id", v."privacy"
  85     FROM "videoLiveSession" session
  86     INNER JOIN "video" v ON session."liveVideoId" = v."id"
  87     WHERE session."saveReplay" = true
  88       AND session."liveVideoId" IS NOT NULL;
  89     `
  90
  91     const videoLiveSessions = await utils.sequelize.query<{ id: number, privacy: number }>(
  92       query,
  93       { type: Sequelize.QueryTypes.SELECT, transaction: utils.transaction }
  94     )
  95
  96     for (const videoLive of videoLiveSessions) {
  97       const query = `
  98       WITH new_replay_setting AS (
  99         INSERT INTO "videoLiveReplaySetting" ("privacy", "createdAt", "updatedAt")
 100         VALUES (:privacy, NOW(), NOW())
 101         RETURNING id
 102         )
 103       UPDATE "videoLiveSession" SET "replaySettingId" = (SELECT id FROM new_replay_setting)
 104       WHERE "id" = :id
 105       `
 106
 107       const options = {
 108         replacements: { privacy: videoLive.privacy, id: videoLive.id },
 109         type: Sequelize.QueryTypes.UPDATE,
 110         transaction: utils.transaction
 111       }
 112
 113       await utils.sequelize.query(query, options)
 114     }
 115   }
 116 }
 117
 118 function down (options) {
 119   throw new Error('Not implemented.')
 120 }
 121
 122 export {
 123   up,
 124   down
 125 }