query_strings.py
Nov 25, 2022 17:58:38 GMT -8
Post by Uncle Buddy on Nov 25, 2022 17:58:38 GMT -8
<drive>:\treebard\app\python\query_strings.py Last Changed 2024-04-16
# query_strings.py
import dev_tools as dt
from dev_tools import look, seeline
IMAGE_LINKS = ("nested_place_id", "person_id", "source_id")
NOTE_LINKS = ("assertion_id", "event_id", "place_id")
DEFAULT_IMAGES = ("image_female", "image_male", "image_nested_place", "image_source", "image_unisex")
delete_assertion = '''
DELETE FROM assertion
WHERE assertion_id = ?
'''
delete_assertion_by_citation = '''
DELETE FROM assertion
WHERE citation_id = ?
'''
delete_change_date_source = '''
DELETE FROM change_date
WHERE source_id = ?
'''
delete_citation_by_source = '''
DELETE FROM citation
WHERE source_id = ?
'''
delete_color_scheme = '''
DELETE FROM colors_type
WHERE colors_type_id = ?
'''
delete_couple_by_id = '''
DELETE FROM couple
WHERE couple_id = ?
'''
delete_date_format_all = '''DELETE FROM date_format'''
delete_do_list_item = '''
DELETE FROM to_do
WHERE to_do_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_family_tree = '''
DELETE FROM family_tree
WHERE family_tree_id = ?
'''
delete_sources_links = '''
DELETE FROM repositories_links
WHERE sources_links_id = ?
'''
delete_sources_links_assertion = '''
DELETE FROM repositories_links
WHERE assertion_id = ?
'''
delete_sources_links_by_citation = '''
DELETE FROM repositories_links
WHERE citation_id = ?
'''
delete_notes_links_couple = '''
DELETE FROM notes_links
WHERE couple_id = ?
'''
delete_media_links_image = '''
DELETE FROM media_links
WHERE media_id = ?
'''
delete_media_links_image_PATTERN = '''
DELETE FROM media_links
WHERE
'''
delete_media_links_image_nested_place = (
"{delete_media_links_image_PATTERN} {IMAGE_LINKS[0]} = ? AND media_id = ?")
delete_media_links_image_person = (
"{delete_media_links_image_PATTERN} {IMAGE_LINKS[1]} = ? AND media_id = ?")
delete_media_links_image_source = (
"{delete_media_links_image_PATTERN} {IMAGE_LINKS[2]} = ? AND media_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_person = '''
DELETE FROM notes_links
WHERE person_id = ?
'''
delete_notes_links_place_id = '''
DELETE FROM notes_links
WHERE place_id = ?
'''
delete_sources_links_source = '''
DELETE from repositories_links
WHERE source_id = ?
'''
delete_notes_links_unlink_note = '''
DELETE FROM notes_links
WHERE notes_links_id = ?
'''
delete_media = '''
DELETE FROM media
WHERE media_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_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_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 = ?
'''
insert_assertion_new_age = '''
INSERT INTO assertion (event_id, citation_id, ages)
VALUES (?, ?, ?)
'''
insert_assertion_new_date = '''
INSERT INTO assertion (event_id, citation_id, dates)
VALUES (?, ?, ?)
'''
insert_assertion_new_name = '''
INSERT INTO assertion (event_id, citation_id, names)
VALUES (?, ?, ?)
'''
insert_assertion_new_particulars = '''
INSERT INTO assertion (event_id, citation_id, particulars)
VALUES (?, ?, ?)
'''
insert_assertion_new_place = '''
INSERT INTO assertion (event_id, citation_id, places)
VALUES (?, ?, ?)
'''
insert_assertion_new_role = '''
INSERT INTO assertion (event_id, citation_id, roles)
VALUES (?, ?, ?)
'''
insert_citation_new = '''
INSERT INTO citation (citations, source_id)
VALUES (?, ?)
'''
insert_color_scheme = '''
INSERT INTO colors_type
VALUES (null, ?, ?, ?, ?, 0, 0)
'''
insert_couple_new = '''
INSERT INTO couple (person_id1, person_id2) VALUES (?, ?)
'''
insert_couple_new_partner = '''
INSERT INTO couple (person_id1, person_id2)
VALUES (?, ?)
'''
insert_date_format_default = '''
INSERT INTO date_format
VALUES (
1, 'dmy', 'abt', 'est', 'cal', 'bef/aft', 'BCE/CE',
'OS/NS', 'from_to', 'btwn_&')
'''
insert_event_birth_new_person = '''
INSERT INTO event (person_id, event_type_id, age)
VALUES (?, 1, '0')
'''
insert_event_new = '''
INSERT INTO event (event_type_id, person_id)
VALUES (?, ?)
'''
insert_event_type = '''
INSERT INTO event_type (event_types, couple, marital, after_death)
VALUES (?, ?, ?, ?)
'''
insert_event_type_new = '''
INSERT INTO event_type (event_types, couple, after_death)
VALUES (?, ?, ?)
'''
insert_family_tree_new = '''
INSERT INTO family_tree (
user_tree_title, directory, tree_is_open, full_path, filename, colors_type_id)
VALUES (?, ?, 1, ?, ?, 1)
'''
insert_image_new = '''
INSERT INTO media
VALUES (null, ?, ?, "", 1)
'''
insert_media_links_image_PATTERN = '''INSERT INTO media_links (media_id,'''
insert_media_links_image_nested_place = (
f"{insert_media_links_image_PATTERN} "
f"{IMAGE_LINKS[0]}, main_image) VALUES (?, ?, ?)")
insert_media_links_image_person = (
f"{insert_media_links_image_PATTERN} "
f"{IMAGE_LINKS[1]}, main_image) VALUES (?, ?, ?)")
insert_media_links_image_source = (
f"{insert_media_links_image_PATTERN} "
f"{IMAGE_LINKS[2]}, main_image) VALUES (?, ?, ?)")
insert_sources_links_locator = '''
INSERT INTO repositories_links (
source_id, citation_id, locator_id, repository_id, repository_type_id)
VALUES (?, ?, ?, ?, ?)
'''
insert_notes_links_note_PATTERN = '''
INSERT INTO notes_links (
'''
insert_notes_links_note_assertion = (
f"{insert_notes_links_note_PATTERN}{NOTE_LINKS[0]}, "
F"note_id, note_topic_order, note_topic) VALUES (?, ?, ?, ?)")
insert_notes_links_note_event = (
f"{insert_notes_links_note_PATTERN}{NOTE_LINKS[1]}, "
F"note_id, note_topic_order, note_topic) VALUES (?, ?, ?, ?)")
insert_notes_links_note_place = (
f"{insert_notes_links_note_PATTERN}{NOTE_LINKS[2]}, "
F"note_id, note_topic_order, note_topic) VALUES (?, ?, ?, ?)")
insert_roles_links_role_person = '''
INSERT INTO roles_links (event_id, role_type_id, person_id)
VALUES (?, ?, ?)
'''
insert_sources_links_source_and_repository = '''
INSERT INTO repositories_links (source_id, repository_id)
VALUES (?, ?)
'''
insert_locator = '''
INSERT INTO locator (locators, locator_type_id)
VALUES (?, ?)
'''
insert_locator_type = '''
INSERT INTO locator_type (locator_types, abbreviation)
VALUES (?, ?)
'''
insert_name = '''
INSERT INTO name
VALUES (null, ?, ?, ?, ?, '')
'''
insert_name_and_type = '''
INSERT INTO name (names, name_type_id, person_id, sort_order)
VALUES (?, ?, ?, ?)
'''
insert_name_placeholder = '''
INSERT INTO name
VALUES (null, ?, '_____ _____', 25, '_____, _____', '')
'''
insert_name_type_new = '''
INSERT INTO name_type (name_types)
VALUES (?)
'''
insert_nested_place = '''
INSERT INTO nested_place (nest0, nest1, nest2, nest3, nest4, nest5, nest6, nest7, nest8)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
insert_note = '''
INSERT INTO note
VALUES (null, ?, 0)
'''
insert_person_new = '''
INSERT INTO person (gender)
VALUES (?)
'''
insert_place_hint_check_dupes = '''
INSERT INTO place (check_dupes, hint)
VALUES (?, ?)
'''
insert_place_name = '''
INSERT INTO place_name (place_names, place_id, main_place_name)
VALUES (?, ?, ?)
'''
insert_place_name_main = '''
INSERT INTO place_name (place_names, place_id, main_place_name)
VALUES (?, ?, 1)
'''
insert_place_new = '''
INSERT INTO place (place_id)
VALUES (null)
'''
insert_repository = '''
INSERT INTO repository (repositories)
VALUES (?)
'''
insert_repository_type = '''
INSERT INTO repository_type (repository_types)
VALUES (?)
'''
insert_role_type = '''
INSERT INTO role_type VALUES (null, ?, 0, 0)
'''
insert_source_new = '''
INSERT INTO source (sources) VALUES (?)
'''
insert_source_type = '''
INSERT INTO source (sources, source_type_id)
VALUES (?, ?)
'''
insert_source_type_new = '''
INSERT INTO source_type (source_types)
VALUES (?)
'''
insert_to_do = '''
INSERT INTO to_do (to_dos, priority) VALUES (?, ?)
'''
select_all_birth_event_people = '''
SELECT event_id, event.person_id, a.person_id, b.person_id
FROM couple
JOIN person AS a
ON a.person_id = couple.person_id1
JOIN person AS b
ON b.person_id = couple.person_id2
JOIN event
ON event.couple_id = couple.couple_id
WHERE event.person_id IS NOT null
AND event_type_id IN (
SELECT event_type_id
FROM event_type
WHERE event_types in (
"birth", "adoption", "fosterage", "guardianship"))
'''
select_all_color_schemes_hidden = '''
SELECT colors_type_id, color1, color2, color3, color4, built_in, hidden
FROM colors_type
WHERE hidden = 1
'''
select_all_color_schemes_unhidden = '''
SELECT colors_type_id, color1, color2, color3, color4, built_in, hidden
FROM colors_type
WHERE hidden = 0
'''
select_all_event_types = '''
SELECT event_types
FROM event_type
ORDER BY event_types
'''
select_all_event_types_couple = '''
SELECT event_types
FROM event_type
WHERE hidden == 0
AND couple == 1
'''
select_all_event_types_id_couple = '''
SELECT event_types, event_type_id
FROM event_type
WHERE couple = 1
AND hidden = 0
ORDER BY event_types
'''
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_family_tree_ids = '''
SELECT family_tree_id FROM family_tree
'''
select_all_family_tree_titles = '''
SELECT user_tree_title
FROM family_tree
'''
select_all_family_trees = '''
SELECT user_tree_title, family_tree_id
FROM family_tree
'''
select_all_images_nested_place = '''
SELECT DISTINCT file_names, captions, main_image, media_links.media_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_names, captions, main_image, media_links.media_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_names, captions, main_image, media_links.media_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_locator_types = '''
SELECT locator_types FROM locator_type
ORDER BY locator_types COLLATE NOCASE
'''
select_all_name_types = '''
SELECT name_types FROM name_type ORDER BY name_types
'''
select_all_names = '''
SELECT names from name
'''
select_all_names_all_details_order_hierarchy = '''
SELECT person_id, names, name_types, 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, hierarchy
'''
select_all_nested_place_strings_and_ids = '''
SELECT a.place_names, b.place_names, c.place_names, d.place_names,
e.place_names, f.place_names, g.place_names, h.place_names, i.place_names,
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_place_names = '''
SELECT place_names
FROM place_name
'''
select_all_place_names_ordered = '''
SELECT DISTINCT place_names
FROM place_name
ORDER BY place_names
'''
select_all_repository_strings = '''
SELECT repositories
FROM repository
'''
select_all_repository_types = '''
SELECT repository_type_id, repository_types
FROM repository_type
'''
select_all_repository_types_only = '''
SELECT repository_types
FROM repository_type
ORDER BY repository_types COLLATE NOCASE
'''
select_all_source_types = '''
SELECT source_type_id, source_types
FROM source_type
WHERE hidden = 0
ORDER BY source_types
'''
select_all_sources = '''
SELECT sources 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, surety
FROM assertion
WHERE assertion_id = ?
'''
select_assertion_ids_by_event = '''
SELECT assertion_id, dates, places, particulars,
ages, roles
FROM assertion
WHERE event_id = ?
'''
select_assertion_ids_by_name = '''
SELECT assertion_id, names
FROM assertion
WHERE name_id = ?
'''
select_assertion_no_event = '''
SELECT assertion_id, citation_id, dates, places,
particulars, ages, roles, surety
FROM assertion
WHERE event_id is null and name_id is null
'''
select_assertion_no_name = '''
SELECT assertion_id, citation_id, names, surety
FROM assertion
WHERE name_id is null and event_id is null
'''
select_citation_by_id = '''
SELECT citations FROM citation WHERE citation_id = ?
'''
select_citation_by_source = '''
SELECT citation_id
FROM citation
WHERE source_id = ?
'''
select_citation_id = '''
SELECT citation_id FROM assertion WHERE assertion_id = ?
'''
select_citation_string_source = '''
SELECT citations, citation.source_id, sources
FROM citation
JOIN source ON source.source_id = citation.source_id
WHERE citation_id = ?
'''
select_citations_per_source = '''
SELECT citation_id, citations
FROM citation
JOIN source
ON citation.source_id = source.source_id
WHERE sources = ?
'''
select_closing_state_family_tree = '''
SELECT directory
FROM closing_state
JOIN family_tree
ON family_tree.family_tree_id = closing_state.prior_family_tree_id
WHERE closing_state_id = 1
'''
select_closing_state_openpic = '''
SELECT openpic
FROM closing_state
WHERE closing_state_id = 1
'''
select_closing_state_recent_files = '''
SELECT recent_files
FROM closing_state
WHERE closing_state_id = 1
'''
select_colors_type_colors_by_id = '''
SELECT color1, color2, color3, color4
FROM colors_type
WHERE colors_type_id = ?
'''
select_colors_type_colors_default = '''
SELECT color1, color2, color3, color4
FROM colors_type
WHERE colors_type_id = 1
'''
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_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_by_person_id = '''
SELECT couple_id, person_id1, person_id2
FROM couple
WHERE person_id1 = ? or person_id2 = ?
'''
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_event_progeny = '''
SELECT event.couple_id, person_id, person_id1, person_id2,
event_type_id, event_id
FROM event
LEFT JOIN couple ON couple.couple_id = event.couple_id
WHERE person_id1 = ? OR person_id2 = ?
ORDER BY date
'''
select_couple_partners = '''
SELECT person_id1, person_id2
FROM couple
WHERE couple_id = ?
'''
select_current_colors_type = '''
SELECT colors_type_id
FROM current
WHERE current_id = 1
'''
select_current_image_directory = '''
SELECT image_directory
FROM current
WHERE current_id = 1
'''
select_current_nested_place_id = '''
SELECT nested_place_id
FROM current
WHERE current_id = 1
'''
select_current_nested_place_source = '''
SELECT nested_place_id, source_id
FROM current
WHERE current_id = 1
'''
select_current_person_id = '''
SELECT person_id
FROM current
WHERE current_id = 1
'''
select_current_person_nested_place_source = '''
SELECT person_id, nested_place_id, source_id
FROM current
WHERE current_id = 1
'''
select_current_source_id = '''
SELECT source_id
FROM current
WHERE current_id = 1
'''
select_date_format = '''
SELECT date_formats, abt, est, cal, bef_aft, bc_ad, os_ns, span, range
FROM date_format
WHERE date_format_id = 1
'''
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_count_couple = '''
SELECT COUNT(couple_id)
FROM event
WHERE couple_id = ?
'''
select_event_couple_alt_parents = '''
SELECT person_id1, person_id2, event.couple_id, event_id, event_type_id,
date_sorter
FROM event
LEFT JOIN couple ON event.couple_id = couple.couple_id
WHERE event_type_id IN (48, 83, 95)
AND person_id = ?
'''
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_id = '''
SELECT couple_id
FROM event
WHERE event_id = ?
'''
select_event_death_date = '''
SELECT date, event_id
FROM event
WHERE person_id = ? AND event_type_id = 4
'''
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_offspring_by_parent = '''
SELECT event_id
FROM event
JOIN couple ON couple.couple_id = event.couple_id
WHERE event_type_id = 1
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_names, b.place_names, c.place_names, d.place_names,
e.place_names, f.place_names, g.place_names, h.place_names, i.place_names
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_parents = '''
SELECT person_id1, person_id2, event.couple_id, event_id
FROM person
LEFT JOIN event ON person.person_id = event.person_id
LEFT JOIN couple ON event.couple_id = couple.couple_id
WHERE event.person_id = ?
AND event_type_id = 1
'''
select_event_person = '''
SELECT event_id
FROM event
WHERE person_id = ?
'''
select_event_person_id = '''
SELECT person_id
FROM event
WHERE event_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_after_death = '''
SELECT event_types
FROM event_type
WHERE after_death = 1
'''
select_event_type_couple_bool = '''
SELECT couple
FROM event_type
WHERE event_types = ?
'''
select_event_type_id = '''
SELECT event_type_id, couple
FROM event_type
WHERE event_types = ?
'''
select_event_type_id_by_event_id = '''
SELECT event_type_id
FROM event
WHERE event_id = ?
'''
select_event_type_id_by_string = '''
SELECT event_type_id
FROM event_type
WHERE event_types = ?
'''
select_event_type_string = '''
SELECT event_types
FROM event_type
WHERE event_type_id = ?
'''
select_event_types_couple = '''
SELECT event_types
FROM event_type
WHERE couple = 1
'''
select_event_types_for_kintips = '''
SELECT event_type_id, event_types
FROM event_type
WHERE event_types in ('birth', 'fosterage', 'adoption', 'guardianship')
OR couple = 1
'''
select_events_details_generic = '''
SELECT event_types, 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_types
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_types, date, date_sorter, particulars
FROM event
JOIN event_type
ON event.event_type_id = event_type.event_type_id
WHERE event_id = ?
'''
select_family_tree_everything = '''
SELECT * FROM family_tree WHERE user_tree_title = ?
'''
select_family_tree_by_title = '''
SELECT family_tree_id, filename, tree_is_open
FROM family_tree
WHERE user_tree_title = ?
'''
select_family_tree_colors_type_id = '''
SELECT colors_type_id
FROM family_tree
WHERE family_tree_id = ?
'''
select_family_tree_details = '''
SELECT family_tree_id, user_tree_title, tree_is_open
FROM family_tree
WHERE directory = ?
'''
select_family_tree_directory = '''
SELECT directory, user_tree_title
FROM family_tree
WHERE family_tree_id = ?
'''
select_family_tree_id_by_title = '''
SELECT family_tree_id, directory, full_path
FROM family_tree
WHERE user_tree_title = ?
'''
select_family_tree_open = '''
SELECT family_tree_id
FROM family_tree
WHERE tree_is_open = 1
'''
select_family_tree_path = '''
SELECT full_path
FROM family_tree
WHERE family_tree_id = ?
'''
select_family_tree_title = '''
SELECT user_tree_title
FROM family_tree
WHERE directory = ?
'''
select_font_preference = '''
SELECT output_font, font_size, default_output_font, default_font_size
FROM font_preference
WHERE format_id = 1
'''
select_gender = '''
SELECT gender
FROM person
WHERE person_id = ?
'''
select_image_caption = '''
SELECT captions
FROM media
WHERE media_id = ?
'''
select_media_id = '''
SELECT media_id FROM media WHERE file_names = ? AND media_type_id = 1
'''
select_image_setting_builtin_default_images = '''
SELECT default_image_male, default_image_female, default_image_unisex,
default_image_nested_place, default_image_source
FROM image_setting
WHERE image_setting_id = 1
'''
select_image_setting_default_images = '''
SELECT image_male, image_female, image_unisex, image_nested_place, image_source,
default_image_male, default_image_female, default_image_unisex,
default_image_nested_place, default_image_source
FROM image_setting
WHERE image_setting_id = 1
'''
select_image_setting_openpic_dir = '''
SELECT openpic_dir, default_openpic_dir
FROM image_setting
WHERE image_setting_id = 1
'''
select_image_setting_user_images = '''
SELECT image_male, image_female, image_unisex, image_nested_place, image_source
FROM image_setting
WHERE image_setting_id = 1
'''
select_images_all = '''
SELECT file_names, media_id FROM media WHERE media_type_id = 1
'''
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, notes
FROM notes_links
JOIN note ON note.note_id = notes_links.note_id
WHERE assertion_id = ?
'''
select_media_links_count_image_PATTERN = "SELECT COUNT(media_id) FROM media_links WHERE"
select_media_links_count_image_nested_place = (
f"{select_media_links_count_image_PATTERN} {IMAGE_LINKS[0]} = ?")
select_media_links_count_image_person = (
f"{select_media_links_count_image_PATTERN} {IMAGE_LINKS[1]} = ?")
select_media_links_count_image_source = (
f"{select_media_links_count_image_PATTERN} {IMAGE_LINKS[2]} = ?")
select_media_links_main_image_name_nested_place = '''
SELECT file_names
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 main_image = 1
AND media_links.nested_place_id = ?
'''
select_media_links_main_image_name_person = '''
SELECT file_names
FROM media_links
JOIN person
ON media_links.person_id = person.person_id
JOIN media
ON media.media_id = media_links.media_id
WHERE main_image = 1
AND media_links.person_id = ?
'''
select_media_links_main_image_name_source = '''
SELECT file_names
FROM media_links
JOIN source
ON media_links.source_id = source.source_id
JOIN media
ON media.media_id = media_links.media_id
WHERE main_image = 1
AND media_links.source_id = ?
'''
select_media_links_main_image_PATTERN = '''
SELECT media_links_id, media_id, main_image
FROM media_links
WHERE media_id is not null
AND
'''
select_media_links_main_image_nested_place = (
f"{select_media_links_main_image_PATTERN} {IMAGE_LINKS[0]} = ? "
f"ORDER BY main_image DESC")
select_media_links_main_image_person = (
f"{select_media_links_main_image_PATTERN} {IMAGE_LINKS[1]} = ? "
f"ORDER BY main_image DESC")
select_media_links_main_image_source = (
f"{select_media_links_main_image_PATTERN} {IMAGE_LINKS[2]} = ? "
f"ORDER BY main_image DESC")
select_notes_links_note_order_PATTERN = '''
SELECT note_topic_order, notes_links_id
FROM notes_links
WHERE
'''
select_notes_links_note_order_assertion = (
f"{select_notes_links_note_order_PATTERN} {NOTE_LINKS[0]} = ? "
f"AND note_id IS NOT null")
select_notes_links_note_order_event = (
f"{select_notes_links_note_order_PATTERN} {NOTE_LINKS[1]} = ? "
f"AND note_id IS NOT null")
select_notes_links_note_order_place = (
f"{select_notes_links_note_order_PATTERN} {NOTE_LINKS[2]} = ? "
f"AND note_id IS NOT null")
select_notes_links_notes_available_assertion = f'''
SELECT DISTINCT note.note_id, note_topic, notes
FROM note
LEFT JOIN notes_links
ON note.note_id = notes_links.note_id
WHERE note.note_id NOT IN (
SELECT note_id
FROM notes_links
WHERE note_id IS NOT null
AND {NOTE_LINKS[0]} = ?)
OR note_topic IS null
'''
select_notes_links_notes_available_event = f'''
SELECT DISTINCT note.note_id, note_topic, notes
FROM note
LEFT JOIN notes_links
ON note.note_id = notes_links.note_id
WHERE note.note_id NOT IN (
SELECT note_id
FROM notes_links
WHERE note_id IS NOT null
AND {NOTE_LINKS[1]} = ?)
OR note_topic IS null
'''
select_notes_links_notes_available_place = f'''
SELECT DISTINCT note.note_id, note_topic, notes
FROM note
LEFT JOIN notes_links
ON note.note_id = notes_links.note_id
WHERE note.note_id NOT IN (
SELECT note_id
FROM notes_links
WHERE note_id IS NOT null
AND {NOTE_LINKS[2]} = ?)
OR note_topic IS null
'''
select_notes_links_PATTERN = '''
SELECT notes_links_id
FROM notes_links
JOIN note
ON note.note_id = notes_links.note_id
WHERE note_topic = ?
AND notes_links
'''
select_notes_links_assertions = (
f"{select_notes_links_PATTERN}.{NOTE_LINKS[0]} = ?")
select_notes_links_events = (
f"{select_notes_links_PATTERN}.{NOTE_LINKS[1]} = ?")
select_notes_links_places = (
f"{select_notes_links_PATTERN}.{NOTE_LINKS[2]} = ?")
select_notes_links_place = '''
SELECT notes_links.note_id, notes
FROM notes_links
JOIN note ON note.note_id = notes_links.note_id
WHERE place_id = ?
'''
select_roles_links_roles = '''
SELECT
roles_links_id,
role_types,
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_locators_by_repo_citation_source = '''
SELECT repositories_links.repository_id, repositories, repositories_links.repository_type_id,
repository_types, repositories_links.source_id, sources,
repositories_links.citation_id, citations, repositories_links.locator_id, locators,
locator.locator_type_id, locator_types
FROM locator
LEFT JOIN repositories_links ON locator.locator_id = repositories_links.locator_id
LEFT JOIN locator_type
ON locator.locator_type_id = locator_type.locator_type_id
LEFT JOIN repository
ON repository.repository_id = repositories_links.repository_id
LEFT JOIN repository_type
ON repositories_links.repository_type_id = repository_type.repository_type_id
LEFT JOIN source ON source.source_id = repositories_links.source_id
LEFT JOIN citation ON citation.citation_id = repositories_links.citation_id
WHERE repositories_links.locator_id is not null
AND (repositories_links.source_id = ? or repositories_links.citation_id = ?)
'''
select_locator_by_repo = '''
SELECT locator_id, locators, locator_type_id, sources_links_id
FROM locator
LEFT JOIN repositories_links ON locator.locator_id = repositories_links.locator_id
LEFT JOIN repository ON repository.repository_id = repositories_links.repository_id
LEFT JOIN repository_type
ON repositories_links.repository_type_id = repository_type.repository_type_id
WHERE repository_id = ?
'''
select_locators_by_repo = '''
SELECT locators
FROM locator
WHERE (
SELECT COUNT(sources_links_id)
FROM repositories_links
WHERE repositories_links.repository_id = ? AND source_id = ?) = 1 AND locator.repository_id = ?
'''
select_locator_type_id_by_string = '''
SELECT locator.locator_type_id
FROM locator
JOIN locator_type
ON locator.locator_type_id = locator_type.locator_type_id
WHERE locator_types = ?
'''
select_locator_types_abbrevs = '''
SELECT locator_type_id, abbreviation, locator_types
FROM locator_type
WHERE hidden = 0
ORDER BY locator_types COLLATE NOCASE
'''
select_name_all_current = '''
SELECT name_id, names, name_types
FROM name
JOIN name_type
ON name.name_type_id = name_type.name_type_id
WHERE person_id = ?
'''
select_name_details = '''
SELECT names, name_types, 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_types = ?
'''
select_name_type_id_by_string = '''
SELECT name_type_id
FROM name_type
WHERE name_types = ?
'''
select_nested_place_by_id = '''
SELECT place_names
FROM place_name
WHERE place_id = ?
AND main_place_name = 1
'''
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_ids = '''
SELECT nest0, nest1, nest2, nest3, nest4, nest5, nest6, nest7, nest8
FROM nested_place
WHERE nested_place_id = ?
'''
select_nested_place_inclusion = '''
SELECT nested_place_id, a.place_names, b.place_names, c.place_names, d.place_names,
e.place_names, f.place_names, g.place_names, h.place_names, i.place_names
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_nesting_inclusion = '''
SELECT nest0, nest1, nest2, nest3, nest4, nest5, nest6, nest7, nest8
FROM nested_place
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_note_id = '''
SELECT note_id
FROM notes_links
WHERE note_topic = ?
'''
select_note_privacy = '''
SELECT private
FROM note
JOIN notes_links ON note.note_id = notes_links.note_id
WHERE note_topic = ?
'''
select_notes_ids_by_text_PATTERN = '''
SELECT notes_links.note_id, notes
FROM note
JOIN notes_links ON note.note_id = notes_links.note_id
WHERE note_topic = ? AND
'''
select_notes_ids_by_text_assertion = (f"{select_notes_ids_by_text_PATTERN} {NOTE_LINKS[0]} = ?")
select_notes_ids_by_text_event = (f"{select_notes_ids_by_text_PATTERN} {NOTE_LINKS[1]} = ?")
select_notes_ids_by_text_place = (f"{select_notes_ids_by_text_PATTERN} {NOTE_LINKS[2]} = ?")
select_notes_per_PATTERN = '''
SELECT note_topic, notes, notes_links.note_id
FROM note
JOIN notes_links
ON notes_links.note_id = note.note_id
WHERE
'''
select_notes_per_assertion = (
f"{select_notes_per_PATTERN} {NOTE_LINKS[0]} = ? ORDER BY note_topic_order")
select_notes_per_event = (
f"{select_notes_per_PATTERN} {NOTE_LINKS[1]} = ? ORDER BY note_topic_order")
select_notes_per_place = (
f"{select_notes_per_PATTERN} {NOTE_LINKS[2]} = ? ORDER BY note_topic_order")
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 names LIKE ?
OR person.person_id LIKE ?
'''
select_person_event_gender_birth_date = '''
SELECT gender, date, event_id
FROM person
JOIN event on person.person_id = event.person_id
WHERE event_type_id = 1 AND event.person_id = ?
'''
select_person_gender_by_id = '''
SELECT gender
FROM person
WHERE person_id = ?
'''
select_place_check_dupes_from_name = '''
SELECT check_dupes
FROM place
JOIN place_name on place.place_id = place_name.place_id
WHERE place_names = ?
'''
select_place_details = '''
SELECT latitude, longitude, cartesian_coordinates, township, range,
section, legal_subdivision, hint, check_dupes
FROM place
WHERE place_id = ?
'''
select_place_hint = '''
SELECT hint
FROM place
WHERE place_id = ?
'''
select_place_id_with_name = '''
SELECT place_id
FROM place_name
WHERE place_names = ?
'''
select_place_ids_by_string = '''
SELECT place_id
FROM place_name
WHERE place_names = ?
'''
select_place_name_count = '''
SELECT COUNT(place_name_id)
FROM place_name
WHERE place_id = ?
'''
select_place_name_from_id = '''
SELECT place_names
FROM place_name
WHERE place_id = ?
'''
select_place_name_from_id_if_main = '''
SELECT place_names
FROM place_name
WHERE place_id = ?
AND main_place_name = 1
'''
select_place_names = '''
SELECT place_names, place_name_id
FROM place_name
WHERE place_id = ?
ORDER BY main_place_name DESC
'''
select_preference_combo_scroll_width = '''
SELECT combobox_scrollbar_width
FROM preference
WHERE preference_id = 1
'''
select_preference_backups_folder = '''
SELECT backups_folder FROM preference WHERE preference_id = 1
'''
select_preferences = '''
SELECT use_default_images
FROM preferences
WHERE preferences_id = 1
'''
select_repository_id_by_name = '''
SELECT repository_id
FROM repository
WHERE repositories = ?
'''
select_repository_details = '''
SELECT repositories, repository_types
FROM repository
JOIN repository_type
ON repository.repository_type_id = repository_type.repository_type_id
WHERE repository_id = ?
'''
select_repository_type_by_repository = '''
SELECT repository_types
FROM repository_type
JOIN repositories_links
ON repositories_links.repository_type_id = repository_type.repository_type_id
JOIN repository ON repository.repository_id = repositories_links.repository_id
WHERE repositories = ?
'''
select_role_type_by_id = '''
SELECT role_types
FROM role_type
WHERE role_type_id = ?
'''
select_role_type_id = '''
SELECT role_type_id
FROM role_type
WHERE role_types = ?
'''
select_role_types = '''
SELECT role_types
FROM role_type
'''
select_source_by_id = '''
SELECT sources 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 sources = ?
'''
select_source_type_id = '''
SELECT source_type_id
FROM source_type
WHERE source_types = ?
'''
select_to_do = '''
SELECT to_do_id, to_dos
FROM to_do
ORDER BY priority
'''
update_assertion_age = '''
UPDATE assertion
SET ages = ?
WHERE assertion_id = ?
'''
update_assertion_date = '''
UPDATE assertion
SET dates = ?
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 names = ?
WHERE assertion_id = ?
'''
update_assertion_particulars = '''
UPDATE assertion
SET particulars = ?
WHERE assertion_id = ?
'''
update_assertion_place = '''
UPDATE assertion
SET places = ?
WHERE assertion_id = ?
'''
update_assertion_role = '''
UPDATE assertion
SET roles = ?
WHERE assertion_id = ?
'''
update_assertion_surety = '''
UPDATE assertion
SET surety = ?
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_change_date_by_couple_id = '''
UPDATE change_date
SET couple_id = null
WHERE couple_id = ?
'''
update_closing_state_openpic = '''
UPDATE closing_state
SET openpic = ?
WHERE closing_state_id = 1
'''
update_closing_state_recent_files = '''
UPDATE closing_state
SET recent_files = ?
WHERE closing_state_id = 1
'''
update_closing_state_tree = '''
UPDATE closing_state
SET prior_family_tree_id = ?
WHERE closing_state_id = 1
'''
update_color_scheme_hide = '''
UPDATE colors_type
SET hidden = 1
WHERE colors_type_id = ?
'''
update_couple_new_father = '''
UPDATE couple
SET person_id1 = ?
WHERE couple_id = ?
'''
update_couple_new_mother = '''
UPDATE couple
SET person_id2 = ?
WHERE couple_id = ?
'''
update_couple_null1 = '''
UPDATE couple
SET person_id1 = null
WHERE couple_id = ?
'''
update_couple_null2 = '''
UPDATE couple
SET person_id2 = null
WHERE couple_id = ?
'''
update_current_color_scheme_default = '''
UPDATE current
SET colors_type_id = 1
WHERE current_id = 1
'''
update_current_image_directory = '''
UPDATE current
SET image_directory = ?
WHERE current_id = 1
'''
update_current_nested_place = '''
UPDATE current
set nested_place_id = ?
WHERE current_id = 1
'''
update_current_nested_place_to_1 = '''
UPDATE current
SET nested_place_id = 1
WHERE current_id = 1
'''
update_current_person = '''
UPDATE current
SET person_id = ?
WHERE current_id = 1
'''
update_current_person_to_1 = '''
UPDATE current
SET person_id = 1
WHERE current_id = 1
'''
update_current_source = '''
UPDATE current
SET source_id = ?
WHERE current_id = 1
'''
update_date_format_abt = '''
UPDATE date_format
SET abt = ?
WHERE date_format_id = 1
'''
update_date_format_befaft = '''
UPDATE date_format
SET bef_aft = ?
WHERE date_format_id = 1
'''
update_date_format_cal = '''
UPDATE date_format
SET cal = ?
WHERE date_format_id = 1
'''
update_date_format_date_formats = '''
UPDATE date_format
SET date_formats = ?
WHERE date_format_id = 1
'''
update_date_format_epoch = '''
UPDATE date_format
SET bc_ad = ?
WHERE date_format_id = 1
'''
update_date_format_est = '''
UPDATE date_format
SET est = ?
WHERE date_format_id = 1
'''
update_date_format_julegreg = '''
UPDATE date_format
SET os_ns = ?
WHERE date_format_id = 1
'''
update_date_format_range = '''
UPDATE date_format
SET range = ?
WHERE date_format_id = 1
'''
update_date_format_span = '''
UPDATE date_format
SET span = ?
WHERE date_format_id = 1
'''
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_couple_id = '''
UPDATE event
SET couple_id = null
WHERE couple_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_nested_place_unknown_by_nested_place_id = '''
UPDATE event
SET nested_place_id = 1
WHERE nested_place_id = ?
'''
update_event_particulars = '''
UPDATE event
SET particulars = ?
WHERE event_id = ?
'''
update_event_types = '''
UPDATE event
SET event_type_id = ?
WHERE event_id = ?
'''
update_family_tree = '''
UPDATE family_tree
SET tree_is_open = ?
WHERE family_tree_id = ?
'''
update_family_tree_all_trees_closed = '''
UPDATE family_tree
SET tree_is_open = 0
'''
update_family_tree_colors_type_id = '''
UPDATE family_tree
SET colors_type_id = ?
WHERE family_tree_id = ?
'''
update_family_tree_is_open_by_title = '''
UPDATE family_tree
SET tree_is_open = ?
WHERE user_tree_title = ?
'''
update_font_preference = '''
UPDATE font_preference
SET (output_font, font_size) = (?, ?)
WHERE format_id = 1
'''
update_image_caption = '''
UPDATE media
SET captions = ?
WHERE media_id = ?
'''
update_image_setting_image_PATTERN = '''
UPDATE image_setting
SET
'''
update_image_setting_image_female = (
f"{update_image_setting_image_PATTERN} {DEFAULT_IMAGES[0]} "
f"= ? WHERE image_setting_id = 1")
update_image_setting_image_male = (
f"{update_image_setting_image_PATTERN} {DEFAULT_IMAGES[1]} "
f"= ? WHERE image_setting_id = 1")
update_image_setting_image_nested_place = (
f"{update_image_setting_image_PATTERN} {DEFAULT_IMAGES[2]} "
f"= ? WHERE image_setting_id = 1")
update_image_setting_image_source = (
f"{update_image_setting_image_PATTERN} {DEFAULT_IMAGES[3]} "
f"= ? WHERE image_setting_id = 1")
update_image_setting_image_unisex = (
f"{update_image_setting_image_PATTERN} {DEFAULT_IMAGES[4]} "
f"= ? WHERE image_setting_id = 1")
update_image_setting_delete_user_images = '''
UPDATE image_setting
SET (image_male, image_female, image_unisex, image_nested_place,
image_source) = ("", "", "", "", "")
WHERE image_setting_id = 1
'''
update_sources_links_citation = '''
UPDATE repositories_links
SET citation_id = ?
WHERE locator_id = ?
'''
update_media_links_main_image = '''
UPDATE media_links
SET main_image = 1
WHERE media_links_id = ?
'''
update_media_links_main_image_PATTERN = '''
UPDATE media_links
SET main_image = 1
WHERE media_id = (
SELECT media_id
FROM media WHERE file_names = ? AND media_type_id = 1)
AND
'''
update_media_links_main_image_nested_place = (
f"{update_media_links_main_image_PATTERN} {IMAGE_LINKS[0]} = "
f"(SELECT current.{IMAGE_LINKS[0]} FROM current WHERE current_id = 1)")
update_media_links_main_image_person = (
f"{update_media_links_main_image_PATTERN} {IMAGE_LINKS[1]} = "
f"(SELECT current.{IMAGE_LINKS[1]} FROM current WHERE current_id = 1)")
update_media_links_main_image_source = (
f"{update_media_links_main_image_PATTERN} {IMAGE_LINKS[2]} = "
f"(SELECT current.{IMAGE_LINKS[2]} FROM current WHERE current_id = 1)")
update_notes_links_note_order = '''
UPDATE notes_links
SET note_topic_order = ?
WHERE notes_links_id = ?
'''
update_notes_links_order_PATTERN = '''
UPDATE notes_links
SET note_topic_order = ?
WHERE
'''
update_notes_links_note_order_assertion = (
f"{update_notes_links_order_PATTERN} {NOTE_LINKS[0]} = ? AND note_id = ?")
update_notes_links_note_order_event = (
f"{update_notes_links_order_PATTERN} {NOTE_LINKS[1]} = ? AND note_id = ?")
update_notes_links_note_order_place = (
f"{update_notes_links_order_PATTERN} {NOTE_LINKS[2]} = ? AND note_id = ?")
update_roles_links_person_id = '''
UPDATE roles_links
SET person_id = ?
WHERE roles_links_id = ?
'''
update_sources_links_repository = '''
UPDATE repositories_links
SET repository_id = ?
WHERE locator_id = ? AND repository_type_id = ?
'''
update_sources_links_repository_type = '''
UPDATE repositories_links
SET repository_type_id = ?
WHERE locator_id = ? AND repository_id = ?
'''
update_roles_links_role_type = '''
UPDATE roles_links
SET role_type_id = ?
WHERE roles_links_id = ?
'''
update_sources_links_source = '''
UPDATE repositories_links
SET source_id = ?
WHERE locator_id = ?
'''
update_media_links_unmain_image_PATTERN = '''
UPDATE media_links
SET main_image = 0
WHERE main_image = 1
AND media_links.
'''
update_media_links_unmain_image_nested_place = (
f"{update_media_links_unmain_image_PATTERN}{IMAGE_LINKS[0]} = ?")
update_media_links_unmain_image_person = (
f"{update_media_links_unmain_image_PATTERN}{IMAGE_LINKS[1]} = ?")
update_media_links_unmain_image_source = (
f"{update_media_links_unmain_image_PATTERN}{IMAGE_LINKS[2]} = ?")
update_locator_text = '''
UPDATE locator
SET locators = ?
WHERE locator_id = ?
'''
update_locator_type_id = '''
UPDATE locator
SET locator_type_id = ?
WHERE locator_id = ?
'''
update_name_type_sorter = '''
UPDATE name
SET (names, 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 notes = ?
WHERE note_id = ?
'''
update_note_privacy = '''
UPDATE note
SET private = ?
WHERE note_id = ?
'''
update_note_topic_PATTERN = '''
UPDATE notes_links
SET note_topic = ?
WHERE note_id = ?
AND
'''
update_note_topic_assertion = f"{update_note_topic_PATTERN} {NOTE_LINKS[0]} = ?"
update_note_topic_event = f"{update_note_topic_PATTERN} {NOTE_LINKS[1]} = ?"
update_note_topic_place = f"{update_note_topic_PATTERN} {NOTE_LINKS[2]} = ?"
update_person_gender = '''
UPDATE person
SET gender = ?
WHERE person_id = ?
'''
update_place_check_dupes = '''
UPDATE place
SET check_dupes = ?
WHERE place_id = ?
'''
update_place_details = '''
UPDATE place
SET (
latitude, longitude, cartesian_coordinates, township, range, section,
legal_subdivision, hint, check_dupes) = (?, ?, ?, ?, ?, ?, ?, ?, ?)
WHERE place_id = ?
'''
update_place_name_alt = '''
UPDATE place_name
SET place_names = ?
WHERE place_name_id = ?
'''
update_place_name_main = '''
UPDATE place_name
SET place_names = ?
WHERE place_id = ?
AND main_place_name = 1
'''
update_preference_backups_folder = '''
UPDATE preference
SET backups_folder = ?
WHERE preference_id = 1
'''
update_preference_combo_scroll_width = '''
UPDATE preference
SET combobox_scrollbar_width = ?
WHERE preference_id = 1
'''
update_preferences_default_images = '''
UPDATE preferences
SET use_default_images = ?
WHERE preferences_id = 1
'''