server.py 8.05 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
18
19
20
21
config['DEBUG'] = False
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
config.from_pyfile('config.py', silent=True)
22

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

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

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

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

114
115
116
117
118
119
120
121
122
123
def login_required(func):
	@wraps(func)
	def decorator(*args, **kwargs):
		if not 'user' in session:
			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

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

Andreas Valder's avatar
Andreas Valder committed
137
138
@app.route('/videos')
def videos():
139
140
141
142
	c=query("SELECT * FROM courses")
	for i in c:
		if i['semester'] == '':
			i['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
143
144
145
146
	groupedby = request.args.get('groupedby')
	if groupedby not in ['title','semester','organizer']:
		groupedby = 'semester'
	return render_template('videos.html', courses=c, groupedby=groupedby)
Andreas Valder's avatar
Andreas Valder committed
147
148
149

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

Andreas Valder's avatar
Andreas Valder committed
152
153
@app.route('/play')
def play():
Julian Rother's avatar
Julian Rother committed
154
155
156
157
158
159
	if 'lectureid' in request.args:
		id = request.args['lectureid']
		return render_template('play.html',
				lecture=query('SELECT * FROM lectures WHERE id = ?', id)[0],
				videos=query('SELECT * FROM videos WHERE lecture_id = ?', id))
	else:
160
161
162
163
164
165
166
167
168
169
170
171
172
173
		return redirect(url_for('index'))

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

Andreas Valder's avatar
Andreas Valder committed
175
176
177
178
@app.route('/course')
def course():
	if 'courseid' in request.args:
		id = request.args['courseid']
Andreas Valder's avatar
Andreas Valder committed
179
		course = query('SELECT * FROM courses WHERE handle = ?', id)[0]
Andreas Valder's avatar
Andreas Valder committed
180
		return render_template('course.html',
Andreas Valder's avatar
Andreas Valder committed
181
				course=course,
Andreas Valder's avatar
Andreas Valder committed
182
183
				lectures=query('SELECT * FROM lectures  WHERE course_id = ?', course['id']),
				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', course['id']))
Andreas Valder's avatar
Andreas Valder committed
184
185
186
	else:
		return redirect(url_for('index'))

187
@app.route('/login', methods=['GET', 'POST'])
Julian Rother's avatar
Julian Rother committed
188
def login():
189
190
	if request.method == 'GET':
		return render_template('login.html')
Julian Rother's avatar
Julian Rother committed
191
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
Julian Rother's avatar
Julian Rother committed
192
	if user and 'users' in groups:
Julian Rother's avatar
Julian Rother committed
193
		session['user'] = ldapget(user)
194
195
	else:
		flash('Login fehlgeschlagen!')
Julian Rother's avatar
Julian Rother committed
196
197
198
199
200
201
202
203
204
205
206
207
	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
208

Julian Rother's avatar
Julian Rother committed
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
@app.route('/edit')
@login_required
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']),
		'site_texts': ('site_texts', 'key' ['value']),
		'videos': ('videos_data', 'id', ['visible', 'downloadable', 'title',
				'comment', 'internal'])
	}
	query('BEGIN TRANSACTION')
	for key, val in request.get_json():
		table, column, id = key.split('.', 2)
		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')

232
233
if __name__ == '__main__':
	app.run()