server.py 21.9 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 os
6
import sys
Julian Rother's avatar
Julian Rother committed
7
import hashlib
8
import random
9
import traceback
10
import string
11
from socket import gethostname
Julian Rother's avatar
Julian Rother committed
12
from ipaddress import ip_address, ip_network
13
import math
Julian Rother's avatar
Julian Rother committed
14
import locale
15
import base64
16
import json
Julian Rother's avatar
Julian Rother committed
17
import urllib
Julian Rother's avatar
Julian Rother committed
18
19

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

21
app = Flask(__name__)
22

23
config = app.config
24
config.from_pyfile('config.py.example', silent=True)
25
26
27
if sys.argv[0].endswith('run.py'): 
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
28
config.from_pyfile('config.py', silent=True)
29
if sys.argv[0].endswith('runTests.py'):
Andreas Valder's avatar
Andreas Valder committed
30
31
32
33
34
35
36
37
38
	print('running in test mode')
	import tempfile
	# ensure we always use a clean sqlite db for tests
	config['DB_ENGINE'] = 'sqlite'
	config['SQLITE_DB'] = tempfile.mktemp(prefix='flasktestingtmp')
	print('DB File: {}'.format(config['SQLITE_DB']))
	config['SQLITE_INIT_DATA'] = True
	config['SQLITE_INIT_SCHEMA'] = True
	config['DEBUG'] = True
39
	config['DISABLE_SCHEDULER'] = True
40
	config['JOBS_API_KEY'] = '1'
Andreas Valder's avatar
Andreas Valder committed
41
42
if config['DEBUG']:
	app.jinja_env.auto_reload = True
Andreas Valder's avatar
Andreas Valder committed
43

44
45
if not config.get('SECRET_KEY', None):
	config['SECRET_KEY'] = os.urandom(24)
Julian Rother's avatar
Julian Rother committed
46

47
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
48

49
def mod_required(func):
50
	mod_endpoints.append(func.__name__)
51
52
	@wraps(func)
	def decorator(*args, **kwargs):
53
		if not ismod():
54
55
56
57
58
59
			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

60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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'}]
78

79

Julian Rother's avatar
Julian Rother committed
80
from db import query, modify, show, searchquery
81
from template_helper import *
Julian Rother's avatar
Julian Rother committed
82
83
84
85
86
from mail import notify_mods, notify_admins
from ldap import ldapauth
from legacy import legacy_index
from scheduler import sched_func

Julian Rother's avatar
Cleanup    
Julian Rother committed
87
88
89
90
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
91
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
92
93
	return app.view_functions[endpoint](**kargs)

94
95
96
97
98
99
100
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
101
				if endpoint:
102
					return make_response(render_endpoint(endpoint, text, **epargs), code)
Julian Rother's avatar
Julian Rother committed
103
104
				else:
					return text, code
105
106
107
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
108
@app.errorhandler(404)
109
@app.route('/invalidpath')
Julian Rother's avatar
Julian Rother committed
110
def handle_not_found(e=None):
111
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
112

113
114
115
116
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
117
	notify_admins('endpoint_exception', traceback=traceback.format_exc())
118
	return render_template('500.html', online=True), 500
119

120
121
122
123
124
125
126
127
128
129
@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()

130
def genlive(streams):
131
132
133
134
135
136
	for stream in streams:
		stream['visible'] = True
		stream['downloadable'] = False
		stream['path'] = 'pub/hls/%s.m3u8'%stream['livehandle']
		stream['file_size'] = 0
	return streams
137

138
139
140
141
142
143
def genlive_new(lectures):
	hls_format = (query('SELECT * FROM formats WHERE keywords = "hls"') or [{}])[0]
	res = []
	for lecture in lectures:
		if not lecture['stream_job']:
			continue
Julian Rother's avatar
Julian Rother committed
144
145
		res.append({'livehandle': '%i'%lecture['id'], 'visible': True,
				'downloadable': False, 'path': 'pub/hls/%i.m3u8'%lecture['id'],
146
147
				'file_size': 0, 'formats': hls_format, 'lecture_id': lecture['id']})
	return res
148

149
@app.route('/')
150
@register_navbar('Home', icon='home')
151
def index():
152
	# handle legacy urls...
153
154
155
	result = legacy_index()
	if result:
		return result
156

157
	start = date.today()
158
	end = start + timedelta(days=7)
159
	upcomming = query('''
160
		SELECT lectures.*, streams.active AS nowlive, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
161
162
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
163
		LEFT JOIN streams ON lectures.id = streams.lecture_id
164
165
		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
166
167
168
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
169
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
170
171
172
173
174
		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
175
		ORDER BY MAX(videos.time_created) DESC
Andreas Valder's avatar
Andreas Valder committed
176
		LIMIT 6	''',ismod())
177
	livestreams = query('''SELECT streams.handle AS livehandle, lectures.*, "course" AS sep, courses.*
178
179
180
181
182
		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())
183
184
185
186
187
188
	livestreams_new = query('''SELECT lectures.*, "course" AS sep, courses.*
		FROM lectures
		JOIN courses ON courses.id = lectures.course_id
		WHERE lectures.stream_job IS NOT NULL AND (? OR (courses.visible AND courses.listed AND lectures.visible))
		''', ismod())
	for stream in livestreams_new:
Julian Rother's avatar
Julian Rother committed
189
		stream['livehandle'] = '%i'%stream['id']
Julian Rother's avatar
Julian Rother committed
190
	featured = query('SELECT * FROM featured WHERE (? OR visible) ORDER BY `order`', ismod())
191
	featured = list(filter(lambda x: not x['deleted'], featured))
Julian Rother's avatar
Julian Rother committed
192
193
194
195
	for item in featured:
		if item['type'] == 'courses':
			if item['param'] not in ['title', 'semester', 'organizer', 'subject']:
				continue
196
			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
197
198
		elif item['type'] == 'video':
			item['lecture'] = {'id': item['param']}
199
			streams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
Julian Rother's avatar
Julian Rother committed
200
201
202
203
204
205
206
207
208
209
210
					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
211
					''', item['param'])+genlive(streams)
212
	return render_template('index.html', latestvideos=livestreams_new+livestreams+latestvideos, upcomming=upcomming, featured=featured)
213

214
@app.route('/courses')
215
@register_navbar('Videos', icon='film')
216
def courses():
217
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY lower(semester), lower(title)', ismod())
218
	chapters = {}
219
	for i in query('SELECT lectures.course_id AS id, COUNT(chapters.id) AS c FROM chapters JOIN lectures ON chapters.lecture_id = lectures.id WHERE NOT chapters.visible AND NOT chapters.deleted GROUP BY lectures.course_id'):
220
		chapters[i['id']] = i['c']
221
	for course in courses:
222
		course['chapter_count'] = chapters.get(course['id'], 0)
223
224
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
225
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Julian Rother committed
226
	if groupedby not in ['title', 'semester', 'organizer', 'subject']:
Andreas Valder's avatar
Andreas Valder committed
227
		groupedby = 'semester'
228
	return render_template('courses.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
229

230
231
@app.route('/<handle>')
@app.route('/<int:id>')
232
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
233
234
def course(id=None, handle=None):
	if id:
235
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
236
	else:
237
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
238
239
	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'])
240
	chapters = {}
Andreas Valder's avatar
Andreas Valder committed
241
	for i in query('SELECT lectures.id AS id, COUNT(chapters.id) AS c FROM chapters JOIN lectures ON chapters.lecture_id = lectures.id WHERE lectures.course_id = ? AND NOT chapters.visible AND NOT chapters.deleted GROUP BY chapters.lecture_id;', course['id']):
242
		chapters[i['id']] = i['c']
243
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod())
244
	for lecture in lectures:
245
		lecture['perm'] = []
246
		lecture['perm'] += course['perm']
247
		lecture['course'] = course
248
		lecture['chapter_count'] = chapters.get(lecture['id'], 0)
249
250
251
		for perm in perms:
			if perm['lecture_id'] == lecture['id']:
				lecture['perm'].append(perm)
Andreas Valder's avatar
Andreas Valder committed
252
	videos = query('''
253
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, "formats" AS sep, formats.*
Andreas Valder's avatar
Andreas Valder committed
254
255
256
257
258
259
			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
260
			''', course['id'], ismod())
261
	livestreams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
262
263
264
265
266
			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'])
267
268
	videos += genlive(livestreams)
	videos += genlive_new(lectures)
269
270
271
	chapters = []
	if course['coursechapters']:
		chapters = query('SELECT chapters.* FROM chapters JOIN lectures ON lectures.id = chapters.lecture_id WHERE lectures.course_id = ? AND NOT chapters.deleted AND chapters.visible ORDER BY time ASC', course['id'])
272
273
274
275
276
	responsible = query('''SELECT users.*, responsible.course_id AS responsible
			FROM users
			LEFT JOIN responsible ON (responsible.user_id = users.id AND responsible.course_id = ?)
			WHERE users.fsacc != "" AND users.level > 0
			ORDER BY responsible DESC, users.realname ASC''', course['id'])
277
278
	live_sources = query('SELECT * FROM live_sources WHERE NOT deleted')
	return render_template('course.html', course=course, lectures=lectures, videos=videos, chapters=chapters, responsible=responsible, live_sources=live_sources)
Andreas Valder's avatar
Andreas Valder committed
279

Andreas Valder's avatar
Andreas Valder committed
280
@app.route('/faq')
281
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
282
def faq():
283
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
284

285
286
287
288
@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')
289
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
290
def lecture(id, course=None, courseid=None):
Andreas Valder's avatar
Andreas Valder committed
291
292
	lecture = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())[0]
	videos = query('''
293
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, "formats" AS sep, formats.*
Andreas Valder's avatar
Andreas Valder committed
294
295
			FROM videos
			JOIN formats ON (videos.video_format = formats.id)
296
297
298
299
			JOIN courses ON (courses.id = ?)
			WHERE videos.lecture_id = ? AND (? OR videos.visible)
			ORDER BY formats.prio DESC
			''', lecture['course_id'], lecture['id'], ismod())
300
	livestreams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
301
302
303
304
305
306
			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)
307
	videos += genlive_new([lecture])
308
	perms = query('SELECT perm.* FROM perm WHERE ((NOT perm.deleted) AND (perm.lecture_id = ? OR perm.course_id = ?))',
309
			lecture['id'], lecture['course_id'])
310
	if not videos:
311
312
313
314
315
316
		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!')
317
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', lecture['course_id'], ismod())
318
319
	if not courses:
		return render_endpoint('courses', 'Diese Veranstaltung existiert nicht!'), 404
320
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
321
322
323
324
325
	username = password = None
	if request.authorization:
		username = request.authorization.username
		password = request.authorization.password
	if not checkperm(perms, username=username, password=password):
326
		mode, text = permdescr(perms)
327
		if mode == 'rwth':
328
			flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_rwthauth')+'">Hier authorisieren</a>.', category='player')
329
		elif mode == 'l2p':
330
			if 'l2p_courses' in session:
331
				flash(text+'. Du bist kein Teilnehmer des L2P-Kurses! <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Kurse aktualisieren</a>.', category='player')
332
			else:
333
				flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Hier authorisieren</a>.', category='player')
334
		else:
335
			flash(text+'.', category='player')
336
	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
337

338
339
340
341
342
343
344

@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'],
345
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
346
	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',
347
348
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
349
			'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
350
351
352
353
354
	for lecture in lectures:
		lecture['course'] = {}
		for key in lecture:
			if key.startswith('courses_'):
				lecture['course'][key[8:]] = lecture[key]
355
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
356

357
def check_mod(user, groups):
358
359
360
361
362
363
	if not user:
		return False
	for group in config['LDAP_GROUPS']:
		if group in groups:
			return True
	return False
364

365
@app.route('/internal/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
366
def login():
367
368
	if request.method == 'GET':
		return render_template('login.html')
369
370
	userinfo, groups = ldapauth(request.form.get('user'), request.form.get('password'))
	user = userinfo.get('uid')
371
	if not check_mod(user, groups):
372
		flash('Login fehlgeschlagen!')
Andreas Valder's avatar
Andreas Valder committed
373
		return make_response(render_template('login.html'), 403)
374
	session['user'] = userinfo
375
376
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
Julian Rother's avatar
Julian Rother committed
377
		modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
378
379
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
380
	session['_csrf_token'] = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(64))
Andreas Valder's avatar
Andreas Valder committed
381
	session.permanent = True
Julian Rother's avatar
Julian Rother committed
382
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
383

384
@app.route('/internal/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
385
def logout():
386
	session.pop('user', None)
Julian Rother's avatar
Julian Rother committed
387
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
388

389
@app.route('/internal/auth')
390
391
392
def auth(): # For use with nginx auth_request
	if 'X-Original-Uri' not in request.headers:
		return 'Internal Server Error', 500
Julian Rother's avatar
Julian Rother committed
393
	url = urllib.parse.unquote(request.headers['X-Original-Uri']).lstrip(config['VIDEOPREFIX'])
Julian Rother's avatar
Julian Rother committed
394
395
	if request.cookies.get('tracking', '') and request.cookies['tracking'].isdigit():
		cookie = int(request.cookies['tracking'])
396
	else:
Julian Rother's avatar
Julian Rother committed
397
		cookie = random.getrandbits(8*8-1)
398
	if url.endswith('jpg') or ismod():
399
		return "OK", 200
400
	if url.startswith('pub/hls/'):
Julian Rother's avatar
Julian Rother committed
401
		handle = url[len('pub/hls/'):].split('_')[0].split('.')[0]
402
		if handle.isdigit():
403
404
405
406
407
408
			perms = query('''SELECT lectures.id AS lecture, perm.*
					FROM lectures
					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 lectures.id = ?
					AND (courses.visible AND lectures.visible)
409
					ORDER BY perm.video_id DESC, perm.lecture_id DESC, perm.course_id DESC''', int(handle))
410
411
412
413
414
415
416
417
418
		else:
			perms = query('''SELECT lectures.id AS lecture, perm.*
					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)
419
420
421
422
423
424
425
426
427
428
	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)
429
	if not perms:
430
		return "Not found", 404
431
	auth = request.authorization
432
433
434
435
	username = password = None
	if auth:
		username = auth.username
		password = auth.password
436
	if checkperm(perms, username=username, password=password):
437
		try:
438
439
			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'])
440
441
442
443
			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)
444
445
446
		except:
			pass
		r = make_response('OK', 200)
Julian Rother's avatar
Julian Rother committed
447
		r.set_cookie('tracking', str(cookie), max_age=2147483647) # Many many years
448
		return r
449
	password_auth = False
450
451
	for perm in perms:
		if perm['type'] == 'password':
452
453
454
			password_auth = True
			break
	if password_auth:
455
456
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
457

Julian Rother's avatar
Julian Rother committed
458
459
460
461
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

Andreas Valder's avatar
Andreas Valder committed
462
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
463
464
465
466
467
468
469
470
471
472
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']):
473
			pages.append([url_for('lecture',course=i['handle'],id=j['id'])])
Andreas Valder's avatar
Andreas Valder committed
474
475
476
477


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

Julian Rother's avatar
Julian Rother committed
478
@app.route('/internal/dbstatus')
479
@register_navbar('DB-Status', icon='ok', group='weitere')
Julian Rother's avatar
Julian Rother committed
480
481
482
483
484
485
486
@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
487
488
489
490
491
		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
492
	for host in sorted(list(hosts)):
Julian Rother's avatar
Julian Rother committed
493
494
495
496
497
498
499
500
501
502
503
504
		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

505
506
507
def date_json_handler(obj):
	return obj.isoformat() if hasattr(obj, 'isoformat') else obj

508
from edit import edit_handler
509
from jobmanagement import job_handler, job_handler_handle, job_set_state, schedule_job, cancel_job, restart_job
Julian Rother's avatar
Julian Rother committed
510
import feeds
511
import importer
512
import stats
513
514
if 'ICAL_URL' in config:
	import meetings
515
import l2pauth
Julian Rother's avatar
Julian Rother committed
516
import sorter
Andreas Valder's avatar
Andreas Valder committed
517
import timetable
Andreas Valder's avatar
Andreas Valder committed
518
import chapters
519
import icalexport
520
import livestreams
521
import encoding
522
import cutprogress
523
import jobs