aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFelix Hanley <felix@userspace.com.au>2020-02-19 05:57:32 +0000
committerFelix Hanley <felix@userspace.com.au>2020-02-19 05:57:32 +0000
commitc664ab56a4726690ed233a0d1a98aefd1d6a5ac9 (patch)
tree770f4afe4d7e65bb821c7ee22ba01df9fa593420
parent1be2ae83ad4f6aaada4124373028f3ea730b1514 (diff)
downloadsws-c664ab56a4726690ed233a0d1a98aefd1d6a5ac9.tar.gz
sws-c664ab56a4726690ed233a0d1a98aefd1d6a5ac9.tar.bz2
Derive pages and browsers from hits
-rw-r--r--browser.go35
-rw-r--r--cmd/server/hits.go4
-rw-r--r--cmd/server/sites.go23
-rw-r--r--hit.go48
-rw-r--r--page.go29
-rw-r--r--site.go4
-rw-r--r--sql/sqlite3/02_hits.sql2
-rw-r--r--store.go1
-rw-r--r--store/sqlite3.go64
-rw-r--r--templates/site.tmpl18
-rw-r--r--user_agent.go2
11 files changed, 132 insertions, 98 deletions
diff --git a/browser.go b/browser.go
new file mode 100644
index 0000000..b843a8b
--- /dev/null
+++ b/browser.go
@@ -0,0 +1,35 @@
+package sws
+
+import (
+ "time"
+)
+
+type Browser struct {
+ Name string
+ UserAgent string
+ LastSeenAt time.Time
+ Engine string
+ Count int
+}
+
+func BrowsersFromHits(hits []*Hit) map[string]*Browser {
+ out := make(map[string]*Browser)
+ for _, h := range hits {
+ if h.UserAgentHash != nil {
+ b, ok := out[*h.UserAgentHash]
+ if !ok {
+ b = &Browser{
+ // TODO name
+ UserAgent: h.UserAgent.Name,
+ LastSeenAt: h.CreatedAt,
+ }
+ }
+ if b.LastSeenAt.Before(h.CreatedAt) {
+ b.LastSeenAt = h.CreatedAt
+ }
+ b.Count++
+ out[*h.UserAgentHash] = b
+ }
+ }
+ return out
+}
diff --git a/cmd/server/hits.go b/cmd/server/hits.go
index 822abde..992528b 100644
--- a/cmd/server/hits.go
+++ b/cmd/server/hits.go
@@ -31,14 +31,14 @@ func handleHitCounter(db sws.CounterStore) http.HandlerFunc {
return
}
- site, err := db.GetSiteByName(*hit.Host)
+ site, err := db.GetSiteByName(hit.Host)
if err != nil {
log("failed to get site", err)
http.Error(w, "invalid site", http.StatusNotFound)
return
}
hit.SiteID = site.ID
- hit.Addr = &r.RemoteAddr
+ hit.Addr = r.RemoteAddr
if err := db.SaveHit(hit); err != nil {
log("failed to save hit", err)
diff --git a/cmd/server/sites.go b/cmd/server/sites.go
index 1dc265e..7074670 100644
--- a/cmd/server/sites.go
+++ b/cmd/server/sites.go
@@ -42,11 +42,6 @@ func handleSite(db sws.SiteStore, rndr Renderer) http.HandlerFunc {
return
}
- pages, err := db.GetPages(*site, map[string]interface{}{"begin": *begin, "end": *end})
- if err != nil {
- log(err)
- }
-
hits, err := db.GetHits(*site, map[string]interface{}{
"begin": *begin,
"end": *end,
@@ -54,17 +49,23 @@ func handleSite(db sws.SiteStore, rndr Renderer) http.HandlerFunc {
if err != nil {
log(err)
}
+
+ pages := sws.PagesFromHits(hits)
+ browsers := sws.BrowsersFromHits(hits)
+
buckets := sws.HitsToTimeBuckets(hits, time.Hour)
buckets.Fill(begin, end)
payload := struct {
- Site *sws.Site
- Pages []*sws.Page
- Hits sws.TimeBuckets
+ Site *sws.Site
+ Pages map[string]*sws.Page
+ Browsers map[string]*sws.Browser
+ Hits sws.TimeBuckets
}{
- Site: site,
- Pages: pages,
- Hits: buckets,
+ Site: site,
+ Pages: pages,
+ Browsers: browsers,
+ Hits: buckets,
}
if err := rndr.Render(w, "site", payload); err != nil {
log(err)
diff --git a/hit.go b/hit.go
index 724b66b..c1993c5 100644
--- a/hit.go
+++ b/hit.go
@@ -10,32 +10,31 @@ import (
)
type Hit struct {
- ID *int `json:"id"`
- SiteID *int `json:"site_id,omitempty"`
- Addr *string `json:"addr,omitempty"`
+ ID *int `json:"id"`
+ SiteID *int `json:"site_id" db:"site_id"`
+ Addr string `json:"addr"`
// URL components
- Scheme *string `json:"scheme,omitempty"`
- Host *string `json:"host,omitempty"`
- Path *string `json:"path,omitempty"`
- Query *string `json:"query,omitempty"`
- Fragment *string `json:"fragment,omitempty"`
-
- Title *string `json:"title,omitempty"`
- Referrer *string `json:"referrer,omitempty"`
- UserAgentHash *string `json:"user_agent_hash,omitempty"`
- ViewPort *string `json:"view_port,omitempty"`
- NoScript bool `json:"no_script"`
+ Scheme string `json:"scheme"`
+ Host string `json:"host"`
+ Path string `json:"path"`
+ Query *string `json:"query,omitempty"`
+
+ Title *string `json:"title,omitempty"`
+ Referrer *string `json:"referrer,omitempty"`
+ UserAgentHash *string `json:"user_agent_hash,omitempty" db:"user_agent_hash"`
+ ViewPort *string `json:"view_port,omitempty" db:"view_port"`
+ NoScript bool `json:"no_script" db:"no_script"`
+ CreatedAt time.Time `json:"created_at" db:"created_at"`
//Features map[string]string `json:"features,omitempty"`
- CreatedAt *time.Time `json:"created_at,omitempty"`
// TODO
- Site *Site `json:"-"`
- UserAgent *UserAgent `json:"-"`
+ //Site *Site `db:"s"`
+ UserAgent *UserAgent `db:"ua"`
}
func (h Hit) String() string {
var out strings.Builder
- for _, sp := range []*string{h.Scheme, h.Host, h.Path, h.Query, h.Fragment} {
+ for _, sp := range []*string{&h.Scheme, &h.Host, &h.Path, h.Query} {
if sp != nil {
out.WriteString(*sp)
}
@@ -45,7 +44,8 @@ func (h Hit) String() string {
func HitFromRequest(r *http.Request) (*Hit, error) {
out := &Hit{
- CreatedAt: ptrTime(time.Now()),
+ CreatedAt: time.Now(),
+ Addr: r.RemoteAddr,
}
q := r.URL.Query()
@@ -70,7 +70,7 @@ func HitFromRequest(r *http.Request) (*Hit, error) {
if host == "" {
host = ref.Host
}
- out.Host = &host
+ out.Host = host
if h := r.Header.Get("HTTP_X_REQUESTED_WITH"); h == "" {
out.NoScript = true
@@ -78,16 +78,16 @@ func HitFromRequest(r *http.Request) (*Hit, error) {
scheme := q.Get("s")
if scheme != "" {
- out.Scheme = ptrString(strings.TrimSuffix(scheme, ":"))
+ out.Scheme = strings.TrimSuffix(scheme, ":")
} else {
- out.Scheme = &ref.Scheme
+ out.Scheme = ref.Scheme
}
path := q.Get("p")
if path != "" {
- out.Path = &path
+ out.Path = path
} else {
- out.Path = &ref.RawPath
+ out.Path = ref.RawPath
}
query := q.Get("q")
diff --git a/page.go b/page.go
index 566f893..e9ab785 100644
--- a/page.go
+++ b/page.go
@@ -5,12 +5,33 @@ import (
)
type Page struct {
- SiteID int `json:"site_id"`
- Path string `json:"path"`
- Title *string `json:"title,omitempty"`
-
+ SiteID int `json:"site_id"`
+ Path string `json:"path"`
+ Title *string `json:"title,omitempty"`
+ Count int `json:"count"`
LastVisitedAt time.Time `json:"last_visited_at"`
// TODO
Site *Site `json:"-"`
}
+
+func PagesFromHits(hits []*Hit) map[string]*Page {
+ out := make(map[string]*Page)
+ for _, h := range hits {
+ p, ok := out[h.Path]
+ if !ok {
+ p = &Page{
+ Path: h.Path,
+ SiteID: *h.SiteID,
+ Title: h.Title,
+ LastVisitedAt: h.CreatedAt,
+ }
+ }
+ if p.LastVisitedAt.Before(h.CreatedAt) {
+ p.LastVisitedAt = h.CreatedAt
+ }
+ p.Count++
+ out[h.Path] = p
+ }
+ return out
+}
diff --git a/site.go b/site.go
index e152ac2..892a6e5 100644
--- a/site.go
+++ b/site.go
@@ -14,8 +14,8 @@ type Site struct {
Aliases *string `json:"aliases,omitempty"`
Enabled bool `json:"enabled"`
//ExcludePaths []string
- CreatedAt *time.Time `json:"created_at,omitempty"`
- UpdatedAt *time.Time `json:"updated_at,omitempty"`
+ CreatedAt *time.Time `json:"created_at,omitempty" db:"created_at"`
+ UpdatedAt *time.Time `json:"updated_at,omitempty" db:"updated_at"`
}
func (d *Site) Validate() []error {
diff --git a/sql/sqlite3/02_hits.sql b/sql/sqlite3/02_hits.sql
index 7967c17..23cf60a 100644
--- a/sql/sqlite3/02_hits.sql
+++ b/sql/sqlite3/02_hits.sql
@@ -8,7 +8,7 @@ create table user_agents (
create table hits (
id integer primary key autoincrement,
- site_id integer check(site_id >0),
+ site_id integer not null,
addr varchar not null,
scheme varchar not null,
host varchar not null,
diff --git a/store.go b/store.go
index 555b52f..9b2ce4f 100644
--- a/store.go
+++ b/store.go
@@ -11,7 +11,6 @@ type SimpleSiteStore interface {
type SiteStore interface {
GetSites() ([]*Site, error)
GetSiteByID(int) (*Site, error)
- GetPages(Site, map[string]interface{}) ([]*Page, error)
GetHits(Site, map[string]interface{}) ([]*Hit, error)
//SaveSite(*Site) error
}
diff --git a/store/sqlite3.go b/store/sqlite3.go
index be39e7f..44e0da1 100644
--- a/store/sqlite3.go
+++ b/store/sqlite3.go
@@ -5,7 +5,7 @@ import (
"strings"
"github.com/jmoiron/sqlx"
- "github.com/jmoiron/sqlx/reflectx"
+ //"github.com/jmoiron/sqlx/reflectx"
"src.userspace.com.au/sws"
)
@@ -14,7 +14,7 @@ type Sqlite3 struct {
}
func NewSqlite3Store(db *sqlx.DB) *Sqlite3 {
- db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)
+ //db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)
return &Sqlite3{db}
}
@@ -61,7 +61,7 @@ func (s *Sqlite3) SaveSite(d *sws.Site) error {
}
func (s *Sqlite3) GetHits(d sws.Site, filter map[string]interface{}) ([]*sws.Hit, error) {
- pvs := make([]*sws.Hit, 0)
+ hits := make([]*sws.Hit, 0)
sql := stmts["hits"]
filter["site_id"] = *d.ID
@@ -72,15 +72,14 @@ func (s *Sqlite3) GetHits(d sws.Site, filter map[string]interface{}) ([]*sws.Hit
return nil, err
}
defer rows.Close()
-
for rows.Next() {
- pv := &sws.Hit{}
- if err := rows.StructScan(pv); err != nil {
- return pvs, err
+ h := &sws.Hit{}
+ if err := rows.StructScan(h); err != nil {
+ return hits, err
}
- pvs = append(pvs, pv)
+ hits = append(hits, h)
}
- return pvs, nil
+ return hits, nil
}
func (s *Sqlite3) SaveHit(h *sws.Hit) error {
@@ -95,38 +94,6 @@ func (s *Sqlite3) SaveHit(h *sws.Hit) error {
return nil
}
-func (s *Sqlite3) GetPages(d sws.Site, filter map[string]interface{}) ([]*sws.Page, error) {
- pages := make([]*sws.Page, 0)
-
- sql := stmts["pages"]
- filter["h.site_id"] = *d.ID
- for k, _ := range filter {
- sql += " and"
- switch k {
- case "begin":
- sql += fmt.Sprintf(" l.created_at > :%s", k)
- case "end":
- sql += fmt.Sprintf(" l.created_at < :%s", k)
- default:
- sql += fmt.Sprintf(" %s = :%s", k, k)
- }
- }
-
- rows, err := s.db.NamedQuery(sql, filter)
- if err != nil {
- return nil, err
- }
-
- for rows.Next() {
- p := &sws.Page{}
- if err := rows.StructScan(p); err != nil {
- return pages, err
- }
- pages = append(pages, p)
- }
- return pages, nil
-}
-
func processFilter(sql *string, filter map[string]interface{}) {
if sql == nil {
panic("empty sql")
@@ -177,14 +144,9 @@ view_port, no_script, created_at)
values (:site_id, :addr, :scheme, :host, :path, :query, :title, :referrer,
:user_agent_hash, :view_port, :no_script, :created_at)`,
- "pages": `with latest as (select site_id, path, max(created_at) as created_at
-from hits group by site_id, path)
-select h.site_id, h.path, h.created_at as last_visited_at
-from hits h, latest l
-where l.site_id = h.site_id and l.path = h.path and h.created_at = l.created_at`,
-
- "hits": `select site_id, addr, scheme, host, path, title,
-referrer, user_agent_hash, view_port, no_script, created_at
-from hits
-where site_id = :site_id`,
+ "hits": `select h.*,
+ua.hash as "ua.hash", ua.name as "ua.name", ua.last_seen_at as "ua.last_seen_at"
+from hits h
+join user_agents ua on h.user_agent_hash = ua.hash
+where h.site_id = :site_id`,
}
diff --git a/templates/site.tmpl b/templates/site.tmpl
index f52c9cd..6889690 100644
--- a/templates/site.tmpl
+++ b/templates/site.tmpl
@@ -15,6 +15,12 @@
{{ template "pageForList" . }}
{{ end }}
</ul>
+ <h2>Browsers</h2>
+ <ul class="browsers">
+ {{ range .Browsers }}
+ {{ template "browserForList" . }}
+ {{ end }}
+ </ul>
</main>
{{ end }}
@@ -22,6 +28,16 @@
<li>
<h4 class="path">{{ .Path }}</h4>
<span class="title">{{ .Title }}</span>
- <span class="last-visit">{{ .LastVisitedAt.Unix }}</span>
+ <span class="last-visit">{{ .LastVisitedAt|timeLong }}</span>
+ <span class="count">{{ .Count }}</span>
+</li>
+{{ end }}
+
+{{ define "browserForList" }}
+<li>
+ <h4 class="browser">{{ .Name }}</h4>
+ <span class="user-agent">{{ .UserAgent }}</span>
+ <span class="last-seen">{{ .LastSeenAt|timeLong }}</span>
+ <span class="count">{{ .Count }}</span>
</li>
{{ end }}
diff --git a/user_agent.go b/user_agent.go
index 19973b3..f65f45c 100644
--- a/user_agent.go
+++ b/user_agent.go
@@ -14,7 +14,7 @@ var botFromSiteRegexp = regexp.MustCompile("http[s]?://.+\\.\\w+")
type UserAgent struct {
Hash string `json:"hash"`
Name string `json:"name"`
- LastSeenAt time.Time `json:"last_seen_at"`
+ LastSeenAt time.Time `json:"last_seen_at" db:"last_seen_at"`
}
func (ua UserAgent) Bot() bool {