//Desciption: Joins the instance of R1 and the instance of R2
//Precondition: Files must exist and be in the appropriate folders. Attributes must be valid.
public string Join(string R1FileName, string R2FileName, string R1Attr, string R2Attr)
{
//local vars
OuterTable outerTableForJoin = null;
InnerTable innerTableForJoin = null;
string joinResult;
//iterate through R1 instances
foreach (OuterTable outerTable in outerTables)
{
if (outerTable.relationName == R1FileName)
{
outerTableForJoin = outerTable;
break;
}
}
//iterate through R2 instances
foreach (InnerTable innerTable in innerTables)
{
if (innerTable.relationName == R2FileName)
{
innerTableForJoin = innerTable;
break;
}
}
//do join
joinResult = outerTableForJoin.HashJoin(innerTableForJoin, R1Attr, R2Attr);
//return result
return(joinResult);
}
//Description: Gets selectivity of a join
public double SelectivityOfJoin(string R1FileName, string R2FileName, string R1Attr, string R2Attr)
{
//local vars
string joinResult;
String[] linesOfJoinResult;
double selectivityOfJoin;
OuterTable outerTableOfJoin = null;
InnerTable innerTableOfJoin = null;
//find OuterTable object for R1FileName
foreach (OuterTable outerTable in outerTables)
{
if (outerTable.relationName == R1FileName)
{
outerTableOfJoin = outerTable;
break;
}
}
//find InnerTable object for R2FileName
foreach (InnerTable innerTable in innerTables)
{
if (innerTable.relationName == R2FileName)
{
innerTableOfJoin = innerTable;
break;
}
}
//do join
joinResult = Join(R1FileName, R2FileName, R1Attr, R2Attr);
//split joinResult into its separate lines
linesOfJoinResult = joinResult.Split('\n');
//calcualte selectivity of join
selectivityOfJoin = ((double)(linesOfJoinResult.Length - 1)) / (outerTableOfJoin.numRows * innerTableOfJoin.numRows);
return(selectivityOfJoin);
}
private void ClickEvent(object sender, RoutedEventArgs e)
{
//local vars
string joinResult;
String[] linesOfJoinResult;
double selectivityOfJoin;
//if one of the combo boxes has nothing selected
if ((R1AttributeComboBox.SelectedIndex == -1) || (R2AttributeComboBox.SelectedIndex == -1) ||
(R1InstanceComboBox.SelectedIndex == -1) || (R2InstanceComboBox.SelectedIndex == -1))
{
MessageBox.Show("You must make a selection for each Drop Down Box. Try again.");
return;
}
//Remove all items from R1ListBox
while (R1ListBox.Items.Count != 0)
{
R1ListBox.Items.RemoveAt(0);
}
//remove all items from R2ListBox
while (R2ListBox.Items.Count != 0)
{
R2ListBox.Items.RemoveAt(0);
}
//remove all items from JoinResultListBox
while (JoinResultListBox.Items.Count != 0)
{
JoinResultListBox.Items.RemoveAt(0);
}
//find selected R1 instance
foreach (OuterTable outerTable in tableManager.outerTables)
{
if (outerTable.relationName == (string)R1InstanceComboBox.Items.GetItemAt(R1InstanceComboBox.SelectedIndex))
{
selectedOuterTable = outerTable;
}
}
//find selected R2 instance
foreach (InnerTable innerTable in tableManager.innerTables)
{
if (innerTable.relationName == (string)R2InstanceComboBox.Items.GetItemAt(R2InstanceComboBox.SelectedIndex))
{
selectedInnerTable = innerTable;
}
}
//get selected R1 attribute
selectedR1Attribute = (string)((ComboBoxItem)R1AttributeComboBox.Items.GetItemAt(R1AttributeComboBox.SelectedIndex)).Content;
//get selected R2 attribute
selectedR2Attribute = (string)((ComboBoxItem)R2AttributeComboBox.Items.GetItemAt(R2AttributeComboBox.SelectedIndex)).Content;
//fill list box for R1
foreach (String line in selectedOuterTable.outerTableFile)
{
R1ListBox.Items.Add(line);
}
//fill list box for R2
foreach (String line in selectedInnerTable.innerTableFile)
{
R2ListBox.Items.Add(line);
}
//do join
joinResult = tableManager.Join(selectedOuterTable.relationName, selectedInnerTable.relationName, selectedR1Attribute, selectedR2Attribute);
//split join result into the separate lines
linesOfJoinResult = joinResult.Split('\n');
//remove '\r' from the end of each string
for (int i = 0; i < linesOfJoinResult.Length - 1; ++i)
{
linesOfJoinResult[i] = linesOfJoinResult[i].Substring(0, linesOfJoinResult[i].Length - 1);
}
//put join result in list box
foreach (string line in linesOfJoinResult)
{
JoinResultListBox.Items.Add(line);
}
//calculate selectivity
selectivityOfJoin = tableManager.SelectivityOfJoin(selectedOuterTable.relationName, selectedInnerTable.relationName, selectedR1Attribute, selectedR2Attribute);
//set selectivity label
SelectivityOfJoinLabel.Text = "Selectivity of Join: " + Convert.ToString(selectivityOfJoin);
}
//Desciption: Joins an outer and inner table using the hash join algorithm.
//Precondition: outerAttribute and innerAttribute must be valid attributes
public string HashJoin(InnerTable innerTable, string outerAttribute, string innerAttribute)
{
//local vars
String[] outerAttributes = outerTableFile[0].Split(','); //outer attributes
String[] innerAttributes = innerTable.innerTableFile[0].Split(','); //inner attributes
List <long> matchingRecords = new List <long>(); //contains matching records of current record in outer table
StreamWriter joinFile = File.CreateText("../../../JOIN-FILE/JOIN-FILE.csv"); //file to avoid really long string
string joinResult; //result of join
int valueOfOutterAttribute = -1; //value for current record in outer table
int outterAttrIndx = -1; //which attribute for outer table
int innerAttrIndx = -1; //which attribute for inner table
//determine outer table attribute
for (int i = 0; i < outerAttributes.Length; ++i)
{
if (outerAttributes[i] == outerAttribute)
{
outterAttrIndx = i;
break;
}
}
//determine inner table attribute
for (int i = 0; i < innerAttributes.Length; ++i)
{
if (innerAttributes[i] == innerAttribute)
{
innerAttrIndx = i;
break;
}
}
//write header of csv file
joinFile.Write(outerTableFile[0]);
joinFile.Write(",");
joinFile.Write(innerTable.innerTableFile[0]);
//write new line
joinFile.Write("\r\n");
//iterate through rows of outer table
for (int i = 1; i < outerTableFile.Length; ++i)
{
//get value of attribute we care about for current record
valueOfOutterAttribute = Convert.ToInt32(outerTableFile[i].Split(',')[outterAttrIndx]);
//search for matching records in inner table
matchingRecords = innerTable.indexes[innerAttrIndx].Search(valueOfOutterAttribute);
//write matches to join file
foreach (long lineNumber in matchingRecords)
{
//write current record in outer table
joinFile.Write(outerTableFile[i]);
//write comma
joinFile.Write(',');
//write matching record in inner table
joinFile.Write(innerTable.innerTableFile[lineNumber - 1]);
//write new line except at end of file
joinFile.Write("\r\n");
}
}
//close connection
joinFile.Close();
//turn file into string
joinResult = File.ReadAllText("../../../JOIN-FILE/JOIN-FILE.csv");
//cut off extra new line
joinResult = joinResult.Substring(0, joinResult.Length - 2);
//delete join file
File.Delete("../../../JOIN-FILE/JOIN-FILE.csv");
//return result of join
return(joinResult);
}
