def list_publications(project, collection_id, order_by="id"): """ List all publications in a given collection """ project_id = get_project_id_from_name(project) connection = db_engine.connect() collections = get_table("publication_collection") publications = get_table("publication") statement = select([collections]).where( collections.c.id == int_or_none(collection_id)).order_by(str(order_by)) rows = connection.execute(statement).fetchall() if len(rows) != 1: return jsonify({"msg": "Could not find collection in database."}), 404 elif rows[0]["project_id"] != int_or_none(project_id): return jsonify({ "msg": "Found collection not part of project {!r} with ID {}.".format( project, project_id) }), 400 statement = select([publications ]).where(publications.c.publication_collection_id == int_or_none(collection_id)).order_by( str(order_by)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def get_publication_facsimiles(project, publication_id): """ List all fascimilies for the given publication """ connection = db_engine.connect() publication_facsimiles = get_table("publication_facsimile") facsimile_collections = get_table("publication_facsimile_collection") # join in facsimile_collections to we can get the collection title as well tables = join( publication_facsimiles, facsimile_collections, publication_facsimiles.c.publication_facsimile_collection_id == facsimile_collections.c.id) statement = select([publication_facsimiles, facsimile_collections.c.title])\ .where(publication_facsimiles.c.publication_id == int_or_none(publication_id))\ .where(publication_facsimiles.c.deleted != 1)\ .select_from(tables) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def connect_event(event_id): """ Link an event to a location, subject, or tag through event_connection POST data MUST be in JSON format. POST data MUST contain at least one of the following: subject_id: ID for the subject involved in the given event location_id: ID for the location involved in the given event tag_id: ID for the tag involved in the given event """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 events = get_table("event") connection = db_engine.connect() select_event = select([events]).where(events.c.id == int_or_none(event_id)) event_exists = connection.execute(select_event).fetchall() if len(event_exists) != 1: return jsonify({"msg": "Event ID not found in database"}), 404 event_connections = get_table("event_connection") insert = event_connections.insert() new_event_connection = { "event_id": int(event_id), "subject_id": int(request_data["subject_id"]) if request_data.get( "subject_id", None) else None, "location_id": int(request_data["location_id"]) if request_data.get( "location_id", None) else None, "tag_id": int(request_data["tag_id"]) if request_data.get("tag_id", None) else None } try: result = connection.execute(insert, **new_event_connection) new_row = select([ event_connections ]).where(event_connections.c.id == result.inserted_primary_key[0]) new_row = dict(connection.execute(new_row).fetchone()) result = { "msg": "Created new event_connection with ID {}".format( result.inserted_primary_key[0]), "row": new_row } return jsonify(result), 201 except Exception as e: result = { "msg": "Failed to create new event_connection", "reason": str(e) } return jsonify(result), 500 finally: connection.close()
def edit_comment(project, publication_id): """ Takes "filename" and/or "published" as JSON data If there is no publication_comment in the database, creates one Returns "msg" and "comment_id" on success, otherwise 40x """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 filename = request_data.get("filename", None) published = request_data.get("published", None) publications = get_table("publication") comments = get_table("publication_comment") query = select([publications.c.publication_comment_id]).where(publications.c.id == int_or_none(publication_id)) connection = db_engine.connect() result = connection.execute(query).fetchone() if result is None: connection.close() return jsonify("No such publication exists."), 404 comment_id = result[0] values = {} if filename is not None: values["original_filename"] = filename if published is not None: values["published"] = published values["date_modified"] = datetime.now() if len(values) > 0: if comment_id is not None: update = comments.update().where(comments.c.id == int(comment_id)).values(**values) connection.execute(update) connection.close() return jsonify({ "msg": "Updated comment {} with values {}".format(comment_id, str(values)), "comment_id": comment_id }) else: insert = comments.insert().values(**values) r = connection.execute(insert) comment_id = r.inserted_primary_key[0] update = publications.update().where(publications.c.id == int(publication_id)).values({"publication_comment_id": int(comment_id)}) connection.execute(update) connection.close() return jsonify({ "msg": "Created comment {} for publication {} with values {}".format(comment_id, publication_id, str(values)), "comment_id": comment_id }) else: connection.close() return jsonify("No valid update values given."), 400
def add_new_translation(project): """ Add a new translation """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 transaltion = get_table("translation_text") connection = db_engine.connect() result = None translation_id = request_data.get("translation_id", None) # create a new translation if not supplied if translation_id is None and request_data.get("parent_id", None) is not None: translation_id = create_translation(request_data.get("text", None)) # need to add the new id to the location, subject ... table # update table_name set translation_id = translation_id where id = ? target_table = get_table(request_data.get("table_name", None)) values = {} if translation_id is not None: values["translation_id"] = translation_id update = target_table.update().where(target_table.c.id == int( request_data.get("parent_id", None))).values(**values) connection.execute(update) new_translation = { "table_name": request_data.get("table_name", None), "field_name": request_data.get("field_name", None), "text": request_data.get("text", None), "language": request_data.get("language", 'not set'), "translation_id": translation_id } try: insert = transaltion.insert() result = connection.execute(insert, **new_translation) new_row = select([ transaltion ]).where(transaltion.c.id == result.inserted_primary_key[0]) new_row = dict(connection.execute(new_row).fetchone()) result = { "msg": "Created new translation with ID {}".format( result.inserted_primary_key[0]), "row": new_row } return jsonify(result), 201 except Exception as e: result = {"msg": "Failed to create new translation.", "reason": str(e)} return jsonify(result), 500 finally: connection.close() return result
def get_title(project, collection_id): collections = get_table("publication_collection") titles = get_table("publication_collection_title") query = select([collections.c.publication_collection_title_id]).where(collections.c.id == int_or_none(collection_id)) connection = db_engine.connect() result = connection.execute(query).fetchone() if result is None: result.close() return jsonify("No such publication collection exists."), 404 query = select([titles]).where(titles.c.id == int(result[collections.c.publication_collection_title_id])) row = dict(connection.execute(query).fetchone()) connection.close() return jsonify(row)
def delete_event_occurrence(occ_id): """ Logical delete a event_occurrence id of the event_occurrence: Number for publication facsimile page the event occurs in """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 values = {} values["date_modified"] = datetime.now() values["deleted"] = 1 connection = db_engine.connect() event_occurrences = get_table("event_occurrence") try: update = event_occurrences.update().where( event_occurrences.c.id == int(occ_id)).values(**values) connection.execute(update) return jsonify({ "msg": "Delete event_occurrences {} with values {}".format( int(occ_id), str(values)), "occ_id": int(occ_id) }) except Exception as e: result = { "msg": "Failed to delete event_occurrences.", "reason": str(e) } return jsonify(result), 500 finally: connection.close()
def find_event_by_description(): """ List all events whose description contains a given phrase POST data MUST be in JSON format. POST data MUST contain the following: phrase: search-phrase for event description """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 if "phrase" not in request_data: return jsonify({"msg": "No phrase in POST data"}), 400 events = get_table("event") connection = db_engine.connect() statement = select([events]).where( events.c.description.ilike("%{}%".format(request_data["phrase"]))) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def add_new_location(project): """ Add a new location object to the database POST data MUST be in JSON format. POST data MUST contain: name: location name POST data SHOULD also contain: description: location description POST data CAN also contain: legacy_id: legacy id for location latitude: latitude coordinate for location longitude: longitude coordinate for location """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 if "name" not in request_data: return jsonify({"msg": "No name in POST data"}), 400 # Create the translation id translation_id = create_translation(request_data["name"]) # Add a default translation for the location create_translation_text(translation_id, "location") locations = get_table("location") connection = db_engine.connect() new_location = { "name": request_data["name"], "description": request_data.get("description", None), "project_id": get_project_id_from_name(project), "legacy_id": request_data.get("legacy_id", None), "latitude": request_data.get("latitude", None), "longitude": request_data.get("longitude", None), "translation_id": translation_id } try: insert = locations.insert() result = connection.execute(insert, **new_location) new_row = select([ locations ]).where(locations.c.id == result.inserted_primary_key[0]) new_row = dict(connection.execute(new_row).fetchone()) result = { "msg": "Created new location with ID {}".format( result.inserted_primary_key[0]), "row": new_row } return jsonify(result), 201 except Exception as e: result = {"msg": "Failed to create new location", "reason": str(e)} return jsonify(result), 500 finally: connection.close()
def get_subjects(): """ Get all subjects from the database """ connection = db_engine.connect() subject = get_table("subject") columns = [ subject.c.id, cast(subject.c.date_created, Text), subject.c.date_created.label('date_created'), cast(subject.c.date_modified, Text), subject.c.date_modified.label('date_modified'), subject.c.deleted, subject.c.type, subject.c.first_name, subject.c.last_name, subject.c.place_of_birth, subject.c.occupation, subject.c.preposition, subject.c.full_name, subject.c.description, subject.c.legacy_id, cast(subject.c.date_born, Text), subject.c.date_born.label('date_born'), cast(subject.c.date_deceased, Text), subject.c.date_deceased.label('date_deceased'), subject.c.project_id, subject.c.source ] stmt = select(columns) rows = connection.execute(stmt).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def edit_facsimile(project): """ Edit a facsimile object in the database POST data MUST be in JSON format. """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 facsimile_id = request_data.get("id", None) facsimile = get_table("publication_facsimile") connection = db_engine.connect() facsimile_query = select( [facsimile.c.id]).where(facsimile.c.id == int_or_none(facsimile_id)) facsimile_row = connection.execute(facsimile_query).fetchone() if facsimile_row is None: return jsonify({ "msg": "No facsimile with an ID of {} exists.".format(facsimile_id) }), 404 # facsimile_collection_id = request_data.get("facsimile_collection_id", None) page = request_data.get("page", None) priority = request_data.get("priority", None) type = request_data.get("type", None) values = {} if page is not None: values["page_nr"] = page if type is not None: values["type"] = type if priority is not None: values["priority"] = priority values["date_modified"] = datetime.now() if len(values) > 0: try: update = facsimile.update().where( facsimile.c.id == int(facsimile_id)).values(**values) connection.execute(update) return jsonify({ "msg": "Updated facsimile {} with values {}".format( int(facsimile_id), str(values)), "facsimile_id": int(facsimile_id) }) except Exception as e: result = {"msg": "Failed to update facsimile.", "reason": str(e)} return jsonify(result), 500 finally: connection.close() else: connection.close() return jsonify("No valid update values given."), 400
def add_new_subject(project): """ Add a new subject object to the database POST data MUST be in JSON format POST data SHOULD contain: type: subject type description: subject description POST data CAN also contain: first_name: Subject first or given name last_name Subject surname preposition: preposition for subject full_name: Subject full name legacy_id: Legacy id for subject date_born: Subject date of birth date_deceased: Subject date of death """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 subjects = get_table("subject") connection = db_engine.connect() new_subject = { "type": request_data.get("type", None), "description": request_data.get("description", None), "project_id": get_project_id_from_name(project), "first_name": request_data.get("first_name", None), "last_name": request_data.get("last_name", None), "preposition": request_data.get("preposition", None), "full_name": request_data.get("full_name", None), "legacy_id": request_data.get("legacy_id", None), "date_born": request_data.get("date_born", None), "date_deceased": request_data.get("date_deceased", None) } try: insert = subjects.insert() result = connection.execute(insert, **new_subject) new_row = select( [subjects]).where(subjects.c.id == result.inserted_primary_key[0]) new_row = dict(connection.execute(new_row).fetchone()) result = { "msg": "Created new subject with ID {}".format( result.inserted_primary_key[0]), "row": new_row } return jsonify(result), 201 except Exception as e: result = {"msg": "Failed to create new subject.", "reason": str(e)} return jsonify(result), 500 finally: connection.close()
def add_version(project, publication_id): """ Takes "title", "filename", "published", "sort_order", "type" as JSON data "type" denotes version type, 1=base text, 2=other variant Returns "msg" and "version_id" on success, otherwise 40x """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 title = request_data.get("title", None) filename = request_data.get("filename", None) published = request_data.get("published", None) sort_order = request_data.get("sort_order", None) version_type = request_data.get("type", None) publications = get_table("publication") versions = get_table("publication_version") query = select([publications]).where(publications.c.id == int_or_none(publication_id)) connection = db_engine.connect() result = connection.execute(query).fetchone() if result is None: connection.close() return jsonify("No such publication exists."), 404 values = {"publication_id": int(publication_id)} if title is not None: values["name"] = title if filename is not None: values["original_filename"] = filename if published is not None: values["published"] = published if sort_order is not None: values["sort_order"] = sort_order if version_type is not None: values["type"] = version_type insert = versions.insert().values(**values) result = connection.execute(insert) return jsonify({ "msg": "Created new version object.", "version_id": int(result.inserted_primary_key[0]) }), 201
def get_publication_comments(project, publication_id): """ List all comments for the given publication """ connection = db_engine.connect() publications = get_table("publication") publication_comments = get_table("publication_comment") statement = select([ publications.c.publication_comment_id ]).where(publications.c.id == int_or_none(publication_id)) comment_ids = connection.execute(statement).fetchall() comment_ids = [int(row[0]) for row in comment_ids] statement = select([publication_comments ]).where(publication_comments.c.id.in_(comment_ids)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def list_publication_groups(project): """ List all available publication groups """ connection = db_engine.connect() groups = get_table("publication_group") statement = select([groups.c.id, groups.c.published, groups.c.name]) rows = connection.execute(statement).fetchall() result = dict(rows[0]) connection.close() return jsonify(result)
def get_publication_group(project, group_id): """ Get all data for a single publication group """ connection = db_engine.connect() groups = get_table("publication_group") statement = select([groups]).where(groups.c.id == int_or_none(group_id)) rows = connection.execute(statement).fetchall() result = dict(rows[0]) connection.close() return jsonify(result)
def create_facsimile_collection(project): """ Create a new publication_facsimile_collection POST data MUST be in JSON format. POST data SHOULD contain: title: collection type description: collection description folderPath: path to facsimiles for this collection POST data MAY also contain: numberOfPages: total number of pages in this collection startPageNumber: number for starting page of this collection pageComment: Commentary on page numbering externalURL: Externally viewable URL for this facsimile collection """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 collections = get_table("publication_facsimile_collection") connection = db_engine.connect() insert = collections.insert() new_collection = { "title": request_data.get("title", None), "description": request_data.get("description", None), "folder_path": request_data.get("folderPath", None), "external_url": request_data.get("externalUrl", None), "number_of_pages": request_data.get("numberOfPages", None), "start_page_number": request_data.get("startPageNumber", None) } try: result = connection.execute(insert, **new_collection) new_row = select([ collections ]).where(collections.c.id == result.inserted_primary_key[0]) new_row = dict(connection.execute(new_row).fetchone()) result = { "msg": "Created new publication_facsimile_collection with ID {}".format( result.inserted_primary_key[0]), "row": new_row } return jsonify(result), 201 except Exception as e: result = { "msg": "Failed to create new publication_facsimile_collection", "reason": str(e) } return jsonify(result), 500 finally: connection.close()
def get_publication(project, publication_id): """ Get a publication object from the database """ connection = db_engine.connect() publications = get_table("publication") statement = select( [publications]).where(publications.c.id == int_or_none(publication_id)) rows = connection.execute(statement).fetchall() result = dict(rows[0]) connection.close() return jsonify(result)
def get_publications(project): """ List all available publications in the given project """ project_id = get_project_id_from_name(project) connection = db_engine.connect() publication_collections = get_table("publication_collection") publications = get_table("publication") statement = select([ publication_collections.c.id ]).where(publication_collections.c.project_id == project_id) collection_ids = connection.execute(statement).fetchall() collection_ids = [int(row["id"]) for row in collection_ids] statement = select([publications ]).where(publications.c.id.in_(collection_ids)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def edit_title(project, collection_id): """ Takes "filename" and/or "published" as JSON data Returns "msg" and "title_id" on success, otherwise 40x """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 filename = request_data.get("filename", None) published = request_data.get("published", None) collections = get_table("publication_collection") titles = get_table("publication_collection_title") query = select([collections.c.publication_collection_title_id]).where(collections.c.id == int_or_none(collection_id)) connection = db_engine.connect() result = connection.execute(query).fetchone() if result is None: result.close() return jsonify("No such publication collection exists."), 404 values = {} if filename is not None: values["original_filename"] = filename if published is not None: values["published"] = published values["date_modified"] = datetime.now() if len(values) > 0: title_id = int(result[0]) update = titles.update().where(titles.c.id == title_id).values(**values) connection.execute(update) connection.close() return jsonify({ "msg": "Updated publication collection title {} with values {}".format(title_id, str(values)), "title_id": title_id }) else: connection.close() return jsonify("No valid update values given."), 400
def get_publication_versions(project, publication_id): """ List all versions of the given publication """ connection = db_engine.connect() publication_versions = get_table("publication_version") statement = select([publication_versions]).where( publication_versions.c.publication_id == int_or_none(publication_id)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def get_publications_in_group(project, group_id): """ List all publications in a given publication_group """ connection = db_engine.connect() publications = get_table("publication") statement = select([ publications.c.id, publications.c.name ]).where(publications.c.publication_group_id == int_or_none(group_id)) result = [] for row in connection.execute(statement).fetchall(): result.append(dict(row)) connection.close() return jsonify(result)
def edit_version(project, version_id): """ Takes "title", "filename", "published", "sort_order", "type" as JSON data "type" denotes version type, 1=base text, 2=other variant Returns "msg" and "version_id" on success, otherwise 40x """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 title = request_data.get("title", None) filename = request_data.get("filename", None) published = request_data.get("published", None) sort_order = request_data.get("sort_order", None) version_type = request_data.get("type", None) versions = get_table("publication_version") query = select([versions]).where(versions.c.id == int_or_none(version_id)) connection = db_engine.connect() result = connection.execute(query).fetchone() if result is None: connection.close() return jsonify("No such version exists."), 404 values = {} if title is not None: values["name"] = title if filename is not None: values["original_filename"] = filename if published is not None: values["published"] = published if sort_order is not None: values["sort_order"] = sort_order if version_type is not None: values["type"] = version_type values["date_modified"] = datetime.now() if len(values) > 0: update = versions.update().where(versions.c.id == int(version_id)).values(**values) connection.execute(update) connection.close() return jsonify({ "msg": "Updated version {} with values {}".format(int(version_id), str(values)), "manuscript_id": int(version_id) }) else: connection.close() return jsonify("No valid update values given."), 400
def get_publication_version(project, publication_id): """ Get a publication object from the database """ connection = db_engine.connect() publication_v = get_table("publication_version") statement = select([ publication_v ]).where(publication_v.c.publication_id == int_or_none(publication_id)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def get_event_connections(event_id): """ List all event_connections for a given event, to find related locations, subjects, and tags """ event_connections = get_table("event_connection") connection = db_engine.connect() statement = select([ event_connections ]).where(event_connections.c.event_id == int_or_none(event_id)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def get_event_occurrences(event_id): """ Get a list of all event_occurrence in the database, optionally limiting to a given event """ event_occurrences = get_table("event_occurrence") connection = db_engine.connect() statement = select([ event_occurrences ]).where(event_occurrences.c.event_id == int_or_none(event_id)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def delete_facsimile_collection_link(project, f_pub_id): """ List all publication_facsimile objects in the given publication_facsimile_collection """ connection = db_engine.connect() publication_facsimile = get_table("publication_facsimile") values = {} values['deleted'] = 1 values["date_modified"] = datetime.now() update = publication_facsimile.update().where( publication_facsimile.c.id == int(f_pub_id)).values(**values) connection.execute(update) connection.close() result = {"msg": "Deleted publication_facsimile"} return jsonify(result), 200
def list_facsimile_collection_links(project, collection_id): """ List all publication_facsimile objects in the given publication_facsimile_collection """ connection = db_engine.connect() facsimiles = get_table("publication_facsimile") statement = select( [facsimiles]).where(facsimiles.c.publication_facsimile_collection_id == int_or_none(collection_id)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def get_publication_collection_info(project, collection_id): """ Returns published status for publication_collection and associated introduction and title objects Also returns the original_filename for the introduction and title objects """ collections = get_table("publication_collection") intros = get_table("publication_collection_introduction") titles = get_table("publication_collection_title") query = select([collections]).where(collections.c.id == int_or_none(collection_id)) connection = db_engine.connect() collection_result = connection.execute(query).fetchone() if collection_result is None: connection.close() return jsonify("No such publication collection exists"), 404 intro_id = int_or_none(collection_result["publication_collection_introduction_id"]) title_id = int_or_none(collection_result["publication_collection_title_id"]) intro_query = select([intros.c.published, intros.c.original_filename]).where(intros.c.id == intro_id) title_query = select([titles.c.published, titles.c.original_filename]).where(titles.c.id == title_id) intro_result = connection.execute(intro_query).fetchone() title_result = connection.execute(title_query).fetchone() connection.close() result = { "collection_id": int(collection_id), "collection_published": collection_result["published"], "intro_id": intro_id, "intro_published": None if intro_result is None else intro_result["published"], "intro_original_filename": None if intro_result is None else intro_result["original_filename"], "title_id": title_id, "title_published": None if title_result is None else title_result["published"], "title_original_filename": None if title_result is None else title_result["original_filename"] } return jsonify(result)
def add_new_tag(project): """ Add a new tag object to the database POST data MUST be in JSON format. POST data SHOULD contain: type: tag type name: tag name POST data CAN also contain: description: tag description legacy_id: Legacy id for tag """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 tags = get_table("tag") connection = db_engine.connect() new_tag = { "type": request_data.get("type", None), "name": request_data.get("name", None), "project_id": get_project_id_from_name(project), "description": request_data.get("description", None), "legacy_id": request_data.get("legacy_id", None) } try: insert = tags.insert() result = connection.execute(insert, **new_tag) new_row = select([tags ]).where(tags.c.id == result.inserted_primary_key[0]) new_row = dict(connection.execute(new_row).fetchone()) result = { "msg": "Created new tag with ID {}".format( result.inserted_primary_key[0]), "row": new_row } return jsonify(result), 201 except Exception as e: result = {"msg": "Failed to create new tag", "reason": str(e)} return jsonify(result), 500 finally: connection.close()