merchant

commit 6ad92a2d2ccd6a63f48123d835207e13fa32ea81
parent b0017f192d46c6c3bb1433765b1e5ed78f719ec5
Author: Christian Blättler <blatc2@bfh.ch>
Date:   Mon, 30 Oct 2023 20:30:10 +0100

review feedback

Diffstat:
M
src/backenddb/merchant-0002.sql
 | 
39
++++++++++++---------------------------

1 file changed, 12 insertions(+), 27 deletions(-)
diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
@@ -46,7 +46,7 @@ CREATE TABLE IF NOT EXISTS merchant_token_families
   ,name TEXT NOT NULL UNIQUE
   ,description TEXT
   ,description_i18n BYTEA NOT NULL
-  ,duration INTERVAL NOT NULL
+  ,duration BIGINT NOT NULL
   ,kind TEXT NOT NULL CHECK (kind IN ('subscription', 'discount'))
   ,issued BIGINT DEFAULT 0
   ,redeemed BIGINT DEFAULT 0
@@ -72,10 +72,10 @@ COMMENT ON COLUMN merchant_token_families.redeemed
 CREATE TABLE IF NOT EXISTS merchant_token_keys
   (token_keys_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
   ,token_family_serial BIGINT REFERENCES merchant_token_families(token_family_serial) ON DELETE CASCADE
-  ,start_date TIMESTAMP NOT NULL
-  ,expiration_date TIMESTAMP NOT NULL -- TODO: Do we need this or can we calculate it from the duration?
+  ,start_date BIGINT NOT NULL
+  ,expiration_date BIGINT NOT NULL
   ,pub BYTEA NOT NULL
-  ,h_pub BYTEA NOT NULL UNIQUE
+  ,h_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(h_pub)=32)
   ,priv BYTEA
   ,cipher TEXT NOT NULL CHECK (cipher IN ('rsa', 'cs'))
   ,UNIQUE (token_family_serial, start_date)
@@ -98,37 +98,22 @@ COMMENT ON COLUMN merchant_token_keys.priv
 COMMENT ON COLUMN merchant_token_keys.cipher
  IS 'Cipher used (rsa or cs).';
 
-CREATE OR REPLACE FUNCTION merchant_token_calculate_expiration_date()
-RETURNS TRIGGER AS $$
-BEGIN
-    NEW.expiration_date := NEW.start_date + (SELECT duration
-                                             FROM merchant_token_families
-                                             WHERE token_family_serial = NEW.token_family_serial);
-    RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-COMMENT ON FUNCTION merchant_token_calculate_expiration_date
- IS 'Calculates the expiration date for a token key based on the start date and the associated token family duration.';
-
-CREATE TRIGGER merchant_token_set_expiration_date
-BEFORE INSERT ON merchant_token_keys
-FOR EACH ROW
-EXECUTE FUNCTION merchant_token_calculate_expiration_date();
-COMMENT ON TRIGGER merchant_token_set_expiration_date ON merchant_token_keys
- IS 'Trigger to set the expiration date for a token key before insertion.';
 
 CREATE TABLE IF NOT EXISTS merchant_spent_tokens
   (spent_token_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,order_serial BIGINT REFERENCES merchant_contract_terms(order_serial) ON DELETE CASCADE
+  ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+  ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
   ,token_key_serial BIGINT REFERENCES merchant_token_key(token_key_serial) ON DELETE CASCADE
-  ,token_pub BYTEA NOT NULL UNIQUE
-  ,token_sig BYTEA NOT NULL CHECK (LENGTH(token_pub)=64)
+  ,token_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(token_pub)=32)
+  ,token_sig BYTEA NOT NULL CHECK (LENGTH(token_sig)=64)
   ,blind_sig BYTEA NOT NULL
   );
 COMMENT ON TABLE merchant_spent_tokens
  IS 'Tokens that have been spent by customers.';
-COMMENT ON COLUMN merchant_spent_tokens.order_id
- IS 'Order the token was spent on.';
+COMMENT ON COLUMN merchant_spent_tokens.merchant_serial
+ IS 'Merchant serial where the token was spent.';
+COMMENT ON COLUMN merchant_spent_tokens.h_contract_terms
+ IS 'This is no foreign key by design.';
 COMMENT ON COLUMN merchant_spent_tokens.token_key_serial
  IS 'Token family to which the spent token belongs.';
 COMMENT ON COLUMN merchant_spent_tokens.token_pub