Creating and Deleting Types and Places

[Uncle Buddy]

In order to properly rewrite some of the code pertaining to the places functionalities, I have to deal with user-defined types and places, which I've been putting off for a long time.

The problem is that if a user likes certain types and has to create them himself because they're not built into Treebard, he'd probably rather not have to create them over every time he starts a new tree. I recall having to do this myself, and also there was the inconvenience of having event types in the way which I considered off-the-wall and which I had no intention of ever using. This sounds silly but when you could type "c" and WOULD get census--if only cartulary and caste were not in the way--then every time you start a new tree you will want to try and remember how to get to the event types list and delete cartulary and caste so you can type a c to get census. (I can't tell you how many times I looked up "cartulary" in the dictionary, and I still don't remember what it is. Why is that event type even there?)

I no longer consider census to be an event type, but in Treebard, if you want to add an event type "census" then you can. And if you want to delete a kin type, event type, or place, then you should be able to do it. A dialog will open if you try to delete an event type, for example. In a minimal version, you'd just be asked if you want to delete the type from the current tree or all trees. If you wanted to pick and choose, you'd be directed to open each tree separately and delete them one at a time, but normally I think users would rather have all their choices laid out in a single dialog once.

If a type is being used in a certain tree, that tree would be shown in the rows, but the checkbutton in the corresponding cell would be disabled and the user will be informed that the type can't be deleted from that tree because it's being used there.

The Delete Type dialog will have types as columns and trees as rows, and checkbuttons as cell values so the user can delete a type selectively. The Delete Place dialog will have single places like "Aspen" and "Colorado" and "USA" as columns, with trees as rows and checkbuttons in the cells.

Why am I lumping types and places together? These are things that Treebard will want to come pre-loaded with, which the user can add to. I'd started down this road at one time but it was too much to deal with at the time. So I kept the places and types in the database of the tree, knowing this would have to change eventually.

When you create a type, it will automatically be saved for all trees. Treebard should not be bloated by extraneous user settings. Most people want to do genealogy vs. spending a week learning how to set user options. So there's the danger that someone will dive into their work without setting any options, and end up having to redo their work later when they find out what the options are. An overly-complex set of options is enough to send some folks looking for a less intimidating program. So fewer settings is better than too many of them.

Treebard already has a global database, used during startup to access defaults when no current tree has been selected yet. Possibly to this `treebard.db`, tables can be added such as`trees_types` and those needed for places.

To handle the deleted types, they could be hidden instead of being deleted. A "deleted" event type would not display in the selection list. A boolean column called `hidden` and maybe also another boolean column called `deleted` could come into play. Users could outright delete types they created themselves, but built-in types would just be hidden.

I read somewhere that a lot of database professionals never delete anything. This can come in handy, for example, when creating an undo functionality. The `hidden` boolean already exists in some of Treebard's database tables, but possibly all of the types tables as well as the places tables should be moved to a database that is not specific to any particular tree.

I am not a fan of the idea of Treebard coming pre-loaded with places. Too many apps in our increasingly monoculture online environment are linked at the hip to google, google maps, etc. But I don't think users should have to re-create all their places every time they start a new tree. Genealogists who focus on certain geographical areas would find this particularly tedious, and the more research they do into boundary & name changes, the more annoying it would be to re-input all their findings. Since Treebard intends to encourage detailed research into historical places, we have to provide convenient ways to record and edit detailed research findings.

[Uncle Buddy]

Here's a Python file I made to test my ability to access two databases at once and to perform JOINs across them. I'd never done this before, and once I realized foreign keys were being accessed across databases I had to prove to myself that it was actually possible and not hard to do.

# join_tables_from_different_databases_sqlite

# stackoverflow.com/questions/28461997/joining-across-databases-with-sqlite3-pysqlite
# pythontic.com/database/sqlite/attach # THIS IS BETTER THAN THE FIRST REFERENCE

# APPARENTLY YOU CAN'T "create a foreign key" when doing this sort of thing but I don't think I ever do that anyway except manually. These example prove you can USE foreign keys.

import sqlite3

conn = sqlite3.connect('d:/treebard_gps/data/settings/treebard.db')
conn.execute('PRAGMA foreign_keys = 1')
cur = conn.cursor()
att = 'ATTACH DATABASE ? AS sample'
sample = 'd:/treebard_gps/data/sample_tree/sample_tree.tbd'
cur.execute(att, (sample,))

cur.execute(
   '''
       SELECT main.event_type.event_types
       FROM sample.finding
           JOIN main.event_type
               ON main.event_type.event_type_id = sample.finding.event_type_id
       WHERE sample.finding.finding_id < 10
   ''')
results = cur.fetchall()
print(results)

det = "DETACH DATABASE sample"
cur.execute(det)
cur.close()
conn.close()

# [('birth',), ('religious conversion',), ('occupation',), ('death',), ('marriage',), ('guardianship',), ('birth',), ('birth',), ('wedding',)]

# HERE'S THE SAME THING FROM QUERYING DONE ON SAME DATA IN A SINGLE DB
conn = sqlite3.connect("d:/treebard_gps/data/sample_tree/sample_tree.tbd")
cur = conn.cursor()

cur.execute(
   '''
       SELECT event_types
       FROM finding
           JOIN event_type
               ON event_type.event_type_id = finding.event_type_id
       WHERE finding_id < 10
   ''')
results2 = cur.fetchall()
cur.close()
conn.close()

print(results2)
# [('birth',), ('religious conversion',), ('occupation',), ('death',), ('marriage',), ('guardianship',), ('birth',), ('birth',), ('wedding',)]

# PROVE THAT OTHER QUERIES CAN BE DONE AND COMMITTED:
conn = sqlite3.connect('d:/treebard_gps/data/settings/treebard.db')
conn.execute('PRAGMA foreign_keys = 1')
cur = conn.cursor()
att = 'ATTACH DATABASE ? AS sample'
sample = 'd:/treebard_gps/data/sample_tree/sample_tree.tbd'
cur.execute(att, (sample,))

cur.execute(
   '''
       INSERT INTO main.event_type (event_types)
       VALUES ("lunch")
   ''')
conn.commit()
cur.execute(
   '''
       SELECT main.event_type.event_types, main.event_type.event_type_id
       FROM main.event_type
       WHERE main.event_type.event_types = 'lunch'
   ''')
results3 = cur.fetchone()
print(results3)
# ('lunch', 111)

cur.execute(
   '''
       UPDATE main.event_type
       SET event_types = 'dinner'
       WHERE event_types = 'lunch'
   ''')
conn.commit()
cur.execute(
   '''
       SELECT main.event_type.event_types, main.event_type.event_type_id
       FROM main.event_type
       WHERE main.event_type.event_types = 'dinner'
   ''')
results4 = cur.fetchone()
print(results4)
# ('dinner', 111)

cur.execute(
   '''
       DELETE FROM main.event_type
       WHERE event_types IN ('breakfast', 'lunch', 'dinner')
   ''')
conn.commit()
cur.execute(
   '''
       SELECT main.event_type.event_types, main.event_type.event_type_id
       FROM main.event_type
       WHERE main.event_type.event_type_id > 100
   ''')
results5 = cur.fetchall()
print(results5)
# [('inurnment', 101), ('cohabitation', 102), ('living together', 103), ('wedding anniversary', 104)]

det = "DETACH DATABASE sample"
cur.execute(det)
cur.close()
conn.close()

[Uncle Buddy]

To summarize the code in the previous post:

In order to use two databases at once in SQLite & Python, you only have to...

1) ...add three lines of code to the connection at the beginning:

att = 'ATTACH DATABASE ? AS sample'
sample = 'd:/treebard_gps/data/sample_tree/sample_tree.tbd'
cur.execute(att, (sample,))

2) ...add two lines of code to the disconnection at the end:

det = "DETACH DATABASE sample"
cur.execute(det)

3) ...refer to the two databases by their aliases using dot notation, i.e. `database.table.column`. In the example, `sample` is the alias for the specific family tree data and `main` is the alias for the global database that contains data common to all trees such as types and places. As expected, the extra prefix `database. ...` is not needed unless there are ambiguous column names without it.

In order to use two databases at once in the SQLite console tool, it's even easier:

ATTACH 'd:/treebard_gps/data/sample_tree/sample_tree.tbd' AS sample;

DETACH DATABASE sample;

[Uncle Buddy]

It looks like someone's statement that you can't create foreign keys between attached databases really threw me somewhere useless. I haven't been able to find anything that doesn't work between the main connection and an attached database. Based on this, I think I have a green light to go ahead with the plan to separate place tables and type tables into the common database. Here are my unedited notes, which could have just been replaced with "Whaddaya mean I can't create foreign keys with attached databases???":

Jun 26, 2022 7:07:49 GMT -8 Uncle Buddy said:

APPARENTLY YOU CAN'T "create a foreign key" when doing this sort of thing...

I think I figured out how attaching a second database becomes a limitation, and I think I know what has to be done about it.

The problem might be that if there's a place table in the common database, then in the family tree database, you'll have to do this in the common database (pseudocode)...

# create a junction table
CREATE TABLE nested_places (nested_place_id INTEGER PRIMARY KEY AUTOINCREMENT, nest0 INTEGER REFERENCES place (place_id)...)

# create a new place
INSERT INTO main.place (places) VALUES ('Siberia');

...but in the family tree database you'd need a schema like this:

CREATE TABLE assertion (assertion_id INTEGER PRIMARY KEY AUTOINCREMENT, nested_place_id INTEGER REFERENCES main.nested_place (main.nested_place_id)...)

Based on the statement I read about attaching tables that you "can't create foreign keys", I predict that this will not work. But look at this JOIN between two databases which is based on equivalence of a foreign key to the primary key in the other database, because this did work. What is the difference?

SELECT main.event_type.event_types
FROM sample.finding
JOIN main.event_type
ON main.event_type.event_type_id = sample.finding.event_type_id
WHERE sample.finding.finding_id < 10

To figure out what the difference is, here are the two schemas involved:
(in the common database):
CREATE TABLE event_type (event_type_id INTEGER PRIMARY KEY AUTOINCREMENT, event_types TEXT...)
(in the family tree database):
CREATE TABLE finding (finding_id INTEGER PRIMARY KEY AUTOINCREMENT, event_type_id INTEGER REFERENCES event_type (event_type_id)...)

The examples appear to be working because there's an event_type table in the family tree database that's a duplicate of the event_type_table in the common database. If I were to drop the original event_type table from the family tree table, the join may or may not still work, because the JOIN reference would still be valid...

JOIN main.event_type
ON main.event_type.event_type_id = sample.finding.event_type_id

...but the schema reference would not:

CREATE TABLE finding (finding_id INTEGER PRIMARY KEY AUTOINCREMENT, event_type_id INTEGER REFERENCES event_type (event_type_id)...)

To test this, first I'll recreate the finding table, retaining the event_type_id column but deleting the line...

FOREIGN KEY (event_type_id) REFERENCES event_type (event_type_id),

...which should make me wonder what this line is even for, if everything still works. I know that foreign keys enforce referential integrity but is that anything other than stuff like `ON UPDATE CASCADE, ON DELETE CASCADE`? If not, maybe I shouldn't be bothering with the FOREIGN KEY line or with that line `conn.execute('PRAGMA foreign_keys = 1')`. What I'm saying is this: can you use foreign keys in some sort of informal way (to do joins only) without the rest of "enforcing referential integrity", whatever that is? It seems that, as usual, my knowledge of SQL is only skin deep. Is `FOREIGN KEY (event_type_id) REFERENCES event_type (event_type_id)` the thing you can't do with an attached database, i.e. "creating foreign keys"?

If the examples still work after recreating the finding table, then the next thing I'll try is to drop the event_type table from the family tree database. The example should still work then.

My hypothesis at this point is that the references among tables using values equivalent to primary keys are not exactly what foreign keys are really about, in formal SQL terms. So everything I want to do should be doable easily, because "enforcing referential integrity" in Treebard is not left up to SQL anyway, if that just means stuff like ON DELETE CASCADE. I don't use that feature of SQL at all, in fact I went to considerable effort to delete it from all my schemas.

If everything still works, at that point, I'll experimentally try to redo the table with FOREIGN KEY statement but this time trying to create a foreign key between two tables. If that fails as I expect it to, the next step will be to re-research the definition of foreign key, enforcing referential integrity, and to try and figure out what others have said on this topic if I can figure out how to phrase an internet search on the topic. As a last resort, after doing all that, I'd post a pruned-down version of this discussion, with examples, as a question on Stack Overflow and see if anyone's willing to explain what I'm missing in my understanding of SQL.

RESULTS:

Deleting the FOREIGN KEY line from the finding table (while retaining the event_type_id column) changed nothing. As expected, I still get the same results:
[('birth',), ('religious conversion',), ('occupation',), ('death',), ('marriage',), ('guardianship',), ('birth',), ('birth',), ('wedding',)]
('lunch', 112)
('dinner', 112)
[('inurnment', 101), ('cohabitation', 102), ('living together', 103), ('wedding anniversary', 104)]

Renaming the event_type table in the family tree table to event_type_new; nothing changes:
[('birth',), ('religious conversion',), ('occupation',), ('death',), ('marriage',), ('guardianship',), ('birth',), ('birth',), ('wedding',)]
('lunch', 113)
('dinner', 113)
[('inurnment', 101), ('cohabitation', 102), ('living together', 103), ('wedding anniversary', 104)]

Finally, creating a test table in the family tree database and attaching it and trying to create a foreign key between the two databases does in fact seem to work. Also it seems the dot notation to say which database you're referring to is not needed, although it would be needed in case of naming ambiguity:
C:\Users\Lutherman>sqlite3 d:/treebard_gps/data/settings/treebard.db
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
app_setting          default_format       place
chart_type           event_type           places_places
closing_state        kin_type             report_type
color_scheme         media_type           role_type
default_date_format  name_type
sqlite> attach 'd:/treebard_gps/data/sample_tree/sample_tree.tbd' as sample;
sqlite> CREATE TABLE sample.test (test_id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT);
sqlite> SELECT * FROM test;
sqlite> INSERT INTO sample.test VALUES (1, "test1");
sqlite> INSERT INTO sample.test VALUES (2, "test2");
sqlite> select * from test;
1|test1
2|test2
sqlite> ALTER TABLE sample.test ADD COLUMN fk INTEGER REFERENCES main.event_type (main.event_type_id);
Error: near ".": syntax error
sqlite> ALTER TABLE sample.test ADD COLUMN fk INTEGER REFERENCES event_type (event_type_id);
sqlite> .schema test
CREATE TABLE sample.test (test_id INTEGER PRIMARY KEY AUTOINCREMENT, text TEXT, fk INTEGER REFERENCES event_type (event_type_id));
sqlite> UPDATE test SET fk = 97;
sqlite> select * from test;
1|test1|97
2|test2|97
sqlite> SELECT event_types FROM event_type JOIN test ON test.fk = event_type.event_type_id;
acceleration
acceleration
sqlite> detach database 'sample';
sqlite> select * from test;
Error: no such table: test
sqlite>

[Uncle Buddy]

Apparently I had forgotten, or never knew, that you can tell a column to reference a column in another table, for example in a JOIN or in a CREATE TABLE command, without giving this relationship the status of a foreign key with its enforcement powers. In other words, I'd assumed you could not do a JOIN without a foreign key. Apparently you can. Apparently a reference between two columns can exist without being a foreign key, and the difference is that a foreign key has built-in constraints, such as not allowing something in a foreign key column unless the reference primary key actually exists. Also there are more constraints that can be added, such as ON DELETE CASCADE etc.

On the other hand, you can use a reference column like a foreign key (I don't know what to call it now) without making it a real foreign key.

This confusion has cost me some time but I have a lot of structural changes to make in order to separate the family tree database from the type tables and place tables, and I wanted to know what might go wrong before I go to all that trouble.

I still have a question in my mind, whether the two following schemas are the same:

CREATE TABLE table1 (pk_id PRIMARY KEY, table2_id INTEGER REFERENCES table2 (table2_id));

CREATE TABLE table1 (pk_id PRIMARY KEY, table2_id INTEGER, FOREIGN KEY (table2_id) REFERENCES table2 (table2_id));

I'm guessing they do exactly the same thing. I don't think you need to say to SQLite that something references something else unless SQLite is expected to do something about it. I'm guessing that referencing an ID from another table can be done casually with no ill effects until you wind up in a situation where it would have been nice to have a foreign key constraint to prevent some bad data from being saved.

[Uncle Buddy]

It looks like someone's statement that you can't create foreign keys between attached databases really threw me...

Now that I'm trying to put this into practice, I found that I was not able to do this:

CREATE TABLE sample.table (..., new_column INTEGER REFERENCES main.some_table (some_column), ...);

Error: near ".": syntax error

The error was in regards to `main.some_table`. The `CREATE TABLE sample.table` was not a problem.

EDIT:

Yesterday when I posted this I made the statement that it was in fact not possible to create a foreign key from an attached table to the main connected table. This was apparently wrong again (I've been flip-flopping on this every other day...).

The problem is the dot notation, and the dot notation is not needed unless the referencing table and the referenced table--which are in different databases--have the same name. For example, assuming you're connected to a main database `main` and another database `tree` is attached, and you want a column `dog_name_id` in same-named tables from both databases. You can't do this only because you can't do `main.dog_name`:

CREATE TABLE tree.dog_name(... , dog_name_id INTEGER, ... , FOREIGN KEY (dog_name_id) REFERENCES main.dog_name (dog_name_id), ...)

It seems like there is a simple workaround, which is to not give the two tables the same name, and then the dot notation would not be needed. I haven't actually had any problems (see real schemas in next post) since I don't repeat any table names in two databases that will have to interact.

In case that's still confusing, see the next post.

[Uncle Buddy]

[EDIT: Possibly this post is wrong. See the post right after this one...]

The output below will show real schemas which gave no problem, wherein foreign keys were created which referenced same-named columns between two differently-named tables in two different databases.

sqlite3 d:/treebard_gps/data/settings/treebard.db
SQLite version 3.34.0 2020-12-01 16:14:00

sqlite> .tables
app_setting          default_format       nested_place
chart_type           event_type           place
closing_state        kin_type             places_places
color_scheme         media_type           report_type
default_date_format  name_type            role_type

sqlite> attach 'd:/treebard_gps/data/sample_tree/sample_tree.tbd' as tree;

sqlite> .tables
app_setting           report_type           tree.images_elements
chart_type            role_type             tree.links_links
closing_state         tree.assertion        tree.media
color_scheme          tree.citation         tree.name
default_date_format   tree.contact          tree.note
default_format        tree.current          tree.person
event_type            tree.date_change      tree.project
kin_type              tree.date_format      tree.report
media_type            tree.finding          tree.repository
name_type             tree.findings_notes   tree.setting
nested_place          tree.findings_roles   tree.source
place                 tree.format           tree.test
places_places         tree.image            tree.to_do

sqlite> .schema nested_place
CREATE TABLE nested_place (nested_place_id INTEGER PRIMARY KEY AUTOINCREMENT, nest0 INTEGER NOT NULL DEFAULT 1, nest1 INTEGER DEFAULT NULL, nest2 INTEGER DEFAULT NULL, nest3 INTEGER DEFAULT NULL, nest4 INTEGER DEFAULT NULL, nest5 INTEGER DEFAULT NULL, nest6 INTEGER DEFAULT NULL, nest7 INTEGER DEFAULT NULL, nest8 INTEGER DEFAULT NULL, FOREIGN KEY (nest0) REFERENCES place (place_id), FOREIGN KEY (nest1) REFERENCES place (place_id), FOREIGN KEY (nest2) REFERENCES place (place_id), FOREIGN KEY (nest3) REFERENCES place (place_id), FOREIGN KEY (nest4) REFERENCES place (place_id), FOREIGN KEY (nest5) REFERENCES place (place_id), FOREIGN KEY (nest6) REFERENCES place (place_id), FOREIGN KEY (nest7) REFERENCES place (place_id), FOREIGN KEY (nest8) REFERENCES place (place_id));

sqlite> .schema assertion
CREATE TABLE tree."assertion" (assertion_id INTEGER PRIMARY KEY AUTOINCREMENT, citation_id INTEGER NOT NULL, finding_id INTEGER, event_type_id INTEGER, date TEXT NOT NULL DEFAULT '-0000-00-00-------', nested_place_id INTEGER, particulars TEXT NOT NULL DEFAULT '', age TEXT NOT NULL DEFAULT '', name_id INTEGER, surety FLOAT DEFAULT 3.0, FOREIGN KEY (citation_id) REFERENCES citation (citation_id), FOREIGN KEY (finding_id) REFERENCES finding (finding_id), FOREIGN KEY (event_type_id) REFERENCES event_type (event_type_id), FOREIGN KEY (nested_place_id) REFERENCES nested_place (nested_place_id), FOREIGN KEY (name_id) REFERENCES name (name_id));

sqlite> .schema finding
CREATE TABLE tree."finding" (finding_id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT NOT NULL DEFAULT '-0000-00-00-------', particulars TEXT NOT NULL DEFAULT '', age TEXT NOT NULL DEFAULT '', person_id INTEGER DEFAULT null, event_type_id INTEGER NOT NULL, date_sorter TEXT NOT NULL DEFAULT '0,0,0', age1 TEXT NOT NULL DEFAULT '', kin_type_id1 INTEGER, age2 TEXT NOT NULL DEFAULT '', kin_type_id2 INTEGER, person_id1 INTEGER DEFAULT null, person_id2 INTEGER DEFAULT null, nested_place_id INTEGER NOT NULL DEFAULT 1 REFERENCES nested_place (nested_place_id), FOREIGN KEY (person_id) REFERENCES person (person_id), FOREIGN KEY (event_type_id) REFERENCES event_type (event_type_id) FOREIGN KEY (kin_type_id1) REFERENCES kin_type (kin_type_id), FOREIGN KEY (kin_type_id2) REFERENCES kin_type (kin_type_id), FOREIGN KEY (person_id1) REFERENCES person (person_id), FOREIGN KEY (person_id2) REFERENCES person (person_id));

sqlite> .schema name
CREATE TABLE tree."name" (name_id INTEGER PRIMARY KEY AUTOINCREMENT, person_id INTEGER NOT NULL, names TEXT NOT NULL, name_type_id INTEGER NOT NULL DEFAULT 18, sort_order TEXT NOT NULL, used_by TEXT NOT NULL DEFAULT '', FOREIGN KEY (person_id) REFERENCES person (person_id), FOREIGN KEY (name_type_id) REFERENCES name_type (name_type_id));

The moral of the story is: if someone on Stack Overflow (for example) says, "You can't do X," (even in the accepted answer), look at the date on the answer. Also, in this case, despite the answerer's high reputation, they had made the bald "no can do" remark without explanation or example, so unless you were the person who wrote the answer or someone with experience equivalent to theirs, you'd have to guess what they were talking about like I had to. Possibly SQLite has changed since the remark was made, because the same post if I remember correctly also said that you "had to" use the dot notation whenever referencing any tables, once you'd attached a second database to the connection. This also turned out not to be true, which I found by experimentation, and then when I looked at the instructions (out of sheer desperation), even SQLite's documentation says you don't have to name the table except in the case of ambiguous namings.

[Uncle Buddy]

The problem didn't show up when creating the schema, but when trying to implement it. It stands to reason that creating foreign keys between databases would be based on the assumption that the two databases can always see each other. I guess since the creators of SQLite can't assume that two databases will always be on the same connection, they shouldn't or can't allow enforcement of foreign keys between two databases.

When I tried to create the schema with `main.table` I couldn't use the dot notation. So I dropped the dot notation and the schema was allowed to exist. But when I wrote the query, I had to use the dot notation, which was then not allowed in the query.

It's another in a series of flip-flops on this issue. Today I'm saying that you have to settle for referencing separate databases kinda informally or off-the-cuff, not using a schema that creates a foreign key or uses the `REFERENCES` key word. I will now attempt to actually do some research on this question but I doubt it's worth putting a lot of time into. Probably my lack of experience is all that's preventing this from being obvious to me.

[Uncle Buddy]

From a question on this very topic (2014)...

stackoverflow.com/questions/22289837/foreign-key-in-main-database-referencing-attached-database

SQLite's built-in foreign key constraints do not work across databases. The only workaround would be to manually write check constraints and triggers that do the same checking.

...which is followed by a comment (2015):

According to the SQLite documentation, triggers can only act upon tables that are in the same database as the table that was changed. See the section titled "Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers" here: sqlite.org/lang_createtrigger.html

I don't know what a trigger is. I've read about them a long time ago. I'm not sure whether it's worth the trouble. Can Treebard allow the user to input a nested place that includes a primary (single nest) place which doesn't actually exist in the place table? Somehow I think this is not going to happen anyway. Think about it. The nested_place table has references in it. Maybe they're not "foreign keys" formally, but they're still references and to get the strings that are displayed for each nest in a nested place, the integer in the column of the nested_place table is looked up in the place table. So in order to input the nested_place nests to begin with, the place already has to exist. I don't see where FK enforcement is a show stopper in this scenario.

I could redo the table schema so that there is no `REFERENCES` key word between databases.

This other thought keeps occurring to me: what if the place table is in the common database, but the user has to re-create nested places for each tree? I don't like that idea. However, there will be notes attached to nested places also, and the note table is in the tree database.

The right thing to do might be to keep the place and nested_place tables in the common database where they are now, and add therein a places_notes table so that any note could be re-used with any number of nested places or single places. This table would have an FK for places_notes_id, one for place_id, and one for nested_place_id. The purpose of the notes is to replace the notion of recording a span date for a nesting during which time that nested place (e.g. "Dallas, Republic of Texas") actually existed. I no longer think this can be handled by a date only. The research involved does not lend itself to being expressed as a date. I think a note is needed.

Such a date is apparently used with nested places in Gramps, and then Gramps uses the date to make decisions for the user as to which place the user intended. Treebard is opposed to doing that sort of thing. The user has to make his own decisions. That's why we do research and save notes. We have to review our notes to decide what we think about something.

Tentatively then, the plan is to 1) redo schemas attempting to cross-reference foreign keys among different databases, and 2) add a table places_notes to treebard.db.

Fast progress has been made on the new Duplicate Place Dialog which is shaping up to be far simpler and superior to the previous version which was broken during some part of the recent rewritings that have taken place.

[Uncle Buddy]

As far as foreign key constraints are concerned, I already got rid of some of them. I no longer use anything like ON DELETE CASCADE or ON UPDATE CASCADE. This forces the developer to think when creating and deleting things, and remember to insert, update, and delete values from tables when required, in order to maintain referential integrity. This does not have to be done by magical FK constraints. Most developers who answered questions about this said they preferred not to add these magical constraints.

Take this thinking a step further, and who needs ANY foreign key constraints? Not that I'm against them. They've helped me catch plenty of mistakes. Of course SQLite existed for some time before it used any foreign key constraints at all. Because of this, when doing an insert, update or delete query, you have to remember to manually turn FKs on. In SQLite 4, if it's ever finished, they'll be on by default.

What if I googled "live without foreign key constraints entirely" or something along those lines?

[Uncle Buddy]

Jul 3, 2022 0:07:53 GMT -8 Uncle Buddy said:

What if I googled "live without foreign key constraints entirely" or something along those lines?

It's about time I figured this out. A foreign key without foreign key constraints is still a foreign key. The app can include its own checks and constraints. There are advantages and disadvantages either way. For example, without FK constraints, you can delete primary keys and orphaned foreign keys in either order, or you can wait a while if you want. You can collect the orphans and delete them all at once as a batch operation. The disadvantage is that you can't forget to do stuff, or your data will get messed up.

More than one of these references also mentions the importance of consistent naming practices for tables and columns. From the last reference below:

...in situations where you have good naming conventions, the foreign keys are instantly clear. Developers don't have to look up foreign keys, or try a query to see if it works; they can just see the relationships.

From the same Stack Overflow answer:

I think foreign key constraints quickly become helpful as the number of different teams grow using a database grows. It becomes difficult to enforce consistent naming; knowledge of the DB becomes fragmented; it's easy for db actions to have unintended consequences for another team.

Well fortunately we're not running a Mars probe here. It's just a genealogy app.

docs.planetscale.com/learn/operating-without-foreign-key-constraints

ardalis.com/related-data-without-foreign-keys/

stackoverflow.com/questions/12065526/database-design-without-foreign-keys

stackoverflow.com/questions/1876013/why-are-foreign-keys-more-used-in-theory-than-in-practice