edit.py

import math

from server import *

# field types:
# 	boolean
# 	shortstring
# 	text
# 	datetime
# 	duration
# 	videotime

editable_tables = { #pylint: disable=invalid-name
	'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?'},
			'login_info': {'type': 'text', 'description': 'Zusätliche Informationen, die dem Nutzer angezeigt werden, wenn er sich anmelden muss.'}
		},
		'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.'},
			'stream_settings':	{'type': 'text'}
			},
		'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'},
			'deleted':  {'type': 'boolean'}
		},
		'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(inputpath):
	path = parseeditpath(inputpath)
	desc = path['tableinfo']['editable_fields'][path['column']].get('description', '')
	if desc != '':
		desc = '<br>'+desc
	return desc

@app.template_filter(name='getfieldchangelog')
def getfieldchangelog(inputpath):
	path = parseeditpath(inputpath)
	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', path['table'], path['id'], path['column'])
	for entry in changelog:
		entry['id_value'] = str(entry['id_value'])
		entry['value_new'] = str(entry['value_new'])
		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=None):
	if not ignore:
		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 = ?),?,?,true)'%(
					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 in ['ref', '_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)
	assert editable_tables[table]['idcolumn'] == 'id'
	id = modify('INSERT INTO %s (%s) VALUES (%s)'%(editable_tables[table]['table'],
				','.join(columns), ','.join(['?']*len(values))), *values,
				get_id=True)
	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():
	page = max(0, int(request.args.get('page', 0)))
	pagesize = min(500, int(request.args.get('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:
		if not query('SELECT id FROM responsible WHERE course_id = ? AND user_id = ?', course_id, user_id):
			modify('INSERT 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 = {} #pylint: disable=invalid-name
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)