--------------------------
ǡ١ (DB) ȤϢưˤĤ
* SimRM DB С: 1.0 (2007-10-18)
--------------------------

DB Ȥäƥ᡼ǡ
Υƥȥեϼ SimRM Υơ֥빽ˤĤƵҤ롣
ʤεˡ PostgreSQL ˰¸Ƥ뤫⤷ʤ


///////////////////////////////////////////////////////////
// б RDBMS
///////////////////////////////////////////////////////////
[PostgreSQL]
- BSD 饤󥹤Ǥ뤳ȡ
- ꡢƳưפ˴뤳ȡ
- ɥ (ܸޤ) ˥䤹ȡ



///////////////////////////////////////////////////////////
// ơ֥
///////////////////////////////////////////////////////////
[reservations, reservations_info, mail_info]
  ᥤȤʤơ֥뷲
  ͽ᡼ξ
  mail_info ϱ³Ū˥ǡݻ롣


[memos]
  ˴ؤơ֥롣
  ()

[stop_mail]
  ߤƤ᡼Υơ֥롣
  ()

[user_list]
  桼ꥹȡ(᡼륢ɥ쥹)


[histories, histories_info]
  ᡼


[white_list, black_list]
   / ݤ桼ꥹȡ(ɽ)



///////////////////////////////////////////////////////////
// ơ֥ܺپ (̾)
///////////////////////////////////////////////////////////

[reservations]
  r_id            /* ᡼ֹ */
  rinfo_id        /* reservation_info ιֹ */
  to_user_id      /* 桼 ID */


[reservations_info]
  rinfo_id        /* ֹ */
  minfo_id        /* mail_info ιֹ */
  next_send_time  /* ľΥ᡼ͽ */
  sent_hinfo_id   /* hisotries_info ֹ (ֺǶ᡼) */


[mail_info]
  minfo_id        /* ֹ */
  from_user_id    /* 桼 ID */
  received_time   /* ᡼μ */
  saved_filename  /* ¸᡼ե̾ */
  subject         /* ᡼η̾ */


[histories]
  h_id            /* ֹ */
  hinfo_id        /* histories_info ιֹ */
  denied          /* ݤ줿ɤ */
  to_user_id      /* 桼 ID */
  white_id        /*  white_list ֹ */
  black_id        /*  black_list ֹ */


[histories_info]
  hinfo_id        /* ֹ */
  minfo_id        /* mail_info ιֹ */ 
  sent_time       /*  */


[user_list]
  user_id         /* 桼 ID */
  user_address    /* 桼̾ (᡼륢ɥ쥹) */


[white_list]
  white_id        /* ۥ磻ȥꥹȹ ID */
  white_valid     /* ߡͭɤ */
  white_pattern   /* ɽ */


[black_list]
  black_id        /* ֥åꥹȹ ID */
  black_valid     /* ߡͭɤ */
  black_pattern   /* ɽ */



///////////////////////////////////////////////////////////
// ơ֥ܺپ (̾ȥǡơ֥)
// * ֥ơ֥פϥơ֥̾ˤ -> ο
///////////////////////////////////////////////////////////

[reservations] -> 8
  r_id            serial  PRIMARY KEY,
  rinfo_id        integer NOT NULL REFERENCES reservations_info (rinfo_id),
  to_user_id      integer NOT NULL REFERENCES user_list (user_id)


[reservations_info] -> 7
  rinfo_id        serial  PRIMARY KEY,
  minfo_id        integer NOT NULL REFERENCES mail_info (minfo_id),
  next_send_time  timestamp with time zone NOT NULL,
  sent_hinfo_id   integer NOT NULL REFERENCES histories_info (hinfo_id)


[mail_info] -> 4
  minfo_id        serial  PRIMARY KEY,
  from_user_id    integer NOT NULL REFERENCES user_list (user_id),
  received_time   timestamp with time zone NOT NULL,
  saved_filename  text NULL,
  subject         text NOT NULL


[histories] -> 6
  h_id            serial  PRIMARY KEY,
  hinfo_id        integer NOT NULL REFERENCES histories_info (hinfo_id),
  denied          boolean NOT NULL,
  to_user_id      integer NOT NULL REFERENCES user_list (user_id),
  white_id        integer NOT NULL REFERENCES white_list (white_id),
  black_id        integer NOT NULL REFERENCES black_list (black_id)


[histories_info] -> 5
  hinfo_id        serial  PRIMARY KEY,
  minfo_id        integer NOT NULL REFERENCES mail_info (minfo_id),
  sent_time       timestamp with time zone NOT NULL


[user_list] -> 1
  user_id         serial  PRIMARY KEY,
  user_address    text NOT NULL UNIQUE


[white_list] -> 2
  white_id        serial  PRIMARY KEY,
  white_valid     boolean NOT NULL,
  white_pattern   text NOT NULL UNIQUE


[black_list] -> 3
  black_id        serial  PRIMARY KEY,
  black_valid     boolean NOT NULL,
  black_pattern   text NOT NULL UNIQUE



///////////////////////////////////////////////////////////
// ưή
///////////////////////////////////////////////////////////
ư DB ȥ᡼եå
Τ褦ήǽԤ

1.
   ¸᡼ե뤬 DB ˤ뤫å롣
   mail_info  saved_filename 򥭡˸롣
   DB ˤ᡼ե 1.1 ء
   DB ˤʤ᡼ե 1.2 ء

1.1. 
   next_send_time ᤮Ƥʤв⤷ʤ
   next_send_time ᤮Ƥ᡼
   ˱ƺޤϹ
   (clean Ǥ褤)

1.2.
   DB ˥ǡɲä롣



///////////////////////////////////////////////////////////
// [] PostgreSQL ǤΥơ֥ SQL ʸ
///////////////////////////////////////////////////////////

CREATE TABLE user_list (
  user_id         serial  PRIMARY KEY,
  user_address    text NOT NULL UNIQUE
);


CREATE TABLE white_list (
  white_id        serial  PRIMARY KEY,
  white_valid     boolean NOT NULL,
  white_pattern   text NOT NULL UNIQUE
);


CREATE TABLE black_list (
  black_id        serial  PRIMARY KEY,
  black_valid     boolean NOT NULL,
  black_pattern   text NOT NULL UNIQUE
);


CREATE TABLE mail_info (
  minfo_id        serial  PRIMARY KEY,
  from_user_id    integer NOT NULL REFERENCES user_list (user_id),
  received_time   timestamp with time zone NOT NULL,
  saved_filename  text NULL,
  subject         text NOT NULL
);


CREATE TABLE histories_info (
  hinfo_id        serial  PRIMARY KEY,
  minfo_id        integer NOT NULL REFERENCES mail_info (minfo_id),
  sent_time       timestamp with time zone NOT NULL
);


CREATE TABLE histories (
  h_id            serial  PRIMARY KEY,
  hinfo_id        integer NOT NULL REFERENCES histories_info (hinfo_id),
  denied          boolean NOT NULL,
  to_user_id      integer NOT NULL REFERENCES user_list (user_id),
  white_id        integer NOT NULL REFERENCES white_list (white_id),
  black_id        integer NOT NULL REFERENCES black_list (black_id)
);


CREATE TABLE reservations_info (
  rinfo_id        serial  PRIMARY KEY,
  minfo_id        integer NOT NULL REFERENCES mail_info (minfo_id),
  next_send_time  timestamp with time zone NOT NULL,
  sent_hinfo_id   integer NOT NULL REFERENCES histories_info (hinfo_id)
);


CREATE TABLE reservations (
  r_id            serial  PRIMARY KEY,
  rinfo_id        integer NOT NULL REFERENCES reservations_info (rinfo_id),
  to_user_id      integer NOT NULL REFERENCES user_list (user_id)
);



///////////////////////////////////////////////////////////
// [] PostgreSQL ǤΥǡɲ SQL ʸ (ץǡ)
// * 8.2 ̤ʣԥǡʤᡢ1 Ĥ
///////////////////////////////////////////////////////////

INSERT INTO user_list (user_address) VALUES ('address1@hoo.foo.com');
INSERT INTO user_list (user_address) VALUES ('address2@hoo.foo.com');
INSERT INTO user_list (user_address) VALUES ('address3@hoo.foo.com');


INSERT INTO white_list (white_valid, white_pattern) VALUES (true, E'address1\\@hoo\\.foo\\.com');
INSERT INTO white_list (white_valid, white_pattern) VALUES (true, E'address2\\@hoo\\.foo\\.com');


INSERT INTO black_list (black_valid, black_pattern) VALUES (false, E'address1\\@hoo\\.foo\\.com');
INSERT INTO black_list (black_valid, black_pattern) VALUES (false, E'address2\\@hoo\\.foo\\.com');


[EOF]
