roach.sql

/*  
This will result in error:
  SQL Error [XX000]: ERROR: unable to encode table key: *tree.DJSON

on insert
*/  
CREATE TABLE json_events2 (
  uuid            STRING PRIMARY KEY,
  value           JSONB,
  event_type      STRING,
  scenario_id     STRING,
  conversation_id STRING,
  account_id      STRING,
  sequence_id     STRING,
  unknown         JSONB,
  timestamp       TIMESTAMP WITH TIME ZONE
);


CREATE INDEX IF NOT EXISTS json_debug_index  ON json_events2 (scenario_id, timestamp desc, event_type) STORING (uuid, conversation_id, sequence_id, value, unknown);


ALTER TABLE json_events2 ADD COLUMN IF NOT EXISTS version INT;

update json_events2
set version = (unknown->>'scenarioIdVersion')::INT
where version is null;

insert into json_events2 (uuid, value, event_type, scenario_id, conversation_id, account_id, sequence_id, version, unknown, timestamp) values 
('Cg7AK5YxVEJiTv6bYnHp23', '{"a":"value"}', 'eventType', 'scenarioId', 'conversationId', 'accountId', 'sequenceId', '1', '{"random":"shit","scenarioIdVersion":"1","random2":"shit2"}', '2018-09-22 21:38:41.058') 


/*
  This will run ok
*/

CREATE TABLE json_events3 (
  uuid            STRING PRIMARY KEY,
  value           JSONB,
  event_type      STRING,
  scenario_id     STRING,
  conversation_id STRING,
  account_id      STRING,
  sequence_id     STRING,
  unknown         JSONB,
  timestamp       TIMESTAMP WITH TIME ZONE
);


ALTER TABLE json_events3 ADD COLUMN IF NOT EXISTS version INT;

update json_events3
set version = (unknown->>'scenarioIdVersion')::INT
where version is null;

CREATE INDEX IF NOT EXISTS json_debug_index  ON json_events3 (scenario_id, timestamp desc, event_type) STORING (uuid, conversation_id, sequence_id, value, unknown);

insert into json_events3 (uuid, value, event_type, scenario_id, conversation_id, account_id, sequence_id, version, unknown, timestamp) values 
('Cg7AK5YxVEJiTv6bYnHp23', '{"a":"value"}', 'eventType', 'scenarioId', 'conversationId', 'accountId', 'sequenceId', '1', '{"random":"shit","scenarioIdVersion":"1","random2":"shit2"}', '2018-09-22 21:38:41.058')