def delete(self, request, schema, table):
schema, table = actions.get_table_name(schema, table)
meta_schema = actions.get_meta_schema_name(schema)
edit_table = actions.get_edit_table_name(schema, table)
actions._get_engine().execute(
"DROP TABLE {schema}.{table} CASCADE;".format(
schema=meta_schema, table=edit_table
)
)
edit_table = actions.get_insert_table_name(schema, table)
actions._get_engine().execute(
"DROP TABLE {schema}.{table} CASCADE;".format(
schema=meta_schema, table=edit_table
)
)
edit_table = actions.get_delete_table_name(schema, table)
actions._get_engine().execute(
"DROP TABLE {schema}.{table} CASCADE;".format(
schema=meta_schema, table=edit_table
)
)
actions._get_engine().execute(
"DROP TABLE {schema}.{table} CASCADE;".format(schema=schema, table=table)
)
return JsonResponse({}, status=status.HTTP_200_OK)
def delete(self, request, schema, table):
schema, table = actions.get_table_name(schema, table)
meta_schema = actions.get_meta_schema_name(schema)
edit_table = actions.get_edit_table_name(schema, table)
actions._get_engine().execute(
"DROP TABLE \"{schema}\".\"{table}\" CASCADE;".format(
schema=meta_schema, table=edit_table))
edit_table = actions.get_insert_table_name(schema, table)
actions._get_engine().execute(
"DROP TABLE \"{schema}\".\"{table}\" CASCADE;".format(
schema=meta_schema, table=edit_table))
edit_table = actions.get_delete_table_name(schema, table)
actions._get_engine().execute(
"DROP TABLE \"{schema}\".\"{table}\" CASCADE;".format(
schema=meta_schema, table=edit_table))
actions._get_engine().execute(
"DROP TABLE \"{schema}\".\"{table}\" CASCADE;".format(
schema=schema, table=table))
table_object, _ = DBTable.objects.get_or_create(name=table,
schema__name=schema)
table_object.delete()
return JsonResponse({}, status=status.HTTP_200_OK)
def post(self, request, schema, table):
columns = actions.analyze_columns(sec.dbname, schema, table)
comment = {
'Name': request.POST['name'],
'Source': self._load_url_list(request, 'source'),
'Reference date': self._load_list(request, 'ref_date'),
'Date of Collection': self._load_list(request, 'date_col'),
'Spatial Resolution': self._load_list(request, 'spat_res'),
'Licence': self._load_url_list(request, 'licence'),
'Description': self._load_list(request, 'descr'),
'Column': self._load_col_list(request, columns),
'Changes':[],
'Notes': self._load_list(request, 'notes'),
'Instructions for proper use': self._load_list(request, 'instr'),
}
engine = actions._get_engine()
conn = engine.connect()
trans = conn.begin()
try:
conn.execute(
sqla.text("COMMENT ON TABLE {schema}.{table} IS :comment ;".format(
schema=schema,
table=table)),
comment=json.dumps(comment)
)
except Exception as e:
raise e
else:
trans.commit()
finally:
conn.close()
return redirect('/dataedit/view/{schema}/{table}'.format(schema=schema,
table=table))
def post(self, request, schema, table):
columns = actions.analyze_columns(sec.dbname, schema, table)
comment = {
'Name': request.POST['name'],
'Source': self._load_url_list(request, 'source'),
'Reference date': self._load_list(request, 'ref_date'),
'Date of Collection': self._load_list(request, 'date_col'),
'Spatial Resolution': self._load_list(request, 'spat_res'),
'Licence': self._load_url_list(request, 'licence'),
'Description': self._load_list(request, 'descr'),
'Column': self._load_col_list(request, columns),
'Changes': [],
'Notes': self._load_list(request, 'notes'),
'Instructions for proper use': self._load_list(request, 'instr'),
}
engine = actions._get_engine()
conn = engine.connect()
trans = conn.begin()
try:
conn.execute(sqla.text(
"COMMENT ON TABLE {schema}.{table} IS :comment ;".format(
schema=schema, table=table)),
comment=json.dumps(comment))
except Exception as e:
raise e
else:
trans.commit()
finally:
conn.close()
return redirect('/dataedit/view/{schema}/{table}'.format(schema=schema,
table=table))
def get_bibtype_id(bibtype):
engine = _get_engine()
sess = Session(bind=engine)
et = sess.query(ref.EntryType).filter(
ref.EntryType.label == bibtype).first()
sess.close()
return et
def post(self, request, entries_id=None):
# I do not know why this is necessary, but it is :/
data = {k: v for k, v in request.POST.items()}
del data['csrfmiddlewaretoken']
engine = _get_engine()
metadata = MetaData()
metadata.create_all(bind=engine)
sess = Session(bind=engine)
bibtype = data.pop('bibtype')
entry_type = get_bibtype_id(bibtype)
if 'edit' in data:
entries_id = data['edit']
del data['edit']
# TODO: Remove if clause after rigorous testing
data['entry_types_id'] = entry_type.entry_types_id if entry_type else 1
form = FORM_MAP[bibtype](**data)
if entries_id:
form.edit(sess,entries_id)
else:
form.save(sess)
sess.commit()
return redirect('/literature')
def add_table_tags(request):
"""
Updates the tags on a table according to the tag values in request.
The update will delete all tags that are not present in request and add all tags that are.
:param request: A HTTP-request object sent by the Django framework. The *POST* field must contain the following values:
* schema: The name of a schema
* table: The name of a table
* Any number of values that start with 'tag_' followed by the id of a tag.
:return: Redirects to the previous page
"""
ids = {
int(field[len('tag_'):])
for field in request.POST if field.startswith('tag_')
}
schema = request.POST['schema']
table = request.POST.get('table', None)
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
session.query(Table_tags).filter(
Table_tags.table_name == table
and Table_tags.schema_name == schema).delete()
for id in ids:
t = Table_tags(**{
'schema_name': schema,
'table_name': table,
'tag': id
})
session.add(t)
session.commit()
return redirect(request.META['HTTP_REFERER'])
def get_bibtype_id(bibtype):
engine = _get_engine()
sess = Session(bind=engine)
et = sess.query(ref.EntryType).filter(
ref.EntryType.label == bibtype).first()
sess.close()
return et
def get_dependencies(schema, table, found=None):
if not found:
found = {(schema, table)}
query = 'SELECT DISTINCT \
ccu.table_name AS foreign_table, \
ccu.table_schema AS foreign_schema \
FROM \
information_schema.table_constraints AS tc \
JOIN information_schema.constraint_column_usage AS ccu \
ON ccu.constraint_name = tc.constraint_name \
WHERE constraint_type = \'FOREIGN KEY\' AND tc.table_schema=\'{schema}\'\
AND tc.table_name=\'{table}\';'.format(schema=schema, table=table)
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
result = session.execute(query)
found_new = {(row.foreign_schema, row.foreign_table)
for row in result
if (row.foreign_schema, row.foreign_table) not in found}
found = found.union(found_new)
found.add((schema, table))
session.close()
for s, t in found_new:
found = found.union(get_dependencies(s, t, found))
return found
def put(self, request, schema, table, row_id=None):
schema, table = actions.get_table_name(schema, table)
if not row_id:
return JsonResponse(actions._response_error('This methods requires an id'),
status=status.HTTP_400_BAD_REQUEST)
column_data = request.data['query']
if row_id and column_data.get('id', int(row_id)) != int(row_id):
raise actions.APIError(
'Id in URL and query do not match. Ids may not change.',
status=status.HTTP_409_CONFLICT)
engine = actions._get_engine()
conn = engine.connect()
# check whether id is already in use
exists = conn.execute('select count(*) '
'from {schema}.{table} '
'where id = {id};'.format(schema=schema,
table=table,
id=row_id)).first()[0] > 0 if row_id else False
conn.close()
if exists:
response = self.__update_rows(request, schema, table, column_data, row_id)
actions.apply_changes(schema, table)
return JsonResponse(response)
else:
result = self.__insert_row(request, schema, table, column_data, row_id)
actions.apply_changes(schema, table)
return JsonResponse(result, status=status.HTTP_201_CREATED)
def post(self, request, entries_id=None):
# I do not know why this is necessary, but it is :/
data = {k: v for k, v in request.POST.items()}
del data['csrfmiddlewaretoken']
engine = _get_engine()
metadata = MetaData()
metadata.create_all(bind=engine)
sess = Session(bind=engine)
bibtype = data.pop('bibtype')
entry_type = get_bibtype_id(bibtype)
if 'edit' in data:
entries_id = data['edit']
del data['edit']
# TODO: Remove if clause after rigorous testing
data['entry_types_id'] = entry_type.entry_types_id if entry_type else 1
form = FORM_MAP[bibtype](**data)
if entries_id:
form.edit(sess,entries_id)
else:
form.save(sess)
sess.commit()
return redirect('/literature')
def post(self, request):
results = []
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
search_view = sqla.Table("meta_search", metadata, autoload=True)
filter_tags = [
int(key[len('select_'):]) for key in request.POST
if key.startswith('select_')
]
tag_agg = array_agg(Table_tags.tag)
query = session.query(
search_view.c.schema.label('schema'),
search_view.c.table.label('table'), tag_agg).outerjoin(
Table_tags, (search_view.c.table == Table_tags.table_name)
and (search_view.c.table == Table_tags.table_name))
if filter_tags:
query = query.having(tag_agg.contains(filter_tags))
query = query.group_by(search_view.c.schema, search_view.c.table)
results = session.execute(query)
session.commit()
ret = [{'schema': r.schema, 'table': r.table} for r in results]
print(ret)
return render(request, 'dataedit/search.html', {
'results': ret,
'tags': get_all_tags(),
'selected': filter_tags
})
def get_readable_table_names(schema):
"""
Loads all tables from a schema with their corresponding comments, extracts
their readable names, if possible.
:param schema: The schema name as string
:return: A dictionary with that maps table names to readable names as returned by :py:meth:`dataedit.views.read_label`
"""
engine = actions._get_engine()
conn = engine.connect()
try:
res = conn.execute(
"SELECT table_name as TABLE "
"FROM information_schema.tables where table_schema='{table_schema}';"
.format(table_schema=schema))
except Exception as e:
raise e
return {}
finally:
conn.close()
return {
r[0]: read_label(r[0], load_metadata_from_db(schema, r[0]))
for r in res
}
def post(self, request, schema, table):
"""
Handles the send event of the form created in the get-method. The
metadata is transformed into a JSON-dictionary and stored in the tables
comment inside the database.
:param request: A HTTP-request object sent by the Django framework
:param schema: Name of a schema
:param table: Name of a table
:return: Redirects to the view of the specified table
"""
columns = actions.analyze_columns(sec.dbname, schema, table)
comment = load_meta(request.POST)
engine = actions._get_engine()
conn = engine.connect()
trans = conn.begin()
try:
conn.execute(sqla.text(
"COMMENT ON TABLE {schema}.{table} IS :comment ;".format(
schema=schema, table=table)),
comment=json.dumps(comment))
except Exception as e:
raise e
else:
trans.commit()
finally:
conn.close()
return redirect('/dataedit/view/{schema}/{table}'.format(schema=schema,
table=table))
def tag_editor(request, id=""):
tags = get_all_tags()
create_new = True
for t in tags:
if id != "" and int(id) == t["id"]:
tag = t
# inform the user if tag is assigned to an object
engine = actions._get_engine()
Session = sessionmaker()
session = Session(bind=engine)
assigned = session.query(Table_tags).filter(
Table_tags.tag == t["id"]).count() > 0
return render(request=request,
template_name='dataedit/tag_editor.html',
context={
"name": tag['name'],
"id": tag['id'],
"color": tag['color'],
"assigned": assigned
})
return render(request=request,
template_name='dataedit/tag_editor.html',
context={
"name": "",
"color": "#000000",
"assigned": False
})
def list_references(request, error=None):
engine = _get_engine()
sess = Session(bind=engine)
refs = [r for r in sess.query(ref.Entry)]
return render(request, 'literature/list_references.html', {
'refs': refs,
'error': error
})
def load_tags():
engine = _get_engine()
Session = sessionmaker(bind=engine)
session = Session()
tags = list(session.query(Tag))
d = {tag.id: {'id': tag.id, 'name': tag.name, 'color': "#" + format(tag.color, '06X')} for tag in tags}
session.close()
return d
def list_references(request, error=None):
engine = _get_engine()
sess = Session(bind=engine)
refs = sorted((r for r in sess.query(ref.Entry)), key=lambda r: r.title)
return render(request, "literature/list_references.html", {
"refs": refs,
"error": error
})
def edit_tag(id, name, color):
engine = actions._get_engine()
Session = sessionmaker()
session = Session(bind=engine)
result = session.query(Tag).filter(Tag.id == id).one()
result.name = name
result.color = str(int(color[1:], 16))
session.commit()
def get(self, request, schema, table):
"""
Collects the following information on the specified table:
* Postgresql comment on this table
* A list of all columns
* A list of all revisions of this table
:param request: An HTTP-request object sent by the Django framework
:param schema: Name of a schema
:param table: Name of a table stored in this schema
:return:
"""
if schema not in schema_whitelist or schema.startswith('_'):
raise Http404("Schema not accessible")
tags = [] # TODO: Unused - Remove
db = sec.dbname
engine = actions._get_engine()
if not engine.dialect.has_table(engine, table, schema=schema):
raise Http404
actions.create_meta(schema, table)
comment_on_table = load_comment_from_db(schema, table)
revisions = []
api_changes = change_requests(schema, table)
data = api_changes.get('data')
display_message = api_changes.get('display_message')
display_items = api_changes.get('display_items')
is_admin = False
if request.user and not request.user.is_anonymous():
is_admin = request.user.has_admin_permissions(schema, table)
return render(
request, 'dataedit/dataedit_overview.html', {
'comment_on_table': dict(comment_on_table),
'revisions': revisions,
'kinds': ['table', 'map', 'graph'],
'table': table,
'schema': schema,
'tags': tags,
'data': data,
'display_message': display_message,
'display_items': display_items,
'is_admin': is_admin,
'host': request.get_host()
})
def add_tag(name, color):
engine = actions._get_engine()
Session = sessionmaker()
session = Session(bind=engine)
session.add(
Tag(**{
"name": name,
"color": str(int(color[1:], 16)),
"id": None
}))
session.commit()
def delete_tag(id):
engine = actions._get_engine()
Session = sessionmaker()
session = Session(bind=engine)
# delete all occurrences of the tag from Table_tag
session.query(Table_tags).filter(Table_tags.tag == id).delete()
# delete the tag from Tag
session.query(Tag).filter(Tag.id == id).delete()
session.commit()
def add_tag(name, color):
engine = actions._get_engine()
Session = sessionmaker()
session = Session(bind=engine)
session.add(
Tag(**{
'name': name,
'color': str(int(color[1:], 16)),
'id': None
}))
session.commit()
def get_readable_table_names(schema):
engine = actions._get_engine()
conn = engine.connect()
try:
res = conn.execute('SELECT table_name as TABLE, obj_description(((\'{table_schema}.\' || table_name ))::regclass) as COMMENT ' \
'FROM information_schema.tables where table_schema=\'{table_schema}\';'.format(table_schema=schema))
except Exception as e:
raise e
return {}
finally:
conn.close()
return {table: read_label(table, comment) for (table,comment) in res}
def get_readable_table_names(schema):
engine = actions._get_engine()
conn = engine.connect()
try:
res = conn.execute('SELECT table_name as TABLE, obj_description(((\'{table_schema}.\' || table_name ))::regclass) as COMMENT ' \
'FROM information_schema.tables where table_schema=\'{table_schema}\';'.format(table_schema=schema))
except Exception as e:
raise e
return {}
finally:
conn.close()
return {table: read_label(table, comment) for (table, comment) in res}
def get(self, request, entries_id=None):
if entries_id:
engine = _get_engine()
sess = Session(bind=engine)
entry = sess.query(ref.Entry).filter(ref.Entry.entries_id == entries_id).first()
btype = entry.entry_types.label if entry.entry_types else 'article'
else:
entry = ref.Entry()
btype = None
return render(request, 'literature/reference_form.html',
{'entry': entry,
'years': range(datetime.datetime.now().year, 1899, -1),
'id': entries_id, 'btype': btype}, )
def get(self, request, entries_id=None):
if entries_id:
engine = _get_engine()
sess = Session(bind=engine)
entry = sess.query(ref.Entry).filter(ref.Entry.entries_id == entries_id).first()
btype = entry.entry_types.label if entry.entry_types else 'article'
else:
entry = ref.Entry()
btype = None
return render(request, 'literature/reference_form.html',
{'entry': entry,
'years': range(datetime.datetime.now().year, 1899, -1),
'id': entries_id, 'btype': btype}, )
def printable(model, field):
if field == "tags":
tags = []
engine = _get_engine()
Session = sessionmaker(bind=engine)
session = Session()
for tag_id in getattr(model, field):
tag = session.query(Tag).get(tag_id)
tags.append(tag.name)
session.close()
return tags
else:
return getattr(model, field)
def get_all_tags(schema=None, table=None):
"""
Load all tags of a specific table
:param schema: Name of a schema
:param table: Name of a table
:return:
"""
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
try:
if table == None:
# Neither table, not schema are defined
result = session.execute(sqla.select([Tag]).order_by("name"))
session.commit()
r = [{
"id": r.id,
"name": r.name,
"color": "#" + format(r.color, "06X"),
"usage_count": r.usage_count,
"usage_tracked_since": r.usage_tracked_since,
} for r in result]
return sort_tags_by_popularity(r)
if schema == None:
# default schema is the public schema
schema = "public"
result = session.execute(
session.query(
Tag.name.label("name"),
Tag.id.label("id"),
Tag.color.label("color"),
Tag.usage_count.label("usage_count"),
Tag.usage_tracked_since.label("usage_tracked_since"),
TableTags.table_name,
).filter(TableTags.tag == Tag.id).filter(
TableTags.table_name == table).filter(
TableTags.schema_name == schema).order_by("name"))
session.commit()
finally:
session.close()
r = [{
"id": r.id,
"name": r.name,
"color": "#" + format(r.color, "06X"),
"usage_count": r.usage_count,
"usage_tracked_since": r.usage_tracked_since,
} for r in result]
return sort_tags_by_popularity(r)
def listtables(request, schema_name):
"""
:param request: A HTTP-request object sent by the Django framework
:param schema_name: Name of a schema
:return: Renders the list of all tables in the specified schema
"""
searchedQueryString = request.GET.get("query")
searchedTagIds = list(map(
int,
request.GET.getlist("tags"),
))
for tag_id in searchedTagIds:
increment_usage_count(tag_id)
engine = actions._get_engine()
conn = engine.connect()
labels = get_readable_table_names(schema_name)
query = ("SELECT tablename FROM pg_tables WHERE schemaname = '{schema}' "
"AND pg_has_role('{user}', tableowner, 'MEMBER');".format(
schema=schema_name, user=sec.dbuser))
tables = conn.execute(query)
tables = [
(table.tablename, labels.get(table.tablename),
get_all_tags(schema_name, table.tablename)) for table in tables
if not table.tablename.startswith("_") and
(searchedQueryString is None or searchedQueryString in table.tablename)
]
# Apply tag filter later on, because I am not smart enough to do it inline.
tables = [
tableEntry for tableEntry in tables
if {tag['id']
for tag in tableEntry[2]}.issuperset(searchedTagIds or set())
]
tables = sorted(tables, key=lambda x: x[0])
return render(
request,
"dataedit/dataedit_tablelist.html",
{
"schema": schema_name,
"tables": tables,
"query": searchedQueryString,
"tags": searchedTagIds
},
)
def add_table_tags(request):
ids = {int(field[len('tag_'):]) for field in request.POST if field.startswith('tag_')}
schema = request.POST['schema']
table = request.POST.get('table',None)
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
session.query(Table_tags).filter(Table_tags.table_name==table and Table_tags.schema_name==schema).delete()
for id in ids:
t = Table_tags(**{'schema_name':schema, 'table_name':table, 'tag':id})
session.add(t)
session.commit()
return redirect(request.META['HTTP_REFERER'])
def get(self, request, schema='model_draft', table=None):
"""Handle GET request (render the page).
"""
engine = actions._get_engine()
can_add = False
columns = None
pk_fields = None
n_rows = None
if table:
# get information about the table
# if upload: table must exist in schema model_draft
if schema != 'model_draft':
raise Http404('Can only upload to schema model_draft')
if not engine.dialect.has_table(engine, table, schema=schema):
raise Http404('Table does not exist')
table_obj = Table.load(schema, table)
if not request.user.is_anonymous:
user_perms = login_models.UserPermission.objects.filter(
table=table_obj)
level = request.user.get_table_permission_level(table_obj)
can_add = level >= login_models.WRITE_PERM
columns = get_column_description(schema, table)
# get number of rows
sql = "SELECT COUNT(*) FROM {schema}.{table}".format(schema=schema,
table=table)
res = actions.perform_sql(sql)
n_rows = res['result'].fetchone()[0]
context = {
"config":
json.dumps({ # pass as json string
"canAdd": can_add,
"columns": columns,
"schema": schema,
"table": table,
"nRows": n_rows
}),
"schema":
schema,
"table":
table,
"can_add":
can_add
}
return render(request, "dataedit/wizard.html", context=context)
def load_tags():
engine = _get_engine()
Session = sessionmaker(bind=engine)
session = Session()
tags = list(session.query(Tag))
d = {
tag.id: {
"id": tag.id,
"name": tag.name,
"color": "#" + format(tag.color, "06X"),
"usage_count": tag.usage_count,
"usage_tracked_since": tag.usage_tracked_since,
}
for tag in tags
}
session.close()
return d
def read_bibtexfile(bibtex_file):
engine = _get_engine()
metadata = MetaData()
metadata.create_all(bind=engine)
sess = Session(bind=engine)
bibtex_database = btp.load(bibtex_file)
for ent in bibtex_database.entries:
props = {k.name: ent[k.name.replace('entries.', '')] for k in
ref.Entry.__table__.c if k.name.replace('entries.', '') in ent}
props['entry_types_id'] = get_bibtype_id(ent['ENTRYTYPE'])
en = ref.Entry(**props)
sess.add(en)
sess.commit()
sess.close()
return redirect('/literature')
def increment_usage_count(tag_id):
"""
Increment usage count of a specific tag
:param tag_id: ID of the tag which usage count should be incremented
:return:
"""
engine = actions._get_engine()
Session = sessionmaker()
session = Session(bind=engine)
try:
result = session.query(Tag).filter_by(id=tag_id).first()
result.usage_count += 1
session.commit()
finally:
session.close()
def read_bibtexfile(bibtex_file):
engine = _get_engine()
metadata = MetaData()
metadata.create_all(bind=engine)
sess = Session(bind=engine)
bibtex_database = btp.load(bibtex_file)
for ent in bibtex_database.entries:
props = {k.name: ent[k.name.replace('entries.', '')] for k in
ref.Entry.__table__.c if k.name.replace('entries.', '') in ent}
props['entry_types_id'] = get_bibtype_id(ent['ENTRYTYPE'])
en = ref.Entry(**props)
print(en.entry_types_id.label)
sess.add(en)
sess.commit()
sess.close()
return redirect('/literature')
def get_all_tags(schema=None, table=None):
"""
Load all tags of a specific table
:param schema: Name of a schema
:param table: Name of a table
:return:
"""
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
try:
if table == None:
# Neither table, not schema are defined
result = session.execute(sqla.select([Tag]).order_by('name'))
session.commit()
r = [{
'id': r.id,
'name': r.name,
'color': "#" + format(r.color, '06X')
} for r in result]
return r
if schema == None:
# default schema is the public schema
schema = 'public'
result = session.execute(
session.query(
Tag.name.label('name'), Tag.id.label('id'),
Tag.color.label('color'),
Table_tags.table_name).filter(Table_tags.tag == Tag.id).filter(
Table_tags.table_name == table).filter(
Table_tags.schema_name == schema).order_by('name'))
session.commit()
finally:
session.close()
return [{
'id': r.id,
'name': r.name,
'color': "#" + format(r.color, '06X')
} for r in result]
def post(self, request):
results = []
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
search_view = sqla.Table("meta_search", metadata, autoload=True)
filter_tags = [int(key[len('select_'):]) for key in request.POST if key.startswith('select_')]
tag_agg = array_agg(Table_tags.tag)
query = session.query(search_view.c.schema.label('schema'), search_view.c.table.label('table'), tag_agg).outerjoin(Table_tags, (search_view.c.table == Table_tags.table_name) and (search_view.c.table == Table_tags.table_name))
if filter_tags:
query = query.having(tag_agg.contains(filter_tags))
query = query.group_by(search_view.c.schema, search_view.c.table)
results = session.execute(query)
session.commit()
ret = [{'schema': r.schema, 'table':r.table} for r in results]
print(ret)
return render(request, 'dataedit/search.html', {'results': ret, 'tags':get_all_tags(), 'selected': filter_tags})
def get_all_tags(schema=None, table=None):
engine = actions._get_engine()
metadata = sqla.MetaData(bind=engine)
Session = sessionmaker()
session = Session(bind=engine)
print("Load tags for" , schema, table)
if table == None:
# Neither table, not schema are defined
result = session.execute(sqla.select([Tag]))
session.commit()
r = [{'id':r.id, 'name': r.name, 'color':"#" + format(r.color, '06X')} for r in result]
print(r)
return r
if schema == None:
# default schema is the public schema
schema='public'
result = session.execute(session.query(Tag.name.label('name'), Tag.id.label('id'), Tag.color.label('color'), Table_tags.table_name).filter(Table_tags.tag == Tag.id).filter(Table_tags.table_name == table).filter(Table_tags.schema_name == schema))
session.commit()
return [{'id':r.id, 'name': r.name, 'color':"#" + format(r.color, '06X')} for r in result]
def show_entry(request, entries_id):
engine = _get_engine()
sess = Session(bind=engine)
entry = sess.query(ref.Entry).filter(
ref.Entry.entries_id == entries_id).first()
return render(request, 'literature/reference.html', {'entry': entry})
def list_references(request, error=None):
engine = _get_engine()
sess = Session(bind=engine)
refs = [r for r in sess.query(ref.Entry)]
return render(request, 'literature/list_references.html', {'refs': refs, 'error':error})
