sql-edition/schema/d.sql

   1 ------------------------------------------------------------------------------
   2 ------------------------------------------------------------------------------
   3 ---                                                                        ---
   4 ---    DISPENSE IN POSTGRES REWRITE                                        ---
   5 ---                           Bernard Blackham <[email protected]> ---
   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 TABLE coke_requests (
 394         request_id integer,
 395     request_slot integer,
 396         request_handled boolean NOT NULL DEFAULT false,
 397         request_time timestamp DEFAULT now()
 398 );
 399
 400 CREATE OR REPLACE FUNCTION update_slot_info(integer, varchar(20), integer, integer) RETURNS void AS
 401         '
 402         BEGIN
 403                 -- Check such a slot exists
 404                 IF NOT EXISTS(SELECT item_name FROM items WHERE item_data[1] = cast($1 as varchar) AND source_id = ''coke'') THEN
 405                         RAISE EXCEPTION ''There is no such slot %'', $1;
 406                 END IF;
 407                 IF $2 IS NOT NULL THEN
 408                         UPDATE items SET item_name = $2 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke'';
 409                 END IF;
 410                 IF $3 IS NOT NULL THEN
 411                         UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke'';
 412                 END IF;
 413                 IF $4 IS NOT NULL THEN
 414                         UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
 415                 END IF;
 416         END;
 417         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 418 REVOKE ALL ON FUNCTION update_slot_info(integer, varchar, integer, integer) FROM public;
 419
 420 CREATE OR REPLACE FUNCTION dispense_slot(integer, integer) RETURNS void AS
 421         '
 422         elog(NOTICE, q(And out pops a drink!));
 423         INSERT INTO coke_requests VALUES ($1, $2);
 424         NOTIFY coke_requests;
 425         ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
 426 REVOKE ALL ON FUNCTION dispense_slot(integer, integer) FROM public;
 427
 428 CREATE OR REPLACE FUNCTION coke_slot_trigger() RETURNS "trigger" AS
 429         '
 430         DECLARE
 431                 new_name VARCHAR(20);
 432                 new_cost INTEGER;
 433                 new_stock INTEGER;
 434         BEGIN
 435                 -- Check if we are to handle this request
 436                 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN
 437                         RETURN NEW;
 438                 END IF;
 439
 440                 -- Check if we are wanted to update a slot
 441                 IF NEW.item_name = ''update_coke'' THEN
 442                         -- Check parameters
 443                         IF NEW.params IS NULL THEN
 444                                 RAISE EXCEPTION ''Missing parameters for update_coke'';
 445                         END IF;
 446                         IF array_upper(NEW.params, 1) <> 4 THEN
 447                                 RAISE EXCEPTION ''Invalid number of parameters for update_coke'';
 448                         END IF;
 449                         IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''coke'' and item_data[1] IS NOT NULL) THEN
 450                                 new_name = NEW.params[1];
 451                                 RAISE EXCEPTION ''No idea what slot % is.'', new_name;
 452                         END IF;
 453                         new_name = NEW.params[2];
 454                         IF NEW.params[3] <> '''' THEN
 455                                 new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER);
 456                                 IF new_cost <= 0 THEN
 457                                         RAISE EXCEPTION ''Amount must be positive'';
 458                                 END IF;
 459                                 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
 460                         END IF;
 461                         IF NEW.params[4] <> '''' THEN
 462                                 new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER);
 463                                 IF new_stock <= -1 THEN
 464                                         RAISE EXCEPTION ''Stock count must be -1 or >= 0'';
 465                                 END IF;
 466                                 UPDATE items SET item_stock = new_stock WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
 467                         END IF;
 468                         UPDATE items SET item_name = new_name WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1];
 469                 END IF;
 470
 471                 -- The default handler will have checked that we have stock and
 472                 -- deducted money et al.
 473                 --
 474                 -- So if we get this far, we don''t need to care about much.
 475                 -- Dropping the drink & checking stock occurs in the AFTER-INSERT trigger.
 476
 477                 -- Mark the request as having been dealt with
 478                 NEW.handled = true;
 479                 RETURN NEW;
 480         END;
 481         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 482 REVOKE ALL ON FUNCTION coke_slot_trigger() FROM public;
 483 CREATE TRIGGER coke_slot_trigger BEFORE INSERT ON requests FOR EACH ROW
 484         EXECUTE PROCEDURE coke_slot_trigger();
 485
 486 CREATE OR REPLACE FUNCTION coke_slot_dispense() RETURNS "trigger" AS
 487         '
 488         DECLARE
 489                 slotnum integer;
 490                 stock integer;
 491         BEGIN
 492                 IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN
 493                         -- Drop a drink and update stock
 494                         SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''coke'';
 495                         PERFORM dispense_slot(NEW.request_id, slotnum);
 496                 END IF;
 497                 RETURN NULL;
 498         END;
 499         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 500 REVOKE ALL ON FUNCTION coke_slot_dispense() FROM public;
 501 CREATE TRIGGER coke_slot_dispense AFTER INSERT ON requests FOR EACH ROW
 502         EXECUTE PROCEDURE coke_slot_dispense();
 503
 504 -- Don't show the update function to non-coke people
 505 DROP FUNCTION can_show_pre_coke(varchar, varchar);
 506 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_coke;
 507 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
 508         'SELECT can_show_pre_coke($1, $2) AND NOT ($2 = ''update_coke'' AND NOT in_coke($1))'
 509         LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 510 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
 511
 512 ------------------------------------------------------------------------------
 513 --- Vending Machine Services                                               ---
 514 ------------------------------------------------------------------------------
 515
 516 INSERT INTO sources VALUES ('vend');
 517
 518 INSERT INTO items VALUES ('vend', 'twisties',                   120, array['11'], NULL, 8);
 519 INSERT INTO items VALUES ('vend', 'cheese and onion',   125, array['21'], NULL, 0);
 520 INSERT INTO items VALUES ('vend', 'update_vend', NULL, NULL, '<slot number> <new name> <new price> <new count>', NULL);
 521
 522 CREATE TABLE vend_requests (
 523         request_id integer,
 524     request_slot varchar(2),
 525         request_handled boolean NOT NULL DEFAULT false,
 526         request_time timestamp DEFAULT now()
 527 );
 528
 529 CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS
 530         'SELECT $1 ~ ''^[0-9]{2}$''' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 531 REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public;
 532
 533 CREATE OR REPLACE FUNCTION update_vend_slot_info(varchar(2), varchar(20), integer, integer) RETURNS void AS
 534         '
 535         BEGIN
 536                 -- Check such a slot exists
 537                 IF NOT valid_vend_slot($1) THEN
 538                         RAISE EXCEPTION ''There is no such slot %'', $1;
 539                 END IF;
 540                 IF $2 IS NOT NULL THEN
 541                         UPDATE items SET item_name = $2 WHERE item_data[1] = $1 and source_id = ''vend'';
 542                 END IF;
 543                 IF $3 IS NOT NULL THEN
 544                         UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = $1 and source_id = ''vend'';
 545                 END IF;
 546                 IF $4 IS NOT NULL THEN
 547                         UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend'';
 548                 END IF;
 549         END;
 550         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 551 REVOKE ALL ON FUNCTION update_vend_slot_info(varchar(2), varchar, integer, integer) FROM public;
 552
 553 CREATE OR REPLACE FUNCTION vend_slot(integer, varchar(2)) RETURNS void AS
 554         '
 555         elog(NOTICE, q(And out pops a snack!));
 556         INSERT INTO vend_requests VALUES ($1, $2);
 557         NOTIFY vend_requests;
 558         ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
 559 REVOKE ALL ON FUNCTION vend_slot(integer, varchar(2)) FROM public;
 560
 561 CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS
 562         '
 563         DECLARE
 564                 new_name VARCHAR(20);
 565                 new_cost INTEGER;
 566                 new_stock INTEGER;
 567         BEGIN
 568                 -- Check if we are to handle this request
 569                 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN
 570                         RETURN NEW;
 571                 END IF;
 572
 573                 -- Check if we are wanted to update a slot
 574                 IF NEW.item_name = ''update_vend'' THEN
 575                         -- Check parameters
 576                         IF NEW.params IS NULL THEN
 577                                 RAISE EXCEPTION ''Missing parameters for update_vend'';
 578                         END IF;
 579                         IF array_upper(NEW.params, 1) <> 4 THEN
 580                                 RAISE EXCEPTION ''Invalid number of parameters for update_vend'';
 581                         END IF;
 582                         IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''vend'' and item_data[1] IS NOT NULL) THEN
 583                                 new_name = NEW.params[1];
 584                                 RAISE EXCEPTION ''No idea what slot % is.'', new_name;
 585                         END IF;
 586                         new_name = NEW.params[2];
 587                         IF NEW.params[3] <> '''' THEN
 588                                 new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER);
 589                                 IF new_cost <= 0 THEN
 590                                         RAISE EXCEPTION ''Amount must be positive'';
 591                                 END IF;
 592                                 UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
 593                         END IF;
 594                         IF NEW.params[4] <> '''' THEN
 595                                 new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER);
 596                                 IF new_stock <= -1 THEN
 597                                         RAISE EXCEPTION ''Stock count must be -1 or >= 0'';
 598                                 END IF;
 599                                 UPDATE items SET item_stock = new_stock WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
 600                         END IF;
 601                         UPDATE items SET item_name = new_name WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1];
 602                 END IF;
 603
 604                 -- The default handler will have checked that we have stock and
 605                 -- deducted money et al.
 606                 --
 607                 -- So if we get this far, we don''t need to care about much.
 608                 -- Dropping the snack occurs in the AFTER-INSERT trigger.
 609
 610                 -- Mark the request as having been dealt with
 611                 NEW.handled = true;
 612                 RETURN NEW;
 613         END;
 614         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 615 REVOKE ALL ON FUNCTION vend_slot_trigger() FROM public;
 616 CREATE TRIGGER vend_slot_trigger BEFORE INSERT ON requests FOR EACH ROW
 617         EXECUTE PROCEDURE vend_slot_trigger();
 618
 619 CREATE OR REPLACE FUNCTION vend_slot_dispense() RETURNS "trigger" AS
 620         '
 621         DECLARE
 622                 slotnum varchar(2);
 623         BEGIN
 624                 IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN
 625                         -- Drop a snack and update stock
 626                         SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''vend'';
 627                         PERFORM vend_slot(NEW.request_id, slotnum);
 628                 END IF;
 629                 RETURN NULL;
 630         END;
 631         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 632 REVOKE ALL ON FUNCTION vend_slot_dispense() FROM public;
 633 CREATE TRIGGER vend_slot_dispense AFTER INSERT ON requests FOR EACH ROW
 634         EXECUTE PROCEDURE vend_slot_dispense();
 635
 636 -- Don't show the update function to non-coke people
 637 DROP FUNCTION can_show_pre_vend(varchar, varchar);
 638 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_vend;
 639 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
 640         'SELECT can_show_pre_vend($1, $2) AND NOT ($2 = ''update_vend'' AND NOT in_coke($1))'
 641         LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 642 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
 643
 644 ------------------------------------------------------------------------------
 645 --- UCC Door Services                                                      ---
 646 ------------------------------------------------------------------------------
 647
 648 CREATE TABLE doorcontrollers (
 649     user_name varchar(16) UNIQUE references users(user_name),
 650         reason varchar(250)
 651 );
 652
 653 CREATE OR REPLACE FUNCTION in_door(varchar(16)) RETURNS boolean
 654     AS 'SELECT EXISTS(SELECT user_name FROM doorcontrollers WHERE user_name = $1)'
 655     LANGUAGE sql EXTERNAL SECURITY DEFINER;
 656 REVOKE ALL ON FUNCTION in_door(varchar(16)) FROM public;
 657
 658 INSERT INTO sources VALUES ('door');
 659
 660 INSERT INTO items VALUES ('door', 'opendoor', NULL, NULL, NULL, NULL);
 661
 662 CREATE OR REPLACE FUNCTION open_door() RETURNS void AS
 663         '
 664         system("/usr/bin/sudo /usr/sbin/opendoor");
 665         ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER;
 666 REVOKE ALL ON FUNCTION open_door() FROM public;
 667
 668 CREATE OR REPLACE FUNCTION door_open_trigger() RETURNS "trigger" AS
 669         '
 670         BEGIN
 671                 -- Check if we are to handle this request
 672                 IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''door'' AND item_name = NEW.item_name) THEN
 673                         RETURN NEW;
 674                 END IF;
 675
 676                 -- You can''t open the door for somebody else
 677                 IF NEW.request_requestor_name <> NEW.request_target_name THEN
 678                         RAISE EXCEPTION ''You can''''t open the door for somebody else.'';
 679                 END IF;
 680
 681                 -- Ensure the user is in the door group
 682                 IF NOT in_door(NEW.request_requestor_name) THEN
 683                         RAISE EXCEPTION ''You are not permitted to open the door.'';
 684                 END IF;
 685
 686                 -- Mark the request as having been dealt with
 687                 NEW.handled = true;
 688                 RETURN NEW;
 689         END;
 690         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 691 REVOKE ALL ON FUNCTION door_open_trigger() FROM public;
 692 CREATE TRIGGER door_open_trigger BEFORE INSERT ON requests FOR EACH ROW
 693         EXECUTE PROCEDURE door_open_trigger();
 694
 695 CREATE OR REPLACE FUNCTION door_open_do() RETURNS "trigger" AS
 696         '
 697         BEGIN
 698                 IF NEW.handled AND NEW.item_name = ''opendoor'' THEN
 699                         PERFORM open_door();
 700                 END IF;
 701                 RETURN NULL;
 702         END;
 703         ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER;
 704 REVOKE ALL ON FUNCTION door_open_do() FROM public;
 705 CREATE TRIGGER door_open_do AFTER INSERT ON requests FOR EACH ROW
 706         EXECUTE PROCEDURE door_open_do();
 707
 708 -- Don't show the door functions to non-door people
 709 DROP FUNCTION can_show_pre_door(varchar, varchar);
 710 ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_door;
 711 CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS
 712         'SELECT can_show_pre_door($1, $2) AND NOT ($2 = ''opendoor'' AND (NOT in_door($1)))' LANGUAGE SQL EXTERNAL SECURITY DEFINER;
 713 REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public;
 714