Slightly reminds me of how we used to use TINYINT in MySQL to store boolean flags (which can store a range from -128 to 127 or 0-255 if unsigned).
I'm the kind of nerd who secretly wished to store many such bools in one integer and use the bitwise operators to query them, but restrained myself from ever doing this, since if I managed to sneak that through code review, the number of curses of my name would no doubt become so large (and so loud) over time that it would affect my employability.
Now I wonder if it possible to create an SQL VIEW in any of the popular RDBMSs that would pack flags in one int8, but would expose them as separate columns in the VIEW in a way that would make not just SELECTs, but also UPDATEs and INSERTs, work.
That’s ubiquitous in slightly lower level programming (pretty much every non-small C program probably does it in some way or other), but the problem with doing that in this database scenario is that you might run into atomicity issues.
To toggle a flag, you have to read the column, modify it, and write it back. If someone else does that to another flag at the same time, there’s a race that one of you might win, at the expense of the other flag’s new value.
Unless you employ a lock that would otherwise not be necessary.
Slightly reminds me of how we used to use TINYINT in MySQL to store boolean flags (which can store a range from -128 to 127 or 0-255 if unsigned).
I'm the kind of nerd who secretly wished to store many such bools in one integer and use the bitwise operators to query them, but restrained myself from ever doing this, since if I managed to sneak that through code review, the number of curses of my name would no doubt become so large (and so loud) over time that it would affect my employability.
Now I wonder if it possible to create an SQL VIEW in any of the popular RDBMSs that would pack flags in one int8, but would expose them as separate columns in the VIEW in a way that would make not just SELECTs, but also UPDATEs and INSERTs, work.
That’s ubiquitous in slightly lower level programming (pretty much every non-small C program probably does it in some way or other), but the problem with doing that in this database scenario is that you might run into atomicity issues.
To toggle a flag, you have to read the column, modify it, and write it back. If someone else does that to another flag at the same time, there’s a race that one of you might win, at the expense of the other flag’s new value.
Unless you employ a lock that would otherwise not be necessary.