v3/queries.yaml

#
# 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.
#
# ----
#
# ### TBD: DOCCO
#
# CURSOR NAMING:
#   c_* performs various actions
#   q_* runs queries
#

election:
    c_salt_mayvote: UPDATE mayvote SET salt = ? WHERE _ROWID_ = ?
    c_open: |
        UPDATE election
        SET salt = ?, opened_key = ?, open_at = unixepoch('now')
        WHERE eid = ?
    c_close: |
        UPDATE election
        SET closed = 1, close_at = unixepoch('now')
        WHERE eid = ?
    c_add_issue: |
        INSERT INTO issue VALUES (?, ?, ?, ?, ?, ?)
        ON CONFLICT DO UPDATE SET
            title=excluded.title,
            description=excluded.description,
            type=excluded.type,
            kv=excluded.kv
    c_delete_issue: DELETE FROM issue WHERE iid = ?
    c_add_vote: |
        INSERT INTO vote (vote_token, ciphertext)
        VALUES (?, ?)
    c_add_mayvote: INSERT INTO mayvote (pid, iid) VALUES (?, ?)
    c_add_mayvote_all: |
        INSERT INTO mayvote (pid, iid)
        SELECT ?, iid FROM issue WHERE eid = ?
    c_delete_mayvote: |
        DELETE FROM mayvote
        WHERE iid IN (
            SELECT iid
            FROM issue
            WHERE eid = ?
            )
    c_delete_issues: DELETE FROM issue WHERE eid = ?
    c_delete_election: DELETE FROM election WHERE eid = ?

    # Fast check to see if the Election exists. No data returned.
    q_check_election: SELECT 1 FROM election WHERE eid = ?

    q_metadata: SELECT * FROM election WHERE eid = ?
    q_issues: SELECT * FROM issue WHERE eid = ? ORDER BY iid
    q_get_issue: SELECT * FROM issue WHERE iid = ?
    q_get_mayvote: SELECT * FROM mayvote WHERE pid = ? AND iid = ?
    q_tally: SELECT * FROM mayvote WHERE iid = ?
    q_find_issues: |
        SELECT m.*
        FROM mayvote m
        JOIN issue i ON m.iid = i.iid
        WHERE m.pid = ? AND i.eid = ?
    q_has_voted: |
        SELECT 1 FROM vote
        WHERE vote_token = ?
        LIMIT 1
    q_all_issues: |
        SELECT m._ROWID_
        FROM mayvote m
        JOIN issue i ON m.iid = i.iid
        WHERE i.eid = ?

    q_recent_vote: |
        SELECT ciphertext FROM vote
        WHERE vote_token = ?
        ORDER BY _ROWID_ DESC
        LIMIT 1
    # ALTERNATIVE to above:
    #
    # SELECT ciphertext FROM vote
    # WHERE vote_token = ?
    # AND vid = (SELECT MAX(vid) FROM vote WHERE vote_token = ?)
    #
    # ... this is clearer about the MAX() and avoids the sorting from
    #     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 e.eid, e.title, e.owner_pid, e.authz, e.closed,
               e.open_at, e.close_at,
               (e.opened_key IS NOT NULL) AS is_opened,
               COUNT(i.iid) AS issue_count,
               p.name AS owner_name
        FROM mayvote m
        JOIN issue i ON m.iid = i.iid
        JOIN election e ON i.eid = e.eid
        INNER JOIN person p ON e.owner_pid = p.pid
        WHERE m.pid = ?
          AND e.salt IS NOT NULL
          AND (e.closed IS NULL OR e.closed = 0)
        GROUP BY e.eid
        ORDER BY e._ROWID_

    # Which elections were created by PID?
    q_owned: |
        SELECT eid, title, authz, closed, open_at, close_at,
               (opened_key IS NOT NULL) AS is_opened
        FROM election WHERE owner_pid = ?


person:
    c_add_person: |
        INSERT INTO person VALUES (?, ?, ?)
        ON CONFLICT DO UPDATE SET
            name=excluded.name,
            email=excluded.email
    c_delete_person: DELETE FROM person WHERE pid = ?

    q_person: SELECT * FROM person ORDER BY pid
    q_get_person: SELECT * FROM person WHERE pid = ?