hub: prepare db schema update

3 files changed, 67 insertions, 14 deletions

diff --git a/src/hub/src/spreadspace.org/sfive/s5srvWeb.go b/src/hub/src/spreadspace.org/sfive/s5srvWeb.go
index 281a4fe..0aa1426 100644
--- a/src/hub/src/spreadspace.org/sfive/s5srvWeb.go
+++ b/src/hub/src/spreadspace.org/sfive/s5srvWeb.go
@@ -1,6 +1,7 @@
 package sfive
 
 import (
+	"encoding/json"
 	"fmt"
 	"net/http"
 	"strconv"
@@ -90,6 +91,21 @@ func (self StatsSinkServer) getUpdate(c web.C, w http.ResponseWriter, r *http.Re
 	}
 }
 
+func (self StatsSinkServer) getStats(c web.C, w http.ResponseWriter, r *http.Request) {
+	filter := getFilter(r)
+	stats, err := self.store.GetStats(&filter)
+	if err == nil {
+		js, err := json.Marshal(stats)
+		if err == nil {
+			fmt.Fprintf(w, "%v", js)
+		} else {
+			fmt.Fprintf(w, "Stats: Failed formatting stats: %v", err)
+		}
+	} else {
+		fmt.Fprintf(w, "Stats: Failed retrieving stats: %v", err)
+	}
+}
+
 func clientCount(c web.C, w http.ResponseWriter, r *http.Request) {
 }
 
@@ -100,5 +116,6 @@ func (self StatsSinkServer) ServeWeb() {
 	goji.Get("/sources/:id", self.getSource)
 	goji.Get("/updates", self.getUpdateList)
 	goji.Get("/updates/:id", self.getUpdate)
+	goji.Get("/stats", self.getStats)
 	goji.Serve()
 }
diff --git a/src/hub/src/spreadspace.org/sfive/s5store.go b/src/hub/src/spreadspace.org/sfive/s5store.go
index e50daf3..82ac2d1 100644
--- a/src/hub/src/spreadspace.org/sfive/s5store.go
+++ b/src/hub/src/spreadspace.org/sfive/s5store.go
@@ -53,9 +53,9 @@ type clientDataDb struct {
 // in DB, StatisticsData/DataUpdate is flattened compared to JSON DTOs
 type dataUpdateDb struct {
 	Id            int
-	SourceId      int // foreign key to sourcesTn
-	StartTime     time.Time
-	Duration      time.Duration
+	SourceId      int   // foreign key to sourcesTn
+	StartTime     int64 // time.Time
+	Duration      int64 // time.Duration
 	ClientCount   uint
 	BytesReceived uint
 	BytesSent     uint
@@ -98,8 +98,8 @@ func dataUpdateFromStatisticsData(value StatisticsData) dataUpdateDb {
 	return dataUpdateDb{
 		-1,
 		-1,
-		value.StartTime,
-		value.Duration,
+		value.StartTime.Unix(),
+		int64(value.Duration.Seconds()),
 		value.Data.ClientCount,
 		value.Data.BytesReceived,
 		value.Data.BytesSent}
@@ -176,13 +176,13 @@ func getFilteredDataUpdateSelect(filter *StatsFilter) (string, map[string]interf
 	if filter.start != nil {
 		query += insertAnd(&needsAnd)
 		query += " StartTime >= :filterstart"
-		parameters["filterstart"] = filter.start
+		parameters["filterstart"] = filter.start.Unix()
 		needsAnd = true
 	}
 	if filter.end != nil {
 		query += insertAnd(&needsAnd)
 		query += " StartTime < :filterend"
-		parameters["filterend"] = filter.end
+		parameters["filterend"] = filter.end.Unix()
 		needsAnd = true
 	}
 
@@ -381,21 +381,38 @@ func (s sqliteStore) GetUpdates(filter *StatsFilter) (res []dataUpdateDb, err er
 	return
 }
 
+func getCountEntriesSql() string {
+	return "count(*) as UpdateCount"
+}
+
+// TODO this is totally utterly wrong
+func getClientCountSql() string {
+	return "avg(ClientCount) as ClientCount"
+}
+
+func getBpsStatsSql() string {
+	return "sum(BytesSent) as BytesSent, sum(BytesReceived) as BytesReceived, min(StartTime) as StartTime, max(StartTime) as LastStartTime"
+}
+
 func (s sqliteStore) CountUpdateEntries(filter *StatsFilter) (count int64, err error) {
 	sourceSql, parameters := getFilteredDataUpdateSelect(filter)
-	count, err = s.db.SelectInt("select count(*) from "+sourceSql, parameters)
+	count, err = s.db.SelectInt("select "+getCountEntriesSql()+" from "+sourceSql, parameters)
 	return
 }
 
-func (s sqliteStore) CountClients(filter *StatsFilter) uint {
+func (s sqliteStore) CountClients(filter *StatsFilter) int {
 	sourceSql, parameters := getFilteredDataUpdateSelect(filter)
-	count, _ := s.db.SelectInt("select sum(ClientCount) from "+sourceSql, parameters)
-	return uint(count)
+	count, err := s.db.SelectInt("select "+getClientCountSql()+" from "+sourceSql, parameters)
+	if err != nil {
+		s5l.Printf("CountClients: %v", err)
+		return -1
+	}
+	return int(count)
 }
 
 type bpsQueryResult struct {
-	BytesReceived uint
 	BytesSent     uint
+	BytesReceived uint
 	StartTime     time.Time
 	LastStartTime time.Time
 }
@@ -405,7 +422,7 @@ func (s sqliteStore) GetAverageBps(filter *StatsFilter) (uint, error) {
 	res := bpsQueryResult{}
 	err := s.db.SelectOne(
 		res,
-		"select (sum(BytesSent) as BytesSent, sum(BytesReceived) as BytesReceived, min(StartTime) as StartTime, max(StartTime) as LastStartTime) from "+sourceSql, parameters)
+		"select "+getBpsStatsSql()+" from "+sourceSql, parameters)
 	if err == nil {
 		bps := (res.BytesSent + res.BytesReceived) / uint(res.StartTime.Sub(res.LastStartTime).Seconds())
 		return bps, nil
@@ -413,6 +430,25 @@ func (s sqliteStore) GetAverageBps(filter *StatsFilter) (uint, error) {
 	return 0, err
 }
 
+type statsResult struct {
+	UpdateCount   *int
+	ClientCount   *float32
+	BytesSent     *uint
+	BytesReceived *uint
+	StartTime     *int64
+	LastStartTime *int64
+}
+
+func (s sqliteStore) GetStats(filter *StatsFilter) (statsResult, error) { // (map[string]interface{}, error) {
+	sourceSql, parameters := getFilteredDataUpdateSelect(filter)
+	_ = sourceSql
+	sql := "select " + getCountEntriesSql() + "," + getClientCountSql() + "," + getBpsStatsSql() + " from " + sourceSql
+	s5l.Printf("stats sql: %v", sql)
+	res := statsResult{}
+	err := s.db.SelectOne(&res, sql, parameters)
+	return res, err
+}
+
 func NewStore(path string) (store sqliteStore, err error) {
 	db, err := initDb(path)
 	if err != nil {
diff --git a/src/hub/src/spreadspace.org/sfive/s5store_test.go b/src/hub/src/spreadspace.org/sfive/s5store_test.go
index 2bf862e..6b86acf 100644
--- a/src/hub/src/spreadspace.org/sfive/s5store_test.go
+++ b/src/hub/src/spreadspace.org/sfive/s5store_test.go
@@ -12,7 +12,7 @@ func TestGetFilter(t *testing.T) {
 	if fe != "(select * from DataUpdates where StartTime >= :filterstart)" {
 		t.Errorf("get filter sql failed: fe: %v", fe)
 	}
-	if !queryStartTime.Equal(*np["filterstart"].(*time.Time)) {
+	if queryStartTime.Unix() != np["filterstart"].(int64) {
 		t.Errorf("get filter sql failed: np: %v != %v", np["filterstart"], queryStartTime)
 	}
 }