Bitwise operators in select statements for using Bitmasks: basic

This demonstrates searching bitmasks using named constants

You must execute the code again to save those values in the database.

/*
Usage: uncomment the select statements to run them. This app appears to only run the first SELECT that it encounters
This demonstrates using bitwise oprerators in select queries 
*/

CREATE TABLE 
	"store_orderitem" 
	(
	"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
	"order_name" varchar(100) NOT NULL, 
	"product" varchar(50) NOT NULL, 
	"quantity" integer NOT NULL, 
	"bitmask" text NOT NULL CHECK ((JSON_VALID("bitmask") OR "bitmask" IS NULL))
	);

INSERT INTO "main"."store_orderitem" ("id", "order_name", "product", "quantity", "bitmask") VALUES ('11', 'MyOrder', 'pants', '1', '2129924');
INSERT INTO "main"."store_orderitem" ("id", "order_name", "product", "quantity", "bitmask") VALUES ('12', 'MyOrder', 'pants', '1', '2129924');
INSERT INTO "main"."store_orderitem" ("id", "order_name", "product", "quantity", "bitmask") VALUES ('13', 'MyOrder', 'pants', '1', '2359360');
INSERT INTO "main"."store_orderitem" ("id", "order_name", "product", "quantity", "bitmask") VALUES ('14', 'MyOrder', 'pants', '1', '2359360');

-- Define CTE for bitmask constants
WITH BitmaskConstants AS (
    SELECT
        1 AS COLOR_BLACK,
        2 AS COLOR_RED,
        4 AS COLOR_LIME,
        8 AS COLOR_BLUE,
        16 AS COLOR_YELLOW,
        32 AS COLOR_CYAN,
        64 AS COLOR_MAGENTA,
        128 AS COLOR_SILVER,
        256 AS COLOR_GRAY,
        512 AS COLOR_MAROON,
        1024 AS COLOR_OLIVE,
        2048 AS COLOR_GREEN,
        4096 AS COLOR_PURPLE,
        8192 AS COLOR_TEAL,
        16384 AS COLOR_NAVY,
        32768 AS SIZE_XS,
        65536 AS SIZE_S,
        131072 AS SIZE_M,
        262144 AS SIZE_L,
        524288 AS SIZE_XL,
        1048576 AS SIZE_XXL,
        2097152 AS MATERIAL_COTTON,
        4194304 AS MATERIAL_POLYESTER,
        8388608 AS MATERIAL_LINEN,
        16777216 AS MATERIAL_DENIM
)

/*
select * from store_orderitem;
   
 SELECT * FROM store_orderitem
   WHERE bitmask = '2129924';
*/

-- find items with the COLOR_MAGENTA attribute (bit value `64`) using bitwise & and "not equals" <> 0 
/*
 SELECT * FROM store_orderitem
     WHERE CAST(bitmask AS INTEGER) & (SELECT COLOR_MAGENTA FROM BitmaskConstants) <> 0;
*/
-- find items without the COLOR_MAGENTA attribute (bit value `64`) using bitwise & and  = 0 
/*
 SELECT * FROM store_orderitem
     WHERE CAST(bitmask AS INTEGER) & (SELECT COLOR_MAGENTA FROM BitmaskConstants) = 0;
*/

SELECT 
	     * 
     FROM 
	     store_orderitem
     WHERE 
	     CAST(bitmask AS INTEGER) 
	     & ((SELECT SIZE_XS FROM BitmaskConstants) 
	     | (SELECT COLOR_LIME FROM BitmaskConstants)) = ((SELECT SIZE_XS FROM BitmaskConstants) 
	     | (SELECT COLOR_LIME FROM BitmaskConstants));

id	order_name	product	quantity	bitmask
11	MyOrder	pants	1	2129924
12	MyOrder	pants	1	2129924