Switch to context API
[cacert-boardvoting.git] / models.go
1 package main
2
3 import (
4 "database/sql"
5 "github.com/jmoiron/sqlx"
6 "time"
7 )
8
9 const (
10 sqlGetDecisions = `
11 SELECT decisions.id, decisions.tag, decisions.proponent,
12 voters.name AS proposer, decisions.proposed, decisions.title,
13 decisions.content, decisions.votetype, decisions.status, decisions.due,
14 decisions.modified
15 FROM decisions
16 JOIN voters ON decisions.proponent=voters.id
17 ORDER BY proposed DESC
18 LIMIT 10 OFFSET 10 * $1`
19 sqlGetDecision = `
20 SELECT decisions.id, decisions.tag, decisions.proponent,
21 voters.name AS proposer, decisions.proposed, decisions.title,
22 decisions.content, decisions.votetype, decisions.status, decisions.due,
23 decisions.modified
24 FROM decisions
25 JOIN voters ON decisions.proponent=voters.id
26 WHERE decisions.tag=$1;`
27 sqlGetVoter = `
28 SELECT voters.id, voters.name, voters.enabled, voters.reminder
29 FROM voters
30 JOIN emails ON voters.id=emails.voter
31 WHERE emails.address=$1 AND voters.enabled=1`
32 sqlVoteCount = `
33 SELECT vote, COUNT(vote)
34 FROM votes
35 WHERE decision=$1 GROUP BY vote`
36 sqlCountOlderThanDecision = `
37 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`
38 sqlGetVotesForDecision = `
39 SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
40 FROM votes
41 JOIN voters ON votes.voter=voters.id
42 WHERE decision=$1`
43 sqlListUnvotedDecisions = `
44 SELECT decisions.id, decisions.tag, decisions.proponent,
45 voters.name AS proposer, decisions.proposed, decisions.title,
46 decisions.content AS content, decisions.votetype, decisions.status, decisions.due,
47 decisions.modified
48 FROM decisions
49 JOIN voters ON decisions.proponent=voters.id
50 WHERE decisions.status=0 AND decisions.id NOT IN (
51 SELECT decision FROM votes WHERE votes.voter=$2)
52 ORDER BY proposed DESC
53 LIMIT 10 OFFSET 10 * $1`
54 )
55
56 var db *sqlx.DB
57
58 type VoteType int
59 type VoteStatus int
60
61 type Decision struct {
62 Id int
63 Proposed time.Time
64 ProponentId int `db:"proponent"`
65 Title string
66 Content string
67 Quorum int
68 Majority int
69 Status VoteStatus
70 Due time.Time
71 Modified time.Time
72 Tag string
73 VoteType VoteType
74 }
75
76 type Email struct {
77 VoterId int `db:"voter"`
78 Address string
79 }
80
81 type Voter struct {
82 Id int
83 Name string
84 Enabled bool
85 Reminder string // reminder email address
86 }
87
88 type VoteChoice int
89
90 type Vote struct {
91 DecisionId int `db:"decision"`
92 VoterId int `db:"voter"`
93 Vote VoteChoice
94 Voted time.Time
95 Notes string
96 }
97
98 const (
99 voteAye = 1
100 voteNaye = -1
101 voteAbstain = 0
102 )
103
104 const (
105 voteTypeMotion = 0
106 voteTypeVeto = 1
107 )
108
109 func (v VoteType) String() string {
110 switch v {
111 case voteTypeMotion:
112 return "motion"
113 case voteTypeVeto:
114 return "veto"
115 default:
116 return "unknown"
117 }
118 }
119
120 func (v VoteType) QuorumAndMajority() (int, int) {
121 switch v {
122 case voteTypeMotion:
123 return 3, 50
124 default:
125 return 1, 99
126 }
127 }
128
129 func (v VoteChoice) String() string {
130 switch v {
131 case voteAye:
132 return "aye"
133 case voteNaye:
134 return "naye"
135 case voteAbstain:
136 return "abstain"
137 default:
138 return "unknown"
139 }
140 }
141
142 const (
143 voteStatusDeclined = -1
144 voteStatusPending = 0
145 voteStatusApproved = 1
146 voteStatusWithdrawn = -2
147 )
148
149 func (v VoteStatus) String() string {
150 switch v {
151 case voteStatusDeclined:
152 return "declined"
153 case voteStatusPending:
154 return "pending"
155 case voteStatusApproved:
156 return "approved"
157 case voteStatusWithdrawn:
158 return "withdrawn"
159 default:
160 return "unknown"
161 }
162 }
163
164 type VoteSums struct {
165 Ayes int
166 Nayes int
167 Abstains int
168 }
169
170 func (v *VoteSums) VoteCount() int {
171 return v.Ayes + v.Nayes + v.Abstains
172 }
173
174 type VoteForDisplay struct {
175 Vote
176 Name string
177 }
178
179 type DecisionForDisplay struct {
180 Decision
181 Proposer string `db:"proposer"`
182 *VoteSums
183 Votes []VoteForDisplay
184 }
185
186 func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
187 decisionStmt, err := db.Preparex(sqlGetDecision)
188 if err != nil {
189 logger.Println("Error preparing statement:", err)
190 return
191 }
192 defer decisionStmt.Close()
193
194 decision = &DecisionForDisplay{}
195 if err = decisionStmt.Get(decision, tag); err != nil {
196 if err == sql.ErrNoRows {
197 decision = nil
198 err = nil
199 } else {
200 logger.Printf("Error getting motion %s: %v\n", tag, err)
201 }
202 }
203 decision.VoteSums, err = decision.Decision.VoteSums()
204 return
205 }
206
207 // FindDecisionsForDisplayOnPage loads a set of decisions from the database.
208 //
209 // This function uses OFFSET for pagination which is not a good idea for larger data sets.
210 //
211 // TODO: migrate to timestamp base pagination
212 func FindDecisionsForDisplayOnPage(page int64) (decisions []*DecisionForDisplay, err error) {
213 decisionsStmt, err := db.Preparex(sqlGetDecisions)
214 if err != nil {
215 logger.Println("Error preparing statement:", err)
216 return
217 }
218 defer decisionsStmt.Close()
219
220 rows, err := decisionsStmt.Queryx(page - 1)
221 if err != nil {
222 logger.Printf("Error loading motions for page %d: %v\n", page, err)
223 return
224 }
225 defer rows.Close()
226
227 for rows.Next() {
228 var d DecisionForDisplay
229 if err = rows.StructScan(&d); err != nil {
230 logger.Printf("Error loading motions for page %d: %v\n", page, err)
231 return
232 }
233 d.VoteSums, err = d.Decision.VoteSums()
234 if err != nil {
235 return
236 }
237 decisions = append(decisions, &d)
238 }
239 return
240 }
241
242 func FindVotersUnvotedDecisionsForDisplayOnPage(page int64, voter *Voter) (decisions []*DecisionForDisplay, err error) {
243 decisionsStmt, err := db.Preparex(sqlListUnvotedDecisions)
244 if err != nil {
245 logger.Println("Error preparing statement:", err)
246 return
247 }
248 defer decisionsStmt.Close()
249
250 rows, err := decisionsStmt.Queryx(page-1, voter.Id)
251 if err != nil {
252 logger.Printf("Error loading motions for page %d: %v\n", page, err)
253 return
254 }
255 defer rows.Close()
256
257 for rows.Next() {
258 var d DecisionForDisplay
259 if err = rows.StructScan(&d); err != nil {
260 logger.Printf("Error loading motions for page %d: %v\n", page, err)
261 return
262 }
263 d.VoteSums, err = d.Decision.VoteSums()
264 if err != nil {
265 return
266 }
267 decisions = append(decisions, &d)
268 }
269 return
270 }
271
272 func (d *Decision) VoteSums() (sums *VoteSums, err error) {
273 votesStmt, err := db.Preparex(sqlVoteCount)
274 if err != nil {
275 logger.Println("Error preparing statement:", err)
276 return
277 }
278 defer votesStmt.Close()
279
280 voteRows, err := votesStmt.Queryx(d.Id)
281 if err != nil {
282 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
283 return
284 }
285 defer voteRows.Close()
286
287 sums = &VoteSums{}
288 for voteRows.Next() {
289 var vote VoteChoice
290 var count int
291 if err = voteRows.Scan(&vote, &count); err != nil {
292 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
293 return
294 }
295 switch vote {
296 case voteAye:
297 sums.Ayes = count
298 case voteNaye:
299 sums.Nayes = count
300 case voteAbstain:
301 sums.Abstains = count
302 }
303 }
304 return
305 }
306
307 func (d *DecisionForDisplay) LoadVotes() (err error) {
308 votesStmt, err := db.Preparex(sqlGetVotesForDecision)
309 if err != nil {
310 logger.Println("Error preparing statement:", err)
311 return
312 }
313 defer votesStmt.Close()
314 err = votesStmt.Select(&d.Votes, d.Id)
315 if err != nil {
316 logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
317 }
318 return
319 }
320
321 func (d *Decision) OlderExists() (result bool, err error) {
322 olderStmt, err := db.Preparex(sqlCountOlderThanDecision)
323 if err != nil {
324 logger.Println("Error preparing statement:", err)
325 return
326 }
327 defer olderStmt.Close()
328
329 if err := olderStmt.Get(&result, d.Proposed); err != nil {
330 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
331 }
332 return
333 }
334
335 func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
336 findVoterStmt, err := db.Preparex(sqlGetVoter)
337 if err != nil {
338 logger.Println("Error preparing statement:", err)
339 return
340 }
341 defer findVoterStmt.Close()
342
343 voter = &Voter{}
344 if err = findVoterStmt.Get(voter, emailAddress); err != nil {
345 if err != sql.ErrNoRows {
346 logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
347 } else {
348 err = nil
349 voter = nil
350 }
351 }
352 return
353 }