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