X-Git-Url: https://git.ucc.asn.au/?p=zanchey%2Fdispense2.git;a=blobdiff_plain;f=sql-edition%2Fschema%2Fd.sql;h=6ef9f57f23b4e350a074f03f9894218a72156b9c;hp=4733e5a9109b9bd5b73ffe4def43beb5c222980f;hb=0a3a7ac40cf6544543691a4ffa03df77a79a5329;hpb=2b7498ef0aedc319f95d3b5994e7ac6ac5d4c8ee;ds=sidebyside diff --git a/sql-edition/schema/d.sql b/sql-edition/schema/d.sql index 4733e5a..6ef9f57 100644 --- 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, ' ', 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, ' ', 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;