1 /*
2 Copyright (c) 2000, 2014, Oracle and/or its affiliates.
3
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU General Public License as published by
6 the Free Software Foundation; version 2 of the License.
7
8 This program is distributed in the hope that it will be useful,
9 but WITHOUT ANY WARRANTY; without even the implied warranty of
10 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 GNU General Public License for more details.
12
13 You should have received a copy of the GNU General Public License
14 along with this program; if not, write to the Free Software
15 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */
16
17 /*
18 ** example file of UDF (user definable functions) that are dynamicly loaded
19 ** into the standard mysqld core.
20 **
21 ** The functions name, type and shared library is saved in the new system
22 ** table 'func'. To be able to create new functions one must have write
23 ** privilege for the database 'mysql'. If one starts MySQL with
24 ** --skip-grant, then UDF initialization will also be skipped.
25 **
26 ** Syntax for the new commands are:
27 ** create function <function_name> returns {string|real|integer}
28 ** soname <name_of_shared_library>
29 ** drop function <function_name>
30 **
31 ** Each defined function may have a xxxx_init function and a xxxx_deinit
32 ** function. The init function should alloc memory for the function
33 ** and tell the main function about the max length of the result
34 ** (for string functions), number of decimals (for double functions) and
35 ** if the result may be a null value.
36 **
37 ** If a function sets the 'error' argument to 1 the function will not be
38 ** called anymore and mysqld will return NULL for all calls to this copy
39 ** of the function.
40 **
41 ** All strings arguments to functions are given as string pointer + length
42 ** to allow handling of binary data.
43 ** Remember that all functions must be thread safe. This means that one is not
44 ** allowed to alloc any global or static variables that changes!
45 ** If one needs memory one should alloc this in the init function and free
46 ** this on the __deinit function.
47 **
48 ** Note that the init and __deinit functions are only called once per
49 ** SQL statement while the value function may be called many times
50 **
51 ** Function 'metaphon' returns a metaphon string of the string argument.
52 ** This is something like a soundex string, but it's more tuned for English.
53 **
54 ** Function 'myfunc_double' returns summary of codes of all letters
55 ** of arguments divided by summary length of all its arguments.
56 **
57 ** Function 'myfunc_int' returns summary length of all its arguments.
58 **
59 ** Function 'udf_sequence' returns an sequence starting from a certain number.
60 **
61 ** Function 'myfunc_argument_name' returns name of argument.
62 **
63 ** On the end is a couple of functions that converts hostnames to ip and
64 ** vice versa.
65 **
66 ** A dynamicly loadable file should be compiled shared.
67 ** (something like: gcc -shared -o my_func.so myfunc.cc).
68 ** You can easily get all switches right by doing:
69 ** cd sql ; make udf_example.o
70 ** Take the compile line that make writes, remove the '-c' near the end of
71 ** the line and add -shared -o udf_example.so to the end of the compile line.
72 ** The resulting library (udf_example.so) should be copied to some dir
73 ** searched by ld. (/usr/lib ?)
74 ** If you are using gcc, then you should be able to create the udf_example.so
75 ** by simply doing 'make udf_example.so'.
76 **
77 ** After the library is made one must notify mysqld about the new
78 ** functions with the commands:
79 **
80 ** CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
81 ** CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
82 ** CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
83 ** CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "udf_example.so";
84 ** CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
85 ** CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
86 ** CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so";
87 ** CREATE FUNCTION myfunc_argument_name RETURNS STRING SONAME "udf_example.so";
88 **
89 ** After this the functions will work exactly like native MySQL functions.
90 ** Functions should be created only once.
91 **
92 ** The functions can be deleted by:
93 **
94 ** DROP FUNCTION metaphon;
95 ** DROP FUNCTION myfunc_double;
96 ** DROP FUNCTION myfunc_int;
97 ** DROP FUNCTION lookup;
98 ** DROP FUNCTION reverse_lookup;
99 ** DROP FUNCTION avgcost;
100 ** DROP FUNCTION myfunc_argument_name;
101 **
102 ** The CREATE FUNCTION and DROP FUNCTION update the func@mysql table. All
103 ** Active function will be reloaded on every restart of server
104 ** (if --skip-grant-tables is not given)
105 **
106 ** If you ge problems with undefined symbols when loading the shared
107 ** library, you should verify that mysqld is compiled with the -rdynamic
108 ** option.
109 **
110 ** If you can't get AGGREGATES to work, check that you have the column
111 ** 'type' in the mysql.func table. If not, run 'mysql_upgrade'.
112 **
113 */
114
115 #ifdef _WIN32
116 /* Silence warning about deprecated functions , gethostbyname etc*/
117 #define _WINSOCK_DEPRECATED_NO_WARNINGS
118 #endif
119
120 #ifdef STANDARD
121 /* STANDARD is defined, don't use any mysql functions */
122 #include <stdlib.h>
123 #include <stdio.h>
124 #include <string.h>
125 #ifdef __WIN__
126 typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */
127 typedef __int64 longlong;
128 #else
129 typedef unsigned long long ulonglong;
130 typedef long long longlong;
131 #endif /*__WIN__*/
132 #else
133 #include "mariadb.h"
134 #include <my_sys.h>
135 #if defined(MYSQL_SERVER)
136 #include <m_string.h> /* To get strmov() */
137 #else
138 /* when compiled as standalone */
139 #include <string.h>
140 #define strmov(a,b) stpcpy(a,b)
141 #define bzero(a,b) memset(a,0,b)
142 #endif
143 #endif
144 #include <mysql.h>
145 #include <ctype.h>
146
147
148 #ifdef HAVE_DLOPEN
149
150 #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST)
151 static pthread_mutex_t LOCK_hostname;
152 #endif
153
154 /* These must be right or mysqld will not find the symbol! */
155
156 my_bool metaphon_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
157 void metaphon_deinit(UDF_INIT *initid);
158 char *metaphon(UDF_INIT *initid, UDF_ARGS *args, char *result,
159 unsigned long *length, char *is_null, char *error);
160 my_bool myfunc_double_init(UDF_INIT *, UDF_ARGS *args, char *message);
161 double myfunc_double(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
162 char *error);
163 my_bool myfunc_int_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
164 longlong myfunc_int(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
165 char *error);
166 my_bool udf_sequence_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
167 void udf_sequence_deinit(UDF_INIT *initid);
168 longlong udf_sequence(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
169 char *error);
170 my_bool avgcost_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
171 void avgcost_deinit( UDF_INIT* initid );
172 void avgcost_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
173 void avgcost_clear( UDF_INIT* initid, char* is_null, char *error );
174 void avgcost_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
175 double avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
176 my_bool avg2_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
177 void avg2_deinit( UDF_INIT* initid );
178 void avg2_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
179 void avg2_clear( UDF_INIT* initid, char* is_null, char *error );
180 void avg2_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
181 void avg2_remove( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
182 double avg2( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
183 my_bool is_const_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
184 char *is_const(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long
185 *length, char *is_null, char *error);
186
187
188 /*************************************************************************
189 ** Example of init function
190 ** Arguments:
191 ** initid Points to a structure that the init function should fill.
192 ** This argument is given to all other functions.
193 ** my_bool maybe_null 1 if function can return NULL
194 ** Default value is 1 if any of the arguments
195 ** is declared maybe_null.
196 ** unsigned int decimals Number of decimals.
197 ** Default value is max decimals in any of the
198 ** arguments.
199 ** unsigned int max_length Length of string result.
200 ** The default value for integer functions is 21
201 ** The default value for real functions is 13+
202 ** default number of decimals.
203 ** The default value for string functions is
204 ** the longest string argument.
205 ** char *ptr; A pointer that the function can use.
206 **
207 ** args Points to a structure which contains:
208 ** unsigned int arg_count Number of arguments
209 ** enum Item_result *arg_type Types for each argument.
210 ** Types are STRING_RESULT, REAL_RESULT
211 ** and INT_RESULT.
212 ** char **args Pointer to constant arguments.
213 ** Contains 0 for not constant argument.
214 ** unsigned long *lengths; max string length for each argument
215 ** char *maybe_null Information of which arguments
216 ** may be NULL
217 **
218 ** message Error message that should be passed to the user on fail.
219 ** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
220 ** try to keep the error message less than 80 bytes long!
221 **
222 ** This function should return 1 if something goes wrong. In this case
223 ** message should contain something useful!
224 **************************************************************************/
225
226 #define MAXMETAPH 8
227
metaphon_init(UDF_INIT * initid,UDF_ARGS * args,char * message)228 my_bool metaphon_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
229 {
230 if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT)
231 {
232 strcpy(message,"Wrong arguments to metaphon; Use the source");
233 return 1;
234 }
235 initid->max_length=MAXMETAPH;
236 return 0;
237 }
238
239 /****************************************************************************
240 ** Deinit function. This should free all resources allocated by
241 ** this function.
242 ** Arguments:
243 ** initid Return value from xxxx_init
244 ****************************************************************************/
245
246
metaphon_deinit(UDF_INIT * initid)247 void metaphon_deinit(UDF_INIT *initid __attribute__((unused)))
248 {
249 }
250
251 /***************************************************************************
252 ** UDF string function.
253 ** Arguments:
254 ** initid Structure filled by xxx_init
255 ** args The same structure as to xxx_init. This structure
256 ** contains values for all parameters.
257 ** Note that the functions MUST check and convert all
258 ** to the type it wants! Null values are represented by
259 ** a NULL pointer
260 ** result Possible buffer to save result. At least 255 byte long.
261 ** length Pointer to length of the above buffer. In this the function
262 ** should save the result length
263 ** is_null If the result is null, one should store 1 here.
264 ** error If something goes fatally wrong one should store 1 here.
265 **
266 ** This function should return a pointer to the result string.
267 ** Normally this is 'result' but may also be an alloced string.
268 ***************************************************************************/
269
270 /* Character coding array */
271 static char codes[26] = {
272 1,16,4,16,9,2,4,16,9,2,0,2,2,2,1,4,0,2,4,4,1,0,0,0,8,0
273 /* A B C D E F G H I J K L M N O P Q R S T U V W X Y Z*/
274 };
275
276 /*--- Macros to access character coding array -------------*/
277
278 #define ISVOWEL(x) (codes[(x) - 'A'] & 1) /* AEIOU */
279
280 /* Following letters are not changed */
281 #define NOCHANGE(x) (codes[(x) - 'A'] & 2) /* FJLMNR */
282
283 /* These form diphthongs when preceding H */
284 #define AFFECTH(x) (codes[(x) - 'A'] & 4) /* CGPST */
285
286 /* These make C and G soft */
287 #define MAKESOFT(x) (codes[(x) - 'A'] & 8) /* EIY */
288
289 /* These prevent GH from becoming F */
290 #define NOGHTOF(x) (codes[(x) - 'A'] & 16) /* BDH */
291
292
metaphon(UDF_INIT * initid,UDF_ARGS * args,char * result,unsigned long * length,char * is_null,char * error)293 char *metaphon(UDF_INIT *initid __attribute__((unused)),
294 UDF_ARGS *args, char *result, unsigned long *length,
295 char *is_null, char *error __attribute__((unused)))
296 {
297 const char *word=args->args[0];
298 const char *w_end;
299 char *org_result;
300 char *n, *n_start, *n_end; /* pointers to string */
301 char *metaph_end; /* pointers to end of metaph */
302 char ntrans[32]; /* word with uppercase letters */
303 int KSflag; /* state flag for X to KS */
304
305 if (!word) /* Null argument */
306 {
307 /* The length is expected to be zero when the argument is NULL. */
308 assert(args->lengths[0] == 0);
309 *is_null=1;
310 return 0;
311 }
312
313 w_end=word+args->lengths[0];
314 org_result=result;
315
316 /*--------------------------------------------------------
317 * Copy word to internal buffer, dropping non-alphabetic
318 * characters and converting to uppercase.
319 *-------------------------------------------------------*/
320
321 for (n = ntrans + 1, n_end = ntrans + sizeof(ntrans)-2;
322 word != w_end && n < n_end; word++ )
323 if ( isalpha ( *word ))
324 *n++ = toupper ( *word );
325
326 if ( n == ntrans + 1 ) /* return empty string if 0 bytes */
327 {
328 *length=0;
329 return result;
330 }
331 n_end = n; /* set n_end to end of string */
332 ntrans[0] = 'Z'; /* ntrans[0] should be a neutral char */
333 n[0]=n[1]=0; /* pad with nulls */
334 n = ntrans + 1; /* assign pointer to start */
335
336 /*------------------------------------------------------------
337 * check for all prefixes:
338 * PN KN GN AE WR WH and X at start.
339 *----------------------------------------------------------*/
340
341 switch ( *n ) {
342 case 'P':
343 case 'K':
344 case 'G':
345 if ( n[1] == 'N')
346 *n++ = 0;
347 break;
348 case 'A':
349 if ( n[1] == 'E')
350 *n++ = 0;
351 break;
352 case 'W':
353 if ( n[1] == 'R' )
354 *n++ = 0;
355 else
356 if ( *(n + 1) == 'H')
357 {
358 n[1] = *n;
359 *n++ = 0;
360 }
361 break;
362 case 'X':
363 *n = 'S';
364 break;
365 }
366
367 /*------------------------------------------------------------
368 * Now, loop step through string, stopping at end of string
369 * or when the computed metaph is MAXMETAPH characters long
370 *----------------------------------------------------------*/
371
372 KSflag = 0; /* state flag for KS translation */
373
374 for (metaph_end = result + MAXMETAPH, n_start = n;
375 n < n_end && result < metaph_end; n++ )
376 {
377
378 if ( KSflag )
379 {
380 KSflag = 0;
381 *result++ = *n;
382 }
383 else
384 {
385 /* drop duplicates except for CC */
386 if ( *( n - 1 ) == *n && *n != 'C' )
387 continue;
388
389 /* check for F J L M N R or first letter vowel */
390 if ( NOCHANGE ( *n ) ||
391 ( n == n_start && ISVOWEL ( *n )))
392 *result++ = *n;
393 else
394 switch ( *n ) {
395 case 'B': /* check for -MB */
396 if ( n < n_end || *( n - 1 ) != 'M' )
397 *result++ = *n;
398 break;
399
400 case 'C': /* C = X ("sh" sound) in CH and CIA */
401 /* = S in CE CI and CY */
402 /* dropped in SCI SCE SCY */
403 /* else K */
404 if ( *( n - 1 ) != 'S' ||
405 !MAKESOFT ( n[1]))
406 {
407 if ( n[1] == 'I' && n[2] == 'A' )
408 *result++ = 'X';
409 else
410 if ( MAKESOFT ( n[1]))
411 *result++ = 'S';
412 else
413 if ( n[1] == 'H' )
414 *result++ = (( n == n_start &&
415 !ISVOWEL ( n[2])) ||
416 *( n - 1 ) == 'S' ) ?
417 (char)'K' : (char)'X';
418 else
419 *result++ = 'K';
420 }
421 break;
422
423 case 'D': /* J before DGE, DGI, DGY, else T */
424 *result++ =
425 ( n[1] == 'G' &&
426 MAKESOFT ( n[2])) ?
427 (char)'J' : (char)'T';
428 break;
429
430 case 'G': /* complicated, see table in text */
431 if (( n[1] != 'H' || ISVOWEL ( n[2]))
432 && (
433 n[1] != 'N' ||
434 (
435 (n + 1) < n_end &&
436 (
437 n[2] != 'E' ||
438 *( n + 3 ) != 'D'
439 )
440 )
441 )
442 && (
443 *( n - 1 ) != 'D' ||
444 !MAKESOFT ( n[1])
445 )
446 )
447 *result++ =
448 ( MAKESOFT ( *( n + 1 )) &&
449 n[2] != 'G' ) ?
450 (char)'J' : (char)'K';
451 else
452 if ( n[1] == 'H' &&
453 !NOGHTOF( *( n - 3 )) &&
454 *( n - 4 ) != 'H')
455 *result++ = 'F';
456 break;
457
458 case 'H': /* H if before a vowel and not after */
459 /* C, G, P, S, T */
460
461 if ( !AFFECTH ( *( n - 1 )) &&
462 ( !ISVOWEL ( *( n - 1 )) ||
463 ISVOWEL ( n[1])))
464 *result++ = 'H';
465 break;
466
467 case 'K': /* K = K, except dropped after C */
468 if ( *( n - 1 ) != 'C')
469 *result++ = 'K';
470 break;
471
472 case 'P': /* PH = F, else P = P */
473 *result++ = *( n + 1 ) == 'H'
474 ? (char)'F' : (char)'P';
475 break;
476 case 'Q': /* Q = K (U after Q is already gone */
477 *result++ = 'K';
478 break;
479
480 case 'S': /* SH, SIO, SIA = X ("sh" sound) */
481 *result++ = ( n[1] == 'H' ||
482 ( *(n + 1) == 'I' &&
483 ( n[2] == 'O' ||
484 n[2] == 'A'))) ?
485 (char)'X' : (char)'S';
486 break;
487
488 case 'T': /* TIO, TIA = X ("sh" sound) */
489 /* TH = 0, ("th" sound ) */
490 if ( *( n + 1 ) == 'I' && ( n[2] == 'O'
491 || n[2] == 'A') )
492 *result++ = 'X';
493 else
494 if ( n[1] == 'H' )
495 *result++ = '0';
496 else
497 if ( *( n + 1) != 'C' || n[2] != 'H')
498 *result++ = 'T';
499 break;
500
501 case 'V': /* V = F */
502 *result++ = 'F';
503 break;
504
505 case 'W': /* only exist if a vowel follows */
506 case 'Y':
507 if ( ISVOWEL ( n[1]))
508 *result++ = *n;
509 break;
510
511 case 'X': /* X = KS, except at start */
512 if ( n == n_start )
513 *result++ = 'S';
514 else
515 {
516 *result++ = 'K'; /* insert K, then S */
517 KSflag = 1; /* this flag will cause S to be
518 inserted on next pass thru loop */
519 }
520 break;
521
522 case 'Z':
523 *result++ = 'S';
524 break;
525 }
526 }
527 }
528 *length= (unsigned long) (result - org_result);
529 return org_result;
530 }
531
532
533 /***************************************************************************
534 ** UDF double function.
535 ** Arguments:
536 ** initid Structure filled by xxx_init
537 ** args The same structure as to xxx_init. This structure
538 ** contains values for all parameters.
539 ** Note that the functions MUST check and convert all
540 ** to the type it wants! Null values are represented by
541 ** a NULL pointer
542 ** is_null If the result is null, one should store 1 here.
543 ** error If something goes fatally wrong one should store 1 here.
544 **
545 ** This function should return the result.
546 ***************************************************************************/
547
myfunc_double_init(UDF_INIT * initid,UDF_ARGS * args,char * message)548 my_bool myfunc_double_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
549 {
550 uint i;
551
552 if (!args->arg_count)
553 {
554 strcpy(message,"myfunc_double must have at least one argument");
555 return 1;
556 }
557 /*
558 ** As this function wants to have everything as strings, force all arguments
559 ** to strings.
560 */
561 for (i=0 ; i < args->arg_count; i++)
562 args->arg_type[i]=STRING_RESULT;
563 initid->maybe_null=1; /* The result may be null */
564 initid->decimals=2; /* We want 2 decimals in the result */
565 initid->max_length=6; /* 3 digits + . + 2 decimals */
566 return 0;
567 }
568
569
myfunc_double(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * error)570 double myfunc_double(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
571 char *is_null, char *error __attribute__((unused)))
572 {
573 unsigned long val = 0;
574 unsigned long v = 0;
575 uint i, j;
576
577 for (i = 0; i < args->arg_count; i++)
578 {
579 if (args->args[i] == NULL)
580 continue;
581 val += args->lengths[i];
582 for (j=args->lengths[i] ; j-- > 0 ;)
583 v += args->args[i][j];
584 }
585 if (val)
586 return (double) v/ (double) val;
587 *is_null=1;
588 return 0.0;
589 }
590
591
592 /***************************************************************************
593 ** UDF long long function.
594 ** Arguments:
595 ** initid Return value from xxxx_init
596 ** args The same structure as to xxx_init. This structure
597 ** contains values for all parameters.
598 ** Note that the functions MUST check and convert all
599 ** to the type it wants! Null values are represented by
600 ** a NULL pointer
601 ** is_null If the result is null, one should store 1 here.
602 ** error If something goes fatally wrong one should store 1 here.
603 **
604 ** This function should return the result as a long long
605 ***************************************************************************/
606
607 /* This function returns the sum of all arguments */
608
myfunc_int(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * error)609 longlong myfunc_int(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
610 char *is_null __attribute__((unused)),
611 char *error __attribute__((unused)))
612 {
613 longlong val = 0;
614 uint i;
615
616 for (i = 0; i < args->arg_count; i++)
617 {
618 if (args->args[i] == NULL)
619 continue;
620 switch (args->arg_type[i]) {
621 case STRING_RESULT: /* Add string lengths */
622 val += args->lengths[i];
623 break;
624 case INT_RESULT: /* Add numbers */
625 val += *((longlong*) args->args[i]);
626 break;
627 case REAL_RESULT: /* Add numers as longlong */
628 val += (longlong) *((double*) args->args[i]);
629 break;
630 default:
631 break;
632 }
633 }
634 return val;
635 }
636
637 /*
638 At least one of _init/_deinit is needed unless the server is started
639 with --allow_suspicious_udfs.
640 */
myfunc_int_init(UDF_INIT * initid,UDF_ARGS * args,char * message)641 my_bool myfunc_int_init(UDF_INIT *initid __attribute__((unused)),
642 UDF_ARGS *args __attribute__((unused)),
643 char *message __attribute__((unused)))
644 {
645 return 0;
646 }
647
648 /*
649 Simple example of how to get a sequences starting from the first argument
650 or 1 if no arguments have been given
651 */
652
udf_sequence_init(UDF_INIT * initid,UDF_ARGS * args,char * message)653 my_bool udf_sequence_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
654 {
655 if (args->arg_count > 1)
656 {
657 strmov(message,"This function takes none or 1 argument");
658 return 1;
659 }
660 if (args->arg_count)
661 args->arg_type[0]= INT_RESULT; /* Force argument to int */
662
663 if (!(initid->ptr=(char*) malloc(sizeof(longlong))))
664 {
665 strmov(message,"Couldn't allocate memory");
666 return 1;
667 }
668 bzero(initid->ptr,sizeof(longlong));
669 /*
670 udf_sequence() is a non-deterministic function : it has different value
671 even if called with the same arguments.
672 */
673 initid->const_item=0;
674 return 0;
675 }
676
udf_sequence_deinit(UDF_INIT * initid)677 void udf_sequence_deinit(UDF_INIT *initid)
678 {
679 if (initid->ptr)
680 free(initid->ptr);
681 }
682
udf_sequence(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * error)683 longlong udf_sequence(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
684 char *is_null __attribute__((unused)),
685 char *error __attribute__((unused)))
686 {
687 ulonglong val=0;
688 if (args->arg_count)
689 val= *((longlong*) args->args[0]);
690 return ++*((longlong*) initid->ptr) + val;
691 }
692
693
694 /****************************************************************************
695 ** Some functions that handles IP and hostname conversions
696 ** The orignal function was from Zeev Suraski.
697 **
698 ** CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
699 ** CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
700 **
701 ****************************************************************************/
702
703 #ifdef __WIN__
704 #include <winsock2.h>
705 #else
706 #include <sys/socket.h>
707 #include <netinet/in.h>
708 #include <arpa/inet.h>
709 #include <netdb.h>
710 #endif
711
712 my_bool lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
713 void lookup_deinit(UDF_INIT *initid);
714 char *lookup(UDF_INIT *initid, UDF_ARGS *args, char *result,
715 unsigned long *length, char *null_value, char *error);
716 my_bool reverse_lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
717 void reverse_lookup_deinit(UDF_INIT *initid);
718 char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result,
719 unsigned long *length, char *null_value, char *error);
720
721
722 /****************************************************************************
723 ** lookup IP for an hostname.
724 **
725 ** This code assumes that gethostbyname_r exists and inet_ntoa() is thread
726 ** safe (As it is in Solaris)
727 ****************************************************************************/
728
729
lookup_init(UDF_INIT * initid,UDF_ARGS * args,char * message)730 my_bool lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
731 {
732 if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT)
733 {
734 strmov(message,"Wrong arguments to lookup; Use the source");
735 return 1;
736 }
737 initid->max_length=11;
738 initid->maybe_null=1;
739 #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST)
740 (void) pthread_mutex_init(&LOCK_hostname,MY_MUTEX_INIT_SLOW);
741 #endif
742 return 0;
743 }
744
lookup_deinit(UDF_INIT * initid)745 void lookup_deinit(UDF_INIT *initid __attribute__((unused)))
746 {
747 #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST)
748 (void) pthread_mutex_destroy(&LOCK_hostname);
749 #endif
750 }
751
lookup(UDF_INIT * initid,UDF_ARGS * args,char * result,unsigned long * res_length,char * null_value,char * error)752 char *lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
753 char *result, unsigned long *res_length, char *null_value,
754 char *error __attribute__((unused)))
755 {
756 uint length;
757 char name_buff[256];
758 struct hostent *hostent;
759 #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST)
760 int tmp_errno;
761 char hostname_buff[2048];
762 struct hostent tmp_hostent;
763 #endif
764 struct in_addr in;
765
766 if (!args->args[0] || !(length=args->lengths[0]))
767 {
768 *null_value=1;
769 return 0;
770 }
771 if (length >= sizeof(name_buff))
772 length=sizeof(name_buff)-1;
773 memcpy(name_buff,args->args[0],length);
774 name_buff[length]=0;
775 #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST)
776 if (!(hostent=gethostbyname_r(name_buff,&tmp_hostent,hostname_buff,
777 sizeof(hostname_buff), &tmp_errno)))
778 {
779 *null_value=1;
780 return 0;
781 }
782 #else
783 pthread_mutex_lock(&LOCK_hostname);
784 if (!(hostent= gethostbyname((char*) name_buff)))
785 {
786 pthread_mutex_unlock(&LOCK_hostname);
787 *null_value= 1;
788 return 0;
789 }
790 pthread_mutex_unlock(&LOCK_hostname);
791 #endif
792 memcpy(&in, *hostent->h_addr_list, sizeof(in.s_addr));
793 *res_length= (ulong) (strmov(result, inet_ntoa(in)) - result);
794 return result;
795 }
796
797
798 /****************************************************************************
799 ** return hostname for an IP number.
800 ** The functions can take as arguments a string "xxx.xxx.xxx.xxx" or
801 ** four numbers.
802 ****************************************************************************/
803
reverse_lookup_init(UDF_INIT * initid,UDF_ARGS * args,char * message)804 my_bool reverse_lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
805 {
806 if (args->arg_count == 1)
807 args->arg_type[0]= STRING_RESULT;
808 else if (args->arg_count == 4)
809 args->arg_type[0]=args->arg_type[1]=args->arg_type[2]=args->arg_type[3]=
810 INT_RESULT;
811 else
812 {
813 strmov(message,
814 "Wrong number of arguments to reverse_lookup; Use the source");
815 return 1;
816 }
817 initid->max_length=32;
818 initid->maybe_null=1;
819 #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST)
820 (void) pthread_mutex_init(&LOCK_hostname,MY_MUTEX_INIT_SLOW);
821 #endif
822 return 0;
823 }
824
reverse_lookup_deinit(UDF_INIT * initid)825 void reverse_lookup_deinit(UDF_INIT *initid __attribute__((unused)))
826 {
827 #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST)
828 (void) pthread_mutex_destroy(&LOCK_hostname);
829 #endif
830 }
831
reverse_lookup(UDF_INIT * initid,UDF_ARGS * args,char * result,unsigned long * res_length,char * null_value,char * error)832 char *reverse_lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
833 char *result, unsigned long *res_length,
834 char *null_value, char *error __attribute__((unused)))
835 {
836 #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST)
837 char name_buff[256];
838 struct hostent tmp_hostent;
839 int tmp_errno;
840 #endif
841 struct hostent *hp;
842 unsigned long taddr;
843 uint length;
844
845 if (args->arg_count == 4)
846 {
847 if (!args->args[0] || !args->args[1] ||!args->args[2] ||!args->args[3])
848 {
849 *null_value=1;
850 return 0;
851 }
852 sprintf(result,"%d.%d.%d.%d",
853 (int) *((longlong*) args->args[0]),
854 (int) *((longlong*) args->args[1]),
855 (int) *((longlong*) args->args[2]),
856 (int) *((longlong*) args->args[3]));
857 }
858 else
859 { /* string argument */
860 if (!args->args[0]) /* Return NULL for NULL values */
861 {
862 *null_value=1;
863 return 0;
864 }
865 length=args->lengths[0];
866 if (length >= (uint) *res_length-1)
867 length=(uint) *res_length;
868 memcpy(result,args->args[0],length);
869 result[length]=0;
870 }
871
872 taddr = inet_addr(result);
873 if (taddr == (unsigned long) -1L)
874 {
875 *null_value=1;
876 return 0;
877 }
878 #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST)
879 if (!(hp=gethostbyaddr_r((char*) &taddr,sizeof(taddr), AF_INET,
880 &tmp_hostent, name_buff,sizeof(name_buff),
881 &tmp_errno)))
882 {
883 *null_value=1;
884 return 0;
885 }
886 #else
887 pthread_mutex_lock(&LOCK_hostname);
888 if (!(hp= gethostbyaddr((char*) &taddr, sizeof(taddr), AF_INET)))
889 {
890 pthread_mutex_unlock(&LOCK_hostname);
891 *null_value= 1;
892 return 0;
893 }
894 pthread_mutex_unlock(&LOCK_hostname);
895 #endif
896 *res_length=(ulong) (strmov(result,hp->h_name) - result);
897 return result;
898 }
899
900 /*
901 ** Syntax for the new aggregate commands are:
902 ** create aggregate function <function_name> returns {string|real|integer}
903 ** soname <name_of_shared_library>
904 **
905 ** Syntax for avgcost: avgcost( t.quantity, t.price )
906 ** with t.quantity=integer, t.price=double
907 ** (this example is provided by Andreas F. Bobak <bobak@relog.ch>)
908 */
909
910
911 struct avgcost_data
912 {
913 ulonglong count;
914 longlong totalquantity;
915 double totalprice;
916 };
917
918
919 /*
920 ** Average Cost Aggregate Function.
921 */
922 my_bool
avgcost_init(UDF_INIT * initid,UDF_ARGS * args,char * message)923 avgcost_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
924 {
925 struct avgcost_data* data;
926
927 if (args->arg_count != 2)
928 {
929 strcpy(
930 message,
931 "wrong number of arguments: AVGCOST() requires two arguments"
932 );
933 return 1;
934 }
935
936 if ((args->arg_type[0] != INT_RESULT) || (args->arg_type[1] != REAL_RESULT) )
937 {
938 strcpy(
939 message,
940 "wrong argument type: AVGCOST() requires an INT and a REAL"
941 );
942 return 1;
943 }
944
945 /*
946 ** force arguments to double.
947 */
948 /*args->arg_type[0] = REAL_RESULT;
949 args->arg_type[1] = REAL_RESULT;*/
950
951 initid->maybe_null = 0; /* The result may be null */
952 initid->decimals = 4; /* We want 4 decimals in the result */
953 initid->max_length = 20; /* 6 digits + . + 10 decimals */
954
955 if (!(data = (struct avgcost_data*) malloc(sizeof(struct avgcost_data))))
956 {
957 strmov(message,"Couldn't allocate memory");
958 return 1;
959 }
960 data->totalquantity = 0;
961 data->totalprice = 0.0;
962
963 initid->ptr = (char*)data;
964
965 return 0;
966 }
967
968 void
avgcost_deinit(UDF_INIT * initid)969 avgcost_deinit( UDF_INIT* initid )
970 {
971 free(initid->ptr);
972 }
973
974
975 /* This is only for MySQL 4.0 compability */
976 void
avgcost_reset(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * message)977 avgcost_reset(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message)
978 {
979 avgcost_clear(initid, is_null, message);
980 avgcost_add(initid, args, is_null, message);
981 }
982
983 /* This is needed to get things to work in MySQL 4.1.1 and above */
984
985 void
avgcost_clear(UDF_INIT * initid,char * is_null,char * message)986 avgcost_clear(UDF_INIT* initid, char* is_null __attribute__((unused)),
987 char* message __attribute__((unused)))
988 {
989 struct avgcost_data* data = (struct avgcost_data*)initid->ptr;
990 data->totalprice= 0.0;
991 data->totalquantity= 0;
992 data->count= 0;
993 }
994
995
996 void
avgcost_add(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * message)997 avgcost_add(UDF_INIT* initid, UDF_ARGS* args,
998 char* is_null __attribute__((unused)),
999 char* message __attribute__((unused)))
1000 {
1001 if (args->args[0] && args->args[1])
1002 {
1003 struct avgcost_data* data = (struct avgcost_data*)initid->ptr;
1004 longlong quantity = *((longlong*)args->args[0]);
1005 longlong newquantity = data->totalquantity + quantity;
1006 double price = *((double*)args->args[1]);
1007
1008 data->count++;
1009
1010 if ( ((data->totalquantity >= 0) && (quantity < 0))
1011 || ((data->totalquantity < 0) && (quantity > 0)) )
1012 {
1013 /*
1014 ** passing from + to - or from - to +
1015 */
1016 if ( ((quantity < 0) && (newquantity < 0))
1017 || ((quantity > 0) && (newquantity > 0)) )
1018 {
1019 data->totalprice = price * (double)newquantity;
1020 }
1021 /*
1022 ** sub q if totalq > 0
1023 ** add q if totalq < 0
1024 */
1025 else
1026 {
1027 price = data->totalprice / (double)data->totalquantity;
1028 data->totalprice = price * (double)newquantity;
1029 }
1030 data->totalquantity = newquantity;
1031 }
1032 else
1033 {
1034 data->totalquantity += quantity;
1035 data->totalprice += price * (double)quantity;
1036 }
1037
1038 if (data->totalquantity == 0)
1039 data->totalprice = 0.0;
1040 }
1041 }
1042
1043
1044 double
avgcost(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * error)1045 avgcost( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)),
1046 char* is_null, char* error __attribute__((unused)))
1047 {
1048 struct avgcost_data* data = (struct avgcost_data*)initid->ptr;
1049 if (!data->count || !data->totalquantity)
1050 {
1051 *is_null = 1;
1052 return 0.0;
1053 }
1054
1055 *is_null = 0;
1056 return data->totalprice/(double)data->totalquantity;
1057 }
1058
1059
1060 /*
1061 ** Average 2 (number, sum)*/
1062 struct avg2_data
1063 {
1064 ulonglong count;
1065 double sum;
1066 };
1067
1068
1069 my_bool
avg2_init(UDF_INIT * initid,UDF_ARGS * args,char * message)1070 avg2_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
1071 {
1072 struct avg2_data* data;
1073
1074 if (args->arg_count != 2)
1075 {
1076 strcpy(
1077 message,
1078 "wrong number of arguments: AVG2() requires two arguments"
1079 );
1080 return 1;
1081 }
1082
1083 if ((args->arg_type[0] != INT_RESULT) || (args->arg_type[1] != REAL_RESULT) )
1084 {
1085 strcpy(
1086 message,
1087 "wrong argument type: AVG2() requires an INT and a REAL"
1088 );
1089 return 1;
1090 }
1091
1092 /*
1093 ** force arguments to double.
1094 */
1095 /*args->arg_type[0] = REAL_RESULT;
1096 args->arg_type[1] = REAL_RESULT;*/
1097
1098 initid->maybe_null = 0; /* The result may be null */
1099 initid->decimals = 4; /* We want 4 decimals in the result */
1100 initid->max_length = 20; /* 6 digits + . + 10 decimals */
1101
1102 if (!(data = (struct avg2_data*) malloc(sizeof(struct avg2_data))))
1103 {
1104 strmov(message,"Couldn't allocate memory");
1105 return 1;
1106 }
1107 data->count = 0;
1108 data->sum = 0.0;
1109
1110 initid->ptr = (char*)data;
1111
1112 return 0;
1113 }
1114
1115 void
avg2_deinit(UDF_INIT * initid)1116 avg2_deinit( UDF_INIT* initid )
1117 {
1118 free(initid->ptr);
1119 }
1120
1121
1122 /* This is only for MySQL 4.0 compability */
1123 void
avg2_reset(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * message)1124 avg2_reset(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message)
1125 {
1126 avgcost_clear(initid, is_null, message);
1127 avgcost_add(initid, args, is_null, message);
1128 }
1129
1130 /* This is needed to get things to work in MySQL 4.1.1 and above */
1131
1132 void
avg2_clear(UDF_INIT * initid,char * is_null,char * message)1133 avg2_clear(UDF_INIT* initid, char* is_null __attribute__((unused)),
1134 char* message __attribute__((unused)))
1135 {
1136 struct avg2_data* data = (struct avg2_data*)initid->ptr;
1137 data->sum= 0.0;
1138 data->count= 0;
1139 }
1140
1141
1142 void
avg2_add(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * message)1143 avg2_add(UDF_INIT* initid, UDF_ARGS* args,
1144 char* is_null __attribute__((unused)),
1145 char* message __attribute__((unused)))
1146 {
1147 if (args->args[0] && args->args[1])
1148 {
1149 struct avg2_data* data = (struct avg2_data*)initid->ptr;
1150 longlong quantity = *((longlong*)args->args[0]);
1151 double sum = *((double*)args->args[1]);
1152
1153 data->count += quantity;
1154 data->sum += sum;
1155 }
1156 }
1157
1158
1159 void
avg2_remove(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * message)1160 avg2_remove(UDF_INIT* initid, UDF_ARGS* args,
1161 char* is_null __attribute__((unused)),
1162 char* message __attribute__((unused)))
1163 {
1164 if (args->args[0] && args->args[1])
1165 {
1166 struct avg2_data* data = (struct avg2_data*)initid->ptr;
1167 longlong quantity = *((longlong*)args->args[0]);
1168 double sum = *((double*)args->args[1]);
1169
1170 data->count -= quantity;
1171 data->sum -= sum;
1172 }
1173 }
1174
1175
1176 double
avg2(UDF_INIT * initid,UDF_ARGS * args,char * is_null,char * error)1177 avg2( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)),
1178 char* is_null, char* error __attribute__((unused)))
1179 {
1180 struct avg2_data* data = (struct avg2_data*)initid->ptr;
1181 if (!data->count)
1182 {
1183 *is_null = 1;
1184 return 0.0;
1185 }
1186
1187 *is_null = 0;
1188 return data->sum/(double)data->count;
1189 }
1190
1191 my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args,
1192 char *message);
1193 char *myfunc_argument_name(UDF_INIT *initid, UDF_ARGS *args, char *result,
1194 unsigned long *length, char *null_value,
1195 char *error);
1196
myfunc_argument_name_init(UDF_INIT * initid,UDF_ARGS * args,char * message)1197 my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args,
1198 char *message)
1199 {
1200 if (args->arg_count != 1)
1201 {
1202 strmov(message,"myfunc_argument_name_init accepts only one argument");
1203 return 1;
1204 }
1205 initid->max_length= args->attribute_lengths[0];
1206 initid->maybe_null= 1;
1207 initid->const_item= 1;
1208 return 0;
1209 }
1210
myfunc_argument_name(UDF_INIT * initid,UDF_ARGS * args,char * result,unsigned long * length,char * null_value,char * error)1211 char *myfunc_argument_name(UDF_INIT *initid __attribute__((unused)),
1212 UDF_ARGS *args, char *result,
1213 unsigned long *length, char *null_value,
1214 char *error __attribute__((unused)))
1215 {
1216 if (!args->attributes[0])
1217 {
1218 *null_value= 1;
1219 return 0;
1220 }
1221 (*length)--; /* space for ending \0 (for debugging purposes) */
1222 if (*length > args->attribute_lengths[0])
1223 *length= args->attribute_lengths[0];
1224 memcpy(result, args->attributes[0], *length);
1225 result[*length]= 0;
1226 return result;
1227 }
1228
1229
1230
is_const_init(UDF_INIT * initid,UDF_ARGS * args,char * message)1231 my_bool is_const_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
1232 {
1233 if (args->arg_count != 1)
1234 {
1235 strmov(message, "IS_CONST accepts only one argument");
1236 return 1;
1237 }
1238 initid->ptr= (char*)((args->args[0] != NULL) ? (size_t)1 : (size_t)0);
1239 return 0;
1240 }
1241
is_const(UDF_INIT * initid,UDF_ARGS * args,char * result,unsigned long * length,char * is_null,char * error)1242 char * is_const(UDF_INIT *initid, UDF_ARGS *args __attribute__((unused)),
1243 char *result, unsigned long *length,
1244 char *is_null, char *error __attribute__((unused)))
1245 {
1246 if (initid->ptr != 0) {
1247 sprintf(result, "const");
1248 } else {
1249 sprintf(result, "not const");
1250 }
1251 *is_null= 0;
1252 *length= (uint) strlen(result);
1253 return result;
1254 }
1255
1256
1257
check_const_len_init(UDF_INIT * initid,UDF_ARGS * args,char * message)1258 my_bool check_const_len_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
1259 {
1260 if (args->arg_count != 1)
1261 {
1262 strmov(message, "CHECK_CONST_LEN accepts only one argument");
1263 return 1;
1264 }
1265 if (args->args[0] == 0)
1266 {
1267 initid->ptr= (char*)"Not constant";
1268 }
1269 else if(strlen(args->args[0]) == args->lengths[0])
1270 {
1271 initid->ptr= (char*)"Correct length";
1272 }
1273 else
1274 {
1275 initid->ptr= (char*)"Wrong length";
1276 }
1277 initid->max_length = 100;
1278 return 0;
1279 }
1280
check_const_len(UDF_INIT * initid,UDF_ARGS * args,char * result,unsigned long * length,char * is_null,char * error)1281 char * check_const_len(UDF_INIT *initid, UDF_ARGS *args __attribute__((unused)),
1282 char *result, unsigned long *length,
1283 char *is_null, char *error __attribute__((unused)))
1284 {
1285 strmov(result, initid->ptr);
1286 *length= (uint) strlen(result);
1287 *is_null= 0;
1288 return result;
1289 }
1290
1291
1292 #endif /* HAVE_DLOPEN */
1293