Fixes for hit filtering

1 files changed, 63 insertions, 41 deletions

diff --git a/store/sqlite3.go b/store/sqlite3.go
index 77490c1..be39e7f 100644
--- a/store/sqlite3.go
+++ b/store/sqlite3.go
@@ -3,7 +3,6 @@ package store
 import (
 	"fmt"
 	"strings"
-	"time"
 
 	"github.com/jmoiron/sqlx"
 	"github.com/jmoiron/sqlx/reflectx"
@@ -61,24 +60,18 @@ func (s *Sqlite3) SaveSite(d *sws.Site) error {
 	return nil
 }
 
-func (s *Sqlite3) GetHits(d sws.Site, begin, end time.Time, f map[string]interface{}) ([]*sws.Hit, error) {
+func (s *Sqlite3) GetHits(d sws.Site, filter map[string]interface{}) ([]*sws.Hit, error) {
 	pvs := make([]*sws.Hit, 0)
 
-	filter := map[string]interface{}{
-		"begin": begin,
-		"end":   end,
-	}
-
-	sql := stmts["filterHits"]
-	for k, v := range f {
-		filter[k] = v
-		sql += fmt.Sprintf("%s = :%s", k, k)
-	}
+	sql := stmts["hits"]
+	filter["site_id"] = *d.ID
+	processFilter(&sql, filter)
 
 	rows, err := s.db.NamedQuery(sql, filter)
 	if err != nil {
 		return nil, err
 	}
+	defer rows.Close()
 
 	for rows.Next() {
 		pv := &sws.Hit{}
@@ -102,20 +95,22 @@ func (s *Sqlite3) SaveHit(h *sws.Hit) error {
 	return nil
 }
 
-func (s *Sqlite3) GetPages(d sws.Site, begin, end time.Time) ([]*sws.Page, error) {
+func (s *Sqlite3) GetPages(d sws.Site, filter map[string]interface{}) ([]*sws.Page, error) {
 	pages := make([]*sws.Page, 0)
 
-	filter := map[string]interface{}{
-		// "begin":   begin,
-		// "end":     end,
-		"site_id": *d.ID,
-	}
-
 	sql := stmts["pages"]
-	// for k, v := range f {
-	// 	filter[k] = v
-	// 	sql += fmt.Sprintf("%s = :%s", k, k)
-	// }
+	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 {
@@ -132,37 +127,64 @@ func (s *Sqlite3) GetPages(d sws.Site, begin, end time.Time) ([]*sws.Page, error
 	return pages, nil
 }
 
+func processFilter(sql *string, filter map[string]interface{}) {
+	if sql == nil {
+		panic("empty sql")
+	}
+	for k, _ := range filter {
+		*sql += " and"
+		switch k {
+		case "begin":
+			*sql += fmt.Sprintf(" created_at > :%s", k)
+		case "end":
+			*sql += fmt.Sprintf(" created_at < :%s", k)
+		default:
+			*sql += fmt.Sprintf(" %s = :%s", k, k)
+		}
+	}
+}
+
 var stmts = map[string]string{
 	"sites": `select id, name, description, aliases, enabled,
-created_at, updated_at from sites`,
+created_at, updated_at
+from sites`,
 
 	"siteByName": `select id, name, description, aliases, enabled,
-created_at, updated_at from sites where name = $1 limit 1`,
+created_at, updated_at
+from sites
+where name = $1 limit 1`,
 
 	"siteByID": `select id, name, description, aliases, enabled,
-created_at, updated_at from sites where id = $1 limit 1`,
+created_at, updated_at
+from sites
+where id = $1 limit 1`,
 
 	"saveSite": `insert into sites (
-name, description, aliases, enabled, created_at, updated_at) values (:name,
-:description, :aliases, :enabled, :created_at, :updated_at)`,
+name, description, aliases, enabled, created_at, updated_at)
+values (:name, :description, :aliases, :enabled, :created_at, :updated_at)`,
 
 	"userAgentByHash": `select id, hash, name, last_seen_at from sites
 where hash = $1 limit 1`,
 
-	"saveUserAgent": `insert into user_agents (hash, name, last_seen_at)
-values (:hash, :name, :last_seen_at) on conflict(hash) do update set
-last_seen_at = :last_seen_at`,
+	"saveUserAgent": `insert into user_agents
+(hash, name, last_seen_at)
+values (:hash, :name, :last_seen_at)
+on conflict(hash) do update set last_seen_at = :last_seen_at`,
 
 	"saveHit": `insert into hits (
 site_id, addr, scheme, host, path, query, title, referrer, user_agent_hash,
-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 id, site_id, path, max(created_at) from
-hits group by site_id, path) select h.id, h.site_id, h.path,
-h.created_at as last_visited_at from hits h, latest l where l.id = h.id`,
-
-	"filterHits": `select site_id, addr, scheme, host, path, title,
-referrer, user_agent_hash, view_port, no_script, created_at from hits where created_at > :begin
-and created_at < :end`,
+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`,
 }