d6eee15a317685941952af73f2a96f489b852545
[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 )
25
26 var sqlStatements = map[sqlKey]string{
27 sqlLoadDecisions: `
28 SELECT decisions.id, decisions.tag, decisions.proponent,
29 voters.name AS proposer, decisions.proposed, decisions.title,
30 decisions.content, decisions.votetype, decisions.status, decisions.due,
31 decisions.modified
32 FROM decisions
33 JOIN voters ON decisions.proponent=voters.id
34 ORDER BY proposed DESC
35 LIMIT 10 OFFSET 10 * $1`,
36 sqlLoadUnvotedDecisions: `
37 SELECT decisions.id, decisions.tag, decisions.proponent,
38 voters.name AS proposer, decisions.proposed, decisions.title,
39 decisions.content, decisions.votetype, decisions.status, decisions.due,
40 decisions.modified
41 FROM decisions
42 JOIN voters ON decisions.proponent=voters.id
43 WHERE decisions.status = 0 AND decisions.id NOT IN (
44 SELECT votes.decision
45 FROM votes
46 WHERE votes.voter = $1)
47 ORDER BY proposed DESC
48 LIMIT 10 OFFSET 10 * $2;`,
49 sqlLoadDecisionByTag: `
50 SELECT decisions.id, decisions.tag, decisions.proponent,
51 voters.name AS proposer, decisions.proposed, decisions.title,
52 decisions.content, decisions.votetype, decisions.status, decisions.due,
53 decisions.modified
54 FROM decisions
55 JOIN voters ON decisions.proponent=voters.id
56 WHERE decisions.tag=$1;`,
57 sqlLoadDecisionById: `
58 SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
59 decisions.title, decisions.content, decisions.votetype, decisions.status,
60 decisions.due, decisions.modified
61 FROM decisions
62 WHERE decisions.id=$1;`,
63 sqlLoadVoteCountsForDecision: `
64 SELECT vote, COUNT(vote)
65 FROM votes
66 WHERE decision=$1 GROUP BY vote`,
67 sqlLoadVotesForDecision: `
68 SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
69 FROM votes
70 JOIN voters ON votes.voter=voters.id
71 WHERE decision=$1`,
72 sqlLoadEnabledVoterByEmail: `
73 SELECT voters.id, voters.name, voters.enabled, voters.reminder
74 FROM voters
75 JOIN emails ON voters.id=emails.voter
76 WHERE emails.address=$1 AND voters.enabled=1`,
77 sqlCountOlderThanDecision: `
78 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`,
79 sqlCountOlderThanUnvotedDecision: `
80 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1
81 AND status=0
82 AND id NOT IN (SELECT decision FROM votes WHERE votes.voter=$2)`,
83 sqlCreateDecision: `
84 INSERT INTO decisions (
85 proposed, proponent, title, content, votetype, status, due, modified,tag
86 ) VALUES (
87 :proposed, :proponent, :title, :content, :votetype, 0,
88 :due,
89 :proposed,
90 'm' || strftime('%Y%m%d', :proposed) || '.' || (
91 SELECT COUNT(*)+1 AS num
92 FROM decisions
93 WHERE proposed
94 BETWEEN date(:proposed) AND date(:proposed, '1 day')
95 )
96 )`,
97 sqlUpdateDecision: `
98 UPDATE decisions
99 SET proponent=:proponent, title=:title, content=:content,
100 votetype=:votetype, due=:due, modified=:modified
101 WHERE id=:id`,
102 sqlUpdateDecisionStatus: `
103 UPDATE decisions
104 SET status=:status, modified=:modified WHERE id=:id
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 type VoteForDisplay struct {
238 Vote
239 Name string
240 }
241
242 type DecisionForDisplay struct {
243 Decision
244 Proposer string `db:"proposer"`
245 *VoteSums
246 Votes []VoteForDisplay
247 }
248
249 func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
250 decisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionByTag])
251 if err != nil {
252 logger.Println("Error preparing statement:", err)
253 return
254 }
255 defer decisionStmt.Close()
256
257 decision = &DecisionForDisplay{}
258 if err = decisionStmt.Get(decision, tag); err != nil {
259 if err == sql.ErrNoRows {
260 decision = nil
261 err = nil
262 } else {
263 logger.Printf("Error getting motion %s: %v\n", tag, err)
264 }
265 }
266 decision.VoteSums, err = decision.Decision.VoteSums()
267 return
268 }
269
270 // FindDecisionsForDisplayOnPage loads a set of decisions from the database.
271 //
272 // This function uses OFFSET for pagination which is not a good idea for larger data sets.
273 //
274 // TODO: migrate to timestamp base pagination
275 func FindDecisionsForDisplayOnPage(page int64, unvoted bool, voter *Voter) (decisions []*DecisionForDisplay, err error) {
276 var decisionsStmt *sqlx.Stmt
277 if unvoted && voter != nil {
278 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadUnvotedDecisions])
279 } else {
280 decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadDecisions])
281 }
282 if err != nil {
283 logger.Println("Error preparing statement:", err)
284 return
285 }
286 defer decisionsStmt.Close()
287
288 var rows *sqlx.Rows
289 if unvoted && voter != nil {
290 rows, err = decisionsStmt.Queryx(voter.Id, page-1)
291 } else {
292 rows, err = decisionsStmt.Queryx(page - 1)
293 }
294 if err != nil {
295 logger.Printf("Error loading motions for page %d: %v\n", page, err)
296 return
297 }
298 defer rows.Close()
299
300 for rows.Next() {
301 var d DecisionForDisplay
302 if err = rows.StructScan(&d); err != nil {
303 logger.Printf("Error loading motions for page %d: %v\n", page, err)
304 return
305 }
306 d.VoteSums, err = d.Decision.VoteSums()
307 if err != nil {
308 return
309 }
310 decisions = append(decisions, &d)
311 }
312 return
313 }
314
315 func (d *Decision) VoteSums() (sums *VoteSums, err error) {
316 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVoteCountsForDecision])
317 if err != nil {
318 logger.Println("Error preparing statement:", err)
319 return
320 }
321 defer votesStmt.Close()
322
323 voteRows, err := votesStmt.Queryx(d.Id)
324 if err != nil {
325 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
326 return
327 }
328 defer voteRows.Close()
329
330 sums = &VoteSums{}
331 for voteRows.Next() {
332 var vote VoteChoice
333 var count int
334 if err = voteRows.Scan(&vote, &count); err != nil {
335 logger.Printf("Error fetching vote sums for motion %s: %v\n", d.Tag, err)
336 return
337 }
338 switch vote {
339 case voteAye:
340 sums.Ayes = count
341 case voteNaye:
342 sums.Nayes = count
343 case voteAbstain:
344 sums.Abstains = count
345 }
346 }
347 return
348 }
349
350 func (d *DecisionForDisplay) LoadVotes() (err error) {
351 votesStmt, err := db.Preparex(sqlStatements[sqlLoadVotesForDecision])
352 if err != nil {
353 logger.Println("Error preparing statement:", err)
354 return
355 }
356 defer votesStmt.Close()
357 err = votesStmt.Select(&d.Votes, d.Id)
358 if err != nil {
359 logger.Printf("Error selecting votes for motion %s: %v\n", d.Tag, err)
360 }
361 return
362 }
363
364 func (d *Decision) OlderExists(unvoted bool, voter *Voter) (result bool, err error) {
365 var olderStmt *sqlx.Stmt
366 if unvoted && voter != nil {
367 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanUnvotedDecision])
368 } else {
369 olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanDecision])
370 }
371 if err != nil {
372 logger.Println("Error preparing statement:", err)
373 return
374 }
375 defer olderStmt.Close()
376
377 if unvoted && voter != nil {
378 if err = olderStmt.Get(&result, d.Proposed, voter.Id); err != nil {
379 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
380 }
381 } else {
382 if err = olderStmt.Get(&result, d.Proposed); err != nil {
383 logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
384 }
385 }
386
387 return
388 }
389
390 func (d *Decision) Create() (err error) {
391 insertDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlCreateDecision])
392 if err != nil {
393 logger.Println("Error preparing statement:", err)
394 return
395 }
396 defer insertDecisionStmt.Close()
397
398 result, err := insertDecisionStmt.Exec(d)
399 if err != nil {
400 logger.Println("Error creating motion:", err)
401 return
402 }
403
404 lastInsertId, err := result.LastInsertId()
405 if err != nil {
406 logger.Println("Error getting id of inserted motion:", err)
407 return
408 }
409
410 getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
411 if err != nil {
412 logger.Println("Error preparing statement:", err)
413 return
414 }
415 defer getDecisionStmt.Close()
416
417 err = getDecisionStmt.Get(d, lastInsertId)
418 if err != nil {
419 logger.Println("Error getting inserted motion:", err)
420 }
421
422 return
423 }
424
425 func (d *Decision) LoadWithId() (err error) {
426 getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
427 if err != nil {
428 logger.Println("Error preparing statement:", err)
429 return
430 }
431 defer getDecisionStmt.Close()
432
433 err = getDecisionStmt.Get(d, d.Id)
434 if err != nil {
435 logger.Println("Error loading updated motion:", err)
436 }
437
438 return
439 }
440
441 func (d *Decision) Update() (err error) {
442 updateDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecision])
443 if err != nil {
444 logger.Println("Error preparing statement:", err)
445 return
446 }
447 defer updateDecisionStmt.Close()
448
449 result, err := updateDecisionStmt.Exec(d)
450 if err != nil {
451 logger.Println("Error updating motion:", err)
452 return
453 }
454 affectedRows, err := result.RowsAffected()
455 if err != nil {
456 logger.Print("Problem determining the affected rows")
457 return
458 } else if affectedRows != 1 {
459 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
460 }
461
462 err = d.LoadWithId()
463 return
464 }
465
466 func (d *Decision) UpdateStatus() (err error) {
467 updateStatusStmt, err := db.PrepareNamed(sqlStatements[sqlUpdateDecisionStatus])
468 if err != nil {
469 logger.Println("Error preparing statement:", err)
470 return
471 }
472 defer updateStatusStmt.Close()
473
474 result, err := updateStatusStmt.Exec(d)
475 if err != nil {
476 logger.Println("Error setting motion status:", err)
477 return
478 }
479 affectedRows, err := result.RowsAffected()
480 if err != nil {
481 logger.Print("Problem determining the affected rows")
482 } else if affectedRows != 1 {
483 logger.Printf("WARNING wrong number of affected rows: %d (1 expected)\n", affectedRows)
484 }
485
486 err = d.LoadWithId()
487 return
488 }
489
490 func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
491 findVoterStmt, err := db.Preparex(sqlStatements[sqlLoadEnabledVoterByEmail])
492 if err != nil {
493 logger.Println("Error preparing statement:", err)
494 return
495 }
496 defer findVoterStmt.Close()
497
498 voter = &Voter{}
499 if err = findVoterStmt.Get(voter, emailAddress); err != nil {
500 if err != sql.ErrNoRows {
501 logger.Printf("Error getting voter for address %s: %v\n", emailAddress, err)
502 } else {
503 err = nil
504 voter = nil
505 }
506 }
507 return
508 }