commit 9060502019fccb872af1d62347b84cc2cbc02e81
parent 5bc7c2d2e65c21c2f87f9634e545c44ae5a5e36e
Author: Florian Dold <dold@taler.net>
Date: Sun, 19 Jul 2026 15:57:29 +0200
db: drop redundant sqlite indexes and add missing constraints
Some indexes were a prefix of a composite or the primary key. New
constraints close gaps the mappers assumed couldn't occur.
Diffstat:
1 file changed, 43 insertions(+), 30 deletions(-)
diff --git a/packages/taler-wallet-core/src/db-sqlite-schema.ts b/packages/taler-wallet-core/src/db-sqlite-schema.ts
@@ -29,9 +29,18 @@
* explicit in dbtx-sqlite.ts, never derived by string munging.
* - timestamps are INTEGER microseconds; Number.MAX_SAFE_INTEGER means "never".
* - amounts stay TEXT in their canonical string form.
- * - status enums are INTEGER, so the non-final range is a BETWEEN. The one
- * exceptions are CoinStatus and ExchangeMigrationReason, which are *string*
- * enums upstream; those columns are TEXT and say so.
+ * - keys, hashes and signatures are BLOB. The record types expose them as
+ * Crockford base32 strings, and dbtx-sqlite.ts converts at the field
+ * mapping -- see BLOB_COLUMNS below, which is the single source of truth,
+ * and SQLITE_BLOB_PLAN.md for why. A new column holding key material
+ * should be BLOB and listed there; a TEXT one silently fails to match a
+ * correctly-encoded parameter, because sqlite never compares TEXT equal to
+ * BLOB.
+ * - status enums are INTEGER, so the non-final range is a BETWEEN. Several
+ * enums are *string* enums upstream and are TEXT instead: CoinStatus,
+ * ExchangeMigrationReason, RefreshReason, DenomLossEventType,
+ * MerchantContractTokenKind and WithdrawalRecordType. Each such column
+ * says so where it is declared.
* - values that are only ever read and written whole are JSON TEXT.
* - fields that an index needs are real columns, even when they also appear
* inside a JSON payload conceptually.
@@ -42,7 +51,7 @@
*
* Bump this when adding a migration to {@link schemaMigrations}.
*/
-export const SQLITE_SCHEMA_VERSION = 3;
+export const SQLITE_SCHEMA_VERSION = 4;
/**
* A single, ordered schema evolution step.
@@ -70,9 +79,8 @@ export interface SchemaMigration {
/**
* The baseline schema, version 1.
*
- * Only the tables needed by the methods implemented so far are present; this
- * grows as dbtx-sqlite.ts does, rather than being written speculatively for
- * all 43 tables at once.
+ * Complete: every store the wallet uses has a table here, and every method of
+ * WalletDbTransaction is implemented against it.
*/
/**
@@ -231,10 +239,12 @@ CREATE TABLE IF NOT EXISTS currency_info (
CREATE TABLE IF NOT EXISTS contacts (
alias TEXT NOT NULL,
alias_type TEXT NOT NULL,
- mailbox_base_uri TEXT,
- mailbox_address TEXT,
- source TEXT,
- petname TEXT,
+ -- NOT NULL: ContactEntry declares all of these as required, and the mapper
+ -- reads them unguarded, so a NULL would surface as null typed as string.
+ mailbox_base_uri TEXT NOT NULL,
+ mailbox_address TEXT NOT NULL,
+ source TEXT NOT NULL,
+ petname TEXT NOT NULL,
PRIMARY KEY (alias, alias_type)
);
@@ -309,8 +319,6 @@ CREATE TABLE IF NOT EXISTS denominations (
verification_status INTEGER NOT NULL,
PRIMARY KEY (exchange_base_url, denom_pub_hash)
);
-CREATE INDEX IF NOT EXISTS denominations_by_exchange
- ON denominations (exchange_base_url);
CREATE INDEX IF NOT EXISTS denominations_by_verification_status
ON denominations (verification_status);
-- Serves findDenominationByFamilyFromExpiry. denom_pub_hash is part of the
@@ -411,10 +419,6 @@ CREATE TABLE IF NOT EXISTS slates (
token_issue_pub TEXT NOT NULL,
description_i18n TEXT
);
-CREATE INDEX IF NOT EXISTS slates_by_purchase_and_choice
- ON slates (purchase_id, choice_index);
-CREATE INDEX IF NOT EXISTS slates_by_purchase_choice_output
- ON slates (purchase_id, choice_index, output_index);
CREATE INDEX IF NOT EXISTS slates_by_purchase_choice_output_repeat
ON slates (purchase_id, choice_index, output_index, repeat_index);
@@ -458,6 +462,10 @@ CREATE TABLE IF NOT EXISTS donation_planchets (
udi_nonce BLOB PRIMARY KEY,
donau_base_url TEXT NOT NULL,
donor_tax_id_hash BLOB NOT NULL,
+ -- TEXT, not BLOB, unlike its neighbours: this salt comes from the donau
+ -- service rather than from an encodeCrock call here, so nothing guarantees
+ -- it is Crockford at all. Converting it would risk an EncodingError on
+ -- real data. Same for purchases.donau_tax_id_salt.
donor_hash_salt TEXT NOT NULL,
donor_tax_id TEXT NOT NULL,
donation_year INTEGER NOT NULL,
@@ -480,13 +488,12 @@ CREATE TABLE IF NOT EXISTS donation_receipts (
donation_unit_pub_hash BLOB NOT NULL,
donation_unit_sig TEXT NOT NULL,
donor_tax_id_hash BLOB NOT NULL,
+ -- TEXT, not BLOB: see the note in donation_planchets.
donor_hash_salt TEXT NOT NULL,
donor_tax_id TEXT NOT NULL,
value TEXT NOT NULL,
udi_index INTEGER NOT NULL
);
-CREATE INDEX IF NOT EXISTS donation_receipts_by_status
- ON donation_receipts (status);
CREATE INDEX IF NOT EXISTS donation_receipts_by_status_and_donau
ON donation_receipts (status, donau_base_url);
@@ -519,6 +526,7 @@ CREATE TABLE IF NOT EXISTS purchases (
donau_base_url TEXT,
donau_amount TEXT,
donau_tax_id_hash BLOB,
+ -- TEXT, not BLOB: supplied by the donau service, not encodeCrock'd here.
donau_tax_id_salt TEXT,
donau_tax_id TEXT,
donau_year INTEGER,
@@ -774,7 +782,15 @@ CREATE TABLE IF NOT EXISTS exchanges (
current_account_pub BLOB,
peer_payments_disabled INTEGER,
direct_deposit_disabled INTEGER,
- no_fees INTEGER
+ no_fees INTEGER,
+ -- The three details_pointer columns are one value. The mapper checks only
+ -- the master pub and then reads the other two unguarded, so a partially
+ -- set pointer would yield null typed as string.
+ CHECK (
+ (details_pointer_master_pub IS NULL) = (details_pointer_currency IS NULL)
+ AND (details_pointer_master_pub IS NULL)
+ = (details_pointer_update_clock IS NULL)
+ )
);
CREATE TABLE IF NOT EXISTS exchange_details (
@@ -796,8 +812,6 @@ CREATE TABLE IF NOT EXISTS exchange_details (
bank_compliance_language TEXT,
default_peer_push_expiration TEXT
);
-CREATE INDEX IF NOT EXISTS exchange_details_by_exchange
- ON exchange_details (exchange_base_url);
-- The pointer identifies at most one details row.
CREATE UNIQUE INDEX IF NOT EXISTS exchange_details_by_pointer
ON exchange_details (exchange_base_url, currency, master_public_key);
@@ -827,8 +841,6 @@ CREATE TABLE IF NOT EXISTS denomination_families (
fee_refresh TEXT NOT NULL,
fee_refund TEXT NOT NULL
);
-CREATE INDEX IF NOT EXISTS denomination_families_by_exchange
- ON denomination_families (exchange_base_url);
CREATE UNIQUE INDEX IF NOT EXISTS denomination_families_by_params
ON denomination_families (
exchange_base_url, exchange_master_pub, value,
@@ -888,9 +900,14 @@ CREATE TABLE IF NOT EXISTS withdrawal_groups (
denoms_sel TEXT,
abort_reason TEXT,
fail_reason TEXT,
- -- Variant correctness lives here rather than in a side table: bank_info is
- -- present exactly for the bank-integrated variant.
+ -- Variant correctness lives here rather than in a side table: bank_info and
+ -- taler_withdraw_uri are present exactly for the bank-integrated variant.
+ -- The URI is included because the mapper reads it unguarded for that
+ -- variant, and it is the only copy of the value.
CHECK ((withdrawal_type = 'bank-integrated') = (bank_info IS NOT NULL))
+ CHECK (
+ (withdrawal_type = 'bank-integrated') = (taler_withdraw_uri IS NOT NULL)
+ )
);
CREATE INDEX IF NOT EXISTS withdrawal_groups_by_status
ON withdrawal_groups (status);
@@ -915,8 +932,6 @@ CREATE TABLE IF NOT EXISTS planchets (
);
CREATE UNIQUE INDEX IF NOT EXISTS planchets_by_group_and_index
ON planchets (withdrawal_group_id, coin_idx);
-CREATE INDEX IF NOT EXISTS planchets_by_group
- ON planchets (withdrawal_group_id);
CREATE INDEX IF NOT EXISTS planchets_by_coin_ev
ON planchets (coin_ev_hash);
@@ -940,8 +955,6 @@ CREATE TABLE IF NOT EXISTS coins (
coin_source TEXT NOT NULL,
source_transaction_id TEXT
);
-CREATE INDEX IF NOT EXISTS coins_by_exchange
- ON coins (exchange_base_url);
CREATE INDEX IF NOT EXISTS coins_by_denom_pub_hash
ON coins (denom_pub_hash);
CREATE INDEX IF NOT EXISTS coins_by_coin_ev_hash
