On Fri, 17 Jun 2011, Erik Huelsmann wrote: > We've been talking on GoogleTalk about the way oldcode is using the pattern: > > INSERT <garbage> > <do stuff> > UPDATE <true values> > > I'm now looking at LedgerSMB/OE.pm::generate_orders() which is using > this pattern between lines 2354 (the INSERT) and 2440 (the UPDATE). > > This specific case uses the INSERT to acquire a unique ID from the > index, continues to insert records in another table with that specific > ID and then inserts the true data in the main table. You seemed to say > that it's very hard to rewrite code not to use that pattern. While I > admit that it's going to be a lot of work to do so for the entire code > base, I'm not seeing how the problem of doing so is going to be > 'hard', especially if we're going to target 8.4 and beyond. [Which all > have the INSERT INTO ... RETURNING ... functionality which would allow > the INSERT to tell us the ID it generated for the record it's > inserted.] > > Specifically this case, I'm thinking, should be quite easy to add a > wrapping transaction -- which is a good idea even if only to make sure > all data gets added to both tables, or nothing. The idea would be to > insert all "meaningful" data in an insert, then insert the orderlines > in the lines table followed by an update to the table adding the > calculated amounts.
I'm throwing this out there unconsidered. I accept that it may be an utterly stupid idea. Thus disclaimed, I note that one way to use a globally unique ID, would be to keep a sequence table. Basically you'd have two columns: a sequence and a type. Your first action is to insert a new type record, which returns the ID thus generated, which may be then used as a foreign key in any number of other places. The advantage, is that you're not generating IDs in a bunch of different places. (However, I'm not actually sure that you're doing that now, so maybe that's irrelevant.) Add a control-number field, and an order, invoice, GL, etc. number can be retroactively added to the sequence table, to identify what document of type type, the user might expect to find associated with that ID. (or not) Anyway, it was a random thought I had while reading this, which may be negated by the workings of modern PostGreSQL (I've been more of a developer of applications which use MySQL). I won't be offended by having this ignored for being non-applicable.:) Luke ------------------------------------------------------------------------------ EditLive Enterprise is the world's most technically advanced content authoring tool. Experience the power of Track Changes, Inline Image Editing and ensure content is compliant with Accessibility Checking. http://p.sf.net/sfu/ephox-dev2dev _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
