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