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  }