Newer
Older
# 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.
"""
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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)
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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])
)
""",
],
1: [
"""
-- 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])
)
""",
],
5: [
"""
-- 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
],