You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

database.go 3.0KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778
  1. package main
  2. import (
  3. "database/sql"
  4. _ "github.com/mattn/go-sqlite3"
  5. )
  6. var statements = map[string]*sql.Stmt{}
  7. func initDB(filepath string) *sql.DB {
  8. db, err := sql.Open("sqlite3", filepath)
  9. // Here we check for any db errors then exit
  10. if err != nil || db == nil {
  11. panic(err)
  12. }
  13. migrate(db)
  14. statements["getTasks"] = mustPrepare(db, "SELECT id, name, points, maxPoints, presentations FROM tasks WHERE user = ? AND class = ?")
  15. statements["addTask"] = mustPrepare(db, "INSERT INTO tasks(user, class, name, points, maxPoints, presentations) VALUES(?, ?, ?, ?, ?, ?)")
  16. statements["updateTask"] = mustPrepare(db, "UPDATE tasks SET name = ?, points = ?, maxPoints = ?, presentations = ? WHERE user = ? AND class = ? AND id = ?")
  17. statements["deleteTask"] = mustPrepare(db, "DELETE FROM tasks WHERE user = ? AND class = ? AND id = ?")
  18. statements["getCourses"] = mustPrepare(db, "SELECT tasks.class, tasks.user, SUM(tasks.points), SUM(tasks.maxPoints), limits.minimum, limits.minimumType FROM tasks left JOIN limits ON tasks.user = limits.user and tasks.class = limits.class WHERE tasks.user = ? GROUP BY tasks.class")
  19. statements["setPerc"] = mustPrepare(db, "REPLACE INTO limits (id, class, user, minimum, minimumType, presentations) VALUES ((SELECT id FROM limits WHERE class = ? and user = ?), ?, ?, ?, ?, (SELECT presentations FROM limits WHERE class = ? and user = ?))")
  20. statements["getPerc"] = mustPrepare(db, "SELECT minimum, minimumType FROM limits WHERE class = ? and user = ?")
  21. statements["setPres"] = mustPrepare(db, "REPLACE INTO limits (id, class, user, minimum, minimumType, presentations) VALUES ((SELECT id FROM limits WHERE class = ? and user = ?), ?, ?, (SELECT minimum FROM limits WHERE class = ? and user = ?), (SELECT minimumType FROM limits WHERE class = ? and user = ?), ?)")
  22. statements["getPres"] = mustPrepare(db, "SELECT presentations FROM limits WHERE class = ? and user = ?")
  23. statements["delCourse"] = mustPrepare(db, "DELETE FROM tasks WHERE user = ? AND class = ?")
  24. statements["delLimits"] = mustPrepare(db, "DELETE FROM limits WHERE user = ? AND class = ?")
  25. return db
  26. }
  27. func migrate(db *sql.DB) {
  28. sql := `
  29. CREATE TABLE IF NOT EXISTS tasks(
  30. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  31. class VARCHAR NOT NULL,
  32. user VARCHAR NOT NULL,
  33. name VARCHAR NOT NULL,
  34. points INTEGER NOT NULL,
  35. maxPoints INTEGER NOT NULL,
  36. presentations INTEGER NOT NULL
  37. );`
  38. _, err := db.Exec(sql)
  39. // Exit if something goes wrong with our SQL statement above
  40. if err != nil {
  41. panic(err)
  42. }
  43. sql2 := `
  44. CREATE TABLE IF NOT EXISTS limits(
  45. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  46. class VARCHAR NOT NULL,
  47. user VARCHAR NOT NULL,
  48. minimum INTEGER NOT NULL DEFAULT 0,
  49. minimumType VARCHAR(32) NOT NULL DEFAULT 'none',
  50. presentations INTEGER NOT NULL DEFAULT 0
  51. );`
  52. _, err2 := db.Exec(sql2)
  53. if err2 != nil {
  54. panic(err2)
  55. }
  56. // TODO: Use goose (https://bitbucket.org/liamstask/goose) for migrations
  57. }
  58. func mustPrepare(db *sql.DB, sql string) *sql.Stmt {
  59. stmt, err := db.Prepare(sql)
  60. if err != nil {
  61. panic(err)
  62. }
  63. return stmt
  64. }