#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include "accesscache.h"
#include "blend.h"

static const char Userinfo[] = "user=postgres password=DnasisPro";

/* cachefilesデータベースに接続する */
int connectCacheDB()
{
  char conninfo[PATH_MAX_LEN + 1];

  sprintf(conninfo, "dbname=%s ", CACHE_DB);
  strcat(conninfo, Userinfo);
  if (Conn) {
    PQfinish(Conn);
    Conn = NULL;
  }
  Conn = PQconnectdb(conninfo);
  if (PQstatus(Conn) != CONNECTION_OK) {
    fprintf(stderr, "Connection to database %s is failed.\n", PQdb(Conn));
    fprintf(stderr, "%s", PQerrorMessage(Conn));
    PQfinish(Conn);
    Conn = NULL;
    return 0;
  }
  return 1;
}

/* データベース接続を終了する */
int disconnectCacheDB()
{
  if (Conn) {
    PQfinish(Conn);
    Conn = NULL;
    return 1;
  }
  return 0;
}

/* キャッシュファイルのパス名を作成                     */
/* pathはパス名を格納する領域。NULLの場合は新たに確保。 */
char *assembleCacheFileName(char *name, char *species, char *revision,
                            char *target, char *path, int length)
{
  if (!name || !species || !revision || !target) {
    fprintf(stderr, "assembleCacheFileName: invalid argument.\n");
    return NULL;
  }
  if (!path) {
    path = (char *)malloc(PATH_MAX_LEN + 1);
    if (!path) {
      fprintf(stderr, "assembleCacheFileName: memory allocation error.\n");
      return NULL;
    }
    onmemStrings++;
  } else {
    if (strlen(name) + strlen(species) + strlen(revision) + strlen(target)
        + strlen(CACHEDIR) + 10 > length) {
      fprintf(stderr, "assembleCacheFileName: copying area is too short.\n");
      return NULL;
    }
  }
  strcpy(path, CACHEDIR);
  strcat(path, name);
  strcat(path, "/");
  strcat(path, species);
  strcat(path, "/");
  strcat(path, revision);
  strcat(path, "/");
  strcat(path, target);
  strcat(path, ".cache");
  return path;
}

/*****************************************************************************/
/* データベースから指定条件にマッチしたEasyGraphTrackを得る                  */
/*   [戻り値] 成功: 条件にヒットしたEasyGraphの構造体へのポインタ            */
/*            エラー: NULL                                                   */
/*****************************************************************************/
EasyGraphTrack *findEasyGraphTrack(char *trackname,
                                   char *species,
                                   char *revision,
                                   char *target,
                                   char *date)
{
  EasyGraphTrack *egt;
  EasyGraph  *graph;
  char       sql[SQL_MAX_LEN + 1];
  int        nTuples;
  char       *filename;
  int        fieldpos;
  char       *maxstr;
  char       *minstr;

  /* EasyGraphTrack構造体の作成 */
  egt = (EasyGraphTrack *)malloc(sizeof(EasyGraphTrack));
  if (!egt) {
    fprintf(stderr, "findEasyGraphTrack: Could not alloc memory.\n");
    return NULL;
  }
  onmemEGraphTrackNum++;
  memset(egt, 0, sizeof(EasyGraphTrack));

  /* cachefiles DB を検索 */
  sprintf(sql, "SELECT * FROM %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\' AND target=\'%s\';",
          CACHE_TABLE, trackname, species, revision, target);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_TUPLES_OK) {
    fprintf(stderr, "[SQL] %s\nfindEasyGraphTrack: %s\n",
            sql, PQerrorMessage(Conn));
    PQclear(Results);
    free(egt); onmemEGraphTrackNum--; /* EasyGraphTrack */
    return NULL;
  }
  nTuples = PQntuples(Results);
  if (nTuples == 0) {
    PQclear(Results);
    free(egt); onmemEGraphTrackNum--; /* EasyGraphTrack */
    return NULL;
  }
  filename = PQgetvalue(Results, 0, PQfnumber(Results, "filename"));
  if (filename == NULL) {
    fprintf(stderr, "no cache file.\n");
    free(egt); onmemEGraphTrackNum--; /* EasyGraphTrack */
    return NULL;
  }

  /* EasyGraphTrackの内容を作成 */
  fieldpos = PQfnumber(Results, "name");
  if (fieldpos >= 0) strcpy(egt->name, PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "species");
  if (fieldpos >= 0) strcpy(egt->species, PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "revision");
  if (fieldpos >= 0) strcpy(egt->revision, PQgetvalue(Results, 0, fieldpos));
  
  fieldpos = PQfnumber(Results, "red");
  if (fieldpos >= 0) egt->color.red = atoi(PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "green");
  if (fieldpos >= 0) egt->color.green = atoi(PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "blue");
  if (fieldpos >= 0) egt->color.blue = atoi(PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "comment");
  if (fieldpos >= 0) strcpy(egt->comment, PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "desc_url");
  if (fieldpos >= 0) strcpy(egt->desc_url, PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "species_url");
  if (fieldpos >= 0) strcpy(egt->species_url, PQgetvalue(Results, 0, fieldpos));
//  fieldpos = PQfnumber(Results, "optformat");
//  if (fieldpos >= 0) strcpy(egt->optformat, PQgetvalue(Results, 0, fieldpos));
  
  maxstr = PQgetvalue(Results, 0, PQfnumber(Results, "max"));
  minstr = PQgetvalue(Results, 0, PQfnumber(Results, "min"));
  if (maxstr) 
    egt->max = atof(maxstr);
  if (egt->max == 0) {
    if (strstr(egt->name, "GC"))
      egt->max = 1;
    else
      egt->max = 100;    /* きめうち */
  }
  if (minstr)
    egt->min = atof(minstr);
  else
    egt->min = 0;
  egt->blend = NOBLEND; /* blendの指定も無し */
//  egt->color = DefaultGraphColor;
  egt->graphs = (EasyGraph **)malloc(sizeof(EasyGraph *) * DEFAULT_GRAPH_ENTRY);
  if (!egt->graphs) {
    fprintf(stderr, "findEasyGraphTrack: memory allocation error.\n");
    free(egt); onmemEGraphTrackNum--; /* EasyGraphTrack */
    return NULL;
  }
  onmemPointers++;
  egt->graphareanum = DEFAULT_GRAPH_ENTRY;

  egt->graphnum = 1;
  *egt->graphs = (EasyGraph *)malloc(sizeof(EasyGraph) * egt->graphnum);
  if (*egt->graphs == NULL) {
    fprintf(stderr, "findEasyGraphTrack: memory allocation error.\n");
    free(egt->graphs); onmemPointers--; /* Pointer */
    free(egt); onmemEGraphTrackNum--;   /* EasyGraphTrack */
    return NULL;
  }
  onmemEGraphNum++;

  graph = *egt->graphs;
  memset(graph, 0, sizeof(EasyGraph));
  if (index(filename, '/')) {
    strcpy(graph->filename, filename);
  } else {
    assembleCacheFileName(egt->name, egt->species, egt->revision, target,
                          graph->filename, PATH_MAX_LEN + 1);
  }
  fieldpos = PQfnumber(Results, "target");
//fprintf(stderr, "fieldpos=%d\n", fieldpos);
  if (fieldpos >= 0) strcpy(graph->target, PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "range_start");
  if (fieldpos >= 0) graph->start = atoi(PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "range_end");
  if (fieldpos >= 0) graph->end = atoi(PQgetvalue(Results, 0, fieldpos));
  fieldpos = PQfnumber(Results, "unit");
  if (fieldpos >= 0) graph->unit = atoi(PQgetvalue(Results, 0, fieldpos));  
  graph->valuenum = graph->end - graph->start + 1;
  graph->values = (EG_VALUE *)malloc(ENTRY_SIZE * graph->valuenum);
  if (!graph->values) {
    fprintf(stderr, "findEasyGraphTrack: memory allocation error.\n");
    free(egt->graphs); onmemPointers--; /* Pointer */
    free(egt); onmemEGraphTrackNum--;   /* EasyGraphTrack */
    return NULL;
  }
  onmemValues++;

  PQclear(Results);

  return egt;
}

/*****************************************************************************/
/* 指定name,species,revision,targeにマッチしたキャッシュファイル名を得る     */
/*****************************************************************************/
EasyGraphTrack *makeEasyGraphTrack(char *trackname, char *species,
                                   char *revision, char *date)
{
  EasyGraphTrack *egt;

  if (!trackname || !species || !revision) {
    fprintf(stderr, "makeEasyGraphTrack: invalid argument.\n");
    return NULL;
  }
  egt = (EasyGraphTrack *)malloc(sizeof(EasyGraphTrack));
  if (!egt) {
    fprintf(stderr, "makeEasyGraphTrack: memory allocation error.\n");
    return NULL;
  }
  onmemEGraphTrackNum++;
  egt->ettype = et_graphTrack;
  strcpy(egt->name, trackname);
  egt->color = DefaultGraphColor;
  strcpy(egt->species, species);
  strcpy(egt->revision, revision);
  egt->max = DefaultGraphValueMax;
  egt->min = 0;
  egt->blend = NOBLEND;
  if (date) strcpy(egt->date, date);
  return egt;
}

/*****************************************************************************/
/* データベースから指定条件にマッチしたEasyColorTrackを得る                  */
/*   [戻り値] 成功: 条件にヒットしたEasyColorの構造体へのポインタ            */
/*            エラー: NULL                                                   */
/*****************************************************************************/
EasyColorTrack *findEasyColorTrack(char *trackname,
                                   char *species,
                                   char *revision,
                                   char *target,
                                   char *date)
{
  char sql[SQL_MAX_LEN + 1];
  int  nTuples;
  int  tid;
  int  i;
  EasyColorTrack *ect = NULL;
  EasyColor      *ec = NULL;
  COLOR *colors = NULL;

  /* EasyColorTrack構造体の作成 */
  ect = (EasyColorTrack *)malloc(sizeof(EasyColorTrack));
  if (!ect) {
    fprintf(stderr, "findEasyColorTrack: memory allocation error.\n");
    return NULL;
  }
  onmemEColorTrackNum++;

  /* colortracks DB を検索 */
  sprintf(sql, "SELECT * FROM %s, %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\' AND target=\'%s\' AND %s.tid=%s.tid;",
          COLOR_TRACK_TABLE, COLOR_TABLE,
          trackname, species, revision, target,
          COLOR_TRACK_TABLE, COLOR_TABLE);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_TUPLES_OK) {
    fprintf(stderr, "[SQL] %s\nfindEasyColorTrack: %s\n",
            sql, PQerrorMessage(Conn));
    PQclear(Results);
    free(ect); onmemEColorTrackNum--; /* EasyColorTrack */
    return NULL;
  }
  nTuples = PQntuples(Results);
  if (nTuples == 0) {
    fprintf(stderr, "*** no data that name='%s', species='%s', revision='%s' and target='%s'\n",
            trackname, species, revision, target);
    PQclear(Results);
    free(ect); onmemEColorTrackNum--; /* EasyColroTrack */
    return NULL;
  }

  /* EasyColorTrackの内容を作成 */
  tid = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "tid")));
  ect->ettype = et_colorTrack;
  strcpy(ect->name, trackname);
  strcpy(ect->comment, PQgetvalue(Results, 0, PQfnumber(Results, "comment")));
  strcpy(ect->desc_url, PQgetvalue(Results, 0, PQfnumber(Results, "desc_url")));
  ect->backcolor.red = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "red")));
  ect->backcolor.green = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "green")));
  ect->backcolor.blue = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "blue")));
  strcpy(ect->species, species);
  strcpy(ect->revision, revision);
  strcpy(ect->species_url, PQgetvalue(Results, 0, PQfnumber(Results, "species_url")));
  ect->blend = resolveBlend(PQgetvalue(Results, 0, PQfnumber(Results, "blend")));
  strcpy(ect->optattr, PQgetvalue(Results, 0, PQfnumber(Results, "optstring")));
  strcpy(ect->date, date);
  PQclear(Results);

  /* tid と target が一致するCOLORの検索 */
  sprintf(sql, "SELECT * FROM %s WHERE tid=\'%d\' AND target=\'%s\' ORDER BY r_start;",
          COLOR_TABLE, tid, target);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_TUPLES_OK) {
    fprintf(stderr, "[SQL] %s\nfindEasyColorTrack: %s\n", sql,
            PQerrorMessage(Conn));
    PQclear(Results);
    free(ect); onmemEColorTrackNum--; /* EasyColorTrack */
    return NULL;
  }
  nTuples = PQntuples(Results);

  /* 一致するCOLORがない場合はEasyColorを作成せずに終了 */
  if (nTuples <= 0) {
    PQclear(Results);
    return ect;
  }

  ec = (EasyColor *)malloc(sizeof(EasyColor));
  if (!ec) {
    fprintf(stderr, "findEasyColorTrack: memory allocation error.\n");
    free(ect); onmemEColorTrackNum--; /* EasyColorTrack */
    PQclear(Results);
    return NULL;
  }
  onmemEColorNum++;

  colors = (COLOR *)malloc(nTuples * sizeof(COLOR));
  if (!colors) {
    fprintf(stderr, "findEasyColorTrack: memory allocation error.\n");
    free(ect); onmemEColorTrackNum--; /* EasyColorTrack */
    free(ec);  onmemEColorNum--;      /* EasyColor */
    PQclear(Results);
    return NULL;
  }
  onmemColors++;

  strcpy(ec->target, target);
  ec->start = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "r_start")));
  ec->end = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "r_end")));
  ec->unit = ec->end - ec->start + 1;
  ec->colornum = nTuples;
  strcpy(ec->url, PQgetvalue(Results, 0, PQfnumber(Results, "url")));
  ec->optnum = 0;      /* 省略 */
  ec->optattrs = NULL; /* 省略 */
  for (i = 0; i < nTuples; i++) {
    colors[i].red = atoi(PQgetvalue(Results, i, PQfnumber(Results, "red")));
    colors[i].green = atoi(PQgetvalue(Results, i, PQfnumber(Results, "green")));
    colors[i].blue = atoi(PQgetvalue(Results, i, PQfnumber(Results, "blue")));
    colors[i].start = atoi(PQgetvalue(Results, i, PQfnumber(Results, "r_start")));
    colors[i].end = atoi(PQgetvalue(Results, i, PQfnumber(Results, "r_end")));
    if (colors[i].start < ec->start)
      ec->start = colors[i].start;
    if (colors[i].end > ec->end)
      ec->end = colors[i].end;
  }
  PQclear(Results);
  ec->colors = colors;
  ec->colornum = nTuples;
  addColorToTrack(ect, ec);
//dumpEasyColor(stderr, ect->easycolors);
  free(ec); onmemEColorNum--; /* EasyColor */
  return ect;
}

/*****************************************************************************/
/* 指定DBに格納されている遺伝子情報から、指定の種・リビジョン・対象の        */
/* グラフ用データを指定パス名のファイルに書き出す。                          */
/*****************************************************************************/
int createGraphData(char *name, char *species, char *revision,
                    char *target, char *path)
{
  PGconn    *conn;
  PGresult  *resGene;
  char      conninfo[PATH_MAX_LEN + 1];
  char      sql[SQL_MAX_LEN + 1];
  int       nTuples;
  int       fd;
  int       i, j, tmp;
  EG_VALUE  *buf;
  off_t     start, end;     /* DBにあったstart/end (単位bp) */
  off_t     b_start, b_end; /* バッファの先頭と末尾 (単位bp) */
  off_t     g_start, g_end; /* バッファと重なる遺伝子の先頭と末尾 (単位bp) */
  off_t     spos, epos;     /* バッファ内での位置 (単位byte) */
  int       entrynum;
  char      *tmpcol;
  char      dbname[NAME_MAX_LEN + 1];

  if (!path) {
    return 0;
  }

  /* キャッシュ DBに接続 */
  connectCacheDB();

  /* 対象targetのrangeを調べる */
  sprintf(sql, "SELECT * FROM %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\' AND target=\'%s\';", CACHE_TABLE, name, species, revision, target);
//  fprintf(stderr, "[SQLc] %s\n", sql);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
    fprintf(stderr, "createGraphData: %s", PQerrorMessage(Conn));
    return 0;
  }
  nTuples = PQntuples(Results);
  if (nTuples != 1) {
    fprintf(stderr, "invalid min value of range_end\n");
    return 0;
  }
  start = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "r_start")));
  end   = atoi(PQgetvalue(Results, 0, PQfnumber(Results, "r_end")));
  tmpcol = PQgetvalue(Results, 0, PQfnumber(Results, "dbname"));
  if (!tmpcol) {
    fprintf(stderr, "no databases.\n");
    return 0;
  }
  strcpy(dbname, tmpcol);
  PQclear(Results);
  disconnectCacheDB();

  fprintf(stderr, "target:%s range(%d,%d)\n", target, (int)start, (int)end);

  /* graph用キャッシュファイル作成 */
  fd = open(path, O_CREAT|O_TRUNC|O_WRONLY, 0644);
  if (fd == -1) {
    fprintf(stderr, "createGraphCacheFile: could not open file: %s\n", path);
    return 0;
  }

  buf = (EG_VALUE *)malloc(BUFSIZE);
  if (buf == NULL) {
    fprintf(stderr, "createGraphCacheFile: could not allocate memory.\n");
    return 0;
  }
  onmemValues++;

  fprintf(stderr, "=======================\n");

  /* 指定のDBに接続 */
  sprintf(conninfo, "dbname=%s ", dbname);
  strcat(conninfo, Userinfo);
  conn = PQconnectdb(conninfo);
  if (PQstatus(conn) != CONNECTION_OK) {
    fprintf(stderr, "Connection to database %s failed.\n", PQdb(conn));
    fprintf(stderr, "%s", PQerrorMessage(conn));
    PQfinish(conn);
    return 0;
  }

//  entrynum = end - start + 1;
  entrynum = end + 1;
//  b_start = start;
  b_start = 0;
  b_end = b_start + BUF_ENTRIES - 1;
  if (b_end > end) b_end = end;

  while (entrynum > 0) {
    sprintf(sql, "SELECT range_start,range_end FROM gene WHERE target=\'%s\' AND range_start <= %d AND range_end >= %d;", target, (int)b_end, (int)b_start);
//    fprintf(stderr, "[SQL] %s\n", sql);
    resGene = PQexec(conn, sql);
    if (PQresultStatus(resGene) != PGRES_COMMAND_OK) {
      fprintf(stderr, "createGraphData: %s", PQerrorMessage(conn));
      return 0;
    }
    nTuples = PQntuples(resGene);
    fprintf(stderr, "[%d,%d]\t %d genes.  remained entrynum=%d\n",
            (int)b_start, (int)b_end, nTuples, entrynum);

    memset(buf, 0, BUFSIZE);
    for (i = 0; i < nTuples; i++) {
      g_start = atoi(PQgetvalue(resGene, i, 0));
      g_end = atoi(PQgetvalue(resGene, i, 1));
      if (g_start > b_end || g_end < b_start)
        continue;
      spos = (g_start < b_start) ? 0 : g_start - b_start;
      epos = (g_end > b_end) ? b_end - b_start : g_end - b_start;
      fprintf(stderr, "    gene(%d-%d), buffer pos(%d-%d)\n",
              (int)g_start, (int)g_end, (int)spos, (int)epos);
      for(j = spos; j < epos; j++) {
        buf[j] = buf[j] + 1;
      }
    }
    PQclear(resGene);
    tmp = lseek(fd, b_start * ENTRY_SIZE, SEEK_SET);
    if (tmp == -1) {
      fprintf(stderr, "lseek failed.\n");
      return 0;
    }
    if (entrynum < BUF_ENTRIES)
      write(fd, buf, entrynum * ENTRY_SIZE);
    else
      write(fd, buf, BUFSIZE);

    entrynum -= BUF_ENTRIES;
    b_start += BUF_ENTRIES;
    b_end += BUF_ENTRIES;
  }
  close(fd);
  return 1;
}

/*****************************************************************************/
/* 指定name,species,revision,targeにマッチしたキャッシュファイル名を得る     */
/*****************************************************************************/
char *findCacheFileName(char *name, char *species, char *revision,
                        char *target, int *rlength)
{
  char       *path;
  char       sql[SQL_MAX_LEN + 1];
  int        nTuples;
  char       *filename;
  int        length;

  path = (char *)malloc(PATH_MAX_LEN + 1);
  if (!path) {
    fprintf(stderr, "findCacheFiles: memory allocation error.\n");
    return NULL;
  }
  onmemStrings++;
  /* キャッシュテーブルを検索 */
  sprintf(sql, "SELECT * FROM %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\' AND target=\'%s\';",
          CACHE_TABLE, name, species, revision, target);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
    fprintf(stderr, "findCacheFileName: %s", PQerrorMessage(Conn));
    return NULL;
  }
  nTuples = PQntuples(Results);
  if (nTuples == 0) {
    return NULL;
  }
  filename = PQgetvalue(Results, 0, PQfnumber(Results, "filename"));
  if (filename == NULL) {
    path = assembleCacheFileName(name, species, revision, target,
                                 NULL, PATH_MAX_LEN + 1);
  } else {
    strcpy(path, filename);
    length = strlen(filename);
  }
  return path;
}

/*****************************************************************************/
/*  EasyGraphの情報をデータベースに登録する                                  */
/*****************************************************************************/
int addEasyGraphToDB(EasyGraphTrack *egt, EasyGraph *eg)
{
  char   sql[SQL_MAX_LEN + 1];
  char   blendname[24];
  int    nTuples;

  sprintf(sql, "SELECT * FROM %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\' AND target=\'%s\';", CACHE_TABLE,
          egt->name, egt->species, egt->revision, eg->target);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_TUPLES_OK) {
    fprintf(stderr, "[SQL1] %s\n", sql);
    fprintf(stderr, "addEasyGraphToDB(select): %s\n", PQerrorMessage(Conn));
    PQclear(Results);
    return 0;
  }
  nTuples = PQntuples(Results);
  PQclear(Results);
  getBlendName(egt->blend, blendname, 24);
  /* キャッシュDB内に既に存在したらdeleteする */
  if (nTuples != 0) {
    sprintf(sql, "DELETE FROM %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\' AND target=\'%s\';",
            CACHE_TABLE, egt->name, egt->species, egt->revision, eg->target);
    Results = PQexec(Conn, sql);
    if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
      fprintf(stderr, "[SQL2] %s\n", sql);
      fprintf(stderr, "addEasyGraphToDB(delete): %s\n", PQerrorMessage(Conn));
      PQclear(Results);
      return 0;
    }
    PQclear(Results);
  }
  sprintf(sql, "INSERT INTO %s (name, species, revision, target, date, type, r_start, r_end, unit, blend, dbname, filename, min, max, red, green, blue, comment, desc_url, species_url, optformat) VALUES (\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%d\',\'%d\',\'%d\',\'%s\',\'%s\',\'%s\',\'%lf\',\'%lf\',\'%d\',\'%d\',\'%d\',\'%s\',\'%s\',\'%s\',\'%s\');",
     CACHE_TABLE,
     egt->name, egt->species, egt->revision, eg->target, egt->date, "graph",
     (int)eg->start, (int)eg->end, (int)eg->unit, blendname, "NULL", "NULL",
     (double)egt->min, (double)egt->max, egt->color.red, egt->color.green, egt->color.blue, 
     egt->comment, egt->desc_url, egt->species_url, "NULL"
    );
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
    fprintf(stderr, "[SQL3] %s\n", sql);
    fprintf(stderr, "addEasyGraphToDB(insert): %s", PQerrorMessage(Conn));
    PQclear(Results);
    return 0;
  }
  PQclear(Results);
  return 1;
}

/*****************************************************************************/
/*  EasyColorの情報をデータベースに登録する                                  */
/*****************************************************************************/
int addEasyColorTrackToDB(EasyColorTrack *ect)
{
  int  nTuples;
  int  tid;
  char sql[SQL_MAX_LEN + 1];
  char blendname[24];

  /* colortracks DBを検索して同じのが無いか調べる */
  sprintf(sql, "SELECT tid FROM %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\';", COLOR_TRACK_TABLE, ect->name, ect->species, ect->revision);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_TUPLES_OK) {
    fprintf(stderr, "[SQL] %s\naddEasyColorTrackToDB: %s\n",
            sql, PQerrorMessage(Conn));
    PQclear(Results);
    return 0;
  }
  getBlendName(ect->blend, blendname, 24);
  nTuples = PQntuples(Results);
  if (nTuples == 0) {
    /* 無かった場合は insert を実行 */
    PQclear(Results);
    sprintf(sql, "INSERT INTO %s (name, species, revision, date, blend, red, green, blue, species_url, desc_url, comment, optstring) VALUES (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%d\', \'%d\', \'%d\', \'%s\', \'%s\', \'%s\', \'%s\');",
            COLOR_TRACK_TABLE,
            ect->name, ect->species, ect->revision, ect->date, blendname,
            ect->backcolor.red, ect->backcolor.green, ect->backcolor.blue,
            ect->species_url, ect->desc_url, ect->comment, ect->optattr);
    Results = PQexec(Conn, sql);
    if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
      fprintf(stderr, "[SQL] %s\naddEasyColorTrackToDB: %s\n",
              sql, PQerrorMessage(Conn));
      PQclear(Results);
      return 0;
    }
  } else {
    /* 有った場合は update を実行 */
    tid = atoi(PQgetvalue(Results, 0, 0));
    PQclear(Results);
    sprintf(sql, "UPDATE %s SET date=\'%s\', blend=\'%s\', red=\'%d\', green=\'%d\', blue=\'%d\', species_url=\'%s\', desc_url=\'%s\', comment=\'%s\', optstring=\'%s\' WHERE tid=\'%d\';",
            COLOR_TRACK_TABLE, ect->date, blendname,
            ect->backcolor.red, ect->backcolor.green, ect->backcolor.blue,
            ect->species_url, ect->desc_url, ect->comment, ect->optattr, tid);
    Results = PQexec(Conn, sql);
    if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
      fprintf(stderr, "[SQL] %s\naddEasyColorTrackToDB: %s\n",
              sql, PQerrorMessage(Conn));
      PQclear(Results);
      return 0;
    }
  }
  PQclear(Results);
  return 1;
}

/*****************************************************************************/
/*  EasyColorの情報をデータベースに登録する                                  */
/*****************************************************************************/
int addEasyColorToDB(EasyColorTrack *ect, EasyColor *ec)
{
  char   sql[SQL_MAX_LEN + 1];
  int    nTuples;
  int    tid;
  int    i;
  int    startpos;
  int    endpos;

//dumpEasyColor(stderr, ec);
  /* tidを取得する */
  sprintf(sql, "SELECT tid FROM %s WHERE name=\'%s\' AND species=\'%s\' AND revision=\'%s\';", COLOR_TRACK_TABLE, ect->name, ect->species, ect->revision);
  Results = PQexec(Conn, sql);
  if (PQresultStatus(Results) != PGRES_TUPLES_OK) {
    fprintf(stderr, "[SQL] %s\naddEasyColorToDB: %s\n", sql,
            PQerrorMessage(Conn));
    PQclear(Results);
    return 0;
  }
  nTuples = PQntuples(Results);
  if (nTuples > 0)
    tid = atoi(PQgetvalue(Results, 0, 0));
  else
    tid = 0;
  PQclear(Results);

  /* 同じ tid と targetのレコードを削除する */
  for (i = 0; i < ec->colornum; i++) {
    sprintf(sql, "DELETE FROM %s WHERE tid=\'%d\' AND target=\'%s\' AND r_start=\'%d\';",
            COLOR_TABLE, tid, ec->target, (int)ec->colors[i].start);
    Results = PQexec(Conn, sql);
    if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
      fprintf(stderr, "[SQL] %s\naddEasyColorToDB: %s\n", sql,
              PQerrorMessage(Conn));
    }
    PQclear(Results);
  }

  /* COLORSの数だけレコードを追加する */
  startpos = ec->start;
  endpos = startpos + ec->unit - 1;
  for (i = 0; i < ec->colornum; i++) {
    sprintf(sql, "INSERT INTO %s VALUES (\'%d\', \'%s\', \'%d\', \'%d\', \'%d\', \'%d\', \'%d\', \'%s\');",
            COLOR_TABLE, tid, ec->target, startpos, endpos,
            ec->colors[i].red, ec->colors[i].green, ec->colors[i].blue,
            ec->url);
    Results = PQexec(Conn, sql);
    if (PQresultStatus(Results) != PGRES_COMMAND_OK) {
      fprintf(stderr, "[SQLi] %s\naddEasyColorToDB: %s\n",
              sql, PQerrorMessage(Conn));
    }
    startpos += ec->unit;
    endpos += ec->unit;
    PQclear(Results);
  }
  return 1;
}

/*****************************************************************************/
/*  キャッシュファイル格納ディレクトリを作成する                             */
/*****************************************************************************/
int createDirectories(EasyGraphTrack *egt)
{
  char path[PATH_MAX_LEN + 1];

  if (!egt) {
    fprintf(stderr, "createDirectories: invalid argument.\n");
    return 0;
  }
  strcpy(path, CACHEDIR);
  strcat(path, egt->name);
  if (access(path, F_OK)) {
    fprintf(stderr, "creating directory: %s\n", path);
    mkdir(path, 0777);
  }
  strcat(path, "/");
  strcat(path, egt->species);
  if (access(path, F_OK)) {
    fprintf(stderr, "creating directory: %s\n", path);
    mkdir(path, 0777);
  }
  strcat(path, "/");
  strcat(path, egt->revision);
  if (access(path, F_OK)) {
    fprintf(stderr, "creating directory: %s\n", path);
    mkdir(path, 0777);
  }
  return 1;
}

/*****************************************************************************/
/* 表示ピクセル数の調整を行う                                                */
/* (例) 25bpを10ピクセルで表示 ⇒ 1ピクセル当り2.5bp ⇒ 2bpとする            */
/*      実際に必要な幅は、25 / 2 = 12.5ピクセル ⇒ 13ピクセルとする          */
/* [戻り値] 正常: entrynum,  エラー: 0                                       */
/*****************************************************************************/
int adjustWidth(int entrynum, int *unit, int *width)
{
  if (!unit || !width) {
    fprintf(stderr, "adjustWidth: invalid argument.\n");
    return 0;
  }
  if (*width == 0)                    /* 表示幅が設定されていない場合 */
    *width = entrynum;
  *unit = entrynum / *width;          /* 1ピクセル当りのbp数(unit)を求める */
  if (*unit == 0)
    *unit = 1;                        /* unitの最小値は1とする */
  *width = entrynum / *unit;
  if (*width * *unit < entrynum)      /* 表示ピクセル数の調整 */
    (*width)++;
  return entrynum;
}