--
-- Test foreign-data wrapper multicdr_fdw.
--

-- Clean up in case a prior regression run failed
SET client_min_messages TO 'error';
DROP ROLE IF EXISTS file_fdw_superuser, file_fdw_user, no_priv_user;
SET client_min_messages TO 'warning';

CREATE ROLE file_fdw_superuser LOGIN SUPERUSER; -- is a superuser
CREATE ROLE file_fdw_user LOGIN;                -- has priv and user mapping
CREATE ROLE no_priv_user LOGIN;                 -- has priv but no user mapping

-- Install multicdr_fdw
CREATE EXTENSION multicdr_fdw;

-- file_fdw_superuser owns fdw-related objects
SET ROLE file_fdw_superuser;
CREATE SERVER file_server FOREIGN DATA WRAPPER multicdr_fdw;

-- privilege tests
SET ROLE file_fdw_user;
CREATE FOREIGN DATA WRAPPER multicdr_fdw2 HANDLER file_fdw_handler VALIDATOR file_fdw_validator;   -- ERROR
CREATE SERVER file_server2 FOREIGN DATA WRAPPER multicdr_fdw;   -- ERROR
CREATE USER MAPPING FOR file_fdw_user SERVER file_server;   -- ERROR

SET ROLE file_fdw_superuser;
GRANT USAGE ON FOREIGN SERVER file_server TO file_fdw_user;

SET ROLE file_fdw_user;
CREATE USER MAPPING FOR file_fdw_user SERVER file_server;

-- create user mappings and grant privilege to test users
SET ROLE file_fdw_superuser;
CREATE USER MAPPING FOR file_fdw_superuser SERVER file_server;
CREATE USER MAPPING FOR no_priv_user SERVER file_server;

-- validator tests
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (directory 'foo');
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (directory '');      -- ERROR
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (directory 'foo', pattern '');
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (directory 'foo', pattern '', mapfields '');
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (directory 'foo', pattern '', mapfields '', posfields '');
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (posfields '1,foo,2');          -- ERROR
CREATE FOREIGN TABLE tbl () SERVER file_server;  -- ERROR

-- test date restrictions
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (directory 'foo', dateformat '$1=YYYY');          -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat '$1=YYYY', dateminfield 'datemin');
SELECT * FROM tbl LIMIT 1; -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin TIMESTAMP ) SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat '$1=YYYY', dateminfield 'datemin');
SELECT * FROM tbl LIMIT 1; -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin text ) SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat '$1=YYYY', dateminfield 'datemin');
SELECT * FROM tbl LIMIT 1; -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin text ) SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat '$1=YYYY', dateminfield 'datemin_none');
SELECT * FROM tbl LIMIT 1; -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin TIMESTAMP ) SERVER file_server OPTIONS (directory 'foo', dateformat '$1=YYYY', dateminfield 'datemin'); -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin TIMESTAMP ) SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat '$2=YYYY', dateminfield 'datemin'); -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin TIMESTAMP ) SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat '$1=YYYY', dateminfield 'datemin');
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin TIMESTAMP ) SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat 'YYYY', dateminfield 'datemin'); -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;
CREATE FOREIGN TABLE tbl ( datemin TIMESTAMP ) SERVER file_server OPTIONS (directory 'foo', pattern '(.*).cdr', dateformat '$1', dateminfield 'datemin'); -- ERROR
DROP FOREIGN TABLE IF EXISTS tbl;

-- various selects
CREATE FOREIGN TABLE tbl_alpha (
  f1 text,
  f2 int4,
  f3 text,
  f4 int4,
  f5 text
) SERVER file_server
OPTIONS (directory '@abs_srcdir@/data/regression1', pattern '.*\.cdr$', posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329', rowminlen '339', rowmaxlen '340');
CREATE FOREIGN TABLE tbl_beta (
  f1 text,
  f2 int4,
  f3 text,
  f4 int4,
  f5 text
) SERVER file_server
OPTIONS (directory '@abs_srcdir@/data/regression2', pattern '.*\.cdr$', posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329', rowminlen '339', rowmaxlen '340');
GRANT SELECT ON tbl_beta TO file_fdw_user;
CREATE FOREIGN TABLE tbl_bad (
  f1 text,
  f2 text
) SERVER file_server
OPTIONS (directory '@abs_srcdir@/data/regression9000', pattern '.*\.cdr$', posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329', rowminlen '339', rowmaxlen '340');

-- basic query tests
SELECT * FROM tbl_alpha ORDER BY f4;
SELECT * FROM tbl_beta WHERE f4 < 5 AND f2 < 7 ORDER BY f5;
SELECT * FROM tbl_alpha c JOIN tbl_beta t ON (t.f2 = c.f2) WHERE c.f4 < 100 AND t.f4 < 50;

-- rows count
\t on
SELECT SUM(f4) FROM tbl_alpha;      -- 1572121
SELECT count(*) FROM tbl_alpha;     -- 19
SELECT count(*) FROM tbl_beta;      -- 490
\t off

-- error context report tests
SELECT * FROM tbl_bad;               -- ERROR

-- misc query tests
\t on
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM tbl_alpha;
\t off
PREPARE st(int) AS SELECT * FROM tbl_alpha WHERE f2 = $1;
EXECUTE st(10);
EXECUTE st(20);
DEALLOCATE st;

-- date restrictions tests
CREATE FOREIGN TABLE tbl_date ( 
	f1 text, 
	f2 text, 
	filename text,
	datemin TIMESTAMP,
	datemax TIMESTAMP
) SERVER file_server 
OPTIONS (directory '@abs_srcdir@/data/regression3', 
	pattern 'MSC_(.*)-([[:digit:]]+)\.cdr$',
	dateformat '$1=YYYYMMDDHHMISS',
	posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329',
	mapfields '0,1, 0,0,0',
	dateminfield 'datemin',
	datemaxfield 'datemax',
	filefield 'filename',
	rowminlen '339', 
	rowmaxlen '340'
);
\t on
SELECT filename, COUNT(filename) FROM tbl_date GROUP BY filename ORDER BY filename;
SELECT filename, COUNT(filename) FROM tbl_date WHERE
	datemin = CAST('2001-12-01 01:23:45' AS TIMESTAMP) AND
	datemax = CAST('2015-12-01 01:23:45' AS TIMESTAMP) GROUP BY filename ORDER BY filename;
SELECT filename, COUNT(filename) FROM tbl_date WHERE
	datemin = CAST('2012-12-01 01:23:45' AS TIMESTAMP) AND
	datemax = CAST('2012-12-31 01:23:45' AS TIMESTAMP) GROUP BY filename ORDER BY filename;
SELECT filename, COUNT(filename) FROM tbl_date WHERE
	datemax = CAST('2012-12-31 01:23:45' AS TIMESTAMP) GROUP BY filename ORDER BY filename;
SELECT filename, COUNT(filename) FROM tbl_date WHERE
	datemin = CAST('2013-01-01 01:23:45' AS TIMESTAMP) GROUP BY filename ORDER BY filename;
SELECT filename, COUNT(filename) FROM tbl_date WHERE
	filename = '@abs_srcdir@/data/regression3/MSC_20121201074000-1.cdr' AND
	datemin = CAST('2012-12-01 07:39:00' AS TIMESTAMP) AND
	datemax = CAST('2012-12-01 07:41:00' AS TIMESTAMP) GROUP BY filename ORDER BY filename;
\t off
DROP FOREIGN TABLE IF EXISTS tbl_date;

-- tableoid
SELECT tableoid::regclass, f2 FROM tbl_alpha;

-- updates aren't supported
INSERT INTO tbl_alpha VALUES(1,2); -- ERROR
UPDATE tbl_alpha SET f2 = 1; -- ERROR
DELETE FROM tbl_alpha WHERE f2 = 100; -- ERROR
SELECT * FROM tbl_alpha FOR UPDATE OF tbl_alpha; -- ERROR
-- but this should be ignored
SELECT * FROM tbl_alpha FOR UPDATE;

-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM tbl_beta WHERE f4 < 32 ORDER BY f2 LIMIT 5;
SET ROLE file_fdw_user;
SELECT * FROM tbl_beta WHERE f4 < 32 ORDER BY f2 LIMIT 5;
SET ROLE no_priv_user;
SELECT * FROM tbl_beta WHERE f4 < 32 ORDER BY f2;   -- ERROR
SET ROLE file_fdw_user;
\t on
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM tbl_beta WHERE f2 > 0;
\t off

-- privilege tests for object
SET ROLE file_fdw_superuser;
ALTER FOREIGN TABLE tbl_beta OWNER TO file_fdw_user;
ALTER FOREIGN TABLE tbl_beta OPTIONS (SET directory 'foobar');
SET ROLE file_fdw_user;
ALTER FOREIGN TABLE tbl_beta OPTIONS (SET directory 'foobar'); -- ERROR
SET ROLE file_fdw_superuser;

-- mapping tests
CREATE FOREIGN TABLE tbl_gamma (
  f1 text,
  f2 int4,
  f3 text,
  f4 int4,
  f5 text
) SERVER file_server
OPTIONS (directory '@abs_srcdir@/data/regression1', pattern '.*\.cdr$', posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329', mapfields '4,3,2,1,0');

SELECT * FROM tbl_gamma ORDER BY f2;
\t on
SELECT SUM(f2) FROM tbl_gamma;      -- 1572121
\t off

-- filefield test one
DROP FOREIGN TABLE tbl_gamma;
CREATE FOREIGN TABLE tbl_gamma (
  f1 text,
  f2 int4,
  f3 text,
  f4 int4,
  filename text
) SERVER file_server
OPTIONS (directory '@abs_srcdir@/data/regression1', pattern '.*\.cdr$', posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329', mapfields '4,3,2,1,0', filefield 'filename', rowminlen '339', rowmaxlen '340');

\t on
SELECT distinct filename FROM tbl_gamma;
\t off

-- filefield test two
DROP FOREIGN TABLE tbl_gamma;
CREATE FOREIGN TABLE tbl_gamma (
  f1 text,
  f2 int4,
  filename text,
  f3 text,
  f4 int4
) SERVER file_server
OPTIONS (directory '@abs_srcdir@/data/regression2', pattern '.*\.cdr$', posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329', mapfields '4,3,-1,1,0', filefield 'filename', rowminlen '339', rowmaxlen '340');

\t on
SELECT count(DISTINCT Filename) FROM tbl_gamma; -- 4
\t off

-- empty fields test
CREATE FOREIGN TABLE tbl_emptyspaces (
  f1 text,
  f2 int4,
  f3 text,
  f4 int4,
  f5 text
) SERVER file_server
OPTIONS (directory '@abs_srcdir@/data/regression4', pattern '.*\.cdr$', posfields '0,6,15,40,50,71,92,113,175,212,217,227,232,319,329', mapfields '4,3,2,1,0');

\t on
SELECT count(*) FROM tbl_emptyspaces; -- 19
SELECT count(*) FROM tbl_emptyspaces WHERE f1 is NULL; -- 2
\t off

-- cleanup
RESET ROLE;
DROP EXTENSION multicdr_fdw CASCADE;
DROP ROLE file_fdw_superuser, file_fdw_user, no_priv_user;

RESET client_min_messages;
-- vim: ft=sql