To add a user use this command
```
-flask create-user "email address"
+flask create-user "email address" "host"
```
db = get_db()
with db.xact():
- rv = db.prepare("SELECT id FROM voter WHERE email=$1")(user)
+ rv = db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")(user, request.host)
if len(rv) == 0:
- db.prepare("INSERT INTO voter(\"email\") VALUES($1)")(user)
- rv = db.prepare("SELECT id FROM voter WHERE email=$1")(user)
+ db.prepare("INSERT INTO voter(\"email\", \"host\") VALUES($1, $2)")(user, request.host)
+ rv = db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")(user, request.host)
g.voter = rv[0].get("id");
g.proxies_given = ""
rv = db.prepare("SELECT email, voter_id FROM voter, proxy WHERE proxy.proxy_id = voter.id AND proxy.revoked IS NULL AND proxy.voter_id = $1 ")(g.voter)
return action in g.roles
def get_voters():
- rv = get_db().prepare("SELECT email FROM voter")
+ rv = get_db().prepare("SELECT email FROM voter WHERE host=$1")(request.host)
return rv
def get_all_proxies():
- rv = get_db().prepare("SELECT p.id as id, v1.email as voter_email, v1.id as voterid, v2.email as proxy_email, v2.id as proxyid FROM voter AS v1, voter AS v2, proxy AS p WHERE v2.id = p.proxy_id AND v1.id = p.voter_id AND p.revoked is NULL ORDER BY voter_email, proxy_email")
+ rv = get_db().prepare("SELECT p.id as id, v1.email as voter_email, v1.id as voterid, "\
+ + "v2.email as proxy_email, v2.id as proxyid "\
+ + "FROM voter AS v1, voter AS v2, proxy AS p "\
+ + "WHERE v2.id = p.proxy_id AND v1.id = p.voter_id AND p.revoked is NULL "\
+ + "AND v1.host=$1 AND v2.host=$1 ORDER BY voter_email, proxy_email")(request.host)
return rv
@app.teardown_appcontext
db.execute(f.read())
db.prepare("UPDATE \"schema_version\" SET \"version\"=5")()
+ if ver < 6:
+ with app.open_resource('sql/from_5.sql', mode='r') as f:
+ db.execute(f.read())
+ rv=db.prepare("INSERT INTO voter (email, host) (SELECT vt.email, m.host FROM motion AS m, voter AS vt, vote as v "\
+ + "WHERE (m.id=v.motion_id AND v.voter_id = vt.id) OR (m.id=v.motion_id AND v.proxy_id = vt.id) "\
+ + "GROUP BY m.host, vt.email ORDER BY m.host, vt.email)")()
+ rv=db.prepare("UPDATE vote SET voter_id = "\
+ + "(SELECT v_new.id FROM motion AS m, voter AS v_new, voter as v_old "\
+ + "WHERE v_new.email = v_old.email AND v_old.id = vote.voter_id AND "\
+ + "vote.motion_id = m.id AND m.host = v_new.host AND v_old.host is NULL)")()
+ rv=db.prepare("UPDATE vote SET proxy_id = "\
+ + "(SELECT v_new.id FROM motion AS m, voter AS v_new, voter as v_old "\
+ + "WHERE v_new.email = v_old.email AND v_old.id = vote.proxy_id AND "\
+ + "vote.motion_id = m.id AND m.host = v_new.host AND v_old.host is NULL)")()
+ db.prepare("DELETE FROM voter WHERE host IS Null")()
+ db.prepare("ALTER TABLE \"voter\" ALTER COLUMN \"host\" SET NOT NULL")()
+ db.prepare("UPDATE \"schema_version\" SET \"version\"=6")()
+
init_db()
proxy=request.form.get("proxy", "")
if voter == proxy :
return _('Error, voter equals proxy.'), 400
- rv = get_db().prepare("SELECT id FROM voter WHERE email=$1")(voter);
+ rv = get_db().prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")(voter, request.host);
if len(rv) == 0:
return _('Error, voter not found.'), 400
voterid = rv[0].get("id")
- rv = get_db().prepare("SELECT id FROM voter WHERE email=$1")(proxy);
+ rv = get_db().prepare("SELECT id, host FROM voter WHERE email=$1 AND host=$2")(proxy, request.host);
if len(rv) == 0:
return _('Error, proxy not found.'), 400
proxyid = rv[0].get("id")
@app.cli.command("create-user")
@click.argument("email")
-def create_user(email):
+@click.argument("host")
+def create_user(email, host):
db = get_db()
with db.xact():
- rv = db.prepare("SELECT id FROM voter WHERE lower(email)=lower($1)")(email)
- messagetext=_("User '%s' already exists.") % (email)
+ rv = db.prepare("SELECT id FROM voter WHERE lower(email)=lower($1) AND host=$2")(email, host)
+ messagetext=_("User '%s' already exists on %s.") % (email, host)
if len(rv) == 0:
- db.prepare("INSERT INTO voter(\"email\") VALUES($1)")(email)
- messagetext=_("User '%s' inserted.") % (email)
+ db.prepare("INSERT INTO voter(\"email\", \"host\") VALUES($1, $2)")(email, host)
+ messagetext=_("User '%s' inserted to %s.") % (email, host)
click.echo(messagetext)
--- /dev/null
+ALTER TABLE "voter" ADD COLUMN "host" VARCHAR(500) NULL;
--- sample data for scheme version 4
-INSERT INTO voter (id,email) VALUES (1, 'User A');
-INSERT INTO voter (id,email) VALUES (2, 'User B');
-INSERT INTO voter (id,email) VALUES (3, 'User C');
+-- sample data for scheme version 6
+INSERT INTO voter (id,email, host) VALUES (1, 'User A', '127.0.0.1:5000');
+INSERT INTO voter (id,email, host) VALUES (2, 'User B', '127.0.0.1:5000');
+INSERT INTO voter (id,email, host) VALUES (3, 'User C', '127.0.0.1:5000');
ALTER SEQUENCE voter_id_seq RESTART WITH 4;
INSERT INTO motion (id,identifier,name,type,host,content,posed,posed_by,deadline,canceled,cancelation_reason,canceled_by) VALUES
DROP TABLE IF EXISTS voter;
-CREATE TABLE voter (id serial NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY(id));
+CREATE TABLE voter (id serial NOT NULL,
+ email VARCHAR(255) NOT NULL,
+ host VARCHAR(500) NOT NULL,
+ PRIMARY KEY(id));
DROP TABLE IF EXISTS motion;
DROP TABLE IF EXISTS schema_version;
CREATE TABLE schema_version (version INTEGER NOT NULL);
-INSERT INTO schema_version(version) VALUES(5);
+INSERT INTO schema_version(version) VALUES(6);
</tr>
</table>
</form>
-{%- if proxies %}
<form action="/proxy/revoke" method="POST">
<div class="motion card" id="votes">
<div class="card-heading text-white bg-info">
<th>{{_('Proxy')}}</th>
<th></th>
</thead>
+ {%- if proxies %}
{%- for row in proxies %}
<tr>
<td>{{row.voter_email}}</td>
<td><button type="submit" class="btn btn-danger" name="id" value="{{row.id}}">{{_('Revoke')}}</button></td>
</tr>
{%- endfor %}
+ {%- endif %}
</table>
</div>
</div>
</form>
-{%- endif %}
<form action="/proxy/revokeall" method="POST">
<button type="submit" class="btn btn-danger">{{_('Revoke all')}}</button>
</form>
--- /dev/null
+-- sample data for scheme version 4
+INSERT INTO voter (id,email) VALUES (1, 'User A');
+INSERT INTO voter (id,email) VALUES (2, 'User B');
+INSERT INTO voter (id,email) VALUES (3, 'User C');
+INSERT INTO voter (id,email) VALUES (4, 'User D');
+INSERT INTO voter (id,email) VALUES (5, 'User E');
+ALTER SEQUENCE voter_id_seq RESTART WITH 6;
+
+INSERT INTO motion (id,identifier,name,type,host,content,posed,posed_by,deadline,canceled,cancelation_reason,canceled_by) VALUES
+ (1,'g1.20200402.001','Motion A','group1','127.0.0.1:5000','My special motion','2020-04-02 21:40:33.780364',1,'2020-04-02 21:40:33.780364',Null,Null,Null);
+INSERT INTO motion (id,identifier,name,type,host,content,posed,posed_by,deadline,canceled,cancelation_reason,canceled_by) VALUES
+ (2,'g1.20200402.002','Motion B','group1','127.0.0.1:5001','A second motion','2020-04-02 21:41:26.588442',1,'2020-04-04 21:41:26.588442',Null,Null,Null);
+INSERT INTO motion (id,identifier,name,type,host,content,posed,posed_by,deadline,canceled,cancelation_reason,canceled_by) VALUES
+ (3,'g1.20200402.003','Motion C','group1','127.0.0.1:5000','A third motion', '2020-04-02 21:47:24.969588',1,'2020-04-04 21:47:24.969588','2020-04-03 21:48:24.969588','Entered with wrong text',1);
+-- add motion with timespan from now to 1 day from now
+INSERT INTO motion (id,identifier,name,type,host,content,posed,posed_by,deadline,canceled,cancelation_reason,canceled_by) VALUES
+ (4,'g1.20200402.004','Motion D','group1','127.0.0.1:5000','A fourth motion', current_timestamp ,1,current_timestamp + interval '1' day,Null,Null,Null);
+ALTER SEQUENCE motion_id_seq RESTART WITH 5;
+
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (1,1,1,'yes','2020-04-02 21:54:34.469784');
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (1,2,2,'yes','2020-04-02 21:54:34.469784');
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (1,3,2,'no','2020-04-02 21:54:34.469784');
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (2,1,1,'yes','2020-04-02 21:54:34.469784');
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (2,2,2,'no','2020-04-02 21:54:34.469784');
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (2,3,3,'no','2020-04-02 21:54:34.469784');
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (3,3,3,'yes','2020-04-02 21:48:34.469784');
+INSERT INTO vote (motion_id,voter_id,proxy_id,result,entered) VALUES (4,1,5,'yes','2020-04-02 21:48:34.469784');
--- /dev/null
+DROP TABLE IF EXISTS voter;
+CREATE TABLE voter (id serial NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY(id));
+
+
+DROP TABLE IF EXISTS motion;
+CREATE TABLE motion (id serial NOT NULL,
+ identifier VARCHAR(20) NOT NULL,
+ name VARCHAR(250) NOT NULL,
+ type VARCHAR(250) NOT NULL,
+ host VARCHAR(500) NOT NULL,
+ content text NOT NULL,
+ posed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ posed_by int NOT NULL,
+ deadline timestamp NOT NULL DEFAULT (CURRENT_TIMESTAMP + interval '3 days'),
+ canceled timestamp NULL DEFAULT NULL,
+ cancelation_reason text NULL DEFAULT NULL,
+ canceled_by int NULL DEFAULT NULL,
+ PRIMARY KEY(id));
+CREATE UNIQUE INDEX motion_ident ON motion (identifier);
+
+DROP TABLE IF EXISTS vote;
+DROP TYPE IF EXISTS "vote_type";
+CREATE TYPE "vote_type" AS ENUM ('yes', 'no', 'abstain');
+CREATE TABLE vote (motion_id INTEGER NOT NULL,
+ voter_id INTEGER NOT NULL,
+ result vote_type NOT NULL,
+ entered timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ proxy_id INTEGER NOT NULL,
+ PRIMARY KEY(motion_id, voter_id));
+
+DROP TABLE IF EXISTS proxy;
+CREATE TABLE proxy (id serial NOT NULL,
+ voter_id INTEGER NOT NULL,
+ proxy_id INTEGER NOT NULL,
+ granted timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ granted_by int NOT NULL,
+ revoked timestamp NULL DEFAULT NULL,
+ revoked_by int NULL DEFAULT NULL,
+ PRIMARY KEY(id));
+CREATE INDEX proxy_voter ON proxy (voter_id);
+CREATE INDEX proxy_proxy ON proxy (proxy_id);
+
+DROP TABLE IF EXISTS schema_version;
+CREATE TABLE schema_version (version INTEGER NOT NULL);
+INSERT INTO schema_version(version) VALUES(5);
GROUP_PREFIX = {'127.0.0.1:5000': {'group1': 'g1', 'group2': 'g2'}},
DURATION = {'127.0.0.1:5000':[3, 7, 14]},
SERVER_NAME = '127.0.0.1:5000',
+ DEFAULT_HOST = '127.0.0.1:5000',
MAX_PROXY=2
)
environ_base={'USER_ROLES': user},
data=dict(vote=vote)
)
-
def createMotion(self, user, motiontitle, motioncontent, days, category):
return self.app.post(
+ '\nNo <span class=\"badge badge-pill badge-secondary\">'+str(no)+'</span><br>'\
+ '\nAbstain <span class=\"badge badge-pill badge-secondary\">'+str(abstain)+'</span>'
+
+ def open_DB(self):
+ return postgresql.open(app.config.get("DATABASE"), user=app.config.get("USER"), password=app.config.get("PASSWORD"))
+
# functions to clear database
def db_clear(self):
- with postgresql.open(app.config.get("DATABASE"), user=app.config.get("USER"), password=app.config.get("PASSWORD")) as db:
+ with self.open_DB() as db:
with app.open_resource('sql/schema.sql', mode='r') as f:
db.execute(f.read())
def db_sampledata(self):
- with postgresql.open(app.config.get("DATABASE"), user=app.config.get("USER"), password=app.config.get("PASSWORD")) as db:
+ with self.open_DB() as db:
with app.open_resource('sql/sample_data.sql', mode='r') as f:
db.execute(f.read())
--- /dev/null
+import motion
+from motion import app
+from motion import init_db
+from tests.test_basics import BasicTest
+
+
+class DatabaseTests(BasicTest):
+ def setUp(self):
+ global user
+ user = 'testuser/'
+
+ def tearDown(self):
+ pass
+
+ def test_V5(self):
+ with self.open_DB() as db:
+ with app.open_resource('tests/sql/schema_test_v5.sql', mode='r') as f:
+ db.execute(f.read())
+ with app.open_resource('tests/sql/sample_data_test_v4.sql', mode='r') as f:
+ db.execute(f.read())
+
+ init_db()
+
+ ver = db.prepare("SELECT version FROM schema_version")()[0][0]
+ self.assertGreaterEqual(ver,6)
+
+ # test motion 1
+ motion_id=1
+ host=app.config.get("DEFAULT_HOST")
+ aid=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User A", host)[0][0]
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, aid)
+ self.assertEqual(rn[0].get("result"),"yes")
+ self.assertEqual(rn[0].get("proxy_id"),aid)
+
+ bid=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User B", host)[0][0]
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, bid)
+ self.assertEqual(rn[0].get("result"),"yes")
+ self.assertEqual(rn[0].get("proxy_id"),bid)
+ # proxy vote where proxy voted herself too
+ cid=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User C", host)[0][0]
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, cid)
+ self.assertEqual(rn[0].get("result"),"no")
+ self.assertEqual(rn[0].get("proxy_id"),bid)
+
+ # test motion 3
+ motion_id=3
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, cid)
+ self.assertEqual(rn[0].get("result"),"yes")
+ self.assertEqual(rn[0].get("proxy_id"),cid)
+
+ # test motion 4 and proxy vote where proxy did not vote herself
+ motion_id=4
+ eid=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User E", host)[0][0]
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, aid)
+ self.assertEqual(rn[0].get("result"),"yes")
+ self.assertEqual(rn[0].get("proxy_id"),eid)
+
+ # test motion 2
+ motion_id=2
+ host='127.0.0.1:5001'
+ aid=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User A", host)[0][0]
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, aid)
+ self.assertEqual(rn[0].get("result"),"yes")
+ self.assertEqual(rn[0].get("proxy_id"),aid)
+
+ bid=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User B", host)[0][0]
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, bid)
+ self.assertEqual(rn[0].get("result"),"no")
+ self.assertEqual(rn[0].get("proxy_id"),bid)
+
+ cid=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User C", host)[0][0]
+ rn=db.prepare("SELECT * FROM vote WHERE motion_id=$1 AND voter_id=$2")(motion_id, cid)
+ self.assertEqual(rn[0].get("result"),"no")
+ self.assertEqual(rn[0].get("proxy_id"),cid)
+
+ # User E not in host '127.0.0.1:5001'
+ rn=db.prepare("SELECT id FROM voter WHERE email=$1 AND host=$2")("User E", host)
+ self.assertEqual(len(rn),0)
+
+ # deleted User D
+ rn=db.prepare("SELECT id FROM voter WHERE email=$1")("User D")
+ self.assertEqual(len(rn),0)
from datetime import datetime
from tests.test_basics import BasicTest
+import postgresql
+from motion import app
# no specific rights required
class GeneralTests(BasicTest):
self.init_test()
global user
user='testuser/proxyadmin:*'
+ global userid
+ userid=4
self.db_sampledata()
def tearDown(self):
response = self.addProxy(user, voter, proxy)
self.assertEqual(response.status_code, 400)
self.assertIn(str.encode('Error, voter equals proxy.'), response.data)
+
+ voter='User A'
+ proxy='User Z'
+ response = self.addProxy(user, voter, proxy)
+ self.assertEqual(response.status_code, 400)
+ self.assertIn(str.encode('Error, proxy not found.'), response.data)
voter='User A'
proxy='User B'
result = self.app.get('proxy', environ_base={'USER_ROLES': user}, follow_redirects=True)
testtext= '<table>\n '\
+ '<thead>\n '\
- + '<th>Voter</th>\n <th>Proxy</th>\n <th></th>\n </thead>\n '\
+ + '<th>Voter</th>\n <th>Proxy</th>\n <th></th>\n </thead>\n'\
+ '</table>\n'
+ self.assertNotIn(str.encode(testtext), result.data)
+
+ proxytest="proxytest"
+ with self.open_DB() as db:
+ db.prepare("INSERT INTO voter(\"email\", \"host\") VALUES($1, $2)")(proxytest, '127.0.0.1:5001')
+ result = self.app.get('proxy', environ_base={'USER_ROLES': user}, follow_redirects=True)
+
+ response = self.addProxy(user, proxytest, 'testuser')
+ self.assertEqual(response.status_code, 400)
+ self.assertIn(str.encode('Error, voter not found.'), response.data)
+
+ response = self.addProxy(user, 'testuser', proxytest)
+ self.assertEqual(response.status_code, 400)
+ self.assertIn(str.encode('Error, proxy not found.'), response.data)
+
+ def test_see_proxy_host_only(self):
+ proxytest="proxytest"
+ with self.open_DB() as db:
+ db.prepare("INSERT INTO voter(\"email\", \"host\") VALUES($1, $2)")(proxytest, '127.0.0.1:5001')
+ result = self.app.get('proxy', environ_base={'USER_ROLES': user}, follow_redirects=True)
+ testtext= 'div class="container">\n<form action="/proxy/add" method="POST">'
+ self.assertIn(str.encode(testtext), result.data)
+ testtext= 'proxy granted to:'
+ self.assertNotIn(str.encode(testtext), result.data)
+ testtext= 'holds proxy of:'
+ self.assertNotIn(str.encode(testtext), result.data)
+ testtext= '<select class="float form-control" name="voter">\n '\
+ + '<option>User A</option>\n <option>User B</option>\n '\
+ + '<option>User C</option>\n '\
+ + '<option>testuser</option>\n '\
+ + '</select>\n'
+ self.assertIn(str.encode(testtext), result.data)
+ testtext= '<select class="float form-control" name="proxy">\n '\
+ + '<option>User A</option>\n '\
+ + '<option>User B</option>\n '\
+ + '<option>User C</option>\n '\
+ + '<option>testuser</option>\n '\
+ + '</select>\n'
self.assertIn(str.encode(testtext), result.data)
+ self.assertNotIn(str.encode(proxytest), result.data)
class ProxyVoteTests(BasicTest):
from motion import create_user
from motion import app
-def db_select(self, sql, parameter):
- with postgresql.open(app.config.get("DATABASE"), user=app.config.get("USER"), password=app.config.get("PASSWORD")) as db:
- rv = db.prepare(sql)(parameter)
+
+def db_select2(self, sql, parameter, parameter2):
+ with self.open_DB() as db:
+ rv = db.prepare(sql)(parameter, parameter2)
return rv
class GeneralTests(BasicTest):
def test_create_user(self):
user = 'John Doe'
+ host= app.config.get("DEFAULT_HOST")
+ runner = app.test_cli_runner()
+ result = runner.invoke(create_user, (user, host))
+ assert result.exit_code == 0
+ self.assertIn("User 'John Doe' inserted to %s." % host, result.output)
+
+ rv = db_select2(self,"SELECT email FROM voter WHERE lower(email)=lower($1) AND host=$2", user, host)
+ self.assertIn(user, rv[0].get("email"))
+
+ result = runner.invoke(create_user, (user, host))
+ assert result.exit_code == 0
+ self.assertIn("User 'John Doe' already exists on %s." % host, result.output)
+
+ # test with second host
+ host= '127.0.0.1:5001'
runner = app.test_cli_runner()
- result = runner.invoke(create_user, [user])
+ result = runner.invoke(create_user, (user, host))
assert result.exit_code == 0
- self.assertIn("User 'John Doe' inserted.", result.output)
+ self.assertIn("User 'John Doe' inserted to 127.0.0.1:5001.", result.output)
- rv = db_select(self,"SELECT email FROM voter WHERE lower(email)=lower($1)", user)
+ rv = db_select2(self,"SELECT email FROM voter WHERE lower(email)=lower($1) AND host=$2", user, host)
self.assertIn(user, rv[0].get("email"))
- result = runner.invoke(create_user, [user])
+ result = runner.invoke(create_user, (user, host))
assert result.exit_code == 0
- self.assertIn("User 'John Doe' already exists.", result.output)
+ self.assertIn("User 'John Doe' already exists on 127.0.0.1:5001.", result.output)