misc foo

[zanchey/dispense2.git] / sql-edition / schema / d.sql

diff --git a/sql-edition/schema/d.sql b/sql-edition/schema/d.sql
index 4733e5a..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;
@@ -230,9 +237,9 @@ REVOKE ALL ON FUNCTION final_request_handler() FROM public;
 CREATE TRIGGER zzz_last_request_trigger BEFORE INSERT ON requests FOR EACH ROW
        EXECUTE PROCEDURE final_request_handler();
 
----------------------------------------------------------------------------------
---- Dummy Services                                                            ---
----------------------------------------------------------------------------------
+------------------------------------------------------------------------------
+--- Dummy Services                                                         ---
+------------------------------------------------------------------------------
 
 INSERT INTO sources VALUES ('nothing');
 
@@ -262,9 +269,9 @@ CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
        LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
 
----------------------------------------------------------------------------------
---- Coke Bank Services                                                        ---
----------------------------------------------------------------------------------
+------------------------------------------------------------------------------
+--- Coke Bank Services                                                     ---
+------------------------------------------------------------------------------
 
 INSERT INTO sources VALUES ('cokebank');
 
@@ -374,9 +381,9 @@ CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
        LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
 
----------------------------------------------------------------------------------
---- Coke Machine Services                                                     ---
----------------------------------------------------------------------------------
+------------------------------------------------------------------------------
+--- Coke Machine Services                                                  ---
+------------------------------------------------------------------------------
 
 INSERT INTO sources VALUES ('coke');
 
@@ -390,6 +397,14 @@ 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,
+       request_handled boolean NOT NULL DEFAULT false,
+       request_time timestamp DEFAULT now()
+);
+
 CREATE OR REPLACE FUNCTION update_slot_info(integer, varchar(20), integer, integer) RETURNS void AS
        '
        BEGIN
@@ -410,16 +425,13 @@ CREATE OR REPLACE FUNCTION update_slot_info(integer, varchar(20), integer, integ
        ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION update_slot_info(integer, varchar, integer, integer) FROM public;
 
-CREATE OR REPLACE FUNCTION dispense_slot(integer) RETURNS void AS
+CREATE OR REPLACE FUNCTION dispense_slot(integer, integer) RETURNS void AS
        '
        elog(NOTICE, q(And out pops a drink!));
+       INSERT INTO coke_requests VALUES ($1, $2);
+       NOTIFY coke_requests;
        ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
-REVOKE ALL ON FUNCTION dispense_slot(integer) FROM public;
-
-CREATE OR REPLACE FUNCTION update_slot_status() RETURNS void AS
-       '
-       ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
-REVOKE ALL ON FUNCTION update_slot_status() FROM public;
+REVOKE ALL ON FUNCTION dispense_slot(integer, integer) FROM public;
 
 CREATE OR REPLACE FUNCTION coke_slot_trigger() RETURNS "trigger" AS
        '
@@ -488,8 +500,7 @@ CREATE OR REPLACE FUNCTION coke_slot_dispense() RETURNS "trigger" AS
                IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN
                        -- Drop a drink and update stock
                        SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''coke'';
-                       PERFORM dispense_slot(slotnum);
-                       PERFORM update_slot_status();
+                       PERFORM dispense_slot(NEW.request_id, slotnum);
                END IF;
                RETURN NULL;
        END;
@@ -506,9 +517,9 @@ CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
        LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
 
----------------------------------------------------------------------------------
---- Vending Machine Services                                                  ---
----------------------------------------------------------------------------------
+------------------------------------------------------------------------------
+--- Vending Machine Services                                               ---
+------------------------------------------------------------------------------
 
 INSERT INTO sources VALUES ('vend');
 
@@ -516,12 +527,34 @@ 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);
 
-CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS
+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
-               RETURN $1 ~ ''^[0-9]{2}$'';
+               UPDATE vend_requests SET request_handled = true WHERE request_id = $1;
+               PERFORM refund($1);
        END;
-       ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
+       '
+       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;
 REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
 
 CREATE OR REPLACE FUNCTION update_vend_slot_info(varchar(2), varchar(20), integer, integer) RETURNS void AS
@@ -544,11 +577,16 @@ CREATE OR REPLACE FUNCTION update_vend_slot_info(varchar(2), varchar(20), intege
        ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION update_vend_slot_info(varchar(2), varchar, integer, integer) FROM public;
 
-CREATE OR REPLACE FUNCTION vend_slot(varchar(2)) RETURNS void AS
+CREATE OR REPLACE FUNCTION vend_slot(integer, varchar(2)) RETURNS void AS
        '
-       elog(NOTICE, q(And out pops a snack!));
-       ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
-REVOKE ALL ON FUNCTION vend_slot(varchar(2)) FROM public;
+       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
        '
@@ -597,7 +635,7 @@ CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS
                -- deducted money et al.
                --
                -- So if we get this far, we don''t need to care about much.
-               -- Dropping the drink & checking stock occurs in the AFTER-INSERT trigger.
+               -- Dropping the snack occurs in the AFTER-INSERT trigger.
 
                -- Mark the request as having been dealt with
                NEW.handled = true;
@@ -616,7 +654,7 @@ CREATE OR REPLACE FUNCTION vend_slot_dispense() RETURNS "trigger" AS
                IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN
                        -- Drop a snack and update stock
                        SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''vend'';
-                       PERFORM vend_slot(slotnum);
+                       PERFORM vend_slot(NEW.request_id, slotnum);
                END IF;
                RETURN NULL;
        END;
@@ -633,9 +671,9 @@ CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
        LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
 
----------------------------------------------------------------------------------
---- UCC Door Services                                                         ---
----------------------------------------------------------------------------------
+------------------------------------------------------------------------------
+--- UCC Door Services                                                      ---
+------------------------------------------------------------------------------
 
 CREATE TABLE doorcontrollers (
     user_name varchar(16) UNIQUE references users(user_name),
@@ -701,7 +739,6 @@ CREATE TRIGGER door_open_do AFTER INSERT ON requests FOR EACH ROW
 DROP FUNCTION can_show_pre_door(varchar, varchar);
 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_door;
 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
-       'SELECT can_show_pre_door($1, $2) AND NOT ($2 = ''opendoor'' AND NOT in_door($1))'
-       LANGUAGE SQL EXTERNAL SECURITY DEFINER;
+       'SELECT can_show_pre_door($1, $2) AND NOT ($2 = ''opendoor'' AND (NOT in_door($1)))' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;