/// <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]); }