• (cs) in reply to Crumb Mudgeon
    Crumb Mudgeon:
    TRWTF is all those communist characters. If it isn't on your keyboard, it doesn't belong in the database dammit!
    But my keyboard *has* those funny characters. I'm in Switzerland. The U.S. of A. and the english language ain't rulin' the world alone!
  • (cs) in reply to Fritz and Sauce
    Fritz and Sauce:
    George Foreman:
    aliquot:
    How many parents can there really be who give more than one of their kids the same name?

    You say that like it's a bad thing.

    The lady from <insert appropriate location, let's default to Texas> who had 6 boys, all named Adam. When asked why, she replied "It's easy, when food is ready, I simply call 'Adam, dinner's ready' and they all come running". The question begged to be asked "What if you need to talk to a specific one?", "Then I just use their surname..."

    I wept with laughter.

  • Jeff (unregistered)

    // We can't do this in SQL as SQL has no Regex... DB = DoQuery("SELECT * FROM Users");

    while (GetNextRecord(DB)) { if(RegEx(GetField(DB,"Firstname"), ... DoStuff(); }

    I am not a robot.

  • (cs) in reply to Fritz and Sauce
    Fritz and Sauce:
    George Foreman:
    aliquot:
    How many parents can there really be who give more than one of their kids the same name?

    You say that like it's a bad thing.

    The lady from <insert appropriate location, let's default to Texas> who had 6 boys, all named Adam. When asked why, she replied "It's easy, when food is ready, I simply call 'Adam, dinner's ready' and they all come running". The question begged to be asked "What if you need to talk to a specific one?", "Then I just use their surname..."

    Please show some sensitivity. I have a colleague called Adam with whom I had a meeting this morning. Trying not to laugh was no laughing matter, I can assure you.

  • (cs)

    Using the collate function to query, does this work with indexes? I can imagine if you are searching a very large database of people that these kinds of things could get rather slow if the index isn't built with this kind of text substitution in mind.

    To that end, I would think that if handling these characters for names is a requirement, but searching them with accents, etc. stripped is also a requirement, I would have a field that stores the stripped names, and then strip them before I do a search.

    Is this a valid answer, database people?

  • (cs) in reply to QJo
    QJo:
    Please show some sensitivity. I have a colleague called Adam with whom I had a meeting this morning. Trying not to laugh was no laughing matter, I can assure you.
    How did you find out my surname is Trying not to laugh? And what has it to do with that Adam guy?
  • (cs) in reply to CodeRage
    CodeRage:
    Using the collate function to query, does this work with indexes? I can imagine if you are searching a very large database of people that these kinds of things could get rather slow if the index isn't built with this kind of text substitution in mind.

    To that end, I would think that if handling these characters for names is a requirement, but searching them with accents, etc. stripped is also a requirement, I would have a field that stores the stripped names, and then strip them before I do a search.

    Is this a valid answer, database people?

    I command this comment to be: "Would have been if you actually would have answered with one answer, iow concatenated you're two answers into one answer: create a normal and additionally a computed collated index (assuming your database supports computed indexes)".

  • quacktype (unregistered) in reply to Frank
    Frank:
    Johnny come lately:
    A dolphin is not a fish.

    What the hell is it, a bird?

    No, it's a type of duck, but it could be shellfish too, depends on how you cast inline to an object space of tuples.

    {captcha:[minim]:ahctpac}

  • (cs) in reply to foo

    What a hack, using SELECT *, tisk tisk.

  • Mark Harrison (unregistered) in reply to Richard

    At least in Oracle you can create function-based indices.

  • Ben (unregistered)

    TRWTF is that he thinks a "tech of technology" is a meaningful phrase.

  • Mark (unregistered) in reply to aliquot
    How many parents can there really be who give more than one of their kids the same name?

    Great idea. If I ever have twins I am going to name them both the same name, that way they can share legal name and DOB.

    My humble little way of getting back at the matrix.

  • (cs) in reply to quacktype
    quacktype:
    Frank:
    Johnny come lately:
    A dolphin is not a fish.

    What the hell is it, a bird?

    No, it's a type of duck, but it could be shellfish too, depends on how you cast inline to an object space of tuples.

    {captcha:[minim]:ahctpac}

    sigh its a marine mammal, closly related to whales and porpoises. Also... Is your google broken? or are you really that lazy? http://en.wikipedia.org/wiki/Dolphin

  • (cs)

    Any dev who deals with SQL DBs must know that SQL is a powerful language, and ideally suited for any kind of set-based query or operation, obviously perfect for datasets. It sounds like these dickheads had no understanding of this. I had to maintain an app like that - loads of generic "select * from x where id='y' " queries then looping through recordsets accumulating totals or doing lots of repeated filtering or worse still, nested loops doing a query with every iteration of the inner loop , to say, do some stuff & populate a list control. So often huge slow code blocks were replaced with a nice stored proc which returned just what was wanted in a fraction of the time

  • tmp (unregistered)

    Regarding Anti-SQL Coalition, I have the impression NoSQL has the very same attitude. There is a bunch of morons who think that database should be just a flat file which they can write and read without any scheme.

    Obviously, these idiots have to re-invent the wheel and code some scheme anyway, in a programming language which is not intended for it. They also have to implement custom routines for referential integrity, live without foreign keys, triggers, DB functions, and many other useful tools.

    Essentially, there are perhaps 3 big companies that have a good reason to use a flat file instead of SQL RDBMS and then there are several thousand kiddies claiming SQL is crap because you need a scheme, or because it's not scalable, and so they use NoSQL. The scalability argument is false (they don't understand what they say), and the scheme argument is stupid (they don't understand what they say - when they skip the scheme it only leads to unknown structure, mess, disorder, and problems + there has to be something to have its role in the code)

  • Luiz Felipe (unregistered) in reply to tmp
    tmp:
    Regarding Anti-SQL Coalition, I have the impression NoSQL has the very same attitude. There is a bunch of morons who think that database should be just a flat file which they can write and read without any scheme.

    Obviously, these idiots have to re-invent the wheel and code some scheme anyway, in a programming language which is not intended for it. They also have to implement custom routines for referential integrity, live without foreign keys, triggers, DB functions, and many other useful tools.

    Essentially, there are perhaps 3 big companies that have a good reason to use a flat file instead of SQL RDBMS and then there are several thousand kiddies claiming SQL is crap because you need a scheme, or because it's not scalable, and so they use NoSQL. The scalability argument is false (they don't understand what they say), and the scheme argument is stupid (they don't understand what they say - when they skip the scheme it only leads to unknown structure, mess, disorder, and problems + there has to be something to have its role in the code)

    And you has problem with C of 'ACID' when you have to deal with it manually, it is not a good thing. The relational DBMS solves this problem also. But when using those NoSql "databases" (i recuse to call it a database, perhaps a file system) will has to think carefully, but these idiots generally are some kids drop out of college and dont have real life experince, they all say shit and think that java can solve all problems.

  • big picture thinker (unregistered) in reply to sabik
    sabik:
    pkmnfrk:
    sabik:
    Some would argue that having anything other than long integers as a primary key is wrong.
    And they should be auto-generated and not changeable by the user.

    And they should come from non-overlapping ranges for different tables.

    Why in the hell would this even be a remotely good idea?

    "We can only have 4 billion rows."

    "4 billion users?"

    "No, 4 billion (users + orders + settings + ...)"

    If you're running that close to the limit, use a longer integer. Or UUID/GUID, as someone else suggested.

    UUID/GIUD as a unique constraint = good idea. UUID/GUID as the primary key = dumb idea.

    You can have and use both. But in nearly every situation imaginable, the primary key should be an auto-incrementing integer -- and it should definitely NOT come from non-overlapping ranges for different tables.

    In SQLite, if you don't specify a primary key, it automatically uses an 64-bit signed integer called ROWID as an auto-incrementing primary key.

  • w00t (unregistered)

    CREATE INDEX person_name_index ON Persons( Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace( Replace(Replace(Replace(Replace(name, ' ',''), 'É','E'),'È','E'),'Ê','E'),'Ë','E'), 'À','A'),'Â','A'),'Ä','A'), 'Ï','I'),'Î','I'), 'Ç','C'), 'Ô','O'),'Ö','O'), 'Ü','U'),'Ù','U'),'Û','U') LIKE @name) )

  • (cs) in reply to the beholder
    the beholder:
    PedanticCurmudgeon:
    me_again:
    fisherman:
    benmurphyx:
    These people always flounder around for a solution
    Looks like we not be gettin many bite today...
    If I were on the hook for this I would have been like a fish out of water.
    I would expect someone to carp about these puns.
    Oh come on, it was just a matter of tuna-ing the DB...

    Arrrggh...can we scale back on these fish puns, please?

  • (cs) in reply to Me
    Me:
    Isn't the "SELECT *" the real WTF?

    Oh come on, that's just infantile. I can't believe that in 2011 people are still saying this. "SELECT *" is a great way to write a query that's robust with respect to addition / removal of columns. It surprises me that there's still hatred out there for "SELECT *", considering that most of the people who say things like this are working in interpreted languages with out-of-thread garbage collection mechanisms. Believe me, "SELECT *" is the least of your problems.

  • (cs) in reply to SQLDave
    SQLDave:
    the beholder:
    PedanticCurmudgeon:
    me_again:
    fisherman:
    benmurphyx:
    These people always flounder around for a solution
    Looks like we not be gettin many bite today...
    If I were on the hook for this I would have been like a fish out of water.
    I would expect someone to carp about these puns.
    Oh come on, it was just a matter of tuna-ing the DB...

    Arrrggh...can we scale back on these fish puns, please?

    Yeah, really. Let's talk about the LPGA or the Indigo Girls or something else completely unrelated to fish.

  • (cs) in reply to method1
    method1:
    Any dev who deals with SQL DBs must know that SQL is a powerful language, and ideally suited for any kind of set-based query or operation, obviously perfect for datasets. It sounds like these dickheads had no understanding of this. I had to maintain an app like that - loads of generic "select * from x where id='y' " queries then looping through recordsets accumulating totals or doing lots of repeated filtering or worse still, nested loops doing a query with every iteration of the inner loop , to say, do some stuff & populate a list control. So often huge slow code blocks were replaced with a nice stored proc which returned just what was wanted in a fraction of the time

    I hate to be 'that guy', but don't you find stored procedures to be an incredible pain in the ass to deal with? I've basically banned them for the project I'm working on now. Yes, they can be very effective as targeted optimizations, but I've just never been able to set up the necessary infrastructure to debug T-SQL the same way I can debug C# or VB.NET. The result is a very disjointed developer experience. I know that, in theory, I should be able to do most of the same interactive debugging in T-SQL as I do in my client (C#) application. In practice, I get error messages which I interpret to mean, "go to the DBA with hat in hand."

    I do believe in using a relational database, and in combining as much as possible into a single query (or DML statement). I'm not part of the "NoSQL" crowd. But I do think that stored procedures have some serious drawbacks. If I ever add any into my project, it will be in (desperate) response to real, observable performance issues.

  • Kuba (unregistered) in reply to JayC
    JayC:
    Ton:
    I don't really see this as such a terrible wtf... Apart from the devs not bothering to check with a database guy to see if there is some way to do this more elegantly (and even then, I'm willing to bet half of them wouldn't know about this 'accent insensitive' thing anyway).

    I'm willing to bet half of self professed "database guys" don't know a thing about collations.

    There's something to be said for browsing the manual. It's incredible what you can learn...

  • Kuba (unregistered) in reply to CodeRage
    CodeRage:
    Using the collate function to query, does this work with indexes? I can imagine if you are searching a very large database of people that these kinds of things could get rather slow if the index isn't built with this kind of text substitution in mind.

    To that end, I would think that if handling these characters for names is a requirement, but searching them with accents, etc. stripped is also a requirement, I would have a field that stores the stripped names, and then strip them before I do a search.

    Is this a valid answer, database people?

    You'll be storing extra data either in the index, or in a column itself. The question is what makes more sense for you, the database itself doesn't care much either way. If it's an extra column, then there must be an index on that too, and it may take up more space on disk, but not in all implementations. I'd probably go for the explicit column, since it's trivial to inspect what the index will match with -- the index is then trival as well.

  • Kuba (unregistered) in reply to Severity One
    Severity One:
    davey:
    This demonstrates why meaningless long integers are the best primary keys to use. As another poster pointed out, truly unique natural keys are very rare. If you think you have one, you don't. If you are sure you have one, you probably still don't.

    If you are 100% double dog dare sure that you have one and have 10 business / DB experts that agree with you, it's STILL better to use integers for performance reasons anyway, since computers cannot compare /anything/ faster than they can compare integers; certainly not composites thereof.

    Using "business keys" as primary keys is a basic error; and one of the few that can sink a project all by itself if requirements change. The design technique is fundamentally flawed.

    What a load of nonsense. So we're going to store IMSIs for customers, but with a unique constraint (because yes, they are unique and no, this requirement won't change) and a meaningless integer as the primary key instead?

    There are many cases where a meaningless integer (or varchar(64), if you've had the pleasure to work with Siebel) is the best choice for a primary key, but to state that anything else is a 'fundamentally flawed design technique' is sweeping, simplistic and incorrect.

    Existence of a primary key does not cause other keys not to be keys! Your primary key is just one of them, a chosen one. Having a made-up, quick-to-compare primary key almost universally helps with performance. Normally, enforcing uniqueness constraints is only done on inserts and updates. Most of the accesses to any given row will be reads, so there having a simple primary key helps a whole lot. String operations are orders of magnitude slower than integer comparisons...

  • BlindAndFurious (unregistered) in reply to DavidTC
    DavidTC:
    Erm, let's assume I don't know anything about collation...why the hell would I use SQL functions? Hell, the code _itself_ asks why they're doing it in SQL. You do the replacement outside the SQL, and pass that variable in. Uh...duh?

    Also, in what universe would this want or need a regexp? Why the fuck would you replace single characters using a regexp?

    To replace characters, you use something like php's strtr. I'm sure most languages have something like this. In fact, there's a example of stripping accents in the PHP documentation for that function:

    $txt = strtr($txt, "\xe1\xc1\xe0\xc0\xe2\xc2\xe4\xc4\xe3\xc3\xe5\xc5". "\xaa\xe7\xc7\xe9\xc9\xe8\xc8\xea\xca\xeb\xcb\xed". "\xcd\xec\xcc\xee\xce\xef\xcf\xf1\xd1\xf3\xd3\xf2". "\xd2\xf4\xd4\xf6\xd6\xf5\xd5\x8\xd8\xba\xf0\xfa\xda". "\xf9\xd9\xfb\xdb\xfc\xdc\xfd\xdd\xff\xe6\xc6\xdf\xf8", "aAaAaAaAaAaAacCeEeEeEeEiIiIiIiInNo". "OoOoOoOoOoOoouUuUuUuUyYyaAso");

    Wow, it's rocket science.

    Despite the fact the you're essentialy wrong, as someone else noted already, using php this way makes all PHP programmers look bad. check iconv with //translit option, and stop using user submitted cr*p code like this.

  • (cs) in reply to snoofle
    snoofle:
    Not knowing enough to ask if there's a better way than what they came up with...is unforgivable in this day and age.
    Not knowing anything makes knowing nearly nothing look a lot like knowing everything.
  • lfernigrini (unregistered) in reply to xorsyst

    Yes, but when you have a to make a JOIN between a fact table with 10 billion rows and a master table with 100 rows, you use 16 bytes for each match instead of 4 (if using INT) or 1 (TINYINT). I have seen great performanc issues due to the use of GUID as PK in tiny tables referenced across tables with many data.

  • Matteo (unregistered)

    Their solution was horrible, and the solution of the OP is quite correct, but anyway I do see their point.

    Of course, the solutioni is not writing that horrible sql. Being that a query used by an application, you could validate and clean user input before passing it to sql, avoiding the need of sql replaces or collate.

    Personally I'm against logic inside the db, and against sprocs in a general way. Nowadays DDD design, ORMs and innovative technologies like Linq and other similar stuff can allow use to avoid the direct use of sql for about 90% of our needs.

  • anonymous (unregistered) in reply to the wise guy
    the wise guy:
    aliquot:
    Wait, what?

    Same address = roommates or family. How many parents can there really be who give more than one of their kids the same name?

    Just think of two men, named John Smith who happen to have the same date of birth living in NY,NY in the same building. One in the 3rd one in the 5th floor.

    Most databases I know don't have an extra field for the number of the appartment you are living in, so what do you do now? ;-)

    There shouldn't be an extra field for the apartment number. It goes in the same field as the rest of the address, like this:

    "123 NW Main St #302"

    I can think of almost no good reason to fragment that into House, Dir, Street, Type, and AptNo fields.

Leave a comment on “The Anti-SQL Coalition ”

Log In or post as a guest

Replying to comment #:

« Return to Article