server.py 16.4 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, searchquery, ldapauth, ldapget, convert_timestamp
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
238
239
240
		return render_template('login.html')
	session['user'] = ldapget(user)
	dbuser = query('SELECT * FROM users WHERE name = ?', user)
	if not dbuser:
		query('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user)
		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
	}
262
	query('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
		if key in ignore:
			continue
Julian Rother's avatar
Julian Rother committed
270
		print('edit:', key, val)
271
		key = prefix+key
Andreas Valder's avatar
Andreas Valder committed
272
		print (key,val)
273
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
274
275
		assert table in tabs
		assert column in tabs[table][2]
276
		query('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'])
277
		query('UPDATE %s SET %s = ?, time_updated = ? WHERE %s = ?'%(tabs[table][0], column, tabs[table][1]), val, datetime.now(), id)
278
	query('COMMIT')
279
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
280

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

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

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

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

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

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

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

Julian Rother's avatar
Julian Rother committed
425
import feeds
426
import importer
Andreas Valder's avatar
Andreas Valder committed
427
import schedule