1package sqlite
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log"
8 "strings"
9 "time"
10
11 "github.com/charmbracelet/soft-serve/server/db"
12 "github.com/charmbracelet/soft-serve/server/db/types"
13 "modernc.org/sqlite"
14 sqlitelib "modernc.org/sqlite/lib"
15)
16
17var _ db.DB = &Sqlite{}
18
19// Sqlite is a SQLite database.
20type Sqlite struct {
21 path string
22 db *sql.DB
23}
24
25// New creates a new DB in the given path.
26func New(path string) (*Sqlite, error) {
27 var err error
28 log.Printf("Opening SQLite db: %s\n", path)
29 db, err := sql.Open("sqlite", path+
30 "?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)")
31 if err != nil {
32 return nil, err
33 }
34 d := &Sqlite{
35 db: db,
36 path: path,
37 }
38 if err = d.CreateDB(); err != nil {
39 return nil, err
40 }
41 return d, d.db.Ping()
42}
43
44// Close closes the database.
45func (d *Sqlite) Close() error {
46 return d.db.Close()
47}
48
49// CreateDB creates the database and tables.
50func (d *Sqlite) CreateDB() error {
51 return d.wrapTransaction(func(tx *sql.Tx) error {
52 if _, err := tx.Exec(sqlInsertConfig); err != nil {
53 return err
54 }
55 if _, err := tx.Exec(sqlCreateUserTable); err != nil {
56 return err
57 }
58 if _, err := tx.Exec(sqlCreatePublicKeyTable); err != nil {
59 return err
60 }
61 if _, err := tx.Exec(sqlCreateRepoTable); err != nil {
62 return err
63 }
64 if _, err := tx.Exec(sqlCreateCollabTable); err != nil {
65 return err
66 }
67 return nil
68 })
69}
70
71const defaultConfigID = 1
72
73// GetConfig returns the server config.
74func (d *Sqlite) GetConfig() (*types.Config, error) {
75 var c types.Config
76 if err := d.wrapTransaction(func(tx *sql.Tx) error {
77 r := tx.QueryRow(sqlSelectConfig, defaultConfigID)
78 if err := r.Scan(&c.ID, &c.Name, &c.Host, &c.Port, &c.AnonAccess, &c.AllowKeyless, &c.CreatedAt, &c.UpdatedAt); err != nil {
79 return err
80 }
81 return nil
82 }); err != nil {
83 return nil, err
84 }
85 return &c, nil
86}
87
88// SetConfigName sets the server config name.
89func (d *Sqlite) SetConfigName(name string) error {
90 return d.wrapTransaction(func(tx *sql.Tx) error {
91 _, err := tx.Exec(sqlUpdateConfigName, name, defaultConfigID)
92 return err
93 })
94}
95
96// SetConfigHost sets the server config host.
97func (d *Sqlite) SetConfigHost(host string) error {
98 return d.wrapTransaction(func(tx *sql.Tx) error {
99 _, err := tx.Exec(sqlUpdateConfigHost, host, defaultConfigID)
100 return err
101 })
102}
103
104// SetConfigPort sets the server config port.
105func (d *Sqlite) SetConfigPort(port int) error {
106 return d.wrapTransaction(func(tx *sql.Tx) error {
107 _, err := tx.Exec(sqlUpdateConfigPort, port, defaultConfigID)
108 return err
109 })
110}
111
112// SetConfigAnonAccess sets the server config anon access.
113func (d *Sqlite) SetConfigAnonAccess(access string) error {
114 return d.wrapTransaction(func(tx *sql.Tx) error {
115 _, err := tx.Exec(sqlUpdateConfigAnon, access, defaultConfigID)
116 return err
117 })
118}
119
120// SetConfigAllowKeyless sets the server config allow keyless.
121func (d *Sqlite) SetConfigAllowKeyless(allow bool) error {
122 return d.wrapTransaction(func(tx *sql.Tx) error {
123 _, err := tx.Exec(sqlUpdateConfigKeyless, allow, defaultConfigID)
124 return err
125 })
126}
127
128// AddUser adds a new user.
129func (d *Sqlite) AddUser(name, login, email, password string, isAdmin bool) error {
130 var l *string
131 var e *string
132 var p *string
133 if login != "" {
134 login = strings.ToLower(login)
135 l = &login
136 }
137 if email != "" {
138 email = strings.ToLower(email)
139 e = &email
140 }
141 if password != "" {
142 p = &password
143 }
144 if err := d.wrapTransaction(func(tx *sql.Tx) error {
145 if _, err := tx.Exec(sqlInsertUser, name, l, e, p, isAdmin); err != nil {
146 return err
147 }
148 return nil
149 }); err != nil {
150 return err
151 }
152 return nil
153}
154
155// DeleteUser deletes a user.
156func (d *Sqlite) DeleteUser(id int) error {
157 return d.wrapTransaction(func(tx *sql.Tx) error {
158 _, err := tx.Exec(sqlDeleteUser, id)
159 return err
160 })
161}
162
163// GetUser returns a user by ID.
164func (d *Sqlite) GetUser(id int) (*types.User, error) {
165 var u types.User
166 if err := d.wrapTransaction(func(tx *sql.Tx) error {
167 r := tx.QueryRow(sqlSelectUser, id)
168 if err := r.Scan(&u.ID, &u.Name, &u.Login, &u.Email, &u.Password, &u.Admin, &u.CreatedAt, &u.UpdatedAt); err != nil {
169 return err
170 }
171 return nil
172 }); err != nil {
173 return nil, err
174 }
175 return &u, nil
176}
177
178// GetUserByLogin returns a user by login.
179func (d *Sqlite) GetUserByLogin(login string) (*types.User, error) {
180 login = strings.ToLower(login)
181 var u types.User
182 if err := d.wrapTransaction(func(tx *sql.Tx) error {
183 r := tx.QueryRow(sqlSelectUserByLogin, login)
184 if err := r.Scan(&u.ID, &u.Name, &u.Login, &u.Email, &u.Password, &u.Admin, &u.CreatedAt, &u.UpdatedAt); err != nil {
185 return err
186 }
187 return nil
188 }); err != nil {
189 return nil, err
190 }
191 return &u, nil
192}
193
194// GetUserByLogin returns a user by login.
195func (d *Sqlite) GetUserByEmail(email string) (*types.User, error) {
196 email = strings.ToLower(email)
197 var u types.User
198 if err := d.wrapTransaction(func(tx *sql.Tx) error {
199 r := tx.QueryRow(sqlSelectUserByEmail, email)
200 if err := r.Scan(&u.ID, &u.Name, &u.Login, &u.Email, &u.Password, &u.Admin, &u.CreatedAt, &u.UpdatedAt); err != nil {
201 return err
202 }
203 return nil
204 }); err != nil {
205 return nil, err
206 }
207 return &u, nil
208}
209
210// GetUserByPublicKey returns a user by public key.
211func (d *Sqlite) GetUserByPublicKey(key string) (*types.User, error) {
212 var u types.User
213 if err := d.wrapTransaction(func(tx *sql.Tx) error {
214 r := tx.QueryRow(sqlSelectUserByPublicKey, key)
215 if err := r.Scan(&u.ID, &u.Name, &u.Login, &u.Email, &u.Password, &u.Admin, &u.CreatedAt, &u.UpdatedAt); err != nil {
216 return err
217 }
218 return nil
219 }); err != nil {
220 return nil, err
221 }
222 return &u, nil
223}
224
225// SetUserName sets the user name.
226func (d *Sqlite) SetUserName(user *types.User, name string) error {
227 return d.wrapTransaction(func(tx *sql.Tx) error {
228 _, err := tx.Exec(sqlUpdateUserName, name, user.ID)
229 return err
230 })
231}
232
233// SetUserLogin sets the user login.
234func (d *Sqlite) SetUserLogin(user *types.User, login string) error {
235 if login == "" {
236 return fmt.Errorf("login cannot be empty")
237 }
238 login = strings.ToLower(login)
239 return d.wrapTransaction(func(tx *sql.Tx) error {
240 _, err := tx.Exec(sqlUpdateUserLogin, login, user.ID)
241 return err
242 })
243}
244
245// SetUserEmail sets the user email.
246func (d *Sqlite) SetUserEmail(user *types.User, email string) error {
247 if email == "" {
248 return fmt.Errorf("email cannot be empty")
249 }
250 email = strings.ToLower(email)
251 return d.wrapTransaction(func(tx *sql.Tx) error {
252 _, err := tx.Exec(sqlUpdateUserEmail, email, user.ID)
253 return err
254 })
255}
256
257// SetUserPassword sets the user password.
258func (d *Sqlite) SetUserPassword(user *types.User, password string) error {
259 if password == "" {
260 return fmt.Errorf("password cannot be empty")
261 }
262 return d.wrapTransaction(func(tx *sql.Tx) error {
263 _, err := tx.Exec(sqlUpdateUserPassword, password, user.ID)
264 return err
265 })
266}
267
268// SetUserAdmin sets the user admin.
269func (d *Sqlite) SetUserAdmin(user *types.User, admin bool) error {
270 return d.wrapTransaction(func(tx *sql.Tx) error {
271 _, err := tx.Exec(sqlUpdateUserAdmin, admin, user.ID)
272 return err
273 })
274}
275
276// AddUserPublicKey adds a new user public key.
277func (d *Sqlite) AddUserPublicKey(user *types.User, key string) error {
278 return d.wrapTransaction(func(tx *sql.Tx) error {
279 _, err := tx.Exec(sqlInsertPublicKey, user.ID, key)
280 return err
281 })
282}
283
284// DeleteUserPublicKey deletes a user public key.
285func (d *Sqlite) DeleteUserPublicKey(id int) error {
286 return d.wrapTransaction(func(tx *sql.Tx) error {
287 _, err := tx.Exec(sqlDeletePublicKey, id)
288 return err
289 })
290}
291
292// GetUserPublicKeys returns the user public keys.
293func (d *Sqlite) GetUserPublicKeys(user *types.User) ([]*types.PublicKey, error) {
294 keys := make([]*types.PublicKey, 0)
295 if err := d.wrapTransaction(func(tx *sql.Tx) error {
296 rows, err := tx.Query(sqlSelectUserPublicKeys, user.ID)
297 if err != nil {
298 return err
299 }
300 if err := rows.Err(); err != nil {
301 return err
302 }
303 defer rows.Close()
304 for rows.Next() {
305 var k types.PublicKey
306 if err := rows.Scan(&k.ID, &k.UserID, &k.PublicKey, &k.CreatedAt, &k.UpdatedAt); err != nil {
307 return err
308 }
309 keys = append(keys, &k)
310 }
311 return nil
312 }); err != nil {
313 return nil, err
314 }
315 return keys, nil
316}
317
318// AddRepo adds a new repo.
319func (d *Sqlite) AddRepo(name, projectName, description string, isPrivate bool) error {
320 name = strings.ToLower(name)
321 return d.wrapTransaction(func(tx *sql.Tx) error {
322 _, err := tx.Exec(sqlInsertRepo, name, projectName, description, isPrivate)
323 return err
324 })
325}
326
327// DeleteRepo deletes a repo.
328func (d *Sqlite) DeleteRepo(name string) error {
329 name = strings.ToLower(name)
330 return d.wrapTransaction(func(tx *sql.Tx) error {
331 _, err := tx.Exec(sqlDeleteRepoWithName, name)
332 return err
333 })
334}
335
336// GetRepo returns a repo by name.
337func (d *Sqlite) GetRepo(name string) (*types.Repo, error) {
338 name = strings.ToLower(name)
339 var r types.Repo
340 if err := d.wrapTransaction(func(tx *sql.Tx) error {
341 rows := tx.QueryRow(sqlSelectRepoByName, name)
342 if err := rows.Scan(&r.ID, &r.Name, &r.ProjectName, &r.Description, &r.Private, &r.CreatedAt, &r.UpdatedAt); err != nil {
343 return err
344 }
345 if err := rows.Err(); err != nil {
346 return err
347 }
348 return nil
349 }); err != nil {
350 return nil, err
351 }
352 return &r, nil
353}
354
355// SetRepoProjectName sets the repo project name.
356func (d *Sqlite) SetRepoProjectName(name string, projectName string) error {
357 name = strings.ToLower(name)
358 return d.wrapTransaction(func(tx *sql.Tx) error {
359 _, err := tx.Exec(sqlUpdateRepoProjectNameByName, projectName, name)
360 return err
361 })
362}
363
364// SetRepoDescription sets the repo description.
365func (d *Sqlite) SetRepoDescription(name string, description string) error {
366 name = strings.ToLower(name)
367 return d.wrapTransaction(func(tx *sql.Tx) error {
368 _, err := tx.Exec(sqlUpdateRepoDescriptionByName, description,
369 name)
370 return err
371 })
372}
373
374// SetRepoPrivate sets the repo private.
375func (d *Sqlite) SetRepoPrivate(name string, private bool) error {
376 name = strings.ToLower(name)
377 return d.wrapTransaction(func(tx *sql.Tx) error {
378 _, err := tx.Exec(sqlUpdateRepoPrivateByName, private, name)
379 return err
380 })
381}
382
383// AddRepoCollab adds a new repo collaborator.
384func (d *Sqlite) AddRepoCollab(repo *types.Repo, user *types.User) error {
385 return d.wrapTransaction(func(tx *sql.Tx) error {
386 _, err := tx.Exec(sqlInsertCollab, repo.ID, user.ID)
387 return err
388 })
389}
390
391// DeleteRepoCollab deletes a repo collaborator.
392func (d *Sqlite) DeleteRepoCollab(userID int, repoID int) error {
393 return d.wrapTransaction(func(tx *sql.Tx) error {
394 _, err := tx.Exec(sqlDeleteCollab, repoID, userID)
395 return err
396 })
397}
398
399// ListRepoCollabs returns a list of repo collaborators.
400func (d *Sqlite) ListRepoCollabs(repo *types.Repo) ([]*types.User, error) {
401 collabs := make([]*types.User, 0)
402 if err := d.wrapTransaction(func(tx *sql.Tx) error {
403 rows, err := tx.Query(sqlSelectRepoCollabs, repo.ID)
404 if err != nil {
405 return err
406 }
407 if err := rows.Err(); err != nil {
408 return err
409 }
410 defer rows.Close()
411 for rows.Next() {
412 var c types.User
413 if err := rows.Scan(&c.ID, &c.Name, &c.Login, &c.Email, &c.Admin, &c.CreatedAt, &c.UpdatedAt); err != nil {
414 return err
415 }
416 collabs = append(collabs, &c)
417 }
418 return nil
419 }); err != nil {
420 return nil, err
421 }
422 return collabs, nil
423}
424
425// WrapTransaction runs the given function within a transaction.
426func (d *Sqlite) wrapTransaction(f func(tx *sql.Tx) error) error {
427 ctx, cancel := context.WithTimeout(context.Background(), time.Second*5)
428 defer cancel()
429 tx, err := d.db.BeginTx(ctx, nil)
430 if err != nil {
431 log.Printf("error starting transaction: %s", err)
432 return err
433 }
434 for {
435 err = f(tx)
436 if err != nil {
437 serr, ok := err.(*sqlite.Error)
438 if ok && serr.Code() == sqlitelib.SQLITE_BUSY {
439 continue
440 }
441 log.Printf("error in transaction: %s", err)
442 return err
443 }
444 err = tx.Commit()
445 if err != nil {
446 log.Printf("error committing transaction: %s", err)
447 return err
448 }
449 break
450 }
451 return nil
452}