-- validate lock tuple trigger

-- set defaults for the testing environment
\set ON_ERROR_STOP 1
SET client_min_messages='notice';

-- load extension for PostgreSQL 9.1 and later
CREATE EXTENSION tuplock;

-- test in a distinct schema...
CREATE SCHEMA test_tuplock;
SET search_path TO test_tuplock, public;

CREATE TABLE test(
  tid SERIAL PRIMARY KEY,
  data TEXT NOT NULL,
  -- locking attribute
  lock BOOLEAN NOT NULL DEFAULT FALSE
);

-- create the locking trigger
CREATE TRIGGER test_lock
  BEFORE UPDATE OR DELETE ON test
  FOR EACH ROW EXECUTE PROCEDURE tuplock(lock);

-- must also forbid truncate
CREATE TRIGGER test_lock2
BEFORE TRUNCATE ON test
EXECUTE PROCEDURE tuplock();

-- table for bad examples
CREATE TABLE test2(
  tid SERIAL PRIMARY KEY,
  data TEXT NOT NULL,
  lock BOOLEAN NOT NULL DEFAULT FALSE);

-- current status: 0
SELECT COUNT(*) FROM test;

-- first insert new values
INSERT INTO test(data) VALUES('hello');
INSERT INTO test(data) VALUES('world');
INSERT INTO test(data) VALUES('!');
INSERT INTO test(data) VALUES('foo');
INSERT INTO test(data) VALUES('bla');

-- show all
SELECT * FROM test ORDER BY tid;

-- update is fine
UPDATE test
  SET data='HELLO'
  WHERE data='hello';

-- delete is fine
DELETE FROM test
 WHERE data = '!';

-- then lock all but foo & bla entries
UPDATE test
  SET lock = TRUE
  WHERE lock = FALSE AND length(data) <> 3;

-- errors are fine, now:
\set ON_ERROR_STOP 0

-- now updates are not ok
UPDATE test
  SET data='WORLD'
  WHERE data='world'; -- fails

UPDATE test
  SET lock = FALSE
  WHERE lock; -- fails

-- now deletes are not ok
DELETE FROM test
  WHERE data = 'HELLO'; -- fails

-- as well as truncates
TRUNCATE test;

-- test2
INSERT INTO test2(data)
  VALUES ('hello world');

-- ERROR: insert is not allowed
CREATE TRIGGER test2_lock_insert
  BEFORE INSERT ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(lock);

INSERT INTO test2(data)
  VALUES ('hello world 2');

DROP TRIGGER test2_lock_insert ON test2;

-- ERROR: for each statement
CREATE TRIGGER test2_lock_stmt
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH STATEMENT EXECUTE PROCEDURE tuplock();

UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';

DROP TRIGGER test2_lock_stmt ON test2;

-- ERROR: too many arguments
CREATE TRIGGER test2_lock_nargs
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(lock, data);

UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';

DROP TRIGGER test2_lock_nargs ON test2;

-- ERROR: no arg
CREATE TRIGGER test2_lock_zero
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock();

UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';

DROP TRIGGER test2_lock_zero ON test2;

-- ERROR: wrong type
CREATE TRIGGER test2_lock_text
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(data);

UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';

DROP TRIGGER test2_lock_text ON test2;

-- ERROR: no such attribute
CREATE TRIGGER test2_lock_foo
  BEFORE UPDATE OR DELETE ON test2
  FOR EACH ROW EXECUTE PROCEDURE tuplock(foo);

UPDATE test2
  SET lock = TRUE
  WHERE data = 'hello world';

DROP TRIGGER test2_lock_foo ON test2;

-- back to normal
\set ON_ERROR_STOP 1

-- this update is ok
UPDATE test
  SET data = 'FOO'
  WHERE data = 'foo';

-- this delete is ok
DELETE FROM test
  WHERE data='bla';

-- show all
SELECT * FROM test ORDER BY tid;

-- cleanup
DROP SCHEMA test_tuplock CASCADE;
DROP EXTENSION tuplock;