private void ProcessDeleteStatement(List <string> tokens, ref QueryResult queryResult) { // <Delete Stm> ::= DELETE <From Clause> TABLE <Where Clause> IntSchTable tableToDeleteFrom = null; int tableOffset = 0; for (int i = 1; i < tokens.Count; ++i) { if (tokens[i] == "from") { tableOffset = i + 1; break; } } tableToDeleteFrom = GetTable(tokens[tableOffset]); SQLConditional sqlConditional = ParseWhereClause(tokens); if (!ValidateColumns(ref queryResult, sqlConditional.GetColumns(), tableToDeleteFrom, allowWildCard: false)) { return; } tableToDeleteFrom.Delete(sqlConditional); }
private void ProcessUpdateStatement(List <string> tokens, ref QueryResult queryResult) { string nameOfTableToUpdate = tokens[1]; List <string> columnsToUpdate = new List <string>(); List <string> updatedValues = new List <string>(); ParseUpdates(tokens, ref columnsToUpdate, ref updatedValues); SQLConditional conditional = ParseWhereClause(tokens); IntSchTable tableToUpdate = GetTable(nameOfTableToUpdate); // Validate update columns if (!ValidateColumns(ref queryResult, columnsToUpdate, tableToUpdate, /*allowWildCard=*/ false)) { return; } // Validate conditional columns List <string> conditionalColumns = conditional.GetColumns(); if (!ValidateColumns(ref queryResult, conditionalColumns, tableToUpdate, /*allowWildCard=*/ false)) { return; } tableToUpdate.Update(columnsToUpdate, updatedValues, conditional); }
private bool ValidateColumns(ref QueryResult queryResult, List <string> colsToValidate, IntSchTable tableToValidate, bool allowWildCard) { List <string> missingColumns = new List <string>(); foreach (string column in colsToValidate) { if (column != "*" && !tableToValidate.ContainsColumn(column)) { missingColumns.Add(column); } else if (column == "*" && !allowWildCard) { queryResult.Error = new InputError("SQL", "Wildcard (*) is not allowed."); return(false); } } if (missingColumns.Count != 0) { string errorMessage = "Could not find columns: " + String.Join(",", missingColumns); string errorType = "SQL"; queryResult.Error = new InputError(errorType, errorMessage); return(false); } return(true); }
public IntSchTable CreateTableFromImport(string importName, string importContents) { // TODO validate that the csv is in a valid format // Split the imported csv by line string[] tableContents = importContents.Split( new[] { "\r\n", "\r", "\n" }, StringSplitOptions.None ); string[] columnNames = tableContents[0].Split(','); string tableNameToCreate = GetNewTableName(importName); string newTableCreateStatement = GetCreateStatementForNewTable(tableNameToCreate, columnNames); string[] insertStatements = GenerateInsertStatements(tableNameToCreate, columnNames, tableContents); SubmitQuery(newTableCreateStatement); foreach (string insertStatement in insertStatements) { SubmitQuery(insertStatement); } IntSchTable createdTable = GetTable(tableNameToCreate); return(createdTable); }
private bool ProcessCreateTableStatement(List <string> tokens, ref QueryResult queryResult) { // assuming only the following rule is accepted // <Create Stm> ::= CREATE TABLE Id '(' <ID List> ')' ------ NO SUPPORT for <Constraint Opt> string newTableName = tokens[2]; foreach (IntSchTable tbl in tables) { if (tbl.Name == newTableName) { string errorType = "SQL"; string errorMsg = "table " + tbl.Name + " already exists"; queryResult.Error = new InputError(errorType, errorMsg); return(false); } } List <string> columnNames = new List <string>(); List <string> columnTypes = new List <string>(); int idCount = 2; for (int i = 4; i < tokens.Count; ++i) { if (tokens[i] == ")") { break; } else if (tokens[i] == ",") { continue; } else { if (idCount == 2) { columnNames.Add(tokens[i]); --idCount; } else if (idCount == 1) { columnTypes.Add(tokens[i]); idCount = 2; } } } IntSchTable newTable = new IntSchTable(newTableName); for (int i = 0; i < columnNames.Count; ++i) { newTable.AddColumn(columnNames[i], columnTypes[i]); } tables.Add(newTable); return(true); }
public QueryResult(string qt, string d, string acc, InputError err) { queryType = qt; done = d; accepted = acc; error = err; results = null; }
private bool ProcessCreateTableStatement(List <string> tokens) { // assuming only the following rule is accepted // <Create Stm> ::= CREATE TABLE Id '(' <ID List> ')' ------ NO SUPPORT for <Constraint Opt> string newTableName = tokens[2]; foreach (IntSchTable tbl in tables) { if (tbl.Name == newTableName) { //cannot create a new table with the same name return(false); } } List <string> columnNames = new List <string>(); List <string> columnTypes = new List <string>(); int idCount = 2; for (int i = 4; i < tokens.Count; ++i) { if (tokens[i] == ")") { break; } else if (tokens[i] == ",") { continue; } else { if (idCount == 2) { columnNames.Add(tokens[i]); --idCount; } else if (idCount == 1) { columnTypes.Add(tokens[i]); idCount = 2; } } } IntSchTable newTable = new IntSchTable(newTableName); for (int i = 0; i < columnNames.Count; ++i) { newTable.AddColumn(columnNames[i], columnTypes[i]); } tables.Add(newTable); return(true); }
private void ProcessDropStatement(List <string> tokens, ref QueryResult queryResult) { string nameOfTableToDrop = tokens[2]; IntSchTable tableToDrop = GetTable(nameOfTableToDrop); if (tableToDrop == null) { string errorMessage = "Could not find table: " + nameOfTableToDrop; string errorType = "SQL"; queryResult.Error = new InputError(errorType, errorMessage); return; } tables.Remove(tableToDrop); }
private void ProcessSelectStatement(List <string> tokens, ref QueryResult queryResult) { // <Select Stm> ::= SELECT <Columns> <From Clause> <Where Clause> <Group Clause> <Having Clause> <Order Clause>\ IntSchTable tableToSelectFrom = null; IntSchTable results = null; List <string> colsToSelect = new List <string>(); List <string> colsToValidate = new List <string>(); int tableOffset = 0; for (int i = 1; i < tokens.Count; ++i) { if (tokens[i] == "from") { tableOffset = i + 1; break; } else if (tokens[i] == ",") { continue; } else if (i + 3 < tokens.Count && (tokens[i].ToLower() == "avg" || tokens[i].ToLower() == "max" || tokens[i].ToLower() == "min" || tokens[i].ToLower() == "sum")) { if (tokens[i + 2] == "*") { string errorMessage = "Invalid arguments for function " + tokens[i] + "()"; string errorType = "SQL"; queryResult.Error = new InputError(errorType, errorMessage); return; } colsToSelect.Add(tokens[i] + tokens[i + 1] + tokens[i + 2] + tokens[i + 3]); colsToValidate.Add(tokens[i + 2]); i += 3; } else if (i + 3 < tokens.Count && tokens[i].ToLower() == "count") { colsToSelect.Add(tokens[i] + tokens[i + 1] + tokens[i + 2] + tokens[i + 3]); colsToValidate.Add(tokens[i + 2]); i += 3; } else { colsToSelect.Add(tokens[i]); colsToValidate.Add(tokens[i]); } } string nameOfTableToSelectFrom = tokens[tableOffset]; // Validate Table's existence for (int i = 0; i < tables.Count; ++i) { if (tables[i].Name == nameOfTableToSelectFrom) { tableToSelectFrom = tables[i]; break; } } if (tableToSelectFrom == null) { string errorMessage = "Could not find table: " + nameOfTableToSelectFrom; string errorType = "SQL"; queryResult.Error = new InputError(errorType, errorMessage); return; } if (!ValidateColumns(ref queryResult, colsToValidate, tableToSelectFrom, /*allowWildcard=*/ true)) { return; } SQLConditional sqlConditional = ParseWhereClause(tokens); // Validate conditional List <string> conditionalColumns = sqlConditional.GetColumns(); if (!ValidateColumns(ref queryResult, conditionalColumns, tableToSelectFrom, /*allowWildCard=*/ false)) { return; } // Execute Selection results = tableToSelectFrom.Select(colsToSelect, sqlConditional); queryResult.Results = results; return; }
// create table test(col1 whatever, col2 whatever, col3 whatever) private string[,] ProcessCreateTableStatement(List <string> tokens) { // assuming only the following rule is accepted // <Create Stm> ::= CREATE TABLE Id '(' <ID List> ')' ------ NO SUPPORT for <Constraint Opt> string newTableName = tokens[2]; if (tables.ContainsKey(newTableName)) { //cannot create a new table with the same name return(new string[, ] { { "Table " + newTableName + " already exists" } }); } List <string> columnNames = new List <string>(); List <TypeEnum> columnTypes = new List <TypeEnum>(); int idCount = 2; for (int i = 4; i < tokens.Count; ++i) { if (tokens[i] == ")") { break; } else if (tokens[i] == ",") { continue; } else { if (idCount == 2) { columnNames.Add(tokens[i]); --idCount; } else if (idCount == 1) { TypeEnum type; switch (tokens[i]) { case "varchar": type = TypeEnum.String; break; case "int": type = TypeEnum.Integer; break; case "float": type = TypeEnum.Float; break; default: return(new string[, ] { { "Type is not supported: " + tokens[i] } }); } columnTypes.Add(type); idCount = 2; } } } IntSchTable newTable = new IntSchTable(newTableName); for (int i = 0; i < columnNames.Count; ++i) { newTable.AddColumn(columnNames[i], columnTypes[i]); } tables.Add(newTableName, newTable); return(new string[, ] { { "Succesfully created table " + newTableName } }); }