Mysql UDF

Mysql UDF (User-Defined Function Interface)，可以用于Mysql功能扩展。
一般来说使用C/C++实现。

    这里实现一个名字为fx_msg的function.
主要功能是开启一个线程进行监听sockt连接，
当调用此函数时将指定的消息发送到socket连接上.

select FX_MSG(CMD[,PARM1,PARM2...]);

初始化 FX_MSG 监听环境
CMD:INIT PARM:int MAX_Conn , int PORT

关闭所有连接，关才监听
CMD:CLOSE

查看当前连接Client信息
CMD:LISTCLIENT

查看当前版本信息
CMD:VERSION

还设想了一些功能，懒得做了。。。
.....

 

 

布置开发环境.
不喜欢 M$ 的 VC （也是不太会用)(-:
还是用eclipse习惯。哈哈。。。。

呵呵，操作系统还是比较习惯用XP.....

eclipse 3.3 CDT 用来起还是相当不
错(-: .  不过它只是一个编辑器，
不具备编译功能。

download cygwin 做为cygwin编译器.
注意安装的时候要 选中 开发工具中
的 gcc / g++ 等。

要做mysql的UDF当然需要mysql的一些
头文件 ，去mysql.com下载mysql的原
码包。 解开，里面有个include目录。
使用这个就可以了。
不过我使用的时候里面有几个和mysql
版本号相关的预编译宏编译不过,不知道
为啥了，手动处理一下。该删的删，该改
的改。

 

闲话少说，动手:

A. eclipse 新建一个Shared Library工程 。
呵呵，别建搞错了。默认可不是这个(-:

B.选择工具链:Cygwin GCC

C.Project Properties中把mysql的include包含进来

原代码:

//data_struct.h
//***********************************************
#ifndef DATASTRUCT_H_
#define DATASTRUCT_H_

/**
 * client connection infomation
 */
typedef struct client{
 long sock_fd ;
 char *ip     ;
 long port    ;
 struct client *next;
}Client,*ClientPtr;

 

typedef struct {
 ClientPtr g_client;
 long g_sockFD     ;
 int INITOK        ; 
} Status;

#endif /*DATASTRUCT_H_*/

 

fxmsg.h
//*********************************************
#include "mysql.h"
#include 
#include 
#include 
#include 
#include 
#include 
#include 
#include 

#include "data_struct.h"

#ifndef FXMSG_H_
#define FXMSG_H_

 

#define BOOL_TRUE  100
#define BOOL_FALSE 200


int initCheck(UDF_INIT *initid, UDF_ARGS *args, char *message);
int sendCheck(UDF_INIT *initid, UDF_ARGS *args, char *message);
int doInit(UDF_INIT *initid, UDF_ARGS *args, char *message);
int closeCheck(UDF_INIT *initid, UDF_ARGS *args, char *message);
int doClose(UDF_INIT *initid, UDF_ARGS *args, char *message);
void fxlog(char *c);

Status g_status;

#endif /*FXMSG_H_*/

 

cmd_close.c
//************************************

#include "fxmsg.h"

int closeCheck(UDF_INIT *initid, UDF_ARGS *args, char *message){
 if(args->arg_count !=1){
  strcpy(message,"ERROR. ERR Parameter! Eg: select fx_msg('CLOSE');");
  return BOOL_FALSE;
 }
   
 if( BOOL_FALSE == g_status.INITOK ){
  strcpy(message,"ERROR. INITOK is false.");
  return BOOL_FALSE;
 }
 
 return BOOL_TRUE;
}

 

int doClose(UDF_INIT *initid, UDF_ARGS *args, char *message){
 
 ClientPtr temp ;
 
 while(g_status.g_client!=NULL){
  temp = g_status.g_client;
  g_status.g_client = g_status.g_client->next;
  
  char buf[200];
  sprintf(buf," close connection [fd:%ld] ip:%s:%ld",temp->sock_fd,temp->ip,temp->port);
  fxlog(buf);
  
  close(temp->sock_fd);
  free(temp);
 }
 
 char buf[200];
 sprintf(buf," close g_sockFD ! [fd:%ld] ",g_status.g_sockFD);
 fxlog(buf);
 
 close(g_status.g_sockFD);
 
 g_status.INITOK = BOOL_FALSE;
 
 char buf2[200];
 sprintf(buf2," INITOK:%d ",g_status.INITOK);
 fxlog(buf2);
 
 return BOOL_TRUE;
}

 

 


cmd_init.c
//*******************************

#include "fxmsg.h"


int initCheck(UDF_INIT *initid, UDF_ARGS *args, char *message){
 if(args->arg_count !=3 || args->arg_type[1]!=INT_RESULT || args->arg_type[2]!=INT_RESULT){
  strcpy(message,"ERROR. ERR Parameter! Eg: select fx_msg('INIT',10,20000);");
  return BOOL_FALSE;
 }
   
 if( BOOL_TRUE == g_status.INITOK ){
  strcpy(message,"ERROR. Already INIT OK."); 
  return BOOL_FALSE;
 }
 
 return BOOL_TRUE;
}


void *accepter(void *sockfd);
int initServer(int port , int listenQueueLength);

int doInit(UDF_INIT *initid, UDF_ARGS *args, char *message){
 
 const long port = *((long long*) args->args[2]);

 char logBuffer[200];
 sprintf(logBuffer,"listenPort:%ld",port);
 fxlog(logBuffer);
 
 g_status.g_sockFD = initServer(port , 10);
 
 if(g_status.g_sockFD == -1){
  strcpy(message,"InitServer ERR!"); 
  return BOOL_FALSE;
 }
 
 
 pthread_t tid;
 pthread_create(&tid,NULL,(void*)&accepter,NULL);
 
 return BOOL_TRUE;
}

 


void *accepter(void *sockfd){
 while( 1 ){
  
  struct sockaddr_in info;
  int buf_size = sizeof(struct sockaddr_in);
  int fd = accept(g_status.g_sockFD,(struct sockaddr *)&info, &buf_size);
  
  
  char logbuf[100];
  sprintf(logbuf,"accept return -> fd:%d,g_sockFD:%ld",fd,g_status.g_sockFD);
  fxlog(logbuf);
  
  if(fd<0){
   fxlog("************* because fd<0 so accepter end!************");
   return (void*)-1;
  }
    
  ClientPtr temp = (ClientPtr)malloc(sizeof(Client));
  
  if(!temp){
   fxlog("malloc result adder is 0");
   close(fd);
   return (void*)-1;
  }
  
  temp->sock_fd = fd;
  temp->port=info.sin_port;
  temp->ip=inet_ntoa(info.sin_addr);
  
  if(g_status.g_client == NULL){
   g_status.g_client = temp;
   temp->next = NULL;
  }else{
   temp->next = g_status.g_client;
   g_status.g_client = temp;
  }
 }
}

 

/**
 * 初始化服务器，绑定监听端口，开始监听
 * RET socket 描述符 / -1出错
 */
int initServer(int port , int listenQueueLength){
 
 int sock_fd=0;
 
 if((sock_fd = socket(AF_INET,SOCK_STREAM,0)) == -1){
  return -1;
 }
  
  
 /**
  * 设定监听协议、端口等
  */
 struct sockaddr_in servaddr;
 servaddr.sin_family = AF_INET;
 servaddr.sin_port = htons(port);
 servaddr.sin_addr.s_addr = htons(INADDR_ANY);

  
 /**
  * 绑定监听端口
  */
 if( bind(sock_fd,(struct sockaddr*)&servaddr,sizeof(servaddr)) == -1){
  return -1;
 }


 /**
  * 监听
  */
 if( listen(sock_fd,listenQueueLength) == -1){
  return -1;
 }
 
 return sock_fd;
}

    

 


cmd_send.c
//*****************************

#include "fxmsg.h"

int sendCheck(UDF_INIT *initid, UDF_ARGS *args, char *message){
 if(args->arg_count !=2){
  strcpy(message,"ERROR. ERR Parameter! Eg: select fx_msg(&apos;SEND&apos;,&apos;a message&apos;);");
  return 1;
 }
   
 if(args->arg_type[0]!=STRING_RESULT || args->arg_type[1]!=STRING_RESULT){
  strcpy(message,"ERROR. ERR Parameter! Eg: select fx_msg(&apos;SEND&apos;,&apos;a message&apos;);");
  return 1;
 }
 
 return 0;
}

 

 


//fxmsg.c
//********************************


#include "mysql.h"
#include "fxmsg.h"

my_bool fx_msg_init(UDF_INIT *initid, UDF_ARGS *args, char *message){
 
 /**
  *  parm is null;
  */
 if(args->arg_count == 0){
  strcpy(message,"ERROR. NO Parameter! Eg: select fx_msg(CMD[,p1,p2...]);");
  return 1;
 }
 
 if(args->arg_type[0]!=STRING_RESULT){
  strcpy(message,"ERROR. Type of CMD is String!");
  return 1;
 }
 
 const char *CMD =  args->args[0];
 
 /**
  * INIT CHECK
  */
 if( strcmp(CMD,"INIT") == 0 ){
  
  fxlog("INIT...");
  
  pthread_mutex_t INIT_LOCK=PTHREAD_MUTEX_INITIALIZER; 
  if(pthread_mutex_lock(&INIT_LOCK)!=0){
   strcpy(message,"ERROR. lock INIT_LOCK Err!");
   return 1;
  }
  
  if(initCheck(initid, args, message) == BOOL_FALSE){
   pthread_mutex_unlock(&INIT_LOCK);
   return 1;
  }
  
  int ret = doInit(initid, args, message) ;
  
  char buf1[100];
  sprintf(buf1,"INITOK:%d",g_status.INITOK);
  fxlog(buf1);
  
  g_status.INITOK = BOOL_TRUE;
  
  char buf2[100];
  sprintf(buf2,"INITOK:%d",g_status.INITOK);
  fxlog(buf2);
  
  pthread_mutex_unlock(&INIT_LOCK);
  
  if(ret == BOOL_TRUE){
   return 0;
  }else{
   return 1;
  }
 }
 
 
 else if( strcmp(CMD,"CLOSE") == 0 ){
  
  fxlog("CLOSE...");
  
  if(closeCheck(initid, args, message) == BOOL_FALSE ){
   return 1;
  }
  
  doClose(initid, args, message);
  
  return 0;
 }
 
 
 else if( strcmp(CMD,"SEND") == 0 ){
  
  fxlog("SEND...");
  
  return sendCheck(initid, args, message);
 }
 
 
 else if( strcmp(CMD,"LISTCLIENT") == 0){
  fxlog("LISTCLIENT...");
 }
 
 
 else if( strcmp(CMD,"VERSION") == 0){
  fxlog("VERSION...");
 }
 
 else{
  strcpy(message,"ERROR. Unknow CMD!");
  return 1;
 }

 
 initid->max_length=255;
 initid->maybe_null=1;
 
 return 0;
}

 

 

char *fx_msg(UDF_INIT *initid, UDF_ARGS *args, char *result,unsigned long *length, char *is_null, char *error){
 
 const char *CMD =  args->args[0];
 if( strcmp(CMD,"INIT") == 0 ){
  *length=strlen("OK");
  memcpy(result,"OK",*length);
  return result;
 }
 
 else if( strcmp(CMD,"CLOSE") == 0 ){
  *length=strlen("OK");
  memcpy(result,"OK",*length);
  return result;
 }
 
 else if( strcmp(CMD,"SEND") == 0 ){
  
  fxlog("SEND....1...");
  
  const char *msg=args->args[1];
  int msgLen = strlen(msg);
  ClientPtr temp = g_status.g_client;
  while(temp != NULL){
   fxlog("SEND....2...");
   long sendbyte = send(temp->sock_fd,msg,msgLen,0);
   char senBuf[2000];
   sprintf(senBuf,"send message to %ld [%ld bytes]!",temp->sock_fd,sendbyte);
   if(sendbyte == -1){
    strcat(senBuf," Send Message Err!");
   }
   fxlog(senBuf);
   
   temp = temp->next;
  }
  fxlog("SEND....3...");
  memcpy(result,msg,(size_t)msgLen);
  *length=msgLen;
  return result;
 }
 
 
 else if( strcmp(CMD,"LISTCLIENT") == 0){
  char buf[1000];
  sprintf(buf,"==========ClientList========/n");
  ClientPtr temp = g_status.g_client;
  while(temp != NULL){
   char xbuf[100];
   sprintf(xbuf,"fd:%ld ip:%s port:%ld/n",temp->sock_fd,temp->ip,temp->port);
   strcat(buf,xbuf);
   temp=temp->next;
  }
  
  *length = strlen(buf);
  char *resbuf = (char*)malloc(*length);
  initid->ptr = resbuf;
  strcpy(resbuf,buf);
  result = resbuf;
  return result;
 }
 
 else if( strcmp(CMD,"VERSION") == 0 ){
  char *ver = "FX_MSG_VER:100010";
  *length=strlen(ver);
  memcpy(result,ver,*length);
  return result;
 }
 
 
 *is_null = 1;
 return 0;
}

 


void fx_msg_deinit(UDF_INIT *initid){
 if(initid->ptr != NULL){
  free(initid->ptr);
 }
}
 

//tools.c
//******************************

#include 
#include 
#include 

void fxlog(char *c){
 char *buf = (char*)malloc(strlen(c)+1);
 strcpy(buf,c);
 fprintf(stdout,buf);
 fprintf(stdout,"/n");
 fflush(stdout);
 free(buf);
}

时间紧任务急,大概的功能可能实现了.

以上 代码在
root@fes-middle01 so]# gcc --version
gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

[root@fes-middle01 mysql]# bin/mysql -uroot -pskywest
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 3 to server version: 5.0.22-max

中编译运行通过.

 

编译方法:
gcc -I. -O0 -g3 -Wall -c -fmessage-length=0 -ofxmsg.o fxmsg.c
gcc -I. -O0 -g3 -Wall -c -fmessage-length=0 -ocmd_close.o cmd_close.c
gcc -I. -O0 -g3 -Wall -c -fmessage-length=0 -ocmd_init.o cmd_init.c
gcc -I. -O0 -g3 -Wall -c -fmessage-length=0 -ocmd_send.o cmd_send.c
gcc -I. -O0 -g3 -Wall -c -fmessage-length=0 -otools.o tools.c
gcc -shared -oFXMSG.so fxmsg.o cmd_send.o cmd_init.o cmd_close.o tools.o

将生成的so文件放到 /usr/lib中
cp FXMSG.so /usr/lib

建立 fx_msg function.
CREATE FUNCTION fx_msg RETURNS STRING SONAME ; FXMSG.so;

删除时候用:
DROP FUNCTION fx_msg;