-
Notifications
You must be signed in to change notification settings - Fork 55
/
webuser.go
187 lines (158 loc) · 5.17 KB
/
webuser.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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
// Copyright 2020 Readium Foundation. All rights reserved.
// Use of this source code is governed by a BSD-style license
// that can be found in the LICENSE file exposed on Github (readium) in the project repository.
package webuser
import (
"database/sql"
"errors"
"log"
"github.com/readium/readium-lcp-server/config"
"github.com/readium/readium-lcp-server/dbutils"
uuid "github.com/satori/go.uuid"
)
// ErrNotFound error trown when user is not found
var ErrNotFound = errors.New("User not found")
// WebUser interface for user db interaction
type WebUser interface {
Get(id int64) (User, error)
GetByEmail(email string) (User, error)
Add(c User) error
Update(c User) error
DeleteUser(UserID int64) error
ListUsers(page int, pageNum int) func() (User, error)
}
// User struct defines a user
type User struct {
ID int64 `json:"id"`
UUID string `json:"uuid"`
Name string `json:"name,omitempty"`
Email string `json:"email,omitempty"`
Password string `json:"password,omitempty"`
Hint string `json:"hint"`
}
type dbUser struct {
db *sql.DB
dbGetUser *sql.Stmt
dbGetByEmail *sql.Stmt
dbList *sql.Stmt
}
// Get returns a user
func (user dbUser) Get(id int64) (User, error) {
row := user.dbGetUser.QueryRow(id)
var c User
err := row.Scan(&c.ID, &c.UUID, &c.Name, &c.Email, &c.Password, &c.Hint)
if err != nil {
return User{}, ErrNotFound
}
return c, err
}
// GetByEmail returns a user
func (user dbUser) GetByEmail(email string) (User, error) {
row := user.dbGetByEmail.QueryRow(email)
var c User
err := row.Scan(&c.ID, &c.UUID, &c.Name, &c.Email, &c.Password, &c.Hint)
return c, err
}
// Add inserts a user
func (user dbUser) Add(newUser User) error {
// Create uuid
uid, err_u := uuid.NewV4()
if err_u != nil {
return err_u
}
newUser.UUID = uid.String()
_, err := user.db.Exec(dbutils.GetParamQuery(config.Config.FrontendServer.Database,
"INSERT INTO \"user\" (uuid, name, email, password, hint) VALUES (?, ?, ?, ?, ?)"),
newUser.UUID, newUser.Name, newUser.Email, newUser.Password, newUser.Hint)
return err
}
// Update updates a user
func (user dbUser) Update(changedUser User) error {
_, err := user.db.Exec(dbutils.GetParamQuery(config.Config.FrontendServer.Database,
"UPDATE \"user\" SET name=? , email=?, password=?, hint=? WHERE id=?"),
changedUser.Name, changedUser.Email, changedUser.Password, changedUser.Hint, changedUser.ID)
return err
}
// DeleteUser deletes a user
func (user dbUser) DeleteUser(userID int64) error {
// delete user purchases
_, err := user.db.Exec(dbutils.GetParamQuery(config.Config.FrontendServer.Database, "DELETE FROM purchase WHERE user_id=?"), userID)
if err != nil {
return err
}
// delete user
_, err = user.db.Exec(dbutils.GetParamQuery(config.Config.FrontendServer.Database, "DELETE FROM \"user\" WHERE id=?"), userID)
return err
}
// ListUsers lists users
func (user dbUser) ListUsers(page int, pageNum int) func() (User, error) {
var rows *sql.Rows
var err error
driver, _ := config.GetDatabase(config.Config.FrontendServer.Database)
if driver == "mssql" {
rows, err = user.dbList.Query(pageNum*page, page)
} else {
rows, err = user.dbList.Query(page, pageNum*page)
}
if err != nil {
return func() (User, error) { return User{}, err }
}
return func() (User, error) {
var u User
var err error
if rows.Next() {
err = rows.Scan(&u.ID, &u.UUID, &u.Name, &u.Email, &u.Password, &u.Hint)
} else {
rows.Close()
err = ErrNotFound
}
return u, err
}
}
// Open returns a WebUser interface (db interaction)
func Open(db *sql.DB) (i WebUser, err error) {
driver, _ := config.GetDatabase(config.Config.FrontendServer.Database)
// if sqlite, create the content table in the frontend db if it does not exist
if driver == "sqlite3" {
_, err = db.Exec(tableDef)
if err != nil {
log.Println("Error creating user table")
return
}
}
var dbGetUser *sql.Stmt
dbGetUser, err = db.Prepare(dbutils.GetParamQuery(config.Config.FrontendServer.Database,
"SELECT id, uuid, name, email, password, hint FROM \"user\" WHERE id = ?"))
if err != nil {
return
}
var dbGetByEmail *sql.Stmt
if driver == "mssql" {
dbGetByEmail, err = db.Prepare("SELECT TOP 1 id, uuid, name, email, password, hint FROM \"user\" WHERE email = ?")
} else {
dbGetByEmail, err = db.Prepare(dbutils.GetParamQuery(config.Config.FrontendServer.Database,
"SELECT id, uuid, name, email, password, hint FROM \"user\" WHERE email = ? LIMIT 1"))
}
if err != nil {
return
}
var dbList *sql.Stmt
if driver == "mssql" {
dbList, err = db.Prepare("SELECT id, uuid, name, email, password, hint FROM \"user\" ORDER BY email desc OFFSET ? ROWS FETCH NEXT ? ROWS ONLY")
} else {
dbList, err = db.Prepare(dbutils.GetParamQuery(config.Config.FrontendServer.Database,
"SELECT id, uuid, name, email, password, hint FROM \"user\" ORDER BY email desc LIMIT ? OFFSET ?"))
}
if err != nil {
return
}
i = dbUser{db, dbGetUser, dbGetByEmail, dbList}
return
}
const tableDef = "CREATE TABLE IF NOT EXISTS \"user\" (" +
"id integer NOT NULL PRIMARY KEY," +
"uuid varchar(255) NOT NULL," +
"name varchar(64) NOT NULL," +
"email varchar(64) NOT NULL," +
"password varchar(64) NOT NULL," +
"hint varchar(64) NOT NULL)"