+++ /dev/null
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
---- ---
---- DISPENSE IN POSTGRES REWRITE ---
---- ---
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-
-/*
-
-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, '<username> <cents>', 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, '<cents> <reason>', 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, '<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
- -- 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, '<slot number> <new name> <new price> <new count>', 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;
-
git.ucc.asn.au / uccvend-vendserver.git / blobdiff
UCC git Repository :: git.ucc.asn.au