server.py 16.3 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
def register_navbar(name, icon=None):
58
	def wrapper(func):
59
60
61
		endpoint = func.__name__
		app.jinja_env.globals['navbar'].append((endpoint, name, icon,
					not endpoint in mod_endpoints))
62
63
64
		return func
	return wrapper

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

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

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

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

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

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

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

118
119
120
121
@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)

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

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

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

164
165
@app.route('/course/<handle>')
@app.route('/course/<int:id>')
166
@handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
167
168
def course(id=None, handle=None):
	if id:
169
		course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0]
170
	else:
171
		course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0]
172
173
174
	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())
175
176
177
178
179
	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
180
	videos = query('''
181
			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
182
183
184
185
186
187
			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
188
189
			''', course['id'], ismod())
	return render_template('course.html', course=course, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
190

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

196
@app.route('/play/<int:id>')
Andreas Valder's avatar
Andreas Valder committed
197
@app.route('/embed/<int:id>', endpoint='embed')
198
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
199
def lecture(id):
200
	lectures = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())
201
	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())
202
203
204
205
	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:
206
		return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404
207
	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
208
209
	return render_template('embed.html' if request.endpoint == 'embed' else 'lecture.html', course=courses[0], lecture=lectures[0], videos=videos, chapters=chapters)

210
211
212
213
214
215
216

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

224
225
226
def check_mod(user, groups):
	return user and 'users' in groups

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

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

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

279
280
@app.route('/newcourse', methods=['GET', 'POST'])
@mod_required
281
def new_course():
Julian Rother's avatar
Julian Rother committed
282
	id = modify('''
283
284
285
286
287
288
289
290
291
292
293
294
295
		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
296
def new_lecture(courseid):
Julian Rother's avatar
Julian Rother committed
297
	id = modify('''
298
299
300
301
302
303
304
305
306
307
		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

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

Andreas Valder's avatar
Andreas Valder committed
350
351
352
353
354
@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
355

Andreas Valder's avatar
Andreas Valder committed
356
@app.route('/changelog')
Andreas Valder's avatar
Andreas Valder committed
357
358
@register_navbar('Changelog', 'book')
@mod_required
359
def changelog():
360
	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')
361
	return render_template('changelog.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
362

Julian Rother's avatar
Julian Rother committed
363
364
365
366
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

367
368
369
370
371
372
373
374
375
@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
376
	id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
377
378
379
380
381
				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
382
383
384
@app.route('/newpsa', methods=['POST', 'GET'])
@mod_required
def new_announcement():
Julian Rother's avatar
Julian Rother committed
385
	id = modify('INSERT INTO announcements (text, internal, time_created, time_updated, created_by) VALUES ("Neue Ankündigung", "", ?, ?, ?)',
Julian Rother's avatar
Julian Rother committed
386
387
388
389
390
			datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid'])
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return id,  200

391
392
393
@app.route('/newfeatured', methods=['POST', 'GET'])
@mod_required
def new_featured():
Julian Rother's avatar
Julian Rother committed
394
	id = modify('INSERT INTO featured (time_created, time_updated, created_by) VALUES (?, ?, ?)',
395
396
397
398
399
			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
400
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
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
417
418
419
420
421
@app.route('/sortlog')
@register_navbar('Sortierlog', 'sort-by-attributes-alt')
@mod_required
def sortlog():
	return render_template('sortlog.html')
Andreas Valder's avatar
Andreas Valder committed
422

Julian Rother's avatar
Julian Rother committed
423
import feeds
424
import importer
Andreas Valder's avatar
Andreas Valder committed
425
import schedule