From cb87b6f646888bf62af31e8b23bb642de9a57344 Mon Sep 17 00:00:00 2001 From: Joseph Date: Mon, 27 Mar 2023 07:23:27 -0400 Subject: New spi files --- src/exchangedb/spi/own_test.sql | 216 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 216 insertions(+) create mode 100644 src/exchangedb/spi/own_test.sql (limited to 'src/exchangedb/spi/own_test.sql') diff --git a/src/exchangedb/spi/own_test.sql b/src/exchangedb/spi/own_test.sql new file mode 100644 index 000000000..369c56a60 --- /dev/null +++ b/src/exchangedb/spi/own_test.sql @@ -0,0 +1,216 @@ + +DROP TABLE joseph_test.X; +CREATE TABLE joseph_test.X ( + a integer +); + +INSERT INTO joseph_test.X (a) VALUES (1), (2), (3), (4), (5), (6), (7); + +DROP TABLE joseph_test.Y; +CREATE TABLE joseph_test.Y (col1 INT, col2 INT); +INSERT INTO joseph_test.Y (col1,col2) VALUES (1,2), (2,0), (0,4), (4,0), (0,6), (6,7), (7,8); + +DROP TABLE joseph_test.Z; +CREATE TABLE joseph_test.Z(col1 BYTEA); +DROP TABLE deposits; +/*CREATE TABLE deposits( + deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,shard INT8 NOT NULL + ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) + ,known_coin_id INT8 NOT NULL + ,amount_with_fee_val INT8 NOT NULL + ,amount_with_fee_frac INT4 NOT NULL + ,wallet_timestamp INT8 NOT NULL + ,exchange_timestamp INT8 NOT NULL + ,refund_deadline INT8 NOT NULL + ,wire_deadline INT8 NOT NULL + ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) + ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) + ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) + ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16) + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) + ,done BOOLEAN NOT NULL DEFAULT FALSE + ,policy_blocked BOOLEAN NOT NULL DEFAULT FALSE + ,policy_details_serial_id INT8); +*/ +--INSERT INTO deposits VALUES (); + + + +CREATE TABLE deposits( + deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,shard INT8 NOT NULL + ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) + ,known_coin_id INT8 NOT NULL + ,amount_with_fee_val INT8 NOT NULL + ,amount_with_fee_frac INT4 NOT NULL + ,wallet_timestamp INT8 NOT NULL + ,exchange_timestamp INT8 NOT NULL + ,refund_deadline INT8 NOT NULL + ,wire_deadline INT8 NOT NULL + ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) + ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) + ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) + ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16) + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) + ,done BOOLEAN NOT NULL DEFAULT FALSE + ,policy_blocked BOOLEAN NOT NULL DEFAULT FALSE + ,policy_details_serial_id INT8); + + +CREATE OR REPLACE FUNCTION pg_spi_insert_int() + RETURNS VOID + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_insert_int'; +DROP FUNCTION pg_spi_select_from_x(); +CREATE OR REPLACE FUNCTION pg_spi_select_from_x() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_select_from_x'; + +/*DROP FUNCTION pg_spi_select_pair_from_y(); +CREATE OR REPLACE FUNCTION pg_spi_select_pair_from_y() + RETURNS valuest + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_select_pair_from_y'; +*/ +/*CREATE OR REPLACE FUNCTION pg_spi_select_with_cond() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_select_with_cond'; +*/ +DROP FUNCTION pg_spi_update_y(); +CREATE OR REPLACE FUNCTION pg_spi_update_y() + RETURNS VOID + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_update_y'; +DROP FUNCTION pg_spi_prepare_example(); + +CREATE OR REPLACE FUNCTION pg_spi_prepare_example() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_example'; + +DROP FUNCTION pg_spi_prepare_example_without_saveplan(); +CREATE OR REPLACE FUNCTION pg_spi_prepare_example_without_saveplan() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_example_without_saveplan'; + +CREATE OR REPLACE FUNCTION pg_spi_prepare_insert() + RETURNS VOID + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_insert'; + +CREATE OR REPLACE FUNCTION pg_spi_prepare_insert_without_saveplan() + RETURNS VOID + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_insert_without_saveplan'; + +/*DROP FUNCTION pg_spi_prepare_select_with_cond(); +CREATE OR REPLACE FUNCTION pg_spi_prepare_select_with_cond() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond'; +*/ +DROP FUNCTION pg_spi_prepare_select_with_cond_without_saveplan(); +CREATE OR REPLACE FUNCTION pg_spi_prepare_select_with_cond_without_saveplan() + RETURNS INT8 + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond_without_saveplan'; + +DROP FUNCTION pg_spi_prepare_update(); +CREATE OR REPLACE FUNCTION pg_spi_prepare_update() + RETURNS VOID + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_prepare_update'; + +DROP FUNCTION pg_spi_get_dep_ref_fees( + IN in_timestamp INT8 + ,IN merchant_pub BYTEA + ,IN wire_target_h_payto BYTEA + ,IN wtid BYTEA); +CREATE OR REPLACE FUNCTION pg_spi_get_dep_ref_fees( + IN in_timestamp INT8 + ,IN merchant_pub BYTEA + ,IN wire_target_h_payto BYTEA + ,IN wtid BYTEA +) + RETURNS VOID + LANGUAGE c COST 100 +AS '$libdir/own_test', 'pg_spi_get_dep_ref_fees'; + +CREATE OR REPLACE FUNCTION update_pg_spi_get_dep_ref_fees( + IN in_refund_deadline INT8, + IN in_merchant_pub BYTEA, + IN in_wire_target_h_payto BYTEA +) +RETURNS SETOF record +LANGUAGE plpgsql VOLATILE +AS $$ +DECLARE + +BEGIN +RETURN QUERY + UPDATE deposits + SET done = TRUE + WHERE NOT (done OR policy_blocked) + AND refund_deadline < in_refund_deadline + AND merchant_pub = in_merchant_pub + AND wire_target_h_payto = in_wire_target_h_payto + RETURNING + deposit_serial_id, + coin_pub, + amount_with_fee_val, + amount_with_fee_frac; +END $$; + +CREATE OR REPLACE FUNCTION stored_procedure_update( +IN in_number INT8 +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + UPDATE joseph_test.Y + SET col1 = 4 + WHERE col2 = in_number; +END $$; + +CREATE OR REPLACE FUNCTION stored_procedure_select(OUT out_value INT8) +RETURNS INT8 +LANGUAGE plpgsql +AS $$ +BEGIN + SELECT 1 + INTO out_value + FROM joseph_test.X; + RETURN; +END $$; + + +CREATE OR REPLACE FUNCTION stored_procedure_insert( +IN in_number INT8, +OUT out_number INT8) +RETURNS INT8 +LANGUAGE plpgsql +AS $$ +BEGIN + INSERT INTO joseph_test.X (a) + VALUES (in_number) + RETURNING a INTO out_number; +END $$; + +CREATE OR REPLACE FUNCTION stored_procedure_select_with_cond( +IN in_number INT8, +OUT out_number INT8 +) +RETURNS INT8 +LANGUAGE plpgsql +AS $$ +BEGIN + SELECT col1 INTO out_number + FROM joseph_test.Y + WHERE col2 = in_number; + RETURN; +END $$; -- cgit v1.2.3