I've mentioned this bit of code recently:
INSERT INTO accession_analysis ( accession_id , analysis_id , reference_id , status , status_text , extra_parameters ) SELECT accession_id , ? , ? , ? , ? , ?, ? FROM accessions WHERE request_id = ? ORDER BY accession_id
Well, it's slightly different. Before we were just holding the current state ( waiting, working, success, failure ) and now we're holding text information, too, and you don't just want the current state, you want to be able to look back. So, in addition to
accession_analysis, we're adding
accession_analysis_status, which will have, so far, a unique id, an id for the AA it connects to, and then the the status information.
And the run now. I have to add a run number to the
accession_analysisschema. I can do that, but that's not germane right now.
What is germane is how to store the status into
accession_analysis_statusat about the same time as it goes into
accession_analysis, getting the
accession_analysistable. It would be far easier if I was using an iterative approach, but then I'm blasting the DB with many connections instead of just one.
An approach would be to find all the
accession_analysiselements without a matching
accession_analysis_status, and then inserting them into
accession_analysis_status. Something like
INSERT INTO accession_analysis_status ( aa_id , status , status_text ) SELECT id , status ,status_text FROM accession_analysis WHERE there's no aa_id corresponding to the id in accession_analysis
But clearly, I don't know how to express this as SQL yet.