On 2016/10/17 3:54 PM, Daniel Polski wrote:
Let's say I have a table like this:
CREATE TABLE table1(
INSERT INTO table1 VALUES(1,1, 0, 1);
INSERT INTO table1 VALUES(2,1, 1, 1);
INSERT INTO table1 VALUES(3,1, 4, 1);
INSERT INTO table1 VALUES(4,1, 7, 1);
INSERT INTO table1 VALUES(5,2, 0, 1);
The "bit_position" represent individual bits in bytes, and "val"
represent if the bit should be set or clear (unit 1= 0b10010011, unit
2=0xb00000001 for the data in the above table).
How can I get the byte data in a single select instead of parsing
through the individual bits one by one?
Luckily all data needed is in the table, and Igor's method will work
just dandy in your case. Why not store the flags in full rather than per
line though? You can easily manipulate the values by using standard
bit-masking and boolean bit-wise functions in SQL.
Ex1. Set bit 3 of field fbit for unit 1: UPDATE Tabel1 SET fbit =
((1 << 3) || fbit) WHERE unit = 1;
Ex2. Read bit 3 of field fbit for unit 1: SELECT (fbit && (1 << 3)) >
0 FROM Tabel1 WHERE unit = 1;
This way you can just read the fbit from a record in stead of having to
go through all the grouping and aggregating to pivot it.
10 points though for finding the most inefficient way to store
nonvolatile bit-flags since magnetic core memory.
sqlite-users mailing list