server.py 10.8 KB
Newer Older
Julian Rother's avatar
Julian Rother committed
1
#!/bin/python
2

3
from flask import *
4
from functools import wraps
5
import datetime
6
import sqlite3
7
import os
Julian Rother's avatar
Julian Rother committed
8
import re
9

10
app = Flask(__name__)
11
config = app.config
12
13
config['DB_SCHEMA'] = 'db_schema.sql'
config['DB_DATA'] = 'db_example.sql'
14
15
16
config['DB_ENGINE'] = 'sqlite'
config['SQLITE_DB'] = 'db.sqlite'
config['SQLITE_INIT_SCHEMA'] = True
17
config['SQLITE_INIT_DATA'] = False
18
config['DEBUG'] = False
19
config['VIDEOPREFIX'] = 'https://videoag.fsmpi.rwth-aachen.de'
20
21
22
23
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
config.from_pyfile('config.py', silent=True)
24
app.jinja_env.globals['videoprefix'] = config['VIDEOPREFIX']
25
mod_endpoints = []
26

27
28
29
30
31
32
33
34
35
36
37
if config['DB_ENGINE'] == 'sqlite':
	created = not os.path.exists(config['SQLITE_DB'])
	db = sqlite3.connect(config['SQLITE_DB'])
	cur = db.cursor()
	if config['SQLITE_INIT_SCHEMA']:
		cur.executescript(open(config['DB_SCHEMA']).read())
	if config['SQLITE_INIT_DATA'] and created:
		cur.executescript(open(config['DB_DATA']).read())
	db.commit()
	db.close()

38
39
40
41
42
# Row wrapper for sqlite
def dict_factory(cursor, row):
	d = {}
	for idx, col in enumerate(cursor.description):
		if type(row[idx]) == str:
43
			d[col[0].split('.')[-1]] = row[idx].replace('\\n','\n').replace('\\r','\r')
44
45
46
47
		else:
			d[col[0].split('.')[-1]] = row[idx]
	return d

48
def query(operation, *params):
49
	if config['DB_ENGINE'] == 'mysql':
50
		import mysql.connector
51
		if 'db' not in g or not g.db.is_connected():
52
			g.db = mysql.connector.connect(user=config['MYSQL_USER'], password=config['MYSQL_PASSWD'], host=config['MYSQL_HOST'], database=config['MYSQL_DB'])
Julian Rother's avatar
Julian Rother committed
53
54
55
		if not hasattr(request, 'db'):
			request.db = g.db.cursor(dictionary=True)
		request.db.execute(operation.replace('?', '%s'), params)
56
	elif config['DB_ENGINE'] == 'sqlite':
Julian Rother's avatar
Julian Rother committed
57
		if 'db' not in g:
58
			g.db = sqlite3.connect(config['SQLITE_DB'])
59
			g.db.row_factory = dict_factory
60
			g.db.isolation_level = None
Julian Rother's avatar
Julian Rother committed
61
62
63
64
65
66
		if not hasattr(request, 'db'):
			request.db = g.db.cursor()
		request.db.execute(operation, params)
	else:
		return []
	return request.db.fetchall()
67

68
69
70
71
72
73
@app.teardown_request
def commit_db(*args):
	if hasattr(request, 'db'):
		request.db.close()
		g.db.commit()

74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
def searchquery(text, columns, match, tables, suffix, *suffixparams):
	params = []
	subexprs = []
	words = text.split(' ')
	prio = len(words)+1
	for word in words:
		if word == '' or word.isspace():
			continue
		matchexpr = ' OR '.join(['%s LIKE ?'%column for column in match])
		subexprs.append('SELECT %s, %s AS _prio FROM %s WHERE %s'%(columns, str(prio), tables, matchexpr))
		params += ['%'+word+'%']*len(match)
		prio -= 1
	if subexprs == []:
		return []
	expr = 'SELECT *,SUM(_prio) AS _score FROM (%s) AS _tmp %s'%(' UNION '.join(subexprs), suffix)
	return query(expr, *params, *suffixparams)

Julian Rother's avatar
Julian Rother committed
91
LDAP_USERRE = re.compile(r'[^a-z0-9]')
Julian Rother's avatar
Julian Rother committed
92
93
94
95
96
notldap = {
	'videoag':('videoag', ['users','videoag'], {'uid': 'videoag', 'givenName': 'Video', 'sn': 'Geier'}),
	'gustav':('passwort', ['users'], {'uid': 'gustav', 'givenName': 'Gustav', 'sn': 'Geier'})
}

Julian Rother's avatar
Julian Rother committed
97
98
99
def ldapauth(user, password):
	user = LDAP_USERRE.sub(r'', user.lower())
	if 'LDAP_HOST' in config:
100
		import ldap3
Julian Rother's avatar
Julian Rother committed
101
102
103
104
105
106
107
108
109
110
111
112
		try:
			conn = ldap3.Connection(config['LDAP_HOST'], 'uid=%s,ou=users,dc=fsmpi,dc=rwth-aachen,dc=de'%user, password, auto_bind=True)
			if conn.search("ou=groups,dc=fsmpi,dc=rwth-aachen,dc=de", "(&(cn=*)(memberUid=%s))"%user, attributes=['cn']):
				groups = [e.cn.value for e in conn.entries]
			conn.unbind()
			return user, groups
		except ldap3.core.exceptions.LDAPBindError:
			pass
	elif config.get('DEBUG') and user in notldap and password == notldap[user][0]:
		return user, notldap[user][1]
	return None, []

Julian Rother's avatar
Julian Rother committed
113
114
115
def ldapget(user):
	user = LDAP_USERRE.sub(r'', user.lower())
	if 'LDAP_HOST' in config:
116
		import ldap3
Julian Rother's avatar
Julian Rother committed
117
118
119
120
121
122
		conn = ldap3.Connection('ldaps://rumo.fsmpi.rwth-aachen.de', auto_bind=True)
		conn.search("ou=users,dc=fsmpi,dc=rwth-aachen,dc=de", "(uid=%s)"%user,
				attributes=ldap3.ALL_ATTRIBUTES)
		e = conn.entries[0]
		return {'uid': user, 'givenName': e.givenName.value, 'sn':e.sn.value}
	else:
Julian Rother's avatar
Julian Rother committed
123
		return notldap[user][2]
Julian Rother's avatar
Julian Rother committed
124

125
126
127
128
129
130
def ismod(*args):
	return ('user' in session)

app.jinja_env.globals['ismod'] = ismod

def mod_required(func):
131
	mod_endpoints.append(func.__name__)
132
133
	@wraps(func)
	def decorator(*args, **kwargs):
134
		if not ismod():
135
136
137
138
			flash('Diese Funktion ist nur für Moderatoren verfügbar!')
			return redirect(url_for('login', ref=request.url))
		else:
			return func(*args, **kwargs)
139
	print(decorator.__name__)
140
141
	return decorator

142
app.jinja_env.globals['navbar'] = []
143
def register_navbar(name, icon=None):
144
	def wrapper(func):
145
146
147
		endpoint = func.__name__
		app.jinja_env.globals['navbar'].append((endpoint, name, icon,
					not endpoint in mod_endpoints))
148
149
150
		return func
	return wrapper

151
@app.route('/')
152
@register_navbar('Home', icon='home')
153
def index():
154
	return render_template('index.html', latestvideos=query('''
155
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
156
157
158
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
159
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
160
161
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
162
				LIMIT 6
163
			''', ismod()))
164

Andreas Valder's avatar
Andreas Valder committed
165
@app.route('/videos')
166
@register_navbar('Videos', icon='film')
Andreas Valder's avatar
Andreas Valder committed
167
def videos():
168
169
170
171
	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
172
173
174
	groupedby = request.args.get('groupedby')
	if groupedby not in ['title','semester','organizer']:
		groupedby = 'semester'
175
	return render_template('videos.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
176
177

@app.route('/faq')
178
@register_navbar('FAQ', icon='question-sign')
Andreas Valder's avatar
Andreas Valder committed
179
def faq():
180
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
181

Andreas Valder's avatar
Andreas Valder committed
182
183
@app.route('/play')
def play():
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
	if not 'lectureid' in request.args:
		return redirect(url_for('videos'))
	id = request.args.get('lectureid')
	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 lectures:
		flash('Diese Vorlesung existiert nicht!')
		return app.view_functions['videos'](), 404
	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:
		flash('Diese Veranstaltung existiert nicht!')
		return app.view_functions['videos'](), 404
	return render_template('play.html', course=courses[0], lecture=lectures[0], videos=videos)
199
200
201
202
203
204
205

@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'],
206
			'courses', 'WHERE (? OR (visible AND listed)) GROUP BY id ORDER BY _score DESC, semester DESC LIMIT 20', ismod())
207
208
209
	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)',
210
			'WHERE (? OR (coursevisible AND listed AND visible)) GROUP BY id ORDER BY _score DESC, time DESC LIMIT 30', ismod())
211
	return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures)
Andreas Valder's avatar
Andreas Valder committed
212

Andreas Valder's avatar
Andreas Valder committed
213
214
@app.route('/course')
def course():
215
216
217
218
219
220
221
222
223
	if not 'courseid' in request.args:
		return redirect(url_for('videos'))
	id = request.args['courseid']
	courses = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', id, ismod())
	if not courses:
		flash('Diese Veranstaltung existiert nicht!')
		return app.view_functions['videos'](), 404
	lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible)', courses[0]['id'], ismod())
	videos = query('''
Andreas Valder's avatar
Andreas Valder committed
224
			SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description
225
226
227
			FROM videos
			JOIN lectures ON (videos.lecture_id = lectures.id)
			JOIN formats ON (videos.video_format = formats.id)
Andreas Valder's avatar
Andreas Valder committed
228
			JOIN courses ON (lectures.course_id = courses.id)
229
			WHERE lectures.course_id= ? AND (? OR videos.visible)
230
			ORDER BY formats.prio DESC
231
			''', courses[0]['id'], ismod())
232
	return render_template('course.html', course=courses[0], lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
233

234
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
235
def login():
236
237
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
238
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
Julian Rother's avatar
Julian Rother committed
239
	if user and 'users' in groups:
Julian Rother's avatar
Julian Rother committed
240
		session['user'] = ldapget(user)
241
242
	else:
		flash('Login fehlgeschlagen!')
Julian Rother's avatar
Julian Rother committed
243
244
245
246
247
248
249
250
251
252
253
254
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	else:
		return redirect(url_for('index'))

@app.route('/logout')
def logout():
	session.pop('user')
	if 'ref' in request.values:
		return redirect(request.values['ref'])
	else:
		return redirect(url_for('index'))
Julian Rother's avatar
Julian Rother committed
255

256
@app.route('/edit', methods=['GET', 'POST'])
257
@mod_required
Julian Rother's avatar
Julian Rother committed
258
259
260
def edit():
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
Andreas Valder's avatar
Andreas Valder committed
261
				'handle', 'organizer', 'subject', 'semester', 'downloadable',
Julian Rother's avatar
Julian Rother committed
262
263
				'internal', 'responsible']),
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
Andreas Valder's avatar
Andreas Valder committed
264
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist']),
265
		'site_texts': ('site_texts', 'key', ['value']),
Andreas Valder's avatar
Andreas Valder committed
266
		'videos': ('videos_data', 'id', ['visible'])
Julian Rother's avatar
Julian Rother committed
267
268
	}
	query('BEGIN TRANSACTION')
269
	if request.is_json:
Julian Rother's avatar
Julian Rother committed
270
271
272
273
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
274
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
275
276
277
278
279
		assert table in tabs
		assert column in tabs[table][2]
		query('UPDATE %s SET %s = ? WHERE %s = ?'%(tabs[table][0], column,
					tabs[table][1]), val, id)
	query('COMMIT TRANSACTION')
280
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
281

282
283
284
285
286
@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'])
287
288
	ip = request.headers.get('X-Real-IP', '')
	videos = query('''SELECT videos.path, videos.id
289
290
291
292
293
294
			FROM videos
			JOIN lectures ON (videos.lecture_id = lectures.id)
			JOIN courses ON (lectures.course_id = courses.id)
			WHERE videos.path = ?
			AND (? OR (courses.visible AND lectures.visible AND videos.visible))''',
			url, ismod())
295
296
	if videos and (url.startswith('pub') or ismod()):
		query('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.datetime.now(), videos[0]['id'], url)
297
		return "OK", 200
298
	elif url.endswith('jpg'):
299
300
301
		return "OK", 200
	else:
		return "Not allowed", 403
Julian Rother's avatar
Julian Rother committed
302

Andreas Valder's avatar
Andreas Valder committed
303
304

@app.route('/schedule')
305
306
@register_navbar('Drehplan', 'calendar')
@mod_required
Andreas Valder's avatar
Andreas Valder committed
307
308
309
def schedule():
	return render_template('schedule.html')

310
if __name__ == '__main__':
311
	app.run(threaded=True)