from flask import Flask, g, request, url_for, redirect, session, render_template, flash, Response from werkzeug.routing import Rule from functools import wraps from datetime import date, timedelta, datetime, time, MINYEAR import threading import os import sys import hashlib import random app = Flask(__name__) app.jinja_env.trim_blocks = True app.jinja_env.lstrip_blocks = True def timer_func(): with app.test_request_context(): pass # do something timer = threading.Timer(60*60, timer_func) timer.start() timer = threading.Timer(0, timer_func) timer.daemon = True timer.start() config = app.config config.from_pyfile('config.py.example', silent=True) if not sys.argv[0].endswith('run.py'): config['SQLITE_INIT_DATA'] = False config['DEBUG'] = False config.from_pyfile('config.py', silent=True) if config['DEBUG']: app.jinja_env.auto_reload = True if not config.get('SECRET_KEY', None): config['SECRET_KEY'] = os.urandom(24) from db import query, modify, searchquery, ldapauth, ldapget mod_endpoints = [] @app.template_global() def ismod(*args): return ('user' in session) def mod_required(func): mod_endpoints.append(func.__name__) @wraps(func) def decorator(*args, **kwargs): if not ismod(): 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 app.jinja_env.globals['navbar'] = [] def register_navbar(name, icon=None): def wrapper(func): endpoint = func.__name__ app.jinja_env.globals['navbar'].append((endpoint, name, icon, not endpoint in mod_endpoints)) return func return wrapper def render_endpoint(endpoint, flashtext=None, **kargs): if flashtext: flash(flashtext) # request.endpoint is used for navbar highlighting request.url_rule = Rule(request.path, endpoint=endpoint) return app.view_functions[endpoint](**kargs) def handle_errors(endpoint, text, code, *errors, **epargs): def wrapper(func): @wraps(func) def decorator(*args, **kwargs): try: return func(*args, **kwargs) except errors: if endpoint: return render_endpoint(endpoint, text, **epargs), code else: return text, code return decorator return wrapper @app.errorhandler(404) def handle_not_found(e): return render_endpoint('index', 'Diese Seite existiert nicht!'), 404 @app.template_filter(name='semester') def human_semester(s, long=False): if not s or s == 'zeitlos' or len(s) != 6: return 'Zeitlos' year = s[0:4] semester = s[4:6].upper() if not year.isdigit() or semester not in ['SS', 'WS']: print('Invalid semester string "%s"'%s) return '??' if not long: return semester+year[2:] elif semester == 'SS': return 'Sommersemester %s'%year else: return 'Wintersemester %s/%s'%(year, str(int(year)+1)[2:]) @app.template_filter(name='date') def human_date(d): return d.strftime('%d.%m.%Y') @app.template_filter(name='time') def human_date(d): return d.strftime('%H:%M') @app.template_filter() def rfc3339(d): return d.strftime('%Y-%m-%dT%H:%M:%S+02:00') @app.template_global() def get_announcements(minlevel=0): return query('SELECT * FROM announcements WHERE NOT deleted AND (? OR visible) AND level >= ? ORDER BY level DESC', ismod(), minlevel) @app.template_filter() def fixnl(s): # To be remove, as soon as db schema is cleaned-up return str(s).replace('\n', '<br>') @app.route('/') @register_navbar('Home', icon='home') def index(): start = date.today() - timedelta(days=1) end = start + timedelta(days=7) upcomming = query (''' SELECT lectures.*,courses.short, courses.title AS course_title FROM lectures JOIN courses ON (lectures.course_id = courses.id) WHERE (time > ?) AND (time < ?) and lectures.visible and courses.visible and courses.listed ORDER BY time ASC LIMIT 30''',start,end) for i in upcomming: i['date'] = i['time'].date() latestvideos=query(''' SELECT lectures.*, max(videos.time_updated) AS lastvidtime, courses.short, courses.downloadable, courses.title AS coursetitle FROM lectures LEFT JOIN videos ON (videos.lecture_id = lectures.id) LEFT JOIN courses on (courses.id = lectures.course_id) WHERE (? OR (courses.visible AND courses.listed AND lectures.visible AND videos.visible)) GROUP BY videos.lecture_id ORDER BY lastvidtime DESC LIMIT 6 ''',ismod()) featured = query('SELECT * FROM featured WHERE NOT deleted AND (? OR visible)', ismod()) return render_template('index.html', latestvideos=latestvideos, upcomming=upcomming, featured=featured) @app.route('/course') @register_navbar('Videos', icon='film') def courses(): courses = query('SELECT * FROM courses WHERE (? OR (visible AND listed)) ORDER BY title', ismod()) for course in courses: if course['semester'] == '': course['semester'] = 'zeitlos' groupedby = request.args.get('groupedby') if groupedby not in ['title', 'semester', 'organizer']: groupedby = 'semester' return render_template('courses.html', courses=courses, groupedby=groupedby) @app.route('/course/<handle>') @app.route('/course/<int:id>') @handle_errors('courses', 'Diese Veranstaltung existiert nicht!', 404, IndexError) def course(id=None, handle=None): if id: course = query('SELECT * FROM courses WHERE id = ? AND (? OR visible)', id, ismod())[0] else: course = query('SELECT * FROM courses WHERE handle = ? AND (? OR visible)', handle, ismod())[0] course['auth'] = query('SELECT * FROM auth WHERE course_id = ? ORDER BY auth_type', course['id']) auths = query('SELECT auth.* FROM auth JOIN lectures ON (auth.lecture_id = lectures.id) WHERE lectures.course_id = ? ORDER BY auth.auth_type', course['id']) lectures = query('SELECT * FROM lectures WHERE course_id = ? AND (? OR visible) ORDER BY time, duration DESC', course['id'], ismod()) for lecture in lectures: lecture['auth'] = [] for auth in auths: if auth['lecture_id'] == lecture['id']: lecture['auth'].append(auth) videos = query(''' SELECT videos.*, (videos.downloadable AND courses.downloadable) as downloadable, formats.description AS format_description, formats.player_prio, formats.prio FROM videos JOIN lectures ON (videos.lecture_id = lectures.id) JOIN formats ON (videos.video_format = formats.id) JOIN courses ON (lectures.course_id = courses.id) WHERE lectures.course_id= ? AND (? OR videos.visible) ORDER BY lectures.time, formats.prio DESC ''', course['id'], ismod()) return render_template('course.html', course=course, lectures=lectures, videos=videos) @app.route('/faq') @register_navbar('FAQ', icon='question-sign') def faq(): return render_template('faq.html') @app.route('/play/<int:id>') @app.route('/embed/<int:id>', endpoint='embed') @handle_errors('course', 'Diese Vorlesung existiert nicht!', 404, IndexError) def lecture(id): lectures = query('SELECT * FROM lectures WHERE id = ? AND (? OR visible)', id, ismod()) videos = query('SELECT videos.*, formats.description AS format_description, formats.prio, formats.player_prio FROM videos JOIN formats ON (videos.video_format = formats.id) WHERE lecture_id = ? AND (? OR visible)', id, ismod()) 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: return render_endpoint('course', 'Diese Veranstaltung existiert nicht!'), 404 chapters = query('SELECT * FROM chapters WHERE lecture_id = ? AND NOT deleted AND (? OR visible) ORDER BY time ASC', id, ismod()) return render_template('embed.html' if request.endpoint == 'embed' else 'lecture.html', course=courses[0], lecture=lectures[0], videos=videos, chapters=chapters) @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', ismod()) 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', ismod()) return render_template('search.html', searchtext=request.args['q'], courses=courses, lectures=lectures) def check_mod(user, groups): return user and 'users' in groups @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'GET': return render_template('login.html') user, groups = ldapauth(request.form.get('user'), request.form.get('password')) if not check_mod(user, groups): flash('Login fehlgeschlagen!') return render_template('login.html') session['user'] = ldapget(user) dbuser = query('SELECT * FROM users WHERE name = ?', user) if not dbuser: modify('INSERT INTO users (name, realname, fsacc, level, calendar_key, rfc6238) VALUES (?, ?, ?, 1, "", "")', user, session['user']['givenName'], user) dbuser = query('SELECT * FROM users WHERE name = ?', user) session['user']['dbid'] = dbuser[0]['id'] return redirect(request.values.get('ref', url_for('index'))) @app.route('/logout', methods=['GET', 'POST']) def logout(): session.pop('user') return redirect(request.values.get('ref', url_for('index'))) @app.route('/edit', methods=['GET', 'POST']) @mod_required def edit(prefix="", ignore=[]): # All editable tables are expected to have a 'time_updated' field tabs = { 'courses': ('courses_data', 'id', ['visible', 'listed', 'title', 'short', 'handle', 'organizer', 'subject', 'semester', 'downloadable', 'internal', 'responsible','deleted']), 'lectures': ('lectures_data', 'id', ['visible', 'title', 'comment', 'internal', 'speaker', 'place', 'time', 'duration', 'jumplist','deleted']), 'videos': ('videos_data', 'id', ['visible','deleted']), 'chapters': ('chapters', 'id', ['time', 'text', 'visible', 'deleted']), 'announcements': ('announcements', 'id', ['text', 'internal', 'level', 'visible', 'deleted']) } modify('BEGIN') if request.is_json: changes = request.get_json().items() else: changes = request.args.items() for key, val in changes: if key in ignore: continue key = prefix+key table, id, column = key.split('.', 2) assert table in tabs assert column in tabs[table][2] modify('INSERT INTO changelog ("table",id_value,id_key,field,value_new,value_old,"when",who,executed) VALUES (?,?,?,?,?,(SELECT %s FROM %s WHERE %s = ?),?,?,1)'%(column,tabs[table][0],tabs[table][1]),table,id,tabs[table][1],column,val,id,datetime.now(),session['user']['givenName']) modify('UPDATE %s SET %s = ?, time_updated = ? WHERE %s = ?'%(tabs[table][0], column, tabs[table][1]), val, datetime.now(), id) modify('COMMIT') return "OK", 200 @app.route('/newcourse', methods=['GET', 'POST']) @mod_required def new_course(): id = modify(''' INSERT INTO courses_data (visible, title, short, handle, organizer, subject, created_by, time_created, time_updated, semester, settings, description, internal, responsible, feed_url) VALUES (0, "Neue Veranstaltung", "Neu", ?, "", "", ?, ?, ?, "", "", "", "", ?, "") ''', 'new'+str(random.randint(0,1000)), session['user']['dbid'], datetime.now(), datetime.now(), session['user']['givenName']) edit(prefix='courses.'+str(id)+'.', ignore=['ref']) if 'ref' in request.values: return redirect(request.values['ref']) return str(id), 200 @app.route('/newlecture/<courseid>', methods=['GET', 'POST']) @mod_required def new_lecture(courseid): id = modify(''' INSERT INTO lectures_data (course_id, visible, drehplan, title, comment, internal, speaker, place, time, time_created, time_updated, jumplist, titlefile) VALUES (?, 0, "", "Noch kein Titel", "", "", "", "", ?, ?, ?, "", "") ''', courseid, datetime.now(), datetime.now(), datetime.now()) edit(prefix='lectures.'+str(id)+'.', ignore=['ref']) if 'ref' in request.values: return redirect(request.values['ref']) return str(id), 200 @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']) ip = request.headers.get('X-Real-IP', '') if url.endswith('jpg'): return "OK", 200 videos = query('''SELECT videos.path, videos.id, lectures.id AS lecture_id, courses.id AS course_id, auth.* FROM videos JOIN lectures ON (videos.lecture_id = lectures.id) JOIN courses ON (lectures.course_id = courses.id) LEFT JOIN auth ON (videos.id = auth.video_id OR lectures.id = auth.lecture_id OR courses.id = auth.course_id) WHERE videos.path = ? AND (? OR (courses.visible AND lectures.visible AND videos.visible)) ORDER BY auth.video_id DESC, auth.lecture_id DESC, auth.course_id DESC''', url, ismod()) if not videos: return "Not allowed", 403 allowed = False types = [] auth = request.authorization for video in videos: if videos[0] and ((videos[0]['video_id'] and not video['video_id']) \ or (videos[0]['lecture_id'] and not video['lecture_id'])): break types.append(video['auth_type']) if video['auth_type'] == 'public': allowed = True break elif video['auth_type'] == 'password': if auth and video['auth_user'] == auth.username and video['auth_passwd'] == auth.password: allowed = True break if not types[0] or allowed or ismod() or \ (auth and check_mod(*ldapauth(auth.username, auth.password))): return 'OK', 200 modify('INSERT INTO log VALUES (?, "", ?, "video", ?, ?)', ip, datetime.now(), videos[0]['id'], url) elif 'password' in types: return Response("Login required", 401, {'WWW-Authenticate': 'Basic realm="Login Required"'}) return "Not allowed", 403 @app.route('/stats') @register_navbar('Statistiken', 'stats') @mod_required def stats(): return render_template('stats.html') @app.route('/changelog') @register_navbar('Changelog', 'book') @mod_required def changelog(): changelog = query('SELECT *, ( "table" || "." || id_value || "." ||field) as path FROM changelog LEFT JOIN users ON (changelog.who = users.id) ORDER BY `when` DESC LIMIT 50') return render_template('changelog.html', changelog=changelog) @app.route('/files/<filename>') def files(filename): return redirect(config['VIDEOPREFIX']+'/'+filename) @app.route('/newchapter/<int:lectureid>', methods=['POST', 'GET']) def suggest_chapter(lectureid): time = request.values['time'] text = request.values['text'] assert(time and text) time = int(time) submitter = None if not ismod(): submitter = request.environ['REMOTE_ADDR'] id = modify('INSERT INTO chapters (lecture_id, time, text, time_created, time_updated, created_by, submitted_by) VALUES (?, ?, ?, ?, ?, ?, ?)', lectureid, time, text, datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid'], submitter) if 'ref' in request.values: return redirect(request.values['ref']) return 'OK', 200 @app.route('/newpsa', methods=['POST', 'GET']) @mod_required def new_announcement(): id = modify('INSERT INTO announcements (text, internal, time_created, time_updated, created_by) VALUES ("Neue Ankündigung", "", ?, ?, ?)', datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid']) if 'ref' in request.values: return redirect(request.values['ref']) return id, 200 @app.route('/newfeatured', methods=['POST', 'GET']) @mod_required def new_featured(): id = modify('INSERT INTO featured (time_created, time_updated, created_by) VALUES (?, ?, ?)', datetime.now(), datetime.now(), session.get('user', {'dbid':None})['dbid']) if 'ref' in request.values: return redirect(request.values['ref']) return id, 200 @app.route('/sitemap.xml') def sitemap(): pages=[] # static pages for rule in app.url_map.iter_rules(): if 'GET' in rule.methods and len(rule.arguments)==0: if rule.endpoint not in mod_endpoints: pages.append([rule.rule]) for i in query('select * from courses where visible and listed'): pages.append([url_for('course',handle=i['handle'])]) for j in query('select * from lectures where (course_id = ? and visible)',i['id']): pages.append([url_for('lecture',id=j['id'])]) return Response(render_template('sitemap.xml', pages=pages), 200, {'Content-Type': 'application/atom+xml'} ) @app.route('/sortlog') @register_navbar('Sortierlog', 'sort-by-attributes-alt') @mod_required def sortlog(): return render_template('sortlog.html') import feeds import importer import schedule