[Dbmail] Patch for mysql/dbmysql.c

[Craig Whitmore]

Hi there DBMailers..

Below is a patch for mysql/dbmysql.c to make the update of the pbsp table
easier. It uses the command REPLACE which will do an INSERT or an UPDATE
depending of the entry is there or not. This means only 1 SQL query instead
of 2 (1 to see if its there and one to INSERT or UPDATE). The postgreSQL
version hasn't been changed as I don't know if if it has a similar command
as REPLACE that mysql does.

If anyone has any comments on this please let me know. It is based on the
rc3/rc4 but can easily be edited for v1.0

Thanks
Craig Whitmore


------------------------------------------------------------------------
--- dbmysql.c.OLD       2002-12-18 11:27:17.000000000 +1300
+++ dbmysql.c   2002-12-18 12:51:35.000000000 +1300
@@ -1529,48 +1529,12 @@
   char timestr[30];
   time_t td;
   struct tm tm;
-  u64_t id=0;

   time(&td);              /* get time */
   tm = *localtime(&td);   /* get components */
   strftime(timestr, sizeof(timestr), "%G-%m-%d %H:%M:%S", &tm);

-  snprintf(query, DEF_QUERYSIZE, "SELECT idnr FROM pbsp WHERE ipnumber =
'%s'", ip);
-  if (db_query_read(query) == -1)
-    {
-      trace(TRACE_ERROR,"db_log_ip(): could not access ip-log table
(pop/imap-before-smtp): %s",
-           mysql_error(&read_conn));
-      return -1;
-    }
-
-  if ((res = mysql_store_result(&read_conn)) == NULL)
-    {
-      trace(TRACE_ERROR,"db_log_ip(): could not check ip-log
(pop/imap-before-smtp): %s",
-           mysql_error(&read_conn));
-      return -1;
-    }
-
-  row = mysql_fetch_row(res);
-  id = row ? strtoull(row[0], NULL, 10) : 0;
-
-  mysql_free_result(res);
-
-  if (id)
-    {
-      /* this IP is already in the table, update the 'since' field */
-      snprintf(query, DEF_QUERYSIZE, "UPDATE pbsp SET since = '%s' WHERE
idnr=%llu",timestr,id);
-
-      if (db_query(query) == -1)
-       {
-         trace(TRACE_ERROR,"db_log_ip(): could not update ip-log
(pop/imap-before-smtp): %s",
-               mysql_error(&write_conn));
-         return -1;
-       }
-    }
-  else
-    {
-      /* IP not in table, insert row */
-      snprintf(query, DEF_QUERYSIZE, "INSERT INTO pbsp (since, ipnumber)
VALUES ('%s','%s')",
+  snprintf(query, DEF_QUERYSIZE, "REPLACE INTO pbsp (since, ipnumber)
VALUES ('%s','%s')",
               timestr, ip);

       if (db_query(query) == -1)
@@ -1579,7 +1543,6 @@
                mysql_error(&write_conn));
          return -1;
        }
-    }

   trace(TRACE_DEBUG,"db_log_ip(): ip [%s] logged\n",ip);
   return 0;