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
The following commit(s) were added to refs/heads/trunk by this push:
new d40f6ce Change: one database for all elections, rather than
per-election.
d40f6ce is described below
commit d40f6ce383f211391e617a595bbded922dcf8efa
Author: Greg Stein <[email protected]>
AuthorDate: Fri Sep 19 23:39:00 2025 -0500
Change: one database for all elections, rather than per-election.
* schema.sql: update for new design
- METADATA becomes ELECTIONS
- add OWNER_PID and AUTHZ
- add EID to the ISSUES table
* crypto.shuffle() updated for py3.11 change. Needs further work.
* election.py:
- stash the EID for this Election; use as needed
- update some cursors and queries for the schema change
- update .perform() calls to add the EID as needed
- .add_issue() needs an EID parameter
* tweak check_coverage for the schema and API changes
---
v3/schema.sql | 46 ++++++++++++++++++++++++++++++++++++----------
v3/steve/crypto.py | 3 ++-
v3/steve/election.py | 24 ++++++++++++++----------
v3/test/check_coverage.py | 14 +++++++-------
4 files changed, 59 insertions(+), 28 deletions(-)
diff --git a/v3/schema.sql b/v3/schema.sql
index 800af3a..67ddfa5 100644
--- a/v3/schema.sql
+++ b/v3/schema.sql
@@ -17,13 +17,15 @@
* under the License.
*/
-/* ### TBD DOCCO. */
+/* There is a per-install SQLite database containing all election data
+ for the site. This file defines/constructs the schema of that database.
+ */
-/* ### $ sqlite3 testing.db < schema.sql
+/* ### $ sqlite3 steve.db < steve/v3/schema.sql
###
### OR:
### >>> import sqlite3
- ### >>> conn = sqlite3.connect('testing.db')
+ ### >>> conn = sqlite3.connect('steve.db')
### >>> conn.executescript(open('schema.sql').read())
###
### ? maybe: conn.commit() and/or conn.close() ... the DML statements
@@ -34,7 +36,6 @@
/* --------------------------------------------------------------------- */
/* Various metadata about the Election contained in this database.
- Only one row will exist.
An Election has three states:
@@ -49,18 +50,24 @@
3. Closed. The election is closed.
DEFINITION: salt and opened_key are NOT NULL. closed is 1.
*/
-CREATE TABLE METADATA (
+CREATE TABLE ELECTIONS (
- /* The Election ID. This value might be replicated in the
- filesystem holding this database. To remain independent of
- application file choices, the ID is stored here. */
+ /* The Election ID. This is a unique text string. We do not use
+ AUTOINCREMENT, so that URLs for elections cannot be deduced. */
eid TEXT PRIMARY KEY NOT NULL,
/* Title of this election. */
title TEXT NOT NULL,
+ /* Who is the owner/creator of this election? */
+ owner_pid TEXT NOT NULL,
+
+ /* What authz group is allowed to edit this election? If NULL,
+ then only the OWNER_PID can edit. */
+ /* ### contents/format is TBD; think "which PMC" or "Foundation" */
+ authz TEXT,
+
/* ### if we have monitors, they go here. */
- /* ### maybe add an owner? */
/* A salt value to use for hashing this Election. 16 bytes.
This will be NULL until the Election is opened. */
@@ -76,16 +83,32 @@ CREATE TABLE METADATA (
opened). 1 for closed (implies it was opened). */
closed INTEGER
+ /* ### add foreign key for owner_pid */
+
+ /*
+ ,
+ FOREIGN KEY (convo_id) REFERENCES convo(id)
+ ON DELETE RESTRICT
+ ON UPDATE NO ACTION,
+
+ FOREIGN KEY (file_id) REFERENCES files(fname)
+ ON DELETE RESTRICT
+ ON UPDATE NO ACTION
+ */
) STRICT;
/* --------------------------------------------------------------------- */
-/* The set of issues to vote upon in this Election. */
+/* The set of issues to vote upon for a given Election. */
CREATE TABLE ISSUES (
/* The Issue ID, matching [-a-zA-Z0-9]+ */
+ /* ### switch to autoincrement? use TITLE for humans. */
iid TEXT PRIMARY KEY NOT NULL,
+ /* Which election is this issue associated with? */
+ eid TEXT NOT NULL,
+
/* Simple one-line title for this issue. */
title TEXT NOT NULL,
@@ -105,6 +128,8 @@ CREATE TABLE ISSUES (
This will be NULL until the Election is opened. */
salt BLOB
+ /* ### add foreign reference for EID */
+
) STRICT;
/* --------------------------------------------------------------------- */
@@ -155,6 +180,7 @@ CREATE TABLE VOTES (
) STRICT;
+/* ### review queries.yaml to figure out proper indexes */
CREATE INDEX I_BY_PERSON ON VOTES (person_token);
CREATE INDEX I_BY_ISSUE ON VOTES (issue_token);
diff --git a/v3/steve/crypto.py b/v3/steve/crypto.py
index ca969c0..ed8b13c 100644
--- a/v3/steve/crypto.py
+++ b/v3/steve/crypto.py
@@ -78,4 +78,5 @@ def _hash(data: bytes, salt: bytes) -> bytes:
def shuffle(x):
"Ensure we use the strongest RNG available for shuffling."
- return random.shuffle(x, passlib.utils.rng.random)
+ ### second param was removed in 3.11. need to revisit this.
+ return random.shuffle(x) ###, passlib.utils.rng.random)
diff --git a/v3/steve/election.py b/v3/steve/election.py
index 9ce9d2c..5095586 100644
--- a/v3/steve/election.py
+++ b/v3/steve/election.py
@@ -35,8 +35,9 @@ class Election:
S_OPEN = 'open'
S_CLOSED = 'closed'
- def __init__(self, db_fname):
+ def __init__(self, db_fname, eid):
self.db = db.DB(db_fname)
+ self.eid = eid
# Construct cursors for all operations.
self.c_salt_issue = self.db.add_statement(
@@ -44,11 +45,13 @@ class Election:
self.c_salt_person = self.db.add_statement(
'UPDATE PERSON SET salt = ? WHERE _ROWID_ = ?')
self.c_open = self.db.add_statement(
- 'UPDATE METADATA SET salt = ?, opened_key = ?')
+ 'UPDATE ELECTIONS SET salt = ?, opened_key = ?'
+ ' WHERE eid = ?')
self.c_close = self.db.add_statement(
- 'UPDATE METADATA SET closed = 1')
+ 'UPDATE ELECTIONS SET closed = 1'
+ ' WHERE eid = ?')
self.c_add_issue = self.db.add_statement(
- '''INSERT INTO ISSUES VALUES (?, ?, ?, ?, ?, ?)
+ '''INSERT INTO ISSUES VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT DO UPDATE SET
title=excluded.title,
description=excluded.description,
@@ -74,8 +77,9 @@ class Election:
''')
# Cursors for running queries.
- self.q_metadata = self.db.add_query('metadata',
- 'SELECT * FROM METADATA')
+ ### need to add eid to the ISSUES queries; likely ELECTIONS
+ self.q_metadata = self.db.add_query('elections',
+ 'SELECT * FROM ELECTIONS')
self.q_issues = self.db.add_query('issues',
'SELECT * FROM ISSUES ORDER BY iid')
self.q_person = self.db.add_query('person',
@@ -103,7 +107,7 @@ class Election:
print('SALT:', salt)
print('KEY:', opened_key)
- self.c_open.perform((salt, opened_key))
+ self.c_open.perform((salt, opened_key, self.eid))
def gather_election_data(self):
"Gather a definition of this election for keying and anti-tamper."
@@ -135,7 +139,7 @@ class Election:
assert self.is_open()
# Simple tweak of the metadata to close the Election.
- self.c_close.perform()
+ self.c_close.perform((self.eid,))
def add_salts(self):
"Set the SALT column in the ISSUES and PERSON tables."
@@ -180,14 +184,14 @@ class Election:
return (issue.title, issue.description, issue.type,
self.json2kv(issue.kv))
- def add_issue(self, iid, title, description, vtype, kv):
+ def add_issue(self, iid, eid, title, description, vtype, kv):
"Add or update an issue designated by IID."
assert self.is_editable()
assert vtype in vtypes.TYPES
# 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, title, description, vtype,
+ self.c_add_issue.perform((iid, eid, title, description, vtype,
self.kv2json(kv), None))
def delete_issue(self, iid):
diff --git a/v3/test/check_coverage.py b/v3/test/check_coverage.py
index 10745b1..75208da 100755
--- a/v3/test/check_coverage.py
+++ b/v3/test/check_coverage.py
@@ -28,7 +28,7 @@ import os.path
import sqlite3
import json
-import coverage
+import coverage # pip3 install coverage
# Ensure that we can import the "steve" package.
THIS_DIR = os.path.realpath(os.path.dirname(__file__))
@@ -54,12 +54,12 @@ def touch_every_line():
pass
conn = sqlite3.connect(TESTING_DB)
conn.executescript(open(SCHEMA_FILE).read())
- conn.execute('INSERT INTO METADATA'
- f' VALUES ("{eid}", "title", NULL, NULL, NULL)')
+ conn.execute('INSERT INTO ELECTIONS VALUES'
+ f' ("{eid}", "title", "alice", NULL, NULL, NULL, NULL)')
conn.commit()
# Ready to load up the Election and exercise it.
- e = steve.election.Election(TESTING_DB)
+ e = steve.election.Election(TESTING_DB, eid)
_ = e.get_metadata() # while EDITABLE
@@ -71,8 +71,8 @@ def touch_every_line():
e.delete_person('david')
_ = e.get_person('alice')
- e.add_issue('a', 'issue A', None, 'yna', None)
- e.add_issue('b', 'issue B', None, 'stv', {
+ e.add_issue('a', eid, 'issue A', None, 'yna', None)
+ e.add_issue('b', eid, 'issue B', None, 'stv', {
'seats': 3,
'labelmap': {
'a': 'Alice',
@@ -83,7 +83,7 @@ def touch_every_line():
},
})
_ = e.list_issues()
- e.add_issue('c', 'issue C', None, 'yna', None)
+ e.add_issue('c', eid, 'issue C', None, 'yna', None)
e.delete_issue('c')
_ = e.get_issue('a')
