"""Read-only SELECT comparisons inside a brand-new memory fixture only."""
import sqlite3,json,platform
ORDER_MAP={'name-up':'name ASC,id ASC','id-down':'id DESC'}
def selected(c,key):
 if key not in ORDER_MAP:raise ValueError('unsupported ordering key')
 return c.execute('SELECT id,name FROM item ORDER BY '+ORDER_MAP[key]).fetchall()
def demo():
 c=sqlite3.connect(':memory:');rows=[(1,'alpha'),(2,"O'Reilly"),(3,'gamma')]
 try:
  c.execute('CREATE TABLE item(id INTEGER PRIMARY KEY,name TEXT)');c.executemany('INSERT INTO item VALUES(?,?)',rows)
  ordinary=c.execute('SELECT id,name FROM item WHERE name=?',("O'Reilly",)).fetchall();assert ordinary==[(2,"O'Reilly")]
  fixture="' OR 1=1 --";unsafe_sql="SELECT id,name FROM item WHERE name='"+fixture+"' ORDER BY id";unsafe=c.execute(unsafe_sql).fetchall();bound=c.execute('SELECT id,name FROM item WHERE name=?',(fixture,)).fetchall();assert unsafe==rows and bound==[]
  literal=c.execute('SELECT ? FROM item ORDER BY id',('name',)).fetchall();assert literal==[('name',)]*3
  ordered=selected(c,'id-down');assert ordered==list(reversed(rows))
  named=c.execute('SELECT id FROM item WHERE name=:value',{'value':'alpha','extra':'unused'}).fetchall();assert named==[(1,)]
  errors=[]
  for name,fn in [('unknown-identifier',lambda:selected(c,'name; anything')),('wrong-qmark-count',lambda:c.execute('SELECT id FROM item WHERE name=?',())),('missing-named-key',lambda:c.execute('SELECT id FROM item WHERE name=:value',{}))]:
   try:fn()
   except (ValueError,sqlite3.ProgrammingError)as e:errors.append({'case':name,'error':str(e)})
   else:raise AssertionError(name)
  assert c.execute('SELECT id,name FROM item ORDER BY id').fetchall()==rows
  return {'python':platform.python_version(),'sqlite':sqlite3.sqlite_version,'authored_rows':rows,'ordinary_quoted_value_result':ordinary,'unsafe_memory_select':{'input':fixture,'sql':unsafe_sql,'result':unsafe},'same_value_bound_result':bound,'select_placeholder_is_literal_not_column':literal,'fixed_allowlist_order_result':ordered,'named_binding_extra_key_ignored_result':named,'rejected_cases':errors,'fixture_rows_unchanged':True,'production_query_or_authorization_security_test':False,'all_assertions_passed':True}
 finally:c.close()
if __name__=='__main__':print(json.dumps(demo(),indent=2))