- Feature Articles
- CodeSOD
- Error'd
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
This code is ACID-compliant - when you see it, your eyes are burned to the point of going blind.
Admin
Back in the day (TM) I was a developer of two different 'database' systems that did not have transactions. The one you are more likely have heard of was the Pick System. (Named after Dick Pick) If you don't have transactions you have to do interesting things to make sure that your data doesn't get corrupted. It doesn't seem like the author of this TDWTF was ever taught that transactions existed. Or exceptions. Or log files.
Admin
Maybe the said author was working with MySQL before it added transactions...
The article is clear that the whole situation broke because stuff suddenly started throwing exceptions for what were, in fact, given the overall "design" of the system, actually real errors. Of course, the system shouldn't have been designed like that, but with that "feature" in its design, they are real errors. It's much too long since I did any work with SQL of any flavour that I'm willing to propose an alternative, unfortunately. (Or fortunately, perhaps...)
Admin
I dunno what to write about this article because the implementation seems so crazy, I can't even imagine what they try to solve.
Admin
Devs: 100% sure nobody will use this s**t, just limit the import to 10k. If this hits 10k I will eat my hat!
Admin
10,000 bottles of beer in the database, clearly too drunk to succeed.
Admin
Although SELECT COUNT is better than SELECT *, it still forces the DB engine to count the rows. I normally use SELECT DISTINCT 'X' because at least that tells the DB you don't care how many rows and you only want to know whether there are any.
obviously if the normal case is no rows then that probably makes no difference
Admin
On a sufficiently dumb DBMS this will scan the table anyway. Why not simply LIMIT 1 (FIRST 1 depending on dialect)?
Admin
Best is the simple
SELECT MAX(id) FROM data_import
Admin
There is a few DB out there (MsSQL & PostgresSQL AFAIR) where the DB actually stores the row count, so SELECT COUNT(*) is basically a noop. Still have the round trip over the network, so SELECT LIMIT is the way to go for that one in an transaction scope with the DELETE.
Admin
"If this hits 10k I will eat my hat!"
By the time this hits 10k, I will be long gone from here and it will be someone else's problem!
Admin
Take one down, pass it to ROUND(), give it permission to read
Admin
It sounds as if that smart boss has been around since there were only 9 items in the database.
Admin
Well, transactions or no transactions, row counts or not, and for anyone that didn't figure it out from the original article, the right way to implement this thing is, of course, to add an extra column with the import status...
Or hey, do as they do in the system I work with... keep a completely separate import-database where you have table's (with the same name as the original) you can insert in and then have triggers on those tables do the importing on the fly........ (NO! I'm kidding... don't do that!)
Admin
A simple COUNT() will not help you if you initially had less than 5000 rows, incremented the IDs and then the process broke before more 5000 rows were inserted. The COUNT() will still be less than 10000, but your crude error check should throw. And a COUNT(*) WHERE id > 10000 will not benefit from this optimization.
Admin
I think this code evolved.
Originally it simply replaced the data with the new data. Something went wrong one day and they wrote this to provide a rollback without altering the table. Yeah, that's what transactions are for, but we see plenty of things where someone obviously wasn't aware of some feature and did what they could.
Admin
"(Named after Dick Pick)"
Yes, really. You can't make this stuff up, folks.
Admin
Edit Admin
I've worked with systems in decades past that needed this kind of thinking. And with devs who did this kind of thinking. I've been the sort of dev who did something like this ... and hated that I had to. Or didn't know any better way.