nmerriweather has asked for the wisdom of the Perl Monks concerning the following question:

I tried looking online, but I don't know where to being.

In mysql, there exists a column type called set.

""If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the the members have the following decimal and binary values:""
SET Member      Decimal Value      Binary Value
'a'     1     0001
'b'     2     0010
'c'     4     0100
'd'     8     1000

[download]
Which allows one to store a; a,b ; a,c ; or any permutation

I know there's a way to do this in perl -- can someone either please point me to a module/perldoc/chapter in one of the perlbooks i have for more info?, or give me some valuable information they have gleaned in their experience

Replies are listed 'Best First'.
Re: What is the perl equivelant to a 'Set' column type in mysql?
by matija (Priest) on Apr 30, 2004 at 19:34 UTC
[reply]
      You're looking at a big Thank You.
[reply]
Re: What is the perl equivelant to a 'Set' column type in mysql?
by nmerriweather (Friar) on Apr 30, 2004 at 21:27 UTC
    Actually, I dont think that does excatly what I want to do -- but it is the right topic.

    I have a set of music genres that a band can be: rock, pop, jazz, metal, etc -- the set is longer, but finite.

    I have several bands, and want to compute a storable value for each band relating to the genres that they span (ie, a jazzy rock band).

    So, I need to basically contruct a 2 functions - one that takes the list of genres for a band and returns a storable number/string based on a reference set of available generes, and another that does the reverse.

    Does this make sense? I basically want to replicate the functionality of mysql in this regard, so I'm not tied to using them as a backend datastore.
[reply]
      What MySQL basically does is assign each set value a number, then uses those numbers to set and clear bits in an integer. For example: 
      use constant GENRE_ROCK => 0;
use constant GENRE_POP => 1;
use constant GENRE_JAZZ => 2;
use constant GENRE_METAL => 3;
use constant GENRE_LAST => 3;

sub pack_set
{
  my $bits == 0;
  foreach my $genre (@_)
  {
      $bits |= (1<<$genre);
  }
  $bits;
}

sub unpack_set
{
  my($bits)=@_;
  my @ret = ();
  my $i = 0;
  while ($bits)
  {
    if ($bits & 1)
    {
       push(@ret,$i);
    }
    $i++;
    $bits >>= 1;
  }
  @ret;
}

my $ella = pack_set(GENRE_POP, GENRE_JAZZ);
warn "Packed set is $ella\n";
print "Ella's Genres: ";
foreach my $genre (unpack_set($ella))
{
  $genre == GENRE_ROCK and print "rock ";
  $genre == GENRE_POP and print "pop ";
  $genre == GENRE_JAZZ and print "jazz ";
  $genre == GENRE_METAL and print "metal ";
}
print "\n";

      [download]

      Storing the numbers in a hash instead of with constants would make it easier to print them.

[reply]
[d/l]
[select]

        Small logic nit. The set bits are 0 1 2 4 8.... In your example a jazzy pop band would be a metal entity as well as a last....It is also worth noting that you can only rely on 31-32 (sign bit) genres unless you can guarantee long 64 bit ints.

        cheers

        tachyon

[reply]
        awesome -- I knew thats the logic behind how mysql did it, I just didn't know the way it processed it you rock. like a hurricane.
[reply]

Back to Seekers of Perl Wisdom