query.go 6.36 KB
Newer Older
Julien Schröter's avatar
Julien Schröter committed
1 2 3
package pool

import (
4
	"database/sql"
Frederik Wegner's avatar
Frederik Wegner committed
5
	"errors"
Julien Schröter's avatar
Julien Schröter committed
6 7
	"fmt"

8 9
	"gitlab.akamu.de/akamu/game-server-go/dbhandler"
	"gitlab.akamu.de/akamu/game-server-go/schemas"
Julien Schröter's avatar
Julien Schröter committed
10 11
)

Frederik Wegner's avatar
Frederik Wegner committed
12 13
var ErrorInvalidPools = errors.New("The selection of pools was invalid.")

14 15 16
// Must match the value in endpoint/duel/query.go
const QuestionsPerRound = 2

Julien Schröter's avatar
Julien Schröter committed
17
type PoolQuery interface {
18
	Select(userID uint32) ([]schemas.PoolSchema, error)
19
	SelectUserPools(userID, secondaryUser uint32) ([]uint32, error)
Frederik Wegner's avatar
Frederik Wegner committed
20
	PatchUserPools(userID uint32, selection []uint32) error
Julien Schröter's avatar
Julien Schröter committed
21 22 23 24 25
}

type MySQLPoolQuery struct{}

// Select returns a list of all available playable pools.
26
func (m MySQLPoolQuery) Select(userID uint32) ([]schemas.PoolSchema, error) {
27
	queryString := "SELECT `idpool`, `name`, `shortform`, `description`,`image`, `idpool` IN (SELECT DISTINCT pool from user_pool WHERE user=?) AS selected FROM `pool` WHERE `idpool` IN (SELECT `pool` FROM `pool_question` INNER JOIN `question` ON (`idquestion`=`pool_question`.`question`) WHERE `published`=1 AND (`university` IS NULL OR `university`=(SELECT `university` FROM `user` WHERE `iduser`=?)) GROUP BY `pool` HAVING COUNT(DISTINCT `question`) >= ?)"
28

Julien Schröter's avatar
Julien Schröter committed
29 30 31 32 33 34 35 36 37 38 39 40
	db, err := dbhandler.GetDBConnection()

	// Check for errors opening the database
	if err != nil {
		return nil, fmt.Errorf("Could not open database connection. " + err.Error())
	}

	// Check for errors connecting the database
	if db.Ping() != nil {
		return nil, fmt.Errorf("Could not open database connection.")
	}

41
	// Fetch pools from database
42
	rows, errRows := db.Query(queryString, userID, userID, QuestionsPerRound)
Frederik Wegner's avatar
Frederik Wegner committed
43

44 45 46
	// Check whether query failed
	if errRows != nil {
		return nil, fmt.Errorf("Unable to fetch pools." + errRows.Error())
47 48
	}

49 50 51 52 53 54 55 56 57 58 59 60 61
	defer rows.Close()

	// Generate output slice
	var pools []schemas.PoolSchema

	for rows.Next() {
		var p schemas.PoolSchema

		selected := false
		p.Selected = &selected

		var selectedInt int

62 63
		var nullDescription sql.NullString

64
		// Get pool from result
65
		errScan := rows.Scan(&(p.ID), &(p.Name), &(p.Code), &nullDescription, &(p.Image), &selectedInt)
66 67
		if errScan != nil {
			return nil, fmt.Errorf("failed to fetch pools from result." + errScan.Error())
68
		}
69

70 71
		p.Description = nullDescription.String

72 73 74 75 76
		if selectedInt == 1 {
			*(p.Selected) = true
		}

		pools = append(pools, p)
77 78
	}

79 80 81 82
	return pools, nil
}

// Select returns a list of all available playable pools.
83
func (m MySQLPoolQuery) SelectUserPools(userID, secondaryUser uint32) ([]uint32, error) {
84 85 86 87 88 89
	db, err := dbhandler.GetDBConnection()
	if err != nil {
		return nil, fmt.Errorf("failed to get database instance: %v", err)
	}

	// Fetch pools from database
90
	rows, err := db.Query("SELECT DISTINCT `pool` FROM `user_pool` WHERE `user`=? AND `pool` IN (SELECT `pool` FROM `pool_question` INNER JOIN `question` ON (`idquestion`=`pool_question`.`question`) WHERE `published`=1 AND (`university` IS NULL OR `university`= ALL (SELECT `university` FROM `user` WHERE `iduser`=? OR `iduser`=?)) GROUP BY `pool` HAVING COUNT(DISTINCT `question`) >= ?)", userID, userID, secondaryUser, QuestionsPerRound)
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110

	// Check whether query failed
	if err != nil {
		return nil, fmt.Errorf("failed fetching pools from database: %v", err)
	}

	defer rows.Close()

	// Generate output
	var pools []uint32

	for rows.Next() {
		var pool uint32
		if err := rows.Scan(&pool); err != nil {
			return nil, fmt.Errorf("failed aggregating pools from database: %v", err)
		}

		pools = append(pools, pool)
	}

Frederik Wegner's avatar
Frederik Wegner committed
111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
	return pools, nil
}

// PatchUserPools insert relations into user_pool for all pools in the array `selection`.
// Relations to pools that are not in `selection` are deleted.
func (m MySQLPoolQuery) PatchUserPools(userID uint32, selection []uint32) error {
	db, err := dbhandler.GetDBConnection()

	// Check for errors opening the database
	if err != nil {
		return fmt.Errorf("Could not open database connection. " + err.Error())
	}

	tx, errTx := db.Begin()
	if errTx != nil {
		return errTx
	}

	// get complete list of pools as reference
	allPools, errGetAll := m.getAllPools(db)
	if errGetAll != nil {
		return fmt.Errorf("Could not query the complete pool list. %s", errGetAll.Error())
	}

	// compose selection map and init false
	selectionMap := make(map[uint32]bool)
	for _, pool := range allPools {
		selectionMap[pool.ID] = false
	}
	// check if all pools the user wants to select are actually present
	for _, poolID := range selection {
		if _, getOk := selectionMap[poolID]; !getOk {
			return ErrorInvalidPools
		} else {
			// set true in selectionMap, meaning this pool is selected
			selectionMap[poolID] = true
		}
	}

	for poolID, selected := range selectionMap {
		if selected {
			// insert relation
			if _, errExec := tx.Exec("INSERT INTO `user_pool` (`user`, `pool`) VALUES (?,?)", userID, poolID); errExec != nil {
				return errExec
			}
		} else {
			// delete relation
			if _, errExec := tx.Exec("DELETE FROM `user_pool` WHERE `user`=? AND `pool`=?;", userID, poolID); errExec != nil {
				return errExec
			}
		}
	}

	if errCommit := tx.Commit(); errCommit != nil {
		return errCommit
	}
	return nil
}

func (MySQLPoolQuery) getAllPools(db dbhandler.DBAccess) ([]schemas.PoolSchema, error) {
Julien Schröter's avatar
Julien Schröter committed
171
	// Fetch available pools from database
172
	rows, errRows := db.Query("SELECT `idpool`, `name`, `shortform`, `image` FROM `pool`")
Julien Schröter's avatar
Julien Schröter committed
173 174 175 176 177 178 179 180 181 182 183 184 185 186 187

	// Check whether query failed
	if errRows != nil {
		return nil, fmt.Errorf("Unable to fetch pools." + errRows.Error())
	}

	defer rows.Close()

	// Generate output slice
	var pools []schemas.PoolSchema

	for rows.Next() {
		var p schemas.PoolSchema

		// Get pool from result
188
		errScan := rows.Scan(&(p.ID), &(p.Name), &(p.Code), &(p.Image))
Julien Schröter's avatar
Julien Schröter committed
189 190 191 192 193 194 195 196 197
		if errScan != nil {
			return nil, fmt.Errorf("Failed to fetch pools from result." + errScan.Error())
		}

		pools = append(pools, p)
	}

	return pools, nil
}
198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226

func (MySQLPoolQuery) getUserSelection(db dbhandler.DBAccess, userID uint32) ([]uint32, error) {
	// Fetch pool_user entries for user from database
	rows, errRows := db.Query("SELECT DISTINCT `pool` FROM `user_pool` WHERE `user`=?", userID)

	// Check whether query failed
	if errRows != nil {
		return nil, fmt.Errorf("Unable to fetch user_pools." + errRows.Error())
	}

	defer rows.Close()

	// Generate output slice
	var selection []uint32

	for rows.Next() {
		var poolID uint32

		// Get pool from result
		errScan := rows.Scan(&poolID)
		if errScan != nil {
			return nil, fmt.Errorf("Failed to fetch pools from result." + errScan.Error())
		}

		selection = append(selection, poolID)
	}

	return selection, nil
}