/
database.go
66 lines (57 loc) · 1.54 KB
/
database.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
package database
import (
"fmt"
"os"
"github.com/jmoiron/sqlx"
_ "github.com/mattn/go-sqlite3"
)
const (
// This is the format for a SQL Datetime Literal.
SQLDateTimeFormat = "2006-01-02 15:04:05"
schema = `CREATE TABLE visits (
id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
ip varchar(255) NOT NULL,
host text NOT NULL,
user_agent text NOT NULL,
path text NOT NULL,
created_at datetime NOT NULL
);`
checkIfSchemaExists = `SELECT COUNT(*) as does_exist FROM sqlite_master WHERE type='table' AND name='visits';`
insertVisit = `INSERT INTO visits (ip, host, path, user_agent, created_at) VALUES (:ip, :host, :path, :user_agent, :created_at)`
)
type TableCheck struct {
DoesExist int `db:"does_exist"`
}
func Initialize() (*sqlx.DB, error) {
db, err := sqlx.Connect("sqlite3", os.Getenv("PING_DB"))
if err != nil {
return nil, err
}
if err := db.Ping(); err != nil {
return db, err
}
var check TableCheck
if err := db.Get(&check, checkIfSchemaExists); err != nil {
return db, err
}
if check.DoesExist < 1 {
if _, err := db.Exec(schema); err != nil {
return db, err
}
}
return db, nil
}
type Visit struct {
IP string `db:"ip"`
Host string `db:"host"`
Path string `db:"path"`
UserAgent string `db:"user_agent"`
CreatedAt string `db:"created_at"`
}
func (v *Visit) String() string {
return fmt.Sprintf("<%s | %s requested %s%s @ %s>", v.CreatedAt, v.IP, v.Host, v.Path, v.UserAgent)
}
func (v *Visit) Save(db *sqlx.DB) error {
_, err := db.NamedExec(insertVisit, v)
return err
}