server.py 9.87 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
def ismod(*args):
	return ('user' in session)

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

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

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

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

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

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

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

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

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

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

260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
@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
278

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