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()
