Long Discursion on Managing Customization Configuration for a Friday Afternoon in December

As someone who has built (and now has to maintain) numerous customizations, I find that I regularly need to set up a customization to allow configuration linking it to an arbitrary set of objects in Tessitura.  For example: every time an ADA seat is sold online, I want to automatically create a CSI so that the ticket office staff know that they need to reach out to the customer for additional information.  To decide when to create the CSI, I want the procedure to consult a list of modes of sale to know whether the sale was online vs. by a cashier.

Now, the way I currently do this (usually) is that I create a local entry to T_DEFAULTS, then enter the list of MOS ids as a comma separated string.  Then I pull it into my procedure using something like this:

select @mos = dbo.FS_GET_DEFAULT_VALUE(null, ‘CPSMA’, ‘ADA CSI Modes of Sale’);

select m.*

from TR_MOS as m

inner join (select value from dbo.LFT_CPSMA_SPLIT_BIGINT_LIST(@mos, ‘,’))

I probably have this construction in my numerous custom procedures somewhere between four and six million times.  I think it’s pretty standard...I think a number of internal Tessitura processes do this as well.  Once you’ve been doing this for a decade or so, a couple of significant concerns crop up.

1. If a new MOS is created for some reason, how do I know how many configuration entries might need updating?  This is hard enough for our organization, but now with multiple organizations in a consortium it’s going to get a lot harder.
2. If the structure or usage of the tessitura object changes, how do I find the utilities that rely on it quickly?

Currently my answers to these are:

1. Try to remember.  I suppose I could search “MOS” and “Mode of Sale” and “Modes of Sale” in T_DEFAULTS and hope that I have a good naming convention, and then puzzle over each entry.  But that also relies on me being notified about each change and then allotting an afternoon to research.
2. I can use SQL Server dependencies, or more likely my own homebrew procedure for finding dependencies and then review each of them.
3. Increasingly, “wait until someone reports that something is broken, then use by extensive institutional knowledge to realize what might be going wrong and then go in and fix it.”

So I’m mulling over a system that might be more transparent.  What I’m thinking about starts with two local tables.  The first identifies an organization (TR_ORGANIZATION: “But what if it’s a consortium?”), a customization (“ADA Seat CSI Generation”), a component within the customization (“Scanned Modes of Sale”), the table name (“TR_MOS”) and the id column name (“id”).  The second matches each customization/component entry with the actual ids, and whether or not that id is actively configured.

The idea is that when a new component is defined, the second table will be populated with the ids of all current Tessitura objects of that type, default inactive.  This will presumably be handled by a utility (or a trigger? more on the complications later).  Then another utility would be used to update the list to activate the ids initially desired configured for the customization.  Just having these as System Tables, specifically the second table, would be pretty ugly and hard to manage, so I think this system would want to rely on utilities and reports for setting, updating, and of course managing data integrity.

Now, instead of splitting a string, which has performance issues and also has the issue of only allowing an arbitrary number of ids in it (limited by the string length of each id used, so impossible to compute ahead of time), and the two step process of finding the configuration string and then splitting it, we can just run:

inner join dbo.LFT_GET_CUSTOMIZATION_CONFIG(@organization, “ADA Seat CSI Generation”, “Scanned Modes of Sale”) as smos on smos.id = m.id

or even a direct join on the table.

Now, if TR_MOS were to change in some major way (T_MEMB_LEVEL is going to soon…) I can at least get a quick list of configurations relying on it from the first table, although I’ll still need to find dependencies for cases where it is used but configuration is not required.  But if someone adds a new MOS, I can know immediately and know which configurations need to be checked, because it will be missing from the second table.

So, two local tables, a utility to create new configuration types (this could probably be managed as a System Table also), a utility to prefill the id table for new configuration types or remove them if the configuration is deleted  (if not just handled by a trigger on the configuration types table), a utility to activate, deactivate and  add ids to the id table, and a set of reports or report options to show current configurations and find missing Tessitura objects in the configuration sets.

One issue of complexity is that some aspects (matching up with the specified tables for editing and reporting) will naturally require executing constructed query strings, which on its face prohibits using views or functions.  I don’t know enough about CLRS to know if they could assist here.  One example of a place where being able to do this would be nice is to allow you to populate a selection list for values to activate with descriptions from the table in question (this would also require a “description column” value in the configuration types table.

Has anyone else worried about this issue?  It might just be a symptom of our excessive inclination to customization over a decade and a half on Tessitura, which has created piles of technical debt absorbing an ever increasing portion of my life.  Any obvious problems with this idea?  Does this give anyone any other ideas?