string[] GetWhereClause(string p_command)
{
string temp = MySubString.SubString(p_command, p_command.IndexOf("where"), p_command.Length - 1);
temp = temp.Replace("where", "");
temp = temp.Replace(" ", "");
return(temp.Split('='));
}
public string ParseSQLCommand(string p_command)
{
Schema _oldSchema = new Schema();
Schema _newSchema = new Schema();
JoinSchema _joinSchema = new JoinSchema();
string parseResult = "";
string _tableName = "";
string[] SplittedCommand = p_command.Split(' ');
SplittedCommand = SplittedCommand.Where(x => x != "").Select(x => x.Trim()).ToArray();
var _dir = @"C:\DBparser\Schemas";
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
foreach (var item in SplittedCommand)
{
Console.WriteLine(item);
}
Console.WriteLine();
switch (SplittedCommand[0].ToUpper())
{
case "SELECT":
string m_selected = "";
var singleTableName = "";
string[] fromTwoTables = new string[2];
if (SplittedCommand[1] == "*")
{
if (!@SplittedCommand[3].Contains(","))
{
singleTableName = @SplittedCommand[3];
}
else
{
fromTwoTables = MySubString.SubString(p_command, p_command.IndexOf("from") + 5, p_command.Length - 1).Split(',');
for (int i = 0; i < fromTwoTables.Length; i++)
{
fromTwoTables[i] = fromTwoTables[i].Trim();
}
}
if (File.Exists(Path.Combine(_dir, singleTableName + ".txt")) || File.Exists(Path.Combine(_dir, fromTwoTables[0] + ".txt")))
{
_newSchema = new Schema();
if (!p_command.Contains("where"))
{
if (fromTwoTables[1] != null)
{
Schema table1 = JsonConvert.DeserializeObject <Schema>(File.ReadAllText(Path.Combine(_dir, fromTwoTables[0] + ".txt")));
Schema table2 = JsonConvert.DeserializeObject <Schema>(File.ReadAllText(Path.Combine(_dir, fromTwoTables[1] + ".txt")));
//Cartesian(_newSchema, table1, table2);
//parseResult = JsonConvert.SerializeObject(_newSchema);
Cartesian(_joinSchema, table1, table2);
parseResult = JsonConvert.SerializeObject(_joinSchema);
}
else
{
m_selected = File.ReadAllText(Path.Combine(_dir, singleTableName + ".txt"));
var q = JsonConvert.DeserializeObject <Schema>(m_selected);
foreach (var item in q.Data)
{
_joinSchema.Data.Add(item.Key, new List <Tuple <string, string> >());
foreach (var item2 in item.Value)
{
_joinSchema.Data[item.Key].Add(new Tuple <string, string>(item2.Key, item2.Value));
}
}
parseResult = JsonConvert.SerializeObject(_joinSchema);
}
}
else
{
string[] whereClauseKeyValue = new string[2];
whereClauseKeyValue = GetWhereClause(p_command);
if (fromTwoTables[1] == null)
{
_oldSchema = JsonConvert.DeserializeObject <Schema>(File.ReadAllText(Path.Combine(_dir, singleTableName + ".txt")));
foreach (var record in _oldSchema.Data)
{
List <Tuple <string, string> > temp = new List <Tuple <string, string> >();
foreach (var attribute in record.Value)
{
if (attribute.Key == whereClauseKeyValue[0].Trim() && attribute.Value == whereClauseKeyValue[1].Trim().Replace("'", ""))
{
foreach (var item in record.Value)
{
temp.Add(new Tuple <string, string>(item.Key, item.Value));
}
}
}
_joinSchema.Data.Add(record.Key, temp);
}
}
else
{
fromTwoTables[1] = MySubString.SubString(fromTwoTables[1], 0, fromTwoTables[1].IndexOf(' ') - 1);
Schema table1 = JsonConvert.DeserializeObject <Schema>(File.ReadAllText(Path.Combine(_dir, fromTwoTables[0] + ".txt")));
Schema table2 = JsonConvert.DeserializeObject <Schema>(File.ReadAllText(Path.Combine(_dir, fromTwoTables[1] + ".txt")));
//Cartesian(_joinSchema, table1, table2);
if (whereClauseKeyValue.Any(x => x.Contains(".")))
{
var table1WhereAttribute = whereClauseKeyValue[0].Split('.');
var table2WhereAttribute = whereClauseKeyValue[1].Split('.');
SortMergeJoin(_joinSchema, table1, table2, table1WhereAttribute[1], table2WhereAttribute[1]);
}
else
{
//WRECKED
foreach (var record in _newSchema.Data.Reverse())
{
if (record.Value.Any(x => x.Key == whereClauseKeyValue[0].Trim() && x.Value != whereClauseKeyValue[1].Trim().Replace("'", "")))
{
_joinSchema.Data.Remove(record.Key);
}
}
}
}
parseResult = JsonConvert.SerializeObject(_joinSchema);
}
}
else
{
parseResult = "No Schema!!";
}
}
else
{
string m_selectedField = MySubString.SubString(p_command, p_command.IndexOf("select") + 7, p_command.LastIndexOf("from") - 2);
string[] selectedFieldsArray = m_selectedField.Split(',');
selectedFieldsArray = selectedFieldsArray.Select(x => x.Trim()).ToArray();
_tableName = MySubString.SubString(p_command, p_command.LastIndexOf("from") + 5, p_command.Length - 1);
if (File.Exists(Path.Combine(_dir, _tableName + ".txt")))
{
m_selected = File.ReadAllText(Path.Combine(_dir, _tableName + ".txt"));
JObject json = JObject.Parse(m_selected);
List <JProperty> removeList = new List <JProperty>();
foreach (var record in json["Data"])
{
foreach (JProperty data in record.Values().Reverse())
{
if (!selectedFieldsArray.Contains(data.Name))
{
removeList.Add(data);
data.Remove();
}
}
}
m_selected = json.ToString();
parseResult = m_selected;
}
else
{
parseResult = "No Schema!";
}
}
break;
case "UPDATE":
_tableName = @SplittedCommand[1].Replace(System.Environment.NewLine, "").Replace("set", "");
string m_toUpdate = File.ReadAllText(Path.Combine(_dir, _tableName + ".txt"));
string m_fieldsToUpdate = MySubString.SubString(p_command, p_command.IndexOf("set") + 3, p_command.Length - 1);
string[] m_fields = m_fieldsToUpdate.Trim().Split(',');
_oldSchema = JsonConvert.DeserializeObject <Schema>(m_toUpdate);
foreach (var item in m_fields)
{
string[] temp = item.Trim().Split('=');
string attrName = temp[0].Trim();
string updateValue = temp[1].Replace("'", "").Trim();
foreach (var record in _oldSchema.Data.Values)
{
record[attrName] = updateValue;
}
}
File.Delete(Path.Combine(_dir, _tableName + ".txt"));
File.WriteAllText(Path.Combine(_dir, _tableName + ".txt"), JsonConvert.SerializeObject(_oldSchema, Formatting.Indented));
break;
case "INSERT":
string m_tableNameI = @SplittedCommand[2];
string m_value = MySubString.SubString(p_command, p_command.IndexOf('(') + 1, p_command.IndexOf(')') - 1);
Console.WriteLine(m_value);
bool IsInsertError = false;
string[] m_valueSplitted = m_value.Split(',');
_oldSchema = JsonConvert.DeserializeObject <Schema>(File.ReadAllText(Path.Combine(_dir, m_tableNameI + ".txt")));
int insertValueIndex = 0;
int surrogateKey = _oldSchema.Data.Count;
Dictionary <string, string> newRecord = new Dictionary <string, string>();
foreach (var item in _oldSchema.Attributes)
{
int attrLength = GetAttributeLength(item.Value);
if (insertValueIndex < m_valueSplitted.Length)
{
//if (ValidateValueAndType(GetAttributeName(item.Value), m_valueSplitted[insertValueIndex], attrLength))
//{
// newRecord.Add(item.Key, m_valueSplitted[insertValueIndex]);
//}
newRecord.Add(item.Key, m_valueSplitted[insertValueIndex].Replace("'", ""));
}
else
{
newRecord.Add(item.Key, "");
}
insertValueIndex++;
}
surrogateKey += 1;
_oldSchema.Data.Add(surrogateKey.ToString(), newRecord);
if (!IsInsertError)
{
File.Delete(Path.Combine(_dir, m_tableNameI + ".txt"));
File.WriteAllText(Path.Combine(_dir, m_tableNameI + ".txt"), JsonConvert.SerializeObject(_oldSchema, Formatting.Indented));
}
break;
case "CREATE":
string m_tableNameC = SplittedCommand[2];
string m_schema = MySubString.SubString(p_command, p_command.IndexOf('(') + 1, p_command.LastIndexOf(';') - 2);
_newSchema = new Schema(m_tableNameC);
string[] properties = m_schema.Replace(System.Environment.NewLine, "").Split(',');
foreach (var item in properties)
{
string[] singleProperty = item.Split(' ');
_newSchema.Attributes.Add(singleProperty[0], singleProperty[1]);
}
_dir = @"C:\DBparser\Schemas"; // folder location
if (!Directory.Exists(_dir)) // if it doesn't exist, create
{
Directory.CreateDirectory(_dir);
}
// use Path.Combine to combine 2 strings to a path
File.WriteAllText(Path.Combine(_dir, m_tableNameC + ".txt"), JsonConvert.SerializeObject(_newSchema, Formatting.Indented));
parseResult = "Create Table Successfully!";
break;
case "DROP":
_tableName = SplittedCommand[2];
File.Delete(Path.Combine(_dir, _tableName + ".txt"));
parseResult = "Drop " + _tableName;
//File.WriteAllText(Path.Combine(_dir, m_tableNameI + ".txt"), schemaJson.ToString())
break;
case "DELETE":
if (SplittedCommand[1] == "*")
{
_tableName = SplittedCommand[3];
}
else
{
_tableName = SplittedCommand[2];
}
string m_toDelete = File.ReadAllText(Path.Combine(_dir, _tableName + ".txt"));
JObject deleteJson = JObject.Parse(m_toDelete);
deleteJson["Data"] = null;
Console.WriteLine(deleteJson);
File.Delete(Path.Combine(_dir, _tableName + ".txt"));
File.WriteAllText(Path.Combine(_dir, _tableName + ".txt"), deleteJson.ToString());
parseResult = "Delete records from " + _tableName;
break;
}
return(parseResult);
}
string GetAttributeName(string attribute)
{
return(MySubString.SubString(attribute, 0, attribute.IndexOf('(') - 1));
}
int GetAttributeLength(string attribute)
{
attribute = MySubString.SubString(attribute, attribute.IndexOf('(') + 1, attribute.IndexOf(')') - 1);
return(Convert.ToInt32(attribute));
}