def test_get_close_db(app):
with app.app_context():
db = get_db()
assert db is get_db()
with pytest.raises(ProgrammingError) as e:
db.execute('SELECT 1')
assert 'closed' in str(e.value)
def get_new_uri(self, class_uri, subgraph_id=None, base=None):
# construct a unique uri
if type(class_uri) == str:
class_uri = URIRef(class_uri)
if base is None:
base = self.get_base()
db = get_db()
used_uris = {
str(parse_n3_term(row['subject']))
for row in db.execute(
'SELECT DISTINCT subject FROM knowledge').fetchall()
}
used_uris.update({
str(parse_n3_term(row['subject']))
for row in db.execute(
'SELECT DISTINCT subject FROM ontology').fetchall()
})
used_uris.update({
str(parse_n3_term(row['uri']))
for row in db.execute('SELECT DISTINCT uri FROM user').fetchall()
})
uri = str(base) + get_uri_suffix(class_uri) + '_'
if subgraph_id:
uri += str(subgraph_id) + '_'
return URIRef(
next(uri + str(i) for i in count(1)
if uri + str(i) not in used_uris))
def overview(subgraph_id):
user_id = g.user['id']
db = get_db()
if subgraph_id is None:
return jsonify(error='{} id cannot be empty.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
subgraph = db.execute('SELECT * FROM subgraph WHERE id = ?',
(subgraph_id, )).fetchone()
if subgraph is None:
message = '{} with id {} does not exist.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term'],
subgraph_id)
return redirect(url_for('tool.index', message=quote(message)))
if not subgraph_access(user_id, subgraph_id) and not subgraph['finished']:
message = 'You have no access to {} with id {}.'.format(
current_app.config['FRONTEND_CONFIG']['subgraph_term'],
subgraph_id)
return redirect(url_for('tool.index', message=quote(message)))
return render_template(
'tool/overview.html',
subgraph=subgraph,
rdf_graph=get_subgraph_knowledge(subgraph_id).get_graph(clean=True,
ontology=True))
def edit_user():
user_id = request.json.get('user_id')
user_name = request.json.get('name')
user_password = request.json.get('password')
user_is_admin = request.json.get('admin') is not None
if user_id is None:
return jsonify(error='User id cannot be empty.')
try:
user_id = int(user_id)
except ValueError:
return jsonify(error='User id has to be an integer.')
if user_id == g.user['id'] and not user_is_admin:
return jsonify(
error='You cannot remove admin rights from your own account.')
if not user_name or user_name.isspace():
return jsonify(error='User name cannot be empty.')
db = get_db()
db.execute('UPDATE user SET username = ?, admin = ? WHERE id = ?',
(user_name, user_is_admin, user_id))
if user_password is not None and not user_password.isspace():
db.execute('UPDATE user SET password = ? WHERE id = ?',
(generate_password_hash(user_password), user_id))
db.commit()
return jsonify(user_name=user_name, user_is_admin=user_is_admin)
def __init__(self, subgraph_id):
super().__init__(Graph())
self.id = subgraph_id
# since rdf triples are not ordered but the values of the property fields in the tool are we need to store this
# additional information outside the rdf graph object:
self.properties = defaultdict(
dict
) # includes deleted! (There has to be a better name for this?)
self.root = None
db = get_db()
for row in db.execute('SELECT * FROM namespace').fetchall():
self.graph.namespace_manager.bind(row['prefix'],
parse_n3_term(row['uri']))
for row in db.execute(
'SELECT subject, predicate, object, property_index, deleted FROM knowledge WHERE subgraph_id = ?',
(subgraph_id, )).fetchall():
subject, predicate, object_ = row_to_rdf(row)
index = row['property_index']
deleted = row['deleted']
if deleted is None and str(object_) != '':
self.graph.add((subject, predicate, object_))
if index is not None:
self.properties[(subject, predicate)][index] = object_
def delete_individual_recursive(self, uri):
if type(uri) == str:
uri = URIRef(uri)
db = get_db()
db_cursor = db.cursor()
stack = [uri]
deleted = []
while stack:
u = stack.pop()
deleted.append(str(u))
stack += self.get_individual_children(u)
# remove links from parents
self.graph.remove((None, None, u))
db_cursor.execute(
'UPDATE knowledge SET deleted = ? WHERE subgraph_id = ? AND object = ? AND deleted IS NULL',
(uri.n3(), self.id, u.n3()))
# remove links to children
self.graph.remove((u, None, None))
db_cursor.execute(
'UPDATE knowledge SET deleted = ? WHERE subgraph_id = ? AND subject = ? AND deleted IS NULL',
(uri.n3(), self.id, u.n3()))
db.commit()
self.root = None # forces a rebuild of the root entity
return deleted
def load_rdf_data(self, data, rdf_format='turtle'):
super().load_rdf_data(data, rdf_format)
self.properties = defaultdict(dict)
db = get_db()
db.execute('DELETE FROM knowledge WHERE subgraph_id = ?', (self.id, ))
for subject, predicate, object_ in self.graph[::]:
index = None
if (subject, predicate) in self.properties:
index = max(self.properties[(subject, predicate)].keys()) + 1
self.properties[(subject, predicate)][index] = object_
elif predicate in get_ontology().graph[:RDF.type:OWL.ObjectProperty] \
or predicate in get_ontology().graph[:RDF.type:OWL.DatatypeProperty]:
index = 1
self.properties[(subject, predicate)][index] = object_
db.execute(
'INSERT INTO knowledge (subgraph_id, subject, predicate, object, property_index)'
' VALUES (?, ?, ?, ?, ?)',
(self.id, subject.n3(), predicate.n3(), object_.n3(), index))
db.commit()
self.root = None # forces a rebuild of the root entity
self.get_root(
) # to test immediately whether everything is building correctly
def test_set_finished(client, auth):
db = get_db()
assert not db.execute('SELECT * FROM subgraph WHERE id = 1').fetchone()['finished']
auth.login()
response = client.get('/_set_finished?subgraph_id=1&finished=true')
assert 'error' not in response.get_json()
assert db.execute('SELECT * FROM subgraph WHERE id = 1').fetchone()['finished']
def test_undo_delete_subgraph(client, auth):
db = get_db()
assert db.execute('SELECT * FROM subgraph WHERE id = 4').fetchone()['deleted']
auth.login()
response = client.get('/_undo_delete_subgraph?subgraph_id=4')
assert 'error' not in response.get_json()
assert not db.execute('SELECT * FROM subgraph WHERE id = 4').fetchone()['deleted']
def get_overview():
subgraph_id = request.json.get('subgraph_id')
if subgraph_id is None:
return jsonify(error='Subgraph id cannot be empty.')
try:
subgraph_id = int(subgraph_id)
except ValueError:
return jsonify(error='{} id has to be an integer.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
db = get_db()
subgraph = db.execute('SELECT * FROM subgraph WHERE id = ?',
(subgraph_id, )).fetchone()
if subgraph is None:
return jsonify(error='{} with id {} does not exist.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term'],
subgraph_id))
if not subgraph['finished']:
return jsonify(error='You have no access to {} with id {}.'.format(
current_app.config['FRONTEND_CONFIG']['subgraph_term'],
subgraph_id))
render_overview_table = get_template_attribute('tool/overview_table.html',
'overview')
overview_table = render_overview_table(
get_subgraph_knowledge(subgraph_id).get_graph(clean=True,
ontology=True))
return jsonify(overview_table=overview_table)
def read_only_view(subgraph_id):
"""Edit view.
Displays knowledge about a subgraph and allows users to edit it."""
db = get_db()
subgraph = db.execute('SELECT * FROM subgraph WHERE id = ?',
(subgraph_id, )).fetchone()
if subgraph is None:
message = '{} with id {} does not exist.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term'],
subgraph_id)
# for some reason I need to unquote two times (once via url_for, once via quote) on the server, else
# I'd get a bad request
return redirect(url_for('tool.index', message=quote(message)))
if not subgraph['finished']:
message = 'You have no access to {} with id {}.'.format(
current_app.config['FRONTEND_CONFIG']['subgraph_term'],
subgraph_id)
return redirect(url_for('tool.index', message=quote(message)))
root = get_subgraph_knowledge(subgraph_id).get_root()
return render_template('tool/edit.html',
subgraph=subgraph,
root=root,
read_only=True)
def test_edit_subgraph_name(client, auth):
db = get_db()
assert db.execute('SELECT * FROM subgraph WHERE id = 1').fetchone()['name'] == 'Clinical trial 1'
auth.login()
response = client.get('/_edit_subgraph_name?subgraph_id=1&name=test')
assert 'error' not in response.get_json()
assert response.get_json()['name'] == 'test'
assert db.execute('SELECT * FROM subgraph WHERE id = 1').fetchone()['name'] == 'test'
def test_delete_subgraph(client, auth):
db = get_db()
assert not db.execute('SELECT * FROM subgraph WHERE id = 1').fetchone()['deleted']
auth.login()
response = client.get('/_delete_subgraph?subgraph_id=1')
assert 'error' not in response.get_json()
assert response.get_json()['name'] == 'Clinical trial 1'
assert db.execute('SELECT * FROM subgraph WHERE id = 1').fetchone()['deleted']
def test_add_subgraph(client, auth):
db = get_db()
assert not db.execute('SELECT EXISTS (SELECT 1 FROM subgraph WHERE name = "new")').fetchone()[0]
auth.login()
response = client.get('/_add_subgraph?name=new')
assert 'error' not in response.get_json()
new = db.execute('SELECT * FROM subgraph WHERE name = "new"').fetchone()
assert new
assert response.get_json()['redirect'] == url_for('tool.index', subgraph_id=new['id'])
def __init__(self):
super().__init__(Graph())
db = get_db()
for row in db.execute('SELECT * FROM ontology').fetchall():
self.graph.add(row_to_rdf(row))
for row in db.execute('SELECT * FROM namespace').fetchall():
self.graph.namespace_manager.bind(row['prefix'],
parse_n3_term(row['uri']))
def search_view():
filter_object = get_filter()
db = get_db()
rows = db.execute(
'SELECT id, name FROM access JOIN subgraph ON subgraph_id = id'
' WHERE deleted = 0 AND finished = 1').fetchall()
return render_template('tool/search.html',
filter=filter_object,
subgraphs=rows)
def user_administration(user_id):
db = get_db()
user = db.execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
subgraph_list = db.execute(
'SELECT id, name, finished'
' FROM access JOIN subgraph ON subgraph_id = id'
' WHERE user_id = ? and deleted = 0'
' ORDER BY subgraph_id ASC', (user_id, )).fetchall()
return render_template('tool/admin_user.html',
user=user,
subgraph_list=subgraph_list)
def undo_delete_user():
user_id = request.json.get('user_id')
if user_id is None:
return jsonify(error='User id cannot be empty.')
try:
user_id = int(user_id)
except ValueError:
return jsonify(error='User id has to be an integer.')
db = get_db()
db.execute('UPDATE user SET deleted = 0 WHERE id = ?', (user_id, ))
db.commit()
return jsonify()
def change_label(self, entity_uri, label):
entity_uri = URIRef(entity_uri)
label = Literal(label)
self.graph.remove((entity_uri, RDFS.label, None))
self.graph.add((entity_uri, RDFS.label, label))
db = get_db()
db.execute(
'UPDATE knowledge SET object = ? '
' WHERE subgraph_id = ? AND subject = ? AND predicate = ?',
(label.n3(), self.id, entity_uri.n3(), RDFS.label.n3()))
db.commit()
self.root = None # forces a rebuild of the root entity
def delete_subgraph():
"""Marks a subgraph as deleted.
The subgraph is not actually deleted from the db and can be made accessible again by setting the
deleted flag back to 0.
Request args:
subgraph_id (int): Id of the Subgraph to delete.
Context:
g.user (sqlite3.Row): Logged in user. Must have access to the subgraph.
Returns JSON:
name (str): The new name of the deleted subgraph.
error (str): An error message if something went wrong.
"""
user_id = g.user['id']
subgraph_id = request.json.get('subgraph_id')
if subgraph_id is None:
return jsonify(error='{} id cannot be empty.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
try:
subgraph_id = int(subgraph_id)
except ValueError:
return jsonify(error='{} id has to be an integer.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
db = get_db()
db_cursor = db.cursor()
if not subgraph_access(user_id, subgraph_id):
return jsonify(error=MSG_SUBGRAPH_ACCESS.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term'],
subgraph_id, user_id))
subgraph_name = db_cursor.execute('SELECT name FROM subgraph WHERE id = ?',
(subgraph_id, )).fetchone()[0]
db_cursor.execute('UPDATE subgraph SET deleted = 1 WHERE id = ?',
(subgraph_id, ))
db.commit()
return jsonify(name=subgraph_name)
def index(message=None):
if message:
message = unquote(message)
db = get_db()
user_list = db.execute(
'SELECT id, username, admin, uri'
' FROM user'
' WHERE deleted = 0'
' ORDER BY id ASC', ).fetchall()
show_admin_message, admin_message = get_admin_message()
return render_template('tool/admin.html',
user_list=user_list,
show_admin_message=show_admin_message,
admin_message=admin_message,
message=message)
def edit_subgraph_name():
"""Changes the name of a subgraph.
Request args:
subgraph_id (int): Id of the Subgraph to change.
name (str): The new name of the subgraph.
Context:
g.user (sqlite3.Row): Logged in user. Must have access to the subgraph.
Returns JSON:
name (str): The new name of the subgraph. (identical to the argument, only to make js simpler)
error (str): An error message if something went wrong.
"""
user_id = g.user['id']
subgraph_id = request.json.get('subgraph_id')
subgraph_name = request.json.get('name')
if subgraph_id is None:
return jsonify(error='{} id cannot be empty.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
try:
subgraph_id = int(subgraph_id)
except ValueError:
return jsonify(error='{} id has to be an integer.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
if not subgraph_name or subgraph_name.isspace():
return jsonify(error='{} name cannot be empty.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
db = get_db()
db_cursor = db.cursor()
if not subgraph_access(user_id, subgraph_id):
return jsonify(error=MSG_SUBGRAPH_ACCESS.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term'],
subgraph_id, user_id))
db_cursor.execute('UPDATE subgraph SET name = ? WHERE id = ?',
(subgraph_name, subgraph_id))
db.commit()
return jsonify(name=subgraph_name)
def undo_delete_subgraph():
"""Marks a subgraph as not deleted.
Request args:
subgraph_id (int): Id of the Subgraph to un-delete.
Context:
g.user (sqlite3.Row): Logged in user. Must have access to the subgraph.
Returns JSON:
error (str): An error message if something went wrong.
"""
user_id = g.user['id']
subgraph_id = request.json.get('subgraph_id')
if subgraph_id is None:
return jsonify(error='{} id cannot be empty.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
try:
subgraph_id = int(subgraph_id)
except ValueError:
return jsonify(error='{} id has to be an integer.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
db = get_db()
db_cursor = db.cursor()
access = db_cursor.execute(
'SELECT EXISTS ('
' SELECT 1 FROM access JOIN subgraph ON subgraph_id = id WHERE user_id = ? AND subgraph_id = ?'
')', (user_id, subgraph_id)).fetchone()[0]
if not access:
return jsonify(error=MSG_SUBGRAPH_ACCESS.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term'],
subgraph_id, user_id))
db_cursor.execute('UPDATE subgraph SET deleted = 0 WHERE id = ?',
(subgraph_id, ))
db.commit()
return jsonify()
def load_rdf_data(self, data, rdf_format='turtle'):
super().load_rdf_data(data, rdf_format)
# todo check if the graph works: build root and all possible children?
# check property orders: [f.order for f in fields] != list(range(1, len(fields) + 1))
db = get_db()
db.execute('DELETE FROM ontology')
for subject, predicate, object_ in self.graph[::]:
db.execute(
'INSERT INTO ontology (subject, predicate, object) VALUES (?, ?, ?)',
(subject.n3(), predicate.n3(), object_.n3()))
for prefix, uri in self.graph.namespaces():
db.execute('INSERT INTO namespace (prefix, uri) VALUES (?, ?)',
(prefix, uri.n3()))
db.commit()
def undo_delete_individual(self, uri):
if type(uri) == str:
uri = URIRef(uri)
db = get_db()
rows = db.execute(
'SELECT subject, predicate, object FROM knowledge WHERE subgraph_id = ? AND deleted = ?',
(self.id, uri.n3())).fetchall()
for row in rows:
self.graph.add(row_to_rdf(row))
db.execute(
'UPDATE knowledge SET deleted = NULL WHERE subgraph_id = ? AND deleted = ?',
(self.id, uri.n3()))
db.commit()
self.root = None # forces a rebuild of the root entity
def new_value(self, entity_uri, property_uri):
if type(entity_uri) == str:
entity_uri = URIRef(entity_uri)
if type(property_uri) == str:
property_uri = URIRef(property_uri)
index = self.get_property_free_index(entity_uri, property_uri)
value = Literal('')
self.graph.add((entity_uri, property_uri, value))
self.properties[(entity_uri, property_uri)][index] = value
db = get_db()
db.execute(
'INSERT INTO knowledge (subgraph_id, subject, predicate, object, property_index) VALUES (?, ?, ?, ?, ?)',
(self.id, entity_uri.n3(), property_uri.n3(), value.n3(), index))
db.commit()
self.root = None # forces a rebuild of the root entity
return index
def search():
filter_data = request.json
# todo get_result(filter_data) should be a method of the filter
# get list of subgraphs that are finished and not deleted
db = get_db()
rows = db.execute('SELECT id FROM access JOIN subgraph ON subgraph_id = id'
' WHERE deleted = 0 AND finished = 1').fetchall()
results = {row['id'] for row in rows}
knowledge = {
subgraph_id:
{(str(p), str(o))
for s, p, o in get_subgraph_knowledge(subgraph_id).get_graph(
clean=True)}
for subgraph_id in results
}
results = filter(
lambda subgraph_id: all(po in knowledge[subgraph_id]
for po in filter_data.items()), results)
return jsonify(results=[str(subgraph_id) for subgraph_id in results])
def index(message=None):
"""Home view of the tool.
Contains list of accessible subgraphs.
"""
user_id = g.user['id']
if message:
message = unquote(message)
db = get_db()
# for the subgraph menu
subgraph_list = db.execute(
'SELECT id, name, finished'
' FROM access JOIN subgraph ON subgraph_id = id'
' WHERE user_id = ? and deleted = 0'
' ORDER BY subgraph_id ASC', (user_id, )).fetchall()
return render_template('tool/index.html',
subgraph_list=subgraph_list,
message=message)
def change_value(self, entity_uri, property_uri, index, value):
if type(entity_uri) == str:
entity_uri = URIRef(entity_uri)
if type(property_uri) == str:
property_uri = URIRef(property_uri)
db = get_db()
# delete previous value from graph
prev_value = db.execute(
'SELECT object FROM knowledge '
' WHERE subgraph_id = ? AND subject = ? AND predicate = ? AND property_index = ?',
(self.id, entity_uri.n3(), property_uri.n3(), index)).fetchone()
if prev_value:
self.graph.remove((entity_uri, property_uri,
parse_n3_term(prev_value['object'])))
# add new value to graph
validity, value = self.check_property_value(property_uri, value)
if validity:
# the check returned an error message
del self.properties[(entity_uri, property_uri)][index]
db.execute(
'UPDATE knowledge SET object = ? '
' WHERE subgraph_id = ? AND subject = ? AND predicate = ? AND property_index = ?',
(Literal('').n3(), self.id, entity_uri.n3(), property_uri.n3(),
index))
db.commit()
self.root = None
return validity
self.graph.add((entity_uri, property_uri, value))
self.properties[(entity_uri, property_uri)][index] = value
db.execute(
'UPDATE knowledge SET object = ? '
' WHERE subgraph_id = ? AND subject = ? AND predicate = ? AND property_index = ?',
(value.n3(), self.id, entity_uri.n3(), property_uri.n3(), index))
db.commit()
self.root = None # forces a rebuild of the root entity
def set_finished():
"""Changes the finished flag of a subgraph.
Request args:
subgraph_id (int): Id of the Subgraph to change.
finished (str: 'true' or 'false'): The new value of the finished flag of the subgraph.
Context:
g.user (sqlite3.Row): Logged in user. Must have access to the subgraph.
Returns JSON:
error (str): An error message if something went wrong.
"""
user_id = g.user['id']
subgraph_id = request.json.get('subgraph_id')
finished = request.json.get('finished')
if subgraph_id is None:
return jsonify(error='{} id cannot be empty.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
try:
subgraph_id = int(subgraph_id)
except ValueError:
return jsonify(error='{} id has to be an integer.'.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term']))
if finished is None:
return jsonify(error='Argument "finished" cannot be empty.')
db = get_db()
if not subgraph_access(user_id, subgraph_id):
return jsonify(error=MSG_SUBGRAPH_ACCESS.format(
current_app.config['FRONTEND_CONFIG']['Subgraph_term'],
subgraph_id, user_id))
db.execute('UPDATE subgraph SET finished = ? WHERE id = ?',
(finished, subgraph_id))
db.commit()
return jsonify()
