From cc6bab5a785a7e0fd32dd4ca24d3dc78d0dd6866 Mon Sep 17 00:00:00 2001 From: Markus Grüneis Date: Tue, 21 Oct 2014 23:51:10 +0200 Subject: hub: prepare db schema update --- src/hub/src/spreadspace.org/sfive/s5srvWeb.go | 17 +++++++ src/hub/src/spreadspace.org/sfive/s5store.go | 62 ++++++++++++++++++----- src/hub/src/spreadspace.org/sfive/s5store_test.go | 2 +- 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) } } -- cgit v1.2.3