public void exec()
{
DataTable dt = new DataTable("temp");
List <string> fields = new List <string>();
scan s = new scan("account");
project p = new project(fields);
avg a = new avg("balance", "branch_name");
select se = new select("avg(balance)", "gt", 700);
s.open();
dt = s.cloneSchema();
/* get all tuples from SCAN */
while (s.hasMore())
{
dt.ImportRow(s.next());
}
s.close();
a.loadFormAcces(m_form, m_resultTable);
a.open(dt);
dt.Clear();
// because might be returning different columns
dt = a.cloneSchema();
while (a.hasMore())
{
dt.ImportRow(a.next());
}
a.close();
se.open(dt);
dt.Clear();
while (se.hasMore())
{
dt.ImportRow(se.next());
}
se.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query9", 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.query9." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("branch T");
DataTable dto = new DataTable("branch S");
List <string> fields = new List <string>();
fields.Add("T.branch_name");
scan branch_T = new scan("branch");
scan branch_S = new scan("branch");
project p = new project(fields);
distinct d = new distinct(fields);
renamefield t_branch_name = new renamefield("branch_name", "T.branch_name");
renamefield t_assets = new renamefield("assets", "T.assets");
renamefield s_assets = new renamefield("assets", "S.assets");
renamefield s_branch_city = new renamefield("branch_city", "S.branch_city");
select sel_city = new select("S.branch_city", "eq", "Brooklyn");
List <string> joinon = new List <string>();
joinon.Add("T.assets");
joinon.Add("gt");
joinon.Add("S.assets");
joinon.Add("int");
join j = new join(joinon);
/* read in branch S */
branch_S.open();
dt = branch_S.cloneSchema();
while (branch_S.hasMore())
{
dt.ImportRow(branch_S.next());
}
branch_S.close();
/* read in branch T */
branch_T.open();
dto = branch_T.cloneSchema();
while (branch_T.hasMore())
{
dto.ImportRow(branch_T.next());
}
branch_T.close();
/* rename fields */
t_branch_name.open(dto);
t_assets.open(dto);
s_branch_city.open(dt);
s_assets.open(dt);
/* select S.branch_city eq 'Brooklyn' */
sel_city.open(dt);
dt.Clear();
while (sel_city.hasMore())
{
dt.ImportRow(sel_city.next());
}
sel_city.close();
/* join */
j.open(dto, dt);
dt.Clear();
dt = j.cloneSchema();
while (j.hasMore())
{
dt.ImportRow(j.next());
}
j.close();
/* distinct */
d.open(dt);
dt.Clear();
while (d.hasMore())
{
dt.ImportRow(d.next());
}
d.close();
/* project */
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query12", 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.query12." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("temp");
List <string> fields = new List <string>();
fields.Add("account_number");
scan s = new scan("account");
project p = new project(fields);
select s1 = new select("balance", "gte", 700);
select s2 = new select("balance", "lte", 900);
s.open();
dt = s.cloneSchema();
/* get all tuples from SCAN */
while (s.hasMore())
{
dt.ImportRow(s.next());
}
s.close();
/* add selects here */
s1.open(dt);
/* prepare for new set */
dt.Clear();
while (s1.hasMore())
{
dt.ImportRow(s1.next());
}
s1.close();
s2.open(dt);
dt.Clear();
while (s2.hasMore())
{
dt.ImportRow(s2.next());
}
s2.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query2", 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.query2." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("temp");
List <string> empty = new List <string>();
scan s = new scan("customer");
project p = new project(empty);
s.open();
dt = s.cloneSchema();
/* get all tuples from SCAN */
while (s.hasMore())
{
dt.ImportRow(s.next());
}
s.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query1", 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.query1." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("tempMain");
DataTable dto = new DataTable("tempSecondary");
List <string> fields = new List <string>();
fields.Add("C.customer_name");
fields.Add("customer_city");
project p = new project(fields);
distinct d = new distinct(fields);
scan s1 = new scan("customer");
scan s2 = new scan("borrower");
scan s3 = new scan("loan");
renamefield borRen = new renamefield("customer_name", "B.customer_name");
renamefield borRen2 = new renamefield("loan_number", "B.loan_number");
renamefield cusRen = new renamefield("customer_name", "C.customer_name");
renamefield loaRen = new renamefield("loan_number", "L.loan_number");
select sloan = new select("branch_name", "eq", "Perryridge");
List <string> join1 = new List <string>();
join1.Add("B.loan_number");
join1.Add("eq");
join1.Add("L.loan_number");
join1.Add("str");
List <string> join2 = new List <string>();
join2.Add("C.customer_name");
join2.Add("eq");
join2.Add("B.customer_name");
join2.Add("str");
join j1 = new join(join1);
join j2 = new join(join2);
/* Read in borrower */
s2.open();
dt = s2.cloneSchema();
while (s2.hasMore())
{
dt.ImportRow(s2.next());
}
s2.close();
/* read in loan */
s3.open();
dto = s3.cloneSchema();
while (s3.hasMore())
{
dto.ImportRow(s3.next());
}
s3.close();
/* run select against loan */
sloan.open(dto);
dto.Clear();
while (sloan.hasMore())
{
dto.ImportRow(sloan.next());
}
sloan.close();
/* rename borrow and loan for join */
loaRen.open(dto);
borRen.open(dt);
borRen2.open(dt);
j1.open(dt, dto);
// clean temporary storage
dt.Clear();
dto.Clear();
/* obtain result of join */
dto = j1.cloneSchema();
while (j1.hasMore())
{
dto.ImportRow(j1.next());
}
j1.close();
/* read in customer relation */
s1.open();
dt = s1.cloneSchema();
while (s1.hasMore())
{
dt.ImportRow(s1.next());
}
s1.close();
// rename customer column
cusRen.open(dt);
/* join customer with result of previous join */
j2.open(dt, dto);
dt.Clear();
dto.Clear();
dt = j2.cloneSchema();
while (j2.hasMore())
{
dt.ImportRow(j2.next());
}
j2.close();
d.open(dt);
dt.Clear();
while (d.hasMore())
{
dt.ImportRow(d.next());
}
d.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query13", 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.query13." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("tempMain");
DataTable dto = new DataTable("tempSecondary");
List <string> fields = new List <string>();
fields.Add("borrower.customer_name");
project p = new project(fields);
distinct d = new distinct(fields);
scan scanBorrower = new scan("borrower");
scan scanLoan = new scan("loan");
scan scanBranch = new scan("branch");
select s = new select("loan.branch_name", "eq", "Perryridge");
renamefield renLoanLoanNumber = new renamefield("loan_number", "loan.loan_number");
renamefield renBorLoanNumber = new renamefield("loan_number", "borrower.loan_number");
renamefield renLoanBranchName = new renamefield("branch_name", "loan.branch_name");
renamefield renBranchBranchName = new renamefield("branch_name", "branch.branch_name");
renamefield renBorCustName = new renamefield("customer_name", "borrower.customer_name");
List <string> joinloanbranch = new List <string>();
joinloanbranch.Add("loan.branch_name");
joinloanbranch.Add("eq");
joinloanbranch.Add("branch.branch_name");
joinloanbranch.Add("str");
List <string> joinborrower = new List <string>();
joinborrower.Add("borrower.loan_number");
joinborrower.Add("eq");
joinborrower.Add("loan.loan_number");
joinborrower.Add("str");
join joinlb = new join(joinloanbranch);
join joinbo = new join(joinborrower);
orderby o = new orderby("borrower.customer_name", "asc", "str");
/* Read in loan */
scanLoan.open();
dt.Clear();
dt = scanLoan.cloneSchema();
while (scanLoan.hasMore())
{
dt.ImportRow(scanLoan.next());
}
scanLoan.close();
/* rename loan fields */
renLoanLoanNumber.open(dt);
renLoanBranchName.open(dt);
/* where loan.branch_name = 'Perryridge' */
s.open(dt);
dt.Clear();
while (s.hasMore())
{
dt.ImportRow(s.next());
}
s.close();
/* scan branch into dto */
scanBranch.open();
dto.Clear();
dto = scanBranch.cloneSchema();
while (scanBranch.hasMore())
{
dto.ImportRow(scanBranch.next());
}
scanBranch.close();
/* rename branch_name to branch.branch_name */
renBranchBranchName.open(dto);
/* join loan, branch */
joinlb.open(dt, dto);
/* clean table to store results */
dto.Clear();
dto = joinlb.cloneSchema();
while (joinlb.hasMore())
{
dto.ImportRow(joinlb.next());
}
joinlb.close();
/* dto holds results of join */
/* scan in borrower relation */
scanBorrower.open();
dt.Clear();
dt = scanBorrower.cloneSchema();
while (scanBorrower.hasMore())
{
dt.ImportRow(scanBorrower.next());
}
scanBorrower.close();
/* rename borrower */
renBorCustName.open(dt);
renBorLoanNumber.open(dt);
/* join borrower with dto */
joinbo.open(dt, dto);
dt.Clear();
dto.Clear();
dt = joinbo.cloneSchema();
while (joinbo.hasMore())
{
dt.ImportRow(joinbo.next());
}
joinbo.close();
/* dt holds final join results */
/* run distinct filter */
d.open(dt);
dt.Clear();
while (d.hasMore())
{
dt.ImportRow(d.next());
}
d.close();
/* order by */
o.open(dt);
dt.Clear();
while (o.hasMore())
{
dt.ImportRow(o.next());
}
o.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* dt holds results */
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query15", 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.query15." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("temp");
DataTable dto = new DataTable("temp");
List <string> fields = new List <string>();
fields.Add("branch_name");
fields.Add("count(distinct customer_name)");
scan sdepositor = new scan("depositor");
scan saccount = new scan("account");
count c = new count("customer_name", "branch_name", true);
renamefield renDep = new renamefield("account_number", "depositor.account_number");
renamefield renAcc = new renamefield("account_number", "account.account_number");
List <string> joinon = new List <string>();
joinon.Add("depositor.account_number");
joinon.Add("eq");
joinon.Add("account.account_number");
joinon.Add("str");
join j = new join(joinon);
project p = new project(fields);
/* read in depositor */
sdepositor.open();
dt = sdepositor.cloneSchema();
/* get all tuples from SCAN */
while (sdepositor.hasMore())
{
dt.ImportRow(sdepositor.next());
}
sdepositor.close();
/* read in account */
saccount.open();
dto = saccount.cloneSchema();
while (saccount.hasMore())
{
dto.ImportRow(saccount.next());
}
saccount.close();
/* rename fields before join */
renAcc.open(dto);
renDep.open(dt);
/* join relations */
j.open(dt, dto);
dt.Clear();
dto.Clear();
dt = j.cloneSchema();
while (j.hasMore())
{
dt.ImportRow(j.next());
}
c.loadFormAcces(m_form, m_resultTable);
/* count */
c.open(dt);
dt.Clear();
dt = c.cloneSchema();
while (c.hasMore())
{
dt.ImportRow(c.next());
}
c.close();
/* project */
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query10", 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.query10." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("temp");
List <string> fields = new List <string>();
scan s = new scan("customer");
project p = new project(fields);
count c = new count("*", string.Empty, false);
s.open();
dt = s.cloneSchema();
/* get all tuples from SCAN */
while (s.hasMore())
{
dt.ImportRow(s.next());
}
s.close();
c.loadFormAcces(m_form, m_resultTable);
c.open(dt);
dt.Clear();
// because might be returning different columns
dt = c.cloneSchema();
while (c.hasMore())
{
dt.ImportRow(c.next());
}
c.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query6", 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.query6." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("borrower");
DataTable dto = new DataTable("customer");
List <string> fields = new List <string>();
fields.Add("customer.customer_name");
fields.Add("customer_city");
scan sL = new scan("borrower");
scan sR = new scan("customer");
project p = new project(fields);
renamefield rL = new renamefield("customer_name", "borrower.customer_name");
renamefield rR = new renamefield("customer_name", "customer.customer_name");
List <string> joinon = new List <string>();
joinon.Add("borrower.customer_name");
joinon.Add("eq");
joinon.Add("customer.customer_name");
joinon.Add("str");
join j = new join(joinon);
distinct d = new distinct(fields);
/* Read in Borrower */
sL.open();
dt = sL.cloneSchema();
/* get all tuples from SCAN */
while (sL.hasMore())
{
dt.ImportRow(sL.next());
}
sL.close();
rL.open(dt);
/* Read in Customer */
sR.open();
dto = sR.cloneSchema();
while (sR.hasMore())
{
dto.ImportRow(sR.next());
}
sR.close();
rR.open(dto);
/* compute join */
j.open(dt, dto);
dt.Clear();
dt = j.cloneSchema();
while (j.hasMore())
{
dt.ImportRow(j.next());
}
j.close();
/* add distinct here */
d.open(dt);
dt.Clear();
while (d.hasMore())
{
dt.ImportRow(d.next());
}
d.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query11", 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.query11." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable dt = new DataTable("temp");
List <string> empty = new List <string>();
empty.Add("branch_city");
empty.Add("total_assets");
scan s = new scan("branch");
project p = new project(empty);
sum summation = new sum("assets", "branch_city");
renamefield totalassets = new renamefield("sum(assets)", "total_assets");
orderby assetOrdering = new orderby("total_assets", "desc", "int");
s.open();
dt = s.cloneSchema();
/* get all tuples from SCAN */
while (s.hasMore())
{
dt.ImportRow(s.next());
}
s.close();
summation.loadFormAcces(m_form, m_resultTable);
/* run sum aggregation */
summation.open(dt);
dt.Clear();
dt = summation.cloneSchema();
while (summation.hasMore())
{
dt.ImportRow(summation.next());
}
summation.close();
totalassets.open(dt);
assetOrdering.open(dt);
dt.Clear();
while (assetOrdering.hasMore())
{
dt.ImportRow(assetOrdering.next());
}
assetOrdering.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query8", 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.query8." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}
public void exec()
{
DataTable branchData = new DataTable("temp");
DataTable dt = new DataTable("temp");
List <string> subfields = new List <string>();
List <string> fields = new List <string>();
subfields.Add("assets");
fields.Add("branch_name");
scan s = new scan("branch");
project subp = new project(subfields);
project p = new project(fields);
select s1 = new select("branch_city", "eq", "Brooklyn");
s.open();
branchData = s.cloneSchema();
/* get all tuples from SCAN */
while (s.hasMore())
{
branchData.ImportRow(s.next());
}
s.close();
/* execute sub query */
dt = branchData.Copy();
s1.open(dt);
dt.Clear();
while (s1.hasMore())
{
dt.ImportRow(s1.next());
}
s1.close();
subp.open(dt);
dt.Clear();
List <int> values = new List <int>();
while (subp.hasMore())
{
DataRow dr = subp.next();
Object[] obs = dr.ItemArray;
values.Add((int)obs[0]);
}
subp.close();
int maximumvalue = values.Max();
selectALL sAll = new selectALL("assets", "gt", maximumvalue);
sAll.open(branchData);
dt = branchData.Clone();
while (sAll.hasMore())
{
dt.ImportRow(sAll.next());
}
sAll.close();
p.open(dt);
dt.Clear();
dt = p.cloneSchema();
while (p.hasMore())
{
dt.ImportRow(p.next());
}
p.close();
/* update stats */
int resultCnt = dt.Rows.Count;
m_stats.Add("count.query14", 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.query14." + dc.ColumnName);
m_stats.Add(statBuilder.ToString(), distinctCounter.Count);
}
m_outDT = dt.Copy();
}