def get_work_manifestation_occurrences(project=None,
work_manifestation_id=None):
work_sql = """ SELECT id, title \
FROM work_manifestation WHERE deleted != 1 """
if work_manifestation_id is not None:
work_sql = work_sql + " AND id = :work_manifestation_id "
statement = sqlalchemy.sql.text(work_sql).bindparams(
work_manifestation_id=work_manifestation_id)
else:
statement = sqlalchemy.sql.text(work_sql)
connection = db_engine.connect()
work_manifestations = []
result = connection.execute(statement)
work_manifestation = result.fetchone()
while work_manifestation is not None:
work_manifestation = dict(work_manifestations)
occurrence_sql = "SELECT \
pub_c.name as collection_name, pub_c.id as collection_id, ev.description, ev.id, ev_o.publication_comment_id, \
publication_facsimile_id, publication_facsimile_page, \
publication_manuscript_id, publication_version_id, ev.type, \
pub.id as publication_id, pub.name as publication_name, pub.original_filename as original_filename, \
ev_o.publication_song_id as publication_song_id, \
ev_c.id as ev_c_id \
FROM event_connection ev_c \
JOIN event ev ON ev.id = ev_c.event_id \
JOIN event_occurrence ev_o ON ev_o.event_id = ev_c.event_id \
JOIN publication pub ON pub.id = ev_o.publication_id \
JOIN publication_collection pub_c ON pub_c.id = pub.publication_collection_id \
JOIN work_manifestation ON ev_c.work_manifestation_id = work_manifestation.id \
WHERE ev.deleted != 1 AND ev_o.deleted != 1 AND ev_c.deleted != 1 AND work_manifestation.id = :work_manifestation_id ORDER BY pub_c.name ASC"
statement_occ = sqlalchemy.sql.text(occurrence_sql).bindparams(
work_manifestation=work_manifestation['id'])
work_manifestation['occurrences'] = []
connection_2 = db_engine.connect()
result_2 = connection_2.execute(statement_occ)
occurrence = result_2.fetchone()
while occurrence is not None:
occurrenceData = dict(occurrence)
work_manifestation['occurrences'].append(occurrenceData)
occurrence = result_2.fetchone()
if len(work_manifestation['occurrences']) > 0:
work_manifestations.append(work_manifestation)
connection_2.close()
work_manifestation = result.fetchone()
connection.close()
return jsonify(work_manifestations)
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 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 get_facsimile_pages(project, col_pub, section_id=None):
logger.info("Getting facsimile page")
try:
pub_id = col_pub.split('_')[1]
connection = db_engine.connect()
sql = sqlalchemy.sql.text(
"SELECT pf.*, pf.page_nr as page_number, pfc.number_of_pages, pfc.start_page_number, pfc.id as collection_id\
FROM publication_facsimile pf\
JOIN publication_facsimile_collection pfc on pfc.id = pf.publication_facsimile_collection_id\
WHERE pf.deleted != 1 AND pfc.deleted != 1 AND pf.publication_id = :pub_id"
)
if section_id is not None:
section_id = str(section_id).replace('ch', '')
sql = " ".join([sql, "and pf.section_id = :section"])
statement = sql.bindparams(pub_id=pub_id, section=section_id)
else:
statement = sql.bindparams(pub_id=pub_id)
result = connection.execute(statement).fetchone()
facs = dict(result)
connection.close()
return jsonify(facs), 200
except Exception:
logger.exception(
"Exception while getting facsimile page from database")
return Response("Couldn't get facsimile page.",
status=404,
content_type="text/json")
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_media_image_metadata(project, media_id, lang):
logger.info("Getting media metadata...")
try:
connection = db_engine.connect()
sql = sqlalchemy.sql.text("""SELECT
(select text from translation_text where translation_id = m.description_translation_id and language = :lang) as media_description_translation,
(select text from translation_text where translation_id = m.title_translation_id and language = :lang) as media_title_translation,
(select text from translation_text where translation_id = mc.title_translation_id and language = :lang) as media_collection_title_translation,
(select text from translation_text where translation_id = mc.description_translation_id and language = :lang) as media_collection_description_translation,
(select text from translation_text where translation_id = l.name_translation_id and language = :lang) as location_name_translation,
(select text from translation_text where translation_id = m.art_technique_translation_id and language = :lang) as media_art_technique_translation,
m.*,
mc.image_path,
s.full_name, s.description as subject_description, s.date_born, s.id as subject_id, s.date_deceased,
l.name as location_name, l.country as location_country, l.description as location_description
FROM media m
JOIN media_collection mc ON mc.id = m.media_collection_id
JOIN media_connection mcon ON mcon.media_id = m.id
LEFT JOIN location l ON l.id = mcon.location_id
LEFT JOIN subject s ON s.id = mcon.subject_id
WHERE m.id = :id or m.legacy_id = :id"""
).bindparams(id=media_id, lang=lang)
result = connection.execute(sql).fetchone()
connection.close()
return jsonify(dict(result)), 200
except Exception:
logger.exception("Failed to get media metadata from database.")
return Response("Couldn't get media metadata.",
status=404,
content_type="text/json")
def get_person_occurrences_by_collection(project, object_type, collection_id):
connection = db_engine.connect()
occurrence_sql = "SELECT publication.publication_collection_id AS collection_id, event_occurrence.id, event_occurrence.event_id, \
type, description, event_occurrence.publication_id, event_occurrence.publication_version_id, event_occurrence.publication_facsimile_id, \
event_occurrence.publication_comment_id, event_occurrence.publication_manuscript_id FROM event_occurrence, publication \
WHERE event_occurrence.publication_id=publication.id AND publication.publication_collection_id={} AND \
event_occurrence.type='{}'".format(collection_id, object_type)
occurrences = []
result = connection.execute(occurrence_sql)
row = result.fetchone()
while row is not None:
occurrences.append(dict(row))
row = result.fetchone()
subjects = []
for occurrence in occurrences:
subject_sql = "SELECT DISTINCT event_connection.subject_id, subject.full_name, subject.legacy_id, subject.project_id \
FROM event_occurrence, event_connection, subject \
WHERE event_occurrence.event_id={} AND event_occurrence.event_id = event_connection.event_id AND \
event_connection.subject_id = subject.id".format(
occurrence["event_id"])
result = connection.execute(subject_sql)
row = result.fetchone()
while row is not None:
subjects.append(dict(row))
row = result.fetchone()
connection.close()
return jsonify(subjects)
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_reading_text(project,
collection_id,
publication_id,
section_id=None,
language=None):
"""
Get reading text for a given publication
"""
can_show, message = get_published_status(project, collection_id,
publication_id)
if can_show:
logger.info("Getting XML for {} and transforming...".format(
request.full_path))
connection = db_engine.connect()
select = "SELECT legacy_id FROM publication WHERE id = :p_id AND original_filename IS NULL"
statement = sqlalchemy.sql.text(select).bindparams(p_id=publication_id)
result = connection.execute(statement).fetchone()
if result is None or language is not None:
filename = "{}_{}_est.xml".format(collection_id, publication_id)
if language is not None:
filename = "{}_{}_{}_est.xml".format(collection_id,
publication_id, language)
logger.debug("Filename (est) for {} is {}".format(
publication_id, filename))
connection.close()
else:
filename = "{}_est.xml".format(result["legacy_id"])
connection.close()
logger.debug("Filename (est) for {} is {}".format(
publication_id, filename))
xsl_file = "est.xsl"
bookId = get_collection_legacy_id(collection_id)
if bookId is None:
bookId = collection_id
bookId = '"{}"'.format(bookId)
if section_id is not None:
section_id = '"{}"'.format(section_id)
content = get_content(project, "est", filename, xsl_file, {
"bookId": bookId,
"sectionId": section_id
})
else:
content = get_content(project, "est", filename, xsl_file,
{"bookId": bookId})
data = {
# @TODO: investigate if id should have language in its value or not (similar to filename).
"id": "{}_{}_est".format(collection_id, publication_id),
"content": content.replace(" id=", " data-id=")
}
if language is not None:
data["language"] = language
return jsonify(data), 200
else:
return jsonify({
"id": "{}_{}".format(collection_id, publication_id),
"error": message
}), 403
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 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 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 get_location(project, location_id):
logger.info("Getting location /{}/location/{}".format(
project, location_id))
connection = db_engine.connect()
project_id = get_project_id_from_name(project)
# Check if location_id is a number
try:
location_id = int(location_id)
location_sql = "SELECT * FROM location WHERE id = :id AND deleted = 0 AND project_id = :p_id "
except ValueError:
location_id = location_id
location_sql = "SELECT * FROM location WHERE legacy_id = :id AND deleted = 0 AND project_id = :p_id "
statement = sqlalchemy.sql.text(location_sql).bindparams(id=location_id,
p_id=project_id)
return_data = connection.execute(statement).fetchone()
if return_data is None:
location_sql = "SELECT * FROM location WHERE legacy_id = :id AND deleted = 0 AND project_id = :p_id "
statement = sqlalchemy.sql.text(location_sql).bindparams(
id=str(location_id), p_id=project_id)
return_data = connection.execute(statement).fetchone()
connection.close()
if return_data is None:
return jsonify({"msg":
"Desired location 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 get_publication_song(project, song_id):
logger.info("Getting songs /{}/song/{}".format(project, song_id))
connection = db_engine.connect()
song_sql = "SELECT \
ps.volume as song_volume, ps.id as song_id, ps.original_id as song_original_id, ps.name as song_name, ps.type as song_type, number as song_number, \
variant as song_variant, landscape as song_landscape, place as song_place, recorder_firstname as song_recorder_firstname, \
recorder_lastname as song_recorder_lastname, recorder_born_name as song_recorder_born_name, performer_firstname as song_performer_firstname,\
performer_lastname as song_performer_lastname, performer_born_name as song_performer_born_name, \
original_collection_location as song_original_collection_location, \
original_collection_signature as song_original_collection_signature,\
ps.original_publication_date as song_original_publication_date, page_number as song_page_number, subtype as song_subtype, \
ps.note as song_note, ps.comment as song_comment, ps.lyrics as song_lyrics \
FROM publication_song ps "
# Check if song_id is a number
try:
song_id = int(song_id)
song_sql = song_sql + " WHERE ps.id = :song_id "
except ValueError:
song_id = song_id
song_sql = song_sql + " WHERE ps.original_id = :song_id "
statement = sqlalchemy.sql.text(song_sql).bindparams(song_id=song_id)
return_data = connection.execute(statement).fetchone()
connection.close()
if return_data is None:
return jsonify({"msg": "Desired song not found in database."}), 404
else:
return jsonify(dict(return_data)), 200
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 get_work_manifestations_by_author(project, author_id):
logger.info(
"Getting results for /workregister/author/manifestations/<author_id>")
connection = db_engine.connect()
# Only allow int
if str(author_id).isnumeric() is False:
connection.close()
return jsonify({"msg":
"Desired author_id not found in database."}), 404
sql = "SELECT w_m.*, w_r.project_id 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 s.id = :author_id "\
"AND e_c.deleted = 0 AND e.deleted = 0 AND w_r.deleted = 0 AND w_m.deleted = 0 AND s.deleted = 0 "\
"ORDER BY w_m.title"
sql = text(sql).bindparams(author_id=author_id)
data = []
result = connection.execute(sql)
row = result.fetchone()
while row is not None:
data.append(dict(row))
row = result.fetchone()
connection.close()
return jsonify(data)
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 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 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_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 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 get_variant(project, collection_id, publication_id, section_id=None):
"""
Get all variants for a given publication, optionally specifying a section (chapter)
"""
can_show, message = get_published_status(project, collection_id,
publication_id)
if can_show:
logger.info("Getting XML for {} and transforming...".format(
request.full_path))
connection = db_engine.connect()
select = "SELECT sort_order, name, type, legacy_id, id, original_filename FROM publication_version WHERE publication_id = :p_id ORDER BY type, sort_order ASC"
statement = sqlalchemy.sql.text(select).bindparams(p_id=publication_id)
variation_info = []
for row in connection.execute(statement).fetchall():
variation_info.append(dict(row))
connection.close()
bookId = get_collection_legacy_id(collection_id)
if bookId is None:
bookId = collection_id
bookId = '"{}"'.format(bookId)
if section_id is not None:
section_id = '"{}"'.format(section_id)
params = {"bookId": bookId, "sectionId": str(section_id)}
else:
params = {"bookId": bookId}
for index in range(len(variation_info)):
variation = variation_info[index]
if variation["type"] == 1:
xsl_file = "poem_variants_est.xsl"
else:
xsl_file = "poem_variants_other.xsl"
if variation["original_filename"] is None and variation[
"legacy_id"] is not None:
filename = "{}.xml".format(variation["legacy_id"])
else:
filename = "{}_{}_var_{}.xml".format(collection_id,
publication_id,
variation["id"])
variation_info[index]["content"] = get_content(
project, "var", filename, xsl_file, params)
data = {
"id": "{}_{}_var".format(collection_id, publication_id),
"variations": variation_info
}
return jsonify(data), 200
else:
return jsonify({
"id": "{}_{}".format(collection_id, publication_id),
"error": message
}), 403
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 get_letter_info(letter_id):
if letter_id is None:
return []
connection = db_engine.connect()
statement = text("SELECT c.id, c.title from correspondence c \
where c.legacy_id = :letter_id ")
data = connection.execute(statement, letter_id=letter_id).fetchone()
connection.close()
return data
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 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 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 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)