--
-- external tables 1 - short and simple functional tests. The full set of tests
-- exists in cdbunit.
--
-- start_matchsubs
--
-- # replace return code in error message (platform specific)
--
-- m/ERROR\:\s+external table .* command ended with .* not found/
-- s/nosuchcommand\:\s*(command)? not found/nosuchcommand\: NOT FOUND/
--
-- m/ERROR\:\s+external table .* command ended with .*No such file.*/
-- s/nosuchfile\.txt\:\s*No such file (or directory)?/nosuchfile\.txt\: NO SUCH FILE/
-- m/ERROR\:\s+external table .* command ended with .*No such file.*/i
-- s/cat\: (cannot open)? nosuchfile\.txt/cat\: nosuchfile\.txt/
--
-- # remove line number - redhat
-- m/ERROR\:\s+external table .* command ended with .*NOT FOUND.*/i
-- s/\s+line \d+\://
-- # remove cannot open - solaris
-- m/ERROR\:\s+external table .* command ended with .*cat\: cannot open.*/i
-- s/cat\: cannot open (.*)$/cat\: $1\: NO SUCH FILE/
--
-- end_matchsubs
SET gp_foreign_data_access = true;
set optimizer_disable_missing_stats_collection = on;
CREATE TABLE REG_REGION (R_REGIONKEY INT, R_NAME CHAR(25), R_COMMENT VARCHAR(152)) DISTRIBUTED BY (R_REGIONKEY);

-- start_ignore
-- --------------------------------------
-- check platform
-- --------------------------------------
drop external web table if exists check_echo;
CREATE EXTERNAL WEB TABLE check_echo (x text)
execute E'(echo gpfdist)'
on SEGMENT 0
format 'text';
select * from check_echo;


-- end_ignore


-- --------------------------------------
-- 'gpfdist' protocol
-- --------------------------------------

CREATE EXTERNAL WEB TABLE gpfdist_status (x text)
execute E'( python $GPHOME/bin/lib/gppinggpfdist.py @hostname@:7070 2>&1 || echo) '
on SEGMENT 0
FORMAT 'text' (delimiter '|');

CREATE EXTERNAL WEB TABLE gpfdist_start (x text)
execute E'((@gpwhich_gpfdist@ -p 7070 -d @abs_srcdir@/data  </dev/null >/dev/null 2>&1 &); sleep 2; echo "starting...") '
on SEGMENT 0
FORMAT 'text' (delimiter '|');

CREATE EXTERNAL WEB TABLE gpfdist_stop (x text)
execute E'(/bin/pkill gpfdist || killall gpfdist) > /dev/null 2>&1; echo "stopping..."'
on SEGMENT 0
FORMAT 'text' (delimiter '|');

-- start_ignore
select * from gpfdist_stop;
select * from gpfdist_status;
select * from gpfdist_start;
select * from gpfdist_status;
-- end_ignore

CREATE EXTERNAL TABLE EXT_NATION  ( N_NATIONKEY  INTEGER ,
                            N_NAME       CHAR(25) ,
                            N_REGIONKEY  INTEGER ,
                            N_COMMENT    VARCHAR(152))
location ('gpfdist://@hostname@:7070/nation.tbl' )
FORMAT 'text' (delimiter '|');
CREATE EXTERNAL TABLE EXT_REGION  (LIKE REG_REGION)
location ('gpfdist://@hostname@:7070/region.tbl' )
FORMAT 'text' (delimiter '|');

SELECT * FROM EXT_NATION;
SELECT * FROM EXT_REGION;
SELECT * FROM EXT_REGION as r, EXT_NATION as n WHERE n.N_REGIONKEY = r.R_REGIONKEY;

-- start_ignore
select * from gpfdist_status;
select * from gpfdist_stop;
select * from gpfdist_status;
-- end_ignore

-- drop tables
DROP EXTERNAL TABLE EXT_NATION;
DROP EXTERNAL TABLE EXT_REGION;

--
-- gpfdist in csv (mpp-1519, etc)
--
CREATE EXTERNAL WEB TABLE gpfdist_csv_start (x text)
execute E'((@gpwhich_gpfdist@ -p 7070 -d @abs_srcdir@/data  </dev/null >/dev/null 2>&1 &); sleep 2; echo "starting...") '
on SEGMENT 0
FORMAT 'text' (delimiter '|');
-- 
-- "
-- (end the double quote so emacs highlighting works correctly)
--
-- start_ignore
select * from gpfdist_status;
select * from gpfdist_stop;
select * from gpfdist_status;
select * from gpfdist_csv_start;
select * from gpfdist_status;
-- end_ignore

create external table ext_whois (
source_lineno			int,
domain_name			varchar(350),
subdomain			varchar(150),
tld				varchar(50),
ip_address			inet,
ip_address_int			bigint,
reverse_dns			varchar(512),
reverse_domain			varchar(350),
registrar			varchar(200),
registrar_referral		varchar(512),
whois_server			varchar(512),
harvest_date			timestamp with time zone,
created_on			timestamp with time zone,
update_date			timestamp with time zone,
expire_date			timestamp with time zone,
rank				int,
status				char(1),
update_status			int,
nameserver1			varchar(512),
nameserver1_domain_name		varchar(350),
nameserver1_subdomain		varchar(150),
nameserver1_tld			varchar(50),
nameserver2			varchar(512),
nameserver2_domain_name		varchar(350),
nameserver2_subdomain		varchar(150),
nameserver2_tld			varchar(50),
nameserver3			varchar(512),
nameserver3_domain_name		varchar(350),
nameserver3_subdomain		varchar(150),
nameserver3_tld			varchar(50),
nameserver4			varchar(512),
nameserver4_domain_name		varchar(350),
nameserver4_subdomain		varchar(150),
nameserver4_tld			varchar(50),
nameserver5			varchar(512),
nameserver5_domain_name		varchar(350),
nameserver5_subdomain		varchar(150),
nameserver5_tld			varchar(50),
registrant_name			varchar(200),
registrant_organization		varchar(200),
registrant_email		varchar(512),
registrant_email_domain		varchar(350),
registrant_email_subdomain	varchar(150),
registrant_email_tld		varchar(50),
registrant_phone		varchar(50),
registrant_fax			varchar(50),
registrant_addrall		varchar(1024),
registrant_street1		varchar(200),
registrant_street2		varchar(200),
registrant_street3		varchar(200),
registrant_city			varchar(200),
registrant_state_province	varchar(100),
registrant_postal_code		varchar(50),
registrant_country		varchar(100),
tech_name			varchar(200),
tech_organization		varchar(200),
tech_email			varchar(512),
tech_email_domain		varchar(350),
tech_email_subdomain		varchar(150),
tech_email_tld			varchar(50),
tech_phone			varchar(50),
tech_fax			varchar(50),
tech_addrall			varchar(1024),
tech_street1			varchar(200),
tech_street2			varchar(200),
tech_street3			varchar(200),
tech_city			varchar(200),
tech_state_province		varchar(100),
tech_postal_code		varchar(50),
tech_country			varchar(100),
admin_name			varchar(200),
admin_organization		varchar(200),
admin_email			varchar(512),
admin_email_domain		varchar(350),
admin_email_subdomain		varchar(150),
admin_email_tld			varchar(50),
admin_phone			varchar(50),
admin_fax			varchar(50),
admin_addrall			varchar(1024),
admin_street1			varchar(200),
admin_street2			varchar(200),
admin_street3			varchar(200),
admin_city			varchar(200),
admin_state_province		varchar(100),
admin_postal_code		varchar(50),
admin_country			varchar(100),
rec_path			varchar(512),
raw_record			text
)
location ('gpfdist://@hostname@:7070/whois.csv' )
format 'csv' ( quote as '"' header);
-- "
-- start_ignore
select count(*) from ext_whois;
-- end_ignore

-- test FILL EMPTY FIELDS
create external table ext_fill1(a int, b int, c int)
location('gpfdist://@hostname@:7070/missing_fields1.data' )
format 'text' ( delimiter '|' fill missing fields);

create external table ext_fill2(a int, b int, c int)
location('gpfdist://@hostname@:7070/missing_fields2.data' )
format 'text' ( delimiter '|' fill missing fields);

-- also test force not null in addition to fill empty fields
create external table ext_fill3_fnn(a text, b text, c text)
location('gpfdist://@hostname@:7070/missing_fields1.data' )
format 'csv' ( delimiter '|' fill missing fields force not null c);

create external table ext_fill4_fnn(a text, b text, c text)
location('gpfdist://@hostname@:7070/missing_fields1.data' )
format 'csv' ( delimiter '|' fill missing fields force not null b,c);

select * from ext_fill1 order by a,b,c; -- should pad missing attrs with nulls
-- start_ignore
select * from ext_fill2 order by a,b,c; -- should fail due to empty data line
-- end_ignore
select * from ext_fill3_fnn where c is null; -- should be empty
select * from ext_fill3_fnn where b is null; -- one row result
select * from ext_fill4_fnn where c is null or b is null; -- should be empty

drop external table ext_fill1;
drop external table ext_fill2;
drop external table ext_fill3_fnn;
drop external table ext_fill4_fnn;

-- test NEWLINE
create external table ext_newline1(N_NATIONKEY INT, N_NAME text, N_REGIONKEY INT, N_COMMENT text)
location('gpfdist://@hostname@:7070/nation.tbl' )
format 'text' ( delimiter '|' newline 'lf');

create external table ext_newline2(like ext_newline1)
location('gpfdist://@hostname@:7070/nation.tbl' )
format 'text' ( delimiter '|' newline 'cr');

create external table ext_willfail(a int)
location('gpfdist://@hostname@:7070/nation.tbl' )
format 'text' ( delimiter '|' newline 'blah'); -- should fail with invalid newline.

select * from ext_newline1; -- should pass. using the correct linefeed. file has 'lf'.
-- start_ignore
select * from ext_newline2; -- should fail. using an incorrect linefeed. file has 'lf'.
-- end_ignore

drop external table ext_newline1;
drop external table ext_newline2;

-- MPP-6698
create external table ext_mpp6698(a text)
location('gpfdist://@hostname@:7070/mpp6698.data' )
format 'csv' (quote '''');

select * from ext_mpp6698 order by a; -- should ignore the quotes
drop external table ext_mpp6698;

-- MPP-12839
create external table ext_mpp12839
(
  userguid varchar(36),
  action_time timestamp without time zone,
  action_type smallint,
  object_id integer,
  general_param character varying(8000),
  plu_id smallint,
  page_name character varying(2000),
  browser integer,
  platform integer,
  filtering_data character varying(8000),
  is_new_user boolean,
  dummy character varying(1)
)
location('gpfdist://@hostname@:7070/mpp12839*.data' )
format 'text' (delimiter E'\177' null '' newline 'crlf')
segment reject limit 100 rows;

select * from ext_mpp12839; -- should not reach reject limit
drop external table ext_mpp12839;

-- --------------------------------------
-- some negative tests
-- --------------------------------------

--
-- test for exec child process stderr showing in error message
--
create external web table ext_stderr1(a text) execute 'nosuchcommand' ON 0 format 'text';
create external web table ext_stderr2(a text) execute 'cat nosuchfile.txt' ON 0 format 'text';

--
-- bad csv (quote must be a single char)
--
create external table bad_whois (
source_lineno			int,
domain_name			varchar(350)
)
location ('gpfdist://@hostname@:7070/whois.csv' )
format 'csv' ( header quote as 'ggg');
select count(*) from bad_whois;
drop external table bad_whois;

--
-- try a bad protocol
--
create external table badt2 (x text) 
location ('bad_protocol://@hostname@@abs_srcdir@/data/no/such/place/badt2.tbl' )
format 'text' (delimiter '|');

--
-- ALTER
--
--create external table ext (a int, x text) 
--location ('gpfdist://@hostname@:7070@abs_srcdir@/data/no/such/place/badt1.tbl' )
--format 'text';
--alter foreign table ext drop column a; -- should fail (wrong object)
--alter table ext drop column a; -- should pass with warning
--alter external table ext add column a int;
--alter external table ext drop column a;
--alter external table ext add column extnewcol int not null; -- should fail (constraints not allowed)
--alter external table ext add column extnewcol int;
--alter external table ext alter column extnewcol set default 1; -- should fail (unsupported alter type)

--
-- TRUNCATE/UPDATE/DELETE/INSERT (INTO RET)
--
--truncate ext;
--delete from ext;
--update ext set x='1' where x='2';
--insert into ext(x) values('a');
--drop table ext; -- should fail (wrong object)
--drop foreign table ext; -- should fail (wrong object)
--drop external table ext;

----------------------------------------------------------------------
-- CUSTOM PROTOCOLS
----------------------------------------------------------------------
-- DROP EXTERNAL TABLE IF EXISTS ext_w;
-- DROP EXTERNAL TABLE IF EXISTS ext_r;
-- DROP ROLE IF EXISTS extprotu;
-- CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE;
-- CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE;

-- DROP PROTOCOL IF EXISTS demoprot;
-- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'nosuchfunc'); -- should fail
-- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'boolin'); -- should fail
-- CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed
-- CREATE PROTOCOL demoprot_untrusted (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed

-- CREATE ROLE extprotu NOSUPERUSER;
-- SET SESSION AUTHORIZATION extprotu;

-- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail
-- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail

-- RESET SESSION AUTHORIZATION;
-- ALTER PROTOCOL demoprot_untrusted OWNER TO extprotu; -- should fail. protocol is not trusted
-- GRANT SELECT ON PROTOCOL demoprot_untrusted TO extprotu; -- should fail. protocol is not trusted
-- GRANT SELECT ON PROTOCOL demoprot TO extprotu;
-- GRANT INSERT ON PROTOCOL demoprot TO extprotu;
-- SET SESSION AUTHORIZATION extprotu;

-- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed
-- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed
-- DROP EXTERNAL TABLE IF EXISTS ext_w;
-- DROP EXTERNAL TABLE IF EXISTS ext_r;

-- RESET SESSION AUTHORIZATION;
-- REVOKE INSERT ON PROTOCOL demoprot FROM extprotu;
-- SET SESSION AUTHORIZATION extprotu;

-- CREATE WRITABLE EXTERNAL TABLE ext_w(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should fail
-- CREATE READABLE EXTERNAL TABLE ext_r(a int) location('demoprot://demoprotfile.txt') format 'text'; -- should succeed
-- DROP EXTERNAL TABLE ext_r;
-- RESET SESSION AUTHORIZATION;
-- REVOKE ALL PRIVILEGES ON PROTOCOL demoprot FROM extprotu;
-- DROP ROLE IF EXISTS extprotu;

--
-- WET tests
--

--
-- CREATE (including LIKE, DISTRIBUTED BY)
--
-- positive
create writable external table wet_pos1(a text, b text) location('gpfdist://@hostname@:7070/wet.out') format 'text';
create writable external table wet_pos2(a text, b text) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(b);
create writable external table wet_pos3(like wet_pos2) location('gpfdist://@hostname@:7070/wet.out') format 'text' distributed by(a,b);
create writable external table wet_region(like reg_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text';
create readable external table ret_region(like wet_region) location('gpfdist://@hostname@:7070/wet_region.out') format 'text';

-- negative
create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070@abs_srcdir@/badt1.tbl') format 'text';
create writable external table wet_neg1(a text, b text) location('gpfdist://@hostname@:7070/wet.out', 'gpfdist://@hostname@:7070/wet.out') format 'text';
create writable external web table wet_pos5(a text, b text) execute 'some command' on segment 0 format 'text';

--
-- SELECT from WET (negative)
--
select * from wet_pos1;
--
-- WET: export some data with INSERT SELECT, INSERT and COPY. 
--
-- COPY reg_region FROM STDIN DELIMITER '|';
-- 0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to 
-- 1|AMERICA|hs use ironic, even requests. s
-- \.
INSERT INTO wet_region SELECT * from reg_region;

-- COPY wet_region FROM STDIN DELIMITER '|';
-- 2|ASIA|ges. thinly even pinto beans ca
-- 3|EUROPE|ly final courts cajole furiously final excuse
-- \.
-- INSERT INTO wet_region VALUES(4,'MIDDLE EAST','uickly special');

--
-- Now use RET to see if data was exported correctly.
-- NOTE: since we don't bother cleaning up the exported file, it may grow bigger
-- in between runs, so we don't check for count(*), and instead check for distinct.
--
-- SELECT DISTINCT(R_NAME) FROM ret_region ORDER BY R_NAME;

-- start_ignore
select * from gpfdist_status;
select * from gpfdist_stop;
select * from gpfdist_status;
-- end_ignore

--
-- get an error for missing gpfdist
--
select count(*) from ext_whois;

--
-- test CREATE EXTERNAL TABLE privileges
--
-- show gp_external_grant_privileges; -- MUST BE OFF for the following tests to work.
-- CREATE ROLE exttab1_su SUPERUSER; -- SU with no privs in pg_auth
-- CREATE ROLE exttab1_u1 CREATEEXTTABLE(protocol='gpfdist', type='readable'); 
-- CREATE ROLE exttab1_u2 CREATEEXTTABLE(protocol='gpfdist', type='writable'); 
-- CREATE ROLE exttab1_u3 CREATEEXTTABLE(protocol='gpfdist') NOCREATEEXTTABLE(protocol='gpfdist', type='readable'); -- fail due to conflict 

-- SET SESSION AUTHORIZATION exttab1_su;
-- create readable external table auth_ext_test1(a int) location ('gpfdist://host:8000/file') format 'text';
-- SET SESSION AUTHORIZATION exttab1_u1;
-- create readable external table auth_ext_test2(a int) location ('gpfdist://host:8000/file') format 'text';
-- create writable external table auth_ext_test3(a int) location ('gpfdist://host:8000/file') format 'text'; -- fail
-- SET SESSION AUTHORIZATION exttab1_u2;
-- create writable external table auth_ext_test3(a int) location ('gpfdist://host:8000/file') format 'text';
-- RESET SESSION AUTHORIZATION;
-- ALTER ROLE exttab1_u2 NOCREATEEXTTABLE(protocol='gpfdist', type='writable');
-- SET SESSION AUTHORIZATION exttab1_u2;
-- create writable external table auth_ext_test4(a int) location ('gpfdist://host:8000/file') format 'text'; -- fail
-- RESET SESSION AUTHORIZATION;

-- drop external table auth_ext_test1;
-- drop external table auth_ext_test2;
-- drop external table auth_ext_test3;

-- DROP ROLE exttab1_su;
-- DROP ROLE exttab1_u1;
-- DROP ROLE exttab1_u2;

drop external table ext_whois;
drop external table gpfdist_csv_start;
drop external table check_echo;
drop external table wet_pos1;
drop external table wet_pos2;
drop external table wet_pos3;
drop external table wet_region;
drop external table ret_region;
drop table reg_region;

-- Tests for MPP-2513: dropping an external table must result in the deletion
-- of the relation pg_exttable row. This was done when using drop external
-- table but not drop schema cascade;

create schema exttabletest;
CREATE EXTERNAL TABLE exttabletest.EXT_NATION  ( N_NATIONKEY  INTEGER ,
                            N_NAME       CHAR(25) ,
                            N_REGIONKEY  INTEGER ,
                            N_COMMENT    VARCHAR(152))
location ('gpfdist://host:8000/nation.tbl' )
FORMAT 'text' (delimiter '|');
-- Don't just return the row, as we'll get skew from OID differences between
-- runs.
select count(*) from pg_catalog.pg_exttable where reloid in (select r.oid from pg_class r where r.relname in ('ext_nation', 'ext_whois', 'gpfdist_status'));
drop schema exttabletest cascade;
select count(*) from pg_catalog.pg_exttable where reloid in (select r.oid from pg_class r where r.relname in ('ext_nation', 'ext_whois', 'gpfdist_status'));
SET gp_foreign_data_access = false;

CREATE USER nonsuperproto;
GRANT SELECT ON PROTOCOL pxf TO nonsuperproto;
GRANT INSERT ON PROTOCOL pxf TO nonsuperproto;
REVOKE SELECT ON PROTOCOL pxf FROM nonsuperproto;
REVOKE INSERT ON PROTOCOL pxf FROM nonsuperproto;
DROP USER nonsuperproto;

-- start_ignore
select * from gpfdist_status;
select * from gpfdist_start;
select * from gpfdist_status;
-- end_ignore

-- Tests for MPP17980: generating artificial CTIDs for external table scans
-- This is necessary because the planner currently generates plans that include
-- the CTID attribute for external tables. 

CREATE EXTERNAL TABLE ext_mpp17980 ( id int , id1 int , id2 int)
LOCATION ('gpfdist://@hostname@:7070/mpp17980.data')
FORMAT 'CSV' ( DELIMITER ',' NULL ' ');

CREATE TABLE mpp17980 (id int, date date, amt decimal(10,2))
DISTRIBUTED randomly PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE  END (date '2009-01-01') EXCLUSIVE 
EVERY (INTERVAL '1 month') );

INSERT INTO mpp17980 VALUES ( 1,'2008-02-20',122.11);

SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( 1 ) ;  -- This returns 18 tuples

SELECT COUNT(*) FROM ext_mpp17980 WHERE id IN ( SELECT id FROM mpp17980 ) ; -- This should return 18 tuples but returns only 1

SELECT ctid, * FROM ext_mpp17980;

DROP EXTERNAL TABLE ext_mpp17980;
DROP TABLE mpp17980;

-- start_ignore
select * from gpfdist_status;
select * from gpfdist_stop;
select * from gpfdist_status;
-- end_ignore

drop external table gpfdist_status;
drop external table gpfdist_start;
drop external table gpfdist_stop;