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(&apos;CLOSE&apos;);"); 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(&apos;INIT&apos;,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;