public void AddValidatedColumn_Invalid1()
{
GenericDataTable Table2 = CreateTestTable("Table2", ReqValidation: false);
Table2.AddValidatedColumn();
Table2.AddValidatedColumn();
}
public void PATCH_ValidArguments()
{
CreateTestTable(ReqValidation: false);
ResponseProvider Response = ExecuteSimpleRequest("/DataTable?table=Table1", HttpMethod.PATCH, new JObject()
{
{ "Add", new JObject()
{
{ "Column1", "Blob" },
{ "Validated", "Integer" }
} },
{ "Delete", new JArray()
{
"IntegerColumn"
} },
{ "Rename", new JObject()
{
{ "StringColumn", "Column2" }
} }
});
//Verify results
Assert.IsTrue(Response.StatusCode == HttpStatusCode.OK);
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, "Table1");
Dictionary <string, DataType> Columns = Table.GetColumns();
Assert.IsTrue(Columns.Count == 4);
Assert.IsTrue(Columns["Column1"] == DataType.Blob);
Assert.IsTrue(Columns["Column2"] == DataType.String);
Assert.IsTrue(Table.ReqValidation);
}
public void CREATE_ValidArguments()
{
ResponseProvider Response = ExecuteSimpleRequest("/DataTable", HttpMethod.POST, new JObject()
{
{ "Name", "Table1" },
{ "Columns", new JObject()
{
{ "StringColumn", "String" },
{ "IntegerColumn", "Integer" }
} },
{ "Department", "Administrators" },
{ "RequireValidation", true }
});
//Verify results
Assert.IsTrue(Response.StatusCode == HttpStatusCode.Created);
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, "Table1");
Assert.IsNotNull(Table);
Dictionary <string, DataType> Columns = Table.GetColumns();
Assert.IsTrue(Columns.Count == 4);
Assert.IsTrue(Columns.ContainsKey("rowid") && Columns["rowid"] == DataType.Integer);
Assert.IsTrue(Columns.ContainsKey("StringColumn") && Columns["StringColumn"] == DataType.String);
Assert.IsTrue(Columns.ContainsKey("IntegerColumn") && Columns["IntegerColumn"] == DataType.Integer);
Assert.IsTrue(Columns.ContainsKey("Validated") && Columns["Validated"] == DataType.Integer);
}
/// <summary>
/// Create a datatable for testing purposes.
/// The name of the table will be "Table1", and it will have 2 columns (StringColumn and IntegerColumn)
/// </summary>
/// <param name="Connection"></param>
/// <returns></returns>
private static GenericDataTable CreateTestTable(string Name = "Table1", Dictionary <string, DataType> Columns = null, int DepartmentID = 1, bool ReqValidation = true)
{
if (Columns == null)
{
Columns = new Dictionary <string, DataType>()
{
{ "StringColumn", DataType.String },
{ "IntegerColumn", DataType.Integer },
};
}
GenericDataTable TestTable = new GenericDataTable(Connection, Name, Columns, DepartmentID, ReqValidation);
TestTable.Insert(new List <Dictionary <string, dynamic> > {
{ new Dictionary <string, dynamic>()
{
{ "StringColumn", "Text1" },
{ "IntegerColumn", 1 }
} },
{ new Dictionary <string, dynamic>()
{
{ "StringColumn", "Text2" },
{ "IntegerColumn", 2 }
} },
{ new Dictionary <string, dynamic>()
{
{ "StringColumn", "Text3" },
{ "IntegerColumn", 3 },
{ "Validated", 1 }
} }
});
return(TestTable);
}
public override void DELETE()
{
//Get required fields
if (!Params.ContainsKey("table"))
{
Response.Send("Missing params", HttpStatusCode.BadRequest);
return;
}
if (!JSON.TryGetValue <JArray>("RowIDs", out JToken RowIDs))
{
Response.Send("Missing fields", HttpStatusCode.BadRequest);
return;
}
//Check if all specified table exist
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, Params["table"][0]);
if (Table == null)
{
Response.Send("No such table", HttpStatusCode.NotFound);
return;
}
List <int> IDs = (((JArray)RowIDs).Where(ID => ID.Type == JTokenType.Integer).Select(ID => (int)ID)).ToList();
Table.Delete(IDs);
Response.Send(HttpStatusCode.OK);
}
public void GetTableNames_ValidDepartment()
{
CreateTestTable("Table2");
Assert.IsTrue(GenericDataTable.GetTableNames(Connection, 2).Count == 0);
CreateTestTable("Table3", DepartmentID: 2);
Assert.IsTrue(GenericDataTable.GetTableNames(Connection, 2).Count == 1);
}
public void DELETE_InvalidArguments(JObject JSON, string URL, HttpStatusCode StatusCode, string ResponseMessage = null)
{
GenericDataTable TestTable = CreateTestTable();
TestTable.Insert(new List <Dictionary <string, dynamic> > {
{ new Dictionary <string, dynamic>()
{
{ "StringColumn", "Text1" },
{ "IntegerColumn", 1 }
} },
{ new Dictionary <string, dynamic>()
{
{ "StringColumn", "Text2" },
{ "IntegerColumn", 2 }
} },
{ new Dictionary <string, dynamic>()
{
{ "StringColumn", "Text3" },
{ "IntegerColumn", 3 }
} }
});
ResponseProvider Response = ExecuteSimpleRequest(URL, HttpMethod.DELETE, JSON);
Assert.IsTrue(Response.StatusCode == StatusCode);
if (ResponseMessage != null)
{
Assert.IsTrue(Encoding.UTF8.GetString(Response.Data) == ResponseMessage);
}
}
public void PATCH_ValidArguments()
{
CreateTestTable();
ResponseProvider Response = ExecuteSimpleRequest("/data?table=Table1", HttpMethod.PATCH, new JObject()
{
{ "1", new JObject()
{
{ "StringColumn", "Hello World!" },
{ "IntegerColumn", 12345 }
} }
});
Assert.IsTrue(Response.StatusCode == HttpStatusCode.OK);
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, "Table1");
JArray Expected = new JArray()
{
1, "Hello World!", 12345, 0
};
JArray Actual = (JArray)Table.GetRows()["Rows"][0];
Assert.IsTrue(JArray.DeepEquals(Expected, JArray.Parse(Actual.ToString())));
Expected = new JArray()
{
2, "Text2", 2, 0
};
Actual = (JArray)Table.GetRows()["Rows"][1];
Assert.IsTrue(JArray.DeepEquals(Expected, JArray.Parse(Actual.ToString())));
}
public void DELETE_ValidArguments()
{
CreateTestTable();
ResponseProvider Response = ExecuteSimpleRequest("/DataTable?table=Table1", HttpMethod.DELETE, null);
Assert.IsTrue(Response.StatusCode == HttpStatusCode.OK);
Assert.IsNull(GenericDataTable.GetTableByName(Connection, "Table1"));
}
public override void POST()
{
const string RX = "^[A-z]{1}[0-9A-Za-z_]*$";
//Get all required fields
if (
!JSON.TryGetValue <string>("Name", out JToken Name) ||
!JSON.TryGetValue <JObject>("Columns", out JToken Columns) ||
!JSON.TryGetValue <string>("Department", out JToken DepartmentVal) ||
!JSON.TryGetValue <bool>("RequireValidation", out JToken RequireValidation)
)
{
Response.Send("Missing fields", HttpStatusCode.BadRequest);
return;
}
//Check name
if (!Regex.IsMatch((string)Name, RX))
{
Response.Send("Invalid name", HttpStatusCode.BadRequest);
return;
}
if (GenericDataTable.Exists(Connection, (string)Name))
{
Response.Send("Already exists", HttpStatusCode.BadRequest);
return;
}
//Check Department
Department Dept = Department.GetByName(Connection, (string)DepartmentVal);
if (Dept == null)
{
Response.Send("No such department", HttpStatusCode.BadRequest);
return;
}
//Convert columns
Dictionary <string, DataType> ColumnDict = new Dictionary <string, DataType>();
foreach (KeyValuePair <string, JToken> Entry in (JObject)Columns)
{
if (GenericDataTable.ReservedColumns.Contains(Entry.Key) || !Regex.IsMatch(Entry.Key, RX))
{
Response.Send("Invalid or reserved column name", HttpStatusCode.BadRequest);
return;
}
if (!Enum.TryParse((string)Entry.Value, out DataType DT))
{
Response.Send("Invalid column type. Type must be either Integer, String, Real, or Blob", HttpStatusCode.BadRequest);
return;
}
ColumnDict.Add(Entry.Key, DT);
}
new GenericDataTable(Connection, (string)Name, ColumnDict, Dept, (bool)RequireValidation);
Response.Send(HttpStatusCode.Created);
}
public override void POST()
{
//Get required fields
if (!Params.ContainsKey("table"))
{
Response.Send("Missing params", HttpStatusCode.BadRequest);
return;
}
//Check if all specified table exist
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, Params["table"][0]);
if (Table == null)
{
Response.Send("No such table", HttpStatusCode.NotFound);
return;
}
//Build insertion dict
Dictionary <string, DataType> Columns = Table.GetColumns();
Dictionary <string, dynamic> Dict = new Dictionary <string, dynamic>();
foreach (KeyValuePair <string, JToken> Entry in JSON)
{
if (!Columns.ContainsKey(Entry.Key))
{
Response.Send("No such column: " + Entry.Key, HttpStatusCode.BadRequest);
return;
}
if (Entry.Key == "rowid")
{
Response.Send("Can't set row ID", HttpStatusCode.BadRequest);
return;
}
if (Columns[Entry.Key] == DataType.Integer)
{
try {
int input = (int)Entry.Value;
} catch {
Response.Send("Invalid row value at " + Entry.Key, HttpStatusCode.BadRequest);
return;
}
}
Dict.Add(Entry.Key, Columns[Entry.Key] == DataType.Integer ? (int)Entry.Value : (dynamic)Entry.Value);
}
//Insert data and return response
Table.Insert(Dict);
Response.Send(HttpStatusCode.Created);
}
public void GetTableNames_ValidArgs()
{
//Table1 is created during init
CreateTestTable("Table2");
CreateTestTable("Table3");
List <string> FunctionResult = GenericDataTable.GetTableNames(Connection);
List <string> TableNames = new List <string>()
{
"Table1", "Table2", "Table3"
};
Assert.IsTrue(FunctionResult.SequenceEqual(TableNames));
}
public void GetTables_ValidArgs()
{
//Table1 is created during init
GenericDataTable Table2 = CreateTestTable("Table2");
GenericDataTable Table3 = CreateTestTable("Table3");
List <GenericDataTable> FunctionResult = GenericDataTable.GetTables(Connection);
List <GenericDataTable> Tables = new List <GenericDataTable>()
{
Table, Table2, Table3
};
Assert.IsTrue(FunctionResult.Count == Tables.Count);
Assert.IsTrue((Tables.SelectMany(Entry1 => FunctionResult.Where(Entry2 => Entry1.Name == Entry2.Name).Select(Entry2 => Entry1))).Count() == Tables.Count);
}
public override void GET()
{
//Get required fields
if (!Params.ContainsKey("table"))
{
Response.Send("Missing params", HttpStatusCode.BadRequest);
return;
}
//If any optional parameters were given, overwrite the default values with those specified by the parameters
int Begin = 0;
if (Params.ContainsKey("begin"))
{
int.TryParse(Params["begin"][0], out Begin);
}
int End = 25;
if (Params.ContainsKey("end"))
{
int.TryParse(Params["end"][0], out End);
}
bool isUnvalidated = false;
if (Params.ContainsKey("isunvalidated"))
{
string val = Params["isunvalidated"][0];
bool.TryParse(val, out isUnvalidated);
}
//Check if all specified tables exist
if (!GenericDataTable.Exists(Connection, Params["table"]))
{
Response.Send("No such table", HttpStatusCode.NotFound);
return;
}
//Create response JSON
JObject Result = new JObject();
foreach (string TableName in Params["table"])
{
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, TableName);
Result.Add(TableName, isUnvalidated ? Table.GetUnvalidatedRows(Begin, End) : Table.GetRows(Begin, End));
}
Response.Send(Result, HttpStatusCode.OK);
}
public void DELETE_ValidArguments()
{
CreateTestTable();
ResponseProvider Response = ExecuteSimpleRequest("/Data?table=Table1", HttpMethod.DELETE, new JObject()
{
{ "RowIDs", new JArray()
{
1, 2
} }
});
Assert.IsTrue(Response.StatusCode == HttpStatusCode.OK);
JObject Data = GenericDataTable.GetTableByName(Connection, "Table1").GetRows();
Assert.IsTrue(Data.ContainsKey("Columns"));
Assert.IsTrue(Data.ContainsKey("Rows"));
Assert.IsTrue(((JArray)Data["Rows"]).Count == 1);
}
public void POST_ValidArguments()
{
CreateTestTable();
ResponseProvider Response = ExecuteSimpleRequest("/data?table=Table1", HttpMethod.POST, new JObject()
{
{ "StringColumn", "SomeText" },
{ "IntegerColumn", 12345 },
{ "Validated", 1 }
});
Assert.IsTrue(Response.StatusCode == HttpStatusCode.Created);
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, "Table1");
JObject Data = Table.GetRows();
Assert.IsTrue(((JArray)Data["Rows"][3]).Count == 4);
Assert.IsTrue((int)Data["Rows"][3][0] == 4);
Assert.IsTrue((string)Data["Rows"][3][1] == "SomeText");
Assert.IsTrue((int)Data["Rows"][3][2] == 12345);
Assert.IsTrue((int)Data["Rows"][3][3] == 1);
}
public override void GET()
{
int departmentID = 0;
// If there's a department, set departmentID to its ID
if (Params.ContainsKey("department"))
{
Department department = Department.GetByName(Connection, Params["department"][0]);
// Check if the specified department exists. If it doesn't, send a 404 Not Found
if (department == null)
{
Response.Send("No such department", HttpStatusCode.NotFound);
return;
}
departmentID = department.ID;
}
// Retrieve tables, convert to JSON, and send.
Response.Send(JsonConvert.SerializeObject(GenericDataTable.GetTables(Connection, departmentID)), HttpStatusCode.OK);
}
public override void DELETE()
{
// Get all required values
if (!Params.ContainsKey("table"))
{
Response.Send("Missing params", HttpStatusCode.BadRequest);
return;
}
//If table exists, delete it
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, Params["table"][0]);
if (Table == null)
{
Response.Send("No such table", HttpStatusCode.NotFound);
return;
}
else
{
Table.DropTable();
Response.Send(StatusCode: HttpStatusCode.OK);
}
}
public static void ClassInit(TestContext _)
{
Connection = Database.Init(true);
Table = CreateTestTable();
}
public void GetTables_InvalidDepartment() => GenericDataTable.GetTables(Connection, 12345);
public override void PATCH()
{
// Get all required values
if (!Params.ContainsKey("table"))
{
Response.Send("Missing params", HttpStatusCode.BadRequest);
return;
}
bool FoundAdd = JSON.TryGetValue <JObject>("Add", out JToken AddValue);
bool FoundDelete = JSON.TryGetValue <JArray>("Delete", out JToken DeleteValue);
bool FoundRename = JSON.TryGetValue <JObject>("Rename", out JToken RenameValue);
if (!FoundAdd && !FoundDelete && !FoundRename)
{
Response.Send("Missing fields", HttpStatusCode.BadRequest);
return;
}
//Check if table exists
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, Params["table"][0]);
if (Table == null)
{
Response.Send("No such table", HttpStatusCode.NotFound);
return;
}
Dictionary <string, DataType> ToAdd = new Dictionary <string, DataType>();
List <string> ToDelete = new List <string>();
Dictionary <string, string> ToRename = new Dictionary <string, string>();
Dictionary <string, DataType> Columns = Table.GetColumns();
//Add all specified columns, if any;
if (FoundAdd)
{
JObject Add = (JObject)AddValue;
foreach (KeyValuePair <string, JToken> Entry in Add)
{
if (Entry.Key.ToLower() == "validated")
{
Table.AddValidatedColumn();
continue;
}
if (Columns.ContainsKey(Entry.Key) ||
!Regex.IsMatch(Entry.Key, GenericDataTable.RX) ||
((string)Entry.Key).ToLower() == "rowid"
)
{
Response.Send("Invalid entry in Add (" + Entry.Key + ")", HttpStatusCode.BadRequest);
return;
}
if (!Enum.TryParse((string)Entry.Value, out DataType DT))
{
Response.Send("Invalid type", HttpStatusCode.BadRequest);
return;
}
ToAdd.Add(Entry.Key, DT);
}
}
//Delete all specified columns, if any;
if (FoundDelete)
{
JArray Delete = (JArray)DeleteValue;
foreach (JToken Entry in Delete)
{
if (Entry.Type != JTokenType.String ||
!Columns.ContainsKey((string)Entry) ||
((string)Entry).ToLower() == "rowid"
)
{
Response.Send("Invalid entry in Delete (" + Entry + ")", HttpStatusCode.BadRequest);
return;
}
ToDelete.Add((string)Entry);
}
}
//Rename all specified columns, if any;
if (FoundRename)
{
JObject Rename = (JObject)RenameValue;
foreach (KeyValuePair <string, JToken> Entry in Rename)
{
if (
Entry.Value.Type != JTokenType.String ||
((string)Entry.Value).ToLower() == "validated" ||
((string)Entry.Value).ToLower() == "rowid" ||
((string)Entry.Key).ToLower() == "validated" ||
((string)Entry.Key).ToLower() == "rowid" ||
Columns.ContainsKey((string)Entry.Value) ||
!Columns.ContainsKey(Entry.Key) ||
!Regex.IsMatch((string)Entry.Value, GenericDataTable.RX)
)
{
Response.Send("Invalid entry in Rename (" + Entry.Key + ")", HttpStatusCode.BadRequest);
return;
}
ToRename.Add(Entry.Key, (string)Entry.Value);
}
}
if (ToAdd.Count > 0)
{
Table.AddColumn(ToAdd);
}
if (ToDelete.Count > 0)
{
Table.DropColumn(ToDelete);
}
if (ToRename.Count > 0)
{
Table.RenameColumn(ToRename);
}
Response.Send(HttpStatusCode.OK);
}
public void GetTableByName_Valid() => Assert.IsNotNull(GenericDataTable.GetTableByName(Connection, "Table1"));
public void GetTableByName_Invalid() => Assert.IsNull(GenericDataTable.GetTableByName(Connection, "SomeTable"));
public void Exists_InvalidTable() => Assert.IsFalse(GenericDataTable.Exists(Connection, "SomeTable"));
public void Exists_ValidTable() => Assert.IsTrue(GenericDataTable.Exists(Connection, "Table1"));
public void GetUnvalidatedRows_Columnheck()
{
GenericDataTable Table2 = CreateTestTable("Table2", ReqValidation: false);
Table2.GetUnvalidatedRows();
}
public void DropTable()
{
Table.DropTable();
Assert.IsTrue(GenericDataTable.GetTableByName(Connection, "UnitTestTable") == null);
Connection.Query("SELECT * FROM UnitTestTable");
}
public BasicDataRow(List <object> record, GenericDataTable genericdatatable)
: this(genericdatatable)
{
_record = record;
}
public override void PATCH()
{
//Get required fields
if (!Params.ContainsKey("table"))
{
Response.Send("Missing params", HttpStatusCode.BadRequest);
return;
}
//Check if all specified table exist
GenericDataTable Table = GenericDataTable.GetTableByName(Connection, Params["table"][0]);
if (Table == null)
{
Response.Send("No such table", HttpStatusCode.NotFound);
return;
}
//Convert JSON to update dict
Dictionary <int, Dictionary <string, dynamic> > Dict = new Dictionary <int, Dictionary <string, dynamic> >();
Dictionary <string, DataType> Columns = Table.GetColumns();
foreach (KeyValuePair <string, JToken> Entry in JSON)
{
if (!int.TryParse(Entry.Key, out int RowID))
{
Response.Send("Invalid row ID", HttpStatusCode.BadRequest);
return;
}
if (Entry.Value.Type != JTokenType.Object)
{
Response.Send("Invalid row data", HttpStatusCode.BadRequest);
return;
}
Dict.Add(RowID, new Dictionary <string, dynamic>());
foreach (KeyValuePair <string, JToken> Column in (JObject)Entry.Value)
{
if (Columns.ContainsKey(Column.Key))
{
if (Column.Key == "rowid")
{
Response.Send("Can't modify row ID", HttpStatusCode.BadRequest);
return;
}
if (Columns[Column.Key] == DataType.Integer && Column.Value.Type != JTokenType.Integer)
{
Response.Send("Invalid datatype (" + Column.Key + " should be Integer)", HttpStatusCode.BadRequest);
return;
}
Dict[RowID].Add(Column.Key, Columns[Column.Key] == DataType.Integer ? (int)Column.Value : (dynamic)Column.Value);
}
else
{
Response.Send("Unknown column " + Column.Key, HttpStatusCode.BadRequest);
return;
}
}
}
Table.Update(Dict);
Response.Send(HttpStatusCode.OK);
}
public BasicDataRow(GenericDataTable genericdatatable)
{
_genericdatatable = genericdatatable;
}
