ppom
f205b65e93
According to discussion here https://github.com/sqlpage/SQLPage/pull/897#issuecomment-2835516830 Co-authored-by: ppom <>
58 lines
2.0 KiB
SQL
58 lines
2.0 KiB
SQL
select * FROM sqlpage_shell;
|
|
|
|
-- Display the list of players with a link for each one to start playing
|
|
INSERT INTO players(name, game_id)
|
|
SELECT $Player as name,
|
|
CAST($id AS INTEGER) as game_id
|
|
WHERE $Player IS NOT NULL;
|
|
|
|
SELECT 'list' as component,
|
|
'Players' as title;
|
|
SELECT name as title,
|
|
sqlpage.link(
|
|
'next-question.sql',
|
|
json_object(
|
|
'game_id', game_id,
|
|
'player', name
|
|
)
|
|
) as link
|
|
FROM players
|
|
WHERE game_id = CAST($id AS INTEGER);
|
|
---------------------------
|
|
-- Player insertion form --
|
|
---------------------------
|
|
SELECT 'form' as component,
|
|
'Add a new player' as title,
|
|
'Add to game' as validate;
|
|
SELECT 'Player' as name,
|
|
'Player name' as placeholder,
|
|
TRUE as autofocus;
|
|
-- Insert a new question into the game_questions table for each new player
|
|
INSERT INTO game_questions(
|
|
game_id,
|
|
question_id,
|
|
wrong_answer,
|
|
impostor,
|
|
game_order
|
|
)
|
|
SELECT CAST($id AS INTEGER) as game_id,
|
|
questions.id as question_id,
|
|
-- When the true answer is small, set the wrong answer to just +/- 1, otherwise -25%/+75%.
|
|
-- When it is a date between 1200 and 2100, make it -25 % or +75 % of the distance to today
|
|
CAST(CASE
|
|
WHEN questions.true_answer < 10 THEN questions.true_answer + 1 - 2 * abs(random() %2) -- wrong answer = true answer +/- 1
|
|
WHEN questions.true_answer > 1200
|
|
AND questions.true_answer < 2100 THEN 2023 - (2023 - questions.true_answer) * (abs(random() %2) + 0.75) -- wrong answer = true answer -25% or +75% of the distance to today
|
|
ELSE questions.true_answer * (abs(random() %2) + 0.75)
|
|
END AS INTEGER) as wrong_answer,
|
|
-- wrong answer = true answer +/- 50% TODO: better wrong answer generation
|
|
$Player as impostor,
|
|
random() as game_order
|
|
FROM questions
|
|
LEFT JOIN game_questions ON questions.id = game_questions.question_id
|
|
AND game_questions.game_id = CAST($id AS INTEGER)
|
|
WHERE game_questions.question_id IS NULL
|
|
AND $Player IS NOT NULL
|
|
ORDER BY random()
|
|
LIMIT 1;
|