new_tree.py
Dec 26, 2023 0:09:17 GMT -8
Post by Uncle Buddy on Dec 26, 2023 0:09:17 GMT -8
<drive>:\treebard\new_tree.py Last Changed 2024-07-25
# new_tree.py
import sqlite3
from base import tbard_path
import dev_tools as dt
from dev_tools import look, seeline
# The tables in `unigeds.db` are completely blank so we don't have to
# remember which tables have default rows in them. Default values are added,
# edited, and remembered in this module. These functions run every time the
# user creates a new tree, since the default new tree is now completely
# blank. This code is run in `make_tree()` which is in `opening.py`.
# When a table schema is altered while developing in `sample_tree.db`,
# do this to make a whole new `unigeds.db` in a few easy steps:
# ************FIRST DELETE `unigeds.db` and `unigeds.sql`***************
# C:\Users\Lutherman>sqlite3 d:/treebard/data/sample_tree/sample_tree.tbd
# SQLite version 3.34.0 2020-12-01 16:14:00
# sqlite> .output d:/treebard/data/settings/unigeds.sql
# sqlite> .schema
# sqlite> .exit
# C:\Users\Lutherman>sqlite3 d:/treebard/data/settings/unigeds.db < d:/treebard/data/settings/unigeds.sql
# Error: near line 1: object name reserved for internal use: sqlite_sequence
# (I DON'T KNOW what the error is but the import of the schema seems to work fine.)
# IMPORTANT FINAL STEP: Since this new version of unigeds.db is being created by copying
# sample_tree.tbd, it will include main_tbd, current_tbd, and traits_tbd. These tables
# should be dropped since they should be created by this module and should not exist
# in unigeds.db:
# sqlite3 d:/treebard/data/settings/unigeds.db
# sqlite> drop table main_tbd;
# sqlite> drop table traits_tbd;
# sqlite> drop table current_tbd;
EVENT_TYPES = [
(1,'birth'),
(2,'marriage'),
(3,'illness'),
(4,'death'),
(5,'burial'),
(6,'occupation'),
(7,'imprisonment'),
(8,'deportation'),
(9,'invention'),
(10,"religious conversion"),
(11,'wedding'),
(12,"ran away from tree"),
(13,'residence'),
(14,'autopsy'),
(15,'divorce'),
(16,'engagement'),
(17,'annulment'),
(18,'separation'),
(19,"eye color"),
(20,"hair color"),
(21,'height'),
(22,'weight'),
(23,'build'),
(24,'complexion'),
(25,'race'),
(26,"ethnic origin"),
(27,"marks, scars"),
(28,'disability'),
(29,'condition'),
(30,'religion'),
(31,'career'),
(32,"number of children, total"),
(33,"number of children, living"),
(34,"marital status"),
(35,"political affiliation"),
(36,"special talent"),
(37,'hobby'),
(38,"able to read"),
(39,"able to write"),
(40,"first language"),
(41,'nationality'),
(42,"education level"),
(43,"draft registration"),
(44,"legal problem"),
(45,"tobacco use"),
(46,"alcohol use"),
(47,"drug problem"),
(48,'guardianship'),
(49,'inquest'),
(50,'relationship'),
(51,"bar mitzvah"),
(52,"bas mitzvah"),
(53,"jury duty"),
(54,'baptism'),
(55,'excommunication'),
(56,'betrothal'),
(57,'resignation'),
(58,'naturalization'),
(59,"marriage license"),
(60,'christening'),
(61,'confirmation'),
(62,'cremation'),
(63,'will'),
(64,'retirement'),
(65,'ordination'),
(66,'graduation'),
(67,'emigration'),
(68,'probate'),
(69,'enrollment'),
(70,'execution'),
(71,'employment'),
(72,"land grant"),
(73,"name change"),
(74,"land purchase"),
(75,"land sale"),
(76,"military induction"),
(77,"military enlistment"),
(78,"military discharge"),
(79,"military promotion"),
(80,"military service"),
(81,"marriage banns"),
(82,"missing in action"),
(83,'adoption'),
(84,'reburial'),
(85,"filing for divorce"),
(86,"reading of the will"),
(87,'exhumation'),
(88,'funeral'),
(89,"celebration of life"),
(90,'partnership'),
(91,"natural disaster"),
(92,'blessing'),
(93,"anniversary celebration"),
(94,"first communion"),
(95,'fosterage'),
(96,"posthumous offspring"),
(97,'immigration'),
(98,"marriage contract"),
(99,'reunion'),
(100,"scattering of ashes"),
(101,'inurnment'),
(102,'cohabitation'),
(103,"living together"),
(104,"wedding anniversary"),
(105,"patent filing"),
(106,"patent granted"),
(107,'interment'),
(108,'learning'),
(109,'conversion'),
(110,'travel'),
(111,'caste'),
(112,'description'),
(113,"number of marriages"),
(114,'property'),
(115,'imaginary'),
(116,"marriage settlement"),
(117,"unknown event type"),
(118,'education'),
(119,'specialty'),
(120,'award')]
TRAITS_TBD_EVENT_TYPES = [
(2, 1, 1, 0),
(5, 0, 0, 1),
(11, 1, 1, 0),
(14, 0, 0, 1),
(15, 1, 1, 0),
(16, 1, 0, 0),
(17, 1, 1, 0),
(18, 1, 1, 0),
(49, 0, 0, 1),
(50, 1, 1, 0),
(56, 1, 1, 0),
(59, 1, 1, 0),
(62, 0, 0, 1),
(68, 0, 0, 1),
(81, 1, 0, 0),
(84, 0, 0, 1),
(85, 1, 1, 0),
(86, 0, 0, 1),
(87, 0, 0, 1),
(88, 0, 0, 1),
(89, 0, 0, 1),
(90, 1, 1, 0),
(93, 1, 1, 0),
(96, 0, 0, 1),
(98, 1, 1, 0),
(100, 0, 0, 1),
(101, 0, 0, 1),
(102, 1, 1, 0),
(103, 1, 1, 0),
(104, 1, 1, 0),
(116, 1, 1, 0),
(1, 0, 0, 0),
(3, 0, 0, 0),
(4, 0, 0, 0),
(6, 0, 0, 0),
(7, 0, 0, 0),
(8, 0, 0, 0),
(9, 0, 0, 0),
(10, 0, 0, 0),
(12, 0, 0, 0),
(13, 0, 0, 0),
(19, 0, 0, 0),
(20, 0, 0, 0),
(21, 0, 0, 0),
(22, 0, 0, 0),
(23, 0, 0, 0),
(24, 0, 0, 0),
(25, 0, 0, 0),
(26, 0, 0, 0),
(27, 0, 0, 0),
(28, 0, 0, 0),
(29, 0, 0, 0),
(30, 0, 0, 0),
(31, 0, 0, 0),
(32, 0, 0, 0),
(33, 0, 0, 0),
(34, 0, 0, 0),
(35, 0, 0, 0),
(36, 0, 0, 0),
(37, 0, 0, 0),
(38, 0, 0, 0),
(39, 0, 0, 0),
(40, 0, 0, 0),
(41, 0, 0, 0),
(42, 0, 0, 0),
(43, 0, 0, 0),
(44, 0, 0, 0),
(45, 0, 0, 0),
(46, 0, 0, 0),
(47, 0, 0, 0),
(48, 0, 0, 0),
(51, 0, 0, 0),
(52, 0, 0, 0),
(53, 0, 0, 0),
(54, 0, 0, 0),
(55, 0, 0, 0),
(57, 0, 0, 0),
(58, 0, 0, 0),
(60, 0, 0, 0),
(61, 0, 0, 0),
(63, 0, 0, 0),
(64, 0, 0, 0),
(65, 0, 0, 0),
(66, 0, 0, 0),
(67, 0, 0, 0),
(69, 0, 0, 0),
(70, 0, 0, 0),
(71, 0, 0, 0),
(72, 0, 0, 0),
(73, 0, 0, 0),
(74, 0, 0, 0),
(75, 0, 0, 0),
(76, 0, 0, 0),
(77, 0, 0, 0),
(78, 0, 0, 0),
(79, 0, 0, 0),
(80, 0, 0, 0),
(82, 0, 0, 0),
(83, 0, 0, 0),
(91, 0, 0, 0),
(92, 0, 0, 0),
(94, 0, 0, 0),
(95, 0, 0, 0),
(97, 0, 0, 0),
(99, 0, 0, 0),
(105, 0, 0, 0),
(106, 0, 0, 0),
(107, 0, 0, 0),
(108, 0, 0, 0),
(109, 0, 0, 0),
(110, 0, 0, 0),
(111, 0, 0, 0),
(112, 0, 0, 0),
(113, 0, 0, 0),
(114, 0, 0, 0),
(115, 0, 0, 0),
(117, 0, 0, 0),
(118, 0, 0, 0),
(119, 0, 0, 0),
(120, 0, 0, 0)]
NAME_TYPES = [
(1,"birth name"),
(2,"also known as"),
(3,'pseudonym'),
(4,"ID number",),
(5,"adoptive name"),
(6,"married name"),
(7,'handle'),
(8,"alternate spelling"),
(9,'mis-spelling'),
(10,"legally changed name"),
(11,"stage name"),
(12,'nickname'),
(13,"call name"),
(14,"official name"),
(15,"anglicized name"),
(16,"unknown name"),
(17,"religious order name"),
(18,"other name type"),
(19,"code name"),
(20,"pen name"),
(21,"given name"),
(22,"reference name"),
(23,'surname'),
(24,"name at work"),
(25,"placeholder for name"),
(26,"immigrant name"),
(27,'title'),
(28,"name prefix"),
(29,"name suffix"),
(30,"surname prefix"),
(31,"usual name"),
(32,"social security number"),
(33,"permit number"),
(34,"forum handle")]
SOURCE_TYPES = [
(1,'newspaper'),
(2,"technical journal"),
(3,'magazine'),
(4,"genealogy newsletter"),
(5,'blog'),
(6,"baptism record"),
(7,"bible record"),
(8,"birth certificate"),
(9,"birth register"),
(10,'book'),
(11,"grave marker"),
(12,'census'),
(13,"church record"),
(14,"death certificate"),
(15,'yearbook'),
(16,"tree_id (organization)"),
(17,"tree_id (church)"),
(18,"tree_id (city etc.)"),
(19,"tree_id (telephone)"),
(20,'deed'),
(21,"land patent"),
(22,"patent (invention)"),
(23,'diary'),
(24,"email message"),
(25,'interview'),
(26,"personal knowledge"),
(27,"family story"),
(28,"audio record"),
(29,"video record"),
(30,"letter or postcard"),
(31,"probate record"),
(32,'will'),
(33,"legal proceedings record"),
(34,'manuscript'),
(35,'map'),
(36,"marriage certificate"),
(37,"marriage license"),
(38,"marriage register (county etc.)"),
(39,"marriage record (church)"),
(40,"naturalization file"),
(41,'obituary'),
(42,"pension file"),
(43,'photograph'),
(44,"painting or drawing"),
(45,"passenger list"),
(46,"tax roll"),
(47,"death index"),
(48,"birth index"),
(49,"town record"),
(50,'transcription'),
(51,"web page"),
(52,"military record"),
(53,"draft registration"),
(54,"enlistment record"),
(55,"muster roll"),
(56,"burial record"),
(57,"cemetery record"),
(58,"death notice"),
(59,"marriage index"),
(60,"secondary source"),
(61,"alumni publication"),
(62,"parish record"),
(63,'passport'),
(64,"passport application"),
(65,"identification card"),
(66,"immigration record"),
(67,"border crossing record"),
(68,"funeral tree records"),
(69,'theory'),
(70,'guess'),
(71,'hunch'),
(72,'hypothesis'),
(73,"article (clipping; source unknown)"),
(74,'newsletter'),
(75,'tree_id'),
(76,'brochure'),
(77,'pamphlet'),
(78,'poster'),
(79,'jewelry'),
(80,"church service bulletin"),
(81,'advertisement'),
(82,'cemetery'),
(83,"application for social security card form SS5 (USA)"),
(84,'contact'),
(85,"prison records"),
(86,"arrest record"),
(87,'researcher')]
PLACE_TYPES = [
(1,'nation'),
(2,'province'),
(3,'state'),
(4,'county'),
(5,'city'),
(6,'township'),
(7,'parish'),
(8,'island'),
(9,'archipelago'),
(10,'continent'),
(11,"unincorporated town"),
(12,'barangay'),
(13,'purok'),
(14,"Indian country"),
(15,'colony'),
(16,'settlement'),
(17,'village'),
(18,'empire'),
(19,'address')]
ROLE_TYPES = [
(1,"religious officer"),
(2,"justice of the peace"),
(3,"arresting officer"),
(4,'supervisor'),
(5,'employer'),
(6,'midwife'),
(7,'bridesmaid'),
(8,"best man"),
(9,"bible bearer"),
(10,"flower girl"),
(11,'witness'),
(12,'employee'),
(13,'assistant'),
(14,'roommate'),
(15,'landlady'),
(16,'landlord'),
(17,'unknown'),
(18,"foster parent"),
(19,"makeup artist"),
(20,'financier'),
(21,'florist'),
(22,'usher'),
(23,'photographer'),
(24,'bartender'),
(25,'bodyguard'),
(26,"adoptive parent"),
(27,'hairdresser'),
(28,'chauffeur'),
(29,'treasurer'),
(30,'trainer'),
(31,'secretary'),
(32,'navigator'),
(33,'pallbearer'),
(39,'neighbor'),
(40,'maid'),
(41,'pilot'),
(43,'undertaker'),
(44,"mining partner"),
(47,"legal guardian"),
(55,'other'),
(87,"interior decorator"),
(88,'executioner'),
(90,'driver'),
(96,'host'),
(97,'hostess'),
(99,"farm hand"),
(100,"ranch hand"),
(103,"junior partner"),
(114,'butler'),
(115,'boarder'),
(116,'chef'),
(117,"patent attorney")]
TRANSCRIPTION_TYPES = [
(1,"International Phonetic Alphabet (API)"),
(2,'Wade-Giles'),
(3,"Hanyu Pinyin (Pinyin)"),
(4,"Wapuro romaji (romaji)"),
(5,'hangul'),
(6,'kana'),
(7,'IPA'),
(8,"Traditional IPA"),
(9,"Modern IPA")]
TRAITS_TBD_TRANSCRIPTION_TYPES = [
(1, 0, 1),
(2, 1, 0),
(3, 1, 0),
(4, 1, 0),
(5, 0, 1),
(6, 0, 1),
(7, 0, 1),
(8, 0, 1),
(9, 0, 1)]
KIN_TYPES = [
(1,'mother','mother'),
(2,'father','father'),
(3,'parent','parent'),
(4,'son','son'),
(5,'daughter','daughter'),
(6,'child','child'),
(7,'sister','sister'),
(8,'brother','brother'),
(9,'sibling','sibling'),
(10,'husband','husband'),
(11,'wife','wife'),
(12,'spouse','spouse'),
(13,'partner','partner'),
(14,"son of sibling of father",'cousin'),
(15,"son of sibling of mother",'cousin'),
(16,"daughter of sibling of father",'cousin'),
(17,"daughter of sibling of mother",'cousin'),
(18,"father of father",'grandfather'),
(19,"mother of father",'grandmother'),
(20,"father of mother",'grandfather'),
(21,"mother of mother",'grandmother'),
(22,"parent of father",'grandparent'),
(23,"parent of mother",'grandparent'),
(24,"son of son",'grandson'),
(25,"son of daughter",'grandson'),
(26,"daughter of son",'granddaughter'),
(27,"daughter of daughter",'granddaughter'),
(28,"sister of mother",'aunt'),
(29,"sister of father",'aunt'),
(30,"brother of father",'uncle'),
(31,"brother of mother",'uncle'),
(32,"daughter of sister of mother",'niece'),
(33,"daughter of brother of mother",'niece'),
(34,"daughter of sister of father",'niece'),
(35,"daughter of brother of father",'niece'),
(36,"son of sister of mother",'nephew'),
(37,"son of brother of mother",'nephew'),
(38,"son of sister of father",'nephew'),
(39,"son of brother of father",'nephew'),
(40,"father of father of father",'great-grandfather'),
(41,"father of mother of father",'great-grandfather'),
(42,"father of father of mother",'great-grandfather'),
(43,"father of mother of mother",'great-grandfather'),
(44,"mother of father of father",'great-grandmother'),
(45,"mother of mother of father",'great-grandmother'),
(46,"mother of father of mother",'great-grandmother'),
(47,"mother of mother of mother",'great-grandmother'),
(48,"brother of father of father",'great-uncle'),
(49,"sister of mother of father",'great-aunt'),
(50,"brother of father of mother",'great-uncle'),
(51,"sister of mother of mother",'great-aunt'),
(52,"father of spouse",'father-in-law'),
(53,"mother of spouse",'mother-in-law'),
(54,"brother of spouse",'brother-in-law'),
(55,"husband of sibling",'brother-in-law'),
(56,"husband of brother of spouse",'brother-in-law'),
(57,"husband of sister of spouse",'brother-in-law'),
(58,"sister of spouse",'sister-in-law'),
(59,"wife of sibling",'sister-in-law'),
(60,"wife of brother of spouse",'brother-in-law'),
(61,"wife of sister of spouse",'brother-in-law'),
(62,"husband of child",'son-in-law'),
(63,"wife of child",'daughter-in-law')]
MEDIA_TYPES = [
(1,'image'),
(2,'audio'),
(3,'video'),
(4,'tree movie'),
(5,'newsreel'),
(6,'microfilm'),
(7,'microfiche'),
(8,'cd-rom')]
def populate_tables(family_tree_database_file):
""" Run this in opening.py `make_tree`. """
conn = sqlite3.connect(family_tree_database_file)
conn.execute('PRAGMA foreign_keys = 1')
cur = conn.cursor()
create_treebard_tables(cur, conn)
insert_types_values(cur, conn)
insert_default_values(cur, conn)
cur.close()
conn.close()
def create_treebard_tables(cur, conn):
cur.execute(
''' CREATE TABLE IF NOT EXISTS current_tbd (
current_tbd_id INTEGER PRIMARY KEY,
nested_place_id INTEGER,
person_id INTEGER,
citation_id INTEGER REFERENCES citation (citation_id),
source_id integer references source (source_id),
image_directory TEXT DEFAULT '',
FOREIGN KEY (person_id) REFERENCES person (person_id),
FOREIGN KEY (nested_place_id)
REFERENCES nested_place (nested_place_id))
''')
conn.commit()
cur.execute(
''' CREATE TABLE IF NOT EXISTS main_tbd (
main_tbd_id INTEGER PRIMARY KEY,
media_links_id INTEGER,
place_name_id INTEGER,
name_id INTEGER,
FOREIGN KEY (media_links_id) REFERENCES media_links (media_links_id),
FOREIGN KEY (place_name_id) REFERENCES place_name (place_name_id),
FOREIGN KEY (name_id) REFERENCES name (name_id))
''')
conn.commit()
cur.execute(
''' CREATE TABLE IF NOT EXISTS traits_tbd (
traits_tbd_id INTEGER PRIMARY KEY,
assertion_id INTEGER UNIQUE DEFAULT null,
surety REAL DEFAULT null,
event_type_id INTEGER UNIQUE DEFAULT null,
couple BOOLEAN DEFAULT null,
marital BOOLEAN DEFAULT null,
after_death BOOLEAN DEFAULT null,
transcription_type_id INTEGER UNIQUE DEFAULT null,
romanized BOOLEAN DEFAULT null,
phonetic BOOLEAN DEFAULT null,
place_id INTEGER UNIQUE DEFAULT null,
hint TEXT DEFAULT null,
FOREIGN KEY (assertion_id) REFERENCES assertion (assertion_id),
FOREIGN KEY (event_type_id)
REFERENCES event_type (event_type_id),
FOREIGN KEY (transcription_type_id)
REFERENCES transcription_type (transcription_type_id),
FOREIGN KEY (place_id) REFERENCES place (place_id))
''')
conn.commit()
def insert_default_values(cur, conn):
""" Treebard uses a default person which can be changed into a real person
with a real name etc., and a default unknown place which can't be
changed but is not seen by the user.
"""
cur.execute("INSERT INTO person (person_id, gender) VALUES (1, 'unknown')")
conn.commit()
cur.execute(
''' INSERT INTO place (place_id, latitude, longitude)
VALUES (1, '', '')
''')
conn.commit()
cur.execute(
''' INSERT INTO place_name (place_name_id, place_name_text, place_id)
VALUES (1, 'unknown', 1)
''')
conn.commit()
cur.execute("INSERT INTO main_tbd (place_name_id) VALUES (1)")
conn.commit()
cur.execute(
''' INSERT INTO nested_place (
nested_place_id, nest0, nest1, nest2, nest3, nest4, nest5,
nest6, nest7, nest8)
VALUES (1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
''')
conn.commit()
cur.execute(
''' INSERT INTO event (
event_id, date, particulars, age, person_id, event_type_id,
date_sorter, nested_place_id, age1, age2, couple_id)
VALUES (1, "-0000-00-00-------", "", 0, 1, 1, "0,0,0", 1, "", "", null)
''')
conn.commit()
cur.execute(
""" SELECT name_type_id FROM name_type
WHERE name_type_text = 'placeholder for name'
""")
name_type_id = cur.fetchone()[0]
cur.execute(
''' INSERT INTO name (
name_id, person_id, name_text, name_type_id, sort_order, used_by)
VALUES (
1, 1, "Default person #1", ?, "default person #1", "")
''',
(name_type_id,))
conn.commit()
cur.execute("INSERT INTO main_tbd (name_id) VALUES (1)")
conn.commit()
cur.execute("INSERT INTO current_tbd (person_id) VALUES (1)")
conn.commit()
def insert_types_values(cur, conn):
cur.executemany(
''' INSERT INTO event_type (event_type_id, event_type_text)
VALUES (?, ?)
''',
EVENT_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO kin_type (
kin_type_id, kin_type_text, common_name)
VALUES (?, ?, ?)
''',
KIN_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO media_type (media_type_id, media_type_text)
VALUES (?, ?)
''',
MEDIA_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO name_type (name_type_id, name_type_text)
VALUES (?, ?)
''',
NAME_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO place_type (
place_type_id, place_type_text)
VALUES (?, ?)
''',
PLACE_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO role_type (
role_type_id, role_type_text)
VALUES (?, ?)
''',
ROLE_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO source_type (
source_type_id, source_type_text)
VALUES (?, ?)
''',
SOURCE_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO transcription_type (
transcription_type_id, transcription_type_text)
VALUES (?, ?)
''',
TRANSCRIPTION_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO traits_tbd (
transcription_type_id, romanized, phonetic)
VALUES (?, ?, ?)
''',
TRAITS_TBD_TRANSCRIPTION_TYPES)
conn.commit()
cur.executemany(
''' INSERT INTO traits_tbd (
event_type_id, couple, marital, after_death)
VALUES (?, ?, ?, ?)
''',
TRAITS_TBD_EVENT_TYPES)
conn.commit()