Ejemplo n.º 1
0
        public void TestSkylineAmountOfTupelsDataTable()
        {
            string skylineSampleSql = TestContext.DataRow["skylineSQL"].ToString();

            SQLCommon common = new SQLCommon();

            common.SkylineType = new SkylineSQL();
            PrefSQLModel model    = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql);
            DataTable    dtNative = common.ExecuteFromPrefSqlModel(Helper.ConnectionString, Helper.ProviderName, model);

            common.SkylineType = new SkylineBNL();
            DataTable dtBNL = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql);

            common.SkylineType = new SkylineBNLSort();
            DataTable dtBNLSort = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql);

            DataTable dtHexagon = new DataTable();

            if (model.ContainsOpenPreference == false)
            {
                common.SkylineType = new SkylineHexagon();
                dtHexagon          = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql);
            }

            DataTable dtDQ = new DataTable();

            //D&Q does not work with incomparable tuples
            if (model.WithIncomparable == false)
            {
                common.SkylineType = new SkylineDQ();
                dtDQ = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, skylineSampleSql);
            }

            int currentDataRowIndex = TestContext.DataRow.Table.Rows.IndexOf(TestContext.DataRow);

            //Check tuples (every algorithm should deliver the same amount of tuples)
            Assert.AreEqual(dtNative.Rows.Count, dtBNL.Rows.Count, 0,
                            "BNL Amount of tupels in query " + currentDataRowIndex + " do not match");
            Assert.AreEqual(dtNative.Rows.Count, dtBNLSort.Rows.Count, 0,
                            "BNLSort Amount of tupels in query " + currentDataRowIndex + " do not match");

            //Hexagon cannot handle Categorical preference that have no explicit OTHERS
            if (model.ContainsOpenPreference == false)
            {
                Assert.AreEqual(dtNative.Rows.Count, dtHexagon.Rows.Count, 0,
                                "Hexagon Amount of tupels in query " + currentDataRowIndex + " do not match");
            }
            //D&Q does not work with incomparable tuples
            if (model.WithIncomparable == false)
            {
                Assert.AreEqual(dtNative.Rows.Count, dtDQ.Rows.Count, 0,
                                "D&Q Amount of tupels in query " + currentDataRowIndex + " do not match");
            }
        }
Ejemplo n.º 2
0
        public void TestIssue54()
        {
            string prefSQL = "SELECT c.Title AS Name, c.Price, c.Consumption, m.Name AS Manufacturer, b.Name AS Body "
                             + "FROM Cars c "
                             + "LEFT OUTER JOIN Makes m ON c.Make_Id = m.Id "
                             + "LEFT OUTER JOIN Bodies b ON c.Body_Id = b.Id "
                             + "WHERE m.Name = 'VW' AND b.Name = 'Bus' "
                             + "SKYLINE OF c.Price LOW 1000 EQUAL, c.Consumption LOW";

            var common = new SQLCommon
            {
                SkylineType            = new SkylineBNL(),
                ShowInternalAttributes = true
            };


            try
            {
                //If there is no exception in the execution of this query the test is successful
                DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);
                Assert.IsTrue(true);
            }
            catch
            {
                Assert.IsFalse(true);
            }
        }
Ejemplo n.º 3
0
        public void TestIssue67()
        {
            string prefSQL = "SELECT c.Title as Name, c.Price, co.Name As Color  "
                             + "FROM Cars c "
                             + "LEFT OUTER JOIN Colors co ON c.Color_Id = co.Id "
                             + "SKYLINE OF c.Price HIGH, co.Name ('pink' >> 'black' >> OTHERS EQUAL)  "
                             + "ORDER BY BEST_RANK()";


            SQLCommon common = new SQLCommon();

            common.SkylineType = new SkylineDQ();

            try
            {
                //If there is no exception in the execution of this query the test is successful
                DataTable dtStandalone = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);

                String        sql    = common.ParsePreferenceSQL(prefSQL);
                SqlConnection cnnSQL = new SqlConnection(Helper.ConnectionString); //for CLR performance tets
                cnnSQL.Open();
                SqlDataAdapter dap   = new SqlDataAdapter(sql, cnnSQL);
                DataTable      dtCLR = new DataTable();
                dap.Fill(dtCLR);

                Assert.AreEqual(dtStandalone.Rows.Count, dtCLR.Rows.Count);
            }
            catch
            {
                Assert.IsFalse(true);
            }
        }
Ejemplo n.º 4
0
        public void TestSamplingOnlyNonDominatedObjectsWithinSampleSkyline()
        {
            string skylineSampleSql = TestContext.DataRow["skylineSampleSQL"].ToString();
            string entireSkylineSql = TestContext.DataRow["entireSkylineSQL"].ToString();
            string testComment      = TestContext.DataRow["comment"].ToString();

            Debug.WriteLine(testComment);
            Debug.WriteLine(skylineSampleSql);

            string baseQuery;
            string operators;
            int    numberOfRecords;

            string[] parameter;

            var common = new SQLCommon
            {
                SkylineType =
                    new SkylineBNL()
                {
                    Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
                }
            };

            PrefSQLModel prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql);
            string       ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample);

            prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out baseQuery, out operators,
                                                         out numberOfRecords);

            IEnumerable <CLRSafeHashSet <int> > useSubsets = UseSubsets(prefSqlModelSkylineSample);
            var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(useSubsets);
            var utility         = new SkylineSamplingUtility(subsetsProducer);
            var skylineSample   = new SkylineSampling(utility)
            {
                SubsetCount      = prefSqlModelSkylineSample.SkylineSampleCount,
                SubsetDimension  = prefSqlModelSkylineSample.SkylineSampleDimension,
                SelectedStrategy = common.SkylineType
            };

            DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                                                                    entireSkylineSql);
            DataTable sampleSkyline = skylineSample.GetSkylineTable(baseQuery, operators);

            HashSet <int> entireSkylineObjectsIds = GetHashSetOfIdsFromDataTable(entireSkyline);
            HashSet <int> sampleSkylineObjectsIds = GetHashSetOfIdsFromDataTable(sampleSkyline);

            sampleSkylineObjectsIds.ExceptWith(entireSkylineObjectsIds);

            Debug.WriteLine("wrong objects:");
            foreach (int i in sampleSkylineObjectsIds)
            {
                Debug.WriteLine(i);
            }

            Assert.IsTrue(sampleSkylineObjectsIds.IsSubsetOf(entireSkylineObjectsIds),
                          "Dominated objects contained in Sample Skyline (i.e., objects which are not contained in the entire Skyline).");
        }
Ejemplo n.º 5
0
        private void btnExecute_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();

            sw.Start();

            btnExecute.Enabled = false;

            SQLCommon parser = new SQLCommon();

            if (optSQL.Checked)
            {
                parser.SkylineType = new SkylineSQL();
            }
            else if (optBNL.Checked)
            {
                parser.SkylineType = new SkylineBNLSort();
            }
            else if (optHexagon.Checked)
            {
                parser.SkylineType = new SkylineHexagon();
            }
            else if (optDQ.Checked)
            {
                parser.SkylineType = new SkylineDQ();
            }

            if (chkShowSkyline.Checked)
            {
                parser.ShowInternalAttributes = true;
            }
            else
            {
                parser.ShowInternalAttributes = false;
            }

            DataTable dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, txtPrefSQL.Text);

            BindingSource sBind = new BindingSource();

            sBind.DataSource = dt;

            gridSkyline.AutoGenerateColumns = true;
            gridSkyline.DataSource          = dt;

            gridSkyline.DataSource = sBind;
            gridSkyline.Refresh();

            sw.Stop();

            txtTime.Text     = sw.ElapsedMilliseconds.ToString();
            txtTimeAlgo.Text = parser.TimeInMilliseconds.ToString();
            txtRecords.Text  = dt.Rows.Count.ToString();


            btnExecute.Enabled = true;
        }
Ejemplo n.º 6
0
        public void TestSQLSelectWithCoalesce()
        {
            string sql = "select COALESCE(cars.id, 0) FROM cars SKYLINE OF cars.price LOW, cars.mileage LOW";

            SQLCommon common = new SQLCommon();
            DataTable dt     = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);

            Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
        }
Ejemplo n.º 7
0
        public void TestSQLSelectSubqueryWithGroupBy()
        {
            string sql = "SELECT t.color_id, Amount FROM (SELECT cars.color_id, COUNT(*) AS Amount FROM cars GROUP BY cars.color_id) t";

            SQLCommon common = new SQLCommon();
            DataTable dt     = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);

            Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
        }
Ejemplo n.º 8
0
        public void TestSQLSelectSubquery()
        {
            string sql = "select t.id FROM (SELECT * FROM cars) t";

            SQLCommon common = new SQLCommon();
            DataTable dt     = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);

            Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
        }
Ejemplo n.º 9
0
        public void TestSQLSkylineWithDisfavour()
        {
            string sql = "SELECT cars.title, colors.name FROM cars LEFT OUTER JOIN colors ON cars.color_id = colors.ID SKYLINE OF colors.name DISFAVOUR 'red', cars.price LOW";

            SQLCommon common = new SQLCommon();
            DataTable dt     = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, sql);

            Assert.IsTrue(dt.Rows.Count > 0, "Select result in no data");
        }
Ejemplo n.º 10
0
        public void TestSamplingNumberOfObjectsWithinSampleSkylineWithCountOneEqualsEntireSkyline()
        {
            string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
            string testComment      = TestContext.DataRow["comment"].ToString();

            Debug.WriteLine(testComment);

            var common = new SQLCommon {
                SkylineType = new SkylineBNL()
            };

            DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                                                                    entireSkylineSQL);

            PrefSQLModel entirePrefSqlModel = common.GetPrefSqlModelFromPreferenceSql(entireSkylineSQL);

            DataTable sampleSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                                                                    entireSkylineSQL + " SAMPLE BY RANDOM_SUBSETS COUNT 1 DIMENSION " + entirePrefSqlModel.Skyline.Count);

            Assert.AreEqual(entireSkyline.Rows.Count, sampleSkyline.Rows.Count,
                            "Unexpected number of Skyline objects.");
        }
Ejemplo n.º 11
0
        public void TestWeightedSum_DataTable()
        {
            string[] strPrefSQL = GetPreferences();

            for (int i = 0; i <= strPrefSQL.GetUpperBound(0); i++)
            {
                SQLCommon common = new SQLCommon();
                common.SkylineType = new SkylineSQL();
                DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, strPrefSQL[i]);


                //Check tuples (every algorithm should deliver the same amount of tuples)
                Assert.AreEqual(dt.Rows.Count, 55208, 0, "WeightedSum Sorting failed");
            }
        }
        public void TestSamplingNumberOfObjectsWithinEntireSkyline()
        {
            string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
            string testComment = TestContext.DataRow["comment"].ToString();
            int expectedNumberOfEntireSkylineObjects =
                int.Parse(TestContext.DataRow["expectedNumberOfEntireSkylineObjects"].ToString());
            Debug.WriteLine(testComment);
            Debug.WriteLine(entireSkylineSQL);

            var common = new SQLCommon { SkylineType = new SkylineBNL() };

            DataTable skyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                entireSkylineSQL);

            Assert.AreEqual(expectedNumberOfEntireSkylineObjects, skyline.Rows.Count,
                "Unexpected number of Skyline objects.");
        }
Ejemplo n.º 13
0
        public void TestSamplingNumberOfObjectsWithinEntireSkyline()
        {
            string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
            string testComment      = TestContext.DataRow["comment"].ToString();
            int    expectedNumberOfEntireSkylineObjects =
                int.Parse(TestContext.DataRow["expectedNumberOfEntireSkylineObjects"].ToString());

            Debug.WriteLine(testComment);
            Debug.WriteLine(entireSkylineSQL);

            var common = new SQLCommon {
                SkylineType = new SkylineBNL()
            };

            DataTable skyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                                                              entireSkylineSQL);

            Assert.AreEqual(expectedNumberOfEntireSkylineObjects, skyline.Rows.Count,
                            "Unexpected number of Skyline objects.");
        }
Ejemplo n.º 14
0
        public void TestIssue66()
        {
            string prefSQL = " SELECT * FROM ( SELECT * FROM cars )b";

            var common = new SQLCommon
            {
                SkylineType            = new SkylineBNL(),
                ShowInternalAttributes = false
            };


            try
            {
                //If there is no exception in the execution of this query the test is successful
                DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);
                Assert.IsTrue(true);
            }
            catch
            {
                Assert.IsFalse(true);
            }
        }
Ejemplo n.º 15
0
        public void TestIssue63()
        {
            string prefSQL = "SELECT TOP 5 c.Id, c.title AS IDFROM, c.title AS IDWHERE, c.title AS IDTOP "
                             + "FROM Cars c "
                             + "SKYLINE OF c.id LOW ";

            var common = new SQLCommon
            {
                SkylineType            = new SkylineBNL(),
                ShowInternalAttributes = true
            };


            try
            {
                //If there is no exception in the execution of this query the test is successful
                DataTable dt = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, prefSQL);
                Assert.IsTrue(true);
            }
            catch
            {
                Assert.IsFalse(true);
            }
        }
        public void TestSamplingNumberOfObjectsWithinSampleSkylineWithCountOneEqualsEntireSkyline()
        {
            string entireSkylineSQL = TestContext.DataRow["entireSkylineSQL"].ToString();
            string testComment = TestContext.DataRow["comment"].ToString();
            Debug.WriteLine(testComment);

            var common = new SQLCommon { SkylineType = new SkylineBNL() };

            DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                entireSkylineSQL);

            PrefSQLModel entirePrefSqlModel = common.GetPrefSqlModelFromPreferenceSql(entireSkylineSQL);

            DataTable sampleSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                entireSkylineSQL + " SAMPLE BY RANDOM_SUBSETS COUNT 1 DIMENSION " + entirePrefSqlModel.Skyline.Count);

            Assert.AreEqual(entireSkyline.Rows.Count, sampleSkyline.Rows.Count,
              "Unexpected number of Skyline objects.");
        }
Ejemplo n.º 17
0
        public void Run()
        {
            try
            {
                //SKYLINE Queries

                //string strPrefSQL = "SELECT t.id                           FROM cars t SKYLINE OF t.price LOW, t.mileage LOW";
                //string strPrefSQL = "select cars.id from cars";
                //string strPrefSQL = "SELECT *                           FROM cars t WHERE t.doors = 5 SKYLINE OF t.price LOW, t.mileage LOW, t.horsepower HIGH, t.consumption LOW, t.registrationnumeric HIGH";
                //string strPrefSQL = "SELECT t.title AS Modellname, t.price AS Preis, t.consumption AS Verbrauch FROM cars t LEFT OUTER JOIN Makes m ON t.make_id = m.id LEFT OUTER JOIN Bodies b ON t.body_id = b.id WHERE m.name = 'VW' AND b.name = 'Bus' SKYLINE OF t.price LOW 1000 EQUAL, t.consumption LOW";
                //string strPrefSQL = "SELECT t.id, t.title, t.price, c.name, t.enginesize FROM cars t LEFT OUTER JOIN colors c ON t.color_id = c.id SKYLINE OF t.price LOW, c.name ('pink' >> 'black' >> OTHERS INCOMPARABLE), t.enginesize HIGH ORDER BY BEST_RANK()";
                //string strPrefSQL = "SELECT t.id, t.title, t.price, c.name, t.enginesize FROM cars t LEFT OUTER JOIN colors c ON t.color_id = c.id SKYLINE OF t.price LOW, c.name (OTHERS EQUAL >> 'pink' >> 'red'), t.enginesize HIGH ORDER BY SUM_RANK()";
                //string strPrefSQL = "SELECT t.id, t.title, t.price         FROM cars t SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY BEST_RANK()";
                //string strPrefSQL = "SELECT t.id, t.price, t.mileage       FROM cars t SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY SUM_RANK()";



                //string strPrefSQL = "SELECT t.id, t.title, bodies.name AS Chassis, t.price, fuels.name                        FROM cars t LEFT OUTER JOIN fuels ON t.fuel_id = fuels.ID LEFT OUTER JOIN bodies ON t.body_id = bodies.ID SKYLINE OF bodies.name ('bus' >> OTHERS EQUAL) IS MORE IMPORTANT THAN t.price LOW, fuels.name ('petrol' >> OTHERS EQUAL)";
                //string strPrefSQL = "SELECT t.id, t.title, t.price, t.mileage, t.enginesize                    FROM cars t SKYLINE OF t.mileage LOW IS MORE IMPORTANT THAN t.price LOW, t.engineszie HIGH ";



                //SKYLINE Queries with JOINS
                //string strPrefSQL = "SELECT c.id, c.price, b.name          FROM cars t LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.price LOW, b.name ('Bus' >> 'Kleinwagen')";
                //string strPrefSQL = "SELECT c.id, c.price                  FROM cars t LEFT OUTER JOIN colors cc ON c.color_id = cc.id SKYLINE OF c.horsepower HIGH, cc.name ('red' >> 'blue' >> 'yellow')";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH, colors.name ('red' >> 'blue' >> 'yellow' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.pric       FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name (OTHERS INCOMPARABLE >> 'blue' >> 'red')";
                //string strPrefSQL = "SELECT c.id, c.price                  FROM cars t LEFT OUTER JOIN colors cc ON c.color_id = cc.id LEFT OUTER JOIN fuels f ON f.id = c.fuel_id SKYLINE OF c.price LOW 1000 INCOMPARABLE, cc.name ('red' == 'blue' >> OTHERS INCOMPARABLE >> 'gray'), f.name ('petrol' >> OTHERS INCOMPARABLE >> 'diesel')";
                //string strPrefSQL = "SELECT c.title AS Name, c.Price       FROM cars t LEFT OUTER JOIN colors co ON c.color_id = co.ID LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.Price LOW, c.Mileage LOW";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('silver' >> 'yellow' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t.id, t.title, t.price         FROM cars t LEFT OUTER JOIN colors ON t.color_id = colors.id SKYLINE OF t.price LOW, t.mileage LOW, colors.name ('red' >> {'blue', 'yellow'} >> OTHERS INCOMPARABLE)";

                //string strPrefSQL = "SELECT t.id                        FROM cars t LEFT OUTER JOIN bodies ON t.body_id = bodies.ID LEFT OUTER JOIN colors ON colors.id = t.color_id SKYLINE OF t.price AROUND 10000, colors.name ('red' >> OTHERS EQUAL), bodies.name ('van' >> 'compact car' >> OTHERS EQUAL) ORDER BY BEST_RANK()";



                //RANKING Queries
                string strPrefSQL = "SELECT t.id, t.title FROM cars t ORDER BY WEIGHTEDSUM (t.price LOW 0.8, t.mileage LOW 0.2)";
                //string strPrefSQL = "SELECT t.id, t.title FROM cars t LEFT OUTER JOIN colors c ON t.color_id = c.id ORDER BY WEIGTHEDSUM (t.price LOW 0.5, c.name ('brown' >> 'green' >> OTHERS EQUAL) 0.5)";



                //Playground --> Test here your queries
                //string strPrefSQL = "SELECT c.id, c.price, b.name          FROM cars_small c   LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.price LOW, b.name ('Bus' >> 'Kleinwagen')";
                //string strPrefSQL = "SELECT c.id, c.price                  FROM cars_small c   LEFT OUTER JOIN colors cc ON c.color_id = cc.id ORDER BY WEIGHTEDSUM (c.price LOW 0.5, cc.name ('brown' >> 'green') 0.5)";
                //string strPrefSQL = "SELECT c.id, c.price                  FROM cars_small c   LEFT OUTER JOIN colors cc ON c.color_id = cc.id SKYLINE OF c.horsepower HIGH, cc.name ('red' >> 'blue' >> 'yellow')";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars_small t1  LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t1.id                          FROM cars t1        ORDER BY WEIGHTEDSUM (t1.price HIGH 0.5, t1.mileage HIGH 0.5, t1.horsepower LOW 0.5, t1.enginesize LOW 0.5, t1.consumption HIGH 0.5, t1.doors LOW 0.5, t1.cylinders LOW 0.5)";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t1        LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH, colors.name ('red' >> 'blue' >> 'yellow' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t1        SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY BEST_RANK()";
                //string strPrefSQL = "SELECT t1.id                          FROM cars t1        SKYLINE OF t1.price LOW, t1.mileage LOW";
                //string strPrefSQL = "SELECT cars.id, cars.consumption      FROM cars           SKYLINE OF cars.consumption LOW, cars.enginesize HIGH, cars.price LOW";
                //string strPrefSQL = "SELECT *                              FROM cars           SKYLINE OF cars.registrationnumeric HIGH, cars.mileage LOW, cars.horsepower HIGH 100 EQUAL";
                //string strPrefSQL = "SELECT cars.id, cars.horsepower       FROM cars           SKYLINE OF cars.horsepower HIGH, cars.mileage LOW";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t1        LEFT OUTER JOIN colors ON t1.color_id = colors.ID SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price, t1.mileage, t1.enginesize           FROM cars t1        SKYLINE OF t1.price LOW, t1.mileage LOW, t1.enginesize HIGH ORDER BY SUM_RANK()";
                //string strPrefSQL = "SELECT t1.id                          FROM cars t1  SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW, t1.cylinders HIGH";
                //string strPrefSQL = "SELECT t1.id                          FROM cars_norm t1   SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower LOW, t1.enginesize LOW, t1.doors LOW, t1.consumption LOW, t1.cylinders LOW";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price      FROM cars t1        SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY BEST_RANK()";
                //string strPrefSQL = "SELECT t1.id AS ID, t1.title, t1.price FROM cars_small t1 SKYLINE OF t1.price LOW, t1.title ('hans' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price, t1.mileage, colors.name FROM cars_small t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('red' >> 'blue' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price, t1.mileage, colors.name FROM cars_small t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name (OTHERS INCOMPARABLE >> 'blue' >> 'red')";
                //string strPrefSQL = "SELECT * FROM cars_small cs SKYLINE OF cs.price LOW, cs.mileage LOW SAMPLE BY RANDOM_SUBSETS COUNT 2 DIMENSION 1";
                //string strPrefSQL = "SELECT t1.id, t1.price, t1.mileage FROM cars_small t1 SKYLINE OF t1.price LOW, t1.mileage LOW";
                //string strPrefSQL = "SELECT t1.id, t1.price, t1.mileage FROM cars_small t1 SKYLINE OF t1.price LOW, t1.mileage LOW ORDER BY SUM_RANK()";
                //string strPrefSQL = "SELECT t1.id                          FROM cars t1  SKYLINE OF t1.price LOW, t1.mileage LOW, t1.horsepower HIGH, t1.enginesize HIGH, t1.doors HIGH, t1.consumption LOW";

                //string strPrefSQL = "SELECT c.id, c.price                  FROM cars_medium c   LEFT OUTER JOIN colors cc ON c.color_id = cc.id LEFT OUTER JOIN fuels f ON f.id = c.fuel_id SKYLINE OF c.price LOW 1000 INCOMPARABLE, cc.name ('red' == 'blue' >> OTHERS INCOMPARABLE >> 'gray'), f.name ('petrol' >> OTHERS INCOMPARABLE >> 'diesel')";
                //string strPrefSQL = "SELECT c.title AS Name, c.Price, c.Mileage, co.Name AS Color, b.Name AS Body FROM Cars AS c LEFT OUTER JOIN colors co ON c.color_id = co.ID LEFT OUTER JOIN bodies b ON c.body_id = b.ID SKYLINE OF c.Price LOW, c.Mileage LOW";

                //string strPrefSQL = "SELECT t.id FROM cars t SKYLINE OF t.price LOW, t.mileage LOW";
                //string strPrefSQL = "SELECT t1.id, t1.title, t1.price, colors.name FROM cars t1 LEFT OUTER JOIN colors ON t1.color_id = colors.ID WHERE t1.price < 10000 SKYLINE OF t1.price LOW, colors.name ('silver' >> 'yellow' >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT cars.id, cars.title FROM  cars SKYLINE OF cars.price LOW,cars.mileage LOW,cars.horsepower HIGH,cars.enginesize HIGH,cars.consumption LOW,cars.doors HIGH,cars.seats HIGH";

                //string strPrefSQL = "SELECT t.id, t.title, t.price, colors.name AS colour FROM cars t LEFT OUTER JOIN colors ON t.color_id = colors.id SKYLINE OF t.price LOW, t.mileage LOW, colors.name ('red' >> {'blue', 'yellow'} >> OTHERS INCOMPARABLE)";
                //string strPrefSQL = "SELECT t.id, t.title FROM cars t ORDER BY WEIGHTEDSUM (t.price LOW 0.8, t.mileage LOW 0.2)";

                //string strPrefSQL = "SELECT t.id, t.title  FROM cars t    ORDER BY WEIGHTEDSUM( t.price LOW 0.8, t.mileage LOW 0.2)";

                //Query that results in more than 4000 Characters

                /*string strPrefSQL = "SELECT  t1.id	, t1.title	, t1.price	, t1.mileage	, colors.name FROM cars_small t1 " +
                 *                  "LEFT OUTER JOIN colors ON t1.color_id = colors.ID " +
                 *                  "LEFT OUTER JOIN bodies ON t1.body_id = bodies.id " +
                 *                  "LEFT OUTER JOIN Conditions ON t1.Condition_Id = Conditions.Id " +
                 *                  "LEFT OUTER JOIN Models ON t1.Model_Id = Models.Id " +
                 *                  "LEFT OUTER JOIN Makes ON t1.Make_Id = Makes.Id " +
                 *                  "LEFT OUTER JOIN Drives ON t1.Drive_Id = Drives.Id " +
                 *                  "LEFT OUTER JOIN Efficiencies ON t1.Efficiency_Id = Efficiencies.Id " +
                 *                  "LEFT OUTER JOIN Pollutions ON t1.Pollution_Id = Pollutions.Id " +
                 *                  "LEFT OUTER JOIN Transmissions ON t1.Transmission_Id = Transmissions.Id " +
                 *                  "LEFT OUTER JOIN Fuels ON t1.Fuel_Id = Fuels.Id " +
                 *                  "WHERE t1.price < 10000  " +
                 *                  "SKYLINE OF  " +
                 *                  "t1.price LOW " +
                 *                  ", colors.name " +
                 *                  "(" +
                 *                  "'anthracite' >> 'beige' >> 'blue' >> 'bordeaux' >> " +
                 *                  "    'brown' >> 'yellow' >> 'gold' >> 'gray' >> 'green' >> " +
                 *                  "    'orange' >> 'pink' >> 'red' >> 'black' >> 'silver' >> " +
                 *                  "    'turquoise' >> 'violet' >> 'white'" +
                 *                  ") " +
                 *
                 *                  ", bodies.name " +
                 *                  "(" +
                 *                  "    'bus' >> 'cabriolet' >> 'coupé' >> 'van' >> " +
                 *                  "    'compact car' >> 'estate car' >> 'minivan' >> " +
                 *                  "    'limousine' >> 'pick-up' >> 'scooter' >> 'suv' " +
                 *                  ")" +
                 *                  ", fuels.name " +
                 *                  "(" +
                 *                  "    'hybrid' >> 'bioethanol' >> 'diesel' >> 'gas' >> " +
                 *                  "    'electro' >> 'petrol' " +
                 *                  ")";*/

                //Query with 2 id spalten
                //string strPrefSQL = "SELECT t.id, colors.id as colorid, t.title, t.price, colors.name AS colour FROM cars t LEFT OUTER JOIN colors ON t.color_id = colors.id SKYLINE OF t.price LOW, t.mileage LOW, colors.name ('red' >> 'blue' >> OTHERS EQUAL)";

                Debug.WriteLine(strPrefSQL);
                SQLCommon parser = new SQLCommon();


                //Choose here your algorithm
                parser.SkylineType = new SkylineSQL();
                //parser.SkylineType = new SkylineBNL();
                //parser.SkylineType = new SkylineBNLSort();
                //parser.SkylineType = new SkylineHexagon();
                //parser.SkylineType = new SkylineDQ();
                //parser.SkylineType = new MultipleSkylineBNL();
                //parser.SkylineType = new SkylineDecisionTree();


                //Some other available properties
                parser.ShowInternalAttributes = true;
                parser.SkylineUpToLevel       = 1;



                //First parse only (to get the parsed string for CLR)
                Debug.WriteLine(parser.ParsePreferenceSQL(strPrefSQL));



                //Now parse and execute
                Stopwatch sw = new Stopwatch();
                sw.Start();
                DataTable dt = parser.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName, strPrefSQL);
                sw.Stop();


                StringBuilder sb = new StringBuilder();
                sb.AppendLine("------------------------------------------");
                sb.AppendLine("STATISTIC");
                sb.AppendLine("------------------------------------------");
                sb.AppendLine("         skyline size:" + dt.Rows.Count.ToString().PadLeft(6));
                sb.AppendLine("               fields:" + dt.Columns.Count.ToString().PadLeft(6));
                sb.AppendLine("algo  time elapsed ms:" + parser.TimeInMilliseconds.ToString().PadLeft(6));
                sb.AppendLine("total time elapsed ms:" + sw.ElapsedMilliseconds.ToString().PadLeft(6));
                Debug.Write(sb);
            }
            catch (Exception ex)
            {
                Debug.WriteLine("ERROR: " + ex);
            }

            Environment.Exit(0);
        }
Ejemplo n.º 18
0
        private void TestCompareAlgorithms()
        {
            var common = new SQLCommon
            {
                SkylineType = new SkylineBNL()
            };


            var sql =
                "SELECT cs.*, colors.name, fuels.name, bodies.name, makes.name, conditions.name FROM cars_large cs LEFT OUTER JOIN colors ON cs.color_id = colors.ID LEFT OUTER JOIN fuels ON cs.fuel_id = fuels.ID LEFT OUTER JOIN bodies ON cs.body_id = bodies.ID LEFT OUTER JOIN makes ON cs.make_id = makes.ID LEFT OUTER JOIN conditions ON cs.condition_id = conditions.ID SKYLINE OF cs.price LOW, cs.mileage LOW, cs.horsepower HIGH, cs.enginesize HIGH, cs.consumption LOW, cs.cylinders HIGH, cs.seats HIGH, cs.doors HIGH, cs.gears HIGH, colors.name ('red' >> OTHERS INCOMPARABLE), fuels.name ('diesel' >> 'petrol' >> OTHERS EQUAL), bodies.name ('limousine' >> 'coupé' >> 'suv' >> 'minivan' >> OTHERS EQUAL), makes.name ('BMW' >> 'MERCEDES-BENZ' >> 'HUMMER' >> OTHERS EQUAL), conditions.name ('new' >> 'occasion' >> OTHERS EQUAL)";

            PrefSQLModel model  = common.GetPrefSqlModelFromPreferenceSql(sql);
            string       sqlBNL = common.ParsePreferenceSQL(sql);


            DataTable entireSkylineDataTable =
                common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                                              sql);

            DbProviderFactory factory = DbProviderFactories.GetFactory(Helper.ProviderName);

            // use the factory object to create Data access objects.
            DbConnection connection = factory.CreateConnection();

            // will return the connection object (i.e. SqlConnection ...)
            connection.ConnectionString = Helper.ConnectionString;

            var dtEntire = new DataTable();

            DbDataAdapter dap           = factory.CreateDataAdapter();
            DbCommand     selectCommand = connection.CreateCommand();

            selectCommand.CommandText = sqlBNL;
            dap.SelectCommand         = selectCommand;

            dap.Fill(dtEntire);

            var common2 = new SQLCommon
            {
                SkylineType = new SkylineSQL()
            };
            string sqlNative = common2.ParsePreferenceSQL(sql);

            var dtEntire2 = new DataTable();

            DbDataAdapter dap2           = factory.CreateDataAdapter();
            DbCommand     selectCommand2 = connection.CreateCommand();

            selectCommand2.CommandText = sqlNative;
            dap2.SelectCommand         = selectCommand2;

            dap2.Fill(dtEntire2);
            connection.Close();

            foreach (DataRow i in dtEntire2.Rows)
            {
                var has = false;
                foreach (DataRow j in entireSkylineDataTable.Rows)
                {
                    if ((int)i[0] == (int)j[0])
                    {
                        has = true;
                        break;
                    }
                }
                if (!has)
                {
                    Debug.WriteLine(i[0]);
                }
            }
        }
Ejemplo n.º 19
0
        private void TestForSetCoverage()
        {
            var common = new SQLCommon
            {
                SkylineType =
                    new SkylineBNL()
                {
                    Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
                },
                ShowInternalAttributes = true
            };

            DataTable entireSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                             Helper.ProviderName, _entireSkylineSql);

            //Console.WriteLine("time entire: " + common.TimeInMilliseconds);
            //Console.WriteLine("count entire: " + entireSkylineDataTable.Rows.Count);

            int[] skylineAttributeColumns = SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);
            IReadOnlyDictionary <long, object[]> entireSkylineNormalized =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);

            SkylineSamplingHelper.NormalizeColumns(entireSkylineNormalized, skylineAttributeColumns);

            for (var i = 0; i < 10; i++)
            {
                DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                                 Helper.ProviderName, _skylineSampleSql);
                Console.WriteLine("time sample: " + common.TimeInMilliseconds);
                Console.WriteLine("count sample: " + sampleSkylineDataTable.Rows.Count);

                IReadOnlyDictionary <long, object[]> sampleSkylineNormalized =
                    prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);
                SkylineSamplingHelper.NormalizeColumns(sampleSkylineNormalized, skylineAttributeColumns);

                IReadOnlyDictionary <long, object[]> baseRandomSampleNormalized =
                    SkylineSamplingHelper.GetRandomSample(entireSkylineNormalized,
                                                          sampleSkylineDataTable.Rows.Count);
                IReadOnlyDictionary <long, object[]> secondRandomSampleNormalized =
                    SkylineSamplingHelper.GetRandomSample(entireSkylineNormalized,
                                                          sampleSkylineDataTable.Rows.Count);

                double setCoverageCoveredBySecondRandomSample = SetCoverage.GetCoverage(baseRandomSampleNormalized,
                                                                                        secondRandomSampleNormalized, skylineAttributeColumns);
                double setCoverageCoveredBySkylineSample = SetCoverage.GetCoverage(baseRandomSampleNormalized,
                                                                                   sampleSkylineNormalized, skylineAttributeColumns);

                DataTable entireSkylineDataTableBestRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                                         Helper.ProviderName,
                                                                                         _entireSkylineSqlBestRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));
                DataTable entireSkylineDataTableSumRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                                        Helper.ProviderName,
                                                                                        _entireSkylineSqlSumRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));

                IReadOnlyDictionary <long, object[]> entireSkylineDataTableBestRankNormalized =
                    prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableBestRank, 0);
                SkylineSamplingHelper.NormalizeColumns(entireSkylineDataTableBestRankNormalized, skylineAttributeColumns);
                IReadOnlyDictionary <long, object[]> entireSkylineDataTableSumRankNormalized =
                    prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableSumRank, 0);
                SkylineSamplingHelper.NormalizeColumns(entireSkylineDataTableSumRankNormalized, skylineAttributeColumns);

                double setCoverageCoveredByEntireBestRank = SetCoverage.GetCoverage(baseRandomSampleNormalized,
                                                                                    entireSkylineDataTableBestRankNormalized, skylineAttributeColumns);
                double setCoverageCoveredByEntireSumRank = SetCoverage.GetCoverage(baseRandomSampleNormalized,
                                                                                   entireSkylineDataTableSumRankNormalized, skylineAttributeColumns);

                Console.WriteLine("sc second random: " + setCoverageCoveredBySecondRandomSample);
                Console.WriteLine("sc sample       : " + setCoverageCoveredBySkylineSample);
                Console.WriteLine("sc entire best  : " + setCoverageCoveredByEntireBestRank);
                Console.WriteLine("sc entire sum   : " + setCoverageCoveredByEntireSumRank);
                Console.WriteLine();
                //Console.WriteLine("set coverage covered by second random sample: " +
                //                  setCoverageCoveredBySecondRandomSample);
                //Console.WriteLine("set coverage covered by skyline sample: " + setCoverageCoveredBySkylineSample);
            }
        }
Ejemplo n.º 20
0
        private void TestForClusterAnalysis()
        {
            var common = new SQLCommon
            {
                SkylineType =
                    new SkylineBNL()
                {
                    Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
                },
                ShowInternalAttributes = true
            };

            DbProviderFactory factory = DbProviderFactories.GetFactory(Helper.ProviderName);

            // use the factory object to create Data access objects.
            DbConnection connection = factory.CreateConnection();

            // will return the connection object (i.e. SqlConnection ...)
            connection.ConnectionString = Helper.ConnectionString;

            var dt = new DataTable();

            connection.Open();

            DbDataAdapter dap           = factory.CreateDataAdapter();
            DbCommand     selectCommand = connection.CreateCommand();

            selectCommand.CommandTimeout = 0; //infinite timeout

            string strQuery;
            string operators;
            int    numberOfRecords;

            string[] parameter;

            string ansiSql =
                common.GetAnsiSqlFromPrefSqlModel(common.GetPrefSqlModelFromPreferenceSql(_entireSkylineSql));

            prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out strQuery, out operators,
                                                         out numberOfRecords);

            selectCommand.CommandText = strQuery;
            dap.SelectCommand         = selectCommand;
            dt = new DataTable();

            dap.Fill(dt);

            DataTable entireSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                             Helper.ProviderName,
                                                                             _entireSkylineSql);

            int[] skylineAttributeColumns = SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);
            IReadOnlyDictionary <long, object[]> entireSkylineNormalized =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);

            SkylineSamplingHelper.NormalizeColumns(entireSkylineNormalized, skylineAttributeColumns);

            DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                             Helper.ProviderName,
                                                                             _skylineSampleSql);
            IReadOnlyDictionary <long, object[]> sampleSkylineNormalized =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);

            SkylineSamplingHelper.NormalizeColumns(sampleSkylineNormalized, skylineAttributeColumns);

            for (var i = 0; i < skylineAttributeColumns.Length; i++)
            {
                dt.Columns.RemoveAt(0);
            }

            IReadOnlyDictionary <long, object[]> full = prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(dt, 0);

            SkylineSamplingHelper.NormalizeColumns(full, skylineAttributeColumns);

            ClusterAnalysis.CalcMedians(full, skylineAttributeColumns);

            IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > entireBuckets =
                ClusterAnalysis.GetBuckets(entireSkylineNormalized, skylineAttributeColumns);
            IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > sampleBuckets =
                ClusterAnalysis.GetBuckets(sampleSkylineNormalized, skylineAttributeColumns);

            //IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aggregatedEntireBuckets =
            //    ClusterAnalysis.GetAggregatedBuckets(entireBuckets);
            //IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aggregatedSampleBuckets =
            //    ClusterAnalysis.GetAggregatedBuckets(sampleBuckets);

            IReadOnlyDictionary <BigInteger, List <IReadOnlyDictionary <long, object[]> > > fullB =
                ClusterAnalysis.GetBuckets(full, skylineAttributeColumns);
            // IReadOnlyDictionary<int, List<IReadOnlyDictionary<long, object[]>>> aFullB =
            //     ClusterAnalysis.GetAggregatedBuckets(fullB);

            IOrderedEnumerable <KeyValuePair <BigInteger, List <IReadOnlyDictionary <long, object[]> > > > sorted = fullB.OrderBy(l => l.Value.Count)
                                                                                                                    .ThenBy(l => l.Key);

            int len = Convert.ToInt32(Math.Pow(2, skylineAttributeColumns.Length));

            //for (var i = 0; i < len; i++)
            foreach (KeyValuePair <BigInteger, List <IReadOnlyDictionary <long, object[]> > > s in sorted)
            {
                BigInteger i             = s.Key;
                int        entire        = entireBuckets.ContainsKey(i) ? entireBuckets[i].Count : 0;
                int        sample        = sampleBuckets.ContainsKey(i) ? sampleBuckets[i].Count : 0;
                double     entirePercent = (double)entire / entireSkylineNormalized.Count;
                double     samplePercent = (double)sample / sampleSkylineNormalized.Count;
                int        fullX         = fullB.ContainsKey(i) ? fullB[i].Count : 0;
                double     fullP         = (double)fullX / full.Count;
                Console.WriteLine("-- {0,5} -- {5,6} ({6,7:P2} %) -- {1,6} ({3,7:P2} %) -- {2,6} ({4,7:P2} %)", i,
                                  entire, sample, entirePercent,
                                  samplePercent, fullX, fullP);
            }

            Console.WriteLine();
            Console.WriteLine("{0} - {1} - {2}", entireSkylineNormalized.Count, sampleSkylineNormalized.Count,
                              full.Count);
        }
Ejemplo n.º 21
0
        private void TestForDominatedObjects()
        {
            var common = new SQLCommon
            {
                SkylineType =
                    new SkylineBNL()
                {
                    Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString
                },
                ShowInternalAttributes = true
            };

            DataTable entireSkylineDataTable =
                common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                                              _entireSkylineSql);

            int[] skylineAttributeColumns =
                SkylineSamplingHelper.GetSkylineAttributeColumns(entireSkylineDataTable);

            DbProviderFactory factory = DbProviderFactories.GetFactory(Helper.ProviderName);

            // use the factory object to create Data access objects.
            DbConnection connection = factory.CreateConnection();

            // will return the connection object (i.e. SqlConnection ...)
            connection.ConnectionString = Helper.ConnectionString;

            var dtEntire = new DataTable();

            connection.Open();

            DbDataAdapter dap           = factory.CreateDataAdapter();
            DbCommand     selectCommand = connection.CreateCommand();

            selectCommand.CommandTimeout = 0; //infinite timeout

            string strQueryEntire;
            string operatorsEntire;
            int    numberOfRecordsEntire;

            string[] parameterEntire;

            string ansiSqlEntire =
                common.GetAnsiSqlFromPrefSqlModel(
                    common.GetPrefSqlModelFromPreferenceSql(_entireSkylineSql));

            prefSQL.SQLParser.Helper.DetermineParameters(ansiSqlEntire, out parameterEntire,
                                                         out strQueryEntire, out operatorsEntire,
                                                         out numberOfRecordsEntire);

            selectCommand.CommandText = strQueryEntire;
            dap.SelectCommand         = selectCommand;
            dtEntire = new DataTable();

            dap.Fill(dtEntire);

            for (var ii = 0; ii < skylineAttributeColumns.Length; ii++)
            {
                dtEntire.Columns.RemoveAt(0);
            }

            DataTable sampleSkylineDataTable = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                             Helper.ProviderName,
                                                                             _skylineSampleSql);

            DataTable entireSkylineDataTableBestRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                                     Helper.ProviderName,
                                                                                     _entireSkylineSqlBestRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));
            DataTable entireSkylineDataTableSumRank = common.ParseAndExecutePrefSQL(Helper.ConnectionString,
                                                                                    Helper.ProviderName,
                                                                                    _entireSkylineSqlSumRank.Replace("XXX", sampleSkylineDataTable.Rows.Count.ToString()));

            IReadOnlyDictionary <long, object[]> sampleSkylineDatabase =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(sampleSkylineDataTable, 0);
            IReadOnlyDictionary <long, object[]> entireDatabase =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(dtEntire, 0);
            IReadOnlyDictionary <long, object[]> entireSkylineDatabase =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTable, 0);
            IReadOnlyDictionary <long, object[]> entireSkylineDatabaseBestRank =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableBestRank, 0);
            IReadOnlyDictionary <long, object[]> entireSkylineDatabaseSumRank =
                prefSQL.SQLSkyline.Helper.GetDatabaseAccessibleByUniqueId(entireSkylineDataTableSumRank, 0);
            IReadOnlyDictionary <long, object[]> randomSample =
                SkylineSamplingHelper.GetRandomSample(entireSkylineDatabase, sampleSkylineDataTable.Rows.Count);

            var dominatedObjectsEntireSkyline = new DominatedObjects(entireDatabase, entireSkylineDatabase,
                                                                     skylineAttributeColumns);
            var dominatedObjectsSampleSkyline = new DominatedObjects(entireDatabase, sampleSkylineDatabase,
                                                                     skylineAttributeColumns);
            var dominatedObjectsEntireSkylineBestRank = new DominatedObjects(entireDatabase,
                                                                             entireSkylineDatabaseBestRank, skylineAttributeColumns);
            var dominatedObjectsEntireSkylineSumRank = new DominatedObjects(entireDatabase, entireSkylineDatabaseSumRank,
                                                                            skylineAttributeColumns);
            var dominatedObjectsRandomSample = new DominatedObjects(entireDatabase, randomSample,
                                                                    skylineAttributeColumns);

            Debug.WriteLine("entire database size: {0}", entireDatabase.Keys.ToList().Count);
            Debug.WriteLine("entire skyline size: {0}", entireSkylineDataTable.Rows.Count);
            Debug.WriteLine("sample skyline size: {0}", sampleSkylineDatabase.Keys.ToList().Count);
            Debug.WriteLine("random skyline size: {0}", randomSample.Keys.ToList().Count);
            Debug.WriteLine("best skyline size: {0}", entireSkylineDatabaseBestRank.Keys.ToList().Count);
            Debug.WriteLine("sum skyline size: {0}", entireSkylineDatabaseSumRank.Keys.ToList().Count);
            Debug.WriteLine("");
            Debug.WriteLine("");

            WriteSummary("entire", dominatedObjectsEntireSkyline);
            WriteSummary("sample", dominatedObjectsSampleSkyline);
            WriteSummary("random", dominatedObjectsRandomSample);
            WriteSummary("best", dominatedObjectsEntireSkylineBestRank);
            WriteSummary("sum", dominatedObjectsEntireSkylineSumRank);

            WriteDominatingObjects("entire", dominatedObjectsEntireSkyline);
            WriteDominatingObjects("sample", dominatedObjectsSampleSkyline);
            WriteDominatingObjects("random", dominatedObjectsRandomSample);
            WriteDominatingObjects("best", dominatedObjectsEntireSkylineBestRank);
            WriteDominatingObjects("sum", dominatedObjectsEntireSkylineSumRank);
        }
        public void TestSamplingOnlyNonDominatedObjectsWithinSampleSkyline()
        {
            string skylineSampleSql = TestContext.DataRow["skylineSampleSQL"].ToString();
            string entireSkylineSql = TestContext.DataRow["entireSkylineSQL"].ToString();
            string testComment = TestContext.DataRow["comment"].ToString();
            Debug.WriteLine(testComment);
            Debug.WriteLine(skylineSampleSql);

            string baseQuery;
            string operators;
            int numberOfRecords;
            string[] parameter;

            var common = new SQLCommon
            {
                SkylineType =
                    new SkylineBNL() { Provider = Helper.ProviderName, ConnectionString = Helper.ConnectionString }
            };

            PrefSQLModel prefSqlModelSkylineSample = common.GetPrefSqlModelFromPreferenceSql(skylineSampleSql);
            string ansiSql = common.GetAnsiSqlFromPrefSqlModel(prefSqlModelSkylineSample);

            prefSQL.SQLParser.Helper.DetermineParameters(ansiSql, out parameter, out baseQuery, out operators,
                out numberOfRecords);

            IEnumerable<CLRSafeHashSet<int>> useSubsets = UseSubsets(prefSqlModelSkylineSample);
            var subsetsProducer = new FixedSkylineSamplingSubsetsProducer(useSubsets);
            var utility = new SkylineSamplingUtility(subsetsProducer);
            var skylineSample = new SkylineSampling(utility)
            {
                SubsetCount = prefSqlModelSkylineSample.SkylineSampleCount,
                SubsetDimension = prefSqlModelSkylineSample.SkylineSampleDimension,
                SelectedStrategy = common.SkylineType
            };

            DataTable entireSkyline = common.ParseAndExecutePrefSQL(Helper.ConnectionString, Helper.ProviderName,
                entireSkylineSql);
            DataTable sampleSkyline = skylineSample.GetSkylineTable(baseQuery, operators);

            HashSet<int> entireSkylineObjectsIds = GetHashSetOfIdsFromDataTable(entireSkyline);
            HashSet<int> sampleSkylineObjectsIds = GetHashSetOfIdsFromDataTable(sampleSkyline);

            sampleSkylineObjectsIds.ExceptWith(entireSkylineObjectsIds);

            Debug.WriteLine("wrong objects:");
            foreach (int i in sampleSkylineObjectsIds)
            {
                Debug.WriteLine(i);
            }

            Assert.IsTrue(sampleSkylineObjectsIds.IsSubsetOf(entireSkylineObjectsIds),
                "Dominated objects contained in Sample Skyline (i.e., objects which are not contained in the entire Skyline).");
        }