Can someone explain this SQL query?

Discussion in 'OT Technology' started by SubOptimal, Feb 12, 2010.

  1. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    Can someone explain this SQL query?

    Code:
    sql> SELECT rowid, intIP,
        ...>   ((intIP >> 24) & 255) ||'.'||
        ...>   ((intIP >> 16) & 255) ||'.'||
        ...>   ((intIP >>  8) & 255) ||'.'||
        ...>   ((intIP      ) & 255) AS strIP
        ...> FROM IP_table;
    
    It's used to convert IP integers (e.g. 3232235777) to IP addresses (192.168.1.1). The only part of it I understand are the "SELECT rowid, intIP", || means concat, and from table. :hs:
     
  2. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    '>>' is binary shift right, '& 255' is a binary and mask
     
  3. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
  4. mobbarley

    mobbarley Active Member

    Joined:
    Mar 4, 2005
    Messages:
    9,256
    Likes Received:
    2
    Location:
    Sydney
    the number '3232235777' (i havent checked) should be the 4x 8bit segments of the ip address:
    http://en.wikipedia.org/wiki/File:Ipv4_address.svg

    these groups bits are shifted right to the RHS 8 bits and then the rest are removed by the AND operation.
     
  5. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    It is, I converted 192.168.1.1 to that integer, I'm just having problems understanding that SQL query to convert it back. I'm kind of familiar with bitmasks, but I've never heard of binary shifting.

    So to get the first number of 192, you shift 24 bits to the right like in the SQL query and add a mask 0f 255...

    Code:
    11000000 10101000 00000001 00000001 = 192.168.1.1 or 3232235777
    00000000 00000000 00000000 11111111 = bitmask of 255
    __________________________________
    __________________________________1
    
    It's 1, instead of 192? What am I doing wrong?
     
    Last edited: Feb 12, 2010
  6. CodeX

    CodeX Guest

    You didn't do the shift...

    You would get

    11000000 10101000 00000001 00000001 = 192.168.1.1
    >> 24
    11111111 11111111 11111111 11000000 = 255.255.255.192
    AND
    00000000 00000000 00000000 11111111 = 0.0.0.255
    =
    ---------------------------------------
    00000000 00000000 00000000 11000000 = 0.0.0.192 (or simply 192 as decimal)
     
    Last edited by a moderator: Feb 12, 2010
  7. CodeX

    CodeX Guest

    When you right shift a signed value it preserves the sign bit, so if the highest order bit was a 1, a 1 will be shifted in.

    I have no idea why this would be a signed number if it is representing an IP address... if it is unsigned then the AND mask is unnecessary as all 0's will be shifted in.
     
  8. SubOptimal

    SubOptimal New Member

    Joined:
    Jun 27, 2002
    Messages:
    4,410
    Likes Received:
    0
    This is fucking awesome!!1 I think I got it now, thanks guys! :bowdown:
     

Share This Page