1 ------------------------------------------------------------------------------
2 ------------------------------------------------------------------------------
4 --- DISPENSE IN POSTGRES REWRITE ---
7 ------------------------------------------------------------------------------
8 ------------------------------------------------------------------------------
12 All the operations are done
18 DROP SEQUENCE request_id_seq;
19 DROP FUNCTION get_services(varchar);
23 DROP TABLE cokecontrollers;
24 DROP TABLE doorcontrollers;
25 DROP FUNCTION get_balance();
26 DROP FUNCTION get_balance(varchar);
29 CREATE OR REPLACE FUNCTION get_ident() RETURNS varchar AS 'pg_ident.so', 'get_ident' STRICT LANGUAGE C;
30 REVOKE ALL ON FUNCTION get_ident() FROM public;
33 user_name varchar(16) NOT NULL UNIQUE PRIMARY KEY,
34 user_balance_cents integer NOT NULL,
35 user_balance_bytes integer NOT NULL
38 CREATE TABLE cokecontrollers (
39 user_name varchar(16) UNIQUE references users(user_name),
43 CREATE TABLE sources (
44 source_name varchar(20) NOT NULL UNIQUE PRIMARY KEY
48 source_name varchar(20) REFERENCES sources(source_name),
49 item_name varchar(20) NOT NULL UNIQUE PRIMARY KEY,
50 item_cost_cents integer,
52 item_syntax varchar(80),
53 item_stock integer -- -1 for "in stock", 0 for "out of stock", > 0 for a value of stock, NULL for N/A
56 CREATE VIEW services AS
57 SELECT i.source_name as provider,
66 CREATE SEQUENCE request_id_seq START 1;
67 CREATE TABLE requests (
68 request_id integer NOT NULL DEFAULT nextval('request_id_seq'),
69 request_requestor_name varchar(16) REFERENCES users(user_name),
70 request_target_name varchar(16) REFERENCES users(user_name),
71 item_name varchar(20),
73 handled boolean NOT NULL DEFAULT false
76 -- request(target username, item_name, params)
77 CREATE OR REPLACE FUNCTION do_request(varchar(16), varchar(20), varchar[]) RETURNS boolean AS
84 RAISE EXCEPTION ''Got a null ident! Eepe!'';
86 IF NOT EXISTS(SELECT user_name FROM users WHERE user_name = $1) THEN
87 RAISE NOTICE ''Adding user %!!'', $1;
88 INSERT INTO users VALUES ($1, 0, 0);
90 INSERT INTO requests VALUES (DEFAULT, ident, $1, $2, $3);
94 LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
95 REVOKE ALL ON FUNCTION do_request(varchar(16),varchar(20),varchar[]) FROM public;
96 GRANT EXECUTE ON FUNCTION do_request(varchar(16), varchar(20), varchar[]) TO anondispense;
98 -- get_balance(target username)
99 CREATE OR REPLACE FUNCTION get_balance(varchar(16)) RETURNS users
100 AS 'SELECT * FROM users WHERE user_name = $1'
101 LANGUAGE sql EXTERNAL SECURITY DEFINER;
102 REVOKE ALL ON FUNCTION get_balance(varchar(16)) FROM public;
103 GRANT EXECUTE ON FUNCTION get_balance(varchar(16)) TO anondispense;
105 -- get_balance() ... returns everybody
106 CREATE OR REPLACE FUNCTION get_balance() RETURNS SETOF users
107 AS 'SELECT * FROM users'
108 LANGUAGE sql EXTERNAL SECURITY DEFINER;
109 REVOKE ALL ON FUNCTION get_balance() FROM public;
110 GRANT EXECUTE ON FUNCTION get_balance() TO anondispense;
112 -- internal functions follow
114 CREATE OR REPLACE FUNCTION in_coke(varchar(16)) RETURNS boolean
115 AS 'SELECT EXISTS(SELECT * FROM cokecontrollers WHERE user_name = $1)'
116 LANGUAGE sql EXTERNAL SECURITY DEFINER;
117 REVOKE ALL ON FUNCTION in_coke(varchar(16)) FROM public;
119 CREATE OR REPLACE FUNCTION byte_to_cent_trigger() RETURNS "trigger" AS
122 bytes_per_cent INTEGER;
123 cents_per_meg INTEGER;
126 bytes_per_cent = 1024*1024/cents_per_meg;
127 IF NEW.user_balance_bytes > bytes_per_cent THEN
128 NEW.user_balance_cents = NEW.user_balance_cents - (NEW.user_balance_bytes/bytes_per_cent);
129 NEW.user_balance_bytes = NEW.user_balance_bytes % bytes_per_cent;
133 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
134 REVOKE ALL ON FUNCTION byte_to_cent_trigger() FROM public;
135 CREATE TRIGGER byte_to_cent_trigger BEFORE INSERT OR UPDATE ON users FOR EACH ROW
136 EXECUTE PROCEDURE byte_to_cent_trigger();
138 CREATE OR REPLACE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
143 IF $1 NOT IN (SELECT user_name FROM users) THEN
147 IF ident = $1 OR in_coke(ident) THEN
152 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
153 REVOKE ALL ON FUNCTION can_show(varchar(16), varchar(20)) FROM public;
154 CREATE OR REPLACE FUNCTION get_services(varchar(16)) RETURNS SETOF services AS
155 'SELECT * from services WHERE can_show($1, item_name)' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
157 REVOKE ALL ON FUNCTION get_services(varchar(16)) FROM public;
158 GRANT EXECUTE ON FUNCTION get_services(varchar(16)) TO anondispense;
160 CREATE OR REPLACE FUNCTION first_request_handler() RETURNS "trigger" AS
167 -- Sanity check that requestor isn''t acting on somebody else''s behalf
168 -- when they''re not in coke.
169 IF NEW.request_target_name <> NEW.request_requestor_name AND NOT in_coke(NEW.request_requestor_name) THEN
170 RAISE EXCEPTION ''You are not allowed to perform operations on behalf of somebody else'';
173 -- Sanity check that the item is in the items table
174 IF NOT EXISTS (SELECT item_name FROM items where item_name = NEW.item_name) THEN
175 RAISE EXCEPTION ''We do not stock anything like that.'';
178 -- If we have a balance for the item, ensure there is sufficient money
179 SELECT INTO cost items.item_cost_cents FROM items WHERE item_name = NEW.item_name;
181 -- If the cost is null, it may vary and account keeping is done by the
183 IF cost IS NOT NULL THEN
184 SELECT INTO balance users.user_balance_cents FROM users WHERE user_name = NEW.request_target_name;
185 IF in_coke(NEW.request_requestor_name) THEN
186 IF balance - cost < -2000 THEN
187 -- They''re in coke, let them take balances to -$20.00
188 RAISE EXCEPTION ''You may not dispense below -$20.00.'';
191 -- Else simply ensure there is enough money
192 IF cost > balance THEN
193 RAISE EXCEPTION ''You do not have sufficient funds to do that.'';
197 -- Deduct money. This will be rolled back if any other triggers fail.
198 UPDATE users SET user_balance_cents = user_balance_cents - cost WHERE user_name = NEW.request_target_name;
201 -- Adjust stock levels as required
202 SELECT INTO stock item_stock FROM items WHERE item_name = NEW.item_name;
203 IF stock IS NOT NULL THEN
205 RAISE EXCEPTION ''We are out of stock of that item.'';
208 UPDATE items SET item_stock = item_stock - 1 WHERE item_name = NEW.item_name;
210 -- if item stock is -1, then stock levels are adjusted by other things
215 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
216 REVOKE ALL ON FUNCTION first_request_handler() FROM public;
217 CREATE TRIGGER a00_first_request_trigger BEFORE INSERT ON requests FOR EACH ROW
218 EXECUTE PROCEDURE first_request_handler();
220 CREATE OR REPLACE FUNCTION final_request_handler() RETURNS "trigger" AS
223 IF NEW.handled = false THEN
224 RAISE EXCEPTION ''Nothing wanted to service your request!'';
228 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
229 REVOKE ALL ON FUNCTION final_request_handler() FROM public;
230 CREATE TRIGGER zzz_last_request_trigger BEFORE INSERT ON requests FOR EACH ROW
231 EXECUTE PROCEDURE final_request_handler();
233 ---------------------------------------------------------------------------------
234 --- Dummy Services ---
235 ---------------------------------------------------------------------------------
237 INSERT INTO sources VALUES ('nothing');
239 INSERT INTO items VALUES ('nothing', 'nothing', NULL, NULL, NULL, NULL);
240 CREATE OR REPLACE FUNCTION nothing_nothing_trigger() RETURNS "trigger" AS
244 -- Check if we are to handle this request
245 IF NEW.handled OR NEW.item_name <> ''nothing'' THEN
249 -- Mark the request as having been dealt with
253 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
254 REVOKE ALL ON FUNCTION nothing_nothing_trigger() FROM public;
255 CREATE TRIGGER nothing_nothing_trigger BEFORE INSERT ON requests FOR EACH ROW
256 EXECUTE PROCEDURE nothing_nothing_trigger();
258 DROP FUNCTION can_show_pre_nothing(varchar, varchar);
259 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_nothing;
260 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
261 'SELECT can_show_pre_nothing($1, $2) OR $2 = ''nothing'''
262 LANGUAGE SQL EXTERNAL SECURITY DEFINER;
263 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
265 ---------------------------------------------------------------------------------
266 --- Coke Bank Services ---
267 ---------------------------------------------------------------------------------
269 INSERT INTO sources VALUES ('cokebank');
271 INSERT INTO items VALUES ('cokebank', 'give', NULL, NULL, '<username> <cents>', NULL);
272 CREATE OR REPLACE FUNCTION cokebank_give_trigger() RETURNS "trigger" AS
277 source_user VARCHAR(8);
278 target_user VARCHAR(8);
279 source_user_balance INTEGER;
281 -- Check if we are to handle this request
282 IF NEW.handled OR NEW.item_name <> ''give'' THEN
286 -- Check for our two parameters, username, amount
287 IF NEW.params IS NULL THEN
288 RAISE EXCEPTION ''Missing parameters for give'';
290 IF array_upper(NEW.params, 1) <> 2 THEN
291 RAISE EXCEPTION ''Invalid number of parameters for give'';
293 IF NEW.params[1] NOT IN (SELECT user_name FROM users) THEN
294 RAISE EXCEPTION ''Invalid user to give to'';
296 give_amount = CAST(CAST(NEW.params[2] AS TEXT) AS INTEGER);
297 source_user = NEW.request_target_name;
298 target_user = NEW.params[1];
300 -- Can''t give to one''s self
301 IF source_user = target_user THEN
302 RAISE EXCEPTION ''You can''''t give to one''''s self'';
304 -- Can''t give negative amounts
305 IF give_amount <= 0 THEN
306 RAISE EXCEPTION ''You can only give positive balances'';
309 -- Check the appropriate balances
310 SELECT INTO source_user_balance users.user_balance_cents FROM users WHERE user_name = source_user;
312 IF source_user_balance < give_amount AND not in_coke(NEW.request_requestor_name) THEN
313 RAISE EXCEPTION ''You do not have sufficient funds to give that much!'';
316 -- Perform the request
317 UPDATE users SET user_balance_cents = user_balance_cents - give_amount WHERE user_name = source_user;
318 UPDATE users SET user_balance_cents = user_balance_cents + give_amount WHERE user_name = target_user;
319 -- Mark the request as having been dealt with
323 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
324 REVOKE ALL ON FUNCTION cokebank_give_trigger() FROM public;
325 CREATE TRIGGER cokebank_give_trigger BEFORE INSERT ON requests FOR EACH ROW
326 EXECUTE PROCEDURE cokebank_give_trigger();
328 INSERT INTO items VALUES ('cokebank', 'acct', NULL, NULL, '<cents> <reason>', NULL);
329 CREATE OR REPLACE FUNCTION cokebank_acct_trigger() RETURNS "trigger" AS
333 target_user VARCHAR(8);
337 -- Check if we are to handle this request
338 IF NEW.handled OR NEW.item_name <> ''acct'' THEN
342 -- Check requestor is in coke
343 IF NOT in_coke(NEW.request_requestor_name) THEN
344 RAISE EXCEPTION ''You need to be in the coke group to use acct'';
347 -- Check for our two parameters, amount and reason
348 IF NEW.params IS NULL THEN
349 RAISE EXCEPTION ''No parameters supplied to acct'';
351 IF array_upper(NEW.params, 1) <> 2 THEN
352 RAISE EXCEPTION ''Invalid number of parameters for acct'';
354 amount = CAST(CAST(NEW.params[1] AS TEXT) AS INTEGER);
355 reason = NEW.params[2];
356 target_user = NEW.request_target_name;
358 -- Perform the request
359 UPDATE users SET user_balance_cents = user_balance_cents + amount WHERE user_name = target_user;
360 -- Mark the request as having been dealt with
364 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
365 REVOKE ALL ON FUNCTION cokebank_acct_trigger() FROM public;
366 CREATE TRIGGER cokebank_acct_trigger BEFORE INSERT ON requests FOR EACH ROW
367 EXECUTE PROCEDURE cokebank_acct_trigger();
369 -- Don't show the acct function to non-coke people
370 DROP FUNCTION can_show_pre_cokebank(varchar, varchar);
371 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_cokebank;
372 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
373 'SELECT can_show_pre_cokebank($1, $2) AND NOT ($2 = ''acct'' AND NOT in_coke($1))'
374 LANGUAGE SQL EXTERNAL SECURITY DEFINER;
375 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
377 ---------------------------------------------------------------------------------
378 --- Coke Machine Services ---
379 ---------------------------------------------------------------------------------
381 INSERT INTO sources VALUES ('coke');
383 INSERT INTO items VALUES ('coke', 'passion pop', 80, array['0'], NULL, 8);
384 INSERT INTO items VALUES ('coke', 'vb', 80, array['1'], NULL, 0);
385 INSERT INTO items VALUES ('coke', 'emu export', 80, array['2'], NULL, 3);
386 INSERT INTO items VALUES ('coke', 'whiskey', 80, array['3'], NULL, 4);
387 INSERT INTO items VALUES ('coke', 'champagne', 80, array['4'], NULL, 9);
388 INSERT INTO items VALUES ('coke', 'grape juice', 80, array['5'], NULL, 1);
389 INSERT INTO items VALUES ('coke', 'coke powder', 80, array['6'], NULL, 11);
391 INSERT INTO items VALUES ('coke', 'update_coke', NULL, NULL, '<slot number> <new name> <new price> <stock count>', NULL);
393 CREATE OR REPLACE FUNCTION update_slot_info(integer, varchar(20), integer, integer) RETURNS void AS
396 -- Check such a slot exists
397 IF NOT EXISTS(SELECT item_name FROM items WHERE item_data[1] = cast($1 as varchar) AND source_id = ''coke'') THEN
398 RAISE EXCEPTION ''There is no such slot %'', $1;
400 IF $2 IS NOT NULL THEN
401 UPDATE items SET item_name = $2 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke'';
403 IF $3 IS NOT NULL THEN
404 UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke'';
406 IF $4 IS NOT NULL THEN
407 UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
410 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
411 REVOKE ALL ON FUNCTION update_slot_info(integer, varchar, integer, integer) FROM public;
413 CREATE OR REPLACE FUNCTION dispense_slot(integer) RETURNS void AS
415 elog(NOTICE, q(And out pops a drink!));
416 ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
417 REVOKE ALL ON FUNCTION dispense_slot(integer) FROM public;
419 CREATE OR REPLACE FUNCTION update_slot_status() RETURNS void AS
421 ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
422 REVOKE ALL ON FUNCTION update_slot_status() FROM public;
424 CREATE OR REPLACE FUNCTION coke_slot_trigger() RETURNS "trigger" AS
427 new_name VARCHAR(20);
431 -- Check if we are to handle this request
432 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN
436 -- Check if we are wanted to update a slot
437 IF NEW.item_name = ''update_coke'' THEN
439 IF NEW.params IS NULL THEN
440 RAISE EXCEPTION ''Missing parameters for update_coke'';
442 IF array_upper(NEW.params, 1) <> 4 THEN
443 RAISE EXCEPTION ''Invalid number of parameters for update_coke'';
445 IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''coke'' and item_data[1] IS NOT NULL) THEN
446 new_name = NEW.params[1];
447 RAISE EXCEPTION ''No idea what slot % is.'', new_name;
449 new_name = NEW.params[2];
450 IF NEW.params[3] <> '''' THEN
451 new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER);
452 IF new_cost <= 0 THEN
453 RAISE EXCEPTION ''Amount must be positive'';
455 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
457 IF NEW.params[4] <> '''' THEN
458 new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER);
459 IF new_stock <= -1 THEN
460 RAISE EXCEPTION ''Stock count must be -1 or >= 0'';
462 UPDATE items SET item_stock = new_stock WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
464 UPDATE items SET item_name = new_name WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
467 -- The default handler will have checked that we have stock and
468 -- deducted money et al.
470 -- So if we get this far, we don''t need to care about much.
471 -- Dropping the drink & checking stock occurs in the AFTER-INSERT trigger.
473 -- Mark the request as having been dealt with
477 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
478 REVOKE ALL ON FUNCTION coke_slot_trigger() FROM public;
479 CREATE TRIGGER coke_slot_trigger BEFORE INSERT ON requests FOR EACH ROW
480 EXECUTE PROCEDURE coke_slot_trigger();
482 CREATE OR REPLACE FUNCTION coke_slot_dispense() RETURNS "trigger" AS
488 IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN
489 -- Drop a drink and update stock
490 SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''coke'';
491 PERFORM dispense_slot(slotnum);
492 PERFORM update_slot_status();
496 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
497 REVOKE ALL ON FUNCTION coke_slot_dispense() FROM public;
498 CREATE TRIGGER coke_slot_dispense AFTER INSERT ON requests FOR EACH ROW
499 EXECUTE PROCEDURE coke_slot_dispense();
501 -- Don't show the update function to non-coke people
502 DROP FUNCTION can_show_pre_coke(varchar, varchar);
503 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_coke;
504 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
505 'SELECT can_show_pre_coke($1, $2) AND NOT ($2 = ''update_coke'' AND NOT in_coke($1))'
506 LANGUAGE SQL EXTERNAL SECURITY DEFINER;
507 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
509 ---------------------------------------------------------------------------------
510 --- Vending Machine Services ---
511 ---------------------------------------------------------------------------------
513 INSERT INTO sources VALUES ('vend');
515 INSERT INTO items VALUES ('vend', 'twisties', 120, array['11'], NULL, 8);
516 INSERT INTO items VALUES ('vend', 'cheese and onion', 125, array['21'], NULL, 0);
517 INSERT INTO items VALUES ('vend', 'update_vend', NULL, NULL, '<slot number> <new name> <new price> <new count>', NULL);
519 CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS
522 RETURN $1 ~ ''^[0-9]{2}$'';
524 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
525 REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
527 CREATE OR REPLACE FUNCTION update_vend_slot_info(varchar(2), varchar(20), integer, integer) RETURNS void AS
530 -- Check such a slot exists
531 IF NOT valid_vend_slot($1) THEN
532 RAISE EXCEPTION ''There is no such slot %'', $1;
534 IF $2 IS NOT NULL THEN
535 UPDATE items SET item_name = $2 WHERE item_data[1] = $1 and source_id = ''vend'';
537 IF $3 IS NOT NULL THEN
538 UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = $1 and source_id = ''vend'';
540 IF $4 IS NOT NULL THEN
541 UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
544 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
545 REVOKE ALL ON FUNCTION update_vend_slot_info(varchar(2), varchar, integer, integer) FROM public;
547 CREATE OR REPLACE FUNCTION vend_slot(varchar(2)) RETURNS void AS
549 elog(NOTICE, q(And out pops a snack!));
550 ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
551 REVOKE ALL ON FUNCTION vend_slot(varchar(2)) FROM public;
553 CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS
556 new_name VARCHAR(20);
560 -- Check if we are to handle this request
561 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN
565 -- Check if we are wanted to update a slot
566 IF NEW.item_name = ''update_vend'' THEN
568 IF NEW.params IS NULL THEN
569 RAISE EXCEPTION ''Missing parameters for update_vend'';
571 IF array_upper(NEW.params, 1) <> 4 THEN
572 RAISE EXCEPTION ''Invalid number of parameters for update_vend'';
574 IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''vend'' and item_data[1] IS NOT NULL) THEN
575 new_name = NEW.params[1];
576 RAISE EXCEPTION ''No idea what slot % is.'', new_name;
578 new_name = NEW.params[2];
579 IF NEW.params[3] <> '''' THEN
580 new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER);
581 IF new_cost <= 0 THEN
582 RAISE EXCEPTION ''Amount must be positive'';
584 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
586 IF NEW.params[4] <> '''' THEN
587 new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER);
588 IF new_stock <= -1 THEN
589 RAISE EXCEPTION ''Stock count must be -1 or >= 0'';
591 UPDATE items SET item_stock = new_stock WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
593 UPDATE items SET item_name = new_name WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
596 -- The default handler will have checked that we have stock and
597 -- deducted money et al.
599 -- So if we get this far, we don''t need to care about much.
600 -- Dropping the drink & checking stock occurs in the AFTER-INSERT trigger.
602 -- Mark the request as having been dealt with
606 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
607 REVOKE ALL ON FUNCTION vend_slot_trigger() FROM public;
608 CREATE TRIGGER vend_slot_trigger BEFORE INSERT ON requests FOR EACH ROW
609 EXECUTE PROCEDURE vend_slot_trigger();
611 CREATE OR REPLACE FUNCTION vend_slot_dispense() RETURNS "trigger" AS
616 IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN
617 -- Drop a snack and update stock
618 SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''vend'';
619 PERFORM vend_slot(slotnum);
623 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
624 REVOKE ALL ON FUNCTION vend_slot_dispense() FROM public;
625 CREATE TRIGGER vend_slot_dispense AFTER INSERT ON requests FOR EACH ROW
626 EXECUTE PROCEDURE vend_slot_dispense();
628 -- Don't show the update function to non-coke people
629 DROP FUNCTION can_show_pre_vend(varchar, varchar);
630 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_vend;
631 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
632 'SELECT can_show_pre_vend($1, $2) AND NOT ($2 = ''update_vend'' AND NOT in_coke($1))'
633 LANGUAGE SQL EXTERNAL SECURITY DEFINER;
634 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
636 ---------------------------------------------------------------------------------
637 --- UCC Door Services ---
638 ---------------------------------------------------------------------------------
640 CREATE TABLE doorcontrollers (
641 user_name varchar(16) UNIQUE references users(user_name),
645 CREATE OR REPLACE FUNCTION in_door(varchar(16)) RETURNS boolean
646 AS 'SELECT EXISTS(SELECT user_name FROM doorcontrollers WHERE user_name = $1)'
647 LANGUAGE sql EXTERNAL SECURITY DEFINER;
648 REVOKE ALL ON FUNCTION in_door(varchar(16)) FROM public;
650 INSERT INTO sources VALUES ('door');
652 INSERT INTO items VALUES ('door', 'opendoor', NULL, NULL, NULL, NULL);
654 CREATE OR REPLACE FUNCTION open_door() RETURNS void AS
656 system("/usr/bin/sudo /usr/sbin/opendoor");
657 ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
658 REVOKE ALL ON FUNCTION open_door() FROM public;
660 CREATE OR REPLACE FUNCTION door_open_trigger() RETURNS "trigger" AS
663 -- Check if we are to handle this request
664 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''door'' AND item_name = NEW.item_name) THEN
668 -- You can''t open the door for somebody else
669 IF NEW.request_requestor_name <> NEW.request_target_name THEN
670 RAISE EXCEPTION ''You can''''t open the door for somebody else.'';
673 -- Ensure the user is in the door group
674 IF NOT in_door(NEW.request_requestor_name) THEN
675 RAISE EXCEPTION ''You are not permitted to open the door.'';
678 -- Mark the request as having been dealt with
682 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
683 REVOKE ALL ON FUNCTION door_open_trigger() FROM public;
684 CREATE TRIGGER door_open_trigger BEFORE INSERT ON requests FOR EACH ROW
685 EXECUTE PROCEDURE door_open_trigger();
687 CREATE OR REPLACE FUNCTION door_open_do() RETURNS "trigger" AS
690 IF NEW.handled AND NEW.item_name = ''opendoor'' THEN
695 ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
696 REVOKE ALL ON FUNCTION door_open_do() FROM public;
697 CREATE TRIGGER door_open_do AFTER INSERT ON requests FOR EACH ROW
698 EXECUTE PROCEDURE door_open_do();
700 -- Don't show the door functions to non-door people
701 DROP FUNCTION can_show_pre_door(varchar, varchar);
702 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_door;
703 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
704 'SELECT can_show_pre_door($1, $2) AND NOT ($2 = ''opendoor'' AND NOT in_door($1))'
705 LANGUAGE SQL EXTERNAL SECURITY DEFINER;
706 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;