server.py 16.5 KB
Newer Older
1
from flask import Flask, g, request, url_for, redirect, session, render_template, flash, 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

11
app = Flask(__name__)
12

Andreas Valder's avatar
Andreas Valder committed
13
14
15
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True

16
17
18
19
20
21
22
23
24
25
def timer_func():
	with app.test_request_context():
		pass # do something
	timer = threading.Timer(60*60, timer_func)
	timer.start()

timer = threading.Timer(0, timer_func)
timer.daemon = True
timer.start()

26
config = app.config
27
config.from_pyfile('config.py.example', silent=True)
28
if not sys.argv[0].endswith('run.py'): 
29
30
	config['SQLITE_INIT_DATA'] = False
	config['DEBUG'] = False
31
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
32
33
if config['DEBUG']:
	app.jinja_env.auto_reload = True
34
35
if not config.get('SECRET_KEY', None):
	config['SECRET_KEY'] = os.urandom(24)
Julian Rother's avatar
Julian Rother committed
36

Julian Rother's avatar
Julian Rother committed
37
from db import query, modify, searchquery, ldapauth, ldapget
Julian Rother's avatar
Julian Rother committed
38

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

Julian Rother's avatar
Cleanup    
Julian Rother committed
41
@app.template_global()
42
43
44
45
def ismod(*args):
	return ('user' in session)

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

56
app.jinja_env.globals['navbar'] = []
57
58
59
60
61
# 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):
62
	def wrapper(func):
63
		endpoint = func.__name__
64
		app.jinja_env.globals['navbar'].append((endpoint, name, iconlib, icon, not endpoint in mod_endpoints))
65
66
67
		return func
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
68
69
70
71
def render_endpoint(endpoint, flashtext=None, **kargs):
	if flashtext:
		flash(flashtext)
	# request.endpoint is used for navbar highlighting
72
	request.url_rule = Rule(request.path, endpoint=endpoint)
Julian Rother's avatar
Cleanup    
Julian Rother committed
73
74
	return app.view_functions[endpoint](**kargs)

75
76
77
78
79
80
81
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
82
83
84
85
				if endpoint:
					return render_endpoint(endpoint, text, **epargs), code
				else:
					return text, code
86
87
88
		return decorator
	return wrapper

Julian Rother's avatar
Cleanup    
Julian Rother committed
89
90
@app.errorhandler(404)
def handle_not_found(e):
91
	return render_endpoint('index', 'Diese Seite existiert nicht!'), 404
Julian Rother's avatar
Cleanup    
Julian Rother committed
92

Julian Rother's avatar
Julian Rother committed
93
@app.template_filter(name='semester')
94
95
96
97
98
99
100
101
102
103
104
105
106
107
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
108
109
110

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

Andreas Valder's avatar
Andreas Valder committed
113
114
115
116
@app.template_filter(name='time')
def human_date(d):
	return d.strftime('%H:%M')

Julian Rother's avatar
Julian Rother committed
117
118
119
120
@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

121
122
123
124
@app.template_global()
def get_announcements(minlevel=0):
	return query('SELECT * FROM announcements WHERE NOT deleted AND (? OR visible) AND level >= ? ORDER BY level DESC', ismod(), minlevel)

125
126
127
128
129
@app.template_filter()
def fixnl(s):
	# To be remove, as soon as db schema is cleaned-up
	return str(s).replace('\n', '<br>')

130
@app.route('/')
131
@register_navbar('Home', icon='home')
132
def index():
133
134
	start = date.today() - timedelta(days=1)
	end = start + timedelta(days=7)
135
136
	upcomming = query('''
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
137
138
		FROM lectures
		JOIN courses ON (lectures.course_id = courses.id)
139
140
		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
141
142
143
	for i in upcomming:
		i['date'] = i['time'].date()
	latestvideos=query('''
144
		SELECT lectures.*, "course" AS sep, courses.*
Andreas Valder's avatar
Andreas Valder committed
145
146
147
148
149
		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
150
		ORDER BY MAX(videos.time_updated) DESC
Andreas Valder's avatar
Andreas Valder committed
151
		LIMIT 6	''',ismod())
152
153
	featured = query('SELECT * FROM featured WHERE NOT deleted AND (? OR visible)', ismod())
	return render_template('index.html', latestvideos=latestvideos, upcomming=upcomming, featured=featured)
154

155
@app.route('/course')
156
@register_navbar('Videos', icon='film')
157
def courses():
158
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY title', ismod())
159
160
161
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
162
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
163
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
164
		groupedby = 'semester'
165
	return render_template('courses.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
166

167
168
@app.route('/course/<handle>')
@app.route('/course/<int:id>')
169
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
170
171
def course(id=None, handle=None):
	if id:
172
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
173
	else:
174
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
175
176
177
	course['auth'] = query('SELECT * FROM auth WHERE course_id = ? ORDER BY auth_type', course['id'])
	auths = query('SELECT auth.* FROM auth JOIN lectures ON (auth.lecture_id = lectures.id) WHERE lectures.course_id = ? ORDER BY auth.auth_type', course['id'])
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod())
178
179
180
181
182
	for lecture in lectures:
		lecture['auth'] = []
		for auth in auths:
			if auth['lecture_id'] == lecture['id']:
				lecture['auth'].append(auth)
Andreas Valder's avatar
Andreas Valder committed
183
	videos = query('''
184
			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
185
186
187
188
189
190
			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
191
192
			''', course['id'], ismod())
	return render_template('course.html', course=course, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
193

Andreas Valder's avatar
Andreas Valder committed
194
@app.route('/faq')
195
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
196
def faq():
197
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
198

199
@app.route('/play/<int:id>')
Andreas Valder's avatar
Andreas Valder committed
200
@app.route('/embed/<int:id>', endpoint='embed')
201
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
202
def lecture(id):
203
	lectures = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())
204
	videos = query('SELECT videos.*, formats.description AS format_description, formats.prio, formats.player_prio FROM videos JOIN formats ON (videos.video_format = formats.id) WHERE lecture_id = ? AND (? OR visible)', id, ismod())
205
206
207
208
	if not videos:
		flash('Zu dieser Vorlesung wurden noch keine Videos veröffentlicht!')
	courses = query('SELECT * FROM courses WHERE id = ? AND (? OR (visible AND listed))', lectures[0]['course_id'], ismod())
	if not courses:
209
		return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404
210
	chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod())
Andreas Valder's avatar
Andreas Valder committed
211
212
	return render_template('embed.html' if request.endpoint == 'embed' else 'lecture.html', course=courses[0], lecture=lectures[0], videos=videos, chapters=chapters)

213
214
215
216
217
218
219

@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'],
220
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
221
	lectures = searchquery(q, 'lectures.*, courses.visible AS coursevisible, courses.listed, "course" AS sep, courses.*',
222
223
			['lectures.title', 'lectures.comment', 'lectures.speaker', 'courses.short'],
			'lectures LEFT JOIN courses on (courses.id = lectures.course_id)',
224
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
225
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
226

227
228
229
def check_mod(user, groups):
	return user and 'users' in groups

230
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
231
def login():
232
233
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
234
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
235
	if not check_mod(user, groups):
236
		flash('Login fehlgeschlagen!')
237
238
239
240
		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
241
		modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
242
243
		dbuser = query('SELECT * FROM users WHERE name = ?', user)
	session['user']['dbid'] = dbuser[0]['id']
Julian Rother's avatar
Julian Rother committed
244
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
245

Julian Rother's avatar
Julian Rother committed
246
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
247
248
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
249
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
250

251
@app.route('/edit', methods=['GET', 'POST'])
252
@mod_required
253
def edit(prefix="", ignore=[]):
254
	# All editable tables are expected to have a 'time_updated' field
Julian Rother's avatar
Julian Rother committed
255
256
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
257
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
258
				'internal', 'responsible','deleted']),
Julian Rother's avatar
Julian Rother committed
259
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
260
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']),
261
		'videos': ('videos_data', 'id', ['visible','deleted']),
Julian Rother's avatar
Julian Rother committed
262
		'chapters': ('chapters', 'id', ['time', 'text', 'visible', 'deleted']),
Andreas Valder's avatar
Andreas Valder committed
263
264
		'announcements': ('announcements', 'id', ['text', 'internal', 'level', 'visible', 'deleted']),
		'featured': ('featured', 'id', ['text', 'title', 'visible', 'deleted'])
Julian Rother's avatar
Julian Rother committed
265
	}
Julian Rother's avatar
Julian Rother committed
266
	modify('BEGIN')
267
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
268
269
270
271
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
272
273
274
		if key in ignore:
			continue
		key = prefix+key
275
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
276
277
		assert table in tabs
		assert column in tabs[table][2]
Julian Rother's avatar
Julian Rother committed
278
279
280
		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']['givenName'])
		modify('UPDATE %s SET %s = ?, time_updated = ? WHERE %s = ?'%(tabs[table][0], column, tabs[table][1]), val, datetime.now(), id)
	modify('COMMIT')
281
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
282

283
284
@app.route('/newcourse', methods=['GET', 'POST'])
@mod_required
285
def new_course():
Julian Rother's avatar
Julian Rother committed
286
	id = modify('''
287
288
289
290
291
292
293
294
295
296
297
298
299
		INSERT INTO courses_data
			(visible, title, short, handle, organizer, subject, created_by, time_created,
			 time_updated, semester, settings, description, internal, responsible, feed_url)
			VALUES (0, "Neue Veranstaltung", "Neu", ?, "", "", ?, ?, ?, "", "", "", "", ?, "")
		''', 'new'+str(random.randint(0,1000)), session['user']['dbid'], datetime.now(), datetime.now(),
		session['user']['givenName'])
	edit(prefix='courses.'+str(id)+'.', ignore=['ref'])
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return str(id), 200

@app.route('/newlecture/<courseid>', methods=['GET', 'POST'])
@mod_required
300
def new_lecture(courseid):
Julian Rother's avatar
Julian Rother committed
301
	id = modify('''
302
303
304
305
306
307
308
309
310
311
		INSERT INTO lectures_data
			(course_id, visible, drehplan, title, comment, internal, speaker, place,
				time, time_created, time_updated, jumplist, titlefile)
			VALUES (?, 0, "", "Noch kein Titel", "", "", "", "", ?, ?, ?, "", "")
		''', courseid, datetime.now(), datetime.now(), datetime.now())
	edit(prefix='lectures.'+str(id)+'.', ignore=['ref'])
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return str(id), 200

312
313
314
315
316
@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'])
317
	ip = request.headers.get('X-Real-IP', '')
318
	if url.endswith('jpg'):
319
		return "OK", 200
320
	videos = query('''SELECT videos.path, videos.id, lectures.id AS lecture_id, courses.id AS course_id, auth.*
321
322
323
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
324
			LEFT JOIN auth ON (videos.id = auth.video_id OR lectures.id = auth.lecture_id OR courses.id = auth.course_id)
325
326
      WHERE videos.path = ?
      AND (? OR (courses.visible AND lectures.visible AND videos.visible))
327
			ORDER BY auth.video_id DESC, auth.lecture_id DESC, auth.course_id DESC''',
328
329
			url, ismod())
	if not videos:
330
		return "Not allowed", 403
331
332
333
334
	allowed = False
	types = []
	auth = request.authorization
	for video in videos:
335
336
		if videos[0] and ((videos[0]['video_id'] and not video['video_id']) \
				or (videos[0]['lecture_id'] and not video['lecture_id'])):
337
338
339
340
341
342
343
344
345
			break
		types.append(video['auth_type'])
		if video['auth_type'] == 'public':
			allowed = True
			break
		elif video['auth_type'] == 'password':
			if auth and video['auth_user'] == auth.username and video['auth_passwd'] == auth.password:
				allowed = True
				break
346
347
	if not types[0] or allowed or ismod() or \
			(auth and check_mod(*ldapauth(auth.username, auth.password))):
348
		return 'OK', 200
Julian Rother's avatar
Julian Rother committed
349
		modify('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), videos[0]['id'], url)
350
351
352
	elif 'password' in types:
		return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'})
	return "Not allowed", 403
Andreas Valder's avatar
Andreas Valder committed
353

Andreas Valder's avatar
Andreas Valder committed
354
@app.route('/stats')
Andreas Valder's avatar
Andreas Valder committed
355
@register_navbar('Statistiken', icon='stats')
Andreas Valder's avatar
Andreas Valder committed
356
357
358
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
359

Andreas Valder's avatar
Andreas Valder committed
360
@app.route('/changelog')
Andreas Valder's avatar
Andreas Valder committed
361
@register_navbar('Changelog', icon='book')
Andreas Valder's avatar
Andreas Valder committed
362
@mod_required
363
def changelog():
364
	changelog = query('SELECT *, ( "table" || "." || id_value || "." ||field) as path FROM changelog LEFT JOIN users ON (changelog.who = users.id) ORDER BY `when` DESC LIMIT 50')
365
	return render_template('changelog.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
366

Julian Rother's avatar
Julian Rother committed
367
368
369
370
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

371
372
373
374
375
376
377
378
379
@app.route('/newchapter/<int:lectureid>', methods=['POST', 'GET'])
def suggest_chapter(lectureid):
	time = request.values['time']
	text = request.values['text']
	assert(time and text)
	time = int(time)
	submitter = None
	if not ismod():
		submitter = request.environ['REMOTE_ADDR']
Julian Rother's avatar
Julian Rother committed
380
	id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
381
382
383
384
385
				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

Julian Rother's avatar
Julian Rother committed
386
387
388
@app.route('/newpsa', methods=['POST', 'GET'])
@mod_required
def new_announcement():
Julian Rother's avatar
Julian Rother committed
389
	id = modify('INSERT INTO announcements (text, internal, time_created, time_updated, created_by) VALUES ("Neue Ankündigung", "", ?, ?, ?)',
Julian Rother's avatar
Julian Rother committed
390
391
392
393
394
			datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid'])
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return id,  200

395
396
397
@app.route('/newfeatured', methods=['POST', 'GET'])
@mod_required
def new_featured():
Julian Rother's avatar
Julian Rother committed
398
	id = modify('INSERT INTO featured (time_created, time_updated, created_by) VALUES (?, ?, ?)',
399
400
401
402
403
			datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid'])
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return id,  200

Andreas Valder's avatar
Andreas Valder committed
404
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
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'} )


Andreas Valder's avatar
Andreas Valder committed
421
@app.route('/sortlog')
Andreas Valder's avatar
Andreas Valder committed
422
@register_navbar('Sortierlog', icon='sort-by-attributes-alt')
Andreas Valder's avatar
Andreas Valder committed
423
424
425
@mod_required
def sortlog():
	return render_template('sortlog.html')
Andreas Valder's avatar
Andreas Valder committed
426

Julian Rother's avatar
Julian Rother committed
427
import feeds
428
import importer
Andreas Valder's avatar
Andreas Valder committed
429
import schedule