sql_module.c

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

/*
  The PUBLISH plugin is a GPL plugin for partysip.
  Copyright (C) 2002 2003  Aymeric MOIZARD - <[e-mail]>
  
  The PUBLISH 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 PUBLISH 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_publish_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_publish_authorize_user(osip_from_t *fr, osip_uri_t *requri, char *eventp)
{

  return 200;
}

int module_publish_dialog_exist(char *if_match, char *event)
{
  char sql_request[1024];
  MYSQL_RES *result;
  int i;
  if (if_match==NULL || if_match[0]=='\0')
    return -1;
  if (event==NULL || event[0]=='\0')
    return -1;

  snprintf(sql_request, 1024,
	   "SELECT "
	   "mid "
	   "FROM %s "
	   "WHERE 1 "
	   "AND sip_etag = \'%s\' "
	   "LIMIT 0, 2",
	   event, if_match);
  
  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, 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_publish_delete_publication(char *if_match, osip_from_t *fr, osip_uri_t *to, char *eventp)
{
  /* set expires data? add marker? */
  return;
}

int module_publish_update_dialog(publication_t *sub)
{
  char sql_request[6000];
  MYSQL_RES *result;
  int i;

  char buf[5000];
  mysql_escape_string(buf, sub->body,
		      strlen(sub->body));
  /* try to UPDATE existing entry */
  if (sub->if_match[0]!='\0')
    {
      snprintf(sql_request, 6000,
	       "UPDATE presence "
	       "SET "
	       "username =\'%s\',"
	       "domain =\'%s\',"
	       "sip_etag =\'%s\',"
	       "inc_time =UNIX_TIMESTAMP(), "
	       "exp_time =UNIX_TIMESTAMP() + %i ,"
	       "ctype = \'%s\', "
	       "body = \'%s\' "
	       "WHERE 1 AND sip_etag = \'%s\'",
	       sub->username,
	       sub->domain,
	       sub->sip_etag,
	       sub->exp_time,
	       sub->ctype,
	       buf,
	       sub->if_match);
  
      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_INFO2, NULL,
					  "%s plugin: update has been done (%i)!\n",
					  PLUGIN_NAME, num_rows));
		  return 0;
		}
	    }
	}
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_BUG, NULL,
			      "%s plugin: unreachable code!\n",
			      PLUGIN_NAME));
      return -1;
    }
  /* update has failed, INSERT new dialog */

  /* first, erase all old publication:
     to be improved:
     -> use domain name
     -> agregate several publication?
  */
  snprintf(sql_request, 6000,
	   "DELETE "
	   "FROM presence "
	   "WHERE 1 AND username = \'%s\'",
	   sub->username);

  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));
    }
  
  snprintf(sql_request, 6000,
	  "INSERT INTO presence "
	  "("
	  "username,"
	  "domain,"
	  "sip_etag,"
	  "inc_time,"
	  "exp_time,"
	  "ctype, "
	  "body"
	  ") VALUES ("
	  "\'%s\',"
	  "\'%s\',"
	  "\'%s\',"
	  "UNIX_TIMESTAMP() ,"
	  "UNIX_TIMESTAMP()+ %i ,"
	  "\'%s\',"
	  "\'%s\'"
	  ")",
	  sub->username,
	  sub->domain,
	  sub->sip_etag,
	  sub->exp_time,
	  sub->ctype,
	  buf);
  
  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 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_INFO2, NULL,
				      "%s plugin: new entry inserted (%i)!\n",
				      PLUGIN_NAME, num_rows));
	      return 0;
	    }
        }
    }

  return -1;
}


int module_publish_store_dialog(osip_message_t *sub, osip_message_t *response)
{
  publication_t _sub;
  osip_header_t *sip_etag;
  osip_header_t *if_match;
  osip_header_t *expires;
  osip_body_t *body;
  char *tmp;

  if (sub->content_type==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, "SIP-ETag", 0, &sip_etag);
  if (sip_etag==NULL || sip_etag->hvalue==NULL)
    return -1;  

  osip_message_header_get_byname(sub, "SIP-If-Match", 0, &if_match);

  body = (osip_body_t*) osip_list_get(sub->bodies, 0);
  if (body==NULL || body->body==NULL)
    return -1;

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

  /* fill datas */
  snprintf(_sub.username, 64, "%s", sub->req_uri->username);
  snprintf(_sub.domain, 128, "%s", sub->req_uri->host);
  snprintf(_sub.sip_etag, 64, "%s", sip_etag->hvalue);
  if (if_match!=NULL && if_match->hvalue!=NULL)
    snprintf(_sub.if_match, 64, "%s", if_match->hvalue);

  _sub.exp_time = atoi(expires->hvalue);

  snprintf(_sub.body, 4096, body->body);

  tmp=NULL;
  osip_content_type_to_str(sub->content_type, &tmp);
  if (tmp==NULL)
    return -1;
  snprintf(_sub.ctype, 32, tmp);
  osip_free(tmp);

  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO4, NULL,
			  "%s plugin: body: %s\n", PLUGIN_NAME,
			  _sub.body));

  module_publish_update_dialog(&_sub);
  return 0;
}