<?php
#*******************************************************************************
# Easy Gene/Graph/Color Track ファイルのデータをDBに格納する。
# $InsertHostにDB作成後に、$PublicHostにDBをコピーする。
# EasyGene  DB名：cachegene_name_species_revision
# EasyGraph DB名：cachegraph_name_species_revision
# EasyColor DB名：cachecolor_name_species_revision
function CreateCacheDB
($easytrackfile_url, $IsWriteLogfile=true) 
{
  #-------------------------------------------------------------------------------
  # Global変数
  // browser.config.php の設定
  global $utgb_DB_user;
  global $utgb_log_dir;
  global $utgb_cachedb_insert_host;
  global $utgb_cachedb_public_host;
  global $utgb_postgre_install_dir;
  // このファイル内
  global $fp_log;
  global $hDB_cache_create;
  global $IsWriteLog;
  global $fp_log;
  global $LineNum;
  global $DataNum;
  global $InsertedDataNum;
  global $Update_NewData;
  global $Update_ReplaceData;
  global $Update_NotUniqueDataName;
  global $NotUniqueDataNum;
  global $NotUniqueExonDataNum;
  global $result_value;
  $IsWriteLog = $IsWriteLogfile;
  $LineNum = 0;
  $DataNum = 0;
  $InsertedDataNum = 0;
  $Update_NewData = 0;
  $Update_ReplaceData = 0;
  $Update_NotUniqueDataName = 0;
  $NotUniqueDataNum = 0;
  $NotUniqueExonDataNum = 0;
  $result_value = array();
  
  if ($utgb_cachedb_insert_host == '') {
    $InsertHost = $_SERVER['SERVER_NAME'];
  } else {
    $InsertHost = $utgb_cachedb_insert_host;
  }
  if ($utgb_cachedb_public_host == '') {
    $PublicHost = $_SERVER['SERVER_NAME'];
  } else {
    $PublicHost = $utgb_cachedb_public_host;
  }
  
  #-------------------------------------------------------------------------------
  # ファイルオープン
  # EasyTrackファイル
  $fp_track = fopen($easytrackfile_url, "r") or die("unable to open file.\n");
  # logファイル処理（上書き）
  if ($IsWriteLog) {
    $trackfilename = basename($easytrackfile_url); 
    $logfilename = $utgb_log_dir."/log_CreateCacheDB_from_$trackfilename.log";
    if (! file_exists($logfilename)) {
      $fp_temp = fopen($logfilename, "w");
      # ディレクトリに書き込み権限が必要
      fwrite($fp_temp, "");
      # 空のファイルを作成する
      fclose($fp_temp);
    }
    //$fp_log = fopen($logfilename, "a") or die ("unable to open log file.\n");
    $fp_log = fopen($logfilename, "a");
    # 書き込みファイルは排他的ロックする
    //flock($fp_log, LOCK_EX) or exit("unable to lock log file.\n");
    flock($fp_log, LOCK_EX);
  }
  # log 書き込みStart
  //echo("begin\n");
  WriteLogFile("------------------------------------------------", 0, false);
  $date_now = date("F j, Y, H:i:s");
  WriteLogFile("BEGIN : $date_now", 0, false);
  WriteLogFile("Track file path : $easytrackfile_url", 0, false);
  
  #-------------------------------------------------------------------------------
  # Track行解析
  $Track_Type = ""; # gene || graph || color
  $Track_name = "";
  $Track_species = "";
  $Track_revision = "";
  $Track_species_url = "";
  $Track_description_url = "";
  $Track_color = "";
  $Track_comment = "";
  $Track_date = "";
  $Track_max = 0.0; # Graphのみ
  $Track_min = 0.0; # Graphのみ
  $Track_blend = ""; # GraphとColorのみ
  $Track_optattr = "";
  $Track_memo = ""; # Track行またはTrack行以前に記述された'#'以下
  
  while (!feof($fp_track)) {
    # PHP 4.3以降では最後まで読むはず
    $line = fgets($fp_track);
    $LineNum++;
    
    # コメント行処理（2行目以降が'#'のみでないときは上書きする）
    if (preg_match("/^#/", $line, $matches) >= 1) {
      $str_temp = str_replace("#", "", $line);
      $str_temp = trim($str_temp);
      if ($str_temp != "") {
        $Track_memo = $str_temp;
      }
      continue;
    }
    
    # Track行処理
    if (preg_match("/^geneTrack/", $line, $matches) >= 1) {
      # EasyGeneTrackの場合
      $Track_Type = "gene";
    } elseif (preg_match("/^graphTrack/", $line, $matches) >= 1) {
      # EasyGraphTrackの場合
      $Track_Type = "graph";
    } elseif (preg_match("/^colorTrack/", $line, $matches) >= 1) {
      # EasyColorTrackの場合
      $Track_Type = "color";
    } else {
      # 文法違反エラー
      WriteLogFile("Track line is invalid.", $LineNum, true);
      return $result_value;
    }
    
    $line = chop($line);
    $line_ori = $line;
    
    # 項目の複数チェックなし
    
    # 空白対応（空白を許すのはcommentとoptattr）
    # commentとoptattrを抽出してから、$lineから削除
    # 行頭にはないはず
    # ダブルクォーテーションで囲んでいる場合と囲んでいない場合を考慮する
    # comment
    if ((preg_match("/[\s]comment=\"([^\"]+)\"/", $line, $matches)) ||
        (preg_match("/[\s]comment=([^\s]+)/", $line, $matches))) {
      $Track_comment = $matches[1];
      $Track_comment = pg_escape_string($Track_comment);
      $line = str_replace("$matches[0]", "", $line);
    }
    # optattr
    if ((preg_match("/[\s]optattr=\"([^\"]+)\"/", $line, $matches)) ||
        (preg_match("/[\s]optattr=([^\s]+)/", $line, $matches))) {
      $Track_optattr = $matches[1];
      $Track_optattr = pg_escape_string($Track_optattr);
      $line = str_replace("$matches[0]", "", $line);
    }
    
    # 追加属性対応
    # "/"を[]や()内に用いていないことが前提条件。
    # 前処理
    # 文頭と文末に','を付加する
    #  ','でスプリットできないので判定のためにつけているが、
    #  正規表現の前後につけると、連続した場合に認識できない。
    $Track_optattr = ",".$Track_optattr.",";
    
    # Select 型
    # $matches[0] : ,fromSpecies/select[medaka,fugu,zebrafish](disp,color,sort)
    # $matches[1]（必須）: fromSpecies
    # $matches[2]（必須）: medaka,fugu,zebrafish
    # $matches[3]（不要）: disp,color,sort
    $Track_opt_select_name = NULL;
    $Track_opt_select_name_element = NULL;
    if (preg_match_all("/,([^,\/]+)\/select\[([^\]]+)\]\(([^\)]+)\)/", $Track_optattr, $matches)) {
      for ($i = 0; $i < count($matches[0]); $i++) {
        $opt_terms = explode(",", $matches[2][$i]);
        # 自動インクリメント
        $Track_opt_select_name[] = $matches[1][$i];
        for ($j = 0; $j < count($opt_terms); $j++) {
          # hash & array
          $Track_opt_select_name_element[$matches[1][$i]][] = $opt_terms[$j];
        }
      }
    }
    # (disp,color,sort)が無い場合に対応
    if (preg_match_all("/,([^,\/]+)\/select\[([^\]]+)\][^\(]/", $Track_optattr, $matches)) {
      for ($i = 0; $i < count($matches[0]); $i++) {
        $opt_terms = explode(",", $matches[2][$i]);
        # 自動インクリメント
        $Track_opt_select_name[] = $matches[1][$i];
        for ($j = 0; $j < count($opt_terms); $j++) {
          # hash & array
          $Track_opt_select_name_element[$matches[1][$i]][] = $opt_terms[$j];
        }
      }
    }
    
    # Real 型
    # $matches[0] : ,matchRatio/real[0.7,1.0](gradation,ubound,lbound)
    # $matches[1]（必須）: matchRatio
    # $matches[2]（不要）: 0.7,1.0
    # $matches[3]（不要）: gradation,ubound,lbound
    # 例えば、',clusterid/real'だけでも可能。
    $Track_opt_real_name = NULL;
    if (preg_match_all("/,([^,\/]+)\/real\[([^\]]+)\]\(([^\)]+)\)/", $Track_optattr, $matches)) {
      for ($i = 0; $i < count($matches[0]); $i++) {
        $Track_opt_real_name[] = $matches[1][$i];
      }
    }
    if (preg_match_all("/,([^,\/]+)\/real\[([^\]]+)\][^\(]/", $Track_optattr, $matches)) {
      for ($i = 0; $i < count($matches[0]); $i++) {
        $Track_opt_real_name[] = $matches[1][$i];
      }
    }
    if (preg_match_all("/,([^,\/]+)\/real[^\[]/", $Track_optattr, $matches)) {
      for ($i = 0; $i < count($matches[0]); $i++) {
        $Track_opt_real_name[] = $matches[1][$i];
      }
    }
    
    # 前処理で付加したカンマをトリミングする
    $Track_optattr = trim($Track_optattr, ",");
    
    # 各項目を取得
    # Gene/Graph/Color 共通
    $Track_name             = ParseLine($line, "name");
    $Track_species          = ParseLine($line, "species");
    $Track_revision         = ParseLine($line, "revision");
    $Track_species_url      = ParseLine($line, "species_url");
    $Track_description_url  = ParseLine($line, "description_url");
    $Track_color            = ParseLine($line, "color");
    $Track_date             = ParseLine($line, "date");
    $Track_max              = ParseLine($line, "max"); # Graphのみ
    $Track_min              = ParseLine($line, "min"); # Graphのみ
    $Track_blend            = ParseLine($line, "blend"); # GraphとColorのみ
  
    # ' #'以下のコメントがある場合はトラックのコメントとみなして上書き
    $str_temp = ParseLineMemo($line);
    if ($str_temp != "") {
      $Track_memo = $str_temp;
    }
    
    # カンマ区切りで取得
    $Track_colors = preg_split("/,/", $Track_color);
    
    # 必須項目チェック
    if ($Track_name == "" || $Track_species == "" || $Track_revision == "") {
      WriteLogFile("Name, Species, and Revision are indispensable.", $LineNum, true);
      return $result_value;
    }
    
    # 省略していた場合の処理
    if ($Track_colors[0] == "") { $Track_colors[0] = 0;}
    if (! isset($Track_colors[1])) { $Track_colors[1] = 0;}
    if (! isset($Track_colors[2])) { $Track_colors[2] = 0;}
    
    # graphのmin,maxのいづれかを省略していた場合は、
    # 0.0を代入して、使用するフラグを0にして格納する。
    $Track_UseMinMax = 0;
    if ($Track_max == "") { $Track_max = 0.0; $Track_UseMinMax = 1; }
    if ($Track_min == "") { $Track_min = 0.0; $Track_UseMinMax = 1; }
    
    break;
  }
  
  #-------------------------------------------------------------------------------
  # graph / color の場合は、このバージョンではcachedbを作成しない。
  # トラック行はtrackinformation DBに、データ部分はキャッシュファイルに格納する。
  if ($Track_Type == "graph" || $Track_Type == "color") {
    WriteLogFile("Track type is $Track_Type", 0, false);
    WriteLogFile("------------------------------------------------", 0, false);
    # ファイルクローズ
    FileClose();
    // 戻り値
    $result_value['track_name'] = $Track_name;
    $result_value['species'] = $Track_species;
    $result_value['revision'] = $Track_revision;
    $result_value['kind'] = $Track_Type;
    $result_value['description_url'] = $Track_description_url;
    $result_value['species_url'] = $Track_species_url;
    $result_value['optattr_str'] = $Track_optattr;
    $result_value['comment'] = $Track_comment;
    $result_value['color_r'] = $Track_colors[0];
    $result_value['color_g'] = $Track_colors[1];
    $result_value['color_b'] = $Track_colors[2];
    $result_value['status'] = 'GRAPH_COLOR';
    return $result_value;
  }

  #-------------------------------------------------------------------------------
  # DB parameter設定
  # DB名
  switch ($Track_Type) {
  case "gene":
    $DB_NAME = "cachegene_".$Track_name."_".$Track_species."_".$Track_revision;
    break;
  case "graph":
    $DB_NAME = "cachegraph_".$Track_name."_".$Track_species."_".$Track_revision;
    break;
  case "color":
    $DB_NAME = "cachecolor_".$Track_name."_".$Track_species."_".$Track_revision;
    break;
  default:
    WriteLogFile("Track type is invalid.", $LineNum, true);
    return $result_value;
  }
  
  #-------------------------------------------------------------------------------
  # DB 作成処理
  
	# LD_LIBRARY_PATH が設定されていないケースに対応
	$ld_path_str = "env LD_LIBRARY_PATH=$utgb_postgre_install_dir/lib $utgb_postgre_install_dir/bin/";
	
  # Trackが新規か既存かを調べる
  //$DB_check_connection = "host=$InsertHost dbname=$DB_NAME user=$utgb_DB_user";
  $DB_check_connection = "host=$PublicHost dbname=$DB_NAME user=$utgb_DB_user";
  if (($hDB_check = pg_connect($DB_check_connection)) == FALSE) {
    $SQL_MODE = "INSERT";
    $FLAG_RETURN = "INSERT";
  } else {
    $SQL_MODE = "INSERT";
    $FLAG_RETURN = "UPDATE";
  }
  
  // Update の際は、一旦DBを削除することに方針変換
	// 他ホストの場合は、apacheユーザでも実行できる。
	// 自ホストの場合は、管理者ユーザになってもdropdbできない。
  if ($FLAG_RETURN == "UPDATE") {
    $command = $ld_path_str."dropdb -U $utgb_DB_user -h $InsertHost $DB_NAME";
    $result = shell_exec(escapeshellcmd($command));
    WriteLogFile("DropDB $DB_NAME", 0, false);
  }
	
  # DB 作成
  $command = $ld_path_str."createdb -U $utgb_DB_user $DB_NAME -h $InsertHost";
  $result = shell_exec(escapeshellcmd($command));
  WriteLogFile("CreateDB $DB_NAME", 0, false);
  
  # Table 作成
  switch ($Track_Type) {
  case "gene":
    //$command = "psql -U $utgb_DB_user -h $InsertHost $DB_NAME < DB_Schime_EasyGeneTrackCache.sql";
    $command = $ld_path_str."psql -U $utgb_DB_user -h $InsertHost $DB_NAME < ./cache/DB_Schime_EasyGeneTrackCache.sql";
    break;
  case "graph":
    //$command = "psql -U $utgb_DB_user -h $InsertHost $DB_NAME < DB_Schime_EasyGraphTrackCache.sql";
    $command = $ld_path_str."psql -U $utgb_DB_user -h $InsertHost $DB_NAME < ./cache/DB_Schime_EasyGraphTrackCache.sql";
    break;
  case "color":
    //$command = "psql -U $utgb_DB_user -h $InsertHost $DB_NAME < DB_Schime_EasyColorTrackCache.sql";
    $command = $ld_path_str."psql -U $utgb_DB_user -h $InsertHost $DB_NAME < ./cache/DB_Schime_EasyColorTrackCache.sql";
    break;
  default:
    WriteLogFile("Track type is invalid.", $LineNum, true);
  }
  #$result = shell_exec(escapeshellcmd($command)); # "<" でエラーになる。
  $result = shell_exec($command);
  #WriteLogFile($result, 0, false);
  
  # DB 接続
  #$DB_connection = "dbname=$DB_NAME user=$utgb_DB_user";
  $DB_connection = "host=$InsertHost dbname=$DB_NAME user=$utgb_DB_user";
  if (($hDB_cache_create = pg_connect($DB_connection)) == FALSE) {
    WriteLogFile("Can't connect DBMS. DB name : '$DB_NAME'", 0, true);
    return $result_value;
  }
  
  #-------------------------------------------------------------------------------
  # DB格納開始
  pg_query($hDB_cache_create, "BEGIN");
  
  #-------------------------------------------------------------------------------
  # Track行格納
  
  # Track Table
  if ($SQL_MODE == "INSERT") {
    switch ($Track_Type) {
    case "gene":
      $SQL_query = "
        INSERT INTO Track 
        (track_name, comment, description_url, color_red, color_green, color_blue, 
          species, revision, species_url, date, optattr_str, memo) 
          VALUES 
          ('$Track_name', '$Track_comment', '$Track_description_url', 
           $Track_colors[0], $Track_colors[1], $Track_colors[2], 
           '$Track_species', '$Track_revision', '$Track_species_url', 
           '$Track_date', '$Track_optattr', '$Track_memo');
      ";
      break;
    case "graph":
      $SQL_query = "
        INSERT INTO Track 
        (track_name, comment, description_url, color_red, color_green, color_blue, 
          species, revision, species_url, use_minmax, max, min, blend, date, optattr_str, memo) 
          VALUES 
          ('$Track_name', '$Track_comment', '$Track_description_url', 
           $Track_colors[0], $Track_colors[1], $Track_colors[2], 
           '$Track_species', '$Track_revision', '$Track_species_url', 
           $Track_UseMinMax, $Track_max, $Track_min, '$Track_blend',
           '$Track_date', '$Track_optattr', '$Track_memo');
      ";
       break;
    case "color":
      $SQL_query = "
        INSERT INTO Track 
        (track_name, comment, description_url, color_red, color_green, color_blue, 
          species, revision, species_url, blend, date, optattr_str, memo) 
          VALUES 
          ('$Track_name', '$Track_comment', '$Track_description_url', 
           $Track_colors[0], $Track_colors[1], $Track_colors[2], 
           '$Track_species', '$Track_revision', '$Track_species_url', 
           '$Track_blend',
           '$Track_date', '$Track_optattr', '$Track_memo');
      ";
       break;
    default:
       WriteLogFile("Track type is invalid.", 0, true);
       return $result_value;
    }
    
    ExecSQL_Insert($SQL_query);
  }
  else {
    // Update
    // 1行のみのテーブル
    switch ($Track_Type) {
    case "gene":
      $SQL_query = "
        UPDATE Track SET
          track_name='$Track_name', 
          comment='$Track_comment', 
          description_url='$Track_description_url', 
          color_red=$Track_colors[0], 
          color_green=$Track_colors[1], 
          color_blue=$Track_colors[2], 
          species='$Track_species', 
          revision='$Track_revision', 
          species_url='$Track_species_url', 
          date='$Track_date', 
          optattr_str='$Track_optattr', 
          memo='$Track_memo';
      ";
      break;
    case "graph":
      $SQL_query = "
        UPDATE Track SET
          track_name='$Track_name', 
          comment='$Track_comment', 
          description_url='$Track_description_url', 
          color_red=$Track_colors[0], 
          color_green=$Track_colors[1], 
          color_blue=$Track_colors[2], 
          species='$Track_species', 
          revision='$Track_revision', 
          species_url='$Track_species_url', 
          use_minmax=$Track_UseMinMax, 
          max=$Track_max, 
          min=$Track_min, 
          blend='$Track_blend', 
          date='$Track_date', 
          optattr_str='$Track_optattr', 
          memo='$Track_memo';
      ";
       break;
    case "color":
      $SQL_query = "
        UPDATE Track SET
          track_name='$Track_name', 
          comment='$Track_comment', 
          description_url='$Track_description_url', 
          color_red=$Track_colors[0], 
          color_green=$Track_colors[1], 
          color_blue=$Track_colors[2], 
          species='$Track_species', 
          revision='$Track_revision', 
          species_url='$Track_species_url', 
          blend='$Track_blend', 
          date='$Track_date', 
          optattr_str='$Track_optattr', 
          memo='$Track_memo';
      ";
       break;
    default:
       WriteLogFile("Track type is invalid.", 0, true);
       return $result_value;
    }
    
    ExecSQL_Update($SQL_query);
  }
  
  // Updateのときも、追加属性のデータを削除はしない。
  // 一部Updateに対応する。
  /*
  if ($SQL_MODE == "UPDATE") {
    $SQL_query = "DELETE FROM OptattrSelectName;";
    $res_temp = pg_query($hDB_cache_create, $SQL_query);
    $SQL_query = "DELETE FROM OptattrRealName;";
    $res_temp = pg_query($hDB_cache_create, $SQL_query);
  }
  */
  
  # 追加属性
  # Select 型
  $OptSelectFlag = false;
  if (isset($Track_opt_select_name)) {
    for ($i = 0; $i < count($Track_opt_select_name); $i++) {
      $OptSelectFlag = true;
      $strSelectName = $Track_opt_select_name[$i];
      $SQL_query_check = "SELECT * FROM OptattrSelectName WHERE select_name_str='$strSelectName'";
      $res_temp = pg_query($hDB_cache_create, $SQL_query_check);
      if (pg_num_rows($res_temp) == 0) {
        $SQL_query = "
          INSERT INTO OptattrSelectName (select_name_str) VALUES ('$strSelectName');
        ";
        ExecSQL_Insert($SQL_query);
      }
    }
  }
  
  # Real 型
  $OptRealFlag = false;
  if (isset($Track_opt_real_name)) {
    for ($i = 0; $i < count($Track_opt_real_name); $i++) {
      $OptRealFlag = true;
      $strRealName = $Track_opt_real_name[$i];
      $SQL_query_check = "SELECT * FROM OptattrRealName WHERE real_name_str='$strRealName'";
      $res_temp = pg_query($hDB_cache_create, $SQL_query_check);
      if (pg_num_rows($res_temp) == 0) {
        $SQL_query = "
          INSERT INTO OptattrRealName (real_name_str) VALUES ('$strRealName');
        ";
        ExecSQL_Insert($SQL_query);
      }
    }
  }
  
  #-------------------------------------------------------------------------------
  # Data行格納
  while (!feof($fp_track)) {
    # PHP 4.3以降では最後まで読むはず
    $line = fgets($fp_track);
    $LineNum++;
    # コメント行などData行（Gene行/Graph行/Color行）で無い場合は次の行へ
    if (($Track_Type == "gene") && (preg_match("/^gene/", $line, $matches) < 1) ||
        ($Track_Type == "graph") && (preg_match("/^graph/", $line, $matches) < 1) ||
        ($Track_Type == "color") && (preg_match("/^color/", $line, $matches) < 1)) {
      continue;
    }
    $DataNum++;
    $line = chop($line);
    $line_ori = $line;
    $Data_target = "";
    $Data_range = "";
    $Data_range_start = 0;
    $Data_range_end = 0;
    $Data_name = "";
    $Data_url = "";
    $Data_strand = "";
    $Data_memo = "";

    #-------------------------------------------------------------------------
    # Data Table
    # パース
    $Data_target = ParseLine($line, "target");
    $Data_range = ParseLine($line, "range");
    $Data_name = ParseLine($line, "name");
    $Data_url = ParseLine($line, "url");
    $Data_strand = ParseLine($line, "strand");
    $Data_color = ParseLine($line, "color");
    $Data_memo = ParseLineMemo($line);
    unset($Data_ranges);
    $Data_ranges = preg_split("/,/", $Data_range);
    # 省略していた場合の処理
    if ($Data_ranges[0] == "") { $Data_ranges[0] = 0;}
    if (! isset($Data_ranges[1])) { $Data_ranges[1] = 0;}
    $Data_range_start = (int)$Data_ranges[0];
    $Data_range_end = (int)$Data_ranges[1];
    unset($Data_colors);
    $Data_colors = preg_split("/,/", $Data_color);
    # 省略していた場合の処理
    if ($Data_colors[0] == "") { $Data_colors[0] = 0;}
    if (! isset($Data_colors[1])) { $Data_colors[1] = 0;}
    if (! isset($Data_colors[2])) { $Data_colors[2] = 0;}
    $Data_color_red = (int)$Data_colors[0];
    $Data_color_green = (int)$Data_colors[1];
    $Data_color_blue = (int)$Data_colors[2];

    # gene のUpdateの場合は、'name'をユニークIDとみなす。（仕様追加）
    # graph, color のUpdateの場合は、targetとrangeでユニークIDとみなす。
    if ($SQL_MODE == "UPDATE") {
      if ($Track_Type == "gene") {
        $SQL_query_delete = "SELECT data_id FROM Data WHERE name='$Data_name';";
      } else {
        $SQL_query_delete = "
          SELECT data_id FROM Data 
            WHERE target='$Data_target'
             AND range_start=$Data_range_start AND range_end=$Data_range_end;
        ";
      }
      $res_temp = pg_query($hDB_cache_create, $SQL_query_delete);
      $datanum_temp = pg_num_rows($res_temp);
      if ($datanum_temp == 0) {
        // 新規にデータを追加する。
        $Update_NewData++;
      } else if ($datanum_temp == 1) {
        // 'name'に対応したデータを削除する。
        $data_id_delete = pg_fetch_result($res_temp, 0, 'data_id');
        $SQL_query_delete = "DELETE FROM OptattrDataSelect WHERE data_id=$data_id_delete;";
        $res_temp = pg_query($hDB_cache_create, $SQL_query_delete);
        $SQL_query_delete = "DELETE FROM OptattrDataReal WHERE data_id=$data_id_delete;";
        $res_temp = pg_query($hDB_cache_create, $SQL_query_delete);
        if ($Track_Type == "gene") {
          $SQL_query_delete = "DELETE FROM Exon WHERE data_id=$data_id_delete;";
          $res_temp = pg_query($hDB_cache_create, $SQL_query_delete);
        }
        $SQL_query_delete = "DELETE FROM Data WHERE data_id=$data_id_delete;";
        $res_temp = pg_query($hDB_cache_create, $SQL_query_delete);
        $Update_ReplaceData++;
      } else {
        // 同一の'name'のgeneもしくは同じ範囲のgraph,colorが複数あるので、deleteは行わない。
        $Update_NotUniqueDataName++;
      }
    }

    # Unique チェック
    # DBでUnique制約をかけるとINSERT時にAbortされるので、
    #  ここでチェックして、ログに記述する。
    # Geneの場合はRangeだけでは重複の可能性があるので
    #  NameもまとめてUniqueとみなす。（仕様ではない）
    if ($Track_Type == "gene") {
      $SQL_query_Unique = "
        SELECT data_id FROM Data 
          WHERE target='$Data_target'
            AND range_start=$Data_range_start AND range_end=$Data_range_end
            AND name='$Data_name';
      ";
    } else {
      $SQL_query_Unique = "
        SELECT data_id FROM Data 
          WHERE target='$Data_target'
            AND range_start=$Data_range_start AND range_end=$Data_range_end;
      ";
    }
    $res = pg_query($hDB_cache_create, $SQL_query_Unique);
    if (pg_num_rows($res) >= 1) {
      #WriteLogFile("$line_ori", $LineNum, false);
      #WriteLogFile("Not Unique Data.\n SQL : $SQL_query_Unique", 0, false);
      $NotUniqueDataNum++;
      #continue;
    }
    
    // Data Insert
    switch ($Track_Type)
    {
    case "gene":
      $SQL_query = "
        INSERT INTO Data 
        (target, range_start, range_end, name, url, strand, memo, all_str) 
          VALUES 
          ('$Data_target', $Data_range_start, $Data_range_end,
            '$Data_name', '$Data_url', '$Data_strand', '$Data_memo', '$line_ori');
      ";
      break;
    case "graph":
      # unitやnumsをもとのデータのまま保存している
      $SQL_query = "
        INSERT INTO Data 
        (target, range_start, range_end, url, 
          color_red, color_green, color_blue, memo, str) 
          VALUES 
          ('$Data_target', $Data_range_start, $Data_range_end,
            '$Data_url', $Data_color_red, $Data_color_green, $Data_color_blue,
            '$Data_memo', '$line_ori');
      ";
      break;
    case "color":
      # unitやcolorsをもとのデータのまま保存している
      $SQL_query = "
        INSERT INTO Data 
        (target, range_start, range_end, url, memo, str) 
          VALUES 
          ('$Data_target', $Data_range_start, $Data_range_end,
            '$Data_url', '$Data_memo', '$line_ori');
      ";
      break;
    default:
      WriteLogFile("Track type is invalid.", 0, true);
      return $result_value;
    }
    ExecSQL_Insert($SQL_query);
    
    # Data ID 取得
    $Data_ID = ExecSQL_GetID($SQL_query_Unique, "data_id");

    #-------------------------------------------------------------------------
    # Exon Table
    # Exon は複数
    # 行末の場合は右側は空白にはならない
    if ($Track_Type == "gene") {
      if (preg_match_all("/[\s]exon=([^\s]+)/", $line, $matches)) {
        for ($i = 0; $i < count($matches[0]); $i++) {
          $Gene_exon = preg_split("/,/", $matches[1][$i]);
          # Unique チェック
          $SQL_query_Unique = "
            SELECT * FROM Exon 
              WHERE data_id=$Data_ID 
               AND exon_start=$Gene_exon[0] AND exon_end=$Gene_exon[1];
          ";
          $res = pg_query($hDB_cache_create, $SQL_query_Unique);
          if (pg_num_rows($res) >= 1) {
            #WriteLogFile("$line_ori", $LineNum, false);
            #WriteLogFile("Not Unique Exon.\n SQL : $SQL_query_Unique", 0, false);
            $NotUniqueExonDataNum++;
            continue;
          }
          # INSERT
          $SQL_query = "
            INSERT INTO Exon 
            (data_id, exon_start, exon_end) 
              VALUES 
              ($Data_ID, $Gene_exon[0], $Gene_exon[1]);
          ";
          ExecSQL_Insert($SQL_query);
        }
      }
    }

    #-------------------------------------------------------------------------
    # 追加属性
    # Select 型
    if ($OptSelectFlag) {
      DataOptattrSelect($line, $Track_opt_select_name, $Data_ID);
    }
    # Real 型
    if ($OptRealFlag) {
      DataOptattrReal($line, $Track_opt_real_name, $Data_ID);
    }
    
    #-------------------------------------------------------------------------
    # データ入力成功
    $InsertedDataNum++;
    if ($InsertedDataNum % 1000 == 0) {
      //echo("Num of inserted data : $InsertedDataNum\n");
    }
  }
  
  #-------------------------------------------------------------------------------
  # DB 処理
  # COMMIT
  pg_query($hDB_cache_create, "COMMIT");
  # Close
  pg_close($hDB_cache_create);
  
  #-------------------------------------------------------------------------------
  # データ数等書き込み
  //if ($SQL_MODE == "INSERT") {
  if ($FLAG_RETURN == "INSERT") {
    WriteLogFile("New Track", 0, false);
    $FailedDataNum = $DataNum - $InsertedDataNum;
    WriteLogFile("Num of Data : $DataNum", 0, false);
    WriteLogFile("Num of Failed Data : $FailedDataNum", 0, false);
    WriteLogFile("Num of Not Unique Data : $NotUniqueDataNum", 0, false);
  } else {
    WriteLogFile("Update Track", 0, false);
    WriteLogFile("Num of Data : $DataNum", 0, false);
    WriteLogFile("Num of New Data : $Update_NewData", 0, false);
    WriteLogFile("Num of Replaced Data : $Update_ReplaceData", 0, false);
    WriteLogFile("Num of Not Unique Name : $Update_NotUniqueDataName", 0, false);
  }
  WriteLogFile("Num of Not Unique Exon Data : $NotUniqueExonDataNum", 0, false);
  $date_now = date("F j, Y, H:i:s");
  WriteLogFile("COMMIT : $date_now", 0, false);
  #-------------------------------------------------------------------------------
  # $PublicHostへ DB Copy
  if (isset($PublicHost) && ($InsertHost != $PublicHost)) {
    // $PublicHostの DB 削除
		// 自ホストの場合は、apacheユーザが管理者ユーザになってもdropdbできない。
/*
    $command = $ld_path_str."dropdb -U $utgb_DB_user -h $PublicHostv $DB_NAME";
    $result = shell_exec(escapeshellcmd($command));
    //$result = exec($command);
*/
	  if ($FLAG_RETURN == "INSERT") {
	    // 空のDBを作成
	    $command = $ld_path_str."createdb -U $utgb_DB_user -h $PublicHost $DB_NAME";
	    $result = shell_exec(escapeshellcmd($command));
		} else {
			// Tableを削除
		  switch ($Track_Type) {
		  case "gene":
    //$command = "psql -U $utgb_DB_user -h $InsertHost $DB_NAME < DB_Schime_EasyGeneTrackCache.sql";
    $command = $ld_path_str."psql -U $utgb_DB_user -h $PublicHost $DB_NAME < ./cache/DB_DROPTABLE_EasyGeneTrackCache.sql";
		    break;
		  case "graph":
    //$command = "psql -U $utgb_DB_user -h $InsertHost $DB_NAME < DB_Schime_EasyGraphTrackCache.sql";
    //$command = $ld_path_str."psql -U $utgb_DB_user -h $PublicHost $DB_NAME < ./cache/DB_DELETE_EasyGraphTrackCache.sql";
		    break;
		  case "color":
    //$command = "psql -U $utgb_DB_user -h $InsertHost $DB_NAME < DB_Schime_EasyColorTrackCache.sql";
    //$command = $ld_path_str."psql -U $utgb_DB_user -h $PublicHost $DB_NAME < ./cache/DB_DELETE_EasyColorTrackCache.sql";
		    break;
		  default:
		    WriteLogFile("Track type is invalid.", $LineNum, true);
		  }
		  #$result = shell_exec(escapeshellcmd($command)); # "<" でエラーになる。
		  $result = shell_exec($command);
		}
		
    // DBをコピー
    $command = $ld_path_str."pg_dump -U $utgb_DB_user -h $InsertHost -F c $DB_NAME | ".$ld_path_str."pg_restore -U postgres -h $PublicHost -d $DB_NAME -F c";
    #$result = shell_exec(escapeshellcmd($command));
    $result = shell_exec($command);
    $date_now = date("F j, Y, H:i:s");
    WriteLogFile("Finish copying DB to $PublicHost : $date_now", 0, false);
  }
  WriteLogFile("------------------------------------------------", 0, false);
  //echo("finish\n");
  # ファイルクローズ
  FileClose();
  // 戻り値
  $result_value['track_name'] = $Track_name;
  $result_value['species'] = $Track_species;
  $result_value['revision'] = $Track_revision;
  $result_value['kind'] = $Track_Type;
  $result_value['description_url'] = $Track_description_url;
  $result_value['species_url'] = $Track_species_url;
  $result_value['optattr_str'] = $Track_optattr;
  $result_value['comment'] = $Track_comment;
  $result_value['color_r'] = $Track_colors[0];
  $result_value['color_g'] = $Track_colors[1];
  $result_value['color_b'] = $Track_colors[2];
  //if ($SQL_MODE == "INSERT") {
  if ($FLAG_RETURN == "INSERT") {
    $result_value['status'] = 'SUCCESS_INSERT';
  } else {
    $result_value['status'] = 'SUCCESS_UPDATE';
  }
  return $result_value;
}
#*******************************************************************************


#-------------------------------------------------------------------------------
# 汎用パース処理
# '$term="()"'または'$term=()'の()内を返す。
# $termが無ければ空を返す。
function ParseLine($line, $term)
{
  $str ="";
  if ((preg_match("/[\s]$term=\"([^\"]+)\"/", $line, $matches)) ||
      (preg_match("/[\s]$term=([^\s]+)/", $line, $matches))) {
    $str = $matches[1];
    $str = trim($str);
    # シングルクォーテーション処理など
    //$str = str_replace("'", "\'", $str);
    $str = pg_escape_string($str);
  }
  
  return $str;
}

#-------------------------------------------------------------------------------
# コメント用パース処理
# '#"()"'または' #()'の()内を返す。
# コメントが無ければ空を返す。
function ParseLineMemo($line)
{
  $str ="";
  if ((preg_match("/[\s]#[\s]*\"([^\"]+)\"/", $line, $matches)) ||
      (preg_match("/[\s]#[\s]*([^\s]+)/", $line, $matches))) {
    $str = $matches[1];
    $str = trim($str);
    # シングルクォーテーション処理など
    //$str = str_replace("'", "\'", $str);
    $str = pg_escape_string($str);
  }
  
  return $str;
}

#-------------------------------------------------------------------------------
# 追加属性 Select型 パース & SQL 実行 INSERT
function DataOptattrSelect($line, $Track_opt_select_name, $Data_ID)
{
  global $LineNum;
  if (isset($Track_opt_select_name) == FALSE) {
    return;
  }
  for ($i = 0; $i < count($Track_opt_select_name); $i++) {
    $strSelectName = $Track_opt_select_name[$i];
    # ダブルクォーテーションで囲んでいる場合と囲んでいない場合がある
    #  囲んでいない場合は空白はない
    # Track行で指定したもの以外も許可する
    if ((preg_match("/[\s]($strSelectName)=\"([^\"]+)\"/", $line, $matches)) ||
        (preg_match("/[\s]($strSelectName)=([^\s]+)/", $line, $matches))) {
      $SQL_query = "
        INSERT INTO OptattrDataSelect 
        (data_id, select_name_id, select_name) 
          VALUES 
          ($Data_ID, 
            (SELECT select_name_id FROM OptattrSelectName 
              WHERE select_name_str='$strSelectName'),
            '$matches[2]');
      ";
      ExecSQL_Insert($SQL_query);
    }
    else {
      # 追加属性の値が無い場合
      WriteLogFile("Not found OptattrSelect '$strSelectName'.", $LineNum, false);
      # ""を入力しておく
      $SQL_query = "
        INSERT INTO OptattrDataSelect 
        (data_id, select_name_id, select_name) 
          VALUES 
          ($Data_ID, 
            (SELECT select_name_id FROM OptattrSelectName 
              WHERE select_name_str='$strSelectName'),
            '');
      ";
      ExecSQL_Insert($SQL_query);
    }
  }
}

#-------------------------------------------------------------------------------
# 追加属性 Real型 パース & SQL 実行 INSERT
function DataOptattrReal($line, $Track_opt_real_name, $Data_ID)
{
  global $LineNum;
  if (isset($Track_opt_real_name) == FALSE) {
    return;
  }
  # ダブルクォーテーションで囲んではいない
  # 行末の場合は右側は空白にはならない
  for ($i = 0; $i < count($Track_opt_real_name); $i++) {
    $strRealName = $Track_opt_real_name[$i];
    if (preg_match("/[\s]($strRealName)=([^\s]+)/", $line, $matches)) {
      $temp_value = (double)$matches[2];
      $SQL_query = "
        INSERT INTO OptattrDataReal 
        (data_id, real_name_id, real_value) 
          VALUES 
          ($Data_ID, 
            (SELECT real_name_id FROM OptattrRealName 
              WHERE real_name_str='$strRealName'), 
            $temp_value);
      ";
      ExecSQL_Insert($SQL_query);
    }
    else {
      # 追加属性の値が無い場合
      WriteLogFile("Not found OptattrReal '$strRealName'.", $LineNum, false);
      # 0.0を入力しておく
      $SQL_query = "
        INSERT INTO OptattrDataReal 
        (data_id, real_name_id, real_value) 
          VALUES 
          ($Data_ID, 
            (SELECT real_name_id FROM OptattrRealName 
              WHERE real_name_str='$strRealName'), 
            0.0);
      ";
      ExecSQL_Insert($SQL_query);
    }
  }
}

#-------------------------------------------------------------------------------
# SQL 実行 INSERT
function ExecSQL_Insert($sql)
{
  global $hDB_cache_create;
  global $LineNum;
#  $sql = ereg_replace(";", "\\;", $sql);   // ; -> \; へ置換
  $res = pg_query($hDB_cache_create, $sql);
  if (pg_result_status($res) != PGSQL_COMMAND_OK) {
    // INSERT 失敗。（Unique制約に引っかかるデータなど）
    $error_msg = pg_result_error($res);
    pg_free_result($res);
    WriteLogFile($error_msg, $LineNum, false);
    WriteLogFile("INSERT failed.\r\nSQL : $sql", 0, true);
    #return FALSE;
  }
  pg_free_result($res);
  return TRUE;
}

#-------------------------------------------------------------------------------
# SQL 実行 UPDATE
function ExecSQL_Update($sql)
{
  global $hDB_cache_create;
  global $LineNum;
#  $sql = ereg_replace(";", "\\;", $sql);   // ; -> \; へ置換
  $res = pg_query($hDB_cache_create, $sql);
  if (pg_result_status($res) != PGSQL_COMMAND_OK) {
    // UPDATE 失敗。（Unique制約に引っかかるデータなど）
    $error_msg = pg_result_error($res);
    pg_free_result($res);
    WriteLogFile($error_msg, $LineNum, false);
    WriteLogFile("UPDATE failed.\nSQL : $sql", 0, true);
    #return FALSE;
  }
  pg_free_result($res);
  return TRUE;
}

#-------------------------------------------------------------------------------
# SQL 実行 ID取得
function ExecSQL_GetID($sql, $id_str)
{
  global $hDB_cache_create;
  global $LineNum;
#  $sql = ereg_replace(";", "\\;", $sql);   // ; -> \; へ置換
  $res = pg_query($hDB_cache_create, $sql);
  $id = -1;
  if (pg_result_status($res) == PGSQL_TUPLES_OK) {
    # 最も値の大きいIDを返す
    for ($i = 0; $i < pg_num_rows($res); $i++) {
      $temp_id = pg_fetch_result($res, $i, $id_str);
      if ($id < $temp_id) {
        $id = $temp_id;
      }
    }
  } else {
    $error_msg = pg_result_error($res);
    WriteLogFile($error_msg, $LineNum, false);
  }
  pg_free_result($res);
  if ($id == -1) {
    WriteLogFile("Could not get id.", $LineNum, false);
    WriteLogFile("SQL : $sql", 0, true);
  }
  return $id;
}

#-------------------------------------------------------------------------------
# ログファイル書き込み
function WriteLogFile($error_msg, $line_num = 0, $bIsQuit = false)
{
  global $IsWriteLog;
  if ($IsWriteLog) {
    global $fp_log;
    if ($line_num > 0) {
      fwrite($fp_log, "data_id $line_num : $error_msg\r\n");
    } else {
      fwrite($fp_log, "$error_msg\r\n");
    }
  }
  if ($bIsQuit) {
    ResException(true);
  }
}

#-------------------------------------------------------------------------------
# DB エラー処理
function ResException($bIsQuit = false)
{
  global $hDB_cache_create;
  global $result_value;
  # ROLLBACK
  pg_query($hDB_cache_create, "ROLLBACK");
  # DB切断
  pg_close($hDB_cache_create);
  $date_now = date("F j, Y, H:i:s");
  WriteLogFile("ROLLBACK : $date_now", 0, false);
  WriteLogFile("------------------------------------------------", 0, false);
  if ($bIsQuit) {
    # ファイル処理
    FileClose();
    //exit;
    $result_value['status'] = 'FALSE';
  }
}

#-------------------------------------------------------------------------------
# ファイル処理
function FileClose()
{
  #global $fp_log, $fp_track;
  global $fp_log;
  # ロック解除
  flock($fp_log, LOCK_UN);
  # file close
  fclose($fp_log);
  #fclose($fp_track);
}

?>