Strings, Arithmetic AND / Bitwise Operators - or something completely different?

18 views
Skip to first unread message

Matthias Hanft

unread,
May 31, 2021, 3:19:49 PMMay 31
to firebird...@googlegroups.com
Hi,

I'd like to store kind of "set of flags" in a row (for example, in
Delphi, "set of 0..25").

What does work is a column VARCHAR(26), containg 'A' if flag 0 is
set, 'B' if flag 1 is set, and so on.

If I want to get all rows with flag 1 set, I can do something like
SELECT * FROM ... WHERE FLAGS CONTAINING 'B';

Not sure if that's efficient, I thought of some bitwise INTEGER
where bit 0 is set for flag 0, bit 1 for flag 1, and so on.

But then, I'd neet something like
SELECT * FROM ... WHERE (FLAGS AND 2)<>0;
if I want all rows with flag 1 set (because 2^1=2).
But Firebird doesn't seem to support "arithmetic AND" resp. bitwise
operators - right?

So I keep to the first solution - or is there a totally better
way to store such a "flag set"?

Thank you,

-Matt

Tomasz Tyrakowski

unread,
May 31, 2021, 3:55:14 PMMay 31
to firebird...@googlegroups.com
First, there's BIN_AND function in Firebird, so you can write
BIN_AND(FLAGS, 2) and it's the same as bitwise "FLAGS AND 2".
But packing bits into integers, while saving space, is somewhat
cumbersome (so if DB size is not your primary concern, reconsider).
Besides, if in the future your 26 flags become 126 flags, you may run
out of bits in your ints (and you'll need to introduce another field, as
a continuation of the existing one, all queries get complicated etc.),
while altering CHAR(26) to CHAR(126) is easy.
I personally express flag fields as char fields consisting of 'Y's and
'N's, e.g. 'YYYYNNY'. Then you can filter by that field using LIKE, for
example: SELECT ... FROM ... WHERE BIT_FIELD LIKE '__Y_Y_N' (_ in LIKE
stands for "any single character"), which gives all rows with BIT_FIELD
containing "Y" at positions 3 and 5 and N at position 7 (all other
positions are irrelevant). I know such fields take more space than
"bit-packed" ints, but their processing is simpler and they are human
readable (you can easily modify such data with a sql tool).
Setting a flag at a given position (bit_pos) can be achieved via a
statement like this: UPDATE ... set BIT_FIELD = substring(BIT_FIELD from
1 for bit_pos-1) || 'Y' || substring(BIT_FIELD from bit_pos + 1).
Using LIKE probably prevents FB from taking advantage of indices, but so
does BIN_AND.
I'm not saying that's the best possible solution, but it's worked for me
just fine, so just a suggestion ;)

cheers
Tomasz

________________________________


Ta wiadomość zawiera poufne informacje przeznaczone tylko dla adresata. Jeżeli nie jesteście Państwo jej adresatem, bądź otrzymaliście ją przez pomyłkę, prosimy o powiadomienie o tym nadawcy oraz trwałe jej usunięcie.

Tim Crawford

unread,
May 31, 2021, 6:14:41 PMMay 31
to firebird...@googlegroups.com, Tomasz Tyrakowski
Maybe you want to think about normalization for many many reasons.
If you go down a route with repeating values in a table you may live to regret it.....

Here is some pseudo DDL and Delphi (well free pacal anyway)
for tables and insert/update of a row and flags for the row

Create table MASTER
    ( id        INTEGER     not NULL
                            constraint  MASTER_id_pk
                                PRIMARY KEY
                                using INDEX MASTER_id_idx
    , alt_key   varchar(25) not null  
                                UNIQUE
                                using index MASTER_ALT_KEY_IDX_U
    );

Create table MASTER_FLAG
    ( -- rows only exist if flag is set
      MASTER_id INTEGER     not NULL
    , Flag      varchar(1)  not null
    , constraint mf_id_flag_c
        unique (master_id, flag)
        using index mf_id_flag_idx_u
    );

You could have a flag_val smallint not null
if you want to keep a row for every flag, but assuming
the flags are sparse then a waste, and requires extra sql
in where clauses to check flag_val
             
Delphi pseudo code to update db for a master/master_flag set

type  tFlags = (A, B, C, D);
const cFlags : array of char = ('A','B','C','D');
var   flag   : tFlags;
type masterRec = record
    id : integer;
    altKey: string;
      flags : set of tFlags;
  end;
var master: masterRec;
begin
  master.alt_key:= 'A Master Key';
  insert or update
    MASTER    (alt_key)
    values    (:master.altKey)
    matching  (alt_key)
    returning id
    into      master.id;
  master.flags:= [A, C];
  for flag in tflags do
    if flag in master.flags
    then insert or update
         MASTER_FLAGS (master_id, flag)
         values       (:master.Id, :cFlags[flag])
         matching     (master_id, flag)
    else delete from master_flags
         where master_id = :master.id
           and flag      = :cFlags[flag]
    ;
end

Now you can just select with join

-- All MASTER rows with flag 'C'
Select  *
from    MASTER m
join    MASTER_FLAGS mf on
     on mf.master_id = m.id
    and mf.flag_name = 'C' -- or mf_flag_NAME in ('A','B')
;
-- all master rows that do NOT have B flag
Select *
from    MASTER m
left
join    MASTER_FLAGS mf on
     on mf.master_id = m.id
    and mf.flag_name = 'B'
where   mf.flag_name is null;
;

Possible more efficient if MASTER had a lot of colums being returned?
Select *
from MASTER m
where
    not exists (select 1 from master_flags mf
                where mf.master_id = m.id
                and   mf.flag = 'B' -- or 'in'
               )

Mark Rotteveel

unread,
Jun 1, 2021, 4:25:55 AMJun 1
to firebird...@googlegroups.com
On 2021-05-31 21:55, Tomasz Tyrakowski wrote:
> First, there's BIN_AND function in Firebird, so you can write
> BIN_AND(FLAGS, 2) and it's the same as bitwise "FLAGS AND 2".
> But packing bits into integers, while saving space, is somewhat
> cumbersome (so if DB size is not your primary concern, reconsider).
> Besides, if in the future your 26 flags become 126 flags, you may run
> out of bits in your ints (and you'll need to introduce another field,
> as
> a continuation of the existing one, all queries get complicated etc.),
> while altering CHAR(26) to CHAR(126) is easy.

Firebird introduces a 128-bit integer type, INT128, so that would still
leave you with two bits of growth ;)

Mark

Mark Rotteveel

unread,
Jun 1, 2021, 4:26:47 AMJun 1
to firebird...@googlegroups.com
I meant to say "Firebird 4 introduces ...."

Mark

Tomasz Tyrakowski

unread,
Jun 1, 2021, 4:39:45 AMJun 1
to Tim Crawford, firebird...@googlegroups.com
On 01.06.2021 at 00:14, Tim Crawford wrote:
> Maybe you want to think about normalization for many many reasons.
> If you go down a route with repeating values in a table you may live to
> regret it.....

Agreed. A char field containing a dozen flags probably doesn't fit the
first normalized form, but I'm not dogmatic :). Clarity of code first,
efficiency second, all the rest third.
Should the table with "flag fields" contain a billion rows, I would
probably go for indexed master-detail (as you suggested), 'cause
filtering with LIKE would be very inefficient. However, for rasonably
sized tables, yes/no fields simplify client code and are easier to
process by hand (if needed).
But, as I wrote previously, even though the yes/no flag fields have
worked for me, they don't have to work for others - it all depends on a
particular scenario.
Thanks for the correction, though.

have a good one
Reply all
Reply to author
Forward
0 new messages