sketch of kv storage in singlestore.sql

create database kv;
use kv;

/*

possibly add a namespace to all of this
lets start with BLOB for values

GLOBAL:
exists key
delete key

BLOB:
put key = <any value>
get key

LIST: insert order must be maintained
list append key value
list del key value
list clear
list range -> input is start and end (inclusive)
probably will add list insert/list pop in the future

SET:
set add key value
set remove key value
set get key -> returns full set
set intersection -> input (list of keys) output (intersected set)
set union -> input (list of keys) output (union set)
set clear

*/

begin;
insert ignore into keyspace values ("hi");
replace into blobvalues values ("hi", "taylor");
commit;

insert into jsonvalues values ("hi", '["taylor"]')
on duplicate key update v = json_array_append(v, "taylor");

insert ignore into setvalues values ("hi", "taylor");

select t, json_agg(sv.v :> text)
from keyspace ks
left join setvalues sv on ks.k = sv.k
where
  ks.k = "hi";

create table keyspace (
  k text,
  t enum ("blob", "set", "list"),
  primary key (k)
);

create table blobvalues (
  k text,
  v blob,
  primary key (k)
);

create table setvalues (
  k text,
  v blob,
  primary key (k, v)
);

create table listvalues (
  k text,
  i bigint,
  v blob,
  primary key (k, i)
);

create table jsonvalues (
  k text,
  v json,
  primary key (k)
);