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