summaryrefslogtreecommitdiffstats
path: root/store
diff options
context:
space:
mode:
Diffstat (limited to 'store')
-rw-r--r--store/sql_channel_store.go28
-rw-r--r--store/sql_channel_store_test.go18
-rw-r--r--store/sql_post_store.go149
-rw-r--r--store/sql_post_store_test.go132
-rw-r--r--store/store.go4
5 files changed, 331 insertions, 0 deletions
diff --git a/store/sql_channel_store.go b/store/sql_channel_store.go
index 80586d21a..14896c0a0 100644
--- a/store/sql_channel_store.go
+++ b/store/sql_channel_store.go
@@ -834,3 +834,31 @@ func (s SqlChannelStore) GetForExport(teamId string) StoreChannel {
return storeChannel
}
+
+func (s SqlChannelStore) AnalyticsTypeCount(teamId string, channelType string) StoreChannel {
+ storeChannel := make(StoreChannel)
+
+ go func() {
+ result := StoreResult{}
+
+ v, err := s.GetReplica().SelectInt(
+ `SELECT
+ COUNT(Id) AS Value
+ FROM
+ Channels
+ WHERE
+ TeamId = :TeamId
+ AND Type = :ChannelType`,
+ map[string]interface{}{"TeamId": teamId, "ChannelType": channelType})
+ if err != nil {
+ result.Err = model.NewAppError("SqlChannelStore.AnalyticsTypeCount", "We couldn't get channel type counts", err.Error())
+ } else {
+ result.Data = v
+ }
+
+ storeChannel <- result
+ close(storeChannel)
+ }()
+
+ return storeChannel
+}
diff --git a/store/sql_channel_store_test.go b/store/sql_channel_store_test.go
index 60d3de56a..f6a0fb713 100644
--- a/store/sql_channel_store_test.go
+++ b/store/sql_channel_store_test.go
@@ -561,6 +561,24 @@ func TestChannelStoreGetMoreChannels(t *testing.T) {
if list.Channels[0].Name != o3.Name {
t.Fatal("missing channel")
}
+
+ if r1 := <-store.Channel().AnalyticsTypeCount(o1.TeamId, model.CHANNEL_OPEN); r1.Err != nil {
+ t.Fatal(r1.Err)
+ } else {
+ if r1.Data.(int64) != 2 {
+ t.Log(r1.Data)
+ t.Fatal("wrong value")
+ }
+ }
+
+ if r1 := <-store.Channel().AnalyticsTypeCount(o1.TeamId, model.CHANNEL_PRIVATE); r1.Err != nil {
+ t.Fatal(r1.Err)
+ } else {
+ if r1.Data.(int64) != 2 {
+ t.Log(r1.Data)
+ t.Fatal("wrong value")
+ }
+ }
}
func TestChannelStoreGetChannelCounts(t *testing.T) {
diff --git a/store/sql_post_store.go b/store/sql_post_store.go
index ea913ab6a..7894ff488 100644
--- a/store/sql_post_store.go
+++ b/store/sql_post_store.go
@@ -603,3 +603,152 @@ func (s SqlPostStore) GetForExport(channelId string) StoreChannel {
return storeChannel
}
+
+func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChannel {
+ storeChannel := make(StoreChannel)
+
+ go func() {
+ result := StoreResult{}
+
+ query :=
+ `SELECT
+ t1.Name, COUNT(t1.UserId) AS Value
+ FROM
+ (SELECT DISTINCT
+ DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
+ Posts.UserId
+ FROM
+ Posts, Channels
+ WHERE
+ Posts.ChannelId = Channels.Id
+ AND Channels.TeamId = :TeamId
+ ORDER BY Name DESC) AS t1
+ GROUP BY Name
+ ORDER BY Name DESC
+ LIMIT 30`
+
+ if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES {
+ query =
+ `SELECT
+ TO_CHAR(t1.Name, 'YYYY-MM-DD') AS Name, COUNT(t1.UserId) AS Value
+ FROM
+ (SELECT DISTINCT
+ DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name,
+ Posts.UserId
+ FROM
+ Posts, Channels
+ WHERE
+ Posts.ChannelId = Channels.Id
+ AND Channels.TeamId = :TeamId
+ ORDER BY Name DESC) AS t1
+ GROUP BY Name
+ ORDER BY Name DESC
+ LIMIT 30`
+ }
+
+ var rows model.AnalyticsRows
+ _, err := s.GetReplica().Select(
+ &rows,
+ query,
+ map[string]interface{}{"TeamId": teamId, "Time": model.GetMillis() - 1000*60*60*24*31})
+ if err != nil {
+ result.Err = model.NewAppError("SqlPostStore.AnalyticsUserCountsWithPostsByDay", "We couldn't get user counts with posts", err.Error())
+ } else {
+ result.Data = rows
+ }
+
+ storeChannel <- result
+ close(storeChannel)
+ }()
+
+ return storeChannel
+}
+
+func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel {
+ storeChannel := make(StoreChannel)
+
+ go func() {
+ result := StoreResult{}
+
+ query :=
+ `SELECT
+ Name, COUNT(Value) AS Value
+ FROM
+ (SELECT
+ DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
+ '1' AS Value
+ FROM
+ Posts, Channels
+ WHERE
+ Posts.ChannelId = Channels.Id
+ AND Channels.TeamId = :TeamId
+ AND Posts.CreateAt >:Time) AS t1
+ GROUP BY Name
+ ORDER BY Name DESC
+ LIMIT 30`
+
+ if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES {
+ query =
+ `SELECT
+ Name, COUNT(Value) AS Value
+ FROM
+ (SELECT
+ TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name,
+ '1' AS Value
+ FROM
+ Posts, Channels
+ WHERE
+ Posts.ChannelId = Channels.Id
+ AND Channels.TeamId = :TeamId
+ AND Posts.CreateAt > :Time) AS t1
+ GROUP BY Name
+ ORDER BY Name DESC
+ LIMIT 30`
+ }
+
+ var rows model.AnalyticsRows
+ _, err := s.GetReplica().Select(
+ &rows,
+ query,
+ map[string]interface{}{"TeamId": teamId, "Time": model.GetMillis() - 1000*60*60*24*31})
+ if err != nil {
+ result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCountsByDay", "We couldn't get post counts by day", err.Error())
+ } else {
+ result.Data = rows
+ }
+
+ storeChannel <- result
+ close(storeChannel)
+ }()
+
+ return storeChannel
+}
+
+func (s SqlPostStore) AnalyticsPostCount(teamId string) StoreChannel {
+ storeChannel := make(StoreChannel)
+
+ go func() {
+ result := StoreResult{}
+
+ v, err := s.GetReplica().SelectInt(
+ `SELECT
+ COUNT(Posts.Id) AS Value
+ FROM
+ Posts,
+ Channels
+ WHERE
+ Posts.ChannelId = Channels.Id
+ AND Channels.TeamId = :TeamId`,
+ map[string]interface{}{"TeamId": teamId})
+ if err != nil {
+ result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCount", "We couldn't get post counts", err.Error())
+ } else {
+ result.Data = v
+ }
+
+ storeChannel <- result
+ close(storeChannel)
+ }()
+
+ return storeChannel
+}
diff --git a/store/sql_post_store_test.go b/store/sql_post_store_test.go
index b2256417e..872423c5a 100644
--- a/store/sql_post_store_test.go
+++ b/store/sql_post_store_test.go
@@ -580,3 +580,135 @@ func TestPostStoreSearch(t *testing.T) {
t.Fatal("returned wrong search result")
}
}
+
+func TestUserCountsWithPostsByDay(t *testing.T) {
+ Setup()
+
+ t1 := &model.Team{}
+ t1.DisplayName = "DisplayName"
+ t1.Name = "a" + model.NewId() + "b"
+ t1.Email = model.NewId() + "@nowhere.com"
+ t1.Type = model.TEAM_OPEN
+ t1 = Must(store.Team().Save(t1)).(*model.Team)
+
+ c1 := &model.Channel{}
+ c1.TeamId = t1.Id
+ c1.DisplayName = "Channel2"
+ c1.Name = "a" + model.NewId() + "b"
+ c1.Type = model.CHANNEL_OPEN
+ c1 = Must(store.Channel().Save(c1)).(*model.Channel)
+
+ o1 := &model.Post{}
+ o1.ChannelId = c1.Id
+ o1.UserId = model.NewId()
+ o1.CreateAt = model.GetMillis()
+ o1.Message = "a" + model.NewId() + "b"
+ o1 = Must(store.Post().Save(o1)).(*model.Post)
+
+ o1a := &model.Post{}
+ o1a.ChannelId = c1.Id
+ o1a.UserId = model.NewId()
+ o1a.CreateAt = o1.CreateAt
+ o1a.Message = "a" + model.NewId() + "b"
+ o1a = Must(store.Post().Save(o1a)).(*model.Post)
+
+ o2 := &model.Post{}
+ o2.ChannelId = c1.Id
+ o2.UserId = model.NewId()
+ o2.CreateAt = o1.CreateAt - (1000 * 60 * 60 * 24)
+ o2.Message = "a" + model.NewId() + "b"
+ o2 = Must(store.Post().Save(o2)).(*model.Post)
+
+ o2a := &model.Post{}
+ o2a.ChannelId = c1.Id
+ o2a.UserId = o2.UserId
+ o2a.CreateAt = o1.CreateAt - (1000 * 60 * 60 * 24)
+ o2a.Message = "a" + model.NewId() + "b"
+ o2a = Must(store.Post().Save(o2a)).(*model.Post)
+
+ if r1 := <-store.Post().AnalyticsUserCountsWithPostsByDay(t1.Id); r1.Err != nil {
+ t.Fatal(r1.Err)
+ } else {
+ row1 := r1.Data.(model.AnalyticsRows)[0]
+ if row1.Value != 2 {
+ t.Fatal("wrong value")
+ }
+
+ row2 := r1.Data.(model.AnalyticsRows)[1]
+ if row2.Value != 1 {
+ t.Fatal("wrong value")
+ }
+ }
+}
+
+func TestPostCountsByDay(t *testing.T) {
+ Setup()
+
+ t1 := &model.Team{}
+ t1.DisplayName = "DisplayName"
+ t1.Name = "a" + model.NewId() + "b"
+ t1.Email = model.NewId() + "@nowhere.com"
+ t1.Type = model.TEAM_OPEN
+ t1 = Must(store.Team().Save(t1)).(*model.Team)
+
+ c1 := &model.Channel{}
+ c1.TeamId = t1.Id
+ c1.DisplayName = "Channel2"
+ c1.Name = "a" + model.NewId() + "b"
+ c1.Type = model.CHANNEL_OPEN
+ c1 = Must(store.Channel().Save(c1)).(*model.Channel)
+
+ o1 := &model.Post{}
+ o1.ChannelId = c1.Id
+ o1.UserId = model.NewId()
+ o1.CreateAt = model.GetMillis()
+ o1.Message = "a" + model.NewId() + "b"
+ o1 = Must(store.Post().Save(o1)).(*model.Post)
+
+ o1a := &model.Post{}
+ o1a.ChannelId = c1.Id
+ o1a.UserId = model.NewId()
+ o1a.CreateAt = o1.CreateAt
+ o1a.Message = "a" + model.NewId() + "b"
+ o1a = Must(store.Post().Save(o1a)).(*model.Post)
+
+ o2 := &model.Post{}
+ o2.ChannelId = c1.Id
+ o2.UserId = model.NewId()
+ o2.CreateAt = o1.CreateAt - (1000 * 60 * 60 * 24 * 2)
+ o2.Message = "a" + model.NewId() + "b"
+ o2 = Must(store.Post().Save(o2)).(*model.Post)
+
+ o2a := &model.Post{}
+ o2a.ChannelId = c1.Id
+ o2a.UserId = o2.UserId
+ o2a.CreateAt = o1.CreateAt - (1000 * 60 * 60 * 24 * 2)
+ o2a.Message = "a" + model.NewId() + "b"
+ o2a = Must(store.Post().Save(o2a)).(*model.Post)
+
+ time.Sleep(1 * time.Second)
+ t.Log(t1.Id)
+
+ if r1 := <-store.Post().AnalyticsPostCountsByDay(t1.Id); r1.Err != nil {
+ t.Fatal(r1.Err)
+ } else {
+ row1 := r1.Data.(model.AnalyticsRows)[0]
+ if row1.Value != 2 {
+ t.Log(row1)
+ t.Fatal("wrong value")
+ }
+
+ row2 := r1.Data.(model.AnalyticsRows)[1]
+ if row2.Value != 2 {
+ t.Fatal("wrong value")
+ }
+ }
+
+ if r1 := <-store.Post().AnalyticsPostCount(t1.Id); r1.Err != nil {
+ t.Fatal(r1.Err)
+ } else {
+ if r1.Data.(int64) != 4 {
+ t.Fatal("wrong value")
+ }
+ }
+}
diff --git a/store/store.go b/store/store.go
index bd2c3681e..42329b036 100644
--- a/store/store.go
+++ b/store/store.go
@@ -75,6 +75,7 @@ type ChannelStore interface {
CheckPermissionsToByName(teamId string, channelName string, userId string) StoreChannel
UpdateLastViewedAt(channelId string, userId string) StoreChannel
IncrementMentionCount(channelId string, userId string) StoreChannel
+ AnalyticsTypeCount(teamId string, channelType string) StoreChannel
}
type PostStore interface {
@@ -87,6 +88,9 @@ type PostStore interface {
GetEtag(channelId string) StoreChannel
Search(teamId string, userId string, params *model.SearchParams) StoreChannel
GetForExport(channelId string) StoreChannel
+ AnalyticsUserCountsWithPostsByDay(teamId string) StoreChannel
+ AnalyticsPostCountsByDay(teamId string) StoreChannel
+ AnalyticsPostCount(teamId string) StoreChannel
}
type UserStore interface {
generated by cgit v1.2.3-1-g7c22 (git 2.25.1) at 2024-06-28 09:29:54 +0000