1:
# frozen_string_literal: true
2:
0
3:
require 'base64'
0
4:
require 'bcrypt'
0
5:
require 'digest'
0
6:
require 'mysql2'
0
7:
require 'mysql2-cs-bind'
0
8:
require 'open3'
0
9:
require 'securerandom'
0
10:
require 'sinatra/base'
0
11:
require 'sinatra/json'
12:
0
13:
module Isupipe
0
14:
class App < Sinatra::Base
0
15:
enable :logging
0
16:
set :show_exceptions, :after_handler
0
17:
set :sessions, domain: 't.isucon.pw', path: '/', expire_after: 1000*60
0
18:
set :session_secret, ENV.fetch('ISUCON13_SESSION_SECRETKEY', 'isucon13_session_cookiestore_defaultsecret').unpack('H*')[0]
19:
0
20:
POWERDNS_SUBDOMAIN_ADDRESS = ENV.fetch('ISUCON13_POWERDNS_SUBDOMAIN_ADDRESS')
21:
0
22:
DEFAULT_SESSION_ID_KEY = 'SESSIONID'
0
23:
DEFAULT_SESSION_EXPIRES_KEY = 'EXPIRES'
0
24:
DEFAULT_USER_ID_KEY = 'USERID'
0
25:
DEFAULT_USERNAME_KEY = 'USERNAME'
26:
0
27:
class HttpError < StandardError
0
28:
attr_reader :code
29:
0
30:
def initialize(code, message = nil)
13
31:
super(message || "HTTP error #{code}")
13
32:
@code = code
33:
end
34:
end
35:
0
36:
error HttpError do
13
37:
e = env['sinatra.error']
13
38:
status e.code
13
39:
json(error: e.message)
40:
end
41:
0
42:
helpers do
0
43:
def db_conn
11450
44:
Thread.current[:db_conn] ||= connect_db
45:
end
46:
0
47:
def connect_db
21
48:
Mysql2::Client.new(
49:
host: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_ADDRESS', '127.0.0.1'),
50:
port: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_PORT', '3306').to_i,
51:
username: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_USER', 'isucon'),
52:
password: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_PASSWORD', 'isucon'),
53:
database: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_DATABASE', 'isupipe'),
54:
symbolize_keys: true,
55:
cast_booleans: true,
56:
reconnect: true,
57:
)
58:
end
59:
0
60:
def db_transaction(&block)
3818
61:
db_conn.query('BEGIN')
3818
62:
ok = false
63:
begin
3818
64:
retval = block.call(db_conn)
3802
65:
db_conn.query('COMMIT')
3802
66:
ok = true
3802
67:
retval
68:
ensure
3814
69:
unless ok
12
70:
db_conn.query('ROLLBACK')
71:
end
72:
end
73:
end
74:
0
75:
def decode_request_body(data_class)
1727
76:
body = JSON.parse(request.body.tap(&:rewind).read, symbolize_names: true)
6308
77:
data_class.new(**data_class.members.map { |key| [key, body[key]] }.to_h)
78:
end
79:
0
80:
def cast_as_integer(str)
755
81:
Integer(str, 10)
82:
rescue
0
83:
raise HttpError.new(400)
84:
end
85:
0
86:
def verify_user_session!
1410
87:
sess = session[DEFAULT_SESSION_ID_KEY]
1410
88:
unless sess
0
89:
raise HttpError.new(403)
90:
end
91:
1410
92:
session_expires = sess[DEFAULT_SESSION_EXPIRES_KEY]
1410
93:
unless session_expires
0
94:
raise HttpError.new(403)
95:
end
96:
1410
97:
now = Time.now
1410
98:
if now.to_i > session_expires
0
99:
raise HttpError.new(401, 'session has expired')
100:
end
101:
102:
nil
103:
end
104:
0
105:
def fill_livestream_response(tx, livestream_model)
4714
106:
owner_model = tx.xquery('SELECT * FROM users WHERE id = ?', livestream_model.fetch(:user_id)).first
4714
107:
owner = fill_user_response(tx, owner_model)
108:
4714
109:
tags = tx.xquery('SELECT * FROM livestream_tags WHERE livestream_id = ?', livestream_model.fetch(:id)).map do |livestream_tag_model|
15757
110:
tag_model = tx.xquery('SELECT * FROM tags WHERE id = ?', livestream_tag_model.fetch(:tag_id)).first
111:
{
15757
112:
id: tag_model.fetch(:id),
113:
name: tag_model.fetch(:name),
114:
}
115:
end
116:
4714
117:
livestream_model.slice(:id, :title, :description, :playlist_url, :thumbnail_url, :start_at, :end_at).merge(
118:
owner:,
119:
tags:,
120:
)
121:
end
122:
0
123:
def fill_livecomment_response(tx, livecomment_model)
1096
124:
comment_owner_model = tx.xquery('SELECT * FROM users WHERE id = ?', livecomment_model.fetch(:user_id)).first
1096
125:
comment_owner = fill_user_response(tx, comment_owner_model)
126:
1096
127:
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livecomment_model.fetch(:livestream_id)).first
1096
128:
livestream = fill_livestream_response(tx, livestream_model)
129:
1096
130:
livecomment_model.slice(:id, :comment, :tip, :created_at).merge(
131:
user: comment_owner,
132:
livestream:,
133:
)
134:
end
135:
0
136:
def fill_livecomment_report_response(tx, report_model)
62
137:
reporter_model = tx.xquery('SELECT * FROM users WHERE id = ?', report_model.fetch(:user_id)).first
62
138:
reporter = fill_user_response(tx, reporter_model)
139:
62
140:
livecomment_model = tx.xquery('SELECT * FROM livecomments WHERE id = ?', report_model.fetch(:livecomment_id)).first
62
141:
livecomment = fill_livecomment_response(tx, livecomment_model)
142:
62
143:
report_model.slice(:id, :created_at).merge(
144:
reporter:,
145:
livecomment:,
146:
)
147:
end
148:
0
149:
def fill_reaction_response(tx, reaction_model)
863
150:
user_model = tx.xquery('SELECT * FROM users WHERE id = ?', reaction_model.fetch(:user_id)).first
863
151:
user = fill_user_response(tx, user_model)
152:
863
153:
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', reaction_model.fetch(:livestream_id)).first
863
154:
livestream = fill_livestream_response(tx, livestream_model)
155:
863
156:
reaction_model.slice(:id, :emoji_name, :created_at).merge(
157:
user:,
158:
livestream:,
159:
)
160:
end
161:
0
162:
def fill_user_response(tx, user_model)
7172
163:
theme_model = tx.xquery('SELECT * FROM themes WHERE user_id = ?', user_model.fetch(:id)).first
164:
7172
165:
icon_model = tx.xquery('SELECT image FROM icons WHERE user_id = ?', user_model.fetch(:id)).first
166:
image =
7172
167:
if icon_model
4712
168:
icon_model.fetch(:image)
169:
else
2460
170:
File.binread(FALLBACK_IMAGE)
171:
end
7172
172:
icon_hash = Digest::SHA256.hexdigest(image)
173:
174:
{
7172
175:
id: user_model.fetch(:id),
176:
name: user_model.fetch(:name),
177:
display_name: user_model.fetch(:display_name),
178:
description: user_model.fetch(:description),
179:
theme: {
180:
id: theme_model.fetch(:id),
181:
dark_mode: theme_model.fetch(:dark_mode),
182:
},
183:
icon_hash:,
184:
}
185:
end
186:
end
187:
188:
# 初期化
0
189:
post '/api/initialize' do
1
190:
out, status = Open3.capture2e('../sql/init.sh')
1
191:
unless status.success?
0
192:
logger.warn("init.sh failed with out=#{out}")
0
193:
halt 500
194:
end
195:
1
196:
json(
197:
language: 'ruby',
198:
)
199:
end
200:
201:
# top
0
202:
get '/api/tag' do
62
203:
tag_models = db_transaction do |tx|
62
204:
tx.query('SELECT * FROM tags')
205:
end
206:
62
207:
json(
208:
tags: tag_models.map { |tag_model|
209:
{
6386
210:
id: tag_model.fetch(:id),
211:
name: tag_model.fetch(:name),
212:
}
213:
},
214:
)
215:
end
216:
217:
# 配信者のテーマ取得API
0
218:
get '/api/user/:username/theme' do
5
219:
verify_user_session!
220:
5
221:
username = params[:username]
222:
5
223:
theme_model = db_transaction do |tx|
5
224:
user_model = tx.xquery('SELECT id FROM users WHERE name = ?', username).first
5
225:
unless user_model
0
226:
raise HttpError.new(404)
227:
end
5
228:
tx.xquery('SELECT * FROM themes WHERE user_id = ?', user_model.fetch(:id)).first
229:
end
230:
5
231:
json(
232:
id: theme_model.fetch(:id),
233:
dark_mode: theme_model.fetch(:dark_mode),
234:
)
235:
end
236:
237:
# livestream
238:
0
239:
ReserveLivestreamRequest = Data.define(
240:
:tags,
241:
:title,
242:
:description,
243:
:playlist_url,
244:
:thumbnail_url,
245:
:start_at,
246:
:end_at,
247:
)
248:
249:
# reserve livestream
0
250:
post '/api/livestream/reservation' do
84
251:
verify_user_session!
84
252:
sess = session[DEFAULT_SESSION_ID_KEY]
84
253:
unless sess
0
254:
raise HttpError.new(401)
255:
end
84
256:
user_id = sess[DEFAULT_USER_ID_KEY]
84
257:
unless sess
0
258:
raise HttpError.new(401)
259:
end
260:
84
261:
req = decode_request_body(ReserveLivestreamRequest)
262:
84
263:
livestream = db_transaction do |tx|
264:
# 2023/11/25 10:00からの１年間の期間内であるかチェック
84
265:
term_start_at = Time.utc(2023, 11, 25, 1)
84
266:
term_end_at = Time.utc(2024, 11, 25, 1)
84
267:
reserve_start_at = Time.at(req.start_at, in: 'UTC')
84
268:
reserve_end_at = Time.at(req.end_at, in: 'UTC')
84
269:
if reserve_start_at >= term_end_at || reserve_end_at <= term_start_at
2
270:
raise HttpError.new(400, 'bad reservation time range')
271:
end
272:
273:
# 予約枠をみて、予約が可能か調べる
274:
# NOTE: 並列な予約のoverbooking防止にFOR UPDATEが必要
82
275:
tx.xquery('SELECT * FROM reservation_slots WHERE start_at >= ? AND end_at <= ? FOR UPDATE', req.start_at, req.end_at).each do |slot|
629
276:
count = tx.xquery('SELECT slot FROM reservation_slots WHERE start_at = ? AND end_at = ?', slot.fetch(:start_at), slot.fetch(:end_at)).first.fetch(:slot)
629
277:
logger.info("#{slot.fetch(:start_at)} ~ #{slot.fetch(:end_at)}予約枠の残数 = #{slot.fetch(:slot)}")
629
278:
if count < 1
1
279:
raise HttpError.new(400, "予約期間 #{term_start_at.to_i} ~ #{term_end_at.to_i}に対して、予約区間 #{req.start_at} ~ #{req.end_at}が予約できません")
280:
end
281:
end
282:
81
283:
tx.xquery('UPDATE reservation_slots SET slot = slot - 1 WHERE start_at >= ? AND end_at <= ?', req.start_at, req.end_at)
81
284:
tx.xquery('INSERT INTO livestreams (user_id, title, description, playlist_url, thumbnail_url, start_at, end_at) VALUES(?, ?, ?, ?, ?, ?, ?)', user_id, req.title, req.description, req.playlist_url, req.thumbnail_url, req.start_at, req.end_at)
81
285:
livestream_id = tx.last_id
286:
287:
# タグ追加
81
288:
req.tags.each do |tag_id|
327
289:
tx.xquery('INSERT INTO livestream_tags (livestream_id, tag_id) VALUES (?, ?)', livestream_id, tag_id)
290:
end
291:
81
292:
fill_livestream_response(tx, {
293:
id: livestream_id,
294:
user_id:,
295:
title: req.title,
296:
description: req.description,
297:
playlist_url: req.playlist_url,
298:
thumbnail_url: req.thumbnail_url,
299:
start_at: req.start_at,
300:
end_at: req.end_at,
301:
})
302:
end
303:
81
304:
status 201
81
305:
json(livestream)
306:
end
307:
308:
# list livestream
0
309:
get '/api/livestream/search' do
35
310:
key_tag_name = params[:tag] || ''
311:
35
312:
livestreams = db_transaction do |tx|
313:
livestream_models =
35
314:
if key_tag_name != ''
315:
# タグによる取得
13
316:
tag_id_list = tx.xquery('SELECT id FROM tags WHERE name = ?', key_tag_name, as: :array).map(&:first)
13
317:
tx.xquery('SELECT * FROM livestream_tags WHERE tag_id IN (?) ORDER BY livestream_id DESC', tag_id_list).map do |key_tagged_livestream|
1479
318:
tx.xquery('SELECT * FROM livestreams WHERE id = ?', key_tagged_livestream.fetch(:livestream_id)).first
319:
end
320:
else
321:
# 検索条件なし
22
322:
query = 'SELECT * FROM livestreams ORDER BY id DESC'
22
323:
limit_str = params[:limit] || ''
22
324:
if limit_str != ''
22
325:
limit = cast_as_integer(limit_str)
22
326:
query = "#{query} LIMIT #{limit}"
327:
end
328:
22
329:
tx.xquery(query).to_a
330:
end
331:
35
332:
livestream_models.map do |livestream_model|
2579
333:
fill_livestream_response(tx, livestream_model)
334:
end
335:
end
336:
35
337:
json(livestreams)
338:
end
339:
0
340:
get '/api/livestream' do
76
341:
verify_user_session!
76
342:
sess = session[DEFAULT_SESSION_ID_KEY]
76
343:
unless sess
0
344:
raise HttpError.new(401)
345:
end
76
346:
user_id = sess[DEFAULT_USER_ID_KEY]
76
347:
unless sess
0
348:
raise HttpError.new(401)
349:
end
350:
76
351:
livestreams = db_transaction do |tx|
76
352:
tx.xquery('SELECT * FROM livestreams WHERE user_id = ?', user_id).map do |livestream_model|
94
353:
fill_livestream_response(tx, livestream_model)
354:
end
355:
end
356:
76
357:
json(livestreams)
358:
end
359:
0
360:
get '/api/user/:username/livestream' do
0
361:
verify_user_session!
0
362:
username = params[:username]
363:
0
364:
livestreams = db_transaction do |tx|
0
365:
user = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
0
366:
unless user
0
367:
raise HttpError.new(404, 'user not found')
368:
end
369:
0
370:
tx.xquery('SELECT * FROM livestreams WHERE user_id = ?', user.fetch(:id)).map do |livestream_model|
0
371:
fill_livestream_response(tx, livestream_model)
372:
end
373:
end
374:
0
375:
json(livestreams)
376:
end
377:
378:
# ユーザ視聴開始 (viewer)
0
379:
post '/api/livestream/:livestream_id/enter' do
19
380:
verify_user_session!
19
381:
sess = session[DEFAULT_SESSION_ID_KEY]
19
382:
unless sess
0
383:
raise HttpError.new(401)
384:
end
19
385:
user_id = sess[DEFAULT_USER_ID_KEY]
19
386:
unless sess
0
387:
raise HttpError.new(401)
388:
end
389:
19
390:
livestream_id = cast_as_integer(params[:livestream_id])
391:
19
392:
db_transaction do |tx|
19
393:
created_at = Time.now.to_i
19
394:
tx.xquery('INSERT INTO livestream_viewers_history (user_id, livestream_id, created_at) VALUES(?, ?, ?)', user_id, livestream_id, created_at)
395:
end
396:
19
397:
''
398:
end
399:
400:
# ユーザ視聴終了 (viewer)
0
401:
delete '/api/livestream/:livestream_id/exit' do
9
402:
verify_user_session!
9
403:
sess = session[DEFAULT_SESSION_ID_KEY]
9
404:
unless sess
0
405:
raise HttpError.new(401)
406:
end
9
407:
user_id = sess[DEFAULT_USER_ID_KEY]
9
408:
unless sess
0
409:
raise HttpError.new(401)
410:
end
411:
9
412:
livestream_id = cast_as_integer(params[:livestream_id])
413:
9
414:
db_transaction do |tx|
9
415:
tx.xquery('DELETE FROM livestream_viewers_history WHERE user_id = ? AND livestream_id = ?', user_id, livestream_id)
416:
end
417:
9
418:
''
419:
end
420:
421:
# get livestream
0
422:
get '/api/livestream/:livestream_id' do
1
423:
verify_user_session!
424:
1
425:
livestream_id = cast_as_integer(params[:livestream_id])
426:
1
427:
livestream = db_transaction do |tx|
1
428:
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
1
429:
unless livestream_model
0
430:
raise HttpError.new(404)
431:
end
432:
1
433:
fill_livestream_response(tx, livestream_model)
434:
end
435:
1
436:
json(livestream)
437:
end
438:
439:
# (配信者向け)ライブコメントの報告一覧取得API
0
440:
get '/api/livestream/:livestream_id/report' do
21
441:
verify_user_session!
442:
21
443:
sess = session[DEFAULT_SESSION_ID_KEY]
21
444:
unless sess
0
445:
raise HttpError.new(401)
446:
end
21
447:
user_id = sess[DEFAULT_USER_ID_KEY]
21
448:
unless sess
0
449:
raise HttpError.new(401)
450:
end
451:
21
452:
livestream_id = cast_as_integer(params[:livestream_id])
453:
21
454:
reports = db_transaction do |tx|
21
455:
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
21
456:
if livestream_model.fetch(:user_id) != user_id
0
457:
raise HttpError.new(403, "can't get other streamer's livecomment reports")
458:
end
459:
21
460:
tx.xquery('SELECT * FROM livecomment_reports WHERE livestream_id = ?', livestream_id).map do |report_model|
20
461:
fill_livecomment_report_response(tx, report_model)
462:
end
463:
end
464:
21
465:
json(reports)
466:
end
467:
468:
# get polling livecomment timeline
0
469:
get '/api/livestream/:livestream_id/livecomment' do
160
470:
verify_user_session!
160
471:
livestream_id = cast_as_integer(params[:livestream_id])
472:
160
473:
livecomments = db_transaction do |tx|
160
474:
query = 'SELECT * FROM livecomments WHERE livestream_id = ? ORDER BY created_at DESC'
160
475:
limit_str = params[:limit] || ''
160
476:
if limit_str != ''
18
477:
limit = cast_as_integer(limit_str)
18
478:
query = "#{query} LIMIT #{limit}"
479:
end
480:
160
481:
tx.xquery(query, livestream_id).map do |livecomment_model|
864
482:
fill_livecomment_response(tx, livecomment_model)
483:
end
484:
end
485:
160
486:
json(livecomments)
487:
end
488:
0
489:
get '/api/livestream/:livestream_id/ngwords' do
15
490:
verify_user_session!
15
491:
sess = session[DEFAULT_SESSION_ID_KEY]
15
492:
unless sess
0
493:
raise HttpError.new(401)
494:
end
15
495:
user_id = sess[DEFAULT_USER_ID_KEY]
15
496:
unless sess
0
497:
raise HttpError.new(401)
498:
end
499:
15
500:
livestream_id = cast_as_integer(params[:livestream_id])
501:
15
502:
ng_words = db_transaction do |tx|
15
503:
tx.xquery('SELECT * FROM ng_words WHERE user_id = ? AND livestream_id = ? ORDER BY created_at DESC', user_id, livestream_id).to_a
504:
end
505:
15
506:
json(ng_words)
507:
end
508:
0
509:
PostLivecommentRequest = Data.define(
510:
:comment,
511:
:tip,
512:
)
513:
514:
# ライブコメント投稿
0
515:
post '/api/livestream/:livestream_id/livecomment' do
171
516:
verify_user_session!
171
517:
sess = session[DEFAULT_SESSION_ID_KEY]
171
518:
unless sess
0
519:
raise HttpError.new(401)
520:
end
171
521:
user_id = sess[DEFAULT_USER_ID_KEY]
171
522:
unless sess
0
523:
raise HttpError.new(401)
524:
end
525:
171
526:
livestream_id = cast_as_integer(params[:livestream_id])
527:
171
528:
req = decode_request_body(PostLivecommentRequest)
529:
171
530:
livecomment = db_transaction do |tx|
171
531:
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
171
532:
unless livestream_model
0
533:
raise HttpError.new(404, 'livestream not found')
534:
end
535:
536:
# スパム判定
171
537:
tx.xquery('SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = ? AND livestream_id = ?', livestream_model.fetch(:user_id), livestream_model.fetch(:id)).each do |ng_word|
33
538:
query = <<~SQL
539:
SELECT COUNT(*)
540:
FROM
541:
(SELECT ? AS text) AS texts
542:
INNER JOIN
543:
(SELECT CONCAT('%', ?, '%') AS pattern) AS patterns
544:
ON texts.text LIKE patterns.pattern
545:
SQL
33
546:
hit_spam = tx.xquery(query, req.comment, ng_word.fetch(:word), as: :array).first[0]
33
547:
logger.info("[hit_spam=#{hit_spam}] comment = #{req.comment}")
33
548:
if hit_spam >= 1
1
549:
raise HttpError.new(400, 'このコメントがスパム判定されました')
550:
end
551:
end
552:
170
553:
now = Time.now.to_i
170
554:
tx.xquery('INSERT INTO livecomments (user_id, livestream_id, comment, tip, created_at) VALUES (?, ?, ?, ?, ?)', user_id, livestream_id, req.comment, req.tip, now)
170
555:
livecomment_id = tx.last_id
556:
170
557:
fill_livecomment_response(tx, {
558:
id: livecomment_id,
559:
user_id:,
560:
livestream_id:,
561:
comment: req.comment,
562:
tip: req.tip,
563:
created_at: now,
564:
})
565:
end
566:
170
567:
status 201
170
568:
json(livecomment)
569:
end
570:
571:
# ライブコメント報告
0
572:
post '/api/livestream/:livestream_id/livecomment/:livecomment_id/report' do
48
573:
verify_user_session!
48
574:
sess = session[DEFAULT_SESSION_ID_KEY]
48
575:
unless sess
0
576:
raise HttpError.new(401)
577:
end
48
578:
user_id = sess[DEFAULT_USER_ID_KEY]
48
579:
unless user_id
0
580:
raise HttpError.new(401)
581:
end
582:
48
583:
livestream_id = cast_as_integer(params[:livestream_id])
48
584:
livecomment_id = cast_as_integer(params[:livecomment_id])
585:
48
586:
report = db_transaction do |tx|
48
587:
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
48
588:
unless livestream_model
0
589:
raise HttpError.new(404, 'livestream not found')
590:
end
591:
48
592:
livecomment_model = tx.xquery('SELECT * FROM livecomments WHERE id = ?', livecomment_id).first
48
593:
unless livecomment_model
6
594:
raise HttpError.new(404, 'livecomment not found')
595:
end
596:
42
597:
now = Time.now.to_i
42
598:
tx.xquery('INSERT INTO livecomment_reports(user_id, livestream_id, livecomment_id, created_at) VALUES (?, ?, ?, ?)', user_id, livestream_id, livecomment_id, now)
42
599:
report_id = tx.last_id
600:
42
601:
fill_livecomment_report_response(tx, {
602:
id: report_id,
603:
user_id:,
604:
livestream_id:,
605:
livecomment_id:,
606:
created_at: now,
607:
})
608:
end
609:
42
610:
status 201
42
611:
json(report)
612:
end
613:
0
614:
ModerateRequest = Data.define(:ng_word)
615:
616:
# 配信者によるモデレーション (NGワード登録)
0
617:
post '/api/livestream/:livestream_id/moderate' do
13
618:
verify_user_session!
13
619:
sess = session[DEFAULT_SESSION_ID_KEY]
13
620:
unless sess
0
621:
raise HttpError.new(401)
622:
end
13
623:
user_id = sess[DEFAULT_USER_ID_KEY]
13
624:
unless user_id
0
625:
raise HttpError.new(401)
626:
end
627:
13
628:
livestream_id = cast_as_integer(params[:livestream_id])
629:
13
630:
req = decode_request_body(ModerateRequest)
631:
13
632:
word_id = db_transaction do |tx|
633:
# 配信者自身の配信に対するmoderateなのかを検証
13
634:
owned_livestreams = tx.xquery('SELECT * FROM livestreams WHERE id = ? AND user_id = ?', livestream_id, user_id).to_a
13
635:
if owned_livestreams.empty?
0
636:
raise HttpError.new(400, "A streamer can't moderate livestreams that other streamers own")
637:
end
638:
13
639:
tx.xquery('INSERT INTO ng_words(user_id, livestream_id, word, created_at) VALUES (?, ?, ?, ?)', user_id, livestream_id, req.ng_word, Time.now.to_i)
13
640:
word_id = tx.last_id
641:
642:
# NGワードにヒットする過去の投稿も全削除する
13
643:
tx.xquery('SELECT * FROM ng_words WHERE livestream_id = ?', livestream_id).each do |ng_word|
644:
# ライブコメント一覧取得
14
645:
tx.xquery('SELECT * FROM livecomments').each do |livecomment|
15411
646:
query = <<~SQL
647:
DELETE FROM livecomments
648:
WHERE
649:
id = ? AND
650:
livestream_id = ? AND
651:
(SELECT COUNT(*)
652:
FROM
653:
(SELECT ? AS text) AS texts
654:
INNER JOIN
655:
(SELECT CONCAT('%', ?, '%') AS pattern) AS patterns
656:
ON texts.text LIKE patterns.pattern) >= 1
657:
SQL
15411
658:
tx.xquery(query, livecomment.fetch(:id), livestream_id, livecomment.fetch(:comment), ng_word.fetch(:word))
659:
end
660:
end
661:
13
662:
word_id
663:
end
664:
13
665:
status 201
13
666:
json(word_id:)
667:
end
668:
0
669:
get '/api/livestream/:livestream_id/reaction' do
191
670:
verify_user_session!
671:
191
672:
livestream_id = cast_as_integer(params[:livestream_id])
673:
191
674:
reactions = db_transaction do |tx|
191
675:
query = 'SELECT * FROM reactions WHERE livestream_id = ? ORDER BY created_at DESC'
191
676:
limit_str = params[:limit] || ''
191
677:
if limit_str != ''
17
678:
limit = cast_as_integer(limit_str)
17
679:
query = "#{query} LIMIT #{limit}"
680:
end
681:
191
682:
tx.xquery(query, livestream_id).map do |reaction_model|
709
683:
fill_reaction_response(tx, reaction_model)
684:
end
685:
end
686:
191
687:
json(reactions)
688:
end
689:
0
690:
PostReactionRequest = Data.define(:emoji_name)
691:
0
692:
post '/api/livestream/:livestream_id/reaction' do
154
693:
verify_user_session!
154
694:
sess = session[DEFAULT_SESSION_ID_KEY]
154
695:
unless sess
0
696:
raise HttpError.new(401)
697:
end
154
698:
user_id = sess[DEFAULT_USER_ID_KEY]
154
699:
unless user_id
0
700:
raise HttpError.new(401)
701:
end
702:
154
703:
livestream_id = Integer(params[:livestream_id], 10)
704:
154
705:
req = decode_request_body(PostReactionRequest)
706:
154
707:
reaction = db_transaction do |tx|
154
708:
created_at = Time.now.to_i
154
709:
tx.xquery('INSERT INTO reactions (user_id, livestream_id, emoji_name, created_at) VALUES (?, ?, ?, ?)', user_id, livestream_id, req.emoji_name, created_at)
154
710:
reaction_id = tx.last_id
711:
154
712:
fill_reaction_response(tx, {
713:
id: reaction_id,
714:
user_id:,
715:
livestream_id:,
716:
emoji_name: req.emoji_name,
717:
created_at:,
718:
})
719:
end
720:
154
721:
status 201
154
722:
json(reaction)
723:
end
724:
0
725:
BCRYPT_DEFAULT_COST = 4
0
726:
FALLBACK_IMAGE = '../img/NoImage.jpg'
727:
0
728:
get '/api/user/:username/icon' do
1437
729:
username = params[:username]
730:
1437
731:
image = db_transaction do |tx|
1437
732:
user = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
1437
733:
unless user
0
734:
raise HttpError.new(404, 'not found user that has the given username')
735:
end
1437
736:
tx.xquery('SELECT image FROM icons WHERE user_id = ?', user.fetch(:id)).first
737:
end
738:
1437
739:
content_type 'image/jpeg'
1437
740:
if image
920
741:
image[:image]
742:
else
517
743:
send_file FALLBACK_IMAGE
744:
end
745:
end
746:
0
747:
PostIconRequest = Data.define(:image)
748:
0
749:
post '/api/icon' do
431
750:
verify_user_session!
751:
431
752:
sess = session[DEFAULT_SESSION_ID_KEY]
431
753:
unless sess
0
754:
raise HttpError.new(401)
755:
end
431
756:
user_id = sess[DEFAULT_USER_ID_KEY]
431
757:
unless user_id
0
758:
raise HttpError.new(401)
759:
end
760:
431
761:
req = decode_request_body(PostIconRequest)
431
762:
image = Base64.decode64(req.image)
763:
431
764:
icon_id = db_transaction do |tx|
431
765:
tx.xquery('DELETE FROM icons WHERE user_id = ?', user_id)
431
766:
tx.xquery('INSERT INTO icons (user_id, image) VALUES (?, ?)', user_id, image)
431
767:
tx.last_id
768:
end
769:
431
770:
status 201
431
771:
json(
772:
id: icon_id,
773:
)
774:
end
775:
0
776:
get '/api/user/me' do
3
777:
verify_user_session!
778:
3
779:
sess = session[DEFAULT_SESSION_ID_KEY]
3
780:
unless sess
0
781:
raise HttpError.new(401)
782:
end
3
783:
user_id = sess[DEFAULT_USER_ID_KEY]
3
784:
unless user_id
0
785:
raise HttpError.new(401)
786:
end
787:
3
788:
user = db_transaction do |tx|
3
789:
user_model = tx.xquery('SELECT * FROM users WHERE id = ?', user_id).first
3
790:
unless user_model
0
791:
raise HttpError.new(404)
792:
end
3
793:
fill_user_response(tx, user_model)
794:
end
795:
3
796:
json(user)
797:
end
798:
0
799:
PostUserRequest = Data.define(
800:
:name,
801:
:display_name,
802:
:description,
803:
# password is non-hashed password.
804:
:password,
805:
:theme,
806:
)
807:
808:
# ユーザ登録API
0
809:
post '/api/register' do
435
810:
req = decode_request_body(PostUserRequest)
435
811:
if req.name == 'pipe'
1
812:
raise HttpError.new(400, "the username 'pipe' is reserved")
813:
end
814:
434
815:
hashed_password = BCrypt::Password.create(req.password, cost: BCRYPT_DEFAULT_COST)
816:
434
817:
user = db_transaction do |tx|
434
818:
tx.xquery('INSERT INTO users (name, display_name, description, password) VALUES(?, ?, ?, ?)', req.name, req.display_name, req.description, hashed_password)
433
819:
user_id = tx.last_id
820:
433
821:
tx.xquery('INSERT INTO themes (user_id, dark_mode) VALUES(?, ?)', user_id, req.theme.fetch(:dark_mode))
822:
433
823:
out, status = Open3.capture2e('pdnsutil', 'add-record', 't.isucon.pw', req.name, 'A', '0', POWERDNS_SUBDOMAIN_ADDRESS)
433
824:
unless status.success?
0
825:
raise HttpError.new(500, "pdnsutil failed with out=#{out}")
826:
end
827:
433
828:
fill_user_response(tx, {
829:
id: user_id,
830:
name: req.name,
831:
display_name: req.display_name,
832:
description: req.description,
833:
})
834:
end
835:
433
836:
status 201
433
837:
json(user)
838:
end
839:
0
840:
LoginRequest = Data.define(
841:
:username,
842:
# password is non-hashed password.
843:
:password,
844:
)
845:
846:
# ユーザログインAPI
0
847:
post '/api/login' do
439
848:
req = decode_request_body(LoginRequest)
849:
439
850:
user_model = db_transaction do |tx|
851:
# usernameはUNIQUEなので、whereで一意に特定できる
439
852:
tx.xquery('SELECT * FROM users WHERE name = ?', req.username).first.tap do |user_model|
439
853:
unless user_model
1
854:
raise HttpError.new(401, 'invalid username or password')
855:
end
856:
end
857:
end
858:
438
859:
unless BCrypt::Password.new(user_model.fetch(:password)).is_password?(req.password)
1
860:
raise HttpError.new(401, 'invalid username or password')
861:
end
862:
437
863:
session_end_at = Time.now + 10*60*60
437
864:
session_id = SecureRandom.uuid
437
865:
session[DEFAULT_SESSION_ID_KEY] = {
866:
DEFAULT_SESSION_ID_KEY => session_id,
867:
DEFAULT_USER_ID_KEY => user_model.fetch(:id),
868:
DEFAULT_USERNAME_KEY => user_model.fetch(:name),
869:
DEFAULT_SESSION_EXPIRES_KEY => session_end_at.to_i,
870:
}
871:
437
872:
''
873:
end
874:
875:
# ユーザ詳細API
0
876:
get '/api/user/:username' do
1
877:
verify_user_session!
878:
1
879:
username = params[:username]
880:
1
881:
user = db_transaction do |tx|
1
882:
user_model = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
1
883:
unless user_model
0
884:
raise HttpError.new(404)
885:
end
886:
1
887:
fill_user_response(tx, user_model)
888:
end
889:
1
890:
json(user)
891:
end
892:
0
893:
UserRankingEntry = Data.define(:username, :score)
894:
0
895:
get '/api/user/:username/statistics' do
6
896:
verify_user_session!
897:
6
898:
username = params[:username]
899:
900:
# ユーザごとに、紐づく配信について、累計リアクション数、累計ライブコメント数、累計売上金額を算出
901:
# また、現在の合計視聴者数もだす
902:
6
903:
stats = db_transaction do |tx|
6
904:
user = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
6
905:
unless user
0
906:
raise HttpError.new(400)
907:
end
908:
909:
# ランク算出
6
910:
users = tx.xquery('SELECT * FROM users').to_a
911:
6
912:
ranking = users.map do |user|
3871
913:
reactions = tx.xquery(<<~SQL, user.fetch(:id), as: :array).first[0]
914:
SELECT COUNT(*) FROM users u
915:
INNER JOIN livestreams l ON l.user_id = u.id
916:
INNER JOIN reactions r ON r.livestream_id = l.id
917:
WHERE u.id = ?
918:
SQL
919:
3871
920:
tips = tx.xquery(<<~SQL, user.fetch(:id), as: :array).first[0]
921:
SELECT IFNULL(SUM(l2.tip), 0) FROM users u
922:
INNER JOIN livestreams l ON l.user_id = u.id
923:
INNER JOIN livecomments l2 ON l2.livestream_id = l.id
924:
WHERE u.id = ?
925:
SQL
926:
3867
927:
score = reactions + tips
3867
928:
UserRankingEntry.new(username: user.fetch(:name), score:)
929:
end
930:
2002
931:
ranking.sort_by! { |entry| [entry.score, entry.username] }
754
932:
ridx = ranking.rindex { |entry| entry.username == username }
2
933:
rank = ranking.size - ridx
934:
935:
# リアクション数
2
936:
total_reactions = tx.xquery(<<~SQL, username, as: :array).first[0]
937:
SELECT COUNT(*) FROM users u
938:
INNER JOIN livestreams l ON l.user_id = u.id
939:
INNER JOIN reactions r ON r.livestream_id = l.id
940:
WHERE u.name = ?
941:
SQL
942:
943:
# ライブコメント数、チップ合計
2
944:
total_livecomments = 0
2
945:
total_tip = 0
2
946:
livestreams = tx.xquery('SELECT * FROM livestreams WHERE user_id = ?', user.fetch(:id))
2
947:
livestreams.each do |livestream|
18
948:
tx.xquery('SELECT * FROM livecomments WHERE livestream_id = ?', livestream.fetch(:id)).each do |livecomment|
4
949:
total_tip += livecomment.fetch(:tip)
4
950:
total_livecomments += 1
951:
end
952:
end
953:
954:
# 合計視聴者数
2
955:
viewers_count = 0
2
956:
livestreams.each do |livestream|
18
957:
cnt = tx.xquery('SELECT COUNT(*) FROM livestream_viewers_history WHERE livestream_id = ?', livestream.fetch(:id), as: :array).first[0]
18
958:
viewers_count += cnt
959:
end
960:
961:
# お気に入り絵文字
2
962:
favorite_emoji = tx.xquery(<<~SQL, username).first&.fetch(:emoji_name)
963:
SELECT r.emoji_name
964:
FROM users u
965:
INNER JOIN livestreams l ON l.user_id = u.id
966:
INNER JOIN reactions r ON r.livestream_id = l.id
967:
WHERE u.name = ?
968:
GROUP BY emoji_name
969:
ORDER BY COUNT(*) DESC, emoji_name DESC
970:
LIMIT 1
971:
SQL
972:
973:
{
2
974:
rank:,
975:
viewers_count:,
976:
total_reactions:,
977:
total_livecomments:,
978:
total_tip:,
979:
favorite_emoji:,
980:
}
981:
end
982:
2
983:
json(stats)
984:
end
985:
0
986:
LivestreamRankingEntry = Data.define(:livestream_id, :score)
987:
988:
# ライブ配信統計情報
0
989:
get '/api/livestream/:livestream_id/statistics' do
2
990:
verify_user_session!
2
991:
livestream_id = cast_as_integer(params[:livestream_id])
992:
2
993:
stats = db_transaction do |tx|
2
994:
unless tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
0
995:
raise HttpError.new(400)
996:
end
997:
998:
# ランク算出
2
999:
ranking = tx.xquery('SELECT * FROM livestreams').map do |livestream|
14992
1000:
reactions = tx.xquery('SELECT COUNT(*) FROM livestreams l INNER JOIN reactions r ON l.id = r.livestream_id WHERE l.id = ?', livestream.fetch(:id), as: :array).first[0]
1001:
14992
1002:
total_tips = tx.xquery('SELECT IFNULL(SUM(l2.tip), 0) FROM livestreams l INNER JOIN livecomments l2 ON l.id = l2.livestream_id WHERE l.id = ?', livestream.fetch(:id), as: :array).first[0]
1003:
14992
1004:
score = reactions + total_tips
14992
1005:
LivestreamRankingEntry.new(livestream_id: livestream.fetch(:id), score:)
1006:
end
14994
1007:
ranking.sort_by! { |entry| [entry.score, entry.livestream_id] }
944
1008:
ridx = ranking.rindex { |entry| entry.livestream_id == livestream_id }
2
1009:
rank = ranking.size - ridx
1010:
1011:
# 視聴者数算出
2
1012:
viewers_count = tx.xquery('SELECT COUNT(*) FROM livestreams l INNER JOIN livestream_viewers_history h ON h.livestream_id = l.id WHERE l.id = ?', livestream_id, as: :array).first[0]
1013:
1014:
# 最大チップ額
2
1015:
max_tip = tx.xquery('SELECT IFNULL(MAX(tip), 0) FROM livestreams l INNER JOIN livecomments l2 ON l2.livestream_id = l.id WHERE l.id = ?', livestream_id, as: :array).first[0]
1016:
1017:
# リアクション数
2
1018:
total_reactions = tx.xquery('SELECT COUNT(*) FROM livestreams l INNER JOIN reactions r ON r.livestream_id = l.id WHERE l.id = ?', livestream_id, as: :array).first[0]
1019:
1020:
# スパム報告数
2
1021:
total_reports = tx.xquery('SELECT COUNT(*) FROM livestreams l INNER JOIN livecomment_reports r ON r.livestream_id = l.id WHERE l.id = ?', livestream_id, as: :array).first[0]
1022:
1023:
{
2
1024:
rank:,
1025:
viewers_count:,
1026:
max_tip:,
1027:
total_reactions:,
1028:
total_reports:,
1029:
}
1030:
end
1031:
2
1032:
json(stats)
1033:
end
1034:
0
1035:
get '/api/payment' do
1
1036:
total_tip = db_transaction do |tx|
1
1037:
tx.xquery('SELECT IFNULL(SUM(tip), 0) FROM livecomments', as: :array).first[0]
1038:
end
1039:
1
1040:
json(total_tip:)
1041:
end
1042:
end
1043:
end