Beispiel #1
0
        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();
        }
Beispiel #2
0
        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();
        }
Beispiel #3
0
        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();
        }
Beispiel #4
0
        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();
        }
Beispiel #5
0
        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();
        }