server.py 22.5 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

14
app = Flask(__name__)
15

Andreas Valder's avatar
Andreas Valder committed
16
17
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True
Julian Rother's avatar
Julian Rother committed
18
app.add_template_global(random.randint, name='randint')
19
20
app.add_template_global(datetime, name='datetime')
app.add_template_global(timedelta, name='timedelta')
Andreas Valder's avatar
Andreas Valder committed
21

22
23
scheduler = sched.scheduler()
def run_scheduler():
Andreas Valder's avatar
Andreas Valder committed
24
	import time
25
	time.sleep(1) # UWSGI does weird things on startup
26
27
	while True:
		scheduler.run()
28
		time.sleep(10)
29

30
31
32
def sched_func(delay, priority=0, firstdelay=None, args=[], kargs={}):
	if firstdelay == None:
		firstdelay = random.randint(1, 120)
33
34
35
	def wrapper(func):
		def sched_wrapper():
			with app.test_request_context():
36
				func(*args, **kargs)
37
			scheduler.enter(delay, priority, sched_wrapper)
38
		scheduler.enter(firstdelay, priority, sched_wrapper)
39
40
41
42
		return func
	return wrapper

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

44
config = app.config
45
config.from_pyfile('config.py.example', silent=True)
46
47
48
if sys.argv[0].endswith('run.py'): 
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
49
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
50
51
if config['DEBUG']:
	app.jinja_env.auto_reload = True
52
53
if not config.get('SECRET_KEY', None):
	config['SECRET_KEY'] = os.urandom(24)
Julian Rother's avatar
Julian Rother committed
54

Julian Rother's avatar
Julian Rother committed
55
from db import query, modify, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
56

57
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
58

Julian Rother's avatar
Cleanup    
Julian Rother committed
59
@app.template_global()
60
61
62
63
def ismod(*args):
	return ('user' in session)

def mod_required(func):
64
	mod_endpoints.append(func.__name__)
65
66
	@wraps(func)
	def decorator(*args, **kwargs):
67
		if not ismod():
68
69
70
71
72
73
			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

74
75
csrf_endpoints = []

76
def csrf_protect(func):
77
	csrf_endpoints.append(func.__name__)
78
79
80
81
	@wraps(func)
	def decorator(*args, **kwargs):
		if '_csrf_token' in request.values:
			token = request.values['_csrf_token']
Andreas Valder's avatar
Andreas Valder committed
82
		elif request.get_json() and ('_csrf_token' in request.get_json()):
83
84
			token = request.get_json()['_csrf_token']
		else:
85
			token = None
86
87
88
89
90
91
		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

92
93
94
95
96
97
@app.url_defaults
def csrf_inject(endpoint, values):
	if endpoint not in csrf_endpoints or not session['_csrf_token']:
		return
	values['_csrf_token'] = session['_csrf_token']

98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
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'}]
116
117

@app.template_filter()
118
def checkperm(perms, username=None, password=None):
119
120
	if ismod():
		return True
121
122
123
	perms = evalperm(perms)
	for perm in perms:
		if perm['type'] == 'public':
124
			return True
125
126
		elif perm['type'] == 'password':
			if perm['param1'] == username and perm['param2'] == password:
127
				return True
128
129
		elif perm['type'] == 'l2p':
			if perm['param1'] in session.get('l2p_courses', []):
130
				return True
131
		elif perm['type'] == 'rwth':
132
133
134
135
136
			if session.get('rwthintern', False):
				return True
	return False

@app.template_filter()
137
138
def permdescr(perms):
	perms = evalperm(perms)
139
140
141
142
	public = False
	password = False
	l2p_courses = []
	rwth_intern = False
143
144
	for perm in perms:
		if perm['type'] == 'public':
145
			public = True
146
		elif perm['type'] == 'password':
147
			password = True
148
		elif perm['type'] == 'l2p':
149
			l2p_courses.append(perm['param1'])
150
		elif perm['type'] == 'rwth':
151
			rwth_intern = True
152
	if public or not perms:
153
154
155
156
157
158
159
		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'
	if l2p_courses:
		if password:
160
161
			return 'l2p', 'Nur für Teilnehmer der Veranstaltung und Nutzer mit Passwort verfügbar'
		return 'l2p', 'Nur für Teilnehmer der Veranstaltung verfügbar'
162
163
164
165
	if password:
		return 'password', 'Nur für Nutzer mit Passwort verfügbar'
	return 'public', 'Öffentlich verfügbar'

166
app.jinja_env.globals['navbar'] = []
167
168
169
170
171
# 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):
172
	def wrapper(func):
173
		endpoint = func.__name__
174
		app.jinja_env.globals['navbar'].append((endpoint, name, iconlib, icon, not endpoint in mod_endpoints))
175
176
177
		return func
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
178
179
180
181
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
182
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
183
184
	return app.view_functions[endpoint](**kargs)

185
186
187
188
189
190
191
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
192
				if endpoint:
193
					return make_response(render_endpoint(endpoint, text, **epargs), code)
Julian Rother's avatar
Julian Rother committed
194
195
				else:
					return text, code
196
197
198
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
199
200
@app.errorhandler(404)
def handle_not_found(e):
201
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
202

203
204
205
206
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
207
	return render_template('500.html'), 500
208

209
210
211
212
213
214
215
216
217
218
@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
219
220
# debian ships jinja2 without this test...
@app.template_test(name='equalto')
221
222
223
def equalto(a,b):
	return a == b

Julian Rother's avatar
Julian Rother committed
224
@app.template_filter(name='semester')
225
226
227
228
229
230
231
232
233
234
235
236
237
238
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
239
240
241

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

Andreas Valder's avatar
Andreas Valder committed
244
@app.template_filter(name='time')
245
def human_time(d):
Andreas Valder's avatar
Andreas Valder committed
246
247
	return d.strftime('%H:%M')

Julian Rother's avatar
Julian Rother committed
248
249
250
251
@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

252
253
@app.template_global()
def get_announcements(minlevel=0):
254
255
	offset = timedelta()
	if ismod():
256
		offset = timedelta(hours=24)
257
258
259
260
	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 []
261

262
263
264
265
266
@app.template_filter()
def fixnl(s):
	# To be remove, as soon as db schema is cleaned-up
	return str(s).replace('\n', '<br>')

267
@app.route('/')
268
@register_navbar('Home', icon='home')
269
def index():
270
271
	start = date.today() - timedelta(days=1)
	end = start + timedelta(days=7)
272
273
	upcomming = query('''
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
274
275
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
276
277
		WHERE (time > ?) AND (time < ?) and lectures.visible and courses.visible and courses.listed
		ORDER BY time ASC LIMIT 30''',start,end)
Andreas Valder's avatar
Andreas Valder committed
278
279
280
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
281
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
282
283
284
285
286
		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
287
		ORDER BY MAX(videos.time_updated) DESC
Andreas Valder's avatar
Andreas Valder committed
288
		LIMIT 6	''',ismod())
289
290
	featured = query('SELECT * FROM featured WHERE NOT deleted AND (? OR visible)', ismod())
	return render_template('index.html', latestvideos=latestvideos, upcomming=upcomming, featured=featured)
291

292
@app.route('/course')
293
@register_navbar('Videos', icon='film')
294
def courses():
295
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY title', ismod())
296
297
298
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
299
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
300
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
301
		groupedby = 'semester'
302
	return render_template('courses.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
303

304
305
@app.route('/course/<handle>')
@app.route('/course/<int:id>')
306
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
307
308
def course(id=None, handle=None):
	if id:
309
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
310
	else:
311
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
312
313
	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'])
314
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod())
315
	for lecture in lectures:
316
		lecture['perm'] = []
317
		lecture['perm'] += course['perm']
318
		lecture['course'] = course
319
320
321
		for perm in perms:
			if perm['lecture_id'] == lecture['id']:
				lecture['perm'].append(perm)
Andreas Valder's avatar
Andreas Valder committed
322
	videos = query('''
323
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description, formats.player_prio, formats.prio
Andreas Valder's avatar
Andreas Valder committed
324
325
326
327
328
329
			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
330
331
			''', course['id'], ismod())
	return render_template('course.html', course=course, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
332

Andreas Valder's avatar
Andreas Valder committed
333
@app.route('/faq')
334
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
335
def faq():
336
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
337

338
@app.route('/play/<int:id>')
Andreas Valder's avatar
Andreas Valder committed
339
@app.route('/embed/<int:id>', endpoint='embed')
340
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
341
def lecture(id):
Andreas Valder's avatar
Andreas Valder committed
342
343
344
345
346
	lecture = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())[0]
	videos = query('''
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description, formats.player_prio, formats.prio
			FROM videos
			JOIN formats ON (videos.video_format = formats.id)
347
348
349
350
			JOIN courses ON (courses.id = ?)
			WHERE videos.lecture_id = ? AND (? OR videos.visible)
			ORDER BY formats.prio DESC
			''', lecture['course_id'], lecture['id'], ismod())
351
	perms = query('SELECT perm.* FROM perm WHERE ((NOT perm.deleted) AND (perm.lecture_id = ? OR perm.course_id = ?))',
352
			lecture['id'], lecture['course_id'])
353
354
	if not videos:
		flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
355
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', lecture['course_id'], ismod())
356
357
	if not courses:
		return render_endpoint('courses', 'Diese Veranstaltung existiert nicht!'), 404
358
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
359
360
	if not checkperm(perms):
		mode, text = permdescr(perms)
361
362
363
364
365
366
		if mode == 'rwth':
			flash(text+'. <a target="_blank" href="'+url_for('start_rwthauth')+'">Hier authorisieren</a>.')
		elif mode == 'l2p':
			flash(text+'. <a target="_blank" href="'+url_for('start_l2pauth')+'">Hier authorisieren</a>.')
		else:
			flash(text+'.')
367
	return render_template('embed.html' if request.endpoint == 'embed' else 'lecture.html', course=courses[0], lecture=lecture, videos=videos, chapters=chapters)
Andreas Valder's avatar
Andreas Valder committed
368

369
370
371
372
373
374
375

@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'],
376
			'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
377
378
379
380
381
	#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',
382
383
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
384
			'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
385
386
387
388
389
	for lecture in lectures:
		lecture['course'] = {}
		for key in lecture:
			if key.startswith('courses_'):
				lecture['course'][key[8:]] = lecture[key]
390
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
391

392
393
394
def check_mod(user, groups):
	return user and 'users' in groups

395
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
396
def login():
397
398
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
399
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
400
	if not check_mod(user, groups):
401
		flash('Login fehlgeschlagen!')
402
403
404
405
		return render_template('login.html')
	session['user'] = ldapget(user)
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
Julian Rother's avatar
Julian Rother committed
406
		modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
407
408
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
409
	session['_csrf_token'] = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(128))
Julian Rother's avatar
Julian Rother committed
410
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
411

Julian Rother's avatar
Julian Rother committed
412
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
413
414
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
415
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
416

417
# name: (tablename, idcolumn, [editable_fields], [fields_to_set_at_creation_time])
418
419
420
tabs = {
	'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
			'handle', 'organizer', 'subject', 'semester', 'downloadable',
421
			'internal', 'responsible','deleted','description'],
422
			['created_by', 'time_created', 'time_updated']),
423
	'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
424
425
426
427
428
429
430
431
432
433
			'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted'],
			['course_id', 'time_created', 'time_updated']),
	'videos': ('videos_data', 'id', ['visible','deleted'],
			['created_by', 'time_created', 'time_updated']),
	'chapters': ('chapters', 'id', ['time', 'text', 'visible', 'deleted'],
			['created_by', 'time_created', 'time_updated']),
	'announcements': ('announcements', 'id', ['text', 'level', 'visible',
			'deleted', 'time_publish', 'time_expire'],
			['created_by', 'time_created', 'time_updated']),
	'featured': ('featured', 'id', ['title', 'text', 'internal', 'visible', 'deleted'],
434
			['created_by', 'time_created', 'time_updated']),
435
	'perm': ('perm', 'id', ['type', 'param1', 'param2', 'deleted'],
436
437
438
			['course_id', 'lecture_id', 'video_id', 'created_by', 'time_created', 'time_updated']),
	'sorterrorlog': ('sorterrorlog_data', 'id', ['deleted'],
			['time_created', 'time_updated'])
439
440
}

441
@app.route('/edit', methods=['GET', 'POST'])
442
@mod_required
443
@csrf_protect
444
def edit(prefix='', ignore=[]):
445
	# All editable tables are expected to have a 'time_updated' field
446
	ignore.append('ref')
447
	ignore.append('prefix')
448
	ignore.append('_csrf_token')
449
450
	if not prefix and 'prefix' in request.args:
		prefix = request.args['prefix']
Julian Rother's avatar
Julian Rother committed
451
	modify('BEGIN')
452
	changes = request.values.items()
453
	if (request.method == 'POST') and (request.get_json()):
Julian Rother's avatar
Julian Rother committed
454
455
		changes = request.get_json().items()
	for key, val in changes:
456
457
458
		if key in ignore:
			continue
		key = prefix+key
459
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
460
461
		assert table in tabs
		assert column in tabs[table][2]
462
463
		modify('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']['dbid'])
Julian Rother's avatar
Julian Rother committed
464
465
		modify('UPDATE %s SET %s = ?, time_updated = ? WHERE %s = ?'%(tabs[table][0], column, tabs[table][1]), val, datetime.now(), id)
	modify('COMMIT')
466
467
	if 'ref' in request.values:
		return redirect(request.values['ref'])
468
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
469

470
@app.route('/new/<table>', methods=['GET', 'POST'])
471
@mod_required
472
@csrf_protect
473
474
def create(table):
	assert table in tabs
475
476
477
478
479
480
481
	defaults = {'created_by': session['user']['dbid'], 'time_created': datetime.now(), 'time_updated': datetime.now()}
	columns = []
	values = []
	for column, val in defaults.items():
		if column in tabs[table][3]:
			columns.append(column)
			values.append(val)
Andreas Valder's avatar
Andreas Valder committed
482
	args = request.values.items()
483
	if (request.method == 'POST') and (request.get_json()):
484
485
486
		args = request.get_json().items()
	for column, val in args:
		if (column == 'ref') or (column == '_csrf_token'):
487
			continue
488
489
		assert column in tabs[table][2]+tabs[table][3]
		assert column not in defaults
490
491
492
493
		columns.append(column)
		values.append(val)
	id = modify('INSERT INTO %s (%s) VALUES (%s)'%(tabs[table][0],
				','.join(columns), ','.join(['?']*len(values))), *values)
494
495
496
497
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return str(id), 200

498
499
500
501
502
@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'])
503
	ip = request.headers.get('X-Real-IP', '')
504
	if url.endswith('jpg') or ismod():
505
		return "OK", 200
506
	perms = query('''SELECT videos.path, videos.id AS vid, perm.*
507
508
509
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
510
			LEFT JOIN perm ON (videos.id = perm.video_id OR lectures.id = perm.lecture_id OR courses.id = perm.course_id)
511
      WHERE videos.path = ?
512
      AND (courses.visible AND lectures.visible AND videos.visible)
513
			ORDER BY perm.video_id DESC, perm.lecture_id DESC, perm.course_id DESC''',
514
			url)
515
	if not perms:
516
		return "Not allowed", 403
517
	auth = request.authorization
518
519
520
521
	username = password = None
	if auth:
		username = auth.username
		password = auth.password
522
	if checkperm(perms, username=username, password=password):
523
		return 'OK', 200
524
		modify('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), perms[0]['vid'], url)
525
	password_auth = False
526
527
	for perm in perms:
		if perm['type'] == 'password':
528
529
530
			password_auth = True
			break
	if password_auth:
531
532
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
533

Andreas Valder's avatar
Andreas Valder committed
534
@app.route('/stats')
Andreas Valder's avatar
Andreas Valder committed
535
@register_navbar('Statistiken', icon='stats')
Andreas Valder's avatar
Andreas Valder committed
536
537
538
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
539

Andreas Valder's avatar
Andreas Valder committed
540
@app.route('/changelog')
Andreas Valder's avatar
Andreas Valder committed
541
@register_navbar('Changelog', icon='book')
Andreas Valder's avatar
Andreas Valder committed
542
@mod_required
543
def changelog():
544
545
546
	changelog = query('SELECT * FROM changelog LEFT JOIN users ON (changelog.who = users.id) ORDER BY `when` DESC LIMIT 50')
	for entry in changelog:
		entry['path'] = '.'.join([entry['table'], entry['id_value'], entry['field']])
547
	return render_template('changelog.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
548

Julian Rother's avatar
Julian Rother committed
549
550
551
552
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

553
554
555
556
557
@app.route('/newchapter/<int:lectureid>', methods=['POST', 'GET'])
def suggest_chapter(lectureid):
	time = request.values['time']
	text = request.values['text']
	assert(time and text)
558
559
560
561
562
563
564
	try:
		x = datetime.strptime(time,'%H:%M:%S')
		time= timedelta(hours=x.hour,minutes=x.minute,seconds=x.second).total_seconds()
		time = int(time)
	except ValueError:
		flash('Falsches Zeitformat, "%H:%M:%S" wird erwartet. Z.B. "01:39:42" für eine Kapitel bei Stunde 1, Minute 39, Sekunde 42')
		
565
566
567
	submitter = None
	if not ismod():
		submitter = request.environ['REMOTE_ADDR']
Julian Rother's avatar
Julian Rother committed
568
	id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
569
570
571
572
573
				lectureid, time, text, datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid'], submitter)
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return 'OK',  200

574
575
576
577
578
579
580
581
582
583
@app.route('/chapters/<int:lectureid>')
def chapters(lectureid):
	chapters = query("SELECT * FROM chapters WHERE lecture_id = ? and visible ORDER BY time DESC", lectureid)
	last = None
	for c in chapters:
		c['start'] = c['time']
		c['end'] = last['start'] if last else 9999
		last = c
	return Response(render_template('chapters.srt',chapters=chapters), 200, {'Content-Type':'text/vtt'})

Andreas Valder's avatar
Andreas Valder committed
584
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
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']):
			pages.append([url_for('lecture',id=j['id'])])


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

Julian Rother's avatar
Julian Rother committed
600
import feeds
601
import importer
Andreas Valder's avatar
Andreas Valder committed
602
import sorter
603
604
if 'ICAL_URL' in config:
	import meetings
605
import l2pauth
Andreas Valder's avatar
Andreas Valder committed
606
607
if 'JOBS_API_KEY' in config:
	import jobs
Andreas Valder's avatar
Andreas Valder committed
608
import timetable