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