Trouble Ahead



  • Another team is doing a graft-on project to our system. We were just asked to comment on their proposed db schema changes. This table is presented in its unmodified entirety:

     

    create table InboundMessages (
    isProd varchar2(1) default='Y',
    isQA varchar2(1) default='N',
    isDev varchar2(1) default='N',
    flags varchar2(64) not null, -- copied from msgXml
    msgId number not null, -- copied from msgXml
    state varchar2(16) not null, -- copied from msgXml
    msgXml clob, -- the inbound message we received
    extraData varchar2(4000), -- sometimes there's extra data in the MessageMap - store it here
    created timestamp not null, -- required for audit
    createdBy varchar2(64) not null, -- required for audit
    updated timestamp null, -- required for audit
    updatedBy varchar2(64) null -- required for audit
    );



  • So, what's with the first three columns, and the last four "audit" columns?  And I love the self-documenting "flags"...  What flags?  For what?!

    This table can essentially be whittled down to, what, five columns and it wouldn't lose a thing (assuming you chuck the auditing into another table)...



  • I can't get past the is{Prod,QA,Dev} columns. So the data in the database dictates your environment? And it does so for every single record? Which means that this table is going to contain a mixture of data from different environments? No, nothing bad could ever come from that. If you're ever in my neck of the woods, let me know, because you - sir - deserve a beer.


  • ♿ (Parody)

    @snoofle said:

    Another team is doing a graft-on project to our system. We were just asked to comment on their proposed db schema changes. This table is presented in its unmodified entirety:

    So this is what the current table actually looks like? The prod/qa/dev stuff is just...frightening. Hey, at least your XML is being stored as a clob (NB...this looks like Oracle). I have a DB where they store XML in blobs. Never played with the xmltype, but seems like it should have advantages over clobs.



  • @C-Octothorpe said:

    So, what's with the first three columns, and the last four "audit" columns?  And I love the self-documenting "flags"...  What flags?  For what?!

    This table can essentially be whittled down to, what, five columns and it wouldn't lose a thing (assuming you chuck the auditing into another table)...

    We got bought out a little while back, and the much larger conglomerate has formal auditing. The folks here are confused by the everything-must-be-auditable mandate. The audit columns are mandatory for all tables, even when they don't make sense because the record gets overwritten (eg: use a history table instead of doing it this way) - blind obedience by folks who don't quite understand what auditing is for.

    Yes, they are planning to only have one set of tables for prod/qa/dev data. Their rationale is that in the current setup, the prod data is too big to copy to the modest dev db. This way, we can just flag test data in the prod db and have the prod code ignore it (select ... where isProd='Y' and ...). A few of us flatly refused to sabotage the business (we used that word) in this way, and we explained it to the higher ups. Apparently this is a new concept to them, but they seem willing to listen.

    The flags field is essentially a bunch of 1-char fields from within the xml so that you can query it without using xpath, but that still leaves it wholly confusing, and as horribly slow sql (write sql to pick out a random number of flags from that string and you'll see what I mean).




  • @boomzilla said:

    @snoofle said:
    Another team is doing a graft-on project to our system. We were just asked to comment on their proposed db schema changes. This table is presented in its unmodified entirety:
    So this is what the current table actually looks like? The prod/qa/dev stuff is just...frightening. Hey, at least your XML is being stored as a clob (NB...this looks like Oracle). I have a DB where they store XML in blobs. Never played with the xmltype, but seems like it should have advantages over clobs.
    The current tables are nothing like this (except for the auditing columns). There is a clob to store the xml for auditing, but it's never used by the code; the data is broken up into component fields and stored as individual columns - like a normal f'g db.

    This place is like India-West; the worst India had to offer got shipped here, and they spend all day in slow motion fucking things up in the worst possible way. There are 4 of us out of about 100+ developers that actually get it, and we do make some headway, mostly because I know how to scare managers in terms they understand (massive costs within 6 months for support and slower upgrades/enhancements). It's just that the sheer volume of stupidity manages to get past us because we don't control enough of what's going on to stop all of it.




  • delete from InboundMessages where isDev = 'Y'
    * Hits F5

    WIN!



  • @Dorus said:

    delete from InboundMessages where isDev = 'Y'
    * Hits F5

    WIN!

    That's funny but it only masks the underlying problem: the db schema is still effed up.

    Has management ever heard of a reasonable sample of the data?  Who says you need all production data in dev?  Other than stress/load/performance testing, I wouldn't bother with more than 50k records max.



  • @dohpaz42 said:

    I can't get past the is{Prod,QA,Dev} columns. So the data in the database dictates your environment? And it does so for every single record? Which means that this table is going to contain a mixture of data from different environments? No, nothing bad could ever come from that. If you're ever in my neck of the woods, let me know, because you - sir - deserve a beer.
     

    I'd settle for an explanation of why it's three separate Y/N fields instead of one "dataenvironment" field with permissible values of "Prod", "QA" or "Dev".  They can start by describing a plausible scenario where zero, two or three of the fields has the value Y.



  • @da Doctah said:

    @dohpaz42 said:

    I can't get past the is{Prod,QA,Dev} columns. So the data in the database dictates your environment? And it does so for every single record? Which means that this table is going to contain a mixture of data from different environments? No, nothing bad could ever come from that. If you're ever in my neck of the woods, let me know, because you - sir - deserve a beer.
     

    I'd settle for an explanation of why it's three separate Y/N fields instead of one "dataenvironment" field with permissible values of "Prod", "QA" or "Dev".  They can start by describing a plausible scenario where zero, two or three of the fields has the value Y.

    That's easy, it's called corrupt data.



  • @da Doctah said:

    @dohpaz42 said:

    I can't get past the is{Prod,QA,Dev} columns. So the data in the database dictates your environment? And it does so for every single record? Which means that this table is going to contain a mixture of data from different environments? No, nothing bad could ever come from that. If you're ever in my neck of the woods, let me know, because you - sir - deserve a beer.
     

    I'd settle for an explanation of why it's three separate Y/N fields instead of one "dataenvironment" field with permissible values of "Prod", "QA" or "Dev".  They can start by describing a plausible scenario where zero, two or three of the fields has the value Y.

    Or why they don't at least use a Boolean. Or does that not exist in that particular implementation of SQL?
    Or an integer and the first bit would describe the state of production, second bit and so on...



  • I especially like the fact that the default is for data to be isProd, because the only circumstance where data could be inserted without specifying would be in a production app/script/etc.



  • Even if it wasn't a terrible way to "separate" production/dev/QA data, haven't these people ever heard of a bitfield?



  • @da Doctah said:

    I'd settle for an explanation of why it's three separate Y/N fields instead of one "dataenvironment" field with permissible values of "Prod", "QA" or "Dev".  They can start by describing a plausible scenario where zero, two or three of the fields has the value Y.

    I could see QA and Dev not being mutually-exclusive. But Prod definitely needs to be mutually-exclusive with both QA and Dev.



  • @boomzilla said:

    @snoofle said:
    Another team is doing a graft-on project to our system. We were just asked to comment on their proposed db schema changes. This table is presented in its unmodified entirety:

    So this is what the current table actually looks like? The prod/qa/dev stuff is just...frightening. Hey, at least your XML is being stored as a clob (NB...this looks like Oracle). I have a DB where they store XML in blobs. Never played with the xmltype, but seems like it should have advantages over clobs.

    xml datatype in MSSQL = best thing EVAR. It doesn't make XML any less shitty, but it does mean you can view the field contents without having to pipe them through another app that reformats the XML so it makes sense.

    @blakeyrat said:

    @da Doctah said:
    I'd settle for an explanation of why it's three separate Y/N fields instead of one "dataenvironment" field with permissible values of "Prod", "QA" or "Dev".  They can start by describing a plausible scenario where zero, two or three of the fields has the value Y.

    I could see QA and Dev not being mutually-exclusive. But Prod definitely needs to be mutually-exclusive with both QA and Dev.

    Well, first the creators of this table would need to learn what constraints are. Or to replace the 3 crap "is" columns with a ServerType int field. (I'm presuming they're using an RDBMS that doesn't support boolean datatypes, which is quite frankly something that would make me run for the hills.)

    Am I the only one who noticed that the 3 is-columns are defined as nullable, or is this an RDBMS where "default" implies not null? If they are nullable I can see you having some FUN TIMES.



  • @da Doctah said:

    I'd settle for an explanation of why it's three separate Y/N fields instead of one "dataenvironment" field with permissible values of "Prod", "QA" or "Dev".  They can start by describing a plausible scenario where zero, two or three of the fields has the value Y.

    Because inevitably someone who knows someone else who once read a DB book cover which has been wrapped around a cooking book will add a unique constraint to the flags column. With only one environment column, it would be unpossable to have records with the same flags in Dev and Prod at the same time. Duh!

     


  • ♿ (Parody)

    @The_Assimilator said:

    I'm presuming they're using an RDBMS that doesn't support boolean datatypes, which is quite frankly something that would make me run for the hills.

    Why would you need an explicit boolean data type? Still assuming this is Oracle, the standard way to do it is a number(1) field. Maybe your statement is just a proxy for detection of Oracle.



  • @boomzilla said:

    Why would you need an explicit boolean data type?
    Because it takes 1/32 the storage space of an int?



  • Here's $40...  Go buy yourself a 1TB HD.  :)



  • @boomzilla said:

    @The_Assimilator said:
    I'm presuming they're using an RDBMS that doesn't support boolean datatypes, which is quite frankly something that would make me run for the hills.

    Why would you need an explicit boolean data type? Still assuming this is Oracle, the standard way to do it is a number(1) field. Maybe your statement is just a proxy for detection of Oracle.


    Booleans aren't the same as one-bit integers. What should you get if you add two booleans together? Either an error or the logical OR is defensible, whereas adding two one-bit integers is a logical XOR.

    (Keeps thing simple for the ORM writers, too, but that's not a massively high priority).



  • @C-Octothorpe said:

    Here's $40...  Go buy yourself a 1TB HD.  :)

    Grr... that is not the solution. I know you're joking, but it really annoys me that programmers today seem to code like memory is free and infinite. They don't realize the performance problem it creates because they never test it on production size stuff. Drives me nuts. I think I actually know the product our OP is working with here... the "audit" columns are baked into their API and basically, if you create a table without them, you can't access it through the API, OR the API refuses to build, or worse. They did a nice job creating an object/relational API which handles customization, but unfortunately, it hobbles the database, and as a result, due to the bad foundation, the whole application performs like crap. Unless you're working with 2 customers worth of sample data.



  • @snoofle said:

    msgXml clob, -- the inbound message we received
     

    never heard of this datatype, but assuming from the rest of the table, it's Character Large Object?



  • @boomzilla said:

    @The_Assimilator said:
    I'm presuming they're using an RDBMS that doesn't support boolean datatypes, which is quite frankly something that would make me run for the hills.

    Why would you need an explicit boolean data type? Still assuming this is Oracle, the standard way to do it is a number(1) field. Maybe your statement is just a proxy for detection of Oracle.

    Well, I'd argue that if such a type of variable exists and your data fits perfectly into the schema of the variable, why not use it?
    If it walks like a boolean, looks like a boolean and quacks like a boolean - why use an Int?



  • @SEMI-HYBRID code said:

    @snoofle said:

    msgXml clob, -- the inbound message we received
     

    never heard of this datatype, but assuming from the rest of the table, it's Character Large Object?

    Google says you're correct.

  • ♿ (Parody)

    @Rhywden said:

    Well, I'd argue that if such a type of variable exists and your data fits perfectly into the schema of the variable, why not use it?

    If it walks like a boolean, looks like a boolean and quacks like a boolean - why use an Int?

    I would, too, but that's not what we were talking about.



  • @pjt33 said:

    @boomzilla said:
    @The_Assimilator said:
    I'm presuming they're using an RDBMS that doesn't support boolean datatypes, which is quite frankly something that would make me run for the hills.
    Why would you need an explicit boolean data type? Still assuming this is Oracle, the standard way to do it is a number(1) field. Maybe your statement is just a proxy for detection of Oracle.
    Booleans aren't the same as one-bit integers. What should you get if you add two booleans together? Either an error or the logical OR is defensible, whereas adding two one-bit integers is a logical XOR. (Keeps thing simple for the ORM writers, too, but that's not a massively high priority).
    NUMBER(1) is not a one-bit integer; it is a one-digit (decimal) integer. So it's got plenty of capacity for Dev, QA and Prod, with room for future expansion if they get a separate Test environment. Also, if you add two integers (of any size) together, you should get their sum. If you attempt to store this in a field that's too small for the result value, you should get an error. TRWTF would be if, as you suggest, 1+1 could be stored as 0 and not raise an error.



  • @Scarlet Manuka said:

    @pjt33 said:
    @boomzilla said:
    @The_Assimilator said:
    I'm presuming they're using an RDBMS that doesn't support boolean datatypes, which is quite frankly something that would make me run for the hills.
    Why would you need an explicit boolean data type? Still assuming this is Oracle, the standard way to do it is a number(1) field. Maybe your statement is just a proxy for detection of Oracle.
    Booleans aren't the same as one-bit integers. What should you get if you add two booleans together? Either an error or the logical OR is defensible, whereas adding two one-bit integers is a logical XOR. (Keeps thing simple for the ORM writers, too, but that's not a massively high priority).
    NUMBER(1) is not a one-bit integer; it is a one-digit (decimal) integer. So it's got plenty of capacity for Dev, QA and Prod, with room for future expansion if they get a separate Test environment. Also, if you add two integers (of any size) together, you should get their sum. If you attempt to store this in a field that's too small for the result value, you should get an error. TRWTF would be if, as you suggest, 1+1 could be stored as 0 and not raise an error.


    A number1 of architectures use modular arithmetic for unsigned integers. For 16-bit arithmetic, x86-style, this would mean that 65,535 plus one is, guess what? Zero2. Would it raise an error? Depends on the program and architecture.


    1I'm not going to look this up.
    2With the flag set, of course.



  • @jasmine2501 said:

    Grr... that is not the solution. I know you're joking, but it really annoys me that programmers today seem to code like memory is free and infinite. They don't realize the performance problem it creates because they never test it on production size stuff.
    We have a certain ERP and accounting system that seems excellent to a non-developer, but with my company's volume of about a million general ledger entries a month (in the largest database), the stored procedures fail when closing accounting periods after 16 hours of trying.  This software costs 2 grand per seat.

    They never stress-tested their code, that much is obvious.  I cracked open the SQL and also determined that they never even gave a crap about performance.  They had quintuple-nested SQL statements inside of loops, some layers of which were runtime-created using concatenation and EXEC. 

    The slowest sproc was 7 pages long.  I had to print it out, tape the pages together, fasten the whole thing to my office door, and start drawing arrows.  I ended up reducing the whole thing to 14, completely-unrolled simple SQL statements, reducing a 16 hour sproc to 8 seconds and basically saving the company, since the vendor had no clue how to fix it and we needed the periods closed to secure financing for an expansion.



  • @snoofle said:

    Another team is doing a graft-on project to our system. We were just asked to comment on their proposed db schema changes. This table is presented in its unmodified entirety:

     

    create table InboundMessages (
    isProd varchar2(1) default='Y',
    isQA varchar2(1) default='N',
    isDev varchar2(1) default='N',
    flags varchar2(64) not null, -- copied from msgXml
    msgId number not null, -- copied from msgXml
    state varchar2(16) not null, -- copied from msgXml
    msgXml clob, -- the inbound message we received
    extraData varchar2(4000), -- sometimes there's extra data in the MessageMap - store it here
    created timestamp not null, -- required for audit
    createdBy varchar2(64) not null, -- required for audit
    updated timestamp null, -- required for audit
    updatedBy varchar2(64) null -- required for audit
    );

     

     

    isProd    varchar2(1)  default='1',
    sQA varchar2(1) default='0',
     

    Oh yeah i hurd you like flags so i put a flag in your char dawg



  • @hoodaticus said:

    reducing a 16 hour sproc to 8 seconds and basically saving the company, since the vendor had no clue how to fix it
     

    What were the resultant money streams from the Vendor to your company, if any?

     



  •  @dhromed said:

    What were the resultant money streams from the Vendor to your company, if any?
    I told them that if they wanted teh codez, we'd want 5 free seat licenses and 3 years of free support.  But when I explained their incompetence to my CTO, who had long since become fed-up with their inability to make their own product actually work, he said, 'No way in hell; fuck ' + @vendorName + '!'.  Which was later communicated to them by one of their licensed dealers.


  • ♿ (Parody)

    @hoodaticus said:

    'No way in hell; fuck ' + @vendorName + '!'.  Which was later communicated to them by one of their licensed dealers.

    ZOMG! The dreaded invection injection vulnerability!



  • @hoodaticus said:

     @dhromed said:

    What were the resultant money streams from the Vendor to your company, if any?
    I told them that if they wanted teh codez, we'd want 5 free seat licenses and 3 years of free support.  But when I explained their incompetence to my CTO, who had long since become fed-up with their inability to make their own product actually work, he said, 'No way in hell; fuck ' + @vendorName + '!'.  Which was later communicated to them by one of their licensed dealers.
    I think I would have, at the very least, demanded a refund in addition to telling them to FOAD.  Depending on the size of the refund, I *may* have given them teh codez...

    Tell me you got some kind of bonus or recognition for this (yes, I'm a money whore, and don't fucking pretend for one second you're not).



  • @C-Octothorpe said:

    Tell me you got some kind of bonus or recognition for this (yes, I'm a money whore, and don't fucking pretend for one second you're not).
    Oh yeah, I'm a major whore.  I did get a 25% raise later, when I suggested that I might accept a certain proposition from a certain 4-letter online computer retailer to re-do their website.  I think the @productName rewrites I did probably went a long way to proving I was worth keeping.



  • @hoodaticus said:

    I had to print it out, tape the pages together, fasten the whole thing to my office door, and start drawing arrows.
    plz send teh pics!



  • @fatbull said:

    Filed under: ಠ_ಠ

    I like how the single underscore merges with the underline.



  • @derula said:

    Filed under: , " ", how, the, I, Like, ಠ_ಠ, --, >>, under there

    I like how your tagging script found ಠ_ಠ's companions "", -- and >_>.

    Not my creation, by the way.



  • @fatbull said:

    I like how your tagging script found ಠ_ಠ's companions "_", -_- and >_>.
     

    Too bad those are squares on this Mac... Not eyes.

    EDIT:




  • @Zemm said:

    Filed under: _

    Why does this exist?

    Oh, it's in Private Use section, so it's probably invented by Apple for use on their machines, but then somehow found its way back to other people's fonts?



    Edit: For the record: I'm on Linux and can see them. DejaVu Sans seems to have it.



  • @derula said:

    @Zemm said:
    Filed under: _

    Why does this exist?

    Oh, it's in Private Use section, so it's probably invented by Apple for use on their machines, but then somehow found its way back to other people's fonts?



    Edit: For the record: I'm on Linux and can see them. DejaVu Sans seems to have it.

     

    Can't see them here in Windows Vista, using Stylish to make TDWTF use Cambria.

    WELP.

    MY LOSS.



  • @dhromed said:

    Can't see them here in Windows Vista, using Stylish to make TDWTF use Cambria.
    They show up for me in Windows 7 in Opera, but not in IE.



  • Instead of apple logos, My Firefox displays the typical "no glyph for this" blocks with the illegible code inside. Opera 11.5 displays absolutely nothing at all.



  • Interestingly I'm on the iPad now and the eyes are visible, and the underlined underscore is visible. But my screenshot looks like crap.

    Edit: I decided to try Opera 11.50 (on Windows 7) and I get dots instead of Apples.



  • @Zemm said:

    Edit: I decided to try Opera 11.50 (on Windows 7) and I get dots instead of Apples.

    11.10, XP, questionmark-in-a-box. Which is how Apple wants you to imagine your computer asq.



  • @Sutherlands said:

    @SEMI-HYBRID code said:

    @snoofle said:

    msgXml clob, -- the inbound message we received
     

    never heard of this datatype, but assuming from the rest of the table, it's Character Large Object?

    Google says you're correct.
     

    TEXT / LONGTEXT was not enterprisey enough name for oracle to use?

    ("It's a DATATYPE, dammit, it's purpose shouldn't be obvious for people who never worked with oracle!"

    "Okay, let's name it 'clob'."

    [suggester gets an applause and a raise] )

     


  • ♿ (Parody)

    @SEMI-HYBRID code said:

    TEXT / LONGTEXT was not enterprisey enough name for oracle to use?

    The CLOB actually replaces and improves upon the LONG.

    @SEMI-HYBRID code said:


    ("It's a DATATYPE, dammit, it's purpose shouldn't be obvious for people who never worked with oracle!"

    "Okay, let's name it 'clob'."

    [suggester gets an applause and a raise] )

    I think the name actually seems pretty obvious if you're familiar with databases. Don't most of them have some sort of BLOB type? Plus, you get to say stuff like, "I had to CLOBBerize that column. The stupid users wanted more than 4000 characters."



  • @boomzilla said:

    I think the name actually seems pretty obvious if you're familiar with databases. Don't most of them have some sort of BLOB type? Plus, you get to say stuff like, "I had to CLOBBerize that column. The stupid users wanted more than 4000 characters."

    Meanwhile, Microsoft's simplified their database so that all text-storage tasks can be achieved with a single data type, NVarChar. (It still has a fucking stupid name, though.)


  • ♿ (Parody)

    @blakeyrat said:

    Meanwhile, Microsoft's simplified their database so that all text-storage tasks can be achieved with a single data type, NVarChar. (It still has a fucking stupid name, though.)

    I suspect one reason Oracle wouldn't want to do that is backwards compatibility. VARCHAR2 (which replaced VARCHAR, which was limited to 2000 characters) is limited to 4000 characters, and I'm sure that assumption is baked into so many things already...Having different datatypes for really large stuff probably also makes storage optimizations easier.



  • @boomzilla said:

    @blakeyrat said:
    Meanwhile, Microsoft's simplified their database so that all text-storage tasks can be achieved with a single data type, NVarChar. (It still has a fucking stupid name, though.)

    I suspect one reason Oracle wouldn't want to do that is backwards compatibility. VARCHAR2 (which replaced VARCHAR, which was limited to 2000 characters) is limited to 4000 characters, and I'm sure that assumption is baked into so many things already...Having different datatypes for really large stuff probably also makes storage optimizations easier.

    VARCHAR3, duh.

    VARCHAR311FORWORKGROUPS


  • Trolleybus Mechanic

    @blakeyrat said:

    @boomzilla said:
    @blakeyrat said:
    Meanwhile, Microsoft's simplified their database so that all text-storage tasks can be achieved with a single data type, NVarChar. (It still has a fucking stupid name, though.)
    I suspect one reason Oracle wouldn't want to do that is backwards compatibility. VARCHAR2 (which replaced VARCHAR, which was limited to 2000 characters) is limited to 4000 characters, and I'm sure that assumption is baked into so many things already...Having different datatypes for really large stuff probably also makes storage optimizations easier.

    VARCHAR3, duh.

    VARCHAR311FORWORKGROUPS

     

    Goddamn Mozilla just came out with another fucking Varchar. I think it's Varchar9, but I can't tell since they've removed the fucking version numbers.

    And get this-- their reason is because it's a "living" data type.


Log in to reply