merchant-0026.sql (2081B)
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 17 -- @file merchant-0026.sql 18 -- @brief add hashes for product images 19 -- @author Bohdan Potuzhnyi, Florian Dold 20 -- Release: v1.2.0 21 22 23 BEGIN; 24 25 -- Check patch versioning is in place. 26 SELECT _v.register_patch('merchant-0026', NULL, NULL); 27 28 SET search_path TO public; 29 30 CREATE EXTENSION IF NOT EXISTS pgcrypto; 31 32 -- Was created by merchant-0025.sql 33 ALTER EXTENSION pgcrypto SET SCHEMA public; 34 ALTER EXTENSION pg_trgm SET SCHEMA public; 35 36 SET search_path TO merchant, public; 37 38 ALTER TABLE merchant_inventory 39 ADD COLUMN image_hash TEXT; 40 41 COMMENT ON COLUMN merchant_inventory.image_hash 42 IS 'SHA-256 hash of the base64-encoded image data, used by wallets to fetch product images.'; 43 44 ALTER TABLE merchant_inventory 45 DISABLE TRIGGER trigger_inventory_changes; 46 47 UPDATE merchant_inventory 48 SET image_hash = CASE 49 WHEN (image IS NULL) OR (image = '') 50 THEN NULL 51 ELSE encode( 52 digest( 53 convert_to(image, 'UTF8'), 54 'sha256' 55 ), 56 'hex' 57 ) 58 END; 59 60 ALTER TABLE merchant_inventory 61 ENABLE TRIGGER trigger_inventory_changes; 62 63 CREATE INDEX merchant_inventory_by_image_hash 64 ON merchant_inventory 65 (merchant_serial, image_hash); 66 67 COMMIT;