commit d0835367865b5a0ae941abcec2c1c2d067f10b25 parent c93ce9ea2eb16a91422b8101fecd8c491c7e93b7 Author: Christian Grothoff <christian@grothoff.org> Date: Fri, 28 Jul 2023 12:27:46 +0200 combine v3 and v4 of exchange schema into v2 due to major incompatible tuple change forthcoming Diffstat:
15 files changed, 177 insertions(+), 278 deletions(-)
diff --git a/src/exchangedb/0003-age_withdraw.sql b/src/exchangedb/0002-age_withdrawl.sql diff --git a/src/exchangedb/0003-aml_history.sql b/src/exchangedb/0002-aml_history.sql diff --git a/src/exchangedb/0003-aml_staff.sql b/src/exchangedb/0002-aml_staff.sql diff --git a/src/exchangedb/0003-aml_status.sql b/src/exchangedb/0002-aml_status.sql diff --git a/src/exchangedb/0002-kyc_attributes.sql b/src/exchangedb/0002-kyc_attributes.sql @@ -0,0 +1,155 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +CREATE OR REPLACE FUNCTION create_table_kyc_attributes( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'kyc_attributes'; +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(kyc_attributes_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)' + ',kyc_prox BYTEA NOT NULL CHECK (LENGTH(kyc_prox)=32)' + ',provider VARCHAR NOT NULL' + ',collection_time INT8 NOT NULL' + ',expiration_time INT8 NOT NULL' + ',encrypted_attributes BYTEA NOT NULL' + ',legitimization_serial INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (h_payto)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'KYC data about particular payment addresses' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'hash of payto://-URI the attributes are about' + ,'h_payto' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'short hash of normalized full name and birthdate; used to efficiently find likely duplicate users' + ,'kyc_prox' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'birth date of the user, in format YYYY-MM-DD where a value of 0 is used to indicate unknown (in official documents); NULL if the birth date was not collected by the provider; used for KYC-driven age restrictions' + ,'birthdate' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'time when the attributes were collected by the provider' + ,'collection_time' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'time when the attributes should no longer be considered validated' + ,'expiration_time' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'configuration section name of the provider that affirmed the attributes' + ,'provider' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + '(encrypted) JSON object (as string) with the attributes' + ,'encrypted_attributes' + ,table_name + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Reference the legitimization process for which theses attributes are gathered for.' + ,'legitimization_serial' + ,table_name + ,partition_suffix + ); +END $$; + +COMMENT ON FUNCTION create_table_kyc_attributes + IS 'Creates the kyc_attributes table'; + + +CREATE OR REPLACE FUNCTION constrain_table_kyc_attributes( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'kyc_attributes'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_serial_key ' + 'UNIQUE (kyc_attributes_serial_id)' + ); + -- The legitimization_serial is a foreign key. + -- TODO: due to partitioning by h_payto, we can not simply reference + -- the serial id of the legitimization_processes + -- EXECUTE FORMAT ( + -- 'ALTER TABLE ' || table_name || + -- ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_processes' + -- ' FOREIGN KEY (legitimization_serial) ' + -- ' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE CASCADE + -- ); + -- To search similar users (e.g. during AML checks) + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_similarity_index ' + 'ON ' || table_name || ' ' + '(kyc_prox);' + ); + -- For garbage collection + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_expiration_time ' + 'ON ' || table_name || ' ' + '(expiration_time ASC);' + ); +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('kyc_attributes' + ,'exchange-0003' + ,'create' + ,TRUE + ,FALSE), + ('kyc_attributes' + ,'exchange-0003' + ,'constrain' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0003-purse_actions.sql b/src/exchangedb/0002-purse_actions.sql diff --git a/src/exchangedb/0003-purse_deletion.sql b/src/exchangedb/0002-purse_deletion.sql diff --git a/src/exchangedb/0002-wire_accounts.sql b/src/exchangedb/0002-wire_accounts.sql @@ -19,6 +19,9 @@ CREATE TABLE wire_accounts ,master_sig BYTEA CHECK (LENGTH(master_sig)=64) ,is_active BOOLEAN NOT NULL ,last_change INT8 NOT NULL + ,conversion_url VARCHAR DEFAULT (NULL) + ,debit_restrictions VARCHAR DEFAULT (NULL) + ,credit_restrictions VARCHAR DEFAULT (NULL) ); COMMENT ON TABLE wire_accounts IS 'Table with current and historic bank accounts of the exchange. Entries never expire as we need to remember the last_change column indefinitely.'; @@ -30,5 +33,13 @@ COMMENT ON COLUMN wire_accounts.is_active IS 'true if we are currently supporting the use of this account.'; COMMENT ON COLUMN wire_accounts.last_change IS 'Latest time when active status changed. Used to detect replays of old messages.'; +COMMENT ON COLUMN wire_accounts.conversion_url + IS 'URL of a currency conversion service if conversion is needed when this account is used; NULL if there is no conversion.'; +COMMENT ON COLUMN wire_accounts.debit_restrictions + IS 'JSON array describing restrictions imposed when debiting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; +COMMENT ON COLUMN wire_accounts.credit_restrictions + IS 'JSON array describing restrictions imposed when crediting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; + + -- "wire_accounts" has no sequence because it is a 'mutable' table -- and is of no concern to the auditor diff --git a/src/exchangedb/0003-kyc_attributes.sql b/src/exchangedb/0003-kyc_attributes.sql @@ -1,156 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> --- - -CREATE OR REPLACE FUNCTION create_table_kyc_attributes( - IN partition_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'kyc_attributes'; -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(kyc_attributes_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' - ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)' - ',kyc_prox BYTEA NOT NULL CHECK (LENGTH(kyc_prox)=32)' - ',provider VARCHAR NOT NULL' - ',birthdate VARCHAR' - ',collection_time INT8 NOT NULL' - ',expiration_time INT8 NOT NULL' - ',encrypted_attributes BYTEA NOT NULL' - ',legitimization_serial INT8 NOT NULL' - ') %s ;' - ,table_name - ,'PARTITION BY HASH (h_payto)' - ,partition_suffix - ); - PERFORM comment_partitioned_table( - 'KYC data about particular payment addresses' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'hash of payto://-URI the attributes are about' - ,'h_payto' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'short hash of normalized full name and birthdate; used to efficiently find likely duplicate users' - ,'kyc_prox' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'birth date of the user, in format YYYY-MM-DD where a value of 0 is used to indicate unknown (in official documents); NULL if the birth date was not collected by the provider; used for KYC-driven age restrictions' - ,'birthdate' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'time when the attributes were collected by the provider' - ,'collection_time' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'time when the attributes should no longer be considered validated' - ,'expiration_time' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'configuration section name of the provider that affirmed the attributes' - ,'provider' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - '(encrypted) JSON object (as string) with the attributes' - ,'encrypted_attributes' - ,table_name - ,partition_suffix - ); - PERFORM comment_partitioned_column( - 'Reference the legitimization process for which theses attributes are gathered for.' - ,'legitimization_serial' - ,table_name - ,partition_suffix - ); -END $$; - -COMMENT ON FUNCTION create_table_kyc_attributes - IS 'Creates the kyc_attributes table'; - - -CREATE OR REPLACE FUNCTION constrain_table_kyc_attributes( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'kyc_attributes'; -BEGIN - table_name = concat_ws('_', table_name, partition_suffix); - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' ADD CONSTRAINT ' || table_name || '_serial_key ' - 'UNIQUE (kyc_attributes_serial_id)' - ); - -- The legitimization_serial is a foreign key. - -- TODO: due to partitioning by h_payto, we can not simply reference - -- the serial id of the legitimization_processes - -- EXECUTE FORMAT ( - -- 'ALTER TABLE ' || table_name || - -- ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_processes' - -- ' FOREIGN KEY (legitimization_serial) ' - -- ' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE CASCADE - -- ); - -- To search similar users (e.g. during AML checks) - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_similarity_index ' - 'ON ' || table_name || ' ' - '(kyc_prox);' - ); - -- For garbage collection - EXECUTE FORMAT ( - 'CREATE INDEX ' || table_name || '_expiration_time ' - 'ON ' || table_name || ' ' - '(expiration_time ASC);' - ); -END $$; - - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('kyc_attributes' - ,'exchange-0003' - ,'create' - ,TRUE - ,FALSE), - ('kyc_attributes' - ,'exchange-0003' - ,'constrain' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0004-kyc_attributes.sql b/src/exchangedb/0004-kyc_attributes.sql @@ -1,44 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2023 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> --- - -CREATE OR REPLACE FUNCTION master_table_kyc_attributes_V2() -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - table_name VARCHAR DEFAULT 'kyc_attributes'; -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE ' || table_name || - ' DROP COLUMN birthdate;' - ); -END $$; - -COMMENT ON FUNCTION master_table_kyc_attributes_V2 - IS 'Removes birthdate column from the kyc_attributes table'; - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('kyc_attributes_V2' - ,'exchange-0004' - ,'master' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/0004-wire_accounts.sql b/src/exchangedb/0004-wire_accounts.sql @@ -1,26 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2023 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> --- - -ALTER TABLE wire_accounts - ADD COLUMN conversion_url VARCHAR DEFAULT (NULL), - ADD COLUMN debit_restrictions VARCHAR DEFAULT (NULL), - ADD COLUMN credit_restrictions VARCHAR DEFAULT (NULL); -COMMENT ON COLUMN wire_accounts.conversion_url - IS 'URL of a currency conversion service if conversion is needed when this account is used; NULL if there is no conversion.'; -COMMENT ON COLUMN wire_accounts.debit_restrictions - IS 'JSON array describing restrictions imposed when debiting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; -COMMENT ON COLUMN wire_accounts.credit_restrictions - IS 'JSON array describing restrictions imposed when crediting this account. Empty for no restrictions, NULL if account was migrated from previous database revision or account is disabled.'; diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am @@ -18,19 +18,13 @@ sqlinputs = \ exchange_do_*.sql \ procedures.sql.in \ 0002-*.sql \ - exchange-0002.sql.in \ - 0003-*.sql \ - exchange-0003.sql.in \ - 0004-*.sql \ - exchange-0004.sql.in + exchange-0002.sql.in sql_DATA = \ benchmark-0001.sql \ versioning.sql \ exchange-0001.sql \ exchange-0002.sql \ - exchange-0003.sql \ - exchange-0004.sql \ drop.sql \ procedures.sql @@ -41,9 +35,7 @@ BUILT_SOURCES = \ procedures.sql CLEANFILES = \ - exchange-0002.sql \ - exchange-0003.sql \ - exchange-0004.sql + exchange-0002.sql procedures.sql: procedures.sql.in exchange_do_*.sql chmod +w $@ || true @@ -55,16 +47,6 @@ exchange-0002.sql: exchange-0002.sql.in 0002-*.sql gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ -exchange-0003.sql: exchange-0003.sql.in 0003-*.sql - chmod +w $@ || true - gcc -E -P -undef - < exchange-0003.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ - chmod ugo-w $@ - -exchange-0004.sql: exchange-0004.sql.in 0004-*.sql - chmod +w $@ || true - gcc -E -P -undef - < exchange-0004.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ - chmod ugo-w $@ - check_SCRIPTS = \ test_idempotency.sh diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in @@ -86,5 +86,14 @@ COMMENT ON TYPE exchange_do_array_reserve_insert_return_type #include "0002-revolving_work_shards.sql" #include "0002-partners.sql" #include "0002-partner_accounts.sql" +#include "0002-purse_actions.sql" +#include "0002-purse_deletion.sql" +#include "0002-kyc_attributes.sql" +#include "0002-aml_status.sql" +#include "0002-aml_staff.sql" +#include "0002-aml_history.sql" +#include "0002-age_withdraw.sql" + + COMMIT; diff --git a/src/exchangedb/exchange-0003.sql.in b/src/exchangedb/exchange-0003.sql.in @@ -19,13 +19,6 @@ BEGIN; SELECT _v.register_patch('exchange-0003', NULL, NULL); SET search_path TO exchange; -#include "0003-purse_actions.sql" -#include "0003-purse_deletion.sql" -#include "0003-kyc_attributes.sql" -#include "0003-aml_status.sql" -#include "0003-aml_staff.sql" -#include "0003-aml_history.sql" -#include "0003-age_withdraw.sql" COMMIT; diff --git a/src/exchangedb/exchange-0004.sql.in b/src/exchangedb/exchange-0004.sql.in @@ -1,25 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2023 Taler Systems SA --- --- TALER is free software; you can redistribute it and/or modify it under the --- terms of the GNU General Public License as published by the Free Software --- Foundation; either version 3, or (at your option) any later version. --- --- TALER is distributed in the hope that it will be useful, but WITHOUT ANY --- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR --- A PARTICULAR PURPOSE. See the GNU General Public License for more details. --- --- You should have received a copy of the GNU General Public License along with --- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> --- - -BEGIN; - -SELECT _v.register_patch('exchange-0004', NULL, NULL); -SET search_path TO exchange; - -#include "0004-kyc_attributes.sql" -#include "0004-wire_accounts.sql" - -COMMIT;