SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(thesauri_id) as thesauri_id_short, (SELECT item_name from thesauri as t2 where t2.thesauri_id = thesauri.parent ) as parent_item_name, (SELECT item_name from thesauri as t2 where t2.thesauri_id = thesauri.thesaurus ) as thesaurus_item_name, (CASE thesaurus WHEN 0 THEN 'https://pnm.uni-mainz.de/ontology#thesaurus' WHEN 1 THEN 'http://www.cidoc-crm.org/cidoc-crm/E55_Type' WHEN 2 THEN 'http://www.cidoc-crm.org/cidoc-crm/E55_Type' WHEN 3 THEN 'http://www.cidoc-crm.org/cidoc-crm/E57_Material' WHEN 4 THEN 'https://pnm.uni-mainz.de/ontology#text_content' WHEN 5 THEN 'http://www.cidoc-crm.org/cidoc-crm/E4_Period' WHEN 6 THEN 'http://www.cidoc-crm.org/cidoc-crm/E4_Period' WHEN 7 THEN 'http://www.cidoc-crm.org/cidoc-crm/E55_Type' WHEN 8 THEN 'http://www.cidoc-crm.org/cidoc-crm/E55_Type' WHEN 9 THEN 'https://pnm.uni-mainz.de/ontology#relative_location' WHEN 10 THEN 'http://www.cidoc-crm.org/cidoc-crm/E55_Type' WHEN 11 THEN 'https://pnm.uni-mainz.de/ontology#bond_type' WHEN 12 THEN 'https://pnm.uni-mainz.de/ontology#script' END) as class, CASE WHEN thesaurus=0 THEN 'http://www.w3.org/2004/02/skos/core#ConceptScheme' ELSE 'http://www.w3.org/2004/02/skos/core#Concept' END as skos, (CASE thesaurus WHEN 1 THEN 'http://www.cidoc-crm.org/cidoc-crm/P127_has_broader_term' WHEN 2 THEN 'http://www.cidoc-crm.org/cidoc-crm/P127_has_broader_term' WHEN 5 THEN 'http://www.cidoc-crm.org/cidoc-crm/P9i_forms_part_of' WHEN 6 THEN 'http://www.cidoc-crm.org/cidoc-crm/P9i_forms_part_of' END) as parent_class, CASE WHEN external_key LIKE 'thot%' THEN CONCAT('http://thot.philo.ulg.ac.be/concept/', external_key) ELSE external_key END as external_key_prep FROM thesauri
SELECT places_id, wgs84_latitude, wgs84_longitude, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(places_id) as places_id_short FROM places WHERE wgs84_latitude > 0 AND wgs84_longitude > 0
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(places_id) as places_id_short FROM places
SELECT objects_id, record_id_from_id(objects_id) as objects_id_short, topbib_id FROM objects where NOT ISNULL(topbib_id)
SELECT record_id_from_id(objects_id) as objects_id_short, production_place, production_place_note FROM objects where NOT ISNULL(production_place) OR EXISTS(SELECT objects_id FROM inscriptions_workshops_xref where inscriptions_workshops_xref.objects_id = objects.objects_id)
SELECT record_id_from_id(objects_id) as objects_id_short, length as dimension FROM objects where NOT ISNULL(length) AND length > 0
SELECT record_id_from_id(objects_id) as objects_id_short, height as dimension FROM objects where NOT ISNULL(height) AND height > 0
SELECT record_id_from_id(objects_id) as objects_id_short, width as dimension FROM objects where NOT ISNULL(width) AND width > 0
SELECT record_id_from_id(objects_id) as objects_id_short, thickness as dimension FROM objects where NOT ISNULL(thickness) AND thickness > 0
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(objects_id) as objects_id_short, record_id_from_id(find_groups_id) as find_groups_id_short FROM objects
SELECT inscriptions_id, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(inscriptions_id) as inscriptions_id_short, record_id_from_id(objects_id) as objects_id_short FROM objects_inscriptions_xref
SELECT inscriptions_id, record_id_from_id(inscriptions_id) as inscriptions_id_short, dating, dating_note FROM inscriptions where NOT ISNULL(dating)
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(inscriptions_id) as inscriptions_id_short FROM inscriptions
SELECT record_id_from_id(find_groups_id) as find_groups_id_short, dating, dating_note FROM find_groups where NOT ISNULL(dating)
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(find_groups_id) as find_groups_id_short FROM find_groups
SELECT *,record_id_from_id(objects_id) as objects_id_short, record_id_from_id(workshops_id) as workshops_id_short, IF (note>'', CONCAT('. Note: ', note), note) as note_prep from inscriptions_workshops_xref
Production by <https://pnm.uni-mainz.de/workshop/{workshops_id_short}>. Status: {status}{note_prep}
SELECT dating, dating_note, record_id_from_id(workshops_id) as workshops_id_short FROM workshops where not isnull(dating)
SELECT title, workshops_id, production_place, production_place_note, note, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(workshops_id) as workshops_id_short FROM workshops
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(publications_id) as publications_id_short FROM publications
SELECT *, record_id_from_id(inv_nos_id) as inv_nos_id_short, record_id_from_id(collections_id) as collections_id_short, record_id_from_id(objects_id) as objects_id_short FROM inv_nos
SELECT distinct collections_id , record_id_from_id(objects_id) as objects_id_short, (CASE status WHEN 'main' THEN 'http://www.cidoc-crm.org/cidoc-crm/P52_has_current_owner' WHEN 'alternative' THEN 'http://www.cidoc-crm.org/cidoc-crm/P52_has_current_owner' WHEN 'obsolete' THEN 'http://www.cidoc-crm.org/cidoc-crm/P51_has_former_or_current_owner' WHEN 'erroneous' THEN 'http://www.cidoc-crm.org/cidoc-crm/P51_has_former_or_current_owner' END) as status_class FROM inv_nos
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(inv_nos_id) as inv_nos_id_short, record_id_from_id(objects_id) as objects_id_short, (CASE status WHEN 'main' THEN 'http://www.cidoc-crm.org/cidoc-crm/P48i_is_preferred_identifier_of' WHEN 'alternative' THEN 'http://www.cidoc-crm.org/cidoc-crm/P1i_identifies' WHEN 'obsolete' THEN 'http://www.cidoc-crm.org/cidoc-crm/P1i_identifies' WHEN 'erroneous' THEN 'http://www.cidoc-crm.org/cidoc-crm/P1i_identifies' END) as status_class, (CASE status WHEN 'main' THEN 'https://pnm.uni-mainz.de/ontology#main_inv_no' WHEN 'alternative' THEN 'https://pnm.uni-mainz.de/ontology#alternative_inv_no' WHEN 'obsolete' THEN 'https://pnm.uni-mainz.de/ontology#obsolete_inv_no' WHEN 'erroneous' THEN 'https://pnm.uni-mainz.de/ontology#erroneous_inv_no' END) as status_iri FROM inv_nos
SELECT url, record_id_from_id(collections_id) as collections_id_short FROM collections WHERE NOT ISNULL (url)
SELECT online_collection, record_id_from_id(collections_id) as collections_id_short FROM collections WHERE NOT ISNULL (url)
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(collections_id) as collections_id_short FROM collections
SELECT *, (CASE WHEN (representation = 'human figure') THEN 'https://pnm.uni-mainz.de/ontology#human_figure' WHEN (representation = 'not represented') THEN 'https://pnm.uni-mainz.de/ontology#not_represented' ELSE NULL END) as visual_representation, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(attestations_id) as attestations_id_short, record_id_from_id(inscriptions_id) as inscriptions_id_short FROM attestations
SELECT attestations.attestations_id as attestations_id, persons_id, record_id_from_id(persons_attestations_xref.persons_id) as persons_id_short, persons_attestations_xref.reasoning as reasoning, persons_attestations_xref.note as note, CAST(persons_attestations_xref.date_created AS DATETIME) AS date_created_dt, CAST(persons_attestations_xref.date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(persons_attestations_xref.attestations_id) as attestations_id_short, record_id_from_id(attestations.inscriptions_id) as inscriptions_id_short, record_id_from_id(persons_attestations_xref_id) as persons_attestations_xref_id_id_short, (CASE persons_attestations_xref.status WHEN 'accepted' THEN 'https://pnm.uni-mainz.de/ontology#identification_accepted' WHEN 'weak' THEN 'https://pnm.uni-mainz.de/ontology#identification_is_weak' WHEN 'rejected' THEN 'https://pnm.uni-mainz.de/ontology#identification_rejected' END) as status_iri FROM persons_attestations_xref LEFT JOIN attestations on persons_attestations_xref.attestations_id = attestations.attestations_id
SELECT spellings_attestations_xref.spellings_id as spellings_id, record_id_from_id(spellings_attestations_xref.spellings_id) as spellings_id_short, record_id_from_id(spellings_attestations_xref.attestations_id) as attestations_id_short, record_id_from_id(attestations.inscriptions_id) as inscriptions_id_short FROM spellings_attestations_xref left join attestations on (spellings_attestations_xref.attestations_id = attestations.attestations_id)
SELECT titles_att.titles_id as titles_id, record_id_from_id(titles_att.titles_id) as titles_id_short, record_id_from_id(titles_att.attestations_id) as attestations_id_short, record_id_from_id(attestations.inscriptions_id) as inscriptions_id_short FROM titles_att left join attestations on (titles_att.attestations_id = attestations.attestations_id)
SELECT subject_id, predicate, object_id, record_id_from_id(subject_id) as subject_id_short, (CASE predicate WHEN 'specificates' THEN 'https://pnm.uni-mainz.de/ontology#specificates' WHEN 'refers to' THEN 'https://pnm.uni-mainz.de/ontology#refers_to_title' WHEN 'is a variant of' THEN 'https://pnm.uni-mainz.de/ontology#is_a_variant_of' WHEN 'is the opposite gender form of' THEN 'https://pnm.uni-mainz.de/ontology#is_the_opposite_gender_form_of' END) as predicate_iri FROM title_relations
SELECT spellings_id, ranke, record_id_from_id(spellings_id) as spellings_id_short, record_id_from_id(personal_names_id) as personal_names_id_short FROM spellings WHERE NOT ISNULL(ranke)
SELECT spellings_id, usage_area, usage_area_note, usage_period, usage_period_note, record_id_from_id(spellings_id) as spellings_id_short, record_id_from_id(personal_names_id) as personal_names_id_short FROM spellings WHERE NOT ISNULL(usage_area) OR NOT ISNULL(usage_period)
SELECT ranke, record_id_from_id(spellings_id) as spellings_id_short, record_id_from_id(personal_names_id) as personal_names_id_short FROM spellings
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(spellings_id) as spellings_id_short, record_id_from_id(personal_names_id) as personal_names_id_short FROM spellings
SELECT personal_names_id, tla, record_id_from_id(personal_names_id) as personal_names_id_short FROM names_tla_temp
SELECT personal_names_id, agea, record_id_from_id(personal_names_id) as personal_names_id_short FROM names_agea_temp
SELECT personal_names_id, usage_area, usage_area_note, usage_period, usage_period_note, record_id_from_id(personal_names_id) as personal_names_id_short, gender FROM personal_names WHERE NOT ISNULL(usage_area) OR NOT ISNULL(usage_period) OR NOT ISNULL (gender)
select personal_names.personal_names_id personal_names_id, record_id_from_id(personal_names.personal_names_id) personal_names_id_short, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(personal_names.ranke, ';', numbers.n), ';', -1)) ranke from (select 1 n union select 2 union all select 3 union all select 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9 union all SELECT 10 union all SELECT 11 union all SELECT 12 union all SELECT 13 union all SELECT 14 union all SELECT 15 union all SELECT 16 union all SELECT 17 union all SELECT 18 union all SELECT 19 union all SELECT 20 union all SELECT 21 union all SELECT 22) numbers INNER JOIN personal_names on CHAR_LENGTH(personal_names.ranke) - CHAR_LENGTH(REPLACE(personal_names.ranke, ';', ''))>=numbers.n-1 order by personal_names_id, n
select personal_names.personal_names_id personal_names_id, record_id_from_id(personal_names.personal_names_id) personal_names_id_short, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(personal_names.`scheele-schweitzer`, ';', numbers.n), ';', -1)) `scheele-schweitzer` from (select 1 n union select 2 union all select 3 union all select 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9 union all SELECT 10 union all SELECT 11 union all SELECT 12 union all SELECT 13 union all SELECT 14 union all SELECT 15 union all SELECT 16 union all SELECT 17 union all SELECT 18 union all SELECT 19 union all SELECT 20 union all SELECT 21 union all SELECT 22) numbers INNER JOIN personal_names on CHAR_LENGTH(personal_names.`scheele-schweitzer`) - CHAR_LENGTH(REPLACE(personal_names.`scheele-schweitzer`, ';', ''))>=numbers.n-1 order by personal_names_id, n
SELECT *, record_id_from_id(personal_names_id) as personal_names_id_short FROM personal_names
SELECT *, record_id_from_id(personal_names_id) as personal_names_id_short FROM personal_names where not isnull(translation_en) or not isnull(translation_de)
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(personal_names_id) as personal_names_id_short FROM personal_names
SELECT spellings.personal_names_id as personal_names_id, alternative_readings.personal_names_id as alt_reading, CAST(alternative_readings.date_created AS DATETIME) AS date_created_dt, CAST(alternative_readings.date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(alternative_readings.alternative_readings_id) as alternative_readings_id_short, record_id_from_id(spellings.personal_names_id) as personal_names_id_short, record_id_from_id(spellings.spellings_id) as spellings_id_short FROM alternative_readings LEFT JOIN spellings ON (alternative_readings.spellings_id = spellings.spellings_id)
SELECT name_types_id, usage_area, usage_area_note, usage_period, usage_period_note, record_id_from_id(name_types_id) as name_types_id_short FROM name_types WHERE NOT ISNULL(usage_area) OR NOT ISNULL(usage_period)
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(name_types_id) as name_types_id_short, (CASE category WHEN 'semantic' THEN 'https://pnm.uni-mainz.de/ontology#semantic_name_type' WHEN 'alternative' THEN 'https://pnm.uni-mainz.de/ontology#formal_name_type' END) as category_iri FROM name_types
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(criteria_id) as criteria_id_short FROM criteria
SELECT *, record_id_from_id(names_types_xref_id) names_types_xref_id_short, record_id_from_id(personal_names_id) personal_names_id_short, record_id_from_id(name_types_id) name_types_id_short FROM names_types_xref
SELECT tla, record_id_from_id(titles_id) as titles_id_short FROM titles_tla_temp
SELECT titles_id, usage_area, usage_area_note, usage_period, usage_period_note, record_id_from_id(titles_id) as titles_id_short, gender FROM titles WHERE NOT ISNULL(usage_area) OR NOT ISNULL(usage_period) OR NOT ISNULL (gender)
select titles.titles_id titles_id, record_id_from_id(titles.titles_id) titles_id_short, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(titles.ayedi, ';', numbers.n), ';', -1)) ayedi from (select 1 n union select 2 union all select 3 union all select 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9 union all SELECT 10 union all SELECT 11 union all SELECT 12 union all SELECT 13 union all SELECT 14 union all SELECT 15 union all SELECT 16 union all SELECT 17 union all SELECT 18 union all SELECT 19 union all SELECT 20 union all SELECT 21 union all SELECT 22) numbers INNER JOIN titles on CHAR_LENGTH(titles.ayedi) - CHAR_LENGTH(REPLACE(titles.ayedi, ';', ''))>=numbers.n-1 order by titles_id, n
select titles.titles_id titles_id, record_id_from_id(titles.titles_id) titles_id_short, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(titles.taylor, ';', numbers.n), ';', -1)) taylor from (select 1 n union select 2 union all select 3 union all select 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9 union all SELECT 10 union all SELECT 11 union all SELECT 12 union all SELECT 13 union all SELECT 14 union all SELECT 15 union all SELECT 16 union all SELECT 17 union all SELECT 18 union all SELECT 19 union all SELECT 20 union all SELECT 21 union all SELECT 22) numbers INNER JOIN titles on CHAR_LENGTH(titles.taylor) - CHAR_LENGTH(REPLACE(titles.taylor, ';', ''))>=numbers.n-1 order by titles_id, n
select titles.titles_id titles_id, record_id_from_id(titles.titles_id) titles_id_short, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(titles.hannig, ';', numbers.n), ';', -1)) hannig from (select 1 n union select 2 union all select 3 union all select 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9 union all SELECT 10 union all SELECT 11 union all SELECT 12 union all SELECT 13 union all SELECT 14 union all SELECT 15 union all SELECT 16 union all SELECT 17 union all SELECT 18 union all SELECT 19 union all SELECT 20 union all SELECT 21 union all SELECT 22) numbers INNER JOIN titles on CHAR_LENGTH(titles.hannig) - CHAR_LENGTH(REPLACE(titles.hannig, ';', ''))>=numbers.n-1 order by titles_id, n
select titles.titles_id titles_id, record_id_from_id(titles.titles_id) titles_id_short, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(titles.ward_fischer, ';', numbers.n), ';', -1)) ward_fischer from (select 1 n union select 2 union all select 3 union all select 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9 union all SELECT 10 union all SELECT 11 union all SELECT 12 union all SELECT 13 union all SELECT 14 union all SELECT 15 union all SELECT 16 union all SELECT 17 union all SELECT 18 union all SELECT 19 union all SELECT 20 union all SELECT 21 union all SELECT 22) numbers INNER JOIN titles on CHAR_LENGTH(titles.ward_fischer) - CHAR_LENGTH(REPLACE(titles.ward_fischer, ';', ''))>=numbers.n-1 order by titles_id, n
SELECT titles_id, title, record_id_from_id(titles_id) as titles_id_short FROM titles
SELECT *, record_id_from_id(titles_id) as titles_id_short FROM titles where not isnull(translation_en) or not isnull(translation_de)
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(titles_id) as titles_id_short FROM titles
SELECT bonds_id, bonds.subject_id as subject_id,bonds.object_id as object_id,bonds.predicate as predicate, bonds.wording as wording, CAST(bonds.date_created AS DATETIME) AS date_created_dt, CAST(bonds.date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(bonds_id) as bonds_id_short, record_id_from_id(attestations.inscriptions_id) as inscriptions_id_short FROM bonds left join attestations ON (attestations.attestations_id = bonds.subject_id)
SELECT persons_id, dating, dating_note, region, region_note, record_id_from_id(persons_id) as persons_id_short FROM persons WHERE NOT ISNULL(dating) OR NOT ISNULL(region)
SELECT *, record_id_from_id(persons_id) as persons_id_short, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt FROM persons
SELECT persons_bonds.subject_id as subject_id,persons_bonds.object_id as object_id,persons_bonds.predicate as predicate, CAST(persons_bonds.date_created AS DATETIME) AS date_created_dt, CAST(persons_bonds.date_changed AS DATETIME) AS date_changed_dt, record_id_from_id(persons_bonds_id) as persons_bonds_id_short, record_id_from_id(subject_id) as persons_id_short FROM persons_bonds
SELECT *, CAST(date_created AS DATETIME) AS date_created_dt, CAST(date_changed AS DATETIME) AS date_changed_dt, (CASE table_id_from_id(object_id) WHEN 0 THEN 'thesaurus' WHEN 14 THEN 'criterion' WHEN 2 THEN 'publication' WHEN 4 THEN 'inscription' WHEN 23 THEN 'group' WHEN 20 THEN 'workshop' WHEN 21 THEN 'inscriptions_workshops_xref' WHEN 22 THEN 'place' WHEN 25 THEN 'inv_no' WHEN 26 THEN 'collection' WHEN 8 THEN 'attestation' WHEN 15 THEN 'spellings_attestations_xref' WHEN 1 THEN 'persons_attestations_xref' WHEN 27 THEN 'person' WHEN 28 THEN 'title_att' WHEN 5 THEN 'title' WHEN 29 THEN 'spelling' WHEN 9 THEN 'alternative_reading' WHEN 17 THEN 'name' WHEN 30 THEN 'type' WHEN 31 THEN 'names_types_xref' WHEN 24 THEN 'bond' WHEN 11 THEN 'persons_bond' WHEN 3 THEN 'title_relation' END) as object, IF ((reference_type LIKE 'cp%' OR reference_type LIKE '% cp,%' OR reference_type LIKE '% cp'), IF(NOT reference_type LIKE '%cp (i)%' AND NOT reference_type LIKE '%illegible%', 'https://pnm.uni-mainz.de/ontology#colour_photo','https://pnm.uni-mainz.de/ontology#poor_or_partial_colour_photo'),NULL) as colour_photo, IF ((reference_type LIKE 'bp%' OR reference_type LIKE '% bp,%' OR reference_type LIKE '% bp'), IF(NOT reference_type LIKE '%bp (i)%' AND NOT reference_type LIKE '%illegible%', 'https://pnm.uni-mainz.de/ontology#bw_photo','https://pnm.uni-mainz.de/ontology#poor_or_partial_bw_photo'),NULL) as bw_photo, IF ((reference_type LIKE 's%' OR reference_type LIKE '% s,%' OR reference_type LIKE '% s'), IF(NOT reference_type LIKE '%s (i)%', 'https://pnm.uni-mainz.de/ontology#sketch','https://pnm.uni-mainz.de/ontology#partial_sketch'),NULL) as sketch, IF ((reference_type LIKE 'h%' OR reference_type LIKE '% h,%' OR reference_type LIKE '% h'), IF(NOT reference_type LIKE '%h (i)%', 'https://pnm.uni-mainz.de/ontology#hieroglyphic_transcription','https://pnm.uni-mainz.de/ontology#partial_hieroglyphic_transcription'),NULL) as hieroglyphic_transcription, IF (reference_type LIKE 't%' OR reference_type LIKE '% t,%' OR reference_type LIKE '% t','https://pnm.uni-mainz.de/ontology#translation',NULL) as translation, IF (reference_type LIKE 'd%' OR reference_type LIKE '% d,%' OR reference_type LIKE '% d','https://pnm.uni-mainz.de/ontology#description',NULL) as description, IF (reference_type LIKE 'm%' OR reference_type LIKE '% m,%' OR reference_type LIKE '% m','https://pnm.uni-mainz.de/ontology#mention',NULL) as mention, record_id_from_id(object_id) as object_id_short, record_id_from_id(biblio_refs_id) as biblio_refs_id_short FROM biblio_refs
SELECT *, record_id_from_id(biblio_refs_id) as biblio_refs_id_short, record_id_from_id(object_id) as object_id_short, (CASE table_id_from_id(object_id) WHEN 0 THEN 'thesaurus' WHEN 14 THEN 'criterion' WHEN 2 THEN 'publication' WHEN 4 THEN 'inscription' WHEN 23 THEN 'group' WHEN 20 THEN 'workshop' WHEN 21 THEN 'inscriptions_workshops_xref' WHEN 22 THEN 'place' WHEN 25 THEN 'inv_no' WHEN 26 THEN 'collection' WHEN 8 THEN 'attestation' WHEN 15 THEN 'spellings_attestations_xref' WHEN 1 THEN 'persons_attestations_xref' WHEN 27 THEN 'person' WHEN 28 THEN 'title_att' WHEN 5 THEN 'title' WHEN 29 THEN 'spelling' WHEN 9 THEN 'alternative_reading' WHEN 17 THEN 'name' WHEN 30 THEN 'type' WHEN 31 THEN 'names_types_xref' WHEN 24 THEN 'bond' WHEN 11 THEN 'persons_bond' WHEN 3 THEN 'title_relation' END) as object FROM biblio_refs WHERE not isnull(source_url) or not isnull(source_title)