Derive pages and browsers from hits

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 {