server.py 9.88 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 sqlite3
6
import os
Julian Rother's avatar
Julian Rother committed
7
import re
8

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

25
26
27
28
29
30
31
32
33
34
35
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()

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

46
def query(operation, *params):
47
	if config['DB_ENGINE'] == 'mysql':
48
		import mysql.connector
49
		if 'db' not in g or not g.db.is_connected():
50
			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
51
52
53
		if not hasattr(request, 'db'):
			request.db = g.db.cursor(dictionary=True)
		request.db.execute(operation.replace('?', '%s'), params)
54
	elif config['DB_ENGINE'] == 'sqlite':
Julian Rother's avatar
Julian Rother committed
55
		if 'db' not in g:
56
			g.db = sqlite3.connect(config['SQLITE_DB'])
57
			g.db.row_factory = dict_factory
Julian Rother's avatar
Julian Rother committed
58
59
60
61
62
63
		if not hasattr(request, 'db'):
			request.db = g.db.cursor()
		request.db.execute(operation, params)
	else:
		return []
	return request.db.fetchall()
64

65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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
82
LDAP_USERRE = re.compile(r'[^a-z0-9]')
Julian Rother's avatar
Julian Rother committed
83
84
85
86
87
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
88
89
90
def ldapauth(user, password):
	user = LDAP_USERRE.sub(r'', user.lower())
	if 'LDAP_HOST' in config:
91
		import ldap3
Julian Rother's avatar
Julian Rother committed
92
93
94
95
96
97
98
99
100
101
102
103
		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
104
105
106
def ldapget(user):
	user = LDAP_USERRE.sub(r'', user.lower())
	if 'LDAP_HOST' in config:
107
		import ldap3
Julian Rother's avatar
Julian Rother committed
108
109
110
111
112
113
		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
114
		return notldap[user][2]
Julian Rother's avatar
Julian Rother committed
115

116
117
118
119
120
121
122
def ismod(*args):
	print('mod test', session, 'user' in session, args)
	return ('user' in session)

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

def mod_required(func):
123
124
	@wraps(func)
	def decorator(*args, **kwargs):
125
		if not ismod():
126
127
128
129
130
131
			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

132
133
@app.route('/')
def index():
134
	return render_template('index.html', latestvideos=query('''
135
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
136
137
138
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
139
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
140
141
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
142
				LIMIT 6
143
			''', ismod()))
144

Andreas Valder's avatar
Andreas Valder committed
145
146
@app.route('/videos')
def videos():
147
148
149
150
	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
151
152
153
	groupedby = request.args.get('groupedby')
	if groupedby not in ['title','semester','organizer']:
		groupedby = 'semester'
154
	return render_template('videos.html', courses=courses, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
155
156
157

@app.route('/faq')
def faq():
158
	return render_template('faq.html')
Andreas Valder's avatar
Andreas Valder committed
159

Andreas Valder's avatar
Andreas Valder committed
160
161
@app.route('/play')
def play():
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
	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)
177
178
179
180
181
182
183

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

Andreas Valder's avatar
Andreas Valder committed
191
192
@app.route('/course')
def course():
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
	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('''
			SELECT *, formats.description AS format_description
			FROM videos
			JOIN lectures ON (videos.lecture_id = lectures.id)
			JOIN formats ON (videos.video_format = formats.id)
			WHERE lectures.course_id= ?
			ORDER BY formats.prio DESC
			''', courses[0]['id'])
	return render_template('course.html', course=courses[0], lectures=lectures, videos=videos)
Andreas Valder's avatar
Andreas Valder committed
210

211
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
212
def login():
213
214
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
215
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
Julian Rother's avatar
Julian Rother committed
216
	if user and 'users' in groups:
Julian Rother's avatar
Julian Rother committed
217
		session['user'] = ldapget(user)
218
219
	else:
		flash('Login fehlgeschlagen!')
Julian Rother's avatar
Julian Rother committed
220
221
222
223
224
225
226
227
228
229
230
231
	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
232

233
@app.route('/edit', methods=['GET', 'POST'])
234
@mod_required
Julian Rother's avatar
Julian Rother committed
235
236
237
238
239
240
241
242
def edit():
	tabs = {
		'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short',
				'handle', 'organizer', 'subject', 'credits', 'semester', 'downloadable',
				'internal', 'responsible']),
		'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment',
				'internal', 'speaker', 'place', 'time', 'duration', 'jumplist',
				'titlefile']),
243
		'site_texts': ('site_texts', 'key', ['value']),
Julian Rother's avatar
Julian Rother committed
244
245
246
247
		'videos': ('videos_data', 'id', ['visible', 'downloadable', 'title',
				'comment', 'internal'])
	}
	query('BEGIN TRANSACTION')
Julian Rother's avatar
Julian Rother committed
248
249
250
251
252
	if request.is_json():
		changes = request.get_json().items()
	else:
		changes = request.args.items()
	for key, val in changes:
253
		table, id, column = key.split('.', 2)
Julian Rother's avatar
Julian Rother committed
254
255
256
257
258
		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')
259
	return "OK", 200
Julian Rother's avatar
Julian Rother committed
260

261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
@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'])
	videos = query('''SELECT videos.path
			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())
	if videos and url.startswith('pub'):
		return "OK", 200
	elif videos and ismod():
		return "OK", 200
	else:
		return "Not allowed", 403
Julian Rother's avatar
Julian Rother committed
279

280
if __name__ == '__main__':
281
	app.run(threaded=True)