Sandpiper Schemas WIP 2021-06-15

Last week I made several critical changes to the database schema based on work done building a template plan document generator in Python. This fixes bugs in some foreign key fields and adds linking directly to the plan_slices table. Full changelog and download below!

The gritty details:

  • Added unique and multi link tables pointing directly to plan_slices
    • This is the way to apply plan-specific slice links, in particular for the primary and secondary reference codes. In use, a primary and a secondary may establish codes that are meant to share only between the two of them – for example, an Epicor line code. These should be treated as full overrides of any links living on the slice linking tables that have the same code
    • A solution, when outputting a plan, should first get plan_slice links. Then it should only add links that have a key_field NOT contained in the plan_slices linking tables. For example, establishing key field “secondary-reference” on plan_slice_unique_links would mean that any entries on slice_unique_links with this code would not be exported or used in that plan
    • In the same vein, if a plan_slice has a multi link to key field “autocare-pcdb-parttype” with only one part terminology ID in the plan_slice_multi_link_entries table, and a slice has a multilink to the same key field yet has three part terminology IDs in the slice_multi_link_entries table, the final output would be only one part terminology ID output. The plan-specific multi-link fully replaces the general slice multi-link. There’s no attempt (and should be no attempt) to merge them or union them.
  • Removed multi_link_uuid from the *_multi_links tables. There is no UUID on the MultiLink element in the plan document – because it is a container for the “real” data, the MultiLinkEntry elements. I’ve moved the primary key on all *_multi_links tables to be an autoincrementing integer, and did the same for the referring child tables *_multi_link_entries
  • Added two description fields to plans: local_description (a field for the local sandpiper instance to add private descriptions of plans for use in internal work), and plan_description (which is present in the plan document and shared by both partners as the “true” description of the plan)
  • Added capability_description to capabilities, as defined in the plan but missing from earlier builds
  • Added an “Obsolete” plan status to use for plans that are no longer active but were not rejected; this is for plans that were at one time in force but are no longer desirable
  • Added a plan status_message for use during plan proposals, so that the software can communicate a reason for a status change
  • Fixed bad foreign key field references for many link tables. SQLite3 won’t throw an error if you create an invalid foreign key, apparently (lesson learned!) so these didn’t show up during normal development, only when trying to actually populate and use the data