diff options
Diffstat (limited to 'db')
-rw-r--r-- | db/models.go | 4 | ||||
-rw-r--r-- | db/query.sql | 14 | ||||
-rw-r--r-- | db/query.sql.go | 41 | ||||
-rw-r--r-- | db/schema.sql | 6 |
4 files changed, 62 insertions, 3 deletions
diff --git a/db/models.go b/db/models.go index a6da6bf..1ea2797 100644 --- a/db/models.go +++ b/db/models.go @@ -8,6 +8,10 @@ import ( "github.com/jackc/pgx/v5/pgtype" ) +type Dnsblacklist struct { + Name string +} + type Usage struct { Hardwareaddr int64 Starttime pgtype.Timestamp diff --git a/db/query.sql b/db/query.sql index cfea3f1..9ba172f 100644 --- a/db/query.sql +++ b/db/query.sql @@ -6,4 +6,16 @@ INSERT INTO Usage ( ); -- name: GetUsage :one -SELECT SUM(Ingress) AS Ingress, SUM(Egress) AS Egress FROM Usage; +SELECT SUM(Ingress) AS Ingress, SUM(Egress) AS Egress +FROM Usage; + +-- name: EnterDnsBlackList :exec +INSERT INTO DnsBlackList ( + Name +) VALUES ( + $1 +); + +-- name: GetDnsBlackList :many +SELECT * +FROM DnsBlackList; diff --git a/db/query.sql.go b/db/query.sql.go index d6304a7..80c6469 100644 --- a/db/query.sql.go +++ b/db/query.sql.go @@ -11,6 +11,19 @@ import ( "github.com/jackc/pgx/v5/pgtype" ) +const enterDnsBlackList = `-- name: EnterDnsBlackList :exec +INSERT INTO DnsBlackList ( + Name +) VALUES ( + $1 +) +` + +func (q *Queries) EnterDnsBlackList(ctx context.Context, name string) error { + _, err := q.db.Exec(ctx, enterDnsBlackList, name) + return err +} + const enterUsage = `-- name: EnterUsage :exec INSERT INTO Usage ( HardwareAddr, StartTime, StopTime, Egress, Ingress @@ -38,8 +51,34 @@ func (q *Queries) EnterUsage(ctx context.Context, arg EnterUsageParams) error { return err } +const getDnsBlackList = `-- name: GetDnsBlackList :many +SELECT name +FROM DnsBlackList +` + +func (q *Queries) GetDnsBlackList(ctx context.Context) ([]string, error) { + rows, err := q.db.Query(ctx, getDnsBlackList) + if err != nil { + return nil, err + } + defer rows.Close() + var items []string + for rows.Next() { + var name string + if err := rows.Scan(&name); err != nil { + return nil, err + } + items = append(items, name) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + const getUsage = `-- name: GetUsage :one -SELECT SUM(Ingress) AS Ingress, SUM(Egress) AS Egress FROM Usage +SELECT SUM(Ingress) AS Ingress, SUM(Egress) AS Egress +FROM Usage ` type GetUsageRow struct { diff --git a/db/schema.sql b/db/schema.sql index aece061..c8c35a1 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -1,7 +1,11 @@ -CREATE TABLE Usage ( +CREATE TABLE IF NOT EXISTS Usage ( HardwareAddr BIGINT NOT NULL, StartTime TIMESTAMP NOT NULL, StopTime TIMESTAMP NOT NULL, Egress BIGINT NOT NULL, Ingress BIGINT NOT NULL ); + +CREATE TABLE IF NOT EXISTS DnsBlackList ( + Name TEXT NOT NULL UNIQUE +); |