0 package db
1
2 import (
3 "database/sql"
4 "errors"
5 "gemigit/config"
6 "log"
7 "os"
8 "strconv"
9 "time"
10
11 _ "github.com/mattn/go-sqlite3"
12 )
13
14 type Repo struct {
15 RepoID int
16 UserID int
17 Username string
18 Name string
19 Date int
20 IsPublic bool
21 Description string
22 }
23
24 type User struct {
25 ID int
26 Name string
27 Description string
28 Registration int
29 Connection time.Time
30 Signature string
31 }
32
33 var users = make(map[string]User)
34
35 func userAlreadyExist(username string) (bool, error) {
36 rows, err := db.Query("select * from user WHERE UPPER(name) LIKE UPPER(?)", username)
37 if err != nil {
38 return true, err
39 }
40 defer rows.Close()
41 if rows.Next() {
42 return true, nil
43 }
44 return false, nil
45 }
46
47 func (user *User) repoAlreadyExist(repo string) (bool, error) {
48 rows, err := db.Query("SELECT * FROM repo WHERE UPPER(name) LIKE UPPER(?) AND UPPER(userID) LIKE UPPER(?)", repo, user.ID)
49 if err != nil {
50 return true, err
51 }
52 defer rows.Close()
53 if rows.Next() {
54 return true, nil
55 }
56 return false, nil
57 }
58
59 func DisconnectTimeout() {
60 for k, v := range users {
61 if time.Now().Unix()-v.Connection.Unix() > int64(config.Cfg.Gemigit.AuthTimeout) {
62 delete(users, k)
63 }
64 }
65 }
66
67 var db *sql.DB
68
69 func Init(path string) error {
70
71 new := false
72 file, err := os.Open(path)
73 if os.IsNotExist(err) {
74 file, err := os.Create(path)
75 if err != nil {
76 return err
77 }
78 file.Close()
79 log.Println("Creating database " + path)
80 new = true
81 } else {
82 file.Close()
83 log.Println("Loading database " + path)
84 }
85
86 db, err = sql.Open("sqlite3", path)
87 if err != nil {
88 return err
89 }
90 if new {
91 return createTable(db)
92 }
93 return nil
94 }
95
96 func Close() error {
97 return db.Close()
98 }
99
100 func createTable(db *sql.DB) error {
101 createUserTable := `CREATE TABLE user (
102 "userID" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
103 "name" TEXT UNIQUE,
104 "password" TEXT,
105 "description" TEXT DEFAULT "",
106 "creation" INTEGER
107 );`
108
109 createRepoTable := `CREATE TABLE repo (
110 "repoID" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
111 "userID" integer,
112 "name" TEXT,
113 "description" TEXT DEFAULT "",
114 "creation" INTEGER,
115 "public" INTEGER DEFAULT 0
116 );`
117
118 _, err := db.Exec(createUserTable)
119 if err != nil {
120 return err
121 }
122 log.Println("Users table created")
123 _, err = db.Exec(createRepoTable)
124 if err != nil {
125 return err
126 }
127 log.Println("Repositories table created")
128 return nil
129 }
130
131 func CheckAuth(username string, password string) (bool, error) {
132 rows, err := db.Query("select name, password from user WHERE UPPER(name) LIKE UPPER(?)", username)
133 if err != nil {
134 return false, err
135 }
136 defer rows.Close()
137 if rows.Next() {
138 var dPassword string
139 var dName string
140 err = rows.Scan(&dName, &dPassword)
141 if err != nil {
142 return false, err
143 }
144 if checkPassword(password, dPassword) {
145 return true, nil
146 }
147 }
148 return false, nil
149 }
150
151 func Login(username string, password string, signature string) (bool, error) {
152 rows, err := db.Query("select userID, name, description, creation, password from user WHERE UPPER(name) LIKE UPPER(?)", username)
153 if err != nil {
154 return false, err
155 }
156 defer rows.Close()
157 if rows.Next() {
158 var dID int
159 var dName string
160 var dDescription string
161 var dCreation int
162 var dPassword string
163 err = rows.Scan(&dID, &dName, &dDescription, &dCreation, &dPassword)
164 if err != nil {
165 return false, err
166 }
167 if checkPassword(password, dPassword) {
168 users[signature] = User{ID: dID, Name: dName, Description: dDescription, Registration: dCreation, Connection: time.Now(), Signature: signature}
169 return true, nil
170 }
171 }
172 return false, nil
173 }
174
175 func Register(username string, password string) error {
176
177 if isValid, err := isPasswordValid(password); !isValid {
178 return err
179 }
180
181 if isValid, err := isNameValid(username); !isValid {
182 return err
183 }
184
185 if exist, err := userAlreadyExist(username); exist || err != nil {
186 if err != nil {
187 return err
188 }
189 return errors.New("this name is already taken")
190 }
191
192 hash, err := hashPassword(password)
193 if err != nil {
194 return err
195 }
196
197 _, err = db.Exec("insert into user(name,password,creation) VALUES(?,?,strftime('%!s(MISSING)', 'now'));", username, hash)
198 if err != nil {
199 return err
200 }
201
202 return nil
203 }
204
205 func (user User) CreateRepo(repo string, signature string) error {
206 if err := user.VerifySignature(signature); err != nil {
207 return err
208 }
209
210 if isValid, err := isRepoNameValid(repo); !isValid {
211 return err
212 }
213
214 b, err := user.repoAlreadyExist(repo)
215 if err != nil {
216 return err
217 }
218 if b {
219 return errors.New("repo with the same name already exist")
220 }
221
222 _, err = db.Exec("insert into repo(userID,name,creation,public,description) VALUES(?,?,strftime('%!s(MISSING)', 'now'),0,\"\")", user.ID, repo)
223 if err != nil {
224 return err
225 }
226
227 return nil
228 }
229
230 func (user User) DeleteRepo(repo string, signature string) error {
231 if err := user.VerifySignature(signature); err != nil {
232 return err
233 }
234 statement, err := db.Exec("delete FROM repo WHERE name=? AND userID=?", repo, user.ID)
235 if err != nil {
236 return err
237 }
238 rows, err := statement.RowsAffected()
239 if err != nil {
240 return err
241 }
242 if rows != 1 {
243 return errors.New(strconv.Itoa(int(rows)) + " deleted instead of only one")
244 }
245 return nil
246 }
247
248 func GetUser(signature string) (User, bool) {
249 user, b := users[signature]
250 return user, b
251 }
252
253 func GetPublicUser(name string) (User, error) {
254 rows, err := db.Query("select userID, name, description, creation from user WHERE UPPER(name) LIKE UPPER(?)", name)
255 if err != nil {
256 return User{}, err
257 }
258 defer rows.Close()
259 if rows.Next() {
260 var dID int
261 var dName string
262 var dDescription string
263 var dCreation int
264 err = rows.Scan(&dID, &dName, &dDescription, &dCreation)
265 if err != nil {
266 return User{}, err
267 }
268 return User{ID: dID, Name: dName, Description: dDescription, Registration: dCreation}, nil
269 }
270 return User{}, errors.New(name + ", user not found")
271 }
272
273 func (user User) GetRepo(reponame string) (Repo, error) {
274 rows, err := db.Query("SELECT repoID, userID, name, creation, public, description FROM repo WHERE UPPER(name) LIKE UPPER(?) AND userID=?", reponame, user.ID)
275 if err != nil {
276 return Repo{}, err
277 }
278 defer rows.Close()
279 if rows.Next() {
280 var ID int
281 var uID int
282 var name string
283 var date int
284 var public bool
285 var description string
286 err = rows.Scan(&ID, &uID, &name, &date, &public, &description)
287 if err != nil {
288 return Repo{}, err
289 }
290 return Repo{ID, uID, user.Name, name, date, public, description}, nil
291 }
292 return Repo{}, errors.New("No repository called " + reponame + " by user " + user.Name)
293 }
294
295 func (user User) GetRepos(onlyPublic bool) ([]Repo, error) {
296 var rows *sql.Rows
297 var err error
298 query := "SELECT repoID, userID, name, creation, public, description FROM repo WHERE userID=?"
299 if onlyPublic {
300 query += " AND public=1"
301 }
302 rows, err = db.Query(query, user.ID)
303 if err != nil {
304 return nil, err
305 }
306 defer rows.Close()
307 var repos []Repo
308 for rows.Next() {
309 var ID int
310 var uID int
311 var name string
312 var date int
313 var public bool
314 var description string
315 err = rows.Scan(&ID, &uID, &name, &date, &public, &description)
316 if err != nil {
317 return nil, err
318 }
319 repos = append(repos, Repo{ID, uID, user.Name, name, date, public, description})
320 }
321 return repos, nil
322 }
323
324 func GetPublicRepo() ([]Repo, error) {
325 rows, err := db.Query("SELECT b.name, a.repoID, a.userID, a.name, a.creation, a.public, a.description FROM repo a INNER JOIN user b ON a.userID=b.userID WHERE a.public=1")
326 if err != nil {
327 return nil, err
328 }
329 defer rows.Close()
330 var repos []Repo
331 for rows.Next() {
332 var username string
333 var ID int
334 var uID int
335 var name string
336 var date int
337 var public bool
338 var description string
339 err = rows.Scan(&username, &ID, &uID, &name, &date, &public, &description)
340 if err != nil {
341 return nil, err
342 }
343 repos = append(repos, Repo{ID, uID, username, name, date, public, description})
344 }
345 return repos, nil
346 }
347
348 func IsRepoPublic(repo string, username string) (bool, error) {
349 rows, err := db.Query("SELECT a.public FROM repo a INNER JOIN user b ON a.userID=b.userID WHERE UPPER(a.name) LIKE UPPER(?) AND UPPER(b.name) LIKE UPPER(?)", repo, username)
350 if err != nil {
351 return false, err
352 }
353 defer rows.Close()
354 if rows.Next() {
355 var public bool
356 err = rows.Scan(&public)
357 if err != nil {
358 return false, err
359 }
360 return public, nil
361 }
362 return false, errors.New("No repository called " + repo + " by user " + username)
363 }
364
365 func (user User) TogglePublic(repo string, signature string) error {
366 if err := user.VerifySignature(signature); err != nil {
367 return err
368 }
369 b, err := IsRepoPublic(repo, user.Name)
370 if err != nil {
371 return err
372 }
373 i := 1
374 if b {
375 i = 0
376 }
377 _, err = db.Exec("UPDATE repo SET public=? WHERE UPPER(name) LIKE UPPER(?) AND userID=?", i, repo, user.ID)
378 if err != nil {
379 return err
380 }
381 return nil
382 }
383
384 func (user *User) VerifySignature(signature string) error {
385 if user.Signature != signature {
386 return errors.New("wrong signature")
387 }
388 if users[signature].ID != user.ID {
389 return errors.New("signature doesn't match the user")
390 }
391 return nil
392 }
393
394 func ChangePassword(username string, password string) error {
395 b, err := isPasswordValid(password)
396 if err != nil {
397 return err
398 }
399 if !b {
400 return errors.New("invalid password")
401 }
402 hPassword, err := hashPassword(password)
403 if err != nil {
404 return err
405 }
406 statement, err := db.Exec("UPDATE user SET password=? WHERE UPPER(name) LIKE UPPER(?)", hPassword, username)
407 if err != nil {
408 return err
409 }
410 rows, err := statement.RowsAffected()
411 if err != nil {
412 return err
413 }
414 if rows < 1 {
415 return errors.New("password not changed")
416 }
417 return nil
418 }
419
420 func (user User) ChangePassword(password string, signature string) error {
421 if err := user.VerifySignature(signature); err != nil {
422 return err
423 }
424 return ChangePassword(user.Name, password)
425 }
426
427 func (user User) ChangeDescription(description string, signature string) error {
428 if err := user.VerifySignature(signature); err != nil {
429 return err
430 }
431 statement, err := db.Exec("UPDATE user SET description=? WHERE UPPER(name) LIKE UPPER(?)", description, user.Name)
432 if err != nil {
433 return err
434 }
435 rows, err := statement.RowsAffected()
436 if err != nil {
437 return err
438 }
439 if rows < 1 {
440 return errors.New("no description changed")
441 }
442 u, b := users[signature]
443 if !b {
444 return errors.New("invalid signature detected")
445 }
446 u.Description = description
447 users[signature] = u
448 return nil
449 }
450
451 func (user User) Disconnect(signature string) error {
452 if err := user.VerifySignature(signature); err != nil {
453 return err
454 }
455 delete(users, signature)
456 return nil
457 }
458
459 func (user User) ChangeRepoName(name string, newname string, signature string) error {
460 if err := user.VerifySignature(signature); err != nil {
461 return err
462 }
463 b, err := isRepoNameValid(newname)
464 if err != nil {
465 return err
466 }
467 if !b {
468 return errors.New("invalid name")
469 }
470 statement, err := db.Exec("UPDATE repo SET name=? WHERE UPPER(name) LIKE UPPER(?) AND userID=?", newname, name, user.ID)
471 if err != nil {
472 return err
473 }
474 rows, err := statement.RowsAffected()
475 if err != nil {
476 return err
477 }
478 if rows < 1 {
479 return errors.New("failed to change the repository name")
480 }
481 return nil
482 }
483
484 func (user User) ChangeRepoDesc(name string, newdesc string) error {
485 statement, err := db.Exec("UPDATE repo SET description=? WHERE UPPER(name) LIKE UPPER(?) AND userID=?", newdesc, name, user.ID)
486 if err != nil {
487 return err
488 }
489 rows, err := statement.RowsAffected()
490 if err != nil {
491 return err
492 }
493 if rows < 1 {
494 return errors.New("failed to change the repository description")
495 }
496 return nil
497 }
498
499 func GetRepoDesc(name string, username string) (string, error) {
500 rows, err := db.Query("SELECT a.description FROM repo a INNER JOIN user b ON a.userID=b.userID WHERE UPPER(a.name) LIKE UPPER(?) AND UPPER(b.name) LIKE UPPER(?)", name, username)
501 if err != nil {
502 return "", err
503 }
504 defer rows.Close()
505 if rows.Next() {
506 var description string
507 err = rows.Scan(&description)
508 if err != nil {
509 return "", err
510 }
511 return description, nil
512 }
513 return "", errors.New("No repository called " + name + " by user " + username)
514 }
515
516 func (user *User) UpdateDescription() error {
517 rows, err := db.Query("select description from user WHERE userID=?", user.ID)
518 if err != nil {
519 return err
520 }
521 defer rows.Close()
522 if rows.Next() {
523 var dDescription string
524 err = rows.Scan(&dDescription)
525 if err != nil {
526 return err
527 }
528 user.Description = dDescription
529 }
530 users[user.Signature] = *user
531 return nil
532 }
533