How to Store and Transfer Delphi Sets with More Than 64 Elements

A Delphi set is a good way to hold a list of permissions or options: one bit per element, in, Include, + and * all built in. While the enum has 64 elements or fewer, the set fits in an Int64, which goes straight into a BIGINT column. Element 65 breaks that. A set can have up to 256 elements, but no integer type is that wide.

The answer used here is to treat the set as an array of Int64 words: two words for up to 128 elements, four for 256. The words are stored in separate columns and sent to the browser as hex, and the set type stays exactly as it is in the code.

How Delphi Lays Out a Set

For an enum whose first element has ordinal 0, a set uses one bit per element: element n is bit n mod 8 of byte n div 8. SizeOf is the number of bytes needed for the highest ordinal, so a set of an enum with 128 elements (ordinals 0 to 127) is exactly 16 bytes.

On Windows, which is little-endian, those 16 bytes read as two Int64 values give element n as bit n mod 64 of word n div 64. That means a variant record can show the same memory as a set or as two words, with no conversion code at all. The test program for this article checks every one of the 128 elements against that rule.

The Enum

The values end up in a database, so the position of each element is fixed for good. Two rules follow:

New elements are only added at the end, by renaming a spare. Never insert or reorder.
Bit 63 and bit 127 are never used. Each word then stays positive, which matters on the server (a negative word can be treated as corrupt), in MySQL and in the browser.

Declaring the spares up front keeps SizeOf at 16 bytes and makes the reserved bits visible.

Server Side

unit Permissions;

interface

type
  // Positions are stored in the database, so new items are only ever added
  // at the end (by renaming a spare), never inserted or reordered.
  TPermission = (
    pNone,           pLogin,
    pViewOrders,     pEditOrders,
    pViewCustomers,  pEditCustomers,
    pViewInvoices,   pEditInvoices,
    pViewReports,    pEditSettings,
    p10, p11, p12, p13, p14, p15, p16, p17, p18, p19,
    p20, p21, p22, p23, p24, p25, p26, p27, p28, p29,
    p30, p31, p32, p33, p34, p35, p36, p37, p38, p39,
    p40, p41, p42, p43, p44, p45, p46, p47, p48, p49,
    p50, p51, p52, p53, p54, p55, p56, p57, p58, p59,
    p60, p61, p62,
    pSignBit1,       // bit 63 - never used, so word 1 is never negative
    pViewStock,      pEditStock,
    p66, p67, p68, p69,
    p70, p71, p72, p73, p74, p75, p76, p77, p78, p79,
    p80, p81, p82, p83, p84, p85, p86, p87, p88, p89,
    p90, p91, p92, p93, p94, p95, p96, p97, p98, p99,
    p100, p101, p102, p103, p104, p105, p106, p107, p108, p109,
    p110, p111, p112, p113, p114, p115, p116, p117, p118, p119,
    p120, p121, p122, p123, p124, p125, p126,
    pSignBit2);      // bit 127 - never used, so word 2 is never negative

  TPermissions = set of TPermission;

  TPermissionWord  = (pw1, pw2);
  TPermissionWords = array [TPermissionWord] of Int64;

const
  // Every permission in use; bits for spares and the sign bits are 0
  AllPermissions: TPermissionWords = ($00000000000003FF, $0000000000000003);

type
  TPermissionStore = record
    function ToHex(const AWord: TPermissionWord): string;
    // False, with no permissions, unless every word is 16 hex digits and
    // none has the sign bit set. Bits not in AllPermissions are dropped.
    function FromHex(const AHex: array of string): Boolean;
    // As FromHex, for words read as numbers (from the database, a token, ...)
    function FromWords(const AWords: array of Int64): Boolean;
    case Boolean of
      False: (Words: TPermissionWords);
      True:  (Permissions: TPermissions);
  end;

implementation

uses
  System.SysUtils;

{$IF SizeOf(TPermissions) <> SizeOf(TPermissionWords)}
  {$MESSAGE FATAL 'TPermissions must be exactly two Int64 words'}
{$IFEND}

const
  HexLength = 16;
  HexDigits = '0123456789ABCDEFabcdef';

function IsHex16(const AHex: string): Boolean;
var
  I: Integer;
begin
  Result := Length(AHex) = HexLength;
  if not Result then
    Exit;
  for I := 1 to HexLength do
    if Pos(AHex[I], HexDigits) = 0 then
      Exit(False);
end;

function TPermissionStore.ToHex(const AWord: TPermissionWord): string;
begin
  Result := IntToHex(Words[AWord], HexLength);
end;

function TPermissionStore.FromHex(const AHex: array of string): Boolean;
var
  LWords: TPermissionWords;
  LWord: TPermissionWord;
begin
  Result := Length(AHex) = Length(LWords);
  for LWord := Low(TPermissionWord) to High(TPermissionWord) do
    Result := Result and
              IsHex16(AHex[Ord(LWord)]) and
              TryStrToInt64('$' + AHex[Ord(LWord)], LWords[LWord]);

  if Result then
    Result := FromWords(LWords)
  else
    Permissions := [];
end;

function TPermissionStore.FromWords(const AWords: array of Int64): Boolean;
var
  LWord: TPermissionWord;
begin
  Result := Length(AWords) = Length(Words);
  for LWord := Low(TPermissionWord) to High(TPermissionWord) do
    Result := Result and (AWords[Ord(LWord)] >= 0);

  if Result then
  begin
    for LWord := Low(TPermissionWord) to High(TPermissionWord) do
      Words[LWord] := AWords[Ord(LWord)] and AllPermissions[LWord];
  end
  else
    Permissions := [];
end;

end.

Notes on the unit:

The {$IF SizeOf(...)} check stops the build if anyone adds a 129th element. Without it the set would grow to 17 bytes and the second word would silently miss the top element. Adding one element to the enum above gives F1054 TPermissions must be exactly two Int64 words.
AllPermissions masks out spare bits, so values from outside can never switch on an element that does not exist yet. Update it whenever a spare is renamed.
Because of the variant record, the code that checks permissions never sees the words:

if pEditOrders in LUser.Permissions then
  SaveOrder;

The Database

Each word gets its own BIGINT column. Generated columns give the hex for free, which is useful when the rows are sent to a browser through an ORM such as Aurelius:

CREATE TABLE user_roles (
  Ref          int          NOT NULL AUTO_INCREMENT,
  Name         varchar(40)  NOT NULL,
  Rights       bigint       NOT NULL DEFAULT 0,
  Rights_2     bigint       NOT NULL DEFAULT 0,
  Rights_Hex   varchar(16)  GENERATED ALWAYS AS (LPAD(HEX(Rights),   16, '0')) VIRTUAL,
  Rights_2_Hex varchar(16)  GENERATED ALWAYS AS (LPAD(HEX(Rights_2), 16, '0')) VIRTUAL,
  PRIMARY KEY (Ref)
);

A user with two roles gets the union of both, which in SQL is a bitwise OR on each word:

SELECT COALESCE(r1.Rights,   0) | COALESCE(r2.Rights,   0) AS Rights,
       COALESCE(r1.Rights_2, 0) | COALESCE(r2.Rights_2, 0) AS Rights_2
  FROM users u
  LEFT JOIN user_roles r1 ON r1.Ref = u.Role1
  LEFT JOIN user_roles r2 ON r2.Ref = u.Role2
 WHERE u.Ref = :UserRef;

MySQL's bit operators return an unsigned 64-bit result. A word with bit 63 set would come back as a number above High(Int64). With the sign bit never used, every result fits in a signed BIGINT and reads into an Int64 unchanged.

Reading and writing with FireDAC:

// Reading
if not LStore.FromWords([Query.FieldByName('Rights').AsLargeInt,
                         Query.FieldByName('Rights_2').AsLargeInt]) then
  raise Exception.Create('Corrupt rights for role ' + IntToStr(RoleRef));

// Writing
Query.SQL.Text := 'UPDATE user_roles SET Rights = :R1, Rights_2 = :R2 ' +
                  'WHERE Ref = :Ref AND Ref <> 0';
Query.ParamByName('R1').AsLargeInt := LStore.Words[pw1];
Query.ParamByName('R2').AsLargeInt := LStore.Words[pw2];
Query.ParamByName('Ref').AsInteger := RoleRef;
Query.ExecSQL;

Use one column per word rather than a single BINARY(16). Plain BIGINT columns work with | and & in every MySQL version, index normally, and read straight into Int64.

Sending the Set to the Browser

Each word goes as its own field of 16 hex digits - a JSON number would be rounded above 2⁵³, as explained in Sending Int64 Values to JavaScript. Keeping one field per word also means an old browser that only knows about word 1 keeps working when word 2 is added.

pas2js stores sets as JavaScript objects, so a set of 128 elements works in the browser exactly as on the server. Only the conversion from hex differs. The browser unit declares the same enum and set, and reads the words in 32-bit halves:

unit Permissions_Web;

interface

type
  // The same enum as the server, in the same order
  TPermission = (
    pNone,           pLogin,
    pViewOrders,     pEditOrders,
    pViewCustomers,  pEditCustomers,
    pViewInvoices,   pEditInvoices,
    pViewReports,    pEditSettings,
    p10, p11, p12, p13, p14, p15, p16, p17, p18, p19,
    p20, p21, p22, p23, p24, p25, p26, p27, p28, p29,
    p30, p31, p32, p33, p34, p35, p36, p37, p38, p39,
    p40, p41, p42, p43, p44, p45, p46, p47, p48, p49,
    p50, p51, p52, p53, p54, p55, p56, p57, p58, p59,
    p60, p61, p62,
    pSignBit1,
    pViewStock,      pEditStock,
    p66, p67, p68, p69,
    p70, p71, p72, p73, p74, p75, p76, p77, p78, p79,
    p80, p81, p82, p83, p84, p85, p86, p87, p88, p89,
    p90, p91, p92, p93, p94, p95, p96, p97, p98, p99,
    p100, p101, p102, p103, p104, p105, p106, p107, p108, p109,
    p110, p111, p112, p113, p114, p115, p116, p117, p118, p119,
    p120, p121, p122, p123, p124, p125, p126,
    pSignBit2);

  TPermissions = set of TPermission;

// Reads the two hex words sent by the server into a set.
// False, with an empty set, unless both are 16 hex digits without the sign bit.
function PermissionsFromHex(const AHex1, AHex2: string;
  out APermissions: TPermissions): Boolean;

// The two hex words for a set, to send back to the server
procedure PermissionsToHex(const APermissions: TPermissions;
  out AHex1, AHex2: string);

implementation

uses
  SysUtils;

const
  HexLength     = 16;
  HalfHexLength = 8;
  BitsPerHalf   = 32;
  HalvesPerWord = 2;
  SignBit       = 2147483648;   // bit 31 of a half
  HexDigits     = '0123456789ABCDEFabcdef';

type
  THalves = array [0..3] of NativeInt;   // bits 0..31, 32..63, 64..95, 96..127

function PowerOf2(ABit: Integer): NativeInt;
var
  I: Integer;
begin
  Result := 1;
  for I := 1 to ABit do
    Result := Result * 2;
end;

function IsHex16(const AHex: string): Boolean;
var
  I: Integer;
begin
  Result := Length(AHex) = HexLength;
  if not Result then
    Exit;
  for I := 1 to HexLength do
    if Pos(AHex[I], HexDigits) = 0 then
      Exit(False);
end;

// One word of 16 hex digits into two halves; refuses the sign bit
function ReadWord(const AHex: string; var AHalves: THalves; AWord: Integer): Boolean;
var
  LHi, LLo: NativeInt;
begin
  Result := IsHex16(AHex) and
            TryStrToInt('$' + Copy(AHex, 1, HalfHexLength), LHi) and
            TryStrToInt('$' + Copy(AHex, HalfHexLength + 1, HalfHexLength), LLo) and
            (LHi < SignBit);
  if Result then
  begin
    AHalves[AWord * HalvesPerWord] := LLo;
    AHalves[AWord * HalvesPerWord + 1] := LHi;
  end;
end;

function PermissionsFromHex(const AHex1, AHex2: string;
  out APermissions: TPermissions): Boolean;
var
  LHalves: THalves;
  P: TPermission;
  LHalf: NativeInt;
begin
  APermissions := [];
  Result := ReadWord(AHex1, LHalves, 0) and ReadWord(AHex2, LHalves, 1);
  if not Result then
    Exit;

  for P := Low(TPermission) to High(TPermission) do
  begin
    LHalf := LHalves[Ord(P) div BitsPerHalf];
    if (LHalf div PowerOf2(Ord(P) mod BitsPerHalf)) mod 2 = 1 then
      Include(APermissions, P);
  end;
end;

procedure PermissionsToHex(const APermissions: TPermissions;
  out AHex1, AHex2: string);
var
  LHalves: THalves;
  P: TPermission;
  I: Integer;
begin
  for I := Low(LHalves) to High(LHalves) do
    LHalves[I] := 0;

  for P := Low(TPermission) to High(TPermission) do
    if P in APermissions then
      LHalves[Ord(P) div BitsPerHalf] := LHalves[Ord(P) div BitsPerHalf] +
                                          PowerOf2(Ord(P) mod BitsPerHalf);

  AHex1 := IntToHex(LHalves[1], HalfHexLength) + IntToHex(LHalves[0], HalfHexLength);
  AHex2 := IntToHex(LHalves[3], HalfHexLength) + IntToHex(LHalves[2], HalfHexLength);
end;

end.

After that, the browser code uses the set the same way the server does:

if not PermissionsFromHex(Reply.Rights_Hex, Reply.Rights_2_Hex, FPermissions) then
  FPermissions := [];

btnEditStock.Enabled := pEditStock in FPermissions;

If you would rather not repeat the enum, put it in its own unit and add that unit to both the server and the TMS WEB Core project. The declaration is plain Pascal and compiles in both.

Testing

Server (Delphi 13, Win64) - SizeOf(TPermissions) is 16; each of the 128 elements set on its own lands on the expected bit of the expected word; hex round trips for every element, with spares dropped by the mask; both sign bits and negative words refused; wrong word counts refused; 100,000 random unions checked to stay positive. The {$IF} guard was checked by adding a 129th element.
Browser (TMS WEB Core) - every element converted to hex and compared with the expected digits; every element read back into a one-element set; sign bits refused; the hex produced by the server test read into [pViewOrders, pViewStock]; 5,000 random sets round tripped.
The SQL uses standard MySQL features (generated columns, HEX, LPAD and |) and was not run as part of these tests.

Pitfalls

Reordering the enum moves every stored permission to a different meaning. Treat the order as permanent.
An enum that does not start at ordinal 0, or a set of a subrange, changes the layout. Keep the first element at 0.
Forgetting the mask. Without AllPermissions, a bit set by an old bug or a hand-edited row switches on an element as soon as a spare is renamed.
Treating -1 as "all permissions". It sets the sign bits and every spare. Store the real value and grant "everything" in code instead.
More than 128 elements. The same pattern works with four words for 256; add columns, widen the arrays and update the size check.

PBS Delphi - Large Sets in the Database and the Browser

Back to Articles for Developers
Back to Articles on Delphi
How to Send Int64 Values from Delphi to JavaScript
Interposer Classes in Delphi

If you found this useful, then please consider making a donation.

Please donate if helpful