github.com/haalcala/mattermost-server-change-repo@v0.0.0-20210713015153-16753fbeee5f/store/sqlstore/compliance_store.go (about) 1 // Copyright (c) 2015-present Mattermost, Inc. All Rights Reserved. 2 // See LICENSE.txt for license information. 3 4 package sqlstore 5 6 import ( 7 "strconv" 8 "strings" 9 10 "github.com/pkg/errors" 11 12 "github.com/mattermost/mattermost-server/v5/model" 13 "github.com/mattermost/mattermost-server/v5/store" 14 ) 15 16 type SqlComplianceStore struct { 17 *SqlStore 18 } 19 20 func newSqlComplianceStore(sqlStore *SqlStore) store.ComplianceStore { 21 s := &SqlComplianceStore{sqlStore} 22 23 for _, db := range sqlStore.GetAllConns() { 24 table := db.AddTableWithName(model.Compliance{}, "Compliances").SetKeys(false, "Id") 25 table.ColMap("Id").SetMaxSize(26) 26 table.ColMap("UserId").SetMaxSize(26) 27 table.ColMap("Status").SetMaxSize(64) 28 table.ColMap("Desc").SetMaxSize(512) 29 table.ColMap("Type").SetMaxSize(64) 30 table.ColMap("Keywords").SetMaxSize(512) 31 table.ColMap("Emails").SetMaxSize(1024) 32 } 33 34 return s 35 } 36 37 func (s SqlComplianceStore) createIndexesIfNotExists() { 38 } 39 40 func (s SqlComplianceStore) Save(compliance *model.Compliance) (*model.Compliance, error) { 41 compliance.PreSave() 42 if err := compliance.IsValid(); err != nil { 43 return nil, err 44 } 45 46 if err := s.GetMaster().Insert(compliance); err != nil { 47 return nil, errors.Wrap(err, "failed to save Compliance") 48 } 49 return compliance, nil 50 } 51 52 func (s SqlComplianceStore) Update(compliance *model.Compliance) (*model.Compliance, error) { 53 if err := compliance.IsValid(); err != nil { 54 return nil, err 55 } 56 57 if _, err := s.GetMaster().Update(compliance); err != nil { 58 return nil, errors.Wrap(err, "failed to update Compliance") 59 } 60 return compliance, nil 61 } 62 63 func (s SqlComplianceStore) GetAll(offset, limit int) (model.Compliances, error) { 64 query := "SELECT * FROM Compliances ORDER BY CreateAt DESC LIMIT :Limit OFFSET :Offset" 65 66 var compliances model.Compliances 67 if _, err := s.GetReplica().Select(&compliances, query, map[string]interface{}{"Offset": offset, "Limit": limit}); err != nil { 68 return nil, errors.Wrap(err, "failed to find all Compliances") 69 } 70 return compliances, nil 71 } 72 73 func (s SqlComplianceStore) Get(id string) (*model.Compliance, error) { 74 obj, err := s.GetReplica().Get(model.Compliance{}, id) 75 if err != nil { 76 return nil, errors.Wrapf(err, "failed to get Compliance with id=%s", id) 77 } 78 if obj == nil { 79 return nil, store.NewErrNotFound("Compliance", id) 80 } 81 return obj.(*model.Compliance), nil 82 } 83 84 func (s SqlComplianceStore) ComplianceExport(job *model.Compliance) ([]*model.CompliancePost, error) { 85 props := map[string]interface{}{"StartTime": job.StartAt, "EndTime": job.EndAt} 86 87 keywordQuery := "" 88 keywords := strings.Fields(strings.TrimSpace(strings.ToLower(strings.Replace(job.Keywords, ",", " ", -1)))) 89 if len(keywords) > 0 { 90 91 keywordQuery = "AND (" 92 93 for index, keyword := range keywords { 94 keyword = sanitizeSearchTerm(keyword, "\\") 95 if index >= 1 { 96 keywordQuery += " OR LOWER(Posts.Message) LIKE :Keyword" + strconv.Itoa(index) 97 } else { 98 keywordQuery += "LOWER(Posts.Message) LIKE :Keyword" + strconv.Itoa(index) 99 } 100 101 props["Keyword"+strconv.Itoa(index)] = "%" + keyword + "%" 102 } 103 104 keywordQuery += ")" 105 } 106 107 emailQuery := "" 108 emails := strings.Fields(strings.TrimSpace(strings.ToLower(strings.Replace(job.Emails, ",", " ", -1)))) 109 if len(emails) > 0 { 110 111 emailQuery = "AND (" 112 113 for index, email := range emails { 114 if index >= 1 { 115 emailQuery += " OR Users.Email = :Email" + strconv.Itoa(index) 116 } else { 117 emailQuery += "Users.Email = :Email" + strconv.Itoa(index) 118 } 119 120 props["Email"+strconv.Itoa(index)] = email 121 } 122 123 emailQuery += ")" 124 } 125 126 query := 127 `(SELECT 128 Teams.Name AS TeamName, 129 Teams.DisplayName AS TeamDisplayName, 130 Channels.Name AS ChannelName, 131 Channels.DisplayName AS ChannelDisplayName, 132 Channels.Type AS ChannelType, 133 Users.Username AS UserUsername, 134 Users.Email AS UserEmail, 135 Users.Nickname AS UserNickname, 136 Posts.Id AS PostId, 137 Posts.CreateAt AS PostCreateAt, 138 Posts.UpdateAt AS PostUpdateAt, 139 Posts.DeleteAt AS PostDeleteAt, 140 Posts.RootId AS PostRootId, 141 Posts.ParentId AS PostParentId, 142 Posts.OriginalId AS PostOriginalId, 143 Posts.Message AS PostMessage, 144 Posts.Type AS PostType, 145 Posts.Props AS PostProps, 146 Posts.Hashtags AS PostHashtags, 147 Posts.FileIds AS PostFileIds, 148 Bots.UserId IS NOT NULL AS IsBot 149 FROM 150 Teams, 151 Channels, 152 Users, 153 Posts 154 LEFT JOIN Bots ON Bots.UserId = Posts.UserId 155 WHERE 156 Teams.Id = Channels.TeamId 157 AND Posts.ChannelId = Channels.Id 158 AND Posts.UserId = Users.Id 159 AND Posts.CreateAt > :StartTime 160 AND Posts.CreateAt <= :EndTime 161 ` + emailQuery + ` 162 ` + keywordQuery + `) 163 UNION ALL 164 (SELECT 165 'direct-messages' AS TeamName, 166 'Direct Messages' AS TeamDisplayName, 167 Channels.Name AS ChannelName, 168 Channels.DisplayName AS ChannelDisplayName, 169 Channels.Type AS ChannelType, 170 Users.Username AS UserUsername, 171 Users.Email AS UserEmail, 172 Users.Nickname AS UserNickname, 173 Posts.Id AS PostId, 174 Posts.CreateAt AS PostCreateAt, 175 Posts.UpdateAt AS PostUpdateAt, 176 Posts.DeleteAt AS PostDeleteAt, 177 Posts.RootId AS PostRootId, 178 Posts.ParentId AS PostParentId, 179 Posts.OriginalId AS PostOriginalId, 180 Posts.Message AS PostMessage, 181 Posts.Type AS PostType, 182 Posts.Props AS PostProps, 183 Posts.Hashtags AS PostHashtags, 184 Posts.FileIds AS PostFileIds, 185 Bots.UserId IS NOT NULL AS IsBot 186 FROM 187 Channels, 188 Users, 189 Posts 190 LEFT JOIN Bots ON Bots.UserId = Posts.UserId 191 WHERE 192 Channels.TeamId = '' 193 AND Posts.ChannelId = Channels.Id 194 AND Posts.UserId = Users.Id 195 AND Posts.CreateAt > :StartTime 196 AND Posts.CreateAt <= :EndTime 197 ` + emailQuery + ` 198 ` + keywordQuery + `) 199 ORDER BY PostCreateAt 200 LIMIT 30000` 201 202 var cposts []*model.CompliancePost 203 204 if _, err := s.GetReplica().Select(&cposts, query, props); err != nil { 205 return nil, errors.Wrap(err, "unable to export compliance") 206 } 207 return cposts, nil 208 } 209 210 func (s SqlComplianceStore) MessageExport(after int64, limit int) ([]*model.MessageExport, error) { 211 props := map[string]interface{}{"StartTime": after, "Limit": limit} 212 query := 213 `SELECT 214 Posts.Id AS PostId, 215 Posts.CreateAt AS PostCreateAt, 216 Posts.UpdateAt AS PostUpdateAt, 217 Posts.DeleteAt AS PostDeleteAt, 218 Posts.Message AS PostMessage, 219 Posts.Type AS PostType, 220 Posts.Props AS PostProps, 221 Posts.OriginalId AS PostOriginalId, 222 Posts.RootId AS PostRootId, 223 Posts.FileIds AS PostFileIds, 224 Teams.Id AS TeamId, 225 Teams.Name AS TeamName, 226 Teams.DisplayName AS TeamDisplayName, 227 Channels.Id AS ChannelId, 228 CASE 229 WHEN Channels.Type = 'D' THEN 'Direct Message' 230 WHEN Channels.Type = 'G' THEN 'Group Message' 231 ELSE Channels.DisplayName 232 END AS ChannelDisplayName, 233 Channels.Name AS ChannelName, 234 Channels.Type AS ChannelType, 235 Users.Id AS UserId, 236 Users.Email AS UserEmail, 237 Users.Username, 238 Bots.UserId IS NOT NULL AS IsBot 239 FROM 240 Posts 241 LEFT OUTER JOIN Channels ON Posts.ChannelId = Channels.Id 242 LEFT OUTER JOIN Teams ON Channels.TeamId = Teams.Id 243 LEFT OUTER JOIN Users ON Posts.UserId = Users.Id 244 LEFT JOIN Bots ON Bots.UserId = Posts.UserId 245 WHERE 246 Posts.UpdateAt > :StartTime AND 247 Posts.Type NOT LIKE 'system_%' 248 ORDER BY PostUpdateAt 249 LIMIT :Limit` 250 251 var cposts []*model.MessageExport 252 if _, err := s.GetReplica().Select(&cposts, query, props); err != nil { 253 return nil, errors.Wrap(err, "unable to export messages") 254 } 255 return cposts, nil 256 }