diff --git a/Database/spam_db.sql b/Database/spam_db.sql new file mode 100644 index 0000000..6a784eb --- /dev/null +++ b/Database/spam_db.sql @@ -0,0 +1,244 @@ +drop table part; +drop table encoding; +drop table header; +drop table recipient; +drop table message; + +drop sequence message_id_generator; +drop sequence recipient_id_generator; +drop sequence header_id_generator; +drop sequence attachment_id_generator; +drop sequence part_id_generator; + + +-------------------------------------------------------------------------------- +-- Sequences for primary keys +-- +create sequence MESSAGE_ID_GENERATOR start 1; +create sequence RECIPIENT_ID_GENERATOR start 1; +create sequence HEADER_ID_GENERATOR start 1; +create sequence ATTACHMENT_ID_GENERATOR start 1; +create sequence PART_ID_GENERATOR start 1; + + +-------------------------------------------------------------------------------- +-- Table MESSAGE +-- +-- Store an RFC822-compliant message. +-- +create table MESSAGE +( -- unique message ID, automatically generated + message_id integer default nextval('MESSAGE_ID_GENERATOR'), + + -- subject of the message (arbitrary text) + subject text not null, + + -- the email address of the message's sender + sender_name varchar(150) not null, + + -- the real name of the message's sender (if any) + sender_address varchar(150) not null, + + -- the time that the message was sent + time_sent timestamp with time zone not null, + + -- message priority + priority char(7) + default 'normal' + not null + check (priority in ('Lowest', 'Low', 'Normal', 'High', 'Highest')), + + -- raw headers of the message (arbitrary text) + -- as per RFC822, everything up to the first blank line + raw_headers text not null, + + -- raw body of the message (arbitrary text) + -- as per RFC822, everything after the first blank line + raw_content text not null, + + -- MD5 hash of the message body, for quick comparison purposes and + -- indexing + content_hash char(32) not null, + + -- raw source of the message (arbitrary text) + -- = headers + blank line + body + raw_source text not null, + + -- is the message body written in HTML? + is_html boolean + default false + not null, + + -- is the message a multipart MIME message? + is_multipart boolean + default false + not null, + + -- primary key + primary key (message_id) +); + + +-------------------------------------------------------------------------------- +-- Table RECIPIENT +-- +-- Store recipients for an RFC822-compliant message. +-- +create table RECIPIENT +( -- unique recipient ID, automatically generated + recipient_id integer default nextval('RECIPIENT_ID_GENERATOR'), + + -- recipient's display name (if any) + recipient_name varchar(150) not null, + + -- recipient's email address + address varchar(150) not null, + + -- type of recipient (To, Cc or Bcc) + recipient_type char(3) + default 'to' + not null + check (recipient_type in ('To', 'Cc', 'Bcc')), + + -- ID of message that this recipient belongs to + message_id integer not null, + + -- primary key + primary key (recipient_id), + + -- foreign key to MESSAGE + foreign key (message_id) references MESSAGE +); + + +-------------------------------------------------------------------------------- +-- Table ENCODING +-- +-- Lookup table of various encoding types. +-- +create table ENCODING +( -- unqiue encoding ID + encoding_id integer, + + -- name of encoding + encoding_name varchar(20) not null, + + -- primary key + primary key (encoding_id) +); + +-------------------------------------------------------------------------------- +-- Defined encoding types +-- + +-- Unspecified encodings should be dealt with at the application level. +insert into encoding (encoding_id, encoding_name) values (1, 'unspecified'); + +-- The most common encodings appear to be 7bit, 8bit, base64 and quoted-printable. +insert into encoding (encoding_id, encoding_name) values (2, '7bit'); +insert into encoding (encoding_id, encoding_name) values (3, '8bit'); +insert into encoding (encoding_id, encoding_name) values (4, 'binhex'); +insert into encoding (encoding_id, encoding_name) values (5, 'base64'); +insert into encoding (encoding_id, encoding_name) values (6, 'uuencode'); +insert into encoding (encoding_id, encoding_name) values (7, 'applesingle'); +insert into encoding (encoding_id, encoding_name) values (8, 'appledouble'); +insert into encoding (encoding_id, encoding_name) values (9, 'quoted-printable'); + +-- Any other encodings encountered should be added to this table as required. + + +-------------------------------------------------------------------------------- +-- Table PART +-- +-- Store parts of a multi-part MIME message. This includes attachments. +-- +create table PART +( -- unique part ID, automatically generated + part_id integer default nextval('PART_ID_GENERATOR'), + + -- MIME type of the part data + mime_type varchar(30) not null, + + -- MIME subtype of the part data + mime_subtype varchar(50) not null, + + -- type of encoding (reference to ENCODING lookup table) + encoding_id integer + default 1 + not null, + + -- raw headers of the part (arbitrary text) + part_raw_headers text not null, + + -- raw body of the part (arbitrary text) + part_raw_content text not null, + + -- is this part an attachment? (either implied by giving the part a name + -- or explicitly specified using a Content-disposition header) + is_attachment boolean not null, + + -- if this part is an attachment, store its name here + attachment_name varchar(150), + + -- ID of message that this part belongs to + message_id integer not null, + + -- ID of part that this part belongs to (if any) + parent_part integer, + + -- primary key + primary key (part_id), + + -- foreign key to ENCODING + foreign key (encoding_id) references ENCODING, + + -- foreign key to MESSAGE + foreign key (message_id) references MESSAGE, + + -- foreign key to PART + foreign key (parent_part) references PART +); + + +-------------------------------------------------------------------------------- +-- Table HEADER +-- +-- Store additional headers of an RFC822-compliant message. +-- +create table HEADER +( -- unique header ID, automatically generated + header_id integer default nextval('HEADER_ID_GENERATOR'), + + -- name of the header (e.g., "X-Mailer" or "Return-Path") + header_name varchar(50) not null, + + -- value of the header (arbitrary text) + header_value text not null, + + -- ID of message that this header belongs to + message_id integer not null, + + -- ID of message part that this header belongs to (if applicable) + part_id integer not null, + + -- primary key + primary key (header_id), + + -- foreign key to MESSAGE + foreign key (message_id) references MESSAGE, + + -- foreign key to PART + foreign key (message_id) references MESSAGE +); + + +-------------------------------------------------------------------------------- +-- Table TEST_DATE +-- +-- A temporary table for quickly testing whether we can insert a particular +-- date value into the . +-- +create table TEST_DATE +( -- timestamp column + a_date timestamp with time zone +);