X-Git-Url: https://git.ucc.asn.au/?p=uccvend-vendserver.git;a=blobdiff_plain;f=sql-edition%2Fschema%2Fd.sql;fp=sql-edition%2Fschema%2Fd.sql;h=0000000000000000000000000000000000000000;hp=6ef9f57f23b4e350a074f03f9894218a72156b9c;hb=e423f11f190d685156cb557319b169d2b79b752f;hpb=9ac8ba25e1fabd0edd0c90850bf8fa7532048224;ds=sidebyside diff --git a/sql-edition/schema/d.sql b/sql-edition/schema/d.sql deleted file mode 100644 index 6ef9f57..0000000 --- a/sql-edition/schema/d.sql +++ /dev/null @@ -1,744 +0,0 @@ ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ---- --- ---- 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 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; -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); - -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 - -- 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); - -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; -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 - ' - 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 - ' - 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; -