From 2eff222c524fa3b5ce2dd4a636aaec8dfb0862c7 Mon Sep 17 00:00:00 2001
From: Christian Grothoff <christian@grothoff.org>
Date: Sun, 27 Nov 2022 15:31:39 +0100
Subject: more work on SQL refactoring

---
 src/exchangedb/0002-contracts.sql | 72 +++++++++++++++++++++++++++++----------
 1 file changed, 54 insertions(+), 18 deletions(-)

(limited to 'src/exchangedb/0002-contracts.sql')

diff --git a/src/exchangedb/0002-contracts.sql b/src/exchangedb/0002-contracts.sql
index 224430c95..409653060 100644
--- a/src/exchangedb/0002-contracts.sql
+++ b/src/exchangedb/0002-contracts.sql
@@ -15,8 +15,8 @@
 --
 
 
-CREATE OR REPLACE FUNCTION create_table_contracts(
-  IN shard_suffix VARCHAR DEFAULT NULL
+CREATE FUNCTION create_table_contracts(
+  IN partition_suffix VARCHAR DEFAULT NULL
 )
 RETURNS VOID
 LANGUAGE plpgsql
@@ -24,36 +24,67 @@ AS $$
 DECLARE
   table_name VARCHAR DEFAULT 'contracts';
 BEGIN
-
   PERFORM create_partitioned_table(
-    'CREATE TABLE IF NOT EXISTS %I '
-      '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE
-      ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)'
-      ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)'
-      ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)'
-      ',e_contract BYTEA NOT NULL'
-      ',purse_expiration INT8 NOT NULL'
-      ',PRIMARY KEY (purse_pub)'
-    ') %s ;'
+     'CREATE TABLE %I '
+     '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
+     ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)'
+     ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)'
+     ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)'
+     ',e_contract BYTEA NOT NULL'
+     ',purse_expiration INT8 NOT NULL'
+     ',PRIMARY KEY (purse_pub)'
+     ') %s ;'
     ,table_name
     ,'PARTITION BY HASH (purse_pub)'
-    ,shard_suffix
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_table(
+     'encrypted contracts associated with purses'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'public key of the purse that the contract is associated with'
+    ,'purse_pub'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'signature over the encrypted contract by the purse contract key'
+    ,'contract_sig'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'Public ECDH key used to encrypt the contract, to be used with the purse private key for decryption'
+    ,'pub_ckey'
+    ,table_name
+    ,partition_suffix
+  );
+  PERFORM comment_partitioned_column(
+     'AES-GCM encrypted contract terms (contains gzip compressed JSON after decryption)'
+    ,'e_contract'
+    ,table_name
+    ,partition_suffix
   );
-
 END
 $$;
 
-CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition(
+
+CREATE FUNCTION constrain_table_contracts(
   IN partition_suffix VARCHAR
 )
 RETURNS VOID
 LANGUAGE plpgsql
 AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'contracts';
 BEGIN
+  table_name = concat_ws('_', table_name, partition_suffix);
   EXECUTE FORMAT (
-    'ALTER TABLE contracts_' || partition_suffix || ' '
-      'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key '
-        'UNIQUE (contract_serial_id) '
+    'ALTER TABLE ' || table_name ||
+    ' ADD CONSTRAINT ' || table_name || '_contract_serial_id_key'
+    ' UNIQUE (contract_serial_id) '
   );
 END
 $$;
@@ -70,4 +101,9 @@ INSERT INTO exchange_tables
     ,'exchange-0002'
     ,'create'
     ,TRUE
+    ,FALSE),
+    ('contracts'
+    ,'exchange-0002'
+    ,'constrain'
+    ,TRUE
     ,FALSE);
-- 
cgit v1.2.3