Add new table user_roles

This commit adds a new database table user_roles to prepare for the
introduction of a voter management system. All existing enabled voters
are added to the VOTER role.

1 files changed, 15 insertions, 9 deletions

diff --git a/models.go b/models.go
index 19d701c..2ce7db4 100644
--- a/models.go
+++ b/models.go
@@ -1,5 +1,5 @@
 /*
-   Copyright 2017-2019 Jan Dittberner
+   Copyright 2017-2020 Jan Dittberner
 
    Licensed under the Apache License, Version 2.0 (the "License");
    you may not use this program except in compliance with the License.
@@ -86,14 +86,16 @@ FROM   votes
 JOIN   voters ON votes.voter=voters.id
 WHERE  decision=$1`,
 	sqlLoadEnabledVoterByEmail: `
-SELECT voters.id, voters.name, voters.enabled, voters.reminder
+SELECT voters.id, voters.name, voters.reminder
 FROM   voters
 JOIN   emails ON voters.id=emails.voter
-WHERE  emails.address=$1 AND voters.enabled=1`,
+JOIN   user_roles ON user_roles.voter_id=voters.id
+WHERE  emails.address=$1 AND user_roles.role='VOTER'`,
 	sqlGetEnabledVoterById: `
-SELECT id, name, enabled, reminder
+SELECT voters.id, voters.name, voters.reminder
 FROM   voters
-WHERE  enabled=1 AND id=$1`,
+JOIN   user_roles ON user_roles.voter_id=voters.id
+WHERE  user_roles.role='VOTER' AND voters.id=$1`,
 	sqlCountOlderThanDecision: `
 SELECT COUNT(*) > 0 FROM decisions WHERE proposed < $1`,
 	sqlCountOlderThanUnvotedDecision: `
@@ -123,10 +125,15 @@ WHERE  decisions.status=0 AND :now > due`,
 	sqlGetNextPendingDecisionDue: `
 SELECT due FROM decisions WHERE status=0 ORDER BY due LIMIT 1`,
 	sqlGetVotersForProxy: `
-SELECT id, name, reminder
-FROM   voters WHERE enabled=1 AND id != $1`,
+SELECT voters.id, voters.name, voters.reminder
+FROM   voters
+JOIN   user_roles ON user_roles.voter_id=voters.id
+WHERE  user_roles.role='VOTER' AND voters.id != $1`,
 	sqlGetReminderVoters: `
-SELECT id, name, reminder FROM voters WHERE enabled=1 AND reminder!='' AND reminder IS NOT NULL`,
+SELECT voters.id, voters.name, voters.reminder
+FROM   voters
+JOIN   user_roles ON user_roles.voter_id=voters.id
+WHERE  user_roles.role='VOTER' AND reminder!='' AND reminder IS NOT NULL`,
 	sqlFindUnvotedDecisionsForVoter: `
 SELECT tag, title, votetype, due
 FROM   decisions
@@ -162,7 +169,6 @@ type Decision struct {
 type Voter struct {
 	Id       int64
 	Name     string
-	Enabled  bool
 	Reminder string // reminder email address
 }