- commit
- 815cc66
- parent
- b2996f0
- author
- Eric Bower
- date
- 2024-03-29 13:32:34 +0000 UTC
chore(ui): more analytics API endpoints
4 files changed,
+89,
-45
+2,
-1
1@@ -18,11 +18,12 @@ func main() {
2 userID := args[1]
3
4 stats, err := dbpool.VisitSummary(
5- &db.SummarOpts{
6+ &db.SummaryOpts{
7 FkID: userID,
8 By: "user_id",
9 Interval: "day",
10 Origin: shared.StartOfMonth(),
11+ Where: "AND post_id IS NOT NULL OR (post_id IS NULL AND project_id IS NULL)",
12 },
13 )
14 if err != nil {
M
db/db.go
+3,
-2
1@@ -145,11 +145,12 @@ type Analytics struct {
2 UsersWithPost int
3 }
4
5-type SummarOpts struct {
6+type SummaryOpts struct {
7 FkID string
8 By string
9 Interval string
10 Origin time.Time
11+ Where string
12 }
13
14 type PostAnalytics struct {
15@@ -368,7 +369,7 @@ type DB interface {
16 ReplaceAliasesForPost(aliases []string, postID string) error
17
18 InsertVisit(view *AnalyticsVisits) error
19- VisitSummary(opts *SummarOpts) (*SummaryVisits, error)
20+ VisitSummary(opts *SummaryOpts) (*SummaryVisits, error)
21
22 AddPicoPlusUser(username string, paymentType, txId string) error
23 FindFeatureForUser(userID string, feature string) (*FeatureFlag, error)
+62,
-36
1@@ -1024,18 +1024,50 @@ func (me *PsqlDB) InsertVisit(view *db.AnalyticsVisits) error {
2 return err
3 }
4
5-func (me *PsqlDB) visitUnique(fkID, by, interval string, origin time.Time) ([]*db.VisitInterval, error) {
6+func (me *PsqlDB) visitUniqueBlog(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
7+ uniqueVisitors := fmt.Sprintf(`SELECT
8+ date_trunc('%s', created_at) as interval_start,
9+ count(DISTINCT ip_address) as unique_visitors
10+ FROM analytics_visits
11+ WHERE %s=$1 AND created_at >= $2 %s
12+ GROUP BY interval_start`, opts.Interval, opts.By, opts.Where)
13+
14+ intervals := []*db.VisitInterval{}
15+ rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
16+ if err != nil {
17+ return nil, err
18+ }
19+
20+ for rs.Next() {
21+ interval := &db.VisitInterval{}
22+ err := rs.Scan(
23+ &interval.Interval,
24+ &interval.Visitors,
25+ )
26+ if err != nil {
27+ return nil, err
28+ }
29+
30+ intervals = append(intervals, interval)
31+ }
32+ if rs.Err() != nil {
33+ return nil, rs.Err()
34+ }
35+ return intervals, nil
36+}
37+
38+func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
39 uniqueVisitors := fmt.Sprintf(`SELECT
40 post_id,
41 project_id,
42 date_trunc('%s', created_at) as interval_start,
43 count(DISTINCT ip_address) as unique_visitors
44 FROM analytics_visits
45- WHERE %s=$1 AND created_at >= $2
46- GROUP BY post_id, project_id, interval_start`, interval, by)
47+ WHERE %s=$1 AND created_at >= $2 %s
48+ GROUP BY post_id, project_id, interval_start`, opts.Interval, opts.By, opts.Where)
49
50 intervals := []*db.VisitInterval{}
51- rs, err := me.Db.Query(uniqueVisitors, fkID, origin)
52+ rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
53 if err != nil {
54 return nil, err
55 }
56@@ -1064,38 +1096,30 @@ func (me *PsqlDB) visitUnique(fkID, by, interval string, origin time.Time) ([]*d
57 return intervals, nil
58 }
59
60-func (me *PsqlDB) visitReferer(fkID, by string, origin time.Time) ([]*db.VisitUrl, error) {
61+func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
62 topUrls := fmt.Sprintf(`SELECT
63 referer,
64- post_id,
65- project_id,
66 count(*) as referer_count
67 FROM analytics_visits
68- WHERE %s=$1 AND created_at >= $2
69- GROUP BY referer, post_id, project_id
70- LIMIT 10`, by)
71+ WHERE %s=$1 AND created_at >= $2 AND referer <> '' %s
72+ GROUP BY referer
73+ LIMIT 10`, opts.By, opts.Where)
74
75 intervals := []*db.VisitUrl{}
76- rs, err := me.Db.Query(topUrls, fkID, origin)
77+ rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
78 if err != nil {
79 return nil, err
80 }
81
82 for rs.Next() {
83 interval := &db.VisitUrl{}
84- var postID sql.NullString
85- var projectID sql.NullString
86 err := rs.Scan(
87 &interval.Url,
88- &postID,
89- &projectID,
90 &interval.Count,
91 )
92 if err != nil {
93 return nil, err
94 }
95- interval.PostID = postID.String
96- interval.ProjectID = projectID.String
97
98 intervals = append(intervals, interval)
99 }
100@@ -1105,38 +1129,30 @@ func (me *PsqlDB) visitReferer(fkID, by string, origin time.Time) ([]*db.VisitUr
101 return intervals, nil
102 }
103
104-func (me *PsqlDB) visitUrl(fkID, by string, origin time.Time) ([]*db.VisitUrl, error) {
105+func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
106 topUrls := fmt.Sprintf(`SELECT
107 path,
108- post_id,
109- project_id,
110 count(*) as path_count
111 FROM analytics_visits
112- WHERE %s=$1 AND created_at >= $2
113- GROUP BY path, post_id, project_id
114- LIMIT 10`, by)
115+ WHERE %s=$1 AND created_at >= $2 AND path <> '' %s
116+ GROUP BY path
117+ LIMIT 10`, opts.By, opts.Where)
118
119 intervals := []*db.VisitUrl{}
120- rs, err := me.Db.Query(topUrls, fkID, origin)
121+ rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
122 if err != nil {
123 return nil, err
124 }
125
126 for rs.Next() {
127 interval := &db.VisitUrl{}
128- var postID sql.NullString
129- var projectID sql.NullString
130 err := rs.Scan(
131 &interval.Url,
132- &postID,
133- &projectID,
134 &interval.Count,
135 )
136 if err != nil {
137 return nil, err
138 }
139- interval.PostID = postID.String
140- interval.ProjectID = projectID.String
141
142 intervals = append(intervals, interval)
143 }
144@@ -1146,16 +1162,26 @@ func (me *PsqlDB) visitUrl(fkID, by string, origin time.Time) ([]*db.VisitUrl, e
145 return intervals, nil
146 }
147
148-func (me *PsqlDB) VisitSummary(opts *db.SummarOpts) (*db.SummaryVisits, error) {
149- visitors, err := me.visitUnique(opts.FkID, opts.By, opts.Interval, opts.Origin)
150- if err != nil {
151- return nil, err
152+func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error) {
153+ var visitors []*db.VisitInterval
154+ var err error
155+ if opts.Where == "" {
156+ visitors, err = me.visitUnique(opts)
157+ if err != nil {
158+ return nil, err
159+ }
160+ } else {
161+ visitors, err = me.visitUniqueBlog(opts)
162+ if err != nil {
163+ return nil, err
164+ }
165 }
166- urls, err := me.visitUrl(opts.FkID, opts.By, opts.Origin)
167+
168+ urls, err := me.visitUrl(opts)
169 if err != nil {
170 return nil, err
171 }
172- refs, err := me.visitReferer(opts.FkID, opts.By, opts.Origin)
173+ refs, err := me.visitReferer(opts)
174 if err != nil {
175 return nil, err
176 }
+22,
-6
1@@ -521,7 +521,7 @@ func getProjectObjects(apiConfig *shared.ApiConfig, ctx ssh.Context) http.Handle
2 }
3 }
4
5-func getAnalytics(apiConfig *shared.ApiConfig, ctx ssh.Context, sumtype string) http.HandlerFunc {
6+func getAnalytics(apiConfig *shared.ApiConfig, ctx ssh.Context, sumtype, bytype, where string) http.HandlerFunc {
7 logger := apiConfig.Cfg.Logger
8 dbpool := apiConfig.Dbpool
9 return func(w http.ResponseWriter, r *http.Request) {
10@@ -531,8 +531,18 @@ func getAnalytics(apiConfig *shared.ApiConfig, ctx ssh.Context, sumtype string)
11 return
12 }
13
14- year := &db.SummarOpts{FkID: user.ID, By: "user_id", Interval: "month", Origin: shared.StartOfYear()}
15- month := &db.SummarOpts{FkID: user.ID, By: "user_id", Interval: "day", Origin: shared.StartOfMonth()}
16+ fkID := user.ID
17+ by := "user_id"
18+ if bytype == "project" {
19+ fkID = shared.GetField(r, 0)
20+ by = "project_id"
21+ } else if bytype == "post" {
22+ fkID = shared.GetField(r, 0)
23+ by = "post_id"
24+ }
25+
26+ year := &db.SummaryOpts{FkID: fkID, By: by, Interval: "month", Origin: shared.StartOfYear(), Where: where}
27+ month := &db.SummaryOpts{FkID: fkID, By: by, Interval: "day", Origin: shared.StartOfMonth(), Where: where}
28
29 opts := year
30 if sumtype == "month" {
31@@ -578,12 +588,18 @@ func CreateRoutes(apiConfig *shared.ApiConfig, ctx ssh.Context) []shared.Route {
32 shared.NewCorsRoute("GET", "/api/tokens", getTokens(apiConfig, ctx)),
33 shared.NewCorsRoute("POST", "/api/tokens", createToken(apiConfig, ctx)),
34 shared.NewCorsRoute("DELETE", "/api/tokens/(.+)", deleteToken(apiConfig, ctx)),
35- shared.NewCorsRoute("GET", "/api/projects", getProjects(apiConfig, ctx)),
36+ shared.NewCorsRoute("GET", "/api/projects/(.+)/analytics", getAnalytics(apiConfig, ctx, "month", "project", "")),
37+ shared.NewCorsRoute("GET", "/api/projects/(.+)/analytics/year", getAnalytics(apiConfig, ctx, "year", "project", "")),
38 shared.NewCorsRoute("GET", "/api/projects/(.+)", getProjectObjects(apiConfig, ctx)),
39+ shared.NewCorsRoute("GET", "/api/projects", getProjects(apiConfig, ctx)),
40+ shared.NewCorsRoute("GET", "/api/posts/analytics/year", getAnalytics(apiConfig, ctx, "year", "user", "AND post_id IS NOT NULL OR (post_id IS NULL AND project_id IS NULL)")),
41+ shared.NewCorsRoute("GET", "/api/posts/analytics", getAnalytics(apiConfig, ctx, "month", "user", "AND post_id IS NOT NULL OR (post_id IS NULL AND project_id IS NULL)")),
42+ shared.NewCorsRoute("GET", "/api/posts/(.+)/analytics", getAnalytics(apiConfig, ctx, "month", "post", "")),
43+ shared.NewCorsRoute("GET", "/api/posts/(.+)/analytics/year", getAnalytics(apiConfig, ctx, "year", "post", "")),
44 shared.NewCorsRoute("GET", "/api/posts/prose", getPosts(apiConfig, ctx, "prose")),
45 shared.NewCorsRoute("GET", "/api/posts/pastes", getPosts(apiConfig, ctx, "pastes")),
46 shared.NewCorsRoute("GET", "/api/posts/feeds", getPosts(apiConfig, ctx, "feeds")),
47- shared.NewCorsRoute("GET", "/api/analytics/year", getAnalytics(apiConfig, ctx, "year")),
48- shared.NewCorsRoute("GET", "/api/analytics", getAnalytics(apiConfig, ctx, "month")),
49+ shared.NewCorsRoute("GET", "/api/analytics/year", getAnalytics(apiConfig, ctx, "year", "user", "")),
50+ shared.NewCorsRoute("GET", "/api/analytics", getAnalytics(apiConfig, ctx, "month", "user", "")),
51 }
52 }