Initial import
[zanchey/dispense2.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 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;
118
119 CREATE OR REPLACE FUNCTION byte_to_cent_trigger() RETURNS "trigger" AS
120         '
121         DECLARE
122                 bytes_per_cent INTEGER;
123                 cents_per_meg INTEGER;
124         BEGIN
125                 cents_per_meg = 4;
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;
130                 END IF;
131                 RETURN NEW;
132         END;
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();
137
138 CREATE OR REPLACE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
139         '
140         DECLARE
141                 ident varchar;
142         BEGIN
143                 IF $1 NOT IN (SELECT user_name FROM users) THEN
144                         RETURN false;
145                 END IF;
146                 ident = get_ident();
147                 IF ident = $1 OR in_coke(ident) THEN
148                         RETURN true;
149                 END IF;
150                 RETURN false;
151         END;
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;
156
157 REVOKE ALL ON FUNCTION get_services(varchar(16)) FROM public;
158 GRANT EXECUTE ON FUNCTION get_services(varchar(16)) TO anondispense;
159
160 CREATE OR REPLACE FUNCTION first_request_handler() RETURNS "trigger" AS
161         '
162         DECLARE
163                 cost INTEGER;
164                 stock INTEGER;
165                 balance INTEGER;
166         BEGIN
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'';
171                 END IF;
172
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.'';
176                 END IF;
177
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;
180                 
181                 -- If the cost is null, it may vary and account keeping is done by the
182                 -- relevant modules
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.'';
189                                 END IF;
190                         ELSE
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.'';
194                                 END IF;
195                         END IF;
196
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;
199                 END IF;
200
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
204                         IF stock = 0 THEN
205                                 RAISE EXCEPTION ''We are out of stock of that item.'';
206                         END IF;
207                         IF stock > 0 THEN
208                                 UPDATE items SET item_stock = item_stock - 1 WHERE item_name = NEW.item_name;
209                         END IF;
210                         -- if item stock is -1, then stock levels are adjusted by other things
211                 END IF;
212
213                 RETURN NEW;
214         END;
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();
219
220 CREATE OR REPLACE FUNCTION final_request_handler() RETURNS "trigger" AS
221         '
222         BEGIN
223                 IF NEW.handled = false THEN
224                         RAISE EXCEPTION ''Nothing wanted to service your request!'';
225                 END IF;
226                 RETURN NEW;
227         END;
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();
232
233 ---------------------------------------------------------------------------------
234 --- Dummy Services                                                            ---
235 ---------------------------------------------------------------------------------
236
237 INSERT INTO sources VALUES ('nothing');
238
239 INSERT INTO items VALUES ('nothing', 'nothing', NULL, NULL, NULL, NULL);
240 CREATE OR REPLACE FUNCTION nothing_nothing_trigger() RETURNS "trigger" AS
241         '
242         DECLARE
243         BEGIN
244                 -- Check if we are to handle this request
245                 IF NEW.handled OR NEW.item_name <> ''nothing'' THEN
246                         RETURN NEW;
247                 END IF;
248
249                 -- Mark the request as having been dealt with
250                 NEW.handled = true;
251                 RETURN NEW;
252         END;
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();
257
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;
264
265 ---------------------------------------------------------------------------------
266 --- Coke Bank Services                                                        ---
267 ---------------------------------------------------------------------------------
268
269 INSERT INTO sources VALUES ('cokebank');
270
271 INSERT INTO items VALUES ('cokebank', 'give', NULL, NULL, '<username> <cents>', NULL);
272 CREATE OR REPLACE FUNCTION cokebank_give_trigger() RETURNS "trigger" AS
273         '
274         DECLARE
275                 give_amount INTEGER;
276
277                 source_user VARCHAR(8);
278                 target_user VARCHAR(8);
279                 source_user_balance INTEGER;
280         BEGIN
281                 -- Check if we are to handle this request
282                 IF NEW.handled OR NEW.item_name <> ''give'' THEN
283                         RETURN NEW;
284                 END IF;
285
286                 -- Check for our two parameters, username, amount
287                 IF NEW.params IS NULL THEN
288                         RAISE EXCEPTION ''Missing parameters for give'';
289                 END IF;
290                 IF array_upper(NEW.params, 1) <> 2 THEN
291                         RAISE EXCEPTION ''Invalid number of parameters for give'';
292                 END IF;
293                 IF NEW.params[1] NOT IN (SELECT user_name FROM users) THEN
294                         RAISE EXCEPTION ''Invalid user to give to'';
295                 END IF;
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];
299
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'';
303                 END IF;
304                 -- Can''t give negative amounts
305                 IF give_amount <= 0 THEN
306                         RAISE EXCEPTION ''You can only give positive balances'';
307                 END IF;
308
309                 -- Check the appropriate balances
310                 SELECT INTO source_user_balance users.user_balance_cents FROM users WHERE user_name = source_user;
311
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!'';
314                 END IF;
315
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
320                 NEW.handled = true;
321                 RETURN NEW;
322         END;
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();
327
328 INSERT INTO items VALUES ('cokebank', 'acct', NULL, NULL, '<cents> <reason>', NULL);
329 CREATE OR REPLACE FUNCTION cokebank_acct_trigger() RETURNS "trigger" AS
330         '
331         DECLARE
332                 amount INTEGER;
333                 target_user VARCHAR(8);
334                 reason VARCHAR;
335                 user_bal RECORD;
336         BEGIN
337                 -- Check if we are to handle this request
338                 IF NEW.handled OR NEW.item_name <> ''acct'' THEN
339                         RETURN NEW;
340                 END IF;
341
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'';
345                 END IF;
346
347                 -- Check for our two parameters, amount and reason
348                 IF NEW.params IS NULL THEN
349                         RAISE EXCEPTION ''No parameters supplied to acct'';
350                 END IF;
351                 IF array_upper(NEW.params, 1) <> 2 THEN
352                         RAISE EXCEPTION ''Invalid number of parameters for acct'';
353                 END IF;
354                 amount = CAST(CAST(NEW.params[1] AS TEXT) AS INTEGER);
355                 reason = NEW.params[2];
356                 target_user = NEW.request_target_name;
357
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
361                 NEW.handled = true;
362                 RETURN NEW;
363         END;
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();
368
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;
376
377 ---------------------------------------------------------------------------------
378 --- Coke Machine Services                                                     ---
379 ---------------------------------------------------------------------------------
380
381 INSERT INTO sources VALUES ('coke');
382
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);
390
391 INSERT INTO items VALUES ('coke', 'update_coke',        NULL, NULL, '<slot number> <new name> <new price> <stock count>', NULL);
392
393 CREATE OR REPLACE FUNCTION update_slot_info(integer, varchar(20), integer, integer) RETURNS void AS
394         '
395         BEGIN
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;
399                 END IF;
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'';
402                 END IF;
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'';
405                 END IF;
406                 IF $4 IS NOT NULL THEN
407                         UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
408                 END IF;
409         END;
410         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
411 REVOKE ALL ON FUNCTION update_slot_info(integer, varchar, integer, integer) FROM public;
412
413 CREATE OR REPLACE FUNCTION dispense_slot(integer) RETURNS void AS
414         '
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;
418
419 CREATE OR REPLACE FUNCTION update_slot_status() RETURNS void AS
420         '
421         ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
422 REVOKE ALL ON FUNCTION update_slot_status() FROM public;
423
424 CREATE OR REPLACE FUNCTION coke_slot_trigger() RETURNS "trigger" AS
425         '
426         DECLARE
427                 new_name VARCHAR(20);
428                 new_cost INTEGER;
429                 new_stock INTEGER;
430         BEGIN
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
433                         RETURN NEW;
434                 END IF;
435
436                 -- Check if we are wanted to update a slot
437                 IF NEW.item_name = ''update_coke'' THEN
438                         -- Check parameters
439                         IF NEW.params IS NULL THEN
440                                 RAISE EXCEPTION ''Missing parameters for update_coke'';
441                         END IF;
442                         IF array_upper(NEW.params, 1) <> 4 THEN
443                                 RAISE EXCEPTION ''Invalid number of parameters for update_coke'';
444                         END IF;
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;
448                         END IF;
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'';
454                                 END IF;
455                                 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
456                         END IF;
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'';
461                                 END IF;
462                                 UPDATE items SET item_stock = new_stock WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
463                         END IF;
464                         UPDATE items SET item_name = new_name WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
465                 END IF;
466
467                 -- The default handler will have checked that we have stock and
468                 -- deducted money et al.
469                 --
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.
472
473                 -- Mark the request as having been dealt with
474                 NEW.handled = true;
475                 RETURN NEW;
476         END;
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();
481
482 CREATE OR REPLACE FUNCTION coke_slot_dispense() RETURNS "trigger" AS
483         '
484         DECLARE
485                 slotnum integer;
486                 stock integer;
487         BEGIN
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();
493                 END IF;
494                 RETURN NULL;
495         END;
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();
500
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;
508
509 ---------------------------------------------------------------------------------
510 --- Vending Machine Services                                                  ---
511 ---------------------------------------------------------------------------------
512
513 INSERT INTO sources VALUES ('vend');
514
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);
518
519 CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS
520         '
521         BEGIN
522                 RETURN $1 ~ ''^[0-9]{2}$'';
523         END;
524         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
525 REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
526
527 CREATE OR REPLACE FUNCTION update_vend_slot_info(varchar(2), varchar(20), integer, integer) RETURNS void AS
528         '
529         BEGIN
530                 -- Check such a slot exists
531                 IF NOT valid_vend_slot($1) THEN
532                         RAISE EXCEPTION ''There is no such slot %'', $1;
533                 END IF;
534                 IF $2 IS NOT NULL THEN
535                         UPDATE items SET item_name = $2 WHERE item_data[1] = $1 and source_id = ''vend'';
536                 END IF;
537                 IF $3 IS NOT NULL THEN
538                         UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = $1 and source_id = ''vend'';
539                 END IF;
540                 IF $4 IS NOT NULL THEN
541                         UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
542                 END IF;
543         END;
544         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
545 REVOKE ALL ON FUNCTION update_vend_slot_info(varchar(2), varchar, integer, integer) FROM public;
546
547 CREATE OR REPLACE FUNCTION vend_slot(varchar(2)) RETURNS void AS
548         '
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;
552
553 CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS
554         '
555         DECLARE
556                 new_name VARCHAR(20);
557                 new_cost INTEGER;
558                 new_stock INTEGER;
559         BEGIN
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
562                         RETURN NEW;
563                 END IF;
564
565                 -- Check if we are wanted to update a slot
566                 IF NEW.item_name = ''update_vend'' THEN
567                         -- Check parameters
568                         IF NEW.params IS NULL THEN
569                                 RAISE EXCEPTION ''Missing parameters for update_vend'';
570                         END IF;
571                         IF array_upper(NEW.params, 1) <> 4 THEN
572                                 RAISE EXCEPTION ''Invalid number of parameters for update_vend'';
573                         END IF;
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;
577                         END IF;
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'';
583                                 END IF;
584                                 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
585                         END IF;
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'';
590                                 END IF;
591                                 UPDATE items SET item_stock = new_stock WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
592                         END IF;
593                         UPDATE items SET item_name = new_name WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
594                 END IF;
595
596                 -- The default handler will have checked that we have stock and
597                 -- deducted money et al.
598                 --
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.
601
602                 -- Mark the request as having been dealt with
603                 NEW.handled = true;
604                 RETURN NEW;
605         END;
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();
610
611 CREATE OR REPLACE FUNCTION vend_slot_dispense() RETURNS "trigger" AS
612         '
613         DECLARE
614                 slotnum varchar(2);
615         BEGIN
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);
620                 END IF;
621                 RETURN NULL;
622         END;
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();
627
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;
635
636 ---------------------------------------------------------------------------------
637 --- UCC Door Services                                                         ---
638 ---------------------------------------------------------------------------------
639
640 CREATE TABLE doorcontrollers (
641     user_name varchar(16) UNIQUE references users(user_name),
642         reason varchar(250)
643 );
644
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;
649
650 INSERT INTO sources VALUES ('door');
651
652 INSERT INTO items VALUES ('door', 'opendoor', NULL, NULL, NULL, NULL);
653
654 CREATE OR REPLACE FUNCTION open_door() RETURNS void AS
655         '
656         system("/usr/bin/sudo /usr/sbin/opendoor");
657         ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
658 REVOKE ALL ON FUNCTION open_door() FROM public;
659
660 CREATE OR REPLACE FUNCTION door_open_trigger() RETURNS "trigger" AS
661         '
662         BEGIN
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
665                         RETURN NEW;
666                 END IF;
667
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.'';
671                 END IF;
672
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.'';
676                 END IF;
677
678                 -- Mark the request as having been dealt with
679                 NEW.handled = true;
680                 RETURN NEW;
681         END;
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();
686
687 CREATE OR REPLACE FUNCTION door_open_do() RETURNS "trigger" AS
688         '
689         BEGIN
690                 IF NEW.handled AND NEW.item_name = ''opendoor'' THEN
691                         PERFORM open_door();
692                 END IF;
693                 RETURN NULL;
694         END;
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();
699
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;
707

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