table_forum_threads.go (8624B)
1 package database 2 3 import ( 4 "dkforest/pkg/utils" 5 "github.com/ProtonMail/go-crypto/openpgp/clearsign" 6 "github.com/google/uuid" 7 html2 "html" 8 "regexp" 9 "strings" 10 "time" 11 12 "github.com/sirupsen/logrus" 13 14 bf "dkforest/pkg/blackfriday/v2" 15 ) 16 17 type ForumCategoryID int64 18 19 type ForumCategory struct { 20 ID ForumCategoryID 21 Idx int64 22 Name string 23 Slug string 24 } 25 26 type ForumThreadID int64 27 type ForumThreadUUID string 28 29 type ForumThread struct { 30 ID ForumThreadID 31 UUID ForumThreadUUID 32 Name string 33 UserID UserID 34 CategoryID ForumCategoryID 35 CreatedAt time.Time 36 User User 37 Category ForumCategory 38 } 39 40 func MakeForumThread(threadName string, userID UserID, categoryID ForumCategoryID) ForumThread { 41 return ForumThread{UUID: ForumThreadUUID(uuid.New().String()), Name: threadName, UserID: userID, CategoryID: categoryID} 42 } 43 44 func (u *ForumThread) DoSave(db *DkfDB) { 45 if err := db.db.Save(u).Error; err != nil { 46 logrus.Error(err) 47 } 48 } 49 50 func (d *DkfDB) GetForumCategories() (out []ForumCategory, err error) { 51 err = d.db.Find(&out).Order("idx ASC, name ASC").Error 52 return 53 } 54 55 func (d *DkfDB) GetForumCategoryBySlug(slug string) (out ForumCategory, err error) { 56 err = d.db.First(&out, "slug = ?", slug).Error 57 return 58 } 59 60 type ForumMessageID int64 61 62 type ForumMessageUUID string 63 64 type ForumMessage struct { 65 ID ForumMessageID 66 UUID ForumMessageUUID 67 Message string 68 UserID UserID 69 ThreadID ForumThreadID 70 IsSigned bool 71 CreatedAt time.Time 72 User User 73 } 74 75 func MakeForumMessage(message string, userID UserID, threadID ForumThreadID) ForumMessage { 76 return ForumMessage{UUID: ForumMessageUUID(uuid.New().String()), Message: message, UserID: userID, ThreadID: threadID} 77 } 78 79 type ForumReadRecord struct { 80 UserID UserID 81 ThreadID ForumThreadID 82 ReadAt time.Time 83 } 84 85 func (d *DkfDB) UpdateForumReadRecord(userID UserID, threadID ForumThreadID) { 86 now := time.Now() 87 res := d.db.Table("forum_read_records").Where("user_id = ? AND thread_id = ?", userID, threadID).Update("read_at", now) 88 if res.RowsAffected == 0 { 89 d.db.Create(ForumReadRecord{UserID: userID, ThreadID: threadID, ReadAt: now}) 90 } 91 } 92 93 // DoSave user in the database, ignore error 94 func (u *ForumReadRecord) DoSave(db *DkfDB) { 95 if err := db.db.Save(u).Error; err != nil { 96 logrus.Error(err) 97 } 98 } 99 100 // DoSave user in the database, ignore error 101 func (u *ForumMessage) DoSave(db *DkfDB) { 102 if err := db.db.Save(u).Error; err != nil { 103 logrus.Error(err) 104 } 105 } 106 107 func (m *ForumMessage) Escape(db *DkfDB) string { 108 msg := m.Message 109 if m.IsSigned { 110 if b, _ := clearsign.Decode([]byte(msg)); b != nil { 111 msg = string(b.Plaintext) 112 } 113 } 114 res := strings.Replace(msg, "\r", "", -1) 115 res = html2.EscapeString(res) 116 resBytes := bf.Run([]byte(res), bf.WithRenderer(MyRendererForum(db, true, true)), bf.WithExtensions(bf.CommonExtensions|bf.HardLineBreak)) 117 res = string(resBytes) 118 119 // Tags 120 var tagRgx = regexp.MustCompile(`@(\w{3,20})`) 121 if tagRgx.MatchString(res) { 122 res = tagRgx.ReplaceAllStringFunc(res, func(s string) string { 123 if user, err := db.GetUserByUsername(Username(strings.TrimPrefix(s, "@"))); err == nil { 124 return `<span style="color: ` + user.ChatColor + `;">` + s + `</span>` 125 } 126 return s 127 }) 128 } 129 return res 130 } 131 132 func (d *DkfDB) GetForumMessage(messageID ForumMessageID) (out ForumMessage, err error) { 133 err = d.db.First(&out, "id = ?", messageID).Error 134 return 135 } 136 137 func (d *DkfDB) GetForumMessageByUUID(messageUUID ForumMessageUUID) (out ForumMessage, err error) { 138 err = d.db.First(&out, "uuid = ?", messageUUID).Error 139 return 140 } 141 142 func (d *DkfDB) DeleteForumMessageByID(messageID ForumMessageID) error { 143 return d.db.Where("id = ?", messageID).Delete(&ForumMessage{}).Error 144 } 145 146 func (d *DkfDB) DeleteForumThreadByID(threadID ForumThreadID) error { 147 return d.db.Where("id = ?", threadID).Delete(&ForumThread{}).Error 148 } 149 150 func (m *ForumMessage) CanEdit() bool { 151 //return time.Since(m.CreatedAt) < time.Hour 152 return true 153 } 154 155 func (m *ForumMessage) ValidateSignature(pkey string) bool { 156 if pkey == "" { 157 return false 158 } 159 return utils.PgpCheckClearSignMessage(pkey, m.Message) 160 } 161 162 func (d *DkfDB) GetForumThread(threadID ForumThreadID) (out ForumThread, err error) { 163 err = d.db.First(&out, "id = ? AND is_club = 1", threadID).Error 164 return 165 } 166 167 func (d *DkfDB) GetForumThreadByID(threadID ForumThreadID) (out ForumThread, err error) { 168 err = d.db.First(&out, "id = ? AND is_club = 0", threadID).Error 169 return 170 } 171 172 func (d *DkfDB) GetForumThreadByUUID(threadUUID ForumThreadUUID) (out ForumThread, err error) { 173 err = d.db.First(&out, "uuid = ? AND is_club = 0", threadUUID).Error 174 return 175 } 176 177 func (d *DkfDB) GetForumThreads() (out []ForumThread, err error) { 178 err = d.db.Order("id DESC").Find(&out).Error 179 return 180 } 181 182 type ForumNews struct { 183 ForumThread 184 ForumMessage 185 User 186 } 187 188 type ForumThreadAug struct { 189 ForumThread 190 Author string 191 AuthorChatColor string 192 LastMsgAuthor string 193 LastMsgChatColor string 194 LastMsgChatFont string 195 LastMsgCreatedAt time.Time 196 IsUnread bool 197 RepliesCount int64 198 } 199 200 func (d *DkfDB) GetClubForumThreads(userID UserID) (out []ForumThreadAug, err error) { 201 err = d.db.Raw(`SELECT t.*, 202 u.username as author, 203 u.chat_color as author_chat_color, 204 lu.username as last_msg_author, 205 lu.chat_color as last_msg_chat_color, 206 lu.chat_font as last_msg_chat_font, 207 m.created_at as last_msg_created_at, 208 COALESCE((r.read_at < m.created_at), 1) as is_unread 209 FROM forum_threads t 210 INNER JOIN users u ON u.id = t.user_id 211 LEFT JOIN forum_messages m ON m.thread_id = t.id AND m.id = (SELECT max(id) FROM forum_messages WHERE thread_id = t.id) 212 INNER JOIN users lu ON lu.id = m.user_id 213 LEFT JOIN forum_read_records r ON r.user_id = ? AND r.thread_id = t.id 214 WHERE t.is_club = 1 215 ORDER BY t.id DESC`, userID).Scan(&out).Error 216 return 217 } 218 219 func (d *DkfDB) GetPublicForumCategoryThreads(userID UserID, categoryID ForumCategoryID) (out []ForumThreadAug, err error) { 220 err = d.db.Raw(`SELECT t.*, 221 u.username as author, 222 u.chat_color as author_chat_color, 223 lu.username as last_msg_author, 224 lu.chat_color as last_msg_chat_color, 225 lu.chat_font as last_msg_chat_font, 226 m.created_at as last_msg_created_at, 227 COALESCE((r.read_at < m.created_at), 1) as is_unread, 228 mmm.replies_count 229 FROM forum_threads t 230 -- Count replies 231 LEFT JOIN (SELECT mm.thread_id, COUNT(mm.id) as replies_count FROM forum_messages mm GROUP BY mm.thread_id) as mmm ON mmm.thread_id = t.id 232 -- Join author user 233 INNER JOIN users u ON u.id = t.user_id 234 -- Find last message for thread 235 LEFT JOIN forum_messages m ON m.thread_id = t.id AND m.id = (SELECT max(id) FROM forum_messages WHERE thread_id = t.id) 236 -- Join last message user 237 INNER JOIN users lu ON lu.id = m.user_id 238 -- Get read record for the authUser & thread 239 LEFT JOIN forum_read_records r ON r.user_id = ? AND r.thread_id = t.id 240 WHERE t.is_club = 0 AND t.category_id = ? 241 ORDER BY m.created_at DESC, t.id DESC`, userID, categoryID).Scan(&out).Error 242 return 243 } 244 245 func (d *DkfDB) GetForumNews(categoryID ForumCategoryID) (out []ForumNews, err error) { 246 err = d.db.Raw(`SELECT t.*, 247 m.*, 248 mu.* 249 FROM forum_threads t 250 -- Find first message for thread 251 INNER JOIN forum_messages m ON m.thread_id = t.id AND m.id = (SELECT min(id) FROM forum_messages WHERE thread_id = t.id) 252 -- Join last message user 253 INNER JOIN users mu ON mu.id = m.user_id 254 WHERE t.category_id = ? 255 ORDER BY m.created_at DESC, t.id DESC`, categoryID).Scan(&out).Error 256 return 257 } 258 259 func (d *DkfDB) GetPublicForumThreadsSearch(userID UserID) (out []ForumThreadAug, err error) { 260 err = d.db.Raw(`SELECT t.*, 261 u.username as author, 262 u.chat_color as author_chat_color, 263 lu.username as last_msg_author, 264 lu.chat_color as last_msg_chat_color, 265 lu.chat_font as last_msg_chat_font, 266 m.created_at as last_msg_created_at, 267 COALESCE((r.read_at < m.created_at), 1) as is_unread, 268 mmm.replies_count 269 FROM forum_threads t 270 -- Count replies 271 LEFT JOIN (SELECT mm.thread_id, COUNT(mm.id) as replies_count FROM forum_messages mm GROUP BY mm.thread_id) as mmm ON mmm.thread_id = t.id 272 -- Join author user 273 INNER JOIN users u ON u.id = t.user_id 274 -- Find last message for thread 275 LEFT JOIN forum_messages m ON m.thread_id = t.id AND m.id = (SELECT max(id) FROM forum_messages WHERE thread_id = t.id) 276 -- Join last message user 277 INNER JOIN users lu ON lu.id = m.user_id 278 -- Get read record for the authUser & thread 279 LEFT JOIN forum_read_records r ON r.user_id = ? AND r.thread_id = t.id 280 WHERE t.is_club = 0 281 ORDER BY m.created_at DESC, t.id DESC`, userID).Scan(&out).Error 282 return 283 } 284 285 func (d *DkfDB) GetThreadMessages(threadID ForumThreadID) (out []ForumMessage, err error) { 286 err = d.db.Preload("User").Find(&out, "thread_id = ?", threadID).Error 287 return 288 }