public void TestSelectAll()
{
string password = "******";
Database database1 = new Database("aitor", "aitoru", password);
Table tab = new Table("Users", new List <TableColumn>());
TableColumn tablecolumn1 = new TableColumn("Name", "string");
tablecolumn1.AddValue("Alvaro");
tablecolumn1.AddValue("Ronny");
TableColumn tablecolumn2 = new TableColumn("Age", "int");
tablecolumn2.AddValue("20");
tablecolumn2.AddValue("22");
tab.AddTableColumn(tablecolumn1);
tab.AddTableColumn(tablecolumn2);
database1.AddTable(tab);
CompareWhere compared2 = new CompareWhere("Name", "Alvaro", "=");
Table newTab = database1.SelectAll(tab.GetName(), compared2, user);
Table tab1 = database1.SelectAll(tab.GetName(), null, user);
Assert.AreEqual("Users", tab1.GetName());
Assert.AreEqual(2, tab1.GetList().Count);
Assert.AreEqual("Alvaro", tab1.GetList().ElementAt(0).GetList().ElementAt(0));
Assert.AreEqual("Ronny", tab1.GetList().ElementAt(0).GetList().ElementAt(1));
Assert.AreEqual("20", tab1.GetList().ElementAt(1).GetList().ElementAt(0));
Assert.AreEqual("22", tab1.GetList().ElementAt(1).GetList().ElementAt(1));
Assert.AreEqual("Alvaro", newTab.GetList().ElementAt(0).GetList().ElementAt(0));
Assert.AreEqual(1, newTab.GetList().ElementAt(0).GetList().Count);
Assert.AreNotEqual("Margo", tab1.GetList().ElementAt(1).GetList().ElementAt(0));
}
public void TestGetPositions()
{
CompareWhere compare1 = new CompareWhere("age", "20", "=");
CompareWhere compare2 = new CompareWhere("age", "10", ">");
CompareWhere compare3 = new CompareWhere("age", "30", "<");
tableColumn.AddValue("10");
tableColumn.AddValue("20");
tableColumn.AddValue("30");
List <int> positions1 = tableColumn.GetPositions(compare1);
List <int> positions2 = tableColumn.GetPositions(compare2);
List <int> positions3 = tableColumn.GetPositions(compare3);
Assert.AreEqual(1, positions1.Count);
Assert.AreEqual(1, positions1.ElementAt(0));
Assert.AreEqual(2, positions2.Count);
Assert.AreEqual(1, positions2.ElementAt(0));
Assert.AreEqual(2, positions2.ElementAt(1));
Assert.AreEqual(2, positions3.Count);
Assert.AreEqual(0, positions3.ElementAt(0));
Assert.AreEqual(1, positions3.ElementAt(1));
}
public Update(string table, CompareWhere condition, List <string> setColumns, List <string> setValues)
{
m_table = table;
m_condition = condition;
m_setColumns = setColumns;
m_setValues = setValues;
}
public void TestUpdate()
{
string contraseña = "1234";
database = new Database("aitor", "aitoru", contraseña);
//Parameters that will be inserted in the function
List <string> setAttribute = new List <string>();
setAttribute.Add("Nombre");
setAttribute.Add("Apellido");
List <string> value = new List <string>();
value.Add("Aitor");
value.Add("Urabain");
string table = "Empleado";
CompareWhere compared = new CompareWhere("Nombre", "Ronny", "=");
//Creating the elements for changing with the update
Table tab = new Table("Empleado", new List <TableColumn>());
TableColumn tablecolumn1 = new TableColumn("Nombre", "string");
tablecolumn1.AddValue("Alvaro");
tablecolumn1.AddValue("Ronny");
TableColumn tablecolumn2 = new TableColumn("Apellido", "string");
tablecolumn2.AddValue("Margo");
tablecolumn2.AddValue("Caiza");
tab.AddTableColumn(tablecolumn1);
tab.AddTableColumn(tablecolumn2);
database.AddTable(tab);
// executing the update method
//user != admin
Assert.AreEqual(Messages.SecurityNotSufficientPrivileges, database.Update(setAttribute, value, table, compared, user2));
//user == admin
database.Update(setAttribute, value, table, compared, user);
// looking if it has changed
Boolean welldone = false;
foreach (TableColumn tc in tab.GetList())
{
if (tc.GetName() == "Nombre")
{
foreach (string st in tc.GetList())
{
if (st == "Aitor")
{
welldone = true;
}
}
}
}
Assert.AreEqual(true, welldone);
}
public void TestSelect()
{
string password = "******";
Database database1 = new Database("aitor", "aitoru", password);
//Creating elements for select method parameters
List <string> selects = new List <string>();
selects.Add("Name");
CompareWhere compared = new CompareWhere("Surname", "Caiza", "=");
Table tab = new Table("Employee", new List <TableColumn>());
TableColumn tablecolumn1 = new TableColumn("Name", "string");
tablecolumn1.AddValue("Alvaro");
tablecolumn1.AddValue("Ronny");
TableColumn tablecolumn2 = new TableColumn("Surname", "string");
tablecolumn2.AddValue("Margo");
tablecolumn2.AddValue("Caiza");
tab.AddTableColumn(tablecolumn1);
tab.AddTableColumn(tablecolumn2);
database1.AddTable(tab);
Table tableResult = new Table("Result", new List <TableColumn>());
TableColumn tcResult = new TableColumn("Name", "string");
tcResult.AddValue("Ronny");
tableResult.AddTableColumn(tcResult);
Table nullTable = new Table("nullTable", new List <TableColumn>());
nullTable.AddTableColumn(tcResult);
// executing the select method
Table result = database1.Select(tab.GetName(), selects, compared, user);
Assert.AreEqual("Ronny", result.GetList().ElementAt(0).GetList().ElementAt(0));
selects.Add("Surname");
CompareWhere compared2 = new CompareWhere("Name", "Alvaro", "=");
Table result2 = database1.Select(tab.GetName(), selects, compared2, user);
Assert.AreEqual("Alvaro", result2.GetList().ElementAt(0).GetList().ElementAt(0));
Assert.AreEqual("Margo", result2.GetList().ElementAt(1).GetList().ElementAt(0));
Assert.AreEqual("Ronny", nullTable.GetList().ElementAt(0).GetList().ElementAt(0));
}
public void TestDelete()
{
string contraseña = "1234";
database = new Database("aitor", "aitoru", contraseña);
CompareWhere compared = new CompareWhere("Nombre", "Ronny", "=");
//Creating the elements for changing with the update
Table tab = new Table("Empleado", new List <TableColumn>());
TableColumn tablecolumn1 = new TableColumn("Nombre", "string");
tablecolumn1.AddValue("Alvaro");
tablecolumn1.AddValue("Ronny");
TableColumn tablecolumn2 = new TableColumn("Apellido", "string");
tablecolumn2.AddValue("Margo");
tablecolumn2.AddValue("Caiza");
tab.AddTableColumn(tablecolumn1);
tab.AddTableColumn(tablecolumn2);
database.AddTable(tab);
// executing the update method
//user != admin
Assert.AreEqual(Messages.SecurityNotSufficientPrivileges, database.Delete(tab.GetName(), compared, user2));
//user == admin
database.Delete(tab.GetName(), compared, user);
// looking if it has changed
Boolean welldone = false;
foreach (TableColumn tc in tab.GetList())
{
foreach (string st in tc.GetList())
{
if (st == "Ronny")
{
welldone = true;
}
}
}
Assert.AreNotEqual(true, welldone);
}
public static IQuery Parse(string miniSqlSentence)
{
const string selectAllPattern = @"SELECT \* FROM ([a-zA-Z0-9]+);";
const string deletePattern = @"DELETE FROM ([a-zA-Z0-9]+) WHERE ([a-zA-Z]+)([=><])('{0,1})([a-zA-Z0-9-\.\s]+)\4;";
const string updatePattern = @"UPDATE ([a-zA-Z0-9]+) SET ([a-zA-Z0-9]+=('{0,1})[a-zA-Z0-9\.\s-]+\3(,[a-zA-Z0-9]+=('{0,1})[a-zA-Z0-9\.\s-]+\5)*) WHERE ([a-zA-Z]+)([=><])('{0,1})([a-zA-Z0-9-\.\s]+)\8;";
const string selectAllWherePattern = @"SELECT \* FROM ([a-zA-Z0-9]+) WHERE ([a-zA-Z]+)([=><])('{0,1})([a-zA-Z0-9-\.\s]+)\4;";
const string selectColumnsPattern = @"SELECT ([a-zA-Z0-9]+(,[a-zA-Z0-9]+)*) FROM ([a-zA-Z0-9]+);";
const string selectColumnsWherePattern = @"SELECT ([a-zA-Z0-9]+(,[a-zA-Z0-9]+)*) FROM ([a-zA-Z0-9]+) WHERE ([a-zA-Z0-9]+)([=><])('{0,1})([a-zA-Z0-9-\.\s]+)\6;";
const string insertIntoPattern = @"INSERT INTO ([a-zA-Z0-9]+) VALUES \(((('{0,1})[a-zA-Z0-9\.\s-]+\4)+(,('{0,1})[a-zA-Z0-9\.\s-]+\6)*)\);";
const string dropTablePattern = @"DROP TABLE ([a-zA-Z0-9]+);";
const string createTablePattern = @"CREATE TABLE ([a-zA-Z0-9]+) \(((([a-zA-Z0-9]+) (INT|DOUBLE|TEXT))+((,([a-zA-Z0-9]+) (INT|DOUBLE|TEXT))*))\);";
const string grantPrivelegePattern = @"GRANT (INSERT|DELETE|SELECT|UPDATE) ON ([a-zA-Z0-9]+) TO ([a-zA-Z0-9-_\.\s]+);";
const string revokePrivelegePattern = @"REVOKE (INSERT|DELETE|SELECT|UPDATE) ON ([a-zA-Z0-9]+) TO ([a-zA-Z0-9-_\.\s]+);";
const string addUserPattern = @"ADD USER \('([a-zA-Z0-9]+)','([a-zA-Z0-9]+)',([a-zA-Z0-9]+)\);";
const string createSecurityProfilePattern = @"CREATE SECURITY PROFILE ([a-zA-Z0-9-_\.\s]+);";
const string deleteSecurityProfilePattern = @"DROP SECURITY PROFILE ([a-zA-Z0-9-_\.\s]+);";
const string deleteUserPattern = @"DELETE USER ([a-zA-Z0-9-_\.\s]+);";
Match match = Regex.Match(miniSqlSentence, selectAllWherePattern);
if (match.Success)
{
CompareWhere cW = new CompareWhere(match.Groups[2].Value, match.Groups[5].Value, match.Groups[3].Value);
SelectAllWhere selectAllWhere = new SelectAllWhere(match.Groups[1].Value, cW);
return(selectAllWhere);
}
match = Regex.Match(miniSqlSentence, selectAllPattern);
if (match.Success)
{
SelectAll selectAll = new SelectAll(match.Groups[1].Value);
return(selectAll);
}
match = Regex.Match(miniSqlSentence, selectColumnsWherePattern);
if (match.Success)
{
string[] columnNames = match.Groups[1].Value.Split(',');
CompareWhere cW = new CompareWhere(match.Groups[4].Value, match.Groups[7].Value, match.Groups[5].Value);
SelectColumnWhere selectColumnWhere = new SelectColumnWhere(match.Groups[3].Value, cW, columnNames);
return(selectColumnWhere);
}
match = Regex.Match(miniSqlSentence, selectColumnsPattern);
if (match.Success)
{
string[] columnNames = match.Groups[1].Value.Split(',');
SelectColumns selectColumns = new SelectColumns(match.Groups[3].Value, columnNames);
return(selectColumns);
}
match = Regex.Match(miniSqlSentence, deletePattern);
if (match.Success)
{
CompareWhere cW = new CompareWhere(match.Groups[2].Value, match.Groups[5].Value, match.Groups[3].Value);
Delete delete = new Delete(match.Groups[1].Value, cW);
return(delete);
}
match = Regex.Match(miniSqlSentence, updatePattern);
if (match.Success)
{
CompareWhere cW = new CompareWhere(match.Groups[6].Value, match.Groups[9].Value, match.Groups[7].Value);
List <string> setColumns = new List <string>();
List <string> setValues = new List <string>();
string set = match.Groups[2].Value.Replace("'", "");
string[] setElements = set.Split(',');
for (int i = 0; i < setElements.Length; i++)
{
string[] columnAndValue = setElements[i].Split('=');
string column = columnAndValue[0];
string value = columnAndValue[1];
setColumns.Add(column);
setValues.Add(value);
}
Update update = new Update(match.Groups[1].Value, cW, setColumns, setValues);
return(update);
}
match = Regex.Match(miniSqlSentence, insertIntoPattern);
if (match.Success)
{
string temp = match.Groups[2].Value.Replace("'", "");
string[] columnNames = temp.Split(',');
InsertInto insertInto = new InsertInto(match.Groups[1].Value, null, columnNames);
return(insertInto);
}
match = Regex.Match(miniSqlSentence, createTablePattern);
if (match.Success)
{
List <TableColumn> columns = new List <TableColumn>();
string tableName = match.Groups[1].Value;
string[] temp = match.Groups[2].Value.Split(',');
for (int i = 0; i < temp.Length; i++)
{
string[] columnAndValue = temp[i].Split(' ');
string column = columnAndValue[0];
string value = columnAndValue[1];
TableColumn tc = new TableColumn(column, value);
columns.Add(tc);
}
CreateTable createTable = new CreateTable(tableName, columns);
return(createTable);
}
match = Regex.Match(miniSqlSentence, dropTablePattern);
if (match.Success)
{
DropTable dropTable = new DropTable(match.Groups[1].Value);
return(dropTable);
}
match = Regex.Match(miniSqlSentence, grantPrivelegePattern);
if (match.Success)
{
GrantPrivilege grantPrivilege = new GrantPrivilege(StringToPrivilegeType(match.Groups[1].Value), match.Groups[2].Value, match.Groups[3].Value);
return(grantPrivilege);
}
match = Regex.Match(miniSqlSentence, revokePrivelegePattern);
if (match.Success)
{
RevokePrivilege revokePrivilege = new RevokePrivilege(StringToPrivilegeType(match.Groups[1].Value), match.Groups[2].Value, match.Groups[3].Value);
return(revokePrivilege);
}
match = Regex.Match(miniSqlSentence, addUserPattern);
if (match.Success)
{
AddUser addUser = new AddUser(match.Groups[1].Value, match.Groups[2].Value, match.Groups[3].Value);
return(addUser);
}
match = Regex.Match(miniSqlSentence, createSecurityProfilePattern);
if (match.Success)
{
CreateSecurityProfile createProfile = new CreateSecurityProfile(match.Groups[1].Value);
return(createProfile);
}
match = Regex.Match(miniSqlSentence, deleteSecurityProfilePattern);
if (match.Success)
{
DeleteSecurityProfile deleteProfile = new DeleteSecurityProfile(match.Groups[1].Value);
return(deleteProfile);
}
match = Regex.Match(miniSqlSentence, deleteUserPattern);
if (match.Success)
{
DeleteUser deleteUser = new DeleteUser(match.Groups[1].Value);
return(deleteUser);
}
return(null);
}
public SelectAllWhere(string table, CompareWhere c)
{
m_table = table;
m_compare = c;
}
public Delete(string table, CompareWhere condition)
{
m_table = table;
m_condition = condition;
}
