------------------------------------------------------------------------------ ------------------------------------------------------------------------------ --- --- --- DISPENSE IN POSTGRES REWRITE --- --- Bernard Blackham --- --- --- ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ /* All the operations are done */ \connect 'dispense'; DROP TABLE requests; DROP SEQUENCE request_id_seq; DROP FUNCTION get_services(varchar); DROP VIEW services; DROP TABLE items; DROP TABLE sources; DROP TABLE cokecontrollers; DROP TABLE doorcontrollers; DROP FUNCTION get_balance(); DROP FUNCTION get_balance(varchar); DROP TABLE users; CREATE OR REPLACE FUNCTION get_ident() RETURNS varchar AS 'pg_ident.so', 'get_ident' STRICT LANGUAGE C; REVOKE ALL ON FUNCTION get_ident() FROM public; CREATE TABLE users ( user_name varchar(16) NOT NULL UNIQUE PRIMARY KEY, user_balance_cents integer NOT NULL, user_balance_bytes integer NOT NULL ); CREATE TABLE cokecontrollers ( user_name varchar(16) UNIQUE references users(user_name), reason varchar(250) ); CREATE TABLE sources ( source_name varchar(20) NOT NULL UNIQUE PRIMARY KEY ); CREATE TABLE items ( source_name varchar(20) REFERENCES sources(source_name), item_name varchar(20) NOT NULL UNIQUE PRIMARY KEY, item_cost_cents integer, item_data varchar[], item_syntax varchar(80), item_stock integer -- -1 for "in stock", 0 for "out of stock", > 0 for a value of stock, NULL for N/A ); CREATE VIEW services AS SELECT i.source_name as provider, i.item_name, i.item_cost_cents, i.item_syntax, i.item_stock, i.item_data FROM items i ; CREATE SEQUENCE request_id_seq START 1; CREATE TABLE requests ( request_id integer NOT NULL DEFAULT nextval('request_id_seq'), request_requestor_name varchar(16) REFERENCES users(user_name), request_target_name varchar(16) REFERENCES users(user_name), item_name varchar(20), params varchar[], handled boolean NOT NULL DEFAULT false ); -- request(target username, item_name, params) CREATE OR REPLACE FUNCTION do_request(varchar(16), varchar(20), varchar[]) RETURNS boolean AS ' DECLARE ident VARCHAR; BEGIN ident = get_ident(); IF ident IS NULL THEN RAISE EXCEPTION ''Got a null ident! Eepe!''; END IF; IF NOT EXISTS(SELECT user_name FROM users WHERE user_name = $1) THEN RAISE NOTICE ''Adding user %!!'', $1; INSERT INTO users VALUES ($1, 0, 0); END IF; INSERT INTO requests VALUES (DEFAULT, ident, $1, $2, $3); RETURN true; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION do_request(varchar(16),varchar(20),varchar[]) FROM public; GRANT EXECUTE ON FUNCTION do_request(varchar(16), varchar(20), varchar[]) TO anondispense; -- get_balance(target username) CREATE OR REPLACE FUNCTION get_balance(varchar(16)) RETURNS users AS 'SELECT * FROM users WHERE user_name = $1' LANGUAGE sql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION get_balance(varchar(16)) FROM public; GRANT EXECUTE ON FUNCTION get_balance(varchar(16)) TO anondispense; -- get_balance() ... returns everybody CREATE OR REPLACE FUNCTION get_balance() RETURNS SETOF users AS 'SELECT * FROM users' LANGUAGE sql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION get_balance() FROM public; GRANT EXECUTE ON FUNCTION get_balance() TO anondispense; -- internal functions follow 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; REVOKE ALL ON FUNCTION in_coke(varchar(16)) FROM public; CREATE OR REPLACE FUNCTION byte_to_cent_trigger() RETURNS "trigger" AS ' DECLARE bytes_per_cent INTEGER; cents_per_meg INTEGER; BEGIN cents_per_meg = 4; bytes_per_cent = 1024*1024/cents_per_meg; IF NEW.user_balance_bytes > bytes_per_cent THEN NEW.user_balance_cents = NEW.user_balance_cents - (NEW.user_balance_bytes/bytes_per_cent); NEW.user_balance_bytes = NEW.user_balance_bytes % bytes_per_cent; END IF; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION byte_to_cent_trigger() FROM public; CREATE TRIGGER byte_to_cent_trigger BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE byte_to_cent_trigger(); CREATE OR REPLACE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS ' DECLARE ident varchar; BEGIN IF $1 NOT IN (SELECT user_name FROM users) THEN RETURN false; END IF; ident = get_ident(); IF ident = $1 OR in_coke(ident) THEN RETURN true; END IF; RETURN false; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION can_show(varchar(16), varchar(20)) FROM public; CREATE OR REPLACE FUNCTION get_services(varchar(16)) RETURNS SETOF services AS 'SELECT * from services WHERE can_show($1, item_name)' LANGUAGE SQL EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION get_services(varchar(16)) FROM public; GRANT EXECUTE ON FUNCTION get_services(varchar(16)) TO anondispense; CREATE OR REPLACE FUNCTION first_request_handler() RETURNS "trigger" AS ' DECLARE cost INTEGER; stock INTEGER; balance INTEGER; BEGIN -- Sanity check that requestor isn''t acting on somebody else''s behalf -- when they''re not in coke. IF NEW.request_target_name <> NEW.request_requestor_name AND NOT in_coke(NEW.request_requestor_name) THEN RAISE EXCEPTION ''You are not allowed to perform operations on behalf of somebody else''; END IF; -- Sanity check that the item is in the items table IF NOT EXISTS (SELECT item_name FROM items where item_name = NEW.item_name) THEN RAISE EXCEPTION ''We do not stock anything like that.''; END IF; -- If we have a balance for the item, ensure there is sufficient money SELECT INTO cost items.item_cost_cents FROM items WHERE item_name = NEW.item_name; -- If the cost is null, it may vary and account keeping is done by the -- relevant modules IF cost IS NOT NULL THEN SELECT INTO balance users.user_balance_cents FROM users WHERE user_name = NEW.request_target_name; IF in_coke(NEW.request_requestor_name) THEN IF balance - cost < -2000 THEN -- They''re in coke, let them take balances to -$20.00 RAISE EXCEPTION ''You may not dispense below -$20.00.''; END IF; ELSE -- Else simply ensure there is enough money IF cost > balance THEN RAISE EXCEPTION ''You do not have sufficient funds to do that.''; END IF; END IF; -- Deduct money. This will be rolled back if any other triggers fail. UPDATE users SET user_balance_cents = user_balance_cents - cost WHERE user_name = NEW.request_target_name; END IF; -- Adjust stock levels as required SELECT INTO stock item_stock FROM items WHERE item_name = NEW.item_name; IF stock IS NOT NULL THEN IF stock = 0 THEN RAISE EXCEPTION ''We are out of stock of that item.''; END IF; IF stock > 0 THEN UPDATE items SET item_stock = item_stock - 1 WHERE item_name = NEW.item_name; END IF; -- if item stock is -1, then stock levels are adjusted by other things END IF; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION first_request_handler() FROM public; CREATE TRIGGER a00_first_request_trigger BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE first_request_handler(); CREATE OR REPLACE FUNCTION final_request_handler() RETURNS "trigger" AS ' BEGIN IF NEW.handled = false THEN RAISE EXCEPTION ''Nothing wanted to service your request!''; END IF; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; 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 --- ------------------------------------------------------------------------------ INSERT INTO sources VALUES ('nothing'); INSERT INTO items VALUES ('nothing', 'nothing', NULL, NULL, NULL, NULL); CREATE OR REPLACE FUNCTION nothing_nothing_trigger() RETURNS "trigger" AS ' DECLARE BEGIN -- Check if we are to handle this request IF NEW.handled OR NEW.item_name <> ''nothing'' THEN RETURN NEW; END IF; -- Mark the request as having been dealt with NEW.handled = true; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION nothing_nothing_trigger() FROM public; CREATE TRIGGER nothing_nothing_trigger BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE nothing_nothing_trigger(); DROP FUNCTION can_show_pre_nothing(varchar, varchar); ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_nothing; CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS 'SELECT can_show_pre_nothing($1, $2) OR $2 = ''nothing''' LANGUAGE SQL EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; ------------------------------------------------------------------------------ --- Coke Bank Services --- ------------------------------------------------------------------------------ INSERT INTO sources VALUES ('cokebank'); INSERT INTO items VALUES ('cokebank', 'give', NULL, NULL, ' ', NULL); CREATE OR REPLACE FUNCTION cokebank_give_trigger() RETURNS "trigger" AS ' DECLARE give_amount INTEGER; source_user VARCHAR(8); target_user VARCHAR(8); source_user_balance INTEGER; BEGIN -- Check if we are to handle this request IF NEW.handled OR NEW.item_name <> ''give'' THEN RETURN NEW; END IF; -- Check for our two parameters, username, amount IF NEW.params IS NULL THEN RAISE EXCEPTION ''Missing parameters for give''; END IF; IF array_upper(NEW.params, 1) <> 2 THEN RAISE EXCEPTION ''Invalid number of parameters for give''; END IF; IF NEW.params[1] NOT IN (SELECT user_name FROM users) THEN RAISE EXCEPTION ''Invalid user to give to''; END IF; give_amount = CAST(CAST(NEW.params[2] AS TEXT) AS INTEGER); source_user = NEW.request_target_name; target_user = NEW.params[1]; -- Can''t give to one''s self IF source_user = target_user THEN RAISE EXCEPTION ''You can''''t give to one''''s self''; END IF; -- Can''t give negative amounts IF give_amount <= 0 THEN RAISE EXCEPTION ''You can only give positive balances''; END IF; -- Check the appropriate balances SELECT INTO source_user_balance users.user_balance_cents FROM users WHERE user_name = source_user; IF source_user_balance < give_amount AND not in_coke(NEW.request_requestor_name) THEN RAISE EXCEPTION ''You do not have sufficient funds to give that much!''; END IF; -- Perform the request UPDATE users SET user_balance_cents = user_balance_cents - give_amount WHERE user_name = source_user; UPDATE users SET user_balance_cents = user_balance_cents + give_amount WHERE user_name = target_user; -- Mark the request as having been dealt with NEW.handled = true; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION cokebank_give_trigger() FROM public; CREATE TRIGGER cokebank_give_trigger BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE cokebank_give_trigger(); INSERT INTO items VALUES ('cokebank', 'acct', NULL, NULL, ' ', NULL); CREATE OR REPLACE FUNCTION cokebank_acct_trigger() RETURNS "trigger" AS ' DECLARE amount INTEGER; target_user VARCHAR(8); reason VARCHAR; user_bal RECORD; BEGIN -- Check if we are to handle this request IF NEW.handled OR NEW.item_name <> ''acct'' THEN RETURN NEW; END IF; -- Check requestor is in coke IF NOT in_coke(NEW.request_requestor_name) THEN RAISE EXCEPTION ''You need to be in the coke group to use acct''; END IF; -- Check for our two parameters, amount and reason IF NEW.params IS NULL THEN RAISE EXCEPTION ''No parameters supplied to acct''; END IF; IF array_upper(NEW.params, 1) <> 2 THEN RAISE EXCEPTION ''Invalid number of parameters for acct''; END IF; amount = CAST(CAST(NEW.params[1] AS TEXT) AS INTEGER); reason = NEW.params[2]; target_user = NEW.request_target_name; -- Perform the request UPDATE users SET user_balance_cents = user_balance_cents + amount WHERE user_name = target_user; -- Mark the request as having been dealt with NEW.handled = true; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION cokebank_acct_trigger() FROM public; CREATE TRIGGER cokebank_acct_trigger BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE cokebank_acct_trigger(); -- Don't show the acct function to non-coke people DROP FUNCTION can_show_pre_cokebank(varchar, varchar); ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_cokebank; CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS 'SELECT can_show_pre_cokebank($1, $2) AND NOT ($2 = ''acct'' AND NOT in_coke($1))' LANGUAGE SQL EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; ------------------------------------------------------------------------------ --- Coke Machine Services --- ------------------------------------------------------------------------------ INSERT INTO sources VALUES ('coke'); INSERT INTO items VALUES ('coke', 'passion pop', 80, array['0'], NULL, 8); INSERT INTO items VALUES ('coke', 'vb', 80, array['1'], NULL, 0); INSERT INTO items VALUES ('coke', 'emu export', 80, array['2'], NULL, 3); INSERT INTO items VALUES ('coke', 'whiskey', 80, array['3'], NULL, 4); INSERT INTO items VALUES ('coke', 'champagne', 80, array['4'], NULL, 9); INSERT INTO items VALUES ('coke', 'grape juice', 80, array['5'], NULL, 1); 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 -- Check such a slot exists IF NOT EXISTS(SELECT item_name FROM items WHERE item_data[1] = cast($1 as varchar) AND source_id = ''coke'') THEN RAISE EXCEPTION ''There is no such slot %'', $1; END IF; IF $2 IS NOT NULL THEN UPDATE items SET item_name = $2 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke''; END IF; IF $3 IS NOT NULL THEN UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke''; END IF; IF $4 IS NOT NULL THEN UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend''; END IF; END; ' 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, 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, integer) FROM public; CREATE OR REPLACE FUNCTION coke_slot_trigger() RETURNS "trigger" AS ' DECLARE new_name VARCHAR(20); new_cost INTEGER; new_stock INTEGER; BEGIN -- Check if we are to handle this request IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN RETURN NEW; END IF; -- Check if we are wanted to update a slot IF NEW.item_name = ''update_coke'' THEN -- Check parameters IF NEW.params IS NULL THEN RAISE EXCEPTION ''Missing parameters for update_coke''; END IF; IF array_upper(NEW.params, 1) <> 4 THEN RAISE EXCEPTION ''Invalid number of parameters for update_coke''; END IF; IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''coke'' and item_data[1] IS NOT NULL) THEN new_name = NEW.params[1]; RAISE EXCEPTION ''No idea what slot % is.'', new_name; END IF; new_name = NEW.params[2]; IF NEW.params[3] <> '''' THEN new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER); IF new_cost <= 0 THEN RAISE EXCEPTION ''Amount must be positive''; END IF; UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1]; END IF; IF NEW.params[4] <> '''' THEN new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER); IF new_stock <= -1 THEN RAISE EXCEPTION ''Stock count must be -1 or >= 0''; END IF; UPDATE items SET item_stock = new_stock WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1]; END IF; UPDATE items SET item_name = new_name WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1]; END IF; -- The default handler will have checked that we have stock and -- 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. -- Mark the request as having been dealt with NEW.handled = true; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION coke_slot_trigger() FROM public; CREATE TRIGGER coke_slot_trigger BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE coke_slot_trigger(); CREATE OR REPLACE FUNCTION coke_slot_dispense() RETURNS "trigger" AS ' DECLARE slotnum integer; stock integer; BEGIN 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(NEW.request_id, slotnum); END IF; RETURN NULL; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION coke_slot_dispense() FROM public; CREATE TRIGGER coke_slot_dispense AFTER INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE coke_slot_dispense(); -- Don't show the update function to non-coke people DROP FUNCTION can_show_pre_coke(varchar, varchar); ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_coke; CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS 'SELECT can_show_pre_coke($1, $2) AND NOT ($2 = ''update_coke'' AND NOT in_coke($1))' LANGUAGE SQL EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; ------------------------------------------------------------------------------ --- Vending Machine Services --- ------------------------------------------------------------------------------ INSERT INTO sources VALUES ('vend'); 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 '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 ' BEGIN -- Check such a slot exists IF NOT valid_vend_slot($1) THEN RAISE EXCEPTION ''There is no such slot %'', $1; END IF; IF $2 IS NOT NULL THEN UPDATE items SET item_name = $2 WHERE item_data[1] = $1 and source_id = ''vend''; END IF; IF $3 IS NOT NULL THEN UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = $1 and source_id = ''vend''; END IF; IF $4 IS NOT NULL THEN UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend''; END IF; END; ' 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(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(integer, varchar(2)) FROM public; CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS ' DECLARE new_name VARCHAR(20); new_cost INTEGER; new_stock INTEGER; BEGIN -- Check if we are to handle this request IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN RETURN NEW; END IF; -- Check if we are wanted to update a slot IF NEW.item_name = ''update_vend'' THEN -- Check parameters IF NEW.params IS NULL THEN RAISE EXCEPTION ''Missing parameters for update_vend''; END IF; IF array_upper(NEW.params, 1) <> 4 THEN RAISE EXCEPTION ''Invalid number of parameters for update_vend''; END IF; IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''vend'' and item_data[1] IS NOT NULL) THEN new_name = NEW.params[1]; RAISE EXCEPTION ''No idea what slot % is.'', new_name; END IF; new_name = NEW.params[2]; IF NEW.params[3] <> '''' THEN new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER); IF new_cost <= 0 THEN RAISE EXCEPTION ''Amount must be positive''; END IF; UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1]; END IF; IF NEW.params[4] <> '''' THEN new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER); IF new_stock <= -1 THEN RAISE EXCEPTION ''Stock count must be -1 or >= 0''; END IF; UPDATE items SET item_stock = new_stock WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1]; END IF; UPDATE items SET item_name = new_name WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1]; END IF; -- The default handler will have checked that we have stock and -- deducted money et al. -- -- So if we get this far, we don''t need to care about much. -- Dropping the snack occurs in the AFTER-INSERT trigger. -- Mark the request as having been dealt with NEW.handled = true; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION vend_slot_trigger() FROM public; CREATE TRIGGER vend_slot_trigger BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE vend_slot_trigger(); CREATE OR REPLACE FUNCTION vend_slot_dispense() RETURNS "trigger" AS ' DECLARE slotnum varchar(2); BEGIN 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(NEW.request_id, slotnum); END IF; RETURN NULL; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION vend_slot_dispense() FROM public; CREATE TRIGGER vend_slot_dispense AFTER INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE vend_slot_dispense(); -- Don't show the update function to non-coke people DROP FUNCTION can_show_pre_vend(varchar, varchar); ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_vend; CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS 'SELECT can_show_pre_vend($1, $2) AND NOT ($2 = ''update_vend'' AND NOT in_coke($1))' LANGUAGE SQL EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; ------------------------------------------------------------------------------ --- UCC Door Services --- ------------------------------------------------------------------------------ CREATE TABLE doorcontrollers ( user_name varchar(16) UNIQUE references users(user_name), reason varchar(250) ); CREATE OR REPLACE FUNCTION in_door(varchar(16)) RETURNS boolean AS 'SELECT EXISTS(SELECT user_name FROM doorcontrollers WHERE user_name = $1)' LANGUAGE sql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION in_door(varchar(16)) FROM public; INSERT INTO sources VALUES ('door'); INSERT INTO items VALUES ('door', 'opendoor', NULL, NULL, NULL, NULL); CREATE OR REPLACE FUNCTION open_door() RETURNS void AS ' system("/usr/bin/sudo /usr/sbin/opendoor"); ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION open_door() FROM public; CREATE OR REPLACE FUNCTION door_open_trigger() RETURNS "trigger" AS ' BEGIN -- Check if we are to handle this request IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''door'' AND item_name = NEW.item_name) THEN RETURN NEW; END IF; -- You can''t open the door for somebody else IF NEW.request_requestor_name <> NEW.request_target_name THEN RAISE EXCEPTION ''You can''''t open the door for somebody else.''; END IF; -- Ensure the user is in the door group IF NOT in_door(NEW.request_requestor_name) THEN RAISE EXCEPTION ''You are not permitted to open the door.''; END IF; -- Mark the request as having been dealt with NEW.handled = true; RETURN NEW; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION door_open_trigger() FROM public; CREATE TRIGGER door_open_trigger BEFORE INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE door_open_trigger(); CREATE OR REPLACE FUNCTION door_open_do() RETURNS "trigger" AS ' BEGIN IF NEW.handled AND NEW.item_name = ''opendoor'' THEN PERFORM open_door(); END IF; RETURN NULL; END; ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; REVOKE ALL ON FUNCTION door_open_do() FROM public; CREATE TRIGGER door_open_do AFTER INSERT ON requests FOR EACH ROW EXECUTE PROCEDURE door_open_do(); -- Don't show the door functions to non-door people 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; REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;