diff options
Diffstat (limited to 'src/pl/plpython/sql')
| -rw-r--r-- | src/pl/plpython/sql/plpython_drop.sql | 6 | ||||
| -rw-r--r-- | src/pl/plpython/sql/plpython_error.sql | 13 | ||||
| -rw-r--r-- | src/pl/plpython/sql/plpython_function.sql | 324 | ||||
| -rw-r--r-- | src/pl/plpython/sql/plpython_populate.sql | 28 | ||||
| -rw-r--r-- | src/pl/plpython/sql/plpython_schema.sql | 42 | ||||
| -rw-r--r-- | src/pl/plpython/sql/plpython_test.sql | 70 |
6 files changed, 483 insertions, 0 deletions
diff --git a/src/pl/plpython/sql/plpython_drop.sql b/src/pl/plpython/sql/plpython_drop.sql new file mode 100644 index 00000000000..319d5e0925d --- /dev/null +++ b/src/pl/plpython/sql/plpython_drop.sql @@ -0,0 +1,6 @@ +-- +-- For paranoia's sake, don't leave an untrusted language sitting around +-- +SET client_min_messages = WARNING; + +DROP PROCEDURAL LANGUAGE plpythonu CASCADE; diff --git a/src/pl/plpython/sql/plpython_error.sql b/src/pl/plpython/sql/plpython_error.sql new file mode 100644 index 00000000000..f1939eb8ffb --- /dev/null +++ b/src/pl/plpython/sql/plpython_error.sql @@ -0,0 +1,13 @@ + +-- test error handling, i forgot to restore Warn_restart in +-- the trigger handler once. the errors and subsequent core dump were +-- interesting. + +SELECT invalid_type_uncaught('rick'); +SELECT invalid_type_caught('rick'); +SELECT invalid_type_reraised('rick'); +SELECT valid_type('rick'); + +-- Security sandbox tests +SELECT write_file('/tmp/plpython','Only trusted users should be able to do this!'); +SELECT read_file('/tmp/plpython'); diff --git a/src/pl/plpython/sql/plpython_function.sql b/src/pl/plpython/sql/plpython_function.sql new file mode 100644 index 00000000000..c849c3e5b97 --- /dev/null +++ b/src/pl/plpython/sql/plpython_function.sql @@ -0,0 +1,324 @@ + + +CREATE FUNCTION global_test_one() returns text + AS +'if not SD.has_key("global_test"): + SD["global_test"] = "set by global_test_one" +if not GD.has_key("global_test"): + GD["global_test"] = "set by global_test_one" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpythonu; + +CREATE FUNCTION global_test_two() returns text + AS +'if not SD.has_key("global_test"): + SD["global_test"] = "set by global_test_two" +if not GD.has_key("global_test"): + GD["global_test"] = "set by global_test_two" +return "SD: " + SD["global_test"] + ", GD: " + GD["global_test"]' + LANGUAGE plpythonu; + + +CREATE FUNCTION static_test() returns int4 + AS +'if SD.has_key("call"): + SD["call"] = SD["call"] + 1 +else: + SD["call"] = 1 +return SD["call"] +' + LANGUAGE plpythonu; + +-- import python modules + +CREATE FUNCTION import_fail() returns text + AS +'try: + import foosocket +except Exception, ex: + plpy.notice("import socket failed -- %s" % str(ex)) + return "failed as expected" +return "succeeded, that wasn''t supposed to happen"' + LANGUAGE plpythonu; + + +CREATE FUNCTION import_succeed() returns text + AS +'try: + import array + import bisect + import calendar + import cmath + import errno + import math + import md5 + import operator + import random + import re + import sha + import string + import time + import whrandom +except Exception, ex: + plpy.notice("import failed -- %s" % str(ex)) + return "failed, that wasn''t supposed to happen" +return "succeeded, as expected"' + LANGUAGE plpythonu; + +CREATE FUNCTION import_test_one(text) RETURNS text + AS +'import sha +digest = sha.new(args[0]) +return digest.hexdigest()' + LANGUAGE plpythonu; + +CREATE FUNCTION import_test_two(users) RETURNS text + AS +'import sha +plain = args[0]["fname"] + args[0]["lname"] +digest = sha.new(plain); +return "sha hash of " + plain + " is " + digest.hexdigest()' + LANGUAGE plpythonu; + +CREATE FUNCTION argument_test_one(users, text, text) RETURNS text + AS +'keys = args[0].keys() +keys.sort() +out = [] +for key in keys: + out.append("%s: %s" % (key, args[0][key])) +words = args[1] + " " + args[2] + " => {" + ", ".join(out) + "}" +return words' + LANGUAGE plpythonu; + + +-- these triggers are dedicated to HPHC of RI who +-- decided that my kid's name was william not willem, and +-- vigorously resisted all efforts at correction. they have +-- since gone bankrupt... + +CREATE FUNCTION users_insert() returns trigger + AS +'if TD["new"]["fname"] == None or TD["new"]["lname"] == None: + return "SKIP" +if TD["new"]["username"] == None: + TD["new"]["username"] = TD["new"]["fname"][:1] + "_" + TD["new"]["lname"] + rv = "MODIFY" +else: + rv = None +if TD["new"]["fname"] == "william": + TD["new"]["fname"] = TD["args"][0] + rv = "MODIFY" +return rv' + LANGUAGE plpythonu; + + +CREATE FUNCTION users_update() returns trigger + AS +'if TD["event"] == "UPDATE": + if TD["old"]["fname"] != TD["new"]["fname"] and TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpythonu; + + +CREATE FUNCTION users_delete() RETURNS trigger + AS +'if TD["old"]["fname"] == TD["args"][0]: + return "SKIP" +return None' + LANGUAGE plpythonu; + + +CREATE TRIGGER users_insert_trig BEFORE INSERT ON users FOR EACH ROW + EXECUTE PROCEDURE users_insert ('willem'); + +CREATE TRIGGER users_update_trig BEFORE UPDATE ON users FOR EACH ROW + EXECUTE PROCEDURE users_update ('willem'); + +CREATE TRIGGER users_delete_trig BEFORE DELETE ON users FOR EACH ROW + EXECUTE PROCEDURE users_delete ('willem'); + + +-- nested calls +-- + +CREATE FUNCTION nested_call_one(text) RETURNS text + AS +'q = "SELECT nested_call_two(''%s'')" % args[0] +r = plpy.execute(q) +return r[0]' + LANGUAGE plpythonu ; + +CREATE FUNCTION nested_call_two(text) RETURNS text + AS +'q = "SELECT nested_call_three(''%s'')" % args[0] +r = plpy.execute(q) +return r[0]' + LANGUAGE plpythonu ; + +CREATE FUNCTION nested_call_three(text) RETURNS text + AS +'return args[0]' + LANGUAGE plpythonu ; + +-- some spi stuff + +CREATE FUNCTION spi_prepared_plan_test_one(text) RETURNS text + AS +'if not SD.has_key("myplan"): + q = "SELECT count(*) FROM users WHERE lname = $1" + SD["myplan"] = plpy.prepare(q, [ "text" ]) +try: + rv = plpy.execute(SD["myplan"], [args[0]]) + return "there are " + str(rv[0]["count"]) + " " + str(args[0]) + "s" +except Exception, ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpythonu; + +CREATE FUNCTION spi_prepared_plan_test_nested(text) RETURNS text + AS +'if not SD.has_key("myplan"): + q = "SELECT spi_prepared_plan_test_one(''%s'') as count" % args[0] + SD["myplan"] = plpy.prepare(q) +try: + rv = plpy.execute(SD["myplan"]) + if len(rv): + return rv[0]["count"] +except Exception, ex: + plpy.error(str(ex)) +return None +' + LANGUAGE plpythonu; + + +/* really stupid function just to get the module loaded +*/ +CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu; + +/* a typo +*/ +CREATE FUNCTION invalid_type_uncaught(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + SD["plan"] = plpy.prepare(q, [ "test" ]) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; + +/* for what it's worth catch the exception generated by + * the typo, and return None + */ +CREATE FUNCTION invalid_type_caught(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError, ex: + plpy.notice(str(ex)) + return None +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; + +/* for what it's worth catch the exception generated by + * the typo, and reraise it as a plain error + */ +CREATE FUNCTION invalid_type_reraised(text) RETURNS text + AS +'if not SD.has_key("plan"): + q = "SELECT fname FROM users WHERE lname = $1" + try: + SD["plan"] = plpy.prepare(q, [ "test" ]) + except plpy.SPIError, ex: + plpy.error(str(ex)) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; + + +/* no typo no messing about +*/ +CREATE FUNCTION valid_type(text) RETURNS text + AS +'if not SD.has_key("plan"): + SD["plan"] = plpy.prepare("SELECT fname FROM users WHERE lname = $1", [ "text" ]) +rv = plpy.execute(SD["plan"], [ args[0] ]) +if len(rv): + return rv[0]["fname"] +return None +' + LANGUAGE plpythonu; +/* Flat out syntax error +*/ +CREATE FUNCTION sql_syntax_error() RETURNS text + AS +'plpy.execute("syntax error")' + LANGUAGE plpythonu; + +/* check the handling of uncaught python exceptions + */ +CREATE FUNCTION exception_index_invalid(text) RETURNS text + AS +'return args[1]' + LANGUAGE plpythonu; + +/* check handling of nested exceptions + */ +CREATE FUNCTION exception_index_invalid_nested() RETURNS text + AS +'rv = plpy.execute("SELECT test5(''foo'')") +return rv[0]' + LANGUAGE plpythonu; + + +CREATE FUNCTION join_sequences(sequences) RETURNS text + AS +'if not args[0]["multipart"]: + return args[0]["sequence"] +q = "SELECT sequence FROM xsequences WHERE pid = ''%s''" % args[0]["pid"] +rv = plpy.execute(q) +seq = args[0]["sequence"] +for r in rv: + seq = seq + r["sequence"] +return seq +' + LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION read_file(text) RETURNS text AS ' + return open(args[0]).read() +' LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION write_file(text,text) RETURNS text AS ' + open(args[0],"w").write(args[1]) + return "Wrote to file: %s" % args[0] +' LANGUAGE plpythonu; + +-- +-- Universal Newline Support +-- + +CREATE OR REPLACE FUNCTION newline_lf() RETURNS integer AS +'x = 100\ny = 23\nreturn x + y\n' +LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION newline_cr() RETURNS integer AS +'x = 100\ry = 23\rreturn x + y\r' +LANGUAGE plpythonu; + +CREATE OR REPLACE FUNCTION newline_crlf() RETURNS integer AS +'x = 100\r\ny = 23\r\nreturn x + y\r\n' +LANGUAGE plpythonu; diff --git a/src/pl/plpython/sql/plpython_populate.sql b/src/pl/plpython/sql/plpython_populate.sql new file mode 100644 index 00000000000..a1963e76eea --- /dev/null +++ b/src/pl/plpython/sql/plpython_populate.sql @@ -0,0 +1,28 @@ + +INSERT INTO users (fname, lname, username) VALUES ('jane', 'doe', 'j_doe'); +INSERT INTO users (fname, lname, username) VALUES ('john', 'doe', 'johnd'); +INSERT INTO users (fname, lname, username) VALUES ('willem', 'doe', 'w_doe'); +INSERT INTO users (fname, lname, username) VALUES ('rick', 'smith', 'slash'); + + +-- multi table tests +-- + +INSERT INTO taxonomy (name) VALUES ('HIV I') ; +INSERT INTO taxonomy (name) VALUES ('HIV II') ; +INSERT INTO taxonomy (name) VALUES ('HCV') ; + +INSERT INTO entry (accession, txid) VALUES ('A00001', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00002', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00003', '1') ; +INSERT INTO entry (accession, txid) VALUES ('A00004', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00005', '2') ; +INSERT INTO entry (accession, txid) VALUES ('A00006', '3') ; + +INSERT INTO sequences (sequence, eid, product, multipart) VALUES ('ABCDEF', 1, 'env', 'true') ; +INSERT INTO xsequences (sequence, pid) VALUES ('GHIJKL', 1) ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 2, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 3, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 4, 'gag') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 5, 'env') ; +INSERT INTO sequences (sequence, eid, product) VALUES ('ABCDEF', 6, 'ns1') ;
\ No newline at end of file diff --git a/src/pl/plpython/sql/plpython_schema.sql b/src/pl/plpython/sql/plpython_schema.sql new file mode 100644 index 00000000000..2f8766431fa --- /dev/null +++ b/src/pl/plpython/sql/plpython_schema.sql @@ -0,0 +1,42 @@ + +CREATE TABLE users ( + fname text not null, + lname text not null, + username text, + userid serial, + PRIMARY KEY(lname, fname) + ) ; + +CREATE INDEX users_username_idx ON users(username); +CREATE INDEX users_fname_idx ON users(fname); +CREATE INDEX users_lname_idx ON users(lname); +CREATE INDEX users_userid_idx ON users(userid); + + +CREATE TABLE taxonomy ( + id serial primary key, + name text unique + ) ; + +CREATE TABLE entry ( + accession text not null primary key, + eid serial unique, + txid int2 not null references taxonomy(id) + ) ; + +CREATE TABLE sequences ( + eid int4 not null references entry(eid), + pid serial primary key, + product text not null, + sequence text not null, + multipart bool default 'false' + ) ; +CREATE INDEX sequences_product_idx ON sequences(product) ; + +CREATE TABLE xsequences ( + pid int4 not null references sequences(pid), + sequence text not null + ) ; +CREATE INDEX xsequences_pid_idx ON xsequences(pid) ; + + diff --git a/src/pl/plpython/sql/plpython_test.sql b/src/pl/plpython/sql/plpython_test.sql new file mode 100644 index 00000000000..17d6b2e7922 --- /dev/null +++ b/src/pl/plpython/sql/plpython_test.sql @@ -0,0 +1,70 @@ +-- first some tests of basic functionality +-- +-- better succeed +-- +select stupid(); + +-- check static and global data +-- +SELECT static_test(); +SELECT static_test(); +SELECT global_test_one(); +SELECT global_test_two(); + +-- import python modules +-- +SELECT import_fail(); +SELECT import_succeed(); + +-- test import and simple argument handling +-- +SELECT import_test_one('sha hash of this string'); + +-- test import and tuple argument handling +-- +select import_test_two(users) from users where fname = 'willem'; + +-- test multiple arguments +-- +select argument_test_one(users, fname, lname) from users where lname = 'doe' order by 1; + + +-- spi and nested calls +-- +select nested_call_one('pass this along'); +select spi_prepared_plan_test_one('doe'); +select spi_prepared_plan_test_one('smith'); +select spi_prepared_plan_test_nested('smith'); + +-- quick peek at the table +-- +SELECT * FROM users; + +-- should fail +-- +UPDATE users SET fname = 'william' WHERE fname = 'willem'; + +-- should modify william to willem and create username +-- +INSERT INTO users (fname, lname) VALUES ('william', 'smith'); +INSERT INTO users (fname, lname, username) VALUES ('charles', 'darwin', 'beagle'); + +SELECT * FROM users; + + +SELECT join_sequences(sequences) FROM sequences; +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^A'; +SELECT join_sequences(sequences) FROM sequences + WHERE join_sequences(sequences) ~* '^B'; + +-- error in trigger +-- + +-- +-- Check Universal Newline Support +-- + +SELECT newline_lf(); +SELECT newline_cr(); +SELECT newline_crlf(); |