File: server_options.sql

package info (click to toggle)
postgresql-mysql-fdw 2.9.2-2
  • links: PTS, VCS
  • area: main
  • in suites: sid, trixie
  • size: 1,272 kB
  • sloc: ansic: 5,926; sql: 1,987; sh: 84; makefile: 42
file content (219 lines) | stat: -rw-r--r-- 7,863 bytes parent folder | download | duplicates (2)
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;