UNIGEDS rules vs. Treebard GUI design
May 9, 2024 17:04:04 GMT -8
Post by Uncle Buddy on May 9, 2024 17:04:04 GMT -8
Everything I post today will be as-is and unedited, straight from notes, because it's all so new and tentative and it constitutes a to-do list that's keeping me busy not blogging.
# UNIGEDS RULES
0. Treebard and UNIGEDS are in the public domain, created by a team of one, and that team of one will die someday just like an ordinary mortal, without having ever spent one iota of energy trying to enforce these rules. Take it or leave it, steal it or improve it, ruin it or replace it, I can't control that and I can't take it with me. Donations are accepted at www.treebard.com/donate. The donations go to my wife and son, not to me, so don't hesitate to keep donating after I kick the bucket.
1. Native unigeds.db tables never mention secondary features such as user data, app data, vendor data. Native unigeds.db tables are not about application output such as charts, reports, etc. Native tables only reference primary genealogy elements, i.e. real-world elements that pre-exist genealogy such as persons, places, events, sources, assertion, citations.
2. Vendors can't alter unigeds.db table schemas (can't add or delete columns or constraints to any native table). Please suggest improvements to UNIGEDS at www.treebard.proboards.com.
3. Vendors copy unigeds.db, giving the copy a name such as "smith_family_tree.xyz". Vendors add their own tables to this copy of unigeds.db only when a vendor feature references a primary key from a native table. Vendor table names have to append the vendor name to the table name, e.g. `current_tbd` or `places_fh` or `scary_gramps`.
4. Vendors use a separate back-end structure of their choice for secondary features whose persisted data does not reference unigeds.db primary keys.
5. In unigeds.db tables, for the sake of consistency and predictability, all primary keys are explicitly created using INTEGER PRIMARY KEY in the table schema. In vendor-added unigeds.db tables, it probably doesn't matter what the vendor uses for the PK. Incidentally, SQLite adds its own unique row ID whether you specify a primary key or not, but I'd rather tell it what to do than figure out what it did for me.
6. Vendor-added tables are not imported into or exported from UNIGEDS-backed tables unless two vendors actually talk to each other and agree to do this to each other.
7. A simple export program will be needed to create a subset of the vendor's copy of unigeds.db so that only native tables are provided for different vendors to use. For example, when faced with the rule against exporting vendor tables from unigeds.db copies, I had no choice but to follow my own rule by eliminating a GUI feature I'd been relying on and working hard on for years, in favor of a simpler system of finding existing notes so they can be re-used. This is an example of how UNIGEDS will become a standard for design: it provides strictures, hopefully sensible, illuminating ones, that limit what can be done. And providing limits based on common sense prevent designers from stepping over the line into bad design that don't make good enough sense.
8. The types tables in unigeds.db are empty. The vendor populates these tables to suit the interest of that vendor's application. Types table contain two columns: an ID column for the primary key and a name column for the string associated with the type. This design allows vendors and even users to create their own types, but doesn't force them to, since Treebard's types values are in the public domain so can be used without permission or licensing hassles.
9. The vendor can write his own queries to handle the UNIGEDS values, copy the queries from Treebard's query_strings.py, or some combination of the two. Treebard's query_strings.py is in the public domain.
# TREEBARD DESIGN
1. For data which doesn't reference unigeds.db keys and would otherwise be stored in a single-row table in SQL: Single-row tables are combined into config.txt. On load, an in-memory Python dictionary is created from config.txt. The dict is queried for values, not the .txt. Changes to values are stored directly into config.txt and the Python dict is re-created for each edit. The Python dict is not edited directly, always created from scratch in the same way. Since it's analogous to a SQL table with one fixed row, there are no inserts or deletes to config.txt, only selects and updates.
2. For data which doesn't reference unigeds.db keys and would be stored in multi-row SQL tables: a separate SQLite database tbard.db is used. Tables include to_do, family_tree, contact, and color_scheme. The family_tree table has a foreign key column for color_scheme_id. A junction table called links has foreign key columns for to_do_id, contact_id, and family_tree_id. Every table except family_tree has an INTEGER PRIMARY KEY schema for its primary key. The primary key of the family_tree table is the directory, and is referenced by `directory` throughout the code. For example, the sample tree that comes with Treebard is at the path `<current_drive>/treebard/sample_tree/sample_tree.tbd`. In this case, the value of the `directory` variable, and the PK in the family_tree table, is "sample_tree". If the user creates a new tree titled "Aunt Jean's Tree", the directory will be created by Treebard as `aunt_jeans_tree`.
3. For main user-selected things where there is a choice, such as a display name for a person or place that can have a variety of names: make a multi-row vendor table `main_tbd` in unigeds-treebard.
MAIN_ID MEDIA_LINKS_ID PLACE_NAME_ID NAME_ID
name_id is already linked to a person_id so no person_id col is needed.
place_name_id is already linked to a place_id so no place_id is needed.
But UNIGEDS' media_id can be linked to any number of elements, so instead of media_id we need media_links_id which is linked to only one element. This is the main display image for an element (person, place, or source in Treebard) that can have multiple images linked to it.
4. Some vendor tables are needed--or one table with all the below fields--for app data that reference UNIGEDS PKs. Define UNIQUE constraints to keep one-to-one relationships from being interpretable as one-to_many. Normally one-to-one is defined by placing a pair of data on the same table row, but app data is not allowed in unigeds.db native tables so foreign keys with UNIQUE constraints have to be used instead:
TABLE_NAME_VENDOR: COLUMNS**
assertion_tbd: assertion_id, surety (each FK is UNIQUE)
event_type_tbd: event_type_id, couple, marital, after_death (each FK is UNIQUE)
abbreviation_tbd: kin_type_id, locator_type_id, abbreviations (each FK is UNIQUE)
transcription_type_tbd: transcription_type_id, romanized, phonetic (each FK is UNIQUE)
place_tbd: place_id, dupes, hint (each FK is UNIQUE)
NOTES: Besides the generic INTEGER PRIMARY KEY terms used in a table schema, SQLite also provides AUTOINCREMENT which should not be used. INTEGER PRIMARY KEY already automatically creates unique incremented PKs, while AUTOINCREMENT adds the provision that deleted primary keys cannot be reused, which is restrictive and undesirable. For example, if there were no longer an ID 16 in a table, and there were 25,101 records in the table, the next created PK would have to be 25,202. If AUTOINCREMENT were not used, the next created PK could be 16. This becomes an issue when deleting many records from a table and expecting to start over from the lowest. With AUTOINCREMENT, this won't be possible; SQLite will start over from 25,202. During development this is an annoyance. After development, it could be an annoyance for the user who might remember some person IDs for example with duplicate common names used for different people, the user could save time by entering a remembered ID instead of sifting through duplicate names for the right person.
** Instead of several tiny tables I made this one:
CREATE TABLE misc_links_tbd (misc_links_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, kin_type_id INTEGER UNIQUE DEFAULT null, locator_type_id INTEGER UNIQUE DEFAULT null, abbreviations TEXT DEFAULT null, transcription_type_id INTEGER UNIQUE DEFAULT null, romanized BOOLEAN DEFAULT null, phonetic BOOLEAN DEFAULT null, place_id INTEGER UNIQUE DEFAULT null, dupes BOOLEAN 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 (kin_type_id) REFERENCES kin_type (kin_type_id), FOREIGN KEY (locator_type_id) REFERENCES locator_type (locator_type_id), FOREIGN KEY (transcription_type_id) REFERENCES transcription_type (transcription_type_id), FOREIGN KEY (place_id) REFERENCES place (place_id));
# UNIGEDS RULES
0. Treebard and UNIGEDS are in the public domain, created by a team of one, and that team of one will die someday just like an ordinary mortal, without having ever spent one iota of energy trying to enforce these rules. Take it or leave it, steal it or improve it, ruin it or replace it, I can't control that and I can't take it with me. Donations are accepted at www.treebard.com/donate. The donations go to my wife and son, not to me, so don't hesitate to keep donating after I kick the bucket.
1. Native unigeds.db tables never mention secondary features such as user data, app data, vendor data. Native unigeds.db tables are not about application output such as charts, reports, etc. Native tables only reference primary genealogy elements, i.e. real-world elements that pre-exist genealogy such as persons, places, events, sources, assertion, citations.
2. Vendors can't alter unigeds.db table schemas (can't add or delete columns or constraints to any native table). Please suggest improvements to UNIGEDS at www.treebard.proboards.com.
3. Vendors copy unigeds.db, giving the copy a name such as "smith_family_tree.xyz". Vendors add their own tables to this copy of unigeds.db only when a vendor feature references a primary key from a native table. Vendor table names have to append the vendor name to the table name, e.g. `current_tbd` or `places_fh` or `scary_gramps`.
4. Vendors use a separate back-end structure of their choice for secondary features whose persisted data does not reference unigeds.db primary keys.
5. In unigeds.db tables, for the sake of consistency and predictability, all primary keys are explicitly created using INTEGER PRIMARY KEY in the table schema. In vendor-added unigeds.db tables, it probably doesn't matter what the vendor uses for the PK. Incidentally, SQLite adds its own unique row ID whether you specify a primary key or not, but I'd rather tell it what to do than figure out what it did for me.
6. Vendor-added tables are not imported into or exported from UNIGEDS-backed tables unless two vendors actually talk to each other and agree to do this to each other.
7. A simple export program will be needed to create a subset of the vendor's copy of unigeds.db so that only native tables are provided for different vendors to use. For example, when faced with the rule against exporting vendor tables from unigeds.db copies, I had no choice but to follow my own rule by eliminating a GUI feature I'd been relying on and working hard on for years, in favor of a simpler system of finding existing notes so they can be re-used. This is an example of how UNIGEDS will become a standard for design: it provides strictures, hopefully sensible, illuminating ones, that limit what can be done. And providing limits based on common sense prevent designers from stepping over the line into bad design that don't make good enough sense.
8. The types tables in unigeds.db are empty. The vendor populates these tables to suit the interest of that vendor's application. Types table contain two columns: an ID column for the primary key and a name column for the string associated with the type. This design allows vendors and even users to create their own types, but doesn't force them to, since Treebard's types values are in the public domain so can be used without permission or licensing hassles.
9. The vendor can write his own queries to handle the UNIGEDS values, copy the queries from Treebard's query_strings.py, or some combination of the two. Treebard's query_strings.py is in the public domain.
# TREEBARD DESIGN
1. For data which doesn't reference unigeds.db keys and would otherwise be stored in a single-row table in SQL: Single-row tables are combined into config.txt. On load, an in-memory Python dictionary is created from config.txt. The dict is queried for values, not the .txt. Changes to values are stored directly into config.txt and the Python dict is re-created for each edit. The Python dict is not edited directly, always created from scratch in the same way. Since it's analogous to a SQL table with one fixed row, there are no inserts or deletes to config.txt, only selects and updates.
2. For data which doesn't reference unigeds.db keys and would be stored in multi-row SQL tables: a separate SQLite database tbard.db is used. Tables include to_do, family_tree, contact, and color_scheme. The family_tree table has a foreign key column for color_scheme_id. A junction table called links has foreign key columns for to_do_id, contact_id, and family_tree_id. Every table except family_tree has an INTEGER PRIMARY KEY schema for its primary key. The primary key of the family_tree table is the directory, and is referenced by `directory` throughout the code. For example, the sample tree that comes with Treebard is at the path `<current_drive>/treebard/sample_tree/sample_tree.tbd`. In this case, the value of the `directory` variable, and the PK in the family_tree table, is "sample_tree". If the user creates a new tree titled "Aunt Jean's Tree", the directory will be created by Treebard as `aunt_jeans_tree`.
3. For main user-selected things where there is a choice, such as a display name for a person or place that can have a variety of names: make a multi-row vendor table `main_tbd` in unigeds-treebard.
MAIN_ID MEDIA_LINKS_ID PLACE_NAME_ID NAME_ID
name_id is already linked to a person_id so no person_id col is needed.
place_name_id is already linked to a place_id so no place_id is needed.
But UNIGEDS' media_id can be linked to any number of elements, so instead of media_id we need media_links_id which is linked to only one element. This is the main display image for an element (person, place, or source in Treebard) that can have multiple images linked to it.
4. Some vendor tables are needed--or one table with all the below fields--for app data that reference UNIGEDS PKs. Define UNIQUE constraints to keep one-to-one relationships from being interpretable as one-to_many. Normally one-to-one is defined by placing a pair of data on the same table row, but app data is not allowed in unigeds.db native tables so foreign keys with UNIQUE constraints have to be used instead:
TABLE_NAME_VENDOR: COLUMNS**
assertion_tbd: assertion_id, surety (each FK is UNIQUE)
event_type_tbd: event_type_id, couple, marital, after_death (each FK is UNIQUE)
abbreviation_tbd: kin_type_id, locator_type_id, abbreviations (each FK is UNIQUE)
transcription_type_tbd: transcription_type_id, romanized, phonetic (each FK is UNIQUE)
place_tbd: place_id, dupes, hint (each FK is UNIQUE)
NOTES: Besides the generic INTEGER PRIMARY KEY terms used in a table schema, SQLite also provides AUTOINCREMENT which should not be used. INTEGER PRIMARY KEY already automatically creates unique incremented PKs, while AUTOINCREMENT adds the provision that deleted primary keys cannot be reused, which is restrictive and undesirable. For example, if there were no longer an ID 16 in a table, and there were 25,101 records in the table, the next created PK would have to be 25,202. If AUTOINCREMENT were not used, the next created PK could be 16. This becomes an issue when deleting many records from a table and expecting to start over from the lowest. With AUTOINCREMENT, this won't be possible; SQLite will start over from 25,202. During development this is an annoyance. After development, it could be an annoyance for the user who might remember some person IDs for example with duplicate common names used for different people, the user could save time by entering a remembered ID instead of sifting through duplicate names for the right person.
** Instead of several tiny tables I made this one:
CREATE TABLE misc_links_tbd (misc_links_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, kin_type_id INTEGER UNIQUE DEFAULT null, locator_type_id INTEGER UNIQUE DEFAULT null, abbreviations TEXT DEFAULT null, transcription_type_id INTEGER UNIQUE DEFAULT null, romanized BOOLEAN DEFAULT null, phonetic BOOLEAN DEFAULT null, place_id INTEGER UNIQUE DEFAULT null, dupes BOOLEAN 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 (kin_type_id) REFERENCES kin_type (kin_type_id), FOREIGN KEY (locator_type_id) REFERENCES locator_type (locator_type_id), FOREIGN KEY (transcription_type_id) REFERENCES transcription_type (transcription_type_id), FOREIGN KEY (place_id) REFERENCES place (place_id));