server.py 12.9 KB
Newer Older
1
from flask import Flask, g, request, url_for, redirect, session, render_template, flash, Response
2
from werkzeug.routing import Rule
3
from functools import wraps
4
from datetime import date, timedelta, datetime, time
5
import threading
6
import os
7

8
app = Flask(__name__)
9

Andreas Valder's avatar
Andreas Valder committed
10
11
12
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True

13
14
15
16
17
18
19
20
21
22
def timer_func():
	with app.test_request_context():
		pass # do something
	timer = threading.Timer(60*60, timer_func)
	timer.start()

timer = threading.Timer(0, timer_func)
timer.daemon = True
timer.start()

23
config = app.config
24
25
config['DB_SCHEMA'] = 'db_schema.sql'
config['DB_DATA'] = 'db_example.sql'
26
27
28
config['DB_ENGINE'] = 'sqlite'
config['SQLITE_DB'] = 'db.sqlite'
config['SQLITE_INIT_SCHEMA'] = True
29
config['SQLITE_INIT_DATA'] = False
30
config['DEBUG'] = False
31
config['VIDEOPREFIX'] = 'https://videoag.fsmpi.rwth-aachen.de'
32
33
34
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
35
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
36
37
if config['DEBUG']:
	app.jinja_env.auto_reload = True
Julian Rother's avatar
Julian Rother committed
38

39
from db import query, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
40

41
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
42

Julian Rother's avatar
Cleanup    
Julian Rother committed
43
@app.template_global()
44
45
46
47
def ismod(*args):
	return ('user' in session)

def mod_required(func):
48
	mod_endpoints.append(func.__name__)
49
50
	@wraps(func)
	def decorator(*args, **kwargs):
51
		if not ismod():
52
53
54
55
56
57
			flash('Diese Funktion ist nur für Moderatoren verfügbar!')
			return redirect(url_for('login', ref=request.url))
		else:
			return func(*args, **kwargs)
	return decorator

58
app.jinja_env.globals['navbar'] = []
59
def register_navbar(name, icon=None):
60
	def wrapper(func):
61
62
63
		endpoint = func.__name__
		app.jinja_env.globals['navbar'].append((endpoint, name, icon,
					not endpoint in mod_endpoints))
64
65
66
		return func
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
67
68
69
70
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
71
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
72
73
	return app.view_functions[endpoint](**kargs)

74
75
76
77
78
79
80
81
82
83
84
def handle_errors(endpoint, text, code, *errors, **epargs):
	def wrapper(func):
		@wraps(func)
		def decorator(*args, **kwargs):
			try:
				return func(*args, **kwargs)
			except errors:
				return render_endpoint(endpoint, text, **epargs), code
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
85
86
@app.errorhandler(404)
def handle_not_found(e):
87
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
88

89
@app.route('/')
90
@register_navbar('Home', icon='home')
91
def index():
92
	return render_template('index.html', latestvideos=query('''
93
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
94
95
96
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
97
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
98
99
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
100
				LIMIT 6
101
			''', ismod()))
102

103
@app.route('/course')
104
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
105
def course():
106
107
108
109
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed))', ismod())
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
110
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
111
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
112
		groupedby = 'semester'
113
	return render_template('course.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
114

Andreas Valder's avatar
Andreas Valder committed
115
@app.route('/course/<id>')
116
@app.route('/course/<int:numid>')
117
118
@handle_errors('course', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
def course_id(numid=None, id=None):
119
	if numid:
Andreas Valder's avatar
Andreas Valder committed
120
		courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', numid, ismod())[0]
121
	else:
Andreas Valder's avatar
Andreas Valder committed
122
123
		courses = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', id, ismod())[0]
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible)', courses['id'], ismod())
Andreas Valder's avatar
Andreas Valder committed
124
125
126
127
128
129
130
131
	videos = query('''
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description
			FROM videos
			JOIN lectures ON (videos.lecture_id = lectures.id)
			JOIN formats ON (videos.video_format = formats.id)
			JOIN courses ON (lectures.course_id = courses.id)
			WHERE lectures.course_id= ? AND (? OR videos.visible)
			ORDER BY lectures.time, formats.prio DESC
Andreas Valder's avatar
Andreas Valder committed
132
133
			''', courses['id'], ismod())
	return render_template('course_id.html', course=courses, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
134

Andreas Valder's avatar
Andreas Valder committed
135
@app.route('/faq')
136
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
137
def faq():
138
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
139

140
@app.route('/play/<int:id>')
141
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
142
def play(id):
143
144
145
146
147
148
	lectures = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())
	videos = query('SELECT * FROM videos WHERE lecture_id = ? AND (? OR visible)', id, ismod())
	if not videos:
		flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR (visible AND listed))', lectures[0]['course_id'], ismod())
	if not courses:
149
		return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404
150
	return render_template('play.html', course=courses[0], lecture=lectures[0], videos=videos)
151
152
153
154
155
156
157

@app.route('/search')
def search():
	if 'q' not in request.args:
		return redirect(url_for('index'))
	q = request.args['q']
	courses = searchquery(q, '*', ['title', 'short', 'organizer', 'subject', 'description'],
158
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
159
160
161
	lectures = searchquery(q, 'lectures.*, courses.visible AS coursevisible, courses.listed, courses.short, courses.downloadable, courses.title AS coursetitle',
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
162
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
163
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
164

165
166
167
def check_mod(user, groups):
	return user and 'users' in groups

168
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
169
def login():
170
171
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
172
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
173
	if not check_mod(user, groups):
174
		flash('Login fehlgeschlagen!')
175
176
177
178
179
180
181
		return render_template('login.html')
	session['user'] = ldapget(user)
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
		query('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
Julian Rother's avatar
Julian Rother committed
182
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
183

Julian Rother's avatar
Julian Rother committed
184
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
185
186
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
187
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
188

189
@app.route('/edit', methods=['GET', 'POST'])
190
@mod_required
Julian Rother's avatar
Julian Rother committed
191
192
193
def edit():
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
194
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
195
				'internal', 'responsible','deleted']),
Julian Rother's avatar
Julian Rother committed
196
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
197
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']),
198
		'site_texts': ('site_texts', 'key', ['value']),
199
		'videos': ('videos_data', 'id', ['visible','deleted'])
Julian Rother's avatar
Julian Rother committed
200
	}
201
	query('BEGIN')
202
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
203
204
205
206
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
207
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
208
209
		assert table in tabs
		assert column in tabs[table][2]
210
		query('INSERT INTO changelog ("table",id_value,id_key,field,value_new,value_old,"when",who,executed) VALUES (?,?,?,?,?,(SELECT %s FROM %s WHERE %s = ?),?,?,1)'%(column,tabs[table][0],tabs[table][1]),table,id,tabs[table][1],column,val,id,datetime.now(),session['user']['givenName'])
211
		query('UPDATE %s SET %s = ? WHERE %s = ?'%(tabs[table][0], column,tabs[table][1]), val, id)
212
	query('COMMIT')
213
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
214

215

216
217
218
219
220
@app.route('/auth')
def auth(): # For use with nginx auth_request
	if 'X-Original-Uri' not in request.headers:
		return 'Internal Server Error', 500
	url = request.headers['X-Original-Uri'].lstrip(config['VIDEOPREFIX'])
221
	ip = request.headers.get('X-Real-IP', '')
222
	if url.endswith('jpg'):
223
		return "OK", 200
224
	videos = query('''SELECT videos.path, videos.id, lectures.id AS lecture_id, courses.id AS course_id, auth.*
225
226
227
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
228
			LEFT JOIN auth ON (videos.id = auth.video_id OR lectures.id = auth.lecture_id OR courses.id = auth.course_id)
229
230
      WHERE videos.path = ?
      AND (? OR (courses.visible AND lectures.visible AND videos.visible))
231
			ORDER BY auth.video_id DESC, auth.lecture_id DESC, auth.course_id DESC''',
232
233
			url, ismod())
	if not videos:
234
		return "Not allowed", 403
235
236
237
238
	allowed = False
	types = []
	auth = request.authorization
	for video in videos:
239
240
		if videos[0] and ((videos[0]['video_id'] and not video['video_id']) \
				or (videos[0]['lecture_id'] and not video['lecture_id'])):
241
242
243
244
245
246
247
248
249
			break
		types.append(video['auth_type'])
		if video['auth_type'] == 'public':
			allowed = True
			break
		elif video['auth_type'] == 'password':
			if auth and video['auth_user'] == auth.username and video['auth_passwd'] == auth.password:
				allowed = True
				break
250
251
	if not types[0] or allowed or ismod() or \
			(auth and check_mod(*ldapauth(auth.username, auth.password))):
252
253
254
255
256
		return 'OK', 200
		query('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), videos[0]['id'], url)
	elif 'password' in types:
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
257
258

@app.route('/schedule')
259
260
@register_navbar('Drehplan', 'calendar')
@mod_required
Andreas Valder's avatar
Andreas Valder committed
261
def schedule():
262
263
264
265
266
	if 'kw' not in request.args:
		kw=0
	else:
		kw=int(request.args['kw'])
	start = date.today() - timedelta(days=date.today().weekday() -7*kw)
Andreas Valder's avatar
Andreas Valder committed
267
268
269
	days = [{'date': start, 'lectures': [], 'atonce':0, 'index': 0 }]
	earlieststart=time(23,59)
	latestend=time(0,0)
270
	for i in range(1,7):
Andreas Valder's avatar
Andreas Valder committed
271
		days.append({'date': days[i-1]['date'] + timedelta(days=1), 'atonce':0, 'index': i, 'lectures':[] })
272
273
	for i in days:
		# date and times are burning in sqlite
274
275
		s = datetime.combine(i['date'],time())
		e = datetime.combine(i['date'],time(23,59))
276
277
278
279
280
281
282
		i['lectures'] = query ('''
					SELECT lectures.*,courses.short
					FROM lectures 
					JOIN courses ON (lectures.course_id = courses.id) 
					WHERE (time < ?) AND (time > ?) 
					ORDER BY time ASC'''
				,e,s);
283
284
285
286
287
		# sweepline to find out how many lectures overlap
		maxcol=0;
		curcol=0;
		freecol=[];
		for l in i['lectures']:
288
			# who the hell inserts lectures with zero length?!?!?
289
			l['time_end'] = l['time']+timedelta(minutes=max(l['duration'],1))
290
		for l in sorted([(l['time'],True,l) for l in i['lectures']] + [(l['time_end'],False,l) for l in i['lectures']],key=lambda t:(t[0],t[1])):
291
292
293
294
295
296
297
			if l[1]:
				curcol += 1
				if curcol > maxcol:
					maxcol = curcol
				if len(freecol) == 0:
					freecol.append(maxcol)
				l[2]['schedule_col'] = freecol.pop()
Andreas Valder's avatar
Andreas Valder committed
298
299
				if earlieststart > l[0].time():
					earlieststart = l[0].time()
300
301
302
			else:
				curcol -= 1
				freecol.append(l[2]['schedule_col'])
Andreas Valder's avatar
Andreas Valder committed
303
304
				if latestend < l[0].time():
					latestend = l[0].time()
305
306
		i['maxcol'] = max(maxcol,1)
	times=[]
Andreas Valder's avatar
Andreas Valder committed
307
	s = min(earlieststart,time(8,0))
308
309
	e = max(latestend,time(19,0))
	for i in range(s.hour*4,min(int((60*e.hour/15)/4)*4+5,24*4)):
310
311
		t = i*15
		times.append(time(int(t/60),t%60))
312
	return render_template('schedule.html',days=days,times=times,kw=kw)
Andreas Valder's avatar
Andreas Valder committed
313
314
315
316
317
318

@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
319
320
321
322
323

@app.route('/log')
@register_navbar('Changelog', 'book')
@mod_required
def log():
324
	changelog = query('SELECT *, ( "table" || "." || id_value || "." ||field) as path FROM changelog LEFT JOIN users ON (changelog.who = users.id) ORDER BY "when" DESC LIMIT 50')
325
	return render_template('log.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
326

Andreas Valder's avatar
Andreas Valder committed
327
328
@app.route('/import/<source>/<id>', methods=['GET', 'POST'])
@app.route('/import/<source>/<int:numid>', methods=['GET', 'POST'])
Andreas Valder's avatar
Andreas Valder committed
329
@handle_errors('course', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
Andreas Valder's avatar
Andreas Valder committed
330
@mod_required
Andreas Valder's avatar
Andreas Valder committed
331
332
333
def import_from(numid=None, source=None, id=None):
	if source != "campus":
		return "Unknown source", 404
Andreas Valder's avatar
Andreas Valder committed
334
335
336
337
338
339
340
341
342

	campus={}
	for i in request.values:
		group, id, field = i.split('.', 2)
		if group == 'campus':
			if not id in  campus:
				campus[id] = {}
			campus[id][field] = request.values[i]

Andreas Valder's avatar
Andreas Valder committed
343
344
345
346
347
	if numid:
		courses = query('SELECT * FROM courses WHERE id = ?', numid)[0]
	else:
		courses = query('SELECT * FROM courses WHERE handle = ?', id)[0]
	lectures = query('SELECT * FROM lectures WHERE course_id = ?', courses['id'])
Andreas Valder's avatar
Andreas Valder committed
348
	return render_template('import_campus.html', course=courses, lectures=lectures, campus=campus)