def get_tag(project, tag_id): logger.info("Getting tag /{}/tag/{}".format(project, tag_id)) connection = db_engine.connect() project_id = get_project_id_from_name(project) # Check if tag_id is a number try: tag_id = int(tag_id) tag_sql = "SELECT * FROM tag WHERE id = :id AND deleted = 0 AND project_id = :p_id" except ValueError: tag_id = tag_id tag_sql = "SELECT * FROM tag WHERE id = :id AND deleted = 0 AND project_id = :p_id" statement = sqlalchemy.sql.text(tag_sql).bindparams(id=tag_id, p_id=project_id) return_data = connection.execute(statement).fetchone() if return_data is None: project_id = get_project_id_from_name(project) tag_sql = "SELECT * FROM tag WHERE legacy_id = :id AND deleted = 0 AND project_id = :p_id " statement = sqlalchemy.sql.text(tag_sql).bindparams(id=str(tag_id), p_id=project_id) return_data = connection.execute(statement).fetchone() connection.close() if return_data is None: return jsonify({"msg": "Desired tag not found in database."}), 404 else: return jsonify(dict(return_data)), 200 else: connection.close() return jsonify(dict(return_data)), 200
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 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_galleries(project, lang=None): logger.info("Getting galleries") try: connection = db_engine.connect() project_id = get_project_id_from_name(project) sql = sqlalchemy.sql.text("SELECT mc.*, count(m.id) AS media_count, \ (SELECT text \ FROM translation_text tt \ JOIN translation t ON t.id = tt.translation_id \ WHERE t.id = mc.title_translation_id AND tt.language = :l_id) AS title \ FROM media m\ JOIN media_collection mc ON m.media_collection_id = mc.id\ WHERE m.deleted != 1 AND mc.deleted != 1 AND mc.project_id = :p_id\ GROUP BY mc.id ORDER BY mc.sort_order ASC " ).bindparams(p_id=project_id, l_id=lang) results = [] for row in connection.execute(sql).fetchall(): results.append(dict(row)) connection.close() return jsonify(results), 200 except Exception: logger.exception("Failed to get galleries data.") return Response("Couldn't get galleries.", status=404, content_type="text/json")
def get_correspondence_metadata_for_publication(project, pub_id): logger.info("Getting results for /correspondence/manifestations/") connection = db_engine.connect() project_id = get_project_id_from_name(project) corresp_sql = """SELECT c.*, ec.type,s.full_name as full_name, s.id as subject_id from publication p join correspondence c on concat('Br', c.legacy_id) = substring(p.original_filename, 'Br[0-9]{1,5}') join event_connection ec on ec.correspondence_id = c.id join subject s on s.id = ec.subject_id where p.id = :pub_id and c.project_id = :p_id """ corresp_sql = text(corresp_sql).bindparams(pub_id=pub_id, p_id=project_id) corresp = [] subjects = [] for row in connection.execute(corresp_sql).fetchall(): subject = {} subject[row['type']] = row['full_name'] subject['id'] = row['subject_id'] subjects.append(dict(subject)) corresp.append(dict(row)) if not corresp: data = [] else: data = {'letter': dict(corresp[0]), 'subjects': subjects} connection.close() return jsonify(data), 200
def get_work_manifestation_occurrences_for_project(project, manifestation_id): logger.info( "Getting results for /workregister/manifestation/occurrences/<manifestation_id>" ) connection = db_engine.connect() project_id = get_project_id_from_name(project) # Only allow int if str(manifestation_id).isnumeric() is False: connection.close() return jsonify( {"msg": "Desired manifestation_id not found in database."}), 404 occurrences_sql = "SELECT p.* FROM work_manifestation w_m " \ "JOIN event_connection e_c ON e_c.work_manifestation_id = w_m.id " \ "JOIN event e ON e.id = e_c.event_id " \ "JOIN event_occurrence e_o ON e.id = e_o.event_id " \ "JOIN work_reference w_r ON w_r.work_manifestation_id = w_m.id " \ "JOIN publication p ON p.id = e_o.publication_id " \ "WHERE w_m.id = :mani_id " \ "AND e_c.deleted = 0 AND e.deleted = 0 AND w_r.deleted = 0 AND p.deleted = 0 AND w_m.deleted = 0 AND e_o.deleted = 0 " \ "AND w_r.project_id = :proj_id" occurrences_sql = text(occurrences_sql).bindparams( mani_id=manifestation_id, proj_id=project_id) occurrences = [] result = connection.execute(occurrences_sql) row = result.fetchone() while row is not None: occurrences.append(dict(row)) row = result.fetchone() connection.close() return jsonify(occurrences)
def get_gallery_data(project, gallery_id, lang=None): logger.info("Getting gallery image data") try: connection = db_engine.connect() project_id = get_project_id_from_name(project) sql = sqlalchemy.sql.text( """SELECT mc.id as collection_id, m.image_filename_front AS front, m.image_filename_back AS back, mc.image_path AS folder, (SELECT text FROM translation_text tt JOIN translation t ON t.id = tt.translation_id WHERE t.id = mc.title_translation_id AND tt.language = :lang) AS title, tt_desc.text AS description, (select text from translation_text where translation_id = m.title_translation_id and language = :lang) as media_title_translation, tt_desc.text AS description, (select full_name from subject where id in (select subject_id from media_connection where media_id = m.id ) limit 1) as subject_name FROM media m JOIN media_collection mc ON m.media_collection_id = mc.id JOIN translation t_desc ON t_desc.id = m.description_translation_id JOIN translation_text tt_desc ON tt_desc.translation_id = t_desc.id AND tt_desc.language=:lang WHERE mc.project_id = :p_id AND mc.id= :gallery_id AND m.type='image_ref' AND m.deleted != 1 """ ).bindparams(gallery_id=gallery_id, p_id=project_id, lang=lang) results = [] for row in connection.execute(sql).fetchall(): results.append(dict(row)) connection.close() return jsonify(results), 200 except Exception: logger.exception("Failed to get gallery image data.") return Response("Couldn't get gallery image data.", status=404, content_type="text/json")
def get_media_connections(project, connection_type, media_id): logger.info("Getting media connection data...") if connection_type not in ['tag', 'location', 'subject']: return Response("Couldn't get media connection data.", status=404, content_type="text/json") type_column = "{}_id".format(connection_type) try: project_id = get_project_id_from_name(project) connection = db_engine.connect() sql = sqlalchemy.sql.text(f"SELECT t.* FROM media_connection mcon \ JOIN {connection_type} t ON t.id = mcon.{type_column} \ JOIN media m ON m.id = mcon.media_id \ JOIN media_collection mcol ON mcol.id = m.media_collection_id \ WHERE m.id = :id \ AND t.project_id = :p_id \ AND mcol.deleted != 1 AND t.deleted != 1 AND m.deleted != 1 AND mcon.deleted != 1" ) statement = sql.bindparams(id=media_id, p_id=project_id) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results), 200 except Exception: logger.exception("Failed to get media connection data.") return Response("Couldn't get media connection data.", status=404, content_type="text/json")
def get_gallery_image(project, collection_id, file_name): logger.info("Getting galleries") try: project_id = get_project_id_from_name(project) config = get_project_config(project) connection = db_engine.connect() sql = sqlalchemy.sql.text( "SELECT image_path as image_path from media_collection WHERE project_id = :p_id AND id = :id " ).bindparams(p_id=project_id, id=collection_id) result = connection.execute(sql).fetchone() result = dict(result) connection.close() file_path = safe_join(config["file_root"], "media", str(result['image_path']), "{}".format(str(file_name))) try: output = io.BytesIO() with open(file_path, mode="rb") as img_file: output.write(img_file.read()) content = output.getvalue() output.close() return Response(content, status=200, content_type="image/jpeg") except Exception: logger.exception(f"Failed to read from image file at {file_path}") return Response("File not found: " + file_path, status=404, content_type="text/json") except Exception: logger.exception("Failed to parse gallery image request.") return Response("Couldn't get gallery file.", status=404, content_type="text/json")
def get_work_manifestation_authors_for_project(project, manifestation_id): logger.info( "Getting results for /workregister/manifestation/authors/<manifestation_id>" ) connection = db_engine.connect() project_id = get_project_id_from_name(project) # Only allow int if str(manifestation_id).isnumeric() is False: connection.close() return jsonify( {"msg": "Desired manifestation_id not found in database."}), 404 authors_sql = "SELECT s.* FROM work_manifestation w_m " \ "JOIN event_connection e_c ON e_c.work_manifestation_id = w_m.id " \ "JOIN subject s ON s.id = e_c.subject_id " \ "JOIN event e ON e.id = e_c.event_id " \ "JOIN work_reference w_r ON w_r.work_manifestation_id = w_m.id " \ "WHERE w_m.id = :mani_id " \ "AND e_c.deleted = 0 AND e.deleted = 0 AND w_r.deleted = 0 AND w_m.deleted = 0 AND s.deleted = 0 " \ "AND w_r.project_id = :proj_id " \ "ORDER BY s.last_name, s.full_name" authors_sql = text(authors_sql).bindparams(mani_id=manifestation_id, proj_id=project_id) authors = [] result = connection.execute(authors_sql) row = result.fetchone() while row is not None: authors.append(dict(row)) row = result.fetchone() connection.close() return jsonify(authors)
def list_facsimile_collections(project): """ List all publication_collection objects for a given project """ project_id = get_project_id_from_name(project) connection = db_engine.connect() statement = """ select * from publication_facsimile_collection where deleted != 1 AND ( id in ( select publication_facsimile_collection_id from publication_facsimile where publication_id in ( select id from publication where publication_collection_id in ( select id from publication_collection where project_id = :project_id and deleted != 1 ) ) ) or id not in ( select publication_facsimile_collection_id from publication_facsimile where publication_id in ( select id from publication where publication_collection_id in ( select id from publication_collection where deleted != 1 ) ) ) )""" statement = text(statement).bindparams(project_id=project_id) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
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 get_project_works(project): logger.info("Getting /<project>/works") connection = db_engine.connect() project_id = get_project_id_from_name(project) sql = sqlalchemy.sql.text("SELECT * FROM work WHERE project_id = :p_id") statement = sql.bindparams(p_id=project_id, ) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results)
def get_location_search(project, search_text): logger.info("Getting results from elastic") project_id = get_project_id_from_name(project) if len(search_text) > 0: res = es.search(index='location', body={ "size": 1000, "query": { "bool": { "should": [{ "match": { "name": { "query": str(search_text), "fuzziness": 1 } } }, { "match": { "city": { "query": str(search_text), "fuzziness": 1 } } }, { "match": { "country": { "query": str(search_text), "fuzziness": 1 } } }], "filter": { "term": { "project_id": project_id } }, "minimum_should_match": 1 } }, "highlight": { "fields": { "name": {}, "city": {}, "country": {} } } }) if len(res['hits']) > 0: return jsonify(res['hits']['hits']) else: return jsonify("") else: return jsonify("")
def get_project_subjects(project, language=None): logger.info("Getting /<project>/subjects") connection = db_engine.connect() project_id = get_project_id_from_name(project) if language is not None: query = """select s.id, s.date_created, s.date_modified, s.deleted, s.type, s.translation_id, s.legacy_id, s.date_born, s.date_deceased, s.project_id, s.source, COALESCE(t_fn.text, s.first_name) as first_name, COALESCE(t_ln.text, s.last_name) as last_name, COALESCE(t_plb.text, s.place_of_birth) as place_of_birth, COALESCE(t_occ.text, s.occupation) as occupation, COALESCE(t_prep.text, s.preposition) as preposition, COALESCE(t_fln.text, s.full_name) as full_name, COALESCE(t_desc.text, s.description) as description, COALESCE(t_alias.text, s.alias) as alias, COALESCE(t_prv.text, s.previous_last_name) as previous_last_name, COALESCE(t_alt.text, s.alternative_form) as alternative_form from subject s LEFT JOIN translation_text t_fn ON t_fn.translation_id = s.translation_id and t_fn.language=:lang and t_fn.field_name='first_name' LEFT JOIN translation_text t_ln ON t_ln.translation_id = s.translation_id and t_ln.language=:lang and t_ln.field_name='last_name' LEFT JOIN translation_text t_plb ON t_plb.translation_id = s.translation_id and t_plb.language=:lang and t_plb.field_name='place_of_birth' LEFT JOIN translation_text t_occ ON t_occ.translation_id = s.translation_id and t_occ.language=:lang and t_occ.field_name='occupation' LEFT JOIN translation_text t_prep ON t_prep.translation_id = s.translation_id and t_prep.language=:lang and t_prep.field_name='preposition' LEFT JOIN translation_text t_fln ON t_fn.translation_id = s.translation_id and t_fln.language=:lang and t_fln.field_name='full_name' LEFT JOIN translation_text t_desc ON t_desc.translation_id = s.translation_id and t_desc.language=:lang and t_desc.field_name='description' LEFT JOIN translation_text t_alias ON t_alias.translation_id = s.translation_id and t_alias.language=:lang and t_alias.field_name='alias' LEFT JOIN translation_text t_prv ON t_prv.translation_id = s.translation_id and t_prv.language=:lang and t_prv.field_name='previous_last_name' LEFT JOIN translation_text t_alt ON t_alt.translation_id = s.translation_id and t_alt.language=:lang and t_alt.field_name='alternative_form' WHERE project_id = :p_id """ sql = sqlalchemy.sql.text(query) statement = sql.bindparams(p_id=project_id, lang=language) else: sql = sqlalchemy.sql.text( "SELECT * FROM subject WHERE project_id = :p_id") statement = sql.bindparams(p_id=project_id) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results)
def get_collection_publication_by_legacyid(project, legacy_id): logger.info("Getting /<project>/legacy/<legacy_id>") connection = db_engine.connect() project_id = get_project_id_from_name(project) sql = sqlalchemy.sql.text( "SELECT p.id as pub_id, pc.id as coll_id " "FROM publication p " "JOIN publication_collection pc ON pc.id = p.publication_collection_id " "WHERE (p.legacy_id = :l_id OR pc.legacy_id = :l_id) AND pc.project_id = :p_id " "ORDER BY pc.id") statement = sql.bindparams(l_id=legacy_id, p_id=project_id) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results)
def get_work_manifestations_for_project(project): logger.info("Getting results for /workregister/manifestations/") connection = db_engine.connect() project_id = get_project_id_from_name(project) manifestation_sql = "SELECT json_data FROM get_manifestations_with_authors WHERE project_id = :proj_id" manifestation_sql = text(manifestation_sql).bindparams(proj_id=project_id) manifestations = [] result = connection.execute(manifestation_sql) row = result.fetchone() while row is not None: manifestations.append(dict(row)) row = result.fetchone() connection.close() return jsonify(manifestations)
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()
def get_tooltip(table, row_id, project=None, use_legacy=False): """ Get 'tooltip' style info for a single subject, tag, or location by its ID table should be 'subject', 'tag', or 'location' """ connection = db_engine.connect() try: ident = int(row_id) is_legacy_id = False except ValueError: ident = str(row_id) is_legacy_id = True if use_legacy: ident = str(row_id) is_legacy_id = True project_sql = " AND project_id = :project_id " if project is None: project_sql = "" if is_legacy_id: if table == "subject": stmnt = f"SELECT id, legacy_id, full_name, description FROM subject WHERE legacy_id=:id{project_sql}" else: stmnt = f"SELECT id, legacy_id, name, description FROM {table} WHERE legacy_id=:id{project_sql}" else: if table == "subject": stmnt = f"SELECT id, legacy_id, full_name, description FROM subject WHERE id=:id{project_sql}" else: stmnt = f"SELECT id, legacy_id, name, description FROM {table} WHERE id=:id{project_sql}" sql = sqlalchemy.sql.text(stmnt) if project is None: statement = sql.bindparams(id=ident) else: project_id = get_project_id_from_name(project) statement = sql.bindparams(id=ident, project_id=project_id) result = connection.execute(statement).fetchone() connection.close() if result is None: return dict() else: return dict(result)
def get_project_publication_facsimile_relations(project): logger.info("Getting publication relations for {}".format(project)) connection = db_engine.connect() project_id = get_project_id_from_name(project) sql = sqlalchemy.sql.text( "SELECT pc.id as pc_id, p.id as p_id, pf.id as pf_id,\ pc.name as pc_name, p.name as p_name, pf.page_nr FROM publication_collection pc \ JOIN publication p ON p.publication_collection_id=pc.id \ JOIN publication_facsimile pf ON pf.publication_id = p.id \ WHERE p.deleted != 1 AND pf.deleted != 1 AND pc.deleted != 1 AND project_id=:p_id ORDER BY pc.id" ) statement = sql.bindparams(p_id=project_id) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results)
def get_project_galleries(project): logger.info("Getting project galleries...") try: project_id = get_project_id_from_name(project) connection = db_engine.connect() sql = sqlalchemy.sql.text( "SELECT * FROM media_collection WHERE project_id = :p_id" ).bindparams(p_id=project_id) results = [] for row in connection.execute(sql).fetchall(): results.append(dict(row)) connection.close() return jsonify(results), 200 except Exception: logger.exception("Failed to get galleries list from database.") return Response("Failed to get galleries list from database.", status=404, content_type="text/json")
def get_urn(project, url, legacy_id=None): url = unquote(unquote(url)) logger.info("Getting urn /{}/urn/{}/{}/".format(project, url, legacy_id)) project_id = get_project_id_from_name(project) connection = db_engine.connect() if legacy_id is not None: stmnt = "SELECT * FROM urn_lookup where legacy_id=:l_id AND project_id=:p_id" sql = sqlalchemy.sql.text(stmnt).bindparams(l_id=str(legacy_id), p_id=project_id) else: url_like_str = "%#{}".format(url) stmnt = "SELECT * FROM urn_lookup where url LIKE :url AND project_id=:p_id" sql = sqlalchemy.sql.text(stmnt).bindparams(url=url_like_str, p_id=project_id) return_data = [] for row in connection.execute(sql).fetchall(): return_data.append(dict(row)) connection.close() return jsonify(return_data), 200
def get_collections(project): config = get_project_config(project) if config is None: return jsonify({"msg": "No such project."}), 400 else: logger.info("Getting collections /{}/collections".format(project)) connection = db_engine.connect() status = 1 if config["show_internally_published"] else 2 project_id = get_project_id_from_name(project) sql = sqlalchemy.sql.text( """ SELECT id, name as title, published, date_created, date_modified, date_published_externally, legacy_id, project_id, publication_collection_title_id, publication_collection_introduction_id, name FROM publication_collection WHERE project_id = :p_id AND published>=:p_status ORDER BY name """ ) statement = sql.bindparams(p_status=status, p_id=project_id) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results)
def get_type_gallery_image(project, connection_type, connection_id): logger.info("Getting gallery file") if connection_type not in ['tag', 'location', 'subject']: return Response("Couldn't get media connection data.", status=404, content_type="text/json") type_column = "{}_id".format(connection_type) try: project_id = get_project_id_from_name(project) config = get_project_config(project) connection = db_engine.connect() sql = f"SELECT mcol.image_path, m.image_filename_front FROM media_connection mcon " \ f"JOIN {connection_type} t ON t.id = mcon.{type_column} " \ f"JOIN media m ON m.id = mcon.media_id " \ f"JOIN media_collection mcol ON mcol.id = m.media_collection_id " \ f"WHERE t.id = :id " \ f"AND t.project_id = :p_id " \ f"AND mcol.deleted != 1 AND t.deleted != 1 AND m.deleted != 1 AND mcon.deleted != 1 LIMIT 1" sql = sqlalchemy.sql.text(sql).bindparams(p_id=project_id, id=connection_id) result = connection.execute(sql).fetchone() result = dict(result) connection.close() file_path = safe_join( config["file_root"], "media", str(result['image_path']), str(result['image_filename_front']).replace(".jpg", "_thumb.jpg")) try: output = io.BytesIO() with open(file_path, mode="rb") as img_file: output.write(img_file.read()) content = output.getvalue() output.close() return Response(content, status=200, content_type="image/jpeg") except Exception: logger.exception(f"Failed to read from image file at {file_path}") return Response("File not found: " + file_path, status=404, content_type="text/json") except Exception: logger.exception("Failed to parse gallery image request.") return Response("Couldn't get type file.", status=404, content_type="text/json")
def get_project_locations(project): logger.info("Getting /<project>/locations") connection = db_engine.connect() project_id = get_project_id_from_name(project) # Get both locations and their translations sql = sqlalchemy.sql.text(""" SELECT *, ( SELECT array_to_json(array_agg(row_to_json(d.*))) AS array_to_json FROM ( SELECT tt.id, tt.text, tt."language", t.neutral_text, tt.field_name, tt.table_name, t.id as translation_id, tt.date_modified, tt.date_created FROM (translation t JOIN translation_text tt ON ((tt.translation_id = t.id))) WHERE ((t.id = l.translation_id AND tt.table_name = 'location') AND tt.deleted = 0 AND t.deleted = 0) ORDER BY translation_id DESC) d) AS translations FROM location l WHERE l.project_id = :p_id AND l.deleted = 0 ORDER BY NAME ASC """ ) statement = sql.bindparams(p_id=project_id, ) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results)
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 get_type_gallery_connections(project, connection_type, type_id, limit=None): logger.info("Getting type gallery connection data...") if connection_type not in ['tag', 'location', 'subject']: return Response("Couldn't get gallery type connection data.", status=404, content_type="text/json") if limit is not None: limit = " LIMIT 1 " else: limit = "" type_column = "{}_id".format(connection_type) try: project_id = get_project_id_from_name(project) connection = db_engine.connect() sql = sqlalchemy.sql.text( f"SELECT t.id as t_id, m.id as media_id, m.image_filename_front as filename,\ mcol.id as media_collection_id, mcol.image_path, t.* FROM media_connection mcon \ JOIN {connection_type} t ON t.id = mcon.{type_column} \ JOIN media m ON m.id = mcon.media_id \ JOIN media_collection mcol ON mcol.id = m.media_collection_id \ WHERE t.id = :id \ AND t.project_id = :p_id \ AND mcol.deleted != 1 AND t.deleted != 1 AND m.deleted != 1 AND mcon.deleted != 1 {limit}" ) statement = sql.bindparams(id=type_id, p_id=project_id) results = [] for row in connection.execute(statement).fetchall(): results.append(dict(row)) connection.close() return jsonify(results), 200 except Exception: logger.exception("Failed to get type gallery connection data.") return Response("Couldn't get type gallery connection data.", status=404, content_type="text/json")
def list_publication_collections(project): """ List all publication_collection objects for a given project """ project_id = get_project_id_from_name(project) connection = db_engine.connect() # collections = get_table("publication_collection") statement = """ SELECT pc.id, pc.name as title, pc.published, pc.date_created, pc.date_modified, pc.date_published_externally, pc.legacy_id, pc.project_id, pc.publication_collection_title_id, pc.publication_collection_introduction_id, pc.name, pct.original_filename AS collection_title_filename, pci.original_filename AS collection_intro_filename, pct.published AS collection_title_published, pci.published AS collection_intro_published FROM publication_collection pc LEFT JOIN publication_collection_title pct ON pct.id = pc.publication_collection_title_id LEFT JOIN publication_collection_introduction pci ON pci.id = pc.publication_collection_introduction_id WHERE pc.project_id=:project_id AND pc.published>=1 ORDER BY pc.id """ statement = text(statement).bindparams(project_id=int_or_none(project_id)) # statement = select([collections]).where(collections.c.project_id == int_or_none(project_id)) rows = connection.execute(statement).fetchall() result = [] for row in rows: result.append(dict(row)) connection.close() return jsonify(result)
def new_publication_collection(project): """ Create a new publication_collection object and associated Introduction and Title objects. POST data MUST be in JSON format POST data SHOULD contain the following: name: publication collection name or title datePublishedExternally: date of external publishing for collection published: 0 or 1, is collection published or not POST data MAY also contain the following intro_legacyID: legacy ID for publication_collection_introduction title_legacyID: legacy ID for publication_collection_title """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 collections = get_table("publication_collection") introductions = get_table("publication_collection_introduction") titles = get_table("publication_collection_title") connection = db_engine.connect() transaction = connection.begin() try: new_intro = { "date_published_externally": request_data.get("datePublishedExternally", None), "published": request_data.get("published", None), "legacy_id": request_data.get("intro_legacyID", None) } new_title = { "date_published_externally": request_data.get("datePublishedExternally", None), "published": request_data.get("published", None), "legacy_id": request_data.get("title_legacyID", None) } ins = introductions.insert() result = connection.execute(ins, **new_intro) new_intro_row = select([ introductions ]).where(introductions.c.id == result.inserted_primary_key[0]) new_intro_row = dict(connection.execute(new_intro_row).fetchone()) ins = titles.insert() result = connection.execute(ins, **new_title) new_title_row = select( [titles]).where(titles.c.id == result.inserted_primary_key[0]) new_title_row = dict(connection.execute(new_title_row).fetchone()) new_collection = { "project_id": get_project_id_from_name(project), "name": request_data.get("name", None), "date_published_externally": request_data.get("datePublishedExternally", None), "published": request_data.get("published", None), "publication_collection_introduction_id": new_intro_row["id"], "publication_collection_title_id": new_title_row["id"] } ins = collections.insert() result = connection.execute(ins, **new_collection) new_collection_row = select([ collections ]).where(collections.c.id == result.inserted_primary_key[0]) new_collection_row = dict( connection.execute(new_collection_row).fetchone()) transaction.commit() return jsonify({ "msg": "New publication_collection created.", "new_collection": new_collection_row, "new_collection_intro": new_intro_row, "new_collection_title": new_title_row }), 201 except Exception as e: transaction.rollback() result = { "msg": "Failed to create new publication_collection object", "reason": str(e) } return jsonify(result), 500 finally: connection.close()
def new_publication(project, collection_id): """ Create a new publication object as part of the given publication_collection POST data MUST be in JSON format. POST data SHOULD contain the following: name: publication name POST data MAY also contain the following: publicationComment_id: ID for related publicationComment object datePublishedExternally: date of external publication for publication published: publish status for publication legacyId: legacy ID for publication publishedBy: person responsible for publishing the publication originalFilename: filepath to publication XML file genre: Genre for this publication publicationGroup_id: ID for related publicationGroup, used to group publications for easy publishing of large numbers of publications originalPublicationDate: Date of original publication for physical equivalent """ request_data = request.get_json() if not request_data: return jsonify({"msg": "No data provided."}), 400 project_id = get_project_id_from_name(project) connection = db_engine.connect() collections = get_table("publication_collection") publications = get_table("publication") statement = select([ collections.c.project_id ]).where(collections.c.id == int_or_none(collection_id)) result = connection.execute(statement).fetchall() if len(result) != 1: return jsonify({"msg": "publication_collection not found."}), 404 if result[0]["project_id"] != project_id: return jsonify({ "msg": "publication_collection {} does not belong to project {!r}".format( collection_id, project) }), 400 insert = publications.insert() publication = { "name": request_data.get("name", None), "publication_comment_id": request_data.get("publicationComment_id", None), "date_published_externally": request_data.get("datePublishedExternally", None), "published": request_data.get("published", None), "legacy_id": request_data.get("legacyId", None), "published_by": request_data.get("publishedBy", None), "original_filename": request_data.get("originalFileName", None), "genre": request_data.get("genre", None), "publication_group_id": request_data.get("publicationGroup_id", None), "original_publication_date": request_data.get("originalPublicationDate", None), "publication_collection_id": int_or_none(collection_id) } try: result = connection.execute(insert, **publication) new_row = select([ publications ]).where(publications.c.id == result.inserted_primary_key[0]) new_row = dict(connection.execute(new_row).fetchone()) result = { "msg": "Created new publication 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", "reason": str(e)} return jsonify(result), 500 finally: connection.close()