desired-sql-to-kafka.md

CREATE TABLE widgets (id INT PRIMARY KEY, name VARCHAR);

CREATE TABLE prices (id INT PRIMARY KEY, widget_id INT, price INT);

INSERT INTO widgets VALUES (1, 'foo'), (2, 'bor'), (3, 'baz');

Partition   Key         Value
{"id":1}    {"id":1}    {"id":1,"name":"foo"}
{"id":2}    {"id":2}    {"id":2,"name":"bor"}
{"id":3}    {"id":3}    {"id":3,"name":"baz"}

INSERT INTO prices VALUES (1, 2, 5), (2, 1, 3);

Partition   Key         Value
{"id":2}    {"id":2}    {"id":2,"name":"bor","price":{"id":1,"widget_id":2,"price":5}}
{"id":1}    {"id":1}    {"id":1,"name":"foo","price":{"id":2,"widget_id":1,"price":3}}

UPDATE widgets SET name = 'bar' WHERE id = 2;

Partition   Key         Value
{"id":2}    {"id":2}    {"id":2,"name":"bar","price":{"id":1,"widget_id":2,"price":5}}

UPDATE prices SET price = 4 WHERE id = 2;

Partition   Key         Value
{"id":1}    {"id":1}    {"id":1,"name":"foo","price":{"id":2,"widget_id":1,"price":4}}

INSERT INTO prices VALUES (3, 3, 5);

Partition   Key         Value
{"id":3}    {"id":3}    {"id":3,"name":"baz","price":{"id":3,"widget_id":3,"price":5}}

INSERT INTO widgets VALUES (4, 'qux');

Partition   Key         Value
{"id":4}    {"id":4}    {"id":4,"name":"qux"}

UPDATE prices SET widget_id = 4 WHERE id = 3;

Partition   Key         Value
{"id":3}    {"id":3}    {"id":3,"name":"baz"} // associated price deleted/FK switched to 4
{"id":4}    {"id":4}    {"id":4,"name":"qux","price":{"id":3,"widget_id":4,"price":5}}

DELETE FROM prices WHERE widget_id = 1;

Partition   Key         Value
{"id":1}    {"id":1}    {"id":1,"name":"foo"} // price deleted

DELETE FROM prices WHERE id = 1;

Partition   Key         Value
{"id":1}    {"id":1}    NULL // widget deleted

UPDATE widgets SET id = 1 WHERE id = 2;

Partition   Key         Value
{"id":2}    {"id":2}    NULL // widget PK changed, delete old so compaction happens
{"id":1}    {"id":1}    {"id":1,"name":"bar"} // widget PK changed, price hasn't caught up

UPDATE prices SET widget_id = 1 WHERE widget_id = 2;

Partition   Key         Value
{"id":1}    {"id":1}    {"id":1,"name":"bar","price":{"id":1,"widget_id":1,"price":5}}