grandfather clock

[uccvend-vendserver.git] / sql-edition / schema / d.sql

diff --git a/sql-edition/schema/d.sql b/sql-edition/schema/d.sql
index 110a2e7..6ef9f57 100644 (file)
--- a/sql-edition/schema/d.sql
+++ b/sql-edition/schema/d.sql
@@ -111,6 +111,13 @@ GRANT EXECUTE ON FUNCTION get_balance() TO anondispense;
 
 -- internal functions follow
 
+CREATE OR REPLACE FUNCTION refund(integer) RETURNS void AS
+-- FIXME XXX DO this.
+       '
+       '
+       LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
+REVOKE ALL ON FUNCTION refund(integer) FROM public;
+
 CREATE OR REPLACE FUNCTION in_coke(varchar(16)) RETURNS boolean
     AS 'SELECT EXISTS(SELECT * FROM cokecontrollers WHERE user_name = $1)'
     LANGUAGE sql EXTERNAL SECURITY DEFINER;
@@ -390,6 +397,7 @@ INSERT INTO items VALUES ('coke', 'coke powder',    80, array['6'], NULL, 11);
 
 INSERT INTO items VALUES ('coke', 'update_coke',       NULL, NULL, '<slot number> <new name> <new price> <stock count>', NULL);
 
+DROP TABLE coke_requests;
 CREATE TABLE coke_requests (
        request_id integer,
     request_slot integer,
@@ -519,12 +527,31 @@ INSERT INTO items VALUES ('vend', 'twisties',                     120, array['11'], NULL, 8);
 INSERT INTO items VALUES ('vend', 'cheese and onion',  125, array['21'], NULL, 0);
 INSERT INTO items VALUES ('vend', 'update_vend', NULL, NULL, '<slot number> <new name> <new price> <new count>', NULL);
 
+DROP TABLE vend_requests;
 CREATE TABLE vend_requests (
        request_id integer,
     request_slot varchar(2),
        request_handled boolean NOT NULL DEFAULT false,
        request_time timestamp DEFAULT now()
 );
+GRANT SELECT ON vend_requests TO vendserver;
+
+CREATE OR REPLACE FUNCTION vend_success(integer) RETURNS void AS
+       'UPDATE vend_requests SET request_handled = true WHERE request_id = $1' 
+       LANGUAGE SQL EXTERNAL SECURITY DEFINER;
+REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
+GRANT EXECUTE ON FUNCTION vend_success(integer) TO vendserver;
+
+CREATE OR REPLACE FUNCTION vend_failed(integer) RETURNS void AS
+       '
+       BEGIN
+               UPDATE vend_requests SET request_handled = true WHERE request_id = $1;
+               PERFORM refund($1);
+       END;
+       '
+       LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
+REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
+GRANT EXECUTE ON FUNCTION vend_failed(integer) TO vendserver;
 
 CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS
        'SELECT $1 ~ ''^[0-9]{2}$''' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
@@ -552,10 +579,13 @@ REVOKE ALL ON FUNCTION update_vend_slot_info(varchar(2), varchar, integer, integ
 
 CREATE OR REPLACE FUNCTION vend_slot(integer, varchar(2)) RETURNS void AS
        '
-       elog(NOTICE, q(And out pops a snack!));
-       INSERT INTO vend_requests VALUES ($1, $2);
-       NOTIFY vend_requests;
-       ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
+       BEGIN
+               RAISE NOTICE ''And out pops a snack'';
+               INSERT INTO vend_requests (request_id, request_slot) VALUES ($1, $2);
+               NOTIFY vend_requests;
+               RETURN;
+       END;
+       ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION vend_slot(integer, varchar(2)) FROM public;
 
 CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS