machine/sql/explore.sql

-- First start a local cockroachdb instance:
--
--   cockroach start-single-node --insecure --listen-addr=127.0.0.1
--
--   cockroach sql --insecure --host=127.0.0.1 < ./explore.sql
--
-- You should be able to run this file against the same database more than once
-- w/o error.
--
DROP TABLE IF EXISTS Machines;

CREATE TABLE IF NOT EXISTS Machines (
	dimensions jsonb NOT NULL,
	maintenance_mode STRING NOT NULL DEFAULT '',
	is_quarantined bool NOT NULL DEFAULT FALSE,
	powercycle bool NOT NULL DEFAULT FALSE,
	machine_id STRING PRIMARY KEY AS (
dimensions -> 'id' ->> 0) STORED,
	INDEX by_powercycle (powercycle),
	INVERTED INDEX dimensions_gin (dimensions)
);

INSERT INTO Machines (
	powercycle,
	dimensions)
VALUES (
	FALSE,
	'{ "id": ["skia-e-linux-150"], "cores": ["6"], "cpu": ["x86","x86-64"], "os": ["Linux","Debian","Debian-11","Debian-11.2"]}'),
(
	TRUE,
	'{ "id": ["skia-e-linux-151"], "cores": ["6"], "cpu": ["x86","x86-64"], "os": ["Linux","Debian","Debian-11","Debian-11.6"]}'),
(
	TRUE,
	'{ "id": ["skia-e-mac-201"],  "cores": ["8"], "cpu": ["arm","arm-64"], "os": ["Mac","Mac-12","Mac-12.1"]}')
ON CONFLICT
	DO NOTHING;

SELECT
	'Show how to do a single param query' AS desc;

SELECT
	machine_id
FROM
	Machines
WHERE
	-- Note that value of the key-value pair still needs to be inside an array:
	dimensions @> '{"cpu": ["arm-64"]}';

SELECT
	'Demonstrate we are not doing full table scans.' AS desc;

EXPLAIN
SELECT
	machine_id
FROM
	Machines
WHERE
	dimensions @> '{"cpu": ["x86-64"]}'
	AND dimensions @> '{"os": ["Debian-11.6"]}';

SELECT
	machine_id
FROM
	Machines
WHERE
	dimensions @> '{"cpu": ["x86-64"]}'
	AND dimensions @> '{"os": ["Debian-11.6"]}';

SELECT
	'Force Quarantine a machine' AS desc;

UPDATE
	Machines
SET
	is_quarantined = TRUE
WHERE
	dimensions @> '{"id": ["skia-e-linux-150"]}';

SELECT
	machine_id
FROM
	Machines
WHERE
	is_quarantined = FALSE;

SELECT
	'List powercycle' AS desc;

SELECT
	machine_id
FROM
	Machines@by_powercycle
WHERE
	powercycle = TRUE;

SELECT
	'Demonstrate we are not doing full table scans.' AS desc;

EXPLAIN
SELECT
	machine_id
FROM
	Machines@by_powercycle
WHERE
	powercycle = TRUE;

SELECT
	'Get by id' AS desc;

SELECT
	machine_id
FROM
	Machines
WHERE
	machine_id = 'skia - e - linux - 151';