From ae5d1898037be4f59bf6517ad76b13cc16f595ce Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Thu, 22 Oct 2015 18:04:06 -0700 Subject: Adding analytics tab --- store/sql_channel_store.go | 28 +++++++++ store/sql_channel_store_test.go | 18 ++++++ store/sql_post_store.go | 102 ++++++++++++++++++++++++++++++++ store/sql_post_store_test.go | 128 ++++++++++++++++++++++++++++++++++++++++ store/store.go | 4 ++ 5 files changed, 280 insertions(+) (limited to 'store') diff --git a/store/sql_channel_store.go b/store/sql_channel_store.go index 56e190fee..4b30f646e 100644 --- a/store/sql_channel_store.go +++ b/store/sql_channel_store.go @@ -744,3 +744,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 b4e0f7593..3bfafff4b 100644 --- a/store/sql_channel_store_test.go +++ b/store/sql_channel_store_test.go @@ -460,6 +460,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 6971de9d7..19a4e0adb 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -571,3 +571,105 @@ func (s SqlPostStore) GetForExport(channelId string) StoreChannel { return storeChannel } + +func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChannel { + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + var rows model.AnalyticsRows + _, err := s.GetReplica().Select( + &rows, + `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`, + map[string]interface{}{"TeamId": teamId}) + 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{} + + var rows model.AnalyticsRows + _, err := s.GetReplica().Select( + &rows, + `SELECT + DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, + COUNT(Posts.Id) AS Value + FROM + Posts, + Channels + WHERE + Posts.ChannelId = Channels.Id + AND Channels.TeamId = :TeamId + GROUP BY Name + ORDER BY Name DESC + LIMIT 30`, + map[string]interface{}{"TeamId": teamId}) + 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..4e165d58a 100644 --- a/store/sql_post_store_test.go +++ b/store/sql_post_store_test.go @@ -580,3 +580,131 @@ 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) + 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().AnalyticsPostCountsByDay(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 != 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 27731cee1..e52368290 100644 --- a/store/store.go +++ b/store/store.go @@ -74,6 +74,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 { @@ -86,6 +87,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 { -- cgit v1.2.3-1-g7c22 From 663bec814767fa9c92e7ab2c706c0fe4c0432cf1 Mon Sep 17 00:00:00 2001 From: hmhealey Date: Mon, 26 Oct 2015 11:45:03 -0400 Subject: Moved logic for searching for posts by multiple users/channels into the sql query --- store/sql_post_store.go | 52 +++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 42 insertions(+), 10 deletions(-) (limited to 'store') diff --git a/store/sql_post_store.go b/store/sql_post_store.go index 6971de9d7..ea913ab6a 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -6,6 +6,7 @@ package store import ( "fmt" "regexp" + "strconv" "strings" "github.com/mattermost/platform/model" @@ -413,10 +414,15 @@ func (s SqlPostStore) Search(teamId string, userId string, params *model.SearchP go func() { result := StoreResult{} + queryParams := map[string]interface{}{ + "TeamId": teamId, + "UserId": userId, + } + termMap := map[string]bool{} terms := params.Terms - if terms == "" && params.InChannel == "" && params.FromUser == "" { + if terms == "" && len(params.InChannels) == 0 && len(params.FromUsers) == 0 { result.Data = []*model.Post{} storeChannel <- result return @@ -468,13 +474,45 @@ func (s SqlPostStore) Search(teamId string, userId string, params *model.SearchP ORDER BY CreateAt DESC LIMIT 100` - if params.InChannel != "" { + if len(params.InChannels) > 1 { + inClause := ":InChannel0" + queryParams["InChannel0"] = params.InChannels[0] + + for i := 1; i < len(params.InChannels); i++ { + paramName := "InChannel" + strconv.FormatInt(int64(i), 10) + inClause += ", :" + paramName + queryParams[paramName] = params.InChannels[i] + } + + searchQuery = strings.Replace(searchQuery, "CHANNEL_FILTER", "AND Name IN ("+inClause+")", 1) + } else if len(params.InChannels) == 1 { + queryParams["InChannel"] = params.InChannels[0] searchQuery = strings.Replace(searchQuery, "CHANNEL_FILTER", "AND Name = :InChannel", 1) } else { searchQuery = strings.Replace(searchQuery, "CHANNEL_FILTER", "", 1) } - if params.FromUser != "" { + if len(params.FromUsers) > 1 { + inClause := ":FromUser0" + queryParams["FromUser0"] = params.FromUsers[0] + + for i := 1; i < len(params.FromUsers); i++ { + paramName := "FromUser" + strconv.FormatInt(int64(i), 10) + inClause += ", :" + paramName + queryParams[paramName] = params.FromUsers[i] + } + + searchQuery = strings.Replace(searchQuery, "POST_FILTER", ` + AND UserId IN ( + SELECT + Id + FROM + Users + WHERE + TeamId = :TeamId + AND Username IN (`+inClause+`))`, 1) + } else if len(params.FromUsers) == 1 { + queryParams["FromUser"] = params.FromUsers[0] searchQuery = strings.Replace(searchQuery, "POST_FILTER", ` AND UserId IN ( SELECT @@ -506,13 +544,7 @@ func (s SqlPostStore) Search(teamId string, userId string, params *model.SearchP searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", searchClause, 1) } - queryParams := map[string]interface{}{ - "TeamId": teamId, - "UserId": userId, - "Terms": terms, - "InChannel": params.InChannel, - "FromUser": params.FromUser, - } + queryParams["Terms"] = terms _, err := s.GetReplica().Select(&posts, searchQuery, queryParams) if err != nil { -- cgit v1.2.3-1-g7c22 From e750a8fd361ef6dfce557530a10aaf5ce5a7f37e Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Mon, 26 Oct 2015 10:26:59 -0700 Subject: Fixing unit test --- store/sql_post_store_test.go | 43 +++++++++++++++++++++++++------------------ 1 file changed, 25 insertions(+), 18 deletions(-) (limited to 'store') diff --git a/store/sql_post_store_test.go b/store/sql_post_store_test.go index 4e165d58a..6795c0663 100644 --- a/store/sql_post_store_test.go +++ b/store/sql_post_store_test.go @@ -672,38 +672,45 @@ func TestPostCountsByDay(t *testing.T) { 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) + // 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) 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") - } + // 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 { + // if r1.Data.(int64) != 4 { + // t.Fatal("wrong value") + // } + + if r1.Data.(int64) != 2 { t.Fatal("wrong value") } } -- cgit v1.2.3-1-g7c22 From 05b16f40dffe870f0a119942b5d46b8a8f4c0753 Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Mon, 26 Oct 2015 11:00:34 -0700 Subject: PLT-828 fixing db upgrade code --- store/sql_channel_store.go | 3 +-- store/sql_store.go | 13 +++++++++---- store/sql_team_store.go | 2 +- store/sql_user_store.go | 2 +- 4 files changed, 12 insertions(+), 8 deletions(-) (limited to 'store') diff --git a/store/sql_channel_store.go b/store/sql_channel_store.go index 8bedf0632..07e8e0151 100644 --- a/store/sql_channel_store.go +++ b/store/sql_channel_store.go @@ -40,7 +40,7 @@ func NewSqlChannelStore(sqlStore *SqlStore) ChannelStore { func (s SqlChannelStore) UpgradeSchemaIfNeeded() { - // BEGIN REMOVE AFTER 1.1.0 + // REMOVE AFTER 1.2 SHIP see PLT-828 if s.CreateColumnIfNotExists("ChannelMembers", "NotifyProps", "varchar(2000)", "varchar(2000)", "{}") { // populate NotifyProps from existing NotifyLevel field @@ -83,7 +83,6 @@ func (s SqlChannelStore) UpgradeSchemaIfNeeded() { s.RemoveColumnIfExists("ChannelMembers", "NotifyLevel") } - // END REMOVE AFTER 1.1.0 } func (s SqlChannelStore) CreateIndexesIfNotExists() { diff --git a/store/sql_store.go b/store/sql_store.go index 0d1bfe41b..d5c84d522 100644 --- a/store/sql_store.go +++ b/store/sql_store.go @@ -73,7 +73,8 @@ func NewSqlStore() Store { } schemaVersion := sqlStore.GetCurrentSchemaVersion() - isSchemaVersion07 := false + isSchemaVersion07 := false // REMOVE AFTER 1.2 SHIP see PLT-828 + isSchemaVersion10 := false // REMOVE AFTER 1.2 SHIP see PLT-828 // If the version is already set then we are potentially in an 'upgrade needed' state if schemaVersion != "" { @@ -86,7 +87,11 @@ func NewSqlStore() Store { isSchemaVersion07 = true } - if model.IsPreviousVersion(schemaVersion) || isSchemaVersion07 { + if schemaVersion == "1.0.0" { + isSchemaVersion10 = true + } + + if model.IsPreviousVersion(schemaVersion) || isSchemaVersion07 || isSchemaVersion10 { l4g.Warn("The database schema version of " + schemaVersion + " appears to be out of date") l4g.Warn("Attempting to upgrade the database schema version to " + model.CurrentVersion) } else { @@ -98,7 +103,7 @@ func NewSqlStore() Store { } } - // REMOVE in 1.2 + // REMOVE AFTER 1.2 SHIP see PLT-828 if sqlStore.DoesTableExist("Sessions") { if sqlStore.DoesColumnExist("Sessions", "AltId") { sqlStore.GetMaster().Exec("DROP TABLE IF EXISTS Sessions") @@ -140,7 +145,7 @@ func NewSqlStore() Store { sqlStore.webhook.(*SqlWebhookStore).CreateIndexesIfNotExists() sqlStore.preference.(*SqlPreferenceStore).CreateIndexesIfNotExists() - if model.IsPreviousVersion(schemaVersion) || isSchemaVersion07 { + if model.IsPreviousVersion(schemaVersion) || isSchemaVersion07 || isSchemaVersion10 { sqlStore.system.Update(&model.System{Name: "Version", Value: model.CurrentVersion}) l4g.Warn("The database schema has been upgraded to version " + model.CurrentVersion) } diff --git a/store/sql_team_store.go b/store/sql_team_store.go index 380d979bd..8700a9d04 100644 --- a/store/sql_team_store.go +++ b/store/sql_team_store.go @@ -29,7 +29,7 @@ func NewSqlTeamStore(sqlStore *SqlStore) TeamStore { } func (s SqlTeamStore) UpgradeSchemaIfNeeded() { - // REMOVE in 1.2 + // REMOVE AFTER 1.2 SHIP see PLT-828 s.RemoveColumnIfExists("Teams", "AllowValet") } diff --git a/store/sql_user_store.go b/store/sql_user_store.go index 5fab38ace..d825cda57 100644 --- a/store/sql_user_store.go +++ b/store/sql_user_store.go @@ -41,7 +41,7 @@ func NewSqlUserStore(sqlStore *SqlStore) UserStore { } func (us SqlUserStore) UpgradeSchemaIfNeeded() { - // REMOVE in 1.2 + // REMOVE AFTER 1.2 SHIP see PLT-828 us.CreateColumnIfNotExists("Users", "ThemeProps", "varchar(2000)", "character varying(2000)", "{}") } -- cgit v1.2.3-1-g7c22 From d53de8421421f4251cc4cff2118814246548d687 Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Mon, 26 Oct 2015 22:46:19 -0700 Subject: Fixing postgres --- store/sql_post_store.go | 57 ++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 49 insertions(+), 8 deletions(-) (limited to 'store') diff --git a/store/sql_post_store.go b/store/sql_post_store.go index d1f308b5a..f21bbee7a 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -610,9 +610,7 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan go func() { result := StoreResult{} - var rows model.AnalyticsRows - _, err := s.GetReplica().Select( - &rows, + query := `SELECT t1.Name, COUNT(t1.UserId) AS Value FROM @@ -627,7 +625,31 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan ORDER BY Name DESC) AS t1 GROUP BY Name ORDER BY Name DESC - LIMIT 30`, + LIMIT 30` + + if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + query = + `SELECT + t1.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}) if err != nil { result.Err = model.NewAppError("SqlPostStore.AnalyticsUserCountsWithPostsByDay", "We couldn't get user counts with posts", err.Error()) @@ -648,9 +670,7 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { go func() { result := StoreResult{} - var rows model.AnalyticsRows - _, err := s.GetReplica().Select( - &rows, + query := `SELECT DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, COUNT(Posts.Id) AS Value @@ -662,7 +682,28 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { AND Channels.TeamId = :TeamId GROUP BY Name ORDER BY Name DESC - LIMIT 30`, + LIMIT 30` + + if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + query = + `SELECT + DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name, + COUNT(Posts.Id) AS Value + FROM + Posts, + Channels + WHERE + Posts.ChannelId = Channels.Id + AND Channels.TeamId = :TeamId + GROUP BY Name + ORDER BY Name DESC + LIMIT 30` + } + + var rows model.AnalyticsRows + _, err := s.GetReplica().Select( + &rows, + query, map[string]interface{}{"TeamId": teamId}) if err != nil { result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCountsByDay", "We couldn't get post counts by day", err.Error()) -- cgit v1.2.3-1-g7c22 From 399e9c6f4bbed7f9eac0a75242ec75e4b0d2bb59 Mon Sep 17 00:00:00 2001 From: =Corey Hulen Date: Tue, 27 Oct 2015 09:55:19 -0700 Subject: PLT-25 fixing stats for postgres --- store/sql_post_store.go | 40 +++++++++++++++++++++++----------------- store/sql_post_store_test.go | 41 +++++++++++++++++++---------------------- 2 files changed, 42 insertions(+), 39 deletions(-) (limited to 'store') diff --git a/store/sql_post_store.go b/store/sql_post_store.go index f21bbee7a..7894ff488 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -630,7 +630,7 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { query = `SELECT - t1.Name, COUNT(t1.UserId) AS Value + 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, @@ -650,7 +650,7 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan _, err := s.GetReplica().Select( &rows, query, - map[string]interface{}{"TeamId": teamId}) + 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 { @@ -672,14 +672,17 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { query := `SELECT - DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, - COUNT(Posts.Id) AS Value + Name, COUNT(Value) AS Value FROM - Posts, - Channels - WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId + (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` @@ -687,14 +690,17 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { query = `SELECT - DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name, - COUNT(Posts.Id) AS Value + Name, COUNT(Value) AS Value FROM - Posts, - Channels - WHERE - Posts.ChannelId = Channels.Id - AND Channels.TeamId = :TeamId + (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` @@ -704,7 +710,7 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { _, err := s.GetReplica().Select( &rows, query, - map[string]interface{}{"TeamId": teamId}) + 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 { diff --git a/store/sql_post_store_test.go b/store/sql_post_store_test.go index 6795c0663..872423c5a 100644 --- a/store/sql_post_store_test.go +++ b/store/sql_post_store_test.go @@ -672,21 +672,22 @@ func TestPostCountsByDay(t *testing.T) { 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) + 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) @@ -697,20 +698,16 @@ func TestPostCountsByDay(t *testing.T) { t.Fatal("wrong value") } - // row2 := r1.Data.(model.AnalyticsRows)[1] - // if row2.Value != 2 { - // 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") - // } - - if r1.Data.(int64) != 2 { + if r1.Data.(int64) != 4 { t.Fatal("wrong value") } } -- cgit v1.2.3-1-g7c22