gs_storage_sqlite.c

Go to the documentation of this file.

/* 
 * @file
 *
 * This implements the interface defined in gs_storage.h for
 * the sqlite library.  Rather than communicating to SQLite 
 * directly, these functions use a unix domain socket to talk
 * to the database manager process.
 */
/* $Id: gs_storage_sqlite.c,v 1.105 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 "utility.h"
#include "server.h"
#include "problem.h"
#include "agent.h"
#include "comm_basics.h"
#include "comm_encode.h"
#include "gs_storage_sql_tables.h"
#include "sqlite3.h"


#define GS_QUERY_RETRIES 120

#define FREE_TABLE(x) do { \
    if(x) { \
      sqlite3_free_table(x); \
      x = NULL; \
    } \
  } while(0);

extern int sqliteIsNumber(char *);
int gs_create_criteria_table(char *name, char *description, char *firstValue);

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

int
  gs_add_single_problem(gs_agent_t * gs_agent, gs_problem_t * gs_problem,
      char *cid_string);

struct sockaddr_un GS_SQL_SOCKADDR;

extern sqlite3 *global_db;

int
gs_init_tables(sqlite3 *db)
{
  char **table, *dbErrMsg;
  int i, err, nr, nc;
  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};

  for(i=0; cmds[i]; i++) {
    /*
     * Initialize the database.  Ingnore failure that could result
     * from attempting to re-initialize the database
     */
    err = sqlite3_get_table(db, cmds[i], &table, &nr, &nc, &dbErrMsg);
    if(err != SQLITE_OK && err != SQLITE_CONSTRAINT && err != SQLITE_ERROR) {
      ERRPRINTF("Error initializing database: %d: %s\n", err, dbErrMsg);
      return -1;
    }

    FREE_TABLE(table);
  }

  return 0;
}

int 
gs_init_db(sqlite3 **db)
{
  int err;

  unlink(GRIDSOLVE_SQLITE_DB);

  /*
   * open database
   */
  err = sqlite3_open(GRIDSOLVE_SQLITE_DB, db);
  if( err != SQLITE_OK ) {
    ERRPRINTF("Error connecting to database: %s\n", sqlite3_errmsg(*db));
    return -1;
  }

  if(gs_init_tables(*db) < 0) {
    ERRPRINTF("Error initializing database tables\n");
    return -1;
  }
  
  return 0;
}

int
gs_random_sleep(long lo_usec, long hi_usec)
{
  struct timeval tv;

  gettimeofday(&tv, NULL);

  srand48(tv.tv_usec);

  tv.tv_sec = 0;
  tv.tv_usec = lo_usec + (lrand48() % hi_usec);

  return select(0, NULL, NULL, NULL, &tv);
}

int
gs_db_mgr_run_query(sqlite3 *db, char *query, int sz[3], char ***tbl,
  char **dbErrMsg)
{
  int err, nrows, ncols, retval, continueTrying;
  char **table, *errMsg;
  double wait_time;

  retval = 0;
  *dbErrMsg = NULL;

  DBGPRINTF("About to run query: %s\n", query);

  continueTrying = GS_QUERY_RETRIES;

  wait_time = walltime();

  while(continueTrying) {
    err = sqlite3_get_table(db, query, &table, &nrows, &ncols, &errMsg);

    switch (err) {
      case SQLITE_BUSY:
        DBGPRINTF("[%d] SQLITE_BUSY: sleeping for a while...\n",
          (int)getpid());
        continueTrying--;
        gs_random_sleep(40000, 200000);
        break;
      case SQLITE_OK:
        continueTrying = 0;
        break;
      default:
        continueTrying = 0;
        break;
    }
  }

  wait_time = walltime() - wait_time;

  if(err == SQLITE_BUSY)
    ERRPRINTF("Number of retries exceeded (%d).. total time spent = %g\n",
        GS_QUERY_RETRIES, wait_time);

  if(err != SQLITE_OK) {
    if(errMsg != NULL) {
      ERRPRINTF("SQL Error %d: %s.\n", err, errMsg);
      *dbErrMsg = errMsg;
    }

    sz[0] = err * -1;
    sz[1] = strlen(errMsg);;
    sz[2] = sz[0];

    *tbl = NULL;

    retval = -1;
  }
  else {
    sz[0] = nrows;
    sz[1] = ncols;
    sz[2] = sqlite3_changes(db);

    *tbl = table;
  }

  return retval;
}

int
gs_sqlite_submit_query(char *sql, char ***table, int *nrows, int *ncols,
                       char **dbErrMsg)
{
  int sz[3];

  *nrows = 0;
  *ncols = 0;
  *table = NULL;

  if(!sql)
    return -1;

  if(gs_db_mgr_run_query(global_db, sql, sz, table, dbErrMsg) < 0) {
    ERRPRINTF("Query failed\n");
    return -1;
  }

  *nrows = sz[0];
  *ncols = sz[1];

  return sz[2];
}

int
gs_storage_init(gs_agent_t * gs_agent)
{
  int err;

  err = sqlite3_open(GRIDSOLVE_SQLITE_DB, &global_db);
  if( err != SQLITE_OK ) {
    ERRPRINTF("Error opening to database: %s\n",
        sqlite3_errmsg(global_db));
    return -1;
  }

  return 0;
}

int
gs_sqlite_begin_transaction()
{
  char *dbErrMsg = NULL, **table = NULL;
  int err, nrows, ncols;

  err = gs_sqlite_submit_query("begin transaction;", &table, 
    &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    ERRPRINTF("Error beginning transaction\n");
    return -1;
  }

  FREE_TABLE(table);

  return 0;
}

int
gs_sqlite_commit_transaction()
{
  char *dbErrMsg = NULL, **table = NULL;
  int err, nrows, ncols;

  err = gs_sqlite_submit_query("commit transaction;", &table,
    &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    ERRPRINTF("Error beginning transaction\n");
    return -1;
  }

  FREE_TABLE(table);

  return 0;
}

void
gs_storage_finalize(gs_agent_t * gs_agent)
{
  sqlite3_close(global_db);

  return;
}

int
gs_add_server(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  int err, now, nrows, ncols, i;
  char *sql, *dbErrMsg = NULL, cid_string[2 * CID_LEN + 1],
      server_dottedIP[20], proxy_dottedIP[20], **table, *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);

  now = time(0);
  sql =
      sqlite3_mprintf
      ("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 (%Q,%Q,\"%d\",%Q,\"%d\",%Q,%Q,\"%d\",\"%d\",\"%d\",\"%d\",\"%d\",\"%lf\",\"%lf\",\"%d\",%Q,\"%d\",\"%d\",\"%d\",\"%d\",%Q,%Q);",
       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, sa_str, sp_str);

  free(sa_str);

  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);

  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("SQL ERROR: %s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }  
    sqlite3_free(sql);
    return -1;
  }
  sqlite3_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';
    err = gs_create_criteria_table(attrs->type, "", attrs->value);
    if(err < 0) {
      ERRPRINTF("Error adding criteria table.\n");
      return -1;
    }
    if ( sqliteIsNumber(attrs->value) )
      sql =
        sqlite3_mprintf
        ("INSERT INTO %s (value,componentid) values (%s,%Q);",
         attrs->type, attrs->value, cid_string);
    else
      sql =
        sqlite3_mprintf
        ("INSERT INTO %s (value,componentid) values (%Q,%Q);",
         attrs->type, attrs->value, cid_string);
    DBGPRINTF("Submitting Attribute Query.\n");
    err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
    sqlite3_free(sql);
    if(err < 0) {
      ERRPRINTF("SQL Error: %s\n", dbErrMsg);
      if(dbErrMsg) sqlite3_free(dbErrMsg);
      return -1;
    }
    DBGPRINTF("Attribute added: %s.\n", attrs->type);
    attrs = attrs->next;
  }

  return 0;
}

int
gs_delete_problem_if_last_mapping(gs_agent_t *gs_agent, char *problemname)
{
  char *sql, *dbErrMsg = NULL, **table = NULL;
  int err, ncols, nrows;

  if(gs_sqlite_begin_transaction() < 0)
    ERRPRINTF("Warning: table lock failed\n");

  sql = sqlite3_mprintf("SELECT 1 FROM problem_server WHERE problemname=%Q;",
                       problemname);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);

  sqlite3_free(sql);

  FREE_TABLE(table);

  if(err < 0) {
    if ( dbErrMsg != NULL ) {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }

    gs_sqlite_commit_transaction();

    return -1;
  }

  if(nrows == 1) {
    sql = sqlite3_mprintf("DELETE FROM problems WHERE problemname=%Q;",
                       problemname);
    err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);

    sqlite3_free(sql);

    FREE_TABLE(table);

    if(err < 0) {
      if ( dbErrMsg != NULL ) {
        ERRPRINTF("%s\n", dbErrMsg);
        sqlite3_free(dbErrMsg);
      }

      gs_sqlite_commit_transaction();

      return -1;
    }
  }

  gs_sqlite_commit_transaction();

  return 0;
}

int
gs_delete_server_problems(gs_agent_t *gs_agent, char *cid_string)
{
  int err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;

  sql = sqlite3_mprintf("SELECT problemname FROM problem_server WHERE componentid=%Q;",
                       cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;

    if(gs_delete_problem_if_last_mapping(gs_agent, table[i]) < 0)
      ERRPRINTF("Warning: could not delete unreferenced problems (if needed)\n");
  }

  FREE_TABLE(table);

  sql = sqlite3_mprintf("DELETE FROM problem_server WHERE componentid=%Q;",
                       cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }

  FREE_TABLE(table);

  return 0;
}

int
gs_delete_server(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  int err, ncols, nrows;
  char *sql, *dbErrMsg = NULL, cid_string[2 * CID_LEN + 1], **table = NULL;
  gs_info_t *attrs, *tmp;

  proxy_cid_to_str(cid_string, gs_server->componentid);

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

  sql = sqlite3_mprintf("SELECT infolist FROM servers WHERE componentid=%Q;",
                       cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }

    FREE_TABLE(table);

    return -1;
  }

  attrs = NULL;
  err = gs_decode_infolist(table[1], &attrs);

  FREE_TABLE(table);

  while(attrs != NULL) {
    sql = sqlite3_mprintf("DELETE FROM %Q WHERE componentid=%Q;",
                         attrs->type, cid_string);
    err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
    sqlite3_free(sql);
    if(err < 0) {
      if ( dbErrMsg != NULL )
    sqlite3_free(dbErrMsg);
    }
    tmp = attrs;
    attrs = attrs->next;

    FREE_TABLE(table);

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

  /* DELETE SERVER */
  sql = sqlite3_mprintf("DELETE FROM servers WHERE componentid=%Q;",
                       cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);

  sqlite3_free(sql);

  FREE_TABLE(table);

  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }

  if(gs_delete_server_problems(gs_agent, 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)
{
  int err = 0, nrows = 0, ncols = 0;
  char *sql = 0, *dbErrMsg = 0, **table = 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 = sqlite3_mprintf("INSERT OR %s INTO %s VALUES (%Q,%Q,\"%d\",\"%lf\",\"%lf\",\"%lf\",\"%lf\",\"%d\",\"%d\");",
           conflict_res, tname, cid_string, taskid, agent_taskid, start, duration, remaining, end, active, finished);

  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }

  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)
{
  int err, ncols, nrows;
  char *sql, *dbErrMsg = NULL, **table = NULL;

  sql = sqlite3_mprintf("UPDATE tasks SET t_componentid=%Q, t_taskid=%Q, 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=%Q;",
           cid_string, new_taskid, agent_taskid, start, duration, remaining, end, active, finished, old_taskid);

  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);

  if(err == 0) {
    return -1;
  }

  return 0;
}

int
gs_insert_problem(gs_problem_t * gs_problem)
{
  int err = 0, nrows = 0, ncols = 0;
  char *sql = 0, *dbErrMsg = 0, **table = 0, *penc = 0;

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

  sql = sqlite3_mprintf("INSERT INTO problems (problemname,encoding) VALUES (%Q,%Q);",
         gs_problem->name, penc);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    free(penc);
    gs_sqlite_commit_transaction();
    return -1;
  }

  sqlite3_free(sql);
  free(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)
{
  int i;

  if(gs_sqlite_begin_transaction() < 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) {
      ERRPRINTF("Add problem failed.\n");
      gs_sqlite_commit_transaction();
      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_sqlite_commit_transaction();

  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)
{
  int err = 0, pid = 0, nrows = 0, ncols = 0;
  char *sql = 0, *dbErrMsg = 0, **table = 0, *penc = 0;

  DBGPRINTF("ADDING %s to %s\n", gs_problem->name, cid_string);
  /* 
   * encode the problem
   */
  gs_encode_problem(&penc, gs_problem);

  /* 
   * Check if problem already exists
   */
  sql = sqlite3_mprintf("SELECT encoding FROM problems WHERE problemname=%Q;",
                       gs_problem->name);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if(dbErrMsg != NULL)
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    ERRPRINTF("Failed to submit query '%s'\n", sql);
    free(penc);
    sqlite3_free(sql);
    return -1;
  }
  sqlite3_free(sql);
  if(nrows == 1) {
    pid = 1;
  }
  else {
    FREE_TABLE(table);
  }

  /* 
   * If problem is not in db, insert it
   */
  if(!pid) {
    if(gs_insert_problem(gs_problem) < 0) {
      free(penc);
      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");
      return -1;
    }

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

    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 {
        FREE_TABLE(table);
        ERRPRINTF("Failed to replace old version of problem '%s'.\n",
          gs_problem->name);
        gs_delete_problem(gs_agent, gs_problem->name, cid_string);
        free(penc);
        return -1;
      }
    }
  }

  free(penc);
  FREE_TABLE(table);

  /* 
   * Associate the problem with the server.
   */
  sql =
      sqlite3_mprintf
      ("INSERT INTO problem_server (problemname,componentid,perf_model_expr) VALUES (%Q,%Q,%Q);",
       gs_problem->name, cid_string, "-1");
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    /* 
     * If we violated a contraint, then the problem must already
     * be registered to this server.  Return success.
     */
    if((err * -1) == SQLITE_CONSTRAINT) {
      return 0;
    }
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    ERRPRINTF("failed to associate problem.. query = '%s'\n", sql);
    sqlite3_free(sql);
    return -1;
  }
  sqlite3_free(sql);

  return 0;
}

int
gs_delete_problem(gs_agent_t * gs_agent, char * probname,
                  char *cid_string)
{
  int err, nrows, ncols;
  char *sql, *dbErrMsg = NULL, **table = NULL;

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

  /* 
   * delete association
   */
  sql =
      sqlite3_mprintf
      ("DELETE FROM problem_server WHERE componentid=%Q AND problemname=%Q;",
       cid_string, probname);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  return 0;
}

int
gs_update_perf_expr(char *srv_cid, char *probname, char *expr)
{
  char *sql, *dbErrMsg = NULL, **table = NULL;
  int err, ncols, nrows;

  sql = sqlite3_mprintf("UPDATE problem_server SET perf_model_expr=%Q WHERE componentid=%Q AND problemname=%Q;",
       expr, srv_cid, probname);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);

  if(err == 0) {
    return -1;
  }

  return 0;
}

int
gs_update_ping_list(gs_agent_t * gs_agent, char *cid, char *pings)
{
  int now, err, ncols, nrows;
  char *sql, *dbErrMsg = NULL, **table = NULL;

  now = time(0);
  sql =
      sqlite3_mprintf("UPDATE servers SET server_pings=%Q WHERE componentid=%Q;",
        pings, cid);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);

  if(err == 0) {
    return -1;
  }

  return 0;
}

int
gs_update_workload(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  int now, err, ncols, nrows;
  char *sql, *dbErrMsg = NULL, cid_string[2 * CID_LEN + 1], **table = NULL;

  proxy_cid_to_str(cid_string, gs_server->componentid);

  now = time(0);
  sql =
      sqlite3_mprintf
      ("UPDATE servers SET workload=\"%d\", nproblems=\"%d\", lastupdate=\"%ld\" WHERE componentid=%Q;",
       gs_server->workload, gs_server->nproblems, now, cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);

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

  if(err == 0) {
    return -1;
  }

  return 0;
}

int
gs_server_exists(gs_agent_t * gs_agent, gs_server_t * gs_server)
{
  int err, nrows, ncols;
  char *sql, *dbErrMsg = NULL, **table = NULL, cid_string[2 * CID_LEN + 1];

  proxy_cid_to_str(cid_string, gs_server->componentid);

  sql = sqlite3_mprintf("SELECT 1 FROM servers WHERE componentid=%Q;",
                       cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }

  if(nrows > 0) {
    FREE_TABLE(table);
    return 1;
  }
  else {
    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;
  }
  DBGPRINTF("Connected to db manager.\n");
  gs_server_expire(agent, timeout);
}

void
gs_server_expiration_post(void **args)
{
  gs_agent_t *agent;
  agent = (gs_agent_t *) args[0];
  DBGPRINTF("Closing connection to db manager.\n");
  gs_storage_finalize(agent);
}

int
gs_server_expire(gs_agent_t * gs_agent, int timeout)
{
  int i, err, now, nrows, ncols;
  char *sql, *dbErrMsg = NULL, **table = NULL;

  now = time(0);
  sql = sqlite3_mprintf("SELECT componentid FROM servers WHERE lastupdate < \"%ld\";",
                        now - timeout);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);

  if(table != NULL) {
    gs_server_t tmpsrv;

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

    for(i = 1; i < (nrows + 1) * ncols; i++)
    {
      ERRPRINTF("expiring %s\n", table[i]);

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

    FREE_TABLE(table);
  }

  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 err, i, nrows, ncols, table_els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_server_t **server_list;

  /* 
   * fill in remaining problem information.
   */
  sql = sqlite3_mprintf("SELECT encoding FROM problems WHERE problemname=%Q;",
                       gs_problem->name);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);
  if(nrows > 0) {
    free(gs_problem->name);
    gs_problem->name = NULL;
    err = gs_decode_problem(table[1], gs_problem);
  }
  FREE_TABLE(table);

  if(client_criteria == NULL) {
    /* 
     * get servers associated with this problem.
     */
    sql =
        sqlite3_mprintf
        ("SELECT servers.*,problem_server.perf_model_expr FROM problem_server JOIN servers ON problem_server.componentid = servers.componentid WHERE problemname=%Q;",
         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_SQLITE);
    sql = sqlite3_mprintf(critq, gs_problem->name);
    FREE(critq);
  }
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s %d\n", dbErrMsg, nrows);
      sqlite3_free(dbErrMsg);
    }
    (*servers) = NULL;
    return -1;
  }
  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));
  if(server_list == NULL) {
    ERRPRINTF("malloc error\n");
    FREE_TABLE(table);
    return -1;
  }

  /* save number of table elements before nrows gets clobbered */
  table_els = (nrows + 1) * ncols - 1;

  for(; nrows > 0; nrows--) {
    /* fill in servers array */
    i = nrows * ncols;
    server_list[nrows-1] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    server_list[nrows - 1]->hostname = strdup(table[i]);
    proxy_str_to_ip(&(server_list[nrows - 1]->ipaddress), table[i + 1]);
    server_list[nrows - 1]->port = atoi(table[i + 2]);
    proxy_str_to_ip(&(server_list[nrows - 1]->proxyip), table[i + 3]);
    server_list[nrows - 1]->proxyport = atoi(table[i + 4]);
    proxy_str_to_cid(server_list[nrows - 1]->componentid, table[i + 5]);
    server_list[nrows - 1]->arch = strdup(table[i + 6]);
    server_list[nrows - 1]->data_format = atoi(table[i + 7]);
    server_list[nrows - 1]->kflops = atoi(table[i + 8]);
    server_list[nrows - 1]->workload = atoi(table[i + 9]);
    server_list[nrows - 1]->ncpu = atoi(table[i + 10]);
    server_list[nrows - 1]->status = atoi(table[i + 11]);
    server_list[nrows - 1]->availcpu = atof(table[i + 12]);
    server_list[nrows - 1]->availmem = atof(table[i + 13]);
    server_list[nrows - 1]->nproblems = atoi(table[i + 14]);
    server_list[nrows - 1]->agenthost = strdup(table[i + 15]);
    server_list[nrows - 1]->agentport = atoi(table[i + 16]);
    server_list[nrows - 1]->smart = atoi(table[i + 17]);
    server_list[nrows - 1]->last_update = atoi(table[i + 18]);

    /* skip 19 for addedat */

    /* 
     * fill in server attribuites
     */
    server_list[nrows - 1]->sa_list = NULL;

    gs_decode_infolist(table[i + 20], &(server_list[nrows - 1]->sa_list));

    server_list[nrows - 1]->my_ping_str = strdup(table[i+21]);
 
    server_list[nrows - 1]->perf_expr = strdup(table[i+22]);
  }

  FREE_TABLE(table);

  *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 err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_htm_task **task_list;

  *count = -1;

  sql = sqlite3_mprintf("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=%Q or c_componentid=%Q;", cid_string, cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  } 

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

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

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;

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

    if(!task_list[nrows-1]) {
      ERRPRINTF("malloc error\n");
      FREE_TABLE(table);
      return -1;
    }

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

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

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

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

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

  FREE_TABLE(table);

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

  *tasks = task_list;
  return 0;
}

int
OLD_gs_get_tasks_for_server(char *cid_string, gs_htm_task ***tasks, 
  int *count)
{
  int err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_htm_task **task_list;

  *count = -1;

  sql = sqlite3_mprintf("SELECT * FROM taskviewjoin WHERE componentid=%Q;",
    cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  } 

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

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

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;

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

    if(!task_list[nrows-1]) {
      ERRPRINTF("malloc error\n");
      FREE_TABLE(table);
      return -1;
    }

    /* skip table[i] which is component_id */
    strcpy(task_list[nrows - 1]->id, table[i+1]);
    /* skip table[i+2] which is agent_taskid */
    task_list[nrows - 1]->start = atof(table[i+3]);
    task_list[nrows - 1]->duration = atof(table[i+4]);
    task_list[nrows - 1]->remaining = atof(table[i+5]);
    task_list[nrows - 1]->end = atof(table[i+6]);
    task_list[nrows - 1]->active = atoi(table[i+7]);
    task_list[nrows - 1]->finished = atoi(table[i+8]);
    task_list[nrows - 1]->next = NULL;
  }

  FREE_TABLE(table);

  *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 err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_server_t **server_list;

  sql = sqlite3_mprintf("SELECT * FROM servers WHERE componentid<%Q AND smart=\"1\";", cid);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);
  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

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

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;
    server_list[nrows-1] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[nrows-1]) {
      ERRPRINTF("malloc error\n");
      FREE_TABLE(table);
      return -1;
    }

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

  FREE_TABLE(table);

  *servers = server_list;
  return *count;
}

int
gs_get_all_smart_servers(gs_agent_t * gs_agent, gs_server_t *** servers, int *count)
{
  int err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_server_t **server_list;

  sql = sqlite3_mprintf("SELECT * FROM servers WHERE smart=\"1\";");
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);
  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

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

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;
    server_list[nrows-1] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[nrows-1]) {
      ERRPRINTF("malloc error\n");
      FREE_TABLE(table);
      return -1;
    }

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

  FREE_TABLE(table);

  *servers = server_list;
  return *count;
}

int
gs_get_all_servers(gs_agent_t * gs_agent, gs_server_t *** servers, int *count)
{
  int err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_server_t **server_list;

  sql = sqlite3_mprintf("SELECT * FROM servers WHERE 1;");
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);
  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

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

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;
    server_list[nrows-1] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[nrows-1]) {
      ERRPRINTF("malloc error\n");
      FREE_TABLE(table);
      return -1;
    }

    server_list[nrows - 1]->hostname = strdup(table[i]);
    proxy_str_to_ip(&(server_list[nrows - 1]->ipaddress), table[i + 1]);
    server_list[nrows - 1]->port = atoi(table[i + 2]);
    proxy_str_to_ip(&(server_list[nrows - 1]->proxyip), table[i + 3]);
    server_list[nrows - 1]->proxyport = atoi(table[i + 4]);
    proxy_str_to_cid(server_list[nrows - 1]->componentid, table[i + 5]);
    server_list[nrows - 1]->arch = strdup(table[i + 6]);
    server_list[nrows - 1]->data_format = atoi(table[i + 7]);
    server_list[nrows - 1]->kflops = atoi(table[i + 8]);
    server_list[nrows - 1]->workload = atoi(table[i + 9]);
    server_list[nrows - 1]->ncpu = atoi(table[i + 10]);
    server_list[nrows - 1]->status = atoi(table[i + 11]);
    server_list[nrows - 1]->availcpu = atof(table[i + 12]);
    server_list[nrows - 1]->availmem = atof(table[i + 13]);
    server_list[nrows - 1]->nproblems = atoi(table[i + 14]);
    server_list[nrows - 1]->agenthost = strdup(table[i + 15]);
    server_list[nrows - 1]->agentport = atoi(table[i + 16]);
    server_list[nrows - 1]->smart = atoi(table[i + 17]);
    server_list[nrows - 1]->last_update = atoi(table[i + 18]);
#ifdef GS_SMART_GRIDSOLVE
   if(server_list[nrows - 1]->smart==1){
     server_list[nrows - 1]->my_ping_str = strdup(table[i + 21]);
   }
#endif
  }

  FREE_TABLE(table);

  *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 els, err, i, nrows, ncols;
  char *sql, *dbErrMsg = NULL, **table = NULL, cid_string[2 * CID_LEN + 1];
  gs_problem_t **problem_list;

  proxy_cid_to_str(cid_string, gs_server->componentid);
  sql =
      sqlite3_mprintf
      ("SELECT problems.encoding FROM problem_server JOIN problems ON problem_server.problemname = problems.problemname WHERE componentid=%Q;",
       cid_string);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);
  *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");
    FREE_TABLE(table);
    return -1;
  }

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;
    problem_list[nrows - 1] = (gs_problem_t *) CALLOC(1, sizeof(gs_problem_t));
    gs_decode_problem(table[i], problem_list[nrows - 1]);
  }

  FREE_TABLE(table);

  *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 err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_problem_t **problem_list;

  if(name)
    sql = sqlite3_mprintf("SELECT encoding FROM problems WHERE problemname=%Q;", name);
  else
    sql = sqlite3_mprintf("SELECT encoding FROM problems WHERE 1;");

  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);
  *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");
    FREE_TABLE(table);
    return -1;
  }

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;
    problem_list[nrows - 1] = (gs_problem_t *) CALLOC(1, sizeof(gs_problem_t));
    gs_decode_problem(table[i], problem_list[nrows - 1]);
  }

  FREE_TABLE(table);

  *problems = problem_list;

  return *count;
}

int
gs_create_criteria_table(char *name, char *description, char *firstValue)
{
  char *sql, *dbErrMsg = NULL, **table = NULL;
  int err, nrows, ncols;

  if(gs_sqlite_begin_transaction() < 0)
    ERRPRINTF("Warning: table lock failed\n");

  /* 
   * Check if criteria is already in db
   */
  sql = sqlite3_mprintf("SELECT 1 FROM criteria WHERE critname = %Q;", name);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    gs_sqlite_commit_transaction();
    return -1;
  }

  FREE_TABLE(table);

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

  /* 
   * Create table, if it's not already there.
   */
  if ( sqliteIsNumber(firstValue) )
  {
    sql =
      sqlite3_mprintf
      ("CREATE TABLE %s (value NUMERIC NOT NULL, componentid VARCHAR(128) NOT NULL, UNIQUE('componentid'));",
       name);
  } else
  {
    sql =
      sqlite3_mprintf
      ("CREATE TABLE %s (value VARCHAR(128) NOT NULL, componentid VARCHAR(128) NOT NULL, UNIQUE('componentid'));",
       name);
  }
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    gs_sqlite_commit_transaction();
    return -1;
  }

  sql =
    sqlite3_mprintf
    ("INSERT INTO criteria (critname,description) VALUES (%Q,%Q);", 
     name, description);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    gs_sqlite_commit_transaction();
    return -1;
  }

  gs_sqlite_commit_transaction();

  return 0;
}

int 
gs_get_server(gs_agent_t *gs_agent, char *name, gs_server_t *server) 
{
  char *sql, *dbErrMsg = NULL, **table = NULL;
  int err, nrows, ncols, i;

  sql = sqlite3_mprintf("SELECT * FROM servers WHERE hostname=%Q LIMIT 1;",
      name);
  if ( sql == NULL )
  {
    return -1;
  }
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if ( err < 0 )
  {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("Error from database: %s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  if ( nrows == 0 )
  {
    return -1;
  }

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

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

  FREE_TABLE(table);

  return 0;
}

int
gs_get_all_servers_by_hostname(gs_agent_t * gs_agent, char *hostname,
  gs_server_t *** servers, int *count)
{
  int err, i, nrows, ncols, els;
  char *sql, *dbErrMsg = NULL, **table = NULL;
  gs_server_t **server_list;

  *count = -1;

  sql = sqlite3_mprintf("SELECT * FROM servers WHERE hostname=%Q;", 
    hostname);
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  if(err < 0) {
    if ( dbErrMsg != NULL )
    {
      ERRPRINTF("%s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  sqlite3_free(sql);
  *count = nrows;
  server_list = (gs_server_t **) CALLOC(nrows, sizeof(gs_server_t *));

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

  /* save number of table elements before nrows gets clobbered */
  els = (nrows+1)*ncols;

  for(; nrows > 0; nrows--) {
    i = nrows * ncols;
    server_list[nrows-1] = (gs_server_t *) CALLOC(1, sizeof(gs_server_t));
    if(!server_list[nrows-1]) {
      ERRPRINTF("malloc error\n");
      FREE_TABLE(table);
      return -1;
    }

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

  FREE_TABLE(table);

  *servers = server_list;
  return *count;
}

int
gs_get_task_by_agent_taskid(int agent_taskid, gs_htm_task *task)
{
  char *sql, *dbErrMsg = NULL, **table = NULL;
  int err, nrows, ncols, i;

  sql = sqlite3_mprintf("SELECT * FROM tasks WHERE t_agent_taskid=\"%d\" LIMIT 1;", 
    agent_taskid);

  if ( sql == NULL ) {
    return -1;
  }

  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if ( err < 0 ) {
    if ( dbErrMsg != NULL ) {
      ERRPRINTF("Error from database: %s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }

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

  /* fill in structure */
  i = nrows * ncols;

  strcpy(task->id, table[i+1]);
  task->agent_taskid = atof(table[i+2]);
  task->start = atof(table[i+3]);
  task->duration = atof(table[i+4]);
  task->remaining = atof(table[i+5]);
  task->end = atof(table[i+6]);
  task->active = atoi(table[i+7]);
  task->finished = atoi(table[i+8]);
  task->next = NULL;

  FREE_TABLE(table);

  return 0;
}

int 
gs_get_server_by_cid(gs_agent_t * gs_agent, char *cid, gs_server_t *server) 
{
  char *sql, *dbErrMsg = NULL, **table = NULL;
  int err, nrows, ncols, i;

  sql = sqlite3_mprintf("SELECT * FROM servers WHERE componentid=%Q LIMIT 1;",
      cid);
  if ( sql == NULL )
  {
    return -1;
  }
  err = gs_sqlite_submit_query(sql, &table, &nrows, &ncols, &dbErrMsg);
  sqlite3_free(sql);
  if ( err < 0 )
  {
    if ( dbErrMsg != NULL )
    {
      DBGPRINTF("Error from database: %s\n", dbErrMsg);
      sqlite3_free(dbErrMsg);
    }
    return -1;
  }
  if ( nrows == 0 )
  {
    return -1;
  }
  /* fill in structure */
  i = nrows * ncols;
  server->hostname = strdup(table[i]);
  proxy_str_to_ip(&(server->ipaddress), table[i + 1]);
  server->port = atoi(table[i + 2]);
  proxy_str_to_ip(&(server->proxyip), table[i + 3]);
  server->proxyport = atoi(table[i + 4]);
  proxy_str_to_cid(server->componentid, table[i + 5]);
  server->arch = strdup(table[i + 6]);
  server->data_format = atoi(table[i + 7]);
  server->kflops = atoi(table[i + 8]);
  server->workload = atoi(table[i + 9]);
  server->ncpu = atoi(table[i + 10]);
  server->status = atoi(table[i + 11]);
  server->availcpu = atof(table[i + 12]);
  server->availmem = atof(table[i + 13]);
  server->nproblems = atoi(table[i + 14]);
  server->agenthost = strdup(table[i + 15]);
  server->agentport = atoi(table[i + 16]);
  server->smart = atoi(table[i + 17]);
  server->last_update = atoi(table[i + 18]);

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

  FREE_TABLE(table);

  return 0;
}