server.py 22.7 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
	fsmpi_intern = False
144
145
	for perm in perms:
		if perm['type'] == 'public':
146
			public = True
147
		elif perm['type'] == 'password':
148
			password = True
149
		elif perm['type'] == 'l2p':
150
			l2p_courses.append(perm['param1'])
151
		elif perm['type'] == 'rwth':
152
			rwth_intern = True
153
154
		elif perm['type'] == 'fsmpi':
			fsmpi_intern = True
155
	if public or not perms:
156
157
158
159
160
		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'
161
162
	if fsmpi_intern:
		return 'fsmpi', 'Nur für Fachschaftler verfügbar'
163
164
	if l2p_courses:
		if password:
165
166
			return 'l2p', 'Nur für Teilnehmer der Veranstaltung und Nutzer mit Passwort verfügbar'
		return 'l2p', 'Nur für Teilnehmer der Veranstaltung verfügbar'
167
168
169
170
	if password:
		return 'password', 'Nur für Nutzer mit Passwort verfügbar'
	return 'public', 'Öffentlich verfügbar'

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

Julian Rother's avatar
Cleanup    
Julian Rother committed
183
184
185
186
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
187
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
188
189
	return app.view_functions[endpoint](**kargs)

190
191
192
193
194
195
196
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
197
				if endpoint:
198
					return make_response(render_endpoint(endpoint, text, **epargs), code)
Julian Rother's avatar
Julian Rother committed
199
200
				else:
					return text, code
201
202
203
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
204
205
@app.errorhandler(404)
def handle_not_found(e):
206
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
207

208
209
210
211
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
212
	return render_template('500.html'), 500
213

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

Julian Rother's avatar
Julian Rother committed
229
@app.template_filter(name='semester')
230
231
232
233
234
235
236
237
238
239
240
241
242
243
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
244
245
246

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

Andreas Valder's avatar
Andreas Valder committed
249
@app.template_filter(name='time')
250
def human_time(d):
Andreas Valder's avatar
Andreas Valder committed
251
252
	return d.strftime('%H:%M')

Julian Rother's avatar
Julian Rother committed
253
254
255
256
@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

257
258
@app.template_global()
def get_announcements(minlevel=0):
259
260
	offset = timedelta()
	if ismod():
261
		offset = timedelta(hours=24)
262
263
264
265
	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 []
266

267
268
269
270
271
@app.template_filter()
def fixnl(s):
	# To be remove, as soon as db schema is cleaned-up
	return str(s).replace('\n', '<br>')

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

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

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

Andreas Valder's avatar
Andreas Valder committed
338
@app.route('/faq')
339
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
340
def faq():
341
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
342

343
@app.route('/play/<int:id>')
Andreas Valder's avatar
Andreas Valder committed
344
@app.route('/embed/<int:id>', endpoint='embed')
345
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
346
def lecture(id):
Andreas Valder's avatar
Andreas Valder committed
347
348
349
350
351
	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)
352
353
354
355
			JOIN courses ON (courses.id = ?)
			WHERE videos.lecture_id = ? AND (? OR videos.visible)
			ORDER BY formats.prio DESC
			''', lecture['course_id'], lecture['id'], ismod())
356
	perms = query('SELECT perm.* FROM perm WHERE ((NOT perm.deleted) AND (perm.lecture_id = ? OR perm.course_id = ?))',
357
			lecture['id'], lecture['course_id'])
358
359
	if not videos:
		flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
360
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', lecture['course_id'], ismod())
361
362
	if not courses:
		return render_endpoint('courses', 'Diese Veranstaltung existiert nicht!'), 404
363
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
364
365
	if not checkperm(perms):
		mode, text = permdescr(perms)
366
367
368
369
370
371
		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+'.')
372
	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
373

374
375
376
377
378
379
380

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

397
398
399
def check_mod(user, groups):
	return user and 'users' in groups

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

Julian Rother's avatar
Julian Rother committed
417
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
418
419
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
420
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
421

422
# name: (tablename, idcolumn, [editable_fields], [fields_to_set_at_creation_time])
423
424
425
tabs = {
	'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
			'handle', 'organizer', 'subject', 'semester', 'downloadable',
426
			'internal', 'responsible','deleted','description'],
427
			['created_by', 'time_created', 'time_updated']),
428
	'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
429
430
431
432
433
434
435
436
437
438
			'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'],
439
			['created_by', 'time_created', 'time_updated']),
440
	'perm': ('perm', 'id', ['type', 'param1', 'param2', 'deleted'],
441
442
443
			['course_id', 'lecture_id', 'video_id', 'created_by', 'time_created', 'time_updated']),
	'sorterrorlog': ('sorterrorlog_data', 'id', ['deleted'],
			['time_created', 'time_updated'])
444
445
}

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

475
@app.route('/new/<table>', methods=['GET', 'POST'])
476
@mod_required
477
@csrf_protect
478
479
def create(table):
	assert table in tabs
480
481
482
483
484
485
486
	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
487
	args = request.values.items()
488
	if (request.method == 'POST') and (request.get_json()):
489
490
491
		args = request.get_json().items()
	for column, val in args:
		if (column == 'ref') or (column == '_csrf_token'):
492
			continue
493
494
		assert column in tabs[table][2]+tabs[table][3]
		assert column not in defaults
495
496
497
498
		columns.append(column)
		values.append(val)
	id = modify('INSERT INTO %s (%s) VALUES (%s)'%(tabs[table][0],
				','.join(columns), ','.join(['?']*len(values))), *values)
499
500
501
502
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return str(id), 200

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

Andreas Valder's avatar
Andreas Valder committed
539
@app.route('/stats')
Andreas Valder's avatar
Andreas Valder committed
540
@register_navbar('Statistiken', icon='stats')
Andreas Valder's avatar
Andreas Valder committed
541
542
543
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
544

Andreas Valder's avatar
Andreas Valder committed
545
@app.route('/changelog')
Andreas Valder's avatar
Andreas Valder committed
546
@register_navbar('Changelog', icon='book')
Andreas Valder's avatar
Andreas Valder committed
547
@mod_required
548
def changelog():
549
550
551
	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']])
552
	return render_template('changelog.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
553

Julian Rother's avatar
Julian Rother committed
554
555
556
557
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

558
559
560
561
562
@app.route('/newchapter/<int:lectureid>', methods=['POST', 'GET'])
def suggest_chapter(lectureid):
	time = request.values['time']
	text = request.values['text']
	assert(time and text)
563
564
565
566
567
568
569
	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')
		
570
571
572
	submitter = None
	if not ismod():
		submitter = request.environ['REMOTE_ADDR']
Julian Rother's avatar
Julian Rother committed
573
	id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
574
575
576
577
578
				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

579
580
581
582
583
584
585
586
587
588
@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
589
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
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
605
import feeds
606
import importer
Andreas Valder's avatar
Andreas Valder committed
607
import sorter
608
609
if 'ICAL_URL' in config:
	import meetings
610
import l2pauth
Andreas Valder's avatar
Andreas Valder committed
611
612
if 'JOBS_API_KEY' in config:
	import jobs
Andreas Valder's avatar
Andreas Valder committed
613
import timetable