void Cartesian(JoinSchema target, Schema table1, Schema table2)
{
var cartesianed = new Dictionary <string, List <Tuple <string, string> > >();
int surrogatekey = 1;
foreach (var record1 in table1.Data)
{
var temp = new Dictionary <string, string>();
var temp2 = new Dictionary <string, string>();
var temp3 = new List <Tuple <string, string> >();
foreach (var attribute in record1.Value)
{
temp.Add(attribute.Key, attribute.Value);
}
foreach (var record2 in table2.Data)
{
foreach (var item in temp)
{
temp3.Add(new Tuple <string, string>(item.Key, item.Value));
//temp3.Add(item.Key, item.Value);
}
foreach (var attribute2 in record2.Value)
{
temp2.Add(attribute2.Key, attribute2.Value);
}
foreach (var item in temp2)
{
temp3.Add(new Tuple <string, string>(item.Key, item.Value));
//temp3.Add(item.Key, item.Value);
}
cartesianed.Add(surrogatekey.ToString(), new List <Tuple <string, string> >(temp3));
surrogatekey++;
temp3.Clear();
temp2.Clear();
}
}
foreach (var item in cartesianed)
{
target.Data.Add(item.Key, item.Value);
}
}
private void SubmitButton_Click(object sender, EventArgs e)
{
TableGridView.Columns.Clear();
string result = ParserProvider.ParseSQLCommand(EditorTextBox.Text);
if (!result.Contains(","))
{
EditorTextBox.Text = result;
}
else
{
JoinSchema schema = JsonConvert.DeserializeObject <JoinSchema>(result);
JObject json = JObject.Parse(result);
if (schema.Data.Count != 0)
{
foreach (var item in schema.Data.First().Value)
{
TableGridView.Columns.Add(new DataGridViewTextBoxColumn()
{
HeaderText = item.Item1, Name = item.Item2
});
}
List <string> valueList = new List <string>();
foreach (var item in schema.Data.Values)
{
foreach (var attr in item)
{
valueList.Add(attr.Item2);
}
if (valueList.Any())
{
TableGridView.Rows.Add(valueList.ToArray());
}
valueList.Clear();
}
}
//if (json["Data"].Children().Count() != 0)
//{
// foreach (JProperty item in (JToken)json["Data"]["1"])
// {
// TableGridView.Columns.Add(new DataGridViewTextBoxColumn() { HeaderText = item.Name, Name = item.Name });
// }
// foreach (var record in json["Data"])
// {
// List<string> valueList = new List<string>();
// foreach (JProperty data in record.Values())
// {
// valueList.Add(data.Value.ToString());
// }
// TableGridView.Rows.Add(valueList.ToArray());
// }
//}
else
{
EditorTextBox.Text = "No Values!";
}
}
}
void SortMergeJoin(JoinSchema target, Schema table1, Schema table2, string attr1, string attr2)
{
table1.Data = new Dictionary <string, Dictionary <string, string> >(table1.Data.OrderBy(x => x.Value[attr1]).ToDictionary(x => x.Key, x => x.Value));
table2.Data = new Dictionary <string, Dictionary <string, string> >(table2.Data.OrderBy(x => x.Value[attr2]).ToDictionary(x => x.Key, x => x.Value));
int i = 1;
var tempTable1 = new Schema();
var tempTable2 = new Schema();
foreach (var item in table1.Data)
{
tempTable1.Data.Add(i.ToString(), new Dictionary <string, string>(table1.Data[item.Key]));
//table1.Data.Add(i.ToString(), new Dictionary<string, string>(table1.Data[item.Key]));
i++;
}
i = 1;
foreach (var item in table2.Data)
{
tempTable2.Data.Add(i.ToString(), new Dictionary <string, string>(table2.Data[item.Key]));
//table1.Data.Add(i.ToString(), new Dictionary<string, string>(table1.Data[item.Key]));
i++;
}
table1.Data = new Dictionary <string, Dictionary <string, string> >(tempTable1.Data);
table2.Data = new Dictionary <string, Dictionary <string, string> >(tempTable2.Data);
int surrogatekey = 1;
while (!table1.IsDone() && !table2.IsDone())
{
int leftPosition = table1.Position + 1;
int rightPosition = table2.Position + 1;
List <Tuple <string, string> > newRecord = new List <Tuple <string, string> >();
if (Convert.ToInt32(table1.Data[leftPosition.ToString()][attr1]) == Convert.ToInt32(table2.Data[rightPosition.ToString()][attr2]))
{
foreach (var attribute in table1.Data[leftPosition.ToString()])
{
newRecord.Add(new Tuple <string, string>(attribute.Key, attribute.Value));
}
foreach (var attribute in table2.Data[rightPosition.ToString()])
{
newRecord.Add(new Tuple <string, string>(attribute.Key, attribute.Value));
}
table1.Advance();
table2.Advance();
}
else if (Convert.ToInt32(table1.Data[leftPosition.ToString()][attr1]) < Convert.ToInt32(table2.Data[rightPosition.ToString()][attr2]))
{
table1.Advance();
}
else // if (left.Key > right.Key)
{
table2.Advance();
}
if (newRecord.Any())
{
target.Data.Add(surrogatekey.ToString(), new List <Tuple <string, string> >(newRecord));
surrogatekey++;
}
}
//Cartesian(target, table1, table2);
//foreach (var record in target.Data.Reverse())
//{
// var xx = record.Value.ToLookup(x => x.Item1, x => x.Item2)[attr1];
// var yy = record.Value.ToLookup(y => y.Item1, x => x.Item2)[attr2];
// var table1Attribute = xx.ElementAt(0);
// var table2Attribute = yy.Count() > 1 ? yy.ElementAt(1) : yy.ElementAt(0);
// if (!table1Attribute.Equals(table2Attribute))
// {
// target.Data.Remove(record.Key);
// }
//}
}
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);
}
