Support for bit field in other way

[livius2]

Currently we have Boolean field. But sometimes it is too big overhead to have 1 byte for 1 bit information. 100 boolean fields are 100 bytes instead of only 100/8. The best will be bit field datatype maintained by Firebird itself but i suppose below solution can be also interesting.

---

Currently we can use Integer field and use bin operations, and help self a little by computed fields e.g.:

CREATE TABLE TEST
(
ID INTEGER NOT NULL PRIMARY KEY,
FLAGS INTEGER NOT NULL DEFAULT 0,
IS_STORED COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 0), 1)),
IS_COMPOUND COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 1), 1)),
IS_SOMETHING COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 2), 1)),
...
);

or to have boolean fields results

CREATE TABLE TEST
(
ID INTEGER NOT NULL PRIMARY KEY,
FLAGS INTEGER NOT NULL DEFAULT 0,
IS_STORED COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 0), 1)=1),
IS_COMPOUND COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 1), 1)=1),
IS_SOMETHING COMPUTED BY(BIN_AND(BIN_SHR(FLAGS, 2), 1)=1),
...
);

But above is only for reading. You must do same BIN_AND operation and remember which bit to use to perform update. You cannot do:

UPDATE TEST SET IS_COMPOUND = TRUE WHERE ID=xxx;

---

But i see also other way to go. Introduce new syntax (only proposition of syntax, you can find the better probably)

CREATE TABLE TEST
(
FLAGS INTEGER NOT NULL DEFAULT 0,
IS_STORED  BIT 0 OF FLAGS,
IS_COMPOUND BIT 1 OF FLAGS,
IS_SOMETHING BIT 2 OF FLAGS,
);

And then you can simply support for bit informations for select and also for update statement. This have one advantage compared to bit datatype. You can still set all flags by simply updating FLAGS field itself.

Mayby syntax should be only as some extension for computed field e.g. IS_STORED COMPUTED BY BIT 0 OF FLAGS but it must be finally updatable.

[sim1984]

Create a VIEW with the appropriate triggers.

CREATE TABLE TEST (
  ID INTEGER NOT NULL PRIMARY KEY,
  FLAGS INTEGER DEFAULT 0 NOT NULL
);

CREATE VIEW V_TEST(
    ID,
    FLAGS,
    IS_STORED,
    IS_COMPOUND,
    IS_SOMETHING)
AS
SELECT
  ID,
  FLAGS,
  (BIN_AND(BIN_SHR(FLAGS, 0), 1) = 1) AS IS_STORED,
  (BIN_AND(BIN_SHR(FLAGS, 1), 1) = 1) AS IS_COMPOUND,
  (BIN_AND(BIN_SHR(FLAGS, 2), 1) = 1) AS IS_SOMETHING
FROM TEST
;


SET TERM ^ ;


CREATE OR ALTER TRIGGER TR_V_TEST_BI FOR V_TEST
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.FLAGS = COALESCE(NEW.FLAGS, 0);
  IF (NEW.IS_STORED IS TRUE) THEN
    NEW.FLAGS = BIN_OR(NEW.FLAGS, 1);
  IF (NEW.IS_COMPOUND IS TRUE) THEN
    NEW.FLAGS = BIN_OR(NEW.FLAGS, 2);
  IF (NEW.IS_SOMETHING IS TRUE) THEN
    NEW.FLAGS = BIN_OR(NEW.FLAGS, 4);

  INSERT INTO TEST (ID, FLAGS)
  VALUES (NEW.ID, NEW.FLAGS);
END
^

CREATE OR ALTER TRIGGER TR_V_TEST_BU FOR V_TEST
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  IF (NEW.IS_STORED IS TRUE) THEN
    NEW.FLAGS = BIN_OR(NEW.FLAGS, 1);
  ELSE
    NEW.FLAGS = BIN_XOR(NEW.FLAGS, 1);

  IF (NEW.IS_COMPOUND IS TRUE) THEN
    NEW.FLAGS = BIN_OR(NEW.FLAGS, 2);
  ELSE
    NEW.FLAGS = BIN_XOR(NEW.FLAGS, 2);

  IF (NEW.IS_SOMETHING IS TRUE) THEN
    NEW.FLAGS = BIN_OR(NEW.FLAGS, 4);
  ELSE
    NEW.FLAGS = BIN_XOR(NEW.FLAGS, 4);

  UPDATE TEST
  SET FLAGS = NEW.FLAGS
  WHERE ID = OLD.ID;
END
^

CREATE OR ALTER TRIGGER V_TEST_BD FOR V_TEST
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
  DELETE FROM TEST
  WHERE ID = OLD.ID;
END
^

SET TERM ; ^

[livius2]

Yes, updatable Views are helpfull. But as you can see this is long and not nescessery if supported by engine itself. You must create view, and you have to write 3 triggers and don't make a mistake. Later, if you want to change something in the table, you have to change 5 DB objects.

[asfernandes]

It would be much better if engine just stores boolean fields in more optimized way.

[livius2]

@asfernandes aggree but one advantage cannot be missed:

This have one advantage compared to bit datatype. You can still set all flags by simply updating FLAGS field itself.