[GeneX-dev] Re: Big Scratch table float => int trouble

[<ja...@op...>]

Hey Harry,

Here's the technical email.

I think your idea is fine. Once the data is in ints shuffling it
around is no trouble.

The problem is getting how to properly get a float into a DB column
that only stores ints - not rocket science, I know, but still an
issue. There is a first loading step that takes place in which the
numbers are floats, and we have to convert them to ints. We can move
back that moment as far back in the process as loading the initial MBA
data, but at some point we have to take a float and store it as an int.

I was hoping that we could use DB triggers to do the black magic for
us. That way the users would all think the DB stored floats - they
entered floats into the table, and when they queried the table they
got floats back. What could be easier?

But, that didn't work. By the time the trigger sees the floating point
number it's already been truncated and the data is lost. So we must
use a different approach. 

Since we're going to have *all* the data float columns stored as ints,
we only need to do the conversion when the data is loaded into the DB
- this simplifies everything - so it makes sense for the data loader
to do the conversion when reading the data in.

So loading data in is not such a big deal. What about exporting data?
Should we provide a mechanism that users can get back their floats? Or
are they going to be satisfied with doing everything as ints? We don't
have to spend lots of time on it now, but I think there should be a
simply mechanism - such as setting some global flag - that enables a
user to toggle whether the data matrix comes out as ints or the
original floats.

So, to recap, we will store all data matrix values as ints, and the
data will be returned to the users as ints. In the near future we will
think about some way in which the data can be converted to floats for
export. 

Cheers,
jas.