package main import ( "database/sql" "fmt" "log" _ "github.com/lib/pq" ) // setup the Database func setupDatabase() *sql.DB { connStr := "user=postgres dbname=postgres sslmode=disable" db, err := sql.Open("postgres", connStr) if err != nil { log.Fatal(err) } return db } // getNames gets the name of all the challenges func dbGetName(db *sql.DB) string { // get the current name of the challenges query := fmt.Sprintf("SELECT name FROM challenges WHERE points=200") var names string err := db.QueryRow(query).Scan(&names) if err != nil { log.Fatalf("[ E ] :", err) } return names } // getAllChallenges gets all the challenges from the server func dbGetAllChallenges() []Challenge { // build the query query := fmt.Sprintf("SELECT * FROM challenges") // Execute the query rows, err := db.Query(query) defer rows.Close() if err != nil { log.Printf("[ E ] getAllChallenges query: %v\n\t\t\t query: %s\n", err, query) return []Challenge{} } var challenges []Challenge // iterate over the returned rows for rows.Next() { var uuid, name, description, flag, container, category string var points int var static bool scanErr := rows.Scan(&uuid, &name, &description, &flag, &container, &category, &points, &static) if scanErr != nil { log.Printf("[ E ] scan error: %v", scanErr) return []Challenge{} } newChallenge := Challenge{ UUID: uuid, Name: name, Description: description, Flag: flag, Container: container, Category: category, Points: points, Static: static, } challenges = append(challenges, newChallenge) } return challenges } // dbNewChallenge inserts the given challenge into the database func dbNewChallenge(challenge Challenge) (string, error) { // build the query to be executed query := fmt.Sprintf("INSERT INTO challenges(name, description, flag, container, category, points, static) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING uuid") // execute the query with the challenge values given var uuid string err := db.QueryRow(query, challenge.Name, challenge.Description, challenge.Flag, challenge.Container, challenge.Category, challenge.Points, challenge.Static).Scan(&uuid) // handle errors if err != nil { return "", err } return uuid, nil } // editChallengeUUID edited the challenge with the given uuid using the values in the updatedChallenge func dbEditChallengeUUID(uuid string, updatedChallenge Challenge) error { query := fmt.Sprintf("UPDATE challenges SET name = '%s', description = '%s', flag = '%s', container = '%s', category = '%s', points = %d, static = %t WHERE uuid::text = '%s'", updatedChallenge.Name, updatedChallenge.Description, updatedChallenge.Flag, updatedChallenge.Container, updatedChallenge.Category, updatedChallenge.Points, updatedChallenge.Static, updatedChallenge.UUID) _, _ = db.Exec(query) return nil } // dbGetChallengeByUUID returns the challenge with the given UUID from the database func dbGetChallengeByUUID(uuid string) (Challenge, error) { // build the query to be executed query := fmt.Sprintf("SELECT uuid, name, description, flag, container, category, points, static FROM challenges WHERE uuid::text= '%s'", uuid) challenge := Challenge{} // execute the query storing the values in the challenge struct defined above err := db.QueryRow(query).Scan(&challenge.UUID, &challenge.Name, &challenge.Description, &challenge.Flag, &challenge.Container, &challenge.Category, &challenge.Points, &challenge.Static) if err != nil { return Challenge{}, err } return challenge, nil }