-- No metadataprefix SERVER OPTION
CREATE SERVER oai_server_err1 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository');
-- No url SERVER OPTION
CREATE SERVER oai_server_err2 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (metadataprefix 'oai_dc');
ERROR:  required option 'url' is missing
-- No SERVER OPTION 
CREATE SERVER oai_server_err3 FOREIGN DATA WRAPPER oai_fdw;
ERROR:  required option 'url' is missing
-- Unknown SERVER OPTION
CREATE SERVER oai_server_err4 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         metadataprefix 'oai_dc',
         foo 'bar');  
ERROR:  invalid oai_fdw option "foo"
-- Wrong URL
CREATE SERVER oai_server_err5 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'a_very_wrong_url',
         metadataprefix 'oai_dc'); 
ERROR:  invalid url: 'a_very_wrong_url'
-- Wrong proxy URL
CREATE SERVER oai_server_err6 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         metadataprefix 'oai_dc',
         http_proxy 'a_very_wrong_url'); 
ERROR:  invalid http_proxy: 'a_very_wrong_url'
-- Empty timeout
CREATE SERVER oai_server_err7 FOREIGN DATA WRAPPER oai_fdw
OPTIONS (url 'https://services.dnb.de/oai/repository',
         metadataprefix 'oai_dc',
         connect_timeout '');  
ERROR:  empty value in option 'connect_timeout'
-- Nevative timeout
CREATE SERVER oai_server_err8 FOREIGN DATA WRAPPER oai_fdw
OPTIONS (url 'https://services.dnb.de/oai/repository',
         metadataprefix 'oai_dc',
         connect_timeout '-1');
ERROR:  invalid connect_timeout: -1
HINT:  expected values are positive integers (timeout in seconds)
         
-- Invalid timeout
CREATE SERVER oai_server_err9 FOREIGN DATA WRAPPER oai_fdw
OPTIONS (url 'https://services.dnb.de/oai/repository',
         metadataprefix 'oai_dc',
         connect_timeout 'foo');         
ERROR:  invalid connect_timeout: foo
HINT:  expected values are positive integers (timeout in seconds)
         
-- Empty metadataprefix
CREATE SERVER oai_server_err10 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         metadataprefix '');  
ERROR:  empty value in option 'metadataprefix'
         
-- Empty URL
CREATE SERVER oai_server_err11 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url '',
         metadataprefix 'oai_dc');  
ERROR:  empty value in option 'url'
-- URL without protocol
CREATE SERVER oai_server_err12 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'services.dnb.de/oai/repository',
         metadataprefix 'oai_dc');  
ERROR:  invalid url: 'services.dnb.de/oai/repository'
-- URL with an unsupported protocol
CREATE SERVER oai_server_err13 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'ftp://services.dnb.de/oai/repository',
         metadataprefix 'oai_dc');  
CREATE FOREIGN TABLE oai_table_err13 (
  status boolean         OPTIONS (oai_node 'status')
) 
SERVER oai_server_err13 OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err13 LIMIT 1;
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'ftp://services.dnb.de/oai/repository' failed (1)
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'ftp://services.dnb.de/oai/repository' failed (2)
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'ftp://services.dnb.de/oai/repository' failed (3)
ERROR:  ExecuteOAIRequest => (1) Protocol "ftp" not supported or disabled in libcurl

-- URL with an invalid protocol
CREATE SERVER oai_server_err14 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'foo://services.dnb.de/oai/repository',
         metadataprefix 'oai_dc'); 
ERROR:  invalid url: 'foo://services.dnb.de/oai/repository'
-- Empty connect_retry
CREATE SERVER oai_server_err15 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         connect_retry ''); 
ERROR:  empty value in option 'connect_retry'
         
-- Invalid connect_retry
CREATE SERVER oai_server_err15 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         connect_retry 'foo');
ERROR:  invalid connect_retry: foo
HINT:  expected values are positive integers (retry attempts in case of failure)
-- Unreachable proxy server
-- Timeout 1 second
-- Retry 5 times 
CREATE SERVER oai_server_err16 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         http_proxy 'http://proxy.server.im:8080',
         connect_timeout '1',
         connect_retry '5');
         
CREATE FOREIGN TABLE oai_table_err16 (
  id text OPTIONS (oai_node 'identifier')
) SERVER oai_server_err16 OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err16 LIMIT 1;
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'https://services.dnb.de/oai/repository' failed (1)
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'https://services.dnb.de/oai/repository' failed (2)
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'https://services.dnb.de/oai/repository' failed (3)
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'https://services.dnb.de/oai/repository' failed (4)
WARNING:    ExecuteOAIRequest (ListIdentifiers): request to 'https://services.dnb.de/oai/repository' failed (5)
ERROR:  ExecuteOAIRequest => (5) Could not resolve proxy: proxy.server.im

-- Unreachable proxy server
-- Timeout 1 second
-- Invalid retry (-5 times)
CREATE SERVER oai_server_err17 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         http_proxy 'http://proxy.server.im:8080',
         connect_timeout '1',
         connect_retry '-5');
ERROR:  invalid connect_retry: -5
HINT:  expected values are positive integers (retry attempts in case of failure)
-- Invalid 'request_redirect' value
CREATE SERVER oai_server_err18 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         request_redirect 'foo',
         request_max_redirect '1');
SELECT * FROM OAI_Identify('oai_server_err18');
ERROR:  request_redirect requires a Boolean value
-- Invalid 'request_redirect' value (empty)
CREATE SERVER oai_server_err19 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         request_redirect '',
         request_max_redirect '1');
ERROR:  empty value in option 'request_redirect'
SELECT * FROM OAI_Identify('oai_server_err18');
ERROR:  request_redirect requires a Boolean value
-- Invalid 'request_max_redirect' value
CREATE SERVER oai_server_err20 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         request_redirect 'true',
         request_max_redirect 'foo');
SELECT * FROM OAI_Identify('oai_server_err19');
ERROR:  FOREIGN SERVER does not exist: 'oai_server_err19'
-- Invalid 'request_max_redirect' value (empty)
CREATE SERVER oai_server_err21 FOREIGN DATA WRAPPER oai_fdw 
OPTIONS (url 'https://services.dnb.de/oai/repository',
         request_redirect 'true',
         request_max_redirect '');
ERROR:  empty value in option 'request_max_redirect'
SELECT * FROM OAI_Identify('oai_server_err21');
ERROR:  FOREIGN SERVER does not exist: 'oai_server_err21'
-- Unknown COLUMN OPTION value
CREATE FOREIGN TABLE oai_table_err1 (
  id text                OPTIONS (oai_node 'foo'),
  xmldoc xml             OPTIONS (oai_node 'content')
 ) 
 SERVER oai_server_ulb OPTIONS (setspec 'ulbmsuo',
                                metadataPrefix 'oai_dc');
ERROR:  invalid oai_node option 'foo'
HINT:  Valid column options for oai_fdw are:
CREATE SERVER: 'url', 'metadataprefix'
CREATE TABLE: 'metadataprefix','setspec', 'from', 'until' and 'status'
 
-- Empty COLUMN OPTION value
CREATE FOREIGN TABLE oai_table_err2 (
  id text                OPTIONS (oai_node ''),
  xmldoc xml             OPTIONS (oai_node 'content')
 ) 
 SERVER oai_server_ulb OPTIONS (setspec 'ulbmsuo',
                                metadataPrefix 'oai_dc');
ERROR:  empty value in option 'oai_node'
 
-- Empty SERVER OPTION value
CREATE FOREIGN TABLE oai_table_err3 (
  id text                OPTIONS (oai_node 'identifier'),
  xmldoc xml             OPTIONS (oai_node 'content')
 ) 
 SERVER oai_server_ulb OPTIONS (setspec '',
                                metadataPrefix 'oai_dc');
ERROR:  empty value in option 'setspec'
 
 -- Empty SERVER OPTION value
CREATE FOREIGN TABLE oai_table_err4 (
  id text                OPTIONS (oai_node 'identifier'),
  xmldoc xml             OPTIONS (foo 'content')
 ) 
 SERVER oai_server_ulb OPTIONS (setspec 'ulbmsuo',
                                metadataPrefix '');
ERROR:  empty value in option 'metadataprefix'
                                       
-- No record found: noRecordsMatch: The value of argument 'until' lies before argument 'from'
SELECT * FROM dnb_zdb_oai_dc
WHERE datestamp BETWEEN '2022-02-01' AND '2021-02-02';
WARNING:  OAI noRecordsMatch: The value of argument until '2021-02-02T00:00:00Z' lies before argument from '2022-02-01T00:00:00Z'
 id | content | setspec | datestamp | meta 
----+---------+---------+-----------+------
(0 rows)

-- cannotDisseminateFormat: foo
SELECT * FROM dnb_zdb_oai_dc
WHERE meta = 'foo';
ERROR:  OAI cannotDisseminateFormat: foo
-- badArgument: Unsupported set 'foo' !
SELECT * FROM dnb_zdb_oai_dc
WHERE setspec <@ ARRAY['foo'];
ERROR:  OAI badArgument: Unsupported set 'foo' !
-- GetRecord: wrong identifier format
SELECT * FROM dnb_zdb_oai_dc
WHERE id = 'foo';
WARNING:  OAI idDoesNotExist: String index out of range: -12
 id | content | setspec | datestamp | meta 
----+---------+---------+-----------+------
(0 rows)

-- GetRecord: identifier does not exist
SELECT * FROM dnb_zdb_oai_dc
WHERE id = 'oai:dnb.de/zdb/0000000000';
WARNING:  OAI idDoesNotExist: 
 id | content | setspec | datestamp | meta 
----+---------+---------+-----------+------
(0 rows)

-- Wrong data types
CREATE FOREIGN TABLE oai_table_err5 (
  id int                 OPTIONS (oai_node 'identifier')
 ) 
 SERVER oai_server_ulb OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err5 LIMIT 1;
ERROR:  invalid data type for 'oai_table_err5.id': 23
HINT:  OAI identifier must be of type 'text' or 'varchar'.
 
CREATE FOREIGN TABLE oai_table_err6 (
 doc int                 OPTIONS (oai_node 'content')
) 
SERVER oai_server_ulb OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err6 LIMIT 1;
ERROR:  invalid data type for 'oai_table_err6.doc': 23
HINT:  OAI content expects one of the following types: 'xml', 'text' or 'varchar'.
CREATE FOREIGN TABLE oai_table_err7 (
 sets varchar            OPTIONS (oai_node 'setspec')
) 
SERVER oai_server_ulb OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err7 LIMIT 1;
ERROR:  invalid data type for 'oai_table_err7.sets': 1043
HINT:  OAI setspec expects one of the following types: 'text[]', 'varchar[]'.
CREATE FOREIGN TABLE oai_table_err8 (
 updatedate text   OPTIONS (oai_node 'datestamp')
) 
SERVER oai_server_ulb OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err8 LIMIT 1;
ERROR:  invalid data type for 'oai_table_err8.updatedate': 25
HINT:  OAI datestamp expects a 'timestamp'.
CREATE FOREIGN TABLE oai_table_err9 (
   format xml            OPTIONS (oai_node 'metadataprefix')
) 
SERVER oai_server_ulb OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err9 LIMIT 1;
ERROR:  invalid data type for 'oai_table_err9.format': 142
HINT:  OAI metadataprefix must be of type 'text' or 'varchar'.
CREATE FOREIGN TABLE oai_table_err10 (
  status text         OPTIONS (oai_node 'status')
) 
SERVER oai_server_ulb OPTIONS (metadataPrefix 'oai_dc');
SELECT * FROM oai_table_err10 LIMIT 1;
ERROR:  invalid data type for 'oai_table_err10.status': 25
HINT:  OAI status must be of type 'boolean'.
-- OAI_ListMetadataFormats: Wrong FOREIGN SERVER
SELECT * FROM OAI_ListMetadataFormats('foo');
ERROR:  FOREIGN SERVER does not exist: 'foo'
-- OAI_ListMetadataFormats: NULL FOREIGN SERVER
SELECT * FROM OAI_ListMetadataFormats(NULL);
 metadataprefix | schema | metadatanamespace 
----------------+--------+-------------------
(0 rows)

-- OAI_ListSets: Wrong FOREIGN SERVER
SELECT * FROM OAI_ListSets('foo');
ERROR:  FOREIGN SERVER does not exist: 'foo'
-- OAI_ListSes: NULL FOREIGN SERVER
SELECT * FROM OAI_ListSets(NULL);
 setspec | setname 
---------+---------
(0 rows)

-- OAI_Identify: Wrong FOREIGN SERVER
SELECT * FROM OAI_Identify('foo');
ERROR:  FOREIGN SERVER does not exist: 'foo'
-- OAI_Identify: NULL FOREIGN SERVER
SELECT * FROM OAI_Identify(NULL);
 name | description 
------+-------------
(0 rows)

CREATE SCHEMA exception_schema;
-- IMPORT FOREIGN SCHEMA without 'metadataprerifx' OPTION
IMPORT FOREIGN SCHEMA oai_sets FROM SERVER oai_server_ulb INTO exception_schema;
ERROR:  missing 'metadataprefix' OPTION.
HINT:  A OAI Foreign Table must have a fixed 'metadataprefix'. Execute 'SELECT * FROM OAI_ListMetadataFormats('oai_server_ulb')' to see which formats are offered in the OAI Repository.
-- IMPORT FOREIGN SCHEMA with invalid 'metadataprerifx' OPTION
IMPORT FOREIGN SCHEMA oai_sets FROM SERVER oai_server_ulb INTO exception_schema OPTIONS (metadataprefix 'foo');
ERROR:  invalid 'metadataprefix': 'foo'
-- IMPORT FOREIGN SCHEMA with unknown OPTION
IMPORT FOREIGN SCHEMA oai_sets FROM SERVER oai_server_ulb INTO exception_schema OPTIONS (metadataprefix 'oai_dc', foo 'bar');
ERROR:  invalid FOREIGN SCHEMA OPTION: 'foo'
-- IMPORT FOREIGN SCHEMA with unknown foreign schema
IMPORT FOREIGN SCHEMA foo FROM SERVER oai_server_ulb INTO exception_schema OPTIONS (metadataprefix 'oai_dc');
ERROR:  invalid FOREIGN SCHEMA: 'foo'
-- UPDATE query
UPDATE ulb_ulbmsuo_oai_dc SET status = true;
ERROR:  Operation not supported.
HINT:  The OAI Foreign Data Wrapper does not support UPDATE queries.
-- INSERT query
INSERT INTO ulb_ulbmsuo_oai_dc (status) VALUES (false);
ERROR:  Operation not supported.
HINT:  The OAI Foreign Data Wrapper does not support INSERT queries.
-- DELETE query
DELETE FROM ulb_ulbmsuo_oai_dc;
ERROR:  Operation not supported.
HINT:  The OAI Foreign Data Wrapper does not support DELETE queries.
-- empty user name
CREATE USER MAPPING FOR postgres SERVER oai_server_ulb OPTIONS (user '', password 'foo');
ERROR:  empty value in option 'user'
-- empty password
CREATE USER MAPPING FOR postgres SERVER oai_server_ulb OPTIONS (user 'foo', password '');
ERROR:  empty value in option 'password'
-- invalid option
CREATE USER MAPPING FOR postgres SERVER oai_server_ulb OPTIONS (user 'jim', foo 'bar');
ERROR:  invalid oai_fdw option "foo"