/* $Id: database.c,v 1.8 2006/03/03 17:27:38 bhockney Exp $ */

/* (C) 2004-2006 by Bob Hockney <zeus@ix.netcom.com>    *
 *                                                      *
 * Host and service name resolution for wfwl_syslog     *
 *                                                      *
 * This code is distributed under the terms of GNU GPL  */

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include <syslog.h>
#include <errno.h>
#include <sys/types.h>
#include <sys/wait.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include "main.h"
#include "utils.h"
#include "database.h"

extern struct options opt;
extern struct conn_data *first;
extern struct conn_data_sum *data_sum;
extern struct fields_used fields_used;

unsigned char have_hostnames = 0;
unsigned char have_services = 0;
char *query;
char *errmsg;

#ifdef HAVE_MYSQL
#include <mysql.h>
MYSQL mhd;
MYSQL_RES *mysql_res;
MYSQL_ROW mysql_row;
#endif

#ifdef HAVE_PGSQL
#include <libpq-fe.h>
PGconn *pgconn;
PGresult *pgresult;
PGresult *pgresult2;
char *pg_schema = (char *)&opt.pgsql_wfwl_schema;
#endif

#if HAVE_MYSQL || HAVE_PGSQL
/* Runs database query and returns status as OK or ERROR */
/* Called with test constant for qood query and result number (postgresql)
 * and string message for reporting errors */
int run_query(int queryok, int result, char *e) {

#ifdef HAVE_PGSQL
  PGresult *q = NULL;
#endif

#ifdef HAVE_MYSQL
  if (opt.have_db && strcmp((char *)&opt.db, "mysql") == 0) {
    if (opt.verbose >= VERBOSE_DEBUG)
      fprintf(stderr, query);      
    if (!(mysql_query(&mhd, query) == 0)) {
      if (opt.verbose >= VERBOSE_NOTICE) {
        fprintf(stderr, e);
        fprintf(stderr, "  Server said: %s\n", mysql_error(&mhd));
      }
      return QUERY_ERROR;
    }
  }
#endif

#ifdef HAVE_PGSQL
  if (opt.have_db && strcmp((char *)&opt.db, "pgsql") == 0) {
    if (opt.verbose >= VERBOSE_DEBUG)
      fprintf(stderr, query);  
    q = PQexec(pgconn, query);
    if (!(PQresultStatus(q) == queryok) && opt.verbose >= VERBOSE_NOTICE) {
      fprintf(stderr, e, strip_nl(PQerrorMessage(pgconn)));
      fprintf(stderr, "  Server said: %s\n", strip_nl(PQerrorMessage(pgconn)));
      PQclear(q);
      return QUERY_ERROR;
    }
    if (result == 0) pgresult  = q;
    if (result == 1) pgresult2 = q;

  }
#endif

  return QUERY_OK;
}

#endif

/* Opens a connection to the database server
 * and determines if hostname and service name caches are available */
int open_db(char *db) {

  query = xmalloc(QUERYSIZE);

#ifdef HAVE_MYSQL
  if (strcmp(db, "mysql") == 0) {
    mysql_init(&mhd);
    if (mysql_real_connect(&mhd, (char *)&opt.mysql_server, (char *)&opt.mysql_user,
                           (char *)&opt.mysql_pass, (char*)&opt.mysql_wfwl_db, 3306,
                           NULL, 0) == NULL) {
      if (opt.verbose >= VERBOSE_NOTICE)
        fprintf(stderr, "Could not open MySQL database.  Server said: %s\n", mysql_error(&mhd));
      opt.have_db = 0;

      return FAILURE;

    } else {
      if (opt.verbose >= VERBOSE_INFO)
        fprintf(stderr, "Connected to MySQL database %s on %s\n", (char *)&opt.mysql_wfwl_db, (char *)&opt.mysql_server);

      if (opt.resolve_hosts) {
        if (mysql_query(&mhd, "SELECT count(*) FROM hostnames LIMIT 1\0") == 0) {
          mysql_res = mysql_use_result(&mhd);
          mysql_free_result(mysql_res);
          have_hostnames = 1;
          if (opt.verbose >= VERBOSE_NOTICE)
            fprintf(stderr, "Have hostnames cache\n");
        } else {      
          if (opt.verbose >= VERBOSE_WARNING)
            fprintf(stderr, "Hostnames cache not available. Server said: %s\n", mysql_error(&mhd));
        }
      }
      if (opt.resolve_hosts) {
        if (mysql_query(&mhd, "SELECT count(*) FROM services LIMIT 1\0") == 0) {
          mysql_res = mysql_store_result(&mhd);
          mysql_free_result(mysql_res);
          have_services = 1;
          if (opt.verbose >= VERBOSE_NOTICE)
            fprintf(stderr, "Have service names cache\n");
        } else {      
          if (opt.verbose >= VERBOSE_WARNING)
            fprintf(stderr, "Service names cache not available. Server said: %s\n", mysql_error(&mhd));
        }
      }

      if (opt.verbose >= VERBOSE_NOTICE && have_hostnames && (opt.resolve_hosts & CACHE_POPULATE))
        fprintf(stderr, "Populating hostname cache\n");

      if (opt.verbose >= VERBOSE_NOTICE && have_services && (opt.resolve_hosts & CACHE_POPULATE))
        fprintf(stderr, "Populating service name cache\n");

      if (opt.verbose >= VERBOSE_NOTICE && have_hostnames && (opt.resolve_hosts & CACHE_UPDATE)
                         && (fields_used.shost_name || fields_used.dhost_name))
        fprintf(stderr, "Resolving host names\n");

      if (opt.verbose >= VERBOSE_NOTICE && have_services && (opt.resolve_hosts & CACHE_UPDATE)
                         && (fields_used.src_service || fields_used.dst_service))
        fprintf(stderr, "Resolving service names\n");

      return SUCCESS;
    }
  }
#endif

#ifdef HAVE_PGSQL
  if (strcmp(db, "pgsql") == 0) {
    if (opt.pgsql_have_namespace) {
      strcat(pg_schema, ".");
    } else {
      *pg_schema = '\0';
    }
    pgconn = PQsetdbLogin(strip_nl((char *)&opt.pgsql_server),
                     NULL, NULL, NULL,
                     (char *)&opt.pgsql_db,
                     (char *)&opt.pgsql_user,
                     (char *)&opt.pgsql_pass);

    if (PQstatus(pgconn) == CONNECTION_BAD) {
      fprintf(stderr, "Could not open PostgreSQL database.  Server said: %s\n", strip_nl(PQerrorMessage(pgconn)));
      return FAILURE;
    } else {
      if (opt.verbose >= VERBOSE_INFO)
        fprintf(stderr, "Connected to PostgreSQL database %s on %s\n",
          (char *)&opt.pgsql_db, strip_nl((char *)&opt.pgsql_server));

      if (opt.resolve_hosts) {
        sprintf(query, "SELECT count(*) FROM %shostnames LIMIT 1", pg_schema);
        pgresult = PQexec(pgconn, query);
        if (PQresultStatus(pgresult) != PGRES_FATAL_ERROR) {
          PQclear(pgresult);
          have_hostnames = 1;
          if (opt.verbose >= VERBOSE_NOTICE)
            fprintf(stderr, "Have hostnames cache\n");
        } else {      
          if (opt.verbose >= VERBOSE_WARNING)
            fprintf(stderr, "Hostnames cache not available. Server said: %s\n", strip_nl(PQerrorMessage(pgconn)));
        }
      }
      if (opt.resolve_hosts) {
        sprintf(query, "SELECT count(*) FROM %sservices LIMIT 1", pg_schema);
        pgresult = PQexec(pgconn, query);
        if (PQresultStatus(pgresult) != PGRES_FATAL_ERROR) {
          PQclear(pgresult);
          have_services = 1;
          if (opt.verbose >= VERBOSE_NOTICE)
            fprintf(stderr, "Have service names cache\n");
        } else {      
          if (opt.verbose >= VERBOSE_WARNING)
            fprintf(stderr, "Service names cache not available. Server said: %s\n", strip_nl(PQerrorMessage(pgconn)));
        }
      }

      if (opt.verbose >= VERBOSE_NOTICE && have_hostnames && (opt.resolve_hosts & CACHE_POPULATE))
        fprintf(stderr, "Populating hostname cache\n");

      if (opt.verbose >= VERBOSE_NOTICE && have_services && (opt.resolve_hosts & CACHE_POPULATE))
        fprintf(stderr, "Populating service name cache\n");

      if (opt.verbose >= VERBOSE_NOTICE && have_hostnames && (opt.resolve_hosts & CACHE_UPDATE)
                         && (fields_used.shost_name || fields_used.dhost_name))
        fprintf(stderr, "Resolving host names\n");

      if (opt.verbose >= VERBOSE_NOTICE && have_services && (opt.resolve_hosts & CACHE_UPDATE)
                         && (fields_used.src_service || fields_used.dst_service))
        fprintf(stderr, "Resolving service names\n");

      return SUCCESS;
    }
  }
#endif

  if (opt.verbose >= VERBOSE_INFO)
    fprintf(stderr, "No database server available.  (Is support compiled in?)\n");

  return FAILURE;
}

/* Closes the connection to the database server */
void close_db(char *db) {

#ifdef HAVE_MYSQL
  if (strcmp(db, "mysql") == 0) {
    mysql_close(&mhd);
  }
#endif

#ifdef HAVE_PGSQL
  if (strcmp(db, "pgsql") == 0) {
    PQfinish(pgconn);
  }

#endif

  free(query);
}

/* Lookup hostname in cache */
void get_hostname(struct in_addr *ip, char * hostname) {

  strcpy(hostname, "-");

#ifdef HAVE_MYSQL
  if (opt.have_db && strcmp((char *)&opt.db, "mysql") == 0) {
    sprintf(query, "SELECT hostname FROM hostnames WHERE refresh=0 AND ip_addr=%lu\n",
      (unsigned long int)ntohl(ip->s_addr));
    if (have_hostnames && run_query(0, 0, "Could not resolve hostname.") == 0) {
      if (mysql_field_count(&mhd)) {
        mysql_res = mysql_store_result(&mhd);
        if(mysql_num_rows(mysql_res)) { 
          mysql_row = mysql_fetch_row(mysql_res);
          strcpy(hostname, mysql_row[0]);
          if (opt.verbose >= VERBOSE_DEBUG)
            fprintf(stderr, "Hostname is: %s\n", hostname);
        }
        mysql_free_result(mysql_res);
      }
    }
  }
#endif

#ifdef HAVE_PGSQL
  if (opt.have_db && strcmp((char *)&opt.db, "pgsql") == 0) {
    sprintf(query, "SELECT hostname FROM %shostnames WHERE refresh=0 AND ip_addr=%lu\n",
                    pg_schema, (unsigned long int)ntohl(ip->s_addr));
    if (have_hostnames && run_query(PGRES_TUPLES_OK, 0, "Could not resolve hostname.") == 0) {; 
      if (PQntuples(pgresult)) { /* true if rows returned != 0 */
        strcpy(hostname, PQgetvalue(pgresult, 0, 0));
        if (opt.verbose >= VERBOSE_DEBUG)
          fprintf(stderr, "Hostname is: %s\n", hostname);
      }
      PQclear(pgresult);
    }
  }
#endif

}

/* Populate hostnames cache and resolve hostnames in cache
 * for hosts appearing in report */
void resolve_hosts () {
  struct conn_data *this;
#if HAVE_MYSQL || HAVE_PGSQL
  struct in_addr ip;
  int max = 0, skip, refresh = 0;
#ifdef HAVE_PGSQL
  int i;
#endif
#endif

  this = first;

#ifdef HAVE_MYSQL
  if (opt.have_db && have_hostnames && strcmp((char *)&opt.db, "mysql") == 0) {
    for (skip = opt.begin; this != NULL && skip > 0; skip-- ) {
      this = this->next;
    }
    while ((this != NULL) && (opt.max == 0 || max < opt.max)) {
      refresh = 2;
      if (fields_used.shost_name) {
        sprintf(query, "DELETE FROM hostnames WHERE refresh=1 AND ip_addr=%lu\n",
                (unsigned long int)ntohl(this->shost.s_addr));
        run_query(0, 0, "Could not delete host."); 
        sprintf(query, "INSERT IGNORE INTO hostnames (refresh, ip_addr) VALUES (%d, %lu)\n",
                refresh, (unsigned long int)ntohl(this->shost.s_addr));
        run_query(0, 0, "Could not insert host."); 
      }
      if (fields_used.dhost_name) {
        sprintf(query, "DELETE FROM hostnames WHERE refresh=1 AND ip_addr=%lu\n",
                (unsigned long int)ntohl(this->dhost.s_addr));
        run_query(0, 0, "Could not delete host."); 
        sprintf(query, "INSERT IGNORE INTO hostnames (refresh, ip_addr) VALUES (%d, %lu)\n",
                refresh, (unsigned long int)ntohl(this->dhost.s_addr));
        run_query(0, 0, "Could not insert host."); 
      }
      if (opt.max != 0)
        max++;
      this = this->next;
    }
    sprintf(query, "SELECT ip_addr FROM hostnames WHERE refresh=2\n");
    run_query(0, 0, "Could not select hosts.");
    mysql_res = mysql_store_result(&mhd);
    if(mysql_num_rows(mysql_res) && opt.verbose >= VERBOSE_NOTICE)
      fprintf(stderr, "Found %lu new hosts\n", (unsigned long)mysql_num_rows(mysql_res));

    mysql_row = mysql_fetch_row(mysql_res);
    while (mysql_row != NULL) {
      ip.s_addr = htonl(strtoul(mysql_row[0],NULL,10));
      sprintf(query, "UPDATE hostnames set refresh=0, hostname='%s' WHERE ip_addr=%lu\n",
                      resolve_hostname(ip),
                      strtoul(mysql_row[0],NULL,10));
      run_query(0, 0, "Could not update hostname.");
      mysql_row = mysql_fetch_row(mysql_res);
    }
    mysql_free_result(mysql_res);
  }
#endif

#ifdef HAVE_PGSQL
  if (opt.have_db && have_hostnames && strcmp((char *)&opt.db, "pgsql") == 0) {
    for (skip = opt.begin; this != NULL && skip > 0; skip-- ) {
      this = this->next;
    }
    sprintf(query, "CREATE TEMPORARY TABLE update ( \
        refresh int NOT NULL DEFAULT 1, \
        ip_addr bigint NOT NULL DEFAULT 0)\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not create temporaty table.") == 0)
      PQclear(pgresult);
    
    while ((this != NULL) && (opt.max == 0 || max < opt.max)) {
      refresh = 2;
      if (fields_used.shost_name) {
        sprintf(query, "INSERT INTO update (refresh, ip_addr) VALUES (%d, %lu)\n",
                refresh, (unsigned long int)ntohl(this->shost.s_addr));
        if (run_query(PGRES_COMMAND_OK, 0, "Could not insert hostname.") == 0)
          PQclear(pgresult);
      }
      if (fields_used.dhost_name) {
        sprintf(query, "INSERT INTO update (refresh, ip_addr) VALUES (%d, %lu)\n",
                refresh, (unsigned long int)ntohl(this->dhost.s_addr));
        if (run_query(PGRES_COMMAND_OK, 0, "Could not insert hostname.") == 0)
          PQclear(pgresult);
      }
      if (opt.max != 0)
        max++;
      this = this->next;
    }
    sprintf(query, "UPDATE %shostnames SET refresh=2 FROM update \
        WHERE %shostnames.ip_addr=update.ip_addr \
        AND update.refresh=2 AND %shostnames.refresh=1\n",
        pg_schema, pg_schema, pg_schema);
    if (run_query(PGRES_COMMAND_OK, 0, "Could not update hostnames.") == 0)
      PQclear(pgresult);
    sprintf(query, "INSERT INTO %shostnames (refresh, ip_addr) \
        SELECT DISTINCT max(update.refresh),update.ip_addr FROM update \
        LEFT JOIN %shostnames ON update.ip_addr=%shostnames.ip_addr \
        WHERE %shostnames.ip_addr IS NULL GROUP BY update.ip_addr\n",
        pg_schema, pg_schema, pg_schema, pg_schema);
    if (run_query(PGRES_COMMAND_OK, 0, "Could not insert hostnames.") == 0)
      PQclear(pgresult);
    sprintf(query, "SELECT ip_addr FROM %shostnames WHERE refresh=2\n", pg_schema);
    if (run_query(PGRES_TUPLES_OK, 0, "Could not select hostnames.") == 0) {
      if (PQntuples(pgresult) && opt.verbose >= VERBOSE_NOTICE)
        fprintf(stderr, "Found %i new hostnames\n", PQntuples(pgresult));      
      for (i = 0; i < PQntuples(pgresult); i++) {
        ip.s_addr = htonl(strtoul(PQgetvalue(pgresult, i, 0),NULL,10));
        sprintf(query, "UPDATE %shostnames set refresh=0, hostname='%s' WHERE ip_addr=%lu\n",
                        pg_schema, resolve_hostname(ip),
                        strtoul(PQgetvalue(pgresult, i, 0),NULL,10));
        if (run_query(PGRES_COMMAND_OK, 1, "Could not update hostname.") == 0)
          PQclear(pgresult2);
      }
      PQclear(pgresult);
    }

    sprintf(query, "DROP TABLE update\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not drop temporary table update.") == 0)
      PQclear(pgresult);
  } 
#endif

  return;

}

/* Lookup service name in cache */
void get_service_name(int proto, unsigned int port, char * service) {

  strcpy(service, "-");

#ifdef HAVE_MYSQL
  if (opt.have_db && strcmp((char *)&opt.db, "mysql") == 0) {
    sprintf(query, "SELECT service FROM services WHERE refresh=0 AND ip_protocol=%u AND port=%u\n", proto, port);
    if (have_services && run_query(0, 0, "Could not resolve service.") == 0) {
      if (mysql_field_count(&mhd)) {
        mysql_res = mysql_store_result(&mhd);
        if(mysql_num_rows(mysql_res)) { 
          mysql_row = mysql_fetch_row(mysql_res);
          strcpy(service, mysql_row[0]);
          if (opt.verbose >= VERBOSE_DEBUG)
            fprintf(stderr, "Service Name is: %s\n", service);

        }
        mysql_free_result(mysql_res);
      }
    }
  }
#endif

#ifdef HAVE_PGSQL
  if (opt.have_db && strcmp((char *)&opt.db, "pgsql") == 0) {
    sprintf(query, "SELECT service FROM %sservices WHERE refresh=0 AND ip_protocol=%u AND port=%u\n",
                   pg_schema, proto, port);
    if (have_services && run_query(PGRES_TUPLES_OK, 0, "Could not resolve service name.") == 0) {
      if (PQntuples(pgresult)) { /* true if rows returned != 0 */
        strcpy(service, PQgetvalue(pgresult, 0, 0));
        if (opt.verbose >= VERBOSE_DEBUG)
          fprintf(stderr, "Service Name is: %s\n", service);
      } else
      PQclear(pgresult);
    }
  }
#endif

}

/* Populate service names cache and resolve service names in cache
 * for services appearing in report */
void resolve_services () {
  struct conn_data *this;
#if HAVE_MYSQL || HAVE_PGSQL
  int max = 0, skip, refresh = 0;
#ifdef HAVE_PGSQL
  int i;
#endif
#endif

  this = first;

#ifdef HAVE_MYSQL
  if (opt.have_db && have_services && strcmp((char *)&opt.db, "mysql") == 0) {
    for (skip = opt.begin; this != NULL && skip > 0; skip-- ) {
      this = this->next;
    }
    while ((this != NULL) && (opt.max == 0 || max < opt.max)) {
      if (this->protocol == 6 || this->protocol == 17) {
        refresh = 2;
        if (fields_used.src_service) {
          sprintf(query, "DELETE FROM services WHERE refresh=1 AND ip_protocol=%d AND port=%d\n",
                  this->protocol, this->sport);
          run_query(0, 0, "Could not delete service.");
          sprintf(query, "INSERT IGNORE INTO services (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, this->protocol, this->sport);
          run_query(0, 0, "Could not insert service.");
        }
        if (fields_used.dst_service) {
          sprintf(query, "DELETE FROM services WHERE refresh=1 AND ip_protocol=%d AND port=%d\n",
                  this->protocol, this->dport);
          run_query(0, 0, "Could not delete service.");
          sprintf(query, "INSERT IGNORE INTO services (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, this->protocol, this->dport);
          run_query(0, 0, "Could not insert service.");
        }
      }
      if (opt.max != 0)
        max++;
      this = this->next;
    }
    sprintf(query, "SELECT port, ip_protocol FROM services WHERE refresh=2\n");
    run_query(0, 0, "Could not select services.");
    mysql_res = mysql_store_result(&mhd);
    if(mysql_num_rows(mysql_res) && opt.verbose >= VERBOSE_NOTICE)
      fprintf(stderr, "Found %lu new services\n", (unsigned long)mysql_num_rows(mysql_res));

    mysql_row = mysql_fetch_row(mysql_res);
    while (mysql_row != NULL) {
      sprintf(query, "UPDATE services set refresh=0, service='%s' WHERE port=%li AND ip_protocol=%li\n",
                      get_service(atol(mysql_row[0]), atol(mysql_row[1])),
                      atol(mysql_row[0]), atol(mysql_row[1]));
      run_query(0, 0, "Could not update service.");
      mysql_row = mysql_fetch_row(mysql_res);
    }
    mysql_free_result(mysql_res);
  }
#endif

#ifdef HAVE_PGSQL
  if (opt.have_db && have_services && strcmp((char *)&opt.db, "pgsql") == 0) {
    for (skip = opt.begin; this != NULL && skip > 0; skip-- ) {
      this = this->next;
    }
    sprintf(query, "CREATE TEMPORARY TABLE update ( \
        refresh int NOT NULL DEFAULT 1, \
        port int NOT NULL DEFAULT 0, \
        ip_protocol int NOT NULL DEFAULT 0)\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not create temporary table.") == 0) 
      PQclear(pgresult);
    
    while ((this != NULL) && (opt.max == 0 || max < opt.max)) {
      if (this->protocol == 6 || this->protocol == 17) {
        refresh = 2;
        if (fields_used.src_service) {
          sprintf(query, "INSERT INTO update (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, this->protocol, this->sport);
          if (run_query(PGRES_COMMAND_OK, 0, "Could not insert service.") == 0)
            PQclear(pgresult);
        }
        if (fields_used.dst_service) {
          sprintf(query, "INSERT INTO update (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, this->protocol, this->dport);
          if (run_query(PGRES_COMMAND_OK, 0, "Could not insert service.") == 0)
            PQclear(pgresult);
        }
      }
      if (opt.max != 0)
        max++;
      this = this->next;
    }
    sprintf(query, "UPDATE %sservices SET refresh=2 FROM update \
        WHERE %sservices.ip_protocol=update.ip_protocol AND %sservices.port=update.port \
        AND update.refresh=2 AND %sservices.refresh=1\n",
        pg_schema, pg_schema, pg_schema, pg_schema);
    if (run_query(PGRES_COMMAND_OK, 0, "Could not update services.") == 0)
      PQclear(pgresult);
    sprintf(query, "INSERT INTO %sservices (refresh, port, ip_protocol) \
        SELECT DISTINCT max(update.refresh), update.port, update.ip_protocol FROM update \
        LEFT JOIN %sservices ON update.port=%sservices.port AND update.ip_protocol=%sservices.ip_protocol \
        WHERE %sservices.port IS NULL GROUP BY update.ip_protocol, update.port\n",
        pg_schema, pg_schema, pg_schema, pg_schema, pg_schema);
    if (run_query(PGRES_COMMAND_OK, 0, "Could not insert services.") == 0)
      PQclear(pgresult);
    sprintf(query, "SELECT port, ip_protocol FROM %sservices WHERE refresh=2\n", pg_schema);
    if (run_query(PGRES_TUPLES_OK, 0, "Could not select services.") == 0) {
      if (PQntuples(pgresult) && opt.verbose >= VERBOSE_NOTICE)
        fprintf(stderr, "Found %i new services\n", PQntuples(pgresult));      
	for (i = 0; i < PQntuples(pgresult); i++) {
        sprintf(query, "UPDATE %sservices set refresh=0, service='%s' WHERE port=%li AND ip_protocol=%u\n",
                        pg_schema, get_service(atol(PQgetvalue(pgresult, i, 0)),
                        atoi(PQgetvalue(pgresult, i, 1))), atol(PQgetvalue(pgresult, i, 0)),
                        atoi(PQgetvalue(pgresult, i, 1)));
	  if (run_query(PGRES_COMMAND_OK, 1, "Could not update services.") == 0)
          PQclear(pgresult2);
      }
      PQclear(pgresult);
    }
    sprintf(query, "DROP TABLE update\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not drop table update.") == 0)
      PQclear(pgresult);
  } 
#endif

  return;

}

/* Populate hostnames cache */
void populate_cache () {
  struct conn_data *this;
#if HAVE_MYSQL || HAVE_PGSQL
  int max = 0, skip, refresh = 1;
  struct conn_data_sum *sum;
#endif

  this = first;

#ifdef HAVE_MYSQL
  if (opt.have_db && have_hostnames && have_services && strcmp((char *)&opt.db, "mysql") == 0) {
    for (skip = opt.begin; this != NULL && skip > 0; skip-- ) {
      this = this->next;
    }
    while ((this != NULL) && (opt.max == 0 || max < opt.max)) {
      sum = data_sum;
      while(sum != NULL) {
        if (sum->rowid == this->rowid) {
          sprintf(query, "INSERT IGNORE INTO hostnames (refresh, ip_addr) VALUES (%d, %lu)\n",
                  refresh, (unsigned long int)ntohl(sum->shost.s_addr));
          run_query(0, 0, "Could not insert host."); 
          sprintf(query, "INSERT IGNORE INTO hostnames (refresh, ip_addr) VALUES (%d, %lu)\n",
                  refresh, (unsigned long int)ntohl(sum->dhost.s_addr));
          run_query(0, 0, "Could not insert host.");
          sprintf(query, "INSERT IGNORE INTO services (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, sum->protocol, sum->sport);
          run_query(0, 0, "Could not insert service.");
          sprintf(query, "INSERT IGNORE INTO services (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, sum->protocol, sum->dport);
          run_query(0, 0, "Could not insert service.");
        }
        sum = sum->next;
      }
      if (opt.max != 0)
        max++;
      this = this->next;
    }
  }
#endif

#ifdef HAVE_PGSQL
  if (opt.have_db && have_hostnames && strcmp((char *)&opt.db, "pgsql") == 0) {
    for (skip = opt.begin; this != NULL && skip > 0; skip-- ) {
      this = this->next;
    }
    sprintf(query, "CREATE TEMPORARY TABLE update_hostnames ( \
        refresh int NOT NULL DEFAULT 1, \
        ip_addr bigint NOT NULL DEFAULT 0)\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not create temporaty table.") == 0)
      PQclear(pgresult);
    sprintf(query, "CREATE TEMPORARY TABLE update_services ( \
        refresh int NOT NULL DEFAULT 1, \
        port int NOT NULL DEFAULT 0, \
        ip_protocol int NOT NULL DEFAULT 0)\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not create temporaty table.") == 0)
      PQclear(pgresult);

    while ((this != NULL) && (opt.max == 0 || max < opt.max)) {
      sum = data_sum;
      while(sum != NULL) {
        if (sum->rowid == this->rowid) {
          sprintf(query, "INSERT INTO update_hostnames (refresh, ip_addr) VALUES (%d, %lu)\n",
                  refresh, (unsigned long int)ntohl(sum->shost.s_addr));
          if (run_query(PGRES_COMMAND_OK, 0, "Could not insert hostname.") == 0)
            PQclear(pgresult);
          sprintf(query, "INSERT INTO update_hostnames (refresh, ip_addr) VALUES (%d, %lu)\n",
                  refresh, (unsigned long int)ntohl(sum->dhost.s_addr));
          if (run_query(PGRES_COMMAND_OK, 0, "Could not insert hostname.") == 0)
            PQclear(pgresult);
          sprintf(query, "INSERT INTO update_services (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, sum->protocol, sum->sport);
          if (run_query(PGRES_COMMAND_OK, 0, "Could not insert service.") == 0)
            PQclear(pgresult);
          sprintf(query, "INSERT INTO update_services (refresh, ip_protocol, port) VALUES (%d, %i, %u)\n",
                  refresh, sum->protocol, sum->dport);
          if (run_query(PGRES_COMMAND_OK, 0, "Could not insert service.") == 0)
            PQclear(pgresult);
        }
        sum = sum->next;
      }
      if (opt.max != 0)
        max++;
      this = this->next;
    }
    sprintf(query, "INSERT INTO %shostnames (refresh, ip_addr) \
        SELECT DISTINCT max(update_hostnames.refresh), update_hostnames.ip_addr FROM update_hostnames \
        LEFT JOIN %shostnames ON update_hostnames.ip_addr=%shostnames.ip_addr \
        WHERE %shostnames.ip_addr IS NULL GROUP BY update_hostnames.ip_addr\n",
        pg_schema, pg_schema, pg_schema, pg_schema);
    if (run_query(PGRES_COMMAND_OK, 0, "Could not insert hostnames.") == 0)
      PQclear(pgresult);
    sprintf(query, "INSERT INTO %sservices (refresh, port, ip_protocol) \
        SELECT DISTINCT max(update_services.refresh), update_services.port, update_services.ip_protocol FROM update_services \
        LEFT JOIN %sservices ON update_services.port=%sservices.port AND update_services.ip_protocol=%sservices.ip_protocol \
        WHERE %sservices.port IS NULL GROUP BY update_services.ip_protocol, update_services.port\n",
        pg_schema, pg_schema, pg_schema, pg_schema, pg_schema);
    if (run_query(PGRES_COMMAND_OK, 0, "Could not insert services.") == 0)
      PQclear(pgresult);
    sprintf(query, "DROP TABLE update_hostnames\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not drop temporary table update_hostnames.") == 0)
      PQclear(pgresult);
    sprintf(query, "DROP TABLE update_services\n");
    if (run_query(PGRES_COMMAND_OK, 0, "Could not drop temporary table update_services.") == 0)
      PQclear(pgresult);

  } 
#endif

  return;

}