Using the BIT Procedure to Simplify GE Security's Picture Perfect™ PPSQL Reports

The BIT() procedure makes it easy to decode "flags" and "state" fields stored in the Picture Perfect database, making your queries simpler and more readable.  Here's an example of how to use it to obtain the descriptions of all inputs that are currently offline:

SELECT description FROM inputs WHERE BIT(flags, 0) = 0 ORDER BY 1

The first paramter to BIT() is a column name such as "flags" or "state" or a constant value such as 260.  The second parameter is the bit number starting from zero where zero is the least significant bit.

The BIT() function returns a 1 if the specified bit is 1 and 0 if the specified bit is 0.

Using the Picture Perfect Flags and Values table, we see that the DFO monitoring bit is in the fourth position (bit 3) in the door flags field.  Without the BIT() function, one can use the MOD() built-in function to see whether bit 3 is set by subtracting it from bit 4 and testing whether the result is non-zero.  In SQL, it looks like this:

SELECT description FROM door WHERE MOD(flags,16) - MOD(flags,8) = 0 ORDER BY 1

Let's say that the flags value is 268 for a given door.  The decimal number 268 is the binary number 100001100.

28 (256)
27 (128)
26 (64)
25 (32)
24 (16)
23 (8)
22 (4)
21 (2)
20 (1)
1
0
0
0
0
1
1
0
0

An interesting fact is that when a number (such as 268 where bit 3 is set) is divided by a number that happens to be a power of two (such as 16), the quotient and remainder are distributed like this:

28 (256)
27 (128)
26 (64)
25 (32)
24 (16)
23 (8)
22 (4)
21 (2)
20 (1)
  
1
0
0
0
0
1
1
0
0
  

First, divide by 16, the next highest power of 2 after bit 3:
quotient 16
remainder 12
12

Now, divide 268 by 8, the value of bit 3
quotient 33
remainder 4
-4
   
8

If our flags value is 260, (and bit 3 is therefore not set) then our diagram looks like this:

28 (256)
27 (128)
26 (64)
25 (32)
24 (16)
23 (8)
22 (4)
21 (2)
20 (1)
  
1
0
0
0
0
0
1
0
0
  
quotient 16
remainder 4
4
quotient 33
4
-4
   
0

We observe that when bit 3 is not set, the difference between the value in bit 4 and bit 3 is zero.  We can generalize this observation by stating that if a given bit is set, the difference between the powers of two of the next higher bit and the given bit will equal the power of two corresponding to the specified bit.  That's why the construct MOD(flags, 16) - MOD(flags, 8) <> 0 will be true whenever bit 3 is non-zero and MOD(flags, 16) - MOD(flags, 8) = 0 will be true whenever bit 3 is zero.

Using MOD() produces the desired results but can make your queries unnecessarily convoluted and difficult to debug.

Now that the principle behind MOD() is understood, we discusss the BIT() function which totally hides all of the MOD() business.  Here is how we use BIT() to list all doors whose forced  open monitoring is disabled:

SELECT description FROM door WHERE BIT(flags, 3) = 0 ORDER BY 1

Because BIT() is really a stored procedure, there will be some processor overhead, but because device tables are comparatively small, that overhead is mimimal.

To install the stored procedure, follow these steps.

Copy the following snippet of code into your Windows clipboard:

#----------------- begin ---------------------------
#include "disclaimer.h"
sqlstmt 'DROP PROCEDURE BIT;'
sqlstmt 'CREATE PROCEDURE BIT (val INTEGER, bitno INTEGER) RETURNING INTEGER;
DEFINE lower_bit INTEGER;
DEFINE higher_bit INTEGER;
DEFINE diff INTEGER;
IF bitno > 30 THEN
RETURN (0);
END IF
IF bitno < 0 THEN
RETURN (0);
END IF
LET higher_bit = POW(2, bitno + 1);
LET lower_bit = POW(2, bitno);
LET diff = MOD(val, higher_bit) - MOD(val, lower_bit);
IF DIFF > 0 THEN
RETURN (1);
ELSE
RETURN (0);
END IF
END PROCEDURE;'
sqlstmt 'GRANT EXECUTE ON BIT TO ALL;'
#------------------- end ------------------------------

Use your favorite telnet or ssh client to establish a session on the Picture Perfect host. 

Switch users to root and change to the /tmp directory.

Type:

cat >bit.sh

Then, paste the contents of the Windows clipboard to the terminal session window.

Press Enter once.

Hold down the CTL key and press D (CTL-D).

Type:

sh bit.sh

The result should be that the BIT() routine is now a stored procedure.  You can check it by typing the following command:

selectit 'SELECT procid FROM sysprocedures WHERE procname = "bit"'

If the procedure  has been installed, an integer will be displayed.