2019-04-02 14:28:06 +00:00
package main
import (
"database/sql"
"fmt"
2019-04-03 06:21:25 +00:00
"github.com/gobuffalo/packr/v2"
2019-04-02 14:28:06 +00:00
_ "github.com/mattn/go-sqlite3"
2019-04-05 15:32:11 +00:00
"github.com/mssola/user_agent"
2019-04-05 14:47:04 +00:00
"github.com/rubenv/sql-migrate"
2019-04-05 15:08:05 +00:00
"net/url"
2019-04-02 14:28:06 +00:00
"os"
"path/filepath"
"strings"
2019-05-07 09:23:59 +00:00
"time"
2019-04-02 14:28:06 +00:00
)
type Database struct {
2019-05-18 09:13:02 +00:00
sqlDB * sql . DB
trackingStmt * sql . Stmt
2019-04-02 14:28:06 +00:00
}
2019-05-26 20:32:02 +00:00
var (
db = & Database { }
)
func initDatabase ( ) ( e error ) {
2019-05-01 09:46:52 +00:00
if _ , err := os . Stat ( appConfig . DbPath ) ; os . IsNotExist ( err ) {
_ = os . MkdirAll ( filepath . Dir ( appConfig . DbPath ) , os . ModePerm )
2019-04-02 14:28:06 +00:00
}
2019-05-26 20:32:02 +00:00
db . sqlDB , e = sql . Open ( "sqlite3" , appConfig . DbPath )
2019-04-02 14:28:06 +00:00
if e != nil {
return
}
2019-05-26 20:32:02 +00:00
e = migrateDatabase ( db . sqlDB )
2019-10-19 10:13:02 +00:00
db . trackingStmt , e = db . sqlDB . Prepare ( "insert into views(url, ref, useragent, bot, os) values(:url, :ref, :ua, :bot, :os)" )
2019-05-18 09:13:02 +00:00
if e != nil {
return
}
2019-04-02 14:28:06 +00:00
return
}
func migrateDatabase ( database * sql . DB ) ( e error ) {
2019-04-05 14:47:04 +00:00
migrations := & migrate . PackrMigrationSource {
2019-04-03 06:21:25 +00:00
Box : packr . New ( "migrations" , "migrations" ) ,
2019-04-02 14:28:06 +00:00
}
2019-04-05 14:47:04 +00:00
_ , e = migrate . Exec ( database , "sqlite3" , migrations , migrate . Up )
2019-04-02 14:28:06 +00:00
return
}
// Tracking
2019-05-26 20:32:02 +00:00
func trackView ( urlString string , ref string , ua string ) {
2019-04-05 15:08:05 +00:00
if len ( urlString ) == 0 {
// Don't track empty urls
2019-04-02 14:28:06 +00:00
return
}
2019-04-05 15:08:05 +00:00
if ref != "" {
// Clean referrer and just keep the hostname for more privacy
parsedRef , _ := url . Parse ( ref )
ref = parsedRef . Hostname ( )
}
2019-08-02 16:47:30 +00:00
bot := 0
2019-10-19 10:13:02 +00:00
osString := ""
2019-04-05 15:32:11 +00:00
if ua != "" {
// Parse Useragent
2019-08-02 16:47:30 +00:00
userAgent := user_agent . New ( ua )
if userAgent . Bot ( ) {
bot = 1
}
uaName , uaVersion := userAgent . Browser ( )
2019-04-05 15:32:11 +00:00
ua = uaName + " " + uaVersion
2019-10-19 10:13:02 +00:00
osInfo := userAgent . OSInfo ( )
if osInfo . FullName != "" {
osString = osInfo . FullName
}
2019-04-05 15:32:11 +00:00
}
2019-10-19 10:13:02 +00:00
_ , e := db . trackingStmt . Exec ( sql . Named ( "url" , urlString ) , sql . Named ( "ref" , ref ) , sql . Named ( "ua" , ua ) , sql . Named ( "bot" , bot ) , sql . Named ( "os" , osString ) )
2019-04-02 14:28:06 +00:00
if e != nil {
fmt . Println ( "Inserting into DB failed:" , e )
}
}
// Requesting
type View int
const (
PAGES View = iota + 1
REFERRERS
2019-04-08 19:41:16 +00:00
USERAGENTS
2019-04-09 12:31:15 +00:00
USERAGENTNAMES
2019-10-19 10:13:02 +00:00
OS
2019-04-02 14:28:06 +00:00
HOURS
DAYS
WEEKS
MONTHS
2019-04-15 07:56:41 +00:00
ALLHOURS
ALLDAYS
2019-05-26 06:31:30 +00:00
COUNT
2019-04-02 14:28:06 +00:00
)
type ViewsRequest struct {
2019-04-09 13:17:18 +00:00
view View
from string
2019-05-07 09:23:59 +00:00
fromRel string
2019-04-09 13:17:18 +00:00
to string
2019-05-07 09:23:59 +00:00
toRel string
2019-04-09 13:17:18 +00:00
url string
ref string
ua string
2019-05-01 10:17:37 +00:00
ordercol string
2019-04-09 13:17:18 +00:00
order string
2019-05-01 10:17:37 +00:00
limit string
2019-08-02 17:32:34 +00:00
bots string
2019-10-19 10:13:02 +00:00
os string
2019-04-02 14:28:06 +00:00
}
type RequestResultRow struct {
First string ` json:"first" `
Second int ` json:"second" `
}
2019-05-26 20:32:02 +00:00
func request ( request * ViewsRequest ) ( resultRows [ ] * RequestResultRow , e error ) {
2019-04-09 13:17:18 +00:00
statement , parameters := request . buildStatement ( )
2019-04-02 14:28:06 +00:00
namedArgs := make ( [ ] interface { } , len ( parameters ) )
for i , v := range parameters {
namedArgs [ i ] = v
}
rows , e := db . sqlDB . Query ( statement , namedArgs ... )
if e != nil {
return
2019-05-26 06:31:30 +00:00
}
columns , e := rows . Columns ( )
if e != nil {
return
}
noOfColumns := len ( columns )
resultRows = [ ] * RequestResultRow { }
for rows . Next ( ) {
var first string
var second int
if noOfColumns == 2 {
2019-04-02 14:28:06 +00:00
e = rows . Scan ( & first , & second )
2019-05-26 06:31:30 +00:00
} else if noOfColumns == 1 {
e = rows . Scan ( & second )
2019-04-02 14:28:06 +00:00
}
2019-05-26 06:31:30 +00:00
if e != nil {
_ = rows . Close ( )
return
}
if first == "" {
first = "Undefined"
}
resultRows = append ( resultRows , & RequestResultRow {
First : first ,
Second : second ,
} )
2019-04-02 14:28:06 +00:00
}
2019-05-26 06:31:30 +00:00
return
2019-04-02 14:28:06 +00:00
}
2019-04-09 13:17:18 +00:00
func ( request * ViewsRequest ) buildStatement ( ) ( statement string , parameters [ ] sql . NamedArg ) {
filters , parameters := request . buildFilter ( )
2019-04-02 14:28:06 +00:00
if len ( filters ) > 0 {
filters = " where " + filters + " "
} else {
filters = " "
}
2019-04-09 13:17:18 +00:00
orderrow := "first"
order := "ASC"
2019-05-01 10:17:37 +00:00
if request . ordercol == "second" {
2019-04-09 13:17:18 +00:00
orderrow = "second"
}
2019-04-15 07:41:59 +00:00
if request . order == "DESC" {
2019-04-09 13:17:18 +00:00
order = "DESC"
}
2019-05-01 10:17:37 +00:00
orderStatement := " ORDER BY " + orderrow + " " + order
limitStatement := ""
if len ( request . limit ) != 0 {
limitStatement = " LIMIT :limit"
parameters = append ( parameters , sql . Named ( "limit" , request . limit ) )
}
2019-04-02 14:28:06 +00:00
switch request . view {
case PAGES :
2019-05-01 10:17:37 +00:00
statement = "SELECT url as first, count(*) as second from views" + filters + "group by first" + orderStatement + limitStatement + ";"
2019-04-02 14:28:06 +00:00
case REFERRERS :
2019-05-01 10:17:37 +00:00
statement = "SELECT ref as first, count(*) as second from views" + filters + "group by first" + orderStatement + limitStatement + ";"
2019-04-08 19:41:16 +00:00
case USERAGENTS :
2019-05-01 10:17:37 +00:00
statement = "SELECT useragent as first, count(*) as second from views" + filters + "group by first" + orderStatement + limitStatement + ";"
2019-04-09 12:31:15 +00:00
case USERAGENTNAMES :
2019-05-01 10:17:37 +00:00
statement = "SELECT substr(useragent, 1, pos-1) as first, COUNT(*) as second from (SELECT *, instr(useragent,' ') AS pos FROM views)" + filters + "group by first" + orderStatement + limitStatement + ";"
2019-10-19 10:13:02 +00:00
case OS :
statement = "SELECT os as first, count(*) as second from views" + filters + "group by first" + orderStatement + limitStatement + ";"
2019-04-15 07:56:41 +00:00
case ALLHOURS :
2019-05-01 10:17:37 +00:00
statement = "WITH RECURSIVE hours(hour) AS ( VALUES (datetime(strftime('%Y-%m-%dT%H:00', (SELECT min(time) from views" + filters + "), 'localtime'))) UNION ALL SELECT datetime(hour, '+1 hour') FROM hours WHERE hour <= strftime('%Y-%m-%d %H', (SELECT max(time) from views" + filters + "), 'localtime') ) SELECT strftime('%Y-%m-%d %H', hours.hour) as first, COUNT(time) as second FROM hours LEFT OUTER JOIN (SELECT time from views" + filters + ") ON strftime('%Y-%m-%d %H', hours.hour) = strftime('%Y-%m-%d %H', time, 'localtime') GROUP BY first" + orderStatement + limitStatement + ";"
2019-04-15 07:56:41 +00:00
case ALLDAYS :
2019-05-01 10:17:37 +00:00
statement = "WITH RECURSIVE days(day) AS ( VALUES (datetime((SELECT min(time) from views" + filters + "), 'localtime', 'start of day')) UNION ALL SELECT datetime(day, '+1 day') FROM days WHERE day <= date((SELECT max(time) from views" + filters + "), 'localtime') ) SELECT strftime('%Y-%m-%d', days.day) as first, COUNT(time) as second FROM days LEFT OUTER JOIN (SELECT time from views" + filters + ") ON strftime('%Y-%m-%d', days.day) = strftime('%Y-%m-%d', time, 'localtime') GROUP BY first" + orderStatement + limitStatement + ";"
2019-04-02 14:28:06 +00:00
case HOURS , DAYS , WEEKS , MONTHS :
format := ""
switch request . view {
case HOURS :
format = "%Y-%m-%d %H"
case DAYS :
format = "%Y-%m-%d"
case WEEKS :
format = "%Y-%W"
case MONTHS :
format = "%Y-%m"
}
2019-05-01 10:17:37 +00:00
statement = "SELECT strftime('" + format + "', time, 'localtime') as first, count(*) as second from views" + filters + "group by first" + orderStatement + limitStatement + ";"
2019-05-26 06:31:30 +00:00
case COUNT :
statement = "SELECT count(*) as second from views" + filters + ";"
2019-04-02 14:28:06 +00:00
}
return
}
// Request filters
func ( request * ViewsRequest ) buildFilter ( ) ( filters string , parameters [ ] sql . NamedArg ) {
parameters = [ ] sql . NamedArg { }
var allFilters [ ] string
for _ , filter := range [ ] string {
request . buildDateTimeFilter ( & parameters ) ,
request . buildUrlFilter ( & parameters ) ,
request . buildRefFilter ( & parameters ) ,
2019-04-08 19:48:34 +00:00
request . buildUseragentFilter ( & parameters ) ,
2019-08-02 17:32:34 +00:00
request . buildBotFilter ( & parameters ) ,
2019-10-19 10:13:02 +00:00
request . buildOSFilter ( & parameters ) ,
2019-04-02 14:28:06 +00:00
} {
if len ( filter ) > 0 {
allFilters = append ( allFilters , filter )
}
}
filters = strings . Join ( allFilters , " and " )
return
}
func ( request * ViewsRequest ) buildDateTimeFilter ( namedArg * [ ] sql . NamedArg ) ( dateTimeFilter string ) {
2019-05-07 09:23:59 +00:00
// Generate absolute from / to from relative ones
if len ( request . fromRel ) > 0 {
duration , e := time . ParseDuration ( request . fromRel )
if e == nil {
request . from = time . Now ( ) . Add ( duration ) . Format ( "2006-01-02 15:04:05" )
}
}
if len ( request . toRel ) > 0 {
duration , e := time . ParseDuration ( request . toRel )
if e == nil {
request . to = time . Now ( ) . Add ( duration ) . Format ( "2006-01-02 15:04:05" )
}
}
// Build filter
2019-04-29 10:33:03 +00:00
selector := "datetime(time, 'localtime')"
2019-04-02 14:28:06 +00:00
if len ( request . from ) > 0 && len ( request . to ) > 0 {
* namedArg = append ( * namedArg , sql . Named ( "from" , request . from ) )
* namedArg = append ( * namedArg , sql . Named ( "to" , request . to ) )
2019-04-15 07:56:41 +00:00
dateTimeFilter = selector + " between :from and :to"
2019-04-02 14:28:06 +00:00
} else if len ( request . from ) > 0 {
* namedArg = append ( * namedArg , sql . Named ( "from" , request . from ) )
2019-04-15 07:56:41 +00:00
dateTimeFilter = selector + " >= :from"
2019-04-02 14:28:06 +00:00
} else if len ( request . to ) > 0 {
* namedArg = append ( * namedArg , sql . Named ( "to" , request . to ) )
2019-04-15 07:56:41 +00:00
dateTimeFilter = selector + " <= :to"
2019-04-02 14:28:06 +00:00
}
return
}
func ( request * ViewsRequest ) buildUrlFilter ( namedArg * [ ] sql . NamedArg ) ( urlFilter string ) {
if len ( request . url ) > 0 {
2019-04-08 19:36:11 +00:00
* namedArg = append ( * namedArg , sql . Named ( "url" , "%" + request . url + "%" ) )
urlFilter = "url like :url"
2019-04-02 14:28:06 +00:00
}
return
}
func ( request * ViewsRequest ) buildRefFilter ( namedArg * [ ] sql . NamedArg ) ( refFilter string ) {
2019-04-08 19:45:37 +00:00
if len ( request . ref ) > 0 {
* namedArg = append ( * namedArg , sql . Named ( "ref" , "%" + request . ref + "%" ) )
refFilter = "ref like :ref"
2019-04-02 14:28:06 +00:00
}
return
}
2019-04-08 19:48:34 +00:00
2019-08-02 17:32:34 +00:00
func ( request * ViewsRequest ) buildUseragentFilter ( namedArg * [ ] sql . NamedArg ) ( uaFilter string ) {
2019-04-08 19:48:34 +00:00
if len ( request . ua ) > 0 {
* namedArg = append ( * namedArg , sql . Named ( "ua" , "%" + request . ua + "%" ) )
2019-08-02 17:32:34 +00:00
uaFilter = "useragent like :ua"
}
return
}
func ( request * ViewsRequest ) buildBotFilter ( namedArg * [ ] sql . NamedArg ) ( botFilter string ) {
if len ( request . bots ) > 0 {
if request . bots == "0" || request . bots == "1" {
* namedArg = append ( * namedArg , sql . Named ( "bot" , request . bots ) )
}
botFilter = "bot like :bot"
2019-04-08 19:48:34 +00:00
}
return
}
2019-10-19 10:13:02 +00:00
func ( request * ViewsRequest ) buildOSFilter ( namedArg * [ ] sql . NamedArg ) ( osFilter string ) {
if len ( request . os ) > 0 {
* namedArg = append ( * namedArg , sql . Named ( "os" , "%" + request . os + "%" ) )
osFilter = "os like :os"
}
return
}