1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219
|
\set MYSQL_HOST `echo \'"$MYSQL_HOST"\'`
\set MYSQL_PORT `echo \'"$MYSQL_PORT"\'`
\set MYSQL_USER_NAME `echo \'"$MYSQL_USER_NAME"\'`
\set MYSQL_PASS `echo \'"$MYSQL_PWD"\'`
-- Before running this file User must create database mysql_fdw_regress on
-- MySQL with all permission for MYSQL_USER_NAME user with MYSQL_PWD password
-- and ran mysql_init.sh file to create tables.
\c contrib_regression
CREATE EXTENSION IF NOT EXISTS mysql_fdw;
CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR public SERVER mysql_svr
OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
-- Validate extension, server and mapping details
CREATE OR REPLACE FUNCTION show_details(host TEXT, port TEXT, uid TEXT, pwd TEXT) RETURNS int AS $$
DECLARE
ext TEXT;
srv TEXT;
sopts TEXT;
uopts TEXT;
BEGIN
SELECT e.fdwname, srvname, array_to_string(s.srvoptions, ','), array_to_string(u.umoptions, ',')
INTO ext, srv, sopts, uopts
FROM pg_foreign_data_wrapper e LEFT JOIN pg_foreign_server s ON e.oid = s.srvfdw LEFT JOIN pg_user_mapping u ON s.oid = u.umserver
WHERE e.fdwname = 'mysql_fdw'
ORDER BY 1, 2, 3, 4;
raise notice 'Extension : %', ext;
raise notice 'Server : %', srv;
IF strpos(sopts, host) <> 0 AND strpos(sopts, port) <> 0 THEN
raise notice 'Server_Options : matched';
END IF;
IF strpos(uopts, uid) <> 0 AND strpos(uopts, pwd) <> 0 THEN
raise notice 'User_Mapping_Options : matched';
END IF;
return 1;
END;
$$ language plpgsql;
SELECT show_details(:MYSQL_HOST, :MYSQL_PORT, :MYSQL_USER_NAME, :MYSQL_PASS);
-- Create foreign table and perform basic SQL operations
CREATE FOREIGN TABLE f_mysql_test(a int, b int)
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'mysql_test');
SELECT a, b FROM f_mysql_test ORDER BY 1, 2;
INSERT INTO f_mysql_test (a, b) VALUES (2, 2);
SELECT a, b FROM f_mysql_test ORDER BY 1, 2;
UPDATE f_mysql_test SET b = 3 WHERE a = 2;
SELECT a, b FROM f_mysql_test ORDER BY 1, 2;
DELETE FROM f_mysql_test WHERE a = 2;
SELECT a, b FROM f_mysql_test ORDER BY 1, 2;
DROP FOREIGN TABLE f_mysql_test;
DROP USER MAPPING FOR public SERVER mysql_svr;
DROP SERVER mysql_svr;
-- Server with init_command.
CREATE SERVER mysql_svr1 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT, init_command 'create table init_command_check(a int)');
CREATE USER MAPPING FOR public SERVER mysql_svr1
OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
CREATE FOREIGN TABLE f_mysql_test (a int, b int)
SERVER mysql_svr1 OPTIONS (dbname 'mysql_fdw_regress', table_name 'mysql_test');
-- This will create init_command_check table in mysql_fdw_regress database.
SELECT a, b FROM f_mysql_test ORDER BY 1, 2;
-- init_command_check table created mysql_fdw_regress database can be verified
-- by creating corresponding foreign table here.
CREATE FOREIGN TABLE f_init_command_check(a int)
SERVER mysql_svr1 OPTIONS (dbname 'mysql_fdw_regress', table_name 'init_command_check');
SELECT a FROM f_init_command_check ORDER BY 1;
-- Changing init_command to drop init_command_check table from
-- mysql_fdw_regress database
ALTER SERVER mysql_svr1 OPTIONS (SET init_command 'drop table init_command_check');
SELECT a, b FROM f_mysql_test;
DROP FOREIGN TABLE f_init_command_check;
DROP FOREIGN TABLE f_mysql_test;
DROP USER MAPPING FOR public SERVER mysql_svr1;
DROP SERVER mysql_svr1;
-- Server with use_remote_estimate.
CREATE SERVER mysql_svr1 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS(host :MYSQL_HOST, port :MYSQL_PORT, use_remote_estimate 'TRUE');
CREATE USER MAPPING FOR public SERVER mysql_svr1
OPTIONS(username :MYSQL_USER_NAME, password :MYSQL_PASS);
CREATE FOREIGN TABLE f_mysql_test(a int, b int)
SERVER mysql_svr1 OPTIONS(dbname 'mysql_fdw_regress', table_name 'mysql_test');
-- Below explain will return actual rows from MySQL, but keeping costs off
-- here for consistent regression result.
EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM f_mysql_test WHERE a < 2 ORDER BY 1;
DROP FOREIGN TABLE f_mysql_test;
DROP USER MAPPING FOR public SERVER mysql_svr1;
DROP SERVER mysql_svr1;
-- Create server with secure_auth.
CREATE SERVER mysql_svr1 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS(host :MYSQL_HOST, port :MYSQL_PORT, secure_auth 'FALSE');
CREATE USER MAPPING FOR public SERVER mysql_svr1
OPTIONS(username :MYSQL_USER_NAME, password :MYSQL_PASS);
CREATE FOREIGN TABLE f_mysql_test(a int, b int)
SERVER mysql_svr1 OPTIONS(dbname 'mysql_fdw_regress', table_name 'mysql_test');
-- Below should fail with Warning of secure_auth is false.
SELECT a, b FROM f_mysql_test ORDER BY 1, 2;
DROP FOREIGN TABLE f_mysql_test;
DROP USER MAPPING FOR public SERVER mysql_svr1;
DROP SERVER mysql_svr1;
-- FDW-335: Support for fetch_size option at server level and table level.
CREATE SERVER fetch101 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS( fetch_size '101' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'fetch101'
AND srvoptions @> array['fetch_size=101'];
ALTER SERVER fetch101 OPTIONS( SET fetch_size '202' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'fetch101'
AND srvoptions @> array['fetch_size=101'];
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'fetch101'
AND srvoptions @> array['fetch_size=202'];
CREATE FOREIGN TABLE table30000 ( x int ) SERVER fetch101
OPTIONS ( fetch_size '30000' );
SELECT COUNT(*)
FROM pg_foreign_table
WHERE ftrelid = 'table30000'::regclass
AND ftoptions @> array['fetch_size=30000'];
ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '60000');
SELECT COUNT(*)
FROM pg_foreign_table
WHERE ftrelid = 'table30000'::regclass
AND ftoptions @> array['fetch_size=30000'];
SELECT COUNT(*)
FROM pg_foreign_table
WHERE ftrelid = 'table30000'::regclass
AND ftoptions @> array['fetch_size=60000'];
-- Make sure that changing the table level fetch-size value did not change the
-- server level value.
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'fetch101'
AND srvoptions @> array['fetch_size=202'];
-- Negative test cases for fetch_size option, should error out.
-- Debian: error message varies on 32-bit, disable here since upstream is unwilling to fix it
-- https://github.com/EnterpriseDB/mysql_fdw/pull/227
--ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '-60000');
--ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '123abc');
--ALTER FOREIGN TABLE table30000 OPTIONS ( SET fetch_size '999999999999999999999');
-- Cleanup fetch_size test objects.
DROP FOREIGN TABLE table30000;
DROP SERVER fetch101;
-- FDW-350: Support for reconnect option at server level.
CREATE SERVER reconnect1 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS( reconnect 'true' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'reconnect1'
AND srvoptions @> array['reconnect=true'];
ALTER SERVER reconnect1 OPTIONS( SET reconnect 'false' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'reconnect1'
AND srvoptions @> array['reconnect=false'];
-- Negative test case for reconnect option, should error out.
ALTER SERVER reconnect1 OPTIONS ( SET reconnect 'abc1' );
-- Cleanup reconnect option test objects.
DROP SERVER reconnect1;
-- FDW-404: Support for character_set option at server level.
CREATE SERVER charset101 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS( character_set 'utf8' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'charset101'
AND srvoptions @> array['character_set=utf8'];
ALTER SERVER charset101 OPTIONS( SET character_set 'latin' );
SELECT count(*)
FROM pg_foreign_server
WHERE srvname = 'charset101'
AND srvoptions @> array['character_set=latin'];
-- Cleanup character_set test objects.
DROP SERVER charset101;
-- Cleanup
DROP EXTENSION mysql_fdw;
|