/* print aggregate updates */
public static void printUpdates(DataTable table)
{
// just do stuff.
Console.WriteLine("Aggregate Update:");
Aux.printschema(table.Columns);
foreach (DataRow dr in table.Rows)
{
Aux.printrow(dr);
}
}
public Form1()
{
InitializeComponent();
List <string> relations = new List <string>();
relations.Add("customer");
relations.Add("borrower");
relations.Add("loan");
relations.Add("branch");
relations.Add("depositor");
relations.Add("account");
this.statman = new StatisticsManager(relations);
List <string> items = Aux.printMenu();
this.listBox1.Items.AddRange(items.ToArray());
this.BtnForExecute.Visible = true;
}
private void executeQuery(object sender, DoWorkEventArgs e)
{
int queryId = (int)e.Argument;
DataTable output = new DataTable();
switch (queryId)
{
case 1:
Query1 q1 = new Query1();
/* execute query */
q1.exec();
output = q1.getResults();
/*update stats */
if (statman.retrieveStat("count.query1") == -1)
{
statman.batchUpdateStats(q1.getStats());
}
break;
case 2:
Query2 q2 = new Query2();
/* execute query */
q2.exec();
output = q2.getResults();
/* update stats */
if (statman.retrieveStat("count.query2") == -1)
{
statman.batchUpdateStats(q2.getStats());
}
break;
case 3:
Query3 q3 = new Query3();
/* execute query */
q3.exec();
output = q3.getResults();
/* update stats */
if (statman.retrieveStat("count.query3") == -1)
{
statman.batchUpdateStats(q3.getStats());
}
break;
case 4:
Query4 q4 = new Query4();
/* execute query */
q4.exec();
output = q4.getResults();
/* update stats */
if (statman.retrieveStat("count.query4") == -1)
{
statman.batchUpdateStats(q4.getStats());
}
break;
case 5:
Query5 q5 = new Query5();
/* execute query */
q5.exec();
output = q5.getResults();
/* update stats */
if (statman.retrieveStat("count.query5") == -1)
{
statman.batchUpdateStats(q5.getStats());
}
break;
case 6:
Query6 q6 = new Query6();
/* execute query */
q6.passForm(this, output);
q6.exec();
output = q6.getResults();
break;
case 7:
Query7 q7 = new Query7();
/* execute query */
q7.passForm(this, output);
q7.exec();
output = q7.getResults();
/* update stats */
if (statman.retrieveStat("count.query7") == -1)
{
statman.batchUpdateStats(q7.getStats());
}
break;
case 8:
Query8 q8 = new Query8();
/* execute query */
q8.passForm(this, output);
q8.exec();
output = q8.getResults();
/* update stats */
if (statman.retrieveStat("count.query8") == -1)
{
statman.batchUpdateStats(q8.getStats());
}
break;
case 9:
Query9 q9 = new Query9();
/* execute query */
q9.passForm(this, output);
q9.exec();
output = q9.getResults();
/* update stats */
if (statman.retrieveStat("count.query9") == -1)
{
statman.batchUpdateStats(q9.getStats());
}
break;
case 10:
Query10 q10 = new Query10();
/* execute query */
q10.passForm(this, output);
q10.exec();
output = q10.getResults();
/* update stats */
if (statman.retrieveStat("count.query10") == -1)
{
statman.batchUpdateStats(q10.getStats());
}
break;
case 11:
Query11 q11 = new Query11();
/* execute query */
q11.exec();
output = q11.getResults();
/* update stats */
if (statman.retrieveStat("count.query11") == -1)
{
statman.batchUpdateStats(q11.getStats());
}
break;
case 12:
Query12 q12 = new Query12();
/* execute query */
q12.exec();
output = q12.getResults();
/* update stats */
if (statman.retrieveStat("count.query12") == -1)
{
statman.batchUpdateStats(q12.getStats());
}
break;
case 13:
Query13 q13 = new Query13();
/* execute query */
q13.exec();
output = q13.getResults();
/* update stats */
if (statman.retrieveStat("count.query13") == -1)
{
statman.batchUpdateStats(q13.getStats());
}
break;
case 14:
Query14 q14 = new Query14();
/* execute query */
q14.exec();
output = q14.getResults();
/* update stats */
if (statman.retrieveStat("count.query14") == -1)
{
statman.batchUpdateStats(q14.getStats());
}
break;
case 15:
Query15 q15 = new Query15();
/* execute query */
q15.exec();
output = q15.getResults();
/* update stats */
if (statman.retrieveStat("count.query15") == -1)
{
statman.batchUpdateStats(q15.getStats());
}
break;
default:
Aux.print("Invalid option.");
output.Columns.Add("Invalid Option", System.Type.GetType("System.String"));
break;
} /* end switch */
e.Result = output;
}
/* currently only handles integer comparisons */
public void open(DataTable data)
{
if (!m_groupby)
{
/* bulk count on the field specified */
/* need to put in distinct test here */
DataColumn cntCol = null;
if (!m_cntfield)
{
cntCol = new DataColumn("count(*)", System.Type.GetType("System.Int32"));
}
else
{
cntCol = new DataColumn("count(" + m_field + ")", System.Type.GetType("System.Int32"));
}
/* just counting the number of rows */
m_cnt = data.Rows.Count;
m_results.Columns.Add(cntCol);
List <int> value = new List <int>();
value.Add(m_cnt);
m_results.Rows.Add(value.ToArray()[0]);
}
else
{
/* pack them into bins and calculate for the bins */
/* track bins we have so far */
Dictionary <string, int> bins = new Dictionary <string, int>();
int valueIndex = data.Columns.IndexOf(m_field);
int groupIndex = data.Columns.IndexOf(m_grouping);
/* build result relation */
/* m_grouping | count | sum | average */
/* the counts and sum are all floats to handle any case where it's a value */
DataColumn groupCol = new DataColumn(m_grouping, System.Type.GetType("System.String"));
StringBuilder sb = new StringBuilder();
sb.Append("count(");
if (m_distinct)
{
sb.Append("distinct ");
}
sb.Append(m_field);
sb.Append(")");
/* should make it a double; but this is to simplify other pieces */
DataColumn countCol = new DataColumn(sb.ToString(), System.Type.GetType("System.Int32"));
m_results.Columns.Add(groupCol);
m_results.Columns.Add(countCol);
/* end build result table */
/* to handle distinct */
List <string> valuesSeen = new List <string>();
int printBinUpdates = 0;
string userAggregateInput = string.Empty;
userAggregateInput = this.m_form.GetUserAggregateInput();
/* note even if the userAggregateInput is invalid it will correctly fill */
List <DataRow> completeRows = new List <DataRow>();
if (userAggregateInput.CompareTo(string.Empty) == 0)
{
DataRow[] rows = new DataRow[data.Rows.Count];
data.Rows.CopyTo(rows, 0);
completeRows.AddRange(rows);
}
else
{
/* will this not work with integers */
DataRow[] rows = data.Select(this.m_grouping + " = '" + userAggregateInput + "'");
completeRows.AddRange(rows);
DataRow[] other = data.Select(this.m_grouping + " <> '" + userAggregateInput + "'");
completeRows.AddRange(other);
}
/* begin parsing data */
while (data.Rows.Count > 0)
{
/* user has specified new input */
if (userAggregateInput.CompareTo(this.m_form.GetUserAggregateInput()) != 0)
{
completeRows.Clear();
userAggregateInput = this.m_form.GetUserAggregateInput();
DataRow[] rows = data.Select(this.m_grouping + " = '" + userAggregateInput + "'");
completeRows.AddRange(rows);
DataRow[] other = data.Select(this.m_grouping + " <> '" + userAggregateInput + "'");
completeRows.AddRange(other);
}
DataRow dr = completeRows[0];
Object[] obs = dr.ItemArray;
object value = obs[valueIndex];
string group = (string)obs[groupIndex];
/* we have this group */
if (bins.ContainsKey(group))
{
Boolean distinct_add = true;
if (m_distinct)
{
/* we care about distinct and we've seen this before */
if (valuesSeen.Contains(group.ToString() + value.ToString()))
{
distinct_add = false;
}
}
if (distinct_add)
{
int seekIndex;
/* we have a bin for this group */
bins.TryGetValue(group, out seekIndex);
Object[] obsInTable = m_results.Rows[seekIndex].ItemArray;
/* get current values in group */
int currentCount = (int)obsInTable[1];
currentCount++;
/* should only return 1 item because we only have 1 row per group */
DataRow[] editRow = m_results.Select(m_grouping + " = '" + group + "'");
editRow[0][countCol] = currentCount;
}
}
else
{
/* this is first time we've seen group */
bins.Add(group, m_results.Rows.Count);
/* add it to distinct filter (regardless if we care */
valuesSeen.Add(group.ToString() + value.ToString());
/* set values for m_results */
DataRow newRow = m_results.NewRow();
newRow[0] = group;
newRow[1] = 1;
m_results.Rows.Add(newRow);
} /* end if bins.ContainsKey(group) */
data.Rows.Remove(dr);
completeRows.RemoveAt(0);
System.Threading.Thread.Sleep(this.m_form.GetUserPause());
printBinUpdates++;
if (printBinUpdates == 5)
{
Aux.printUpdates(m_results);
printBinUpdates = 0;
m_update = m_results.Copy();
this.m_form.SetText("Aggregate Update");
this.m_form.SetDataView(m_update);
}
} /* for each tuple */
} /* end if group by */
}
/* currently only handles integer comparisons */
public void open(DataTable data)
{
if (!m_groupby)
{
StringBuilder sb = new StringBuilder();
sb.Append("sum(");
sb.Append(m_field);
sb.Append(")");
DataColumn sumCol = new DataColumn(sb.ToString(), System.Type.GetType("System.Int32"));
m_results.Columns.Add(sumCol);
/* bulk average on the field specified */
int sum = 0;
int columnIndex = data.Columns.IndexOf(m_field);
/* need to calculate sum of all for that field specified */
foreach (DataRow dr in data.Rows)
{
/* grab value of field and add to sum */
Object[] obs = dr.ItemArray;
sum += (int)obs[columnIndex];
}
List <int> value = new List <int>();
value.Add(sum);
m_results.Rows.Add(value.ToArray()[0]);
}
else
{
/* pack them into bins and calculate for the bins */
/* track bins we have so far */
Dictionary <string, int> bins = new Dictionary <string, int>();
/* columns indices we care about from input */
int valueIndex = data.Columns.IndexOf(m_field);
int groupIndex = data.Columns.IndexOf(m_grouping);
/* build result relation */
/* m_grouping | sum */
/* the counts and sum are all floats to handle any case where it's a value */
DataColumn groupCol = new DataColumn(m_grouping, System.Type.GetType("System.String"));
StringBuilder sb = new StringBuilder();
sb.Append("sum(");
sb.Append(m_field);
sb.Append(")");
DataColumn sumCol = new DataColumn(sb.ToString(), System.Type.GetType("System.Int32"));
m_results.Columns.Add(groupCol);
m_results.Columns.Add(sumCol);
/* end build result table */
int printBinsCounter = 0;
string userAggregateInput = string.Empty;
userAggregateInput = this.m_form.GetUserAggregateInput();
/* note even if the userAggregateInput is invalid it will correctly fill */
List <DataRow> completeRows = new List <DataRow>();
if (userAggregateInput.CompareTo(string.Empty) == 0)
{
DataRow[] rows = new DataRow[data.Rows.Count];
data.Rows.CopyTo(rows, 0);
completeRows.AddRange(rows);
}
else
{
DataRow[] rows = data.Select(this.m_grouping + " = '" + userAggregateInput + "'");
completeRows.AddRange(rows);
DataRow[] other = data.Select(this.m_grouping + " <> '" + userAggregateInput + "'");
completeRows.AddRange(other);
}
/* begin parsing data */
while (data.Rows.Count > 0)
{
/* user has specified new input */
if (userAggregateInput.CompareTo(this.m_form.GetUserAggregateInput()) != 0)
{
completeRows.Clear();
userAggregateInput = this.m_form.GetUserAggregateInput();
DataRow[] rows = data.Select(this.m_grouping + " = '" + userAggregateInput + "'");
completeRows.AddRange(rows);
DataRow[] other = data.Select(this.m_grouping + " <> '" + userAggregateInput + "'");
completeRows.AddRange(other);
}
DataRow dr = completeRows[0];
Object[] obs = dr.ItemArray;
int value = (int)obs[valueIndex];
string group = (string)obs[groupIndex];
/* we have this group */
if (bins.ContainsKey(group))
{
int seekIndex;
/* we have a bin for this group */
bins.TryGetValue(group, out seekIndex);
Object[] obsInTable = m_results.Rows[seekIndex].ItemArray;
/* get current values in group */
int currentSum = (int)obsInTable[1];
currentSum += value;
/* should only return 1 item because we only have 1 row per group */
DataRow[] editRow = m_results.Select(m_grouping + " = '" + group + "'");
editRow[0][sumCol] = currentSum;
}
else
{
bins.Add(group, m_results.Rows.Count);
/* set values for m_results */
DataRow newRow = m_results.NewRow();
newRow[groupCol] = group;
newRow[sumCol] = value;
m_results.Rows.Add(newRow);
}
data.Rows.Remove(dr);
completeRows.RemoveAt(0);
System.Threading.Thread.Sleep(this.m_form.GetUserPause());
printBinsCounter++;
/* prints every five rows */
if (printBinsCounter == 3)
{
Aux.printUpdates(m_results);
printBinsCounter = 0;
m_update = m_results.Copy();
this.m_form.SetText("Aggregate Update");
this.m_form.SetDataView(m_update);
}
} /* end for each tuple in input */
} /* end (m_groupby) */
}
public void exec()
{
DataTable dt = new DataTable("temp");
List <string> fields = new List <string>();
scan s = new scan("loan");
project p = new project(fields);
s.open();
dt = s.cloneSchema();
/* get all tuples from SCAN */
while (s.hasMore())
{
dt.ImportRow(s.next());
}
s.close();
/* add orderby here */
orderby orderOutput = new orderby("amount", "desc", "int", "loan_number", "asc", "str");
orderOutput.open(dt);
dt.Clear();
while (orderOutput.hasMore())
{
dt.ImportRow(orderOutput.next());
}
orderOutput.close();
p.open(dt);
/* begin output */
Aux.printschema(p.columns());
while (p.hasMore())
{
Aux.printrow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query5", resultCnt);
List <string> distinctCounter = new List <string>();
/* really inefficient */
foreach (DataColumn dc in dt.Columns)
{
distinctCounter.Clear();
foreach (DataRow dr in dt.Rows)
{
if (!distinctCounter.Contains(dr[dc.ColumnName].ToString()))
{
distinctCounter.Add(dr[dc.ColumnName].ToString());
}
}
StringBuilder statBuilder = new StringBuilder();
statBuilder.Append("distinct.query5." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
/* only handles strings and integers */
public void open(DataTable data)
{
/* set up a connection to the data server */
SqlConnection sqlConn = new SqlConnection("Trusted_Connection=yes;" +
"database=cmsc661; " +
"connection timeout=30");
try
{
sqlConn.Open();
/* fix input data because column_names cannot have dots */
foreach (DataColumn d in data.Columns)
{
d.ColumnName = d.ColumnName.Replace(".", "_dot_");
}
this.m_field = this.m_field.Replace(".", "_dot_");
if (this.m_twoSort)
{
this.m_secondfield = this.m_secondfield.Replace(".", "_dot_");
}
/* assume we break it all into 2 blocks */
DataTable firstBlock = new DataTable();
DataTable secondBlock = new DataTable();
firstBlock = data.Clone();
secondBlock = data.Clone();
for (int i = 0; i < data.Rows.Count; i++)
{
if (i % 2 == 0)
{
firstBlock.ImportRow(data.Rows[i]);
}
else
{
secondBlock.ImportRow(data.Rows[i]);
}
}
firstBlock.TableName = "#temp1";
secondBlock.TableName = "#temp2";
StringBuilder firstInsert = new StringBuilder();
firstInsert.Append("create table #temp1 ");
firstInsert.Append("( ");
StringBuilder secondInsert = new StringBuilder();
secondInsert.Append("create table #temp2 ");
secondInsert.Append("( ");
int counter = 1;
foreach (DataColumn col in data.Columns)
{
firstInsert.Append(col.ColumnName + " ");
secondInsert.Append(col.ColumnName + " ");
if (col.DataType.ToString().CompareTo("System.Int32") == 0)
{
firstInsert.Append("int not null");
secondInsert.Append("int not null");
}
else if (col.DataType.ToString().CompareTo("System.String") == 0)
{
firstInsert.Append("varchar(15) not null");
secondInsert.Append("varchar(15) not null");
}
if (counter < data.Columns.Count)
{
firstInsert.Append(", ");
secondInsert.Append(", ");
}
counter++;
}
firstInsert.Append("); ");
secondInsert.Append("); ");
/* now I need to place them onto the data storage manager */
SqlCommand firstCmd = new SqlCommand(firstInsert.ToString());
firstCmd.Connection = sqlConn;
firstCmd.ExecuteNonQuery();
SqlCommand secondCmd = new SqlCommand(secondInsert.ToString());
secondCmd.Connection = sqlConn;
secondCmd.ExecuteNonQuery();
StringBuilder firstInsertTuples = new StringBuilder();
int columnCounter = 1;
foreach (DataRow r in firstBlock.Rows)
{
firstInsertTuples.Append("insert into #temp1 values (");
columnCounter = 1;
/* so we can properly quote or not quote data */
foreach (DataColumn d in firstBlock.Columns)
{
if (d.DataType.ToString().CompareTo("System.Int32") == 0)
{
firstInsertTuples.Append(r[d].ToString());
}
else if (d.DataType.ToString().CompareTo("System.String") == 0)
{
firstInsertTuples.Append("'" + r[d].ToString() + "'");
}
if (columnCounter < firstBlock.Columns.Count)
{
firstInsertTuples.Append(", ");
}
columnCounter++;
}
firstInsertTuples.Append(");");
}
SqlCommand firstBuild = new SqlCommand(firstInsertTuples.ToString());
firstBuild.Connection = sqlConn;
firstBuild.ExecuteNonQuery();
StringBuilder secondInsertTuples = new StringBuilder();
foreach (DataRow r in secondBlock.Rows)
{
secondInsertTuples.Append("insert into #temp2 values (");
columnCounter = 1;
/* so we can properly quote or not quote data */
foreach (DataColumn d in secondBlock.Columns)
{
if (d.DataType.ToString().CompareTo("System.Int32") == 0)
{
secondInsertTuples.Append(r[d].ToString());
}
else if (d.DataType.ToString().CompareTo("System.String") == 0)
{
secondInsertTuples.Append("'" + r[d].ToString() + "'");
}
if (columnCounter < firstBlock.Columns.Count)
{
secondInsertTuples.Append(", ");
}
columnCounter++;
}
secondInsertTuples.Append(");");
}
SqlCommand secondBuild = new SqlCommand(secondInsertTuples.ToString());
secondBuild.Connection = sqlConn;
secondBuild.ExecuteNonQuery();
/* free memory */
firstBlock.Clear();
secondBlock.Clear();
/* second pass */
StringBuilder queryFirstBlock = new StringBuilder();
queryFirstBlock.Append("select * from #temp1 order by " + this.m_field + " " + this.m_order);
StringBuilder querySecondBlock = new StringBuilder();
querySecondBlock.Append("select * from #temp2 order by " + this.m_field + " " + this.m_order);
int firstIndex = data.Columns.IndexOf(this.m_field);
int secondIndex = -1;
if (this.m_twoSort)
{
queryFirstBlock.Append(", " + this.m_secondfield + " " + this.m_secondorder);
querySecondBlock.Append(", " + this.m_secondfield + " " + this.m_secondorder);
secondIndex = data.Columns.IndexOf(this.m_secondfield);
}
/* then I need to read them back in sorted order */
DataSet ds = new DataSet();
SqlCommand firstCommand = new SqlCommand(queryFirstBlock.ToString(), sqlConn);
SqlCommand secondCommand = new SqlCommand(querySecondBlock.ToString(), sqlConn);
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = firstCommand;
sqlDA.Fill(ds, "temp1");
sqlDA.SelectCommand = secondCommand;
sqlDA.Fill(ds, "temp2");
m_dt = firstBlock.Clone(); /* because data.Clone() will give with dots */
/* then I need to walk through both merging into one list */
int total_count = data.Rows.Count;
Boolean firstRow = false;
Boolean remaining = false;
while (m_dt.Rows.Count < total_count && remaining == false)
{
firstRow = false;
/* if there are tuples to compare in both */
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[1].Rows.Count > 0)
{
if (m_order.CompareTo("desc") == 0)
{
if (m_dataType.CompareTo("int") == 0)
{
if ((int)ds.Tables[0].Rows[0][firstIndex] > (int)ds.Tables[1].Rows[0][firstIndex])
{
m_dt.ImportRow(ds.Tables[0].Rows[0]);
firstRow = true;
}
else /* if a < b or a == b we just take b */
{
m_dt.ImportRow(ds.Tables[1].Rows[0]);
firstRow = false;
}
}
else if (m_dataType.CompareTo("str") == 0)
{
if (((string)ds.Tables[0].Rows[0][firstIndex]).CompareTo((string)ds.Tables[1].Rows[0][firstIndex]) == 1)
{
m_dt.ImportRow(ds.Tables[0].Rows[0]);
firstRow = true;
}
else
{
m_dt.ImportRow(ds.Tables[1].Rows[0]);
firstRow = false;
}
}
}
else /* order is asc */
{
if (m_dataType.CompareTo("int") == 0)
{
if ((int)ds.Tables[0].Rows[0][firstIndex] < (int)ds.Tables[1].Rows[0][firstIndex])
{
m_dt.ImportRow(ds.Tables[0].Rows[0]);
firstRow = true;
}
else /* if a > b or a == b we just take b */
{
m_dt.ImportRow(ds.Tables[1].Rows[0]);
firstRow = false;
}
}
else if (m_dataType.CompareTo("str") == 0)
{
if (((string)ds.Tables[0].Rows[0][firstIndex]).CompareTo((string)ds.Tables[1].Rows[0][firstIndex]) == -1)
{
m_dt.ImportRow(ds.Tables[0].Rows[0]);
firstRow = true;
}
else
{
m_dt.ImportRow(ds.Tables[1].Rows[0]);
firstRow = false;
}
}
}
}
else /* import remaining */
{
remaining = true;
}
/* pull off first tuple from whichever */
if (firstRow && !remaining)
{
ds.Tables[0].Rows.Remove(ds.Tables[0].Rows[0]);
}
else if (!firstRow && !remaining)
{
ds.Tables[1].Rows.Remove(ds.Tables[1].Rows[0]);
}
}
if (remaining) /* pick up any remaining tuples */
{
while (ds.Tables[0].Rows.Count > 0)
{
m_dt.ImportRow(ds.Tables[0].Rows[0]);
ds.Tables[0].Rows.Remove(ds.Tables[0].Rows[0]);
}
while (ds.Tables[1].Rows.Count > 0)
{
m_dt.ImportRow(ds.Tables[1].Rows[0]);
ds.Tables[1].Rows.Remove(ds.Tables[1].Rows[0]);
}
}
/* add second sorting */
if (this.m_twoSort)
{
m_dt.DefaultView.Sort = this.m_field + " " + this.m_order + ", " + this.m_secondfield + " " + this.m_secondorder;
}
}
catch (SqlException sqlE)
{
Console.WriteLine(sqlE.Message.ToString());
}
/* close out our connection */
try
{
sqlConn.Close();
}
catch (SqlException sqlE)
{
Console.WriteLine(sqlE.Message.ToString());
}
finally
{
sqlConn.Close();
}
/* fix tuples placing dots back in */
foreach (DataColumn d in m_dt.Columns)
{
d.ColumnName = d.ColumnName.Replace("_dot_", ".");
}
/* fix tuples of reference object so I don't have to fix this in each query */
foreach (DataColumn d in data.Columns)
{
d.ColumnName = d.ColumnName.Replace("_dot_", ".");
}
this.m_field = this.m_field.Replace("_dot_", ".");
if (this.m_twoSort)
{
this.m_secondfield = this.m_secondfield.Replace("_dot_", ".");
}
/* test sorting */
Aux.printschema(m_dt.Columns);
foreach (DataRow r in m_dt.Rows)
{
Aux.printrow(r);
}
}