sql_module.c

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

  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: dialog update has been done (%i)!\n",
				      PLUGIN_NAME, num_rows));
	      return 0;
	    }
        }
    }

  /* update has failed, INSERT new dialog */
  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_BUG, NULL,
			  "%s plugin: dialog update has failed!\n",
			  PLUGIN_NAME));
  return -1;
}

int module_notify_get_notify_template(char *to_tag, char *event, subscription_t *sub)
{
  char sql_request[1024];
  unsigned int num_rows;
  MYSQL_RES *result;
  MYSQL_ROW row;
  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, "
	  "l_username, l_domain, l_tag, l_cseq, "
	  "r_username, r_domain, r_cseq, "
	  "event, "
	  "exp_time, "
	  "notify_template "
	  "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 */
    {
      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))));
        }
      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;
    }

  num_rows = mysql_num_rows(result);
  if (num_rows==0)
    {
      mysql_free_result(result);
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO2, NULL,
			      "%s plugin: no dialog found!\n",
			      PLUGIN_NAME));
      return -1;
    }

  mysql_data_seek(result, 0);
  row = mysql_fetch_row(result);

  if (row != NULL)
    {
      unsigned long *lengths;
      lengths = mysql_fetch_lengths(result);

      snprintf(sub->l_username, lengths[1]+1, "%s", row[1]);
      snprintf(sub->l_domain, lengths[2]+1, "%s", row[2]);
      snprintf(sub->l_tag, lengths[3]+1, "%s", row[3]);
      /* snprintf(sub->l_cseq, lengths[4]+1, "%s", row[4]); */
      sub->l_cseq = atoi(row[4]);
      snprintf(sub->r_username, lengths[5]+1, "%s", row[5]);
      snprintf(sub->r_domain, lengths[6]+1, "%s", row[6]);
      /* snprintf(sub->r_cseq, lengths[7]+1, "%s", row[7]); */
      sub->r_cseq = atoi(row[7]);

      snprintf(sub->event, lengths[8]+1, "%s", row[8]);
      sub->exp_time = 900;
      snprintf(sub->notify_template, lengths[10]+1, "%s", row[10]);

      mysql_free_result(result);
      return 0;
    }

  mysql_free_result(result);
  return -1;
}

int module_notify_send_all_notification(publication_t *pub, char *event)
{

  char sql_request[1024];
  unsigned int num_rows;
  MYSQL_RES *result;
  MYSQL_ROW row;
  int i;
  int pos;

  if (pub->username[0]=='\0')
    return -1;
  if (pub->domain[0]=='\0')
    return -1;
  if (event==NULL || event[0]=='\0')
    return -1;

  snprintf(sql_request, 1024,
	  "SELECT mid, "
	  "l_username, l_domain, l_tag, l_cseq, "
	  "r_username, r_domain, r_cseq, "
	  "event, "
	  "exp_time, "
	  "notify_template "
	  "FROM subscriptions "
	  "WHERE 1 AND l_username = \'%s\' AND event = \'%s\' AND unix_timestamp(NOW()) - unix_timestamp(exp_time) < 0", pub->username, 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 */
    {
      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))));
        }
      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;
    }

  num_rows = mysql_num_rows(result);
  if (num_rows==0)
    {
      mysql_free_result(result);
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO2, NULL,
			      "%s plugin: no %s information found!\n",
			      PLUGIN_NAME, event));
      return -1;
    }

  mysql_data_seek(result, 0);
  row = mysql_fetch_row(result);
  for (pos=0; row!=NULL; pos++)
    {
      subscription_t sub;
      int auth=0;
      unsigned long *lengths;
      lengths = mysql_fetch_lengths(result);
      
      snprintf(sub.l_username, lengths[1]+1, "%s", row[1]);
      snprintf(sub.l_domain, lengths[2]+1, "%s", row[2]);
      snprintf(sub.l_tag, lengths[3]+1, "%s", row[3]);
      /* snprintf(sub.l_cseq, lengths[4]+1, "%s", row[4]); */
      sub.l_cseq = atoi(row[4]);
      snprintf(sub.r_username, lengths[5]+1, "%s", row[5]);
      snprintf(sub.r_domain, lengths[6]+1, "%s", row[6]);
      /* snprintf(sub.r_cseq, lengths[7]+1, "%s", row[7]); */
      sub.r_cseq = atoi(row[7]);
      
      snprintf(sub.event, lengths[8]+1, "%s", row[8]);
      sub.exp_time = 900;
      snprintf(sub.notify_template, lengths[10]+1, "%s", row[10]);
      
      /* TODO: look for user authorization */
      /* */
      auth=1;
      if (auth==0)
	{
	  build_notify(&sub, NULL);
	}
      else
	{
	  build_notify(&sub, pub);
	}
      pos++;
      row = mysql_fetch_row(result);
    }

  mysql_free_result(result);
  return 0;
}

int module_notify_get_publication(publication_t *pub, char *event)
{
  char sql_request[1024];
  unsigned int num_rows;
  MYSQL_RES *result;
  MYSQL_ROW row;
  int i;
  if (pub->username[0]=='\0')
    return -1;
  if (pub->domain[0]=='\0')
    return -1;
  if (event==NULL || event[0]=='\0')
    return -1;

  snprintf(sql_request, 1024,
	   "SELECT "
	   "ctype, "
	   "body "
	   "FROM %s "
	   "WHERE 1 "
	   "AND username = \'%s\' "
#if 0
	   "AND domain = \'%s\' "
#endif
	   "LIMIT 0, 2",
	   event, pub->username);
  
  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)
    {
      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;
        }
    }

  num_rows = mysql_num_rows(result);
  if (num_rows==0)
    {
      mysql_free_result(result);
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO2, NULL,
			      "%s plugin: no dialog found!\n",
			      PLUGIN_NAME));
      return -1;
    }

  mysql_data_seek(result, 0);
  row = mysql_fetch_row(result);

  if (row != NULL)
    {
      unsigned long *lengths;
      lengths = mysql_fetch_lengths(result);

      snprintf(pub->ctype, lengths[0]+1, "%s", row[0]);
      snprintf(pub->body, lengths[1]+1, "%s", row[1]);

      mysql_free_result(result);
      return 0;
    }

  mysql_free_result(result);
  return -1;
}

int build_notify(subscription_t *sub, publication_t *pub)
{
  osip_message_t *not;
  char *msgbuf;
  size_t len;
  int i;
  char cseq[10];

  char via[1024];
  char *port;
  char random_number[33];
  unsigned int number;
  osip_contact_t *co;


  i = osip_message_init(&not);
  i = osip_message_parse(not, sub->notify_template, strlen(sub->notify_template));
  if (i!=0)
    {
      /* non valid message */
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			      "%s plugin: non valid NOTIFY template!\n",
			      PLUGIN_NAME));
      osip_message_free(not);
      return -1;
    }

  snprintf(cseq, 10, "%i NOTIFY", sub->l_cseq);
  osip_message_set_cseq(not, cseq);

  co = (osip_contact_t*) osip_list_get(not->contacts, 0);
  if (co==NULL||co->url==NULL||co->url->host==NULL)
    {
      /* non valid message */
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			      "%s plugin: missing contact header in NOTIFY template!\n",
			      PLUGIN_NAME));
      osip_message_free(not);
      return -1;
    }

  port = psp_config_get_element("serverport_udp");
  number = osip_build_random_number ();
  snprintf (random_number, 33, "%u", number);
  random_number[15]='\0';

  snprintf(via, 1024, "Via: SIP/2.0/%s %s:%s;branch=z9hG4bKl-pa-%s\r\n", 
	   "UDP",
	   co->url->host,
	   (port)?port:"5060",
	   random_number);

  osip_message_set_via(not, via);

  if (pub==NULL)
    osip_message_set_header(not, "Subscription-State", "pending;expires=600");
  else
    {
      osip_message_set_header(not, "Subscription-State", "active;expires=600");
      if (pub->body[0]=='\0')
	{
	  /* send basic body to inform that user is not available */
	  char buf[1024];
	  snprintf(buf, 1024, "<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
		   "<presence xmlns=\"urn:ietf:params:xml:ns:pidf\""
		   "entity=\"sip:%s@%s\">"
		   "<tuple id=\"sg89ae\">"
		   "<status>"
		   "<basic>closed</basic>"
		   "</status>"
		   "<note>No publication available for user</note>"
		   "</tuple>",
		   pub->username,
		   pub->domain);
	  osip_message_set_content_type(not, "application/pidf+xml");
	  osip_message_set_body(not, buf, strlen(buf));
	}
      else
	{
	  osip_message_set_content_type(not, pub->ctype);
	  osip_message_set_body(not, pub->body, strlen(pub->body));
	}
    }
  
  
  msgbuf=NULL;
  i = osip_message_to_str(not, &msgbuf, &len);
  osip_free(msgbuf);
  if (i!=0)
    {
      /* non valid message */
      OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_ERROR, NULL,
			      "%s plugin: non valid NOTIFY template (xx_to_str failed)!\n",
			      PLUGIN_NAME));
      osip_message_free(not);
      return -1;
    }

  OSIP_TRACE (osip_trace (__FILE__, __LINE__, OSIP_INFO2, NULL,
			  "%s plugin: NOTIFY ready to be sent\n",
			  PLUGIN_NAME));

  psp_core_cb_snd_message(NULL, not, NULL, 0, -1);
  osip_message_free(not);

  i = module_notify_update_notify_template(sub);
  return 0;
}