After 4 weeks of after-work development I finally got the MNP forums upgraded (using YetAnotherForums an OSS hosted on SF).  Instead of just throwing away the 30,000 posts and 3,000 user accounts I decided to port them over.  It probably would have taken me a week if I hadn't done this, but man...I learned me some SQL the past few weeks.  Particularly interesting were bitwise operators.

Basically what I had done (and even the YAF people did) in the past was have fields like IsAdmin, IsApproved etc.  I did come up with a Flags system before, where I would just have a bunch of 1's and 0's which would pertain to each but this is much cooler.  Basically this time you have your Flags field, and the query you run against it runs a bitwise AND or OR on it.  Bitwise AND's work with binary and I learned this stuff back when I was an EE (along with XOR, XAND, NAND, NOR, etc).  Here...I'm still a little tired, here's an example.

    111000
& 100110
----------
    100000

You take the top number if it is true (a 1) AND the bottom number is true (a 1) then the result is true - else it's false (0).  Similar with OR:

     111000
&  100110
-----------
     111110

In this case if the top number is true OR the bottom number is true, the result is true - else it's false.

Say we have a table now that describes a user.

User
------------
UserID
UserName
Flags

with the entry: 1,myUsername, 4 (binary = 100).  4 in this case stands for Administrator (yes, coding up some enumerations should pop into your mind right about here).  How would we test for this in SQL?

SELECT * FROM User WHERE (Flags & 4)

Yeah, the first time I saw this, I was like...errr what?  Basically the & performs a bitwise & against the Flags column and where the user's 4 "column" is 1 in binary it will retrieve that record.

For example if you had the following records:

1, me, 4
2, you, 7
3, bob, 5

and performed the above query, you would get all three of the records above because their 2^2 "column" in binary has a 1 in it.  (100,111, and 101 respectively).  Whoa...no more IsPoopHead columns anymore eh? eeehh?