This is an automated email from the ASF dual-hosted git repository. gstein pushed a commit to branch trunk in repository https://gitbox.apache.org/repos/asf/steve.git
commit dab12c6ec30f7d75c429f4e0a114919b9858abd5 Author: Greg Stein <[email protected]> AuthorDate: Fri Sep 26 08:52:45 2025 -0500 Switch to asfpy.db rather than our layer. steve/db.py was a precursor to asfpy.db. The asfpy version is more advanced, with automatic column naming creating more flexibility in the queries. Switch to loading Election-related queries from the .yaml * remove steve/db.py; obsolete * election.py: - adjust imports - load queries from the .yaml - remove old manual query definitions from the codebase - adjust parameters to all the .perform() invocations - even the "manual" queries get column attributes, unlike the original steve/db.py approach. Switch tuple-indexing to attribute access. * persondb.py: trim the old db import --- v3/steve/db.py | 108 ---------------------------------- v3/steve/election.py | 161 +++++++++++++++------------------------------------ v3/steve/persondb.py | 2 - 3 files changed, 48 insertions(+), 223 deletions(-) diff --git a/v3/steve/db.py b/v3/steve/db.py deleted file mode 100644 index 3df4359..0000000 --- a/v3/steve/db.py +++ /dev/null @@ -1,108 +0,0 @@ -# -# Licensed to the Apache Software Foundation (ASF) under one or more -# contributor license agreements. See the NOTICE file distributed with -# this work for additional information regarding copyright ownership. -# The ASF licenses this file to You under the Apache License, Version 2.0 -# (the "License"); you may not use this file except in compliance with -# the License. You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -# ---- -# -# Convenience wrapper for working with a SQLite database. -# -# This wrapper has several primary purposes: -# -# 1. Easily create a cursor for each statement that might be -# executed by the application. -# 2. Remember the specific string object for those statements, -# and re-use them in cursor.execute() for better performance. -# 3. Rows fetched with SELECT statements are wrapped into a -# namedtuple() instance, such that columns can be easily -# accessed as attributes or numerically indexed as a tuple, -# - -import sqlite3 -import collections -import functools - - -class DB: - - def __init__(self, fname): - - def row_factory(cursor, row): - "Possibly apply namedtuple() to the returned row." - return self.factories.get(cursor, lambda *row: row)(*row) - - # Note: isolation_level=None means autocommit mode. - self.conn = sqlite3.connect(fname, isolation_level=None) - self.conn.row_factory = row_factory - - # CURSOR : FACTORY - self.factories = { } - - def _cursor_for(self, statement): - return self.conn.cursor(functools.partial(NamedTupleCursor, - statement)) - - def add_query(self, table, query): - "Return a cursor to use for this QUERY against TABLE." - - # The query must select all columns. - assert query[:9].lower() == 'select * ' - - # Get all column names for TABLE. - cur = self.conn.execute(f'select * from {table} limit 1') - names = [ info[0] for info in cur.description ] - - # We don't need the results, but cannot leave the cursor hanging, - # as it establishes a lock on this table. This likely closes as - # this method exits, but let's not rely upon that. - cur.close() - - # Create a factory for turning rows into namedtuples. - factory = collections.namedtuple(f'row_factory_{len(self.factories)}', - names, rename=True, - module=DB.__module__) - - # Register the row-wrapper factory for this cursor. - cursor = self._cursor_for(query) - self.factories[cursor] = factory - return cursor - - def add_statement(self, statement): - "Return a cursor for use with a DML SQL statement." - - # Note: rows should not be returned for this statement, and - # (thus) the row_factory should not be called. If called, the - # original row will be returned. - return self._cursor_for(statement) - - -class NamedTupleCursor(sqlite3.Cursor): - - def __init__(self, statement, *args, **kw): - super().__init__(*args, **kw) - self.statement = statement - - def perform(self, params=()): - "Perform the statement with PARAMs, or prepare the query." - - # Use the exact same STATEMENT each time. Python's SQLite module - # caches the parsed statement, if the string is the same object. - self.execute(self.statement, params) - - def first_row(self, params=()): - "Helper method to fetch the first row of a query." - self.perform(params) - row = self.fetchone() - _ = self.fetchall() # run the cursor to completion; should be empty - return row diff --git a/v3/steve/election.py b/v3/steve/election.py index 09fa972..4baa2a6 100644 --- a/v3/steve/election.py +++ b/v3/steve/election.py @@ -23,13 +23,18 @@ import logging import json import sqlite3 +import pathlib + +import asfpy.db from . import crypto -from . import db from . import vtypes _LOGGER = logging.getLogger(__name__) +THIS_DIR = pathlib.Path(__file__).resolve().parent +QUERIES = THIS_DIR.parent / 'queries.yaml' + class Election: @@ -41,86 +46,13 @@ class Election: def __init__(self, db_fname, eid): _LOGGER.debug(f'Opening election ID "{eid}"') - ### switch to asfpy.db - self.db = db.DB(db_fname) + self.db = asfpy.db.DB(db_fname, + yaml_fname=QUERIES, yaml_section='election') self.eid = eid - # Construct cursors for all operations. - self.c_salt_mayvote = self.db.add_statement( - 'UPDATE mayvote SET salt = ? WHERE _ROWID_ = ?') - self.c_open = self.db.add_statement( - 'UPDATE election SET salt = ?, opened_key = ?' - ' WHERE eid = ?') - self.c_close = self.db.add_statement( - 'UPDATE election SET closed = 1' - ' WHERE eid = ?') - self.c_add_issue = self.db.add_statement( - '''INSERT INTO issue VALUES (?, ?, ?, ?, ?, ?) - ON CONFLICT DO UPDATE SET - title=excluded.title, - description=excluded.description, - type=excluded.type, - kv=excluded.kv - ''') - self.c_delete_issue = self.db.add_statement( - 'DELETE FROM issue WHERE iid = ?') - self.c_add_vote = self.db.add_statement( - 'INSERT INTO vote VALUES (NULL, ?, ?)') - self.c_add_mayvote = self.db.add_statement( - 'INSERT INTO mayvote (pid, iid, salt) VALUES (?, ?, NULL)') - self.c_add_mayvote_all = self.db.add_statement( - 'INSERT INTO mayvote (pid, iid, salt)' - ' SELECT ?, iid, NULL FROM issue WHERE eid = ?') - self.c_delete_mayvote = self.db.add_statement( - '''DELETE FROM mayvote - WHERE iid IN ( - SELECT iid - FROM issue - WHERE eid = ? - )''') - self.c_delete_issues = self.db.add_statement( - 'DELETE FROM issue WHERE eid = ?') - self.c_delete_election = self.db.add_statement( - 'DELETE FROM election WHERE eid = ?') - - # Cursors for running queries. - self.q_metadata = self.db.add_query('election', - 'SELECT * FROM election WHERE eid = ?') - self.q_issues = self.db.add_query('issue', - 'SELECT * FROM issue WHERE eid = ? ORDER BY iid') - self.q_get_issue = self.db.add_query('issue', - 'SELECT * FROM issue WHERE iid = ?') - self.q_get_mayvote = self.db.add_query('mayvote', - 'SELECT * FROM mayvote WHERE pid = ? AND iid = ?') - self.q_tally = self.db.add_query('mayvote', - 'SELECT * FROM mayvote WHERE iid = ?') - - # Manual queries: these queries do not follow the 'SELECT * ' - # pattern, so we cannot use .add_query() and will not have - # attribute access to the row results while running the query. - self.m_find_issues = self.db.add_statement( - '''SELECT m.* - FROM mayvote m - JOIN issue i ON m.iid = i.iid - WHERE m.pid = ? AND i.eid = ? - ''') - self.m_has_voted = self.db.add_statement( - '''SELECT 1 FROM vote - WHERE vote_token = ? - LIMIT 1 - ''') - self.m_recent_vote = self.db.add_statement( - '''SELECT ciphertext FROM vote - WHERE vote_token = ? - ORDER BY _ROWID_ DESC - LIMIT 1 - ''') - self.m_all_issues = self.db.add_statement( - '''SELECT m._ROWID_ - FROM mayvote m - JOIN issue i ON m.iid = i.iid - WHERE i.eid = ?; - ''') + def __getattr__(self, name): + "Proxy the cursors." + return self.__dict__.get(name, getattr(self.db, name)) def delete(self): "Delete this Election and its Issues and Person/Issue pairs." @@ -133,13 +65,13 @@ class Election: # Order these things because of referential integrity. # Delete all rows that refer to Issues within this Election. - self.c_delete_mayvote.perform((self.eid,)) + self.c_delete_mayvote.perform(self.eid) # Now, delete all the Issues that are part of this Election. - self.c_delete_issues.perform((self.eid,)) + self.c_delete_issues.perform(self.eid) # Finally, remove the Election itself. - self.c_delete_election.perform((self.eid,)) + self.c_delete_election.perform(self.eid) self.db.conn.execute('COMMIT') @@ -163,7 +95,7 @@ class Election: print('SALT:', salt) print('KEY:', opened_key) - self.c_open.perform((salt, opened_key, self.eid)) + self.c_open.perform(salt, opened_key, self.eid) def gather_election_data(self, pdb): "Gather a definition of this election for keying and anti-tamper." @@ -174,10 +106,10 @@ class Election: # NOTE: all assembly of rows must use a repeatable ordering. - md = self.q_metadata.first_row((self.eid,)) + md = self.q_metadata.first_row(self.eid) mdata = md.eid + md.title - self.q_issues.perform((self.eid,)) + self.q_issues.perform(self.eid) # Use an f-string to render "None" if a column is NULL. idata = ''.join(f'{i.iid}{i.title}{i.description}{i.type}{i.kv}' for i in self.q_issues.fetchall()) @@ -197,7 +129,7 @@ class Election: assert self.is_open() # Simple tweak of the metadata to close the Election. - self.c_close.perform((self.eid,)) + self.c_close.perform(self.eid) def add_salts(self): "Set the SALT column in the MAYVOTE table." @@ -208,20 +140,21 @@ class Election: # Use M_ALL_ISSUES to iterate over all Person/Issue mappings # in this Election (specified by EID). self.db.conn.execute('BEGIN TRANSACTION') - self.m_all_issues.perform((self.eid,)) + self.m_all_issues.perform(self.eid) for mayvote in self.m_all_issues.fetchall(): # MAYVOTE is a 1-tuple: _ROWID_ + #print('COLUMNS:', dir(mayvote)) # Use a distinct cursor to insert the SALT value. salt = crypto.gen_salt() - self.c_salt_mayvote.perform((salt, mayvote[0])) + self.c_salt_mayvote.perform(salt, mayvote.rowid) self.db.conn.execute('COMMIT') def get_metadata(self): "Return basic metadata about this Election." - md = self.q_metadata.first_row((self.eid,)) + md = self.q_metadata.first_row(self.eid) # NOTE: do not return the SALT # note: likely: never return opened_key @@ -231,7 +164,7 @@ class Election: "Return TITLE, DESCRIPTION, TYPE, and KV for issue IID." # NEVER return issue.salt - issue = self.q_get_issue.first_row((iid,)) + issue = self.q_get_issue.first_row(iid) return (issue.title, issue.description, issue.type, self.json2kv(issue.kv)) @@ -243,8 +176,8 @@ class Election: # If we ADD, then SALT will be NULL. If we UPDATE, then it will not # be touched (it should be NULL). - self.c_add_issue.perform((iid, self.eid, title, description, vtype, - self.kv2json(kv))) + self.c_add_issue.perform(iid, self.eid, title, description, vtype, + self.kv2json(kv)) def delete_issue(self, iid): "Delete the Issue designated by IID." @@ -252,16 +185,16 @@ class Election: # Can only delete Issues before the Election is OPEN. assert self.is_editable() - self.c_delete_issue.perform((iid,)) + self.c_delete_issue.perform(iid) def list_issues(self): "Return ordered (IID, TITLE, DESCRIPTION, TYPE, KV) for all ISSUES." def extract_issue(row): - # NOTE: the SALT column is omitted. It should never be exposed. - return row[:4] + (self.json2kv(row.kv),) + return (row.iid, row.title, row.description, row.type, + self.json2kv(row.kv),) - self.q_issues.perform((self.eid,)) + self.q_issues.perform(self.eid) return [ extract_issue(row) for row in self.q_issues.fetchall() ] def add_voter(self, pid: str, iid: str | None = None) -> None: @@ -271,9 +204,9 @@ class Election: assert self.is_editable() if iid: - self.c_add_mayvote.perform((pid, iid,)) + self.c_add_mayvote.perform(pid, iid) else: - self.c_add_mayvote_all.perform((pid, self.eid,)) + self.c_add_mayvote_all.perform(pid, self.eid) def add_vote(self, pid: str, iid: str, votestring: str): "Add VOTESTRING as the (latest) vote by PID for IID." @@ -281,17 +214,17 @@ class Election: # The Election should be open. assert self.is_open() - md = self.q_metadata.first_row((self.eid,)) + md = self.q_metadata.first_row(self.eid) ### validate VOTESTRING for ISSUE.TYPE voting - mayvote = self.q_get_mayvote.first_row((pid, iid,)) + mayvote = self.q_get_mayvote.first_row(pid, iid) vote_token = crypto.gen_vote_token(md.opened_key, pid, iid, mayvote.salt) # Pass MAYVOTE.SALT for PBKDF. ciphertext = crypto.create_vote(vote_token, mayvote.salt, votestring) - self.c_add_vote.perform((vote_token, ciphertext)) + self.c_add_vote.perform(vote_token, ciphertext) def tally_issue(self, iid): """Return the results for a given ISSUE-ID. @@ -307,16 +240,16 @@ class Election: # The Election should be closed. assert self.is_closed() - md = self.q_metadata.first_row((self.eid,)) + md = self.q_metadata.first_row(self.eid) # Need the issue TYPE - issue = self.q_get_issue.first_row((iid,)) + issue = self.q_get_issue.first_row(iid) # Accumulate all MOST-RECENT votes for Issue IID. votes = [ ] # Use mayvote to determine all potential voters for Issue IID. - self.q_tally.perform((iid,)) + self.q_tally.perform(iid) for mayvote in self.q_tally.fetchall(): # Each row is: PID, IID, SALT @@ -326,9 +259,9 @@ class Election: ) # We don't need/want all columns, so only pick CIPHERTEXT. - row = self.m_recent_vote.first_row((vote_token,)) + row = self.m_recent_vote.first_row(vote_token) votestring = crypto.decrypt_votestring( - vote_token, mayvote.salt, row[0], + vote_token, mayvote.salt, row.ciphertext, ) votes.append(votestring) @@ -347,21 +280,23 @@ class Election: # The Election should be open. assert self.is_open() - md = self.q_metadata.first_row((self.eid,)) + md = self.q_metadata.first_row(self.eid) voted_upon = { } - self.m_find_issues.perform((pid, self.eid,)) + self.m_find_issues.perform(pid, self.eid) for row in self.m_find_issues.fetchall(): + #print('COLUMNS:', dir(row)) + # Query is mayvote.* ... so ROW is: PID, IID, SALT vote_token = crypto.gen_vote_token( - md.opened_key, pid, row[1], row[2] + md.opened_key, pid, row.iid, row.salt, ) # Is any vote present? (wicked fast) - voted = self.m_has_voted.first_row((vote_token,)) + voted = self.m_has_voted.first_row(vote_token) - voted_upon[row[1]] = (voted is not None) + voted_upon[row.iid] = (voted is not None) return voted_upon @@ -370,7 +305,7 @@ class Election: # The Election should be open. assert self.is_open() - md = self.q_metadata.first_row((self.eid,)) + md = self.q_metadata.first_row(self.eid) # Compute an opened_key based on the current data. edata = self.gather_election_data(pdb) @@ -398,7 +333,7 @@ class Election: def get_state(self): "Derive our election state from the METADATA table." - md = self.q_metadata.first_row((self.eid,)) + md = self.q_metadata.first_row(self.eid) if md.closed == 1: assert md.salt is not None and md.opened_key is not None return self.S_CLOSED diff --git a/v3/steve/persondb.py b/v3/steve/persondb.py index d43c168..7b9acd0 100644 --- a/v3/steve/persondb.py +++ b/v3/steve/persondb.py @@ -21,8 +21,6 @@ import pathlib -from . import db - import asfpy.db THIS_DIR = pathlib.Path(__file__).resolve().parent
