unigeds_queries.py
Nov 25, 2022 17:58:38 GMT -8
Post by Uncle Buddy on Nov 25, 2022 17:58:38 GMT -8
<drive>:\treebard\unigeds_queries.py Last Changed 2024-07-25
# unigeds_queries.py
# Keep UNIGEDS pure. Nothing in here that references databases other than
# unigeds.db. No vendor cruft. No user data. No app data. Just primary genealogy.
import dev_tools as dt
from dev_tools import look, seeline
delete_assertion = '''
DELETE FROM assertion
WHERE assertion_id = ?
'''
delete_assertion_by_citation = '''
DELETE FROM assertion
WHERE citation_id = ?
'''
delete_assertion_by_event = '''
DELETE FROM assertion
WHERE event_id = ?
'''
delete_assertion_by_name = '''
DELETE FROM assertion
WHERE name_id = ?
'''
delete_citation_by_id = '''
DELETE FROM citation
WHERE citation_id = ?
'''
delete_citation_by_source = '''
DELETE FROM citation
WHERE source_id = ?
'''
delete_event_by_id = '''
DELETE FROM event
WHERE event_id = ?
'''
delete_event_person = '''
DELETE FROM event
WHERE person_id = ?
'''
delete_events_notes_event = '''
DELETE FROM notes_links
WHERE event_id = ?
AND note_id is not null
'''
delete_events_role = '''
DELETE FROM roles_links
WHERE roles_links_id = ?
'''
delete_events_roles_event = '''
DELETE FROM roles_links
WHERE event_id = ?
AND role_type_id is not null
'''
delete_media = '''
DELETE FROM media
WHERE media_id = ?
'''
delete_media_links_citation = '''
DELETE FROM media_links
WHERE citation_id = ?
'''
delete_media_links_image = '''
DELETE FROM media_links
WHERE media_id = ?
'''
delete_media_links_image_nested_place = '''
DELETE FROM media_links
WHERE nested_place_id = ? AND media_id = ?
'''
delete_media_links_image_person = '''
DELETE FROM media_links
WHERE person_id = ? AND media_id = ?
'''
delete_media_links_image_source = '''
DELETE FROM media_links
WHERE source_id = ? AND media_id = ?
'''
delete_media_links_person = '''
DELETE FROM media_links
WHERE person_id = ?
'''
delete_name_by_id = '''
DELETE FROM name
WHERE name_id = ?
'''
delete_name_person = '''
DELETE FROM name
WHERE person_id = ?
'''
delete_nested_place = '''
DELETE from nested_place
WHERE nested_place_id = ?
'''
delete_note = '''
DELETE FROM note
WHERE note_id = ?
'''
delete_note_place = '''
DELETE FROM notes_links
WHERE place_id = ?
'''
delete_note_place_name_by_place_id = '''
DELETE FROM notes_links
WHERE place_name_id = (
SELECT place_name_id FROM place_name WHERE place_id = ?)
'''
delete_notes_links = '''
DELETE FROM notes_links
WHERE note_id = ?
'''
delete_notes_links_citation = '''
DELETE FROM notes_links
WHERE citation_id = ?
'''
delete_notes_links_name = '''
DELETE FROM notes_links
WHERE name_id = ?
'''
delete_notes_links_nesting = '''
DELETE FROM notes_links
WHERE nested_place_id = ?
'''
delete_notes_links_source = '''
DELETE FROM notes_links
WHERE source_id = ?
'''
delete_notes_links_note_assertion = '''
DELETE FROM notes_links
WHERE note_id = ? AND assertion_id = ?
'''
delete_notes_links_note_citation = '''
DELETE FROM notes_links
WHERE note_id = ? AND citation_id = ?
'''
delete_notes_links_note_event = '''
DELETE FROM notes_links
WHERE note_id = ? AND event_id = ?
'''
delete_notes_links_note_place = '''
DELETE FROM notes_links
WHERE note_id = ? AND place_id = ?
'''
delete_notes_links_note_source = '''
DELETE FROM notes_links
WHERE note_id = ? AND source_id = ?
'''
delete_notes_links_person = '''
DELETE FROM notes_links
WHERE person_id = ?
'''
delete_notes_links_unlink_note = '''
DELETE FROM notes_links
WHERE notes_links_id = ?
'''
delete_person = '''
DELETE FROM person
WHERE person_id = ?
'''
delete_place = '''
DELETE FROM place
WHERE place_id = ?
'''
delete_place_name = '''
DELETE FROM place_name
WHERE place_id = ?
'''
delete_place_name_by_string = '''
DELETE FROM place_name
WHERE place_name_text = ?
AND place_id = ?
'''
delete_source = '''
DELETE FROM source
WHERE source_id = ?
'''
insert_assertion_new_age = '''
INSERT INTO assertion (event_id, citation_id, age_assertion)
VALUES (?, ?, ?)
'''
insert_assertion_new_date = '''
INSERT INTO assertion (event_id, citation_id, date_assertion)
VALUES (?, ?, ?)
'''
insert_assertion_new_name = '''
INSERT INTO assertion (event_id, citation_id, name_assertion)
VALUES (?, ?, ?)
'''
insert_assertion_new_particulars = '''
INSERT INTO assertion (event_id, citation_id, particulars_assertion)
VALUES (?, ?, ?)
'''
insert_assertion_new_place = '''
INSERT INTO assertion (event_id, citation_id, place_assertion)
VALUES (?, ?, ?)
'''
insert_assertion_new_role = '''
INSERT INTO assertion (event_id, citation_id, role_assertion)
VALUES (?, ?, ?)
'''
insert_citation_new = '''
INSERT INTO citation (citation_text, source_id)
VALUES (?, ?)
'''
insert_couple_new = '''
INSERT INTO couple (person_id1, person_id2) VALUES (?, ?)
'''
insert_couple_new_partner = '''
INSERT INTO couple (person_id1, person_id2)
VALUES (?, ?)
'''
insert_event_birth_new_person = '''
INSERT INTO event (person_id, event_type_id, age)
VALUES (?, 1, '0')
'''
insert_event_date = '''
INSERT INTO event (date, person_id, event_type_id, date_sorter)
VALUES (?, ?, ?, ?)
'''
insert_event_new = '''
INSERT INTO event (event_type_id, person_id)
VALUES (?, ?)
'''
insert_event_couple = '''
INSERT INTO event (event_type_id, couple_id)
VALUES (?, ?)
'''
insert_event_type = '''
INSERT INTO event_type (event_type_text)
VALUES (?)
'''
insert_image_new = '''
INSERT INTO media
VALUES (null, ?, ?, "", 1)
'''
insert_kin_type = '''
INSERT INTO kin_type (kin_type_text, common_name)
VALUES (?, ?)
'''
insert_media_links_image_nested_place = '''
INSERT INTO media_links (media_id, nested_place_id)
VALUES (?, ?)
'''
insert_media_links_image_person = '''
INSERT INTO media_links (media_id, person_id)
VALUES (?, ?)
'''
insert_media_links_image_source = '''
INSERT INTO media_links (media_id, source_id)
VALUES (?, ?)
'''
insert_media_type = '''
INSERT INTO media_type (media_type_text)
VALUES (?)
'''
insert_name = '''
INSERT INTO name
VALUES (null, ?, ?, ?, ?, '')
'''
insert_name_and_type = '''
INSERT INTO name (name_text, name_type_id, person_id, sort_order)
VALUES (?, ?, ?, ?)
'''
insert_name_placeholder = '''
INSERT INTO name
VALUES (null, ?, '_____ _____', 25, '_____, _____', '')
'''
insert_name_type = '''
INSERT INTO name_type (name_type_text)
VALUES (?)
'''
insert_nested_place = '''
INSERT INTO nested_place (nest0, nest1, nest2, nest3, nest4, nest5, nest6, nest7, nest8)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
insert_note = '''
INSERT INTO note (note_text)
VALUES (?)
'''
insert_notes_links_note_assertion = '''
INSERT INTO notes_links (assertion_id, note_id)
VALUES (?, ?)
'''
insert_notes_links_note_citation = '''
INSERT INTO notes_links (citation_id, note_id)
VALUES (?, ?)
'''
insert_notes_links_note_event = '''
INSERT INTO notes_links (event_id, note_id)
VALUES (?, ?)
'''
insert_notes_links_note_place = '''
INSERT INTO notes_links (place_id, note_id)
VALUES (?, ?)
'''
insert_notes_links_note_source = '''
INSERT INTO notes_links (source_id, note_id)
VALUES (?, ?)
'''
insert_person = ''' INSERT INTO person (person_id) VALUES (null) '''
insert_person_new = '''
INSERT INTO person (gender)
VALUES (?)
'''
insert_place_name = '''
INSERT INTO place_name (place_name_text, place_id)
VALUES (?, ?)
'''
insert_place_new = '''
INSERT INTO place (place_id)
VALUES (null)
'''
insert_place_type = '''
INSERT INTO place_type (place_type_text)
VALUES (?)
'''
insert_role_type = '''
INSERT INTO role_type (role_type_text)
VALUES (?)
'''
insert_roles_links_role_person = '''
INSERT INTO roles_links (event_id, role_type_id, person_id)
VALUES (?, ?, ?)
'''
insert_source_type_by_id = '''
INSERT INTO source (source_name, source_type_id)
VALUES (?, ?)
'''
insert_source_type = '''
INSERT INTO source_type (source_type_text)
VALUES (?)
'''
insert_transcription_type = '''
INSERT INTO transcription_type (transcription_type_text)
VALUES (?)
'''
select_all_current_assertion_notes = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE notes_links.assertion_id IS NOT null
AND assertion_id = ?
'''
select_all_current_citation_notes = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE notes_links.citation_id IS NOT null
AND citation_id = ?
'''
select_all_current_event_notes = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE notes_links.event_id IS NOT null
AND event_id = ?
'''
select_all_current_place_notes = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE notes_links.place_id IS NOT null
AND place_id = ?
'''
select_all_current_source_notes = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE notes_links.source_id IS NOT null
AND source_id = ?
'''
select_all_event_types = '''
SELECT event_type_text
FROM event_type
ORDER BY event_type_text
'''
select_all_events_current_person = '''
SELECT event_id
FROM event
WHERE person_id = ?
'''
select_all_events_notes_ids = '''
SELECT event_id
FROM notes_links
WHERE note_id IS NOT null
'''
select_all_events_roles_ids_distinct = '''
SELECT DISTINCT event_id
FROM roles_links
WHERE role_type_id IS NOT null
'''
select_all_images_nested_place = '''
SELECT DISTINCT file_name, caption, media_links.media_id, media_links_id
FROM media_links
JOIN nested_place
ON media_links.nested_place_id = nested_place.nested_place_id
JOIN media
ON media.media_id = media_links.media_id
WHERE media_links.nested_place_id = ?
'''
select_all_images_person = '''
SELECT DISTINCT file_name, caption, media_links.media_id, media_links_id
FROM media_links
JOIN person
ON media_links.person_id = person.person_id
JOIN media
ON media.media_id = media_links.media_id
WHERE media_links.person_id = ?
'''
select_all_images_source = '''
SELECT DISTINCT file_name, caption, media_links.media_id, media_links_id
FROM media_links
JOIN source
ON media_links.source_id = source.source_id
JOIN media
ON media.media_id = media_links.media_id
WHERE media_links.source_id = ?
'''
select_all_kin_types = '''
SELECT common_name, kin_type_text
FROM kin_type
ORDER BY common_name, kin_type_text
'''
select_all_name_types = '''
SELECT name_type_text FROM name_type ORDER BY name_type_text
'''
select_all_names = '''
SELECT name_text from name
'''
select_all_nested_place_strings_and_ids = '''
SELECT a.place_name_text, b.place_name_text, c.place_name_text, d.place_name_text,
e.place_name_text, f.place_name_text, g.place_name_text, h.place_name_text, i.place_name_text,
nested_place_id
FROM nested_place
JOIN place_name a ON a.place_id = nest0
JOIN place_name b ON b.place_id = nest1
JOIN place_name c ON c.place_id = nest2
JOIN place_name d ON d.place_id = nest3
JOIN place_name e ON e.place_id = nest4
JOIN place_name f ON f.place_id = nest5
JOIN place_name g ON g.place_id = nest6
JOIN place_name h ON h.place_id = nest7
JOIN place_name i ON i.place_id = nest8
WHERE nest0 != 1 or nest1 != 1 or nest2 != 1 or nest3 != 1 or nest4 != 1 or nest5 != 1 or nest6 != 1 or nest7 != 1 or nest8 != 1
'''
select_all_nested_places = '''
SELECT nest0, nest1, nest2, nest3, nest4, nest5, nest6, nest7, nest8
FROM nested_place
'''
select_all_notes = '''
SELECT note_id, note_text
FROM note
'''
select_all_place_names = '''
SELECT place_name_text
FROM place_name
'''
select_all_place_names_ordered = '''
SELECT DISTINCT place_name_text
FROM place_name
ORDER BY place_name_text
'''
select_all_source_types = '''
SELECT source_type_id, source_type_text
FROM source_type
ORDER BY source_type_text
'''
select_all_sources = '''
SELECT source_name FROM source
'''
select_assertion_count_event = '''
SELECT COUNT(assertion_id)
FROM assertion
WHERE event_id = ?
'''
select_assertion_count_name = '''
SELECT COUNT(assertion_id)
FROM assertion
WHERE name_id = ?
'''
select_assertion_description = '''
SELECT assertion_id, citation_id
FROM assertion
WHERE assertion_id = ?
'''
select_assertion_ids_by_event = '''
SELECT assertion_id, date_assertion, place_assertion, particulars_assertion,
age_assertion, role_assertion
FROM assertion
WHERE event_id = ?
'''
select_assertion_ids_by_name = '''
SELECT assertion_id, name_assertion
FROM assertion
WHERE name_id = ?
'''
select_assertion_no_event = '''
SELECT assertion_id, citation_id, date_assertion, place_assertion,
particulars_assertion, age_assertion, role_assertion
FROM assertion
WHERE event_id is null and name_id is null
'''
select_assertion_no_name = '''
SELECT assertion_id, citation_id, name_assertion
FROM assertion
WHERE name_id is null and event_id is null
'''
select_citation_by_source = '''
SELECT citation_id
FROM citation
WHERE source_id = ?
'''
select_citation_details_by_source = '''
SELECT citation.citation_id, citation_text, notes_links.note_id, note_text
FROM citation
LEFT JOIN notes_links ON citation.citation_id = notes_links.citation_id
LEFT JOIN note ON note.note_id = notes_links.note_id
WHERE citation.source_id = ?
'''
select_citation_id = '''
SELECT citation_id FROM assertion WHERE assertion_id = ?
'''
select_citation_string_source = '''
SELECT citation_text, citation.source_id, source_name
FROM citation
JOIN source ON source.source_id = citation.source_id
WHERE citation_id = ?
'''
select_citations_per_source = '''
SELECT citation_id, citation_text
FROM citation
JOIN source
ON citation.source_id = source.source_id
WHERE source_name = ?
'''
select_count_event_id_sources = '''
SELECT COUNT(event_id)
FROM assertion
WHERE event_id = ?
'''
select_count_events_roles = '''
SELECT COUNT (roles_links_id)
FROM roles_links
WHERE event_id = ?
AND role_type_id IS NOT null
'''
select_count_name_id_sources = '''
SELECT COUNT(name_id)
FROM assertion
WHERE name_id = ?
'''
select_count_offspring_notes = '''
SELECT COUNT (notes_links.event_id)
FROM event
JOIN notes_links
ON notes_links.event_id = event.event_id
WHERE event.person_id = ? AND event_type_id = (
SELECT event_type_id FROM event_type WHERE event_type_text = 'birth')
'''
select_couple_by_event_id = '''
SELECT person_id1, person_id2
FROM couple
JOIN event ON event.couple_id = couple.couple_id
WHERE event_id = ?
'''
select_couple_count_person1 = '''
SELECT COUNT (couple_id)
FROM couple
WHERE person_id1 = ?
'''
select_couple_count_person2 = '''
SELECT COUNT (couple_id)
FROM couple
WHERE person_id2 = ?
'''
select_couple_id_by_partners = '''
SELECT couple_id
FROM couple
WHERE (person_id1 = ? AND person_id2 = ?)
OR (person_id1 = ? AND person_id2 = ?)
OR (person_id1 = ? AND person_id2 IS null)
OR (person_id1 IS null AND person_id2 = ?)
'''
select_couple_id_by_partners_r2d2 = '''
SELECT couple_id
FROM couple
WHERE (person_id1 = ? AND person_id2 = ?)
OR (person_id2 = ? AND person_id1 = ?)
'''
select_couple_id_by_partners2 = '''
SELECT couple_id
FROM couple
WHERE (person_id1 = ? AND person_id2 = ?)
OR (person_id1 IS null AND person_id2 = ?)
OR (person_id1 = ? AND person_id2 IS null)
'''
select_couple_id_partner1 = '''
SELECT person_id1, couple_id
FROM couple
WHERE person_id2 = ?
'''
select_couple_id_partner2 = '''
SELECT person_id2, couple_id
FROM couple
WHERE person_id1 = ?
'''
select_couple_partners = '''
SELECT person_id1, person_id2
FROM couple
WHERE couple_id = ?
'''
select_couple_partner1 = '''
SELECT person_id1
FROM couple
WHERE person_id1 = ?
AND couple_id = ?
'''
select_couple_partner2 = '''
SELECT person_id2
FROM couple
WHERE person_id2 = ?
AND couple_id = ?
'''
select_couple_partner1_not = '''
SELECT person_id1
FROM couple
WHERE couple_id = ? AND person_id1 != ?
'''
select_couple_partner2_not = '''
SELECT person_id2
FROM couple
WHERE couple_id = ? AND person_id2 != ?
'''
select_event_age1 = '''
SELECT age1
FROM event
JOIN couple ON event.couple_id = couple.couple_id
WHERE event_id = ?
AND person_id1 = ?
'''
select_event_age2 = '''
SELECT age2
FROM event
JOIN couple ON event.couple_id = couple.couple_id
WHERE event_id = ?
AND person_id2 = ?
'''
select_event_alt_parents = '''
SELECT date_sorter, event_id, couple_id, event_type_text
FROM event
JOIN event_type ON event.event_type_id = event_type.event_type_id
WHERE person_id = ?
AND event.event_type_id IN (
SELECT event_type_id FROM event_type
WHERE event_type_text in ('adoption', 'fosterage', 'guardianship'))
'''
select_event_couple_details = '''
SELECT person_id1, age1, person_id2, age2
FROM event
JOIN couple ON couple.couple_id = event.couple_id
WHERE event_id = ?
'''
select_event_couple = '''
SELECT couple_id, event_id
FROM event
WHERE person_id = ? AND event_type_id = (
SELECT event_type_id FROM event_type
WHERE event_type_text = 'birth')
'''
select_event_couple_id = '''
SELECT couple_id
FROM event
WHERE event_id = ?
'''
select_event_date_birth = '''
SELECT date
FROM event
WHERE person_id = ? AND event_type_id = (
SELECT event_type_id FROM event_type
WHERE event_type_text = 'birth')
'''
select_event_date_sorter = '''
SELECT event_id, date_sorter
FROM event
WHERE couple_id = ?
'''
select_event_death_date = '''
SELECT date
FROM event
WHERE person_id = ?
AND event_type_id = (
SELECT event_type_id FROM event_type
WHERE event_type_text = 'death')
'''
select_event_details_offspring_alt_parentage = '''
SELECT date, date_sorter, particulars
FROM event
WHERE person_id = ?
AND event_type_id IN (1, 48, 83, 95)
AND event_id = ?
'''
select_event_event_type = '''
SELECT event_type_id
FROM event
WHERE event_id = ?
'''
select_event_father = '''
SELECT person_id1
FROM couple
JOIN event ON event.couple_id = couple.couple_id
WHERE event_id = ?
'''
select_event_id_birth = '''
SELECT event_id
FROM event
WHERE event_type_id = 1
AND person_id = ?
'''
select_event_id_death = '''
SELECT event_id
FROM event
WHERE person_id = ? AND event_type_id = (
SELECT event_type_id FROM event_type
WHERE event_type_text = 'death')
'''
select_event_id_offspring_by_parent = '''
SELECT event_id
FROM event
JOIN couple ON couple.couple_id = event.couple_id
WHERE event_type_id IN (
SELECT event_type_id FROM event_type WHERE event_type_text in (
'birth', 'adoption', 'fosterage', 'guardianship'))
AND (couple.person_id1 = ? OR couple.person_id2 = ?)
'''
select_event_mother = '''
SELECT person_id2
FROM couple
JOIN event ON event.couple_id = couple.couple_id
WHERE event_id = ?
'''
select_event_nested_place = '''
SELECT a.place_name_text, b.place_name_text, c.place_name_text, d.place_name_text,
e.place_name_text, f.place_name_text, g.place_name_text, h.place_name_text, i.place_name_text
FROM event
JOIN nested_place ON event.nested_place_id = nested_place.nested_place_id
JOIN place_name a ON a.place_id = nest0
JOIN place_name b ON b.place_id = nest1
JOIN place_name c ON c.place_id = nest2
JOIN place_name d ON d.place_id = nest3
JOIN place_name e ON e.place_id = nest4
JOIN place_name f ON f.place_id = nest5
JOIN place_name g ON g.place_id = nest6
JOIN place_name h ON h.place_id = nest7
JOIN place_name i ON i.place_id = nest8
WHERE event_id = ?
AND nest0 != 1
'''
select_event_nested_place_id_count = '''
SELECT nested_place_id, COUNT (nested_place_id)
FROM event
WHERE nested_place_id = ?
'''
select_event_person = '''
SELECT event_id
FROM event
WHERE person_id = ?
'''
select_event_persons = '''
SELECT person_id1, person_id2
FROM event
JOIN couple ON couple.couple_id = event.couple_id
WHERE event_id = ?
'''
select_event_sorter = '''
SELECT date_sorter
FROM event
WHERE event_id = ?
'''
select_event_type_couple_bool = '''
SELECT couple
FROM event_type
WHERE event_type_text = ?
'''
select_event_type_birth = '''
SELECT event_type_id
FROM event_type WHERE event_type_text = 'birth'
'''
select_event_type_death = '''
SELECT event_type_id
FROM event_type WHERE event_type_text = 'death'
'''
select_events_details_generic = '''
SELECT event_type_text, particulars, age, date, date_sorter
FROM event
JOIN event_type
ON event.event_type_id = event_type.event_type_id
WHERE event_id = ?
'''
select_events_for_person = '''
SELECT event_type_text
FROM event
JOIN event_type
ON event.event_type_id = event_type.event_type_id
WHERE person_id = ?
'''
select_family_events_details_couple_generic = '''
SELECT event_type_text, date, date_sorter, particulars
FROM event
JOIN event_type
ON event.event_type_id = event_type.event_type_id
WHERE event_id = ?
'''
select_gender = '''
SELECT gender
FROM person
WHERE person_id = ?
'''
select_image_caption = '''
SELECT caption
FROM media
WHERE media_id = ?
'''
select_images_all = '''
SELECT file_name, media_id FROM media WHERE media_type_id = 1
'''
select_media_id = '''
SELECT media_id FROM media WHERE file_name = ? AND media_type_id = (
SELECT media_type_id FROM media_type WHERE media_type_text = 'image')
'''
select_notes_links_assertions_by_source = '''
SELECT notes_links_id
FROM notes_links
WHERE assertion_id is not null
AND assertion_id in (
SELECT assertion_id FROM assertion WHERE citation_id = ?)
'''
select_notes_links_assertions_notes = '''
SELECT notes_links.note_id, note_text
FROM notes_links
JOIN note ON note.note_id = notes_links.note_id
WHERE assertion_id = ?
'''
select_media_links_count_image_nested_place = '''
SELECT COUNT(media_id)
FROM media_links
WHERE nested_place_id = ?
'''
select_media_links_count_image_person = '''
SELECT COUNT(media_id)
FROM media_links
WHERE person_id = ?
'''
select_media_links_count_image_source = '''
SELECT COUNT(media_id)
FROM media_links
WHERE source_id = ?
'''
select_media_links_nested_place = '''
SELECT media_links_id, media_id
FROM media_links
WHERE nested_place_id = ?
'''
select_media_links_person = '''
SELECT media_links_id, media_id
FROM media_links
WHERE person_id = ?
'''
select_media_links_source = '''
SELECT media_links_id, media_id
FROM media_links
WHERE source_id = ?
'''
select_roles_links_roles = '''
SELECT
roles_links_id,
role_type_text,
person_id,
roles_links.role_type_id
FROM role_type
JOIN roles_links
ON role_type.role_type_id = roles_links.role_type_id
WHERE event_id = ?
'''
select_name_all_current = '''
SELECT name_id, name_text, name_type_text
FROM name
JOIN name_type
ON name.name_type_id = name_type.name_type_id
WHERE person_id = ?
'''
select_name_by_id = '''
SELECT name_text
FROM name
WHERE person_id = ?
'''
select_name_data = '''
SELECT person_id, name_text, name_type_text, name.name_id, sort_order, used_by
FROM name
JOIN name_type
ON name.name_type_id = name_type.name_type_id
ORDER BY person_id
'''
select_name_details = '''
SELECT name_text, name_type_text, used_by
FROM name
LEFT JOIN name_type
ON name.name_type_id = name_type.name_type_id
WHERE person_id = ?
'''
select_name_id_by_person_id = '''
SELECT name_id
FROM name
WHERE person_id = ?
'''
select_name_sort_order = '''
SELECT sort_order
FROM name
JOIN person
ON person.person_id = name.person_id
WHERE name.person_id = ?
AND name_type_id = 1
'''
select_name_type_id = '''
SELECT name_type_id
FROM name_type
WHERE name_type_text = ?
'''
select_name_type_id_by_string = '''
SELECT name_type_id
FROM name_type
WHERE name_type_text = ?
'''
select_nest_ids = '''
SELECT nest0, nest1, nest2, nest3, nest4, nest5, nest6, nest7, nest8
FROM nested_place
WHERE nested_place_id = ?
'''
select_nested_place_by_nest0 = '''
SELECT nested_place_id
FROM nested_place
WHERE nest0 = ?
'''
select_nested_place_id_inclusion = '''
SELECT nested_place_id
FROM nested_place
WHERE nest0 = ? OR nest1 = ? OR nest2 = ? OR nest3 = ? OR nest4 = ?
OR nest5 = ? OR nest6 = ? OR nest7 = ? OR nest8 = ?
'''
select_nested_place_id_not_this = '''
SELECT nested_place_id
FROM nested_place
WHERE nested_place_id not in (?, 1)
'''
select_nested_place_inclusion = '''
SELECT nested_place_id, a.place_name_text, b.place_name_text, c.place_name_text, d.place_name_text,
e.place_name_text, f.place_name_text, g.place_name_text, h.place_name_text, i.place_name_text
FROM nested_place
JOIN place_name as a ON a.place_id = nest0
JOIN place_name as b ON b.place_id = nest1
JOIN place_name as c ON c.place_id = nest2
JOIN place_name as d ON d.place_id = nest3
JOIN place_name as e ON e.place_id = nest4
JOIN place_name as f ON f.place_id = nest5
JOIN place_name as g ON g.place_id = nest6
JOIN place_name as h ON h.place_id = nest7
JOIN place_name as i ON i.place_id = nest8
WHERE nest0 = ? OR nest1 = ? OR nest2 = ? OR nest3 = ? OR nest4 = ?
OR nest5 = ? OR nest6 = ? OR nest7 = ? OR nest8 = ?
'''
select_nested_place_smallest_nest = '''
SELECT nest0
FROM nested_place
WHERE nested_place_id = ?
'''
select_notes_ids_by_text_assertion = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON note.note_id = notes_links.note_id
WHERE assertion_id = ?
'''
select_notes_ids_by_text_event = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON note.note_id = notes_links.note_id
WHERE event_id = ?
'''
select_notes_ids_by_text_place = '''
SELECT notes_links.note_id, note_text
FROM note
JOIN notes_links
ON note.note_id = notes_links.note_id
WHERE place_id = ?
'''
select_notes_links_place_name_id = '''
SELECT notes_links_id
FROM place_name
JOIN notes_links ON notes_links.place_name_id = place_name.place_name_id
WHERE place_name.place_id = ?
AND place_name_text = ? AND note_id IS NOT null
'''
select_notes_per_assertion = '''
SELECT note_text, notes_links.note_id
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE assertion_id = ?
'''
select_notes_per_event = '''
SELECT note_text, notes_links.note_id
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE event_id = ?
'''
select_notes_per_place = '''
SELECT note_text, notes_links.note_id
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE place_id = ?
'''
select_person = '''
SELECT person_id
FROM event
WHERE event_id = ?
'''
select_person_birth_date = '''
SELECT
event_id, date
FROM person
JOIN event
ON event.person_id = person.person_id
JOIN event_type
ON event.event_type_id = event_type.event_type_id
WHERE event.person_id = ?
AND event.event_type_id = 1
'''
select_person_death_date = '''
SELECT date
FROM person
JOIN event
ON event.person_id = person.person_id
JOIN event_type
ON event.event_type_id = event_type.event_type_id
WHERE event.person_id = ?
AND event.event_type_id = 4
'''
select_person_distinct_like = '''
SELECT DISTINCT
person.person_id
FROM person
JOIN name
ON person.person_id = name.person_id
WHERE name_text LIKE ?
OR person.person_id LIKE ?
'''
select_person_gender_by_id = '''
SELECT gender
FROM person
WHERE person_id = ?
'''
select_place_id_with_name = '''
SELECT place_id
FROM place_name
WHERE place_name_text = ?
'''
select_place_name_count = '''
SELECT COUNT(place_name_id)
FROM place_name
WHERE place_id = ?
'''
select_place_name_from_id = '''
SELECT place_name_text
FROM place_name
WHERE place_id = ?
'''
select_place_name_id_first_different = '''
SELECT place_name_id
FROM place_name
WHERE place_id = ?
AND place_name_text != ?
'''
select_role_type_id = '''
SELECT role_type_id
FROM role_type
WHERE role_type_text = ?
'''
select_role_types = '''
SELECT role_type_text
FROM role_type
'''
select_source_by_id = '''
SELECT source_name FROM source WHERE source_id = ?
'''
select_source_id = '''
SELECT source_id FROM citation WHERE citation_id = ?
'''
select_source_id_by_string = '''
SELECT source_id
FROM source
WHERE source_name = ?
'''
select_source_type_id = '''
SELECT source_type_id
FROM source_type
WHERE source_type_text = ?
'''
update_assertion_age = '''
UPDATE assertion
SET age_assertion = ?
WHERE assertion_id = ?
'''
update_assertion_date = '''
UPDATE assertion
SET date_assertion = ?
WHERE assertion_id = ?
'''
update_assertion_delete_event = '''
UPDATE assertion
SET event_id = null
WHERE event_id = ?
'''
update_assertion_link_event = '''
UPDATE assertion
SET event_id = ?
WHERE assertion_id = ?
'''
update_assertion_link_name = '''
UPDATE assertion
SET name_id = ?
WHERE assertion_id = ?
'''
update_assertion_name = '''
UPDATE assertion
SET name_assertion = ?
WHERE assertion_id = ?
'''
update_assertion_particulars = '''
UPDATE assertion
SET particulars_assertion = ?
WHERE assertion_id = ?
'''
update_assertion_place = '''
UPDATE assertion
SET place_assertion = ?
WHERE assertion_id = ?
'''
update_assertion_role = '''
UPDATE assertion
SET role_assertion = ?
WHERE assertion_id = ?
'''
update_assertion_unlink_event = '''
UPDATE assertion
SET event_id = null
WHERE assertion_id = ?
'''
update_assertion_unlink_name = '''
UPDATE assertion
SET name_id = null
WHERE assertion_id = ?
'''
update_citation_text = '''
UPDATE citation
SET citation_text = ?
WHERE citation_id = ?
'''
update_couple_new_person1 = '''
UPDATE couple
SET person_id1 = ?
WHERE couple_id = ?
'''
update_couple_new_person2 = '''
UPDATE couple
SET person_id2 = ?
WHERE couple_id = ?
'''
update_couple_null1 = '''
UPDATE couple
SET person_id1 = null
WHERE person_id1 = ?
'''
update_couple_null2 = '''
UPDATE couple
SET person_id2 = null
WHERE person_id2 = ?
'''
update_event_age = '''
UPDATE event
SET age = ?
WHERE event_id = ?
'''
update_event_age1 = '''
UPDATE event
SET age1 = ?
WHERE event_id = ?
'''
update_event_age2 = '''
UPDATE event
SET age2 = ?
WHERE event_id = ?
'''
update_event_couple = '''
UPDATE event
SET couple_id = ?
WHERE event_id = ?
'''
update_event_couple_null_by_event_id = '''
UPDATE event
SET couple_id = null
WHERE event_id = ?
'''
update_event_date = '''
UPDATE event
SET (date, date_sorter) = (?, ?)
WHERE event_id = ?
'''
update_event_nested_place = '''
UPDATE event
SET nested_place_id = ?
WHERE event_id = ?
'''
update_event_nested_place_unknown = '''
UPDATE event
SET nested_place_id = 1
WHERE event_id = ?
'''
update_event_particulars = '''
UPDATE event
SET particulars = ?
WHERE event_id = ?
'''
update_event_types = '''
UPDATE event
SET event_type_id = ?
WHERE event_id = ?
'''
update_image_caption = '''
UPDATE media
SET caption = ?
WHERE media_id = ?
'''
update_name_type_sorter = '''
UPDATE name
SET (name_text, name_type_id, sort_order) = (?, ?, ?)
WHERE name_id = ?
'''
update_nested_place_nests = '''
UPDATE nested_place set (
nest0, nest1, nest2, nest3, nest4, nest5, nest6, nest7, nest8)
= (?, ?, ?, ?, ?, ?, ?, ?, ?)
WHERE nested_place_id = ?
'''
update_note_edit = '''
UPDATE note
SET note_text = ?
WHERE note_id = ?
'''
update_person_gender = '''
UPDATE person
SET gender = ?
WHERE person_id = ?
'''
update_place_name_spelling = '''
UPDATE place_name
SET place_name_text = ?
WHERE place_name_text = ?
'''
update_roles_links_person_id = '''
UPDATE roles_links
SET person_id = ?
WHERE roles_links_id = ?
'''
update_roles_links_role_type = '''
UPDATE roles_links
SET role_type_id = ?
WHERE roles_links_id = ?
'''
update_source_name = '''
UPDATE source
SET source_name = ?
WHERE source_id = ?
'''