Skip to content
Snippets Groups Projects
schema.py 7.78 KiB
Newer Older
  • Learn to ignore specific revisions
  • # Copyright 2019 PrivateStorage.io, LLC
    #
    # Licensed 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.
    """
    This module defines the database schema used by the model interface.
    """
    
    
    Tom Prince's avatar
    Tom Prince committed
    
    
    def get_schema_version(cursor):
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS [version] AS SELECT 0 AS [version]
            """
        )
        cursor.execute(
            """
            SELECT [version] FROM [version]
            """
        )
        [(actual_version,)] = cursor.fetchall()
        return actual_version
    
    
    def get_schema_upgrades(from_version):
        """
        Generate unicode strings containing SQL expressions to alter a schema from
        ``from_version`` to the latest version.
    
        :param int from_version: The version of the schema which may require
            upgrade.
        """
        while from_version in _UPGRADES:
            for upgrade in _UPGRADES[from_version]:
                yield upgrade
            yield _INCREMENT_VERSION
            from_version += 1
    
    
    def run_schema_upgrades(upgrades, cursor):
        """
        Apply the given upgrades using the given cursor.
    
        :param list[unicode] upgrades: The SQL statements to apply for the
            upgrade.
    
        :param cursor: A DB-API cursor to use to run the SQL.
        """
        for upgrade in upgrades:
            cursor.execute(upgrade)
    
    
    
    Tom Prince's avatar
    Tom Prince committed
    _INCREMENT_VERSION = """
    
        UPDATE [version]
        SET [version] = [version] + 1
        """
    
    # A mapping from old schema versions to lists of unicode strings of SQL to
    # execute against that version of the schema to create the successor schema.
    _UPGRADES = {
        0: [
            """
            CREATE TABLE [vouchers] (
                [number] text,
                [created] text,                     -- An ISO8601 date+time string.
                [state] text DEFAULT "pending",     -- pending, double-spend, redeemed
    
                [finished] text DEFAULT NULL,       -- ISO8601 date+time string when
                                                    -- the current terminal state was entered.
    
                [token-count] num DEFAULT NULL,     -- Set in the redeemed state to the number
                                                    -- of tokens received on this voucher's
                                                    -- redemption.
    
                PRIMARY KEY([number])
            )
            """,
            """
            CREATE TABLE [tokens] (
                [text] text, -- The random string that defines the token.
                [voucher] text, -- Reference to the voucher these tokens go with.
    
                PRIMARY KEY([text])
                FOREIGN KEY([voucher]) REFERENCES [vouchers]([number])
            )
            """,
            """
            CREATE TABLE [unblinded-tokens] (
                [token] text, -- The base64 encoded unblinded token.
    
                PRIMARY KEY([token])
            )
            """,
            """
            CREATE TABLE [lease-maintenance-spending] (
                [id] integer, -- A unique identifier for a group of activity.
                [started] text, -- ISO8601 date+time string when the activity began.
                [finished] text, -- ISO8601 date+time string when the activity completed (or null).
    
                -- The number of passes that would be required to renew all
                -- shares encountered during this activity.  Note that because
                -- leases on different shares don't necessarily expire at the
                -- same time this is not necessarily the number of passes
                -- **actually** used during this activity.  Some shares may
                -- not have required lease renewal.  Also note that while the
                -- activity is ongoing this value may change.
                [count] integer,
    
                PRIMARY KEY([id])
            )
            """,
        ],
    
            -- Incorrectly track a single public-key for all.  Later version of
            -- the schema moves this elsewhere.
    
            ALTER TABLE [vouchers] ADD COLUMN [public-key] text
            """,
        ],
    
        2: [
            """
            -- Keep track of progress through redemption of each voucher.
    
            ALTER TABLE [vouchers] ADD COLUMN [counter] integer DEFAULT 0
    
        3: [
            """
            -- Reference to the counter these tokens go with.
            ALTER TABLE [tokens] ADD COLUMN [counter] integer NOT NULL DEFAULT 0
            """,
    
            """
            -- Record the total number of tokens for which we expect to be able to
    
            -- redeem this voucher.  We don't want to allow NULL values here at
            -- all because that allows insertion of garbage data going forward.
            -- However to add a non-NULL column to a table we have to supply a
            -- default value.  Since no real vouchers have ever been issued at the
            -- time of this upgrade we'll just make up some value.  It doesn't
            -- particularly matter if it is wrong for some testing voucher someone
            -- used.
            ALTER TABLE [vouchers] ADD COLUMN [expected-tokens] integer NOT NULL DEFAULT 32768
    
        4: [
            """
            CREATE TABLE [invalid-unblinded-tokens] (
                [token] text,  -- The base64 encoded unblinded token.
                [reason] text, -- The reason given for it being considered invalid.
    
                PRIMARY KEY([token])
            )
            """,
        ],
    
            """
            -- Create a table where rows represent a single group of unblinded
            -- tokens all redeemed together.  Some number of these rows represent
            -- a complete redemption of a voucher.
            CREATE TABLE [redemption-groups] (
                -- A unique identifier for this redemption group.
                [rowid] INTEGER PRIMARY KEY,
    
                -- The text representation of the voucher this group is associated with.
                [voucher] text,
    
                -- A flag indicating whether these tokens can be spent or if
                -- they're being held for further inspection.
    
                [spendable] integer,
    
                -- The public key seen when redeeming this group.
                [public-key] text
    
            -- Create one redemption group for every existing, redeemed voucher.
            -- These tokens were probably *not* all redeemed in one group but
            -- we've only preserved one public key for them so we can't do much
            -- better than this.
    
            INSERT INTO [redemption-groups] ([voucher], [public-key], [spendable])
                SELECT DISTINCT([number]), [public-key], 1 FROM [vouchers] WHERE [state] = "redeemed"
            """,
            """
    
            -- Give each voucher a count of "sequestered" tokens.  Currently,
            -- these are unspendable tokens that were issued using a disallowed
            -- public key.
    
            ALTER TABLE [vouchers] ADD COLUMN [sequestered-count] integer NOT NULL DEFAULT 0
    
            -- Give each unblinded token a reference to the [redemption-groups]
            -- table identifying the group that token arrived with.  This lets us
            -- act collectively on tokens from these groups and identify tokens
            -- which are spendable.
            --
            -- The default value is provided for rows that
            -- existed prior to this upgrade which had no group association.  For
            -- unblinded tokens to exist at all there must be at least one voucher
            -- in the vouchers table.  [redemption-groups] will therefore have at
            -- least one row added to it (by the statement a few lines above).
            -- Note that SQLite3 rowid numbering begins at 1.
    
            ALTER TABLE [unblinded-tokens] ADD COLUMN [redemption-group] integer DEFAULT 1