Coding Horrors: You can gather all your technical debt into one easy framework!

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Coding Horrors: You can gather all your technical debt into one easy framework!

«‹›1503 »

redleader: Aug 18, 2005; Engage according to operational parameters

NtotheTC posted:

Gotta admit I laughed hard at the part where will@nomx claimed to be sending emails but they weren't getting though. It's entirely possible he was just using the product.

https://twitter.com/Scott_Helme/status/857617936902754304

# ? Apr 28, 2017 02:28

Adbot: ADBOT LOVES YOU

# ? May 30, 2024 10:11

Dr. Stab: Sep 12, 2010; 👨🏻‍⚕️🩺🔪🙀😱🙀

redleader posted:

https://twitter.com/Scott_Helme/status/857617936902754304

Is there an app you can get that makes your battery appear dangerously low in screenshots?

# ? Apr 28, 2017 02:51

Ranzear: Jul 25, 2013

Dr. Stab posted:

Is there an app you can get that makes your battery appear dangerously low in screenshots?

It's 4pm, why wouldn't his battery be low?

Are batteries never supposed to be actually used, like ram in linux?

# ? Apr 28, 2017 03:00

kitten emergency: Jan 13, 2008; get meow this wack-ass crystal prison

Dr. Stab posted:

Is there an app you can get that makes your battery appear dangerously low in screenshots?

it's called android

# ? Apr 28, 2017 13:42

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

i;m going to kill myself

code:

SQL> SELECT CASE WHEN 'Oracle' NOT IN ('can eat', 'my rear end') THEN 0 ELSE 1 END AS gently caress FROM DUAL;

      gently caress
----------
         0

SQL> SELECT CASE WHEN 'Oracle' NOT IN ('', 'my rear end') THEN 0 ELSE 1 END AS gently caress FROM DUAL;

      gently caress
----------
         1

# ? Apr 28, 2017 15:37

nielsm: Jun 1, 2009

Why but of course the empty string is NULL because it's very convenient

# ? Apr 28, 2017 15:45

dwazegek: Feb 11, 2005; WE CAN USE THIS

What's causing that? Is it because the empty string is treated as null, and all comparisons to null are treated as false? That's backwards as gently caress :psyduck:

# ? Apr 28, 2017 15:55

LOOK I AM A TURTLE: May 22, 2003; "I'm actually a tortoise."; Grimey Drawer

dwazegek posted:

What's causing that? Is it because the empty string is treated as null, and all comparisons to null are treated as false? That's backwards as gently caress

Yes. You always have to use x IS NULL to check for nullness. This particular scenario is definitely really lovely behavior, but I believe it springs from a consistent logical derivation given the axiom that NULL != NULL (and '' != '', '' != NULL, etc.). I don't have an Oracle DB in front of me to investigate the details so I may be somewhat off, but here goes.

pre:

P = x NOT IN ('', 'a')
  = NOT (x IN ('', 'a'))           # a∉b ≡ �(a∈b)
  = NOT (x = '' OR x = 'a')        # a∈(b,c) ≡ a=b ∨ a=c
  = NOT (x = '') AND NOT (x = 'a') # �(a ∨ b) ≡ �a ∧ �b
  = x != '' AND x != 'a'           # �(a=b) ≡ a≠b
  = 0 AND 1                        # a≠b ≡ F if either a or b is null (or even both)
  = 0

# ? Apr 28, 2017 17:08

SupSuper: Apr 8, 2009; At the Heart of the city is an Alien horror, so vile and so powerful that not even death can claim it.

dwazegek posted:

What's causing that? Is it because the empty string is treated as null, and all comparisons to null are treated as false? That's backwards as gently caress

Technically, all comparisons to null (besides IS NULL) evaluate to UNKNOWN, which is subtly different from FALSE, because Oracle has tri-state bools. :eng101:

# ? Apr 28, 2017 17:46

M31: Jun 12, 2012

Since NULL doesn't get stored in indexes it also breaks UNIQUE indexes (you can put in an unlimited amount of empty strings)

# ? Apr 28, 2017 19:14

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Nullos non patieris vivere. There are very, very few reasons to allow nullable columns in your schema to begin with. You'll live much happier if you can assume NULLs only come from unmatched outer joins.

# ? Apr 28, 2017 19:28

nielsm: Jun 1, 2009

NihilCredo posted:

Nullos non patieris vivere. There are very, very few reasons to allow nullable columns in your schema to begin with. You'll live much happier if you can assume NULLs only come from unmatched outer joins.

But then you can't store empty strings?!

# ? Apr 28, 2017 19:34

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

nielsm posted:

But then you can't store empty strings?!

Sorry, I missed Hammerite's original post. Yeah, if Oracle treats '' as NULL it's a massive stab in the back of its users.

I'd honestly consider figuring out a way to automatically add a space or some non-printable character to every empty string before saving it into an Oracle DB, it would still be a coding horror but probably one that's easier to deal with.

# ? Apr 28, 2017 19:45

csammis: Aug 26, 2003; Mental Institution

NihilCredo posted:

Nullos non patieris vivere. There are very, very few reasons to allow nullable columns in your schema to begin with. You'll live much happier if you can assume NULLs only come from unmatched outer joins.

The only use case I could ever think of in which nullable columns were even a little okay was a "modified date" where the business case stated that if a record had never been modified the UI should be presented as blank. It was nicer to allow that value to be NULL rather than have defined a sentinel value or introduced display logic in which "if modified date == created date then modified date is blank"

But yes even this could have been solved by evolving the schema into an even more normal form :c00l:

# ? Apr 28, 2017 19:48

The MUMPSorceress: Jan 6, 2012; ^SHTPSTS; Gary’s Answer

lol, if oracle treats "" as null then they are in the prestigious company of world-leading innovative database platform mumps.

# ? Apr 28, 2017 20:17

VikingofRock: Aug 24, 2008

LOOK I AM A TURTLE posted:

Yes. You always have to use x IS NULL to check for nullness. This particular scenario is definitely really lovely behavior, but I believe it springs from a consistent logical derivation given the axiom that NULL != NULL (and '' != '', '' != NULL, etc.). I don't have an Oracle DB in front of me to investigate the details so I may be somewhat off, but here goes.
pre:
P = x NOT IN ('', 'a')
  = NOT (x IN ('', 'a'))           # a∉b ≡ ¬(a∈b)
  = NOT (x = '' OR x = 'a')        # a∈(b,c) ≡ a=b ∨ a=c
  = NOT (x = '') AND NOT (x = 'a') # ¬(a ∨ b) ≡ ¬a ∧ ¬b
  = x != '' AND x != 'a'           # ¬(a=b) ≡ a≠b
  = 0 AND 1                        # a≠b ≡ F if either a or b is null (or even both)
  = 0

So I might be having a brainfart, but why would (x != '') be 0 here? Shouldn't that be true for every x, including x = NULL?

# ? Apr 28, 2017 20:48

Dylan16807: May 12, 2010

csammis posted:

The only use case I could ever think of in which nullable columns were even a little okay was a "modified date" where the business case stated that if a record had never been modified the UI should be presented as blank. It was nicer to allow that value to be NULL rather than have defined a sentinel value or introduced display logic in which "if modified date == created date then modified date is blank"

But yes even this could have been solved by evolving the schema into an even more normal form

You need special display logic either way so even in that scenario using NULL instead of 0 doesn't buy you much.

# ? Apr 28, 2017 20:47

dwazegek: Feb 11, 2005; WE CAN USE THIS

VikingofRock posted:

So I might be having a brainfart, but why would (x != '') be 0 here? Shouldn't that be true for every x, including x = NULL?

Every comparison with null results in false (or, more accurately, UNKNOWN, which ultimately results in false).

This include equality, inequality, greater/lesser then, etc.

So if b is null, then a = b results in false, but a != b also results in false, which is kinda hosed up as a is not b, but a is also not not b.

It makes more sense if you consider it as unknown. B is null (so its value is unknown), so it's also unknown if a = b, or if a != b.

# ? Apr 28, 2017 21:20

vOv: Feb 8, 2014

I don't know anything about DBA work. If you were storing a database of people with birth and death dates, how would you represent the death dates for living people without using NULL?

# ? Apr 28, 2017 21:25

leper khan: Dec 28, 2010; Honest to god thinks Half Life 2 is a bad game. But at least he likes Monster Hunter.

vOv posted:

I don't know anything about DBA work. If you were storing a database of people with birth and death dates, how would you represent the death dates for living people without using NULL?

Date of death < date of birth?

# ? Apr 28, 2017 21:33

Spatial: Nov 15, 2007

A proper normalised database wouldn't have a single table. There would be a separate death table with a person ID and a death date only for people who had actually died.

# ? Apr 28, 2017 21:34

nielsm: Jun 1, 2009

vOv posted:

I don't know anything about DBA work. If you were storing a database of people with birth and death dates, how would you represent the death dates for living people without using NULL?

CREATE TABLE person_deaths (
person_id INTEGER PRIMARY KEY REFERENCES persons.id,
death_date DATE,
death_cause CHAR(20)
);

E:f;b

# ? Apr 28, 2017 21:37

RandomBlue: Dec 30, 2012; hay guys!; Biscuit Hider

dwazegek posted:

Every comparison with null results in false (or, more accurately, UNKNOWN, which ultimately results in false).

This include equality, inequality, greater/lesser then, etc.

So if b is null, then a = b results in false, but a != b also results in false, which is kinda hosed up as a is not b, but a is also not not b.

It makes more sense if you consider it as unknown. B is null (so its value is unknown), so it's also unknown if a = b, or if a != b.

I'm about 95% sure the only reason we have to put up with this bullshit in DBs is because there was some pedantic rear end in a top hat that wouldn't shut up about "well actually you see, NULL can't equal NULL because..." and now we have to use a special operator whenever we want to check for NULL and DBs noobs will continue to gently caress up their queries when nullable columns exist.

# ? Apr 28, 2017 21:40

Soricidus: Oct 21, 2010; freedom-hating statist shill

nielsm posted:

CREATE TABLE person_deaths (
person_id INTEGER PRIMARY KEY REFERENCES persons.id,
death_date DATE,
death_cause CHAR(20)
);

E:f;b

I'm the CHAR(20)

# ? Apr 28, 2017 21:41

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

VikingofRock posted:

So I might be having a brainfart, but why would (x != '') be 0 here? Shouldn't that be true for every x, including x = NULL?

no, Oracle is at least correct about this. NULL = NULL and NULL <> NULL are both unknown (but not false as LOOK I AM A TURTLE implies, although for the purposes of evaluation as an IF-condition there is no functional difference between unknown and false).

# ? Apr 28, 2017 21:42

RandomBlue: Dec 30, 2012; hay guys!; Biscuit Hider

Spatial posted:

A proper normalised database wouldn't have a single table. There would be a separate death table with a person ID and a death date only for people who had actually died.

I seriously hope you're joking. Hard to tell when it comes to people talking about DB design.

I once worked for a company where all DB changes had to be approved by DBAs even with new development and the DBA we had to deal with would absolutely NOT use surrogate keys. We had tables that had 16 column primary keys that could be up to ~350 bytes IIRC. Writing joins was so god drat painful and everything was super slow because that's a massively stupid way to design things.

I went to the DBA begging to use a sequence for the keys instead and she flatly refused. When I asked her why the only reason she would give me was "My 8 years of DBA experience." I was so mad I was shaking. Pretty sure I'd sent her a couple of articles explaining why surrogate keys were better in most cases, but nope.

That project was around 2000 and processed millions of rows of data and it took 8-12 hours to run the full process entirely because of the horribly lovely DB design. I left when my contract was over after 6 months even though they wanted me to extend, gently caress that poo poo. If you know anything about DB design, never ever take a job where another team will have to approve all DB changes in dev.

# ? Apr 28, 2017 21:48

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

RandomBlue posted:

We had tables that had 16 column primary keys that could be up to ~350 bytes IIRC. Writing joins was so god drat painful and everything was super slow because that's a massively stupid way to design things.

That's nuts. Were you not even allowed to write views to hide that nonsense?

# ? Apr 28, 2017 22:04

redleader: Aug 18, 2005; Engage according to operational parameters

NULL is fine. It doesn't take long to understand its behaviour, and I'd much rather deal with a couple of nullable columns than join on another couple of tables.

The horrors are Oracle's '' === NULL, and schemas where basically every column except the primary key allows nulls.

# ? Apr 28, 2017 22:23

Spatial: Nov 15, 2007

RandomBlue posted:

I seriously hope you're joking. Hard to tell when it comes to people talking about DB design.

No, but this isn't something I do professionally or at scale. What's wrong with it?

# ? Apr 28, 2017 23:19

wide stance: Jan 28, 2011; If there's more than one way to do a job, and one of those ways will result in disaster, then he will do it that way.

RandomBlue posted:

I seriously hope you're joking. Hard to tell when it comes to people talking about DB design.

I once worked for a company where all DB changes had to be approved by DBAs even with new development and the DBA we had to deal with would absolutely NOT use surrogate keys. We had tables that had 16 column primary keys that could be up to ~350 bytes IIRC. Writing joins was so god drat painful and everything was super slow because that's a massively stupid way to design things.

I went to the DBA begging to use a sequence for the keys instead and she flatly refused. When I asked her why the only reason she would give me was "My 8 years of DBA experience." I was so mad I was shaking. Pretty sure I'd sent her a couple of articles explaining why surrogate keys were better in most cases, but nope.

That project was around 2000 and processed millions of rows of data and it took 8-12 hours to run the full process entirely because of the horribly lovely DB design. I left when my contract was over after 6 months even though they wanted me to extend, gently caress that poo poo. If you know anything about DB design, never ever take a job where another team will have to approve all DB changes in dev.

That's downright criminal. Did anyone question why should it take 8-12 hours for that?

# ? Apr 28, 2017 23:45

csammis: Aug 26, 2003; Mental Institution

Spatial posted:

No, but this isn't something I do professionally or at scale. What's wrong with it?

Let's say when a user accesses a Person record they always want to know whether or not they're dead, so every retrieval of a Person from the database requires a JOIN to Person_Death. That means the Person_Death table is as hot as Person, which means space for another table on what may be an expensive storage medium...but a DBA maintaining this schema might not necessarily realize that and put Person_Death on a slower but cheaper storage. Whoops, now all Person retrieval is only as fast as the Person_Death storage!

Getting a more into long-term database design, you may want your Person table to be partitioned by whether or not a person is alive or dead. It might make sense for records corresponding to living people to be on a faster storage medium and dead people can be on nearline storage - accessible, but slower. A DBMS can manage table partitioning by a date column but that date column needs to exist on that table*. "Pfft, just keep a flag on the Person table indicating whether they've died and partition by that"...okay, but now you've violated a ~perfectly normal~ form and you still require a JOIN to get the information that a human probably wants anyway.

"Properly normalized" databases are great in theory. In practice you make a schema that makes sense for your use cases. Normalization and denormalization both have their place in the world.

Dylan16807 posted:

You need special display logic either way so even in that scenario using NULL instead of 0 doesn't buy you much.

I'm not a UI guy at all so don't hold me to this, but shouldn't data binding to an unset field just work? Requiring a client side data binding to understand that a certain sentinel value means "no date" sounds like a horror to me.

* edit: I'm like 98% positive about this. A DBMS might be able to manage table partitioning by information that's not actually on that table but if so that's awfully strange and almost certainly less performant. Oracle probably touts it as a flagship feature :smith:

csammis fucked around with this message at 00:07 on Apr 29, 2017

# ? Apr 29, 2017 00:02

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

I'm pretty sure you can only partition on real columns on the table itself, even with clusters or virtual columns. Partitioning on a state that can change is a recipe for extra IO though, although given that people don't come back to life it wouldn't be too serious there.

Also, death_cause CHAR(20)?? Surely it should be a foreign key to a death_causes table where you can have details if you care to look at them?

# ? Apr 29, 2017 00:37

darthbob88: Oct 13, 2011; YOSPOS

Moonwolf posted:

I'm pretty sure you can only partition on real columns on the table itself, even with clusters or virtual columns. Partitioning on a state that can change is a recipe for extra IO though, although given that people don't come back to life it wouldn't be too serious there.

Also, death_cause CHAR(20)?? Surely it should be a foreign key to a death_causes table where you can have details if you care to look at them?

Especially when there's already an exhaustive list of possible injuries that can cause death.

# ? Apr 29, 2017 01:02

TooMuchAbstraction: Oct 14, 2012; I spent four years making
Waves of Steel
Hell yes I'm going to turn my avatar into an ad for it.; Fun Shoe

darthbob88 posted:

Especially when there's already an exhaustive list of possible injuries that can cause death.

Looking at those codes, they should easily fit into a CHAR(20) column. I don't see the problem. :v:

# ? Apr 29, 2017 01:10

redleader: Aug 18, 2005; Engage according to operational parameters

It's also a pain to query if it's a frequently-used field. I find writing join after join to be horribly irritating and tedious. Yeah, you could create a view, but then you're back to having columns with NULLs.

# ? Apr 29, 2017 01:12

RandomBlue: Dec 30, 2012; hay guys!; Biscuit Hider

Hammerite posted:

That's nuts. Were you not even allowed to write views to hide that nonsense?

I don't recall, probably, but there's only so much you can do when the DB seems to be actively designed to run as slow as possible. Once you've made sure all your queries are using the right indexes there's not a whole lot else you could do in that case. I briefly considered rewriting parts of it with a local DB to demonstrate how much it was killing our performance but that would've been a lot of work and I still would've had to deal with the politics, so I said FI and left when my contract was up.

Spatial posted:

No, but this isn't something I do professionally or at scale. What's wrong with it?

This was already answered, but in most cases if there's a one to one relationship between two tables they should be the same table. Yes, there are exceptions but they're rare, if you carry that type of design to it's extreme conclusion every column has it's own table. This link explains it further but basically it's what we've said: http://www.databaseprimer.com/pages/relationship_1to1/. You're adding complexity and performance overhead for no real benefit.

It's been a while since I had to think about or answer DB design questions and not just doing the design but I believe third normal form is the typical target, beyond that it can be more harm than good, here's a decent article about it: http://agiledata.org/essays/dataNormalization.html

Sometimes you want to go the other way and denormalize for performance reasons but you should make sure that's absolutely necessary before that as you'll probably have to defend that choice at some point.

wide stance posted:

That's downright criminal. Did anyone question why should it take 8-12 hours for that?

Sure, the people on my team. The answer was the keys but we had no power to do anything and everyone involved was a contractor and none of us were going to step on the political wasps nest of escalating this issue with a long-time direct employee. The other reason is it was millions of rows with heavy processing in 2000.

# ? Apr 29, 2017 01:33

Bruegels Fuckbooks: Sep 14, 2004; Now, listen - I know the two of you are very different from each other in a lot of ways, but you have to understand that as far as Grandpa's concerned, you're both pieces of shit! Yeah. I can prove it mathematically.

RandomBlue posted:

Sure, the people on my team. The answer was the keys but we had no power to do anything and everyone involved was a contractor and none of us were going to step on the political wasps nest of escalating this issue with a long-time direct employee. The other reason is it was millions of rows with heavy processing in 2000.

It's bullshit like this that encouraged a lot of people at hosed up companies to start experimenting with NoSQL solutions just to avoid the db teams.

# ? Apr 29, 2017 06:07

Dylan16807: May 12, 2010

csammis posted:

I'm not a UI guy at all so don't hold me to this, but shouldn't data binding to an unset field just work? Requiring a client side data binding to understand that a certain sentinel value means "no date" sounds like a horror to me.

"Just work" how? Do you want a blank space? How big of a blank space? Do you want a description saying that there is no date? What happens when the code tries to extract the year? What happens when the code tries to put a null date into a standard date type?

You need a bunch of code to handle the "no date" case no matter how it's stored in the database.

# ? Apr 29, 2017 06:07

RandomBlue: Dec 30, 2012; hay guys!; Biscuit Hider

Bruegels Fuckbooks posted:

It's bullshit like this that encouraged a lot of people at hosed up companies to start experimenting with NoSQL solutions just to avoid the db teams.

As a long time developer (that almost always involves databases) who's held DBA positions, NoSQL disgusts me on general principal. So does LINQ, both styles, it's harder to work with compared with SQL and there are lots of issues when trying to implement complex queries vs. just doing it in SQL. Sure, it's easier for basic CRUD but most things beyond basic data entry require more than flat CRUD operations.

Also I'm old now and change is bullshit.

# ? Apr 29, 2017 07:29

Adbot: ADBOT LOVES YOU

# ? May 30, 2024 10:11

QuarkJets: Sep 8, 2008

csammis posted:

Let's say when a user accesses a Person record they always want to know whether or not they're dead, so every retrieval of a Person from the database requires a JOIN to Person_Death. That means the Person_Death table is as hot as Person, which means space for another table on what may be an expensive storage medium...but a DBA maintaining this schema might not necessarily realize that and put Person_Death on a slower but cheaper storage. Whoops, now all Person retrieval is only as fast as the Person_Death storage!

Getting a more into long-term database design, you may want your Person table to be partitioned by whether or not a person is alive or dead. It might make sense for records corresponding to living people to be on a faster storage medium and dead people can be on nearline storage - accessible, but slower. A DBMS can manage table partitioning by a date column but that date column needs to exist on that table*. "Pfft, just keep a flag on the Person table indicating whether they've died and partition by that"...okay, but now you've violated a ~perfectly normal~ form and you still require a JOIN to get the information that a human probably wants anyway.

"Properly normalized" databases are great in theory. In practice you make a schema that makes sense for your use cases. Normalization and denormalization both have their place in the world.

I'm not a UI guy at all so don't hold me to this, but shouldn't data binding to an unset field just work? Requiring a client side data binding to understand that a certain sentinel value means "no date" sounds like a horror to me.

* edit: I'm like 98% positive about this. A DBMS might be able to manage table partitioning by information that's not actually on that table but if so that's awfully strange and almost certainly less performant. Oracle probably touts it as a flagship feature

what about having a death_id in the persons table, which is NULL if the person is alive?

# ? Apr 29, 2017 08:44

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Coding Horrors: You can gather all your technical debt into one easy framework!

«‹›1503 »