/
pg.go
103 lines (92 loc) · 2.68 KB
/
pg.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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
package logs
import (
"context"
"database/sql"
"time"
)
type pg struct {
*sql.DB
}
// setupDatabase sets the PostgreSQL database to use Timescale DB
func setupDatabase(db *sql.DB) error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
// Creating a timescaledb extension for the database
const ext = `CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;`
if _, err = tx.Exec(ext); err != nil {
return err
}
// creating schema in the database
const sch = `CREATE SCHEMA IF NOT EXISTS "audit"`
if _, err = tx.Exec(sch); err != nil {
return err
}
// creating the audit log table
const tbl = `CREATE TABLE IF NOT EXISTS audit."Logs" (
"Timestamp" TIMESTAMPTZ NOT NULL,
"UserId" text NOT NULL,
"Action" text NOT NULL
);`
if _, err = tx.Exec(tbl); err != nil {
return err
}
// creating the hypertable of audit log table for timescaledb
const hptbl = `SELECT create_hypertable('audit."Logs"', 'Timestamp',if_not_exists => true);`
if _, err = tx.Exec(hptbl); err != nil {
return err
}
return tx.Commit()
}
// NewPostgresAuditLog returns the AuditLog interface that implements Audit Logs Functions
func NewPostgresAuditLog(db *sql.DB) (AuditLog, error) {
if err := setupDatabase(db); err != nil {
return nil, err
}
return &pg{db}, nil
}
// Add adds a log into database
func (db *pg) Add(ctx context.Context, action string) error {
userID := ctx.Value("userId").(string)
const stmt = `INSERT INTO audit."Logs" VALUES (NOW(), $1, $2);`
if _, err := db.ExecContext(ctx, stmt, userID, action); err != nil {
return err
}
return nil
}
// GetLogsOfUser returns the list of all the Logs for an user
func (db *pg) GetLogsOfUser(ctx context.Context, userID string) ([]*Log, error) {
const stmt = `SELECT * FROM audit."Logs" WHERE "UserId" = $1;`
rows, err := db.QueryContext(ctx, stmt, userID)
if err != nil {
return nil, err
}
lst := make([]*Log, 0, 100)
for rows.Next() {
l := Log{}
if err := rows.Scan(&l.Timestamp, &l.UserID, &l.Action); err != nil {
return nil, err
}
lst = append(lst, &l)
}
return lst, nil
}
// GetLogsBetweenInterval returns the list of all the Logs for an user in a range of interval
func (db *pg) GetLogsBetweenInterval(ctx context.Context, start time.Time, end time.Time, userID string) ([]*Log, error) {
const stmt = `SELECT * FROM audit."Logs" WHERE "UserId" = $1 AND "Timestamp" >= $2 AND "Timestamp" <= $3;`
rows, err := db.QueryContext(ctx, stmt, userID, start, end)
if err != nil {
return nil, err
}
lst := make([]*Log, 0, 100)
for rows.Next() {
l := Log{}
if err := rows.Scan(&l.Timestamp, &l.UserID, &l.Action); err != nil {
return nil, err
}
lst = append(lst, &l)
}
return lst, nil
}