2 from flask import Flask
3 from flask import render_template, redirect
4 from flask import request
11 db = getattr(g, '_database', None)
13 db = g._database = postgresql.open(app.config.get("DATABASE"), user=app.config.get("USER"), password=app.config.get("PASSWORD"))
14 #db.row_factory = sqlite3.Row
18 app.register_blueprint(filters.blueprint)
21 app.config.from_pyfile('config.py')
23 prefix=app.config.get("GROUP_PREFIX")
31 if "USER_ROLES" in env:
32 parts = env.get("USER_ROLES").split("/", 1)
36 if "USER" in env and "ROLES" in env:
37 user = env.get("USER")
38 roles = env.get("ROLES")
42 return "Server misconfigured", 500
43 roles = roles.split(" ")
45 if user == "<invalid>":
46 return "Access denied", 403;
50 rv = db.prepare("SELECT id FROM voter WHERE email=$1")(user)
52 db.prepare("INSERT INTO voter(\"email\") VALUES($1)")(user)
53 rv = db.prepare("SELECT id FROM voter WHERE email=$1")(user)
54 g.voter = rv[0].get("id");
62 if a[0] not in g.roles:
65 g.roles[a[0]] = [group for group in prefix[request.host]]
67 g.roles[a[0]].append(val)
70 def get_allowed_cats(action):
71 return g.roles.get(action, []);
73 def may(action, motion):
74 return motion in get_allowed_cats(action)
76 @app.teardown_appcontext
77 def close_connection(exception):
78 db = getattr(g, '_database', None)
83 with app.app_context():
86 ver = db.prepare("SELECT version FROM schema_version")()[0][0];
87 print("Database Schema version: ", ver)
88 except postgresql.exceptions.UndefinedTableError:
92 with app.open_resource('sql/schema.sql', mode='r') as f:
97 with app.open_resource('sql/from_1.sql', mode='r') as f:
100 for g in [group for group in prefix[app.config.get("DEFAULT_HOST")]]:
101 ct[g] = {"dt": "", "c": 0}
103 p = db.prepare("UPDATE \"motion\" SET \"identifier\"=$1 WHERE \"id\"=$2")
104 for row in db.prepare("SELECT id, \"type\", \"posed\" FROM \"motion\" ORDER BY \"id\" ASC"):
105 dt=row[2].strftime("%Y%m%d")
106 if ct[row[1]]["dt"] != dt:
107 ct[row[1]]["dt"] = dt
109 ct[row[1]]["c"] = ct[row[1]]["c"] + 1
110 name=prefix[app.config.get("DEFAULT_HOST")][row[1]]+"."+dt+"."+("%03d" % ct[row[1]]["c"])
112 db.prepare("ALTER TABLE \"motion\" ALTER COLUMN \"identifier\" SET NOT NULL")()
113 db.prepare("UPDATE \"schema_version\" SET \"version\"=2")()
114 db.prepare("CREATE UNIQUE INDEX motion_ident ON motion (identifier)")()
117 with app.open_resource('sql/from_2.sql', mode='r') as f:
119 db.prepare("UPDATE \"motion\" SET \"host\"=$1")(app.config.get("DEFAULT_HOST"))
120 db.prepare("ALTER TABLE \"motion\" ALTER COLUMN \"host\" SET NOT NULL")()
121 db.prepare("UPDATE \"schema_version\" SET \"version\"=3")()
128 start=int(request.args.get("start", "-1"));
129 q = "SELECT motion.*, votes.*, poser.email AS poser, canceler.email AS canceler, (motion.deadline > CURRENT_TIMESTAMP AND canceled is NULL) AS running FROM motion LEFT JOIN (SELECT motion_id, "\
130 + "COUNT(CASE WHEN result='yes' THEN 'yes' ELSE NULL END) as yes, "\
131 + "COUNT(CASE WHEN result='no' THEN 'no' ELSE NULL END) as no, "\
132 + "COUNT(CASE WHEN result='abstain' THEN 'abstain' ELSE NULL END) as abstain "\
133 + "FROM vote GROUP BY motion_id) as votes ON votes.motion_id=motion.id "\
134 + "LEFT JOIN voter poser ON poser.id = motion.posed_by "\
135 + "LEFT JOIN voter canceler ON canceler.id = motion.canceled_by "
138 p = get_db().prepare(q + "WHERE motion.host = $1 ORDER BY motion.id DESC LIMIT 11")
141 p = get_db().prepare(q + "WHERE motion.host = $1 AND motion.id <= $2 ORDER BY motion.id DESC LIMIT 11")
142 rv = p(request.host, start)
143 rs = get_db().prepare("SELECT id FROM motion WHERE motion.host = $1 AND motion.id > $2 ORDER BY id ASC LIMIT 10")(request.host, start)
148 return render_template('index.html', motions=rv[:10], more=rv[10]["id"] if len(rv) == 11 else None, times=times, prev=prev,
149 categories=get_allowed_cats("create"))
151 def rel_redirect(loc):
153 r.autocorrect_location_header = False
156 @app.route("/motion", methods=['POST'])
158 cat=request.form.get("category", "")
159 if cat not in get_allowed_cats("create"):
160 return "Forbidden", 403
161 time = int(request.form.get("days", "3"));
162 if time not in times:
163 return "Error, invalid length", 500
166 t = db.prepare("SELECT CURRENT_TIMESTAMP")()[0][0];
167 s = db.prepare("SELECT MAX(\"identifier\") FROM \"motion\" WHERE \"type\"=$1 AND \"host\"=$2 AND DATE(\"posed\")=DATE(CURRENT_TIMESTAMP)")
168 sr = s(cat, request.host)
170 if len(sr) == 0 or sr[0][0] is None:
171 ident=prefix[request.host][cat]+"."+t.strftime("%Y%m%d")+".001"
173 ident=prefix[request.host][cat]+"."+t.strftime("%Y%m%d")+"."+("%03d" % (int(sr[0][0].split(".")[2])+1))
174 p = db.prepare("INSERT INTO motion(\"name\", \"content\", \"deadline\", \"posed_by\", \"type\", \"identifier\", \"host\") VALUES($1, $2, CURRENT_TIMESTAMP + $3 * interval '1 days', $4, $5, $6, $7)")
175 p(request.form.get("title", ""), request.form.get("content",""), time, g.voter, cat, ident, request.host)
176 return rel_redirect("/")
178 def motion_edited(motion):
179 return rel_redirect("/?start=" + str(motion) + "#motion-" + str(motion))
181 @app.route("/motion/<string:motion>/cancel", methods=['POST'])
182 def cancel_motion(motion):
183 rv = get_db().prepare("SELECT id, type FROM motion WHERE identifier=$1 AND host=$2")(motion, request.host);
185 return "Error, Not found", 404
187 if not may("cancel", rv[0].get("type")):
188 return "Forbidden", 403
189 if request.form.get("reason", "none") == "none":
190 return "Error, form requires reason", 500
191 rv = get_db().prepare("UPDATE motion SET canceled=CURRENT_TIMESTAMP, cancelation_reason=$1, canceled_by=$2 WHERE identifier=$3 AND host=$4 AND canceled is NULL")(request.form.get("reason", ""), g.voter, motion, request.host)
192 return motion_edited(id)
194 @app.route("/motion/<string:motion>")
195 def show_motion(motion):
196 p = get_db().prepare("SELECT motion.*, poser.email AS poser, canceler.email AS canceler, (motion.deadline > CURRENT_TIMESTAMP AND canceled is NULL) AS running, vote.result FROM motion "\
197 + "LEFT JOIN vote on vote.motion_id=motion.id AND vote.voter_id=$2 "\
198 + "LEFT JOIN voter poser ON poser.id = motion.posed_by "\
199 + "LEFT JOIN voter canceler ON canceler.id = motion.canceled_by "
200 + "WHERE motion.identifier=$1 AND motion.host=$3")
201 rv = p(motion, g.voter, request.host)
203 if may("audit", rv[0].get("type")) and not rv[0].get("running") and not rv[0].get("canceled"):
204 votes = get_db().prepare("SELECT vote.result, voter.email FROM vote INNER JOIN voter ON voter.id = vote.voter_id WHERE vote.motion_id=$1")(rv[0].get("id"));
205 return render_template('single_motion.html', motion=rv[0], may_vote=may("vote", rv[0].get("type")), may_cancel=may("cancel", rv[0].get("type")), votes=votes)
207 @app.route("/motion/<string:motion>/vote", methods=['POST'])
209 v = request.form.get("vote", "abstain")
212 rv = db.prepare("SELECT id, type FROM motion WHERE identifier=$1 AND host=$2")(motion, request.host);
214 return "Error, Not found", 404
215 if not may("vote", rv[0].get("type")):
216 return "Forbidden", 403
217 p = db.prepare("SELECT deadline > CURRENT_TIMESTAMP FROM motion WHERE identifier = $1 AND host=$2")
219 if not p(motion, request.host)[0][0]:
220 return "Error, motion deadline has passed", 500
221 p = db.prepare("SELECT * FROM vote WHERE motion_id = $1 AND voter_id = $2")
224 db.prepare("INSERT INTO vote(motion_id, voter_id, result) VALUES($1,$2,$3)")(id, g.voter, v)
226 db.prepare("UPDATE vote SET result=$3, entered=CURRENT_TIMESTAMP WHERE motion_id=$1 AND voter_id = $2")(id, g.voter, v)
227 return motion_edited(id)