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