New spi files

1 files changed, 216 insertions, 0 deletions

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 $$;