Why do by hand when you can use SQL? (Temporary Tables)
-
When I saw this Side Bar Thread I knew I had to reply in a new post.
In an old company I worked for, I found a nice pice of code... repeated on every part of the application.
"The Coder", who obviously didn't knew about Sessions in Web Applications, faced the problem of a "conversational" dialog in the app. "Input 1" took parameters, retrieved some data from db, directed the user to "Listin' 1", where the user would change some values or perform some actions and finally a "Process 1" page would finish the job.
There are multiple ways to do something like that, for example a framework for working with dialogs, store some data in session and clean it at the end, use id's to reference the items / things and pass'em as parameters to the next part where the actions will be done, etc. But "The Coder" was much more "cleverest" than that, the Temporary Table was the solution.
Form 1 --> Retrieve everything and store in a "Temporary Table". "Listin' 1" --> alter Temporary Table's data. "Process 1" load temporary table data and merge in the db. This is not "too" bad, in some circumstances would be impossed by architecture or prerrequistes, but, believe me, this was not the case:
- Read Contract Header. Create Temporary Table for contract lines. Input Lines. Save Contract. Lines are stored in the Temporary Table (instead of a Map, Collection, lines table, etc).
But wait, there's more... how do you create a temporary table? "Create temporary table..." maybe? Nah, that'd be too easy... lets do it enterprisey:
String sql = "Create Table "+user.getUserName()+"_contracts_temp"+ ... columns ...
Obviously, working in multiple browser windows didn't work, too often (the table was not deleted if the user didn't use "Save Contract" button) strange things happened mixing lines from old contracts into new ones, and much more funny things.
The bad thing, those tables were present in 75% of the application, which was running a 8.000M€ (european millions, that's like 8.000.000.000€) company and providing near 100M revenues to the developing company. Five years after, having not
repairedredone the application, SAP came and the contract was lost.
-
@cbr600f said:
8.000M€ (european millions, that's like 8.000.000.000€)
There is no such thing as a 'European million'. There is a BRITISH billion (and trillion, quadrillion) etc, which is a million million, as opposed to the American billion, which is a thousand million. There is a milliard, which is a thousand million. But a million is the same always, a thousand thousand.
It does, however, appears as though you are using the 'continental' format, of dots to separate groups of 1000, and commas for the decimal point, rather than the 'English' format which is the reverse. So you either mean 8 million euros, or 8 thousand million euros.
-
@m0ffx said:
@cbr600f said:
8.000M€ (european millions, that's like 8.000.000.000€)
There is no such thing as a 'European million'. There is a BRITISH billion (and trillion, quadrillion) etc, which is a million million, as opposed to the American billion, which is a thousand million. There is a milliard, which is a thousand million. But a million is the same always, a thousand thousand.
It does, however, appears as though you are using the 'continental' format, of dots to separate groups of 1000, and commas for the decimal point, rather than the 'English' format which is the reverse. So you either mean 8 million euros, or 8 thousand million euros.
As he's from Spain, I think he means 8,000 M€ which would be 8,000,000,000.00 €.
-
@m0ffx said:
@cbr600f said:
8.000M€ (european millions, that's like 8.000.000.000€)
There is no such thing as a 'European million'. There is a BRITISH billion (and trillion, quadrillion) etc, which is a million million, as opposed to the American billion, which is a thousand million. There is a milliard, which is a thousand million. But a million is the same always, a thousand thousand.
It does, however, appears as though you are using the 'continental' format, of dots to separate groups of 1000, and commas for the decimal point, rather than the 'English' format which is the reverse. So you either mean 8 million euros, or 8 thousand million euros.
The real WTF is thinking that this 30 year out-of-date info is still valid... http://en.wikipedia.org/wiki/Long_and_short_scales
Robin
-
@m0ffx said:
@cbr600f said:
8.000M€ (european millions, that's like 8.000.000.000€)
There is no such thing as a 'European million'. There is a BRITISH billion (and trillion, quadrillion) etc, which is a million million, as opposed to the American billion, which is a thousand million. There is a milliard, which is a thousand million. But a million is the same always, a thousand thousand.
It does, however, appears as though you are using the 'continental' format, of dots to separate groups of 1000, and commas for the decimal point, rather than the 'English' format which is the reverse. So you either mean 8 million euros, or 8 thousand million euros.
British here, a billion is a thousand million. Thanks for playing.
-
@Kemp said:
British here, a billion is a thousand million. Thanks for playing.
In most of continental Europe, a billion is a million millions, and a trillion is a million billions. In the USA a billion is a thousand millions and a trillion is a thousand billions. The UK normally uses the USA scheme nowadays but it didn't do until relatively recently, and even today it can be ambiguous. So, beware... Next time someone tries to sell you something for a billion pounds, check first!
-
@cbr600f said:
But wait, there's more... how do you create a temporary table? "Create temporary table..." maybe? Nah, that'd be too easy... lets do it enterprisey:
Not only would it be 'too easy', but it wouldn't work either...
Think about it - when are temporary tables cleaned up? When the database session finishes that's when. So, either the web server closes down DB sessions once it's finished serving the page - oops, or it keeps the sessions open indefinitely, in which case you can't have more than one person using the system at once.
So, given that the developer didn't know about keeping session ids in cookies, URLs or form data, there's not really much else he could do.
OK, so he should have know about session IDs or at least wondered how other people did it enough to find out about them.
Even not using cookies or session IDs provided by your favourite web programming language, you could simply put the session ID in a hidden field in the form. You could have the (not-really-)temporary table name based on your session ID to allow multiple sessions from the same user, and have a table keeping track of when the temporary tables were created so you can clear them down when they're over a certain age. It should have taken someone a couple of hours to retrofit this into his framework, and all would have been hunky-dory.
-
@pscs said:
@Kemp said:
British here, a billion is a thousand million. Thanks for playing.
The UK normally uses the USA scheme nowadays but it didn't do until relatively recently, and even today it can be ambiguous.25 - 30 years ago is relatively recently? I mean, sure, in the grand scheme of things that long is nothing to the universe, but I'd say it's long enough for people to catch on that we don't use the long scale anymore...
-
@Kemp said:
25 - 30 years ago is relatively recently? I mean, sure, in the grand scheme of things that long is nothing to the universe, but I'd say it's long enough for people to catch on that we don't use the long scale anymore...
It's not that long ago. OK, in 1974 the UK government said that they'd use the US system for government statistics. But no one else followed suit immediately...
When I was at school in the 80s a 'British billion' was still a million million, but we were taught kilograms, litres and metres. Nowadays, you'll find lots of people ignoring what the goverment says we should do and still buying a pound of apples, measuring themselves in feet & inches and buying pints of beer. Similarly, some people still use the European 'billion' in Britain.
In places where it matters (eg financial markets) they don't use the term 'billion' because of possible ambiguity. Eg look on the (US) website http://investors.divx.com/glossary.cfm?GlossaryKeyword=slang - look at 'Yard' - why do you think 'Yard' is used as a term for (US) 'billion' - 'Yard' comes from 'Milliard' which is the non-ambious word for "thousand million".
-
@pscs said:
@Kemp said:
25 - 30 years ago is relatively recently? I mean, sure, in the grand scheme of things that long is nothing to the universe, but I'd say it's long enough for people to catch on that we don't use the long scale anymore...
It's not that long ago. OK, in 1974 the UK government said that they'd use the US system for government statistics. But no one else followed suit immediately...
When I was at school in the 80s a 'British billion' was still a million million, but we were taught kilograms, litres and metres. Nowadays, you'll find lots of people ignoring what the goverment says we should do and still buying a pound of apples, measuring themselves in feet & inches and buying pints of beer. Similarly, some people still use the European 'billion' in Britain.Hmmm... point taken. A billion has just always seemed natural to me as a thousand million. Maybe it's because I use computers far more than I really should so the metric system of prefixes has become an "obvious" thing to me (obvious used in the sense of me thinking that's how it should be, not obvious as in the wider sense).
I don't recall ever having seen anything that quoted a billion as a million million, though I do remember back in high school having a discussion with my maths teacher about which way was the One True Way.
-
@pscs said:
You could have the (not-really-)temporary table name based on your session ID to allow multiple sessions from the same user, and have a table keeping track of when the temporary tables were created so you can clear them down when they're over a certain age.
Ugh, no. Don't make the session ID part of the table name, make it a column of a single permanent table (with an index so you can delete all relevant rows quickly when they're over a certain age).
-
@emurphy said:
@pscs said:
You could have the (not-really-)temporary table name based on your session ID to allow multiple sessions from the same user, and have a table keeping track of when the temporary tables were created so you can clear them down when they're over a certain age.
Ugh, no. Don't make the session ID part of the table name, make it a column of a single permanent table (with an index so you can delete all relevant rows quickly when they're over a certain age).
OK, that would be better, but I was trying to think of a way which could have been 'retrofitted' to the original broken code with minimal impact. Just changing the name of the tables would seem a smaller impact change than fixing the whole system - sort of 'done badly but works' rather than the actual 'done badly and doesn't work either' ;)
-
@Kemp said:
I'm British, and I still have to double-take when someone says 'billion' and I have to figure out whether they mean 1e9 or 1e12. Contrary to what Kemp, above, says, 1e12 is still called billion in some places here (and is what I'm accustomed to, hence the double-take.)@pscs said:
@Kemp said:
British here, a billion is a thousand million. Thanks for playing.
The UK normally uses the USA scheme nowadays but it didn't do until relatively recently, and even today it can be ambiguous.25 - 30 years ago is relatively recently? I mean, sure, in the grand scheme of things that long is nothing to the universe, but I'd say it's long enough for people to catch on that we don't use the long scale anymore...
-
Aaaah so fun watching geeks catfight over dumb shit.
-
@Lysis said:
Aaaah so fun watching geeks catfight over dumb shit.
Well, how would you avoid the confusion? Adopting either approach isn't going to help (it never does in a holy war) and the prefix system won't help us, either, as "kilo" is commonly overloaded to mean "kilogram".
-
The real WTF is the 100M revenue for over 5 years for something like this. And even bigger WTF is that having so much money someone hired interns or God knows what experience the developers there had to develop this crap.
By the way: temporary tables wouldn't solve this problem because the data there would be automatically deleted once the transaction is over and since we're talking about web pages the connection would be lost once the page is rendered, so to maintain a transaction across different pages or between postbacks on the same page you have to use a windows service, message queues etc. - real enterprise s**t, not kid's toys!
-
For all you elite hax0rs who think this wouldn't work with a temporary table, read the damn code: "Create Table "+user.getUserName()+"_contracts_temp"+ ... columns ... would not create a temp table in the true sense of the word in most RDBMS implementations. It would create a static table with the word "temp"in it. WTF?
-
@mr_cartesian said:
For all you elite hax0rs who think this wouldn't work with a temporary table, read the damn code: "Create Table "+user.getUserName()+"_contracts_temp"+ ... columns ... would not create a temp table in the true sense of the word in most RDBMS implementations. It would create a static table with the word "temp"in it. WTF?
Isn't that what I said? Temporary tables will not work for situation like this, because all data will vanish once the transaction is over. And yes this is not a real temporary table, just a regular table created for a while then deleted. I think the original poster realizes that and that's why his comments about this approach being enterprisey are...
