> Have you considered a validation app? Have it read the inputs and look in db for conflicts, rather than attempt the
insert.Zero transactions necessary
I did consider that about a year or two ago when I first conceived the data validation interface. Doing that now would
solvethe problem of concurrent validations blocking one another, but selects would still get blocked if their result is
pendingthe outcome of a load running in the background. If I mitigate that issue by running those loads over night on
aschedule, I still lose out on the benefits of having the loading code do the validation for me...
I would have to explicitly find and report on problems that the load exceptions currently do for me, without any extra
code.
So what I'm saying is that the data being validated is inter-dependent. There are about a dozen different loading
scripts(one for each sheet of the uploaded excel file) whose runs are orchestrated by a master load script that ensures
theyare run in the right order so that the interdependent data can be checked. For example, these are some relative
ordersof what needs to be loaded so that data can be validated:
Study > Animals
Tracers > Infusates > Animals
Treatments > Animals
Tissues > Samples
The Animal load script would fail if the new data in (the tables) Study, Tracers, Infusates, and Treatments aren't
inserted,because it links to those newly created records. And there's no way to detect problems in those new
relationshipsin the unchanged database if they aren't inserted. That's what doing this all in a transaction, and
actuallydoing the inserts (for which I use Django `get_or_create` method calls) provides.
In other words, I would have to save and explicitly check the inter-related sheet data in data structures independent
ofthe database in order to find the equivalent of (for example) `ObjectDoesNotExist` errors that originate from the
database. Right now, I get those errors caught "for free". All I have to do is tell the user what sheet/row/column is
relatedto that error. And it saves me the overhead of having to maintain synchronicity between separate validation
codeand loading code when the loading code changes.
Robert William Leach
Research Software Engineer
133 Carl C. Icahn Lab
Lewis-Sigler Institute for Integrative Genomics
Princeton University
Princeton, NJ 08544
