package main import ( "database/sql" "fmt" "log" _ "github.com/lib/pq" ) // setup the Database func setupDatabase() (*sql.DB, error) { connStr := "host=postgresql port=5432 user=postgres dbname=postgres sslmode=disable" // open a connection to the database db, err := sql.Open("postgres", connStr) if err != nil { log.Fatal(err) } // ping the database err = db.Ping() if err != nil { return nil, err } // create a challenges table if it does not exist yet err = dbCreateTableIfNotExist(db) if err != nil { log.Println(err) } return db, nil } // 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") 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 = '$1', description = '$2', flag = '$3', container = '$4', category = '$5', points = $6, static = $7 WHERE uuid::text = '$8'") err := db.QueryRow(query, updatedChallenge.Name, updatedChallenge.Description, updatedChallenge.Flag, updatedChallenge.Container, updatedChallenge.Category, updatedChallenge.Points, updatedChallenge.Static, updatedChallenge.UUID) if err != nil { return fmt.Errorf("could not edit the challenge: %s", err) } 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= '$1'") challenge := Challenge{} // execute the query storing the values in the challenge struct defined above err := db.QueryRow(query, uuid).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 } func dbDeleteChallengeByUUID(uuid string) error { query := fmt.Sprintf("DELETE FROM challenges WHERE uuid::text = '%s'") err := db.QueryRow(query, uuid) if err != nil { return fmt.Errorf("could not delete the challenge: %s", err) } return nil } func dbCreateTableIfNotExist(db *sql.DB) error { log.Println("Creating a table in case it doesn't exist") query := `CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE TABLE public.challenges ( uuid uuid NOT NULL DEFAULT uuid_generate_v4(), name character varying COLLATE pg_catalog."default" NOT NULL, description character varying COLLATE pg_catalog."default", flag character varying COLLATE pg_catalog."default", container character varying COLLATE pg_catalog."default", category character varying COLLATE pg_catalog."default", points integer, static boolean NOT NULL, CONSTRAINT challenges_pkey PRIMARY KEY (uuid) )` _, err := db.Exec(query) if err != nil { return err } return nil }