database.go (15873B)
1 package database 2 3 import ( 4 "dkforest/pkg/config" 5 "dkforest/pkg/utils" 6 "fmt" 7 "github.com/mattn/go-sqlite3" 8 "github.com/sirupsen/logrus" 9 "gorm.io/driver/sqlite" 10 "gorm.io/gorm" 11 "gorm.io/gorm/logger" 12 "net/url" 13 "path/filepath" 14 "strings" 15 "time" 16 ) 17 18 type DkfDB struct { 19 db *gorm.DB 20 } 21 22 func (d *DkfDB) DB() *gorm.DB { 23 return d.db 24 } 25 26 // Compile time checks to ensure type satisfies IDkfDB interface 27 var _ IDkfDB = (*DkfDB)(nil) 28 29 type IDkfDB interface { 30 AddBlacklistedUser(userID, blacklistedUserID UserID) 31 AddLinkCategory(linkID, categoryID int64) (err error) 32 AddLinkTag(linkID, tagID int64) (err error) 33 AddUserToRoomGroup(roomID RoomID, groupID GroupID, userID UserID) (out ChatRoomUserGroup, err error) 34 AddWhitelistedUser(userID, whitelistedUserID UserID) 35 CanRenameTo(oldUsername, newUsername Username) error 36 CanUseUsername(username Username, isFirstUser bool) error 37 ClearRoomGroup(roomID RoomID, groupID GroupID) (err error) 38 CreateChatReaction(userID UserID, messageID, reaction int64) error 39 CreateChatRoomGroup(roomID RoomID, name, color string) (out ChatRoomGroup, err error) 40 CreateDownload(userID UserID, filename string) (out Download, err error) 41 CreateEncryptedUploadWithSize(fileName string, content []byte, userID UserID, size int64) (*Upload, error) 42 CreateFiledrop() (out Filedrop, err error) 43 CreateInboxMessage(msg string, roomID RoomID, fromUserID, toUserID UserID, isPm, moderators bool, msgID *int64) 44 CreateInvitation(userID UserID) (out Invitation, err error) 45 CreateKarmaHistory(karma int64, description string, userID UserID, fromUserID *int64) (out KarmaHistory, err error) 46 CreateKickMsg(kickedUser, kickedByUser User) 47 CreateLink(url, title, description, shorthand string) (out Link, err error) 48 CreateLinkMirror(linkID int64, link string) (out LinksMirror, err error) 49 CreateLinkPgp(linkID int64, title, description, publicKey string) (out LinksPgp, err error) 50 CreateLinksCategory(category string) (out LinksCategory, err error) 51 CreateLinksTag(tag string) (out LinksTag, err error) 52 CreateMsg(raw, txt, roomKey string, roomID RoomID, userID UserID, toUserID *UserID, hellbanMsg bool) (out ChatMessage, err error) 53 CreateNotification(msg string, userID UserID) 54 CreateOrEditMessage(editMsg *ChatMessage, message, raw, roomKey string, roomID RoomID, fromUserID UserID, toUserID *UserID, upload *Upload, groupID *GroupID, hellbanMsg, modMsg, systemMsg bool) (int64, error) 55 CreateRoom(name string, passwordHash string, ownerID UserID, isListed bool) (out ChatRoom, err error) 56 CreateSecurityLog(userID UserID, typ int64) 57 CreateSession(userID UserID, userAgent string, sessionDuration time.Duration) (Session, error) 58 CreateSessionNotification(msg string, sessionToken string) 59 CreateSnippet(userID UserID, name, text string) (out Snippet, err error) 60 CreateSysMsg(raw, txt, roomKey string, roomID RoomID, userID UserID) error 61 CreateUnkickMsg(kickedUser, kickedByUser User) 62 CreateUpload(fileName string, content []byte, userID UserID) (*Upload, error) 63 CreateUser(username, password, repassword string, registrationDuration int64, signupInfoEnc string) (User, UserErrors) 64 CreateUserBadge(userID UserID, badgeID int64) error 65 CreateXmrInvoice(userID UserID, productID int64) (out XmrInvoice, err error) 66 DeWhitelistUser(roomID RoomID, userID UserID) (err error) 67 DeleteAllChatInbox(userID UserID) error 68 DeleteAllNotifications(userID UserID) error 69 DeleteAllSessionNotifications(sessionToken string) error 70 DeleteAllUserUnusedInvitations(userID UserID) (err error) 71 DeleteChatInboxMessageByChatMessageID(chatMessageID int64) error 72 DeleteChatInboxMessageByID(messageID int64) error 73 DeleteChatMessageByUUID(messageUUID string) error 74 DeleteChatRoomByID(id RoomID) 75 DeleteChatRoomGroup(roomID RoomID, name string) (err error) 76 DeleteChatRoomGroups(roomID RoomID) (err error) 77 DeleteChatRoomMessages(roomID RoomID) error 78 DeleteDownloadByID(downloadID int64) (err error) 79 DeleteForumMessageByID(messageID ForumMessageID) error 80 DeleteForumThreadByID(threadID ForumThreadID) error 81 DeleteLinkByID(id int64) error 82 DeleteLinkCategories(linkID int64) error 83 DeleteLinkMirrorByID(id int64) error 84 DeleteLinkPgpByID(id int64) error 85 DeleteLinkTags(linkID int64) error 86 DeleteNotificationByID(notificationID int64) error 87 DeleteOldAuditLogs() 88 DeleteOldCaptchaRequests() 89 DeleteOldChatMessages() 90 DeleteOldPrivateChatRooms() 91 DeleteOldSecurityLogs() 92 DeleteOldSessions() 93 DeleteOldUploads() 94 DeleteReaction(userID UserID, messageID, reaction int64) error 95 DeleteSessionByToken(token string) error 96 DeleteSessionNotificationByID(sessionNotificationID int64) error 97 DeleteSnippet(userID UserID, name string) 98 DeleteUserByID(userID UserID) (err error) 99 DeleteUserChatInboxMessages(userID UserID) error 100 DeleteUserChatMessages(userID UserID) error 101 DeleteUserOtherSessions(userID UserID, currentToken string) error 102 DeleteUserSessionByToken(userID UserID, token string) error 103 DeleteUserSessions(userID UserID) error 104 DoCreateSession(userID UserID, userAgent string, sessionDuration time.Duration) Session 105 GetActiveUserSessions(userID UserID) (out []Session) 106 GetCategories() (out []CategoriesResult, err error) 107 GetChatMessages(roomID RoomID, roomKey string, username Username, userID UserID, pmUserID *UserID, displayPms PmDisplayMode, mentionsOnly, displayHellbanned, displayIgnored, displayModerators, displayIgnoredMessages bool, msgsLimit, minID1 int64) (out ChatMessages, err error) 108 GetChatRoomByID(roomID RoomID) (out ChatRoom, err error) 109 GetChatRoomByName(roomName string) (out ChatRoom, err error) 110 GetChessSubscribers() (out []User, err error) 111 GetClubForumThreads(userID UserID) (out []ForumThreadAug, err error) 112 GetClubMembers() (out []User, err error) 113 GetFiledropByFileName(fileName string) (out Filedrop, err error) 114 GetFiledropByUUID(uuid string) (out Filedrop, err error) 115 GetFiledrops() (out []Filedrop, err error) 116 GetForumCategories() (out []ForumCategory, err error) 117 GetForumCategoryBySlug(slug string) (out ForumCategory, err error) 118 GetForumMessage(messageID ForumMessageID) (out ForumMessage, err error) 119 GetForumMessageByUUID(messageUUID ForumMessageUUID) (out ForumMessage, err error) 120 GetForumThread(threadID ForumThreadID) (out ForumThread, err error) 121 GetForumThreadByID(threadID ForumThreadID) (out ForumThread, err error) 122 GetForumThreadByUUID(threadUUID ForumThreadUUID) (out ForumThread, err error) 123 GetForumThreads() (out []ForumThread, err error) 124 GetGistByUUID(uuid string) (out Gist, err error) 125 GetIgnoredByUsers(userID UserID) (out []IgnoredUser, err error) 126 GetIgnoredUsers(userID UserID) (out []IgnoredUser, err error) 127 GetLinkByID(linkID int64) (out Link, err error) 128 GetLinkByShorthand(shorthand string) (out Link, err error) 129 GetLinkByUUID(linkUUID string) (out Link, err error) 130 GetLinkCategories(linkID int64) (out []LinksCategory, err error) 131 GetLinkMirrorByID(id int64) (out LinksMirror, err error) 132 GetLinkMirrors(linkID int64) (out []LinksMirror, err error) 133 GetLinkPgpByID(id int64) (out LinksPgp, err error) 134 GetLinkPgps(linkID int64) (out []LinksPgp, err error) 135 GetLinkTags(linkID int64) (out []LinksTag, err error) 136 GetLinks() (out []Link, err error) 137 GetListedChatRooms(userID UserID) (out []ChatRoomAug, err error) 138 GetMemeByFileName(filename string) (out Meme, err error) 139 GetMemeByID(memeID MemeID) (out Meme, err error) 140 GetMemeBySlug(slug string) (out Meme, err error) 141 GetMemes() (out []Meme, err error) 142 GetModeratorsUsers() (out []User, err error) 143 GetOfficialChatRooms() (out []ChatRoom, err error) 144 GetOfficialChatRooms1(userID UserID) (out []ChatRoomAug1, err error) 145 GetOnionBlacklist(hash string) (out OnionBlacklist, err error) 146 GetPmBlacklistedByUsers(userID UserID) (out []PmBlacklistedUsers, err error) 147 GetPmBlacklistedUsers(userID UserID) (out []PmBlacklistedUsers, err error) 148 GetPmWhitelistedUsers(userID UserID) (out []PmWhitelistedUsers, err error) 149 GetPublicForumCategoryThreads(userID UserID, categoryID ForumCategoryID) (out []ForumThreadAug, err error) 150 GetPublicForumThreadsSearch(userID UserID) (out []ForumThreadAug, err error) 151 GetRecentLinks() (out []Link, err error) 152 GetRecentUsersCount() int64 153 GetRoomChatMessageByDate(roomID RoomID, userID UserID, dt time.Time) (out ChatMessage, err error) 154 GetRoomChatMessageByUUID(roomID RoomID, msgUUID string) (out ChatMessage, err error) 155 GetRoomChatMessages(roomID RoomID) (out ChatMessages, err error) 156 GetRoomChatMessagesByDate(roomID RoomID, dt time.Time) (out []ChatMessage, err error) 157 GetRoomGroupByName(roomID RoomID, groupName string) (out ChatRoomGroup, err error) 158 GetRoomGroupUsers(roomID RoomID, groupID GroupID) (out []ChatRoomUserGroup, err error) 159 GetRoomGroups(roomID RoomID) (out []ChatRoomGroup, err error) 160 GetSecurityLogs(userID UserID) (out []SecurityLog, err error) 161 GetSettings() (out Settings) 162 GetThreadMessages(threadID ForumThreadID) (out []ForumMessage, err error) 163 GetUnusedInvitationByToken(token string) (out Invitation, err error) 164 GetUploadByFileName(filename string) (out Upload, err error) 165 GetUploadByID(uploadID UploadID) (out Upload, err error) 166 GetUploads() (out []Upload, err error) 167 GetUserByApiKey(user *User, apiKey string) error 168 GetUserByID(userID UserID) (out User, err error) 169 GetUserBySessionKey(user *User, sessionKey string) error 170 GetUserByUsername(username Username) (out User, err error) 171 GetUserChatInboxMessages(userID UserID) (msgs []ChatInboxMessage, err error) 172 GetUserChatInboxMessagesSent(userID UserID) (msgs []ChatInboxMessage, err error) 173 GetUserInboxMessagesCount(userID UserID) (count int64) 174 GetUserInvitations(userID UserID) (out []Invitation, err error) 175 GetUserLastChatMessageInRoom(userID UserID, roomID RoomID) (out ChatMessage, err error) 176 GetUserNotifications(userID UserID) (msgs []Notification, err error) 177 GetUserNotificationsCount(userID UserID) (count int64) 178 GetUserPrivateNotes(userID UserID) (out UserPrivateNote, err error) 179 GetUserPublicNotes(userID UserID) (out UserPublicNote, err error) 180 GetUserReadMarker(userID UserID, roomID RoomID) (out ChatReadMarker, err error) 181 GetUserRoomGroups(userID UserID, roomID RoomID) (out []ChatRoomUserGroup, err error) 182 GetUserRoomSubscriptions(userID UserID) (out []ChatRoomAug1, err error) 183 GetUserSessionNotifications(sessionToken string) (msgs []SessionNotification, err error) 184 GetUserSessionNotificationsCount(sessionToken string) (count int64) 185 GetUserSnippets(userID UserID) (out []Snippet, err error) 186 GetUserTotalUploadSize(userID UserID) int64 187 GetUserUnusedInvitations(userID UserID) (out []Invitation, err error) 188 GetUserUploads(userID UserID) (out []Upload, err error) 189 GetUsersBadges() (out []UserBadge, err error) 190 GetUsersByID(ids []UserID) (out []User, err error) 191 GetUsersByUsername(usernames []string) (out []User, err error) 192 GetUsersSubscribedToForumThread(threadID ForumThreadID) (out []UserForumThreadSubscription, err error) 193 GetVerifiedUserBySessionID(token string) (out User, err error) 194 GetVerifiedUserByUsername(username Username) (out User, err error) 195 GetWhitelistedUsers(roomID RoomID) (out []ChatRoomWhitelistedUser, err error) 196 GetXmrInvoiceByAddress(address string) (out XmrInvoice, err error) 197 IgnoreMessage(userID UserID, messageID int64) 198 IgnoreUser(userID, ignoredUserID UserID) 199 IsPasswordProhibited(password string) bool 200 IsUserInGroupByID(userID UserID, groupID GroupID) bool 201 IsUserPmBlacklisted(fromUserID, toUserID UserID) bool 202 IsUserPmWhitelisted(fromUserID, toUserID UserID) bool 203 IsUserSubscribedToForumThread(userID UserID, threadID ForumThreadID) bool 204 IsUserSubscribedToRoom(userID UserID, roomID RoomID) bool 205 IsUserWhitelistedInRoom(userID UserID, roomID RoomID) bool 206 IsUsernameAlreadyTaken(username Username) bool 207 NewAudit(authUser User, log string) 208 RmBlacklistedUser(userID, blacklistedUserID UserID) 209 RmUserFromRoomGroup(roomID RoomID, groupID GroupID, userID UserID) (err error) 210 RmWhitelistedUser(userID, whitelistedUserID UserID) 211 SetUserPrivateNotes(userID UserID, notes string) error 212 SetUserPublicNotes(userID UserID, notes string) error 213 SubscribeToForumThread(userID UserID, threadID ForumThreadID) (err error) 214 SubscribeToRoom(userID UserID, roomID RoomID) (err error) 215 ToggleBlacklistedUser(userID, blacklistedUserID UserID) bool 216 ToggleWhitelistedUser(userID, whitelistedUserID UserID) bool 217 UnIgnoreMessage(userID UserID, messageID int64) 218 UnIgnoreUser(userID, ignoredUserID UserID) 219 UnsubscribeFromForumThread(userID UserID, threadID ForumThreadID) (err error) 220 UnsubscribeFromRoom(userID UserID, roomID RoomID) (err error) 221 UpdateChatReadMarker(userID UserID, roomID RoomID) 222 UpdateChatReadRecord(userID UserID, roomID RoomID) 223 UpdateForumReadRecord(userID UserID, threadID ForumThreadID) 224 UserNbDownloaded(userID UserID, filename string) (out int64) 225 WhitelistUser(roomID RoomID, userID UserID) (out ChatRoomWhitelistedUser, err error) 226 } 227 228 func NewDkfDB(dbPath string) *DkfDB { 229 conf := &gorm.Config{} 230 if config.Development.IsTrue() { 231 conf.Logger = logger.Default.LogMode(logger.Silent) 232 } 233 234 db, err := gorm.Open(sqlite.Open(dbPath), conf) 235 if err != nil { 236 logrus.Fatal("Failed to open sqlite3 db : " + err.Error()) 237 } 238 utils.Must(db.DB()).SetMaxIdleConns(1) // 10 239 utils.Must(db.DB()).SetMaxOpenConns(1) // 25 240 //db.LogMode(false) 241 db.Exec("PRAGMA foreign_keys=ON") 242 return &DkfDB{db: db} 243 } 244 245 // DB2 is the SQL database. 246 type DB2 struct { 247 path string // Path to database file. 248 dsnQuery string // DSN query params, if any. 249 memory bool // In-memory only. 250 fqdsn string // Fully-qualified DSN for opening SQLite. 251 } 252 253 // Conn represents a connection to a database. Two Connection objects 254 // to the same database are READ_COMMITTED isolated. 255 type Conn struct { 256 sqlite *sqlite3.SQLiteConn 257 } 258 259 // Connect returns a connection to the database. 260 func (d *DB2) Connect() (*Conn, error) { 261 drv := sqlite3.SQLiteDriver{} 262 c, err := drv.Open(d.fqdsn) 263 if err != nil { 264 return nil, err 265 } 266 267 return &Conn{ 268 sqlite: c.(*sqlite3.SQLiteConn), 269 }, nil 270 } 271 272 // New returns an instance of the database at path. If the database 273 // has already been created and opened, this database will share 274 // the data of that database when connected. 275 func New(path, dsnQuery string, memory bool) (*DB2, error) { 276 q, err := url.ParseQuery(dsnQuery) 277 if err != nil { 278 return nil, err 279 } 280 if memory { 281 q.Set("mode", "memory") 282 q.Set("cache", "shared") 283 } 284 285 if !strings.HasPrefix(path, "file:") { 286 path = fmt.Sprintf("file:%s", path) 287 } 288 289 var fqdsn string 290 if len(q) > 0 { 291 fqdsn = fmt.Sprintf("%s?%s", path, q.Encode()) 292 } else { 293 fqdsn = path 294 } 295 296 return &DB2{ 297 path: path, 298 dsnQuery: dsnQuery, 299 memory: memory, 300 fqdsn: fqdsn, 301 }, nil 302 } 303 304 const bkDelay = 250 305 306 // Backup the database 307 func Backup() error { 308 projectPath := config.Global.ProjectPath.Get() 309 dbPath := filepath.Join(projectPath, config.DbFileName) 310 bckPath := filepath.Join(projectPath, "backup.db") 311 srcDB, err := New(dbPath, "", false) 312 if err != nil { 313 return err 314 } 315 srcConn, err := srcDB.Connect() 316 if err != nil { 317 return err 318 } 319 320 dstDB, err := New(bckPath, "", false) 321 if err != nil { 322 return err 323 } 324 dstConn, err := dstDB.Connect() 325 if err != nil { 326 return err 327 } 328 329 bk, err := dstConn.sqlite.Backup("main", srcConn.sqlite, "main") 330 if err != nil { 331 return err 332 } 333 334 for { 335 done, err := bk.Step(-1) 336 if err != nil { 337 _ = bk.Finish() 338 return err 339 } 340 if done { 341 break 342 } 343 time.Sleep(bkDelay * time.Millisecond) 344 } 345 346 return bk.Finish() 347 } 348 349 func (d *DkfDB) With(clb func(tx *DkfDB)) { 350 _ = d.WithE(func(tx *DkfDB) error { 351 clb(tx) 352 return nil 353 }) 354 } 355 356 func (d *DkfDB) WithE(clb func(tx *DkfDB) error) error { 357 tx := d.Begin() 358 err := clb(tx) 359 if err != nil { 360 tx.Rollback() 361 } else { 362 tx.Commit() 363 } 364 return err 365 } 366 367 func (d *DkfDB) Begin() *DkfDB { 368 return &DkfDB{db: d.db.Begin()} 369 } 370 371 func (d *DkfDB) Commit() *DkfDB { 372 return &DkfDB{db: d.db.Commit()} 373 } 374 375 func (d *DkfDB) Rollback() *DkfDB { 376 return &DkfDB{db: d.db.Rollback()} 377 }