/* 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 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);
}
}
