/// <summary>
/// return the sepecific path according to id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int[, ] getPathByID(int id)
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add("Step");
selectvalue.Add("X");
selectvalue.Add("Y");
string tableName = "Path";
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("SolutionID", id.ToString());
int[, ] res = getPathSize(id);
dataReader =
ExecuteQuery(sql.Select(selectvalue, tableName, condition));
while (dataReader.HasRows)
{
if (dataReader.Read())
{
int step = dataReader.GetInt32(0) - 1;
int x = dataReader.GetInt32(1);
int y = dataReader.GetInt32(2);
res[step, 0] = x;
res[step, 1] = y;
}
}
return(res);
}
/// <summary>
/// return the sepecific Command according to id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string[] getCommandByID(int id)
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add("Step");
selectvalue.Add("Command");
string tableName = "Commands";
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("ID", id.ToString());
string[] res = getCommandsSize(id);
dataReader =
ExecuteQuery(sql.Select(selectvalue, tableName, condition));
// Debug.Log(dataReader.Read());
while (dataReader.HasRows)
{
if (dataReader.Read())
{
int index = dataReader.GetInt32(0) - 1;
res[index] = dataReader.GetString(1);
}
}
return(res);
}
/// GetSensorMatrixById
/// <summary>
/// return the sensor according to id
/// </summary>
/// <param name="sensorId"></param>
/// <returns></returns>
public int[,] GetSensorMatrixById(int sensorId, int timestamp)
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add("Content");
string tableName = Constants.TABLE_SENSOR;
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add(Constants.COLUMN_ID, sensorId.ToString());
condition.Add(Constants.SENSOR_TIMESTAMP, timestamp.ToString());
dataReader =
ExecuteQuery(sql.Select(selectvalue, tableName, condition));
string res = "";
if (dataReader.Read())
{
res = dataReader.GetString(0);
}
string[] split1 = res.Split(';');
int num1 = split1.Length;
int num2 = split1[0].Split(',').Length;
int[,] result = new int[num1, num2];
for (int i = 0; i < split1.Length; i++)
{
string[] split2 = split1[i].Split(',');
for (int j = 0; j < split2.Length; j++)
{
result[i, j] = Convert.ToInt32(split2[j]);
}
}
return(result);
}
public string[][] GetAllMazeRecord()
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add(Constants.COLUMN_ID);
selectvalue.Add(Constants.COLUMN_NODE);
selectvalue.Add(Constants.COLUMN_CONNECTTO);
selectvalue.Add(Constants.COLUMN_DIRECTION);
Dictionary <string, string> condition = new Dictionary <string, string>();
List <string[]> res = new List <string[]>();
dataReader = ExecuteQuery(sql.Select(selectvalue, Constants.TABLE_MAZE, condition));
while (dataReader.HasRows)
{
if (dataReader.Read())
{
res.Add(new string[4] {
dataReader[Constants.COLUMN_ID].ToString(), dataReader[Constants.COLUMN_NODE].ToString(), dataReader[Constants.COLUMN_CONNECTTO].ToString(), dataReader[Constants.COLUMN_DIRECTION].ToString()
});
}
}
return(res.ToArray());
}
/// SetSensorMatrixById
/// <summary>
/// Set the environment record by the sensor.
/// Could be any size.
/// </summary>
/// <param name="sensorId"></param>
/// /// <param name="matrix"></param>
/// <returns></returns>
public int SetSensorMatrixById(int timestamp, int sensorId, int[,] matrix)
{
SqlEncap sql = new SqlEncap();
int result = Constants.RESPONSE_CODE_SUCCESS;
List <string> columnName = new List <string>();
List <string> value = new List <string>();
try
{
columnName.Add(Constants.SENSOR_TIMESTAMP);
columnName.Add(Constants.COLUMN_ID);
columnName.Add(Constants.SENSOR_CONTENT);
string str = "'";
for (int i = 0; i <= matrix.GetUpperBound(0); i++)
{
str += "";
for (int j = 0; j <= matrix.GetUpperBound(1); j++)
{
str += matrix[i, j];
if (j != matrix.GetUpperBound(1))
{
str += ",";
}
}
str += "";
if (i != matrix.GetUpperBound(0))
{
str += ";";
}
}
str += "'";
value.Clear();
value.Add(timestamp.ToString());
value.Add(sensorId.ToString());
value.Add(str);
//Debug.Log(str);
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText =
sql.Insert(Constants.TABLE_SENSOR, columnName, value);
//Debug.Log(dbCommand.CommandText);
dbCommand.ExecuteNonQuery();
}
catch (SqliteException sqlEx)
{
result = Constants.RESPONSE_CODE_FAILURE;
Debug.LogError(sqlEx);
}
return(result);
}
/// <summary>
/// Create a new maze by id and matrix
/// </summary>
/// <param name="mazeId"></param>
/// <param name="exploredMaze"></param>
/// <returns>
/// The successfulness of creating explored maze.
/// </returns>
public int CreateExploredMaze(int mazeId, int[,] exploredMaze)
{
actived_mazeId = mazeId;
SqlEncap sql = new SqlEncap();
int result = Constants.RESPONSE_CODE_SUCCESS;
List <string> columnName = new List <string>();
List <string> value = new List <string>();
try
{
columnName.Add(Constants.COLUMN_ID);
columnName.Add(Constants.MAZE_MATRIX);
string str = "'";
for (int i = 0; i <= exploredMaze.GetUpperBound(0); i++)
{
str += "";
for (int j = 0; j <= exploredMaze.GetUpperBound(1); j++)
{
str += exploredMaze[i, j];
if (j != exploredMaze.GetUpperBound(1))
{
str += ",";
}
}
str += "";
if (i != exploredMaze.GetUpperBound(0))
{
str += ";";
}
}
str += "'";
value.Clear();
value.Add(mazeId.ToString());
value.Add(str);
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText =
sql.Insert(Constants.TABLE_MAZE, columnName, value);
dbCommand.ExecuteNonQuery();
}
catch (SqliteException sqlEx)
{
result = Constants.RESPONSE_CODE_FAILURE;
Debug.LogError(sqlEx);
}
return(result);
}
public void Test_Delete()
{
SqlEncap sql = new SqlEncap();
string tableName = "Maze";
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("X", "1");
condition.Add("Y", "1");
condition.Add("Z", "2");
Assert.AreEqual("DELETE FROM Maze WHERE X = 1 And Y = 1 And Z = 2;", sql.Delete(tableName, condition));
}
/// <summary>
/// Update maze matrix
/// </summary>
/// <param name="updatedMaze"></param>
/// <returns>
/// The successfulness of updating the current maze.
/// </returns>
public int UpdateMaze(int[,] updatedMaze)
{
SqlEncap sql = new SqlEncap();
int result = Constants.RESPONSE_CODE_SUCCESS;
Dictionary <string, string> value = new Dictionary <string, string>();
Dictionary <string, string> condition = new Dictionary <string, string>();
try
{
string str = "'";
for (int i = 0; i <= updatedMaze.GetUpperBound(0); i++)
{
str += "";
for (int j = 0; j <= updatedMaze.GetUpperBound(1); j++)
{
str += updatedMaze[i, j];
if (j != updatedMaze.GetUpperBound(1))
{
str += ",";
}
}
str += "";
if (i != updatedMaze.GetUpperBound(0))
{
str += ";";
}
}
str += "'";
value.Clear();
value.Add(Constants.MAZE_MATRIX, str);
condition.Add(Constants.COLUMN_ID, actived_mazeId.ToString());
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText =
sql.Update(Constants.TABLE_MAZE, value, condition);
dbCommand.ExecuteNonQuery();
}
catch (SqliteException sqlEx)
{
result = Constants.RESPONSE_CODE_FAILURE;
Debug.LogError(sqlEx);
}
return(result);
}
public void Test_Update()
{
SqlEncap sql = new SqlEncap();
string tableName = "Maze";
Dictionary <string, string> setValue = new Dictionary <string, string>();
setValue.Add("X", "1");
setValue.Add("Y", "1");
setValue.Add("Z", "2");
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("ID", "1");
condition.Add("MAZE_ID", "9");
Assert.AreEqual("UPDATE Maze SET X = 1, Y = 1, Z = 2 WHERE ID = 1 And MAZE_ID = 9;", sql.Update(tableName, setValue, condition));
}
public void Test_Select()
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add("X");
selectvalue.Add("Y");
selectvalue.Add("Value");
string tableName = "Maze";
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("ID", "1");
condition.Add("Z", "2");
Assert.AreEqual("SELECT X,Y,Value FROM Maze WHERE ID = 1 And Z = 2;", sql.Select(selectvalue, tableName, condition));
}
/// <summary>
/// return the size of the commands list
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public string[] getCommandsSize(int id)
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add("count(Step)");
string tableName = "Commands";
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("ID", id.ToString());
dataReader =
ExecuteQuery(sql.Select(selectvalue, tableName, condition));
string[] res = new string[0];
if (dataReader.Read())
{
int size = dataReader.GetInt32(0);
res = new string[size];
}
return(res);
}
public void Test_Insert()
{
SqlEncap sql = new SqlEncap();
string tableName = "Maze";
List <string> columnName = new List <string>();
columnName.Add("X");
columnName.Add("Y");
columnName.Add("Z");
List <string> value = new List <string>();
value.Add("1");
value.Add("2");
value.Add("'maze no.1'");
Assert.AreEqual("INSERT INTO Maze (X,Y,Z) VALUES (1,2,'maze no.1');", sql.Insert(tableName, columnName, value));
}
public int[, ] getPathSize(int id)
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add("count(Step)");
string tableName = "Path";
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("SolutionID", id.ToString());
dataReader =
ExecuteQuery(sql.Select(selectvalue, tableName, condition));
int step = 0;
if (dataReader.Read())
{
step = dataReader.GetInt32(0);
}
return(new int[step, 2]);
}
/// <summary>
/// Update maze move history
/// </summary>
/// <param name="path"></param>
/// <returns>
/// The successfulness of updating the move history.
/// </returns>
public int UpdateMoveHistory(String[] path)
{
SqlEncap sql = new SqlEncap();
int result = Constants.RESPONSE_CODE_SUCCESS;
Dictionary <string, string> value = new Dictionary <string, string>();
Dictionary <string, string> condition = new Dictionary <string, string>();
try
{
string str = "'";
for (int i = 0; i < path.Length; i++)
{
if (i != 0)
{
str += ",";
}
str += path[i];
}
str += "'";
value.Clear();
value.Add(Constants.MAZE_HISTORY, str);
condition.Add(Constants.COLUMN_ID, actived_mazeId.ToString());
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText =
sql.Update(Constants.TABLE_MAZE, value, condition);
dbCommand.ExecuteNonQuery();
}
catch (SqliteException sqlEx)
{
result = Constants.RESPONSE_CODE_FAILURE;
Debug.LogError(sqlEx);
}
return(result);
}
/// <summary>
/// Return the size of the maze according to id.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
private int[, ] getMazeSize(int id)
{
SqlEncap sql = new SqlEncap();
List <string> selectvalue = new List <string>();
selectvalue.Add("max(X)");
selectvalue.Add("max(Y)");
string tableName = "Maze";
Dictionary <string, string> condition = new Dictionary <string, string>();
condition.Add("ID", id.ToString());
dataReader =
ExecuteQuery(sql.Select(selectvalue, tableName, condition));
int x = 0;
int y = 0;
if (dataReader.Read())
{
x = dataReader.GetInt32(0) + 1;
y = dataReader.GetInt32(1) + 1;
}
return(new int[x, y]);
}
