Bitwise operators in select statements for using Bitmasks: Advanced

This demonstrates searching bitmasks using named constants. It uses a more complex way to represent the constants, which is more powerful for showing common characteristics.

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 bit_value, 'COLOR_BLACK' AS attribute UNION ALL
        SELECT 2, 'COLOR_RED' UNION ALL
        SELECT 4, 'COLOR_LIME' UNION ALL
        SELECT 8, 'COLOR_BLUE' UNION ALL
        SELECT 16, 'COLOR_YELLOW' UNION ALL
        SELECT 32, 'COLOR_CYAN' UNION ALL
        SELECT 64, 'COLOR_MAGENTA' UNION ALL
        SELECT 128, 'COLOR_SILVER' UNION ALL
        SELECT 256, 'COLOR_GRAY' UNION ALL
        SELECT 512, 'COLOR_MAROON' UNION ALL
        SELECT 1024, 'COLOR_OLIVE' UNION ALL
        SELECT 2048, 'COLOR_GREEN' UNION ALL
        SELECT 4096, 'COLOR_PURPLE' UNION ALL
        SELECT 8192, 'COLOR_TEAL' UNION ALL
        SELECT 16384, 'COLOR_NAVY' UNION ALL
        SELECT 32768, 'SIZE_XS' UNION ALL
        SELECT 65536, 'SIZE_S' UNION ALL
        SELECT 131072, 'SIZE_M' UNION ALL
        SELECT 262144, 'SIZE_L' UNION ALL
        SELECT 524288, 'SIZE_XL' UNION ALL
        SELECT 1048576, 'SIZE_XXL' UNION ALL
        SELECT 2097152, 'MATERIAL_COTTON' UNION ALL
        SELECT 4194304, 'MATERIAL_POLYESTER' UNION ALL
        SELECT 8388608, 'MATERIAL_LINEN' UNION ALL
        SELECT 16777216, 'MATERIAL_DENIM'
),

-- CTE to find matched attributes
MatchedAttributes AS (
    SELECT
        so.id,
        so.order_name,
        so.product,
        so.quantity,
        so.bitmask,
        bc.attribute
    FROM
        store_orderitem so
    CROSS JOIN
        BitmaskConstants bc
    WHERE
        CAST(so.bitmask AS INTEGER) & bc.bit_value <> 0
)

-- Select and aggregate the matched attributes
SELECT
    id,
    order_name,
    product,
    quantity,
    bitmask,
    GROUP_CONCAT(attribute, ', ') AS matched_attributes
FROM
    MatchedAttributes
GROUP BY
    id, order_name, product, quantity, bitmask
ORDER BY
    id;

id	order_name	product	quantity	bitmask	matched_attributes
11	MyOrder	pants	1	2129924	COLOR_LIME, SIZE_XS, MATERIAL_COTTON
12	MyOrder	pants	1	2129924	COLOR_LIME, SIZE_XS, MATERIAL_COTTON
13	MyOrder	pants	1	2359360	COLOR_MAGENTA, SIZE_L, MATERIAL_COTTON
14	MyOrder	pants	1	2359360	COLOR_MAGENTA, SIZE_L, MATERIAL_COTTON