from server import * # field types: # boolean # shortstring # text # datetime # duration # videotime editable_tables = { 'courses': { 'table': 'courses_data', 'idcolumn': 'id', 'editable_fields': { 'visible': {'type': 'boolean', 'description': 'Wenn ein Kurs nicht sichtbar ist sind alle Videos davon nicht abrufbar.'}, 'listed': {'type': 'boolean', 'description': 'Soll die Veranstaltung auf der Hauptseite gelistet werden?'}, 'title': {'type': 'shortstring'}, 'short': {'type': 'shortstring', 'description': 'Abkürzung für die Veranstaltung, z.B. für den Drehplan'}, 'handle': {'type': 'shortstring'}, 'organizer': {'type': 'shortstring'}, 'subject': {'type': 'shortstring'}, 'semester': {'type': 'shortstring'}, 'downloadable': {'type': 'boolean', 'description': 'Hiermit kann der Download-Button disabled werden'}, 'internal': {'type': 'text'}, 'responsible': {'type': 'shortstring'}, 'deleted': {'type': 'boolean'}, 'description': {'type': 'text'}, 'external': {'type': 'boolean', 'description': 'Soll die Veranstaltung nicht im Drehplan angezeigt werden?'}, 'coursechapters': {'type': 'boolean', 'description': 'Sollen auf der Kursseite die Kapitelmarker der Videos angezeigt werden?'}, 'autopublish': {'type': 'boolean', 'description': 'Sollen encodete Videos automatisch verschoben werden?'}, 'autovisible': {'type': 'boolean', 'description': 'Sollen neue Videos automatisch sichtbar sein?'}}, 'creationtime_fields': ['created_by', 'time_created', 'time_updated'] }, 'lectures': { 'table': 'lectures_data', 'idcolumn': 'id', 'editable_fields': { 'visible': {'type': 'boolean', 'description': 'Wenn eine lecture nicht sichtbar ist sind alle Videos davon nicht abrufbar'}, 'title': {'type': 'shortstring'}, 'comment': {'type': 'text'}, 'internal': {'type': 'text'}, 'speaker': {'type': 'shortstring'}, 'place': {'type': 'shortstring'}, 'time': {'type': 'datetime'}, 'duration': {'type': 'duration'}, 'jumplist': {'type': ''}, 'deleted': {'type': 'boolean'}, 'live': {'type': 'boolean', 'description': 'Ist ein Livestream geplant? Muss gesetzt sein damit der RTMP Stream zugeordnet wird.'}, 'norecording': {'type': 'boolean', 'description:': 'Führt dazu, dass der Termin ausgegraut wird.'}}, 'creationtime_fields': ['course_id', 'time_created', 'time_updated'] }, 'videos': { 'table': 'videos_data', 'idcolumn': 'id', 'editable_fields': { 'visible': {'type': 'boolean', 'description': 'Ein nicht sichtbares Video kann nicht abgerufen werden.'}, 'deleted': {'type': 'boolean'}}, 'creationtime_fields': ['created_by', 'time_created', 'time_updated'] }, 'chapters': { 'table': 'chapters', 'idcolumn': 'id', 'editable_fields': { 'time': {'type': 'videotime'}, 'text': {'type': 'shortstring'}, 'visible': {'type': 'boolean'}, 'deleted': {'type': 'boolean'}}, 'creationtime_fields': ['created_by', 'time_created', 'time_updated'] }, 'announcements': { 'table': 'announcements', 'idcolumn': 'id', 'editable_fields': { 'text': {'type': 'text'}, 'level': {'type': 'integer'}, 'visible': {'type': 'boolean'}, 'deleted': {'type': 'boolean'}, 'time_publish': {'type': 'datetime'}, 'time_expire': {'type': 'datetime'}}, 'creationtime_fields': ['created_by', 'time_created', 'time_updated'] }, 'featured': { 'table': 'featured', 'idcolumn': 'id', 'editable_fields': { 'title': {'type': 'shortstring'}, 'text': {'type': 'text'}, 'internal': {'type': 'text'}, 'visible': {'type': 'boolean'}, 'deleted': {'type': 'boolean'}, 'param': {'type': 'shortstring'}, 'param2': {'type': 'shortstring'}, 'order': {'type': 'integer' }}, 'creationtime_fields': ['created_by', 'time_created', 'time_updated', 'type'] }, 'perm': { 'table': 'perm', 'idcolumn': 'id', 'editable_fields': { 'type': {'type': 'shortstring'}, 'param1': {'type': 'shortstring'}, 'param2': {'type': 'shortstring'}, 'deleted': {'type': 'boolean'}}, 'creationtime_fields': ['course_id', 'lecture_id', 'video_id', 'created_by', 'time_created', 'time_updated'] }, 'sorterrorlog': { 'table': 'sorterrorlog_data', 'idcolumn': 'id', 'editable_fields': { 'deleted': {'type': 'boolean'}}, 'creationtime_fields': ['time_created', 'time_updated'] }, 'users': { 'table': 'users', 'idcolumn': 'id', 'editable_fields': { 'mail_notifications': {'type': 'boolean'}, 'notify_chapter_submitted': {'type': 'boolean'}, 'notify_new_video': {'type': 'boolean'}, 'notify_edit': {'type': 'boolean'} }, 'creationtime_fields': [] }, 'live_sources': { 'table': 'live_sources', 'idcolumn': 'id', 'editable_fields': { 'name': {'type': 'shortstring'}, 'description': {'type': 'text'}, }, 'creationtime_fields': ['created_by', 'time_created', 'time_updated'] } } #parses the path to a dict, containing the table, id, field and field type @app.template_filter(name='parseeditpath') def parseeditpath(path): table, id, column = path.split('.', 2) assert table in editable_tables assert column in editable_tables[table]['editable_fields'] type = editable_tables[table]['editable_fields'][column]['type'] return {'table': table, 'id': id, 'column': column, 'type': type, 'tableinfo': editable_tables[table]} @app.template_filter(name='getfielddescription') def getfielddescription(path): p = parseeditpath(path) desc = p['tableinfo']['editable_fields'][p['column']].get('description', '') if desc != '': desc = '<br>'+desc return desc @app.template_filter(name='getfieldchangelog') def getfieldchangelog(path): p = parseeditpath(path) changelog = query('SELECT * FROM changelog LEFT JOIN users ON (changelog.who = users.id) WHERE `table` = ? AND `id_value` = ? and `field` = ? ORDER BY `when` DESC LIMIT 5', p['table'], p['id'], p['column']) for entry in changelog: entry['path'] = '.'.join([entry['table'], entry['id_value'], entry['field']]) return changelog @app.route('/internal/edit', methods=['GET', 'POST']) @mod_required @csrf_protect def edit(prefix='', ignore=[]): # All editable tables are expected to have a 'time_updated' field ignore.append('ref') ignore.append('prefix') ignore.append('_csrf_token') if not prefix and 'prefix' in request.args: prefix = request.args['prefix'] changes = request.values.items() if (request.method == 'POST') and (request.get_json()): changes = request.get_json().items() for key, val in changes: if key in ignore: continue key = prefix+key path = parseeditpath(key) 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)'%(path['column'], path['tableinfo']['table'], path['tableinfo']['idcolumn']), path['table'], path['id'], path['tableinfo']['idcolumn'], path['column'], val, path['id'], datetime.now(), session['user']['dbid']) modify('UPDATE %s SET `%s` = ?, time_updated = ? WHERE `%s` = ?'%(path['tableinfo']['table'], path['column'], path['tableinfo']['idcolumn']), val, datetime.now(),path['id']) for func in edit_handlers.get(path['table'], {}).get(None, []): func(path['table'], path['column'], val, path['id'], session['user']['dbid']) for func in edit_handlers.get(path['table'], {}).get(path['column'], []): func(path['table'], path['column'], val, path['id'], session['user']['dbid']) if 'ref' in request.values: return redirect(request.values['ref']) return "OK", 200 @app.route('/internal/new/<table>', methods=['GET', 'POST']) @mod_required @csrf_protect def create(table): assert table in editable_tables defaults = {'created_by': session['user']['dbid'], 'time_created': datetime.now(), 'time_updated': datetime.now()} columns = [] values = [] for column, val in defaults.items(): if column in editable_tables[table]['creationtime_fields']: columns.append(column) values.append(val) args = request.values.items() if (request.method == 'POST') and (request.get_json()): args = request.get_json().items() for column, val in args: if (column == 'ref') or (column == '_csrf_token'): continue assert column in list(editable_tables[table]['editable_fields'].keys())+editable_tables[table]['creationtime_fields'] assert column not in defaults columns.append('`'+column+'`') values.append(val) id = modify('INSERT INTO %s (%s) VALUES (%s)'%(editable_tables[table]['table'], ','.join(columns), ','.join(['?']*len(values))), *values) if table == 'courses': set_responsible(id, session['user']['dbid'], 1) if 'ref' in request.values: return redirect(request.values['ref']) return str(id), 200 @app.route('/internal/changelog') @register_navbar('Changelog', icon='book', group='weitere') @mod_required def changelog(): if 'page' in request.args: page = max(0, int(request.args['page'])) else: page = 0 if 'pagesize' in request.args: pagesize = min(500, int(request.args['pagesize'])) else: pagesize = 50 changelog = query('SELECT * FROM changelog LEFT JOIN users ON (changelog.who = users.id) ORDER BY `when` DESC LIMIT ? OFFSET ?', pagesize, page*pagesize) pagecount = math.ceil(query('SELECT count(id) as count FROM changelog')[0]['count']/pagesize) for entry in changelog: entry['path'] = '.'.join([entry['table'], entry['id_value'], entry['field']]) return render_template('changelog.html', changelog=changelog, page=page, pagesize=pagesize, pagecount=pagecount) @app.route('/internal/set/responsible/<int:course_id>/<int:user_id>', defaults={'value': True}, methods=['GET', 'POST']) @app.route('/internal/unset/responsible/<int:course_id>/<int:user_id>', defaults={'value': False}, methods=['GET', 'POST']) @mod_required @csrf_protect def set_responsible(course_id, user_id, value): if value: modify('REPLACE INTO responsible (course_id, user_id) values (?, ?)', course_id, user_id); else: modify('DELETE FROM responsible WHERE course_id = ? AND user_id = ?', course_id, user_id); return "OK", 200 edit_handlers = {} def edit_handler(*tables, field=None): def wrapper(func): for table in tables: if table not in edit_handlers: edit_handlers[table] = {} if field not in edit_handlers[table]: edit_handlers[table][field] = [] edit_handlers[table][field].append(func) return func return wrapper @edit_handler('courses') @edit_handler('lectures') def notify_edit(table, column, value, id, user_id): lecture = None if table == 'lectures': lecture = query('SELECT * FROM lectures_data WHERE id = ?', id)[0] course_id = lecture['course_id'] elif table == 'courses': course_id = id course = query('SELECT * FROM courses_data WHERE id = ?', course_id)[0] user = query('SELECT * FROM users WHERE id = ?', user_id)[0] notify_mods('edit', course_id, exclude_uids=[user_id], course=course, lecture=lecture, table=table, column=column, value=value, user=user)