Monday, June 21, 2010

ADS 10 Tip #16 – Bitwise Operators

Bitwise operators have been added to the Advantage SQL engine. The full list of supported operators is below. I included some example values demonstrating the result of each of the operators.

Operator Value 1 Value 2 Result
& ( AND ) 10101010 11001011 10001010
| ( OR ) 10101010 11001011 11101111
^ ( XOR ) 10101010 11001011 01100001
` ( NOT ) 10101010 NA 01010101
<< ( Shift Left ) 11011010 NA 10110100
>> ( Shift Right ) 11011010 NA 01101101

Option Value
Unique 1
Compound 2
Custom 4
Descending 8
Candidate 2048
Binary 4096
These operators can be very useful in resolving a bitmask. For example the Advantage Index Options are defined as a bitmask. The table to the right lists the index options and their associated values. Each of which corresponds to a particular bit value. These options can be added together to define the index type. For example a primary (candidate) key index would have the option 2051 ( Unique + Compound + Candidate ).

Since each of the option values are represented by a specific bit we can use the binary & operator to determine if a particular bit is set. Using our example value for a primary key 2051, we can determine which options have been selected using the & operator. For example IF ( 2051 & 2048 = 2048 ) we know one of the options is Candidate.

The user defined function (UDF) shown below accepts an index option value and returns a string description of the options specified.

CREATE FUNCTION IndexOptions ( Options INTEGER ) RETURNS CHAR ( 100 )
BEGIN
  DECLARE sReturn STRING;
  sReturn = '';

  IF ( Options & 1) = 1 THEN 
    sReturn = 'Unique, ';
  END IF;

  IF ( Options & 2) = 2 THEN 
    sReturn = sReturn + 'Compound, ';
  END IF;

  IF ( Options & 4) = 4 THEN 
    sReturn = sReturn + 'Custom, ';
  END IF;

  IF ( Options & 8) = 8 THEN 
    sReturn = sReturn + 'Descending, ';
  END IF;

  IF ( Options & 2048) = 2048 THEN 
    sReturn = sReturn + 'Candidate, ';
  END IF;

  IF ( Options & 4096) = 4096 THEN 
    sReturn = sReturn + 'Binary, ';
  END IF;

  // Remove the trailing comma
  return LEFT( sReturn, LENGTH(sReturn) - 1);
END;

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

No comments: