/EffiProz-h18/EffiProzDBTest/TestSubselect.cs

//

// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)

// All rights reserved.

//



using System;

using System.Text;

using System.Collections.Generic;

using System.Linq;

using Microsoft.VisualStudio.TestTools.UnitTesting;

using EffiProzDB;

using EffiProzDB.Lib;

using EffiProzDB.DataTypes;

using EffiProzDB.Persist;



namespace EffiProzDBTest

{

    /// <summary>

    /// Summary description for TestSubselect

    /// </summary>

    [TestClass]

    public class TestSubselect : BaseTest

    {

        public TestSubselect()

        {

          

        }



        private TestContext testContextInstance;



        /// <summary>

        ///Gets or sets the test context which provides

        ///information about and functionality for the current test run.

        ///</summary>

        public TestContext TestContext

        {

            get

            {

                return testContextInstance;

            }

            set

            {

                testContextInstance = value;

            }

        }



        void createDataset()

        {



            CleanUp("TestSubselect");

            sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "TestSubselect",

                   user, password, props);



            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "drop table colors if exists; "

                               + "drop table sizes if exists; "

                               + "drop table fruits if exists; "

                               + "drop table trees if exists; ");



            TestHelper.ExecuteUpdateSQL_Success(sessionProxy,

                 "create table colors(id int, val char); "

                 + "insert into colors values(1,'red'); "

                 + "insert into colors values(2,'green'); "

                 + "insert into colors values(3,'orange'); "

                 + "insert into colors values(4,'indigo'); "

                 + "create table sizes(id int, val char); "

                 + "insert into sizes values(1,'small'); "

                 + "insert into sizes values(2,'medium'); "

                 + "insert into sizes values(3,'large'); "

                 + "insert into sizes values(4,'odd'); "

                 + "create table fruits(id int, name char, color_id int); "

                 + "insert into fruits values(1, 'golden delicious',2); "

                 + "insert into fruits values(2, 'macintosh',1); "

                 + "insert into fruits values(3, 'red delicious',1); "

                 + "insert into fruits values(4, 'granny smith',2); "

                 + "insert into fruits values(5, 'tangerine',4); "

                 + "create table trees(id int, name char, fruit_id int, size_id int); "

                 + "insert into trees values(1, 'small golden delicious tree',1,1); "

                 + "insert into trees values(2, 'large macintosh tree',2,3); "

                 + "insert into trees values(3, 'large red delicious tree',3,3); "

                 + "insert into trees values(4, 'small red delicious tree',3,1); "

                 + "insert into trees values(5, 'medium granny smith tree',4,2); ");



        }







     /**

     * This test is basically a sanity check of the data set.

     */

        public void testSimpleJoin()

        {



            String sql =

                "select trees.id, trees.name, sizes.val, fruits.name, colors.val"

                + " from trees, sizes, fruits, colors"

                + " where trees.size_id = sizes.id"

                + " and trees.fruit_id = fruits.id"

                + " and fruits.color_id = colors.id" + " order by 1";

            int expectedRows = 5;

            String[] expectedTrees = new String[] {

            "small golden delicious tree", "large macintosh tree",

            "large red delicious tree", "small red delicious tree",

            "medium granny smith tree"

        };

            String[] expectedSizes = new String[] {

            "small", "large", "large", "small", "medium"

        };

            String[] expectedFruits = new String[] {

            "golden delicious", "macintosh", "red delicious", "red delicious",

            "granny smith"

        };

            String[] expectedColors = new String[] {

            "green", "red", "red", "red", "green"

        };



            Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, null);

            String[] trees = new String[expectedRows];

            String[] fruits = new String[expectedRows];

            String[] sizes = new String[expectedRows];

            String[] colors = new String[expectedRows];





            for (int rowCount = 0; rowCount < expectedRows; rowCount++)

            {

                TestHelper.MatchSingleResultRow(results, rowCount,rowCount+1, expectedTrees[rowCount], expectedSizes[rowCount],

                    expectedFruits[rowCount], expectedColors[rowCount]);



                rowCount++;

            }

        }





         /**

     * Inner select with where clause in outer select having column with same name as where clause in inner select

     */

    public void testWhereClausesColliding()  {



        String sql =

            "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";

        String[] expected = new String[] {

            "golden delicious", "macintosh"

        };



        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);

        for (int rowCount = 0; rowCount < expected.Length; rowCount++)

        {

            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);

        }

       

    }



        /**

     * As above, with table aliases.

     */

    public void testWhereClausesCollidingWithAliases() {



        String sql =

            "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";

        String[] expected = new String[] {

            "golden delicious", "macintosh"

        };



        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);

        for (int rowCount = 0; rowCount < expected.Length; rowCount++)

        {

            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);

        }

    }



         /**

     * Inner select with two tables having columns with the same name, one of which is referred to in the

     * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column,

     * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all).

     */

    public void testHiddenCollision()  {



        String sql =

            "select name from fruits where id in (select fruit_id from trees) order by name";

        String[] expected = new String[] {

            "golden delicious", "granny smith", "macintosh", "red delicious"

        };



        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);

        for (int rowCount = 0; rowCount < expected.Length; rowCount++)

        {

            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);

        }

    }



         /**

     * As above, with table aliases.

     */

    public void testHiddenCollisionWithAliases() {



        String sql =

            "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";

        String[] expected = new String[] {

            "golden delicious", "granny smith", "macintosh", "red delicious"

        };



        compareResults(sql, expected);

    }



    public void compareResults(string sql, string[] expected)

    {

        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);

        for (int rowCount = 0; rowCount < expected.Length; rowCount++)

        {

            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);

        }

    }





         /**

     * Inner select with where clause in outer select having column with same name as select clause in inner select

     */

    public void testWhereSelectColliding()  {



        // Yes, this is a nonsensical query

        String sql =

            "select val from colors where id in (select id from trees where fruit_id = 3) order by val";

        String[] expected = new String[] {

            "indigo", "orange"

        };



        compareResults(sql, expected);

    }



        /**

     * As above, with aliases.

     */

    public void testWhereSelectCollidingWithAliases()  {



        // Yes, this is a nonsensical query

        String sql =

            "select a.val from colors a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.val";

        String[] expected = new String[] {

            "indigo", "orange"

        };



        compareResults(sql, expected);

    }





         /**

     * Inner select involving same table

     */

    public void testSameTable()  {



        String sql =

            "select name from trees where id in (select id from trees where fruit_id = 3) order by name";

        String[] expected = new String[] {

            "large red delicious tree", "small red delicious tree"

        };



        compareResults(sql, expected);

    }



         /**

     * As above with aliases.

     */

    public void testSameTableWithAliases() {



        String sql =

            "select a.name from trees a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.name";

        String[] expected = new String[] {

            "large red delicious tree", "small red delicious tree"

        };



        compareResults(sql, expected);

    }



         /**

     *     Inner select involving same table as one of two joined tables in outer select

     */

    public void testSameTableWithJoin() {



        String sql =

            "select sizes.val from trees, sizes where sizes.id = trees.size_id and trees.id in (select id from trees where fruit_id = 3) order by sizes.val";

        String[] expected = new String[] {

            "large", "small"

        };



        compareResults(sql, expected);

    }



    /**

     * Tests two subselects, anded.

     */

    public void testAndedSubselects() {



        String sql =

            "select name from trees where size_id in (select id from sizes where val = 'large') and fruit_id in (select id from fruits where color_id = 1) order by name";

        String[] expected = new String[] {

            "large macintosh tree", "large red delicious tree"

        };



        compareResults(sql, expected);

    }



        /**

     * Test nested subselects.

     */

    public void testNestedSubselects()  {



        String sql =

            "select name from trees where fruit_id in (select id from fruits where color_id in (select id from colors where val = 'red')) order by name";

        String[] expected = new String[] {

            "large macintosh tree", "large red delicious tree",

            "small red delicious tree"

        };



        compareResults(sql, expected);

    }



    /**

     * Inner select with "not in" in outer select where clause.

     */

    public void testNotIn() {



        String sql =

            "select name from fruits where id not in (select fruit_id from trees) order by name";

        String[] expected = new String[]{ "tangerine" };



        compareResults(sql, expected);

    }



    /**

     * Inner select with "not in" in outer select where clause and same table in inner select where clause.

     */

    public void testNotInSameTableAndColumn()  {



        String sql =

            "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";

        String[] expected = new String[] {

            "macintosh", "red delicious"

        };



        compareResults(sql, expected);

    }



         /**

     * Inner select reusing alias names from outer select, but using them for different tables

     */

    public void testAliasScope() {



        String sql =

            "select a.val, b.name from sizes a, trees b where a.id = b.size_id and b.id in (select a.id from trees a, fruits b where a.fruit_id = b.id and b.name='red delicious') order by a.val";

        String[] expectedSizes = new String[] {

            "large", "small"

        };

        String[] expectedTrees = new String[] {

            "large red delicious tree", "small red delicious tree"

        };



        Assert.AreEqual(expectedSizes.Length, expectedTrees.Length,

            "Programmer error: expected arrays should be of equal length.");





        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expectedTrees.Length);





        for (int rowCount = 0; rowCount < expectedTrees.Length; rowCount++)

        {

            TestHelper.MatchSingleResultRow(results, rowCount, expectedSizes[rowCount], expectedTrees[rowCount]);

        }



    

    }



        #region Additional test attributes

        //

        // You can use the following additional attributes as you write your tests:

        //

        // Use ClassInitialize to run code before running the first test in the class

        // [ClassInitialize()]

        // public static void MyClassInitialize(TestContext testContext) { }

        //

        // Use ClassCleanup to run code after all tests in a class have run

        // [ClassCleanup()]

        // public static void MyClassCleanup() { }

        //

        // Use TestInitialize to run code before running each test 

        // [TestInitialize()]

        // public void MyTestInitialize() { }

        //

        // Use TestCleanup to run code after each test has run

        // [TestCleanup()]

        // public void MyTestCleanup() { }

        //

        #endregion



        [TestMethod]

    public void Subselect()

        {

            createDataset();

            testSimpleJoin();

            testWhereClausesColliding();

            testWhereClausesCollidingWithAliases();

            testWhereSelectColliding();

            testWhereSelectCollidingWithAliases();

            testSameTable();

            testSameTableWithAliases();

            testSameTableWithJoin();

            testAndedSubselects();

            testNestedSubselects();

            testNotIn();

            testNotInSameTableAndColumn();

        }

    }

}