def update_database(db: sqlite3.Connection) -> None:
# This will error if the database has not been created correctly with the metadata.
version = _get_migration(db)
from . import migrations
with db:
if version == 22:
migrations.migration_0023_add_wallet_events.execute(db)
version += 1
if version == 23:
migrations.migration_0024_account_transactions.execute(db)
version += 1
if version == 24:
migrations.migration_0025_invoices.execute(db)
version += 1
if version == 25:
migrations.migration_0026_txo_coinbase_flag.execute(db)
version += 1
if version != MIGRATION_CURRENT:
db.rollback()
assert version == MIGRATION_CURRENT, \
f"Expected migration {MIGRATION_CURRENT}, got {version}"
_ensure_matching_migration(db, MIGRATION_CURRENT)
def execute(conn: sqlite3.Connection) -> None:
# Ensure that for all transactions in block position 0, all outputs for those transactions
# have the IS_COINBASE flag.
conn.execute("UPDATE TransactionOutputs "
f"SET flags=flags|{TransactionOutputFlag.IS_COINBASE} "
"WHERE tx_hash in (SELECT tx_hash FROM Transactions WHERE block_position = 0)")
date_updated = int(time.time())
conn.execute("UPDATE WalletData SET value=?, date_updated=? WHERE key=?",
[json.dumps(MIGRATION),date_updated,"migration"])
def execute(conn: sqlite3.Connection) -> None:
conn.execute("CREATE TABLE IF NOT EXISTS WalletEvents ("
"event_id INTEGER PRIMARY KEY,"
"event_type INTEGER NOT NULL,"
"event_flags INTEGER NOT NULL,"
"account_id INTEGER,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY(account_id) REFERENCES Accounts (account_id)"
")")
date_updated = int(time.time())
conn.execute("UPDATE WalletData SET value=?, date_updated=? WHERE key=?",
[json.dumps(MIGRATION), date_updated, "migration"])
# Inject seed backup reminders for every existing account (can actually only be one for now).
account_rows = list(conn.execute("SELECT * FROM Accounts"))
wallet_event_id = 1
for account_row in account_rows:
conn.execute(
"INSERT INTO WalletEvents (event_id, event_type, event_flags, "
"account_id, date_created, date_updated) VALUES (?, ?, ?, ?, ?, ?)",
(wallet_event_id, WalletEventType.SEED_BACKUP_REMINDER,
WalletEventFlag.UNREAD | WalletEventFlag.FEATURED, account_row[0],
date_updated, date_updated))
wallet_event_id += 1
if wallet_event_id > 1:
conn.execute(
"INSERT INTO WalletData (key, value, date_created, date_updated) VALUES "
"(?, ?, ?, ?)",
("next_wallet_event_id", json.dumps(wallet_event_id), date_updated,
date_updated))
def _get_migration(db: sqlite3.Connection) -> int:
cursor = db.execute("SELECT value FROM WalletData WHERE key='migration'")
row = cursor.fetchone()
if row is None:
raise DatabaseMigrationError(
"wallet database migration metadata not present")
return json.loads(row[0])
def _ensure_journal_mode(self, connection: sqlite3.Connection) -> None:
with self._lock:
cursor = connection.execute(f"PRAGMA journal_mode;")
journal_mode = cursor.fetchone()[0]
if journal_mode.upper() == self.JOURNAL_MODE.value:
return
self._logger.debug(
"Switching database from journal mode %s to journal mode %s",
journal_mode.upper(), self.JOURNAL_MODE.value)
time_start = time.time()
attempt = 1
delay = 0.05
while True:
try:
cursor = connection.execute(
f"PRAGMA journal_mode={self.JOURNAL_MODE.value};")
except sqlite3.OperationalError:
time_delta = time.time() - time_start
if time_delta < 10.0:
delay = min(delay, max(0.05, 10.0 - time_delta))
time.sleep(delay)
self._logger.warning(
"Database %s pragma attempt %d at %ds",
self.JOURNAL_MODE.value, attempt, time_delta)
delay *= 2
attempt += 1
continue
raise
else:
journal_mode = cursor.fetchone()[0]
if journal_mode.upper() != self.JOURNAL_MODE.value:
self._logger.error(
"Database unable to switch from journal mode %s to journal mode %s",
self.JOURNAL_MODE.value, journal_mode.upper())
return
break
self._logger.debug("Database now in journal mode %s",
self.JOURNAL_MODE.value)
def execute(conn: sqlite3.Connection) -> None:
conn.execute("CREATE VIEW IF NOT EXISTS AccountTransactions (account_id, tx_hash) AS "
"SELECT DISTINCT KI.account_id, TD.tx_hash FROM TransactionDeltas TD "
"INNER JOIN KeyInstances KI USING(keyinstance_id)")
# Switch the state constants over from a value to flags.
conn.execute("UPDATE PaymentRequests SET state=8 WHERE state=3")
conn.execute("UPDATE PaymentRequests SET state=4 WHERE state=2")
conn.execute("UPDATE PaymentRequests SET state=2 WHERE state=1")
conn.execute("UPDATE PaymentRequests SET state=1 WHERE state=0")
date_updated = int(time.time())
conn.execute("UPDATE WalletData SET value=?, date_updated=? WHERE key=?",
[json.dumps(MIGRATION),date_updated,"migration"])
def execute(conn: sqlite3.Connection) -> None:
conn.execute("CREATE TABLE IF NOT EXISTS Invoices ("
"invoice_id INTEGER PRIMARY KEY,"
"account_id INTEGER NOT NULL,"
"tx_hash BLOB DEFAULT NULL,"
"payment_uri TEXT NOT NULL,"
"description TEXT NULL,"
"invoice_flags INTEGER NOT NULL,"
"value INTEGER NOT NULL,"
"invoice_data BLOB NOT NULL,"
"date_expires INTEGER DEFAULT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY (account_id) REFERENCES Accounts (account_id),"
"FOREIGN KEY (tx_hash) REFERENCES Transactions (tx_hash)"
")")
# The unique constraint is also required for any upsert operation to work.
# But really we added it to prevent people from making duplicate invoices.
conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS "
"idx_Invoices_unique ON Invoices(payment_uri)")
date_updated = int(time.time())
conn.execute("UPDATE WalletData SET value=?, date_updated=? WHERE key=?",
[json.dumps(MIGRATION), date_updated, "migration"])
def execute(conn: sqlite3.Connection) -> None:
date_created = int(time.time())
conn.execute(
"CREATE TABLE IF NOT EXISTS MasterKeys ("
"masterkey_id INTEGER PRIMARY KEY,"
"parent_masterkey_id INTEGER DEFAULT NULL,"
"derivation_type INTEGER NOT NULL,"
"derivation_data BLOB NOT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY(parent_masterkey_id) REFERENCES MasterKeys (masterkey_id)"
")")
conn.execute(
"CREATE TABLE IF NOT EXISTS Accounts ("
"account_id INTEGER PRIMARY KEY,"
"default_masterkey_id INTEGER DEFAULT NULL,"
"default_script_type INTEGER NOT NULL,"
"account_name TEXT NOT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY(default_masterkey_id) REFERENCES MasterKeys (masterkey_id)"
")")
conn.execute(
"CREATE TABLE IF NOT EXISTS KeyInstances ("
"keyinstance_id INTEGER PRIMARY KEY,"
"account_id INTEGER NOT NULL,"
"masterkey_id INTEGER DEFAULT NULL,"
"derivation_type INTEGER NOT NULL,"
"derivation_data BLOB NOT NULL,"
"script_type INTEGER NOT NULL,"
"flags INTEGER NOT NULL,"
"description TEXT DEFAULT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY(account_id) REFERENCES Accounts (account_id)" +
"FOREIGN KEY(masterkey_id) REFERENCES MasterKeys (masterkey_id)" + ")")
conn.execute("CREATE TABLE IF NOT EXISTS Transactions ("
"tx_hash BLOB PRIMARY KEY,"
"tx_data BLOB DEFAULT NULL,"
"proof_data BLOB DEFAULT NULL,"
"block_height INTEGER DEFAULT NULL,"
"block_position INTEGER DEFAULT NULL,"
"fee_value INTEGER DEFAULT NULL,"
"flags INTEGER NOT NULL DEFAULT 0,"
"description TEXT DEFAULT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL"
")")
conn.execute(
"CREATE TABLE IF NOT EXISTS TransactionOutputs ("
"tx_hash BLOB NOT NULL,"
"tx_index INTEGER NOT NULL,"
"value INTEGER NOT NULL,"
"keyinstance_id INTEGER NOT NULL,"
"flags INTEGER NOT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY (tx_hash) REFERENCES Transactions (tx_hash),"
"FOREIGN KEY (keyinstance_id) REFERENCES KeyInstances (keyinstance_id)"
")")
# The unique constraint is also required for any upsert operation to work.
conn.execute(
"CREATE UNIQUE INDEX IF NOT EXISTS "
"idx_TransactionOutputs_unique ON TransactionOutputs(tx_hash, tx_index)"
)
conn.execute(
"CREATE TABLE IF NOT EXISTS TransactionDeltas ("
"keyinstance_id INTEGER NOT NULL,"
"tx_hash BLOB NOT NULL,"
"value_delta INTEGER NOT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY(tx_hash) REFERENCES Transactions (tx_hash),"
"FOREIGN KEY(keyinstance_id) REFERENCES KeyInstances (keyinstance_id) "
")")
# The unique constraint is also required for any upsert operation to work.
conn.execute(
"CREATE UNIQUE INDEX IF NOT EXISTS idx_TransactionDeltas_unique "
"ON TransactionDeltas(keyinstance_id, tx_hash)")
conn.execute("CREATE TABLE IF NOT EXISTS WalletData ("
"key TEXT NOT NULL,"
"value TEXT NOT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL"
")")
conn.execute(
"CREATE TABLE IF NOT EXISTS PaymentRequests ("
"paymentrequest_id INTEGER PRIMARY KEY,"
"keyinstance_id INTEGER NOT NULL,"
"state INTEGER NOT NULL,"
"description TEXT DEFAULT NULL,"
"expiration INTEGER DEFAULT NULL,"
"value INTEGER DEFAULT NULL,"
"date_created INTEGER NOT NULL,"
"date_updated INTEGER NOT NULL,"
"FOREIGN KEY(keyinstance_id) REFERENCES KeyInstances (keyinstance_id) "
")")
# The unique constraint is also required for any upsert operation to work.
conn.execute(
"CREATE UNIQUE INDEX IF NOT EXISTS idx_WalletData_unique ON WalletData(key)"
)
conn.executemany(
"INSERT INTO WalletData (key, value, date_created, date_updated) VALUES "
"(?, ?, ?, ?)", [
["migration",
json.dumps(MIGRATION), date_created, date_created],
["next_masterkey_id",
json.dumps(1), date_created, date_created],
["next_account_id",
json.dumps(1), date_created, date_created],
["next_keyinstance_id",
json.dumps(1), date_created, date_created],
[
"next_paymentrequest_id",
json.dumps(1), date_created, date_created
],
])