-
Notifications
You must be signed in to change notification settings - Fork 16
/
query.go
131 lines (120 loc) · 3.13 KB
/
query.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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
package repos
import (
"fmt"
"strings"
"github.com/honeynet/ochi/backend/entities"
"github.com/google/uuid"
"github.com/jmoiron/sqlx"
"github.com/jmoiron/sqlx/reflectx"
_ "github.com/mattn/go-sqlite3"
)
type QueryRepo struct {
findByOwnerQuery *sqlx.Stmt
deleteQuery *sqlx.Stmt
createQuery *sqlx.NamedStmt
updateQuery *sqlx.Stmt
getByIdQuery *sqlx.Stmt
db *sqlx.DB
}
// NewQueryRepo creates a query repo
func NewQueryRepo(db *sqlx.DB) (*QueryRepo, error) {
r := &QueryRepo{}
db.Mapper = reflectx.NewMapperFunc("json", strings.ToLower)
_, err := db.Exec(`CREATE TABLE IF NOT EXISTS queries (
id TEXT PRIMARY KEY NOT NULL
, content TEXT NOT NULL
, owner_id TEXT NOT NULL
, active INTEGER NOT NULL
, description TEXT NOT NULL
, CONSTRAINT id_unique UNIQUE (id)
)`)
if err != nil {
return nil, err
}
r.findByOwnerQuery, err = db.Preparex("SELECT * FROM queries WHERE owner_id=?")
if err != nil {
return nil, err
}
r.createQuery, err = db.PrepareNamed(`INSERT INTO queries
(id, owner_id, content, active, description)
VALUES
(:id, :owner_id, :content, :active, :description)`)
if err != nil {
return nil, err
}
r.updateQuery, err = db.Preparex(
`UPDATE queries
SET content=?, active=?, description=?
WHERE id=?`)
if err != nil {
return nil, err
}
r.getByIdQuery, err = db.Preparex("SELECT * FROM queries WHERE id=?")
if err != nil {
return nil, err
}
r.deleteQuery, err = db.Preparex("DELETE FROM queries WHERE id=?")
if err != nil {
return nil, err
}
return r, nil
}
// FindByOwnerId returns a list of queries by owner ID
func (r *QueryRepo) FindByOwnerId(ownerId string) ([]entities.Query, error) {
qs := []entities.Query{}
err := r.findByOwnerQuery.Select(&qs, ownerId)
return qs, err
}
// Create creates a new query
func (r *QueryRepo) Create(owner_id, content, description string, active bool) (entities.Query, error) {
id, err := uuid.NewRandom()
q := entities.Query{}
if err != nil {
return q, err
}
q = entities.Query{ID: id.String(), Content: content, OwnerID: owner_id, Active: active, Description: description}
_, err = r.createQuery.Exec(&q)
if err != nil {
return q, err
}
return q, nil
}
// Update updates an existing query
func (r *QueryRepo) Update(id, content, description string, active bool) error {
res, err := r.updateQuery.Exec(content, active, description, id)
if err != nil {
return err
}
if cnt, err := res.RowsAffected(); err != nil {
return err
} else if cnt == 0 {
return fmt.Errorf("%s not found", id)
}
return nil
}
// GetByID finds a single query by given ID
func (r *QueryRepo) GetByID(id string) (entities.Query, error) {
q := entities.Query{}
err := r.getByIdQuery.Get(&q, id)
return q, err
}
// Delete removes a query by ID
func (r *QueryRepo) Delete(id string) error {
res, err := r.deleteQuery.Exec(id)
if err != nil {
return err
}
if cnt, err := res.RowsAffected(); err != nil {
return err
} else if cnt == 0 {
return fmt.Errorf("%s not found", id)
}
return nil
}
// Close closes DB connection held by this repo.
func (r *QueryRepo) Close() error {
if r.db != nil {
return r.db.Close()
}
return nil
}