server.py 19.6 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
18

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

20
app = Flask(__name__)
21

22
config = app.config
23
config.from_pyfile('config.py.example', silent=True)
24
25
26
if sys.argv[0].endswith('run.py'): 
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
27
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
28
29
if config['DEBUG']:
	app.jinja_env.auto_reload = True
Andreas Valder's avatar
Andreas Valder committed
30

31
32
if not config.get('SECRET_KEY', None):
	config['SECRET_KEY'] = os.urandom(24)
Julian Rother's avatar
Julian Rother committed
33

34
35
from db import query, modify, show, searchquery
from ldap import ldapauth
36
from legacy import legacy_index
37
from scheduler import sched_func
Julian Rother's avatar
Julian Rother committed
38

39
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
40

41
def mod_required(func):
42
	mod_endpoints.append(func.__name__)
43
44
	@wraps(func)
	def decorator(*args, **kwargs):
45
		if not ismod():
46
47
48
49
50
51
			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

52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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'}]
70

71
from template_helper import *
72

73
app.jinja_env.globals['navbar'] = []
74
75
76
77
78
# 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):
79
	def wrapper(func):
80
		endpoint = func.__name__
81
		app.jinja_env.globals['navbar'].append((endpoint, name, iconlib, icon, not endpoint in mod_endpoints))
82
83
84
		return func
	return wrapper

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

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

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

111
112
113
114
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
115
	return render_template('500.html'), 500
116

117
118
119
120
121
122
123
124
125
126
@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()

127
128
129
130
131
132
133
134
135
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


136
@app.route('/')
137
@register_navbar('Home', icon='home')
138
def index():
139
	# handle legacy urls...
140
141
142
	result = legacy_index()
	if result:
		return result
143

144
	start = date.today()
145
	end = start + timedelta(days=7)
146
	upcomming = query('''
147
		SELECT lectures.*, streams.active AS nowlive, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
148
149
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
150
		LEFT JOIN streams ON lectures.id = streams.lecture_id
151
152
		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
153
154
155
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
156
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
157
158
159
160
161
		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
162
		ORDER BY MAX(videos.time_created) DESC
Andreas Valder's avatar
Andreas Valder committed
163
		LIMIT 6	''',ismod())
164
	livestreams = query('''SELECT streams.handle AS livehandle, lectures.*, "course" AS sep, courses.*
165
166
167
168
169
		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
170
	featured = query('SELECT * FROM featured WHERE (? OR visible) ORDER BY `order`', ismod())
171
	featured = list(filter(lambda x: not x['deleted'], featured))
Julian Rother's avatar
Julian Rother committed
172
173
174
175
	for item in featured:
		if item['type'] == 'courses':
			if item['param'] not in ['title', 'semester', 'organizer', 'subject']:
				continue
176
			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
177
178
		elif item['type'] == 'video':
			item['lecture'] = {'id': item['param']}
179
			streams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
Julian Rother's avatar
Julian Rother committed
180
181
182
183
184
185
186
187
188
189
190
					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
191
					''', item['param'])+genlive(streams)
192
	return render_template('index.html', latestvideos=livestreams+latestvideos, upcomming=upcomming, featured=featured)
193

194
@app.route('/courses')
195
@register_navbar('Videos', icon='film')
196
def courses():
197
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY lower(semester), lower(title)', ismod())
198
199
200
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
201
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Julian Rother committed
202
	if groupedby not in ['title', 'semester', 'organizer', 'subject']:
Andreas Valder's avatar
Andreas Valder committed
203
		groupedby = 'semester'
204
	return render_template('courses.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
205

206
207
@app.route('/<handle>')
@app.route('/<int:id>')
208
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
209
210
def course(id=None, handle=None):
	if id:
211
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
212
	else:
213
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
214
215
	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'])
216
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod())
217
	for lecture in lectures:
218
		lecture['perm'] = []
219
		lecture['perm'] += course['perm']
220
		lecture['course'] = course
221
222
223
		for perm in perms:
			if perm['lecture_id'] == lecture['id']:
				lecture['perm'].append(perm)
Andreas Valder's avatar
Andreas Valder committed
224
	videos = query('''
225
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, "formats" AS sep, formats.*
Andreas Valder's avatar
Andreas Valder committed
226
227
228
229
230
231
			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
232
			''', course['id'], ismod())
233
	livestreams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
234
235
236
237
238
			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'])
239
240
241
	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'])
242
	videos += genlive(livestreams)
243
	return render_template('course.html', course=course, lectures=lectures, videos=videos, chapters=chapters)
Andreas Valder's avatar
Andreas Valder committed
244

Andreas Valder's avatar
Andreas Valder committed
245
@app.route('/faq')
246
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
247
def faq():
248
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
249

250
251
252
253
@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')
254
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
255
def lecture(id, course=None, courseid=None):
Andreas Valder's avatar
Andreas Valder committed
256
257
	lecture = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())[0]
	videos = query('''
258
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, "formats" AS sep, formats.*
Andreas Valder's avatar
Andreas Valder committed
259
260
			FROM videos
			JOIN formats ON (videos.video_format = formats.id)
261
262
263
264
			JOIN courses ON (courses.id = ?)
			WHERE videos.lecture_id = ? AND (? OR videos.visible)
			ORDER BY formats.prio DESC
			''', lecture['course_id'], lecture['id'], ismod())
265
	livestreams = query('''SELECT streams.handle AS livehandle, streams.lecture_id, "formats" AS sep, formats.*
266
267
268
269
270
271
			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)
272
	perms = query('SELECT perm.* FROM perm WHERE ((NOT perm.deleted) AND (perm.lecture_id = ? OR perm.course_id = ?))',
273
			lecture['id'], lecture['course_id'])
274
	if not videos:
275
276
277
278
279
280
		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!')
281
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', lecture['course_id'], ismod())
282
283
	if not courses:
		return render_endpoint('courses', 'Diese Veranstaltung existiert nicht!'), 404
284
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
285
286
287
288
289
	username = password = None
	if request.authorization:
		username = request.authorization.username
		password = request.authorization.password
	if not checkperm(perms, username=username, password=password):
290
		mode, text = permdescr(perms)
291
		if mode == 'rwth':
292
			flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_rwthauth')+'">Hier authorisieren</a>.', category='player')
293
		elif mode == 'l2p':
294
			if 'l2p_courses' in session:
295
				flash(text+'. Du bist kein Teilnehmer des L2P-Kurses! <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Kurse aktualisieren</a>.', category='player')
296
			else:
297
				flash(text+'. <a target="_blank" class="reloadonclose" href="'+url_for('start_l2pauth')+'">Hier authorisieren</a>.', category='player')
298
		else:
299
			flash(text+'.', category='player')
300
	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
301

302
303
304
305
306
307
308

@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'],
309
			'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
310
311
312
313
314
	#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',
315
316
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
317
			'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
318
319
320
321
322
	for lecture in lectures:
		lecture['course'] = {}
		for key in lecture:
			if key.startswith('courses_'):
				lecture['course'][key[8:]] = lecture[key]
323
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
324

325
def check_mod(user, groups):
326
327
328
329
330
331
	if not user:
		return False
	for group in config['LDAP_GROUPS']:
		if group in groups:
			return True
	return False
332

333
@app.route('/internal/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
334
def login():
335
336
	if request.method == 'GET':
		return render_template('login.html')
337
338
	userinfo, groups = ldapauth(request.form.get('user'), request.form.get('password'))
	user = userinfo.get('uid')
339
	if not check_mod(user, groups):
340
		flash('Login fehlgeschlagen!')
341
		return render_template('login.html')
342
	session['user'] = userinfo
343
344
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
Julian Rother's avatar
Julian Rother committed
345
		modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
346
347
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
348
	session['_csrf_token'] = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(64))
Andreas Valder's avatar
Andreas Valder committed
349
	session.permanent = True
Julian Rother's avatar
Julian Rother committed
350
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
351

352
@app.route('/internal/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
353
def logout():
354
	session.pop('user', None)
Julian Rother's avatar
Julian Rother committed
355
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
356

357
@app.route('/internal/auth')
358
359
360
361
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
362
363
	if request.cookies.get('tracking', '') and request.cookies['tracking'].isdigit():
		cookie = int(request.cookies['tracking'])
364
	else:
Julian Rother's avatar
Julian Rother committed
365
		cookie = random.getrandbits(8*8-1)
366
	if url.endswith('jpg') or ismod():
367
		return "OK", 200
368
369
	if url.startswith('pub/hls/'):
		handle = url[len('pub/hls/'):].split('_')[0].split('.')[0]
370
		perms = query('''SELECT lectures.id AS lecture, perm.*
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
				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)
388
	if not perms:
389
		return "Not found", 404
390
	auth = request.authorization
391
392
393
394
	username = password = None
	if auth:
		username = auth.username
		password = auth.password
395
	if checkperm(perms, username=username, password=password):
396
		try:
397
398
			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'])
399
400
401
402
			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)
403
404
405
		except:
			pass
		r = make_response('OK', 200)
Julian Rother's avatar
Julian Rother committed
406
		r.set_cookie('tracking', str(cookie), max_age=2147483647) # Many many years
407
		return r
408
	password_auth = False
409
410
	for perm in perms:
		if perm['type'] == 'password':
411
412
413
			password_auth = True
			break
	if password_auth:
414
415
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
416

Julian Rother's avatar
Julian Rother committed
417
418
419
420
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

Andreas Valder's avatar
Andreas Valder committed
421
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
422
423
424
425
426
427
428
429
430
431
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']):
432
			pages.append([url_for('lecture',course=i['handle'],id=j['id'])])
Andreas Valder's avatar
Andreas Valder committed
433
434
435
436


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

Julian Rother's avatar
Julian Rother committed
437
438
439
440
441
442
443
444
445
@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
446
447
448
449
450
		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
451
	for host in sorted(list(hosts)):
Julian Rother's avatar
Julian Rother committed
452
453
454
455
456
457
458
459
460
461
462
463
		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

Andreas Valder's avatar
Andreas Valder committed
464
import edit
Julian Rother's avatar
Julian Rother committed
465
import feeds
466
import importer
467
import stats
Andreas Valder's avatar
Andreas Valder committed
468
import sorter
469
470
if 'ICAL_URL' in config:
	import meetings
471
import l2pauth
Andreas Valder's avatar
Andreas Valder committed
472
473
if 'JOBS_API_KEY' in config:
	import jobs
Andreas Valder's avatar
Andreas Valder committed
474
import timetable
Andreas Valder's avatar
Andreas Valder committed
475
import chapters
476
import icalexport
477
import livestreams