データベースの設計

[ryosan-470]

CREATE TABLE user_info (
id primary_key integer, -- ユーザーID
name text,
mail text,
hashed_password text,   -- ハッシュ化されたパスワード
point integer)     -- スコア

CREATE TABLE score (
id primary_key integer,
problem_id integer,
solved bool
solved_time text)

CREATE TABLE problem (
problem_id integer primary_key,
point integer,  -- 問題の配点
type text,  -- 問題のタイプ(例:web, network, misc…)
title text,  -- 問題タイトル (例:テスト問題)
body text,  -- 問題の本文 htmlで格納
hint text)

各DBの中身の例:

sqlite> SELECT * FROM user_info;
id          name        mail        hashed_password  point
----------  ----------  ----------  ---------------  ----------
1           admin       admin@org   password         0
2           jtwp470     jtwp470.ne  password         101
3           tango       vim@vim.or  vimpass          300
sqlite> SELECT * FROM score;
id          problem_id  solved
----------  ----------  ----------
2           1           true
2           2           true
2           3           false
3           1           false
3           2           true
3           3           true
sqlite> SELECT * FROM problem;
problem_id  point       type        title         body        hint
----------  ----------  ----------  ------------  ----------  ----------
1           1           misc        test problem  no des      No hint
2           100         web         web filer     no des      No hint
3           200         misc        shock?        shock!      No hint

各ユーザーの得点を算出

sqlite> SELECT SUM(point) FROM score INNER JOIN problem ON score.problem_id == problem.problem_id WHERE id = 2 AND solved = "true";  -- ユーザー2の得点
SUM(point)
----------
101
sqlite> SELECT SUM(point) FROM score INNER JOIN problem ON score.problem_id == problem.problem_id WHERE id = 3 AND solved = "true"; -- ユーザー3の得点
SUM(point)
----------
300

[ryosan-470]

#4