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; }