IPv6 and internationalized domain (IDNA) functions for MySQL
------------------------------------------------------------

======================
IMPORTANT NOTICE:

I'm very happy to see that as of MySQL 5.6.3 functions with an _identical_ API seem to be provided by MySQL, but
with slightly different labels:

    inet6_ntop() -> inet6_ntoa()
    inet6_pton() -> inet6_atop()

I've created aliases towards the native functions in the latest release of this UDF, with which this
module should now provide you with a valid upgrade path. The native functions also operate on a VARBINARY(16).

When loading the functions in this UDF, all examples in the manual work out of the box on pre-5.6 versions of MySQL:

    http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-ntoa

======================

These are experimental runtime plug-in (UDF) for MySQL
that add basic IPv6 and IDNA functions that MySQL v5 is still lacking:

  inet6_ntop and inet6_pton()
  idna_to_ascii() and idna_from_ascii()

These files are experimental and provided as-is under a EUPL license.

Pieter Ennes ([email protected])

Copyright ©2009-2011 WatchMouse


LICENSE
-------

Licensed under the EUPL, Version 1.1 or – as soon they will be approved
by the European Commission - subsequent versions of the EUPL (the "Licence");
You may not use this work except in compliance with the Licence. You may
obtain a copy of the Licence at:

   http://ec.europa.eu/idabc/eupl

Unless required by applicable law or agreed to in writing, software
distributed under the Licence is distributed on an "AS IS" basis,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the Licence for the specific language governing permissions and
limitations under the Licence.


DISCLAIMER
----------

These plug-ins should be considered alpha quality at best. The install instructions are only for
Debian GNU/Linux or derivatives at the moment.


INSTALLATION
------------

Install required compile time dependencies, on Debian you probably catch most of them with:

    $ apt-get install libc6-dev libmysqlclient-dev libidn11-dev


Retrieve the source, from trunk:

    $ hg clone https://bitbucket.org/watchmouse/mysql-udf-ipv6
    $ cd mysql-udf-ipv6

or by getting the latest archive:

    $ curl https://bitbucket.org/watchmouse/mysql-udf-ipv6/get/tip.tar.bz2 | tar xvfj -
    $ cd watchmouse-mysql-udf-ipv6-tip

Brian P Kroth was so kind to provide me with his Debian packaging work, which is now included:

    $ dpkg-buildpackage -us -uc
    $ sudo dpkg -i ../mysql-udf-ipv6_0.4_amd64.deb

If you're not on Debian, compile the UDFs simply with:

    $ make

and install the shared objects into your MySQL plug-in directory:

    $ sudo make install

Note that the plug-in install path has changed around MySQL version 5.0.67, make sure to select the correct path in the Makefile.


USAGE
-----

IPv6 functions:

mysql> CREATE FUNCTION inet6_ntop RETURNS STRING SONAME "mysql_udf_ipv6.so";
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE FUNCTION inet6_pton RETURNS STRING SONAME "mysql_udf_ipv6.so";
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION inet6_lookup RETURNS STRING SONAME "mysql_udf_ipv6.so";
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION inet6_rlookup RETURNS STRING SONAME "mysql_udf_ipv6.so";
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION inet6_mask RETURNS STRING SONAME "mysql_udf_ipv6.so";
Query OK, 0 rows affected (0.00 sec)

IDNA functions:

mysql> CREATE FUNCTION idna_to_ascii RETURNS STRING SONAME "mysql_udf_idna.so";
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION idna_from_ascii RETURNS STRING SONAME "mysql_udf_idna.so";
Query OK, 0 rows affected (0.00 sec)


Unload them with:

mysql> DROP FUNCTION inet6_rlookup; DROP FUNCTION inet6_lookup;
mysql> DROP FUNCTION inet6_pton; DROP FUNCTION inet6_ntop; DROP FUNCTION inet6_mask;

mysql> DROP FUNCTION idna_to_ascii; DROP FUNCTION idna_from_ascii;


EXAMPLES
--------

Notation conversion functions:

mysql> select inet6_ntop(inet6_pton('2001:4860:a005::68')), inet6_ntop(inet6_pton('1.2.3.4'));
+----------------------------------------------+-----------------------------------+
| inet6_ntop(inet6_pton('2001:4860:a005::68')) | inet6_ntop(inet6_pton('1.2.3.4')) |
+----------------------------------------------+-----------------------------------+
| 2001:4860:a005::68                           | 1.2.3.4                           | 
+----------------------------------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> select length(inet6_pton('2001:4860:a005::68')), length(inet6_pton('1.2.3.4'));
+------------------------------------------+-------------------------------+
| length(inet6_pton('2001:4860:a005::68')) | length(inet6_pton('1.2.3.4')) |
+------------------------------------------+-------------------------------+
|                                       16 |                             4 | 
+------------------------------------------+-------------------------------+
1 row in set (0.00 sec)

You can neatly store the output of inet6_pton() in a VARBINARY(16) column.

Mask function:

mysql> select inet6_ntop(inet6_mask(inet6_pton('192.0.2.123'), 24)) as 24bit,
              inet6_ntop(inet6_mask(inet6_pton('2001:db8:1234::1234'), 64)) as 64bit;
+-----------+-----------------+
| 24bit     | 64bit           |
+-----------+-----------------+
| 192.0.2.0 | 2001:db8:1234:: | 
+-----------+-----------------+
1 row in set (0.00 sec)

mysql> select inet6_mask(inet6_pton('2001:db8:1::1'), 64) = inet6_mask(inet6_pton('2001:db8:1::2'), 64) as samenet;
+---------+
| samenet |
+---------+
|       1 | 
+---------+
1 row in set (0.00 sec)

mysql> select inet6_mask(inet6_pton('2001:db8:1::1'), 64) = inet6_mask(inet6_pton('2001:db8:2::2'), 64) as samenet;
+---------+
| samenet |
+---------+
|       0 | 
+---------+
1 row in set (0.00 sec)


Lookup functions:

mysql> select inet6_lookup('www.watchmouse.com');
+------------------------------------+
| inet6_lookup('www.watchmouse.com') |
+------------------------------------+
| 2001:1938:80:73::2                 | 
+------------------------------------+
1 row in set (2.08 sec)

mysql> select inet6_rlookup('2a02:f8::ffff:ffe5'), inet6_rlookup('64.128.190.61');
+-------------------------------------+--------------------------------+
| inet6_rlookup('2a02:f8::ffff:ffe5') | inet6_rlookup('64.128.190.61') |
+-------------------------------------+--------------------------------+
| it.watchmouse.com                   | ny.watchmouse.com              | 
+-------------------------------------+--------------------------------+
1 row in set (0.07 sec)

mysql> select inet6_rlookup(inet6_pton('2a02:f8::ffff:ffe5')), inet6_rlookup(inet6_pton('64.128.190.61'));
+-------------------------------------------------+--------------------------------------------+
| inet6_rlookup(inet6_pton('2a02:f8::ffff:ffe5')) | inet6_rlookup(inet6_pton('64.128.190.61')) |
+-------------------------------------------------+--------------------------------------------+
| it.watchmouse.com                               | ny.watchmouse.com                          | 
+-------------------------------------------------+--------------------------------------------+
1 row in set (0.04 sec)

Don't push too many rows through the lookup function, as each lookup may take many seconds to complete.

Internationalized domain functions:

mysql> select idna_to_ascii("testme.ভারত");
+--------------------------------------+
| idna_to_ascii("testme.ভারত") |
+--------------------------------------+
| testme.xn--45brj9c                   |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select idna_from_ascii("testme.xn--45brj9c");
+---------------------------------------+
| idna_from_ascii("testme.xn--45brj9c") |
+---------------------------------------+
| testme.ভারত                   |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select idna_from_ascii(idna_to_ascii("македонија.icom.museum"));
+--------------------------------------------------------------------+
| idna_from_ascii(idna_to_ascii("македонија.icom.museum")) |
+--------------------------------------------------------------------+
| македонија.icom.museum                                   |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)