start of refactoring
[uccvend-vendserver.git] / sql-edition / schema / d.sql
1 ------------------------------------------------------------------------------
2 ------------------------------------------------------------------------------
3 ---                                                                        ---
4 ---    DISPENSE IN POSTGRES REWRITE                                        ---
5 ---                           Bernard Blackham <dagobah@ucc.gu.uwa.edu.au> ---
6 ---                                                                        ---
7 ------------------------------------------------------------------------------
8 ------------------------------------------------------------------------------
9
10 /*
11
12 All the operations are done 
13 */
14
15 \connect 'dispense';
16
17 DROP TABLE requests;
18 DROP SEQUENCE request_id_seq;
19 DROP FUNCTION get_services(varchar);
20 DROP VIEW services;
21 DROP TABLE items;
22 DROP TABLE sources;
23 DROP TABLE cokecontrollers;
24 DROP TABLE doorcontrollers;
25 DROP FUNCTION get_balance();
26 DROP FUNCTION get_balance(varchar);
27 DROP TABLE users;
28
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;
31
32 CREATE TABLE users (
33     user_name varchar(16) NOT NULL UNIQUE PRIMARY KEY,
34     user_balance_cents integer NOT NULL,
35     user_balance_bytes integer NOT NULL
36 );
37
38 CREATE TABLE cokecontrollers (
39     user_name varchar(16) UNIQUE references users(user_name),
40         reason varchar(250)
41 );
42
43 CREATE TABLE sources (
44     source_name varchar(20) NOT NULL UNIQUE PRIMARY KEY
45 );
46
47 CREATE TABLE items (
48     source_name varchar(20) REFERENCES sources(source_name),
49     item_name varchar(20) NOT NULL UNIQUE PRIMARY KEY,
50     item_cost_cents integer,
51     item_data varchar[],
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
54 );
55
56 CREATE VIEW services AS 
57         SELECT  i.source_name as provider,
58                         i.item_name,
59                         i.item_cost_cents,
60                         i.item_syntax,
61                         i.item_stock,
62                         i.item_data
63                         FROM items i
64 ;
65
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),
72     params varchar[],
73         handled boolean NOT NULL DEFAULT false
74 );
75
76 -- request(target username, item_name, params)
77 CREATE OR REPLACE FUNCTION do_request(varchar(16), varchar(20), varchar[]) RETURNS boolean AS 
78         '
79         DECLARE
80                 ident VARCHAR;
81         BEGIN
82                 ident = get_ident();
83                 IF ident IS NULL THEN
84                         RAISE EXCEPTION ''Got a null ident! Eepe!'';
85                 END IF;
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);
89                 END IF;
90                 INSERT INTO requests VALUES (DEFAULT, ident, $1, $2, $3);
91                 RETURN true;
92         END;
93         '
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;
97
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;
104
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;
111
112 -- internal functions follow
113
114 CREATE OR REPLACE FUNCTION refund(integer) RETURNS void AS
115 -- FIXME XXX DO this.
116         '
117         '
118         LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
119 REVOKE ALL ON FUNCTION refund(integer) FROM public;
120
121 CREATE OR REPLACE FUNCTION in_coke(varchar(16)) RETURNS boolean
122     AS 'SELECT EXISTS(SELECT * FROM cokecontrollers WHERE user_name = $1)'
123     LANGUAGE sql EXTERNAL SECURITY DEFINER;
124 REVOKE ALL ON FUNCTION in_coke(varchar(16)) FROM public;
125
126 CREATE OR REPLACE FUNCTION byte_to_cent_trigger() RETURNS "trigger" AS
127         '
128         DECLARE
129                 bytes_per_cent INTEGER;
130                 cents_per_meg INTEGER;
131         BEGIN
132                 cents_per_meg = 4;
133                 bytes_per_cent = 1024*1024/cents_per_meg;
134                 IF NEW.user_balance_bytes > bytes_per_cent THEN
135                         NEW.user_balance_cents = NEW.user_balance_cents - (NEW.user_balance_bytes/bytes_per_cent);
136                         NEW.user_balance_bytes = NEW.user_balance_bytes % bytes_per_cent;
137                 END IF;
138                 RETURN NEW;
139         END;
140         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
141 REVOKE ALL ON FUNCTION byte_to_cent_trigger() FROM public;
142 CREATE TRIGGER byte_to_cent_trigger BEFORE INSERT OR UPDATE ON users FOR EACH ROW
143         EXECUTE PROCEDURE byte_to_cent_trigger();
144
145 CREATE OR REPLACE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
146         '
147         DECLARE
148                 ident varchar;
149         BEGIN
150                 IF $1 NOT IN (SELECT user_name FROM users) THEN
151                         RETURN false;
152                 END IF;
153                 ident = get_ident();
154                 IF ident = $1 OR in_coke(ident) THEN
155                         RETURN true;
156                 END IF;
157                 RETURN false;
158         END;
159         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
160 REVOKE ALL ON FUNCTION can_show(varchar(16), varchar(20)) FROM public;
161 CREATE OR REPLACE FUNCTION get_services(varchar(16)) RETURNS SETOF services AS
162         'SELECT * from services WHERE can_show($1, item_name)' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
163
164 REVOKE ALL ON FUNCTION get_services(varchar(16)) FROM public;
165 GRANT EXECUTE ON FUNCTION get_services(varchar(16)) TO anondispense;
166
167 CREATE OR REPLACE FUNCTION first_request_handler() RETURNS "trigger" AS
168         '
169         DECLARE
170                 cost INTEGER;
171                 stock INTEGER;
172                 balance INTEGER;
173         BEGIN
174                 -- Sanity check that requestor isn''t acting on somebody else''s behalf
175                 -- when they''re not in coke.
176                 IF NEW.request_target_name <> NEW.request_requestor_name AND NOT in_coke(NEW.request_requestor_name) THEN
177                         RAISE EXCEPTION ''You are not allowed to perform operations on behalf of somebody else'';
178                 END IF;
179
180                 -- Sanity check that the item is in the items table
181                 IF NOT EXISTS (SELECT item_name FROM items where item_name = NEW.item_name) THEN
182                         RAISE EXCEPTION ''We do not stock anything like that.'';
183                 END IF;
184
185                 -- If we have a balance for the item, ensure there is sufficient money
186                 SELECT INTO cost items.item_cost_cents FROM items WHERE item_name = NEW.item_name;
187                 
188                 -- If the cost is null, it may vary and account keeping is done by the
189                 -- relevant modules
190                 IF cost IS NOT NULL THEN
191                         SELECT INTO balance users.user_balance_cents FROM users WHERE user_name = NEW.request_target_name;
192                         IF in_coke(NEW.request_requestor_name) THEN
193                                 IF balance - cost < -2000 THEN
194                                         -- They''re in coke, let them take balances to -$20.00
195                                         RAISE EXCEPTION ''You may not dispense below -$20.00.'';
196                                 END IF;
197                         ELSE
198                                 -- Else simply ensure there is enough money
199                                 IF cost > balance THEN
200                                         RAISE EXCEPTION ''You do not have sufficient funds to do that.'';
201                                 END IF;
202                         END IF;
203
204                         -- Deduct money. This will be rolled back if any other triggers fail.
205                         UPDATE users SET user_balance_cents = user_balance_cents - cost WHERE user_name = NEW.request_target_name;
206                 END IF;
207
208                 -- Adjust stock levels as required
209                 SELECT INTO stock item_stock FROM items WHERE item_name = NEW.item_name;
210                 IF stock IS NOT NULL THEN
211                         IF stock = 0 THEN
212                                 RAISE EXCEPTION ''We are out of stock of that item.'';
213                         END IF;
214                         IF stock > 0 THEN
215                                 UPDATE items SET item_stock = item_stock - 1 WHERE item_name = NEW.item_name;
216                         END IF;
217                         -- if item stock is -1, then stock levels are adjusted by other things
218                 END IF;
219
220                 RETURN NEW;
221         END;
222         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
223 REVOKE ALL ON FUNCTION first_request_handler() FROM public;
224 CREATE TRIGGER a00_first_request_trigger BEFORE INSERT ON requests FOR EACH ROW
225         EXECUTE PROCEDURE first_request_handler();
226
227 CREATE OR REPLACE FUNCTION final_request_handler() RETURNS "trigger" AS
228         '
229         BEGIN
230                 IF NEW.handled = false THEN
231                         RAISE EXCEPTION ''Nothing wanted to service your request!'';
232                 END IF;
233                 RETURN NEW;
234         END;
235         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
236 REVOKE ALL ON FUNCTION final_request_handler() FROM public;
237 CREATE TRIGGER zzz_last_request_trigger BEFORE INSERT ON requests FOR EACH ROW
238         EXECUTE PROCEDURE final_request_handler();
239
240 ------------------------------------------------------------------------------
241 --- Dummy Services                                                         ---
242 ------------------------------------------------------------------------------
243
244 INSERT INTO sources VALUES ('nothing');
245
246 INSERT INTO items VALUES ('nothing', 'nothing', NULL, NULL, NULL, NULL);
247 CREATE OR REPLACE FUNCTION nothing_nothing_trigger() RETURNS "trigger" AS
248         '
249         DECLARE
250         BEGIN
251                 -- Check if we are to handle this request
252                 IF NEW.handled OR NEW.item_name <> ''nothing'' THEN
253                         RETURN NEW;
254                 END IF;
255
256                 -- Mark the request as having been dealt with
257                 NEW.handled = true;
258                 RETURN NEW;
259         END;
260         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
261 REVOKE ALL ON FUNCTION nothing_nothing_trigger() FROM public;
262 CREATE TRIGGER nothing_nothing_trigger BEFORE INSERT ON requests FOR EACH ROW
263         EXECUTE PROCEDURE nothing_nothing_trigger();
264
265 DROP FUNCTION can_show_pre_nothing(varchar, varchar);
266 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_nothing;
267 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
268         'SELECT can_show_pre_nothing($1, $2) OR $2 = ''nothing'''
269         LANGUAGE SQL EXTERNAL SECURITY DEFINER;
270 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
271
272 ------------------------------------------------------------------------------
273 --- Coke Bank Services                                                     ---
274 ------------------------------------------------------------------------------
275
276 INSERT INTO sources VALUES ('cokebank');
277
278 INSERT INTO items VALUES ('cokebank', 'give', NULL, NULL, '<username> <cents>', NULL);
279 CREATE OR REPLACE FUNCTION cokebank_give_trigger() RETURNS "trigger" AS
280         '
281         DECLARE
282                 give_amount INTEGER;
283
284                 source_user VARCHAR(8);
285                 target_user VARCHAR(8);
286                 source_user_balance INTEGER;
287         BEGIN
288                 -- Check if we are to handle this request
289                 IF NEW.handled OR NEW.item_name <> ''give'' THEN
290                         RETURN NEW;
291                 END IF;
292
293                 -- Check for our two parameters, username, amount
294                 IF NEW.params IS NULL THEN
295                         RAISE EXCEPTION ''Missing parameters for give'';
296                 END IF;
297                 IF array_upper(NEW.params, 1) <> 2 THEN
298                         RAISE EXCEPTION ''Invalid number of parameters for give'';
299                 END IF;
300                 IF NEW.params[1] NOT IN (SELECT user_name FROM users) THEN
301                         RAISE EXCEPTION ''Invalid user to give to'';
302                 END IF;
303                 give_amount = CAST(CAST(NEW.params[2] AS TEXT) AS INTEGER);
304                 source_user = NEW.request_target_name;
305                 target_user = NEW.params[1];
306
307                 -- Can''t give to one''s self
308                 IF source_user = target_user THEN
309                         RAISE EXCEPTION ''You can''''t give to one''''s self'';
310                 END IF;
311                 -- Can''t give negative amounts
312                 IF give_amount <= 0 THEN
313                         RAISE EXCEPTION ''You can only give positive balances'';
314                 END IF;
315
316                 -- Check the appropriate balances
317                 SELECT INTO source_user_balance users.user_balance_cents FROM users WHERE user_name = source_user;
318
319                 IF source_user_balance < give_amount AND not in_coke(NEW.request_requestor_name) THEN
320                         RAISE EXCEPTION ''You do not have sufficient funds to give that much!'';
321                 END IF;
322
323                 -- Perform the request
324                 UPDATE users SET user_balance_cents = user_balance_cents - give_amount WHERE user_name = source_user;
325                 UPDATE users SET user_balance_cents = user_balance_cents + give_amount WHERE user_name = target_user;
326                 -- Mark the request as having been dealt with
327                 NEW.handled = true;
328                 RETURN NEW;
329         END;
330         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
331 REVOKE ALL ON FUNCTION cokebank_give_trigger() FROM public;
332 CREATE TRIGGER cokebank_give_trigger BEFORE INSERT ON requests FOR EACH ROW
333         EXECUTE PROCEDURE cokebank_give_trigger();
334
335 INSERT INTO items VALUES ('cokebank', 'acct', NULL, NULL, '<cents> <reason>', NULL);
336 CREATE OR REPLACE FUNCTION cokebank_acct_trigger() RETURNS "trigger" AS
337         '
338         DECLARE
339                 amount INTEGER;
340                 target_user VARCHAR(8);
341                 reason VARCHAR;
342                 user_bal RECORD;
343         BEGIN
344                 -- Check if we are to handle this request
345                 IF NEW.handled OR NEW.item_name <> ''acct'' THEN
346                         RETURN NEW;
347                 END IF;
348
349                 -- Check requestor is in coke
350                 IF NOT in_coke(NEW.request_requestor_name) THEN
351                         RAISE EXCEPTION ''You need to be in the coke group to use acct'';
352                 END IF;
353
354                 -- Check for our two parameters, amount and reason
355                 IF NEW.params IS NULL THEN
356                         RAISE EXCEPTION ''No parameters supplied to acct'';
357                 END IF;
358                 IF array_upper(NEW.params, 1) <> 2 THEN
359                         RAISE EXCEPTION ''Invalid number of parameters for acct'';
360                 END IF;
361                 amount = CAST(CAST(NEW.params[1] AS TEXT) AS INTEGER);
362                 reason = NEW.params[2];
363                 target_user = NEW.request_target_name;
364
365                 -- Perform the request
366                 UPDATE users SET user_balance_cents = user_balance_cents + amount WHERE user_name = target_user;
367                 -- Mark the request as having been dealt with
368                 NEW.handled = true;
369                 RETURN NEW;
370         END;
371         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
372 REVOKE ALL ON FUNCTION cokebank_acct_trigger() FROM public;
373 CREATE TRIGGER cokebank_acct_trigger BEFORE INSERT ON requests FOR EACH ROW
374         EXECUTE PROCEDURE cokebank_acct_trigger();
375
376 -- Don't show the acct function to non-coke people
377 DROP FUNCTION can_show_pre_cokebank(varchar, varchar);
378 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_cokebank;
379 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
380         'SELECT can_show_pre_cokebank($1, $2) AND NOT ($2 = ''acct'' AND NOT in_coke($1))'
381         LANGUAGE SQL EXTERNAL SECURITY DEFINER;
382 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
383
384 ------------------------------------------------------------------------------
385 --- Coke Machine Services                                                  ---
386 ------------------------------------------------------------------------------
387
388 INSERT INTO sources VALUES ('coke');
389
390 INSERT INTO items VALUES ('coke', 'passion pop',        80, array['0'], NULL, 8);
391 INSERT INTO items VALUES ('coke', 'vb',                         80, array['1'], NULL, 0);
392 INSERT INTO items VALUES ('coke', 'emu export',         80, array['2'], NULL, 3);
393 INSERT INTO items VALUES ('coke', 'whiskey',            80, array['3'], NULL, 4);
394 INSERT INTO items VALUES ('coke', 'champagne',          80, array['4'], NULL, 9);
395 INSERT INTO items VALUES ('coke', 'grape juice',        80, array['5'], NULL, 1);
396 INSERT INTO items VALUES ('coke', 'coke powder',        80, array['6'], NULL, 11);
397
398 INSERT INTO items VALUES ('coke', 'update_coke',        NULL, NULL, '<slot number> <new name> <new price> <stock count>', NULL);
399
400 DROP TABLE coke_requests;
401 CREATE TABLE coke_requests (
402         request_id integer,
403     request_slot integer,
404         request_handled boolean NOT NULL DEFAULT false,
405         request_time timestamp DEFAULT now()
406 );
407
408 CREATE OR REPLACE FUNCTION update_slot_info(integer, varchar(20), integer, integer) RETURNS void AS
409         '
410         BEGIN
411                 -- Check such a slot exists
412                 IF NOT EXISTS(SELECT item_name FROM items WHERE item_data[1] = cast($1 as varchar) AND source_id = ''coke'') THEN
413                         RAISE EXCEPTION ''There is no such slot %'', $1;
414                 END IF;
415                 IF $2 IS NOT NULL THEN
416                         UPDATE items SET item_name = $2 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke'';
417                 END IF;
418                 IF $3 IS NOT NULL THEN
419                         UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke'';
420                 END IF;
421                 IF $4 IS NOT NULL THEN
422                         UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
423                 END IF;
424         END;
425         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
426 REVOKE ALL ON FUNCTION update_slot_info(integer, varchar, integer, integer) FROM public;
427
428 CREATE OR REPLACE FUNCTION dispense_slot(integer, integer) RETURNS void AS
429         '
430         elog(NOTICE, q(And out pops a drink!));
431         INSERT INTO coke_requests VALUES ($1, $2);
432         NOTIFY coke_requests;
433         ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
434 REVOKE ALL ON FUNCTION dispense_slot(integer, integer) FROM public;
435
436 CREATE OR REPLACE FUNCTION coke_slot_trigger() RETURNS "trigger" AS
437         '
438         DECLARE
439                 new_name VARCHAR(20);
440                 new_cost INTEGER;
441                 new_stock INTEGER;
442         BEGIN
443                 -- Check if we are to handle this request
444                 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN
445                         RETURN NEW;
446                 END IF;
447
448                 -- Check if we are wanted to update a slot
449                 IF NEW.item_name = ''update_coke'' THEN
450                         -- Check parameters
451                         IF NEW.params IS NULL THEN
452                                 RAISE EXCEPTION ''Missing parameters for update_coke'';
453                         END IF;
454                         IF array_upper(NEW.params, 1) <> 4 THEN
455                                 RAISE EXCEPTION ''Invalid number of parameters for update_coke'';
456                         END IF;
457                         IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''coke'' and item_data[1] IS NOT NULL) THEN
458                                 new_name = NEW.params[1];
459                                 RAISE EXCEPTION ''No idea what slot % is.'', new_name;
460                         END IF;
461                         new_name = NEW.params[2];
462                         IF NEW.params[3] <> '''' THEN
463                                 new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER);
464                                 IF new_cost <= 0 THEN
465                                         RAISE EXCEPTION ''Amount must be positive'';
466                                 END IF;
467                                 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
468                         END IF;
469                         IF NEW.params[4] <> '''' THEN
470                                 new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER);
471                                 IF new_stock <= -1 THEN
472                                         RAISE EXCEPTION ''Stock count must be -1 or >= 0'';
473                                 END IF;
474                                 UPDATE items SET item_stock = new_stock WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
475                         END IF;
476                         UPDATE items SET item_name = new_name WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
477                 END IF;
478
479                 -- The default handler will have checked that we have stock and
480                 -- deducted money et al.
481                 --
482                 -- So if we get this far, we don''t need to care about much.
483                 -- Dropping the drink & checking stock occurs in the AFTER-INSERT trigger.
484
485                 -- Mark the request as having been dealt with
486                 NEW.handled = true;
487                 RETURN NEW;
488         END;
489         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
490 REVOKE ALL ON FUNCTION coke_slot_trigger() FROM public;
491 CREATE TRIGGER coke_slot_trigger BEFORE INSERT ON requests FOR EACH ROW
492         EXECUTE PROCEDURE coke_slot_trigger();
493
494 CREATE OR REPLACE FUNCTION coke_slot_dispense() RETURNS "trigger" AS
495         '
496         DECLARE
497                 slotnum integer;
498                 stock integer;
499         BEGIN
500                 IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN
501                         -- Drop a drink and update stock
502                         SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''coke'';
503                         PERFORM dispense_slot(NEW.request_id, slotnum);
504                 END IF;
505                 RETURN NULL;
506         END;
507         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
508 REVOKE ALL ON FUNCTION coke_slot_dispense() FROM public;
509 CREATE TRIGGER coke_slot_dispense AFTER INSERT ON requests FOR EACH ROW
510         EXECUTE PROCEDURE coke_slot_dispense();
511
512 -- Don't show the update function to non-coke people
513 DROP FUNCTION can_show_pre_coke(varchar, varchar);
514 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_coke;
515 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
516         'SELECT can_show_pre_coke($1, $2) AND NOT ($2 = ''update_coke'' AND NOT in_coke($1))'
517         LANGUAGE SQL EXTERNAL SECURITY DEFINER;
518 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
519
520 ------------------------------------------------------------------------------
521 --- Vending Machine Services                                               ---
522 ------------------------------------------------------------------------------
523
524 INSERT INTO sources VALUES ('vend');
525
526 INSERT INTO items VALUES ('vend', 'twisties',                   120, array['11'], NULL, 8);
527 INSERT INTO items VALUES ('vend', 'cheese and onion',   125, array['21'], NULL, 0);
528 INSERT INTO items VALUES ('vend', 'update_vend', NULL, NULL, '<slot number> <new name> <new price> <new count>', NULL);
529
530 DROP TABLE vend_requests;
531 CREATE TABLE vend_requests (
532         request_id integer,
533     request_slot varchar(2),
534         request_handled boolean NOT NULL DEFAULT false,
535         request_time timestamp DEFAULT now()
536 );
537 GRANT SELECT ON vend_requests TO vendserver;
538
539 CREATE OR REPLACE FUNCTION vend_success(integer) RETURNS void AS
540         'UPDATE vend_requests SET request_handled = true WHERE request_id = $1' 
541         LANGUAGE SQL EXTERNAL SECURITY DEFINER;
542 REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
543 GRANT EXECUTE ON FUNCTION vend_success(integer) TO vendserver;
544
545 CREATE OR REPLACE FUNCTION vend_failed(integer) RETURNS void AS
546         '
547         BEGIN
548                 UPDATE vend_requests SET request_handled = true WHERE request_id = $1;
549                 PERFORM refund($1);
550         END;
551         '
552         LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
553 REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
554 GRANT EXECUTE ON FUNCTION vend_failed(integer) TO vendserver;
555
556 CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS
557         'SELECT $1 ~ ''^[0-9]{2}$''' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
558 REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
559
560 CREATE OR REPLACE FUNCTION update_vend_slot_info(varchar(2), varchar(20), integer, integer) RETURNS void AS
561         '
562         BEGIN
563                 -- Check such a slot exists
564                 IF NOT valid_vend_slot($1) THEN
565                         RAISE EXCEPTION ''There is no such slot %'', $1;
566                 END IF;
567                 IF $2 IS NOT NULL THEN
568                         UPDATE items SET item_name = $2 WHERE item_data[1] = $1 and source_id = ''vend'';
569                 END IF;
570                 IF $3 IS NOT NULL THEN
571                         UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = $1 and source_id = ''vend'';
572                 END IF;
573                 IF $4 IS NOT NULL THEN
574                         UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
575                 END IF;
576         END;
577         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
578 REVOKE ALL ON FUNCTION update_vend_slot_info(varchar(2), varchar, integer, integer) FROM public;
579
580 CREATE OR REPLACE FUNCTION vend_slot(integer, varchar(2)) RETURNS void AS
581         '
582         BEGIN
583                 RAISE NOTICE ''And out pops a snack'';
584                 INSERT INTO vend_requests (request_id, request_slot) VALUES ($1, $2);
585                 NOTIFY vend_requests;
586                 RETURN;
587         END;
588         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
589 REVOKE ALL ON FUNCTION vend_slot(integer, varchar(2)) FROM public;
590
591 CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS
592         '
593         DECLARE
594                 new_name VARCHAR(20);
595                 new_cost INTEGER;
596                 new_stock INTEGER;
597         BEGIN
598                 -- Check if we are to handle this request
599                 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN
600                         RETURN NEW;
601                 END IF;
602
603                 -- Check if we are wanted to update a slot
604                 IF NEW.item_name = ''update_vend'' THEN
605                         -- Check parameters
606                         IF NEW.params IS NULL THEN
607                                 RAISE EXCEPTION ''Missing parameters for update_vend'';
608                         END IF;
609                         IF array_upper(NEW.params, 1) <> 4 THEN
610                                 RAISE EXCEPTION ''Invalid number of parameters for update_vend'';
611                         END IF;
612                         IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''vend'' and item_data[1] IS NOT NULL) THEN
613                                 new_name = NEW.params[1];
614                                 RAISE EXCEPTION ''No idea what slot % is.'', new_name;
615                         END IF;
616                         new_name = NEW.params[2];
617                         IF NEW.params[3] <> '''' THEN
618                                 new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER);
619                                 IF new_cost <= 0 THEN
620                                         RAISE EXCEPTION ''Amount must be positive'';
621                                 END IF;
622                                 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
623                         END IF;
624                         IF NEW.params[4] <> '''' THEN
625                                 new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER);
626                                 IF new_stock <= -1 THEN
627                                         RAISE EXCEPTION ''Stock count must be -1 or >= 0'';
628                                 END IF;
629                                 UPDATE items SET item_stock = new_stock WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
630                         END IF;
631                         UPDATE items SET item_name = new_name WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
632                 END IF;
633
634                 -- The default handler will have checked that we have stock and
635                 -- deducted money et al.
636                 --
637                 -- So if we get this far, we don''t need to care about much.
638                 -- Dropping the snack occurs in the AFTER-INSERT trigger.
639
640                 -- Mark the request as having been dealt with
641                 NEW.handled = true;
642                 RETURN NEW;
643         END;
644         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
645 REVOKE ALL ON FUNCTION vend_slot_trigger() FROM public;
646 CREATE TRIGGER vend_slot_trigger BEFORE INSERT ON requests FOR EACH ROW
647         EXECUTE PROCEDURE vend_slot_trigger();
648
649 CREATE OR REPLACE FUNCTION vend_slot_dispense() RETURNS "trigger" AS
650         '
651         DECLARE
652                 slotnum varchar(2);
653         BEGIN
654                 IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN
655                         -- Drop a snack and update stock
656                         SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''vend'';
657                         PERFORM vend_slot(NEW.request_id, slotnum);
658                 END IF;
659                 RETURN NULL;
660         END;
661         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
662 REVOKE ALL ON FUNCTION vend_slot_dispense() FROM public;
663 CREATE TRIGGER vend_slot_dispense AFTER INSERT ON requests FOR EACH ROW
664         EXECUTE PROCEDURE vend_slot_dispense();
665
666 -- Don't show the update function to non-coke people
667 DROP FUNCTION can_show_pre_vend(varchar, varchar);
668 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_vend;
669 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
670         'SELECT can_show_pre_vend($1, $2) AND NOT ($2 = ''update_vend'' AND NOT in_coke($1))'
671         LANGUAGE SQL EXTERNAL SECURITY DEFINER;
672 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
673
674 ------------------------------------------------------------------------------
675 --- UCC Door Services                                                      ---
676 ------------------------------------------------------------------------------
677
678 CREATE TABLE doorcontrollers (
679     user_name varchar(16) UNIQUE references users(user_name),
680         reason varchar(250)
681 );
682
683 CREATE OR REPLACE FUNCTION in_door(varchar(16)) RETURNS boolean
684     AS 'SELECT EXISTS(SELECT user_name FROM doorcontrollers WHERE user_name = $1)'
685     LANGUAGE sql EXTERNAL SECURITY DEFINER;
686 REVOKE ALL ON FUNCTION in_door(varchar(16)) FROM public;
687
688 INSERT INTO sources VALUES ('door');
689
690 INSERT INTO items VALUES ('door', 'opendoor', NULL, NULL, NULL, NULL);
691
692 CREATE OR REPLACE FUNCTION open_door() RETURNS void AS
693         '
694         system("/usr/bin/sudo /usr/sbin/opendoor");
695         ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
696 REVOKE ALL ON FUNCTION open_door() FROM public;
697
698 CREATE OR REPLACE FUNCTION door_open_trigger() RETURNS "trigger" AS
699         '
700         BEGIN
701                 -- Check if we are to handle this request
702                 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''door'' AND item_name = NEW.item_name) THEN
703                         RETURN NEW;
704                 END IF;
705
706                 -- You can''t open the door for somebody else
707                 IF NEW.request_requestor_name <> NEW.request_target_name THEN
708                         RAISE EXCEPTION ''You can''''t open the door for somebody else.'';
709                 END IF;
710
711                 -- Ensure the user is in the door group
712                 IF NOT in_door(NEW.request_requestor_name) THEN
713                         RAISE EXCEPTION ''You are not permitted to open the door.'';
714                 END IF;
715
716                 -- Mark the request as having been dealt with
717                 NEW.handled = true;
718                 RETURN NEW;
719         END;
720         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
721 REVOKE ALL ON FUNCTION door_open_trigger() FROM public;
722 CREATE TRIGGER door_open_trigger BEFORE INSERT ON requests FOR EACH ROW
723         EXECUTE PROCEDURE door_open_trigger();
724
725 CREATE OR REPLACE FUNCTION door_open_do() RETURNS "trigger" AS
726         '
727         BEGIN
728                 IF NEW.handled AND NEW.item_name = ''opendoor'' THEN
729                         PERFORM open_door();
730                 END IF;
731                 RETURN NULL;
732         END;
733         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
734 REVOKE ALL ON FUNCTION door_open_do() FROM public;
735 CREATE TRIGGER door_open_do AFTER INSERT ON requests FOR EACH ROW
736         EXECUTE PROCEDURE door_open_do();
737
738 -- Don't show the door functions to non-door people
739 DROP FUNCTION can_show_pre_door(varchar, varchar);
740 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_door;
741 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
742         'SELECT can_show_pre_door($1, $2) AND NOT ($2 = ''opendoor'' AND (NOT in_door($1)))' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
743 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
744

UCC git Repository :: git.ucc.asn.au