// Wrapper for querying a Database struct over a time interval
func Series(db *sql.DB, interval int, timeRange *database.TimeRange) (*database.Intervals, error) {
// Query
queryBuilder := sq.
Select(fmt.Sprintf("(time / %d) * %d AS startTime", interval, interval), "COUNT(*)").
From("visits")
// Add time constraints if timeRange provided
if timeRange != nil {
if !timeRange.Start.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time >= %d", timeRange.Start.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
if !timeRange.End.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time <= %d", timeRange.End.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
}
// Set query Group By condition
query, _, err := queryBuilder.GroupBy("startTime").ToSql()
if err != nil {
return nil, err
}
// Exec query
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
// Format results
intervals := database.Intervals{}
for rows.Next() {
result := database.Interval{}
var startTime int
rows.Scan(&startTime, &result.Total)
// Format Start and End from TIMESTAMP to ISO time
result.Start = time.Unix(int64(startTime), 0).UTC().Format(time.RFC3339)
result.End = time.Unix(int64(startTime+interval), 0).UTC().Format(time.RFC3339)
intervals.List = append(intervals.List, result)
}
return &intervals, nil
}
func (driver *Sharded) Series(params database.Params) (*database.Intervals, error) {
// Construct DBPath
dbPath := manager.DBPath{
Name: params.DBName,
Directory: driver.directory,
}
// Check if DB file exists
dbExists, err := driver.DBManager.DBExists(dbPath)
if err != nil {
driver.DBManager.Logger.Error("Error executing Series/DBExists on DB %s: %v\n", dbPath, err)
return nil, &errors.InternalError
}
// DB doesn't exist
if !dbExists {
return nil, &errors.InvalidDatabaseName
}
// At this point, there should be shards to query
// Get list of shards by reading directory
shards := listShards(dbPath)
// Aggregated query result
analytics := database.Intervals{}
cachedRequest := cachedRequest(params.URL)
// Read from each shard
for _, shardName := range shards {
// Don't include shard if not in timerange
shardInt, err := shardNameToInt(shardName)
if err != nil {
return nil, err
}
startInt, endInt := timeRangeToInt(params.TimeRange)
if shardInt < startInt || shardInt > endInt {
continue
}
// Get result if is cached
var shardAnalytics *database.Intervals
cacheURL, err := formatURLForCache(params.URL, shardInt, startInt, endInt, params.TimeRange)
if err != nil {
return nil, err
}
cached, inCache := driver.cache.Get(cacheURL)
if inCache {
err = json.Unmarshal(cached, &shardAnalytics)
if err != nil {
driver.DBManager.Logger.Error("Error unmarshaling from cache: %v\n", err)
return nil, err
}
} else {
// Else query shard
// Construct each shard DBPath
shardPath := manager.DBPath{
Name: shardName,
Directory: dbPath.String(),
}
// Get DB shard from manager
db, err := driver.DBManager.Acquire(shardPath)
if err != nil {
driver.DBManager.Logger.Error("Error executing Series/Acquire on DB %s: %v\n", shardPath, err)
return nil, &errors.InternalError
}
defer driver.DBManager.Release(db)
// Check for unique query parameter to call function accordingly
if params.Unique {
shardAnalytics, err = query.SeriesUniq(db.DB, params.Interval, params.TimeRange)
if err != nil {
driver.DBManager.Logger.Error("Error executing SeriesUniq on DB %s: %v\n", shardPath, err)
return nil, &errors.InternalError
}
} else {
shardAnalytics, err = query.Series(db.DB, params.Interval, params.TimeRange)
if err != nil {
driver.DBManager.Logger.Error("Error executing Series on DB %s: %v\n", shardPath, err)
return nil, &errors.InternalError
}
}
// Set shard result in cache if asked
if cachedRequest {
if data, err := json.Marshal(shardAnalytics); err == nil {
err = driver.cache.Set(cacheURL, data)
if err != nil {
driver.DBManager.Logger.Error("Error adding to cache: %v\n", err)
}
}
}
}
// Add shard result to analyticsMap
for _, analytic := range shardAnalytics.List {
analytics.List = append(analytics.List, analytic)
}
}
// Merge time series by Start and End date
analytics.Merge()
return &analytics, nil
}