server/initializers/migrations/0055-video-uuid.ts

   1 import * as Sequelize from 'sequelize'
   2 import * as Promise from 'bluebird'
   3
   4 function up (utils: {
   5   transaction: Sequelize.Transaction,
   6   queryInterface: Sequelize.QueryInterface,
   7   sequelize: Sequelize.Sequelize
   8 }): Promise<void> {
   9   const q = utils.queryInterface
  10
  11   const dataUUID = {
  12     type: Sequelize.UUID,
  13     defaultValue: Sequelize.UUIDV4,
  14     allowNull: true
  15   }
  16
  17   return q.addColumn('Videos', 'uuid', dataUUID)
  18     .then(() => {
  19       const query = 'UPDATE "Videos" SET "uuid" = "id" WHERE "remoteId" IS NULL'
  20       return utils.sequelize.query(query)
  21     })
  22     .then(() => {
  23       const query = 'UPDATE "Videos" SET "uuid" = "remoteId" WHERE "remoteId" IS NOT NULL'
  24       return utils.sequelize.query(query)
  25     })
  26     .then(() => {
  27       dataUUID.defaultValue = null
  28
  29       return q.changeColumn('Videos', 'uuid', dataUUID)
  30     })
  31     .then(() => {
  32       return removeForeignKey(utils.sequelize, 'RequestVideoQadus')
  33     })
  34     .then(() => {
  35       return removeForeignKey(utils.sequelize, 'RequestVideoEvents')
  36     })
  37     .then(() => {
  38       return removeForeignKey(utils.sequelize, 'BlacklistedVideos')
  39     })
  40     .then(() => {
  41       return removeForeignKey(utils.sequelize, 'UserVideoRates')
  42     })
  43     .then(() => {
  44       return removeForeignKey(utils.sequelize, 'VideoAbuses')
  45     })
  46     .then(() => {
  47       return removeForeignKey(utils.sequelize, 'VideoTags')
  48     })
  49     .then(() => {
  50       const query = 'ALTER TABLE "Videos" DROP CONSTRAINT "Videos_pkey"'
  51       return utils.sequelize.query(query)
  52     })
  53     .then(() => {
  54       const query = 'ALTER TABLE "Videos" ADD COLUMN "id2" SERIAL PRIMARY KEY'
  55       return utils.sequelize.query(query)
  56     })
  57     .then(() => {
  58       return q.renameColumn('Videos', 'id', 'oldId')
  59     })
  60     .then(() => {
  61       return q.renameColumn('Videos', 'id2', 'id')
  62     })
  63     .then(() => {
  64       return changeForeignKey(q, utils.sequelize, 'RequestVideoQadus', false)
  65     })
  66     .then(() => {
  67       return changeForeignKey(q, utils.sequelize, 'RequestVideoEvents', false)
  68     })
  69     .then(() => {
  70       return changeForeignKey(q, utils.sequelize, 'BlacklistedVideos', false)
  71     })
  72     .then(() => {
  73       return changeForeignKey(q, utils.sequelize, 'UserVideoRates', false)
  74     })
  75     .then(() => {
  76       return changeForeignKey(q, utils.sequelize, 'VideoAbuses', false)
  77     })
  78     .then(() => {
  79       return changeForeignKey(q, utils.sequelize, 'VideoTags', true)
  80     })
  81     .then(() => {
  82       return q.removeColumn('Videos', 'oldId')
  83     })
  84     .then(() => {
  85       const dataRemote = {
  86         type: Sequelize.BOOLEAN,
  87         defaultValue: false,
  88         allowNull: false
  89       }
  90       return q.addColumn('Videos', 'remote', dataRemote)
  91     })
  92     .then(() => {
  93       const query = 'UPDATE "Videos" SET "remote" = false WHERE "remoteId" IS NULL'
  94       return utils.sequelize.query(query)
  95     })
  96     .then(() => {
  97       const query = 'UPDATE "Videos" SET "remote" = true WHERE "remoteId" IS NOT NULL'
  98       return utils.sequelize.query(query)
  99     })
 100     .then(() => {
 101       return q.removeColumn('Videos', 'remoteId')
 102     })
 103 }
 104
 105 function down (options) {
 106   throw new Error('Not implemented.')
 107 }
 108
 109 function removeForeignKey (sequelize: Sequelize.Sequelize, tableName: string) {
 110   const query = 'ALTER TABLE "' + tableName + '" DROP CONSTRAINT "' + tableName + '_videoId_fkey' + '"'
 111   return sequelize.query(query)
 112 }
 113
 114 function changeForeignKey (q: Sequelize.QueryInterface, sequelize: Sequelize.Sequelize, tableName: string, allowNull: boolean) {
 115   const data = {
 116     type: Sequelize.INTEGER,
 117     allowNull: true
 118   }
 119
 120   return q.addColumn(tableName, 'videoId2', data)
 121     .then(() => {
 122       const query = 'UPDATE "' + tableName + '" SET "videoId2" = ' +
 123                     '(SELECT "id" FROM "Videos" WHERE "' + tableName + '"."videoId" = "Videos"."oldId")'
 124       return sequelize.query(query)
 125     })
 126     .then(() => {
 127       if (allowNull === false) {
 128         data.allowNull = false
 129
 130         return q.changeColumn(tableName, 'videoId2', data)
 131       }
 132
 133       return Promise.resolve()
 134     })
 135     .then(() => {
 136       return q.removeColumn(tableName, 'videoId')
 137     })
 138     .then(() => {
 139       return q.renameColumn(tableName, 'videoId2', 'videoId')
 140     })
 141     .then(() => {
 142       return q.addIndex(tableName, [ 'videoId' ])
 143     })
 144     .then(() => {
 145       const constraintName = tableName + '_videoId_fkey'
 146       const query = 'ALTER TABLE "' + tableName + '" ' +
 147                     ' ADD CONSTRAINT "' + constraintName + '"' +
 148                     ' FOREIGN KEY ("videoId") REFERENCES "Videos" ON DELETE CASCADE'
 149
 150       return sequelize.query(query)
 151     })
 152 }
 153
 154 export {
 155   up,
 156   down
 157 }