package ping import ( "database/sql" "net/http" "lewisdale.dev/oopsie/sites" ) type Status int16 const ( Success Status = iota Failure ) type Ping struct { Site sites.Site Timestamp string Status Status } type PingResponse struct { Site sites.Site Timestamp string Status string } const createQuery = `CREATE TABLE IF NOT EXISTS statuses ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS ping ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, site TEXT NOT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, status INTEGER NOT NULL, FOREIGN KEY (site) REFERENCES sites(url), FOREIGN KEY (status) REFERENCES statuses(id) );` const seedStatusQuery = `INSERT INTO statuses (id, name) VALUES (?, ?) ON CONFLICT (id) DO NOTHING;` func CreateTable(db *sql.DB) { if _, err := db.Exec(createQuery); err != nil { panic(err) } seedStatuses(db) } func seedStatuses(db *sql.DB) { if _, err := db.Exec(seedStatusQuery, Success, "Success"); err != nil { panic(err) } if _, err := db.Exec(seedStatusQuery, Failure, "Failure"); err != nil { panic(err) } } const saveQuery = `INSERT INTO ping (site, status) VALUES (?, ?);` func (p *Ping) Save(db *sql.DB) { db.Exec(saveQuery, p.Site.Url, p.Status) } func SendPing(db *sql.DB, site sites.Site) { p := Ping{ Site: site, } if _, err := http.Get(site.Url); err != nil { p.Status = Failure } else { p.Status = Success } p.Save(db) } func List(db *sql.DB) []PingResponse { rows, err := db.Query(`SELECT sites.url as url, sites.name, ping.timestamp as timestamp, statuses.name as status FROM ping JOIN sites ON ping.site = sites.url JOIN statuses ON ping.status = statuses.id ORDER BY timestamp DESC`) if err != nil { panic(err) } defer rows.Close() pings := make([]PingResponse, 0) for rows.Next() { p := PingResponse{} rows.Scan(&p.Site.Url, &p.Site.Name, &p.Timestamp, &p.Status) pings = append(pings, p) } return pings } type SiteResponse struct { created_at string Name string Url string Pings []Ping } func ListGroupedBySite(db *sql.DB) []SiteResponse { responses := make([]SiteResponse, 0) for _, site := range sites.List(db) { s := SiteResponse{Url: site.Url, Name: site.Name, created_at: site.Created_at} s.Pings = listForSite(db, site) responses = append(responses, s) } return responses } func listForSite(db *sql.DB, site sites.Site) []Ping { rows, err := db.Query(`SELECT ping.timestamp, ping.status FROM ping WHERE site = ? ORDER BY timestamp DESC`, site.Url) if err != nil { panic(err) } defer rows.Close() pings := make([]Ping, 0) for rows.Next() { p := Ping{} rows.Scan(&p.Timestamp, &p.Status) p.Site = site pings = append(pings, p) } return pings }