server.py 14.2 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
Julian Rother's avatar
Julian Rother committed
7
import hashlib
8
import locale
9
import random
10

11
locale.setlocale(locale.LC_ALL, 'de_DE.UTF-8')
12

13
app = Flask(__name__)
14

Andreas Valder's avatar
Andreas Valder committed
15
16
17
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True

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

28
config = app.config
29
30
config['DB_SCHEMA'] = 'db_schema.sql'
config['DB_DATA'] = 'db_example.sql'
31
32
33
config['DB_ENGINE'] = 'sqlite'
config['SQLITE_DB'] = 'db.sqlite'
config['SQLITE_INIT_SCHEMA'] = True
34
config['SQLITE_INIT_DATA'] = False
35
config['DEBUG'] = False
36
config['VIDEOPREFIX'] = 'https://videoag.fsmpi.rwth-aachen.de'
37
38
39
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
40
config.from_pyfile('config.py', silent=True)
Andreas Valder's avatar
Andreas Valder committed
41
42
if config['DEBUG']:
	app.jinja_env.auto_reload = True
Julian Rother's avatar
Julian Rother committed
43

Julian Rother's avatar
Julian Rother committed
44
from db import query, searchquery, ldapauth, ldapget, convert_timestamp
Julian Rother's avatar
Julian Rother committed
45

46
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
47

Julian Rother's avatar
Cleanup    
Julian Rother committed
48
@app.template_global()
49
50
51
52
def ismod(*args):
	return ('user' in session)

def mod_required(func):
53
	mod_endpoints.append(func.__name__)
54
55
	@wraps(func)
	def decorator(*args, **kwargs):
56
		if not ismod():
57
58
59
60
61
62
			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

63
app.jinja_env.globals['navbar'] = []
64
def register_navbar(name, icon=None):
65
	def wrapper(func):
66
67
68
		endpoint = func.__name__
		app.jinja_env.globals['navbar'].append((endpoint, name, icon,
					not endpoint in mod_endpoints))
69
70
71
		return func
	return wrapper

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

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

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

Julian Rother's avatar
Julian Rother committed
97
@app.template_filter(name='semester')
98
99
100
101
102
103
104
105
106
107
108
109
110
111
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
112
113
114
115
116
117
118
119
120

@app.template_filter(name='date')
def human_date(d):
	return d.strftime('%x')

@app.template_filter()
def rfc3339(d):
	return d.strftime('%Y-%m-%dT%H:%M:%S+02:00')

121
@app.route('/')
122
@register_navbar('Home', icon='home')
123
def index():
124
	return render_template('index.html', latestvideos=query('''
125
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
126
127
128
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
129
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
130
131
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
132
				LIMIT 6
133
			''', ismod()))
134

135
@app.route('/course')
136
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
137
def course():
138
139
140
141
	courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed))', ismod())
	for course in courses:
		if course['semester'] == '':
			course['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
142
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
143
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
144
		groupedby = 'semester'
145
	return render_template('course.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
146

Andreas Valder's avatar
Andreas Valder committed
147
@app.route('/course/<id>')
148
@app.route('/course/<int:numid>')
149
150
@handle_errors('course', 'Diese Veranstaltung existiert nicht!', 404, IndexError)
def course_id(numid=None, id=None):
151
	if numid:
Andreas Valder's avatar
Andreas Valder committed
152
		courses = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', numid, ismod())[0]
153
	else:
Andreas Valder's avatar
Andreas Valder committed
154
155
		courses = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', id, ismod())[0]
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible)', courses['id'], ismod())
Andreas Valder's avatar
Andreas Valder committed
156
157
158
159
160
161
162
163
	videos = query('''
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description
			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
Andreas Valder's avatar
Andreas Valder committed
164
165
			''', courses['id'], ismod())
	return render_template('course_id.html', course=courses, lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
166

Andreas Valder's avatar
Andreas Valder committed
167
@app.route('/faq')
168
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
169
def faq():
170
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
171

172
@app.route('/play/<int:id>')
173
@handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError)
174
def play(id):
175
176
177
178
179
180
	lectures = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod())
	videos = query('SELECT * FROM videos WHERE lecture_id = ? AND (? OR visible)', id, ismod())
	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:
181
		return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404
182
	return render_template('play.html', course=courses[0], lecture=lectures[0], videos=videos)
183
184
185
186
187
188
189

@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'],
190
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
191
192
193
	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)',
194
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
195
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
196

197
198
199
def check_mod(user, groups):
	return user and 'users' in groups

200
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
201
def login():
202
203
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
204
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
205
	if not check_mod(user, groups):
206
		flash('Login fehlgeschlagen!')
207
208
209
210
211
212
213
		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
214
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
215

Julian Rother's avatar
Julian Rother committed
216
@app.route('/logout', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
217
218
def logout():
	session.pop('user')
Julian Rother's avatar
Julian Rother committed
219
	return redirect(request.values.get('ref', url_for('index')))
Julian Rother's avatar
Julian Rother committed
220

221
@app.route('/edit', methods=['GET', 'POST'])
222
@mod_required
223
def edit(prefix="", ignore=[]):
Julian Rother's avatar
Julian Rother committed
224
225
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
226
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
227
				'internal', 'responsible','deleted']),
Julian Rother's avatar
Julian Rother committed
228
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
229
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']),
230
		'site_texts': ('site_texts', 'key', ['value']),
231
		'videos': ('videos_data', 'id', ['visible','deleted'])
Julian Rother's avatar
Julian Rother committed
232
	}
233
	query('BEGIN')
234
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
235
236
237
238
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
239
240
241
		if key in ignore:
			continue
		key = prefix+key
242
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
243
244
		assert table in tabs
		assert column in tabs[table][2]
245
		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'])
246
		query('UPDATE %s SET %s = ? WHERE %s = ?'%(tabs[table][0], column,tabs[table][1]), val, id)
247
	query('COMMIT')
248
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
249

250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
@app.route('/newcourse', methods=['GET', 'POST'])
@mod_required
def newcourse():
	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
def newlecture(courseid):
	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

279
280
281
282
283
@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'])
284
	ip = request.headers.get('X-Real-IP', '')
285
	if url.endswith('jpg'):
286
		return "OK", 200
287
	videos = query('''SELECT videos.path, videos.id, lectures.id AS lecture_id, courses.id AS course_id, auth.*
288
289
290
      FROM videos
      JOIN lectures ON (videos.lecture_id = lectures.id)
      JOIN courses ON (lectures.course_id = courses.id)
291
			LEFT JOIN auth ON (videos.id = auth.video_id OR lectures.id = auth.lecture_id OR courses.id = auth.course_id)
292
293
      WHERE videos.path = ?
      AND (? OR (courses.visible AND lectures.visible AND videos.visible))
294
			ORDER BY auth.video_id DESC, auth.lecture_id DESC, auth.course_id DESC''',
295
296
			url, ismod())
	if not videos:
297
		return "Not allowed", 403
298
299
300
301
	allowed = False
	types = []
	auth = request.authorization
	for video in videos:
302
303
		if videos[0] and ((videos[0]['video_id'] and not video['video_id']) \
				or (videos[0]['lecture_id'] and not video['lecture_id'])):
304
305
306
307
308
309
310
311
312
			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
313
314
	if not types[0] or allowed or ismod() or \
			(auth and check_mod(*ldapauth(auth.username, auth.password))):
315
316
317
318
319
		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
320
321

@app.route('/schedule')
322
323
@register_navbar('Drehplan', 'calendar')
@mod_required
Andreas Valder's avatar
Andreas Valder committed
324
def schedule():
325
326
327
328
329
	if 'kw' not in request.args:
		kw=0
	else:
		kw=int(request.args['kw'])
	start = date.today() - timedelta(days=date.today().weekday() -7*kw)
Andreas Valder's avatar
Andreas Valder committed
330
331
332
	days = [{'date': start, 'lectures': [], 'atonce':0, 'index': 0 }]
	earlieststart=time(23,59)
	latestend=time(0,0)
333
	for i in range(1,7):
Andreas Valder's avatar
Andreas Valder committed
334
		days.append({'date': days[i-1]['date'] + timedelta(days=1), 'atonce':0, 'index': i, 'lectures':[] })
335
336
	for i in days:
		# date and times are burning in sqlite
337
338
		s = datetime.combine(i['date'],time())
		e = datetime.combine(i['date'],time(23,59))
339
340
341
342
343
344
345
		i['lectures'] = query ('''
					SELECT lectures.*,courses.short
					FROM lectures 
					JOIN courses ON (lectures.course_id = courses.id) 
					WHERE (time < ?) AND (time > ?) 
					ORDER BY time ASC'''
				,e,s);
346
347
348
349
350
		# sweepline to find out how many lectures overlap
		maxcol=0;
		curcol=0;
		freecol=[];
		for l in i['lectures']:
351
			# who the hell inserts lectures with zero length?!?!?
352
			l['time_end'] = l['time']+timedelta(minutes=max(l['duration'],1))
353
		for l in sorted([(l['time'],True,l) for l in i['lectures']] + [(l['time_end'],False,l) for l in i['lectures']],key=lambda t:(t[0],t[1])):
354
355
356
357
358
359
360
			if l[1]:
				curcol += 1
				if curcol > maxcol:
					maxcol = curcol
				if len(freecol) == 0:
					freecol.append(maxcol)
				l[2]['schedule_col'] = freecol.pop()
Andreas Valder's avatar
Andreas Valder committed
361
362
				if earlieststart > l[0].time():
					earlieststart = l[0].time()
363
364
365
			else:
				curcol -= 1
				freecol.append(l[2]['schedule_col'])
Andreas Valder's avatar
Andreas Valder committed
366
367
				if latestend < l[0].time():
					latestend = l[0].time()
368
369
		i['maxcol'] = max(maxcol,1)
	times=[]
Andreas Valder's avatar
Andreas Valder committed
370
	s = min(earlieststart,time(8,0))
371
372
	e = max(latestend,time(19,0))
	for i in range(s.hour*4,min(int((60*e.hour/15)/4)*4+5,24*4)):
373
374
		t = i*15
		times.append(time(int(t/60),t%60))
375
	return render_template('schedule.html',days=days,times=times,kw=kw)
Andreas Valder's avatar
Andreas Valder committed
376
377
378
379
380
381

@app.route('/stats')
@register_navbar('Statistiken', 'stats')
@mod_required
def stats():
	return render_template('stats.html')
Andreas Valder's avatar
Andreas Valder committed
382
383
384
385
386

@app.route('/log')
@register_navbar('Changelog', 'book')
@mod_required
def log():
387
	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')
388
	return render_template('log.html', changelog=changelog)
Andreas Valder's avatar
Andreas Valder committed
389

Julian Rother's avatar
Julian Rother committed
390
391
392
393
394
@app.route('/files/<filename>')
def files(filename):
	return redirect(config['VIDEOPREFIX']+'/'+filename)

import feeds
395
import importer