server.py 27 KB
Newer Older
1
from flask import Flask, g, request, url_for, redirect, session, render_template, flash, Response, make_response
2
from werkzeug.routing import Rule
3
from functools import wraps
Julian Rother's avatar
Julian Rother committed
4
from datetime import date, timedelta, datetime, time, MINYEAR
5
import threading
6
import os
7
import sys
Julian Rother's avatar
Julian Rother committed
8
import hashlib
9
import random
10
import sched
11
import traceback
12
import string
13
from socket import gethostname
Julian Rother's avatar
Julian Rother committed
14
from ipaddress import ip_address, ip_network
15
import math
Julian Rother's avatar
Julian Rother committed
16
import locale
17
import base64
Julian Rother's avatar
Julian Rother committed
18
19

locale.setlocale(locale.LC_ALL, 'de_DE.utf8')
20

21
app = Flask(__name__)
22

Andreas Valder's avatar
Andreas Valder committed
23
24
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True
Julian Rother's avatar
Julian Rother committed
25
app.add_template_global(random.randint, name='randint')
26
27
app.add_template_global(datetime, name='datetime')
app.add_template_global(timedelta, name='timedelta')
28
app.add_template_global(gethostname, name='gethostname')
29
30
app.add_template_global(min, name='min')
app.add_template_global(max, name='max')
Andreas Valder's avatar
Andreas Valder committed
31

32

33
34
scheduler = sched.scheduler()
def run_scheduler():
Andreas Valder's avatar
Andreas Valder committed
35
	import time
36
	time.sleep(1) # UWSGI does weird things on startup
37
38
	while True:
		scheduler.run()
39
		time.sleep(10)
40

41
42
43
def sched_func(delay, priority=0, firstdelay=None, args=[], kargs={}):
	if firstdelay == None:
		firstdelay = random.randint(1, 120)
44
45
46
	def wrapper(func):
		def sched_wrapper():
			with app.test_request_context():
47
48
49
50
				try:
					func(*args, **kargs)
				except Exception:
					traceback.print_exc()
51
			scheduler.enter(delay, priority, sched_wrapper)
52
		scheduler.enter(firstdelay, priority, sched_wrapper)
53
54
55
56
		return func
	return wrapper

threading.Thread(target=run_scheduler, daemon=True).start()
57

58
config = app.config
59
config.from_pyfile('config.py.example', silent=True)
60
61
62
if sys.argv[0].endswith('run.py'): 
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
63
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
64
65
if config['DEBUG']:
	app.jinja_env.auto_reload = True
Andreas Valder's avatar
Andreas Valder committed
66
67
68

# get git commit
import subprocess
Andreas Valder's avatar
Andreas Valder committed
69
output = subprocess.check_output(['git', "log", "-g", "-1", "--pretty=%H # %h # %d # %s"]).decode('UTF-8').split('#', 3)
Andreas Valder's avatar
Andreas Valder committed
70
app.jinja_env.globals['gitversion'] = { 'hash': output[1], 'longhash': output[0], 'branch': output[2], 'msg': output[3]  }
71

72
73
if not config.get('SECRET_KEY', None):
	config['SECRET_KEY'] = os.urandom(24)
Julian Rother's avatar
Julian Rother committed
74

75
76
from db import query, modify, show, searchquery
from ldap import ldapauth
Julian Rother's avatar
Julian Rother committed
77

78
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
79

Julian Rother's avatar
Cleanup    
Julian Rother committed
80
@app.template_global()
81
82
83
84
def ismod(*args):
	return ('user' in session)

def mod_required(func):
85
	mod_endpoints.append(func.__name__)
86
87
	@wraps(func)
	def decorator(*args, **kwargs):
88
		if not ismod():
89
90
91
92
93
94
			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

95
96
csrf_endpoints = []

97
def csrf_protect(func):
98
	csrf_endpoints.append(func.__name__)
99
100
101
102
	@wraps(func)
	def decorator(*args, **kwargs):
		if '_csrf_token' in request.values:
			token = request.values['_csrf_token']
Andreas Valder's avatar
Andreas Valder committed
103
		elif request.get_json() and ('_csrf_token' in request.get_json()):
104
105
			token = request.get_json()['_csrf_token']
		else:
106
			token = None
107
108
109
110
111
112
		if not ('_csrf_token' in session) or (session['_csrf_token'] != token ) or not token: 
			return 'csrf test failed', 403
		else:
			return func(*args, **kwargs)
	return decorator

113
114
@app.url_defaults
def csrf_inject(endpoint, values):
115
	if endpoint not in csrf_endpoints or not session.get('_csrf_token'):
116
117
118
		return
	values['_csrf_token'] = session['_csrf_token']

119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
def evalperm(perms):
	cperms = []
	lperms = []
	vperms = []
	for perm in perms:
		if perm['course_id']:
			cperms.append(perm)
		elif perm['lecture_id']:
			lperms.append(perm)
		elif perm['video_id']:
			vperms.append(perm)
	if vperms:
		return vperms
	elif lperms:
	 	return lperms
	elif cperms:
		return cperms
	return [{'type': 'public'}]
137

138
139
@app.template_filter()
def base64encode(str):
Andreas Valder's avatar
Andreas Valder committed
140
141
142
143
	try:
		return base64.b64encode(str.encode('UTF-8')).decode('UTF-8')
	except:
		return ''
144

145
@app.template_filter()
146
def checkperm(perms, username=None, password=None):
147
148
	if ismod():
		return True
149
150
151
	perms = evalperm(perms)
	for perm in perms:
		if perm['type'] == 'public':
152
			return True
153
154
		elif perm['type'] == 'password':
			if perm['param1'] == username and perm['param2'] == password:
155
				return True
156
157
		elif perm['type'] == 'l2p':
			if perm['param1'] in session.get('l2p_courses', []):
158
				return True
159
		elif perm['type'] == 'rwth':
160
161
			if session.get('rwthintern', False):
				return True
162
163
164
			if 'X-Real-IP' not in request.headers:
				continue
			ip = ip_address(request.headers['X-Real-IP'])
Julian Rother's avatar
Julian Rother committed
165
			for net in config['RWTH_IP_RANGES']:
166
167
				if ip in ip_network(net):
					return True
168
169
170
	return False

@app.template_filter()
171
172
def permdescr(perms):
	perms = evalperm(perms)
173
174
175
176
	public = False
	password = False
	l2p_courses = []
	rwth_intern = False
177
	fsmpi_intern = False
178
179
	for perm in perms:
		if perm['type'] == 'public':
180
			public = True
181
		elif perm['type'] == 'password':
182
			password = True
183
		elif perm['type'] == 'l2p':
184
			l2p_courses.append(perm['param1'])
185
		elif perm['type'] == 'rwth':
186
			rwth_intern = True
187
188
		elif perm['type'] == 'fsmpi':
			fsmpi_intern = True
189
	if public or not perms:
190
191
192
193
194
		return 'public', 'Öffentlich verfügbar'
	if rwth_intern:
		if password:
			return 'rwth', 'Nur für RWTH-Angehörige und Nutzer mit Passwort verfügbar'
		return 'rwth', 'Nur für RWTH-Angehörige verfügbar'
195
196
	if fsmpi_intern:
		return 'fsmpi', 'Nur für Fachschaftler verfügbar'
197
198
	if l2p_courses:
		if password:
199
200
			return 'l2p', 'Nur für Teilnehmer der Veranstaltung und Nutzer mit Passwort verfügbar'
		return 'l2p', 'Nur für Teilnehmer der Veranstaltung verfügbar'
201
202
	if password:
		return 'password', 'Nur für Nutzer mit Passwort verfügbar'
Julian Rother's avatar
Julian Rother committed
203
	return 'none', 'Nicht verfügbar'
204

205
app.jinja_env.globals['navbar'] = []
206
207
208
209
210
# iconlib can be 'bootstrap'
# ( see: http://getbootstrap.com/components/#glyphicons )
# or 'fa'
# ( see: http://fontawesome.io/icons/ )
def register_navbar(name, iconlib='bootstrap', icon=None):
211
	def wrapper(func):
212
		endpoint = func.__name__
213
		app.jinja_env.globals['navbar'].append((endpoint, name, iconlib, icon, not endpoint in mod_endpoints))
214
215
216
		return func
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
217
218
219
220
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
221
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
222
223
	return app.view_functions[endpoint](**kargs)

224
225
226
227
228
229
230
def handle_errors(endpoint, text, code, *errors, **epargs):
	def wrapper(func):
		@wraps(func)
		def decorator(*args, **kwargs):
			try:
				return func(*args, **kwargs)
			except errors:
Julian Rother's avatar
Julian Rother committed
231
				if endpoint:
232
					return make_response(render_endpoint(endpoint, text, **epargs), code)
Julian Rother's avatar
Julian Rother committed
233
234
				else:
					return text, code
235
236
237
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
238
@app.errorhandler(404)
239
@app.route('/invalidpath')
Julian Rother's avatar
Julian Rother committed
240
def handle_not_found(e=None):
241
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
242

243
244
245
246
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
247
	return render_template('500.html'), 500
248

249
250
251
252
253
254
255
256
257
258
@sched_func(5*60, firstdelay=0)
def dump_error_page():
	if 'ERROR_PAGE' not in config:
		return
	request.url_rule = Rule(request.path, endpoint='handle_internal_error')
	text = render_template('500.html')
	f = open(config['ERROR_PAGE'], 'w')
	f.write(text)
	f.close()

Andreas Valder's avatar
Andreas Valder committed
259
260
# debian ships jinja2 without this test...
@app.template_test(name='equalto')
261
262
263
def equalto(a,b):
	return a == b

264
265
266
267
268
269
270
271
@app.template_filter(name='filterdict')
def jinja2_filterdict(value, attrdel):
	v = dict(value)
	for a in attrdel:
		if a in v:
			del v[a]
	return dict(v)

Julian Rother's avatar
Julian Rother committed
272
@app.template_filter(name='semester')
273
274
275
276
277
278
279
280
281
282
283
284
285
286
def human_semester(s, long=False):
	if not s or s == 'zeitlos' or len(s) != 6:
		return 'Zeitlos'
	year = s[0:4]
	semester = s[4:6].upper()
	if not year.isdigit() or semester not in ['SS', 'WS']:
		print('Invalid semester string "%s"'%s)
		return '??'
	if not long:
		return semester+year[2:]
	elif semester == 'SS':
		return 'Sommersemester %s'%year
	else:
		return 'Wintersemester %s/%s'%(year, str(int(year)+1)[2:])
Julian Rother's avatar
Julian Rother committed
287
288
289

@app.template_filter(name='date')
def human_date(d):
Andreas Valder's avatar
Andreas Valder committed
290
	return d.strftime('%d.%m.%Y')
Julian Rother's avatar
Julian Rother committed
291

292
293
294
295
@app.template_filter(name='fulldate')
def human_fulldate(d):
	return d.strftime('%a, %d.%m.%Y, %H:%M Uhr')

Andreas Valder's avatar
Andreas Valder committed
296
@app.template_filter(name='time')
297
def human_time(d):
Andreas Valder's avatar
Andreas Valder committed
298
299
	return d.strftime('%H:%M')

Julian Rother's avatar
Julian Rother committed
300
301
302
303
@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

304
305
@app.template_global()
def get_announcements(minlevel=0):
306
307
	offset = timedelta()
	if ismod():
308
		offset = timedelta(hours=24)
309
310
311
312
	try:
		return query('SELECT * FROM announcements WHERE NOT deleted AND ((time_expire = NULL) OR time_expire > ?) AND (? OR (visible AND time_publish < ?)) AND level >= ? ORDER BY level DESC', datetime.now()-offset, ismod(), datetime.now(), minlevel)
	except:
		return []
313

314
315
316
317
318
@app.template_filter()
def fixnl(s):
	# To be remove, as soon as db schema is cleaned-up
	return str(s).replace('\n', '<br>')

319
320
321
322
323
324
325
326
327
328
329
@app.template_filter()
def tagid(s):
	if not s:
		return 'EMPTY'
	s = s.replace(' ', '_').lower()
	r = ''
	for c in s:
		if c in string.ascii_lowercase+string.digits+'_':
			r = r + c
	return r

330
331
332
333
334
335
336
337
338
def genlive(streams):
        for stream in streams:
                stream['visible'] = True
                stream['downloadable'] = False
                stream['path'] = 'pub/hls/%s.m3u8'%stream['livehandle']
                stream['file_size'] = 0
        return streams


339
@app.route('/')
340
@register_navbar('Home', icon='home')
341
def index():
342
343
344
	# handle legacy urls...
	if 'course' in request.args:
		return redirect(url_for('course', handle=request.args['course']),code=302)
345
346
	if 'view' in request.args:
		if (request.args['view'] == 'player') and ('lectureid' in request.args) :
347
348
349
350
			courses = query('SELECT courses.handle FROM courses JOIN lectures ON courses.id = lectures.course_id WHERE lectures.id = ?', request.args['lectureid'])
			if not courses:
				return "Not found", 404
			return redirect(url_for('lecture', course=courses[0]['handle'], id=request.args['lectureid']),code=302)
351

352
	start = date.today()
353
	end = start + timedelta(days=7)
354
	upcomming = query('''
355
		SELECT lectures.*, streams.active AS nowlive, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
356
357
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
358
		LEFT JOIN streams ON lectures.id = streams.lecture_id
359
360
		WHERE (time > ?) AND (time < ?) AND (? OR (lectures.visible AND courses.visible AND courses.listed)) AND NOT lectures.norecording
		ORDER BY time ASC LIMIT 30''', start, end, ismod())
Andreas Valder's avatar
Andreas Valder committed
361
362
363
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
364
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
365
366
367
368
369
		FROM lectures
		LEFT JOIN videos ON (videos.lecture_id = lectures.id)
		LEFT JOIN courses on (courses.id = lectures.course_id)
		WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
		GROUP BY videos.lecture_id
370
		ORDER BY MAX(videos.time_created) DESC
Andreas Valder's avatar
Andreas Valder committed
371
		LIMIT 6	''',ismod())
372
	livestreams = query('''SELECT streams.handle AS livehandle, lectures.*, "course" AS sep, courses.*
373
374
375
376
377
		FROM streams
		JOIN lectures ON lectures.id = streams.lecture_id
		JOIN courses ON courses.id = lectures.course_id
		WHERE streams.active AND (? OR (streams.visible AND courses.visible AND courses.listed AND lectures.visible))
		''', ismod())
Julian Rother's avatar
Julian Rother committed
378
	featured = query('SELECT * FROM featured WHERE (? OR visible) ORDER BY `order`', ismod())
379
	featured = list(filter(lambda x: not x['deleted'], featured))
Julian Rother's avatar
Julian Rother committed
380
381
382
383
	for item in featured:
		if item['type'] == 'courses':
			if item['param'] not in ['title', 'semester', 'organizer', 'subject']:
				continue
384
			item['courses'] = query('SELECT * FROM courses WHERE (visible AND listed) AND `%s` = ? ORDER BY `%s`'%(item['param'], item['param']), item['param2'])
Julian Rother's avatar
Julian Rother committed
385
386
		elif item['type'] == 'video':
			item['lecture'] = {'id': item['param']}
387
			streams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
Julian Rother's avatar
Julian Rother committed
388
389
390
391
392
393
394
395
396
397
398
					FROM streams
					JOIN lectures ON lectures.id = streams.lecture_id
					JOIN formats ON formats.keywords = "hls"
					WHERE streams.active AND streams.visible AND lectures.id = ?
					''', item['param'])
			item['videos'] = query('''
					SELECT videos.*, "formats" AS sep, formats.*
					FROM videos
					JOIN formats ON (videos.video_format = formats.id)
					WHERE videos.lecture_id = ? AND videos.visible
					ORDER BY formats.prio DESC
399
					''', item['param'])+genlive(streams)
400
	return render_template('index.html', latestvideos=livestreams+latestvideos, upcomming=upcomming, featured=featured)
401

402
@app.route('/courses')
403
@register_navbar('Videos', icon='film')
404
def courses():
405
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY lower(semester), lower(title)', ismod())
406
407
408
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
409
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Julian Rother committed
410
	if groupedby not in ['title', 'semester', 'organizer', 'subject']:
Andreas Valder's avatar
Andreas Valder committed
411
		groupedby = 'semester'
412
	return render_template('courses.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
413

414
415
@app.route('/<handle>')
@app.route('/<int:id>')
416
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
417
418
def course(id=None, handle=None):
	if id:
419
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
420
	else:
421
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
422
423
	course['perm'] = query('SELECT * FROM perm WHERE (NOT perm.deleted) AND course_id = ? ORDER BY type', course['id'])
	perms = query('SELECT perm.* FROM perm JOIN lectures ON (perm.lecture_id = lectures.id) WHERE (NOT perm.deleted) AND lectures.course_id = ? ORDER BY perm.type', course['id'])
424
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod())
425
	for lecture in lectures:
426
		lecture['perm'] = []
427
		lecture['perm'] += course['perm']
428
		lecture['course'] = course
429
430
431
		for perm in perms:
			if perm['lecture_id'] == lecture['id']:
				lecture['perm'].append(perm)
Andreas Valder's avatar
Andreas Valder committed
432
	videos = query('''
433
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, "formats" AS sep, formats.*
Andreas Valder's avatar
Andreas Valder committed
434
435
436
437
438
439
			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
440
			''', course['id'], ismod())
441
	livestreams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
442
443
444
445
446
447
			FROM streams
			JOIN lectures ON lectures.id = streams.lecture_id
			JOIN formats ON formats.keywords = "hls"
			WHERE streams.active AND (? OR streams.visible) AND lectures.course_id = ?
			''', ismod(), course['id'])
	videos += genlive(livestreams)
448
	return render_template('course.html', course=course, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
449

Andreas Valder's avatar
Andreas Valder committed
450
@app.route('/faq')
451
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
452
def faq():
453
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
454

455
456
457
458
@app.route('/<course>/<int:id>')
@app.route('/<int:courseid>/<int:id>')
@app.route('/<course>/<int:id>/embed', endpoint='embed')
@app.route('/<int:courseid>/<int:id>/embed', endpoint='embed')
459
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
460
def lecture(id, course=None, courseid=None):
Andreas Valder's avatar
Andreas Valder committed
461
462
	lecture = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())[0]
	videos = query('''
463
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, "formats" AS sep, formats.*
Andreas Valder's avatar
Andreas Valder committed
464
465
			FROM videos
			JOIN formats ON (videos.video_format = formats.id)
466
467
468
469
			JOIN courses ON (courses.id = ?)
			WHERE videos.lecture_id = ? AND (? OR videos.visible)
			ORDER BY formats.prio DESC
			''', lecture['course_id'], lecture['id'], ismod())
470
	livestreams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
471
472
473
474
475
476
			FROM streams
			JOIN lectures ON lectures.id = streams.lecture_id
			JOIN formats ON formats.keywords = "hls"
			WHERE streams.active AND (? OR streams.visible) AND lectures.id = ?
			''', ismod(), id)
	videos += genlive(livestreams)
477
	perms = query('SELECT perm.* FROM perm WHERE ((NOT perm.deleted) AND (perm.lecture_id = ? OR perm.course_id = ?))',
478
			lecture['id'], lecture['course_id'])
479
	if not videos:
480
481
482
483
484
485
		if lecture['live'] and lecture['time'] > datetime.now()-timedelta(minutes=30) and lecture['time']-timedelta(hours=20) < datetime.now():
			flash('Der Livestream beginnt um '+human_time(lecture['time'])+' Uhr.')
		elif lecture['time'] > datetime.now():
			flash('Diese Vorlesung hat noch nicht stattgefunden!')
		else:
			flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
486
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', lecture['course_id'], ismod())
487
488
	if not courses:
		return render_endpoint('courses', 'Diese Veranstaltung existiert nicht!'), 404
489
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
490
491
492
493
494
	username = password = None
	if request.authorization:
		username = request.authorization.username
		password = request.authorization.password
	if not checkperm(perms, username=username, password=password):
495
		mode, text = permdescr(perms)
496
		if mode == 'rwth':
497
			flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_rwthauth')+'">Hier authorisieren</a>.', category='player')
498
		elif mode == 'l2p':
499
			if 'l2p_courses' in session:
500
				flash(text+'. Du bist kein Teilnehmer des L2P-Kurses! <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Kurse aktualisieren</a>.', category='player')
501
			else:
502
				flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Hier authorisieren</a>.', category='player')
503
		else:
504
			flash(text+'.', category='player')
505
	return render_template('embed.html' if request.endpoint == 'embed' else 'lecture.html', course=courses[0], lecture=lecture, videos=videos, chapters=chapters, seek=request.args.get('t'))
Andreas Valder's avatar
Andreas Valder committed
506

507
508
509
510
511
512
513

@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'],
514
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
Julian Rother's avatar
Julian Rother committed
515
516
517
518
519
	#lectures = searchquery(q, 'lectures.*, courses.visible AS coursevisible, courses.listed, "course" AS sep, courses.*',
	#			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
	#			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
	#			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
	lectures = searchquery(q, 'lectures.*, courses.visible AS coursevisible, courses.listed, courses.id AS courses_id, courses.visible AS courses_visible, courses.listed AS courses_listed, courses.title AS courses_title, courses.short AS courses_short, courses.handle AS courses_handle, courses.organizer AS courses_organizer, courses.subject AS courses_subject, courses.credits AS courses_credits, courses.created_by AS courses_created_by, courses.time_created AS courses_time_created, courses.time_updated AS courses_time_updated, courses.semester AS courses_semester, courses.downloadable AS courses_downloadable, courses.embedinvisible AS courses_embedinvisible, courses.description AS courses_description, courses.internal AS courses_internal, courses.responsible AS courses_responsible',
520
521
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
522
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
Julian Rother's avatar
Julian Rother committed
523
524
525
526
527
	for lecture in lectures:
		lecture['course'] = {}
		for key in lecture:
			if key.startswith('courses_'):
				lecture['course'][key[8:]] = lecture[key]
528
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
529

530
def check_mod(user, groups):
531
532
533
534
535
536
	if not user:
		return False
	for group in config['LDAP_GROUPS']:
		if group in groups:
			return True
	return False
537

538
@app.route('/internal/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
539
def login():
540
541
	if request.method == 'GET':
		return render_template('login.html')
542
543
	userinfo, groups = ldapauth(request.form.get('user'), request.form.get('password'))
	user = userinfo.get('uid')
544
	if not check_mod(user, groups):
545
		flash('Login fehlgeschlagen!')
546
		return render_template('login.html')
547
	session['user'] = userinfo
548
549
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
Julian Rother's avatar
Julian Rother committed
550
		modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
551
552
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
553
	session['_csrf_token'] = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(64))
Andreas Valder's avatar
Andreas Valder committed
554
	session.permanent = True
Julian Rother's avatar
Julian Rother committed
555
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
556

557
@app.route('/internal/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
558
def logout():
559
	session.pop('user', None)
Julian Rother's avatar
Julian Rother committed
560
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
561

562
@app.route('/internal/auth')
563
564
565
566
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'])
Julian Rother's avatar
Julian Rother committed
567
568
	if request.cookies.get('tracking', '') and request.cookies['tracking'].isdigit():
		cookie = int(request.cookies['tracking'])
569
	else:
Julian Rother's avatar
Julian Rother committed
570
		cookie = random.getrandbits(8*8-1)
571
	if url.endswith('jpg') or ismod():
572
		return "OK", 200
573
574
	if url.startswith('pub/hls/'):
		handle = url[len('pub/hls/'):].split('_')[0].split('.')[0]
575
		perms = query('''SELECT lectures.id AS lecture, perm.*
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
				FROM streams
				JOIN lectures ON (streams.lecture_id = lectures.id)
				JOIN courses ON (lectures.course_id = courses.id)
				LEFT JOIN perm ON ((lectures.id = perm.lecture_id OR courses.id = perm.course_id) AND NOT perm.deleted)
				WHERE streams.handle = ?
				AND (courses.visible AND lectures.visible AND streams.visible)
				ORDER BY perm.video_id DESC, perm.lecture_id DESC, perm.course_id DESC''', handle)
	else:
		perms = query('''SELECT videos.path, videos.id AS vid, perm.*
				FROM videos
				JOIN lectures ON (videos.lecture_id = lectures.id)
				JOIN courses ON (lectures.course_id = courses.id)
				LEFT JOIN perm ON ((videos.id = perm.video_id OR lectures.id = perm.lecture_id OR courses.id = perm.course_id) AND NOT perm.deleted)
				WHERE videos.path = ?
				AND (courses.visible AND lectures.visible AND videos.visible)
				ORDER BY perm.video_id DESC, perm.lecture_id DESC, perm.course_id DESC''',
				url)
593
	if not perms:
594
		return "Not found", 404
595
	auth = request.authorization
596
597
598
599
	username = password = None
	if auth:
		username = auth.username
		password = auth.password
600
	if checkperm(perms, username=username, password=password):
601
		try:
602
603
			if not url.startswith('pub/hls/'):
				modify('INSERT INTO log (id, `time`, `date`, video, source) VALUES (?, ?, ?, ?, 1)', cookie, datetime.now(), datetime.combine(date.today(), time()), perms[0]['vid'])
604
605
606
607
			elif url.endswith('.ts'):
				fmt = url.split('_')[-1].split('-')[0]
				seg = url.split('.')[0].split('-')[-1]
				modify('INSERT INTO hlslog (id, `time`, segment, lecture, handle, format) VALUES (?, ?, ?, ?, ?, ?)', cookie, datetime.now(), seg, perms[0]['lecture'], handle, fmt)
608
609
610
		except:
			pass
		r = make_response('OK', 200)
Julian Rother's avatar
Julian Rother committed
611
		r.set_cookie('tracking', str(cookie), max_age=2147483647) # Many many years
612
		return r
613
	password_auth = False
614
615
	for perm in perms:
		if perm['type'] == 'password':
616
617
618
			password_auth = True
			break
	if password_auth:
619
620
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
621

Julian Rother's avatar
Julian Rother committed
622
623
624
625
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

Andreas Valder's avatar
Andreas Valder committed
626
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
627
628
629
630
631
632
633
634
635
636
def sitemap():
	pages=[]
	# static pages
	for rule in app.url_map.iter_rules():
		if 'GET' in rule.methods and len(rule.arguments)==0:
			if rule.endpoint not in mod_endpoints:
				pages.append([rule.rule])
	for i in query('select * from courses where visible and listed'):
		pages.append([url_for('course',handle=i['handle'])])
		for j in query('select * from lectures where (course_id = ? and visible)',i['id']):
637
			pages.append([url_for('lecture',course=i['handle'],id=j['id'])])
Andreas Valder's avatar
Andreas Valder committed
638
639
640
641


	return Response(render_template('sitemap.xml', pages=pages), 200, {'Content-Type': 'application/atom+xml'} )

642
643
644
645
646

@app.route('/site/')
@app.route('/site/<string:phpfile>')
def legacy(phpfile=None):
	if phpfile=='embed.php' and ('lecture' in request.args):
647
648
649
650
		courses = query('SELECT courses.handle FROM courses JOIN lectures ON courses.id = lectures.course_id WHERE lectures.id = ?', request.args['lecture'])
		if not courses:
			return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
		return redirect(url_for('embed', course=courses[0]['handle'], id=request.args['lecture']),code=302)
651
652
653
654
655
656
657
658
	if phpfile=='embed.php' and ('vid' in request.args):
		lectures = query('SELECT lecture_id FROM videos WHERE id = ?', request.args['vid'])
		if not lectures:
			return render_endpoint('index', 'Dieses Videos existiert nicht!'), 404
		courses = query('SELECT courses.handle FROM courses JOIN lectures ON courses.id = lectures.course_id WHERE lectures.id = ?', lectures[0]['lecture_id'])
		if not courses:
			return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
		return redirect(url_for('embed', course=courses[0]['handle'], id=lectures[0]['lecture_id']),code=302)
659
660
661
662
663
664
665
666
	if phpfile=='feed.php' and ('all' in request.args):
		return redirect(url_for('feed'),code=302)
	if phpfile=='feed.php' and ('newcourses' in request.args):
		return redirect(url_for('courses_feed'),code=302)
	if phpfile=='feed.php':
		return redirect(url_for('feed', handle=request.args.copy().popitem()[0]),code=302)
	print("Unknown legacy url:",request.url)
	return redirect(url_for('index'),code=302)
Julian Rother's avatar
Julian Rother committed
667
668
669
670
671
672
673
674
675
676
677
678

import json

@app.route('/internal/dbstatus')
@register_navbar('DB-Status', icon='ok')
@mod_required
def dbstatus():
	hosts = set()
	clusters = {}
	status = {}
	variables = {}
	for host in config.get('MYSQL_DBSTATUS_HOSTS', [])+[config.get('MYSQL_HOST', None)]:
Andreas Valder's avatar
Andreas Valder committed
679
680
681
682
683
		try:
			for _host in show('SHOW VARIABLES LIKE "wsrep_cluster_address"', host=host)['wsrep_cluster_address'][len('gcomm://'):].split(','):
				hosts.add(_host)
		except:
			pass
Julian Rother's avatar
Julian Rother committed
684
	for host in sorted(list(hosts)):
Julian Rother's avatar
Julian Rother committed
685
686
687
688
689
690
691
692
693
694
695
696
		try:
			status[host] = show('SHOW GLOBAL STATUS LIKE "wsrep%"', host=host)
			variables[host] = show('SHOW GLOBAL VARIABLES LIKE "wsrep%"', host=host)
		except:
			status[host] = {'wsrep_cluster_state_uuid': '', 'wsrep_local_state_comment': 'Not reachable', 'wsrep_cluster_conf_id': '0', 'wsrep_cluster_status': 'Unknown'}
			variables[host] = {'wsrep_node_name': host, 'wsrep_cluster_name': 'unknown'}
		cluster = variables[host]['wsrep_cluster_name']+'-'+status[host]['wsrep_cluster_conf_id']
		if cluster not in clusters:
			clusters[cluster] = []
		clusters[cluster].append(host)
	return render_template('dbstatus.html', clusters=clusters, statuses=status, vars=variables), 200

697
698
699
700
701
702
703
@app.template_global()
def is_readonly():
	try:
		return show('SHOW GLOBAL STATUS LIKE "wsrep_ready"')['wsrep_ready'] != 'ON'
	except:
		return True

Andreas Valder's avatar
Andreas Valder committed
704
import edit
Julian Rother's avatar
Julian Rother committed
705
import feeds
706
import importer
707
import stats
Andreas Valder's avatar
Andreas Valder committed
708
import sorter
709
710
if 'ICAL_URL' in config:
	import meetings
711
import l2pauth
Andreas Valder's avatar
Andreas Valder committed
712
713
if 'JOBS_API_KEY' in config:
	import jobs
Andreas Valder's avatar
Andreas Valder committed
714
import timetable
Andreas Valder's avatar
Andreas Valder committed
715
import chapters
716
import icalexport
717
import livestreams