libeufin-nexus-0007.sql (4166B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024 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-nexus-0007', NULL, NULL); 19 20 SET search_path TO libeufin_nexus; 21 22 -- Add a new submission state reusing a currently unused slot 23 ALTER TYPE submission_state RENAME VALUE 'never_heard_back' TO 'pending'; 24 ALTER TYPE submission_state ADD VALUE 'late_failure'; 25 26 -- Batch of initiated_outgoing_transactions 27 CREATE TABLE initiated_outgoing_batches( 28 initiated_outgoing_batch_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE, 29 creation_date INT8 NOT NULL, 30 sum taler_amount NOT NULL DEFAULT (0, 0), 31 message_id TEXT NOT NULL UNIQUE CHECK (char_length(message_id) <= 35), 32 order_id TEXT UNIQUE, 33 submission_date INT8, 34 submission_counter INT4 NOT NULL DEFAULT 0, 35 status submission_state NOT NULL DEFAULT 'unsubmitted', 36 status_msg TEXT 37 ); 38 COMMENT ON COLUMN initiated_outgoing_transactions.order_id 39 IS 'Order ID of the EBICS upload transaction, used to track EBICS order status.'; 40 41 -- Add batch column to initiated_outgoing_transactions 42 ALTER TABLE initiated_outgoing_transactions 43 ADD COLUMN initiated_outgoing_batch_id INT8 REFERENCES initiated_outgoing_batches (initiated_outgoing_batch_id); 44 45 -- Create a batch for all existing initiated_outgoing_transactions 46 INSERT INTO initiated_outgoing_batches(creation_date, message_id, order_id, submission_date, submission_counter, status, status_msg) 47 SELECT initiation_time, request_uid, order_id, last_submission_time, submission_counter, (CASE WHEN submitted = 'success' OR submitted = 'permanent_failure' THEN 'success' ELSE 'pending' END)::submission_state, failure_message 48 FROM initiated_outgoing_transactions; 49 50 -- Link initiated_outgoing_transactions to their initiated_outgoing_batches 51 UPDATE initiated_outgoing_transactions SET initiated_outgoing_batch_id = ( 52 SELECT initiated_outgoing_batch_id FROM initiated_outgoing_batches WHERE request_uid=message_id 53 ); 54 55 -- Drop now unused columns from initiated_outgoing_transactions and rename some 56 ALTER TABLE initiated_outgoing_transactions 57 DROP COLUMN order_id, 58 DROP COLUMN last_submission_time, 59 DROP COLUMN submission_counter, 60 DROP COLUMN hidden; 61 62 -- Add necessary indexes 63 CREATE INDEX initiated_outgoing_batches_status_index ON initiated_outgoing_batches (status); 64 COMMENT ON INDEX initiated_outgoing_batches_status_index IS 'for listing taler batch by status for a future admin UI'; 65 CREATE INDEX initiated_outgoing_transactions_batch_index ON initiated_outgoing_transactions (initiated_outgoing_batch_id); 66 COMMENT ON INDEX initiated_outgoing_transactions_batch_index IS 'for listing transactions in batches'; 67 68 -- Renaming 69 ALTER TABLE incoming_transactions RENAME COLUMN wire_transfer_subject TO subject; 70 ALTER TABLE incoming_transactions RENAME COLUMN debit_payto_uri TO debit_payto; 71 ALTER TABLE outgoing_transactions RENAME COLUMN wire_transfer_subject TO subject; 72 ALTER TABLE outgoing_transactions RENAME COLUMN credit_payto_uri TO credit_payto; 73 ALTER TABLE outgoing_transactions RENAME COLUMN message_id TO end_to_end_id; 74 ALTER TABLE initiated_outgoing_transactions RENAME COLUMN wire_transfer_subject TO subject; 75 ALTER TABLE initiated_outgoing_transactions RENAME COLUMN credit_payto_uri TO credit_payto; 76 ALTER TABLE initiated_outgoing_transactions RENAME COLUMN submitted TO status; 77 ALTER TABLE initiated_outgoing_transactions RENAME COLUMN failure_message TO status_msg; 78 ALTER TABLE initiated_outgoing_transactions RENAME COLUMN request_uid TO end_to_end_id; 79 COMMIT;