server.py 6.81 KB
Newer Older
1
#!/bin/python
Julian Rother's avatar
Julian Rother committed
2
from flask import Flask, render_template, g, request, url_for, redirect, session
3
import sqlite3
4
import os
Julian Rother's avatar
Julian Rother committed
5
import re
6

7
app = Flask(__name__)
8
config = app.config
9
10
config['DB_SCHEMA'] = 'db_schema.sql'
config['DB_DATA'] = 'db_example.sql'
11
12
13
config['DB_ENGINE'] = 'sqlite'
config['SQLITE_DB'] = 'db.sqlite'
config['SQLITE_INIT_SCHEMA'] = True
14
config['SQLITE_INIT_DATA'] = False
15
16
17
18
19
config['DEBUG'] = False
if __name__ == '__main__':
	config['SQLITE_INIT_DATA'] = True
	config['DEBUG'] = True
config.from_pyfile('config.py', silent=True)
20

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

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

42
def query(operation, *params):
43
	if config['DB_ENGINE'] == 'mysql':
44
		import mysql.connector
45
		if 'db' not in g or not g.db.is_connected():
46
			g.db = mysql.connector.connect(user=config['MYSQL_USER'], password=config['MYSQL_PASSWD'], host=config['MYSQL_HOST'], database=config['MYSQL_DB'])
47
48
49
		cur = g.db.cursor(dictionary=True)
		cur.execute(operation.replace('?', '%s'), params)
		return cur.fetchall()
50
	elif config['DB_ENGINE'] == 'sqlite':
Julian Rother's avatar
Julian Rother committed
51
		if 'db' not in g:
52
			g.db = sqlite3.connect(config['SQLITE_DB'])
53
54
55
56
			g.db.row_factory = dict_factory
		cur = g.db.cursor()
		cur.execute(operation, params)
		return cur.fetchall()
57

58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
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
75
76
77
78
79
LDAP_USERRE = re.compile(r'[^a-z0-9]')
def ldapauth(user, password):
	notldap = {'videoag':('videoag', ['users','videoag']), 'gustav':('passwort', ['users'])}
	user = LDAP_USERRE.sub(r'', user.lower())
	if 'LDAP_HOST' in config:
80
		import ldap3
Julian Rother's avatar
Julian Rother committed
81
82
83
84
85
86
87
88
89
90
91
92
		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
93
94
95
96
97
def ldapget(user):
	notldap = {'videoag': {'uid': 'videoag', 'givenName': 'Video', 'sn': 'Geier'},
		'gustav': {'uid': 'gustav', 'givenName': 'Gustav', 'sn': 'Geier'}}
	user = LDAP_USERRE.sub(r'', user.lower())
	if 'LDAP_HOST' in config:
98
		import ldap3
Julian Rother's avatar
Julian Rother committed
99
100
101
102
103
104
105
106
		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:
		return notldap[user]

107
108
@app.route('/')
def index():
109
	return render_template('index.html', latestvideos=query('''
110
				SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle
111
112
113
				FROM lectures
				LEFT JOIN videos ON (videos.lecture_id = lectures.id)
				LEFT JOIN courses on (courses.id = lectures.course_id)
114
				WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible))
115
116
				GROUP BY videos.lecture_id
				ORDER BY lastvidtime DESC
Andreas Valder's avatar
.    
Andreas Valder committed
117
				LIMIT 6
118
			''', False))
119

Andreas Valder's avatar
Andreas Valder committed
120
121
@app.route('/videos')
def videos():
122
123
124
125
	c=query("SELECT * FROM courses")
	for i in c:
		if i['semester'] == '':
			i['semester'] = 'zeitlos'
Andreas Valder's avatar
Andreas Valder committed
126
127
128
129
	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
130
131
132

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

Andreas Valder's avatar
Andreas Valder committed
135
136
@app.route('/play')
def play():
Julian Rother's avatar
Julian Rother committed
137
138
139
140
141
142
	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:
143
144
145
146
147
148
149
150
151
152
153
154
155
156
		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
157

Andreas Valder's avatar
Andreas Valder committed
158
159
160
161
@app.route('/course')
def course():
	if 'courseid' in request.args:
		id = request.args['courseid']
Andreas Valder's avatar
Andreas Valder committed
162
		course = query('SELECT * FROM courses WHERE handle = ?', id)[0]
Andreas Valder's avatar
Andreas Valder committed
163
		return render_template('course.html',
Andreas Valder's avatar
Andreas Valder committed
164
				course=course,
Andreas Valder's avatar
Andreas Valder committed
165
166
				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
167
168
169
	else:
		return redirect(url_for('index'))

Julian Rother's avatar
Julian Rother committed
170
171
172
@app.route('/login', methods=['POST'])
def login():
	user, groups = ldapauth(request.form.get('user'), request.form.get('password'))
Julian Rother's avatar
Julian Rother committed
173
	if user and 'users' in groups:
Julian Rother's avatar
Julian Rother committed
174
		session['user'] = ldapget(user)
Julian Rother's avatar
Julian Rother committed
175
176
177
178
179
180
181
182
183
184
185
186
	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
187

188
189
if __name__ == '__main__':
	app.run()