| File | % covered | Lines | Relevant Lines | Lines covered | Lines missed | Avg. Hits / Line |
|---|---|---|---|---|---|---|
| app.rb | 74.85 % | 1043 | 497 | 372 | 125 | 678.70 |
-
# frozen_string_literal: true
-
-
require 'base64'
-
require 'bcrypt'
-
require 'digest'
-
require 'mysql2'
-
require 'mysql2-cs-bind'
-
require 'open3'
-
require 'securerandom'
-
require 'sinatra/base'
-
require 'sinatra/json'
-
-
module Isupipe
-
class App < Sinatra::Base
-
enable :logging
-
set :show_exceptions, :after_handler
-
set :sessions, domain: 'u.isucon.dev', path: '/', expire_after: 1000*60
-
set :session_secret, ENV.fetch('ISUCON13_SESSION_SECRETKEY', 'isucon13_session_cookiestore_defaultsecret').unpack('H*')[0]
-
-
POWERDNS_SUBDOMAIN_ADDRESS = ENV.fetch('ISUCON13_POWERDNS_SUBDOMAIN_ADDRESS')
-
-
DEFAULT_SESSION_ID_KEY = 'SESSIONID'
-
DEFAULT_SESSION_EXPIRES_KEY = 'EXPIRES'
-
DEFAULT_USER_ID_KEY = 'USERID'
-
DEFAULT_USERNAME_KEY = 'USERNAME'
-
-
class HttpError < StandardError
-
attr_reader :code
-
-
def initialize(code, message = nil)
-
13
super(message || "HTTP error #{code}")
-
13
@code = code
-
end
-
end
-
-
error HttpError do
-
13
e = env['sinatra.error']
-
13
status e.code
-
13
json(error: e.message)
-
end
-
-
helpers do
-
def db_conn
-
11450
Thread.current[:db_conn] ||= connect_db
-
end
-
-
def connect_db
-
21
Mysql2::Client.new(
-
host: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_ADDRESS', '127.0.0.1'),
-
port: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_PORT', '3306').to_i,
-
username: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_USER', 'isucon'),
-
password: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_PASSWORD', 'isucon'),
-
database: ENV.fetch('ISUCON13_MYSQL_DIALCONFIG_DATABASE', 'isupipe'),
-
symbolize_keys: true,
-
cast_booleans: true,
-
reconnect: true,
-
)
-
end
-
-
def db_transaction(&block)
-
3818
db_conn.query('BEGIN')
-
3818
ok = false
-
begin
-
3818
retval = block.call(db_conn)
-
3802
db_conn.query('COMMIT')
-
3802
ok = true
-
3802
retval
-
ensure
-
3814
unless ok
-
12
db_conn.query('ROLLBACK')
-
end
-
end
-
end
-
-
def decode_request_body(data_class)
-
1727
body = JSON.parse(request.body.tap(&:rewind).read, symbolize_names: true)
-
6308
data_class.new(**data_class.members.map { |key| [key, body[key]] }.to_h)
-
end
-
-
def cast_as_integer(str)
-
755
Integer(str, 10)
-
rescue
-
raise HttpError.new(400)
-
end
-
-
def verify_user_session!
-
1410
sess = session[DEFAULT_SESSION_ID_KEY]
-
1410
unless sess
-
raise HttpError.new(403)
-
end
-
-
1410
session_expires = sess[DEFAULT_SESSION_EXPIRES_KEY]
-
1410
unless session_expires
-
raise HttpError.new(403)
-
end
-
-
1410
now = Time.now
-
1410
if now.to_i > session_expires
-
raise HttpError.new(401, 'session has expired')
-
end
-
-
nil
-
end
-
-
def fill_livestream_response(tx, livestream_model)
-
4714
owner_model = tx.xquery('SELECT * FROM users WHERE id = ?', livestream_model.fetch(:user_id)).first
-
4714
owner = fill_user_response(tx, owner_model)
-
-
4714
tags = tx.xquery('SELECT * FROM livestream_tags WHERE livestream_id = ?', livestream_model.fetch(:id)).map do |livestream_tag_model|
-
15757
tag_model = tx.xquery('SELECT * FROM tags WHERE id = ?', livestream_tag_model.fetch(:tag_id)).first
-
{
-
15757
id: tag_model.fetch(:id),
-
name: tag_model.fetch(:name),
-
}
-
end
-
-
4714
livestream_model.slice(:id, :title, :description, :playlist_url, :thumbnail_url, :start_at, :end_at).merge(
-
owner:,
-
tags:,
-
)
-
end
-
-
def fill_livecomment_response(tx, livecomment_model)
-
1096
comment_owner_model = tx.xquery('SELECT * FROM users WHERE id = ?', livecomment_model.fetch(:user_id)).first
-
1096
comment_owner = fill_user_response(tx, comment_owner_model)
-
-
1096
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livecomment_model.fetch(:livestream_id)).first
-
1096
livestream = fill_livestream_response(tx, livestream_model)
-
-
1096
livecomment_model.slice(:id, :comment, :tip, :created_at).merge(
-
user: comment_owner,
-
livestream:,
-
)
-
end
-
-
def fill_livecomment_report_response(tx, report_model)
-
62
reporter_model = tx.xquery('SELECT * FROM users WHERE id = ?', report_model.fetch(:user_id)).first
-
62
reporter = fill_user_response(tx, reporter_model)
-
-
62
livecomment_model = tx.xquery('SELECT * FROM livecomments WHERE id = ?', report_model.fetch(:livecomment_id)).first
-
62
livecomment = fill_livecomment_response(tx, livecomment_model)
-
-
62
report_model.slice(:id, :created_at).merge(
-
reporter:,
-
livecomment:,
-
)
-
end
-
-
def fill_reaction_response(tx, reaction_model)
-
863
user_model = tx.xquery('SELECT * FROM users WHERE id = ?', reaction_model.fetch(:user_id)).first
-
863
user = fill_user_response(tx, user_model)
-
-
863
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', reaction_model.fetch(:livestream_id)).first
-
863
livestream = fill_livestream_response(tx, livestream_model)
-
-
863
reaction_model.slice(:id, :emoji_name, :created_at).merge(
-
user:,
-
livestream:,
-
)
-
end
-
-
def fill_user_response(tx, user_model)
-
7172
theme_model = tx.xquery('SELECT * FROM themes WHERE user_id = ?', user_model.fetch(:id)).first
-
-
7172
icon_model = tx.xquery('SELECT image FROM icons WHERE user_id = ?', user_model.fetch(:id)).first
-
image =
-
7172
if icon_model
-
4712
icon_model.fetch(:image)
-
else
-
2460
File.binread(FALLBACK_IMAGE)
-
end
-
7172
icon_hash = Digest::SHA256.hexdigest(image)
-
-
{
-
7172
id: user_model.fetch(:id),
-
name: user_model.fetch(:name),
-
display_name: user_model.fetch(:display_name),
-
description: user_model.fetch(:description),
-
theme: {
-
id: theme_model.fetch(:id),
-
dark_mode: theme_model.fetch(:dark_mode),
-
},
-
icon_hash:,
-
}
-
end
-
end
-
-
# 初期化
-
post '/api/initialize' do
-
1
out, status = Open3.capture2e('../sql/init.sh')
-
1
unless status.success?
-
logger.warn("init.sh failed with out=#{out}")
-
halt 500
-
end
-
-
1
json(
-
language: 'ruby',
-
)
-
end
-
-
# top
-
get '/api/tag' do
-
62
tag_models = db_transaction do |tx|
-
62
tx.query('SELECT * FROM tags')
-
end
-
-
62
json(
-
tags: tag_models.map { |tag_model|
-
{
-
6386
id: tag_model.fetch(:id),
-
name: tag_model.fetch(:name),
-
}
-
},
-
)
-
end
-
-
# 配信者のテーマ取得API
-
get '/api/user/:username/theme' do
-
5
verify_user_session!
-
-
5
username = params[:username]
-
-
5
theme_model = db_transaction do |tx|
-
5
user_model = tx.xquery('SELECT id FROM users WHERE name = ?', username).first
-
5
unless user_model
-
raise HttpError.new(404)
-
end
-
5
tx.xquery('SELECT * FROM themes WHERE user_id = ?', user_model.fetch(:id)).first
-
end
-
-
5
json(
-
id: theme_model.fetch(:id),
-
dark_mode: theme_model.fetch(:dark_mode),
-
)
-
end
-
-
# livestream
-
-
ReserveLivestreamRequest = Data.define(
-
:tags,
-
:title,
-
:description,
-
:playlist_url,
-
:thumbnail_url,
-
:start_at,
-
:end_at,
-
)
-
-
# reserve livestream
-
post '/api/livestream/reservation' do
-
84
verify_user_session!
-
84
sess = session[DEFAULT_SESSION_ID_KEY]
-
84
unless sess
-
raise HttpError.new(401)
-
end
-
84
user_id = sess[DEFAULT_USER_ID_KEY]
-
84
unless sess
-
raise HttpError.new(401)
-
end
-
-
84
req = decode_request_body(ReserveLivestreamRequest)
-
-
84
livestream = db_transaction do |tx|
-
# 2023/11/25 10:00からの１年間の期間内であるかチェック
-
84
term_start_at = Time.utc(2023, 11, 25, 1)
-
84
term_end_at = Time.utc(2024, 11, 25, 1)
-
84
reserve_start_at = Time.at(req.start_at, in: 'UTC')
-
84
reserve_end_at = Time.at(req.end_at, in: 'UTC')
-
84
if reserve_start_at >= term_end_at || reserve_end_at <= term_start_at
-
2
raise HttpError.new(400, 'bad reservation time range')
-
end
-
-
# 予約枠をみて、予約が可能か調べる
-
# NOTE: 並列な予約のoverbooking防止にFOR UPDATEが必要
-
82
tx.xquery('SELECT * FROM reservation_slots WHERE start_at >= ? AND end_at <= ? FOR UPDATE', req.start_at, req.end_at).each do |slot|
-
629
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
logger.info("#{slot.fetch(:start_at)} ~ #{slot.fetch(:end_at)}予約枠の残数 = #{slot.fetch(:slot)}")
-
629
if count < 1
-
1
raise HttpError.new(400, "予約期間 #{term_start_at.to_i} ~ #{term_end_at.to_i}に対して、予約区間 #{req.start_at} ~ #{req.end_at}が予約できません")
-
end
-
end
-
-
81
tx.xquery('UPDATE reservation_slots SET slot = slot - 1 WHERE start_at >= ? AND end_at <= ?', req.start_at, req.end_at)
-
81
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
livestream_id = tx.last_id
-
-
# タグ追加
-
81
req.tags.each do |tag_id|
-
327
tx.xquery('INSERT INTO livestream_tags (livestream_id, tag_id) VALUES (?, ?)', livestream_id, tag_id)
-
end
-
-
81
fill_livestream_response(tx, {
-
id: livestream_id,
-
user_id:,
-
title: req.title,
-
description: req.description,
-
playlist_url: req.playlist_url,
-
thumbnail_url: req.thumbnail_url,
-
start_at: req.start_at,
-
end_at: req.end_at,
-
})
-
end
-
-
81
status 201
-
81
json(livestream)
-
end
-
-
# list livestream
-
get '/api/livestream/search' do
-
35
key_tag_name = params[:tag] || ''
-
-
35
livestreams = db_transaction do |tx|
-
livestream_models =
-
35
if key_tag_name != ''
-
# タグによる取得
-
13
tag_id_list = tx.xquery('SELECT id FROM tags WHERE name = ?', key_tag_name, as: :array).map(&:first)
-
13
tx.xquery('SELECT * FROM livestream_tags WHERE tag_id IN (?) ORDER BY livestream_id DESC', tag_id_list).map do |key_tagged_livestream|
-
1479
tx.xquery('SELECT * FROM livestreams WHERE id = ?', key_tagged_livestream.fetch(:livestream_id)).first
-
end
-
else
-
# 検索条件なし
-
22
query = 'SELECT * FROM livestreams ORDER BY id DESC'
-
22
limit_str = params[:limit] || ''
-
22
if limit_str != ''
-
22
limit = cast_as_integer(limit_str)
-
22
query = "#{query} LIMIT #{limit}"
-
end
-
-
22
tx.xquery(query).to_a
-
end
-
-
35
livestream_models.map do |livestream_model|
-
2579
fill_livestream_response(tx, livestream_model)
-
end
-
end
-
-
35
json(livestreams)
-
end
-
-
get '/api/livestream' do
-
76
verify_user_session!
-
76
sess = session[DEFAULT_SESSION_ID_KEY]
-
76
unless sess
-
raise HttpError.new(401)
-
end
-
76
user_id = sess[DEFAULT_USER_ID_KEY]
-
76
unless sess
-
raise HttpError.new(401)
-
end
-
-
76
livestreams = db_transaction do |tx|
-
76
tx.xquery('SELECT * FROM livestreams WHERE user_id = ?', user_id).map do |livestream_model|
-
94
fill_livestream_response(tx, livestream_model)
-
end
-
end
-
-
76
json(livestreams)
-
end
-
-
get '/api/user/:username/livestream' do
-
verify_user_session!
-
username = params[:username]
-
-
livestreams = db_transaction do |tx|
-
user = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
-
unless user
-
raise HttpError.new(404, 'user not found')
-
end
-
-
tx.xquery('SELECT * FROM livestreams WHERE user_id = ?', user.fetch(:id)).map do |livestream_model|
-
fill_livestream_response(tx, livestream_model)
-
end
-
end
-
-
json(livestreams)
-
end
-
-
# ユーザ視聴開始 (viewer)
-
post '/api/livestream/:livestream_id/enter' do
-
19
verify_user_session!
-
19
sess = session[DEFAULT_SESSION_ID_KEY]
-
19
unless sess
-
raise HttpError.new(401)
-
end
-
19
user_id = sess[DEFAULT_USER_ID_KEY]
-
19
unless sess
-
raise HttpError.new(401)
-
end
-
-
19
livestream_id = cast_as_integer(params[:livestream_id])
-
-
19
db_transaction do |tx|
-
19
created_at = Time.now.to_i
-
19
tx.xquery('INSERT INTO livestream_viewers_history (user_id, livestream_id, created_at) VALUES(?, ?, ?)', user_id, livestream_id, created_at)
-
end
-
-
19
''
-
end
-
-
# ユーザ視聴終了 (viewer)
-
delete '/api/livestream/:livestream_id/exit' do
-
9
verify_user_session!
-
9
sess = session[DEFAULT_SESSION_ID_KEY]
-
9
unless sess
-
raise HttpError.new(401)
-
end
-
9
user_id = sess[DEFAULT_USER_ID_KEY]
-
9
unless sess
-
raise HttpError.new(401)
-
end
-
-
9
livestream_id = cast_as_integer(params[:livestream_id])
-
-
9
db_transaction do |tx|
-
9
tx.xquery('DELETE FROM livestream_viewers_history WHERE user_id = ? AND livestream_id = ?', user_id, livestream_id)
-
end
-
-
9
''
-
end
-
-
# get livestream
-
get '/api/livestream/:livestream_id' do
-
1
verify_user_session!
-
-
1
livestream_id = cast_as_integer(params[:livestream_id])
-
-
1
livestream = db_transaction do |tx|
-
1
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
-
1
unless livestream_model
-
raise HttpError.new(404)
-
end
-
-
1
fill_livestream_response(tx, livestream_model)
-
end
-
-
1
json(livestream)
-
end
-
-
# (配信者向け)ライブコメントの報告一覧取得API
-
get '/api/livestream/:livestream_id/report' do
-
21
verify_user_session!
-
-
21
sess = session[DEFAULT_SESSION_ID_KEY]
-
21
unless sess
-
raise HttpError.new(401)
-
end
-
21
user_id = sess[DEFAULT_USER_ID_KEY]
-
21
unless sess
-
raise HttpError.new(401)
-
end
-
-
21
livestream_id = cast_as_integer(params[:livestream_id])
-
-
21
reports = db_transaction do |tx|
-
21
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
-
21
if livestream_model.fetch(:user_id) != user_id
-
raise HttpError.new(403, "can't get other streamer's livecomment reports")
-
end
-
-
21
tx.xquery('SELECT * FROM livecomment_reports WHERE livestream_id = ?', livestream_id).map do |report_model|
-
20
fill_livecomment_report_response(tx, report_model)
-
end
-
end
-
-
21
json(reports)
-
end
-
-
# get polling livecomment timeline
-
get '/api/livestream/:livestream_id/livecomment' do
-
160
verify_user_session!
-
160
livestream_id = cast_as_integer(params[:livestream_id])
-
-
160
livecomments = db_transaction do |tx|
-
160
query = 'SELECT * FROM livecomments WHERE livestream_id = ? ORDER BY created_at DESC'
-
160
limit_str = params[:limit] || ''
-
160
if limit_str != ''
-
18
limit = cast_as_integer(limit_str)
-
18
query = "#{query} LIMIT #{limit}"
-
end
-
-
160
tx.xquery(query, livestream_id).map do |livecomment_model|
-
864
fill_livecomment_response(tx, livecomment_model)
-
end
-
end
-
-
160
json(livecomments)
-
end
-
-
get '/api/livestream/:livestream_id/ngwords' do
-
15
verify_user_session!
-
15
sess = session[DEFAULT_SESSION_ID_KEY]
-
15
unless sess
-
raise HttpError.new(401)
-
end
-
15
user_id = sess[DEFAULT_USER_ID_KEY]
-
15
unless sess
-
raise HttpError.new(401)
-
end
-
-
15
livestream_id = cast_as_integer(params[:livestream_id])
-
-
15
ng_words = db_transaction do |tx|
-
15
tx.xquery('SELECT * FROM ng_words WHERE user_id = ? AND livestream_id = ? ORDER BY created_at DESC', user_id, livestream_id).to_a
-
end
-
-
15
json(ng_words)
-
end
-
-
PostLivecommentRequest = Data.define(
-
:comment,
-
:tip,
-
)
-
-
# ライブコメント投稿
-
post '/api/livestream/:livestream_id/livecomment' do
-
171
verify_user_session!
-
171
sess = session[DEFAULT_SESSION_ID_KEY]
-
171
unless sess
-
raise HttpError.new(401)
-
end
-
171
user_id = sess[DEFAULT_USER_ID_KEY]
-
171
unless sess
-
raise HttpError.new(401)
-
end
-
-
171
livestream_id = cast_as_integer(params[:livestream_id])
-
-
171
req = decode_request_body(PostLivecommentRequest)
-
-
171
livecomment = db_transaction do |tx|
-
171
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
-
171
unless livestream_model
-
raise HttpError.new(404, 'livestream not found')
-
end
-
-
# スパム判定
-
171
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
query = <<~SQL
-
SELECT COUNT(*)
-
FROM
-
(SELECT ? AS text) AS texts
-
INNER JOIN
-
(SELECT CONCAT('%', ?, '%') AS pattern) AS patterns
-
ON texts.text LIKE patterns.pattern
-
SQL
-
33
hit_spam = tx.xquery(query, req.comment, ng_word.fetch(:word), as: :array).first[0]
-
33
logger.info("[hit_spam=#{hit_spam}] comment = #{req.comment}")
-
33
if hit_spam >= 1
-
1
raise HttpError.new(400, 'このコメントがスパム判定されました')
-
end
-
end
-
-
170
now = Time.now.to_i
-
170
tx.xquery('INSERT INTO livecomments (user_id, livestream_id, comment, tip, created_at) VALUES (?, ?, ?, ?, ?)', user_id, livestream_id, req.comment, req.tip, now)
-
170
livecomment_id = tx.last_id
-
-
170
fill_livecomment_response(tx, {
-
id: livecomment_id,
-
user_id:,
-
livestream_id:,
-
comment: req.comment,
-
tip: req.tip,
-
created_at: now,
-
})
-
end
-
-
170
status 201
-
170
json(livecomment)
-
end
-
-
# ライブコメント報告
-
post '/api/livestream/:livestream_id/livecomment/:livecomment_id/report' do
-
48
verify_user_session!
-
48
sess = session[DEFAULT_SESSION_ID_KEY]
-
48
unless sess
-
raise HttpError.new(401)
-
end
-
48
user_id = sess[DEFAULT_USER_ID_KEY]
-
48
unless user_id
-
raise HttpError.new(401)
-
end
-
-
48
livestream_id = cast_as_integer(params[:livestream_id])
-
48
livecomment_id = cast_as_integer(params[:livecomment_id])
-
-
48
report = db_transaction do |tx|
-
48
livestream_model = tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
-
48
unless livestream_model
-
raise HttpError.new(404, 'livestream not found')
-
end
-
-
48
livecomment_model = tx.xquery('SELECT * FROM livecomments WHERE id = ?', livecomment_id).first
-
48
unless livecomment_model
-
6
raise HttpError.new(404, 'livecomment not found')
-
end
-
-
42
now = Time.now.to_i
-
42
tx.xquery('INSERT INTO livecomment_reports(user_id, livestream_id, livecomment_id, created_at) VALUES (?, ?, ?, ?)', user_id, livestream_id, livecomment_id, now)
-
42
report_id = tx.last_id
-
-
42
fill_livecomment_report_response(tx, {
-
id: report_id,
-
user_id:,
-
livestream_id:,
-
livecomment_id:,
-
created_at: now,
-
})
-
end
-
-
42
status 201
-
42
json(report)
-
end
-
-
ModerateRequest = Data.define(:ng_word)
-
-
# 配信者によるモデレーション (NGワード登録)
-
post '/api/livestream/:livestream_id/moderate' do
-
13
verify_user_session!
-
13
sess = session[DEFAULT_SESSION_ID_KEY]
-
13
unless sess
-
raise HttpError.new(401)
-
end
-
13
user_id = sess[DEFAULT_USER_ID_KEY]
-
13
unless user_id
-
raise HttpError.new(401)
-
end
-
-
13
livestream_id = cast_as_integer(params[:livestream_id])
-
-
13
req = decode_request_body(ModerateRequest)
-
-
13
word_id = db_transaction do |tx|
-
# 配信者自身の配信に対するmoderateなのかを検証
-
13
owned_livestreams = tx.xquery('SELECT * FROM livestreams WHERE id = ? AND user_id = ?', livestream_id, user_id).to_a
-
13
if owned_livestreams.empty?
-
raise HttpError.new(400, "A streamer can't moderate livestreams that other streamers own")
-
end
-
-
13
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
word_id = tx.last_id
-
-
# NGワードにヒットする過去の投稿も全削除する
-
13
tx.xquery('SELECT * FROM ng_words WHERE livestream_id = ?', livestream_id).each do |ng_word|
-
# ライブコメント一覧取得
-
14
tx.xquery('SELECT * FROM livecomments').each do |livecomment|
-
15411
query = <<~SQL
-
DELETE FROM livecomments
-
WHERE
-
id = ? AND
-
livestream_id = ? AND
-
(SELECT COUNT(*)
-
FROM
-
(SELECT ? AS text) AS texts
-
INNER JOIN
-
(SELECT CONCAT('%', ?, '%') AS pattern) AS patterns
-
ON texts.text LIKE patterns.pattern) >= 1
-
SQL
-
15411
tx.xquery(query, livecomment.fetch(:id), livestream_id, livecomment.fetch(:comment), ng_word.fetch(:word))
-
end
-
end
-
-
13
word_id
-
end
-
-
13
status 201
-
13
json(word_id:)
-
end
-
-
get '/api/livestream/:livestream_id/reaction' do
-
191
verify_user_session!
-
-
191
livestream_id = cast_as_integer(params[:livestream_id])
-
-
191
reactions = db_transaction do |tx|
-
191
query = 'SELECT * FROM reactions WHERE livestream_id = ? ORDER BY created_at DESC'
-
191
limit_str = params[:limit] || ''
-
191
if limit_str != ''
-
17
limit = cast_as_integer(limit_str)
-
17
query = "#{query} LIMIT #{limit}"
-
end
-
-
191
tx.xquery(query, livestream_id).map do |reaction_model|
-
709
fill_reaction_response(tx, reaction_model)
-
end
-
end
-
-
191
json(reactions)
-
end
-
-
PostReactionRequest = Data.define(:emoji_name)
-
-
post '/api/livestream/:livestream_id/reaction' do
-
154
verify_user_session!
-
154
sess = session[DEFAULT_SESSION_ID_KEY]
-
154
unless sess
-
raise HttpError.new(401)
-
end
-
154
user_id = sess[DEFAULT_USER_ID_KEY]
-
154
unless user_id
-
raise HttpError.new(401)
-
end
-
-
154
livestream_id = Integer(params[:livestream_id], 10)
-
-
154
req = decode_request_body(PostReactionRequest)
-
-
154
reaction = db_transaction do |tx|
-
154
created_at = Time.now.to_i
-
154
tx.xquery('INSERT INTO reactions (user_id, livestream_id, emoji_name, created_at) VALUES (?, ?, ?, ?)', user_id, livestream_id, req.emoji_name, created_at)
-
154
reaction_id = tx.last_id
-
-
154
fill_reaction_response(tx, {
-
id: reaction_id,
-
user_id:,
-
livestream_id:,
-
emoji_name: req.emoji_name,
-
created_at:,
-
})
-
end
-
-
154
status 201
-
154
json(reaction)
-
end
-
-
BCRYPT_DEFAULT_COST = 4
-
FALLBACK_IMAGE = '../img/NoImage.jpg'
-
-
get '/api/user/:username/icon' do
-
1437
username = params[:username]
-
-
1437
image = db_transaction do |tx|
-
1437
user = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
-
1437
unless user
-
raise HttpError.new(404, 'not found user that has the given username')
-
end
-
1437
tx.xquery('SELECT image FROM icons WHERE user_id = ?', user.fetch(:id)).first
-
end
-
-
1437
content_type 'image/jpeg'
-
1437
if image
-
920
image[:image]
-
else
-
517
send_file FALLBACK_IMAGE
-
end
-
end
-
-
PostIconRequest = Data.define(:image)
-
-
post '/api/icon' do
-
431
verify_user_session!
-
-
431
sess = session[DEFAULT_SESSION_ID_KEY]
-
431
unless sess
-
raise HttpError.new(401)
-
end
-
431
user_id = sess[DEFAULT_USER_ID_KEY]
-
431
unless user_id
-
raise HttpError.new(401)
-
end
-
-
431
req = decode_request_body(PostIconRequest)
-
431
image = Base64.decode64(req.image)
-
-
431
icon_id = db_transaction do |tx|
-
431
tx.xquery('DELETE FROM icons WHERE user_id = ?', user_id)
-
431
tx.xquery('INSERT INTO icons (user_id, image) VALUES (?, ?)', user_id, image)
-
431
tx.last_id
-
end
-
-
431
status 201
-
431
json(
-
id: icon_id,
-
)
-
end
-
-
get '/api/user/me' do
-
3
verify_user_session!
-
-
3
sess = session[DEFAULT_SESSION_ID_KEY]
-
3
unless sess
-
raise HttpError.new(401)
-
end
-
3
user_id = sess[DEFAULT_USER_ID_KEY]
-
3
unless user_id
-
raise HttpError.new(401)
-
end
-
-
3
user = db_transaction do |tx|
-
3
user_model = tx.xquery('SELECT * FROM users WHERE id = ?', user_id).first
-
3
unless user_model
-
raise HttpError.new(404)
-
end
-
3
fill_user_response(tx, user_model)
-
end
-
-
3
json(user)
-
end
-
-
PostUserRequest = Data.define(
-
:name,
-
:display_name,
-
:description,
-
# password is non-hashed password.
-
:password,
-
:theme,
-
)
-
-
# ユーザ登録API
-
post '/api/register' do
-
435
req = decode_request_body(PostUserRequest)
-
435
if req.name == 'pipe'
-
1
raise HttpError.new(400, "the username 'pipe' is reserved")
-
end
-
-
434
hashed_password = BCrypt::Password.create(req.password, cost: BCRYPT_DEFAULT_COST)
-
-
434
user = db_transaction do |tx|
-
434
tx.xquery('INSERT INTO users (name, display_name, description, password) VALUES(?, ?, ?, ?)', req.name, req.display_name, req.description, hashed_password)
-
433
user_id = tx.last_id
-
-
433
tx.xquery('INSERT INTO themes (user_id, dark_mode) VALUES(?, ?)', user_id, req.theme.fetch(:dark_mode))
-
-
433
out, status = Open3.capture2e('pdnsutil', 'add-record', 'u.isucon.dev', req.name, 'A', '0', POWERDNS_SUBDOMAIN_ADDRESS)
-
433
unless status.success?
-
raise HttpError.new(500, "pdnsutil failed with out=#{out}")
-
end
-
-
433
fill_user_response(tx, {
-
id: user_id,
-
name: req.name,
-
display_name: req.display_name,
-
description: req.description,
-
})
-
end
-
-
433
status 201
-
433
json(user)
-
end
-
-
LoginRequest = Data.define(
-
:username,
-
# password is non-hashed password.
-
:password,
-
)
-
-
# ユーザログインAPI
-
post '/api/login' do
-
439
req = decode_request_body(LoginRequest)
-
-
439
user_model = db_transaction do |tx|
-
# usernameはUNIQUEなので、whereで一意に特定できる
-
439
tx.xquery('SELECT * FROM users WHERE name = ?', req.username).first.tap do |user_model|
-
439
unless user_model
-
1
raise HttpError.new(401, 'invalid username or password')
-
end
-
end
-
end
-
-
438
unless BCrypt::Password.new(user_model.fetch(:password)).is_password?(req.password)
-
1
raise HttpError.new(401, 'invalid username or password')
-
end
-
-
437
session_end_at = Time.now + 10*60*60
-
437
session_id = SecureRandom.uuid
-
437
session[DEFAULT_SESSION_ID_KEY] = {
-
DEFAULT_SESSION_ID_KEY => session_id,
-
DEFAULT_USER_ID_KEY => user_model.fetch(:id),
-
DEFAULT_USERNAME_KEY => user_model.fetch(:name),
-
DEFAULT_SESSION_EXPIRES_KEY => session_end_at.to_i,
-
}
-
-
437
''
-
end
-
-
# ユーザ詳細API
-
get '/api/user/:username' do
-
1
verify_user_session!
-
-
1
username = params[:username]
-
-
1
user = db_transaction do |tx|
-
1
user_model = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
-
1
unless user_model
-
raise HttpError.new(404)
-
end
-
-
1
fill_user_response(tx, user_model)
-
end
-
-
1
json(user)
-
end
-
-
UserRankingEntry = Data.define(:username, :score)
-
-
get '/api/user/:username/statistics' do
-
6
verify_user_session!
-
-
6
username = params[:username]
-
-
# ユーザごとに、紐づく配信について、累計リアクション数、累計ライブコメント数、累計売上金額を算出
-
# また、現在の合計視聴者数もだす
-
-
6
stats = db_transaction do |tx|
-
6
user = tx.xquery('SELECT * FROM users WHERE name = ?', username).first
-
6
unless user
-
raise HttpError.new(400)
-
end
-
-
# ランク算出
-
6
users = tx.xquery('SELECT * FROM users').to_a
-
-
6
ranking = users.map do |user|
-
3871
reactions = tx.xquery(<<~SQL, user.fetch(:id), as: :array).first[0]
-
SELECT COUNT(*) FROM users u
-
INNER JOIN livestreams l ON l.user_id = u.id
-
INNER JOIN reactions r ON r.livestream_id = l.id
-
WHERE u.id = ?
-
SQL
-
-
3871
tips = tx.xquery(<<~SQL, user.fetch(:id), as: :array).first[0]
-
SELECT IFNULL(SUM(l2.tip), 0) FROM users u
-
INNER JOIN livestreams l ON l.user_id = u.id
-
INNER JOIN livecomments l2 ON l2.livestream_id = l.id
-
WHERE u.id = ?
-
SQL
-
-
3867
score = reactions + tips
-
3867
UserRankingEntry.new(username: user.fetch(:name), score:)
-
end
-
-
2002
ranking.sort_by! { |entry| [entry.score, entry.username] }
-
754
ridx = ranking.rindex { |entry| entry.username == username }
-
2
rank = ranking.size - ridx
-
-
# リアクション数
-
2
total_reactions = tx.xquery(<<~SQL, username, as: :array).first[0]
-
SELECT COUNT(*) FROM users u
-
INNER JOIN livestreams l ON l.user_id = u.id
-
INNER JOIN reactions r ON r.livestream_id = l.id
-
WHERE u.name = ?
-
SQL
-
-
# ライブコメント数、チップ合計
-
2
total_livecomments = 0
-
2
total_tip = 0
-
2
livestreams = tx.xquery('SELECT * FROM livestreams WHERE user_id = ?', user.fetch(:id))
-
2
livestreams.each do |livestream|
-
18
tx.xquery('SELECT * FROM livecomments WHERE livestream_id = ?', livestream.fetch(:id)).each do |livecomment|
-
4
total_tip += livecomment.fetch(:tip)
-
4
total_livecomments += 1
-
end
-
end
-
-
# 合計視聴者数
-
2
viewers_count = 0
-
2
livestreams.each do |livestream|
-
18
cnt = tx.xquery('SELECT COUNT(*) FROM livestream_viewers_history WHERE livestream_id = ?', livestream.fetch(:id), as: :array).first[0]
-
18
viewers_count += cnt
-
end
-
-
# お気に入り絵文字
-
2
favorite_emoji = tx.xquery(<<~SQL, username).first&.fetch(:emoji_name)
-
SELECT r.emoji_name
-
FROM users u
-
INNER JOIN livestreams l ON l.user_id = u.id
-
INNER JOIN reactions r ON r.livestream_id = l.id
-
WHERE u.name = ?
-
GROUP BY emoji_name
-
ORDER BY COUNT(*) DESC, emoji_name DESC
-
LIMIT 1
-
SQL
-
-
{
-
2
rank:,
-
viewers_count:,
-
total_reactions:,
-
total_livecomments:,
-
total_tip:,
-
favorite_emoji:,
-
}
-
end
-
-
2
json(stats)
-
end
-
-
LivestreamRankingEntry = Data.define(:livestream_id, :score)
-
-
# ライブ配信統計情報
-
get '/api/livestream/:livestream_id/statistics' do
-
2
verify_user_session!
-
2
livestream_id = cast_as_integer(params[:livestream_id])
-
-
2
stats = db_transaction do |tx|
-
2
unless tx.xquery('SELECT * FROM livestreams WHERE id = ?', livestream_id).first
-
raise HttpError.new(400)
-
end
-
-
# ランク算出
-
2
ranking = tx.xquery('SELECT * FROM livestreams').map do |livestream|
-
14992
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]
-
-
14992
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]
-
-
14992
score = reactions + total_tips
-
14992
LivestreamRankingEntry.new(livestream_id: livestream.fetch(:id), score:)
-
end
-
14994
ranking.sort_by! { |entry| [entry.score, entry.livestream_id] }
-
944
ridx = ranking.rindex { |entry| entry.livestream_id == livestream_id }
-
2
rank = ranking.size - ridx
-
-
# 視聴者数算出
-
2
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]
-
-
# 最大チップ額
-
2
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]
-
-
# リアクション数
-
2
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]
-
-
# スパム報告数
-
2
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]
-
-
{
-
2
rank:,
-
viewers_count:,
-
max_tip:,
-
total_reactions:,
-
total_reports:,
-
}
-
end
-
-
2
json(stats)
-
end
-
-
get '/api/payment' do
-
1
total_tip = db_transaction do |tx|
-
1
tx.xquery('SELECT IFNULL(SUM(tip), 0) FROM livecomments', as: :array).first[0]
-
end
-
-
1
json(total_tip:)
-
end
-
end
-
end