1 /*-------------------------------------------------------------------------
2 *
3 * mysql_query.c
4 * Type handling for remote MySQL servers
5 *
6 * Portions Copyright (c) 2012-2014, PostgreSQL Global Development Group
7 * Portions Copyright (c) 2004-2021, EnterpriseDB Corporation.
8 *
9 * IDENTIFICATION
10 * mysql_query.c
11 *
12 *-------------------------------------------------------------------------
13 */
14 #include "postgres.h"
15
16 /*
17 * Must be included before mysql.h as it has some conflicting definitions like
18 * list_length, etc.
19 */
20 #include "mysql_fdw.h"
21
22 #include <mysql.h>
23 #include <stdio.h>
24 #include <sys/stat.h>
25 #include <unistd.h>
26
27 #include "access/htup_details.h"
28 #include "catalog/pg_type.h"
29 #include "mysql_query.h"
30 #if PG_VERSION_NUM < 120000
31 #include "optimizer/var.h"
32 #else
33 #include "optimizer/optimizer.h"
34 #endif
35 #include "utils/builtins.h"
36 #include "utils/date.h"
37 #include "utils/datetime.h"
38 #include "utils/lsyscache.h"
39 #include "utils/syscache.h"
40
41 #define DATE_MYSQL_PG(x, y) \
42 do { \
43 x->year = y.tm_year; \
44 x->month = y.tm_mon; \
45 x->day= y.tm_mday; \
46 x->hour = y.tm_hour; \
47 x->minute = y.tm_min; \
48 x->second = y.tm_sec; \
49 } while(0);
50
51 static int32 mysql_from_pgtyp(Oid type);
52 static int dec_bin(int number);
53 static int bin_dec(int binarynumber);
54
55
56 /*
57 * convert_mysql_to_pg:
58 * Convert MySQL data into PostgreSQL's compatible data types
59 */
60 Datum
mysql_convert_to_pg(Oid pgtyp,int pgtypmod,mysql_column * column)61 mysql_convert_to_pg(Oid pgtyp, int pgtypmod, mysql_column *column)
62 {
63 Datum value_datum;
64 Datum valueDatum;
65 regproc typeinput;
66 HeapTuple tuple;
67 int typemod;
68 char str[MAXDATELEN];
69
70 /* get the type's output function */
71 tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(pgtyp));
72 if (!HeapTupleIsValid(tuple))
73 elog(ERROR, "cache lookup failed for type%u", pgtyp);
74
75 typeinput = ((Form_pg_type) GETSTRUCT(tuple))->typinput;
76 typemod = ((Form_pg_type) GETSTRUCT(tuple))->typtypmod;
77 ReleaseSysCache(tuple);
78
79 switch (pgtyp)
80 {
81 /*
82 * MySQL gives BIT / BIT(n) data type as decimal value. The only way
83 * to retrieve this value is to use BIN, OCT or HEX function in MySQL,
84 * otherwise mysql client shows the actual decimal value, which could
85 * be a non - printable character. For exmple in MySQL
86 *
87 * CREATE TABLE t (b BIT(8));
88 * INSERT INTO t SET b = b'1001';
89 * SELECT BIN(b) FROM t;
90 * +--------+
91 * | BIN(b) |
92 * +--------+
93 * | 1001 |
94 * +--------+
95 *
96 * PostgreSQL expacts all binary data to be composed of either '0' or
97 * '1'. MySQL gives value 9 hence PostgreSQL reports error. The
98 * solution is to convert the decimal number into equivalent binary
99 * string.
100 */
101 case BYTEAOID:
102 SET_VARSIZE(column->value, column->length + VARHDRSZ);
103 return PointerGetDatum(column->value);
104
105 case BITOID:
106 sprintf(str, "%d", dec_bin(*((int *) column->value)));
107 valueDatum = CStringGetDatum((char *) str);
108 break;
109 default:
110 valueDatum = CStringGetDatum((char *) column->value);
111 }
112
113 value_datum = OidFunctionCall3(typeinput, valueDatum,
114 ObjectIdGetDatum(pgtyp),
115 Int32GetDatum(typemod));
116
117 return value_datum;
118 }
119
120 /*
121 * mysql_from_pgtyp:
122 * Give MySQL data type for PG type
123 */
124 static int32
mysql_from_pgtyp(Oid type)125 mysql_from_pgtyp(Oid type)
126 {
127 switch (type)
128 {
129 case INT2OID:
130 return MYSQL_TYPE_SHORT;
131 case INT4OID:
132 return MYSQL_TYPE_LONG;
133 case INT8OID:
134 return MYSQL_TYPE_LONGLONG;
135 case FLOAT4OID:
136 return MYSQL_TYPE_FLOAT;
137 case FLOAT8OID:
138 return MYSQL_TYPE_DOUBLE;
139 case NUMERICOID:
140 return MYSQL_TYPE_DOUBLE;
141 case BOOLOID:
142 return MYSQL_TYPE_LONG;
143 case BPCHAROID:
144 case VARCHAROID:
145 case TEXTOID:
146 case JSONOID:
147 case ANYENUMOID:
148 return MYSQL_TYPE_STRING;
149 case NAMEOID:
150 return MYSQL_TYPE_STRING;
151 case DATEOID:
152 return MYSQL_TYPE_DATE;
153 case TIMEOID:
154 case TIMESTAMPOID:
155 case TIMESTAMPTZOID:
156 return MYSQL_TYPE_TIMESTAMP;
157 case BITOID:
158 return MYSQL_TYPE_LONG;
159 case BYTEAOID:
160 return MYSQL_TYPE_BLOB;
161 default:
162 ereport(ERROR,
163 (errcode(ERRCODE_FDW_INVALID_DATA_TYPE),
164 errmsg("cannot convert constant value to MySQL value"),
165 errhint("Constant value data type: %u", type)));
166 break;
167 }
168 }
169
170 /*
171 * bind_sql_var:
172 * Bind the values provided as DatumBind the values and nulls to
173 * modify the target table (INSERT/UPDATE)
174 */
175 void
mysql_bind_sql_var(Oid type,int attnum,Datum value,MYSQL_BIND * binds,bool * isnull)176 mysql_bind_sql_var(Oid type, int attnum, Datum value, MYSQL_BIND *binds,
177 bool *isnull)
178 {
179 /* Clear the bind buffer and attributes */
180 memset(&binds[attnum], 0x0, sizeof(MYSQL_BIND));
181
182 #if MYSQL_VERSION_ID < 80000 || MARIADB_VERSION_ID >= 100000
183 binds[attnum].is_null = (my_bool *) isnull;
184 #else
185 binds[attnum].is_null = isnull;
186 #endif
187
188 /* Avoid to bind buffer in case value is NULL */
189 if (*isnull)
190 return;
191
192 /*
193 * If type is an enum, use ANYENUMOID. We will send string containing the
194 * enum value to the MySQL.
195 */
196 if (type_is_enum(type))
197 type = ANYENUMOID;
198
199 /* Assign the buffer type if value is not null */
200 binds[attnum].buffer_type = mysql_from_pgtyp(type);
201
202 switch (type)
203 {
204 case INT2OID:
205 {
206 int16 dat = DatumGetInt16(value);
207 int16 *bufptr = palloc(sizeof(int16));
208
209 memcpy(bufptr, (char *) &dat, sizeof(int16));
210
211 binds[attnum].buffer = bufptr;
212 }
213 break;
214 case INT4OID:
215 {
216 int32 dat = DatumGetInt32(value);
217 int32 *bufptr = palloc(sizeof(int32));
218
219 memcpy(bufptr, (char *) &dat, sizeof(int32));
220
221 binds[attnum].buffer = bufptr;
222 }
223 break;
224 case INT8OID:
225 {
226 int64 dat = DatumGetInt64(value);
227 int64 *bufptr = palloc(sizeof(int64));
228
229 memcpy(bufptr, (char *) &dat, sizeof(int64));
230
231 binds[attnum].buffer = bufptr;
232 }
233 break;
234 case FLOAT4OID:
235 {
236 float4 dat = DatumGetFloat4(value);
237 float4 *bufptr = palloc(sizeof(float4));
238
239 memcpy(bufptr, (char *) &dat, sizeof(float4));
240
241 binds[attnum].buffer = bufptr;
242 }
243 break;
244 case FLOAT8OID:
245 {
246 float8 dat = DatumGetFloat8(value);
247 float8 *bufptr = palloc(sizeof(float8));
248
249 memcpy(bufptr, (char *) &dat, sizeof(float8));
250
251 binds[attnum].buffer = bufptr;
252 }
253 break;
254 case NUMERICOID:
255 {
256 Datum valueDatum = DirectFunctionCall1(numeric_float8,
257 value);
258 float8 dat = DatumGetFloat8(valueDatum);
259 float8 *bufptr = palloc(sizeof(float8));
260
261 memcpy(bufptr, (char *) &dat, sizeof(float8));
262
263 binds[attnum].buffer = bufptr;
264 }
265 break;
266 case BOOLOID:
267 {
268 int32 dat = DatumGetInt32(value);
269 int32 *bufptr = palloc(sizeof(int32));
270
271 memcpy(bufptr, (char *) &dat, sizeof(int32));
272
273 binds[attnum].buffer = bufptr;
274 }
275 break;
276 case BPCHAROID:
277 case VARCHAROID:
278 case TEXTOID:
279 case JSONOID:
280 case ANYENUMOID:
281 {
282 char *outputString = NULL;
283 Oid outputFunctionId = InvalidOid;
284 bool typeVarLength = false;
285
286 getTypeOutputInfo(type, &outputFunctionId, &typeVarLength);
287 outputString = OidOutputFunctionCall(outputFunctionId, value);
288
289 binds[attnum].buffer = outputString;
290 binds[attnum].buffer_length = strlen(outputString);
291 }
292 break;
293 case NAMEOID:
294 {
295 char *outputString = NULL;
296 Oid outputFunctionId = InvalidOid;
297 bool typeVarLength = false;
298
299 getTypeOutputInfo(type, &outputFunctionId, &typeVarLength);
300 outputString = OidOutputFunctionCall(outputFunctionId, value);
301
302 binds[attnum].buffer = outputString;
303 binds[attnum].buffer_length = strlen(outputString);
304 }
305 break;
306 case DATEOID:
307 {
308 int tz;
309 struct pg_tm tt,
310 *tm = &tt;
311 fsec_t fsec;
312 const char *tzn;
313 Datum valueDatum = DirectFunctionCall1(date_timestamp,
314 value);
315 Timestamp valueTimestamp = DatumGetTimestamp(valueDatum);
316 MYSQL_TIME *ts = palloc0(sizeof(MYSQL_TIME));
317
318 timestamp2tm(valueTimestamp, &tz, tm, &fsec, &tzn,
319 pg_tzset("UTC"));
320
321 DATE_MYSQL_PG(ts, tt);
322
323 binds[attnum].buffer = ts;
324 binds[attnum].buffer_length = sizeof(MYSQL_TIME);
325 }
326 break;
327 case TIMEOID:
328 case TIMESTAMPOID:
329 case TIMESTAMPTZOID:
330 {
331 Timestamp valueTimestamp = DatumGetTimestamp(value);
332 MYSQL_TIME *ts = palloc0(sizeof(MYSQL_TIME));
333 int tz;
334 struct pg_tm tt,
335 *tm = &tt;
336 fsec_t fsec;
337 const char *tzn;
338
339 timestamp2tm(valueTimestamp, &tz, tm, &fsec, &tzn,
340 pg_tzset("UTC"));
341
342 DATE_MYSQL_PG(ts, tt);
343
344 binds[attnum].buffer = ts;
345 binds[attnum].buffer_length = sizeof(MYSQL_TIME);
346 }
347 break;
348 case BITOID:
349 {
350 int32 dat;
351 int32 *bufptr = palloc0(sizeof(int32));
352 char *outputString = NULL;
353 Oid outputFunctionId = InvalidOid;
354 bool typeVarLength = false;
355
356 getTypeOutputInfo(type, &outputFunctionId, &typeVarLength);
357 outputString = OidOutputFunctionCall(outputFunctionId, value);
358
359 dat = bin_dec(atoi(outputString));
360 memcpy(bufptr, (char *) &dat, sizeof(int32));
361 binds[attnum].buffer = bufptr;
362 }
363 break;
364 case BYTEAOID:
365 {
366 int len;
367 char *dat = NULL;
368 char *bufptr;
369 char *result = DatumGetPointer(value);
370
371 if (VARATT_IS_1B(result))
372 {
373 len = VARSIZE_1B(result) - VARHDRSZ_SHORT;
374 dat = VARDATA_1B(result);
375 }
376 else
377 {
378 len = VARSIZE_4B(result) - VARHDRSZ;
379 dat = VARDATA_4B(result);
380 }
381
382 bufptr = palloc(len);
383 memcpy(bufptr, (char *) dat, len);
384 binds[attnum].buffer = bufptr;
385 binds[attnum].buffer_length = len;
386 }
387 break;
388 default:
389 ereport(ERROR,
390 (errcode(ERRCODE_FDW_INVALID_DATA_TYPE),
391 errmsg("cannot convert constant value to MySQL value"),
392 errhint("Constant value data type: %u", type)));
393 break;
394 }
395 }
396
397 /*
398 * mysql_bind_result:
399 * Bind the value and null pointers to get the data from
400 * remote mysql table (SELECT)
401 */
402 void
mysql_bind_result(Oid pgtyp,int pgtypmod,MYSQL_FIELD * field,mysql_column * column)403 mysql_bind_result(Oid pgtyp, int pgtypmod, MYSQL_FIELD *field,
404 mysql_column *column)
405 {
406 MYSQL_BIND *mbind = column->mysql_bind;
407
408 #if MYSQL_VERSION_ID < 80000 || MARIADB_VERSION_ID >= 100000
409 mbind->is_null = (my_bool *) &column->is_null;
410 mbind->error = (my_bool *) &column->error;
411 #else
412 mbind->is_null = &column->is_null;
413 mbind->error = &column->error;
414 #endif
415 mbind->length = &column->length;
416
417 switch (pgtyp)
418 {
419 case BYTEAOID:
420 mbind->buffer_type = MYSQL_TYPE_BLOB;
421 /* Leave room at front for bytea buffer length prefix */
422 column->value = (Datum) palloc0(MAX_BLOB_WIDTH + VARHDRSZ);
423 mbind->buffer = VARDATA(column->value);
424 mbind->buffer_length = MAX_BLOB_WIDTH;
425 break;
426 default:
427 mbind->buffer_type = MYSQL_TYPE_VAR_STRING;
428 column->value = (Datum) palloc0(MAXDATALEN);
429 mbind->buffer = (char *) column->value;
430 mbind->buffer_length = MAXDATALEN;
431 }
432 }
433
434 static int
dec_bin(int number)435 dec_bin(int number)
436 {
437 int rem;
438 int i = 1;
439 int bin = 0;
440
441 while (number != 0)
442 {
443 rem = number % 2;
444 number /= 2;
445 bin += rem * i;
446 i *= 10;
447 }
448
449 return bin;
450 }
451
452 static int
bin_dec(int binarynumber)453 bin_dec(int binarynumber)
454 {
455 int dec = 0;
456 int i = 0;
457 int rem;
458
459 while (binarynumber != 0)
460 {
461 rem = binarynumber % 10;
462 binarynumber /= 10;
463 dec += rem * pow(2, i);
464 ++i;
465 }
466
467 return dec;
468 }
469