From 2b7498ef0aedc319f95d3b5994e7ac6ac5d4c8ee Mon Sep 17 00:00:00 2001 From: Bernard Blackham Date: Fri, 25 Jun 2004 10:47:55 +0000 Subject: [PATCH] Initial import --- 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 | 707 ++++++++++++++++++++++++++++++++ sql-edition/schema/populate.sql | 50 +++ sql-edition/syslog/Makefile | 11 + sql-edition/syslog/pg_syslog.c | 28 ++ 9 files changed, 1066 insertions(+) create mode 100644 sql-edition/api/API.txt create mode 100755 sql-edition/clients/dispense.py create mode 100644 sql-edition/convert.c create mode 100644 sql-edition/ident/Makefile create mode 100644 sql-edition/ident/pg_ident.c create mode 100644 sql-edition/schema/d.sql create mode 100644 sql-edition/schema/populate.sql create mode 100644 sql-edition/syslog/Makefile create mode 100644 sql-edition/syslog/pg_syslog.c diff --git a/sql-edition/api/API.txt b/sql-edition/api/API.txt new file mode 100644 index 0000000..e3ab07f --- /dev/null +++ b/sql-edition/api/API.txt @@ -0,0 +1,141 @@ + 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 new file mode 100755 index 0000000..00310b5 --- /dev/null +++ b/sql-edition/clients/dispense.py @@ -0,0 +1,48 @@ +#!/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 new file mode 100644 index 0000000..bac3b86 --- /dev/null +++ b/sql-edition/convert.c @@ -0,0 +1,31 @@ +/* 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 new file mode 100644 index 0000000..6bde0e0 --- /dev/null +++ b/sql-edition/ident/Makefile @@ -0,0 +1,11 @@ +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 new file mode 100644 index 0000000..2de2121 --- /dev/null +++ b/sql-edition/ident/pg_ident.c @@ -0,0 +1,39 @@ +#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 new file mode 100644 index 0000000..4733e5a --- /dev/null +++ b/sql-edition/schema/d.sql @@ -0,0 +1,707 @@ +------------------------------------------------------------------------------ +------------------------------------------------------------------------------ +--- --- +--- 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 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); + +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) RETURNS void AS + ' + elog(NOTICE, q(And out pops a drink!)); + ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER; +REVOKE ALL ON FUNCTION dispense_slot(integer) FROM public; + +CREATE OR REPLACE FUNCTION update_slot_status() RETURNS void AS + ' + ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER; +REVOKE ALL ON FUNCTION update_slot_status() 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(slotnum); + PERFORM update_slot_status(); + 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); + +CREATE OR REPLACE FUNCTION valid_vend_slot(varchar(2)) RETURNS boolean AS + ' + BEGIN + RETURN $1 ~ ''^[0-9]{2}$''; + END; + ' LANGUAGE plpgsql 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(varchar(2)) RETURNS void AS + ' + elog(NOTICE, q(And out pops a snack!)); + ' LANGUAGE plperlu EXTERNAL SECURITY DEFINER; +REVOKE ALL ON FUNCTION vend_slot(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 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 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(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 new file mode 100644 index 0000000..3f9b1b3 --- /dev/null +++ b/sql-edition/schema/populate.sql @@ -0,0 +1,50 @@ +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 new file mode 100644 index 0000000..d600364 --- /dev/null +++ b/sql-edition/syslog/Makefile @@ -0,0 +1,11 @@ +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 new file mode 100644 index 0000000..1dbdc85 --- /dev/null +++ b/sql-edition/syslog/pg_syslog.c @@ -0,0 +1,28 @@ +#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