-- This script requires SQL Server 2008
CREATE DATABASE Email;
USE testEmail;
-- We store messages in folders
CREATE TABLE Folder
( Id INT PRIMARY KEY IDENTITY
, ParentId INT FOREIGN KEY REFERENCES Folder(Id)
, DisplayName VARCHAR(64) NOT NULL
, Description TEXT
, CONSTRAINT nk_Folder UNIQUE (ParentId, DisplayName)
);
-- an address is a unique emaill address
CREATE TABLE Address
( Id INT PRIMARY KEY IDENTITY
, FriendlyName VARCHAR(128)
, UserName VARCHAR(64) NOT NULL -- always store in lower case
, DomainName VARCHAR(128) NOT NULL -- always store in lower case
, DisplayName AS
CASE WHEN FriendlyName IS NULL THEN
UserName + '@' + DomainName
ELSE
FriendlyName + ' <' + UserName + '@' + DomainName + '>'
END
, CONSTRAINT nk_Address UNIQUE (UserName, DomainName)
);
-- Each message represents a single email
CREATE TABLE Message
( Id INT PRIMARY KEY IDENTITY
, FolderId INT FOREIGN KEY REFERENCES Folder(Id) NOT NULL
, SenderId INT FOREIGN KEY REFERENCES Address(Id) NOT NULL
, SentOn DATETIME NOT NULL
, Subject VARCHAR(1024) NOT NULL
, EmailHeader TEXT
, EmailBody TEXT
, IsUnread BIT NOT NULL DEFAULT 1
, IsJunk BIT NOT NULL DEFAULT 0
, IsStar BIT NOT NULL DEFAULT 0
, Priority TINYINT NOT NULL DEFAULT 3
CHECK (Priority IN (1,2,3,4,5))
);
-- One entry for each header (to/cc/bcc) on a message
CREATE TABLE MessageAddress
( MessageId INT FOREIGN KEY REFERENCES Message(Id) NOT NULL
, HeaderType CHAR(4) NOT NULL
CHECK (HeaderType IN ('to', 'from', 'cc', 'bcc', 'r-to'))
, AddressId INT FOREIGN KEY REFERENCES Address(Id) NOT NULL
, CONSTRAINT pk_MessageAddress
PRIMARY KEY (MessageId, HeaderType, AddressId)
);
-- An attachment is a single binary file
CREATE TABLE Attachment
( Id INT PRIMARY KEY IDENTITY
, FileName VARCHAR(512) NOT NULL
, Extension VARCHAR(64) NOT NULL
, Content VARBINARY(MAX) NOT NULL
, FileSize BIGINT NOT NULL
, Hash CHAR(32) NOT NULL
, CONSTRAINT nk_Attachment UNIQUE (Hash, FileSize)
);
-- Join Table
CREATE TABLE MessageAttachment
( MessageId INT NOT NULL
FOREIGN KEY REFERENCES Message(Id)
, AttachmentId INT NOT NULL
FOREIGN KEY REFERENCES Attachment(Id)
, CONSTRAINT pk_MessageAttachment
PRIMARY KEY (MessageId, AttachmentId)
);
DECLARE @RootId INT;
SET @RootId = 1;
SET IDENTITY_INSERT Folder ON;
INSERT INTO Folder (Id, DisplayName)
VALUES (@RootId, '');
SET IDENTITY_INSERT Folder OFF;
-- some root level folders
INSERT INTO
Folder (ParentId, DisplayName)
VALUES (@RootId, 'Inbox')
, (@RootId, 'Outbox')
, (@RootId, 'Sent')
, (@RootId, 'Trash')
, (@RootId, 'Drafts')
;
DECLARE @InboxId INT;
SET @InboxId = (
SELECT Id
FROM Folder
WHERE ParentId = @RootId
AND DisplayName = 'Inbox'
);
-- a few sub folders
INSERT INTO
Folder (DisplayName, ParentId)
VALUES ('Home', @InboxId)
, ('School', @InboxId)
, ('Work', @InboxId)
;
-- I guess we should a couple of addresses
INSERT INTO
Address (FriendlyName, UserName, DomainName)
VALUES ('Brent Larsen', 'brentoboy', 'gmail.com')
, (NULL, 'jimbob', 'someplace.com')