Exemple #1
0
        /* 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);
            }
        }
Exemple #2
0
        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();
        }
Exemple #3
0
        /* 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);
            }
        }