gs_storage_mysql.c

Go to the documentation of this file.

/* 
 * @file
 *
 * This implements the interface defined in gs_storage.h for
 * the MySQL client library.
 */
/* $Id: gs_storage_mysql.c,v 1.10 2008/12/15 21:00:59 seymour Exp $ */
/* $UTK_Copyright: $ */

#include <ctype.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <time.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <sys/un.h>
#include <unistd.h>

#include "gs_storage.h"
#include "gs_storage_sql_tables.h"
#include "utility.h"
#include "server.h"
#include "problem.h"
#include "agent.h"
#include "comm_basics.h"
#include "comm_encode.h"
#include "mysql.h"


static int
  gs_insert_task(char *, char *, int, double, double, double, double, int, int,
    char *, int);

int
  gs_create_criteria_table(char *name, char *description, char *firstValue),
  gs_mysql_create_clean_db(MYSQL *, const char *, const char *, 
      const char *, const char *, unsigned int, const char *),
  gs_add_single_problem(gs_agent_t * gs_agent, gs_problem_t * gs_problem,
      char *cid_string);

MYSQL mysql_conn;

int
gs_mysql_lock_tables(char *tables[], int n)
{
  char *sql, *tmp;
  int i;

  sql = dstring_sprintf("LOCK TABLES ");
  if(!sql) {
    ERRPRINTF("failed to create string\n");
    return -1;
  }

  for(i=0;i<n;i++) {
    tmp = dstring_sprintf("%s WRITE%c", tables[i], (i==(n-1)) ? ';' : ',');
    if(!tmp) {
      ERRPRINTF("failed to create tmp string\n");
      free(sql);
      return -1;
    }

    sql = dstring_append(sql, tmp);
    if(!sql) {
      ERRPRINTF("failed to create string\n");
      if(tmp) free(tmp);
      return -1;
    }
    free(tmp);
  }

  if(mysql_query(&mysql_conn, sql)) { 
    ERRPRINTF("Failed to lock tables: Error: %s (%d)\n", mysql_error(&mysql_conn), 
       mysql_errno(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  return 0;
}

int
gs_mysql_unlock_tables()
{
  char *sql;

  sql = dstring_sprintf("UNLOCK TABLES;");
  if(!sql) {
    ERRPRINTF("failed to create string\n");
    return -1;
  }

  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("Failed to unlock tables: Error: %s (%d)\n", mysql_error(&mysql_conn),
       mysql_errno(&mysql_conn));
    free(sql);
    return -1;
  }
  free(sql);

  return 0;
}

int
gs_mysql_init_tables(MYSQL *mysql)
{
  char *cmds[] = {
    GS_SQL_SERVER_TABLE,
    GS_SQL_PROBLEM_TABLE,
    GS_SQL_PROBLEM_SERVER_MAPPING_TABLE,
    GS_SQL_CRITERIA_TABLE,
    GS_SQL_TASKS_TABLE,
    GS_SQL_INIT_TASKS_TABLE,
    GS_SQL_COMPLETED_TASKS_TABLE,
    GS_SQL_INIT_COMPLETED_TASKS_TABLE,
    GS_SQL_EXTRA_OPTIONS,
    NULL};
  int i;

  for(i=0; cmds[i]; i++) {
    if(!strcmp(cmds[i], ""))
      continue;

    if(mysql_query(mysql, cmds[i])) {
      ERRPRINTF("Query failed: Error: %s (%d)\n", mysql_error(mysql), mysql_errno(mysql));
      return -1;
    }
  }

  return 0;
}

int
gs_mysql_init_db(gs_agent_t *gs_agent)
{
  MYSQL mysql;

  if(mysql_init(&mysql) == NULL) {
    ERRPRINTF("\nFailed to initate MySQL connection");
    return -1;
  }

  if(gs_mysql_create_clean_db(&mysql, gs_agent->mysql.host, gs_agent->mysql.user,
       gs_agent->mysql.passwd, gs_agent->mysql.db_name, gs_agent->mysql.port,
       gs_agent->mysql.unix_socket) < 0)
  {
    ERRPRINTF("\nFailed to create db.");
    mysql_close(&mysql);
    return -1;
  }

  if(gs_mysql_init_tables(&mysql) < 0) {
    ERRPRINTF("\nFailed to init tables.");
    mysql_close(&mysql);
    return -1;
  }

  mysql_close(&mysql);

  return 0;
}

int
gs_mysql_create_clean_db(MYSQL *mysql, const char *host, const char *user, 
  const char *passwd, const char *db, unsigned int port, const char *unix_socket)
{
  char *sql;

  if(!mysql_real_connect(mysql, host, user, passwd, db, port, unix_socket, 0)) {
    if(!mysql_real_connect(mysql, host, user, passwd, 0, port, unix_socket, 0)) {
      ERRPRINTF("Failed to connect to MySQL: Error: %s\n", mysql_error (mysql));
      return -1;
    }
  }
  else {
    sql = dstring_sprintf("DROP DATABASE %s;", db);

    if(mysql_query(mysql, sql)) { 
      ERRPRINTF("Failed to drop db: Error: %s (%d)\n", mysql_error(mysql), mysql_errno(mysql));
      free(sql);
      return -1;
    }
    free(sql);
  }

  sql = dstring_sprintf("CREATE DATABASE %s;", db);

  if(mysql_query(mysql, sql)) {
    ERRPRINTF("Failed to create db: Error: %s (%d)\n", mysql_error(mysql), mysql_errno(mysql));
    free(sql);
    return -1;
  }
  free(sql);

  if(mysql_select_db(mysql, db)) {
    ERRPRINTF("Failed to select db: Error: %s (%d)\n", mysql_error(mysql), mysql_errno(mysql));
    return -1;
  }

  return 0;
}

int
gs_storage_init(gs_agent_t * gs_agent)
{
  if(mysql_init(&mysql_conn) == NULL) {
    ERRPRINTF("Failed to initiate MySQL connection\n");
    return -1;
  }

  if(!mysql_real_connect(&mysql_conn, gs_agent->mysql.host, gs_agent->mysql.user, gs_agent->mysql.passwd,
       gs_agent->mysql.db_name, gs_agent->mysql.port, gs_agent->mysql.unix_socket, 0)) {
    ERRPRINTF("Failed to connect to database: %s\n", mysql_error(&mysql_conn));
    return -1;
  }

  if(mysql_select_db(&mysql_conn, gs_agent->mysql.db_name)) {
    printf("Failed to select db: Error: %s (%d)\n", mysql_error(&mysql_conn),
         mysql_errno(&mysql_conn));
    return -1;
  }

  return 0;
}

void
gs_storage_finalize(gs_agent_t * gs_agent)
{
  mysql_close(&mysql_conn);
  return;
}

int
gs_add_server(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  int now, i;
  char *sql, cid_string[2 * CID_LEN + 1],
      server_dottedIP[20], proxy_dottedIP[20], *sa_str,
      *escaped_sa_str, *sp_str;
  gs_info_t *attrs;

  DBGPRINTF("Adding Server.\n");

  proxy_cid_to_str(cid_string, gs_server->componentid);
  proxy_ip_to_str(gs_server->ipaddress, server_dottedIP);
  proxy_ip_to_str(gs_server->proxyip, proxy_dottedIP);
  sa_str = strdup("");
  sp_str = strdup(GS_NO_SERVER_PING_UPDATE);
  gs_encode_infolist(&sa_str, gs_server->sa_list);

  escaped_sa_str = (char *)malloc(2 * strlen(sa_str) + 1);
  if(!escaped_sa_str) {
    ERRPRINTF("malloc failed\n");
    free(sa_str);
    return -1;
  }

  mysql_real_escape_string(&mysql_conn, escaped_sa_str, sa_str, strlen(sa_str));

  now = time(0);
  sql = dstring_sprintf("INSERT INTO servers (hostname,ipaddress,port,proxyip,proxyport,componentid,arch,data_format,kflops,workload,ncpu,status,availcpu,availmem,nproblems,agenthost,agentport,smart,lastupdate,addedat,infolist,server_pings) VALUES ('%s','%s','%d','%s','%d','%s','%s','%d','%d','%d','%d','%d','%lf','%lf','%d','%s','%d','%d','%ld','%ld','%s','%s');",
       gs_server->hostname, server_dottedIP, gs_server->port, proxy_dottedIP,
       gs_server->proxyport, cid_string, gs_server->arch,
       gs_server->data_format, gs_server->kflops, gs_server->workload,
       gs_server->ncpu, gs_server->status, gs_server->availcpu,
       gs_server->availmem, gs_server->nproblems, gs_server->agenthost,
       gs_server->agentport, gs_server->smart, now, now, escaped_sa_str, sp_str);

  /* free sa_str, but don't free escaped_sa_str yet, we'll reuse it below */
  free(sa_str);
  free(sp_str);

  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR inserting server: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(escaped_sa_str);
    free(sql);
    return -1;
  }

  free(sql);

  SENSORPRINTF("SRV_UP %s %s\n", gs_server->hostname, cid_string);
  DBGPRINTF("Added Server, now adding attributes.\n");

  /* 
   * Add attributes
   */
  attrs = gs_server->sa_list;
  while(attrs != NULL) {
    i=0;
    while ( !isspace((int)attrs->type[i]) && attrs->type[i] != '\0') i++;
    attrs->type[i] = '\0';
    i=0;
    while ( !isspace((int)attrs->value[i]) && attrs->value[i] != '\0') i++;
    attrs->value[i] = '\0';
    if(gs_create_criteria_table(attrs->type, "", attrs->value) < 0) {
      ERRPRINTF("Error adding criteria table.\n");
      free(escaped_sa_str);
      return -1;
    }

    /* attrs->type shouldn't have any characters that need escaping, 
     * but attrs->value might 
     */
    mysql_real_escape_string(&mysql_conn, escaped_sa_str, attrs->value,
      strlen(attrs->value));
    
    sql = dstring_sprintf("INSERT INTO %s (value,componentid) values ('%s','%s');",
         attrs->type, escaped_sa_str, cid_string);
    DBGPRINTF("Submitting Attribute Query.\n");

    if(mysql_query(&mysql_conn, sql)) {
      ERRPRINTF("SQL ERROR inserting attribute: %s\n", mysql_error(&mysql_conn));
      ERRPRINTF("The query was: %s\n", sql);
      free(escaped_sa_str);
      free(sql);
      return -1;
    }

    free(sql);

    DBGPRINTF("Attribute added: %s.\n", attrs->type);
    attrs = attrs->next;
  }

  free(escaped_sa_str);

  return 0;
}

int
gs_delete_problem_if_last_mapping(char *problemname)
{
  char *sql, *tables_to_lock[] = {"problem_server", "problems"};
  MYSQL_RES *res;

  if(gs_mysql_lock_tables(tables_to_lock, 
       sizeof(tables_to_lock)/sizeof(*tables_to_lock)) < 0)
    ERRPRINTF("Warning: table lock failed\n");

  sql = dstring_sprintf("SELECT 1 FROM problem_server WHERE problemname='%s';",
                       problemname);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR selecting problem-server mappings: %s\n",
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    gs_mysql_unlock_tables();
    free(sql);
    return -1;
  }
  free(sql);

  res=mysql_store_result(&mysql_conn);

  if(mysql_affected_rows(&mysql_conn) == 1) {
    mysql_free_result(res);
    sql = dstring_sprintf("DELETE FROM problems WHERE problemname='%s';",
                       problemname);
    if(mysql_query(&mysql_conn, sql)) {
      ERRPRINTF("SQL ERROR deleting problem: %s\n",
        mysql_error(&mysql_conn));
      ERRPRINTF("The query was: %s\n", sql);
      gs_mysql_unlock_tables();
      free(sql);
      return -1;
    }
    res=mysql_store_result(&mysql_conn);
    free(sql);
  }

  mysql_free_result(res);
  gs_mysql_unlock_tables();

  return 0;
}

int
gs_delete_server_problems(char *cid_string)
{
  MYSQL_RES *res;
  MYSQL_ROW row;
  char *sql;
  int nrows;

  sql = dstring_sprintf("SELECT problemname FROM problem_server WHERE componentid='%s';",
                       cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR deleting problem-server mappings: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  if(nrows > 0) {
    char **pnames;
    int i;

    pnames = (char **)malloc(nrows * sizeof(char *));
    
    for(i=0;i<nrows;i++) {
      row = mysql_fetch_row(res);
      pnames[i] = strdup(row[0]);
    }
    
    mysql_free_result(res);

    for(i=0;i<nrows;i++) {
      if(gs_delete_problem_if_last_mapping(pnames[i]) < 0)
        ERRPRINTF("Warning: could not delete unreferenced problems (if needed)\n");
      free(pnames[i]);
    }

    free(pnames);
  }
  else
    mysql_free_result(res);

  sql = dstring_sprintf("DELETE FROM problem_server WHERE componentid='%s';",
                       cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR deleting problem-server mappings: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  return 0;
}

int
gs_delete_server(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  char *sql, cid_string[2 * CID_LEN + 1];
  gs_info_t *attrs, *tmp;
  MYSQL_RES *res;
  MYSQL_ROW row;

  proxy_cid_to_str(cid_string, gs_server->componentid);

  /* 
   * remove server attributes from their tables.
   */

  sql = dstring_sprintf("SELECT infolist FROM servers WHERE componentid='%s';",
                       cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR selecting infolist: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);

  row = mysql_fetch_row(res);

  attrs = NULL;

  if(row) {
    if(gs_decode_infolist(row[0], &attrs) < 0) {
      ERRPRINTF("Warning: failed to decode infolist\n");
      attrs = NULL;
    }
  }

  mysql_free_result(res);

  while(attrs != NULL) {
    sql = dstring_sprintf("DELETE FROM %s WHERE componentid='%s';",
                         attrs->type, cid_string);
    if(mysql_query(&mysql_conn, sql)) {
      ERRPRINTF("SQL ERROR deleting attribute: %s\n", mysql_error(&mysql_conn));
      ERRPRINTF("The query was: %s\n", sql);
    }

    free(sql);

    tmp = attrs;
    attrs = attrs->next;

    if(tmp) {
      if(tmp->type) free(tmp->type);
      if(tmp->value) free(tmp->value);
      free(tmp);
    }
  }

  /* DELETE SERVER */
  sql = dstring_sprintf("DELETE FROM servers WHERE componentid='%s';",
                       cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR removing server: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  if(gs_delete_server_problems(cid_string) < 0) {
    ERRPRINTF("Failed to delete server-problem mappings\n");
    return -1;
  }

  SENSORPRINTF("SRV_RM %s unknown\n", cid_string);

  return 0;
}

int
gs_insert_submitted_task(char *cid_string, char *taskid, int agent_taskid, 
  double start, double duration, double remaining, double end, int active, 
  int finished)
{
  return gs_insert_task(cid_string, taskid, agent_taskid, start,
    duration, remaining, end, active, finished, "tasks", 1);
}

int
gs_insert_submitted_task_guess(char *cid_string, char *taskid, int agent_taskid,
  double start, double duration, double remaining, double end, int active,
  int finished)
{
  return gs_insert_task(cid_string, taskid, agent_taskid, start,
    duration, remaining, end, active, finished, "tasks", 0);
}

int
gs_insert_completed_task(char *cid_string, char *taskid, int agent_taskid, 
  double start, double duration, double remaining, double end, int active, 
  int finished)
{
  return gs_insert_task(cid_string, taskid, agent_taskid, start,
    duration, remaining, end, active, finished, "completed_tasks", 1);
}

static int
gs_insert_task(char *cid_string, char *taskid, int agent_taskid, double start,
  double duration, double remaining, double end, int active, int finished,
  char *tname, int clobber)
{
  char *sql = 0, *conflict_res;

  switch(clobber) {
    case 0:
      conflict_res = "IGNORE";
      break;
    case 1:
      conflict_res = "REPLACE";
      break;
    default:
      ERRPRINTF("Bad arg value for clobber: %d\n", clobber);
      return -1;
      break;
  }

  sql = dstring_sprintf("INSERT OR %s INTO %s VALUES ('%s','%s','%d','%lf','%lf','%lf','%lf','%d','%d');",
           conflict_res, tname, cid_string, taskid, agent_taskid, start, duration, remaining, end, active, finished);

  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR inserting task: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  return 0;
}

int
gs_update_task(char *cid_string, char *old_taskid, char *new_taskid,
  int agent_taskid, double start, double duration, double remaining, 
  double end, int active, int finished)
{
  char *sql;

  sql = dstring_sprintf("UPDATE tasks SET t_componentid='%s', t_taskid='%s', t_agent_taskid='%d', t_start='%lf', t_duration='%lf', t_remaining='%lf', t_end='%lf', t_active='%d', t_finished='%d' WHERE t_taskid='%s';",
           cid_string, new_taskid, agent_taskid, start, duration, remaining, end, active, finished, old_taskid);

  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR updating task: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  return 0;
}

int
gs_insert_problem(gs_problem_t * gs_problem)
{
  char *sql = 0, *penc = 0, *escaped_penc;

  if(gs_encode_problem(&penc, gs_problem) < 0)
    return -1;

  escaped_penc = (char *) malloc(2 * strlen(penc) + 1);

  if(!escaped_penc) {
    ERRPRINTF("malloc failed\n");
    free(penc);
    return -1;
  }

  mysql_real_escape_string(&mysql_conn, escaped_penc, penc, strlen(penc));

  sql = dstring_sprintf("INSERT INTO problems (problemname,encoding) VALUES ('%s','%s');",
         gs_problem->name, escaped_penc);
  if(mysql_query(&mysql_conn, sql)) {
    gs_mysql_unlock_tables();
    ERRPRINTF("SQL ERROR inserting problem encoding: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    free(penc);
    free(escaped_penc);
    return -1;
  }

  free(sql);
  free(penc);
  free(escaped_penc);

  return 0;
}

int
gs_register_problem_changes(gs_agent_t * gs_agent, 
  gs_problem_t ** gs_problem, int num_services, char **models,
  char **rm_prob, int num_removed, char *cid_string)
{
  char *tables_to_lock[] = {"problems", "problem_server"};
  int i;

  if(gs_mysql_lock_tables(tables_to_lock, 
       sizeof(tables_to_lock)/sizeof(*tables_to_lock)) < 0)
    ERRPRINTF("Warning: table lock failed\n");

  for(i=0;i<num_services;i++) {
    if(gs_add_single_problem(gs_agent, gs_problem[i], cid_string) < 0) {
      gs_mysql_unlock_tables();
      ERRPRINTF("Add problem failed.\n");
      return -1;
    }

    if(gs_update_perf_expr(cid_string, gs_problem[i]->name, models[i]) < 0)
      ERRPRINTF("Warning: failed to update perf model expression\n");
  }

  gs_mysql_unlock_tables();

  for(i=0;i<num_removed;i++) {
    if(gs_delete_problem(gs_agent, rm_prob[i], cid_string) < 0)
      ERRPRINTF("Failed to delete problem: %s\n", rm_prob[i]);
    else
      LOGPRINTF("Unregistered problem %s\n", rm_prob[i]);
  }

  return 0;
}

int
gs_add_single_problem(gs_agent_t * gs_agent, gs_problem_t * gs_problem,
              char *cid_string)
{
  char *sql = 0;
  int pid = 0, nrows = 0;
  MYSQL_RES *res;
  MYSQL_ROW row;

  DBGPRINTF("ADDING %s to %s\n", gs_problem->name, cid_string);

  /* 
   * Check if problem already exists
   */
  sql = dstring_sprintf("SELECT encoding FROM problems WHERE problemname='%s';",
                       gs_problem->name);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR checking for problem: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  if(nrows == 1)
    pid = 1;

  /* 
   * If problem is not in db, insert it
   */
  if(!pid) {
    mysql_free_result(res);

    if(gs_insert_problem(gs_problem) < 0) {
      ERRPRINTF("Failed to insert new problem.\n");
      return -1;
    }
  }
  else {
    int comparison_result;
    char *new_prot, *db_prot;

    /* 
     * Check encoding for differences, looking only at the actual prototype
     */
    gs_problem_t *gs_problem_in_db  = (gs_problem_t *) CALLOC(1, sizeof(gs_problem_t));
    if(!gs_problem_in_db) {
      ERRPRINTF("couldn't malloc space for problem to compare\n");
      mysql_free_result(res);
      return -1;
    }

    row = mysql_fetch_row(res);

    if(gs_decode_problem(row[0], gs_problem_in_db) < 0) {
      mysql_free_result(res);
      ERRPRINTF("decoding problem from database failed\n");
      return -1;
    }

    mysql_free_result(res);

    new_prot = gs_problem_prototype(gs_problem);
    db_prot = gs_problem_prototype(gs_problem_in_db);

    comparison_result = strcmp(new_prot, db_prot);

    gs_free_problem(gs_problem_in_db);
    free(new_prot);
    free(db_prot);

    if(comparison_result != 0) {
      gs_server_t **server_list = NULL;
      int server_match, count, i;

      /* this problem exists, but is different than the problem being
       * registered.  if this is the only server that has the problem
       * then allow the new version to be registered.
       */
      count = gs_get_server_list(gs_agent, gs_problem, NULL, &server_list,
                 &count);

      server_match = 0;

      if(server_list) {
        char srv_cid[CID_LEN];

        proxy_str_to_cid(srv_cid, cid_string);

        for(i = 0; i < count; i++) {
          if(!memcmp(srv_cid, server_list[i]->componentid, CID_LEN))
            server_match = 1;

          gs_server_free(server_list[i]);
        }
        free(server_list);
      }

      /* if only one server has the problem and it matches the
       * current server name, then delete the old problem and
       * allow registration of the new version of the problem.
       */
      if((count == 1) && server_match &&
          (gs_delete_problem(gs_agent, gs_problem->name, cid_string) >= 0) &&
          (gs_insert_problem(gs_problem) >= 0))
      {
        LOGPRINTF("Successfully replaced old version of problem %s\n",
           gs_problem->name);
      }
      else {
        ERRPRINTF("Failed to replace old version of problem '%s'.\n",
          gs_problem->name);
        gs_delete_problem(gs_agent, gs_problem->name, cid_string);
        return -1;
      }
    }
  }

  /* 
   * Associate the problem with the server.
   */
  sql = dstring_sprintf("INSERT INTO problem_server (problemname,componentid,perf_model_expr) VALUES ('%s','%s','%s');",
       gs_problem->name, cid_string, "-1");
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR inserting problem association: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  return 0;
}

int
gs_delete_problem(gs_agent_t * gs_agent, char * probname,
                  char *cid_string)
{
  char *sql;

  if(gs_delete_problem_if_last_mapping(probname) < 0)
    ERRPRINTF("Warning: failed to delete last problem mapping (if needed)\n");

  /* 
   * delete association
   */
  sql = dstring_sprintf("DELETE FROM problem_server WHERE componentid='%s' AND problemname='%s';",
       cid_string, probname);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR deleting problem association: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  return 0;
}

int
gs_update_perf_expr(char *srv_cid, char *probname, char *expr)
{
  char *sql;

  sql = dstring_sprintf("UPDATE problem_server SET perf_model_expr='%s' WHERE componentid='%s' AND problemname='%s';",
       expr, srv_cid, probname);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR updating performance expression: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  return 0;
}

int
gs_update_ping_list(gs_agent_t * gs_agent, char *cid, char *pings)
{
  char *sql;

  sql = dstring_sprintf("UPDATE servers SET server_pings='%s' WHERE componentid='%s';",
        pings, cid);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR updating server workload: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  return 0;
}

int
gs_update_workload(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  int now;
  char *sql, cid_string[2 * CID_LEN + 1];

  proxy_cid_to_str(cid_string, gs_server->componentid);

  now = time(0);
  sql = dstring_sprintf("UPDATE servers SET workload='%d', nproblems='%d', lastupdate='%ld' WHERE componentid='%s';",
       gs_server->workload, gs_server->nproblems, now, cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR updating server workload: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  SENSORPRINTF("WORK %s %d\n", cid_string, gs_server->workload);

  return 0;
}

int
gs_server_exists(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  char *sql, cid_string[2 * CID_LEN + 1];
  int nrows = 0;
  MYSQL_RES *res;

  proxy_cid_to_str(cid_string, gs_server->componentid);

  sql = dstring_sprintf("SELECT 1 FROM servers WHERE componentid='%s';",
                       cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR checking server existence: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  mysql_free_result(res);

  if(nrows > 0)
    return 1;

  return 0;
}

void
gs_server_expiration(void **args)
{
  gs_agent_t *agent;
  int timeout;

  agent = (gs_agent_t *) args[0];
  timeout = *((int *) args[1]);

  gs_server_expire(agent, timeout);
}

void
gs_server_expiration_pre(void **args)
{
  gs_agent_t *agent;
  int timeout;

  agent = (gs_agent_t *) args[0];
  timeout = *((int *) args[1]);
  if(gs_storage_init(agent) < 0 ) {
    ERRPRINTF("Error connecting to database manager.\n");
    return;
  }
  gs_server_expire(agent, timeout);
}

void
gs_server_expiration_post(void **args)
{
  gs_agent_t *agent;
  agent = (gs_agent_t *) args[0];
  gs_storage_finalize(agent);
}

int
gs_server_expire(gs_agent_t * gs_agent, int timeout)
{
  int now, nrows;
  char *sql;
  MYSQL_RES *res;
  MYSQL_ROW row;

  now = time(0);
  sql = dstring_sprintf("SELECT componentid FROM servers WHERE lastupdate < '%ld';",
                        now - timeout);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR selecting servers to expire: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);
  
  if(nrows > 0) {
    gs_server_t tmpsrv;

    /* just in case */
    memset(&tmpsrv, 0, sizeof(tmpsrv));

    while((row = mysql_fetch_row(res))) {
      ERRPRINTF("expiring %s\n", row[0]);

      proxy_str_to_cid(tmpsrv.componentid, row[0]);
      if(gs_delete_server(gs_agent, &tmpsrv) < 0)
        ERRPRINTF("Failed to delete server '%s'\n", row[0]);
    }
  }

  mysql_free_result(res);

  return 0;
}

int
gs_get_server_list(gs_agent_t * gs_agent, gs_problem_t * gs_problem,
                   char *client_criteria, gs_server_t *** servers, int *count)
{
  int i, nrows;
  char *sql;
  gs_server_t **server_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  /* 
   * fill in remaining problem information.
   */
  sql = dstring_sprintf("SELECT encoding FROM problems WHERE problemname='%s';",
                       gs_problem->name);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting problem encodings: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  if(nrows > 0) {
    row = mysql_fetch_row(res);

    free(gs_problem->name);
    gs_problem->name = NULL;

    if(gs_decode_problem(row[0], gs_problem) < 0) {
      ERRPRINTF("decoding problem failed.\n");
      mysql_free_result(res);
      return -1;
    }
  }

  mysql_free_result(res);

  if(client_criteria == NULL) {
    /* 
     * get servers associated with this problem.
     */
    sql = dstring_sprintf("SELECT servers.*,problem_server.perf_model_expr FROM problem_server JOIN servers ON problem_server.componentid = servers.componentid WHERE problemname='%s';",
         gs_problem->name);
  }
  else {                        /* we need to consider server attributes */
    char *critq, *gs_parse_client_criteria(char *, int);

    critq = gs_parse_client_criteria(client_criteria, GS_CRIT_PARSE_MYSQL);
    sql = dstring_sprintf(critq, gs_problem->name);
    FREE(critq);
  }
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting servers associated with problem '%s': %s\n", 
        gs_problem->name, mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    (*servers) = NULL;
    return -1;
  }

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));
  if(server_list == NULL) {
    ERRPRINTF("malloc error\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0;i<nrows;i++) {
    row = mysql_fetch_row(res);

    server_list[i] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));

    server_list[i]->hostname = strdup(row[0]);
    proxy_str_to_ip(&(server_list[i]->ipaddress), row[1]);
    server_list[i]->port = atoi(row[2]);
    proxy_str_to_ip(&(server_list[i]->proxyip), row[3]);
    server_list[i]->proxyport = atoi(row[4]);
    proxy_str_to_cid(server_list[i]->componentid, row[5]);
    server_list[i]->arch = strdup(row[6]);
    server_list[i]->data_format = atoi(row[7]);
    server_list[i]->kflops = atoi(row[8]);
    server_list[i]->workload = atoi(row[9]);
    server_list[i]->ncpu = atoi(row[10]);
    server_list[i]->status = atoi(row[11]);
    server_list[i]->availcpu = atof(row[12]);
    server_list[i]->availmem = atof(row[13]);
    server_list[i]->nproblems = atoi(row[14]);
    server_list[i]->agenthost = strdup(row[15]);
    server_list[i]->agentport = atoi(row[16]);
    server_list[i]->smart = atoi(row[17]);
    server_list[i]->last_update = atol(row[18]);

    /* 
     * fill in server attribuites
     */
    server_list[i]->sa_list = NULL;
    gs_decode_infolist(row[20], &(server_list[i]->sa_list));
    server_list[i]->server_pings = strdup(row[21]);
    server_list[i]->perf_expr = strdup(row[22]);
  }

  mysql_free_result(res);

  *servers = server_list;
  return *count;
}
 
int
task_start_compare_function(const void *p1, const void *p2)
{
  gs_htm_task *s1, *s2;

  if(!p1 || !p2) return 0;

  s1 = *((gs_htm_task **) p1);
  s2 = *((gs_htm_task **) p2);

  if(s1->start > s2->start)
    return 1;
  if(s1->start < s2->start)
    return -1;

  return 0;
}

int
gs_get_tasks_for_server(char *cid_string, gs_htm_task ***tasks, 
  int *count, int sync)
{
  int i, nrows;
  char *sql;
  gs_htm_task **task_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  *count = -1;

  sql = dstring_sprintf("select * from (select * from tasks,completed_tasks where tasks.t_taskid=completed_tasks.c_taskid and tasks.t_componentid=completed_tasks.c_componentid union select * from tasks,completed_tasks where completed_tasks.c_taskid='-1' and tasks.t_taskid not in (select tasks.t_taskid from tasks,completed_tasks where tasks.t_taskid=completed_tasks.c_taskid and tasks.t_componentid=completed_tasks.c_componentid) union select * from tasks,completed_tasks where tasks.t_taskid='-1' and completed_tasks.c_taskid not in (select tasks.t_taskid from tasks,completed_tasks where tasks.t_taskid=completed_tasks.c_taskid and tasks.t_componentid=completed_tasks.c_componentid)) as foo where t_componentid='%s' or c_componentid='%s';", cid_string, cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting tasks: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;
  task_list = (gs_htm_task **) CALLOC(nrows, sizeof(gs_htm_task *));

  if(task_list == NULL) {
    ERRPRINTF("malloc error\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0;i<nrows;i++) {
    row = mysql_fetch_row(res);

    task_list[i] = (gs_htm_task *) CALLOC(1, sizeof(gs_htm_task));

    if(!task_list[i]) {
      ERRPRINTF("malloc error\n");
      mysql_free_result(res);
      return -1;
    }

    if(row[1] && row[10] && strcmp(row[1], "-1") && strcmp(row[10], "-1")) {
      /* entry exists in both tasks and completed_tasks */

      strcpy(task_list[i]->id, row[1]);
      /* skip row[2] which is agent_taskid */
      if(sync) {
        task_list[i]->start = MIN(atof(row[3]), atof(row[12]));
        task_list[i]->end = MAX(atof(row[6]), atof(row[15]));
        task_list[i]->duration = task_list[i]->end - task_list[i]->start;
      }
      else {
        task_list[i]->start = atof(row[3]);
        task_list[i]->end = atof(row[6]);
        task_list[i]->duration = atof(row[4]);
      }
      task_list[i]->remaining = atof(row[14]);
      task_list[i]->active = atoi(row[7]);
      task_list[i]->finished = atoi(row[17]);
      task_list[i]->next = NULL;
    }
    else if(row[1] && strcmp(row[1], "-1")) {
      /* entry exists in only in tasks */

      strcpy(task_list[i]->id, row[1]);
      /* skip row[2] which is agent_taskid */
      task_list[i]->start = atof(row[3]);
      task_list[i]->duration = atof(row[4]);
      task_list[i]->remaining = atof(row[5]);
      task_list[i]->end = atof(row[6]);
      task_list[i]->active = atoi(row[7]);
      task_list[i]->finished = atoi(row[8]);
      task_list[i]->next = NULL;
    }
    else if(row[10] && strcmp(row[10], "-1")) {
      /* entry exists in only in completed_tasks */

      strcpy(task_list[i]->id, row[10]);
      /* skip row[11] which is agent_taskid */
      task_list[i]->start = atof(row[12]);
      task_list[i]->duration = atof(row[13]);
      task_list[i]->remaining = atof(row[14]);
      task_list[i]->end = atof(row[15]);
      task_list[i]->active = atoi(row[16]);
      task_list[i]->finished = atoi(row[17]);
      task_list[i]->next = NULL;
    }
    else {
      /* should not hit this case */
      ERRPRINTF("Unexpected result from query.\n");
      mysql_free_result(res);
      return -1;
    }

    /* make sure duration isn't some weird value */
    if(task_list[i]->duration <= 0.0)
      task_list[i]->duration = 0.001;
  }

  qsort(task_list, nrows, sizeof(gs_htm_task *), task_start_compare_function);

  mysql_free_result(res);
  *tasks = task_list;
  return 0;
}

int 
gs_get_server_ping_list(gs_agent_t * gs_agent, gs_server_t *** servers, char *cid, int *count)
{
  int i, nrows;
  char *sql;
  gs_server_t **server_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  sql = dstring_sprintf("SELECT * FROM servers WHERE componentid<'%s' AND smart='1';", cid);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting servers ping list: %s\n",
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

  if(server_list == NULL) {
    ERRPRINTF("malloc error\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0; i<nrows; i++) {
    row = mysql_fetch_row(res);

    server_list[i] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[i]) {
      ERRPRINTF("malloc error\n");
      mysql_free_result(res);
      return -1;
    }

    server_list[i]->hostname = strdup(row[0]);
    proxy_str_to_ip(&(server_list[i]->ipaddress), row[1]);
    server_list[i]->port = atoi(row[2]);
    proxy_str_to_ip(&(server_list[i]->proxyip), row[3]);
    server_list[i]->proxyport = atoi(row[4]);
    proxy_str_to_cid(server_list[i]->componentid, row[5]);
    server_list[i]->arch = strdup(row[6]);
    server_list[i]->data_format = atoi(row[7]);
    server_list[i]->kflops = atoi(row[8]);
    server_list[i]->workload = atoi(row[9]);
    server_list[i]->ncpu = atoi(row[10]);
    server_list[i]->status = atoi(row[11]);
    server_list[i]->availcpu = atof(row[12]);
    server_list[i]->availmem = atof(row[13]);
    server_list[i]->nproblems = atoi(row[14]);
    server_list[i]->agenthost = strdup(row[15]);
    server_list[i]->agentport = atoi(row[16]);
    server_list[i]->smart = atoi(row[17]);
    server_list[i]->last_update = atol(row[18]);
  }

  mysql_free_result(res);
  *servers = server_list;

  return *count;
}

int
gs_get_all_smart_servers(gs_agent_t * gs_agent, gs_server_t *** servers, int *count)
{
  int i, nrows;
  char *sql;
  gs_server_t **server_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  sql = dstring_sprintf("SELECT * FROM servers WHERE smart='1';");
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting all smart servers: %s\n",
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  } 

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

  if(server_list == NULL) {
    ERRPRINTF("malloc error\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0; i<nrows; i++) {
    row = mysql_fetch_row(res);

    server_list[i] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[i]) {
      ERRPRINTF("malloc error\n");
      mysql_free_result(res);
      return -1;
    }

    server_list[i]->hostname = strdup(row[0]);
    proxy_str_to_ip(&(server_list[i]->ipaddress), row[1]);
    server_list[i]->port = atoi(row[2]);
    proxy_str_to_ip(&(server_list[i]->proxyip), row[3]);
    server_list[i]->proxyport = atoi(row[4]);
    proxy_str_to_cid(server_list[i]->componentid, row[5]);
    server_list[i]->arch = strdup(row[6]);
    server_list[i]->data_format = atoi(row[7]);
    server_list[i]->kflops = atoi(row[8]);
    server_list[i]->workload = atoi(row[9]);
    server_list[i]->ncpu = atoi(row[10]);
    server_list[i]->status = atoi(row[11]);
    server_list[i]->availcpu = atof(row[12]);
    server_list[i]->availmem = atof(row[13]);
    server_list[i]->nproblems = atoi(row[14]);
    server_list[i]->agenthost = strdup(row[15]);
    server_list[i]->agentport = atoi(row[16]);
    server_list[i]->smart = atoi(row[17]);
    server_list[i]->last_update = atol(row[18]);
    /* skip 19 - addedat */
    /* skip 20 - infolist */
    server_list[i]->server_pings = strdup(row[21]);
  }

  mysql_free_result(res);
  *servers = server_list;

  return *count;
}

int
gs_get_all_servers(gs_agent_t * gs_agent, gs_server_t *** servers, int *count)
{
  int i, nrows;
  char *sql;
  gs_server_t **server_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  sql = dstring_sprintf("SELECT * FROM servers WHERE 1;");
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting all known servers: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

  if(server_list == NULL) {
    ERRPRINTF("malloc error\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0; i<nrows; i++) {
    row = mysql_fetch_row(res);

    server_list[i] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[i]) {
      ERRPRINTF("malloc error\n");
      mysql_free_result(res);
      return -1;
    }

    server_list[i]->hostname = strdup(row[0]);
    proxy_str_to_ip(&(server_list[i]->ipaddress), row[1]);
    server_list[i]->port = atoi(row[2]);
    proxy_str_to_ip(&(server_list[i]->proxyip), row[3]);
    server_list[i]->proxyport = atoi(row[4]);
    proxy_str_to_cid(server_list[i]->componentid, row[5]);
    server_list[i]->arch = strdup(row[6]);
    server_list[i]->data_format = atoi(row[7]);
    server_list[i]->kflops = atoi(row[8]);
    server_list[i]->workload = atoi(row[9]);
    server_list[i]->ncpu = atoi(row[10]);
    server_list[i]->status = atoi(row[11]);
    server_list[i]->availcpu = atof(row[12]);
    server_list[i]->availmem = atof(row[13]);
    server_list[i]->nproblems = atoi(row[14]);
    server_list[i]->agenthost = strdup(row[15]);
    server_list[i]->agentport = atoi(row[16]);
    server_list[i]->smart = atoi(row[17]);
    server_list[i]->last_update = atol(row[18]);
  }

  mysql_free_result(res);
  *servers = server_list;
  return *count;
}

int
gs_get_problem_list(gs_agent_t * gs_agent, gs_server_t * gs_server,
                    gs_problem_t *** problems, int *count)
{
  int i, nrows;
  char *sql, cid_string[2 * CID_LEN + 1];
  gs_problem_t **problem_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  proxy_cid_to_str(cid_string, gs_server->componentid);
  sql = dstring_sprintf("SELECT problems.encoding FROM problem_server JOIN problems ON problem_server.problemname = problems.problemname WHERE componentid='%s';",
       cid_string);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting all problems: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;

  problem_list = (gs_problem_t **) CALLOC(nrows, sizeof(gs_problem_t *));
  if(problem_list == NULL) {
    ERRPRINTF("malloc error in gs_get_problem_list\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0; i < nrows; i++) {
    row = mysql_fetch_row(res);

    problem_list[i] = (gs_problem_t *) CALLOC(1, sizeof(gs_problem_t));
    gs_decode_problem(row[0], problem_list[i]);
  }

  mysql_free_result(res);

  *problems = problem_list;

  return *count;
}

int
gs_get_all_problems(gs_agent_t * gs_agent, gs_problem_t *** problems, int *count)
{
  return gs_get_problem_info(gs_agent, problems, count, NULL);
}

int
gs_get_problem_info(gs_agent_t * gs_agent, gs_problem_t *** problems, int *count,
  char *name)
{
  int i, nrows;
  char *sql;
  gs_problem_t **problem_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  if(name)
    sql = dstring_sprintf("SELECT encoding FROM problems WHERE problemname='%s';", name);
  else
    sql = dstring_sprintf("SELECT encoding FROM problems WHERE 1;");

  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting list of problems: %s\n", 
        mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;
  problem_list = (gs_problem_t **) CALLOC(nrows, sizeof(gs_problem_t *));
  if(problem_list == NULL) {
    ERRPRINTF("malloc error in gs_get_problem_list\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0; i < nrows; i++) {
    row = mysql_fetch_row(res);

    problem_list[i] = (gs_problem_t *) CALLOC(1, sizeof(gs_problem_t));
    gs_decode_problem(row[0], problem_list[i]);
  }

  mysql_free_result(res);
  *problems = problem_list;

  return *count;
}

int
gs_create_criteria_table(char *name, char *description, char *firstValue)
{
  char *sql, *tables_to_lock[] = {"criteria", "problems"};
  int nrows;
  MYSQL_RES *res;

  if(gs_mysql_lock_tables(tables_to_lock, 
       sizeof(tables_to_lock)/sizeof(*tables_to_lock)) < 0)
    ERRPRINTF("Warning: table lock failed\n");

  /* 
   * Check if criteria is already in db
   */
  sql = dstring_sprintf("SELECT 1 FROM criteria WHERE critname = '%s';", name);
  if(mysql_query(&mysql_conn, sql)) {
    gs_mysql_unlock_tables();
    ERRPRINTF("SQL ERROR getting criteria: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  mysql_free_result(res);

  if(nrows > 0) {
    gs_mysql_unlock_tables();
    return 0;
  }

  sql = dstring_sprintf("CREATE TABLE %s (value VARCHAR(128) NOT NULL, componentid VARCHAR(128) NOT NULL, UNIQUE(componentid));",
       name);
  if(mysql_query(&mysql_conn, sql)) {
    gs_mysql_unlock_tables();
    ERRPRINTF("SQL ERROR creating criteria table: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  sql = dstring_sprintf("INSERT INTO criteria (critname,description) VALUES ('%s','%s');", 
     name, description);
  if(mysql_query(&mysql_conn, sql)) {
    gs_mysql_unlock_tables();
    ERRPRINTF("SQL ERROR inserting criteria: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }

  gs_mysql_unlock_tables();
  free(sql);

  return 0;
}

int 
gs_get_server(gs_agent_t *gs_agent, char *name, gs_server_t *server) 
{
  char *sql;
  int nrows;
  MYSQL_RES *res;
  MYSQL_ROW row;

  sql = dstring_sprintf("SELECT * FROM servers WHERE hostname='%s' LIMIT 1;",
      name);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting server info: %s\n", mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  if(nrows == 0) {
    mysql_free_result(res);
    return -1;
  }

  row = mysql_fetch_row(res);

  /* fill in structure */
  server->hostname = strdup(row[0]);
  proxy_str_to_ip(&(server->ipaddress), row[1]);
  server->port = atoi(row[2]);
  proxy_str_to_ip(&(server->proxyip), row[3]);
  server->proxyport = atoi(row[4]);
  proxy_str_to_cid(server->componentid, row[5]);
  server->arch = strdup(row[6]);
  server->data_format = atoi(row[7]);
  server->kflops = atoi(row[8]);
  server->workload = atoi(row[9]);
  server->ncpu = atoi(row[10]);
  server->status = atoi(row[11]);
  server->availcpu = atof(row[12]);
  server->availmem = atof(row[13]);
  server->nproblems = atoi(row[14]);
  server->agenthost = strdup(row[15]);
  server->agentport = atoi(row[16]);
  server->smart = atol(row[17]);
  server->last_update = atol(row[18]);

  /* 
   * fill in server attribuites
   */
  server->sa_list = NULL;
  gs_decode_infolist(row[20], &(server->sa_list));

  mysql_free_result(res);

  return 0;
}

int
gs_get_all_servers_by_hostname(gs_agent_t * gs_agent, char *hostname,
  gs_server_t *** servers, int *count)
{
  int i, nrows;
  char *sql;
  gs_server_t **server_list;
  MYSQL_RES *res;
  MYSQL_ROW row;

  *count = -1;

  sql = dstring_sprintf("SELECT * FROM servers WHERE hostname='%s';", 
    hostname);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting all servers by name: %s\n", 
      mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

  if(server_list == NULL) {
    ERRPRINTF("malloc error\n");
    mysql_free_result(res);
    return -1;
  }

  for(i=0;i<nrows;i++) {
    row = mysql_fetch_row(res);

    server_list[i] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[i]) {
      ERRPRINTF("malloc error\n");
      mysql_free_result(res);
      return -1;
    }

    server_list[i]->hostname = strdup(row[0]);
    proxy_str_to_ip(&(server_list[i]->ipaddress), row[1]);
    server_list[i]->port = atoi(row[2]);
    proxy_str_to_ip(&(server_list[i]->proxyip), row[3]);
    server_list[i]->proxyport = atoi(row[4]);
    proxy_str_to_cid(server_list[i]->componentid, row[5]);
    server_list[i]->arch = strdup(row[6]);
    server_list[i]->data_format = atoi(row[7]);
    server_list[i]->kflops = atoi(row[8]);
    server_list[i]->workload = atoi(row[9]);
    server_list[i]->ncpu = atoi(row[10]);
    server_list[i]->status = atoi(row[11]);
    server_list[i]->availcpu = atof(row[12]);
    server_list[i]->availmem = atof(row[13]);
    server_list[i]->nproblems = atoi(row[14]);
    server_list[i]->agenthost = strdup(row[15]);
    server_list[i]->agentport = atoi(row[16]);
    server_list[i]->last_update = atol(row[17]);
  }

  mysql_free_result(res);
  *servers = server_list;
  return *count;
}

int
gs_get_task_by_agent_taskid(int agent_taskid, gs_htm_task *task)
{
  char *sql;
  int nrows;
  MYSQL_RES *res;
  MYSQL_ROW row;

  sql = dstring_sprintf("SELECT * FROM tasks WHERE t_agent_taskid='%d' LIMIT 1;", 
    agent_taskid);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting task by taskid: %s\n", 
      mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  if(nrows == 0) {
    mysql_free_result(res);
    return -1;
  }

  row = mysql_fetch_row(res);

  /* skip row[0] -- component id */
  strcpy(task->id, row[1]);
  task->agent_taskid = atof(row[2]);
  task->start = atof(row[3]);
  task->duration = atof(row[4]);
  task->remaining = atof(row[5]);
  task->end = atof(row[6]);
  task->active = atoi(row[7]);
  task->finished = atoi(row[8]);
  task->next = NULL;

  mysql_free_result(res);

  return 0;
}

int 
gs_get_server_by_cid(gs_agent_t * gs_agent, char *cid, gs_server_t *server) 
{
  char *sql;
  int nrows;
  MYSQL_RES *res;
  MYSQL_ROW row;

  sql = dstring_sprintf("SELECT * FROM servers WHERE componentid='%s' LIMIT 1;",
      cid);
  if(mysql_query(&mysql_conn, sql)) {
    ERRPRINTF("SQL ERROR getting server by component id: %s\n", 
       mysql_error(&mysql_conn));
    ERRPRINTF("The query was: %s\n", sql);
    free(sql);
    return -1;
  }
  free(sql);

  res = mysql_store_result(&mysql_conn);
  nrows = mysql_affected_rows(&mysql_conn);

  if(nrows == 0) {
    mysql_free_result(res);
    return -1;
  }

  row = mysql_fetch_row(res);

  /* fill in structure */

  server->hostname = strdup(row[0]);
  proxy_str_to_ip(&(server->ipaddress), row[1]);
  server->port = atoi(row[2]);
  proxy_str_to_ip(&(server->proxyip), row[3]);
  server->proxyport = atoi(row[4]);
  proxy_str_to_cid(server->componentid, row[5]);
  server->arch = strdup(row[6]);
  server->data_format = atoi(row[7]);
  server->kflops = atoi(row[8]);
  server->workload = atoi(row[9]);
  server->ncpu = atoi(row[10]);
  server->status = atoi(row[11]);
  server->availcpu = atof(row[12]);
  server->availmem = atof(row[13]);
  server->nproblems = atoi(row[14]);
  server->agenthost = strdup(row[15]);
  server->agentport = atoi(row[16]);
  server->smart = atol(row[17]);
  server->last_update = atol(row[18]);

  /* 
   * fill in server attribuites
   */
  server->sa_list = NULL;
  gs_decode_infolist(row[20], &(server->sa_list));

  mysql_free_result(res);

  return 0;
}