5df7d95d27cf176e97360f9d98c71d68b8efd8de
[cacert-boardvoting.git] / models.go
1 package main
2
3 import (
4 "database/sql"
5 "github.com/jmoiron/sqlx"
6 "time"
7 )
8
9 type sqlKey int
10
11 const (
12 sqlLoadDecisions sqlKey = iota
13 sqlLoadUnvotedDecisions
14 sqlLoadDecisionByTag
15 sqlLoadDecisionById
16 sqlLoadVoteCountsForDecision
17 sqlLoadVotesForDecision
18 sqlLoadEnabledVoterByEmail
19 sqlCountOlderThanDecision
20 sqlCountOlderThanUnvotedDecision
21 sqlCreateDecision
22 sqlUpdateDecision
23 sqlUpdateDecisionStatus
24 sqlSelectClosableDecisions
25 )
26
27 var sqlStatements = map[sqlKey]string{
28 sqlLoadDecisions: `
29 SELECT decisions.id, decisions.tag, decisions.proponent,
30 voters.name AS proposer, decisions.proposed, decisions.title,
31 decisions.content, decisions.votetype, decisions.status, decisions.due,
32 decisions.modified
33 FROM decisions
34 JOIN voters ON decisions.proponent=voters.id
35 ORDER BY proposed DESC
36 LIMIT 10 OFFSET 10 * $1`,
37 sqlLoadUnvotedDecisions: `
38 SELECT decisions.id, decisions.tag, decisions.proponent,
39 voters.name AS proposer, decisions.proposed, decisions.title,
40 decisions.content, decisions.votetype, decisions.status, decisions.due,
41 decisions.modified
42 FROM decisions
43 JOIN voters ON decisions.proponent=voters.id
44 WHERE decisions.status = 0 AND decisions.id NOT IN (
45 SELECT votes.decision
46 FROM votes
47 WHERE votes.voter = $1)
48 ORDER BY proposed DESC
49 LIMIT 10 OFFSET 10 * $2;`,
50 sqlLoadDecisionByTag: `
51 SELECT decisions.id, decisions.tag, decisions.proponent,
52 voters.name AS proposer, decisions.proposed, decisions.title,
53 decisions.content, decisions.votetype, decisions.status, decisions.due,
54 decisions.modified
55 FROM decisions
56 JOIN voters ON decisions.proponent=voters.id
57 WHERE decisions.tag=$1;`,
58 sqlLoadDecisionById: `
59 SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
60 decisions.title, decisions.content, decisions.votetype, decisions.status,
61 decisions.due, decisions.modified
62 FROM decisions
63 WHERE decisions.id=$1;`,
64 sqlLoadVoteCountsForDecision: `
65 SELECT vote, COUNT(vote)
66 FROM votes
67 WHERE decision=$1 GROUP BY vote`,
68 sqlLoadVotesForDecision: `
69 SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
70 FROM votes
71 JOIN voters ON votes.voter=voters.id
72 WHERE decision=$1`,
73 sqlLoadEnabledVoterByEmail: `
74 SELECT voters.id, voters.name, voters.enabled, voters.reminder
75 FROM voters
76 JOIN emails ON voters.id=emails.voter
77 WHERE emails.address=$1 AND voters.enabled=1`,
78 sqlCountOlderThanDecision: `
79 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`,
80 sqlCountOlderThanUnvotedDecision: `
81 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1
82 AND status=0
83 AND id NOT IN (SELECT decision FROM votes WHERE votes.voter=$2)`,
84 sqlCreateDecision: `
85 INSERT INTO decisions (
86 proposed, proponent, title, content, votetype, status, due, modified,tag
87 ) VALUES (
88 :proposed, :proponent, :title, :content, :votetype, 0,
89 :due,
90 :proposed,
91 'm' || strftime('%Y%m%d', :proposed) || '.' || (
92 SELECT COUNT(*)+1 AS num
93 FROM decisions
94 WHERE proposed
95 BETWEEN date(:proposed) AND date(:proposed, '1 day')
96 )
97 )`,
98 sqlUpdateDecision: `
99 UPDATE decisions
100 SET proponent=:proponent, title=:title, content=:content,
101 votetype=:votetype, due=:due, modified=:modified
102 WHERE id=:id`,
103 sqlUpdateDecisionStatus: `
104 UPDATE decisions
105 SET status=:status, modified=:modified WHERE id=:id
106 `,
107 sqlSelectClosableDecisions: `
108 SELECT decisions.id, decisions.tag, decisions.proponent, voters.name AS proposer, decisions.proposed, decisions.title,
109 decisions.content, decisions.votetype, decisions.status, decisions.due, decisions.modified
110 FROM decisions
111 JOIN voters ON decisions.proponent=voters.id
112 WHERE decisions.status=0 AND :now > due`,
113 }
114
115 var db *sqlx.DB
116
117 func init() {
118 for _, sqlStatement := range sqlStatements {
119 var stmt *sqlx.Stmt
120 stmt, err := db.Preparex(sqlStatement)
121 if err != nil {
122 logger.Fatalf("ERROR parsing statement %s: %s", sqlStatement, err)
123 }
124 stmt.Close()
125 }
126 }
127
128 type VoteType uint8
129 type VoteStatus int8
130
131 type Decision struct {
132 Id int
133 Proposed time.Time
134 ProponentId int `db:"proponent"`
135 Title string
136 Content string
137 Quorum int
138 Majority int
139 Status VoteStatus
140 Due time.Time
141 Modified time.Time
142 Tag string
143 VoteType VoteType
144 }
145
146 type Email struct {
147 VoterId int `db:"voter"`
148 Address string
149 }
150
151 type Voter struct {
152 Id int
153 Name string
154 Enabled bool
155 Reminder string // reminder email address
156 }
157
158 type VoteChoice int
159
160 type Vote struct {
161 DecisionId int `db:"decision"`
162 VoterId int `db:"voter"`
163 Vote VoteChoice
164 Voted time.Time
165 Notes string
166 }
167
168 const (
169 voteAye = 1
170 voteNaye = -1
171 voteAbstain = 0
172 )
173
174 const (
175 voteTypeMotion = 0
176 voteTypeVeto = 1
177 )
178
179 func (v VoteType) String() string {
180 switch v {
181 case voteTypeMotion:
182 return "motion"
183 case voteTypeVeto:
184 return "veto"
185 default:
186 return "unknown"
187 }
188 }
189
190 func (v VoteType) QuorumAndMajority() (int, int) {
191 switch v {
192 case voteTypeMotion:
193 return 3, 50
194 default:
195 return 1, 99
196 }
197 }
198
199 func (v VoteChoice) String() string {
200 switch v {
201 case voteAye:
202 return "aye"
203 case voteNaye:
204 return "naye"
205 case voteAbstain:
206 return "abstain"
207 default:
208 return "unknown"
209 }
210 }
211
212 const (
213 voteStatusDeclined = -1
214 voteStatusPending = 0
215 voteStatusApproved = 1
216 voteStatusWithdrawn = -2
217 )
218
219 func (v VoteStatus) String() string {
220 switch v {
221 case voteStatusDeclined:
222 return "declined"
223 case voteStatusPending:
224 return "pending"
225 case voteStatusApproved:
226 return "approved"
227 case voteStatusWithdrawn:
228 return "withdrawn"
229 default:
230 return "unknown"
231 }
232 }
233
234 type VoteSums struct {
235 Ayes int
236 Nayes int
237 Abstains int
238 }
239
240 func (v *VoteSums) VoteCount() int {
241 return v.Ayes + v.Nayes + v.Abstains
242 }
243
244 type VoteForDisplay struct {
245 Vote
246 Name string
247 }
248
249 type DecisionForDisplay struct {
250 Decision
251 Proposer string `db:"proposer"`
252 *VoteSums
253 Votes []VoteForDisplay
254 }
255
256 func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
257 decisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionByTag])
258 if err != nil {
259 logger.Println("Error preparing statement:", err)
260 return
261 }
262 defer decisionStmt.Close()
263
264 decision = &DecisionForDisplay{}
265 if err = decisionStmt.Get(decision, tag); err != nil {
266 if err == sql.ErrNoRows {
267 decision = nil
268 err = nil
269 } else {
270 logger.Printf("Error getting motion %s: %v\n", tag, err)
271 }
272 }
273 decision.VoteSums, err = decision.Decision.VoteSums()
274 return
275 }
276
277 // FindDecisionsForDisplayOnPage loads a set of decisions from the database.
278 //
279 // This function uses OFFSET for pagination which is not a good idea for larger data sets.
280 //
281 // TODO: migrate to timestamp base pagination
282 func FindDecisionsForDisplayOnPage(page int64, unvoted bool, voter *Voter) (decisions []*DecisionForDisplay, err error) {
283 var decisionsStmt *sqlx.Stmt
284 if unvoted && voter != nil {
285 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadUnvotedDecisions])
286 } else {
287 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadDecisions])
288 }
289 if err != nil {
290 logger.Println("Error preparing statement:", err)
291 return
292 }
293 defer decisionsStmt.Close()
294
295 var rows *sqlx.Rows
296 if unvoted && voter != nil {
297 rows, err = decisionsStmt.Queryx(voter.Id, page-1)
298 } else {
299 rows, err = decisionsStmt.Queryx(page - 1)
300 }
301 if err != nil {
302 logger.Printf("Error loading motions for page %d: %v\n", page, err)
303 return
304 }
305 defer rows.Close()
306
307 for rows.Next() {
308 var d DecisionForDisplay
309 if err = rows.StructScan(&d); err != nil {
310 logger.Printf("Error loading motions for page %d: %v\n", page, err)
311 return
312 }
313 d.VoteSums, err = d.Decision.VoteSums()
314 if err != nil {
315 return
316 }
317 decisions = append(decisions, &d)
318 }
319 return
320 }
321
322 func (d *Decision) VoteSums() (sums *VoteSums, err error) {
323 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVoteCountsForDecision])
324 if err != nil {
325 logger.Println("Error preparing statement:", err)
326 return
327 }
328 defer votesStmt.Close()
329
330 voteRows, err := votesStmt.Queryx(d.Id)
331 if err != nil {
332 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
333 return
334 }
335 defer voteRows.Close()
336
337 sums = &VoteSums{}
338 for voteRows.Next() {
339 var vote VoteChoice
340 var count int
341 if err = voteRows.Scan(&vote, &count); err != nil {
342 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
343 return
344 }
345 switch vote {
346 case voteAye:
347 sums.Ayes = count
348 case voteNaye:
349 sums.Nayes = count
350 case voteAbstain:
351 sums.Abstains = count
352 }
353 }
354 return
355 }
356
357 func (d *DecisionForDisplay) LoadVotes() (err error) {
358 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVotesForDecision])
359 if err != nil {
360 logger.Println("Error preparing statement:", err)
361 return
362 }
363 defer votesStmt.Close()
364 err = votesStmt.Select(&d.Votes, d.Id)
365 if err != nil {
366 logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
367 }
368 return
369 }
370
371 func (d *Decision) OlderExists(unvoted bool, voter *Voter) (result bool, err error) {
372 var olderStmt *sqlx.Stmt
373 if unvoted && voter != nil {
374 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanUnvotedDecision])
375 } else {
376 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanDecision])
377 }
378 if err != nil {
379 logger.Println("Error preparing statement:", err)
380 return
381 }
382 defer olderStmt.Close()
383
384 if unvoted && voter != nil {
385 if err = olderStmt.Get(&result, d.Proposed, voter.Id); err != nil {
386 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
387 }
388 } else {
389 if err = olderStmt.Get(&result, d.Proposed); err != nil {
390 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
391 }
392 }
393
394 return
395 }
396
397 func (d *Decision) Create() (err error) {
398 insertDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlCreateDecision])
399 if err != nil {
400 logger.Println("Error preparing statement:", err)
401 return
402 }
403 defer insertDecisionStmt.Close()
404
405 result, err := insertDecisionStmt.Exec(d)
406 if err != nil {
407 logger.Println("Error creating motion:", err)
408 return
409 }
410
411 lastInsertId, err := result.LastInsertId()
412 if err != nil {
413 logger.Println("Error getting id of inserted motion:", err)
414 return
415 }
416
417 getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
418 if err != nil {
419 logger.Println("Error preparing statement:", err)
420 return
421 }
422 defer getDecisionStmt.Close()
423
424 err = getDecisionStmt.Get(d, lastInsertId)
425 if err != nil {
426 logger.Println("Error getting inserted motion:", err)
427 }
428
429 return
430 }
431
432 func (d *Decision) LoadWithId() (err error) {
433 getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
434 if err != nil {
435 logger.Println("Error preparing statement:", err)
436 return
437 }
438 defer getDecisionStmt.Close()
439
440 err = getDecisionStmt.Get(d, d.Id)
441 if err != nil {
442 logger.Println("Error loading updated motion:", err)
443 }
444
445 return
446 }
447
448 func (d *Decision) Update() (err error) {
449 updateDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecision])
450 if err != nil {
451 logger.Println("Error preparing statement:", err)
452 return
453 }
454 defer updateDecisionStmt.Close()
455
456 result, err := updateDecisionStmt.Exec(d)
457 if err != nil {
458 logger.Println("Error updating motion:", err)
459 return
460 }
461 affectedRows, err := result.RowsAffected()
462 if err != nil {
463 logger.Print("Problem determining the affected rows")
464 return
465 } else if affectedRows != 1 {
466 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
467 }
468
469 err = d.LoadWithId()
470 return
471 }
472
473 func (d *Decision) UpdateStatus() (err error) {
474 updateStatusStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecisionStatus])
475 if err != nil {
476 logger.Println("Error preparing statement:", err)
477 return
478 }
479 defer updateStatusStmt.Close()
480
481 result, err := updateStatusStmt.Exec(d)
482 if err != nil {
483 logger.Println("Error setting motion status:", err)
484 return
485 }
486 affectedRows, err := result.RowsAffected()
487 if err != nil {
488 logger.Print("Problem determining the affected rows")
489 } else if affectedRows != 1 {
490 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
491 }
492
493 err = d.LoadWithId()
494 return
495 }
496
497 func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
498 findVoterStmt, err := db.Preparex(sqlStatements[sqlLoadEnabledVoterByEmail])
499 if err != nil {
500 logger.Println("Error preparing statement:", err)
501 return
502 }
503 defer findVoterStmt.Close()
504
505 voter = &Voter{}
506 if err = findVoterStmt.Get(voter, emailAddress); err != nil {
507 if err != sql.ErrNoRows {
508 logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
509 } else {
510 err = nil
511 voter = nil
512 }
513 }
514 return
515 }
516
517 func (d *Decision) Close() (err error) {
518 closeDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecisionStatus])
519 if err != nil {
520 logger.Println("Error preparing statement:", err)
521 return
522 }
523 defer closeDecisionStmt.Close()
524
525 quorum, majority := d.VoteType.QuorumAndMajority()
526
527 voteSums, err := d.VoteSums()
528
529 if err != nil {
530 logger.Println("Error getting vote sums")
531 return
532 }
533 votes := voteSums.VoteCount()
534
535 if votes < quorum {
536 d.Status = voteStatusDeclined
537 } else {
538 votes = voteSums.Ayes + voteSums.Nayes
539 if (voteSums.Ayes / votes) > (majority / 100) {
540 d.Status = voteStatusApproved
541 } else {
542 d.Status = voteStatusDeclined
543 }
544 }
545
546 result, err := closeDecisionStmt.Exec(d)
547 if err != nil {
548 logger.Println("Error closing vote:", err)
549 return
550 }
551 affectedRows, err := result.RowsAffected()
552 if err != nil {
553 logger.Println("Error getting affected rows:", err)
554 }
555 if affectedRows != 1 {
556 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
557 }
558
559 notifyMail <- &NotificationClosedDecision{decision: *d, voteSums: *voteSums}
560
561 return
562 }
563
564 func CloseDecisions() (err error) {
565 getClosedDecisionsStmt, err := db.PrepareNamed(sqlStatements[sqlSelectClosableDecisions])
566 if err != nil {
567 logger.Println("Error preparing statement:", err)
568 return
569 }
570 defer getClosedDecisionsStmt.Close()
571
572 params := make(map[string]interface{}, 1)
573 params["now"] = time.Now().UTC()
574 rows, err := getClosedDecisionsStmt.Queryx(params)
575 if err != nil {
576 logger.Println("Error fetching closed decisions", err)
577 return
578 }
579 defer rows.Close()
580 for rows.Next() {
581 d := &Decision{}
582 if err = rows.StructScan(d); err != nil {
583 logger.Println("Error filling decision from database row", err)
584 return
585 }
586 if err = d.Close(); err != nil {
587 logger.Printf("Error closing decision %s: %s\n", d.Tag, err)
588 return
589 }
590 }
591
592 return
593 }