server.py 22.8 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
37
38
39
				try:
					func(*args, **kargs)
				except Exception:
					traceback.print_exc()
40
			scheduler.enter(delay, priority, sched_wrapper)
41
		scheduler.enter(firstdelay, priority, sched_wrapper)
42
43
44
45
		return func
	return wrapper

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

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

Julian Rother's avatar
Julian Rother committed
58
from db import query, modify, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
59

60
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
61

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

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

77
78
csrf_endpoints = []

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

95
96
97
98
99
100
@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']

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

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

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

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

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

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

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

211
212
213
214
@app.errorhandler(500)
@app.errorhandler(Exception)
def handle_internal_error(e):
	traceback.print_exc()
215
	return render_template('500.html'), 500
216

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

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

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

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

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

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

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

275
@app.route('/')
276
@register_navbar('Home', icon='home')
277
def index():
278
279
280
281
	# handle legacy urls...
	if 'course' in request.args:
		return redirect(url_for('course', handle=request.args['course']),code=302)

282
283
	start = date.today() - timedelta(days=1)
	end = start + timedelta(days=7)
284
285
	upcomming = query('''
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
286
287
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
288
289
		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
290
291
292
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
293
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
294
295
296
297
298
		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
299
		ORDER BY MAX(videos.time_updated) DESC
Andreas Valder's avatar
Andreas Valder committed
300
		LIMIT 6	''',ismod())
301
302
	featured = query('SELECT * FROM featured WHERE NOT deleted AND (? OR visible)', ismod())
	return render_template('index.html', latestvideos=latestvideos, upcomming=upcomming, featured=featured)
303

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

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

Andreas Valder's avatar
Andreas Valder committed
345
@app.route('/faq')
346
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
347
def faq():
348
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
349

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

381
382
383
384
385
386
387

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

404
405
406
def check_mod(user, groups):
	return user and 'users' in groups

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

Julian Rother's avatar
Julian Rother committed
424
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
425
426
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
427
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
428

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

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

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

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

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

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

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

580
581
@app.route('/chapters/<int:lectureid>')
def chapters(lectureid):
582
	chapters = query("SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (visible OR ?) ORDER BY time DESC", lectureid, ismod())
583
584
	if not chapters:
		return 'No chapters found', 404
585
586
587
588
589
590
591
	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
592
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
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
608
import feeds
609
import importer
610
import stats
Andreas Valder's avatar
Andreas Valder committed
611
import sorter
612
613
if 'ICAL_URL' in config:
	import meetings
614
import l2pauth
Andreas Valder's avatar
Andreas Valder committed
615
616
if 'JOBS_API_KEY' in config:
	import jobs
Andreas Valder's avatar
Andreas Valder committed
617
import timetable