sql_module.c

partysip 插件开发 用于实时在线管理

/*
  The NOTIFY plugin is a GPL plugin for partysip.
  Copyright (C) 2002 2003  Aymeric MOIZARD - <[e-mail]>
  
  The NOTIFY plugin is free software; you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation; either version 2 of the License, or
  (at your option) any later version.
  
  The NOTIFY plugin is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with Foobar; if not, write to the Free Software
  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

#include <sql_module.h>
#include <partysip/partysip.h>
#include "plugin.h"

extern plugin_ctx_t *plugin_context;
extern char name_config[50];

int module_notify_initialize()
{
  config_element_t *elem;
  MYSQL* sql;
  plugin_context->connected = 0;

  elem = psp_config_get_sub_element("mysql_hostname", name_config, NULL);
  if (elem==NULL||elem->value==NULL) return -1;
  strcpy(plugin_context->mysql_hostname, elem->value);

  elem = psp_config_get_sub_element("mysql_user", name_config, NULL);
  if (elem==NULL||elem->value==NULL) return -1;
  strcpy(plugin_context->mysql_user, elem->value);

  elem = psp_config_get_sub_element("mysql_pwd", name_config, NULL);
  if (elem==NULL||elem->value==NULL) return -1;
  strcpy(plugin_context->mysql_pwd, elem->value);

  elem = psp_config_get_sub_element("mysql_dbname", name_config, NULL);
  if (elem==NULL||elem->value==NULL) return -1;
  strcpy(plugin_context->mysql_dbname, elem->value);
  
  mysql_init(&(plugin_context->mysql_conn));
  
  sql = mysql_real_connect(&(plugin_context->mysql_conn),
			   plugin_context->mysql_hostname,
			   plugin_context->mysql_user,
			   plugin_context->mysql_pwd,
			   plugin_context->mysql_dbname,
			   0, NULL, 0);
  
  if (sql==0)
    {
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			      "%s plugin: could not connect to mysql database (%s)\n", PLUGIN_NAME, plugin_context->mysql_dbname));
      /* If the database does not exist, it's no use to continue. */
      mysql_close(&(plugin_context->mysql_conn));
      return -1;
    }
  plugin_context->connected = 1;
  return 0;
}

int module_notify_authorize_user(osip_from_t *fr, osip_uri_t *requri, char *eventp)
{

  return 200;
}

int module_notify_dialog_exist(char *to_tag, char *event)
{
  char sql_request[1024];
  MYSQL_RES *result;
  int i;

  if (to_tag==NULL || to_tag[0]=='\0')
    return -1;
  if (event==NULL || event[0]=='\0')
    return -1;

  snprintf(sql_request, 1024,
	  "SELECT mid, "
	  "exp_time "
	  "FROM subscriptions "
	  "WHERE 1 AND l_tag = \'%s\' AND event = \'%s\' AND unix_timestamp(NOW()) - unix_timestamp(exp_time) < 0", to_tag, event);

  
  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO4, NULL,
			  "%s plugin: query %s\n",
			  PLUGIN_NAME, sql_request));

  i = mysql_query(&(plugin_context->mysql_conn), sql_request);
  if (i!=0)
    {
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			      "%s plugin: error in mysql_send_query\n",
			      PLUGIN_NAME));
      return -1;
    }

  result = mysql_store_result(&(plugin_context->mysql_conn));
  if (result)  /* there are rows */
    {
      unsigned int num_rows;
      num_rows = mysql_num_rows(result);
      mysql_free_result(result);
      if (num_rows==0)
	{
	  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO2, NULL,
				  "%s plugin: no dialog found!\n",
				  PLUGIN_NAME));
	  return -1;
	}
      return 0;
    }
  else  /* mysql_store_result() returned nothing; should it have? */
    {
      if (mysql_errno(&(plugin_context->mysql_conn)))
        {
	  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
				  "%s plugin: error in mysql_store_result (%s)\n",
				  PLUGIN_NAME,
				  mysql_error(&(plugin_context->mysql_conn))));
	  return -1;
        }
      else if (mysql_field_count(&(plugin_context->mysql_conn)) == 0)
        {
	  /* query does not return data
	     (it was not a SELECT) */
	  /* num_rows = mysql_affected_rows(&(plugin_context->mysql_conn)); */
	  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_BUG, NULL,
				  "%s plugin: several entries affected!\n", PLUGIN_NAME));
	  return -1;
        }
    }

  return 0;
}

void module_notify_delete_subscription(char *to_tag, osip_from_t *fr, osip_to_t *to, char *eventp)
{
  /* set expires data? add marker? */
  return;
}

int module_subscribe_update_dialog(subscription_t *sub)
{
  char sql_request[6000];
  MYSQL_RES *result;
  int i;

  char buf[5000];
  /* try to UPDATE existing entry */
  mysql_escape_string(buf, sub->notify_template,
		      strlen(sub->notify_template));

  snprintf(sql_request, 6000,
	   "UPDATE subscriptions "
	   "SET "
	   "r_cseq =\'%i\', "
	   "notify_template = \'%s\', "
	   "exp_time =DATE_ADD(NOW(),INTERVAL %i SECOND) "
	   "WHERE 1 AND l_tag = \'%s\'",
	   sub->r_cseq,
	   buf,
	   sub->exp_time,
	   sub->l_tag);
  
  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO4, NULL,
			  "%s plugin: UPDATE query %s\n",
			  PLUGIN_NAME, sql_request));

  i = mysql_query(&(plugin_context->mysql_conn), sql_request);
  if (i!=0)
    {
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			      "%s plugin: error in mysql_send_query\n",
			      PLUGIN_NAME));
      return -1;
    }

  result = mysql_store_result(&(plugin_context->mysql_conn));
  if (result)  /* there are rows */
    {
      unsigned int num_rows;
      num_rows = mysql_num_rows(result);
      mysql_free_result(result);
      if (num_rows==0)
	{
	  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_BUG, NULL,
				  "%s plugin: unreachable code?\n",
				  PLUGIN_NAME));
	  return -1;
	}
      return -1;
    }
  else  /* mysql_store_result() returned nothing; should it have? */
    {
      if (mysql_errno(&(plugin_context->mysql_conn)))
        {
	  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
				  "%s plugin: error while updating (%s)\n",
				  PLUGIN_NAME,
				  mysql_error(&(plugin_context->mysql_conn))));
	  return -1;
        }
      else if (mysql_field_count(&(plugin_context->mysql_conn)) == 0)
        {
	  /* query does not return data
	     (it was not a SELECT) */
	  unsigned int num_rows;
	  num_rows = mysql_affected_rows(&(plugin_context->mysql_conn));
	  if (num_rows>0)
	    {
	      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_BUG, NULL,
				      "%s plugin: update has been done (%i)!\n",
				      PLUGIN_NAME, num_rows));
	      return 0;
	    }
        }
    }

  /* update has failed, INSERT new dialog */
  sub->l_cseq = 20;

  snprintf(sql_request, 6000,
	   "INSERT INTO subscriptions "
	   "("
	   "l_username,"
	   "l_domain,"
	   "l_tag,"
	   "l_cseq, "
	   "r_username,"
	   "r_domain,"
	   "r_cseq,"
	   "event,"
	   "exp_time,"
	   "notify_template"
	   ") VALUES ("
	   "\'%s\',"
	   "\'%s\',"
	   "\'%s\',"
	   "\'%i\',"
	   "\'%s\',"
	   "\'%s\',"
	   "\'%i\',"
	   "\'%s\',"
	   "DATE_ADD(NOW(),INTERVAL %i SECOND) ,"
	   "\'%s\'"
	   ")",
	   sub->l_username,
	   sub->l_domain,
	   sub->l_tag,
	   sub->l_cseq,
	   sub->r_username,
	   sub->r_domain,
	   sub->r_cseq,
	   sub->event,
	   sub->exp_time,
	   buf);
  
  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			  "%s plugin: UPDATE query %s\n",
			  PLUGIN_NAME, sql_request));

  i = mysql_query(&(plugin_context->mysql_conn), sql_request);
  if (i!=0)
    {
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			      "%s plugin: error in mysql_send_query\n",
			      PLUGIN_NAME));
      return -1;
    }

  result = mysql_store_result(&(plugin_context->mysql_conn));
  if (result)  /* there are rows */
    {
      unsigned int num_rows;
      num_rows = mysql_num_rows(result);
      mysql_free_result(result);
      if (num_rows==0)
	{
	  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_BUG, NULL,
				  "%s plugin: unreachable code?\n",
				  PLUGIN_NAME));
	  return -1;
	}
      return 0;
    }
  else  /* mysql_store_result() returned nothing; should it have? */
    {
      if (mysql_errno(&(plugin_context->mysql_conn)))
        {
	  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
				  "%s plugin: error while updating (%s)\n",
				  PLUGIN_NAME,
				  mysql_error(&(plugin_context->mysql_conn))));
	  return -1;
        }
      else if (mysql_field_count(&(plugin_context->mysql_conn)) == 0)
        {
	  /* query does not return data
	     (it was not a SELECT) */
	  unsigned int num_rows;
	  num_rows = mysql_affected_rows(&(plugin_context->mysql_conn));
	  if (num_rows>0)
	    {
	      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_BUG, NULL,
				      "%s plugin: new entry inserted (%i)!\n",
				      PLUGIN_NAME, num_rows));
	      return 0;
	    }
        }
    }

  return -1;
}

int module_subscribe_store_dialog(osip_message_t *sub, osip_message_t *response)
{
  char _req_uri[1024];
  char _from[1024];
  char _to[1024];
  char _call_id[1024];
  char _contact[1024];
  char *tmp;
  int i;
  char *banner;
  char *port;
  subscription_t _sub;
  osip_header_t *expires;
  osip_header_t *event;
  osip_generic_param_t *tag_to;
  osip_contact_t *co;
  char *random_number;
  unsigned int number;

  osip_from_param_get_byname (response->to, "tag", &tag_to);
  if (tag_to==NULL || tag_to->gvalue==NULL)
    return -1;

  osip_message_header_get_byname(response, "expires", 0, &expires);
  if (expires==NULL || expires->hvalue==NULL)
    return -1;

  osip_message_header_get_byname(response, "event", 0, &event);
  if (event==NULL || event->hvalue==NULL)
    return -1;

  memset(&_sub, 0, sizeof(subscription_t));

  /* fill datas */
  snprintf(_sub.l_username, 64, "%s", sub->req_uri->username);
  snprintf(_sub.l_domain, 128, "%s", sub->req_uri->host);
  snprintf(_sub.l_tag, 64, "%s", tag_to->gvalue);

  _sub.l_cseq = 1; /* initial default value */

  snprintf(_sub.r_username, 64, "%s", sub->from->url->username);
  snprintf(_sub.r_domain, 128, "%s", sub->from->url->host);

  _sub.r_cseq = atoi(sub->cseq->number);

  snprintf(_sub.event, 64, "%s", event->hvalue);
  _sub.exp_time = atoi(expires->hvalue);

  {
    osip_contact_t *co;
    co = (osip_contact_t*) osip_list_get(sub->contacts, 0);
    if (co==NULL||co->url==NULL) return -1;
    i = osip_uri_to_str(co->url, &tmp);
    if (i!=0) return -1;
    snprintf(_req_uri, 1024, "%s", tmp);
    osip_free(tmp);
  }

  i = osip_from_to_str(sub->from, &tmp);
  if (i!=0) return -1;
  snprintf(_to, 1024, "%s", tmp);
  osip_free(tmp);

  i = osip_to_to_str(response->to, &tmp);
  if (i!=0) return -1;
  snprintf(_from, 1024, "%s", tmp);
  osip_free(tmp);

  osip_call_id_to_str(sub->call_id, &tmp);
  if (i!=0) return -1;
  snprintf(_call_id, 1024, "%s", tmp);
  osip_free(tmp);

  co = (osip_contact_t*) osip_list_get(response->contacts, 0);
  if (co==NULL||co->url==NULL||co->url->host==NULL) return -1;
  i = osip_contact_to_str(co, &tmp);
  if (i!=0) return -1;
  snprintf(_contact, 1024, "%s", tmp);
  osip_free(tmp);

  banner = psp_config_get_element("banner");
  port = psp_config_get_element("serverport_udp");
  random_number = (char *) osip_malloc (33);
  number = osip_build_random_number ();
  sprintf (random_number, "%u", number);
  random_number[15]='\0';

  snprintf(_sub.notify_template, 4096,
	   "NOTIFY %s SIP/2.0\r\n"
	   "Via: SIP/2.0/%s %s:%s;branch=z9hG4bKl-pa-%s\r\n"
	   "From: %s\r\n"
	   "To: %s\r\n"
	   "Call-Id: %s\r\n"
	   "Event: %s\r\n"
	   "Contact: %s\r\n",
	   _req_uri,
	   "UDP",
	   co->url->host,
	   (port)?port:"5060",
	   random_number,
	   _from,
	   _to,
	   _call_id,
	   _sub.event,
	   _contact);

  osip_free(random_number);

  if (banner!=NULL)
    {
      strcat(_sub.notify_template, "User-Agent: ");
      strcat(_sub.notify_template, banner);
      strcat(_sub.notify_template, "\r\n");
    }

  strcat(_sub.notify_template, "Max-Forwards: 70\r\n\r\n");

  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO2, NULL,
			  "%s plugin: notify template: %s\n", PLUGIN_NAME,
			  _sub.notify_template));

  module_subscribe_update_dialog(&_sub);
  return 0;
}

int module_notify_update_notify_template(subscription_t *sub)
{
  char sql_request[6000];
  MYSQL_RES *result;
  int i;

  sub->l_cseq++;

  snprintf(sql_request, 6000,
	  "UPDATE subscriptions "
	  "SET "
	  "l_cseq = \'%i\'"
	  "WHERE 1 AND l_tag = \'%s\'",
	  sub->l_cseq,
	  sub->l_tag);
  
  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO4, NULL,
			  "%s plugin: UPDATE query %s\n",
			  PLUGIN_NAME, sql_request));