sync-0001.sql (3439B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2021, 2023 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 17 -- Everything in one big transaction 18 BEGIN; 19 20 -- Check patch versioning is in place. 21 SELECT _v.register_patch('sync-0001', NULL, NULL); 22 23 CREATE SCHEMA sync; 24 COMMENT ON SCHEMA sync IS 'sync data'; 25 26 SET search_path TO sync; 27 28 CREATE TYPE taler_amount 29 AS 30 (val INT8 31 ,frac INT4 32 ); 33 COMMENT ON TYPE taler_amount 34 IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; 35 36 CREATE TYPE sync_do_lookup_block_range_return_type 37 AS 38 (out_account_missing BOOLEAN 39 ,out_block_missing BOOLEAN 40 ,out_backup_empty BOOLEAN 41 ,nonce BYTEA 42 ,block_hash BYTEA 43 ,prev_nonce BYTEA 44 ,next_nonce BYTEA 45 ,data_blob BYTEA 46 ,upload_sig BYTEA 47 ,old_hash BYTEA 48 ,refs_hash BYTEA 49 ); 50 51 CREATE TYPE sync_do_lookup_block_return_type 52 AS 53 (out_account_missing BOOLEAN 54 ,out_block_missing BOOLEAN 55 ,out_is_first BOOLEAN 56 ,out_is_last BOOLEAN 57 ,nonce BYTEA 58 ,block_hash BYTEA 59 ,prev_nonce BYTEA 60 ,next_nonce BYTEA 61 ,data_size INT8 62 ,refs_hash BYTEA 63 ); 64 65 CREATE TABLE IF NOT EXISTS accounts 66 (account_pub BYTEA PRIMARY KEY CHECK (length(account_pub)=32) 67 ,expiration_date INT8 NOT NULL); 68 69 CREATE INDEX IF NOT EXISTS accounts_expire ON 70 accounts (expiration_date); 71 72 CREATE TABLE IF NOT EXISTS payments 73 (account_pub BYTEA REFERENCES accounts (account_pub) ON DELETE CASCADE 74 ,order_id TEXT PRIMARY KEY 75 ,claim_token BYTEA CHECK (length(claim_token)=16) 76 ,order_timestamp INT8 NOT NULL 77 ,amount taler_amount NOT NULL 78 ,paid BOOLEAN NOT NULL DEFAULT FALSE); 79 80 CREATE INDEX IF NOT EXISTS payments_timestamp ON 81 payments (paid, order_timestamp); 82 83 CREATE TABLE IF NOT EXISTS blocks 84 (nonce BYTEA PRIMARY KEY CHECK (length(nonce)=24) 85 ,account_pub BYTEA REFERENCES accounts (account_pub) ON DELETE CASCADE 86 ,block_hash BYTEA NOT NULL CHECK (length(block_hash)=64) 87 ,prev_nonce BYTEA REFERENCES blocks (nonce) ON DELETE RESTRICT 88 ,next_nonce BYTEA REFERENCES blocks (nonce) ON DELETE RESTRICT 89 ,data_blob BYTEA NOT NULL 90 ,upload_sig BYTEA NOT NULL DEFAULT '' 91 ,old_hash BYTEA NOT NULL DEFAULT '' 92 ,refs_hash BYTEA NOT NULL DEFAULT ''); 93 94 ALTER TABLE IF EXISTS accounts 95 ADD COLUMN IF NOT EXISTS first_block BYTEA REFERENCES blocks (nonce) ON DELETE CASCADE, 96 ADD COLUMN IF NOT EXISTS last_block BYTEA REFERENCES blocks (nonce) ON DELETE CASCADE; 97 98 CREATE TABLE IF NOT EXISTS objects 99 (uid BYTEA PRIMARY KEY CHECK (length(uid)=64) 100 ,account_pub BYTEA REFERENCES accounts (account_pub) ON DELETE CASCADE 101 ,object_hash BYTEA NOT NULL CHECK (length(object_hash)=64) 102 ,refcount INT8 NOT NULL DEFAULT 0 CHECK (refcount >= 0) 103 ,expiration_date INT8 104 ,data_blob BYTEA NOT NULL); 105 106 CREATE INDEX IF NOT EXISTS objects_expire ON 107 objects (expiration_date) 108 WHERE refcount = 0; 109 110 -- Complete transaction 111 COMMIT;