bigint in mysql

The object ID of an celestial object in the Sloan Digital Sky Survey is
described as a 64 bit integer. If I want to make my own mysql database
for a few items I've downloaded (just for my own education), I can preumably
declare the column for that object ID a bigint. This object ID actually
encodes 6 other items, called skyVersion, field, run, rerun, camcol, obj,
which can be extracted from the object ID by partitioning the 64 bit binary
representation of the object ID.

The documentation for mysql leaves me a little puzzled as to how to
declare the object ID. I'm not even sure whether it really supports 64
bit unsigned integers or only 63 bits. There are some comments about
giving wrong answers at the most significant bits. It also says something
about declaring the bigint as a string and mysql being smart enough to
automatically convert it to an integer. It also says something about
arithmetic with these bigints being treated using doubles, i.e. floating
point, which it is the purpose of the string version to avoid.

Question 1: How do I declare the object ID (e.g. calling it objID) as a 64
bit integer in mysql?

Question 2: How should I extract the various items skyVersion, field, run,
rerun, camcol, obj from objID? I could do it, for example, by first dividing
objID by 2^16 to get obj as a remainder, dividing the quotient by something
else to get the next field as the remainder, dividing the new quotient
and so on. Would that get me involved in the floating point stuff that
one is trying to avoid? In any case, is there a mysql command that will
simply partition a 64 bit binary int into specified pieces?

Ignorantly,
Allan Adler


************************************************** **************************
* *
* Disclaimer: I am a guest and *not* a member of the MIT Artificial *
* Intelligence Lab. My actions and comments do not reflect *
* in any way on MIT. Moreover, I am nowhere near the Boston *
* metropolitan area. *
* *
************************************************** **************************

Hi Allan

I do loads of MySQL at work. I i am ever unsure about a column
definition or data type i go for varchar(n) where n=how many chars
you want to a max of 255.

A simpler way to resolve the problem would be to add columns for
skyVersion, bla,bla,bla then split the objectID up before it goes into
the table.

Far less aggro


--
________________________
http://robert.thegeakes.co.uk/
51:21N 00:23E


"Allan Adler" wrote in message
...

The object ID of an celestial object in the Sloan Digital Sky Survey is
described as a 64 bit integer. If I want to make my own mysql database
for a few items I've downloaded (just for my own education), I can
preumably
declare the column for that object ID a bigint. This object ID actually
encodes 6 other items, called skyVersion, field, run, rerun, camcol, obj,
which can be extracted from the object ID by partitioning the 64 bit
binary
representation of the object ID.

The documentation for mysql leaves me a little puzzled as to how to
declare the object ID. I'm not even sure whether it really supports 64
bit unsigned integers or only 63 bits. There are some comments about
giving wrong answers at the most significant bits. It also says something
about declaring the bigint as a string and mysql being smart enough to
automatically convert it to an integer. It also says something about
arithmetic with these bigints being treated using doubles, i.e. floating
point, which it is the purpose of the string version to avoid.

Question 1: How do I declare the object ID (e.g. calling it objID) as a 64
bit integer in mysql?

Question 2: How should I extract the various items skyVersion, field, run,
rerun, camcol, obj from objID? I could do it, for example, by first
dividing
objID by 2^16 to get obj as a remainder, dividing the quotient by
something
else to get the next field as the remainder, dividing the new quotient
and so on. Would that get me involved in the floating point stuff that
one is trying to avoid? In any case, is there a mysql command that will
simply partition a 64 bit binary int into specified pieces?

Ignorantly,
Allan Adler



************************************************** **************************
*
*
* Disclaimer: I am a guest and *not* a member of the MIT Artificial
*
* Intelligence Lab. My actions and comments do not reflect
*
* in any way on MIT. Moreover, I am nowhere near the Boston
*
* metropolitan area.
*
*
*

************************************************** **************************

On Sat, 28 Jun 2003 11:24:00 +0000, Allan Adler wrote:

The documentation for mysql leaves me a little puzzled as to how to
declare the object ID. I'm not even sure whether it really supports 64
bit unsigned integers or only 63 bits. There are some comments about
giving wrong answers at the most significant bits. It also says something
about declaring the bigint as a string and mysql being smart enough to
automatically convert it to an integer. It also says something about
arithmetic with these bigints being treated using doubles, i.e. floating
point, which it is the purpose of the string version to avoid.

Yeah. I'd use an unsigned bigint for this column. Don't do any arithmetic
except for bit shifting kids of things. That's the kind of math you'd be
doing anyway in order to get at certain bits in that ID.


Question 1: How do I declare the object ID (e.g. calling it objID) as a 64
bit integer in mysql?

Something like

create table FooTable (
ObjID unsigned bigint not null default 0
);

ought to define that column in a table. IS that what you're looking for?
You'll probably want to put more stuff into that table, I imagine.


Question 2: How should I extract the various items skyVersion, field, run,
rerun, camcol, obj from objID? I could do it, for example, by first dividing
objID by 2^16 to get obj as a remainder, dividing the quotient by something
else to get the next field as the remainder, dividing the new quotient
and so on. Would that get me involved in the floating point stuff that
one is trying to avoid? In any case, is there a mysql command that will
simply partition a 64 bit binary int into specified pieces?


use bitwise functions, like "&" for AND to and things against bitmask
numbers and "" and "" to shift things around.

http://www.mysql.com/doc/en/Bit_functions.html

Using these functions, you can more quickly and accurately pull certain
ranges of bits out of the ID.

This kind of work is similar to how people treat IP addresses and netmasks
and such. You may find examples of working with those numbers easier to
come across on the net.

Hope it helps.

-pb

Jeff Root writes:
Allan Adler asked about 64-bit integers in MySQL...

Hi, Allan!

I have *zero* experience with any flavor of SQL, so you can
judge the worth of my opinion with that in mind. I'd just
treat the number as a string. That is how it is being used
anyway, both as a whole and in its parts. I see no reason
not to use string manipulations rather than arithmetic.

The integer is given in base 10. It has to be converted to base 2
before its substrings become relevant. I have written a little
Maxima program to do the conversion to base 2 and extraction of the
substrings but the point of my question is whether I can instead do
everything inside MySql.

Allan Adler


************************************************** **************************
* *
* Disclaimer: I am a guest and *not* a member of the MIT Artificial *
* Intelligence Lab. My actions and comments do not reflect *
* in any way on MIT. Moreover, I am nowhere near the Boston *
* metropolitan area. *
* *
************************************************** **************************

Using data from the Sloan Digital Sky Survey, I created a table with 10
entries as follows:

CREATE TABLE top10(objID int(64),
skyversion int(5),
run int(16),
rerun int(11),
camcol int(3),
field int(13),
obj int(16));

I put this in a batch file out.batch and read it in to a mysql session.
I then loaded the following data from a file top10.txt:

582100252142993706 1 2328 20 3 166 298
582100252142994134 1 2328 20 3 166 726
582100252142993685 1 2328 20 3 166 277
582100252142993598 1 2328 20 3 166 190
582100252142994129 1 2328 20 3 166 721
582100153358746287 1 2305 20 3 166 687
582100152821874723 1 2305 20 2 166 35
582100152821874715 1 2305 20 2 166 27
582100152821874718 1 2305 20 2 166 30
582100152821874725 1 2305 20 2 166 37

That worked fine. Now, suppose I wanted to do it all differently. Specifically,
suppose I only wanted the file top10.txt to contain the first column entries
(i.e. the bigints) and to instead compute the other columns from the first
column. Bits 0 through 15 represent the integer in the "obj" column.
Bits 16 through 28 represent the integer in the "field" column. Bits 29-31
represent the integer in the "camcol" column. Bits 32-47 represent the
integer in the "run" column. Bits 48-58 represent the integer in the "rerun"
column. Bits 59-63 represent the integer in thee "skyversion" column.

What exactly should the command be to fill in the table with the given
values in the first column and the computed values in the other columns?

Ignorantly,
Allan Adler


************************************************** **************************
* *
* Disclaimer: I am a guest and *not* a member of the MIT Artificial *
* Intelligence Lab. My actions and comments do not reflect *
* in any way on MIT. Moreover, I am nowhere near the Boston *
* metropolitan area. *
* *
************************************************** **************************

On Mon, 30 Jun 2003 23:58:18 +0000, Allan Adler wrote:


Using data from the Sloan Digital Sky Survey, I created a table with 10
entries as follows:

CREATE TABLE top10(objID int(64),

I think I'd make that an unsigned int so that you get all the digits you
think you want. You're missing one this way, I believe.

skyversion int(5),
run int(16),
rerun int(11),
camcol int(3),
field int(13),
obj int(16));

snip

That worked fine. Now, suppose I wanted to do it all differently.
Specifically, suppose I only wanted the file top10.txt to contain the
first column entries (i.e. the bigints) and to instead compute the other
columns from the first column. Bits 0 through 15 represent the integer
in the "obj" column. Bits 16 through 28 represent the integer in the
"field" column. Bits 29-31 represent the integer in the "camcol" column.
Bits 32-47 represent the integer in the "run" column. Bits 48-58
represent the integer in the "rerun" column. Bits 59-63 represent the
integer in thee "skyversion" column.

What exactly should the command be to fill in the table with the given
values in the first column and the computed values in the other columns?


I assume that you're numbering these bits from left to right, most
significant to least, which is a little odd, but I think that's the way
this astronomical deal is actually done. Correct me if I'm wrong.
(actually, don't correct me, but just learn from the explanation below and
apply it differently)

To get the skyversion, you want the 4 least significant bits, so && the
objID with 0x0000000F: "select objID && 15 from top10".

To get the "rerun" column, shift your objID 4 bits right (to get rid of
the "skyversion" bits) and then take the 11 least significant bits: select
4095 && (objID 4).

To get the "run" column, you want to shift off 15 bits (4 + 11) to the
right, then take the 16 least significant bits.

To get the obj column, you only need shift off to the right all but the
last 16 bits, so that's select objID 48.

All that may be wrong, but that's the way I'd try it at first, at least.

Good luck!

-pb