From e423f11f190d685156cb557319b169d2b79b752f Mon Sep 17 00:00:00 2001 From: Mark Tearle Date: Sat, 8 Nov 2014 21:58:11 +0800 Subject: [PATCH] Remove remaining bits of Bernard's postgres dispense --- sql-edition/api/API.txt | 141 ------ sql-edition/clients/dispense.py | 48 --- sql-edition/convert.c | 31 -- sql-edition/ident/Makefile | 11 - sql-edition/ident/pg_ident.c | 39 -- sql-edition/schema/d.sql | 744 -------------------------------- sql-edition/schema/populate.sql | 50 --- sql-edition/syslog/Makefile | 11 - sql-edition/syslog/pg_syslog.c | 28 -- 9 files changed, 1103 deletions(-) delete mode 100644 sql-edition/api/API.txt delete mode 100755 sql-edition/clients/dispense.py delete mode 100644 sql-edition/convert.c delete mode 100644 sql-edition/ident/Makefile delete mode 100644 sql-edition/ident/pg_ident.c delete mode 100644 sql-edition/schema/d.sql delete mode 100644 sql-edition/schema/populate.sql delete mode 100644 sql-edition/syslog/Makefile delete mode 100644 sql-edition/syslog/pg_syslog.c diff --git a/sql-edition/api/API.txt b/sql-edition/api/API.txt deleted file mode 100644 index e3ab07f..0000000 --- a/sql-edition/api/API.txt +++ /dev/null @@ -1,141 +0,0 @@ - API for the new dispense -------------------------- - -New dispense is written in entirely in postgresql. The server does all the -required processing, meaning that database integrity is always maintained. - -The current interface for dispense is using a postgresql client library. -Client libraries exist for many languages including C, C++, Perl, Python, -Ruby, PHP, or you could also speak with the supplied client 'psql' through -stdin/stdout. - -The details for connecting to the database are: - hostname: dispense.ucc.gu.uwa.edu.au - username: anondispense - database: dispense - -No password is required. You will only be able to connect from 'trusted' -machines - currently this is mussel, mermaid, morwong. - -Note the API still isn't stable - it'd be worth running these commands by hand -to see what the resulting tables look like. - -The useful things can be done once connected: - - - SELECT * FROM get_services(username) - will give you a list of services available to the given user, including - syntax if parameters are required - - SELECT get_balance() - will return a table of all users and their balances. - - SELECT get_balance(username) - will return a table with one row for the given user, displaying their - account balance. - - SELECT do_request(target_user, service_name, parameters) - will perform a request. - * target_user is the user you wish to perform the request upon. - You will only be permitted to act on somebody else if you are in the - coke group. - * service_name is the name of the service as seen in the services - table. - * parameters is an array of parameters for the service, if required. - These should appear in the order as item_syntax from the services - table. - -Example follows: - -$ psql dispense -U anondispense -h dispense.ucc.gu.uwa.edu.au -\Welcome to psql 7.4.1, the PostgreSQL interactive terminal. - -Type: \copyright for distribution terms - \h for help with SQL commands - \? for help on internal slash commands - \g or terminate with semicolon to execute query - \q to quit - --- Equivalent of bringing up the menu. Only items allowed for --- the given user are shown. -dispense=> select * from get_services('dagobah'); - item_name | item_cost_cents | item_syntax | item_stock --------------+-----------------+--------------------------------------+------------ - nothing | | | - give | | | - acct | | | - passion pop | 80 | | 8 - vb | 80 | | 0 - emu export | 80 | | 3 - champagne | 80 | | 9 - coke powder | 80 | | 11 - update_coke | | | - opendoor | | | - grape juice | 80 | | 0 - some foo | 77 | | 3 -(12 rows) - --- Get balance for a user. --- Equivalent of 'dispense acct dagobah' --- Calling just get_balance() will return all users balances. -dispense=> select * from get_balance('dagobah'); - user_name | user_balance_cents | user_balance_bytes ------------+--------------------+-------------------- - dagobah | -5080 | 0 -(1 row) - --- Dispense something for a user. --- Equivalent of 'dispense -u dagobah grape juice' -dispense=> select do_request('dagobah', 'grape juice', NULL); -ERROR: You may not dispense below -$20.00. -CONTEXT: PL/pgSQL function "do_request" line 12 at SQL statement - --- Adjust somebody's account balance up or down. --- Equivalent of 'dispense acct dagobah +10000 BAG123' -dispense=> select do_request('dagobah', 'acct', array['+10000', 'BAG123']); - do_request ------------- - t -(1 row) - --- Get the balance once again ... note that it's higher :) -dispense=> select * from get_balance('dagobah'); - user_name | user_balance_cents | user_balance_bytes ------------+--------------------+-------------------- - dagobah | 4920 | 0 -(1 row) - --- Try that dispense again, but we see there is none -dispense=> select do_request('dagobah', 'grape juice', NULL); -ERROR: We are out of stock of that item. -CONTEXT: PL/pgSQL function "do_request" line 12 at SQL statement - --- Try that dispense again, but we see there is none -dispense=> select do_request('dagobah', 'some foo', NULL); - do_request ------------- - t -(1 row) - --- Updating a slot name/price on the coke machine -dispense=> select do_request('dagobah', 'update_coke', array['3', 'orange blah', '88']); - do_request ------------- - t -(1 row) - --- Note the services have changed -dispense=> select * from get_services('dagobah'); - item_name | item_cost_cents | item_syntax | item_stock --------------+-----------------+--------------------------------------+------------ - nothing | | | - give | | | - acct | | | - passion pop | 80 | | 8 - vb | 80 | | 0 - emu export | 80 | | 3 - champagne | 80 | | 9 - coke powder | 80 | | 11 - update_coke | | | - opendoor | | | - grape juice | 80 | | 0 - orange blah | 88 | | 3 -(12 rows) - -Email comments or queries to diff --git a/sql-edition/clients/dispense.py b/sql-edition/clients/dispense.py deleted file mode 100755 index 00310b5..0000000 --- a/sql-edition/clients/dispense.py +++ /dev/null @@ -1,48 +0,0 @@ -#!/usr/bin/python - -import sys, os, re -from pyPgSQL import PgSQL - -if __name__ == '__main__': - db = PgSQL.connect(user = 'anondispense', host = 'dispense', database = 'dispense') - cursor = db.cursor() - user = os.environ['USER'] - - if len(sys.argv) > 1: - # We have a request. Pass it to dispense by calling - # SELECT do_request(username, service, params as array) - service = sys.argv[1] - params = sys.argv[2:] - if params != []: - paramstr = 'array[' - for i in params: - paramstr += '%s,' - paramstr = paramstr.rstrip(',') + ']' - else: - paramstr = 'NULL' - cursor.execute('SELECT do_request(%%s, %%s, %s)'%paramstr, [user, service]+params) - result = cursor.fetchone()[0] - if result == True: - print 'All good' - else: - print 'Eeeep!' - else: - cursor.execute('SELECT * FROM get_services(%s)', user) - print '%-10s %-50s %-10s %s' % ('Provider', 'Item', 'Cost', 'Stock') - print '-------------------------------------------------------------------------------' - for item in cursor.fetchall(): - print '%10s '%item[0], - if item[3] is None: - item[3] = '' - print '%-50s'%(item[1]+' '+item[3]), - if item[2] is None: - print '%-10s' % '', - else: - print '%-10d' % int(item[2]), - if item[4] is None: - item[4] = 'N/A' - print '%s'%item[4] - cursor.execute('SELECT * FROM get_balance(%s)', user); - [_, cents,bytes] = cursor.fetchone() - print user, 'has', int(cents), 'cents and', int(bytes), 'bytes' - db.commit() diff --git a/sql-edition/convert.c b/sql-edition/convert.c deleted file mode 100644 index bac3b86..0000000 --- a/sql-edition/convert.c +++ /dev/null @@ -1,31 +0,0 @@ -/* Prints a list of all usernames with useable coke balances - * delimited by newlines. - * - * - Bernard Blackham - */ - - -#include -#include "ucc.h" - -int main(int argc, char* argv[]) { - char username[30]; - int32 balance; - - if (argc != 1) { - fprintf(stderr, "Usage: %s\n", argv[0]); - return 1; - } - - SetCokebankToSocks(); - - cokebank_open(); - printf("DELETE from users;\n"); - printf("COPY users (user_name, user_balance_cents, user_balance_bytes) FROM stdin;\n"); - while (cokebank_get_next(username, &balance, 0)) { - printf("%s\t%d\t%d\n", username, cokebank_get(username), cokebank_get_bytes(username)); - } - printf("\\.\n"); - cokebank_close(); - return 0; -} diff --git a/sql-edition/ident/Makefile b/sql-edition/ident/Makefile deleted file mode 100644 index 6bde0e0..0000000 --- a/sql-edition/ident/Makefile +++ /dev/null @@ -1,11 +0,0 @@ -CC = gcc -INCLUDES = -I$(shell pg_config --includedir) -I$(shell pg_config --includedir-server) -CFLAGS = $(INCLUDES) -Wall -O2 - -all: pg_ident.so - -pg_ident.so: pg_ident.o - gcc -shared -lident -o $@ $< - -clean: - rm -f *.o *.so diff --git a/sql-edition/ident/pg_ident.c b/sql-edition/ident/pg_ident.c deleted file mode 100644 index 2de2121..0000000 --- a/sql-edition/ident/pg_ident.c +++ /dev/null @@ -1,39 +0,0 @@ -#include - -#include -#include -#include -#include - -char *ident_result = NULL; - -PG_FUNCTION_INFO_V1(get_ident); -Datum get_ident(PG_FUNCTION_ARGS) { - IDENT *id; - - if (MyProcPort == NULL) - elog(ERROR, "could not get ident for user: no port"); - - if (!ident_result) { - id = ident_lookup(MyProcPort->sock, 5); - if (id && id->identifier) ident_result = strdup(id->identifier); - } - - if (ident_result) { - int len; - VarChar* result; - - len = strlen(ident_result); - if (len > 8) len = 8; - result = palloc(len + VARHDRSZ); - VARATT_SIZEP(result) = len + VARHDRSZ; - memcpy(VARDATA(result), ident_result, len); - - PG_RETURN_VARCHAR_P(result); - } - - elog(ERROR, "could not get ident for user: ident failed"); - - /* not reached */ - PG_RETURN_NULL(); -} diff --git a/sql-edition/schema/d.sql b/sql-edition/schema/d.sql deleted file mode 100644 index 6ef9f57..0000000 --- a/sql-edition/schema/d.sql +++ /dev/null @@ -1,744 +0,0 @@ ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ---- --- ---- DISPENSE IN POSTGRES REWRITE --- ---- Bernard Blackham --- ---- --- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- - -/* - -All the operations are done -*/ - -\connect 'dispense'; - -DROP TABLE requests; -DROP SEQUENCE request_id_seq; -DROP FUNCTION get_services(varchar); -DROP VIEW services; -DROP TABLE items; -DROP TABLE sources; -DROP TABLE cokecontrollers; -DROP TABLE doorcontrollers; -DROP FUNCTION get_balance(); -DROP FUNCTION get_balance(varchar); -DROP TABLE users; - -CREATE OR REPLACE FUNCTION get_ident() RETURNS varchar AS 'pg_ident.so', 'get_ident' STRICT LANGUAGE C; -REVOKE ALL ON FUNCTION get_ident() FROM public; - -CREATE TABLE users ( - user_name varchar(16) NOT NULL UNIQUE PRIMARY KEY, - user_balance_cents integer NOT NULL, - user_balance_bytes integer NOT NULL -); - -CREATE TABLE cokecontrollers ( - user_name varchar(16) UNIQUE references users(user_name), - reason varchar(250) -); - -CREATE TABLE sources ( - source_name varchar(20) NOT NULL UNIQUE PRIMARY KEY -); - -CREATE TABLE items ( - source_name varchar(20) REFERENCES sources(source_name), - item_name varchar(20) NOT NULL UNIQUE PRIMARY KEY, - item_cost_cents integer, - item_data varchar[], - item_syntax varchar(80), - item_stock integer -- -1 for "in stock", 0 for "out of stock", > 0 for a value of stock, NULL for N/A -); - -CREATE VIEW services AS - SELECT i.source_name as provider, - i.item_name, - i.item_cost_cents, - i.item_syntax, - i.item_stock, - i.item_data - FROM items i -; - -CREATE SEQUENCE request_id_seq START 1; -CREATE TABLE requests ( - request_id integer NOT NULL DEFAULT nextval('request_id_seq'), - request_requestor_name varchar(16) REFERENCES users(user_name), - request_target_name varchar(16) REFERENCES users(user_name), - item_name varchar(20), - params varchar[], - handled boolean NOT NULL DEFAULT false -); - --- request(target username, item_name, params) -CREATE OR REPLACE FUNCTION do_request(varchar(16), varchar(20), varchar[]) RETURNS boolean AS - ' - DECLARE - ident VARCHAR; - BEGIN - ident = get_ident(); - IF ident IS NULL THEN - RAISE EXCEPTION ''Got a null ident! Eepe!''; - END IF; - IF NOT EXISTS(SELECT user_name FROM users WHERE user_name = $1) THEN - RAISE NOTICE ''Adding user %!!'', $1; - INSERT INTO users VALUES ($1, 0, 0); - END IF; - INSERT INTO requests VALUES (DEFAULT, ident, $1, $2, $3); - RETURN true; - END; - ' - LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION do_request(varchar(16),varchar(20),varchar[]) FROM public; -GRANT EXECUTE ON FUNCTION do_request(varchar(16), varchar(20), varchar[]) TO anondispense; - --- get_balance(target username) -CREATE OR REPLACE FUNCTION get_balance(varchar(16)) RETURNS users - AS 'SELECT * FROM users WHERE user_name = $1' - LANGUAGE sql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION get_balance(varchar(16)) FROM public; -GRANT EXECUTE ON FUNCTION get_balance(varchar(16)) TO anondispense; - --- get_balance() ... returns everybody -CREATE OR REPLACE FUNCTION get_balance() RETURNS SETOF users - AS 'SELECT * FROM users' - LANGUAGE sql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION get_balance() FROM public; -GRANT EXECUTE ON FUNCTION get_balance() TO anondispense; - --- internal functions follow - -CREATE OR REPLACE FUNCTION refund(integer) RETURNS void AS --- FIXME XXX DO this. - ' - ' - LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION refund(integer) FROM public; - -CREATE OR REPLACE FUNCTION in_coke(varchar(16)) RETURNS boolean - AS 'SELECT EXISTS(SELECT * FROM cokecontrollers WHERE user_name = $1)' - LANGUAGE sql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION in_coke(varchar(16)) FROM public; - -CREATE OR REPLACE FUNCTION byte_to_cent_trigger() RETURNS "trigger" AS - ' - DECLARE - bytes_per_cent INTEGER; - cents_per_meg INTEGER; - BEGIN - cents_per_meg = 4; - bytes_per_cent = 1024*1024/cents_per_meg; - IF NEW.user_balance_bytes > bytes_per_cent THEN - NEW.user_balance_cents = NEW.user_balance_cents - (NEW.user_balance_bytes/bytes_per_cent); - NEW.user_balance_bytes = NEW.user_balance_bytes % bytes_per_cent; - END IF; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION byte_to_cent_trigger() FROM public; -CREATE TRIGGER byte_to_cent_trigger BEFORE INSERT OR UPDATE ON users FOR EACH ROW - EXECUTE PROCEDURE byte_to_cent_trigger(); - -CREATE OR REPLACE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS - ' - DECLARE - ident varchar; - BEGIN - IF $1 NOT IN (SELECT user_name FROM users) THEN - RETURN false; - END IF; - ident = get_ident(); - IF ident = $1 OR in_coke(ident) THEN - RETURN true; - END IF; - RETURN false; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION can_show(varchar(16), varchar(20)) FROM public; -CREATE OR REPLACE FUNCTION get_services(varchar(16)) RETURNS SETOF services AS - 'SELECT * from services WHERE can_show($1, item_name)' LANGUAGE SQL EXTERNAL SECURITY DEFINER; - -REVOKE ALL ON FUNCTION get_services(varchar(16)) FROM public; -GRANT EXECUTE ON FUNCTION get_services(varchar(16)) TO anondispense; - -CREATE OR REPLACE FUNCTION first_request_handler() RETURNS "trigger" AS - ' - DECLARE - cost INTEGER; - stock INTEGER; - balance INTEGER; - BEGIN - -- Sanity check that requestor isn''t acting on somebody else''s behalf - -- when they''re not in coke. - IF NEW.request_target_name <> NEW.request_requestor_name AND NOT in_coke(NEW.request_requestor_name) THEN - RAISE EXCEPTION ''You are not allowed to perform operations on behalf of somebody else''; - END IF; - - -- Sanity check that the item is in the items table - IF NOT EXISTS (SELECT item_name FROM items where item_name = NEW.item_name) THEN - RAISE EXCEPTION ''We do not stock anything like that.''; - END IF; - - -- If we have a balance for the item, ensure there is sufficient money - SELECT INTO cost items.item_cost_cents FROM items WHERE item_name = NEW.item_name; - - -- If the cost is null, it may vary and account keeping is done by the - -- relevant modules - IF cost IS NOT NULL THEN - SELECT INTO balance users.user_balance_cents FROM users WHERE user_name = NEW.request_target_name; - IF in_coke(NEW.request_requestor_name) THEN - IF balance - cost < -2000 THEN - -- They''re in coke, let them take balances to -$20.00 - RAISE EXCEPTION ''You may not dispense below -$20.00.''; - END IF; - ELSE - -- Else simply ensure there is enough money - IF cost > balance THEN - RAISE EXCEPTION ''You do not have sufficient funds to do that.''; - END IF; - END IF; - - -- Deduct money. This will be rolled back if any other triggers fail. - UPDATE users SET user_balance_cents = user_balance_cents - cost WHERE user_name = NEW.request_target_name; - END IF; - - -- Adjust stock levels as required - SELECT INTO stock item_stock FROM items WHERE item_name = NEW.item_name; - IF stock IS NOT NULL THEN - IF stock = 0 THEN - RAISE EXCEPTION ''We are out of stock of that item.''; - END IF; - IF stock > 0 THEN - UPDATE items SET item_stock = item_stock - 1 WHERE item_name = NEW.item_name; - END IF; - -- if item stock is -1, then stock levels are adjusted by other things - END IF; - - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION first_request_handler() FROM public; -CREATE TRIGGER a00_first_request_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE first_request_handler(); - -CREATE OR REPLACE FUNCTION final_request_handler() RETURNS "trigger" AS - ' - BEGIN - IF NEW.handled = false THEN - RAISE EXCEPTION ''Nothing wanted to service your request!''; - END IF; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION final_request_handler() FROM public; -CREATE TRIGGER zzz_last_request_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE final_request_handler(); - ------------------------------------------------------------------------------- ---- Dummy Services --- ------------------------------------------------------------------------------- - -INSERT INTO sources VALUES ('nothing'); - -INSERT INTO items VALUES ('nothing', 'nothing', NULL, NULL, NULL, NULL); -CREATE OR REPLACE FUNCTION nothing_nothing_trigger() RETURNS "trigger" AS - ' - DECLARE - BEGIN - -- Check if we are to handle this request - IF NEW.handled OR NEW.item_name <> ''nothing'' THEN - RETURN NEW; - END IF; - - -- Mark the request as having been dealt with - NEW.handled = true; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION nothing_nothing_trigger() FROM public; -CREATE TRIGGER nothing_nothing_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE nothing_nothing_trigger(); - -DROP FUNCTION can_show_pre_nothing(varchar, varchar); -ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_nothing; -CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS - 'SELECT can_show_pre_nothing($1, $2) OR $2 = ''nothing''' - LANGUAGE SQL EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; - ------------------------------------------------------------------------------- ---- Coke Bank Services --- ------------------------------------------------------------------------------- - -INSERT INTO sources VALUES ('cokebank'); - -INSERT INTO items VALUES ('cokebank', 'give', NULL, NULL, ' ', NULL); -CREATE OR REPLACE FUNCTION cokebank_give_trigger() RETURNS "trigger" AS - ' - DECLARE - give_amount INTEGER; - - source_user VARCHAR(8); - target_user VARCHAR(8); - source_user_balance INTEGER; - BEGIN - -- Check if we are to handle this request - IF NEW.handled OR NEW.item_name <> ''give'' THEN - RETURN NEW; - END IF; - - -- Check for our two parameters, username, amount - IF NEW.params IS NULL THEN - RAISE EXCEPTION ''Missing parameters for give''; - END IF; - IF array_upper(NEW.params, 1) <> 2 THEN - RAISE EXCEPTION ''Invalid number of parameters for give''; - END IF; - IF NEW.params[1] NOT IN (SELECT user_name FROM users) THEN - RAISE EXCEPTION ''Invalid user to give to''; - END IF; - give_amount = CAST(CAST(NEW.params[2] AS TEXT) AS INTEGER); - source_user = NEW.request_target_name; - target_user = NEW.params[1]; - - -- Can''t give to one''s self - IF source_user = target_user THEN - RAISE EXCEPTION ''You can''''t give to one''''s self''; - END IF; - -- Can''t give negative amounts - IF give_amount <= 0 THEN - RAISE EXCEPTION ''You can only give positive balances''; - END IF; - - -- Check the appropriate balances - SELECT INTO source_user_balance users.user_balance_cents FROM users WHERE user_name = source_user; - - IF source_user_balance < give_amount AND not in_coke(NEW.request_requestor_name) THEN - RAISE EXCEPTION ''You do not have sufficient funds to give that much!''; - END IF; - - -- Perform the request - UPDATE users SET user_balance_cents = user_balance_cents - give_amount WHERE user_name = source_user; - UPDATE users SET user_balance_cents = user_balance_cents + give_amount WHERE user_name = target_user; - -- Mark the request as having been dealt with - NEW.handled = true; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION cokebank_give_trigger() FROM public; -CREATE TRIGGER cokebank_give_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE cokebank_give_trigger(); - -INSERT INTO items VALUES ('cokebank', 'acct', NULL, NULL, ' ', NULL); -CREATE OR REPLACE FUNCTION cokebank_acct_trigger() RETURNS "trigger" AS - ' - DECLARE - amount INTEGER; - target_user VARCHAR(8); - reason VARCHAR; - user_bal RECORD; - BEGIN - -- Check if we are to handle this request - IF NEW.handled OR NEW.item_name <> ''acct'' THEN - RETURN NEW; - END IF; - - -- Check requestor is in coke - IF NOT in_coke(NEW.request_requestor_name) THEN - RAISE EXCEPTION ''You need to be in the coke group to use acct''; - END IF; - - -- Check for our two parameters, amount and reason - IF NEW.params IS NULL THEN - RAISE EXCEPTION ''No parameters supplied to acct''; - END IF; - IF array_upper(NEW.params, 1) <> 2 THEN - RAISE EXCEPTION ''Invalid number of parameters for acct''; - END IF; - amount = CAST(CAST(NEW.params[1] AS TEXT) AS INTEGER); - reason = NEW.params[2]; - target_user = NEW.request_target_name; - - -- Perform the request - UPDATE users SET user_balance_cents = user_balance_cents + amount WHERE user_name = target_user; - -- Mark the request as having been dealt with - NEW.handled = true; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION cokebank_acct_trigger() FROM public; -CREATE TRIGGER cokebank_acct_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE cokebank_acct_trigger(); - --- Don't show the acct function to non-coke people -DROP FUNCTION can_show_pre_cokebank(varchar, varchar); -ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_cokebank; -CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS - 'SELECT can_show_pre_cokebank($1, $2) AND NOT ($2 = ''acct'' AND NOT in_coke($1))' - LANGUAGE SQL EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; - ------------------------------------------------------------------------------- ---- Coke Machine Services --- ------------------------------------------------------------------------------- - -INSERT INTO sources VALUES ('coke'); - -INSERT INTO items VALUES ('coke', 'passion pop', 80, array['0'], NULL, 8); -INSERT INTO items VALUES ('coke', 'vb', 80, array['1'], NULL, 0); -INSERT INTO items VALUES ('coke', 'emu export', 80, array['2'], NULL, 3); -INSERT INTO items VALUES ('coke', 'whiskey', 80, array['3'], NULL, 4); -INSERT INTO items VALUES ('coke', 'champagne', 80, array['4'], NULL, 9); -INSERT INTO items VALUES ('coke', 'grape juice', 80, array['5'], NULL, 1); -INSERT INTO items VALUES ('coke', 'coke powder', 80, array['6'], NULL, 11); - -INSERT INTO items VALUES ('coke', 'update_coke', NULL, NULL, ' ', NULL); - -DROP TABLE coke_requests; -CREATE TABLE coke_requests ( - request_id integer, - request_slot integer, - request_handled boolean NOT NULL DEFAULT false, - request_time timestamp DEFAULT now() -); - -CREATE OR REPLACE FUNCTION update_slot_info(integer, varchar(20), integer, integer) RETURNS void AS - ' - BEGIN - -- Check such a slot exists - IF NOT EXISTS(SELECT item_name FROM items WHERE item_data[1] = cast($1 as varchar) AND source_id = ''coke'') THEN - RAISE EXCEPTION ''There is no such slot %'', $1; - END IF; - IF $2 IS NOT NULL THEN - UPDATE items SET item_name = $2 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke''; - END IF; - IF $3 IS NOT NULL THEN - UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = cast($1 as varchar) and source_id = ''coke''; - END IF; - IF $4 IS NOT NULL THEN - UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend''; - END IF; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION update_slot_info(integer, varchar, integer, integer) FROM public; - -CREATE OR REPLACE FUNCTION dispense_slot(integer, integer) RETURNS void AS - ' - elog(NOTICE, q(And out pops a drink!)); - INSERT INTO coke_requests VALUES ($1, $2); - NOTIFY coke_requests; - ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION dispense_slot(integer, integer) FROM public; - -CREATE OR REPLACE FUNCTION coke_slot_trigger() RETURNS "trigger" AS - ' - DECLARE - new_name VARCHAR(20); - new_cost INTEGER; - new_stock INTEGER; - BEGIN - -- Check if we are to handle this request - IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN - RETURN NEW; - END IF; - - -- Check if we are wanted to update a slot - IF NEW.item_name = ''update_coke'' THEN - -- Check parameters - IF NEW.params IS NULL THEN - RAISE EXCEPTION ''Missing parameters for update_coke''; - END IF; - IF array_upper(NEW.params, 1) <> 4 THEN - RAISE EXCEPTION ''Invalid number of parameters for update_coke''; - END IF; - IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''coke'' and item_data[1] IS NOT NULL) THEN - new_name = NEW.params[1]; - RAISE EXCEPTION ''No idea what slot % is.'', new_name; - END IF; - new_name = NEW.params[2]; - IF NEW.params[3] <> '''' THEN - new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER); - IF new_cost <= 0 THEN - RAISE EXCEPTION ''Amount must be positive''; - END IF; - UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1]; - END IF; - IF NEW.params[4] <> '''' THEN - new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER); - IF new_stock <= -1 THEN - RAISE EXCEPTION ''Stock count must be -1 or >= 0''; - END IF; - UPDATE items SET item_stock = new_stock WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1]; - END IF; - UPDATE items SET item_name = new_name WHERE source_name = ''coke'' AND item_data[1] = NEW.params[1]; - END IF; - - -- The default handler will have checked that we have stock and - -- deducted money et al. - -- - -- So if we get this far, we don''t need to care about much. - -- Dropping the drink & checking stock occurs in the AFTER-INSERT trigger. - - -- Mark the request as having been dealt with - NEW.handled = true; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION coke_slot_trigger() FROM public; -CREATE TRIGGER coke_slot_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE coke_slot_trigger(); - -CREATE OR REPLACE FUNCTION coke_slot_dispense() RETURNS "trigger" AS - ' - DECLARE - slotnum integer; - stock integer; - BEGIN - IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''coke'' AND item_name = NEW.item_name) THEN - -- Drop a drink and update stock - SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''coke''; - PERFORM dispense_slot(NEW.request_id, slotnum); - END IF; - RETURN NULL; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION coke_slot_dispense() FROM public; -CREATE TRIGGER coke_slot_dispense AFTER INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE coke_slot_dispense(); - --- Don't show the update function to non-coke people -DROP FUNCTION can_show_pre_coke(varchar, varchar); -ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_coke; -CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS - 'SELECT can_show_pre_coke($1, $2) AND NOT ($2 = ''update_coke'' AND NOT in_coke($1))' - LANGUAGE SQL EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; - ------------------------------------------------------------------------------- ---- Vending Machine Services --- ------------------------------------------------------------------------------- - -INSERT INTO sources VALUES ('vend'); - -INSERT INTO items VALUES ('vend', 'twisties', 120, array['11'], NULL, 8); -INSERT INTO items VALUES ('vend', 'cheese and onion', 125, array['21'], NULL, 0); -INSERT INTO items VALUES ('vend', 'update_vend', NULL, NULL, ' ', NULL); - -DROP TABLE vend_requests; -CREATE TABLE vend_requests ( - request_id integer, - request_slot varchar(2), - request_handled boolean NOT NULL DEFAULT false, - request_time timestamp DEFAULT now() -); -GRANT SELECT ON vend_requests TO vendserver; - -CREATE OR REPLACE FUNCTION vend_success(integer) RETURNS void AS - 'UPDATE vend_requests SET request_handled = true WHERE request_id = $1' - LANGUAGE SQL EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public; -GRANT EXECUTE ON FUNCTION vend_success(integer) TO vendserver; - -CREATE OR REPLACE FUNCTION vend_failed(integer) RETURNS void AS - ' - BEGIN - UPDATE vend_requests SET request_handled = true WHERE request_id = $1; - PERFORM refund($1); - END; - ' - LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public; -GRANT EXECUTE ON FUNCTION vend_failed(integer) TO vendserver; - -CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS - 'SELECT $1 ~ ''^[0-9]{2}$''' LANGUAGE SQL EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION valid_vend_slot(varchar(2)) FROM public; - -CREATE OR REPLACE FUNCTION update_vend_slot_info(varchar(2), varchar(20), integer, integer) RETURNS void AS - ' - BEGIN - -- Check such a slot exists - IF NOT valid_vend_slot($1) THEN - RAISE EXCEPTION ''There is no such slot %'', $1; - END IF; - IF $2 IS NOT NULL THEN - UPDATE items SET item_name = $2 WHERE item_data[1] = $1 and source_id = ''vend''; - END IF; - IF $3 IS NOT NULL THEN - UPDATE items SET item_cost_cents = $3 WHERE item_data[1] = $1 and source_id = ''vend''; - END IF; - IF $4 IS NOT NULL THEN - UPDATE items SET item_stock = $4 WHERE item_data[1] = $1 and source_id = ''vend''; - END IF; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION update_vend_slot_info(varchar(2), varchar, integer, integer) FROM public; - -CREATE OR REPLACE FUNCTION vend_slot(integer, varchar(2)) RETURNS void AS - ' - BEGIN - RAISE NOTICE ''And out pops a snack''; - INSERT INTO vend_requests (request_id, request_slot) VALUES ($1, $2); - NOTIFY vend_requests; - RETURN; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION vend_slot(integer, varchar(2)) FROM public; - -CREATE OR REPLACE FUNCTION vend_slot_trigger() RETURNS "trigger" AS - ' - DECLARE - new_name VARCHAR(20); - new_cost INTEGER; - new_stock INTEGER; - BEGIN - -- Check if we are to handle this request - IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN - RETURN NEW; - END IF; - - -- Check if we are wanted to update a slot - IF NEW.item_name = ''update_vend'' THEN - -- Check parameters - IF NEW.params IS NULL THEN - RAISE EXCEPTION ''Missing parameters for update_vend''; - END IF; - IF array_upper(NEW.params, 1) <> 4 THEN - RAISE EXCEPTION ''Invalid number of parameters for update_vend''; - END IF; - IF NEW.params[1] NOT IN (SELECT item_data[1] FROM items WHERE source_name = ''vend'' and item_data[1] IS NOT NULL) THEN - new_name = NEW.params[1]; - RAISE EXCEPTION ''No idea what slot % is.'', new_name; - END IF; - new_name = NEW.params[2]; - IF NEW.params[3] <> '''' THEN - new_cost = CAST(CAST(NEW.params[3] AS TEXT) AS INTEGER); - IF new_cost <= 0 THEN - RAISE EXCEPTION ''Amount must be positive''; - END IF; - UPDATE items SET item_cost_cents = new_cost WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1]; - END IF; - IF NEW.params[4] <> '''' THEN - new_stock = CAST(CAST(NEW.params[4] AS TEXT) AS INTEGER); - IF new_stock <= -1 THEN - RAISE EXCEPTION ''Stock count must be -1 or >= 0''; - END IF; - UPDATE items SET item_stock = new_stock WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1]; - END IF; - UPDATE items SET item_name = new_name WHERE source_name = ''vend'' AND item_data[1] = NEW.params[1]; - END IF; - - -- The default handler will have checked that we have stock and - -- deducted money et al. - -- - -- So if we get this far, we don''t need to care about much. - -- Dropping the snack occurs in the AFTER-INSERT trigger. - - -- Mark the request as having been dealt with - NEW.handled = true; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION vend_slot_trigger() FROM public; -CREATE TRIGGER vend_slot_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE vend_slot_trigger(); - -CREATE OR REPLACE FUNCTION vend_slot_dispense() RETURNS "trigger" AS - ' - DECLARE - slotnum varchar(2); - BEGIN - IF NEW.handled AND EXISTS(SELECT item_name FROM items WHERE source_name = ''vend'' AND item_name = NEW.item_name) THEN - -- Drop a snack and update stock - SELECT INTO slotnum items.item_data[1] FROM items WHERE item_name = NEW.item_name and source_name = ''vend''; - PERFORM vend_slot(NEW.request_id, slotnum); - END IF; - RETURN NULL; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION vend_slot_dispense() FROM public; -CREATE TRIGGER vend_slot_dispense AFTER INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE vend_slot_dispense(); - --- Don't show the update function to non-coke people -DROP FUNCTION can_show_pre_vend(varchar, varchar); -ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_vend; -CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS - 'SELECT can_show_pre_vend($1, $2) AND NOT ($2 = ''update_vend'' AND NOT in_coke($1))' - LANGUAGE SQL EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; - ------------------------------------------------------------------------------- ---- UCC Door Services --- ------------------------------------------------------------------------------- - -CREATE TABLE doorcontrollers ( - user_name varchar(16) UNIQUE references users(user_name), - reason varchar(250) -); - -CREATE OR REPLACE FUNCTION in_door(varchar(16)) RETURNS boolean - AS 'SELECT EXISTS(SELECT user_name FROM doorcontrollers WHERE user_name = $1)' - LANGUAGE sql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION in_door(varchar(16)) FROM public; - -INSERT INTO sources VALUES ('door'); - -INSERT INTO items VALUES ('door', 'opendoor', NULL, NULL, NULL, NULL); - -CREATE OR REPLACE FUNCTION open_door() RETURNS void AS - ' - system("/usr/bin/sudo /usr/sbin/opendoor"); - ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION open_door() FROM public; - -CREATE OR REPLACE FUNCTION door_open_trigger() RETURNS "trigger" AS - ' - BEGIN - -- Check if we are to handle this request - IF NEW.handled OR NOT EXISTS(SELECT item_name FROM items WHERE source_name = ''door'' AND item_name = NEW.item_name) THEN - RETURN NEW; - END IF; - - -- You can''t open the door for somebody else - IF NEW.request_requestor_name <> NEW.request_target_name THEN - RAISE EXCEPTION ''You can''''t open the door for somebody else.''; - END IF; - - -- Ensure the user is in the door group - IF NOT in_door(NEW.request_requestor_name) THEN - RAISE EXCEPTION ''You are not permitted to open the door.''; - END IF; - - -- Mark the request as having been dealt with - NEW.handled = true; - RETURN NEW; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION door_open_trigger() FROM public; -CREATE TRIGGER door_open_trigger BEFORE INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE door_open_trigger(); - -CREATE OR REPLACE FUNCTION door_open_do() RETURNS "trigger" AS - ' - BEGIN - IF NEW.handled AND NEW.item_name = ''opendoor'' THEN - PERFORM open_door(); - END IF; - RETURN NULL; - END; - ' LANGUAGE plpgsql EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION door_open_do() FROM public; -CREATE TRIGGER door_open_do AFTER INSERT ON requests FOR EACH ROW - EXECUTE PROCEDURE door_open_do(); - --- Don't show the door functions to non-door people -DROP FUNCTION can_show_pre_door(varchar, varchar); -ALTER FUNCTION can_show(varchar(16), varchar(20)) RENAME TO can_show_pre_door; -CREATE FUNCTION can_show(varchar(16), varchar(20)) RETURNS boolean AS - 'SELECT can_show_pre_door($1, $2) AND NOT ($2 = ''opendoor'' AND (NOT in_door($1)))' LANGUAGE SQL EXTERNAL SECURITY DEFINER; -REVOKE ALL ON FUNCTION can_show(varchar, varchar) FROM public; - diff --git a/sql-edition/schema/populate.sql b/sql-edition/schema/populate.sql deleted file mode 100644 index 3f9b1b3..0000000 --- a/sql-edition/schema/populate.sql +++ /dev/null @@ -1,50 +0,0 @@ -DELETE FROM cokecontrollers; -DELETE FROM doorcontrollers; - -COPY cokecontrollers (user_name, reason) FROM stdin; -root automatic -bers 19990421 -mjdawson 19990421 -mtearle 19990421 -nick 19990421 -japester 19990421 -maset 19990421 -djinn 19990519 -grahame 19990714 -maelstrm 19991208 -trs80 20000822 -tieryn 20010328 -dayta 20010328 -ack 20010510 -colm 20010510 -davidb 20010510 -nikita 20010510 -fitz 20010510 -omailes 20010712 -matt 20020412 -susie 20020809 -griffin 20020809 -tpope 20021018 -maelkann 20021031 -dagobah 20030401 -thebmw 20030401 -coxy 20031010 -\. - -COPY doorcontrollers (user_name, reason) FROM stdin; -root by default -trs80 meeting 2000-05-18 cos he\'s cool -chas meeting 2000-05-18 he\'s always in -matt the not meeting of 2002-01-04 -thebmw the meeting of 2002-08-09 -susie the sgm of 2002-10-18 -velithya blah -coxy meeting 2003-03-07 -dagobah meeting 2003-03-07 -davyd meeting 2003-03-07 -grahame cos -falstaff some time -griffin foo -vegeta shrug -\. - diff --git a/sql-edition/syslog/Makefile b/sql-edition/syslog/Makefile deleted file mode 100644 index d600364..0000000 --- a/sql-edition/syslog/Makefile +++ /dev/null @@ -1,11 +0,0 @@ -CC = gcc -INCLUDES = -I$(shell pg_config --includedir) -I$(shell pg_config --includedir-server) -CFLAGS = $(INCLUDES) -Wall -O2 - -all: pg_syslog.so - -pg_syslog.so: pg_syslog.o - gcc -shared -o $@ $< - -clean: - rm -f *.o *.so diff --git a/sql-edition/syslog/pg_syslog.c b/sql-edition/syslog/pg_syslog.c deleted file mode 100644 index 1dbdc85..0000000 --- a/sql-edition/syslog/pg_syslog.c +++ /dev/null @@ -1,28 +0,0 @@ -#include -#include -#include -#include -#include - -PG_FUNCTION_INFO_V1(logmsg); -Datum logmsg(PG_FUNCTION_ARGS) { - text* arg0; - char* msg; - int len; - - arg0 = PG_GETARG_TEXT_P(0); - len = arg0->vl_len - VARHDRSZ; - - msg = (char*) palloc(len+1); - memcpy(msg, arg0->vl_dat, len); - msg[len] = '\0'; - - openlog("postgres", LOG_PID, LOG_LOCAL6); - syslog(LOG_INFO, "[%s]: %s", GetUserNameFromId(GetUserId()), msg); - closelog(); - - pfree(msg); - - /* not reached */ - PG_RETURN_VOID(); -} -- 2.20.1