private void button1_Click(object sender, EventArgs e)
{
//string text = System.IO.File.ReadAllText(@"C:\Users\Matthew\Documents\Life\School\UofI\CS360\projTest.txt");
//MessageBox.Show(text);
string[] input = parse_Sentence(textBox1.Text);
SentenceAnalyzer words = new SentenceAnalyzer();
Input_Metadata data = find_sentence_type(input, ref words);
string sql_code = getSQL(data, input, ref words);
MessageBox.Show(sql_code);
BindData(sql_code);
//print_list(test2.get);
// print_list(test2.greater);
// print_list(test2.less);
// print_list(test2.over1);
// print_list(test2.condition);
// print_list(test2.list_of_attributes);
// print_list(test2.list_of_tables);
// print_nested_list(test2.relationship);
// print_nested_list(test2.synonyms);
}
public string getSQL(Input_Metadata data, string[] input, ref SentenceAnalyzer words)
{
string sql = "";
// checks for type GAAAA...
if (isGA(data.type))
{
GA command_segment = new GA();
for (int j = 1; j < data.values.Count; j++)
{
for (int i = 0; i < words.relationship.Count; i++)
{
if (words.relationship[i].Contains(data.values[j]) && !command_segment.from.Contains(words.list_of_tables[i]))
{
command_segment.from.Add(words.list_of_tables[i]);
}
}
command_segment.select.Add(data.values[j]);
}
return(command_segment.generate_SQL());
}
// checks for type GATAAT...
if (isGTA(data.type))
{
GT command_segment = new GT();
for (int j = 1; j < data.values.Count; j++)
{
for (int i = 0; i < words.relationship.Count; i++)
{
if (words.relationship[i].Contains(data.values[j]) && !command_segment.from.Contains(words.list_of_tables[i]))
{
command_segment.from.Add(words.list_of_tables[i]);
}
if (words.list_of_tables.Contains(data.values[j]) && !command_segment.from.Contains(data.values[j]))
{
command_segment.from.Add(data.values[j]);
}
}
}
return(command_segment.generate_SQL());
}
if (data.type == "GACALV")
{
// the attribute that is being projected is the 2nd element of the values list
GACALV command_segment = new GACALV();
// the value of the SELECT statement is found
command_segment.select = data.values[1];
// the value of the FROM statement is found
for (int i = 0; i < words.relationship.Count; i++)
{
// the attribute is checked against the double list of attributes and their tables. If the attribute is found in the ith list, then it belongs to the ith table, so that is where the query is called from
if (words.relationship[i].Contains(data.values[1]))
{
command_segment.from = words.list_of_tables[i];
}
}
// the value of the WHERE statement is found
// the value of the logical operator in the sql statement is found
if (check_Greater(input, ref words))
{
command_segment.logic = ">";
}
else if (check_Less(input, ref words))
{
command_segment.logic = "<";
}
else
{
command_segment.logic = "=";
}
// the name of the attribute used in the condition is found
command_segment.where = data.values[3];
// the value that the attribute is being checked against is found
//for (int j = 0; j < input.Length; j++)
//{
//if (words.over1.Contains(input[j]))
// {
// command_segment.value = input[j + 1];
//MessageBox.Show(command_segment.generate_SQL());
// }
//}
command_segment.value = data.values[5];
return(command_segment.generate_SQL());
}
if (data.type == "GACAV")
{
// the attribute that is being projected is the 2nd element of the values list
GACAV command_segment = new GACAV();
// the value of the SELECT statement is found
command_segment.select = data.values[1];
// the value of the FROM statement is found
for (int i = 0; i < words.relationship.Count; i++)
{
// the attribute is checked against the double list of attributes and their tables. If the attribute is found in the ith list, then it belongs to the ith table, so that is where the query is called from
if (words.relationship[i].Contains(data.values[1]))
{
command_segment.from = words.list_of_tables[i];
}
}
// the value of the WHERE statement is found
// the value of the logical operator in the sql statement is found
command_segment.logic = "=";
// the name of the attribute used in the condition is found
command_segment.where = data.values[3];
// the value that the attribute is being checked against is found
//for (int j = 0; j < input.Length; j++)
//{
//if (words.over1.Contains(input[j]))
// {
// command_segment.value = input[j + 1];
//MessageBox.Show(command_segment.generate_SQL());
// }
//}
command_segment.value = data.values[4];
return(command_segment.generate_SQL());
}
if (data.type == "GTCALV")
{
GTCALV command_segment = new GTCALV();
command_segment.from = data.values[1];
command_segment.where = data.values[3];
if (check_Greater(input, ref words))
{
command_segment.logic = ">";
}
else if (check_Less(input, ref words))
{
command_segment.logic = "<";
}
else
{
command_segment.logic = "=";
}
command_segment.value = data.values[5];
return(command_segment.generate_SQL());
}
if (data.type == "GTCAV")
{
GTCAV command_segment = new GTCAV();
command_segment.from = data.values[1];
command_segment.where = data.values[3];
command_segment.logic = "=";
command_segment.value = data.values[4];
return(command_segment.generate_SQL());
}
if (data.type == "CALVGA")
{
// the attribute that is being projected is the 2nd element of the values list
CALVGA command_segment = new CALVGA();
// the value of the SELECT statement is found
command_segment.select = data.values[5];
// the value of the FROM statement is found
for (int i = 0; i < words.relationship.Count; i++)
{
// the attribute is checked against the double list of attributes and their tables. If the attribute is found in the ith list, then it belongs to the ith table, so that is where the query is called from
if (words.relationship[i].Contains(data.values[5]))
{
command_segment.from = words.list_of_tables[i];
}
}
// the value of the WHERE statement is found
// the value of the logical operator in the sql statement is found
if (check_Greater(input, ref words))
{
command_segment.logic = ">";
}
else if (check_Less(input, ref words))
{
command_segment.logic = "<";
}
else
{
command_segment.logic = "=";
}
// the name of the attribute used in the condition is found
command_segment.where = data.values[1];
command_segment.value = data.values[3];
return(command_segment.generate_SQL());
}
if (data.type == "CAVGA")
{
// the attribute that is being projected is the 2nd element of the values list
CAVGA command_segment = new CAVGA();
// the value of the SELECT statement is found
command_segment.select = data.values[4];
// the value of the FROM statement is found
for (int i = 0; i < words.relationship.Count; i++)
{
// the attribute is checked against the double list of attributes and their tables. If the attribute is found in the ith list, then it belongs to the ith table, so that is where the query is called from
if (words.relationship[i].Contains(data.values[4]))
{
command_segment.from = words.list_of_tables[i];
}
}
// the value of the WHERE statement is found
// the value of the logical operator in the sql statement is found
command_segment.logic = "=";
// the name of the attribute used in the condition is found
command_segment.where = data.values[1];
command_segment.value = data.values[2];
return(command_segment.generate_SQL());
}
if (data.type == "CALVGT")
{
// the attribute that is being projected is the 2nd element of the values list
CALVGT command_segment = new CALVGT();
// the value of the FROM statement is found
command_segment.from = data.values[5];
// the value of the WHERE statement is found
// the value of the logical operator in the sql statement is found
if (check_Greater(input, ref words))
{
command_segment.logic = ">";
}
else if (check_Less(input, ref words))
{
command_segment.logic = "<";
}
else
{
command_segment.logic = "=";
}
// the name of the attribute used in the condition is found
command_segment.where = data.values[1];
command_segment.value = data.values[3];
return(command_segment.generate_SQL());
}
if (data.type == "CAVGT")
{
// the attribute that is being projected is the 2nd element of the values list
CAVGT command_segment = new CAVGT();
// the value of the FROM statement is found
command_segment.from = data.values[4];
// the value of the WHERE statement is found
// the value of the logical operator in the sql statement is found
command_segment.logic = "=";
// the name of the attribute used in the condition is found
command_segment.where = data.values[1];
command_segment.value = data.values[2];
return(command_segment.generate_SQL());
}
return(sql);
}
// get word = G
// condition word = C
// attribute word = A
// table word = T
// over1 word = 1
public Input_Metadata find_sentence_type(string[] input, ref SentenceAnalyzer words)
{
string type = "";
List <string> values = new List <string>();
for (int i = 0; i < input.Length; i++)
{
if (input[i][0] == '\'' && input[i][input[i].Length - 1] == '\'')
{
type += "V";
values.Add(input[i]);
continue;
}
for (int j = 0; j < words.synonyms.Count; j++)
{
if (words.synonyms[j].Contains(input[i]))
{
type += "A";
values.Add(words.list_of_attributes[j]);
}
}
for (int j = 0; j < words.list_of_tables.Count; j++)
{
if (input[i] == words.list_of_tables[j])
{
type += "T";
values.Add(words.list_of_tables[j]);
}
}
for (int j = 0; j < words.get.Count; j++)
{
if (input[i] == words.get[j])
{
type += "G";
values.Add(words.get[j]);
}
}
//for (int j = 0; j < words.over1.Count; j++)
//{
// if (input[i] == words.over1[j])
// {
// type += "1";
// values.Add(words.over1[j]);
// }
// }
for (int j = 0; j < words.condition.Count; j++)
{
if (input[i] == words.condition[j])
{
type += "C";
values.Add(words.condition[j]);
}
}
for (int j = 0; j < words.greater.Count; j++)
{
if (input[i] == words.greater[j])
{
type += "L";
values.Add(words.greater[j]);
}
}
for (int j = 0; j < words.less.Count; j++)
{
if (input[i] == words.less[j])
{
type += "L";
values.Add(words.less[j]);
}
}
}
Input_Metadata data = new Input_Metadata();
data.type = type;
data.values = values;
//MessageBox.Show(data.type);
return(data);
}