server.py 15.7 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
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True
Julian Rother's avatar
Julian Rother committed
15
app.add_template_global(random.randint, name='randint')
Andreas Valder's avatar
Andreas Valder committed
16

17
18
19
20
21
22
23
24
25
26
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()

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

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

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

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

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

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

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

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

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

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

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

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

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

122
123
124
125
@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)

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

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

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

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

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

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

214
215
216
217
218
219
220

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

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

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

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

252
253
254
255
256
257
258
259
tabs = {
	'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
			'handle', 'organizer', 'subject', 'semester', 'downloadable',
			'internal', 'responsible','deleted']),
	'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
			'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']),
	'videos': ('videos_data', 'id', ['visible','deleted']),
	'chapters': ('chapters', 'id', ['time', 'text', 'visible', 'deleted']),
260
261
	'announcements': ('announcements', 'id', ['text', 'internal', 'level', 'visible', 'deleted']),
	'featured': ('featured', 'id', ['title', 'text', 'internal', 'visible', 'deleted'])
262
263
}

264
@app.route('/edit', methods=['GET', 'POST'])
265
@mod_required
266
def edit(prefix="", ignore=[]):
267
	# All editable tables are expected to have a 'time_updated' field
268
	ignore.append('ref')
Julian Rother's avatar
Julian Rother committed
269
	modify('BEGIN')
270
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
271
272
273
		changes = request.get_json().items()
	else:
		changes = request.args.items()
274
	created = {}
Julian Rother's avatar
Julian Rother committed
275
	for key, val in changes:
276
277
278
		if key in ignore:
			continue
		key = prefix+key
279
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
280
281
		assert table in tabs
		assert column in tabs[table][2]
282
283
		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
284
285
		modify('UPDATE %s SET %s = ?, time_updated = ? WHERE %s = ?'%(tabs[table][0], column, tabs[table][1]), val, datetime.now(), id)
	modify('COMMIT')
286
287
	if 'ref' in request.values:
		return redirect(request.values['ref'])
288
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
289

290
291
292
293
294
295
296
297
298
299
300
@app.route('/new/<table>', methods=['GET', 'POST'])
@mod_required
def create(table):
	assert table in tabs
	id = modify('INSERT INTO %s (created_by, time_created, time_updated) VALUES (?, ?, ?)'%tabs[table][0],
			session['user']['dbid'], datetime.now(), datetime.now())
	edit(prefix=table+'.'+str(id)+'.')
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	return str(id), 200

301
302
@app.route('/newlecture/<courseid>', methods=['GET', 'POST'])
@mod_required
303
def new_lecture(courseid):
Julian Rother's avatar
Julian Rother committed
304
	id = modify('''
305
306
307
308
309
310
311
312
313
314
		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

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

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

Andreas Valder's avatar
Andreas Valder committed
363
@app.route('/changelog')
Andreas Valder's avatar
Andreas Valder committed
364
@register_navbar('Changelog', icon='book')
Andreas Valder's avatar
Andreas Valder committed
365
@mod_required
366
def changelog():
367
368
369
	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']])
370
	return render_template('changelog.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
371

Julian Rother's avatar
Julian Rother committed
372
373
374
375
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

376
377
378
379
380
381
382
383
384
@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
385
	id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
386
387
388
389
390
				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

Andreas Valder's avatar
Andreas Valder committed
391
@app.route('/sitemap.xml')
Andreas Valder's avatar
Andreas Valder committed
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
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
408
@app.route('/sortlog')
Andreas Valder's avatar
Andreas Valder committed
409
@register_navbar('Sortierlog', icon='sort-by-attributes-alt')
Andreas Valder's avatar
Andreas Valder committed
410
411
412
@mod_required
def sortlog():
	return render_template('sortlog.html')
Andreas Valder's avatar
Andreas Valder committed
413

Julian Rother's avatar
Julian Rother committed
414
import feeds
415
import importer
Andreas Valder's avatar
Andreas Valder committed
416
import schedule
Andreas Valder's avatar
Andreas Valder committed
417
import sorter