Just quick update on this. Last night I did get some coding time in after blog-posting. The fix this time was a problem with Create Registrant that was throwing SQL errors all over the place. Turns out I never actually completed writing the DAO for the Registrant data object, and the create() method was broken in a ton of interesting ways.
There’s still a twitch in restoring the state to the editor after creation – it’s not reloading the new registrant into the sessionspace after creation, so you end up with a sort of ‘blank’ registrant screen. Exiting and coming back shows the new entry, so it is creating it, just isn’t reloading.
I’m sort of worried about keeping history right now. The old code did everything in the history table programmatically – so whenever I made a change to a registrant’s information, I had to make a call to logEntry() to create a new history record. So, naturally, there was some inconsistencies in the logging. It also didn’t help that the history table was critical information. It was part informational, part auditing. In fact, some parts of CONGO used the history table to recalculate statistics and status on registrants. (Note – that is SO not going to be in the rewrite).
Anyway – regarding logging, I’m looking into learning MySQL triggers to have registrant history updated automatically whenever a change happens. That way I don’t have to always remember to put in a logging call. We’ll see if this is viable (for instance, how does the trigger know what the ID of the operator is? The one who is actually making the change?)
Stay tuned. 🙂
About Dave Shevett
A wandering geek. Toys, shiny things, pursuits and distractions.
View all posts by Dave Shevett
how does the trigger know what the ID of the operator is?
Assuming you are planning for a multi-threaded process, and you can’t store the current login in a dynamic local table somewhere, I would say, off the top of my head, that the best option would be to add a “last_modified_by” column or similar to your other tables, which can then easily pass that value to the trigger with each call. That has the additional value of letting you see who last touched the record without making a history call.
Well, storing current login information in a local table may very well be possible – I have that capability. The trick is how does the trigger know who ran the process? the trigger doesn’t have access to the application namespace (where $CurrentUser or whatever is). I guess what I need to learn is if… yeah, last modified by – I could see that, but that seems.. hmm. Let me think on that.)
I’d suggest staying away from DB-side triggers due to future portability issues, and go instead with an abstracted datastore layer, and put your audit trail or logging at that point… at the very least, if you decide to go the trigger route, the ‘setup’ method of the store abstraction could then handle adding them if the concrete is MySQL, or do something else for another implementation.
Right, you can easily store current login information, but assuming you are planning for multi-threading, you’re going to have up to 8 different $CurrentUsers running processes on different threads, under the same SQL login. Essentially, you need to tell what random number is associated with any given INSERT statement. I think passing it with the statement is the least convoluted of options.