SQL CREATE TABLE contracts(id integer primary key,tenant text,cents integer,amount_minor integer,currency text);
SQL CREATE TABLE checkpoint(last_id integer);
SQL INSERT INTO checkpoint VALUES(0);
SQL CREATE TABLE effects(tenant text,key text,contract_id integer,amount integer,primary key(tenant,key));
SQL CREATE TABLE route(tenant text primary key,version text);
SQL INSERT INTO route VALUES('A','old'),('B','old');
SQL INSERT INTO contracts VALUES(1,'A',100,NULL,NULL),(2,'B',200,NULL,NULL);
SQL select last_id from checkpoint
SQL select id, cents from contracts where id>0 order by id
SQL BEGIN 
SQL update contracts set amount_minor=100, currency='CNY' where id=1
SQL update checkpoint set last_id=1
SQL COMMIT
checkpoint=1
SQL select last_id from checkpoint
SQL select id, cents from contracts where id>1 order by id
SQL BEGIN 
SQL update contracts set amount_minor=200, currency='CNY' where id=2
SQL update checkpoint set last_id=2
SQL COMMIT
checkpoint=2
SQL select last_id from checkpoint
SQL select id, cents from contracts where id>2 order by id
backfill-process-exit=PASS exit=73
SQL select last_id from checkpoint
checkpoint-restart-idempotent=PASS
SQL select count(*) from effects
SQL select cents, 'CNY' from contracts where id=1
SQL select coalesce(amount_minor,cents), coalesce(currency,'CNY') from contracts where id=1
SQL select coalesce(amount_minor,cents), coalesce(currency,'CNY') from contracts where id=1
SQL select cents, 'CNY' from contracts where id=1
SQL select cents, 'CNY' from contracts where id=1
SQL select cents, 'CNY' from contracts where id=2
SQL select coalesce(amount_minor,cents), coalesce(currency,'CNY') from contracts where id=2
SQL select coalesce(amount_minor,cents), coalesce(currency,'CNY') from contracts where id=2
SQL select cents, 'CNY' from contracts where id=2
SQL select cents, 'CNY' from contracts where id=2
difference-report=[{"id": 1, "old": [100, "CNY"], "candidate": [101, "CNY"]}, {"id": 2, "old": [200, "CNY"], "candidate": [201, "CNY"]}]
SQL select count(*) from effects
shadow-no-effects=PASS
SQL BEGIN 
SQL update route set version='new' where tenant='A'
SQL COMMIT
SQL select version from route where tenant='B'
SQL select tenant from contracts where id=1
SQL select contract_id, amount from effects where tenant='A' and key='change-1'
SQL select version from route where tenant='A'
dispatch=A:new
SQL BEGIN 
SQL update contracts set amount_minor=350,currency='CNY',cents=350 where id=1
SQL insert into effects values('A','change-1',1,350)
SQL COMMIT
SQL select tenant from contracts where id=1
SQL select contract_id, amount from effects where tenant='A' and key='change-1'
SQL select tenant from contracts where id=2
SQL select contract_id, amount from effects where tenant='B' and key='change-1'
SQL select version from route where tenant='B'
dispatch=B:old
SQL BEGIN 
SQL update contracts set cents=450,amount_minor=450,currency='CNY' where id=2
SQL insert into effects values('B','change-1',2,450)
SQL COMMIT
SQL select tenant from contracts where id=2
SQL select contract_id, amount from effects where tenant='B' and key='change-1'
SQL BEGIN 
SQL insert into contracts values(3,'A',175,175,'CNY')
SQL COMMIT
SQL select cents, 'CNY' from contracts where id=1
SQL select cents, 'CNY' from contracts where id=3
SQL select tenant from contracts where id=3
SQL select contract_id, amount from effects where tenant='A' and key='change-1'
idempotency-payload-conflict=PASS target=3 amount=350
SQL select cents, 'CNY' from contracts where id=1
SQL select cents, 'CNY' from contracts where id=3
SQL select count(*) from effects
SQL select tenant from contracts where id=1
SQL select contract_id, amount from effects where tenant='A' and key='change-1'
idempotency-payload-conflict=PASS target=1 amount=351
SQL select cents, 'CNY' from contracts where id=1
SQL select cents, 'CNY' from contracts where id=3
SQL select count(*) from effects
conflict-no-effects=PASS original=350 other=175 effects=2
SQL BEGIN 
SQL update route set version='old'
SQL COMMIT
SQL select tenant from contracts where id=1
SQL select contract_id, amount from effects where tenant='A' and key='after-rollback'
SQL select version from route where tenant='A'
dispatch=A:old
SQL BEGIN 
SQL update contracts set cents=375,amount_minor=375,currency='CNY' where id=1
SQL insert into effects values('A','after-rollback',1,375)
SQL COMMIT
SQL select cents, 'CNY' from contracts where id=1
SQL select coalesce(amount_minor,cents), coalesce(currency,'CNY') from contracts where id=1
SQL select cents, 'CNY' from contracts where id=2
SQL select count(*) from effects
rollback-preserves-committed-data=PASS amounts=375,450 effects=3
PASS chapter22
