The Daily WTF: Curious Perversions in Information Technology

2009-09-15 Reply Admin

ClaudeSuck.de:
xtremezone:
Though there are these things called stored procedures that sort of separate the data access from the application code already. Albeit, they're generally a little bit more slow and difficult to maintain, which can be a double-edged sword.

Stored Procedures are slower than ad-hoc queries extracted from an XML-file???????? Where does this idiot come from?

Well it's true. Stored procs are written by DBAs... which makes 'em slower and less efficient than anything a real developer could possible write :)

2009-09-15 Reply Admin

Franz Kafka:
TGV:
Hey guys, come on. Why defend storing queries in XML, which is stored in a file somewhere? Most database support some form of stored procedure, where, you know, you simply call the procedure called "delete_authorization" and you can keep everything in one neat little package? Plus you get better control over access rights with stored procedures than with XML (I can only begin to imagine the horrors of XML-based access lists linked to db user accounts).

Well, customizing reports wouldn't work too well; if I were doing the sql in config thing, I'd use a separate user for that and limit its access to select only. I still prefer hardcoded sql when possible with column filters for customization.

This reminds of the times where you could f.e. set a filter in File Manager (today Windows Explorer) and the user didn't know why he didn't see files anymore.

I can imagine editing the config file and replacing the SQL by something like DROP DATABASE or TRUNCATE TABLE Customer... After all, the idea is to make editing SQL easy, no?

CAPTCHA: tation (I wonder where the temp went)

2009-09-15 Reply Admin

TGV:
Code Dependent:
Hence the value of using source control.
That works well for bundles and monolithic apps, but for files that reside in different places, I wouldn't stake a lot on it. Nor on the app configuration in this case (the path to the XML file, that must be called, ah, here it is: _T(fileNotFound)).
SQL in XML gives you a lot of flexibility, but flexibility is powerful and therefore dangerous, as the OP already showed.

And how are you going to run procedures that require a bit more processing, e.g. with variables or loops or commits or whatever if your db cannot even handle stored procs?

It's all invoked by the client

Bastard Programmer From Hell

Code Dependent · 2009-09-15 Reply Admin

coyo:
It is horrible for parsing. You have the DOM choice, which gobbles up RAM. You have the SAX choice, which because of the scalar/list/hash-ness of the xml node, is very complex. You can't say a node is done being parsed until its closing tag is encountered. If you are doing depth first, that can be a long long long way off. The only decent parsing model is the pull model, and you will miss any data you are not expecting in that one.

Obviously, you haven't discovered LINQ yet. You really should check out Linq to Xml (assuming you're working on a .Net platform). It's wonderful stuff.

2009-09-15 Reply Admin

[quote user="ChrisSamsDad"][quote user="Franz Kafka"]

I have a rule of thumb on system design: if you're doing some weird non-standard shit that goes against what 99.99% of the rest of the world are doing, you're either a visionary genius or there had really really better be a good reason.

[/quote]

Given that 99.99% of the rest of the world seems to be writing ad-hoc queries stored in some weird place like XML would you really need a good reason to write some weird non-standard shit?

2009-09-15 Reply Admin

Well if the comment and the code disagree, they're probably both wrong. Hmm. All that's left is the name. Therefore if anything is correct, that must be. The comment and the code should both be changed to DELETE!

2009-09-15 Reply Admin

SB:
Jay Jay:
Since when does popular = good idea?

Since Democracy.

Please google "million flies" and "shit".

kthx

2009-09-15 Reply Admin

Our Dev's are for the most part pretty bad at writing sql and data modeling. Since neither they nor their managers feel the need to consult the DBA's having them put SQL into spring beans or using ibatis was a great way to go back and tune the queries and data model for performance.

I am not sure how storing SQL outside of your code is a WTF i'd consider hardcoding the queries inline a bigger WTF.

coyo · 2009-09-15 Reply Admin

dkf:
coyo:
JSON is just as flexible - there is no data that XML can describe that JSON can not.
Not quite. JSON gets messy when you start to want to mix different authorities for name-sets (XML namespaces are ugly and very irritating, but ''do'' work) and JSON has a very restricted set of basic scalar values, whereas XML is a bit better (assuming you're using the schema datatypes spec, which is very common).

To cover the case of different authorities for name-sets is probably why XML is so complicated.

My instinct is to question the real need for this particular design. It sounds like a problem that is tailored to a solution rather than vice versa.

I mean, could this not be covered by { namespace1 => 'data for namespace 1', namespace2 => 'data for namespace 2' ... } ?

I mean, to be fair, if you wanted to, you could even encapsulate xml in JSON with something as WTFish as { name => 'nodename', attributes => { a => 'a', b => 'b' }, namespace => 'foo', nodevalue => "somevalue", childNodesHash => { a => { ... }, b => { ... } }, childNodeList => [ {...}, {...}, {...} ] } (but hey, is probably slimmer)

dkf:
In many ways, the main competitor for XML is ASN.1 because that also handles the multiple-naming-authorities problem. You probably don't want to write new things that use ASN.1 as a serialization format though as it is rather difficult to read without special support software. (CSV only works for simple tabular data, and implementations of it have some significant issues; it's best reserved as an if-all-else-fails interoperability format because of these things.)

Maybe there are cases where XML is the correct choice, but I still hold that that those are the underwhelming minority. It's sort of the anti-perl in a way. The simple cases are much more complicated than they should be, and representing interconnecting branching structures with is teeth gnashing painful.

coyo · 2009-09-15 Reply Admin

Code Dependent:
coyo:
It is horrible for parsing. You have the DOM choice, which gobbles up RAM. You have the SAX choice, which because of the scalar/list/hash-ness of the xml node, is very complex. You can't say a node is done being parsed until its closing tag is encountered. If you are doing depth first, that can be a long long long way off. The only decent parsing model is the pull model, and you will miss any data you are not expecting in that one.
Obviously, you haven't discovered LINQ yet. You really should check out Linq to Xml (assuming you're working on a .Net platform). It's wonderful stuff.

It looks like a pull model, which is my favored way to deal with XML. To be honest though, it looks like little more than perfume for a turd.

2009-09-15 Reply Admin

TGV:
wow:
trwtf is that the article was posted without a long meaningless backstory? cheers!
On a dark, Tuesday morning, Jan Fabry entered his cubicle at Notech Inc. After all the problems of the morning, he hoped for a calm day. Then, at 10:05, an innocent ticket arrived in his mailbox. Quickly, CHARLES dived into the source code control system...
FTFY.

FTFY

2009-09-15 Reply Admin

ClaudeSuck.de:
CAPTCHA: tation (I wonder where the temp went)

+1,048,576 internets for being the first ever hilariously funny "captcha" comment. (Probably also the last ever...)

Now you've got me humming the Tom Waits song. Er ... what were we supposed to be talking about?

2009-09-15 Reply Admin

coyo:
dkf:
In many ways, the main competitor for XML is ASN.1 because that also handles the multiple-naming-authorities problem. You probably don't want to write new things that use ASN.1 as a serialization format though as it is rather difficult to read without special support software. (CSV only works for simple tabular data, and implementations of it have some significant issues; it's best reserved as an if-all-else-fails interoperability format because of these things.)

Maybe there are cases where XML is the correct choice, but I still hold that that those are the underwhelming minority. It's sort of the anti-perl in a way. The simple cases are much more complicated than they should be, and representing interconnecting branching structures with is teeth gnashing painful.

That's almost a good enough summary of XML to persuade management not to use it ... sadly, not quite good enough.

I'm always puzzled by comments that compare ASN.1 unfavourably to XML (though to be fair, dkf is, as usual, correct in identifying a problem with the toolset). The architectural reasons to choose XML are as follows:

(a) The object to be serialized is a tree. (b) The object to be serialized can easily be mapped to a tree. (c) The object to be serialized is damn near impossible to map to a tree, but management considers XML as blat-Ag and won't listen. (d) The object to be serialized is irrelevant, because the fools at the other end only understand XML.

Beyond the underwhelming minority of cases lies the Wub. Or cases (c) and (d), if you prefer.

I think the unspoken objections to ASN.1 are usually two-fold:

(a) It's European, and we don't want no stinkin cheese-eatin surrender code-monkeys here. (b) It's for telcos, innit? We're not a telco.

Granted, it inherently requires a complicated set of tools. You know, specifications, compilers, object mapping, that sort of thing ... quite alien to computer science, really.

On the other hand, it is clearly more elegant than XML. (Actually, an arthritic flea is more elegant than XML.) This thing about "difficult to read..." Yes, another reason that managers like XML is that it is superficially a wrapper for plain text. Which, in fact, it isn't.

We need to prevent these idiots from nitwit category errors like this, and find a way to provide the proper tools instead. For whatever chosen solution -- this isn't an ASN.1 vs XML thing.

2009-09-15 Reply Admin

Mr.'; Drop Database --:
Lars Vargas:
The code snippet from the article translated into English:
Roses are red, Violets are blue. I have multiple personality disorder, and so do I.
FTFY

No no no.

Firstly, you're accepting the inane categorisations of DSM-IV, and secondly you're making the mistake of premature interpretation. Perhaps schizophrenia was intended ...

Roses are red, Violets are blue. I'm schizophrenic, and The Fridge told me so.

It's now "dissociative identity disorder," btw. You can always tell snake-oil from science when they arbitrarily change the definitions on you.

Roses are red, Violets are blue. You're DID because I am not you.

2009-09-15 Reply Admin

Bim Job:
Mr.'; Drop Database --:
Lars Vargas:
The code snippet from the article translated into English:
Roses are red, Violets are blue. I have multiple personality disorder, and so do I.
FTFY
Firstly, you're accepting the inane categorisations of DSM-IV, and secondly you're making the mistake of premature interpretation. Perhaps schizophrenia was intended
...

It's now "dissociative identity disorder," btw. You can always tell snake-oil from science when they arbitrarily change the definitions on you.

Good points, but keep in mind that science changes more often than snake oil does.

takatori · 2009-09-15 Reply Admin

xtremezone:
Though there are these things called stored procedures that sort of separate the data access from the application code already. Albeit, they're generally a little bit more slow and difficult to maintain, which can be a double-edged sword.

I once worked on a project where all queries went through stored procedures. But this project was designed (not by me!) with maintainability of the stored procedures as a primary goal.

The first parameter of each procedure was a unique ID (FNACL0023, ADUSR0012) representing the query you wished to perform, followed by a number of long text parameters. There were multiple procedures, each one with a different number of parameters--Query1(), Query2(), Query4(), Query12(), etc.

The unique ID was the primary key to the "RawQuery" table, which, for ease of maintenance, contained the actual SQL queries to be performed.

The raw query string was read from the table by ID, then string substitution was used to inject your parameter list into the queries, which looked something like:

"SELECT /* ref=FNACL0023 */ a,b,c FROM x,y,z WHERE x.d = %1% AND y.e = '%2%' ORDER BY %3%".

So, all you application had to do was:

CALL Query3('FNACL0023','1',''test'','z.c');

It made for perfectly opaque coding, but at the DBA was happy because he could easily update and add queries with Toad. Adding the query ID in a comment meant that he could easily identify problematic queries which ended up in the logs (itself not such a bad idea, and I've used it since).

The problem was that queries were jealously and closely held by the DBAs, and despite the "ease of maintenance", it was nearly impossible to convince them to make changes or add new ones. They ruthlessly audited each new release, forcing the use of stored procedures on every call. The manager was a former DBA, if you're wondering how they got away with that.

But one day, an enterprising developer found a workaround in the form of query SPTST0001--a test query that the DBAs used regularly. It took only a single parameter--a table name. We're pretty sure that the code looked like this:

"SELECT * FROM %1%"

Immediately, developers changed their coding style to:

CALL Query3('SPTST0001', '(SELECT a,b,c FROM x,y,z WHERE d=1 AND e='test' ORDER BY z.c)');

All it took was an extra set of parenthesis, and a table name magically became usable as a full SQL query. There was not a peep out of the DBA group. So long as we were using stored procedures as they required, they apparently didn't care what was actually IN them.

And besides, the developers finally stopped pestering them to add new queries.

2009-09-16 Reply Admin

SB:
Jay Jay:
Since when does popular = good idea?

Since Democracy.

Ancient Greeks have already demonstrated that democracy only generates discontent.

2009-09-16 Reply Admin

The real WTF is that they are not using an Object Oriented Database.

Relational is, like, so last century.

CAPTCHA: Why do I need to prove I'm not a robot?

Kuba · 2009-09-16 Reply Admin

Ron Piler:
Anonymous Organ Donor:
I hope to god that eventually that XML data is stored in a database... Simply because more people's heads need to explode out of frustration.

Too late. A third-party app we integrate with does exactly that. Not in any fancy sense, they literally dump reams of XML - as CLOBs - into a MySQL instance. After much pressure from us, they've added some extra columns for data we commonly query against, but it's still a dog.

I've seen it worked around thusly: Vendor A insists on only putting CLOBs into teh tablez. Vendor B adds a bunch of triggers to those tables so that a proper relational dataset (read: teh tablez you learn about in college) is synchronized in real time with Vendor A's data. Apart from adding a bit to the CPU load, it works pretty well. Vendor A only needs their (in this case) two oid-like key columns and the CLOB column and doesn't worry much about, like, ohmigod I need to change teh column name nowwhat?, and Vendor B -- who has people who didn't forget what a relational database is for -- can actually use the data in a straightforward and decently performing way.

Incidentally, Vendor A does mostly wholesale inserts/updates/deletes, without doing any joining other than on the two numeric key columns. Vendor B does all the boring college-type stuff like joins, data grouping, etc.

Win-win.

2009-09-16 Reply Admin

Mr.'; Drop Database --:
Good points, but keep in mind that science changes more often than snake oil does.

Ah, but you haven't seen my latest hydrogenated antipuffy face dispustulatory scouring creme with added vitamin X69, have you?

It'll be available in the April L'Oreal range. It also works wonders with haemarrhoids, provided you tweezer them out first.

Clinically proven for your guaranteed* satisfaction. On a cable channel near you, now!

That's science, baby!

(Subject to terms, conditions, and certain Federal regulations)

2009-09-18 Reply Admin

[quote user="Franz Kafka"][quote user="Franz Kafka"] Basically, if space and cpu are less a concern than dev time (which is usually precious), XML works ok.[/quote]

I would rather go with a csv that starts with a clear descriptive header. That is much faster in dev time. Any example I have above is faster in dev time. If the development speed is due to particular tools, again, the presentation layer display something that transcends XML, be it hover over meta-data or a pretty dom tree.

Yes, I demonize XML but that doesn't mean its the worst or that its unworkable. Just about any programmer can look at it and understand it, and for me, that may be its greatest feature.

Thanks for the spirited defence of it.[/quote]

Given the choice between csv and xml, I'd go with xml - it's standardized, so if 2 orgs can agree on a format, they can expect to read each others' stuff. csv is nonstandard and hacky (see how it deals with quotes and backslashes). Hell, even excel generates csv that it can't read. I'd much prefer a fairly verbose xml thing if the other side of the producer/consumer is in some other company.[/quote]

Have you ever worked with more than 100 rows of data?

2009-09-18 Reply Admin

Anonymouse:
Aaron:
I wonder if they also abstracted the data access to the XML file itself? Surely they wouldn't want to hard-code all the xpaths.
The xpaths are of course stored in the DB...

From which they are extracted as an XML recordset. Bôôôaaahh!

coyo · 2009-09-18 Reply Admin

ClaudeSuck.de:
Franz Kafka:
Given the choice between csv and xml, I'd go with xml - it's standardized, so if 2 orgs can agree on a format, they can expect to read each others' stuff. csv is nonstandard and hacky (see how it deals with quotes and backslashes). Hell, even excel generates csv that it can't read. I'd much prefer a fairly verbose xml thing if the other side of the producer/consumer is in some other company.

Have you ever worked with more than 100 rows of data?

Exactly!

Sometimes I wish there was a data size limit to XML. Then you could just tell management. "oh, I'd love to use XML, but there is that darn size limit. Can't use it."

2009-09-18 Reply Admin

Anon for this:
I can speak for the veracity of this WTF as I have seen the same code with slight differences at two separate fortune companies. I would say which ones but you would probably drop your health insurance and sell your mutual funds.
After some research I discovered the work was the result of one single "architect" who is still bouncing around starting project frameworks for big companies.

TopCod3r?

Jake Clarson · 2009-09-19 Reply Admin

There is definitely something to say for keeping SQL in separate files. I mean, that is WAY better then embedding them in code using concatenated one line Strings. Now THAT is a horrible way of dealing with SQL code. Makes it very hard if not downright impossible to edit if such query gets larger then a few lines.

Of course, maybe SQL queries shouldn't be longer then a few lines, but that's another story...

2009-09-20 Reply Admin

The drivel spouted here has finally forced me to comment for the first time ever. Taking some of the finer nuances of databases in no significant order ,

Stored procedures are NOT slower than in-line sql, and are essential for security and scalability. As a DBA who regularly gets to fix the priority 1 foul-ups produced by the developers, the idea that DBA's marginalise developers is a real hoot. Some of them should be made to fix their own dross, but unfortunately, they don't know how, and the DBA's get do it instead.

Having DBA's perform independent code review, verification of thorough unit testing, check for compliance with coding standards and best use of available facilities is clearly a waste of time, so hey, why does anyone bother with one?

And, oh dear me, did widdums have to wait for the nasty DBA to make a change? Well how about getting your employers to recruit enough staff?

2009-09-20 Reply Admin

Aunt Peg:
PRMan:
If you put the SQL in a config file, you can change it per customer and everyone is happy.

I see you are a man familiar with sticking things where they don't belong.

AMEN! Happiness DEFINITELY doesn't belong inside the customer!

CAPTCHA: conventio (n - esp.) - My Uncle the Nun

kevininstructor · 2009-09-21 Reply Admin

My humble comments

Developers have propensity to use methods that are sound but at the same time do not proper for a specific problem. As in the case of storing SQL in XML my thought is (along with reading comments from others here) are the mechanisms which power XML may be overkill in many situations. I like the idea of stored procedures but do not use them. I prefer storing SQL in string resources embedded in a solution. Storing SQL in this manner under Microsoft .NET allows syntax coloring for ease of reading the SQL statements along with no need for concatenation as most developers tend to do which for me is messy. XML stored in CLOBS might appear like a good method to store SQL statements but in my environment there are penalties which may be several milliseconds more than locally stored statements which for the sake of my customers rather go for speed. What about security? Our DBA team and security policies are suffice. One person mentioned Microsoft LINQ technology. I use it all the time but can not see how LINQ can assist with storage of SQL statements without a great deal of extra coding for a primary query. Best to keep code simple so when others need to maintain your code it is simple.

2009-09-27 Reply Admin

Strange replies on this thread...

There is nothing wrong with separating SQL from programming logic. This is a common practice in applications that use object/relational mapping.

Hibernate for example supports externalizing SQL queries in XML. This way the framework can validate your queries (e.g. syntax checking, verifying paths/joins are valid, flagging duplicate queries, etc.) when the application starts up and you can catch errors right away instead of waiting until runtime.

This type of validation is impossible to do if the SQL is buried in the application logic. Also, what happens if your DB schema changes? Have fun hunting for all those SQL strings scattered throughout your codebase.

I reviewed some developers' code on a past project and they did not separate SQL from application code. What a mess! That is TRWTF and I would love to see that practice honored properly on this site. Good luck parsing the following:

String sql = "select first_name, last_name, email, " +
  " address1, address2, city_name, country_name " + 
  " from customer " +
  " inner join country " + 
  " on customer.country_id " +
  " = country.id " +
  " where first_name like '" + 
  firstName + 
  "%' and last_name like '" +
  lastName + 
  "%'" +
  ";";

DELETE. No, INSERT! No, SELECT!

Leave a comment on “DELETE. No, INSERT! No, SELECT! ”