add: adjust user handling per host

Change-Id: Ie04698b334883dbd513c66b8a136e9b23e90d3e4

diff --git a/README.md b/README.md
index 5470c43401c205db452a92c6448d8374a6d08e06..76444524e5882c97210c783e448348902932f3bc 100644 (file)
--- a/README.md
+++ b/README.md
@@ -196,7 +196,7 @@ set FLASK_APP=motion.py
 
 To add a user use this command
 ```
 
 To add a user use this command
 ```

-flask create-user "email address"
+flask create-user "email address" "host"

 
 ```
 
 
 ```
 

diff --git a/motion.py b/motion.py
index e878616065ba72baabbe532636179aa909949eac..a5ceb72e3501a4a02bf6a12d281d8964d1aab451 100644 (file)
--- a/motion.py
+++ b/motion.py
@@ -111,10 +111,10 @@ def lookup_user():
 
     db = get_db()
     with db.xact():
 
     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:
         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)
         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)


@@ -171,11 +171,15 @@ def may_admin(action):
     return action in g.roles
 
 def get_voters():
     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():
     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
     return rv
 
 @app.teardown_appcontext


@@ -237,6 +241,24 @@ def init_db():
                 db.execute(f.read())
                 db.prepare("UPDATE \"schema_version\" SET \"version\"=5")()
 
                 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()
 
 
 init_db()
 
 


@@ -409,11 +431,11 @@ def add_proxy():
     proxy=request.form.get("proxy", "")
     if voter == proxy :
         return _('Error, voter equals proxy.'), 400
     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")
     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")
     if len(rv) == 0:
         return _('Error, proxy not found.'), 400
     proxyid = rv[0].get("id")


@@ -449,12 +471,13 @@ def set_language(language):
 
 @app.cli.command("create-user")
 @click.argument("email")
 
 @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():
     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:
         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)
     click.echo(messagetext)

diff --git a/sql/from_5.sql b/sql/from_5.sql
new file mode 100644 (file)
index 0000000..20c54ec
--- /dev/null
+++ b/sql/from_5.sql
@@ -0,0 +1 @@
+ALTER TABLE "voter" ADD COLUMN "host" VARCHAR(500) NULL;

diff --git a/sql/sample_data.sql b/sql/sample_data.sql
index 40532deefb45a49c8cba3e2a6b321949fdb6740e..6b8054ee1f14b0106478b1a2e118d69ad5e402d8 100644 (file)
--- a/sql/sample_data.sql
+++ b/sql/sample_data.sql
@@ -1,7 +1,7 @@
--- 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
 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

diff --git a/sql/schema.sql b/sql/schema.sql
index 6e0bc561fc317131254f33a35ef1d010cc091d87..690425bdbcf697509ac116210c89fd62181190ee 100644 (file)
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1,5 +1,8 @@
 DROP TABLE IF EXISTS voter;
 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 motion;


@@ -42,4 +45,4 @@ CREATE INDEX proxy_proxy ON proxy (proxy_id);
 
 DROP TABLE IF EXISTS schema_version;
 CREATE TABLE schema_version (version INTEGER NOT NULL);
 
 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);

diff --git a/templates/proxy.html b/templates/proxy.html
index 259606e6441e67001daeeac7f5e6da8d3bef2aaf..f1cd89a62bb813c84e97e628f14c3c6d57c7a260 100644 (file)
--- a/templates/proxy.html
+++ b/templates/proxy.html
@@ -29,7 +29,6 @@
   </tr>
   </table>
 </form>
   </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">
 <form action="/proxy/revoke" method="POST">
 <div class="motion card" id="votes">
   <div class="card-heading text-white bg-info">


@@ -42,6 +41,7 @@
         <th>{{_('Proxy')}}</th>
         <th></th>
       </thead>
         <th>{{_('Proxy')}}</th>
         <th></th>
       </thead>

+      {%- if proxies %}

       {%- for row in proxies %}
       <tr>
         <td>{{row.voter_email}}</td>
       {%- for row in proxies %}
       <tr>
         <td>{{row.voter_email}}</td>


@@ -49,11 +49,11 @@
         <td><button type="submit" class="btn btn-danger" name="id" value="{{row.id}}">{{_('Revoke')}}</button></td>
       </tr>
       {%- endfor %}
         <td><button type="submit" class="btn btn-danger" name="id" value="{{row.id}}">{{_('Revoke')}}</button></td>
       </tr>
       {%- endfor %}

+      {%- endif %}

     </table>
   </div>
 </div>
 </form>
     </table>
   </div>
 </div>
 </form>

-{%- endif %}

 <form action="/proxy/revokeall" method="POST">
   <button type="submit" class="btn btn-danger">{{_('Revoke all')}}</button>
 </form>
 <form action="/proxy/revokeall" method="POST">
   <button type="submit" class="btn btn-danger">{{_('Revoke all')}}</button>
 </form>

diff --git a/tests/sql/sample_data_test_v4.sql b/tests/sql/sample_data_test_v4.sql
new file mode 100644 (file)
index 0000000..161e885
--- /dev/null
+++ b/tests/sql/sample_data_test_v4.sql
@@ -0,0 +1,27 @@
+-- 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');

diff --git a/tests/sql/schema_test_v5.sql b/tests/sql/schema_test_v5.sql
new file mode 100644 (file)
index 0000000..6e0bc56
--- /dev/null
+++ b/tests/sql/schema_test_v5.sql
@@ -0,0 +1,45 @@
+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);

diff --git a/tests/test_basics.py b/tests/test_basics.py
index 9385095a03d447c9d6cbf2578798380aff22c254..f886c34d708a77e08b0245c5ac3988c1a6727c07 100644 (file)
--- a/tests/test_basics.py
+++ b/tests/test_basics.py
@@ -10,6 +10,7 @@ app.config.update(
     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',
     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
 )
 
     MAX_PROXY=2
 )
 


@@ -33,7 +34,6 @@ class BasicTest(TestCase):
             environ_base={'USER_ROLES': user},
             data=dict(vote=vote)
         )
             environ_base={'USER_ROLES': user},
             data=dict(vote=vote)
         )

-        

 
     def createMotion(self, user, motiontitle, motioncontent, days, category):
         return self.app.post(
 
     def createMotion(self, user, motiontitle, motioncontent, days, category):
         return self.app.post(


@@ -74,13 +74,17 @@ class BasicTest(TestCase):
             + '\nNo <span class=\"badge badge-pill badge-secondary\">'+str(no)+'</span><br>'\
             + '\nAbstain <span class=\"badge badge-pill badge-secondary\">'+str(abstain)+'</span>'
 
             + '\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):
     # 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 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())
             with app.open_resource('sql/sample_data.sql', mode='r') as f:
                 db.execute(f.read())

diff --git a/tests/test_db_changes.py b/tests/test_db_changes.py
new file mode 100644 (file)
index 0000000..e39e5a9
--- /dev/null
+++ b/tests/test_db_changes.py
@@ -0,0 +1,82 @@
+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)

diff --git a/tests/test_motion.py b/tests/test_motion.py
index 2b95b1e42291438ab506c1a49a16c77236a4faea..769deb131896a029710f670bad96e0dcdcd80cca 100644 (file)
--- a/tests/test_motion.py
+++ b/tests/test_motion.py
@@ -1,5 +1,7 @@
 from datetime import datetime
 from tests.test_basics import BasicTest
 from datetime import datetime
 from tests.test_basics import BasicTest

+import postgresql
+from motion import app

 
 # no specific rights required
 class GeneralTests(BasicTest):
 
 # no specific rights required
 class GeneralTests(BasicTest):


@@ -502,6 +504,8 @@ class ProxyManagementTests(BasicTest):
         self.init_test()
         global user
         user='testuser/proxyadmin:*'
         self.init_test()
         global user
         user='testuser/proxyadmin:*'

+        global userid
+        userid=4

         self.db_sampledata()
 
     def tearDown(self):
         self.db_sampledata()
 
     def tearDown(self):


@@ -564,6 +568,12 @@ class ProxyManagementTests(BasicTest):
         response = self.addProxy(user, voter, proxy)
         self.assertEqual(response.status_code, 400)
         self.assertIn(str.encode('Error, voter equals proxy.'), response.data)
         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'
 
         voter='User A'
         proxy='User B'


@@ -702,9 +712,48 @@ class ProxyManagementTests(BasicTest):
         result = self.app.get('proxy', environ_base={'USER_ROLES': user}, follow_redirects=True)
         testtext= '<table>\n      '\
             + '<thead>\n        '\
         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'
             + '</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.assertIn(str.encode(testtext), result.data)

+        self.assertNotIn(str.encode(proxytest), result.data)

 
 class ProxyVoteTests(BasicTest):
 
 
 class ProxyVoteTests(BasicTest):
 

diff --git a/tests/test_user_api.py b/tests/test_user_api.py
index 1bec464131e1e34529c11e1ce81e9d643e9ce6d6..f1819f8d36e6a8cfee5b3f6ad0ed9d18c7c2b370 100644 (file)
--- a/tests/test_user_api.py
+++ b/tests/test_user_api.py
@@ -7,9 +7,10 @@ from click.testing import CliRunner
 from motion import create_user
 from motion import app
 
 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):
         return rv
 
 class GeneralTests(BasicTest):


@@ -24,14 +25,29 @@ class GeneralTests(BasicTest):
 
     def test_create_user(self):
         user = 'John Doe'
 
     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()
         runner = app.test_cli_runner()

-        result = runner.invoke(create_user, [user])
+        result = runner.invoke(create_user, (user, host))

         assert result.exit_code == 0
         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"))
 
         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
         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)