aboutsummaryrefslogtreecommitdiff
path: root/src/exchangedb/common-0001.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/exchangedb/common-0001.sql')
-rw-r--r--src/exchangedb/common-0001.sql63
1 files changed, 61 insertions, 2 deletions
diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql
index 7ec6ce6a9..ae5d452a2 100644
--- a/src/exchangedb/common-0001.sql
+++ b/src/exchangedb/common-0001.sql
@@ -56,8 +56,8 @@ BEGIN
'(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)'
',payto_uri VARCHAR NOT NULL'
- ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)'
- ',external_id VARCHAR'
+ ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)' -- FIXME: REMOVE!
+ ',external_id VARCHAR' -- FIXME: REMOVE!
') %s ;'
,'wire_targets'
,'PARTITION BY HASH (wire_target_h_payto)'
@@ -85,6 +85,65 @@ BEGIN
END
$$;
+
+----------------------- legitimizations ---------------------------
+
+CREATE OR REPLACE FUNCTION create_table_legitimizations(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(legitimization_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=64)'
+ ',expiration_time INT8 NOT NULL DEFAULT (0)'
+ ',provider_section VARCHAR NOT NULL'
+ ',provider_user_id VARCHAR DEFAULT NULL'
+ ',provider_legitimization_id VARCHAR DEFAULT NULL'
+ ') %s ;'
+ ,'legitimizations'
+ ,'PARTITION BY HASH (h_payto)'
+ ,shard_suffix
+ );
+
+END
+$$;
+
+-- We need a separate function for this, as we call create_table only once but need to add
+-- those constraints to each partition which gets created
+CREATE OR REPLACE FUNCTION add_constraints_to_legitimizations_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ partition_name VARCHAR;
+BEGIN
+
+ partition_name = concat_ws('_', 'legitimizations', partition_suffix);
+
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || partition_name
+ || ' '
+ 'ADD CONSTRAINT ' || partition_name || '_legitimization_serial_id_key '
+ 'UNIQUE (legitimization_serial_id)');
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index '
+ 'ON '|| partition_name || ' '
+ '(provider_section,provider_legitimization_id)'
+ );
+ EXECUTE FORMAT (
+ 'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index '
+ 'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';'
+ );
+END
+$$;
+
------------------------ reserves -------------------------------
CREATE OR REPLACE FUNCTION create_table_reserves(