Commit | Line | Data |
---|---|---|
2bf40f75 MW |
1 | DROP TABLE IF EXISTS wcf1_conversation; |
2 | CREATE TABLE wcf1_conversation ( | |
3 | conversationID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
4 | subject VARCHAR(255) NOT NULL DEFAULT '', | |
5 | time INT(10) NOT NULL DEFAULT 0, | |
f34884b9 | 6 | firstMessageID INT(10), |
2bf40f75 MW |
7 | userID INT(10), |
8 | username VARCHAR(255) NOT NULL DEFAULT '', | |
9 | lastPostTime INT(10) NOT NULL DEFAULT 0, | |
10 | lastPosterID INT(10), | |
11 | lastPoster VARCHAR(255) NOT NULL DEFAULT '', | |
12 | replies MEDIUMINT(7) NOT NULL DEFAULT 0, | |
13 | attachments SMALLINT(5) NOT NULL DEFAULT 0, | |
14 | participants MEDIUMINT(7) NOT NULL DEFAULT 0, | |
15 | participantSummary TEXT, | |
16 | participantCanInvite TINYINT(1) NOT NULL DEFAULT 0, | |
17 | isClosed TINYINT(1) NOT NULL DEFAULT 0, | |
18 | isDraft TINYINT(1) NOT NULL DEFAULT 0, | |
9544b6b4 MW |
19 | draftData MEDIUMTEXT, |
20 | ||
21 | KEY (userID, isDraft) | |
2bf40f75 MW |
22 | ); |
23 | ||
24 | DROP TABLE IF EXISTS wcf1_conversation_to_user; | |
25 | CREATE TABLE wcf1_conversation_to_user ( | |
26 | conversationID INT(10) NOT NULL, | |
27 | participantID INT(10), | |
a9bfc5bd | 28 | username VARCHAR(255) NOT NULL DEFAULT '', |
2bf40f75 MW |
29 | hideConversation TINYINT(1) NOT NULL DEFAULT 0, |
30 | isInvisible TINYINT(1) NOT NULL DEFAULT 0, | |
31 | lastVisitTime INT(10) NOT NULL DEFAULT 0, | |
32 | ||
33 | UNIQUE KEY (participantID, conversationID), | |
34 | KEY (participantID, hideConversation) | |
35 | ); | |
36 | ||
37 | DROP TABLE IF EXISTS wcf1_conversation_message; | |
38 | CREATE TABLE wcf1_conversation_message ( | |
39 | messageID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
40 | conversationID INT(10) NOT NULL, | |
41 | userID INT(10), | |
42 | username VARCHAR(255) NOT NULL DEFAULT '', | |
43 | message MEDIUMTEXT NOT NULL, | |
44 | time INT(10) NOT NULL DEFAULT 0, | |
45 | attachments SMALLINT(5) NOT NULL DEFAULT 0, | |
2bf40f75 | 46 | enableHtml TINYINT(1) NOT NULL DEFAULT 0, |
2bf40f75 | 47 | ipAddress VARCHAR(39) NOT NULL DEFAULT '', |
e7ed68e5 MW |
48 | lastEditTime INT(10) NOT NULL DEFAULT 0, |
49 | editCount MEDIUMINT(7) NOT NULL DEFAULT 0, | |
a0c1a541 | 50 | hasEmbeddedObjects TINYINT(1) NOT NULL DEFAULT 0, |
2bf40f75 MW |
51 | |
52 | KEY (conversationID, userID), | |
53 | KEY (ipAddress) | |
54 | ); | |
55 | ||
56 | -- labels | |
57 | DROP TABLE IF EXISTS wcf1_conversation_label; | |
58 | CREATE TABLE wcf1_conversation_label ( | |
59 | labelID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
60 | userID INT(10) NOT NULL, | |
61 | label VARCHAR(80) NOT NULL DEFAULT '', | |
7c5aa434 | 62 | cssClassName VARCHAR(255) NOT NULL DEFAULT '' |
2bf40f75 MW |
63 | ); |
64 | ||
65 | DROP TABLE IF EXISTS wcf1_conversation_label_to_object; | |
66 | CREATE TABLE wcf1_conversation_label_to_object ( | |
67 | labelID INT(10) NOT NULL, | |
68 | conversationID INT(10) NOT NULL, | |
69 | ||
70 | UNIQUE KEY (labelID, conversationID) | |
71 | ); | |
72 | ||
73 | ALTER TABLE wcf1_conversation ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; | |
74 | ALTER TABLE wcf1_conversation ADD FOREIGN KEY (lastPosterID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; | |
f34884b9 | 75 | ALTER TABLE wcf1_conversation ADD FOREIGN KEY (firstMessageID) REFERENCES wcf1_conversation_message (messageID) ON DELETE SET NULL; |
2bf40f75 MW |
76 | |
77 | ALTER TABLE wcf1_conversation_to_user ADD FOREIGN KEY (conversationID) REFERENCES wcf1_conversation (conversationID) ON DELETE CASCADE; | |
78 | ALTER TABLE wcf1_conversation_to_user ADD FOREIGN KEY (participantID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; | |
79 | ||
80 | ALTER TABLE wcf1_conversation_message ADD FOREIGN KEY (conversationID) REFERENCES wcf1_conversation (conversationID) ON DELETE CASCADE; | |
81 | ALTER TABLE wcf1_conversation_message ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; | |
82 | ||
83 | ALTER TABLE wcf1_conversation_label ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE CASCADE; | |
84 | ||
85 | ALTER TABLE wcf1_conversation_label_to_object ADD FOREIGN KEY (labelID) REFERENCES wcf1_conversation_label (labelID) ON DELETE CASCADE; | |
86 | ALTER TABLE wcf1_conversation_label_to_object ADD FOREIGN KEY (conversationID) REFERENCES wcf1_conversation (conversationID) ON DELETE CASCADE; |