Mysql/Trigger/UDF/Email Notification

Disclaimer : Cette procedure^W^W Ce brouillon est un hack, le pire que vous pouvez trouver sur le marché.

Background

Il est Dimanche apres-midi un client m'appelle, ils ont besoin d'envoyer des alertes depuis un CRM open source. Cool, on va coder un peu ! Dix minutes passent, c'est sugarcrm sous licence GPLv3. Dix autres minutes passent, la version community “da super open free source qui fait les pizzas au GPL” ne supportent pas les alertes emails (Pour un CRM/ERP ca va pas etre pratique) il faut payer 150$/users/ans. Bon vu le code, il est dimanche je vais pas non plus me fouler à faire du PHP, et coder un module entier. Et là c'est le drame… dans un moment de folie je m'ecrie : “Trigger!”.

Mysql 5.0 Trigger

 1 DROP trigger t_nc;
 2
 3 DELIMITER |
 4
 5 CREATE trigger t_nc after update on mytable for each row BEGIN 
 6   DECLARE isupdated INT DEFAULT 0;
 7   IF OLD.assigned_user_id != NEW.assigned_user_id OR OLD.status != NEW.status THEN
 8      SET isupdated = 1;
 9   END IF;
10   
11   IF isupdated = 1 THEN
12     SET @a='';
13     SET @b='';
14     select user_name from users where id=NEW.assigned_user_id into @a;
15     select user_name from users where id=OLD.assigned_user_id into @b;
16     set @res = mymail(@a,@b);
17   END IF;
18 END;|
  • L1 : On drop le trigger au cas où il existe.
  • L3 : On definit un délimiteur car on a besoin de ; dans le code et on rajoute ce delimiteur à la fin L18.
  • L5 : On crée un trigger t_nc qui sea declenche apres une update sur mytable.
  • L6-L9 : On definit une variable “isupdated” pour definir quand on veux envoyer les emails (quelques champs sont monitorés, ici “assigned_user_id” et “status”). NEW et OLD permettent l'accès avant/apres update.
  • L11-L17 : On recupere, si les champs monitorés sont modifiés, les emails des utilisateurs ( NEW et OLD etant le prefix) et on les passent à la fonction mymail().

Mysql 5.0 UDF - mymail()

UDF : “User Defined Function” qui permettent de rajouter des fonctions comme celles qui suivent.

  • La fonction a été très inspirée du post de forum : FIXME

Il n'y a que MAIL qui MAIL :

/* Les includes necessaires */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>

#include <my_global.h>
#include <my_sys.h>
#include <m_string.h>
#include <mysql.h>
#include <ctype.h>

/* messages d'erreur */
#define ERRPARM ("Wrong mymail Parameters !")
#define ERRMEM ("Out of Memory !")
#define ERROPEN ("exteernal command mail failed!")

/* Autre variables */
#define COMMAND ("/usr/bin/mail %s")
#define COMMAND_OK ("mymail succeeded !")
#define SUBJECT ("CRM CASE UPDATED")
#define BODY ("The Crm need your attention !")

/* Les trois fonctions necessaires */
my_bool mymail_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void mymail_deinit(UDF_INIT *initid);
char * mymail(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);

/* initialisation de la fonction */
my_bool	mymail_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if (!(args->arg_count == 2 && args->arg_type[0] == STRING_RESULT && args->arg_type[1] == STRING_RESULT))
  {
    strncpy(message, ERRPARM, MYSQL_ERRMSG_SIZE);
    return 1;
  }

  initid->maybe_null = 1;
  initid->ptr = (char*) malloc(0);
  if(initid->ptr==NULL)
  {
    strncpy(message, ERRMEM, MYSQL_ERRMSG_SIZE);
    return 1;
  }
  return 0;
}

/* desinitialisation de la fonction */
void mymail_deinit(UDF_INIT *initid)
{
  free(initid->ptr);
}

/* Fonction pour utiliser la commande unix "mail" et envoyer un email depuis mysql UNSECURE HACK */

char * mymail(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error)
{
  FILE *fp = NULL;
  char send_mail_cmd[64] = {'0'};
  char recipient[2048] = {'0'};
  const char *to = args->args[0];
  const char *cc = args->args[1];

  if ((to == NULL) || (cc == NULL))
  {
    *is_null=1;
    return 0;
  }
  snprintf(send_mail_cmd, sizeof(send_mail_cmd), COMMAND, to);
  if (!(fp = popen(send_mail_cmd, "w"))) 
  {
    *is_null = 1;
    strncpy(error, ERROPEN, MYSQL_ERRMSG_SIZE);
    return NULL;
  }
  
  /* set Subject: */ 
  fprintf(fp, "%s\n", SUBJECT);
  /* set Body */
  fprintf(fp, "%s\n.\n", BODY);
  /* set cc */
  fprintf(fp, "%s\n", cc);
  pclose(fp);
  
  /* il faudrait faire d'autre test "histoire de" mais pour ce hack ca suffira. */
  return COMMAND_OK;

} 
  • On compile le tout avec un : gcc -o mymail.so -I/usr/include/mysql -shared mymail.c

Configuration de Mysql

  • On doit copier le mymail.so pour que mysql puisse le loader.
  • On crée la function :
     create function mymail returns string soname 'mymail.so';
    
  • On execute :
    set @mytest=mymail('tata@gmail.com','tonton@gmail.com')
    

Conclusion

  • C'est pour le fun de faire une udf une fois dans ma vie.
  • C'est lent : “ Query OK, 0 rows affected (2.87 sec) ”
  • Il y a plein d'autre methode moins gruik pour arriver au même resultat, c'est juste un “proof of concept”. Par exemple regardez du coté de : http://www.mysqludf.org/lib_mysqludf_sys/index.php
mysql/email_notification_on_update.txt · Last modified: 2011/05/06 09:24 by newacct