public QueryConditionBLL1(List<Item> items, FzRelationEntity sets)
{
//this._resultTuple = new FzTupleEntity();
this._selectedRelations = sets;
this._itemConditions = items;
this._errorMessage = "";
//this._memberships = new List<string>();
this._uRelation = Double.MaxValue;
this._attributes = sets.Scheme.Attributes;
}
public static List<int> GetArrPrimaryKey(FzRelationEntity rel)
{
List<int> result = new List<int>();
int i = 0;
foreach (FzAttributeEntity item in rel.Scheme.Attributes)
{
if (item.PrimaryKey == true)
{
result.Add(i);
}
i++;
}
return result;
}
public static List<int> GetArrPrimaryKey(FzRelationEntity rel)
{
return FzRelationDAL.GetArrPrimaryKey(rel);
}
public FzRelationEntity(FzRelationEntity old)
{
this._relationName = old._relationName;
this._scheme = old._scheme;
this._tuples = old._tuples;
}
public FzRelationEntity ExecuteQuery()
{
FzRelationEntity result = new FzRelationEntity();
try
{
this.GetSectedRelation(); if (this._error) throw new Exception(this._errorMessage);
this.GetSelectedAttr(); if (this._error) throw new Exception(this._errorMessage);
_errorMessage = ExistsAttribute();
if (ErrorMessage != "") { this.Error = true; throw new Exception(_errorMessage); }
if (this._queryText.Contains("where"))
{
List<Item> items = FormatCondition(this._conditionText);
//Check fuzzy set and object here
this.ErrorMessage = ExistsFuzzySet(items);
if (ErrorMessage != "") { this.Error = true; return result; }
QueryConditionBLL condition = new QueryConditionBLL(items, this._selectedRelations);
result.Scheme.Attributes = this._selectedAttributes;
foreach (FzTupleEntity tuple in this._selectedRelations[0].Tuples)
{
if (condition.Satisfy(items, tuple))
{
if (this._selectedAttributeTexts != null)
result.Tuples.Add(GetSelectedAttributes(condition.ResultTuple));
else
result.Tuples.Add(condition.ResultTuple);
}
}
}
else// Select all tuples
{
result.Scheme.Attributes = this._selectedAttributes;
result.RelationName = this._selectedRelations[0].RelationName;
if (this._selectedAttributeTexts != null)
{
foreach (var item in this._selectedRelations[0].Tuples)
result.Tuples.Add(GetSelectedAttributes(item));
}
else
{
foreach (var item in this._selectedRelations[0].Tuples)
result.Tuples.Add(item);
}
}
}
catch (Exception ex)
{
this._error = true;
this._errorMessage = ex.Message;
return result;
}
return result;
}
private String ExistsAttribute1(String[] selectedAttriTexts1, FzRelationEntity rl1)
{
String message = "";
if (selectedAttriTexts1 == null) return "";
foreach (var item in selectedAttriTexts1)
{
int count = 0;
String attr = item.ToLower();
foreach (var item1 in rl1.Scheme.Attributes)
{
if (item1.AttributeName.ToLower() == attr)
count++;
}
if (count == 0)
return message = "Invalid selected object name of attribute: '" + attr + "'.";
}
return message;
}
private void CloseCurrentRelation()
{
try
{
currentRelation = null;
xtraTabDatabase.TabPages[1].Text = "Relation";
GridViewData.Rows.Clear();
GridViewData.Columns.Clear();
UpdateDataRowNumber();
//SwitchValueState(true);
}
catch (Exception ex)
{
MessageBox.Show("ERROR:\n" + ex.Message);
}
}
private void CTMenuRelNode_RenameRelation_Click(object sender, EventArgs e)
{
try
{
if (fdbEntity == null) { MessageBox.Show("Current Database is empty!"); return; }
String relationName = "";
if (currentRelation != null)
relationName = currentRelation.RelationName;
else
relationName = childCurrentNode.Name;
//Set currtn relation
currentRelation = FzRelationBLL.GetRelationByName(relationName, fdbEntity);
renamedRelation = FzRelationBLL.GetRelationByName(relationName, fdbEntity);
DBValues.relationsName = FzRelationBLL.GetListRelationName(fdbEntity);
frmNewName frm = new frmNewName(4);
frm.ShowDialog();
renamedRelation.RelationName = frm.Name;
if (frm.Name == null) return;
if (currentRelation != null)
{
if (renamedRelation.Equals(currentRelation))
{
if (xtraTabDatabase.TabPages[1].Text.Contains("Create Relation"))
xtraTabDatabase.TabPages[1].Text = "Create Relation " + frm.Name;
else xtraTabDatabase.TabPages[1].Text = "Relation " + frm.Name;
childCurrentNode.Name = childCurrentNode.Text = frm.Name;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ShowTuples(FzRelationEntity currentRelation)
{
if (currentRelation.Tuples.Count > 0)
{
int nRow = currentRelation.Tuples.Count;
int nCol = currentRelation.Scheme.Attributes.Count;
FzTupleEntity tuple;
for (int i = 0; i < nRow; i++) // Assign data for GridViewData
{
tuple = currentRelation.Tuples[i];
GridViewData.Rows.Add();
for (int j = 0; j < nCol; j++)
{
GridViewData.Rows[i].Cells[j].Value = tuple.ValuesOnPerRow[j];
}
}
UpdateDataRowNumber();
}
}
private void ShowColumnsAttribute(FzRelationEntity currentRelation)
{
xtraTabDatabase.TabPages[1].Text = "Relation " + currentRelation.RelationName;
xtraTabDatabase.SelectedTabPage = xtraTabDatabase.TabPages[1];
GridViewData.Rows.Clear();
GridViewData.Columns.Clear();
///Add columns to relation
int i = 0;
foreach (FzAttributeEntity attr in currentRelation.Scheme.Attributes)
{
GridViewData.Columns.Add("Column " + i, attr.AttributeName);
i++;
}
}
private void SaveTuples(FzRelationEntity currentRelation)
{
int nRow, nCol;
nRow = GridViewData.Rows.Count - 1;
nCol = GridViewData.Columns.Count;
if (GridViewData.Rows.Count <= 1) return;
GridViewData.CurrentCell = GridViewData.Rows[nRow].Cells[0];
currentRelation.Tuples.Clear();
for (int i = 0; i < nRow; i++)
{
List<Object> objs = new List<object>();
for (int j = 0; j < nCol; j++)
{
if (GridViewData.Rows[i].Cells[j].Value == null)
{
throw new Exception("Value cell is empty!");
}
objs.Add(GridViewData.Rows[i].Cells[j].Value);
}
FzTupleEntity tuple = new FzTupleEntity()
{ ValuesOnPerRow = objs };
currentRelation.Tuples.Add(tuple);
}
}
private void RelationEditor(String create, String open, String delete, String schemeName, string joinrelation1, string joinrelation2, string jointype, string joinname)
{
if (create != String.Empty)
{
AddRelationNode(create, schemeName);//Also add referenced scheme to Relation and add Relation to DB
}
if (open != String.Empty)
{
//IMPORT
currentRelation = FzRelationBLL.GetRelationByName(open, fdbEntity);
///Show the columns attributes of current relation in order to add values
ShowColumnsAttribute(currentRelation);
///Add tuples to relation
ShowTuples(currentRelation);
}
if (joinrelation1 != String.Empty && joinrelation2 != String.Empty && jointype != String.Empty)
{
}
if (delete != String.Empty)
{
FzRelationEntity deleteRelation = FzRelationBLL.GetRelationByName(delete, fdbEntity);
DialogResult result = new DialogResult();
result = MessageBox.Show("Delete this relation ?", "Delete relation " + delete, MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
if (currentRelation != null)
{
if (deleteRelation.Equals(currentRelation))
{
xtraTabDatabase.TabPages[1].Text = "Relation";
GridViewData.Rows.Clear();
GridViewData.Columns.Clear();
UpdateDataRowNumber();
}
}
///Finally, remove NodeRelation and Relation in DB
TreeNode deletedNode = childRelationNode.Nodes[delete];
deletedNode.Remove();
fdbEntity.Relations.Remove(deleteRelation);
deleteRelation = null;
if (childRelationNode.Nodes.Count == 0)
{
childRelationNode.ImageIndex = childRelationNode.SelectedImageIndex = folderImageTree.unselectedState;
}
}
}
}
private void AddRelationNode(String relationName, String schemeName)
{
newRelation = new FzRelationEntity(relationName);
newRelation.Scheme = FzSchemeBLL.GetSchemeByName(schemeName, fdbEntity);
fdbEntity.Relations.Add(newRelation);
TreeNode newNode = new TreeNode();
newNode.Text = relationName;
newNode.Name = relationName;
newNode.ToolTipText = "Relation " + relationName;
newNode.ContextMenuStrip = ContextMenu_RelationNode;
newNode.ImageIndex = relationImageTree.unselectedState;
newNode.SelectedImageIndex = relationImageTree.unselectedState;
childRelationNode.Nodes.Add(newNode);
currentRelation = newRelation;//Advoid null
if (MessageBox.Show("Add values to this relation?", "Add values", MessageBoxButtons.YesNo)
== DialogResult.Yes)
{
ShowColumnsAttribute(newRelation);
}
}
private void ExecutingQuery()
{
try
{
PrepareQuery();
String query = QueryPL.StandardizeQuery(txtQuery.Text.Trim());
String messagefirst = QueryPL.CheckSyntaxfirst(query);
if (messagefirst != "")
{
ShowMessage(messagefirst, Color.Red);
return;
}
FdbEntity newFdb = new FdbEntity() { Relations = fdbEntity.Relations, Schemes = fdbEntity.Schemes };
if (query.Contains("descartes"))
{
_errorMessage = null;
_error = false;
rlAB = new FzRelationEntity();
atAB = new List<FzAttributeEntity>();
_selectedAttributesAB = new List<FzAttributeEntity>();
_conditionTextAB = String.Empty;
_selectedAttributeTextsAB = null;
_indexAB = new List<int>() ;
String relationA = GetRelationTextsA(query);
String queryA = "select * from " + relationA;
QueryPL.StandardizeQuery(queryA);
String message = QueryPL.CheckSyntax(queryA);
if (message != "")
{
ShowMessage(message, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
String relationB = GetRelationB(query);
String queryB = "select * from " + relationB;
QueryPL.StandardizeQuery(queryB);
QueryExcutetionBLL excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("descartes"))
{
int[] arrAB = new int[resultA.Scheme.Attributes.Count];
int same_att = 0;
int a = 0;
bool valueAB;
foreach (FzAttributeEntity att1 in resultA.Scheme.Attributes)
{
a++;
valueAB = false;
foreach (FzAttributeEntity att2 in resultB.Scheme.Attributes)
{
if ((att1.AttributeName.Equals(att2.AttributeName)) && (a < resultA.Scheme.Attributes.Count))
{
valueAB = true;
same_att++;
}
}
if (valueAB == true)
{
arrAB[a - 1] = 1;
}
else
{
arrAB[a - 1] = 0;
}
}
if (same_att > 0)
{
ShowMessage("Between two relations, there mustn't be the same attribute", Color.Red);
return;
}
else
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
FzTupleEntity tpl1;
FzTupleEntity tpl2;
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
if (m == 0)
{
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
if (m < ncol1 - 1)
{
atAB.Add(attribute1);
m++;
}
}
}
atAB.Add(attribute2);
m++;
}
rlAB.Scheme.Attributes = atAB;
int r = 0;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
for (int j = 0; j < nrow2; j++)
{
List<Object> objs = new List<object>();
tpl2 = resultB.Tuples[j];
int k = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
if (k < ncol1 - 1)
{
objs.Add(value1.ToString());
k++;
}
}
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((k >= ncol1 - 1) && (k < m - 1))
{
objs.Add(value2.ToString());
k++;
}
if (k == m - 1 && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) <= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
k++;
}
if (k == m - 1 && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) > Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl2.ValuesOnPerRow[ncol2 - 1]);
k++;
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
r++;
}
}
String queryAB = GetQueryAB(query) + " from " + rlAB.RelationName;
QueryPL.StandardizeQuery(queryAB);
QueryExcutetionBLL excutetionAB = new QueryExcutetionBLL(queryAB.ToLower(), newFdb.Relations);
FzRelationEntity resultAB = new FzRelationEntity();
this._selectedAttributeTextsAB = GetAttributeTextsAB(query);
this._conditionTextAB = GetConditionText(query);
if (_conditionTextAB != String.Empty)
this._conditionTextAB = AddParenthesis(this._conditionTextAB);
this.GetSelectedAttr();
if (this._error) throw new Exception(this._errorMessage);
_errorMessage = ExistsAttribute();
if (ErrorMessage != "") { this.Error = true; throw new Exception(_errorMessage); }
if (query.Contains("where"))
{
List<Item> items = FormatCondition(this._conditionTextAB);
//Check fuzzy set and object here
this.ErrorMessage = ExistsFuzzySet(items);
if (ErrorMessage != "")
{
ShowMessage(ErrorMessage, Color.Red); return;
}
QueryConditionBLL1 condition = new QueryConditionBLL1(items, this.rlAB);
resultAB.Scheme.Attributes = this._selectedAttributesAB;
foreach (FzTupleEntity tuple in this.rlAB.Tuples)
{
if (condition.Satisfy(items, tuple))
{
if (this._selectedAttributeTextsAB != null)
resultAB.Tuples.Add(GetSelectedAttributes(condition.ResultTuple));
else
resultAB.Tuples.Add(condition.ResultTuple);
}
}
}
else// Select all tuples
{
resultAB.Scheme.Attributes = this._selectedAttributesAB;
resultAB.RelationName = this.rlAB.RelationName;
if (this._selectedAttributeTextsAB != null)
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(GetSelectedAttributes(item));
}
else
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(item);
}
}
if (resultAB != null)
{
foreach (FzAttributeEntity attribute in resultAB.Scheme.Attributes)
GridViewResult.Columns.Add(attribute.AttributeName, attribute.AttributeName);
int j, i = -1;
foreach (FzTupleEntity tuple in resultAB.Tuples)
{
GridViewResult.Rows.Add();
i++; j = -1;
foreach (Object value in tuple.ValuesOnPerRow)
GridViewResult.Rows[i].Cells[++j].Value = value.ToString();
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
//txtMessage.Text = "There are "+ GridViewResult.RowCount+" row(s) affected";
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = "There are " + GridViewResult.RowCount + " row(s) affected";
}
}
else
{
if (query.Contains("natural join"))
{
_errorMessage = null;
_error = false;
rlAB = new FzRelationEntity();
atAB = new List<FzAttributeEntity>();
_selectedAttributesAB = new List<FzAttributeEntity>();
_conditionTextAB = String.Empty;
_selectedAttributeTextsAB = null;
_indexAB = new List<int>();
String relationA = GetRelationTextsA(query);
String queryA = "select * from " + relationA;
QueryPL.StandardizeQuery(queryA);
String message = QueryPL.CheckSyntax(queryA);
if (message != "")
{
ShowMessage(message, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
String relationB = GetRelationB(query);
String queryB = "select * from " + relationB;
QueryPL.StandardizeQuery(queryB);
QueryExcutetionBLL excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("natural join"))
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
int[] arr1 = new int[ncol1];
bool same;
int stt = 0;
int stt2;
int dem = 0;
int same1 = 0;
int same2 = 0;
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
same = false;
stt++;
stt2 = 0;
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
stt2++;
if ((attribute1.AttributeName.ToString() == attribute2.AttributeName.ToString()))
{
dem++;
same = true;
arr1[stt - 1] = 1;
if (dem == 1)
{
same1 = stt;
same2 = stt2;
}
}
}
if (same == false && (m < ncol1 - 2))
{
atAB.Add(attribute1);
m++;
arr1[stt - 1] = 0;
}
if (m == ncol1 - 2)
{
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
atAB.Add(attribute2);
m++;
}
}
}
rlAB.Scheme.Attributes = atAB;
int row = 0;
if (dem != 2)
{
ShowMessage("Between two ralations, there is only one the same attribute", Color.Red);
return;
}
else
{
FzTupleEntity tpl1;
FzTupleEntity tpl2;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultB.Tuples[j];
if (tpl1.ValuesOnPerRow[same1 - 1].ToString() == tpl2.ValuesOnPerRow[same2 - 1].ToString())
{
List<Object> objs = new List<object>();
int k = 0;
int stt_cell;
stt_cell = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
stt_cell++;
if ((k < ncol1 - 2) && (arr1[stt_cell - 1] == 0))
{
objs.Add(value1.ToString());
k++;
}
}
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((k >= (ncol1 - 2)) && (k < m - 1))
{
objs.Add(value2.ToString());
k++;
}
}
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) >= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
}
else
{
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) < Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl2.ValuesOnPerRow[ncol2 - 1]);
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
row++;
}
}
}
String queryAB = GetQueryAB(query) + " from " + rlAB.RelationName;
QueryPL.StandardizeQuery(queryAB);
QueryExcutetionBLL excutetionAB = new QueryExcutetionBLL(queryAB.ToLower(), newFdb.Relations);
FzRelationEntity resultAB = new FzRelationEntity();
this._selectedAttributeTextsAB = GetAttributeTextsAB(query);
this._conditionTextAB = GetConditionText(query);
if (_conditionTextAB != String.Empty)
this._conditionTextAB = AddParenthesis(this._conditionTextAB);
this.GetSelectedAttr();
if (this._error) throw new Exception(this._errorMessage);
_errorMessage = ExistsAttribute();
if (ErrorMessage != "") { this.Error = true; throw new Exception(_errorMessage); }
if (query.Contains("where"))
{
List<Item> items = FormatCondition(this._conditionTextAB);
//Check fuzzy set and object here
this.ErrorMessage = ExistsFuzzySet(items);
if (ErrorMessage != "")
{
ShowMessage(ErrorMessage, Color.Red); return;
}
QueryConditionBLL1 condition = new QueryConditionBLL1(items, this.rlAB);
resultAB.Scheme.Attributes = this._selectedAttributesAB;
foreach (FzTupleEntity tuple in this.rlAB.Tuples)
{
if (condition.Satisfy(items, tuple))
{
if (this._selectedAttributeTextsAB != null)
resultAB.Tuples.Add(GetSelectedAttributes(condition.ResultTuple));
else
resultAB.Tuples.Add(condition.ResultTuple);
}
}
}
else// Select all tuples
{
resultAB.Scheme.Attributes = this._selectedAttributesAB;
resultAB.RelationName = this.rlAB.RelationName;
if (this._selectedAttributeTextsAB != null)
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(GetSelectedAttributes(item));
}
else
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(item);
}
}
if (resultAB != null)
{
foreach (FzAttributeEntity attribute in resultAB.Scheme.Attributes)
GridViewResult.Columns.Add(attribute.AttributeName, attribute.AttributeName);
int j, i = -1;
foreach (FzTupleEntity tuple in resultAB.Tuples)
{
GridViewResult.Rows.Add();
i++; j = -1;
foreach (Object value in tuple.ValuesOnPerRow)
GridViewResult.Rows[i].Cells[++j].Value = value.ToString();
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
//txtMessage.Text = "There are " + GridViewResult.RowCount + " row(s) affected";
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = GridViewResult.RowCount + " row(s) affected";
}
}
//
else
{
if (query.Contains("union") && (!query.Contains("union all")))
{
String queryA = null;
queryA = GetQueryA(query);
QueryPL.StandardizeQuery(queryA);
String messageA = null;
messageA = QueryPL.CheckSyntax(queryA);
if (messageA != "")
{
ShowMessage(messageA, Color.Red);
return;
}
String relationB = GetQueryTextsB(query);
String queryB = relationB;
QueryPL.StandardizeQuery(queryB);
String messageB = null;
messageB = QueryPL.CheckSyntax(queryA);
if (messageB != "")
{
ShowMessage(messageB, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
QueryExcutetionBLL excutetionB = null;
excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("union") && (!query.Contains("union all")))
{
int same_att = 0;
int a = 0;
foreach (FzAttributeEntity att1 in resultA.Scheme.Attributes)
{
a++;
int b = 0;
foreach (FzAttributeEntity att2 in resultB.Scheme.Attributes)
{
b++;
if (att1.AttributeName.Equals(att2.AttributeName) && (a == b))
{
same_att++;
}
}
}
if (same_att != resultA.Scheme.Attributes.Count)
{
ShowMessage("The number of columns have to be same, data types have to be same and they have to be in same order.", Color.Red);
return;
}
else
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
GridViewResult.Columns.Add(attribute1.AttributeName, attribute1.AttributeName);
m++;
}
FzTupleEntity tpl1;
FzTupleEntity tpl2;
int r;
r = -1;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
int stt = -1;
Boolean valuecheck = false;
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultB.Tuples[j];
if (tpl1.ValuesOnPerRow[0].ToString() == tpl2.ValuesOnPerRow[0].ToString())
{
valuecheck = true;
}
if (r < nrow2-1)
{
r++;
int k2 = -1;
GridViewResult.Rows.Add();
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if(k2<m-2)
{
GridViewResult.Rows[j].Cells[++k2].Value = value2.ToString();
}
if(tpl1.ValuesOnPerRow[0].ToString() != tpl2.ValuesOnPerRow[0].ToString())
{
GridViewResult.Rows[j].Cells[m-1].Value = value2.ToString();
}
}
}
if ((tpl1.ValuesOnPerRow[0].ToString() == tpl2.ValuesOnPerRow[0].ToString()) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) < Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
GridViewResult.Rows[j].Cells[m - 1].Value = tpl1.ValuesOnPerRow[ncol1 - 1];
}
if ((tpl1.ValuesOnPerRow[0].ToString() == tpl2.ValuesOnPerRow[0].ToString()) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) >= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
GridViewResult.Rows[j].Cells[m - 1].Value = tpl2.ValuesOnPerRow[ncol2 - 1];
}
}
if (valuecheck == false)
{
r++;
int k = -1;
GridViewResult.Rows.Add();
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
GridViewResult.Rows[r].Cells[++k].Value = value1.ToString();
}
}
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = GridViewResult.RowCount + " row(s) affected";
}
}
else
{
if (query.Contains("intersect"))
{
String queryA = null;
queryA = GetQueryA(query);
QueryPL.StandardizeQuery(queryA);
String messageA = null;
messageA = QueryPL.CheckSyntax(queryA);
if (messageA != "")
{
ShowMessage(messageA, Color.Red);
return;
}
String relationB = GetQueryTextsB(query);
String queryB = relationB;
QueryPL.StandardizeQuery(queryB);
String messageB = null;
messageB = QueryPL.CheckSyntax(queryA);
if (messageB != "")
{
ShowMessage(messageB, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
QueryExcutetionBLL excutetionB = null;
excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("intersect"))
{
int same_att = 0;
int a = 0;
foreach (FzAttributeEntity att1 in resultA.Scheme.Attributes)
{
a++;
int b = 0;
foreach (FzAttributeEntity att2 in resultB.Scheme.Attributes)
{
b++;
if (att1.AttributeName.Equals(att2.AttributeName) && (a == b))
{
same_att++;
}
}
}
if (same_att != resultA.Scheme.Attributes.Count)
{
ShowMessage("The number of columns have to be same, data types have to be same and they have to be in same order.", Color.Red);
return;
}
else
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
int row = -1;
//option1
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
GridViewResult.Columns.Add(attribute1.AttributeName, attribute1.AttributeName);
m++;
}
FzTupleEntity tpl1;
FzTupleEntity tpl2;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
String valueCheck = null;
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultB.Tuples[j];
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((tpl1.ValuesOnPerRow[0].ToString() == tpl2.ValuesOnPerRow[0].ToString()) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) > Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
valueCheck = tpl2.ValuesOnPerRow[ncol2 - 1].ToString();
}
if ((tpl1.ValuesOnPerRow[0].ToString() == tpl2.ValuesOnPerRow[0].ToString()) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) <= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
valueCheck = tpl1.ValuesOnPerRow[ncol2 - 1].ToString();
}
}
}
if (valueCheck != null)
{
GridViewResult.Rows.Add();
int k = -1;
row++;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
if (k < m - 2)
{
GridViewResult.Rows[i].Cells[++k].Value = value1.ToString();
}
if (k == m - 2)
{
GridViewResult.Rows[i].Cells[++k].Value = valueCheck;
}
}
}
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = GridViewResult.RowCount + " row(s) affected";
}
}
else
{
if (query.Contains("except"))
{
String queryA = null;
queryA = GetQueryA(query);
QueryPL.StandardizeQuery(queryA);
String messageA = null;
messageA = QueryPL.CheckSyntax(queryA);
if (messageA != "")
{
ShowMessage(messageA, Color.Red);
return;
}
String relationB = GetQueryTextsB(query);
String queryB = relationB;
QueryPL.StandardizeQuery(queryB);
String messageB = null;
messageB = QueryPL.CheckSyntax(queryA);
if (messageB != "")
{
ShowMessage(messageB, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
QueryExcutetionBLL excutetionB = null;
excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("except"))
{
int same_att = 0;
int a = 0;
foreach (FzAttributeEntity att1 in resultA.Scheme.Attributes)
{
a++;
int b = 0;
foreach (FzAttributeEntity att2 in resultB.Scheme.Attributes)
{
b++;
if (att1.AttributeName.Equals(att2.AttributeName) && (a == b))
{
same_att++;
}
}
}
if (same_att != resultA.Scheme.Attributes.Count)
{
ShowMessage("The number of columns have to be same, data types have to be same and they have to be in same order.", Color.Red);
return;
}
else
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
GridViewResult.Columns.Add(attribute1.AttributeName, attribute1.AttributeName);
m++;
}
FzTupleEntity tpl1;
FzTupleEntity tpl2;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
GridViewResult.Rows.Add();
int k = -1;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
String valueCheck = null;
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultB.Tuples[j];
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((tpl1.ValuesOnPerRow[0].ToString() == tpl2.ValuesOnPerRow[0].ToString()) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) <= 1 - Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
valueCheck = tpl1.ValuesOnPerRow[ncol1 - 1].ToString();
}
if ((tpl1.ValuesOnPerRow[0].ToString() == tpl2.ValuesOnPerRow[0].ToString()) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) > 1 - Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
valueCheck = Convert.ToString(1 - Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1]));
}
}
}
if (valueCheck == null)
{
GridViewResult.Rows[i].Cells[++k].Value = value1.ToString();
}
if (valueCheck != null && k < m - 2)
{
GridViewResult.Rows[i].Cells[++k].Value = value1.ToString();
}
if (valueCheck != null && (k == m - 2))
{
GridViewResult.Rows[i].Cells[++k].Value = valueCheck;
}
}
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = GridViewResult.RowCount + " row(s) affected";
}
}
else
{
if (query.Contains("union all"))
{
String queryA = null;
queryA = GetQueryA(query);
QueryPL.StandardizeQuery(queryA);
String messageA = null;
messageA = QueryPL.CheckSyntax(queryA);
if (messageA != "")
{
ShowMessage(messageA, Color.Red);
return;
}
String relationB = GetQueryTextsB(query);
String queryB = relationB;
QueryPL.StandardizeQuery(queryB);
String messageB = null;
messageB = QueryPL.CheckSyntax(queryA);
if (messageB != "")
{
ShowMessage(messageB, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
QueryExcutetionBLL excutetionB = null;
excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("union all"))
{
int same_att = 0;
int a = 0;
foreach (FzAttributeEntity att1 in resultA.Scheme.Attributes)
{
a++;
int b = 0;
foreach (FzAttributeEntity att2 in resultB.Scheme.Attributes)
{
b++;
if (att1.AttributeName.Equals(att2.AttributeName) && (a == b))
{
same_att++;
}
}
}
if (same_att != resultA.Scheme.Attributes.Count)
{
ShowMessage("The number of columns have to be same, data types have to be same and they have to be in same order.", Color.Red);
return;
}
else
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
GridViewResult.Columns.Add(attribute1.AttributeName, attribute1.AttributeName);
m++;
}
FzTupleEntity tpl1;
FzTupleEntity tpl2;
int r;
r = -1;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultB.Tuples[j];
if (r < nrow2 - 1)
{
r++;
int k2 = -1;
GridViewResult.Rows.Add();
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
GridViewResult.Rows[j].Cells[++k2].Value = value2.ToString();
}
}
}
r++;
int k = -1;
GridViewResult.Rows.Add();
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
GridViewResult.Rows[r].Cells[++k].Value = value1.ToString();
}
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = GridViewResult.RowCount + " row(s) affected";
}
}
else
{
if (query.Contains("left join"))
{
_errorMessage = null;
_error = false;
rlAB = new FzRelationEntity();
atAB = new List<FzAttributeEntity>();
_selectedAttributesAB = new List<FzAttributeEntity>();
_conditionTextAB = String.Empty;
_selectedAttributeTextsAB = null;
_indexAB = new List<int>();
String relationA = GetRelationTextsA(query);
String queryA = "select * from " + relationA;
QueryPL.StandardizeQuery(queryA);
String message = QueryPL.CheckSyntax(queryA);
if (message != "")
{
ShowMessage(message, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
String relationB = GetRelationB(query);
String queryB = "select * from " + relationB;
QueryPL.StandardizeQuery(queryB);
QueryExcutetionBLL excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("left join"))
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
int[] arr1 = new int[ncol1];
bool same;
int stt = 0;
int stt2;
int dem = 0;
int same1 = 0;
int same2 = 0;
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
same = false;
stt++;
stt2 = 0;
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
stt2++;
if ((attribute1.AttributeName.ToString() == attribute2.AttributeName.ToString()))
{
dem++;
same = true;
arr1[stt - 1] = 1;
if (dem == 1)
{
same1 = stt;
same2 = stt2;
}
}
}
if (same == false && (m < ncol1 - 2))
{
atAB.Add(attribute1);
m++;
arr1[stt - 1] = 0;
}
if (m == ncol1 - 2)
{
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
atAB.Add(attribute2);
m++;
}
}
}
rlAB.Scheme.Attributes = atAB;
int row = 0;
if (dem != 2)
{
ShowMessage("Between two ralations, there is only one the same attribute", Color.Red);
return;
}
else
{
FzTupleEntity tpl1;
FzTupleEntity tpl2;
bool valuecheck ;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
valuecheck = false;
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultB.Tuples[j];
if (tpl1.ValuesOnPerRow[same1 - 1].ToString() == tpl2.ValuesOnPerRow[same2 - 1].ToString())
{
valuecheck = true;
List<Object> objs = new List<object>();
int k = 0;
int stt_cell;
stt_cell = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
stt_cell++;
if ((k < ncol1 - 2) && (arr1[stt_cell - 1] == 0))
{
objs.Add(value1.ToString());
k++;
}
}
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((k >= (ncol1 - 2)) && (k < m - 1))
{
objs.Add(value2.ToString());
k++;
}
}
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) >= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
}
else
{
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) < Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl2.ValuesOnPerRow[ncol2 - 1]);
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
row++;
}
}
if(valuecheck == false)
{
List<Object> objs = new List<object>();
int k = 0;
int stt_cell;
stt_cell = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
stt_cell++;
if ((k < ncol1 - 2) && (arr1[stt_cell - 1] == 0))
{
objs.Add(value1.ToString());
k++;
}
}
for(int a = 0; a < ncol2;a++)
{
if ((k >= (ncol1 - 2)) && (k < m - 1))
{
objs.Add("NULL");
k++;
}
if (k == m - 1)
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
k++;
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
row++;
}
}
String queryAB = GetQueryAB(query) + " from " + rlAB.RelationName;
QueryPL.StandardizeQuery(queryAB);
QueryExcutetionBLL excutetionAB = new QueryExcutetionBLL(queryAB.ToLower(), newFdb.Relations);
FzRelationEntity resultAB = new FzRelationEntity();
this._selectedAttributeTextsAB = GetAttributeTextsAB(query);
this._conditionTextAB = GetConditionText(query);
if (_conditionTextAB != String.Empty)
this._conditionTextAB = AddParenthesis(this._conditionTextAB);
this.GetSelectedAttr();
if (this._error) throw new Exception(this._errorMessage);
_errorMessage = ExistsAttribute();
if (ErrorMessage != "") { this.Error = true; throw new Exception(_errorMessage); }
if (query.Contains("where"))
{
List<Item> items = FormatCondition(this._conditionTextAB);
//Check fuzzy set and object here
this.ErrorMessage = ExistsFuzzySet(items);
if (ErrorMessage != "")
{
ShowMessage(ErrorMessage, Color.Red); return;
}
QueryConditionBLL1 condition = new QueryConditionBLL1(items, this.rlAB);
resultAB.Scheme.Attributes = this._selectedAttributesAB;
foreach (FzTupleEntity tuple in this.rlAB.Tuples)
{
if (condition.Satisfy(items, tuple))
{
if (this._selectedAttributeTextsAB != null)
resultAB.Tuples.Add(GetSelectedAttributes(condition.ResultTuple));
else
resultAB.Tuples.Add(condition.ResultTuple);
}
}
}
else// Select all tuples
{
resultAB.Scheme.Attributes = this._selectedAttributesAB;
resultAB.RelationName = this.rlAB.RelationName;
if (this._selectedAttributeTextsAB != null)
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(GetSelectedAttributes(item));
}
else
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(item);
}
}
if (resultAB != null)
{
foreach (FzAttributeEntity attribute in resultAB.Scheme.Attributes)
GridViewResult.Columns.Add(attribute.AttributeName, attribute.AttributeName);
int j, i = -1;
foreach (FzTupleEntity tuple in resultAB.Tuples)
{
GridViewResult.Rows.Add();
i++; j = -1;
foreach (Object value in tuple.ValuesOnPerRow)
GridViewResult.Rows[i].Cells[++j].Value = value.ToString();
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
//txtMessage.Text = "There are " + GridViewResult.RowCount + " row(s) affected";
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = GridViewResult.RowCount + " row(s) affected";
}
}
else
if (query.Contains("right join"))
{
_errorMessage = null;
_error = false;
rlAB = new FzRelationEntity();
atAB = new List<FzAttributeEntity>();
_selectedAttributesAB = new List<FzAttributeEntity>();
_conditionTextAB = String.Empty;
_selectedAttributeTextsAB = null;
_indexAB = new List<int>();
String relationA = GetRelationTextsA(query);
String queryA = "select * from " + relationA;
QueryPL.StandardizeQuery(queryA);
String message = QueryPL.CheckSyntax(queryA);
if (message != "")
{
ShowMessage(message, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
String relationB = GetRelationB(query);
String queryB = "select * from " + relationB;
QueryPL.StandardizeQuery(queryB);
QueryExcutetionBLL excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
if (query.Contains("right join"))
{
int ncol2 = resultA.Scheme.Attributes.Count;
int nrow2 = resultA.Tuples.Count;
int ncol1 = resultB.Scheme.Attributes.Count;
int nrow1 = resultB.Tuples.Count;
int m = 0;
int[] arr1 = new int[ncol1];
bool same;
int stt = 0;
int stt2;
int dem = 0;
int same1 = 0;
int same2 = 0;
foreach (FzAttributeEntity attribute1 in resultB.Scheme.Attributes)
{
same = false;
stt++;
stt2 = 0;
foreach (FzAttributeEntity attribute2 in resultA.Scheme.Attributes)
{
stt2++;
if ((attribute1.AttributeName.ToString() == attribute2.AttributeName.ToString()))
{
dem++;
same = true;
arr1[stt - 1] = 1;
if (dem == 1)
{
same1 = stt;
same2 = stt2;
}
}
}
if (same == false && (m < ncol1 - 2))
{
atAB.Add(attribute1);
m++;
arr1[stt - 1] = 0;
}
if (m == ncol1 - 2)
{
foreach (FzAttributeEntity attribute2 in resultA.Scheme.Attributes)
{
atAB.Add(attribute2);
m++;
}
}
}
rlAB.Scheme.Attributes = atAB;
int row = 0;
if (dem != 2)
{
ShowMessage("Between two ralations, there is only one the same attribute", Color.Red);
return;
}
else
{
FzTupleEntity tpl1;
FzTupleEntity tpl2;
bool valuecheck;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultB.Tuples[i];
valuecheck = false;
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultA.Tuples[j];
if (tpl1.ValuesOnPerRow[same1 - 1].ToString() == tpl2.ValuesOnPerRow[same2 - 1].ToString())
{
valuecheck = true;
List<Object> objs = new List<object>();
int k = 0;
int stt_cell;
stt_cell = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
stt_cell++;
if ((k < ncol1 - 2) && (arr1[stt_cell - 1] == 0))
{
objs.Add(value1.ToString());
k++;
}
}
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((k >= (ncol1 - 2)) && (k < m - 1))
{
objs.Add(value2.ToString());
k++;
}
}
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) >= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
}
else
{
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) < Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl2.ValuesOnPerRow[ncol2 - 1]);
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
row++;
}
}
if (valuecheck == false)
{
List<Object> objs = new List<object>();
int k = 0;
int stt_cell;
stt_cell = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
stt_cell++;
if ((k < ncol1 - 2) && (arr1[stt_cell - 1] == 0))
{
objs.Add(value1.ToString());
k++;
}
}
for (int a = 0; a < ncol2; a++)
{
if ((k >= (ncol1 - 2)) && (k < m - 1))
{
objs.Add("NULL");
k++;
}
if ((k == m - 1))
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
k++;
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
row++;
}
}
String queryAB = GetQueryAB(query) + " from " + rlAB.RelationName;
QueryPL.StandardizeQuery(queryAB);
QueryExcutetionBLL excutetionAB = new QueryExcutetionBLL(queryAB.ToLower(), newFdb.Relations);
FzRelationEntity resultAB = new FzRelationEntity();
this._selectedAttributeTextsAB = GetAttributeTextsAB(query);
this._conditionTextAB = GetConditionText(query);
if (_conditionTextAB != String.Empty)
this._conditionTextAB = AddParenthesis(this._conditionTextAB);
this.GetSelectedAttr();
if (this._error) throw new Exception(this._errorMessage);
_errorMessage = ExistsAttribute();
if (ErrorMessage != "") { this.Error = true; throw new Exception(_errorMessage); }
if (query.Contains("where"))
{
List<Item> items = FormatCondition(this._conditionTextAB);
//Check fuzzy set and object here
this.ErrorMessage = ExistsFuzzySet(items);
if (ErrorMessage != "")
{
ShowMessage(ErrorMessage, Color.Red); return;
}
QueryConditionBLL1 condition = new QueryConditionBLL1(items, this.rlAB);
resultAB.Scheme.Attributes = this._selectedAttributesAB;
foreach (FzTupleEntity tuple in this.rlAB.Tuples)
{
if (condition.Satisfy(items, tuple))
{
if (this._selectedAttributeTextsAB != null)
resultAB.Tuples.Add(GetSelectedAttributes(condition.ResultTuple));
else
resultAB.Tuples.Add(condition.ResultTuple);
}
}
}
else// Select all tuples
{
resultAB.Scheme.Attributes = this._selectedAttributesAB;
resultAB.RelationName = this.rlAB.RelationName;
if (this._selectedAttributeTextsAB != null)
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(GetSelectedAttributes(item));
}
else
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(item);
}
}
if (resultAB != null)
{
foreach (FzAttributeEntity attribute in resultAB.Scheme.Attributes)
GridViewResult.Columns.Add(attribute.AttributeName, attribute.AttributeName);
int j, i = -1;
foreach (FzTupleEntity tuple in resultAB.Tuples)
{
GridViewResult.Rows.Add();
i++; j = -1;
foreach (Object value in tuple.ValuesOnPerRow)
GridViewResult.Rows[i].Cells[++j].Value = value.ToString();
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
//txtMessage.Text = "There are " + GridViewResult.RowCount + " row(s) affected";
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
}
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = GridViewResult.RowCount + " row(s) affected";
}
}
else
{
this._selectedRelationTextsAB = null;
lrlAB = new List<FzRelationEntity>();
this._selectedRelationTextsAB = GetRelationTexts(query);
this.GetSectedRelation(newFdb.Relations);
if (this._error) throw new Exception(this._errorMessage);
int t= 0;
t = lrlAB.Count;
if (t >= 3)
{
ShowMessage("Don't support with three or more relations", Color.Red);
return;
}
else
{
if (t == 2)
{
_errorMessage = null;
_error = false;
rlAB = new FzRelationEntity();
atAB = new List<FzAttributeEntity>();
_selectedAttributesAB = new List<FzAttributeEntity>();
_conditionTextAB = String.Empty;
_selectedAttributeTextsAB = null;
_indexAB = new List<int>();
String queryA = null;
queryA = "select * from " + lrlAB[0].RelationName;
QueryPL.StandardizeQuery(queryA);
String messageA = null;
messageA = QueryPL.CheckSyntax(queryA);
if (messageA != "")
{
ShowMessage(messageA, Color.Red);
return;
}
String queryB = "select * from " + lrlAB[1].RelationName;
QueryPL.StandardizeQuery(queryB);
String messageB = null;
messageB = QueryPL.CheckSyntax(queryA);
if (messageB != "")
{
ShowMessage(messageB, Color.Red);
return;
}
QueryExcutetionBLL excutetionA = null;
excutetionA = new QueryExcutetionBLL(queryA.ToLower(), newFdb.Relations);
QueryExcutetionBLL excutetionB = null;
excutetionB = new QueryExcutetionBLL(queryB.ToLower(), newFdb.Relations);
FzRelationEntity resultA = null;
resultA = excutetionA.ExecuteQuery();
if (excutetionA.Error)
{
ShowMessage(excutetionA.ErrorMessage, Color.Red); return;
}
FzRelationEntity resultB = null;
resultB = excutetionB.ExecuteQuery();
if (excutetionB.Error)
{
ShowMessage(excutetionB.ErrorMessage, Color.Red); return;
}
if (resultA != null && resultB != null)
{
int[] arrAB = new int[resultA.Scheme.Attributes.Count];
int same_att = 0;
int a = 0;
bool valueAB;
foreach (FzAttributeEntity att1 in resultA.Scheme.Attributes)
{
a++;
valueAB = false;
foreach (FzAttributeEntity att2 in resultB.Scheme.Attributes)
{
if ((att1.AttributeName.Equals(att2.AttributeName)) && (a < resultA.Scheme.Attributes.Count))
{
valueAB = true;
same_att++;
}
}
if (valueAB == true)
{
arrAB[a - 1] = 1;
}
else
{
arrAB[a - 1] = 0;
}
}
if (same_att == 0)
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
FzTupleEntity tpl1;
FzTupleEntity tpl2;
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
if (m == 0)
{
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
if (m < ncol1 - 1)
{
atAB.Add(attribute1);
m++;
}
}
}
atAB.Add(attribute2);
m++;
}
rlAB.Scheme.Attributes = atAB;
int r = 0;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
for (int j = 0; j < nrow2; j++)
{
List<Object> objs = new List<object>();
tpl2 = resultB.Tuples[j];
int k = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
if (k < ncol1 - 1)
{
objs.Add(value1.ToString());
k++;
}
}
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((k >= ncol1 - 1) && (k < m - 1))
{
objs.Add(value2.ToString());
k++;
}
if (k == m - 1 && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) <= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
k++;
}
if (k == m - 1 && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) > Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl2.ValuesOnPerRow[ncol2 - 1]);
k++;
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
r++;
}
}
String queryAB = GetQueryAB(query) + " from " + rlAB.RelationName;
QueryPL.StandardizeQuery(queryAB);
QueryExcutetionBLL excutetionAB = new QueryExcutetionBLL(queryAB.ToLower(), newFdb.Relations);
FzRelationEntity resultAB = new FzRelationEntity();
this._selectedAttributeTextsAB = GetAttributeTextsAB(query);
this._conditionTextAB = GetConditionText(query);
if (_conditionTextAB != String.Empty)
this._conditionTextAB = AddParenthesis(this._conditionTextAB);
this.GetSelectedAttr();
if (this._error) throw new Exception(this._errorMessage);
_errorMessage = ExistsAttribute();
if (ErrorMessage != "") { this.Error = true; throw new Exception(_errorMessage); }
if (query.Contains("where"))
{
List<Item> items = FormatCondition(this._conditionTextAB);
//Check fuzzy set and object here
this.ErrorMessage = ExistsFuzzySet(items);
if (ErrorMessage != "")
{
ShowMessage(ErrorMessage, Color.Red); return;
}
QueryConditionBLL1 condition = new QueryConditionBLL1(items, this.rlAB);
resultAB.Scheme.Attributes = this._selectedAttributesAB;
foreach (FzTupleEntity tuple in this.rlAB.Tuples)
{
if (condition.Satisfy(items, tuple))
{
if (this._selectedAttributeTextsAB != null)
resultAB.Tuples.Add(GetSelectedAttributes(condition.ResultTuple));
else
resultAB.Tuples.Add(condition.ResultTuple);
}
}
}
else// Select all tuples
{
resultAB.Scheme.Attributes = this._selectedAttributesAB;
resultAB.RelationName = this.rlAB.RelationName;
if (this._selectedAttributeTextsAB != null)
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(GetSelectedAttributes(item));
}
else
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(item);
}
}
if (resultAB != null)
{
foreach (FzAttributeEntity attribute in resultAB.Scheme.Attributes)
GridViewResult.Columns.Add(attribute.AttributeName, attribute.AttributeName);
int j, i = -1;
foreach (FzTupleEntity tuple in resultAB.Tuples)
{
GridViewResult.Rows.Add();
i++; j = -1;
foreach (Object value in tuple.ValuesOnPerRow)
GridViewResult.Rows[i].Cells[++j].Value = value.ToString();
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = "This is decartes operation."+" There are " + GridViewResult.RowCount + " row(s) affected";
}
if (same_att == 1)
{
int ncol1 = resultA.Scheme.Attributes.Count;
int nrow1 = resultA.Tuples.Count;
int ncol2 = resultB.Scheme.Attributes.Count;
int nrow2 = resultB.Tuples.Count;
int m = 0;
bool same;
int stt = 0;
int sttvalue = 0;
int same1 = 0;
int same2 = 0;
int dem = 0;
int stt2;
int[] arrAB1 = new int[ncol1];
foreach (FzAttributeEntity attribute1 in resultA.Scheme.Attributes)
{
same = false;
stt++;
stt2 = 0;
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
stt2++;
if ((attribute1.AttributeName.ToString() == attribute2.AttributeName.ToString()))
{
dem++;
same = true;
arrAB1[stt - 1] = 1;
if (dem == 1)
{
same1 = stt;
same2 = stt2;
}
}
}
if (same == false && (m < ncol1 - 2))
{
atAB.Add(attribute1);
m++;
arrAB1[stt - 1] = 0;
}
if (m == ncol1 - 2)
{
foreach (FzAttributeEntity attribute2 in resultB.Scheme.Attributes)
{
atAB.Add(attribute2);
m++;
}
}
}
rlAB.Scheme.Attributes = atAB;
int row = 0;
FzTupleEntity tpl1;
FzTupleEntity tpl2;
for (int i = 0; i < nrow1; i++)
{
tpl1 = resultA.Tuples[i];
for (int j = 0; j < nrow2; j++)
{
tpl2 = resultB.Tuples[j];
if (tpl1.ValuesOnPerRow[same1 - 1].ToString() == tpl2.ValuesOnPerRow[same2 - 1].ToString())
{
List<Object> objs = new List<object>();
int k = 0;
int stt_cell;
stt_cell = 0;
foreach (Object value1 in tpl1.ValuesOnPerRow)
{
stt_cell++;
if ((k < ncol1 - 2) && (arrAB1[stt_cell - 1] == 0))
{
objs.Add(value1.ToString());
k++;
}
}
foreach (Object value2 in tpl2.ValuesOnPerRow)
{
if ((k >= (ncol1 - 2)) && (k < m - 1))
{
objs.Add(value2.ToString());
k++;
}
}
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) >= Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl1.ValuesOnPerRow[ncol1 - 1]);
}
else
{
if ((k == m - 1) && (Convert.ToDouble(tpl1.ValuesOnPerRow[ncol1 - 1]) < Convert.ToDouble(tpl2.ValuesOnPerRow[ncol2 - 1])))
{
objs.Add(tpl2.ValuesOnPerRow[ncol2 - 1]);
}
}
FzTupleEntity tplab = new FzTupleEntity() { ValuesOnPerRow = objs };
rlAB.Tuples.Add(tplab);
row++;
}
}
}
String queryAB = GetQueryAB(query) + " from " + rlAB.RelationName;
QueryPL.StandardizeQuery(queryAB);
QueryExcutetionBLL excutetionAB = new QueryExcutetionBLL(queryAB.ToLower(), newFdb.Relations);
FzRelationEntity resultAB = new FzRelationEntity();
this._selectedAttributeTextsAB = GetAttributeTextsAB(query);
this._conditionTextAB = GetConditionText(query);
if (_conditionTextAB != String.Empty)
this._conditionTextAB = AddParenthesis(this._conditionTextAB);
this.GetSelectedAttr();
if (this._error) throw new Exception(this._errorMessage);
_errorMessage = ExistsAttribute();
if (ErrorMessage != "") { this.Error = true; throw new Exception(_errorMessage); }
if (query.Contains("where"))
{
List<Item> items = FormatCondition(this._conditionTextAB);
//Check fuzzy set and object here
this.ErrorMessage = ExistsFuzzySet(items);
if (ErrorMessage != "")
{
ShowMessage(ErrorMessage, Color.Red); return;
}
QueryConditionBLL1 condition = new QueryConditionBLL1(items, this.rlAB);
resultAB.Scheme.Attributes = this._selectedAttributesAB;
foreach (FzTupleEntity tuple in this.rlAB.Tuples)
{
if (condition.Satisfy(items, tuple))
{
if (this._selectedAttributeTextsAB != null)
resultAB.Tuples.Add(GetSelectedAttributes(condition.ResultTuple));
else
resultAB.Tuples.Add(condition.ResultTuple);
}
}
}
else// Select all tuples
{
resultAB.Scheme.Attributes = this._selectedAttributesAB;
resultAB.RelationName = this.rlAB.RelationName;
if (this._selectedAttributeTextsAB != null)
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(GetSelectedAttributes(item));
}
else
{
foreach (var item in this.rlAB.Tuples)
resultAB.Tuples.Add(item);
}
}
if (resultAB != null)
{
foreach (FzAttributeEntity attribute in resultAB.Scheme.Attributes)
GridViewResult.Columns.Add(attribute.AttributeName, attribute.AttributeName);
int j, i = -1;
foreach (FzTupleEntity tuple in resultAB.Tuples)
{
GridViewResult.Rows.Add();
i++; j = -1;
foreach (Object value in tuple.ValuesOnPerRow)
GridViewResult.Rows[i].Cells[++j].Value = value.ToString();
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
}
else
{
txtMessage.Text = "There is no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = "This is natural join operation." + " There are " + GridViewResult.RowCount + " row(s) affected";
}
else
{
txtMessage.Text = "Between two ralations, there is only one the same attribute";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
}
}
else
{
String message = null;
message = QueryPL.CheckSyntax(query);
if (message != "")
{
ShowMessage(message, Color.Red);
return;
}
QueryExcutetionBLL excutetion = null;
excutetion = new QueryExcutetionBLL(query.ToLower(), newFdb.Relations);
FzRelationEntity result = null;
result = excutetion.ExecuteQuery();
if (excutetion.Error)
{
ShowMessage(excutetion.ErrorMessage, Color.Red); return;
}
if (query.Contains("oder by"))
{
List<FzTupleEntity> listtuple = new List<FzTupleEntity>();
//List<FzAttributeEntity> listattr = new List<FzAttributeEntity>();
List<String> AttrList = new List<String>();
String[] condition, AttrSelect = null;
int i = 7;//Attribute after "select"
int j = query.IndexOf("from");
String tmp1 = query.Substring(i, j - i);
tmp1 = tmp1.Replace(" ", "");
AttrSelect = tmp1.Split(',');
int left = query.IndexOf("oder by") + 7;//Attribute after "oder by"
int right;
if (query.Contains("desc")){
right = query.IndexOf("desc");
}
else
{
if (query.Contains("asc")){
right = query.IndexOf("asc");
}
else { right = query.Length; }
}
String tmp = query.Substring(left, right - left);
tmp = tmp.Replace(" ", "");
condition = tmp.Split(',');
int indexofAttribute = 0;
for (int a = 0; a < condition.Length; a++ )
{
string temp = condition[a];
for (int b = 0; b < result.Scheme.Attributes.Count; b++)
{
if (result.Scheme.Attributes[b].AttributeName.ToString().ToLower().Contains(temp))
{
indexofAttribute = b;
}
}
for (int c = 0; c < result.Tuples.Count; c++)
{
AttrList.Add(result.Tuples[c].ValuesOnPerRow[indexofAttribute].ToString());
}
}
AttrList.Sort();
for (int d = 0; d < AttrList.Count - 1; d++)
{
if (AttrList[d] == AttrList[d + 1]) { AttrList.Remove(AttrList[d]); }
}
if (query.Contains("desc"))
{
for (int e = AttrList.Count - 1; e >= 0; e--)
{
string temp1 = AttrList[e];
for (int f = 0; f < result.Tuples.Count; f++)
{
if (AttrList[e].Equals(result.Tuples[f].ValuesOnPerRow[indexofAttribute]))
{
listtuple.Add(result.Tuples[f]);
}
}
}
}
else
{
for (int e = 0; e < AttrList.Count; e++)
{
string temp = AttrList[e];
for (int f = 0; f < result.Tuples.Count; f++)
{
if (temp.Equals(result.Tuples[f].ValuesOnPerRow[indexofAttribute]))
{
listtuple.Add(result.Tuples[f]);
}
}
}
}
result.Tuples.Clear();
foreach (FzTupleEntity tuple in listtuple)
{
result.Tuples.Add(tuple);
}
}
if(query.Contains("top")) {
int left = 11;//Attribute after "select"
int right = query.IndexOf("from");
String numtop = query.Substring(left, right - left - 1);
if (int.Parse(numtop.ToLower().ToString()) > result.Tuples.Count)
{
numtop = result.Tuples.Count.ToString();
}
List<FzTupleEntity> listtuple = new List<FzTupleEntity>();
for (int i = 0; i < int.Parse(numtop.ToLower().ToString()); i++) {
listtuple.Add(result.Tuples[i]);
}
result.Tuples.Clear();
foreach (FzTupleEntity tuple in listtuple) {
result.Tuples.Add(tuple);
}
}
if (query.Contains("max") || query.Contains("min") || query.Contains("sum") || query.Contains("avg"))
{
int indexofAttr = 0;
int a = query.IndexOf("(");
int b = query.IndexOf(")");
string c = query.Substring(a, b - a);
c = c.Replace("(", "");
c = c.Replace(")", "");
c = c.Replace(" ", "");
int indexofAttrSum = 0;
int indexofAttrAvg = 0;
if (query.Contains("sum") || query.Contains("avg")) {
String[] AttrSumAvg = null;
int i = 7;//Attribute after "select"
int j = query.IndexOf("from");
String tmp1 = query.Substring(i, j - i);
tmp1 = tmp1.Replace(" ", "");
AttrSumAvg = tmp1.Split(',');
for (int ii = 0; ii < AttrSumAvg.Length; ii++)
{
if (AttrSumAvg[ii].Contains("sum")){
indexofAttrSum = ii;
if (AttrSumAvg[ii].Contains("max"))
{
c = c.Replace("max", "");
}
if (AttrSumAvg[ii].Contains("min"))
{
c = c.Replace("min", "");
}
}
if (AttrSumAvg[ii].Contains("avg"))
{
indexofAttrAvg = ii;
}
}
}
for (int j = 0; j < result.Scheme.Attributes.Count; j++)
{
if (result.Scheme.Attributes[j].AttributeName.ToString().ToLower().Contains(c))
{
indexofAttr = j;
}
}
double value = double.Parse(result.Tuples[0].ValuesOnPerRow[indexofAttr].ToString());
double membership = double.Parse(result.Tuples[0].ValuesOnPerRow[result.Scheme.Attributes.Count - 1].ToString());
double temp ;
for (int i = 1; i < result.Tuples.Count; i++)
{
temp = double.Parse(result.Tuples[i].ValuesOnPerRow[indexofAttr].ToString());
if (query.Contains("max"))
{
if (temp > value)
{
value = temp;
}
}
if (query.Contains("min"))
{
if (temp < value)
{
value = temp;
}
}
if (query.Contains("sum") || query.Contains("avg"))
{
if (query.Contains("max") || query.Contains("min"))
{
//value += temp;
}
else
{
value += temp;
}
if (membership > double.Parse(result.Tuples[i].ValuesOnPerRow[result.Scheme.Attributes.Count - 1].ToString()))
{
membership = double.Parse(result.Tuples[i].ValuesOnPerRow[result.Scheme.Attributes.Count - 1].ToString());
}
}
}
List<FzTupleEntity> listtuple = new List<FzTupleEntity>();
if (query.Contains("max") || query.Contains("min"))
{
for (int i = 0; i < result.Tuples.Count; i++)
{
if (double.Parse(result.Tuples[i].ValuesOnPerRow[indexofAttr].ToString()) == value)
{
listtuple.Add(result.Tuples[i]);
}
}
}
if (query.Contains("sum") || query.Contains("avg"))
{
if (query.Contains("sum"))
{
if (query.Contains("max") || query.Contains("min"))
{
result.Tuples[0].ValuesOnPerRow[indexofAttrSum] = value * listtuple.Count;
}
else
{
result.Tuples[0].ValuesOnPerRow[indexofAttrSum] = value;
}
}
if (query.Contains("avg"))
{
if (query.Contains("max") || query.Contains("min"))
{
result.Tuples[0].ValuesOnPerRow[indexofAttrAvg] = value;
}
else
{
result.Tuples[0].ValuesOnPerRow[indexofAttrAvg] = value / result.Tuples.Count;
}
}
result.Tuples[0].ValuesOnPerRow[result.Scheme.Attributes.Count - 1] = membership;
listtuple.Clear();
listtuple.Add(result.Tuples[0]);
//return;
}
result.Tuples.Clear();
foreach (FzTupleEntity tuple in listtuple)
{
result.Tuples.Add(tuple);
}
}
if (result != null)
{
foreach (FzAttributeEntity attribute in result.Scheme.Attributes)
GridViewResult.Columns.Add(attribute.AttributeName, attribute.AttributeName);
int j, i = -1;
foreach (FzTupleEntity tuple in result.Tuples)
{
GridViewResult.Rows.Add();
i++; j = -1;
foreach (Object value in tuple.ValuesOnPerRow)
GridViewResult.Rows[i].Cells[++j].Value = value.ToString();
}
xtraTabQueryResult.SelectedTabPageIndex = 0;
}
else
{
txtMessage.Text = "There are no relation satisfy the condition";
xtraTabQueryResult.SelectedTabPageIndex = 1;
}
siStatus.Caption = "Ready";
txtMessage.ForeColor = Color.Black;
txtMessage.Text = "There are " + GridViewResult.RowCount + " row(s) affected";
}
}
}
}
}
}
}
}
}
}
catch (Exception ex)
{
return;
//MessageBox.Show("ERROR:\n" + ex.Message);
}
}
/// <summary>
/// Delete tree node on treeList and also delete Object in db
/// </summary>
private void DeleteTreeNode(String deleteNodeName, FzSchemeEntity schemeEntity, FzRelationEntity relationEntity, FzQueryEntity queryEntity)
{
if (schemeEntity != null)
{
TreeNode deletedNode = childSchemeNode.Nodes[deleteNodeName];
deletedNode.Remove();
fdbEntity.Schemes.Remove(schemeEntity);
schemeEntity = null;
if (childSchemeNode.Nodes.Count == 0)
{
childSchemeNode.ImageIndex = childSchemeNode.SelectedImageIndex = 2;
}
}
if (relationEntity != null)
{
TreeNode deletedNode = childRelationNode.Nodes[deleteNodeName];
deletedNode.Remove();
fdbEntity.Relations.Remove(relationEntity);
relationEntity = null;
if (childRelationNode.Nodes.Count == 0)
{
childRelationNode.ImageIndex = childRelationNode.SelectedImageIndex = 2;
}
}
if (queryEntity != null)
{
TreeNode deletedNode = childQueryNode.Nodes[deleteNodeName];
deletedNode.Remove();
fdbEntity.Queries.Remove(queryEntity);
queryEntity = null;
if (childQueryNode.Nodes.Count == 0)
{
childQueryNode.ImageIndex = childQueryNode.SelectedImageIndex = 2;//folder close
}
}
}
public bool OpenFuzzyDatabase(FdbEntity fdb)
{
try
{
SqliteConnection connection = new SqliteConnection(fdb.ConnString);
DataSet ds = new DataSet();
ds.Tables.Add(connection.GetDataTable("SELECT * FROM SystemScheme", "system_scheme"));//Table [0]
ds.Tables.Add(connection.GetDataTable("SELECT * FROM SystemRelation", "system_relation"));//Table [1]
ds.Tables.Add(connection.GetDataTable("SELECT * FROM SystemAttribute", "system_attribute"));//Table [2]
ds.Tables.Add(connection.GetDataTable("SELECT * FROM SystemQuery", "system_query"));//Table [3]
///Load Schemes////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow row in ds.Tables["system_scheme"].Rows)
{
String schemeName = row[1].ToString();
FzSchemeEntity tmpScheme = new FzSchemeEntity(schemeName);
DataTable tmpDt = new DataTable();
tmpDt = connection.GetDataTable("SELECT * FROM SystemAttribute Where SchemeID=" + Convert.ToInt16(row[0]));
if (tmpDt != null)
{
foreach (DataRow item in tmpDt.Rows)
{
Boolean primaryKey = Convert.ToBoolean(item[1]);
String attributeName = Convert.ToString(item[2]);
String typeName = Convert.ToString(item[3]);
String domain = Convert.ToString(item[4]);
String description = Convert.ToString(item[5]);
FzDataTypeEntity tmpDataType = new FzDataTypeEntity(typeName, domain);
FzAttributeEntity tmpAttribute = new FzAttributeEntity(primaryKey, attributeName, tmpDataType, description);
tmpScheme.Attributes.Add(tmpAttribute);
}
fdb.Schemes.Add(tmpScheme);
}
}
///Load Relations//////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow row in ds.Tables["system_relation"].Rows)
{
String relationName = row[1].ToString();
int schemeID = Convert.ToInt16(row[2]);//To get scheme is referenced
String schemeName = connection.GetValueField("SELECT SchemeName FROM SystemScheme WHERE ID=" + schemeID).ToString();
DataTable tmpDt = new DataTable();
tmpDt = connection.GetDataTable("SELECT * FROM " + relationName);
FzRelationEntity tmpRelation = new FzRelationEntity(relationName);//Relation only content relation name, but scheme referenced and list tuples is null
tmpRelation.Scheme = FzSchemeDAL.GetSchemeByName(schemeName, fdb);//Assign scheme referenced to relation, but tuples is null
int nColumns = tmpRelation.Scheme.Attributes.Count;//Get number columns of per row
if (tmpDt != null)//
{
foreach (DataRow tupleRow in tmpDt.Rows)
{
List<Object> objs = new List<object>();
for (int i = 0; i < nColumns; i++)//Add values on per row from tupleRow[i]
{
//values += tupleRow[i].ToString();
objs.Add(tupleRow[i]);
}
FzTupleEntity tmpTuple = new FzTupleEntity() { ValuesOnPerRow = objs };
tmpRelation.Tuples.Add(tmpTuple);
}
}
fdb.Relations.Add(tmpRelation);
}
///Load Queries////////////////////////////////////////////////////////////////////////////////////////////
foreach (DataRow row in ds.Tables["system_query"].Rows)
{
FzQueryEntity tmpQuery = new FzQueryEntity(row[1].ToString(), row[2].ToString());
fdb.Queries.Add(tmpQuery);
}
return true;
}
catch (SQLiteException ex)
{
throw new Exception("ERROR:\n" + ex.Message);
//return false;
}
}
private void treeList1_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
childCurrentNode = e.Node;
String name = childCurrentNode.Name;
if (childCurrentNode.Parent == childRelationNode)
{
currentRelation = FzRelationBLL.GetRelationByName(name, fdbEntity);
ShowColumnsAttribute(currentRelation);
ShowTuples(currentRelation);
}
if (childCurrentNode.Parent == childSchemeNode)
{
currentScheme = FzSchemeBLL.GetSchemeByName(name, fdbEntity);
OpenScheme(currentScheme);
}
if (childCurrentNode.Parent == childQueryNode)
{
currentQuery = FzQueryBLL.GetQueryByName(name, fdbEntity);
OpenQuery(name);
txtQuery.Focus();
}
}
private void CTMenuRelNode_OpenRelation_Click(object sender, EventArgs e)
{
try
{
string relationName = childCurrentNode.Name;
treeList1.SelectedNode = childCurrentNode;
currentRelation = FzRelationBLL.GetRelationByName(relationName, fdbEntity);
ShowColumnsAttribute(currentRelation);
ShowTuples(currentRelation);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}