[Asterisk-Users] Patch for postgres enabled app_voicemail.c

[Matt Davies | MattDavies.Net]

Hello all,

I am just getting going on building my system, but I thought I'd send you
all a patch that I wrote so the command:

show voicemail users

issued from the CLI works properly when there is a postgres backend for the
voicemail. The current version of the app does not display the voicemail
boxes found in a database.

It is called in the load_config function. I haven't done extensive testing
on this other than trying out the results from the CLI.

Hope this helps you all!


Matt



Patch follows:
*** app_voicemail.c 2004-06-23 07:30:02.000000000 -0600
--- app_voicemail.c.new 2004-06-23 07:29:08.000000000 -0600
***************
*** 49,61 ****
  /*
   * PostgreSQL routines written by Otmar Lendl <lendl at nic.at>
   */
! #include <postgresql/libpq-fe.h>
  #define USESQLVM 1
  #endif

  #ifndef USESQLVM
  static inline int sql_init(void) { return 0; }
  static inline void sql_close(void) { }
  #endif

  #include <pthread.h>
--- 49,64 ----
  /*
   * PostgreSQL routines written by Otmar Lendl <lendl at nic.at>
   */
! #include <libpq-fe.h>
  #define USESQLVM 1
  #endif

+ static int append_mailbox(char *, char *, char *);
+
  #ifndef USESQLVM
  static inline int sql_init(void) { return 0; }
  static inline void sql_close(void) { }
+ static void sql_append_mailboxes(void);
  #endif

  #include <pthread.h>
***************
*** 237,250 ****

  #ifdef USEPOSTGRESVM

! PGconn *dbhandler;
  char dboption[256];
  ast_mutex_t postgreslock;

  static int sql_init(void)
  {
  ast_verbose( VERBOSE_PREFIX_3 "Logging into postgres database: %s\n",
dboption);
! /* fprintf(stderr,"Logging into postgres database: %s\n", dboption); */

  dbhandler=PQconnectdb(dboption);
  if (PQstatus(dbhandler) == CONNECTION_BAD) {
--- 240,266 ----

  #ifdef USEPOSTGRESVM

! PGconn *dbhandler = NULL;
  char dboption[256];
  ast_mutex_t postgreslock;

+ static void sql_close(void)
+ {
+ fprintf (stderr, "postgres closing database\n");
+ PQfinish(dbhandler);
+ }
+
  static int sql_init(void)
  {
+ /*
+ * JMD
+ * 6/23/2004
+ * Close the connection if already opened
+ */
+ if (NULL != dbhandler) sql_close();
+
  ast_verbose( VERBOSE_PREFIX_3 "Logging into postgres database: %s\n",
dboption);
! fprintf(stderr,"Logging into postgres database: %s\n", dboption);

  dbhandler=PQconnectdb(dboption);
  if (PQstatus(dbhandler) == CONNECTION_BAD) {
***************
*** 253,270 ****
  }
  ast_mutex_init(&postgreslock);

! /* fprintf(stderr,"postgres login OK\n"); */
  return(0);
  }

! static void sql_close(void)
  {
! PQfinish(dbhandler);
  }


  static struct ast_vm_user *find_user(struct ast_vm_user *ivm, char
*context, char *mailbox)
  {
  PGresult *PGSQLres;


--- 269,371 ----
  }
  ast_mutex_init(&postgreslock);

! fprintf(stderr,"postgres login OK\n");
  return(0);
  }

!
! static void sql_append_mailboxes(void)
  {
! /*
! * Matt Davies (aka JMD)
! * matt at bastionits.com
! * Bastion Information Technology Solutions
! *
! * added sql_append_mailboxes 6/23/2004
! *
!  * This function queries the database and loads the voicemail information
! * so the command show voicemail users works properly
! */
! fprintf (stderr, "   Loading voicemail information from database\n");
! PGresult *PGSQLres;
!
!
! int numFields, i, k;
! char *fname;
! char query[240];
! char optionsToPass[500] = "";
! char options[160] = "";
! char context[90] = "";
! char mailbox[90] = "";
! char password[90] = "";
! char fullname[90] = "";
! char email[90] = "";
! char pager[90] = "";
!
! //fprintf(stdout,"postgres sql_append_user:\n");
!
! sprintf(query, "SELECT * FROM voicemail order by context,mailbox ");
!
! //fprintf(stdout,"postgres sql_append_user: query = %s\n",query);
! ast_mutex_lock(&postgreslock);
! PGSQLres=PQexec(dbhandler,query);
! if (PGSQLres!=NULL) {
! if (PQresultStatus(PGSQLres) == PGRES_BAD_RESPONSE ||
! PQresultStatus(PGSQLres) == PGRES_NONFATAL_ERROR ||
! PQresultStatus(PGSQLres) == PGRES_FATAL_ERROR) {
!
! ast_log(LOG_WARNING,"PGSQL_query: Query Error (%s) Calling
PQreset\n",PQcmdStatus(PGSQLres));
! PQclear(PGSQLres);
! PQreset(dbhandler);
! ast_mutex_unlock(&postgreslock);
! return;
! } else {
! numFields = PQnfields(PGSQLres);
! //fprintf(stdout,"postgres sql_append_user: query found %d rows with %d
fields\n",PQntuples(PGSQLres), numFields);
! for (k = 0; k< (PQntuples(PGSQLres)); k++) {
! for (i=0; i<numFields; i++) {
! fname = PQfname(PGSQLres,i);
! if (!strcmp(fname, "password") && !PQgetisnull (PGSQLres,k,i)) {
! memset (password, 0, sizeof (password));
! strcpy (password,PQgetvalue(PGSQLres,k,i));
! } else if (!strcmp(fname, "context")) {
! memset (context, 0, sizeof (context));
! strcpy (context,PQgetvalue(PGSQLres,k,i));
! } else if (!strcmp(fname, "mailbox")) {
! memset (mailbox, 0, sizeof (mailbox));
! strcpy (mailbox,PQgetvalue(PGSQLres,k,i));
! } else if (!strcmp(fname, "fullname")) {
! memset (fullname, 0, sizeof (fullname));
! strcpy (fullname,PQgetvalue(PGSQLres,k,i));
! } else if (!strcmp(fname, "email")) {
! memset (email, 0, sizeof (email));
! strcpy (email,PQgetvalue(PGSQLres,k,i));
! } else if (!strcmp(fname, "pager")) {
! memset (pager, 0, sizeof (pager));
! strcpy (pager,PQgetvalue(PGSQLres,k,i));
! } else if (!strcmp(fname, "options")) {
! memset (options , 0, sizeof(options));
! strncpy(options, PQgetvalue(PGSQLres,k,i), sizeof(options) - 1);
! }
! }
! sprintf (optionsToPass, "%s,%s,%s,%s,%s", password, fullname, email,
pager, options);
! //fprintf (stdout, "Sending: %s %s %s\n", context, mailbox,
optionsToPass);
! append_mailbox(context, mailbox, optionsToPass);
! }
! }
! PQclear(PGSQLres);
! ast_mutex_unlock(&postgreslock);
! } else {
! ast_log(LOG_WARNING,"PGSQL_query: Connection Error
(%s)\n",PQerrorMessage(dbhandler));
! ast_mutex_unlock(&postgreslock);
! }
! /* not reached */
  }


  static struct ast_vm_user *find_user(struct ast_vm_user *ivm, char
*context, char *mailbox)
  {
+ fprintf (stderr, "postgres find user");
  PGresult *PGSQLres;


***************
*** 276,282 ****

  retval=malloc(sizeof(struct ast_vm_user));

! /* fprintf(stderr,"postgres find_user:\n"); */

  if (retval) {
  *retval->mailbox='\0';
--- 377,383 ----

  retval=malloc(sizeof(struct ast_vm_user));

! fprintf(stdout,"postgres find_user:\n");

  if (retval) {
  *retval->mailbox='\0';
***************
*** 301,307 ****
  }
  sprintf(query, "SELECT password,fullname,email,pager,options FROM
voicemail WHERE context='%s' AND mailbox='%s'", retval->context, mailbox);

! /* fprintf(stderr,"postgres find_user: query = %s\n",query); */
  ast_mutex_lock(&postgreslock);
  PGSQLres=PQexec(dbhandler,query);
  if (PGSQLres!=NULL) {
--- 402,408 ----
  }
  sprintf(query, "SELECT password,fullname,email,pager,options FROM
voicemail WHERE context='%s' AND mailbox='%s'", retval->context, mailbox);

! fprintf(stdout,"postgres find_user: query = %s\n",query);
  ast_mutex_lock(&postgreslock);
  PGSQLres=PQexec(dbhandler,query);
  if (PGSQLres!=NULL) {
***************
*** 317,323 ****
  return(NULL);
  } else {
  numFields = PQnfields(PGSQLres);
! /* fprintf(stderr,"postgres find_user: query found %d rows with %d
fields\n",PQntuples(PGSQLres), numFields); */
  if (PQntuples(PGSQLres) != 1) {
  ast_log(LOG_WARNING,"PGSQL_query: Did not find a unique mailbox for
%s\n",mailbox);
  PQclear(PGSQLres);
--- 418,424 ----
  return(NULL);
  } else {
  numFields = PQnfields(PGSQLres);
! fprintf(stdout,"postgres find_user: query found %d rows with %d
fields\n",PQntuples(PGSQLres), numFields);
  if (PQntuples(PGSQLres) != 1) {
  ast_log(LOG_WARNING,"PGSQL_query: Did not find a unique mailbox for
%s\n",mailbox);
  PQclear(PGSQLres);
***************
*** 408,414 ****
  if (ivm)
  vmu = ivm;
  else
! /* Make a copy, so that on a reload, we have no race */
  vmu = malloc(sizeof(struct ast_vm_user));
  if (vmu) {
  memcpy(vmu, cur, sizeof(struct ast_vm_user));
--- 509,515 ----
  if (ivm)
  vmu = ivm;
  else
! /* Make a copy, so that on a read, we have no race */
  vmu = malloc(sizeof(struct ast_vm_user));
  if (vmu) {
  memcpy(vmu, cur, sizeof(struct ast_vm_user));
***************
*** 3014,3019 ****
--- 3115,3121 ----

  static int append_mailbox(char *context, char *mbox, char *data)
  {
+ fprintf (stderr, "ADD IN Mailbox: %s Context %s Data: %s\n", mbox,
context, data);
  /* Assumes lock is already held */
  char tmp[256] = "";
  char *stringp;
***************
*** 3317,3323 ****
--- 3419,3431 ----
  append_mailbox(cat, var->name, var->value);
  var = var->next;
  }
+ #else
+ while(var) {
+ append_mailbox(cat, var->name, var->value);
+ var = var->next;
+ }
  #endif
+
  } else {
  /* Timezones in this context */
  while(var) {
***************
*** 3395,3400 ****
--- 3503,3519 ----
  }
  }
  ast_destroy(cfg);
+ #ifndef USEMYSQLVM
+ /*
+ * JMD
+ * 6/23/2004
+ * If we are using a database then reload the data from there
+ *
+ * Important : Make sure we have a valid database handle
+ */
+ if (NULL == dbhandler) sql_init();
+ sql_append_mailboxes();
+ #endif
  ast_mutex_unlock(&vmlock);
  return 0;
  } else {
***************
*** 3443,3448 ****
--- 3562,3568 ----
  ast_log(LOG_WARNING, "SQL init\n");
  return res;
  }
+
  #ifndef USEMYSQLVM
  ast_cli_register(&show_voicemail_users_cli);
  ast_cli_register(&show_voicemail_zones_cli);