Foreign Key Fail



  • This system uses an embedded Firebird database. Some of the gems in this database design include the "logging" table

    Table: Logging -

    • Timestamp: DATETIME
    • int: DEVICEID
    • int: CONFIGID
    • Char(1): CONFIGIDHASCHANGED
    • int: MILEAGEID
    • Char(1): MILEAGEIDHASCHANGED
    • ...
    • int: SENSORID
    • Char(1): SENSORIDHASCHANGED
    • ...

    For reference, this is the "sensor" table

    Table: SENSOR -

    • int: SENSORID
    • int: SENSORNUMBER
    • decimal (5,2): SENSORVALUE
    • int: SENSORTYPE

    One thing to note about the implementation of this system is that sensor IDs are NEVER reused in the "logging" table, so the sensor table and logging table are always exactly the same size, and SENSORIDHASCHANGED is always 1. A smaller, but annoying WTF includes the fact that the ID columns are not actually defined as foreign keys.

    Bonus WTF: passwords are identical to usernames and stored in the DB as plain MD5 hashes on a system that is exposed to the internet.



  • Wait, what was the address of the system?



  •  Oh, yeah, the Firebird database system... that has a place in history as the reason why the Firebird browser (then recently renamed from Phoenix) had to change its name yet again to Firefox, thus losing its parallel naming with Thunderbird and Sunbird.



  • @shimon said:

    Wait, what was the address of the system?

    Yea, could you post the URL?  I've got a couple of hours to play around this afternoon.

     MArk B.



  • @SteamBoat said:

    @shimon said:

    Wait, what was the address of the system?

    Yea, could you post the URL?  I've got a couple of hours to play around this afternoon.

     MArk B.

    Sure ... I'll get right on that.



  • @dtobias said:

     Oh, yeah, the Firebird database system... that has a place in history as the reason why the Firebird browser (then recently renamed from Phoenix) had to change its name yet again to Firefox, thus losing its parallel naming with Thunderbird and Sunbird.

    it's Mozilla's own-ass fault for picking a name that everybody already associated with another product in the first place. If they were so keen on theme-naming (and they shouldn't be, considering how much Thunderbird sucks), then they should have picked a theme with enough unique names in it.



  •  @blakeyrat said:

    they should have picked a theme with enough unique names in it.

    In their defense, even if you choose a theme with dfghjk you'll soon run out of domain names these days.



  • @b-redeker said:

     @blakeyrat said:

    they should have picked a theme with enough unique names in it.

    In their defense, even if you choose a theme with dfghjk you'll soon run out of domain names these days.

    Ke$ha seems to have got it figured out. And Snooki.



  •  Rainbird? Icebird? Dewbird? Birdbird? There are lots of birds out there... not sure why they had to stick with fire...



  • @blakeyrat said:

    Thunderbird sucks

    Yay!! I found someone who agrees with me!!!



  • @MeesterTurner said:

    @blakeyrat said:
    Thunderbird sucks

    Yay!! I found someone who agrees with me!!!

    If you need to convince them, just have them "up"grade to version 3. They'll soon be on our side.



  • So, what's wrong with it?

    I use it. It has an excellent search feature, but that tabbed shit is really, really misinformed.

    I'm open to alternatives, though. I'm just not sure what kind of features email really needs that TB doesn't provide.



  • @dhromed said:

    I'm just not sure what kind of features email really needs that TB doesn't provide.

    Mini nukes?



  • @Scarlet Manuka said:

    @dhromed said:
    I'm just not sure what kind of features email really needs that TB doesn't provide.
    Mini nukes?
     

    Well, true, true, though problems solved by mini nukes are not necessarily exclusively solved by them— there may very well be other solutions extant, rendering the applied aftermarket mini nuke a little superfluous.



  • @dhromed said:

    I'm open to alternatives, though. I'm just not sure what kind of features email really needs that TB doesn't provide.

     

    Instead of looking for an alternative to Thunderbird, look for an alternative to email. As a whole, email sucks. I find myself using it less and less.



  • If all you need is essential features, you can just start a command prompt, and do whatever you want there. Interface and ease of use is what you're looking for.

    I've used Thunderbird, Outlook, Outlook Express, Notes, Groupwise and several webmail clients in the past years, and coming back to Outlook (2007) recently surprised me again on how rich and intuitive the interface is. I don't actually hate TB (not like I hate Notes) but it hasn't really grown on me.



  • @dhromed said:

    So, what's wrong with it?

    Well, it uses about 3 times the memory and runs about 3 times slower than the last version. It constantly re-indexes email-- it seems like if you get a single incoming email, it has to re-index the entire mailbox or something crazy, I have no idea what it's doing that makes it so slow really. It still can't reliably talk to Gmail's IMAP servers (and yes, that's possibly/probably Gmail's fault, but that doesn't change the fact that I need the feature to work and it doesn't). Oh, and when it fails to talk to Gmail's IMAP servers, it crashes most of the time.

    @dhromed said:

    I'm open to alternatives, though. I'm just not sure what kind of features email really needs that TB doesn't provide.

    I use web mail for reading/writing, and Windows Live Mail (which is quite good and also free) to download it all for backup.

    I'm not saying Thunderbird 3 is bad because it lacks features, I'm saying it's bad because it's a bloated mess that crashes frequently.



  • Anyone here remembers Pegasus Mail?



  • @bannedfromcoding said:

    Anyone here remembers Pegasus Mail?
     

    Yes. Do you remember BlueWave?



  • @badcaseofspace said:

    @bannedfromcoding said:

    Anyone here remembers Pegasus Mail?
     

    Yes. Do you remember BlueWave?

    No, but my entire life used to revolve around Claris Emailer 2. Is there a point to this?



  • I remember my old cat.

    She was black and developed epilepsy around when she turned 19 and regularly pissed on herself.

    Yea.

     



  •  @dhromed said:

    I remember my old cat.

    She was black and developed epilepsy around when she turned 19 and regularly pissed on herself.

    Yea.

    Did you guys mail much?



  •  You wouldn't believe.



  • @blakeyrat said:

    I'm not saying Thunderbird 3 is bad because it lacks features, I'm saying it's bad because it's a bloated mess that crashes frequently.
     

    That's strange. Thunderbird runs on my PC 24/7, and has never crashed once in the years I've been using it. The only time I close it is when it updates.



  • @blakeyrat said:

    @dtobias said:

     Oh, yeah, the Firebird database system... that has a place in history as the reason why the Firebird browser (then recently renamed from Phoenix) had to change its name yet again to Firefox, thus losing its parallel naming with Thunderbird and Sunbird.

    it's Mozilla's own-ass fault for picking a name that everybody already associated with another product in the first place. If they were so keen on theme-naming (and they shouldn't be, considering how much Thunderbird sucks), then they should have picked a theme with enough unique names in it.

    Or they could've "pulled an Apple": Yes, there is another product that clashes with our new theme name, but we don't give a shit about it. And then make the original trademark owner change their product's name.

    Examples: iPhone, iPad, iOS (ok, in Apple's defense, they actually asked Cisco for permission to use the IOS name)



  • @bannedfromcoding said:

    Anyone here remembers Pegasus Mail?
     

    I still use Pegasus Mail.



  • @bannedfromcoding said:

    Anyone here remembers Pegasus Mail?
     

    No, but I remember All-in-1, a CLI email tool that ran on (I think) VMS.  This was during my degree course year in industry for a large multi-national company.  The students in the organisation used to mail each other all day so when you got a mail on the list you'd type RA and reply to all.  I was sending about a thousand mails a month and so got pretty adept at using the interface.

    At this time one of my jobs at the time was to schedule the unpacking data from archive tapes to the production server then upload the data into the system I worked on.  Once they were uploaded I deleted the files to make room for the next night's unpack operation.  One of the things not to do was delete the docs before you'd uploaded them but this did happen...

    One day I got a mail from the other students, which I read and then immediately afterwards one to the entire department. I hit RA and sent the message "I've just accidentally deleted 12,000 documents from production.  Oh well, easy come easy go."  I then heard the beeps as this mail was delivered to the department.



  • Yup, ALL-IN-1 (all caps) was VMS. I preferred plain VMS Mail Utility than the hog where possible, honestly. On the other hand, DECnotes BBS software was cool.



  • @bannedfromcoding said:

    Yup, ALL-IN-1 (all caps) was VMS. I preferred plain VMS Mail Utility than the hog where possible, honestly. On the other hand, DECnotes BBS software was cool.
     

    ALL-IN-1 seemed to do everything I needed it to (i.e. send mails quickly to my fellow student skivers) and I don't remember it being much better or worse than PINE, which we had at university.

    From ALL-IN-1 the company upgraded to Lotus Notes.

     



  • @RTapeLoadingError said:

    One day I got a mail from the other students, which I read and then immediately afterwards one to the entire department. I hit RA and sent the message "I've just accidentally deleted 12,000 documents from production.  Oh well, easy come easy go."  I then heard the beeps as this mail was delivered to the department.
     

    So that was you!!

    Seriously, did you manage get out of that mess unharmed?



  • @rad131304 said:

    This system uses an embedded Firebird database. Some of the gems in this database design include the "logging" table

    Table: Logging -

    • Timestamp: DATETIME
    • int: DEVICEID
    • int: CONFIGID
    • Char(1): CONFIGIDHASCHANGED
    • int: MILEAGEID
    • Char(1): MILEAGEIDHASCHANGED
    • ...
    • int: SENSORID
    • Char(1): SENSORIDHASCHANGED
    • ...

    For reference, this is the "sensor" table

    Table: SENSOR -

    • int: SENSORID
    • int: SENSORNUMBER
    • decimal (5,2): SENSORVALUE
    • int: SENSORTYPE

    One thing to note about the implementation of this system is that sensor IDs are NEVER reused in the "logging" table, so the sensor table and logging table are always exactly the same size, and SENSORIDHASCHANGED is always 1. A smaller, but annoying WTF includes the fact that the ID columns are not actually defined as foreign keys.

    Bonus WTF: passwords are identical to usernames and stored in the DB as plain MD5 hashes on a system that is exposed to the internet.

    Ah yes, you can always tell a quality DB design by uppercase column names, and boolean columns that are typed as char(1) or tinyint.

    But that can't beat the legacy system I'm currently working on. Most of the customer's DB tables have an "Active" column, which is a 4-byte integer. We suggested changing the datatype to something meaningful, like I dunno, a bit, but that's apparently not possible because their apps are all coded to expect this field to be an integer, even though none of the said apps store anything other than 0 or 1 in this column. Their "solution" was to add a constraint to prevent anything other than 0 or 1 from being inserted.

    But the cherry on the cake is that this column's value is always the INVERSE of what it should be. So if Active = 0 then the item is currently active, but if Active = 1 then it's inactive. Do you know how much fun it is to debug that shit at 4:30 in the morning?

    Other WTFs with this DB include: almost every column in every table is nullable, even columns that are required by the apps; table names beginning with _ to display first in the list of tables; table names beginning with z or zz or zzz to appear last in the list; tables that have been "backed up" by being recreated with _2, _arch, _Backup, _New, and _SS appended to the new names; the primary key column on almost all tables being a varchar; no foreign keys at all, allowing all sort of crap data to be inserted...



  • @The_Assimilator said:

    Ah yes, you can always tell a quality DB design by uppercase column names, and boolean columns that are typed as char(1) or tinyint.

    Sadly, either of those is the recommended way to do booleans in Firebird, as there is no boolean. I suppose you could do decimal(1,0)?

     

    Edit: it's actually smallint, not tinyint ....



  • @The_Assimilator said:

    But that can't beat the legacy system I'm currently working on.

    Does it count if my hell was just created by the software vendor and is in beta right now? I could understand if it was 1999, but there's no excuse for purposely designing something like this in 2010.



  • @rad131304 said:

    [quote user="The_Assimilator"]Ah yes, you can always tell a quality DB design by uppercase column names, and boolean columns that are typed as char(1) or tinyint.

    Sadly, either of those is the recommended way to do booleans in Firebird, as there is no boolean. I suppose you could do decimal(1,0)?

     

    Edit: it's actually smallint, not tinyint ....

    [/quote]

    Jesus H. Christ, Firebird isn't primitive, it's fucking single-celled.

    It doesn't have a bit datatype, nor money/smallmoney, nor uniqueidentifier. It doesn't have cursors, which have been a mainstay of non-toy RDBMSs for over a decade. (Yes, I know cursors are abused. Anyone who wishes to point out this enlightening fact can get fucked. There is a time and a place for everything, even cursors.)

    And then there's the "Converting the identity data type" section which claims "There are many ways to perform the conversion. In general, Firebird is more flexible and powerful in this respect" and then advises you to create a trigger or sproc to emulate identities. Only a knuckle-dragging idiot (or, presumably, a Firebird team member) could think this is "more flexible and powerful". It seems that the open-source mentality of "if it makes people do more work it must be better" is sadly, still alive and well.

    Muddy hell, at this rate SQLite will be more feature-complete than Firebird in a couple of years. Mozilla should have told Firebird to get fucked and kept the name for their browser; at least their product is less of a piece of shit than this so-called RDBMS.

    And you have my deepest sympathy for having to work with something as shitty as Firebird.



  • @The_Assimilator said:

    Jesus H. Christ, Firebird isn't primitive, it's fucking single-celled.

    Criminy. SQL Server is adding in geo-location types, a DateTime field with a resolution of like 0.1 picoseconds or whatever, and these Firdbird guys are sitting on their thumbs going... deeer... currency type?

    I feel better about MySQL now. Slightly. Very slightly.


  • Garbage Person

    @blakeyrat said:

    It *still* can't reliably talk to Gmail's IMAP servers (and yes, that's possibly/probably Gmail's fault, but that doesn't change the fact that I need the feature to work and it doesn't). Oh, and when it fails to talk to Gmail's IMAP servers, it crashes most of the time
    Oddly enough, Gmail's POP servers run mostly like IMAP servers. The normal delete-from-server pop action gets ignored.



  • @b-redeker said:

    @RTapeLoadingError said:

    One day I got a mail from the other students, which I read and then immediately afterwards one to the entire department. I hit RA and sent the message "I've just accidentally deleted 12,000 documents from production.  Oh well, easy come easy go."  I then heard the beeps as this mail was delivered to the department.
     

    So that was you!!

    Seriously, did you manage get out of that mess unharmed?

     

    Yep - that was me.

    I got out unscathed due to having bosses that were pretty cool and who accepted my explanation of what I had done.  As I was the only person involved in performing the unpack > upload > delete process I only really impacted myself.  The users were aware that the archives were being imported over time but lived with the fact that it wasn't going to happen in one fell swoop.

    My boss did make the point that I should be a bit more careful about what I send as an email and to this day (15 years later) I always make sure that the intended recipients are the actual recipients before sending a mail.



  • @blakeyrat said:

    [[T]hese Firdbird guys are sitting on their thumbs going... deeer... currency type?
    MS SQL doesn't have currency either, it has (small)money, which is basically an alias for DECIMAL(), which Firebird supports.



  • @Lingerance said:

    @blakeyrat said:
    [[T]hese Firdbird guys are sitting on their thumbs going... deeer... currency type?
    MS SQL doesn't have currency either, it has (small)money, which is basically an alias for DECIMAL(), which Firebird supports.

    The Pedant Squad attacks!

    On another note, this website seems apropos.



  • @blakeyrat said:

    The Pedant Squad attacks!
    Meh, I was expecting one of two responses to that. 1) You'd confirm what you meant by a currency type, if it was indeed the money types it's kind of a pointless thing to care about. 2) You'd confirm that the currency type was actually something awesomer than just a plain decimal field and it'd store the currency with the amount and when it'd return you'd get the correct precision for the type. Eg: 4500 for Yen, 45.00 for dollars. At which point you'd actually have something to brag about.



  • @Lingerance said:

    @blakeyrat said:
    The Pedant Squad attacks!
    Meh, I was expecting one of two responses to that. 1) You'd confirm what you meant by a currency type, if it was indeed the money types it's kind of a pointless thing to care about. 2) You'd confirm that the currency type was actually something awesomer than just a plain decimal field and it'd store the currency with the amount and when it'd return you'd get the correct precision for the type. Eg: 4500 for Yen, 45.00 for dollars. At which point you'd actually have something to brag about.

    Or maybe I was just making a quick and dirty statement to communicate that I now appreciate MS SQL more than I did before, knowing how crappy some of the competition is. And maybe I didn't expect to be jumped on by a pedant who:

    1) Utterly ignores the *point* of my post in favor of criticizing one particular example, as if that example (even if proven wrong) invalidates the point of the post. You know when you stop focusing on all those trees, maybe you'll notice the forest. I want people to respond to the point I was getting at, not getting sidetracked by trivial fucking details and forgetting to step back and go, "hm, this trivial detail aside, what did that post really say..."

    2) Expects me, apparently, to have near-omniscient knowledge of the product I'm talking about, even if it's a feature I don't use. Look: I assumed the Money type does money-related things. Maybe it rounds the way money is supposed to round. Maybe it converts currencies based on locale settings, as you imply. Maybe it does double-entry accounting every time you insert a value. I do not fucking know what it does. I don't use it.

    Hey: guess what? DateTime2 probably also doesn't fucking track to a precision of 0.1 picoseconds or whatever bullshit value I made up. QUICK! POINT OUT MY ERROR!



  • @blakeyrat said:

    Or maybe I was just making a quick and dirty statement to communicate that I now appreciate MS SQL more than I did before, knowing how crappy some of the competition is.
    So? A money data type isn't overly useful as there's a decimal/number type which does the same thing. Yes the other two types sound useful (assuming you can do range searches like "near X by Y KM"), but as you said they aren't there yet so I didn't bother looking for them, at which point Firebird doesn't look overly bad.



  • @Lingerance said:

    @blakeyrat said:
    Or maybe I was just making a quick and dirty statement to communicate that I now appreciate MS SQL more than I did before, knowing how crappy some of the competition is.
    So? A money data type isn't overly useful as there's a decimal/number type which does the same thing. Yes the other two types sound useful (assuming you can do range searches like "near X by Y KM"), but as you said they aren't there yet so I didn't bother looking for them, at which point Firebird doesn't look overly bad.

    With the disclaimer that I don't fucking use it, the money value would be super-handy if you, for example, want to insert: "$5.24" into the database. Presuming that it can parse that, which I am only assuming because disclaimer: I don't fucking use it.

    Oh hey look, there you fucking go: http://msdn.microsoft.com/en-us/library/ms188688.aspx So no, Money is not simply an alias to decimal(12,4) or whatever. It actually has *gasp* money-related functionality. The example still doesn't matter, but it feels good being right anyway.

    @Lingerance said:

    Yes the other two types sound useful (assuming you can do range searches like "near X by Y KM"), but as you said they aren't there yet so I didn't bother looking for them,

    They are there in SQL Server 2008, they're just not listed on that webpage for some reason. Possibly because they're custom types and not defined by whatever standard T-SQL is written around? I dunno; but they're listed on the features page: http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

    And now, by actually researching your stupid criticisms to my stupid post, I have lost. Completely. You win. You've completely buried the meaning of my original post in your pedantic bullshit.



  • @blakeyrat said:

    Oh hey look, there you fucking go: http://msdn.microsoft.com/en-us/library/ms188688.aspx So no, Money is not simply an alias to decimal(12,4) or whatever. It actually has gasp money-related functionality.

    @http://msdn.microsoft.com/en-us/library/ms188688.aspx said:
    It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.
    Alright, so it's decimal/numeric field that will ignore certain leading non-numeric-non-period values. How unexceedingly useful.
    @blakeyrat said:
    And now, by actually researching your stupid criticisms to my stupid post, I have lost. Completely. You win. You've completely buried the meaning of my original post in your pedantic bullshit.

    Again, wasn't my intent.



  • @Lingerance said:

    Filed under: All I wanted to see was a monetary field that would display the correct amount of decimal places. If MONEY actually does that their documentation page doesn't show that at all and makes statements that indicates that it doesn't.

    How could it? That depends on rounding rules that aren't uniform across all money-tracking options. If you tell SQL what rounding rules to use, then tell it to show 2 decimal, it'll be happy to do that for you. But until it knows what type of rounding you want, it can't possibly display the value accurately to 2 decimal places, nor could any other DB server ever.

    Now, maybe it would be a good idea for every financial institution/product to standardize the rounding rules, but ... MS SQL isn't built for hypothetical best-case worlds, it's built for planet Earth.



  • @blakeyrat said:

    How could it? That depends on rounding rules that aren't uniform across all money-tracking options. If you tell SQL what rounding rules to use, then tell it to show 2 decimal, it'll be happy to do that for you. But until it knows what type of rounding you want, it can't possibly display the value accurately to 2 decimal places, nor could any other DB server ever.
    Column, table, session and/or server option, which is how MySQL handles cases where it has to know how to handle certain data-types (specifically encoding of string-type columns).



  • @Lingerance said:

    @blakeyrat said:
    How could it? That depends on rounding rules that aren't uniform across all money-tracking options. If you tell SQL what rounding rules to use, then tell it to show 2 decimal, it'll be happy to do that for you. But until it knows what type of rounding you want, it can't possibly display the value accurately to 2 decimal places, nor could any other DB server ever.
    Column, table, session and/or server option, which is how MySQL handles cases where it has to know how to handle certain data-types (specifically encoding of string-type columns).

    Oh shit, I forgot the disclaimer!!!

    I don't actually use the Money types in MS SQL. For all I know, those exist in MS SQL as well. Or maybe they're vastly superior. Or maybe they don't exist at all. I don't know because I don't use them.

    I don't actually use the Money types in MS SQL.

    I don't actually use the Money types in MS SQL.

    I don't actually use the Money types in MS SQL.

    There, that should cover the next few posts if I forget the disclaimer again. Christ.



  • @blakeyrat said:

    Oh shit, I forgot the disclaimer!!!
    Uhh what? I was under the impression you asked a hypothetical question, so I answered in what I thought would be the most sane way to actually implement that.



  • You two have some serious communication issues.

     

    I think blakey's ladyfriend may have broken up with him a few days ago.



  • @dhromed said:

    You two have some serious communication issues.

     

    I think blakey's ladyfriend may have broken up with him a few days ago.

     

Log in to reply