Home > Uncategorized > What am I upto these days

What am I upto these days

After struggling with WCF enabling our application (may be partially because I didn’t approach it in a formal way, I didn’t read any good WCF literature before starting the work on WCF) for a couple of sprints, I am working in our application’s Query Object Model along with Sreedhar.
Some of the tests

	    [Test()]
            public void FindFirstNameEqualsNancy()
            {
                // SELECT * FROM Employees WHERE FirstName = 'Nancy'

                IQueryItem query = new QueryBuilder().
                    
                    Where.Employee.FirstName.Equals("Nancy")

                    .ToQueryItem();

                StringAssert.Contains("SELECT  Employees.*   FROM Employees  WHERE  ( (Employees.FirstName = 'Nancy'))", query.GetSQLQuery());

            }

	    [Test()]
            public void FindFirstNameEqualsNancyAndLastNameEqualsDavolio()
            {
                // SELECT * FROM Employees WHERE FirstName = 'Nancy' AND LastName='Davolio'

                IQueryItem query = new QueryBuilder().

                    Where.Employee.FirstName.Equals("Nancy")
                        .And.
                    Employee.LastName.Equals("Davolio")    

                    .ToQueryItem();

                StringAssert.Contains("SELECT  Employees.*   FROM Employees  WHERE  ( (Employees.FirstName = 'Nancy')AND (Employees.LastName = 'Davolio'))", query.GetSQLQuery());

            }

            [Test()]
            public void FindEmployeesHiredInLastMonth()
            {
                //-- Employees Hired in the last month
                //SELECT * FROM Employees WHERE (DATEDIFF(MM, Employees.HireDate, GETDATE()) = 1 )

                IQueryItem query = new QueryBuilder().

                    Where.Employee.HireDate.WasInTheLastMonth()
                    
                    .ToQueryItem();

                StringAssert.Contains("SELECT  Employees.*   FROM Employees  WHERE  ( (DATEDIFF ( MM, Employees.HireDate, GETDATE()) = 1 ))", query.GetSQLQuery());
            }

            [Test()]
            public void FindEmployeesInUSUKWhoAreSalesRepresentatives()
            {
                //-- Employees from the US/UK Facility who are sales representatives
                //SELECT * FROM Employees WHERE (Country = 'Uk' OR Country = 'USA') AND Title = 'Sales Representative'

                IQueryItem query =
                    (
                        (
                            new QueryBuilder().

                            Where.Employee.Country.Equals("US")
                                .Or.
                            Employee.Country.Equals("UK")
                        )

                            &

                        new QueryBuilder().

                        Where.Employee.Title.Equals("Sales Representative")
                    )
                    .ToQueryItem();

                StringAssert.Contains("SELECT  Employees.*   FROM Employees  WHERE  (  (  ( (Employees.Country = 'US')) OR (Employees.Country = 'UK')) AND (Employees.Title = 'Sales Representative'))", query.GetSQLQuery());

            }

            [Test()]
            public void FindEmployeesWhoDonotWork()
            {
                // SELECT * FROM Employees WHERE Title LIKE '%Manager%'

                IQueryItem query = new QueryBuilder().
                    Where.Employee.FindWhoDonotWork
                    .ToQueryItem();

                StringAssert.Contains("SELECT  Employees.*   FROM Employees  WHERE  ( (Employees.Title LIKE '%Manager%'))", query.GetSQLQuery());
            }

            [Test()]
            public void FindOrdersForACustomerByCompanyName()
            {
                // SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Customers.CompanyName = 'Alfreds Futterkiste'

                IQueryItem query = new QueryBuilder().
                    Where.Order.Customer.CompanyName.Equals("Alfreds Futterkiste")
                    .ToQueryItem();

                StringAssert.Contains("SELECT  Orders.*   FROM Orders  INNER JOIN  [Customers] AS Customers ON ( [Customers].[CustomerID] = [Orders].[CustomerID]) WHERE  ( (Customers.CompanyName = 'Alfreds Futterkiste'))", query.GetSQLQuery());
            }

            [Test()]
            public void FindCustomersWhoPlacedOrdersOnAGivenDate()
            {
                // Better Done with IN, DISTINCT Issue in the generated JOIN??
                // SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.OrderDate = '7/4/1996'

                IQueryItem query = new QueryBuilder().
                    Where.Customer.Orders.OrderDate.On(new DateTime(1996, 7, 4))
                    .ToQueryItem();

                StringAssert.Contains("SELECT  Customers.*   FROM Customers  INNER JOIN  [Orders] AS Orders ON ( [Orders].[CustomerID] = [Customers].[CustomerID]) WHERE  ( ( DATEDIFF ( DD, Orders.OrderDate, '7/4/1996 12:00:00 AM') = 0 )) ", query.GetSQLQuery());
            }

A few salient features of the Query Object Model 

It is strongly typed, Where. lists the Tables available, Employee. lists the columns available on Employee table(, or related tables to Employee), FirstName. lists the operators supported for the string datatype, Operator methods take a specific strongly typed datatype parameter.
It is intellisense friendly, Mostly intellisense lists the tokens which are valid as the next token in the expression. Ex: HireDate. lists WasInLastMonth, WasInLastWeek (since hiredate is a datetime), WasinLastWeek(). lists And, Or & ToQueryItem and so on. If only the operator overloads can take & return Interfaces as parameters it would have been more pure. Sigh!
It is intuitive for the client to use
Thanks to Martin Fowler’s atricle on Expression Builder and his Link to the JMock eDSL white paper, the FluentInterface is coming good.
Steve and Nat’s paper was really helpful especially "How to write a language in Java (and C#) " section was really helpful.
"… like a dog’s walking on his hinder legs. It is not done well; but you are surprised to find it done at all." – Dr. Johnson A funny quote from the paper.

I do understand that Querying Is a Business Concern (Thanks to ayende and also to all the typed datasets bashing by the community).
It will be used from middle tier mostly. When it has to be used from the client, we will use abstractions like FindEmployeesWhoDonotWork().
The Method FindEmployeesWhoDonotWork() was a simple extension added on the Query Object Model and offers a nice littler abstraction.
Today’s business rule states Manager, who knows tomorrow business rules might include AA’s (not agile architect but arrogant architects like me, a nice little title suggested by Manu) like me. The clients who use the Query still remain unchanged and the actual change only occurs in one place FindEmployeesWhoDonotWork. Also this ensures the client is not coupled to database things like column names etc…

A special thanks to Prakash for giving me the freedom to express this in a Fixed Bid effort like the one which I am currently in.

Advertisements
Categories: Uncategorized
  1. ambati
    August 27, 2007 at 5:12 am

    This is one of the best feature.
    Sendhil made it possible within short time.
    We are expecting a good tool of Object Query Language from Sendhil.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: