package com.ftinc.si.assist.test;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DBService {
	public static Connection s_connect = null;
	private Statement m_statement = null;
	private boolean m_remote_mode;
	private boolean m_gc = false; //リモートの場合、6分以上の通信途絶で子プロセスを自動終了させるかどうかのフラグ。

	public DBService(boolean isRemote, boolean gc) {
		m_remote_mode = isRemote;
		m_gc = gc;
	}

	public boolean init(String str, String uid, String pwd) {
		//DBサーバを別名に変える必要があるなら置き換える。
		str = Tool.convert2Alias(str);

		if (m_remote_mode) {
			return createDBMediator(str, uid, pwd);
		} else {
			try {
				s_connect = DriverManager.getConnection(str, uid, pwd);
				s_connect.setAutoCommit(false);
			} catch (SQLException e) {
				s_connect = null;
				Tool.alertMSG(null, e.getMessage());
				return false;
			}
		}
		return true;
	}

	//テーブル定義が済かどうかを調べる。
	public boolean isReady() {
		Boolean t_b = false;
		if (m_process == null) {
			try {
				String t_sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
				ResultSet t_res = select(t_sql);
				while (t_res.next()) {
					String t_name = t_res.getString("table_name");
					if ("tbl_TestCaseRecord".equals(t_name)) {
						t_b = true;
					}
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_result;
			try {
				t_result = sendCommand("isReady", "");
				t_b = (Boolean)Tool.getObjectfromJSON(Boolean.class, t_result);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_b;
	}

	public void destroy() {
		if (m_process == null) {
			try {
				if (s_connect != null) {
					s_connect.close();
					s_connect = null;
				}
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			try {
				sendCommand("destroy", "");
				m_resultReader.close();
				m_commandWriter.close();
				m_process.destroy();
			} catch (IOException | SQLException e) {
				//ここに来るのは、不正なSQLの処理でSQLパイプが壊れている場合。
				//もう終わるので警告は出さない。
			}
		}
	}

	private ResultSet select(String q) {
		try {
			m_statement = s_connect.createStatement();
			return m_statement.executeQuery(q);
		} catch (SQLException e) {
			Tool.alertMSG(null, e.getMessage());
		}
		return null;
	}


	private void close(ResultSet r) throws SQLException {
		if (m_process == null) {
			if (r != null) {
				r.close();
			}
			if (m_statement != null) {
				m_statement.close();
				m_statement = null;
			}
		} else {
			try {
				sendCommand("close", "");
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
	}

	//injection対策
	private String _esq(String str) {
		return str.replaceAll(Pattern.quote("\\"), "");
	}

	public JSONRecord getJSONRecord(String cname, String name, String version) {
		String t_sql = "select * from \"tbl_JSONRecord\" where ";
		boolean b_added = false;
		if (name != null && name .length() > 0) {
			if (name.indexOf("%") >= 0) {
				t_sql += "\"Name\" like '" + _esq(name) + "' ";
			} else {
				t_sql += "\"Name\"='" + _esq(name) + "' and \"ClassName\"='" + _esq(cname) + "'";
			}
			b_added = true;
		}
		if (cname != null && cname.length() > 0) {
			if (b_added) {
				t_sql += " and ";
			}
			if (cname.indexOf("%") >= 0) {
				t_sql += "\"ClassName\" like '" + _esq(cname) + "'";
			} else {
				t_sql += "\"ClassName\"='" + _esq(cname) + "'";
			}
		}

		if (version != null && version.length() > 0) {
			//コマンドで明に指定されている場合。
			t_sql += " AND \"Version\"='" + version + "'";
		}

		JSONRecord t_rec = null;
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				while (t_res.next()) {
					t_rec = new JSONRecord(0);
					t_rec.read(t_res);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_result;
			try {
				t_result = sendCommand("getJSONRecord", t_sql);
				t_rec = (JSONRecord)Tool.getObjectfromJSON(JSONRecord.class, t_result);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_rec;
	}

	// 編集用のJTableに表示するため全てのレコードをString[][]に替える。
	// where 以下は前方一致。枝番があることを想定している。
	@SuppressWarnings("unchecked")
	public ArrayList<JSONRecord> getJSONRecWhere(String cname, String subname, String version) {
		String t_sql = "select * from \"tbl_JSONRecord\"";

		if (version != null && version.length() > 0) {
			//コマンドで明に指定されている場合。
			t_sql += " AND \"Version\"='" + version + "'";
		}

		if (cname != null || subname != null) {
			t_sql += " where ";
			String t1_sql = "";
			if (cname != null) {
				t1_sql += "\"ClassName\" like '" + _esq(cname) + "'";
			}
			String t2_sql = "";
			if (subname != null) {
				t2_sql += "\"Name\" like '" + _esq(subname) + "'";
			}
			String t3_sql = "";
			if (t1_sql.length() > 0 && t2_sql.length() > 0) {
				t3_sql = " and ";
			}
			t_sql += t1_sql + t3_sql + t2_sql;
		}

		ArrayList<JSONRecord> t_list = new ArrayList<JSONRecord>();
		if (m_process == null) {

			ResultSet t_res = select(t_sql);
			try {
				if (t_res.next()) {
					JSONRecord t_rec = new JSONRecord(0);
					t_rec.read(t_res);
					t_list.add(t_rec);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getJSONRecWhere", t_sql);
				return (ArrayList<JSONRecord>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}

	public AssertRecord getAssert(int testID) {
		String t_sql = "select * from \"tbl_AssertRecord\" where \"ID\"='" + Integer.valueOf(testID).toString() + "'";
		AssertRecord t_rec = null;
		if (m_process == null) {
			try {
				ResultSet t_res = select(t_sql);
				while ( t_res.next() ) {
					t_rec = new AssertRecord(0);
					t_rec.read(t_res);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getAssert", t_sql);
				t_rec = (AssertRecord)Tool.getObjectfromJSON(AssertRecord.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_rec;
	}

	public ObjectRecord getObjectRecord(ObjectRecord ord) {
		String t_sql = "select * from \"tbl_ObjectRecord\" where \"ID\"='" + ord._id() + "'";
		ObjectRecord t_rec = null;
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				if (t_res.next()) {
					t_rec = new ObjectRecord(0);
					t_rec.read(t_res);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getObjectRecord", t_sql);

				t_rec = (ObjectRecord)Tool.getObjectfromJSON(ObjectRecord.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_rec;
	}

	//strはid+$[0-9]+。指定のTestCase以外で、この名前を消したり変更した時に影響が及ぶObjectRecordを探す。
	//stackとJSONのいずれかで参照しているかどうか判定して探す。
	@SuppressWarnings("unchecked")
	public ArrayList<String> getReferringObjectList(String casename, String objid) {
		ArrayList<String> t_list = new ArrayList<String>();

		String t_sql = "select * from \"tbl_ObjectRecord\" where ";
		t_sql += "\"TestCase\"<>'" + _esq(casename) + "' and (\"Stack\" like '" + objid;//stackで参照している場合。
		t_sql += "%' or \"JSON\" like '%" + objid + "$%')";//JSONの中で参照している場合。

		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				while ( t_res.next() ) {
					t_list.add(t_res.getString("ID"));
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			try {
				t_list = (ArrayList<String>)Tool.getObjectfromJSON(ArrayList.class, sendCommand("getReferringObjectList", t_sql));
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}

	// 同一のTestCaseの中で、testIDより小さくて、他の引数（nullは入ってこない）が合致しているもの。引数の再利用に使用する。
	@SuppressWarnings("unchecked")
	public ArrayList<ObjectRecord> getReusableObjectRecord(String caseName, String className, int testID, int begin) {
		String t_sql = "select * from \"tbl_ObjectRecord\" where \"TestCase\"='" + caseName + "' and \"ClassName\"='"
				+ className + "' and \"TestID\" <'" + Integer.toString(testID) + "' and \"TestID\" >='" + Integer.toString(begin) + "'";

		ArrayList<ObjectRecord> t_list = new ArrayList<ObjectRecord>();
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {

				while ( t_res.next() ) {
					ObjectRecord t_rec = new ObjectRecord(0);
					t_rec.read(t_res);
					t_list.add(t_rec);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getReusableObjectRecord", t_sql);
				return (ArrayList<ObjectRecord>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}


	// Fake
	public FakeMethodRecord getMethodSource(String className, String methodName, String[] argtypes, String version) {
		FakeMethodRecord frd = new FakeMethodRecord(0);

		String a_types = Tool.getJSONfromObject(argtypes);

		String sql_where = " where \"ClassName\"='" + className + "' AND \"MethodName\"='" + methodName + "' AND \"ArgTypes\"='" + a_types + "'";
		if (version != null && version.length() > 0) {
			//コマンドで明に指定されている場合。
			sql_where += " AND \"Version\"='" + version + "'";
		}

		String t_sql = "select * from \"tbl_FakeMethodRecord\"" + sql_where;

		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				while ( t_res.next() ) {
					frd.read(t_res);
				}
				close(t_res);
				if (frd.source == null) {
					return null;
				}

			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
				return null;
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getMethodSource", t_sql);
				frd = (FakeMethodRecord)Tool.getObjectfromJSON(FakeMethodRecord.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return frd;
	}

	@SuppressWarnings("unchecked")
	public ArrayList<FakeMethodRecord> getFakeMethods(String className, String version) {
		String t_sql = "select * from \"tbl_FakeMethodRecord\" where \"ClassName\"='" + className + "'";

		if (version != null && version.length() > 0) {
			//コマンドで明に指定されている場合。
			t_sql += " AND \"Version\"='" + version + "'";
		}

		ArrayList<FakeMethodRecord> t_list = new ArrayList<FakeMethodRecord>();
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				while ( t_res.next() ) {
					FakeMethodRecord frd = new FakeMethodRecord(0);
					frd.read(t_res);
					t_list.add(frd);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getFakeMethods", t_sql);
				return (ArrayList<FakeMethodRecord>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}


	@SuppressWarnings("unchecked")
	public ArrayList<FakeMethodRecord> getAllFakes(String version) {
		String t_sql = "select * from \"tbl_FakeMethodRecord\"";

		if (version != null && version.length() > 0) {
			//コマンドで明に指定されている場合。
			t_sql += " AND \"Version\"='" + version + "'";
		}

		ArrayList<FakeMethodRecord> t_list = new ArrayList<FakeMethodRecord>();
		if (m_process == null) {
			ResultSet t_res = select(t_sql);

			try {
				while ( t_res.next() ) {
					FakeMethodRecord frd = new FakeMethodRecord(0);
					frd.read(t_res);
					t_list.add(frd);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getAllFakes", t_sql);
				return (ArrayList<FakeMethodRecord>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return new ArrayList<FakeMethodRecord>();
	}

	//同一TestCaseの中で、指定の返却型をもつTestCommandのIDをリスト化する。
	@SuppressWarnings("unchecked")
	public ArrayList<String> getTestIDsByReturnType(String caseName, String className, int begin, int end) {
		String t_sql = "select * from \"tbl_TestCommandRecord\" where \"TestCase\" like '" + caseName + "' and \"ReturnType\" like '" + className + "'";
		t_sql += " and \"ID\">='" + Integer.toString(begin) + "' and \"ID\"<'" + Integer.toString(end) + "'";

		ArrayList<String> t_list = new ArrayList<String>();
		if (m_process == null) {
			ResultSet t_res = select(t_sql);

			try {
				while ( t_res.next() ) {
					t_list.add(t_res.getString("ID"));
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getTestIDsByReturnType", t_sql);
				t_list = (ArrayList<String>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}

	//新規のTestCase。一つ当たりTool.unitSize（デフォルト。増やしてもよい）のtestidを持つ。増やすときは同じ名前で連続で獲得。
	//beginは常に最後尾の次を設定する。
	public TestCaseRecord getNewTestCase(String version) {
		//Endが最大のTestCaseを取得し、次の範囲を計算する。
		String sub_sql = "select max(\"End\") from \"tbl_TestCaseRecord\"";
		String t_sql = "select * from \"tbl_TestCaseRecord\" where \"End\"=(" + sub_sql + ")";

		if (m_process == null) {
			ResultSet t_res = select(t_sql);

			int n = 0;
			try {
				while ( t_res.next() ) {
					n = t_res.getInt("End");
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}

			TestCaseRecord t_rec = new TestCaseRecord(2); //新規
			t_rec.begin = n + 1;
			t_rec.end = n + TestCaseRecord.s_unitSize;
			t_rec.groupCode = Tool.group_code;
			t_rec.version = version;
			return t_rec;
		} else {
			String t_json;
			try {
				t_json = sendCommand("getNewTestCase", t_sql);
				return (TestCaseRecord)Tool.getObjectfromJSON(TestCaseRecord.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return null;
	}


	//あいまいな名前と範囲を指定し、TestCaseのリストを取得する。
	@SuppressWarnings("unchecked")
	public ArrayList<TestCaseRecord> getTestCaseList(String substr, int begin, int end, String gcode, String version) {
		String t_sql = "select * from \"tbl_TestCaseRecord\" ";
		if (substr != null) {
			t_sql += "where \"Name\" like '" + _esq(substr) + "' ";
			if (end > 0) {
				t_sql += " AND \"Begin\" <= '" + Integer.valueOf(end).toString() + "' ";
			}
			if (begin > 0) {
				t_sql += " AND \"End\" >= '" + Integer.valueOf(begin).toString() + "'";
			}
		}
		if (gcode.length() > 0) {
			t_sql += " AND \"GroupCode\"='" + gcode + "'";
		}
		if (version != null && version.length() > 0) {
			//コマンドで明に指定されている場合。
			t_sql += " AND \"Version\"='" + version + "'";
		}
		t_sql += " order by \"Begin\"";

		ArrayList<TestCaseRecord> t_list = new ArrayList<TestCaseRecord>();
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			TestCaseRecord t_rec = null;
			try {
				while ( t_res.next() ) {
					t_rec = new TestCaseRecord(0);
					t_rec.read(t_res);
					t_list.add(t_rec);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getTestCaseList", t_sql);
				t_list = (ArrayList<TestCaseRecord>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}


	//Include参照している、TestCaseのリストを取得する。
	@SuppressWarnings("unchecked")
	public ArrayList<String> getReferringTestCaseList(String name, String version) {
		String t_sql = "select * from \"tbl_TestCaseRecord\" ";
		if (name != null) {
			t_sql += "where \"Include\" = '" + _esq(name) + "'";
		}
		if (version != null && version.length() > 0){
			t_sql += " AND \"Version\"='" + version + "'";
		}

		t_sql += " order by \"Begin\"";

		ArrayList<String> t_list = new ArrayList<String>();
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				while ( t_res.next() ) {
					t_list.add(t_res.getString("Name"));
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getReferringTestCaseList", t_sql);
				t_list = (ArrayList<String>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}

	//IDからTestCommandを取得する。
	public TestCommandRecord getTestCmd(String id) {
		String t_sql = "select * from \"tbl_TestCommandRecord\" where \"ID\"=" + id;

		TestCommandRecord t_rec = null;
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				while ( t_res.next() ) {
					t_rec = new TestCommandRecord(0);
					t_rec.read(t_res);
				}
				close(t_res);
				return t_rec;
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getTestCmd", t_sql);

				return (TestCommandRecord)Tool.getObjectfromJSON(TestCommandRecord.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_rec;
	}

	@SuppressWarnings("unchecked")
	public ArrayList<TestCommandRecord> getTestCmdList(String uname, String cname, String mname, String version) {
		ArrayList<TestCommandRecord> t_list = new ArrayList<TestCommandRecord>();

		if (uname == null || uname.length() == 0) uname = "%";
		if (cname == null || cname.length() == 0) cname = "%";
		if (mname == null || mname.length() == 0) mname = "%";

		ArrayList<TestCaseRecord> case_list = getTestCaseList(uname, 0, 0, "", version);
		for (int i = 0; i < case_list.size(); i++) {
			TestCaseRecord c_rec = case_list.get(i);

			String t_sql = "select * from \"tbl_TestCommandRecord\" "
					+ "where \"TestCase\" like '" + uname
					+ "' and \"ClassName\" like '" + cname + "' and \"MethodName\" like '" + mname
					+ "' and \"ID\">='" + Integer.toString(c_rec.begin) + "' and \"ID\"<'" + Integer.toString(c_rec.end) + "'"
					+ " order by \"ID\"";

			if (m_process == null) {
				ResultSet t_res = select(t_sql);
				TestCommandRecord t_rec = null;
				try {
					while ( t_res.next() ) {
						t_rec = new TestCommandRecord();
						t_rec.read(t_res);
						t_list.add(t_rec);
					}
					close(t_res);
				} catch (SQLException e) {
					Tool.alertMSG(null, e.getMessage());
				}
			} else {
				String t_json;
				try {
					t_json = sendCommand("getTestCmdList", t_sql);
					return (ArrayList<TestCommandRecord>)Tool.getObjectfromJSON(ArrayList.class, t_json);
				} catch (IOException | SQLException e) {
					Tool.alertMSG(null, e.getMessage());
				}
			}
		}
		return t_list;
	}

	//Excelからsnapshotを検索する際に使用する。
	@SuppressWarnings("unchecked")
	public ArrayList<TestCommandRecord> getSnapshotCmds(String caseName, String className, String mName, String version) {
		String t_sql = "select * from \"tbl_TestCommandRecord\" where \"ID\" in ";
		t_sql += "(select \"Snapshot\" from  \"tbl_TestCaseRecord\" ";

		if (caseName == null || caseName.length() == 0) caseName = "%";
		if (className == null || className.length() == 0) className = "%";
		if (mName == null || mName.length() == 0) mName = "%";

		if (version != null && version.length() > 0){
			version = " AND \"Version\"='" + version + "'";
		}

		t_sql += "where \"Name\" like '" + caseName + "' and \"Snapshot\" > 0 " + version + ") "
				+ "and \"ClassName\" like '" + className + "' and \"MethodName\" like '" + mName + "'";

		ArrayList<TestCommandRecord> t_list = new ArrayList<TestCommandRecord>();
		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			TestCommandRecord t_rec = null;
			try {
				while ( t_res.next() ) {
					t_rec = new TestCommandRecord(0);
					t_rec.read(t_res);
					t_list.add(t_rec);
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
				return null;
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getTestCmdList", t_sql);
				t_list = (ArrayList<TestCommandRecord>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}


	//caseName外でTestCommand(id）を参照しているTestCommandのリストを取得する。
	@SuppressWarnings("unchecked")
	public ArrayList<String> getReferringTestCmdList(String caseName, String id) {
		ArrayList<String> t_list = new ArrayList<String>();

		String t_sql = "select * from \"tbl_TestCommandRecord\" "
				+ "where (\"ArgTypes\" like '%," +  id + "$%' or \"Subject\" like '%," +  id + "$%') and \"TestCase\"<>'" + caseName + "' order by \"ID\"";

		if (m_process == null) {
			ResultSet t_res = select(t_sql);
			try {
				while ( t_res.next() ) {
					t_list.add(t_res.getString("ID") + "@" + t_res.getString("TestCase"));
				}
				close(t_res);
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
			if (t_list.size() == 0) {
				return null;
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("getReferringTestCmdList", t_sql);
				t_list = (ArrayList<String>)Tool.getObjectfromJSON(ArrayList.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_list;
	}

	// Common。第二引数はコミットするかしないかの指定。
	public void updateRecordBySQL(String t_sql, boolean bCommit) throws SQLException {
		if (t_sql == null) {
			return;
		}
		if (m_process == null) {
			try {
				if (m_statement == null) {
					m_statement = s_connect.createStatement();
				}
				m_statement.execute(t_sql);
				if (bCommit) {
					commit();
				}
			} catch (SQLException e) {
				Tool.alertMSG(null, "@DBService#updateRecordBySQL, SQLException=" + e.getMessage() + "\n sql=" + t_sql);
				if (bCommit) {
					rollback();
				}
			} finally {
				if (bCommit) {
					close(null);
				}
			}
		} else {
			try {
				sendCommand("updateBySQL", t_sql + "\t" + Boolean.toString(bCommit));
			} catch (IOException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
	}

	private void commit() throws SQLException {
		if (m_process == null) {
			s_connect.commit();
		} else {
			try {
				sendCommand("commit", null);
			} catch (IOException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
	}


	private void rollback() throws SQLException {
		if (m_process == null) {
			s_connect.rollback();
		} else {
			try {
				sendCommand("rollback", null);
			} catch (IOException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}

	}

	public boolean saveTestCase(TestCaseRecord rec, ArrayList<Record> removings, HashMap<Integer, TestCommandRecord> tests,	HashMap<String, ObjectRecord> objs,
		HashMap<Integer, AssertRecord> asserts) throws SQLException {

		//group_codeがある時、別グループなら権限なし。
		if (Tool.group_code.length() != 0 && !rec.getGroupCode().equals(Tool.group_code)) {
			return false;
		}

		boolean isOK = true;
		try {
			String t_sql = rec.getUpdateSQL();
			if (t_sql != null) {
				updateRecordBySQL(t_sql, false);
			}

			//最初に削除する。
			if (removings != null) {
				for (int i = 0; i < removings.size(); i++) {
					updateRecordBySQL(removings.get(i).getUpdateSQL(), false);
				}
			}

			//以下変更したものを更新する。
			for(Map.Entry<Integer, TestCommandRecord> entry1 : tests.entrySet()) {
				if (entry1.getValue().isUpdated()) {
					updateRecordBySQL(entry1.getValue().getUpdateSQL(), false);
				}
			}
			for(Map.Entry<String, ObjectRecord> entry2 : objs.entrySet()) {
				ObjectRecord t_rec = entry2.getValue();
				if (t_rec.isUpdated()) {
					updateRecordBySQL(entry2.getValue().getUpdateSQL(), false);
				}
			}
			for(Map.Entry<Integer, AssertRecord> entry3 : asserts.entrySet()) {
				if (entry3.getValue().isUpdated()) {
					updateRecordBySQL(entry3.getValue().getUpdateSQL(), false);
				}
			}
			commit();
		} catch (SQLException e) {
			Tool.alertMSG(null, "@saveTestCase, SQLException=" + e.getMessage());
			rollback();
			isOK = false;
		} catch (Exception e) {
			Tool.alertMSG(null, "@saveTestCase, RuntimeException=" + e.getMessage());
			rollback();
			isOK = false;
		} catch (Error e) {
			Tool.alertMSG(null, "@saveTestCase, Error=" + e.getMessage());
			rollback();
			isOK = false;
		} finally {
			close(null);
		}
		return isOK;
	}

	//テーブル群が存在するかの確認。一番データ数の少ないテーブルにアクセスしてみて判定する。
	public int countCase() {
		String t_sql = "select count(*) from \"tbl_TestCaseRecord\"";
		if (m_process == null) {
			ResultSet t_res;
			try {
				int n = 0;
				m_statement = s_connect.createStatement();
				t_res = m_statement.executeQuery(t_sql);
				if (t_res.next()) {
					n = t_res.getInt(1);
				}
				close(t_res);
				return n;
			} catch (SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		} else {
			String t_json;
			try {
				t_json = sendCommand("countCase", t_sql);
				return Integer.valueOf(t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return -1;
	}

	//テーブル未定義の場合、DBに定義する。
	public void createTable() {
		String err_msg = "";
		if (m_process == null) {
			ArrayList<String> t_list = Tool.createTableSQL();
			if (t_list.size() > 0) {
				try {
					m_statement = s_connect.createStatement();
					for (int i = 0; i < t_list.size(); i++) {
						err_msg = t_list.get(i);
						m_statement.executeUpdate(t_list.get(i));
					}
					commit();

				} catch (SQLException e) {
					Tool.alertMSG(null, e.getMessage() + "\nSQL=\n" + err_msg);
					try {
						rollback();
					} catch (SQLException e1) {
						//rollbackするまでもなかった。
						Tool.logIfDebug(e, "@DBService#createTable");
					}
				} finally {
					try {
						close(null);
					} catch (SQLException e) {
						Tool.logIfDebug(e, "@DBService#createTable");
					}
				}
			}
		} else {
			try {
				sendCommand("createTable", null);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
	}

	//指定したTestCase（自身を含む）配下のレコードを消去する。
	public void removeCase(String label, String version) throws SQLException {
		ArrayList<TestCaseRecord> t_list = getTestCaseList(label, 0, 0, Tool.group_code, version);

		if (t_list.size() > 0) {
			try {
				for (int i = 0; i < t_list.size(); i++) {
					TestCaseRecord t_rec = t_list.get(i);
					t_rec.removed = true;

					String t_sql = t_rec.getUpdateSQL();
					//最初に配下のレコードを消去する。TestCaseの検索を行うので、TestCaseは、この時点で存在しておく必要がある。
					clearCase(t_rec, false);

					//TestCaseを空にする。
					updateRecordBySQL(t_sql, false);
				}
				commit();
			} catch (SQLException e) {
				Tool.alertMSG(null, "@DBService#removeCase, SQLException=" + e.getMessage());
				rollback();
			} finally {
				try {
					close(null);
				} catch (SQLException e) {
					Tool.logIfDebug(e, "@DBService#removeCase");
				}
			}
		}
	}

	//指定したTestCase(自身は含まない)配下のレコードを消去する。
	public void clearCase(TestCaseRecord rec, boolean bCommit) throws SQLException {
		if (rec != null) {
			//group_codeを考慮して消去する。
			String t_sql;
			try {
				ArrayList<TestCommandRecord> t_cmds = getTestCmdList(rec.name, null, null, rec.version);//TestIDは1の位置．

				for (int i = 0; i < t_cmds.size(); i++) {
					t_sql = "delete from \"tbl_TestCommandRecord\" where \"ID\"='" + Integer.toString(t_cmds.get(i).id) + "'";
					updateRecordBySQL(t_sql, false);

					t_sql = "delete from \"tbl_ObjectRecord\" where \"TestID\"='" + Integer.toString(t_cmds.get(i).id) + "'";
					updateRecordBySQL(t_sql, false);

					t_sql = "delete from \"tbl_AssertRecord\" where \"ID\"='" + Integer.toString(t_cmds.get(i).id) + "'";
					updateRecordBySQL(t_sql, false);
				}
				if (bCommit && t_cmds.size() > 0) {
					commit();
				}
			} catch (SQLException e) {
				Tool.alertMSG(null, "@DBService#clearCase, SQLException=" + e.getMessage());
				if (bCommit) {
					rollback();
				}
			} finally {
				try {
					if (bCommit) {
						close(null);//statementの後始末。
					}
				} catch (SQLException e) {
					Tool.logIfDebug(e, "@DBService#clearCase");
				}
			}
		}
	}


	//現在のCaseの完成度を調べる。
	public String getCaseStatus(String casename, String begin, String end) {
		String t_result = "";
		String num_all = "";
		String num_incomp = "";

		ArrayList<String> sqls = new ArrayList<String>();
		String t_sql = "select count(*) from \"tbl_TestCommandRecord\" where \"TestCase\"='" + casename + "' and \"ID\">='" + begin + "' and \"ID\"<='" + end + "'";
		sqls.add(t_sql);
		sqls.add(t_sql + " and \"ArgStatus\" like '%false%'");

		t_sql = "select count(*) from \"tbl_ObjectRecord\" where \"TestCase\"='" + casename + "' and \"TestID\">='" + begin + "' and \"TestID\"<='" + end + "'";
		sqls.add(t_sql);
		sqls.add(t_sql + " and \"Completed\"='false'");

		t_sql = "select count(*) from \"tbl_AssertRecord\" where \"ID\">='" + begin + "' and \"ID\"<='" + end + "'";
		sqls.add(t_sql);
		sqls.add(t_sql + " and \"Completed\"='false'");

		if (m_process == null) {
			ResultSet t_res;
			try {
				m_statement = s_connect.createStatement();

				t_res = m_statement.executeQuery(sqls.get(0));
				if (t_res.next()) {
					num_all = t_res.getString(1);
				} else {
					num_all = "0";
				}
				t_res.close();
			} catch (SQLException e) {
				Tool.logIfDebug(e, "@DBService#getCaseStatus");
			}

			try {
				t_res = m_statement.executeQuery(sqls.get(1));
				if (t_res.next()) {
					num_incomp = t_res.getString(1);
				} else {
					num_incomp = "0";
				}
				t_res.close();
			} catch (SQLException e) {
				Tool.logIfDebug(e, "@DBService#getCaseStatus");
			}
			t_result = "TestCommandRecord(incompleted/all) = " + num_incomp + " / " + num_all + "\n";


			try {
				t_res = m_statement.executeQuery(sqls.get(2));
				if (t_res.next()) {
					num_all = t_res.getString(1);
				} else {
					num_all = "0";
				}
				t_res.close();
			} catch (SQLException e) {
				Tool.logIfDebug(e, "@DBService#getCaseStatus");
			}

			try {
				t_res = m_statement.executeQuery(sqls.get(3));
				if (t_res.next()) {
					num_incomp = t_res.getString(1);
				} else {
					num_incomp = "0";
				}
				t_res.close();
			} catch (SQLException e) {
				Tool.logIfDebug(e, "@DBService#getCaseStatus");
			}
			t_result += "ObjectRecord(incompleted/all) = " + num_incomp + " / " + num_all + "\n";

			try {
				t_res = m_statement.executeQuery(sqls.get(4));
				if (t_res.next()) {
					num_all = t_res.getString(1);
				} else {
					num_all = "0";
				}
				t_res.close();
			} catch (SQLException e) {
				Tool.logIfDebug(e, "@DBService#getCaseStatus");
			}

			try {
				t_res = m_statement.executeQuery(sqls.get(5));
				if (t_res.next()) {
					num_incomp = t_res.getString(1);
				} else {
					num_incomp = "0";
				}
				t_res.close();
			} catch (SQLException e) {
				Tool.logIfDebug(e, "@DBService#getCaseStatus");
			}
			try {
				close(null);
			} catch (SQLException e) {
				Tool.logIfDebug(e, "@DBService#getCaseStatus");
			}
			t_result += "AssertRecord(incompleted/all) = " + num_incomp + " / " + num_all + "\n";
		} else {
			String t_json;
			try {
				t_json = sendCommand("getCaseStatus", Tool.getJSONfromObject(sqls));
				t_result = (String)Tool.getObjectfromJSON(String.class, t_json);
			} catch (IOException | SQLException e) {
				Tool.alertMSG(null, e.getMessage());
			}
		}
		return t_result;
	}

	/////////////
	//子プロセス経由でDBアクセスする。
	private OutputStreamWriter m_commandWriter = null;
	private BufferedReader m_resultReader = null;
	private Process m_process = null;

	//子プロセスでのリモートDBの起動、初期化
	private boolean createDBMediator(String connStr, String uid, String pwd) {
		// java -jar jarfile  //jar実行形式により、別プロセスでも、起動以後のパスの心配は少なくなる。
		String j_path = Tool.s_userdir;

		String t_connect = "-connectStr:" + Tool.esq4CMD(connStr);
		String t_gc = "-gc:" + Boolean.toString(m_gc).toLowerCase();

		ProcessBuilder t_pb = new ProcessBuilder("java", "-jar", Tool.s_jarname + ".jar", "-remoteDB", t_connect, "-uid:" + Tool.esq4CMD(uid), "-pwd:" + Tool.esq4CMD(pwd), t_gc);
		if (j_path != null && j_path.length() > 0) {
			File t_dir = new File(j_path);
			t_pb.directory(t_dir);//ftesting.jarのあるフォルダに移動
		}

		try {
			t_pb.redirectErrorStream(true);

			m_process = t_pb.start();

			//文字化けを防止するため、送受信のコード系を合わせる。相手はDBMediatorなのでUTF-8で送ってくる。
			//BufferedReaderに任せれば、きちんと文字列に変換してくれる。
			m_resultReader = new BufferedReader(new InputStreamReader(m_process.getInputStream(), "UTF-8"));
			m_commandWriter = new OutputStreamWriter(m_process.getOutputStream(), "UTF-8");

			//初期化の結果を受け取る。DBMediator.init()で返すメッセージを受け取る。
			String t_result = sendCommand(null, null);
			if (t_result != null && t_result.length() > 0) {
				if (t_result.matches("^<(fatal|error).*$")) {
				}
				//OK。というかエラーなら例外が発行されているはず。
			}
		} catch (IOException | SQLException e) {
			Tool.alertMSG(null, new String(e.getMessage().getBytes()) + " in DBService#createDBMediatorB");
			m_process.destroy();
			m_process = null;
			return false;
		}
		return true;
	}

	//子プロセス経由でDBアクセスする。テスト対象メソッドの中でDBアクセスするケースが多いので、干渉を防止する。
	private String sendCommand(String cmd, String arg_string) throws IOException, SQLException {
		if (cmd != null) {
			String t_cmd = "<" + cmd + ">" + arg_string + "</" + cmd + ">\n";
			m_commandWriter.write(t_cmd);
			if (!cmd.equals("destroy")) {
				//destroyコマンドの場合、exit済みなのでパイプは切れている。
				try {
					m_commandWriter.flush();
				} catch (Exception e) {
					//ここに来る場合、そもそもプロセスが正常に起動されていない可能性が高い。
					Tool.alertMSG(null, new String(e.getMessage().getBytes()) + " in DBService#sendCommand");
				}
			} else {
				//destroy実行後なので、応答があるはずもなく、すぐに返る。
				return null;
			}
		}

		//falseは読み終わってもプロセスが終了していないことを意味する。特定書式の結果文字列が返る。
		String t_result = Tool.readResultFromStream(m_resultReader, false);

		if (t_result != null && t_result.length() > 0) {
			//""をエスケープする。その段階で途中の改行はなくなる。
			try {
				ArrayList<String> esq_result = Fson.escapeBraket(t_result, "\"");

				final Pattern t_pat = Pattern.compile("^\\s*<(\\w+)>([\\s\\S]*)</(\\w+)>\\s*$");
				Matcher t_m = t_pat.matcher(esq_result.get(0));
				if (t_m.find()) {
					//前と後ろのタグ名が同じ。
					if (t_m.group(1).equals(t_m.group(3))) {
						String response = t_m.group(2);
						response = Fson.restoreBraket(response, esq_result);//元に戻す。

						if ("result".equals(t_m.group(1))) {
							//結果の完成形なので抜ける。
							return response;
						} else if ("error".equals(t_m.group(1)) || "fatal".equals(t_m.group(1))) {
							//エラーである。
							throw new SQLException(response);
						}
					}
				}
			} catch (ParseException e) {
				Tool.alertMSG(null, new String(e.getMessage().getBytes()) + " in DBService#sendCommand");
			}
		}
		return null;
	}

	//Remote専用。
	@SuppressWarnings("unchecked")
	public ArrayList<HashMap<String, String>> selectBySQL(String sql) throws Exception {
		try {
			String t_result = sendCommand("selectBySQL", sql);
			if (t_result != null) {
				return (ArrayList<HashMap<String, String>>)Tool.getObjectfromJSON(ArrayList.class, t_result);
			}
		} catch (Exception e) {
			Tool.alertMSG(null, e.getMessage() + "\n SQL=" + sql);
		}
		throw new IOException("");
	}
}
