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 20e553a3b8a098347a40c0f3262682a9d31f07cc Author: Greg Stein <[email protected]> AuthorDate: Thu Oct 2 01:52:32 2025 -0500 Update schema to include Election (expected) open/close times. * schema.sql: two new columns in "election", and a trigger * queries.yaml: adjust queries to return more columns from "election" * pages.py: revise a couple debug Election instances * election.py: accept/return the new open/close times --- v3/queries.yaml | 9 +++++++-- v3/schema.sql | 20 ++++++++++++++++++++ v3/server/pages.py | 16 ++++++++++++++-- v3/steve/election.py | 12 ++++++++---- 4 files changed, 49 insertions(+), 8 deletions(-) diff --git a/v3/queries.yaml b/v3/queries.yaml index 0125855..6572b37 100644 --- a/v3/queries.yaml +++ b/v3/queries.yaml @@ -85,8 +85,11 @@ election: # the ORDER BY. However, with few revotes, that sort is not # expensive. Collect some data. Or maybe people can lobby. + # Which elections are open for voting by PID? + # Note: SALT and OPENED_KEY are never returned. q_open_to_me: | - SELECT DISTINCT e.eid, e.title + SELECT DISTINCT e.eid, e.title, e.owner_pid, e.authz, e.closed, + e.open_at, e.close_at FROM mayvote m JOIN issue i ON m.iid = i.iid JOIN election e ON i.eid = e.eid @@ -94,8 +97,10 @@ election: AND e.salt IS NOT NULL AND (e.closed IS NULL OR e.closed = 0) ORDER BY e._ROWID_ + + # Which elections were created by PID? q_owned: | - SELECT eid, title, authz, closed + SELECT eid, title, authz, closed, open_at, close_at FROM election WHERE owner_pid = ? diff --git a/v3/schema.sql b/v3/schema.sql index 05bc4bb..b9b429d 100644 --- a/v3/schema.sql +++ b/v3/schema.sql @@ -90,6 +90,17 @@ CREATE TABLE election ( opened). 1 for closed (implies it was formerly-opened). */ closed INTEGER CHECK (closed IS NULL OR closed IN (0, 1)), + /* The approximate times this Election will be opened and closed, + if known. NULL if unknown. These are purely advisory, for humans, + and have no effect upon the actual Election operation. Look + to OPENED_KEY and CLOSED for the current Election state. + + Note: the "prevent_open_close_update" trigger will prevent these + two values from changing once an election is closed. They never + need to be set, but once an election is closed: they are fixed. */ + open_at INTEGER, /* seconds since epoch */ + close_at INTEGER, /* seconds since epoch */ + /* Enforce/declare/document relationships. */ FOREIGN KEY (owner_pid) REFERENCES person(pid) @@ -98,6 +109,15 @@ CREATE TABLE election ( ) STRICT; +/* For posterity, do not allow changes to the time fields, once closed. */ +CREATE TRIGGER prevent_open_close_update +BEFORE UPDATE OF open_at, close_at ON election +FOR EACH ROW +WHEN OLD.closed = 1 +BEGIN + SELECT RAISE(ABORT, 'Cannot modify open_at or close_at when election is closed'); +END; + /* --------------------------------------------------------------------- */ /* The set of Issues to vote upon for a given Election. */ diff --git a/v3/server/pages.py b/v3/server/pages.py index 95e9a06..c394e0e 100644 --- a/v3/server/pages.py +++ b/v3/server/pages.py @@ -38,6 +38,7 @@ DB_FNAME = THIS_DIR / APP.cfg.db sys.path.insert(0, str(THIS_DIR.parent)) import steve.election +import steve.crypto async def signin_info(): @@ -64,12 +65,23 @@ async def home_page(): @APP.use_template('templates/voter.ezt') async def voter_page(): with asfpy.stopwatch.Stopwatch(): + # These are lists of EasyDict instances for each Election. election = steve.election.Election.open_to_pid(DB_FNAME, 'gstein') owned = steve.election.Election.owned_elections(DB_FNAME, 'gstein') ### for now - election = [ edict(eid='123', title='test election') ] - owned = [ edict(eid='456', title='another', authz=None, closed=None) ] + def new_test_election(): + return edict( + eid=steve.crypto.create_id(), + title=f'Title blah:{steve.crypto.create_id()}', + owner_pid='alice', + authz=None, + closed=None, + open_at=None, + close_at=None, + ) + election = [ new_test_election() ] + owned = [ new_test_election() ] result = await signin_info() result.title = 'Voting' diff --git a/v3/steve/election.py b/v3/steve/election.py index 683689b..bd36bd9 100644 --- a/v3/steve/election.py +++ b/v3/steve/election.py @@ -366,15 +366,19 @@ class Election: return j and json.loads(j) @classmethod - def create(cls, db_fname, title, owner_pid, authz=None): + def create(cls, db_fname, title, owner_pid, + authz=None, open_at=None, close_at=None): # Open in autocommit conn = sqlite3.connect(db_fname, isolation_level=None) while True: eid = crypto.create_id() try: - conn.execute('INSERT INTO election (eid, title, owner_pid, authz)' - ' VALUES (?, ?, ?, ?)', - (eid, title, owner_pid, authz, )) + conn.execute('INSERT INTO election' + ' (eid, title, owner_pid,' + ' authz, open_at, close_at)' + ' VALUES (?, ?, ?, ?, ?, ?)', + (eid, title, owner_pid, + authz, open_at, close_at)) break except sqlite3.IntegrityError: _LOGGER.debug('EID conflict(!!) ... trying again.')
