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, | |
219838a1 AE |
32 | joinedAt INT(10) NOT NULL DEFAULT 0, |
33 | leftAt INT(10) NOT NULL DEFAULT 0, | |
f7ac06dc | 34 | lastMessageID INT(10) NULL, |
6066d9ac | 35 | leftByOwnChoice TINYINT(1) NOT NULL DEFAULT 1, |
2bf40f75 MW |
36 | |
37 | UNIQUE KEY (participantID, conversationID), | |
38 | KEY (participantID, hideConversation) | |
39 | ); | |
40 | ||
41 | DROP TABLE IF EXISTS wcf1_conversation_message; | |
42 | CREATE TABLE wcf1_conversation_message ( | |
43 | messageID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
44 | conversationID INT(10) NOT NULL, | |
45 | userID INT(10), | |
46 | username VARCHAR(255) NOT NULL DEFAULT '', | |
47 | message MEDIUMTEXT NOT NULL, | |
48 | time INT(10) NOT NULL DEFAULT 0, | |
49 | attachments SMALLINT(5) NOT NULL DEFAULT 0, | |
2bf40f75 | 50 | enableHtml TINYINT(1) NOT NULL DEFAULT 0, |
2bf40f75 | 51 | ipAddress VARCHAR(39) NOT NULL DEFAULT '', |
e7ed68e5 MW |
52 | lastEditTime INT(10) NOT NULL DEFAULT 0, |
53 | editCount MEDIUMINT(7) NOT NULL DEFAULT 0, | |
a0c1a541 | 54 | hasEmbeddedObjects TINYINT(1) NOT NULL DEFAULT 0, |
2bf40f75 MW |
55 | |
56 | KEY (conversationID, userID), | |
57 | KEY (ipAddress) | |
58 | ); | |
59 | ||
60 | -- labels | |
61 | DROP TABLE IF EXISTS wcf1_conversation_label; | |
62 | CREATE TABLE wcf1_conversation_label ( | |
63 | labelID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
64 | userID INT(10) NOT NULL, | |
65 | label VARCHAR(80) NOT NULL DEFAULT '', | |
7c5aa434 | 66 | cssClassName VARCHAR(255) NOT NULL DEFAULT '' |
2bf40f75 MW |
67 | ); |
68 | ||
69 | DROP TABLE IF EXISTS wcf1_conversation_label_to_object; | |
70 | CREATE TABLE wcf1_conversation_label_to_object ( | |
71 | labelID INT(10) NOT NULL, | |
72 | conversationID INT(10) NOT NULL, | |
73 | ||
74 | UNIQUE KEY (labelID, conversationID) | |
75 | ); | |
76 | ||
5405d205 | 77 | ALTER TABLE wcf1_user_group ADD canBeAddedAsConversationParticipant TINYINT(1) NOT NULL DEFAULT 0; |
83545ad0 | 78 | |
2bf40f75 MW |
79 | ALTER TABLE wcf1_conversation ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; |
80 | ALTER TABLE wcf1_conversation ADD FOREIGN KEY (lastPosterID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; | |
f34884b9 | 81 | ALTER TABLE wcf1_conversation ADD FOREIGN KEY (firstMessageID) REFERENCES wcf1_conversation_message (messageID) ON DELETE SET NULL; |
2bf40f75 MW |
82 | |
83 | ALTER TABLE wcf1_conversation_to_user ADD FOREIGN KEY (conversationID) REFERENCES wcf1_conversation (conversationID) ON DELETE CASCADE; | |
84 | ALTER TABLE wcf1_conversation_to_user ADD FOREIGN KEY (participantID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; | |
f7ac06dc | 85 | ALTER TABLE wcf1_conversation_to_user ADD FOREIGN KEY (lastMessageID) REFERENCES wcf1_conversation_message (messageID) ON DELETE SET NULL; |
2bf40f75 MW |
86 | |
87 | ALTER TABLE wcf1_conversation_message ADD FOREIGN KEY (conversationID) REFERENCES wcf1_conversation (conversationID) ON DELETE CASCADE; | |
88 | ALTER TABLE wcf1_conversation_message ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL; | |
89 | ||
90 | ALTER TABLE wcf1_conversation_label ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE CASCADE; | |
91 | ||
92 | ALTER TABLE wcf1_conversation_label_to_object ADD FOREIGN KEY (labelID) REFERENCES wcf1_conversation_label (labelID) ON DELETE CASCADE; | |
93 | ALTER TABLE wcf1_conversation_label_to_object ADD FOREIGN KEY (conversationID) REFERENCES wcf1_conversation (conversationID) ON DELETE CASCADE; |