[Level 3] Create UDF for Storing MAC Address.

Last time, I introduce about MySQL have two function call inet_aton(), inet_ntoa().
These two functions are for convert IPv4 address between char and it. By reducing the size of storing IPv4 data ( less disk I/O ), to increase the query performance. Click here for reference.

After that, I write the stored procedure for this purpose. Click here for reference.

Now I want to show up how to create a UDF for convert MAC address data between char and byte. If you have no idea about how to create UDF, Click here for reference.

The step as the following:
1. Prepare a template source code ( for C Language ) from MySQL udf sample file called 'udf_example.c'. If you don't have the MySQL source code, you can download it from official website. ( In my server, the source code folder is /usr/src/redhat/SOURCES/mysql-5.1.44/sql/udf_example.c ).

2. Prepare the source code for convert MAC address from char to byte ( you can write it by your own or google it, there are many samples you can find on the net. ). And I download the sample code from code project, it create by Anders Molin.

3. Modify the udf_example.c. In step 3, I'll show you some tips about how to modify the sample code.

a. declare you function name first.

// convert MAC from char to byte

my_bool udf_ether_atob_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void udf_ether_atob_deinit(UDF_INIT *initid);
char *udf_ether_atob(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);

// convert MAC from byte to char

my_bool udf_ether_btoa_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void udf_ether_btoa_deinit(UDF_INIT *initid);
char *udf_ether_btoa(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);

You can see that, I high-light the function name with color red, and function with "_init" means what to do before call the main program, and function with "_deinit" means what to do after leave the main program. So you can pre-allocate the resource in the "_init" block, and release the resouce in th "_deinit" block.

b. define the max length of result.

#define a_MAXMETAPH 17

#define b_MAXMETAPH 6

In this case tha max length of char is 17, and the length of byte is 6.

c. Define the init function.

my_bool udf_ether_atob_init(UDF_INIT *initid, UDF_ARGS *args, char

*message)
{
  if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT)
  {
    strcpy(message,"Wrong arguments to udf_ether_atob;  Use the source");
    return 1;
  }
  initid->max_length=b_MAXMETAPH;
  return 0;
}
 

my_bool udf_ether_btoa_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if (args->arg_count != 1) // not check data type
  {
    strcpy(message,"Wrong arguments to udf_ether_btoa;  Use the source");
    return 1;
  }
  initid->max_length=a_MAXMETAPH;
  return 0;
}
 

You have see that, this two init function both check the number of input parameter. But in udf_ether_btoa_init, it doesnot check the data type of parameter, just because the input parameter is "Byte", and you can ignore to check it.





d. modify the main program.
In udf_ether_atob():

char *udf_ether_atob (...) {

  const char *pszMACAddress=args->args[0];
  char *pbyAddress;
  pbyAddress=result;

...

  if (!pszMACAddress)                                   /* Null argument */
  {
    *is_null=1;
    return 0;
  }

...
  *length= (unsigned long) 6;


  return pbyAddress;
}

I high-light 4 points that you have to take care:
i. use "args->args[num]" to get the input parameters.
ii. define the result value with "pointer", and the caller will also send the point called "result" to reference the result value that you want to pass to MySQL.
iii. remember to check if the parameter is NULL or not.
iv. set the parameter pass from caller called "length", to let MySQL knows the size of the value you return.

in udf_ether_btoa():

char *udf_ether_btoa(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error __attribute__((unused)))
{
  const char *pbyMacAddressInBytes=args->args[0];

  char *pszMACAddress;
  pszMACAddress=result;
  char cSep=':';
...
  if (!pbyMacAddressInBytes)                                    /* Null argument */
  {
    *is_null=1;
    return 0;
  }

...
  *length= (unsigned long) 17;
  return pszMACAddress;
} 

Just the same likt udf_ether_atob, in this time, although we want to pass "Byte" to MySQL, but you can still use the type "char" to return.
PS. In real testing, I find out that in my environment (CentOS5.4) will have "big-endian, small-endidan" issue. Therefor I modify the source code udf_ether_btoa(), to print the value reversely. ( print char from arry[5], array[4] ... to array[0] )

5. compile it and copy the library file to MySQL plugin folder.
Ex.

# gcc -o ./udf_ether.so -shared -fPIC -I /usr/local/include/mysql ./udf_ether.c

&& cp ./udf_ether.so /usr/local/mysql/lib/mysql/plugin/

6. create MySQL function with library.

mysql> DROP FUNCTION IF EXISTS udf_ether_atob;

mysql> CREATE FUNCTION udf_ether_atob RETURNS STRING SONAME 'udf_ether.so';

mysql> DROP FUNCTION IF EXISTS udf_ether_btoa;
mysql> CREATE FUNCTION udf_ether_btoa RETURNS STRING SONAME 'udf_ether.so';

mysql> DROP TABLE IF EXISTS test.test_udf_ether;
mysql> CREATE TABLE test.test_udf_ether (a char(17), b bit(48));
 

mysql> insert into test.test_udf_ether values ('00:ab:cd:ef:00:99',udf_ether_atob('00:ab:cd:ef:00:99'));
mysql> select a, udf_ether_btoa(b) from test.test_udf_ether;
+-------------------+--------------------------+
| a                 | udf_ether_btoa(b) |
+-------------------+--------------------------+
| 00:ab:cd:ef:00:99 | 00:ab:cd:ef:00:99        |
+-------------------+--------------------------+

Hints for MySQL UDF:

1. Download source code
2. # ./configure --with-mysqld-ldflags=--rdynamic
3. # gcc -shared -o ./udf_example.so ./udf_example.c -I/usr/local/include/mysql -fPIC
4. # cp ./udf_example.so /usr/local/mysql/lib/mysql/plugin
5. mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 22 /usr/local/lib/mysql/plugin/udf_example.so: cannot restore segment prot after reloc: Permission denied)
6. # chcon -t texrel_shlib_t /usr/local/lib/mysql/plugin/udf_example.so


Wish this helps.

regards,
Stanley Huang