12.8 MySQL-Funktionen mit der UDF-Schnittstelle entwerfen 

Damit der MySQL-Server UDF-Funktionen verwenden kann, sollte mysqld mit dem Flag –with-mysqld-ldflags=-rdynamic konfiguriert sein, um auf alle Symbole von mysqld zugreifen zu können.

Der Grund liegt darin, dass Sie auf vielen Plattformen (inklusive Linux) eine dynamische Bibliothek (mit dlopen()) von einem statisch gelinkten Programm laden können, was Sie erhalten würden, wenn Sie --with-mysqld-ldflags=-all-static benutzen. Die Funktion dlopen() wird zu einem späteren Zeitpunkt im Buch behandelt.

Wenn Sie eine Funktion schreiben, die Sie für SQL-Statements verwenden wollen, sollten Sie einen entsprechenden C-Funktionsnamen wählen, den Sie auch für MySQL verwenden. Beim Schreiben einer Funktion mit der UDF-Schnittstelle wird Ihnen aber nicht absolut freie Hand gelassen, sondern Sie müssen einiges dabei beachten. Eine UDF-Funktion sieht demnach wie folgt aus:

rückgabetyp funktionsname()

Dies ist die Hauptfunktion (bitte nicht mit der main()-Hauptfunktion verwechseln), mit deren Namen auch das SQL-Statement aufgerufen wird. funktionsname() dient hier nur als Beispiel und kann durch jeden erlaubten Funktionsnamen ersetzt werden. Als Rückgabewert kommen hier im Zusammenhang mit den SQL-Datentypen für Ihre C/C++-Funktion(en) folgende in Frage:

Optional hingegen sind die Funktionen zum Initialisieren bzw. Deinitialisieren. Die Syntax einer Initialisierung sieht wie folgt aus (bezogen auf die Hauptfunktion funktionsname()):

funktionsname_init()

Die Initialisierungsfunktion kann für folgende Aufgaben verwendet werden:

Die Anzahl von Argumenten überprüfen

Die Datentypen überprüfen, die als Argumente übergeben werden. Sind die Argumente nicht vom erforderlichen Typ, müssen Sie dies MySQL mitteilen, um den Argumenttyp zu erzwingen, der für die Hauptfunktion benötigt wird.

Speicher für die Hauptfunktion bereitstellen

Maximale Länge des Rückgabewertes festlegen

Maximale Anzahl von Dezimalstellen für den REAL-Typ (double) festlegen

Festlegen, ob das Ergebnis NULL sein darf oder nicht

Die Funktion zum Deinitialisieren sieht in der Syntax so aus:

funktionsname_deinit() 

Dies ist die Deinitialisierungsfunktion für funktionsname(). Damit sollte jeglicher Speicher freigegeben (Deallokation) werden, der durch die Initialisierungsfunktion zugewiesen wurde.

Wie schon erwähnt, die Funktionen zum Deinitialisieren und zur Initialisierung sind optional und müssen nicht verwendet werden.

Angenommen Ihre Funktion ist bereits in MySQL implementiert und Sie rufen diese nun mit funktionsname() auf, dann ruft MySQL zuvor noch (vor der eigentlichen Hauptfunktion) die Initialisierungsfunktion funktionsname_init() auf, um alle notwendigen Einrichtungen, wie Argumente überprüfen oder die Speicherzuweisung übernehmen, vorzunehmen. Gibt funktionsname_init() einen Fehler zurück, wird das SQL-Statement mit einer entsprechenden Fehlermeldung abgebrochen. Demnach werden bei einem Fehler die Haupt- und Deinitialisierungsfunktion nicht mehr aufgerufen. Wenn alles glatt verlief, wird hingegen funktionsname() aufgerufen und ausgeführt. Wurde die Hauptfunktion ausgeführt, führt die Deinitialisierungsfunktion am Ende alle Aufräumarbeiten durch.

12.8.1 UDF-Sequenzen 

Die Hauptfunktion können Sie auch nicht angeben, wie es Ihnen gerade passt (abgesehen vom Funktionsnamen), sondern auch hierbei müssen Sie einiges beachten. Abhängig vom Rückgabetyp Ihrer Funktion müssen Sie nachfolgende Parameter verwenden, damit STRING, INTEGER oder REAL zurückgegeben werden – was das CREATE FUNCTION-Statement auch fordert:

Bei STRING-Funktionen:

char *funktionsname( UDF_INIT *initid, UDF_ARGS *args,
                     char *result, unsigned long *length,
                     char *is_null, char *error );

Bei INTEGER-Funktionen:

long long funktionsname( UDF_INIT *initid, UDF_ARGS *args,
                         char *is_null, char *error );

Bei REAL-Funktionen:

double funktionsname( UDF_INIT *initid, UDF_ARGS *args,
                      char *is_null, char *error );

Die Initialisierungs- und Deinitialisierungsfunktionen werden immer wie folgt deklariert:

my_bool funktionsname_init( UDF_INIT *initid,
                            UDF_ARGS *args, 
                            char *message);
void funktionsname_deinit( UDF_INIT *initid );

12.8.2 UDF_INIT-Struktur 

Der initid-Parameter wird an alle Funktionen übergeben. Dieser verweist auf eine UDF_INIT-Struktur, die benutzt wird, um Informationen zwischen den Funktionen zu übergeben. Die Initialisierungsfunktion funktionsname_init() sollte alle Strukturvariablen verwenden, die verändert werden sollen. Die Variablen, die Sie nicht verändern wollen, können Sie auf deren Vorgabewert stehen lassen. Folgende UDF_INIT-Strukturvariablen sind dabei vorhanden:

my_bool maybe_null – funktionsname_init() sollte maybe_null auf 1 setzen, wenn funktionsname() NULL zurückgeben kann. Der Vorgabewert ist 1, wenn eines der Argumente als maybe_null deklariert ist.

unsigned int decimals – Anzahl von Dezimalstellen. Der Vorgabewert ist die maximale Anzahl von Dezimalstellen in den Argumenten, die an die Hauptfunktion übergeben werden. (Wenn der Funktion beispielsweise die Argumente 1.34, 1.345 und 1.3 übergeben werden, wäre der Vorgabewert 3, weil 1.345 drei Dezimalstellen hat.)

unsigned int max_length – Maximale Anzahl der Zeichen im String. Der Vorgabewert ist abhängig vom Ergebnistyp der Funktion. Bei String-Funktionen ist die Vorgabe die Länge des längsten Arguments. Bei Ganzzahl-Funktionen ist die Vorgabe 21 Ziffern. Bei REAL-Funktionen ist die Vorgabe 13 plus die Anzahl von Dezimalstellen, die von initid->decimals angezeigt werden. (Bei nummerischen Funktionen enthält die Länge jedes Vorzeichen- oder Dezimalpunkt-Zeichen.) Wenn Sie einen BLOB zurückgeben wollen, können Sie diesen auf 65 KB oder 16 MB setzen. Der Speicher wird nicht zugewiesen, aber dazu verwendet, um zu entscheiden, welcher Spaltentyp benutzt werden soll, falls es notwendig werden sollte, Daten temporär zu speichern.

char *ptr – Ein Zeiger, der für eigene Zwecke verwendet werden kann. Beispielsweise können Funktionen initid->ptr benutzen, um Informationen über den zugewiesenen Speicher zwischen den Funktionen zu kommunizieren, beispielsweise um in funktionsname_init() Speicher zuzuweisen und ihm den folgenden Zeiger zuzuordnen: initid->ptr = reservierter_speicher; In funktionsname() können Sie diesen Speicher anschließend verwenden, und in funktionsname_deinit() wird dieser wieder freigegeben.

12.8.3 UDF_ARGS-Struktur 

Den Parameter args beinhaltet bis auf funktionsname_deinit() ebenfalls alle Funktionen. Dabei handelt es sich um einen Zeiger auf die UDF_ARGS-Struktur, die mit folgenden Strukturvariablen bestückt ist:

unsigned int arg_count – Hier befindet sich die Anzahl der Argumente. Gewöhnlich wird dieser Wert in der Initialisierungsfunktion funktionsname_init() überprüft, wenn Sie wollen, dass Ihre Funktion mit einer bestimmten Anzahl von Argumenten aufgerufen wird. Beispiel der Anwendung:

enum Item_result *arg_type – Hier befindet sich der Typ für die Argumente. Die Typenwerte können Sie mit den Konstanten STRING_RESULT, INT_RESULT und REAL_RESULT folgendermaßen überprüfen:

char **args – Hierbei handelt es sich um die tatsächlichen Argumente, mit denen die Funktion aufgerufen wurde (ähnlich wie argv in der main()-Funktion). Ist das Argument x z. B. ein konstanter Wert, zeigt args->args[x] auf den Argumentwert. Ist der Wert hingegen nicht konstant, ist args->args[x] 0. Was ein konstanter Wert ist, muss ich Ihnen nicht mehr erzählen. Ein nicht –konstantes Argument hingegen ist ein Ausdruck, der auf Werte verweist, die sich von Zeile zu Zeile ändern können wie Spaltennamen oder Funktionen, die mit nicht –konstanten Argumenten aufgerufen werden. Nach jedem Aufruf der Funktion funktionsname() enthält args->args die tatsächlichen Argumente, die für die Zeile übergeben wurden und momentan überarbeitet werden.

unsigned long *lengths – Hier befindet sich die Länge des Strings, für jedes Argument. Bei Argumenten des Typs INT_RESULT oder REAL_RESULT enthält lengths immer noch die maximale Länge des Arguments (wie bei der Initialisierungsfunktion).

12.8.4 Rückgabewert 

Die Initialisierungsfunktion funktionsname_init() sollte, wenn alles glatt verlief, 0, ansonsten bei einem Fehler 1 zurückliefern. Als Fehlermeldung sollten Sie einen terminierten String mit max. MYSQL_ERRMSG_SIZE Länge in den message-Parameter von funktionsname_init() schreiben. Der Rückgabewert von funktionsname() sollte entsprechend der Deklaration dem Wert entsprechen, wie dies C-typisch ist. Bei long long wird eben ein entsprechender Wert zurückgegeben. Bei einem String sollte ein Zeiger auf den Anfang plus der Länge des Strings zurückgegeben werden, z. B.:

memcpy(result, "ein_string_als_rueckgabe", 24);
*length = 24;

Wenn die Zeichenkette die Länge von 255 Bytes nicht überschreitet, müssen Sie sich nicht um die Speicherzuweisung von result kümmern. Ist Ihr Ergebnis allerdings länger, müssen Sie eine dynamische Speicherzuweisung mit malloc() in den Funktionen funktionsname_init() oder funktionsname() vornehmen. Sie können den zugewiesenen Speicher im ptr-Slot in der UDF_INIT-Struktur für eine erneute Benutzung durch zukünftige funktionsname()-Aufrufe speichern.

Wollen Sie den Rückgabewert NULL anzeigen, müssen Sie den Parameter is_null auf 1 setzen. Ebenso auf 1 setzen müssen Sie den error-Parameter, wenn Sie eine Fehlerrückgabe der Hauptfunktion anzeigen wollen.

12.8.5 Benutzerdefinierte Funktionen erstellen 

Damit Sie eine Vorstellung davon bekommen, wie Sie benutzerdefinierte Funktionen erstellen können, soll anhand zweier Beispiele, STRING_RESULT und INT_RESULT, jeweils eine einfache Funktion erstellt werden.

Zuerst ein Beispiel zu einer benutzerdefinierten Funktion für Strings. Die Funktion erwartet einen Parameter als String und dreht diesen String anschließend einfach um – und gibt dies an den MySQL-Server zurück.

/* reverse.c */
#include <mysql/mysql.h>
#include <string.h>
#define MAX 255
char *
reverse (UDF_INIT * initid, UDF_ARGS * args, char *result,
         unsigned long *length, char *is_null, char *error) {
  char name_buf[256];
  int i, j;
  char c;
  if (!args->args[0]) {
    /* Return NULL for NULL values */
    *is_null = 1;
    return 0;
  }
  length = args->lengths[0];
  strcpy (name_buf, args->args[0]);
  for (i = 0, j = strlen (name_buf) - 1; i < j; i++, j--) {
    c = name_buf[i];
    name_buf[i] = name_buf[j];
    name_buf[j] = c;
  }
  memcpy (result, name_buf, (int)length);
  result[(int)length] = 0;
  return result;
}
my_bool
reverse_init (UDF_INIT * initid,UDF_ARGS * args,char *message) {
  if (args->arg_count == 1)
    args->arg_type[0] = STRING_RESULT;
  else {
    strncpy (message, "UPPER() mit einem Argument aufrufen!",
             MYSQL_ERRMSG_SIZE);
    return 1;
  }
  initid->max_length = sizeof (args->args[0]);
  initid->maybe_null = 1;
  return 0;
}

Das zweite Beispiel, ein Beispiel mit einem Integer als Rückgabewert, übernimmt eine Reihe von Zahlen und rechnet diese zu einer Gesamtsumme zusammen und gibt diesen Wert an den MySQL-Server zurück.

/* summe.c */
#include <mysql/mysql.h>
#include <string.h>
#include <stdlib.h>
long long
summe( UDF_INIT * initid, UDF_ARGS * args, char *is_null,
       char *error ) {
  long long summe=0;
  unsigned int i;
  for(i = 0; i<args->arg_count; i++)
    summe += *((long long *) args->args[i]);
  return summe;
}
my_bool
summe_init (UDF_INIT * initid, UDF_ARGS * args, char *message) {
  unsigned int i;
  if (args->arg_count < 1) {
    strncpy (message,
             "SUMME(): Mindestens 1 Argument angeben!",
             MYSQL_ERRMSG_SIZE);
    return 1;
  }
  for (i = 0; i < args->arg_count; i++)
    if (args->arg_type[i] != INT_RESULT) {
      strncpy (message,
               "SUMME(): Nur eine Ganzzahl als Argument!",
               MYSQL_ERRMSG_SIZE);
      return 1;
    }
  initid->maybe_null = 0;
  return 0;
}

12.8.6 Benutzerdefinierte Funktion kompilieren, installieren und ausführen 

Wollen Sie eine Datei dynamisch ladbar machen, sollte diese als gemeinsame nutzbare Objektdatei kompiliert werden. Dies können Sie mit folgendem Befehl (als Beispiel die Stringfunktion reverse.c):

$ gcc -shared -o reverse.so reverse.c

Das gemeinsame Objekt reverse.so müssen Sie nun mit MySQL bekannt machen. Dazu sollten Sie diese Datei erst in den Pfad kopieren, den auch der dynamische Linker ld-linux.so durchsucht. Dieser Pfad kann von System zu System variieren. Meistens ist dies der Pfad /usr/lib oder /usr/local/lib. Sie können diesen aber auch über die Umgebungsvariable LD_LIBRARY_PATH so setzen, dass auf ein Verzeichnis gezeigt wird, wo sich die UDF-Funktionsdateien befinden. Ein Blick in die Manual Pages von dlopen() oder in ein späteres Kapitel verrät Ihnen dazu mehr.

Noch ein anderer eleganter Weg unter Linux wäre (Pfad zum lib-Verzeichnis bitte entsprechend anpassen):

$  mkdir /usr/local/lib/my_mysql_funcs
$  cd /usr/local/lib/my_mysql_funcs
$  cp /path/to/new/func ./
$  echo "/usr/local/lib/my_mysql_funcs" >> /etc/ld.so.conf
$  ldconfig

Unter BSD kann man hier die Variablen in der rc.conf setzen – doch dies führt hier zu weit.

Nachdem Sie die Bibliothek kopiert haben, können Sie den MySQL-Server mit der neuen Funktion bekannt machen (auch hier: Pfad zum lib-Verzeichnis bitte entsprechend anpassen):

$ gcc -shared -o reverse.so reverse.c
$ su
Password:********
# cp reverse.so /usr/local/lib
# rcmysql start
Starting service MySQL                                        done
# exit
exit
$ mysql -uroot -hlocalhost -pk4p6m3o3
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.55-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE FUNCTION reverse
    -> RETURNS STRING SONAME "reverse.so";
...
mysql> SELECT reverse(»Das ist ein langer Text«);
+------------------------------------+
| reverse("Das ist ein langer Text") |
+------------------------------------+
| txeT regnal nie tsi saD            |
+------------------------------------+
1 row in set (0.00 sec)

Neue Funktionen werden also mit CREATE FUNCTION angemeldet. Hier die genaue Syntax zum Anmelden von Funktionen in MySQL:

CREATE FUNCTION <funktionsname> RETURNS (STRING | REAL | INTEGER) 
SONAME <name der shared library>;

Wichtig dabei ist auch, dass der Funktionsname der Hauptfunktion im Listing entsprechen muss. Bei RETURNS müssen Sie den (richtigen) Datentyp angeben, der von der Hauptfunktion zurückgegeben wird. Hinter SONAME geben Sie den Dateinamen der UDF-Datei an. Wie schon erwähnt, ist der Ort der Datei zweitrangig, solange ld-linux.so die Datei über einen Suchpfad findet.

Wollen Sie zum Beispiel die Funktion summe() zum Addieren mehrerer Ganzzahlwerte bei MySQL anmelden, können Sie dies folgendermaßen machen (natürlich muss auch hier entsprechend kompiliert und die Datei installiert werden, wie Sie es zuvor gesehen und (hoffentlich) gemacht haben):

mysql> CREATE FUNCTION summe RETURNS INTEGER SONAME "summe.so";
...
mysql> SELECT summe(5, 5, 5, 6);
+-------------------+
| summe(5, 5, 5, 6) |
+-------------------+
|                21 |
+-------------------+
1 row in set (0.32 sec)

Selbst erstellte UDF-Funktionen können mit dem SQL-Befehl DROP FUNCTION wieder gelöscht werden:

mysql> DROP FUNCTION reverse;
mysql> DROP FUNCTION summe;

Die benutzerdefinierten Funktionen werden dabei in der Verwaltungsdatenbank mysql in der Tabelle func gespeichert, weshalb Sie oder der Entwickler der benutzerdefinierten Funktion auch entsprechende Schreibrechte für CREATE FUNCTION und Löschrechte für DROP FUNCTION benötigen.

mysql> USE mysql;
Database changed
mysql> SELECT * FROM func;
+---------+-----+------------+----------+
| name    | ret | dl         | type     |
+---------+-----+------------+----------+
| reverse |   0 | reverse.so | function |
| summe   |   2 | summe.so   | function |
+---------+-----+------------+----------+
2 rows in set (0.00 sec)

Wollen Sie entgegen der standardmäßigen Einstellung, dass die benutzerdefinierten Funktionen nicht beim Start vom MySQL-Server mitgeladen werden, müssen Sie den Server mit dem Flag –skip-grant-tables starten.