Messages.

1 files changed, 30 insertions, 0 deletions

diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index 8c3e72f..cd1a7ee 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -103,6 +103,18 @@ CREATE TABLE IF NOT EXISTS posts (
 	body TEXT
 );
 
+CREATE TABLE IF NOT EXISTS messages (
+	message_id INTEGER PRIMARY KEY,
+	from_id INTEGER,
+	to_id INTEGER,
+	time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+	subject TEXT,
+	body TEXT,
+	read BOOLEAN DEFAULT 0,
+	deleted_from_inbox BOOLEAN DEFAULT 0,
+	deleted_from_outbox BOOLEAN DEFAULT 0
+);
+
 DROP TRIGGER IF EXISTS update_reply_count;
 CREATE TRIGGER update_reply_count AFTER INSERT ON posts
 BEGIN
@@ -239,3 +251,21 @@ CREATE VIEW post_view AS
 	FROM posts
 	JOIN users AS author ON posts.author_id = author.user_id
 	;
+
+DROP VIEW IF EXISTS message_view;
+CREATE VIEW message_view AS
+	SELECT messages.message_id
+		, messages.from_id
+		, users_from.name AS from_name
+		, messages.to_id
+		, users_to.name AS to_name
+		, messages.time
+		, messages.subject
+		, messages.body
+		, messages.read
+		, messages.deleted_from_inbox
+		, messages.deleted_from_outbox
+	FROM messages
+	JOIN users AS users_from ON messages.from_id = users_from.user_id
+	JOIN users AS users_to ON messages.to_id = users_to.user_id
+	;