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

10
app = Flask(__name__)
11

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

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

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

Julian Rother's avatar
Julian Rother committed
41
from db import query, searchquery, ldapauth, ldapget, convert_timestamp
Julian Rother's avatar
Julian Rother committed
42

43
mod_endpoints = []
Julian Rother's avatar
Julian Rother committed
44

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

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

60
app.jinja_env.globals['navbar'] = []
61
def register_navbar(name, icon=None):
62
	def wrapper(func):
63
64
65
		endpoint = func.__name__
		app.jinja_env.globals['navbar'].append((endpoint, name, 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
114
115
116
117

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

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

132
@app.route('/course')
133
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
134
def course():
135
136
137
138
	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
139
	groupedby = request.args.get('groupedby')
Julian Rother's avatar
Cleanup    
Julian Rother committed
140
	if groupedby not in ['title', 'semester', 'organizer']:
Andreas Valder's avatar
Andreas Valder committed
141
		groupedby = 'semester'
142
	return render_template('course.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
143

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

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

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

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

194
195
196
def check_mod(user, groups):
	return user and 'users' in groups

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

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

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

247
248
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
@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

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

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

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

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

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

import feeds
392
import importer