From ed80b10ec0d42de13c7fce737ae2aaef3f3ef580 Mon Sep 17 00:00:00 2001 From: Bernard Blackham Date: Fri, 25 Jun 2004 13:01:47 +0000 Subject: [PATCH] Support an external dropper. --- sql-edition/schema/d.sql | 77 ++++++++++++++++++++++------------------ 1 file changed, 42 insertions(+), 35 deletions(-) diff --git a/sql-edition/schema/d.sql b/sql-edition/schema/d.sql index 4733e5a..110a2e7 100644 --- a/sql-edition/schema/d.sql +++ b/sql-edition/schema/d.sql @@ -230,9 +230,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 +262,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 +374,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 +390,13 @@ INSERT INTO items VALUES ('coke', 'coke powder', 80, array['6'], NULL, 11); INSERT INTO items VALUES ('coke', 'update_coke', NULL, NULL, ' ', NULL); +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 +417,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 +492,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 +509,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 +519,15 @@ 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 TABLE vend_requests ( + request_id integer, + request_slot varchar(2), + request_handled boolean NOT NULL DEFAULT false, + request_time timestamp DEFAULT now() +); + CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS - ' - BEGIN - RETURN $1 ~ ''^[0-9]{2}$''; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; + '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 +550,13 @@ 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!)); + INSERT INTO vend_requests VALUES ($1, $2); + NOTIFY vend_requests; ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION vend_slot(varchar(2)) FROM public; +REVOKE ALL ON FUNCTION vend_slot(integer, varchar(2)) FROM public; CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS ' @@ -597,7 +605,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 +624,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 +641,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 +709,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; -- 2.20.1