------------------------------------------------------------------test server start--------------------------------------------
---------------------test positive options
----test create server
drop server if exists  obs_server cascade;
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@',
type 'obs'
);

---domain option
drop server if exists  obs_server cascade;
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@',
type 'obs'
);

drop server hdfs_server cascade;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.185.178.241:25000,10.185.178.239:25000',hdfscfgpath '/opt/config', type 'hdfs');
drop server hdfs_server cascade;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (hdfscfgpath '/opt/config', type 'hdfs');

----test alter server
--alter server obs_server options (set address '10.175.38.75');
alter server obs_server options (set encrypt 'on');
alter server obs_server options (set access_key 'ak');
alter server obs_server options (set secret_access_key 'ask');
alter server obs_server options (set type 'obs');
alter server obs_server options (drop encrypt);
alter server obs_server options (add encrypt 'off');
alter server obs_server rename to new_obs_server;
create user new_user password 'Gauss@123';
alter server new_obs_server owner to new_user;
drop server new_obs_server;
drop user new_user;
---------------------test positive options end
---------------------test negative options
---test create server
drop server if exists  obs_server cascade;
--lack type
create server obs_server foreign data wrapper dfs_fdw options (
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@'
);
--lack access_key
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
secret_access_key '@sk@',
type 'obs'
);
--lack secret_access_key
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
type 'obs'
);
--lack type
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@'
);
--unacquainted option
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@',
type 'obs',
hdfscfgpath '/hanfeng'
);

create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@',
type 'obs',
location '/hanfeng'
);

select pg_sleep(5);
-- incorrect option
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key 'PXOCFVMGOMNOJSAWI2I',
secret_access_key '@sk@',
type 'obs'
);
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key 'FiqwtDDeUCtsieCbOCWzrQ3fwJHFJetzzXbdLHE',
type 'obs'
);
---test alter server
drop server if exists obs_server cascade;
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@',
type 'obs'
);

alter server obs_server options (drop address);
alter server obs_server options (drop access_key);
alter server obs_server options (drop secret_access_key);
alter server obs_server options (drop type);
alter server obs_server options (add address '10.185.178.239');
alter server obs_server options (add access_key 'ak');
alter server obs_server options (add secret_access_key 'sak');
alter server obs_server options (add type 'obs');
alter server obs_server options (set type 'hdfs');

--test hdfs server
drop server hdfs_server cascade;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (hdfscfgpath '/opt/config');
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (type 'hdfs');
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (type 'hdfs', hdfscfgpath '/opt/config', encrypt 'off');
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (type 'hdfs', hdfscfgpath '/opt/config', access_key '@ak@');

drop server hdfs_server cascade;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (hdfscfgpath '/opt/config', type 'HDFS');
alter server hdfs_server options(add address '10.185.78.20:25000');
alter server hdfs_server options(add path 'asdf');
alter server hdfs_server options(set address '10');
alter server hdfs_server options(set hdfscfgpath '/han');
alter server hdfs_server options(set type 'hdfs');
alter server hdfs_server options(drop hdfscfgpath );
alter server hdfs_server options(drop address );

------------------------------------------------------------------test server end------------------------------------------
------------------------------------------------------------------test obs foreign table start------------------------------------------
drop server if exists  obs_server cascade;
create server obs_server foreign data wrapper dfs_fdw options (
address '@obshostname@',
encrypt 'off',
access_key '@ak@',
secret_access_key '@sk@',
type 'obs'
);
---------------------test positive
drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/')
distribute by roundrobin
partition by (b, c);
--muti-bucket
drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/, /gaussdb/part_3_2/')
distribute by roundrobin
partition by (b, c);
--test alter server
alter foreign table part_3 options (set encoding 'gbk');
alter foreign table part_3 options (set foldername '/hanfeng/huawei/');

alter foreign table part_3 alter column b set data type int;
alter foreign table part_3 alter column b set not null;
alter foreign table part_3 alter column b set statistics 600;


drop user if exists new_user;
create user new_user password 'Gauss@123';
alter foreign table part_3 owner to new_user;
drop foreign table part_3;
drop user new_user;

---------------------test negative
--incorrect option
--encodingsss
drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encodingsss 'utf-8',format 'orc', foldername '/gaussdb/part_3/')
distribute by roundrobin
partition by (b, c);

--filenames
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/', filenames '/hanfeng/hanfengff')
distribute by roundrobin
partition by (b, c);

--filenames
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/', filenames '/hanfeng/hanfengff')
distribute by roundrobin;
--lack option
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(format 'orc', foldername '/gaussdb/part_3/')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8', foldername '/gaussdb/part_3/')
distribute by roundrobin
partition by (b, c);

drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc')
distribute by roundrobin
partition by (b, c);
--incorrect option value
drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'text', foldername '/gaussdb/part_3/')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername 'gaussdb/part_3/')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername 'gaussdb/part_3/, /hanfeng')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3, /hanfeng/')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/, hanfeng/')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/, /hanfeng')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/, /hanfeng/, ')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '//')
distribute by roundrobin
partition by (b, c);


create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/, /k')
distribute by roundrobin
partition by (b, c);

create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/part_3/, k/')
distribute by roundrobin
partition by (b, c);

--error encoding
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'asd',format 'orc', foldername '/gaussdb/part_3/')
distribute by roundrobin
partition by (b, c);
--repliction disritbute
drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/non_exists/')
distribute by replication
partition by (b, c);

drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/non_exists/')
distribute by hash(a)
partition by (b, c);

--alter foreign table 
drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/gaussdb/non_exists/')
distribute by roundrobin
partition by (b, c);
alter foreign table part_3 add column d  int;
alter foreign table part_3 drop column b;
alter foreign table part_3 rename to new_part_3;

alter foreign table part_3 options (set format 'txt');
alter foreign table part_3 options (drop format);
alter foreign table part_3 options (drop encoding);
alter foreign table part_3 options (drop foldername);
alter foreign table part_3 options( add filenames '/han/');

----test hdfs foreign table
drop server hdfs_server cascade;
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (address '10.185.178.241:25000,10.185.178.239:25000',hdfscfgpath '/opt/config', type 'hdfs');
drop foreign table if exists region020;
create foreign table region020  (a int) SERVER  hdfs_server 
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe', encoding 'GBK')
distribute by replication;
--
alter foreign table region020 options(drop encoding);

alter foreign table region020 options(drop foldername);
alter foreign table region020 options(set format 'text');
alter foreign table region020 options(set foldername '/gaussdb/part_3/,hanfeng/');

drop foreign table if exists region020;
create foreign table region020  (a int) SERVER  hdfs_server 
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe, /han', encoding 'GBK')
distribute by replication;

drop foreign table if exists region020;
create foreign table region020  (a int) SERVER  hdfs_server 
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe,/han', encoding 'GBK')
distribute by replication;

create foreign table region020  (a int) SERVER  hdfs_server 
OPTIONS(format 'orc', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe,/han', encodingxxx 'GBK')
distribute by replication;

------------------------test select
----test muti-bucket
drop foreign table part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/@obsbucket@/obs_ddl/part_3/, /@obsbucket@/obs_ddl/part_3_2/')
distribute by roundrobin
partition by (b, c) automapped;
select * from part_3;

---test non-exists key
drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/@obsbucket@/non_exists/')
distribute by roundrobin
partition by (b, c);
select * from part_3;

drop foreign table if exists part_3;
create foreign table part_3 (a int, b varchar(20), c date)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/@obsbucket@/')
distribute by roundrobin
partition by (b, c);
select * from part_3;

--test 4 partitions
drop foreign table part_3;
create foreign table part_3 (a int, b varchar(20), c date, d int,  e int)
SERVER obs_server OPTIONS(encoding 'utf-8',format 'orc', foldername '/@obsbucket@/obs_ddl/four_partitoin/')
distribute by roundrobin
partition by (b, c, d, e);
select * from part_3;

drop server obs_server cascade;
drop server hdfs_server cascade;
--add llt
drop foreign table if exists llt_001;
create foreign table llt_001(
a int,
b int
)
SERVER gsmpp_server OPTIONS (
    location 'gsobs://10.175.38.120/gaussdbcheck/obscheck/test_rescan| ',
    format 'text',
    encoding 'utf8',
    chunksize '32',
    encrypt 'on',
    access_key '@ak@',
    secret_access_key '@sk@',
compatible_illegal_chars 'true'
) read only;

drop foreign table if exists llt_001;
create foreign table llt_001(
a int,
b int
)
SERVER gsmpp_server OPTIONS (
    location 'gsobs://gaussdbcheck.obs.myhwclouds.com/obscheck/test_rescan|dfs',
    format 'text',
    encoding 'utf8',
    chunksize '32',
    encrypt 'on',
    access_key '@ak@',
    secret_access_key '@sk@',
compatible_illegal_chars 'true'
) read only;
drop foreign table if exists llt_001;