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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
|
\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);
CREATE SERVER mysql_svr1 FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR public SERVER mysql_svr1
OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
-- Create foreign tables and insert data.
CREATE FOREIGN TABLE fdw519_ft1(stu_id int, stu_name varchar(255), stu_dept int)
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress1', table_name 'student');
CREATE FOREIGN TABLE fdw519_ft2(c1 INTEGER, c2 VARCHAR(14), c3 VARCHAR(13))
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test_tbl2');
CREATE FOREIGN TABLE fdw519_ft3 (c1 INTEGER, c2 VARCHAR(10), c3 CHAR(9), c4 BIGINT, c5 pg_catalog.Date, c6 DECIMAL, c7 INTEGER, c8 SMALLINT)
SERVER mysql_svr1 OPTIONS (dbname 'mysql_fdw_regress', table_name 'test_tbl1');
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
INSERT INTO fdw519_ft2 VALUES(10, 'DEVELOPMENT', 'PUNE');
INSERT INTO fdw519_ft2 VALUES(20, 'ADMINISTRATION', 'BANGLORE');
INSERT INTO fdw519_ft3 VALUES (100, 'EMP1', 'ADMIN', 1300, '1980-12-17', 800.23, NULL, 20);
INSERT INTO fdw519_ft3 VALUES (200, 'EMP2', 'SALESMAN', 600, '1981-02-20', 1600.00, 300, 30);
-- Check truncatable option with invalid values.
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (ADD truncatable 'abc');
ERROR: invalid option "truncatable"
HINT: Valid options in this context are: host, port, init_command, secure_auth, use_remote_estimate, fetch_size, reconnect, character_set, mysql_default_file, sql_mode, ssl_key, ssl_cert, ssl_ca, ssl_capath, ssl_cipher
ALTER FOREIGN TABLE fdw519_ft1 OPTIONS (ADD truncatable 'abc');
ERROR: invalid option "truncatable"
HINT: Valid options in this context are: dbname, table_name, max_blob_size, fetch_size
-- Default behavior, should truncate.
TRUNCATE fdw519_ft1;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
ERROR: failed to execute the MySQL query:
Duplicate entry '1' for key 'student.PRIMARY'
-- Set truncatable to false
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (ADD truncatable 'false');
ERROR: invalid option "truncatable"
HINT: Valid options in this context are: host, port, init_command, secure_auth, use_remote_estimate, fetch_size, reconnect, character_set, mysql_default_file, sql_mode, ssl_key, ssl_cert, ssl_ca, ssl_capath, ssl_cipher
-- Truncate the table.
TRUNCATE fdw519_ft1;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- Set truncatable to true
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'true');
ERROR: option "truncatable" not found
TRUNCATE fdw519_ft1;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- truncatable to true on Server but false on table level.
-- Since truncatable option is available since v14, this gives an error on v13
-- and previous versions.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'false');
ERROR: option "truncatable" not found
ALTER TABLE fdw519_ft2 OPTIONS (ADD truncatable 'true');
ERROR: invalid option "truncatable"
HINT: Valid options in this context are: dbname, table_name, max_blob_size, fetch_size
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
TRUNCATE fdw519_ft2;
ERROR: "fdw519_ft2" is not a table
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
ERROR: failed to execute the MySQL query:
Duplicate entry '1' for key 'student.PRIMARY'
INSERT INTO fdw519_ft2 VALUES(10, 'DEVELOPMENT', 'PUNE');
ERROR: failed to execute the MySQL query:
Duplicate entry '10' for key 'test_tbl2.PRIMARY'
INSERT INTO fdw519_ft2 VALUES(20, 'ADMINISTRATION', 'BANGLORE');
ERROR: failed to execute the MySQL query:
Duplicate entry '20' for key 'test_tbl2.PRIMARY'
-- truncatable to true on Server but false on one table and true for other
-- table.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'true');
ERROR: option "truncatable" not found
ALTER TABLE fdw519_ft1 OPTIONS (ADD truncatable 'false');
ERROR: invalid option "truncatable"
HINT: Valid options in this context are: dbname, table_name, max_blob_size, fetch_size
ALTER TABLE fdw519_ft2 OPTIONS (SET truncatable 'true');
ERROR: option "truncatable" not found
TRUNCATE fdw519_ft1, fdw519_ft2;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
-- truncatable to false on Server but false on one table and true for other
-- table.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'false');
ERROR: option "truncatable" not found
ALTER TABLE fdw519_ft1 OPTIONS (SET truncatable 'false');
ERROR: option "truncatable" not found
ALTER TABLE fdw519_ft2 OPTIONS (SET truncatable 'true');
ERROR: option "truncatable" not found
TRUNCATE fdw519_ft1, fdw519_ft2;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
-- Truncate from different servers.
ALTER SERVER mysql_svr OPTIONS (SET truncatable 'true');
ERROR: option "truncatable" not found
ALTER SERVER mysql_svr1 OPTIONS (ADD truncatable 'true');
ERROR: invalid option "truncatable"
HINT: Valid options in this context are: host, port, init_command, secure_auth, use_remote_estimate, fetch_size, reconnect, character_set, mysql_default_file, sql_mode, ssl_key, ssl_cert, ssl_ca, ssl_capath, ssl_cipher
ALTER TABLE fdw519_ft1 OPTIONS (SET truncatable 'true');
ERROR: option "truncatable" not found
TRUNCATE fdw519_ft1, fdw519_ft2, fdw519_ft3;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
SELECT * FROM fdw519_ft2 ORDER BY 1;
c1 | c2 | c3
----+----------------+----------
10 | DEVELOPMENT | PUNE
20 | ADMINISTRATION | BANGLORE
(2 rows)
SELECT * FROM fdw519_ft3 ORDER BY 1;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
-----+------+-----------+------+------------+------------+-----+----
100 | EMP1 | ADMIN | 1300 | 12-17-1980 | 800.23000 | | 20
200 | EMP2 | SALESMAN | 600 | 02-20-1981 | 1600.00000 | 300 | 30
(2 rows)
INSERT INTO fdw519_ft1 VALUES(1, 'One', 101);
ERROR: failed to execute the MySQL query:
Duplicate entry '1' for key 'student.PRIMARY'
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- Truncate with CASCADE is not supported.
TRUNCATE fdw519_ft1 CASCADE;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- Default is RESTRICT, so it is allowed.
TRUNCATE fdw519_ft1 RESTRICT;
ERROR: "fdw519_ft1" is not a table
SELECT * FROM fdw519_ft1 ORDER BY 1;
stu_id | stu_name | stu_dept
--------+----------+----------
1 | One | 101
(1 row)
-- Should throw an error if primary key is referenced by foreign key.
CREATE FOREIGN TABLE fdw519_ft4(stu_id varchar(10), stu_name varchar(255), stu_dept int)
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress1', table_name 'student1');
CREATE FOREIGN TABLE fdw519_ft5(dept_id int, stu_id varchar(10))
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress1', table_name 'dept');
TRUNCATE fdw519_ft4;
ERROR: "fdw519_ft4" is not a table
-- FDW-520: Support generated columns in IMPORT FOREIGN SCHEMA command.
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520)
FROM SERVER mysql_svr INTO public OPTIONS (import_generated 'true');
ERROR: invalid option "import_generated"
\d fdw520
-- Generated column refers to another generated column, should throw an error:
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520_1)
FROM SERVER mysql_svr INTO public OPTIONS (import_generated 'true');
ERROR: invalid option "import_generated"
-- import_generated as false.
DROP FOREIGN TABLE fdw520;
ERROR: foreign table "fdw520" does not exist
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520)
FROM SERVER mysql_svr INTO public OPTIONS (import_generated 'false');
ERROR: invalid option "import_generated"
\d fdw520
-- Without import_generated option, default is true.
DROP FOREIGN TABLE fdw520;
ERROR: foreign table "fdw520" does not exist
IMPORT FOREIGN SCHEMA mysql_fdw_regress LIMIT TO (fdw520)
FROM SERVER mysql_svr INTO public;
\d fdw520
Foreign table "public.fdw520"
Column | Type | Collation | Nullable | Default | FDW options
---------+---------+-----------+----------+---------+-------------
c1 | integer | | not null | |
c `"" 2 | integer | | | |
c3 | integer | | | |
c4 | integer | | not null | |
Server: mysql_svr
FDW options: (dbname 'mysql_fdw_regress', table_name 'fdw520')
-- FDW-521: Insert and update operations on table having generated columns.
INSERT INTO fdw520(c1, "c `"""" 2") VALUES(1, 2);
ERROR: failed to prepare the MySQL query:
The value specified for generated column 'c3' in table 'fdw520' is not allowed.
INSERT INTO fdw520(c1, "c `"""" 2", c3, c4) VALUES(2, 4, DEFAULT, DEFAULT);
ERROR: failed to prepare the MySQL query:
The value specified for generated column 'c3' in table 'fdw520' is not allowed.
-- Should fail.
INSERT INTO fdw520 VALUES(1, 2, 3, 4);
ERROR: failed to prepare the MySQL query:
The value specified for generated column 'c3' in table 'fdw520' is not allowed.
SELECT * FROM fdw520 ORDER BY 1;
c1 | c `"" 2 | c3 | c4
----+---------+----+----
(0 rows)
UPDATE fdw520 SET "c `"""" 2" = 20 WHERE c1 = 2;
SELECT * FROM fdw520 ORDER BY 1;
c1 | c `"" 2 | c3 | c4
----+---------+----+----
(0 rows)
-- Should fail.
UPDATE fdw520 SET c4 = 20 WHERE c1 = 2;
ERROR: failed to prepare the MySQL query:
The value specified for generated column 'c4' in table 'fdw520' is not allowed.
UPDATE fdw520 SET c3 = 20 WHERE c1 = 2;
ERROR: failed to prepare the MySQL query:
The value specified for generated column 'c3' in table 'fdw520' is not allowed.
-- Cleanup
DELETE FROM fdw519_ft1;
DELETE FROM fdw519_ft2;
DELETE FROM fdw519_ft3;
DELETE FROM fdw520;
DROP FOREIGN TABLE fdw519_ft1;
DROP FOREIGN TABLE fdw519_ft2;
DROP FOREIGN TABLE fdw519_ft3;
DROP FOREIGN TABLE fdw519_ft4;
DROP FOREIGN TABLE fdw519_ft5;
DROP FOREIGN TABLE fdw520;
DROP USER MAPPING FOR public SERVER mysql_svr;
DROP SERVER mysql_svr;
DROP USER MAPPING FOR public SERVER mysql_svr1;
DROP SERVER mysql_svr1;
DROP EXTENSION mysql_fdw;
|
