/
sql.go
184 lines (156 loc) · 4.22 KB
/
sql.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
package main
// sql execution support
import (
"errors"
"github.com/jmoiron/modl"
"github.com/stevedomin/termtable"
)
type Question struct {
Number int `json:"number"`
Text string `json:"text"`
Check func([][]interface{}) bool `json:"-"`
}
// compare to lists of strings as though they were a set
func setCompare(s1, s2 []string) bool {
if len(s1) != len(s2) {
return false
}
m := map[string]uint8{}
for _, s := range s1 {
m[s] = 0
}
for _, s := range s2 {
if _, ok := m[s]; !ok {
return false
}
}
return true
}
func stringResults(results [][]interface{}) ([]string, error) {
sres := make([]string, 0, len(results))
for _, res := range results {
if res == nil {
return sres, errors.New("Null encountered where string expected.")
}
sres = append(sres, res[0].(string))
}
return sres, nil
}
func NoopCheck(results [][]interface{}) bool {
return false
}
func CompareResults(results [][]interface{}, answers []string) bool {
if len(results) != len(answers) {
return false
}
if len(results[0]) != 1 {
return false
}
stringRes, err := stringResults(results)
if err != nil {
return false
}
return setCompare(stringRes, answers)
}
func Question1(results [][]interface{}) bool {
return CompareResults(results, Question1Answer)
}
func Question2(results [][]interface{}) bool {
return CompareResults(results, Question2Answer)
}
func Question3(results [][]interface{}) bool {
return CompareResults(results, Question3Answer)
}
func Question4(results [][]interface{}) bool {
return CompareResults(results, Question4Answer)
}
func Question5(results [][]interface{}) bool {
return CompareResults(results, Question5Answer)
}
func Question6(results [][]interface{}) bool {
if len(results) != len(Question6Answer) {
return false
}
if len(results[0]) != 2 {
return false
}
org1 := make([]string, 0, len(results))
org2 := make([]string, 0, len(results))
for _, res := range results {
if res[0] == nil || res[1] == nil {
return false
}
s1, s2 := res[0].(string), res[1].(string)
org1 = append(org1, s1+"|"+s2)
org2 = append(org2, s2+"|"+s1)
}
return setCompare(org1, Question6Answer) || setCompare(org2, Question6Answer)
}
var Questions = []Question{
{1, "List the top 10 employee names by salary.", Question1},
{2, "List employee names who have a bigger salary than their boss.", Question2},
{3, "List employee names who have the biggest salary in their departments.", Question3},
{4, "List department names that have less than 10 people in it.", Question4},
{5, "List employees who have a boss in a different department from them.", Question5},
{6, "List all department names along with the total salary there.", Question6},
}
type SqlResult struct {
Results [][]interface{}
Columns []string
CurrentQuestion int
Answered bool
}
func (s *SqlResult) String() string {
if s == nil {
return ""
}
if len(s.Results) == 0 {
return ""
}
return formatResults(s.Results, s.Columns)
}
func formatResults(results [][]interface{}, columns []string) string {
rows := [][]string{}
for _, row := range results {
s := []string{}
for _, res := range row {
if res == nil {
s = append(s, "NULL")
} else {
s = append(s, res.(string))
}
}
rows = append(rows, s)
}
t := termtable.NewTable(rows, &termtable.TableOptions{Padding: 2, UseSeparator: true})
t.SetHeader(columns)
return t.Render()
}
// Execute sql, checking the results against the questions in the quiz, and
// returning a result which can be String()'d to get a tabularized test result
func execSql(dbm *modl.DbMap, sql string) (*SqlResult, error) {
result := &SqlResult{}
result.Results = make([][]interface{}, 0, 10)
rows, err := dbm.Dbx.Queryx(sql)
if err != nil {
return result, err
}
columns, err := rows.Columns()
if err != nil {
panic(err)
}
result.Columns = columns
var res []interface{}
for rows.Next() {
res, err = rows.SliceScan()
if err != nil {
return result, err
}
result.Results = append(result.Results, res)
}
status := Status{}
dbm.Dbx.Get(&status, "SELECT * FROM _status;")
result.CurrentQuestion = status.CurrentQuestion
result.Answered = Questions[result.CurrentQuestion-1].Check(result.Results)
return result, nil
}