diff options
| -rw-r--r-- | README.md | 2 | ||||
| -rw-r--r-- | boardvoting/migrations/20200414225500_add_roles_table.sql | 34 | ||||
| -rw-r--r-- | models.go | 24 |
3 files changed, 50 insertions, 10 deletions
@@ -6,7 +6,7 @@ This project contains the source code for the CAcert board voting software. The CAcert board voting software is licensed under the terms of the Apache License, Version 2.0. - 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. diff --git a/boardvoting/migrations/20200414225500_add_roles_table.sql b/boardvoting/migrations/20200414225500_add_roles_table.sql new file mode 100644 index 0000000..c36b66c --- /dev/null +++ b/boardvoting/migrations/20200414225500_add_roles_table.sql @@ -0,0 +1,34 @@ +/* + Copyright 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. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +*/ + +-- +migrate Up +-- SQL in section 'Up' is executed when this migration is applied +CREATE TABLE user_roles +( + id INTEGER PRIMARY KEY AUTOINCREMENT, + voter_id VARCHAR(255) NOT NULL REFERENCES voters (id), + role VARCHAR(8) NOT NULL, + created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + UNIQUE (voter_id, role) +); +INSERT INTO user_roles (voter_id, role) +SELECT id, 'VOTER' +FROM voters +WHERE enabled = true; + +-- +migrate Down +-- SQL section 'Down' is executed when this migration is rolled back +DROP TABLE user_roles;
\ No newline at end of file @@ -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 } |