summaryrefslogtreecommitdiff
path: root/models.go
diff options
context:
space:
mode:
Diffstat (limited to 'models.go')
-rw-r--r--models.go206
1 files changed, 126 insertions, 80 deletions
diff --git a/models.go b/models.go
index 410ed3e..a8d24ad 100644
--- a/models.go
+++ b/models.go
@@ -6,8 +6,23 @@ import (
"time"
)
+type sqlKey int
+
const (
- sqlGetDecisions = `
+ sqlLoadDecisions sqlKey = iota
+ sqlLoadUnvotedDecisions
+ sqlLoadDecisionByTag
+ sqlLoadDecisionById
+ sqlLoadVoteCountsForDecision
+ sqlLoadVotesForDecision
+ sqlLoadEnabledVoterByEmail
+ sqlCountOlderThanDecision
+ sqlCountOlderThanUnvotedDecision
+ sqlCreateDecision
+)
+
+var sqlStatements = map[sqlKey]string{
+ sqlLoadDecisions: `
SELECT decisions.id, decisions.tag, decisions.proponent,
voters.name AS proposer, decisions.proposed, decisions.title,
decisions.content, decisions.votetype, decisions.status, decisions.due,
@@ -15,60 +30,83 @@ SELECT decisions.id, decisions.tag, decisions.proponent,
FROM decisions
JOIN voters ON decisions.proponent=voters.id
ORDER BY proposed DESC
-LIMIT 10 OFFSET 10 * $1`
- sqlGetDecision = `
+LIMIT 10 OFFSET 10 * $1`,
+ sqlLoadUnvotedDecisions: `
SELECT decisions.id, decisions.tag, decisions.proponent,
voters.name AS proposer, decisions.proposed, decisions.title,
decisions.content, decisions.votetype, decisions.status, decisions.due,
decisions.modified
FROM decisions
JOIN voters ON decisions.proponent=voters.id
-WHERE decisions.tag=$1;`
- sqlGetVoter = `
-SELECT voters.id, voters.name, voters.enabled, voters.reminder
-FROM voters
-JOIN emails ON voters.id=emails.voter
-WHERE emails.address=$1 AND voters.enabled=1`
- sqlVoteCount = `
+WHERE decisions.status = 0 AND decisions.id NOT IN (
+ SELECT votes.decision
+ FROM votes
+ WHERE votes.voter = $1)
+ORDER BY proposed DESC
+LIMIT 10 OFFSET 10 * $2;`,
+ sqlLoadDecisionByTag: `
+SELECT decisions.id, decisions.tag, decisions.proponent,
+ voters.name AS proposer, decisions.proposed, decisions.title,
+ decisions.content, decisions.votetype, decisions.status, decisions.due,
+ decisions.modified
+FROM decisions
+JOIN voters ON decisions.proponent=voters.id
+WHERE decisions.tag=$1;`,
+ sqlLoadDecisionById: `
+SELECT decisions.id, decisions.tag, decisions.proponent, decisions.proposed,
+ decisions.title, decisions.content, decisions.votetype, decisions.status,
+ decisions.due, decisions.modified
+FROM decisions
+WHERE decisions.id=$1;`,
+ sqlLoadVoteCountsForDecision: `
SELECT vote, COUNT(vote)
FROM votes
-WHERE decision=$1 GROUP BY vote`
- sqlCountOlderThanDecision = `
-SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`
- sqlGetVotesForDecision = `
+WHERE decision=$1 GROUP BY vote`,
+ sqlLoadVotesForDecision: `
SELECT votes.decision, votes.voter, voters.name, votes.vote, votes.voted, votes.notes
FROM votes
JOIN voters ON votes.voter=voters.id
-WHERE decision=$1`
- sqlListUnvotedDecisions = `
-SELECT decisions.id, decisions.tag, decisions.proponent,
- voters.name AS proposer, decisions.proposed, decisions.title,
- decisions.content AS content, decisions.votetype, decisions.status, decisions.due,
- decisions.modified
-FROM decisions
-JOIN voters ON decisions.proponent=voters.id
-WHERE decisions.status=0 AND decisions.id NOT IN (
- SELECT decision FROM votes WHERE votes.voter=$2)
-ORDER BY proposed DESC
-LIMIT 10 OFFSET 10 * $1`
- sqlCreateDecision = `
+WHERE decision=$1`,
+ sqlLoadEnabledVoterByEmail: `
+SELECT voters.id, voters.name, voters.enabled, voters.reminder
+FROM voters
+JOIN emails ON voters.id=emails.voter
+WHERE emails.address=$1 AND voters.enabled=1`,
+ sqlCountOlderThanDecision: `
+SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`,
+ sqlCountOlderThanUnvotedDecision: `
+SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1
+ AND status=0
+ AND id NOT IN (SELECT decision FROM votes WHERE votes.voter=$2)`,
+ sqlCreateDecision: `
INSERT INTO decisions (
proposed, proponent, title, content, votetype, status, due, modified,tag
) VALUES (
- datetime('now','utc'), :proponent, :title, :content, :votetype, 0,
+ :proposed, :proponent, :title, :content, :votetype, 0,
:due,
- datetime('now','utc'),
- 'm' || strftime('%Y%m%d','now') || '.' || (
- SELECT COUNT(*)+1 AS num
- FROM decisions
- WHERE proposed BETWEEN date('now') AND date('now','1 day')
+ :proposed,
+ 'm' || strftime('%Y%m%d', :proposed) || '.' || (
+ SELECT COUNT(*)+1 AS num
+ FROM decisions
+ WHERE proposed
+ BETWEEN date(:proposed) AND date(:proposed, '1 day')
)
-)
-`
-)
+)`,
+}
var db *sqlx.DB
+func init() {
+ for _, sqlStatement := range sqlStatements {
+ var stmt *sqlx.Stmt
+ stmt, err := db.Preparex(sqlStatement)
+ if err != nil {
+ logger.Fatalf("ERROR parsing statement %s: %s", sqlStatement, err)
+ }
+ stmt.Close()
+ }
+}
+
type VoteType uint8
type VoteStatus int8
@@ -198,7 +236,7 @@ type DecisionForDisplay struct {
}
func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err error) {
- decisionStmt, err := db.Preparex(sqlGetDecision)
+ decisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionByTag])
if err != nil {
logger.Println("Error preparing statement:", err)
return
@@ -223,45 +261,25 @@ func FindDecisionForDisplayByTag(tag string) (decision *DecisionForDisplay, err
// This function uses OFFSET for pagination which is not a good idea for larger data sets.
//
// TODO: migrate to timestamp base pagination
-func FindDecisionsForDisplayOnPage(page int64) (decisions []*DecisionForDisplay, err error) {
- decisionsStmt, err := db.Preparex(sqlGetDecisions)
- if err != nil {
- logger.Println("Error preparing statement:", err)
- return
- }
- defer decisionsStmt.Close()
-
- rows, err := decisionsStmt.Queryx(page - 1)
- if err != nil {
- logger.Printf("Error loading motions for page %d: %v\n", page, err)
- return
- }
- defer rows.Close()
-
- for rows.Next() {
- var d DecisionForDisplay
- if err = rows.StructScan(&d); err != nil {
- logger.Printf("Error loading motions for page %d: %v\n", page, err)
- return
- }
- d.VoteSums, err = d.Decision.VoteSums()
- if err != nil {
- return
- }
- decisions = append(decisions, &d)
+func FindDecisionsForDisplayOnPage(page int64, unvoted bool, voter *Voter) (decisions []*DecisionForDisplay, err error) {
+ var decisionsStmt *sqlx.Stmt
+ if unvoted && voter != nil {
+ decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadUnvotedDecisions])
+ } else {
+ decisionsStmt, err = db.Preparex(sqlStatements[sqlLoadDecisions])
}
- return
-}
-
-func FindVotersUnvotedDecisionsForDisplayOnPage(page int64, voter *Voter) (decisions []*DecisionForDisplay, err error) {
- decisionsStmt, err := db.Preparex(sqlListUnvotedDecisions)
if err != nil {
logger.Println("Error preparing statement:", err)
return
}
defer decisionsStmt.Close()
- rows, err := decisionsStmt.Queryx(page-1, voter.Id)
+ var rows *sqlx.Rows
+ if unvoted && voter != nil {
+ rows, err = decisionsStmt.Queryx(voter.Id, page-1)
+ } else {
+ rows, err = decisionsStmt.Queryx(page - 1)
+ }
if err != nil {
logger.Printf("Error loading motions for page %d: %v\n", page, err)
return
@@ -284,7 +302,7 @@ func FindVotersUnvotedDecisionsForDisplayOnPage(page int64, voter *Voter) (decis
}
func (d *Decision) VoteSums() (sums *VoteSums, err error) {
- votesStmt, err := db.Preparex(sqlVoteCount)
+ votesStmt, err := db.Preparex(sqlStatements[sqlLoadVoteCountsForDecision])
if err != nil {
logger.Println("Error preparing statement:", err)
return
@@ -319,7 +337,7 @@ func (d *Decision) VoteSums() (sums *VoteSums, err error) {
}
func (d *DecisionForDisplay) LoadVotes() (err error) {
- votesStmt, err := db.Preparex(sqlGetVotesForDecision)
+ votesStmt, err := db.Preparex(sqlStatements[sqlLoadVotesForDecision])
if err != nil {
logger.Println("Error preparing statement:", err)
return
@@ -332,22 +350,34 @@ func (d *DecisionForDisplay) LoadVotes() (err error) {
return
}
-func (d *Decision) OlderExists() (result bool, err error) {
- olderStmt, err := db.Preparex(sqlCountOlderThanDecision)
+func (d *Decision) OlderExists(unvoted bool, voter *Voter) (result bool, err error) {
+ var olderStmt *sqlx.Stmt
+ if unvoted && voter != nil {
+ olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanUnvotedDecision])
+ } else {
+ olderStmt, err = db.Preparex(sqlStatements[sqlCountOlderThanDecision])
+ }
if err != nil {
logger.Println("Error preparing statement:", err)
return
}
defer olderStmt.Close()
- if err := olderStmt.Get(&result, d.Proposed); err != nil {
- logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
+ if unvoted && voter != nil {
+ if err = olderStmt.Get(&result, d.Proposed, voter.Id); err != nil {
+ logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
+ }
+ } else {
+ if err = olderStmt.Get(&result, d.Proposed); err != nil {
+ logger.Printf("Error finding older motions than %s: %v\n", d.Tag, err)
+ }
}
+
return
}
func (d *Decision) Save() (err error) {
- insertDecisionStmt, err := db.PrepareNamed(sqlCreateDecision)
+ insertDecisionStmt, err := db.PrepareNamed(sqlStatements[sqlCreateDecision])
if err != nil {
logger.Println("Error preparing statement:", err)
return
@@ -359,14 +389,30 @@ func (d *Decision) Save() (err error) {
logger.Println("Error creating motion:", err)
return
}
- logger.Println(result)
- // TODO: implement fetch last id from result
- // TODO: load decision from DB
+
+ lastInsertId, err := result.LastInsertId()
+ if err != nil {
+ logger.Println("Error getting id of inserted motion:", err)
+ }
+ logger.Println("DEBUG new motion has id", lastInsertId)
+
+ getDecisionStmt, err := db.Preparex(sqlStatements[sqlLoadDecisionById])
+ if err != nil {
+ logger.Println("Error preparing statement:", err)
+ return
+ }
+ defer getDecisionStmt.Close()
+
+ err = getDecisionStmt.Get(d, lastInsertId)
+ if err != nil {
+ logger.Println("Error getting inserted motion:", err)
+ }
+
return
}
func FindVoterByAddress(emailAddress string) (voter *Voter, err error) {
- findVoterStmt, err := db.Preparex(sqlGetVoter)
+ findVoterStmt, err := db.Preparex(sqlStatements[sqlLoadEnabledVoterByEmail])
if err != nil {
logger.Println("Error preparing statement:", err)
return