"""Saved plan membership and conditional apply, disposable SQLite only."""
import json,sqlite3,hashlib
plan={'id':'P7','rows':[{'id':12,'old':'A','new':'B','version':1},{'id':13,'old':'A','new':'B','version':1}],'reason':'Illustrative category correction','semantics':'independent rows'}
encoding=json.dumps(plan,sort_keys=True,separators=(',',':'),ensure_ascii=False).encode('utf-8');fingerprint=hashlib.sha256(encoding).hexdigest()
def fresh():
 c=sqlite3.connect(':memory:');c.executescript('CREATE TABLE products(id INTEGER PRIMARY KEY,category TEXT,version INTEGER);CREATE TABLE results(plan TEXT,product INTEGER,outcome TEXT,PRIMARY KEY(plan,product));INSERT INTO products VALUES(12,\'A\',1),(13,\'A\',1);');c.execute("UPDATE products SET category='C',version=2 WHERE id=13");c.execute("INSERT INTO products VALUES(14,'A',1)");c.commit();return c
def snapshot(c):return c.execute('SELECT * FROM products ORDER BY id').fetchall()
def apply_independent(c):
 out=[]
 for r in plan['rows']:
  c.execute('BEGIN IMMEDIATE');prior=c.execute('SELECT outcome FROM results WHERE plan=? AND product=?',('P7',r['id'])).fetchone()
  if prior:c.rollback();out.append({'id':r['id'],'result':'recorded_'+prior[0]});continue
  changed=c.execute('UPDATE products SET category=?,version=version+1 WHERE id=? AND category=? AND version=?',(r['new'],r['id'],r['old'],r['version'])).rowcount
  outcome='applied' if changed==1 else 'conflict';c.execute('INSERT INTO results VALUES(?,?,?)',('P7',r['id'],outcome));c.commit();out.append({'id':r['id'],'result':outcome})
 return out
c=fresh();first=apply_independent(c);after=snapshot(c);restart=apply_independent(c)
assert after==[(12,'B',2),(13,'C',2),(14,'A',1)] and [x['result'] for x in first]==['applied','conflict']
assert restart==[{'id':12,'result':'recorded_applied'},{'id':13,'result':'recorded_conflict'}]
d=fresh()
try:
 d.execute('BEGIN IMMEDIATE')
 for r in plan['rows']:
  if d.execute('UPDATE products SET category=?,version=version+1 WHERE id=? AND category=? AND version=?',(r['new'],r['id'],r['old'],r['version'])).rowcount!=1:raise ValueError('plan conflict')
 d.commit()
except ValueError:d.rollback()
all_or_nothing=snapshot(d);assert all_or_nothing==[(12,'A',1),(13,'C',2),(14,'A',1)]
report={'plan':plan,'sha256OfDefinedEncoding':fingerprint,'firstApply':first,'afterIndependent':after,'restart':restart,'allOrNothingAfterConflict':all_or_nothing,'scope':'Atomic SQLite row/data-result transactions, no external effect or production approval service'}
if __name__=='__main__':print(json.dumps(report,indent=2))