[[PSSdev/WebDB]]

#contents

* ユーザ管理系

** area

 CREATE TABLE area (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL
 );

** user_info

 CREATE TABLE user_info (
   uid INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   last_login INTEGER NOT NULL,
   mail TEXT NOT NULL,
   password TEXT NOT NULL,
   area_id INTEGER NOT NULL REFERENCES area(id),
   gender INTEGER NOT NULL,
   birth INTEGER NOT NULL
 );

* 問題データ系

** question

 CREATE TABLE question (
   qid INTEGER PRIMARY KEY,
   question TEXT NOT NULL,
   answer TEXT NOT NULL,
   hint TEXT NOT NULL,
   description TEXT NOT NULL,
   pronounce TEXT NOT NULL,
   mode INTEGER NOT NULL,
   ctime INTEGER NOT NULL,
   utime INTEGER NOT NULL,
   c_user INTEGER NOT NULL REFERENCES user_info(uid),
   u_user INTEGER NOT NULL REFERENCES user_info(uid)
 );


* 問題集管理系

** genre

 CREATE TABLE genre (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL
 );

** element_info

 CREATE TABLE element_info (
   eid INTEGER PRIMARY KEY,
   type INTEGER NOT NULL,
   genre INTEGER NOT NULL,
   name TEXT NOT NULL,
   author TEXT NOT NULL,
   writer TEXT NOT NULL,
   description TEXT NOT NULL,
   ctime INTEGER NOT NULL,
   utime INTEGER NOT NULL,
   c_user INTEGER NOT NULL REFERENCES user_info(uid),
   u_user INTEGER NOT NULL REFERENCES user_info(uid),
   open BOOLEAN NOT NULL
 );

** folder

 CREATE TABLE folder (
   eid INTEGER NOT NULL REFERENCES element_info(eid),
   fid INTEGER NOT NULL REFERENCES element_info(eid),
   PRIMARY KEY(eid,fid)
 );

** section

 CREATE TABLE section (
   eid INTEGER NOT NULL REFERENCES element_info(eid),
   qid INTEGER NOT NULL REFERENCES question(qid),
   PRIMARY KEY(eid,qid)
 );

* 履歴管理系

** element_log

 CREATE TABLE element_log (
   lid INTEGER PRIMARY KEY,
   uid INTEGER NOT NULL REFERENCES user_info(uid),
   eid INTEGER NOT NULL REFERENCES element_info(eid),
   mid INTEGER NOT NULL,
   first_reply_time INTEGER NOT NULL,
   n_run INTEGER NOT NULL,
   total_reply_sec INTEGER NOT NULL,
   total_strokes INTEGER NOT NULL,
   n_accomplish INTEGER NOT NULL
 );

** question_log

 CREATE TABLE question_log (
   qid INTEGER NOT NULL REFERENCES question(qid),
   uid INTEGER NOT NULL REFERENCES user_info(uid),
   mid INTEGER NOT NULL,
   nc1 INTEGER NOT NULL,
   ni1 INTEGER NOT NULL,
   nc2 INTEGER NOT NULL,
   ni2 INTEGER NOT NULL,
   last_study_time INTEGER NOT NULL,
   last_mistake_time INTEGER NOT NULL,
   reply_sec INTEGER NOT NULL,
   PRIMARY KEY(qid,uid,mid)
 );

** daily_log

 CREATE TABLE daily_log (
   did INTEGER PRIMARY KEY,
   lid INTEGER NOT NULL REFERENCES element_log(lid),
   start_time INTEGER NOT NULL,
   end_time INTEGER NOT NULL,
   strokes INTEGER NOT NULL
 );

** daily_question_log

 CREATE TABLE daily_question_log (
   did INTEGER NOT NULL REFERENCES daily_log(did),
   qid INTEGER NOT NULL REFERENCES question(qid),
   nc1 SMALLINT NOT NULL,
   ni1 SMALLINT NOT NULL,
   nc2 SMALLINT NOT NULL,
   ni2 SMALLINT NOT NULL,
   reply_sec INTEGER NOT NULL,
   PRIMARY KEY(did,qid)
 );

** looped_questions

 CREATE TABLE looped_questions (
   lid INTEGER NOT NULL REFERENCES element_log(lid),
   qid INTEGER NOT NULL REFERENCES question(qid),
   PRIMARY KEY(lid,qid)
 );


潟若若菴遵

トップ   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS