atsebay.t/db.go

package main

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"os"
	"time"
)

var db *sql.DB

func DSNGenerator() string {
	db_user := "atsebayt"
	db_password := "atsebayt"
	db_host := ""
	db_db := "atsebayt"

	if v, exists := os.LookupEnv("MYSQL_HOST"); exists {
		db_host = v
	}
	if v, exists := os.LookupEnv("MYSQL_PASSWORD"); exists {
		db_password = v
	} else if v, exists := os.LookupEnv("MYSQL_ROOT_PASSWORD"); exists {
		db_user = "root"
		db_password = v
	}
	if v, exists := os.LookupEnv("MYSQL_USER"); exists {
		db_user = v
	}
	if v, exists := os.LookupEnv("MYSQL_DATABASE"); exists {
		db_db = v
	}

	return db_user + ":" + db_password + "@" + db_host + "/" + db_db
}

func DBInit(dsn string) (err error) {
	if db, err = sql.Open("mysql", dsn+"?parseTime=true&foreign_key_checks=1"); err != nil {
		return
	}

	_, err = db.Exec(`SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';`)
	for i := 0; err != nil && i < 5; i += 1 {
		if _, err = db.Exec(`SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';`); err != nil && i <= 5 {
			log.Println("An error occurs when trying to connect to DB, will retry in 2 seconds: ", err)
			time.Sleep(2 * time.Second)
		}
	}

	return
}

func DBCreate() (err error) {
	if _, err = db.Exec(`
CREATE TABLE IF NOT EXISTS users(
  id_user INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  login VARCHAR(255) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  firstname VARCHAR(255) NOT NULL,
  lastname VARCHAR(255) NOT NULL,
  time TIMESTAMP NOT NULL,
  promo MEDIUMINT NOT NULL,
  groups VARCHAR(255) NOT NULL,
  is_admin BOOLEAN NOT NULL DEFAULT FALSE
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS user_sessions(
  id_session BLOB(255) NOT NULL,
  id_user INTEGER,
  time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  val TEXT NOT NULL DEFAULT '{}',
  FOREIGN KEY(id_user) REFERENCES users(id_user)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS user_keys(
  id_key INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_user INTEGER NOT NULL,
  type ENUM('pgp', 'ssh') NOT NULL,
  content TEXT NOT NULL,
  time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(id_user) REFERENCES users(id_user)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS surveys(
  id_survey INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_category INTEGER NOT NULL,
  title VARCHAR(255),
  promo MEDIUMINT NOT NULL,
  grp VARCHAR(255) NOT NULL,
  shown BOOLEAN NOT NULL DEFAULT FALSE,
  direct INTEGER DEFAULT NULL,
  corrected BOOLEAN NOT NULL DEFAULT FALSE,
  start_availability TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_availability TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(id_category) REFERENCES categories(id_category)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS survey_quests(
  id_question INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_survey INTEGER NOT NULL,
  title VARCHAR(255),
  description TEXT,
  placeholder VARCHAR(255),
  kind ENUM('none', 'text', 'int', 'ucq', 'mcq', 'list', 'list1', 'list2', 'list3', 'list4', 'list5', 'list6', 'list7', 'list8', 'list9') NOT NULL,
  FOREIGN KEY(id_survey) REFERENCES surveys(id_survey)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS survey_proposals(
  id_proposal INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_question INTEGER NOT NULL,
  label VARCHAR(255),
  FOREIGN KEY(id_question) REFERENCES survey_quests(id_question)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS survey_responses(
  id_response INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_question INTEGER NOT NULL,
  id_user INTEGER NOT NULL,
  answer TEXT NOT NULL,
  time_submit TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  score INTEGER,
  score_explanation TEXT,
  id_corrector INTEGER,
  time_scored TIMESTAMP NULL DEFAULT NULL,
  time_reported TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY(id_question) REFERENCES survey_quests(id_question),
  FOREIGN KEY(id_user) REFERENCES users(id_user),
  FOREIGN KEY(id_corrector) REFERENCES users(id_user)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS correction_templates(
  id_template INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_question INTEGER NOT NULL,
  label VARCHAR(255) NOT NULL,
  re VARCHAR(255) NOT NULL,
  score INTEGER,
  score_explanation TEXT,
  FOREIGN KEY(id_question) REFERENCES survey_quests(id_question)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS student_corrected(
  id_correction INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_user INTEGER NOT NULL,
  id_template INTEGER NOT NULL,
  FOREIGN KEY(id_user) REFERENCES users(id_user),
  FOREIGN KEY(id_template) REFERENCES correction_templates(id_template)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS survey_asks(
  id_ask INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_survey INTEGER NOT NULL,
  id_user INTEGER NOT NULL,
  date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  content TEXT NOT NULL,
  answered BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY(id_user) REFERENCES users(id_user),
  FOREIGN KEY(id_survey) REFERENCES surveys(id_survey)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS user_need_help(
  id_need_help INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_user INTEGER NOT NULL,
  date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  comment TEXT NOT NULL,
  date_treated TIMESTAMP NULL,
  FOREIGN KEY(id_user) REFERENCES users(id_user)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS works(
  id_work INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_category INTEGER NOT NULL,
  title VARCHAR(255),
  promo MEDIUMINT NOT NULL,
  grp VARCHAR(255) NOT NULL,
  shown BOOLEAN NOT NULL DEFAULT FALSE,
  description TEXT NOT NULL,
  tag VARCHAR(255) NOT NULL,
  submission_URL VARCHAR(255) NULL,
  gradation_repo VARCHAR(255) NULL,
  corrected BOOLEAN NOT NULL DEFAULT FALSE,
  start_availability TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_availability TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(id_category) REFERENCES categories(id_category)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS user_work_grades(
  id_gradation INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_user INTEGER NOT NULL,
  id_work INTEGER NOT NULL,
  date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  grade DECIMAL(5,2) NULL,
  comment TEXT NOT NULL,
  FOREIGN KEY(id_user) REFERENCES users(id_user),
  FOREIGN KEY(id_work) REFERENCES works(id_work)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS user_work_repositories(
  id_repository INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_user INTEGER NOT NULL,
  id_work INTEGER NOT NULL,
  uri VARCHAR(255) NOT NULL,
  secret BLOB NOT NULL,
  last_check TIMESTAMP NULL DEFAULT NULL,
  droneref VARCHAR(255) NOT NULL,
  last_tests TIMESTAMP NULL DEFAULT NULL,
  testsref VARCHAR(255) NOT NULL,
  FOREIGN KEY(id_user) REFERENCES users(id_user),
  FOREIGN KEY(id_work) REFERENCES works(id_work),
  UNIQUE one_repo_per_work (id_user, id_work)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS categories(
  id_category INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  label VARCHAR(255) NOT NULL,
  promo MEDIUMINT NOT NULL,
  expand BOOLEAN NOT NULL DEFAULT FALSE
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS survey_shared(
  id_share INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_survey INTEGER NOT NULL,
  secret BLOB NOT NULL,
  count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  FOREIGN KEY(id_survey) REFERENCES surveys(id_survey)
) DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE VIEW IF NOT EXISTS student_scores AS SELECT "survey" AS kind, T.id_user, T.id_survey AS id, Q.id_question, MAX(R.score) AS score FROM (SELECT DISTINCT R.id_user, S.id_survey FROM survey_responses R INNER JOIN survey_quests Q ON R.id_question = Q.id_question INNER JOIN surveys S ON Q.id_survey = S.id_survey) T LEFT OUTER JOIN survey_quests Q ON T.id_survey = Q.id_survey LEFT OUTER JOIN survey_responses R ON R.id_user = T.id_user AND Q.id_question = R.id_question GROUP BY id_user, kind, id, id_question UNION SELECT "work" AS kind, G.id_user, G.id_work AS id, 0 AS id_question, G.grade AS score FROM works W RIGHT OUTER JOIN user_work_grades G ON G.id_work = W.id_work GROUP BY id_user, kind, id, id_question;
`); err != nil {
		return err
	}
	if _, err := db.Exec(`
CREATE VIEW IF NOT EXISTS all_works AS SELECT "work" AS kind, id_work AS id, id_category, title, promo, grp, shown, NULL AS direct, submission_url, corrected, start_availability, end_availability FROM works UNION SELECT "survey" AS kind, id_survey AS id, id_category, title, promo, grp, shown, direct, NULL AS submission_url, corrected, start_availability, end_availability FROM surveys;
`); err != nil {
		return err
	}
	return
}

func DBClose() error {
	return db.Close()
}

func DBPrepare(query string) (*sql.Stmt, error) {
	return db.Prepare(query)
}

func DBQuery(query string, args ...interface{}) (*sql.Rows, error) {
	return db.Query(query, args...)
}

func DBExec(query string, args ...interface{}) (sql.Result, error) {
	return db.Exec(query, args...)
}

func DBQueryRow(query string, args ...interface{}) *sql.Row {
	return db.QueryRow(query, args...)
}