private void buildResult(Dictionary <string, List <string> > TableValues, string completeQuery, Parser qParser,
System.Windows.Forms.DataGridView grid_view, QueryState qs)
{
char[] delim = { '|' };
//get left table
//Console.Out.WriteLine(completeQuery);
// Console.Out.WriteLine(completeQuery.Replace
string query = "Select * " + completeQuery.Substring(completeQuery.IndexOf("FROM"));
Console.Out.WriteLine(query + " !!!!!");
string arg = "/C " + path + "sqlite3 " + path + database + " \"" + query + "\" > " + path + "out.txt";
run_query(query, grid_view, qs, qParser);
List <string> table1 = getResult();
List <string[]> temp = new List <string[]>();
foreach (string str in table1)
{
string[] elements = str.Split(delim);
temp.Add(elements);
}
string attName = "";
for (int col = 0; col < temp[0].Length; col++)
{
List <string> add = new List <string>();
for (int row = 0; row < temp.Count; row++)
{
if (row == 0)
{
attName = temp[row][col];
}
else
{
add.Add(temp[row][col]);
}
}
TableValues.Add(attName, add);
}
}
private void reversePortion(List <String> possibleQ, System.Windows.Forms.DataGridView grid_view, Dictionary <int,
HashSet <int> > sCells, Parser qParser, System.Windows.Forms.RichTextBox rtb, QueryState qs, System.Windows.Forms.DataGridView SQLgrid)
{
possibleQ.Clear();
setSelectedCells(grid_view, sCells);
if (sCells.Count < 1)
{
//MessageBox.Show("Nothing is selected.");
}
else
{
string check = checkCells(grid_view, sCells);
if (check == "Valid Selection")
{
primaryKeyOptions(qParser, sCells, grid_view, possibleQ, qs);
if (!joinOP)
{
BETWEEN_entry(qParser, sCells, grid_view, possibleQ, qs);
string str = fillSQLOption(possibleQ, SQLgrid);
rtb.Text = str;
run_query(str, grid_view, qs, qParser);
fill_dataGrid(getResult(), grid_view, qs, qParser);
previousQuery = str;
}
}
else if (check == "Invalid Selection")
{
MessageBox.Show("Invalid selection: Please try again.");
}
else if (check == "Invalid Insert")
{
MessageBox.Show("Invalid Insert. Last row can only be chosen by itself.");
}
else if (check == "Valid Insert")
{
insertEntry(qParser, sCells, grid_view, rtb, qs);
}
}
}
private void BETWEEN_entry(Parser qParser, Dictionary <int, HashSet <int> > sCells,
System.Windows.Forms.DataGridView grid_view, List <String> possibleQ, QueryState qs)
{
//get all results
Dictionary <string, List <string> > TableValues = new Dictionary <string, List <string> >();
buildResult(TableValues, pkQUERY, qParser, grid_view, qs);
string arg = "/C " + path + "sqlite3 " + path + database + " \"" + pkQUERY + "\" > " + path + "out.txt";
run_command(arg);
List <string> answer = getResult();
int total = answer.Count;
string startSQL = pkQUERY.Substring(0, pkQUERY.IndexOf("WHERE") + 5) + " ";
//Console.Out.WriteLine(startSQL);
string middle = pkQUERY.Substring(pkQUERY.IndexOf("FROM"), pkQUERY.IndexOf("WHERE") - pkQUERY.IndexOf("FROM")).Replace(";", "");
foreach (string att in TableValues.Keys)
{
if (advancedQuery)
{
checkTotal(startSQL + att + " = (SELECT MAX(" + att + ") " + middle + ");", total, answer, possibleQ);
checkTotal(startSQL + att + " = (SELECT MIN(" + att + ") " + middle + ");", total, answer, possibleQ);
checkTotal(startSQL + att + " >= (SELECT AVG(" + att + ") " + middle + ");", total, answer, possibleQ);
checkTotal(startSQL + att + " <= (SELECT AVG(" + att + ") " + middle + ");", total, answer, possibleQ);
}
//Console.Out.WriteLine(startSQL + att + " <= (SELECT AVG(" + att + ") " + middle + ");");
List <string> allOfOneKind = new List <string>();
foreach (string val in TableValues[att])
{
string finalSQL = startSQL + att + " = " + "'" + val + "';";
//Console.Out.WriteLine(finalSQL);
checkTotal(finalSQL, total, answer, possibleQ);
if (!allOfOneKind.Contains(val))
{
allOfOneKind.Add(val);
}
checkTotal(startSQL + att + " >= '" + val + "';", total, answer, possibleQ);
checkTotal(startSQL + att + " <= '" + val + "';", total, answer, possibleQ);
if (advancedQuery)
{
foreach (string val1 in TableValues[att])
{
if (val != val1)
{
checkTotal(startSQL + att + " BETWEEN '" + val + "' and '" + val1 + "';", total, answer, possibleQ);
checkTotal(startSQL + att + " <= '" + val + "' or " + att + " >= '" + val1 + "';", total, answer, possibleQ);
//Console.Out.WriteLine(startSQL + att + " >= '" + val + "' or " + att + " <= '" + val1 + "';");
}
}
}
}
bool notfirst = false;
string end = "";
foreach (string str in allOfOneKind)
{
if (notfirst)
{
end += " or ";
}
notfirst = true;
end += att + " = '" + str + "'";
}
string trySQL = startSQL + end + ";";
//Console.Out.WriteLine(finalSQL);
checkTotal(trySQL, total, answer, possibleQ);
}
}
private bool insertEntry(Parser qParser, Dictionary <int, HashSet <int> > sCells,
System.Windows.Forms.DataGridView grid_view, System.Windows.Forms.RichTextBox rtb, QueryState qs)
{
//determine table
string currentTable = "";
int pkColumn = -1;
string newSQL = "";
if (qParser.tables.Count == 1)
{
currentTable = qParser.tables[0];
}
else
{
MessageBox.Show("Can't insert becuase multiple tables are being accessed.");
return(false);
}
//build attr list
if (DBtables[currentTable].attributes.Count == sCells.ElementAt(0).Value.Count)
{
//bool valid = true;
newSQL = "INSERT INTO " + currentTable + " (";
bool notfirst = false;
string updateMiddle = "";
for (int i = 0; i < grid_view.ColumnCount; i++)
{
if (notfirst)
{
newSQL += ",";
updateMiddle += ",";
}
newSQL += grid_view.Columns[i].Name;
updateMiddle += grid_view.Columns[i].Name + " = " + "\'" + grid_view[i, grid_view.RowCount - 1].Value.ToString() + "\'";
if (DBtables[currentTable].attributes.ContainsKey(grid_view.Columns[i].Name.ToLower()))
{
if (DBtables[currentTable].attributes[grid_view.Columns[i].Name.ToLower()][3] == "1")
{
pkColumn = i;
}
}
notfirst = true;
string type = DBtables[currentTable].attributes[grid_view.Columns[i].Name.ToLower()][1];
int number;
float real;
if (type == "int" || type == "real")
{
string checkOK = grid_view[i, grid_view.RowCount - 1].Value.ToString();
if (!Int32.TryParse(checkOK, out number) && !Single.TryParse(checkOK, out real))
{
MessageBox.Show("Invalid type is trying to be inserted. Insert a value with type " + type + ".");
return(false);
}
}
}
newSQL += ")\nVALUES (";
notfirst = false;
for (int i = 0; i < grid_view.ColumnCount; i++)
{
if (notfirst)
{
newSQL += ",";
}
if (grid_view[i, grid_view.RowCount - 1].Value != null)
{
newSQL += "\'" + grid_view[i, grid_view.RowCount - 1].Value.ToString() + "\'";
notfirst = true;
}
else
{
MessageBox.Show("Can't insert becuase of null value.");
return(false);
}
}
newSQL += ");";
if (pkColumn >= 0)
{
string check = "SELECT * FROM " + currentTable + " WHERE " +
grid_view.Columns[pkColumn].Name + " = " + "\'" + grid_view[pkColumn, grid_view.RowCount - 1].Value.ToString() + "\';";
string arg = "/C " + path + "sqlite3 " + path + database + " \"" + check + "\" > " + path + "out.txt";
run_command(arg);
char[] delim = { '|' };
List <string> verify = getResult();
bool delete = true;
if (verify.Count > 0)
{
foreach (string str in verify)
{
string[] elements = str.Split(delim);
for (int col = 0; col < elements.Length; col++)
{
if (elements[col] != grid_view[col, grid_view.RowCount - 1].Value.ToString())
{
delete = false;
}
}
}
if (delete)
{
string deletSQL = "DELETE FROM " + currentTable + " WHERE " + grid_view.Columns[pkColumn].Name + " = " +
"\'" + grid_view[pkColumn, grid_view.RowCount - 1].Value.ToString() + "\';";
newSQL = deletSQL;
}
else
{
string UpdateQuery = "UPDATE " + currentTable + " SET " + updateMiddle + " WHERE " + grid_view.Columns[pkColumn].Name + " = " +
"\'" + grid_view[pkColumn, grid_view.RowCount - 1].Value.ToString() + "\';";
newSQL = UpdateQuery;
}
}
}
if (newSQL.Length > 0)
{
//Console.Out.WriteLine(newSQL);
rtb.Text = newSQL;
run_query(newSQL, grid_view, qs, qParser);
string redisplaySQL = "SELECT * FROM " + currentTable + ";";
run_query(redisplaySQL, grid_view, qs, qParser);
fill_dataGrid(getResult(), grid_view, qs, qParser);
previousQuery = newSQL;
}
}
else
{
MessageBox.Show("Can't insert becuase not all attributes are present.");
return(false);
}
return(true);
}
private void primaryKeyOptions(Parser qParser, Dictionary <int, HashSet <int> > sCells,
System.Windows.Forms.DataGridView grid_view, List <String> possibleQ, QueryState qs)
{
string newSQL = sqlStart(qParser, sCells, grid_view);
bool needOR = false, notfirst = false;
string where = "";
foreach (int row in sCells.Keys)
{
if (needOR)
{
where += " or ";
}
where += "(";
notfirst = false;
needOR = true;
for (int col = 0; col < grid_view.ColumnCount; col++)
{
if (notfirst)
{
where += " and ";
}
where += grid_view.Columns[col].Name + " = " + "'" + grid_view[col, row].Value.ToString() + "'";
notfirst = true;
}
where += ")";
}
newSQL += where + ";";
//Console.Out.WriteLine(newSQL);
pkQUERY = newSQL;
possibleQ.Add(newSQL);
}
private void fill_dataGrid(List <String> result, System.Windows.Forms.DataGridView grid_view, QueryState qs, Parser qParser)
{
qs.clear();
if (qParser.colorMapping.ContainsKey("*"))
{
qs.star = true;
}
if (result.Count > 0)
{
char[] delim = { '|' };
grid_view.AllowUserToAddRows = true;
totalsize = result.Count;
for (int i = 0; i < result.Count; i++)
{
string[] elements = result.ElementAt(i).Split(delim);
for (int j = 0; j < elements.Length; j++)
{
if (i == 0)
{
//setup grid
grid_view.Rows.Clear();
grid_view.ColumnCount = elements.Length;
grid_view.RowCount = result.Count;
//AllowUserToAddRows
grid_view.Visible = true;
grid_view.ColumnHeadersVisible = true;
DataGridViewCellStyle columnHeaderStyle = new DataGridViewCellStyle();
columnHeaderStyle.BackColor = Color.Beige;
columnHeaderStyle.Font = new Font("Verdana", 10, FontStyle.Bold);
grid_view.ColumnHeadersDefaultCellStyle = columnHeaderStyle;
string str = elements[j];
grid_view.Columns[j].Name = str;
if (DB_table.allPK.ContainsKey(str.ToLower()))
{
qs.pkMap.Add(str, j);
}
if (qs.attMap.ContainsKey(str))
{
qs.attMap.Add(str, j);
}
}
else
{
//Console.Out.WriteLine(elements[j] + " element value = ");
//Console.Out.WriteLine("col = " +j + " row = " + (i-1).ToString());
string colName = grid_view.Columns[j].Name.ToLower();
string type = "";
foreach (DB_table look in DBtables.Values)
{
if (look.attributes.ContainsKey(colName))
{
type = look.attributes[colName][1];
}
}
int number;
float real;
if (type == "int" || type == "real")
{
if (Int32.TryParse(elements[j], out number))
{
grid_view[j, i - 1].Value = number;
}
else if (Single.TryParse(elements[j], out real))
{
grid_view[j, i - 1].Value = real;
}
}
else
{
grid_view[j, i - 1].Value = elements[j];
}
//Console.Out.WriteLine(grid_view[j, i - 1 ].Value.ToString());
}
}
}
}
else
{
grid_view.Rows.Clear();
grid_view.ColumnCount = 0;
MessageBox.Show("No result was returned.");
}
}
public void run_query(string SQLquery, System.Windows.Forms.DataGridView grid_view, QueryState qs, Parser qParser)
{
if (SQLquery.Length > 0)
{
//create file to send to sqlite
string[] write_to_in = { ".header on", SQLquery };
System.IO.File.WriteAllLines(@path + "in.txt", write_to_in);
//send command and run
string arg = "/C " + path + "sqlite3 " + path + database + " < " + path + "in.txt > " + path + "out.txt";
run_command(arg);
}
else
{
Console.Out.WriteLine("\'" + SQLquery + "\' is not a valid sql command");
}
}