Rewriting Places Feature (Part 2)

[Uncle Buddy]

This has been a highly experimental process, rewriting the places feature. The places feature has been maybe the hardest part of creating Treebard, since the beginning. When elements with unique IDs (primary keys in the database) have to be combined into complex or compound elements with their own unique IDs, and both sets of IDs are important, the way it's always seemed to me is that this feature is skating on the edge of denormalization, when in order to get the job done, some kind-of important sacred guidelines of database design are gonna have to be sidestepped or compromised.

Recently I moved all the types tables and the place tables out of the tree database into the common database so that the user can create types and places and they will apply to all the user's trees. This comes with a drawback: no foreign key enforcement between the two databases. In the case of types, I don't think it's going to be a problem. The text column such as "spouse" in the kin_type table has a UNIQUE NOT NULL constraint in every types table, so the user will be free to create kin types, event types, media type, etc. without fear of duplicating types that already exist. Compared to nested places, types are simple.

Serious improvements have been made in nested places and place autofill code in the past week. These nested places, which have to be unique, should probably define their primary key as the unique columns themselves instead of the AUTOINCREMENT integer foreign keys I've always used. This would add a unique constraint to the combination of columns (the nested place) that I could have used today when I found that--because many places with different individual place_ids can be called "Paris"--when the GUI seems to be working, the database still has to be checked. It would have been nice if those columns that comprise a nested place that has to be unique anyway... had also comprised a primary key. Then the duplicates wouldn't have been allowed and I wouldn't have posted earlier that things were going well. Things aren't exactly going to hell, but they are going slower than I thought when I posted earlier today.

I don't know that the lack of foreign key enforcement has caused a problem already, but I strongly suspect that when I reference nested_place_id from the finding and assertion tables, I will be happier in the long run if I put the place tables back into the tree database now before the lack of FK constraints teaches me a lesson the hard way. I'll leave the types tables in the common database and see how it goes, but I don't want to see how it's gonna go for something as complex as the nested places feature to try and live without FK constraints.

There are other ways for the user to get his places data into another tree. I can create an import/export tool that will allow place tables from two Treebard databases to be merged. For me, this isn't even a big priority, although there are times when the research involved to try and track a place through its various changes of name, boundary, and enclosing place can get pretty hairy.

I seem to recall from elementary school social studies classes that the maps which show mainly position and boundary vs. terrain are called "political" maps. I can see why, and I don't generally follow many of the shenanigans of politicians as they fiddle around with the borders of places. (Although I have a great true story to tell--with a terrible ending--about how President Bill Clinton took a historical rose quartz mining claim away from me on his way out of office, so he could have a National Park on his CV.) Aside from those rare cases where it affects me personally, I don't care about the border-mongering of politicians until it comes time to determine if a family or individual I'm researching actually lived in the subject place, or if the relevant place name is just plain wrong for the period of time in question.

So tomorrow I'll be creating my first multi-column primary keys and foreign keys? And definitely moving the place tables back into the tree database.

[Uncle Buddy]

While I am ready to learn a new trick if needed, it should be the right new trick. What I was considering last night was to create a primary key out of nine columns just so those nine columns would be constrained to contain a unique combination of values in each row of the table. Then I got to thinking. Most of those values are going to be null. And I'd have to replace the handy one-column integer primary key and its handy one-column integer foreign key reference with something I just recently got rid of: nine cumbersome columns full of nulls, popping up in a variety of tables where they would not be particularly welcome.

Then I remembered that it's possible, in SQL, to put a UNIQUE constraint on multiple columns. That's the new trick I need to learn. I'm keeping the primary key as is.

But I will be moving the place tables back to the tree database. There are three place tables: `place`, `nested_place`, and `place_name`. Not to mention the tables that will interact with these tables which are `assertion` and `finding` so far. Not to mention that in order to move places to the common database, two more tables had to be created for notes related to places. Two tables that would be totally unnecessary if places were left in the tree database. With all that going on, trying to do it without having foreign key constraints would be asking for trouble.

A tool for importing/exporting places from one tree to another will have to be made. That one's going on the do list.

[Uncle Buddy]

That's pretty much all done except for some code-tweaking that might become necessary to account for having just moved three tables from treebard.db to sample_tree.tbd.

But an immediate problem rears its ugly head. The unique constraint on the nine foreign key columns in the nested_place table doesn't work as-is. It does work. For example, if you insert real place_ids to a row for all nine columns, and the combination of place_ids is unique, it goes in, no problem. Then if you try to do the same insert again, SQLite won't allow it because that combination of place_ids already exists in the database. That's what I wanted.

The problem is the nulls. Every null is unique. Seldom or never will a place nesting have nine nests in it. For each column without a nest, there is a null by default. So the two rows of place_ids here, for example, are actually unique (because each null is unique), and both will go right in:

8, 24, 19, 966, 42, 288, 282, 12, null

8, 24, 19, 966, 42, 288, 282, 12, null

The only choice for populating these columns is a real place_id or a null, because these are constrained foreign key columns. So I can't use an empty string, it has to be an integer referencing a place_id primary key in the place table, or a null.

So there are two choices.

1) There is a default place called "unknown" with place_id 1. The "unknown" is never displayed. I never thought I'd end up replacing all the nulls with it, but I could.

2) I could create another non-place place with some ID such as 9999 and call it something like "null" or whatever, and replace all the nulls in the nested_place table with 9999.

I will try choice number 1 so I don't have to create another non-place place. Some code will have to be adjusted, but what else is new.

[Uncle Buddy]

With many improvements and simplifications done to the places code in general, the conditions part of the code which determines what to update and when to update it is still hard to finish. It's at the point now where fixing one thing breaks another, but it's not a lot of code, only about 300 lines, so finishing should be a simple matter of properly defining what all the conditions are. In order to make it possible to fix the responses to each condition at the same time, I would like to isolate each condition by having it run its own method.

The testing takes place on focus in and focus out to any input field in the place column on the GUI's conclusions table. In the code, each row in this table is called a "finding" while cells in each row are called "conclusions".

CONDITIONS

on FocusIn	on FocusOut	reaction
LEVEL ONE
input is empty	input is filled out	go to level three
input is empty	input is empty	do nothing
input is filled out	input is empty	unlink place from finding
input is filled out	input is filled out	go to level two
LEVEL TWO
content = A	content = A	do nothing
content = A	content = B	go to level three
	LEVEL THREE
	nesting exists	autofilled content goes direct to db, just update finding row with nested_place_id
	nests all new, no dupes	insert place/place_name row for each nest, insert one nested_place row, update finding row
	exists/new, no dupes	insert place/place_name row for new nests, insert one nested_place row, update finding row
	there are dupe(s)	get all IDs from dupe dialog(s) opening