"""Frame membership oracles over authored irregular numeric keys and separate peers."""
import sqlite3,json,platform
def demo():
 c=sqlite3.connect(':memory:');rows=[('a',0,10),('b',1,20),('c',5,30),('d',6,40),('e',10,50)]
 try:
  c.execute('CREATE TABLE v(id TEXT,t INTEGER,amount INTEGER)');c.executemany('INSERT INTO v VALUES(?,?,?)',rows)
  evidence={}
  for kind in ['ROWS','RANGE']:
   frame=kind+' BETWEEN 2 PRECEDING AND CURRENT ROW'
   sql='SELECT id,t,SUM(amount) OVER(ORDER BY t '+frame+'),group_concat(id) OVER(ORDER BY t '+frame+') FROM v ORDER BY t'
   actual=c.execute(sql).fetchall();expected=[]
   for i,(name,t,val)in enumerate(rows):
    members=rows[max(0,i-2):i+1]if kind=='ROWS'else[x for x in rows if t-2<=x[1]<=t]
    expected.append((name,t,sum(x[2]for x in members),','.join(x[0]for x in members)))
   assert actual==expected;evidence[kind]={'sql':sql,'result':actual,'independent_python_membership_oracle_equal':True}
  assert evidence['ROWS']['result'][2]==('c',5,60,'a,b,c') and evidence['RANGE']['result'][2]==('c',5,30,'c')
  c.execute('DELETE FROM v');peers=[('a',0,10),('b',1,20),('c',1,30),('d',3,40)];c.executemany('INSERT INTO v VALUES(?,?,?)',peers)
  default=c.execute('SELECT id,t,SUM(amount) OVER(ORDER BY t) FROM v ORDER BY t,id').fetchall()
  assert default==[(name,t,sum(x[2]for x in peers if x[1]<=t))for name,t,val in peers]
  deterministic=c.execute('SELECT id,t,SUM(amount) OVER(ORDER BY t,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM v ORDER BY t,id').fetchall()
  assert deterministic==[('a',0,10),('b',1,30),('c',1,60),('d',3,100)]
  default_with_id=c.execute('SELECT id,t,SUM(amount) OVER(ORDER BY t,id) FROM v ORDER BY t,id').fetchall();assert default_with_id==deterministic
  assert default[1][2]==default[2][2]==60 and deterministic[1][2]==30
  try:c.execute('SELECT SUM(amount) OVER(ORDER BY t,id RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) FROM v').fetchall()
  except sqlite3.OperationalError as e:bounded_range_two_keys_error=str(e)
  else:raise AssertionError('numeric bounded RANGE requires one expression')
  return {'python':platform.python_version(),'sqlite':sqlite3.sqlite_version,'authored_unique_numeric_rows':rows,'irregular_numeric_frames':evidence,'separate_peer_rows':peers,'default_order_t_range':default,'deterministic_rows_order_t_id':deterministic,'default_order_t_id_changes_peer_contract':default_with_id,'two_key_bounded_range_error':bounded_range_two_keys_error,'current_row_included':True,'calendar_timezone_null_collation_prediction_tested':False,'all_assertions_passed':True}
 finally:c.close()
if __name__=='__main__':print(json.dumps(demo(),indent=2))