Обсуждение: Bit-wise foreign keys
Hey all, I'm tossing an idea around again, namely using bit positions and values as foreign key references. Let's start with a bitof background information: I'm currently parsing a log-file that I want to apply all kinds of statistical analysis to. This file contains lines of recordsof data, among which are some bytes of which each bit marks a certain truth-value. As an internal data-object that'sjust dandy, but presenting it to, for example a user, or to query it for certain masks without having to delve intothe definition of that particular bit-field it would be great to have a textual representation of each bit. Let's say we have this byte, with the attached meanings: pos meaning 0 RED (least significant bit) 1 GREEN 2 BLUE 3 FIRE 4 WATER 5 EARTH 7 AIR (most significant bit) Now if I see a value of 0x05, I know that this corresponds to [RED, FIRE] and if I have a value of 0x41 I know that I'm introuble as there's a bit set that has no meaning! Reeks of a foreign key constraint, doesn't it? An odd one though, as one value can contain multiple bits and thus referencesmultiple foreign values... Do we have anything for this yet? If not, would this be a good feature? I imagine something similar could be done for (fixed-length?) arrays too... Of course something like this would benefit from some convenience functions; you wouldn't really want to get multiple recordsfor the same row with different bits matched - something like bit_accum() to turn the separate results into an arraywould be useful I think. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c97949110251883445726!
On Sep 20, 2010, at 10:06 AM, Alban Hertroys wrote:
> Hey all,
>
> I'm tossing an idea around again, namely using bit positions and values as foreign key references. Let's start with a
bitof background information:
>
> I'm currently parsing a log-file that I want to apply all kinds of statistical analysis to. This file contains lines
ofrecords of data, among which are some bytes of which each bit marks a certain truth-value. As an internal data-object
that'sjust dandy, but presenting it to, for example a user, or to query it for certain masks without having to delve
intothe definition of that particular bit-field it would be great to have a textual representation of each bit.
>
> Let's say we have this byte, with the attached meanings:
> pos meaning
> 0 RED (least significant bit)
> 1 GREEN
> 2 BLUE
> 3 FIRE
> 4 WATER
> 5 EARTH
> 7 AIR (most significant bit)
>
> Now if I see a value of 0x05, I know that this corresponds to [RED, FIRE] and if I have a value of 0x41 I know that
I'min trouble as there's a bit set that has no meaning!
>
> Reeks of a foreign key constraint, doesn't it? An odd one though, as one value can contain multiple bits and thus
referencesmultiple foreign values...
Or references a single foreign value, if you have a reference table with all the valid bit combinations, which'd be
prettysimple to generate programatically for small numbers of combinations.
insert into foo (k integer, v text[]) values (0x21, '{"EARTH","GREEN"}';
You could also apply any other set of constraints you wanted in that way (Fire is Red, Water is either Blue or Green).
Or you could use a separate table to store the (record, enum) pairs with one entry for each set bit in each record,
withone foreign key constraint to the table of records and one to a static table of valid enum value types ((1, 'red'),
(2,'green'), (4, 'blue') ...).
Cheers,
Steve
On 20 Sep 2010, at 19:25, Steve Atkins wrote:
> On Sep 20, 2010, at 10:06 AM, Alban Hertroys wrote:
>> Hey all,
>>
>> I'm tossing an idea around again, namely using bit positions and values as foreign key references. Let's start with
abit of background information:
>>
>> I'm currently parsing a log-file that I want to apply all kinds of statistical analysis to. This file contains lines
ofrecords of data, among which are some bytes of which each bit marks a certain truth-value. As an internal data-object
that'sjust dandy, but presenting it to, for example a user, or to query it for certain masks without having to delve
intothe definition of that particular bit-field it would be great to have a textual representation of each bit.
(...)
> Or references a single foreign value, if you have a reference table with all the valid bit combinations, which'd be
prettysimple to generate programatically for small numbers of combinations.
>
> insert into foo (k integer, v text[]) values (0x21, '{"EARTH","GREEN"}';
>
> You could also apply any other set of constraints you wanted in that way (Fire is Red, Water is either Blue or
Green).
(...)
I managed to find a solution that doesn't involve having n! rows for n bits, which would be a bit problematic if you
everrun into 64-bit bit-fields. My solution won't function as a (foreign key) constraint though, but I didn't really
needthat anyway.
So far it works quite satisfactory, so I thought others might benefit from the idea. It uses standard functionality
that'savailable in every basic Postgres installation since around 8.3 I think (I use 8.4 since a couple of days now).
Attached is what I did (you can source the below through psql).
!DSPAM:737,4ca615d7678303570290129!
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4ca615d7678303570290129!