libeufin-bank-0013.sql (4436B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 BEGIN; 17 18 SELECT _v.register_patch('libeufin-bank-0013', NULL, NULL); 19 20 SET search_path TO libeufin_bank; 21 -- Remove all existing functions 22 DO 23 $do$ 24 BEGIN 25 IF EXISTS (SELECT FROM config WHERE key LIKE 'cashin_%') THEN 26 INSERT INTO config (key, value) VALUES ('conversion_rate', jsonb_build_object( 27 'cashin', jsonb_build_object( 28 'ratio', jsonb_build_object( 29 'val', (SELECT value->'val' FROM config WHERE key='cashin_ratio'), 30 'frac', (SELECT value->'frac' FROM config WHERE key='cashin_ratio') 31 ), 32 'fee', jsonb_build_object( 33 'val', (SELECT value->'val' FROM config WHERE key='cashin_fee'), 34 'frac', (SELECT value->'frac' FROM config WHERE key='cashin_fee') 35 ), 36 'tiny_amount', jsonb_build_object( 37 'val', (SELECT value->'val' FROM config WHERE key='cashin_tiny_amount'), 38 'frac', (SELECT value->'frac' FROM config WHERE key='cashin_tiny_amount') 39 ), 40 'min_amount', jsonb_build_object( 41 'val', (SELECT value->'val' FROM config WHERE key='cashin_min_amount'), 42 'frac', (SELECT value->'frac' FROM config WHERE key='cashin_min_amount') 43 ), 44 'rounding_mode', (SELECT value->'mode' FROM config WHERE key='cashin_rounding_mode') 45 ), 46 'cashout', jsonb_build_object( 47 'ratio', jsonb_build_object( 48 'val', (SELECT value->'val' FROM config WHERE key='cashout_ratio'), 49 'frac', (SELECT value->'frac' FROM config WHERE key='cashout_ratio') 50 ), 51 'fee', jsonb_build_object( 52 'val', (SELECT value->'val' FROM config WHERE key='cashout_fee'), 53 'frac', (SELECT value->'frac' FROM config WHERE key='cashout_fee') 54 ), 55 'tiny_amount', jsonb_build_object( 56 'val', (SELECT value->'val' FROM config WHERE key='cashout_tiny_amount'), 57 'frac', (SELECT value->'frac' FROM config WHERE key='cashout_tiny_amount') 58 ), 59 'min_amount', jsonb_build_object( 60 'val', (SELECT value->'val' FROM config WHERE key='cashout_min_amount'), 61 'frac', (SELECT value->'frac' FROM config WHERE key='cashout_min_amount') 62 ), 63 'rounding_mode', (SELECT value->'mode' FROM config WHERE key='cashout_rounding_mode') 64 ) 65 ) 66 ); 67 DELETE FROM config WHERE key LIKE 'cashin_%' OR key LIKE 'cashout_%'; 68 END IF; 69 END 70 $do$; 71 72 CREATE TABLE conversion_rate_classes 73 ( conversion_rate_class_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 74 ,name TEXT NOT NULL UNIQUE 75 ,description TEXT 76 ,cashin_ratio taler_amount 77 ,cashin_fee taler_amount 78 ,cashin_min_amount taler_amount 79 ,cashin_rounding_mode rounding_mode 80 ,cashout_ratio taler_amount 81 ,cashout_fee taler_amount 82 ,cashout_min_amount taler_amount 83 ,cashout_rounding_mode rounding_mode 84 ); 85 COMMENT ON TABLE conversion_rate_classes IS 'TODO'; 86 ALTER TABLE bank_accounts ADD COLUMN conversion_rate_class_id INT4 REFERENCES conversion_rate_classes(conversion_rate_class_id); 87 88 -- Migrate existing user config 89 INSERT INTO conversion_rate_classes(name, cashout_min_amount) 90 SELECT format('migrated min_cashout=%s.%s', (min_cashout).val, TRIM(TRAILING '0' FROM LPAD((min_cashout).frac::text, 8, '0'))), min_cashout 91 FROM bank_accounts 92 WHERE min_cashout IS NOT NULL 93 GROUP BY min_cashout; 94 UPDATE bank_accounts SET conversion_rate_class_id=( 95 SELECT conversion_rate_class_id FROM conversion_rate_classes WHERE cashout_min_amount=min_cashout 96 ) WHERE min_cashout IS NOT NULL; 97 ALTER TABLE bank_accounts DROP COLUMN min_cashout; 98 99 CREATE INDEX accounts_conversion_rate_class_id ON bank_accounts (conversion_rate_class_id); 100 COMMENT ON INDEX accounts_conversion_rate_class_id 101 IS 'link accounts to their conversion rate class'; 102 COMMIT;