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_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): 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 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})