This one had me scratching my head for a while. I had a tiered query grouped by a number of fields in the table structure. I wanted to get a count of all applicable items matching a set of given statuses.
For example: Toys
Type
Manufacturer # Sold # Returned
Cars
--Vrroom 14 0
--Bigga 16 2
Dolls
--Munchkin 22 4
--Slimer 7 1
etc.
Originally I had a query along the lines of:
SELECT i.InventoryType,
p.ProductName,
m.ManufacturerName,
CASE t.TransactionType
WHEN 1
THEN 'Sold'
WHEN 2
THEN 'Returned'
ELSE 'N/A'
END AS TransactionStatus,
COUNT(t.TransactionID) AS TransactionCount
FROM Inventory i
INNER JOIN Product p ON i.InventoryID = p.InventoryID
INNER JOIN Manufacturer m ON p.ManufacturerID = m.ManufacturerID
INNER JOIN Transaction t ON p.ProductID = t.ProductID
WHERE .... -- Conditions...
(Now this is only a rough approximation of the issue I was facing)
I then grouped within the report and used a conditional field on the transaction status to count applicable items. It sort of worked, but produced 2 lines for the items that had both sales and returns:
Cars
--Vrroom 14 0
--Bigga 16 0
-- 0 2
Dolls
--Munchkin 22 0
-- 0 4
--Slimer 7 0
-- 0 1
At first this looked like an issue within the SSRS report where it was grouping by Details. I removed that grouping which at first looked like it was working (1 row per product manufacturer) but that just "knocked off" one of the values. (I.e. for "Bigga" I'd end up with either 16/0 or 0/2, essentially depending on which status was found first.)
Round 2: I went back to the query determined to do the grouping properly in SQL.
...
m.ManufacturerName,
CASE t.TransactionType
WHEN 1
COUNT(t.TransactionID)
ELSE
0
END AS SoldCount,
CASE t.TransactionType
WHEN 2
COUNT(t.TransactionID)
ELSE
0
END AS ReturnedCount
...
This required t.TransactionType to be in the GroupBy clause which resulted in query results pretty much identical to what I was seeing in the report.
Round 3:
...
m.ManufacturerName,
SUM(CASE t.TransactionType
WHEN 1
THEN 1
ELSE 0
END) AS SoldCount,
SUM(CASE t.TransactionType
WHEN 2
THEN 1
ELSE 0
END) AS ReturnedCount
...
And Bingo!
Cars
--Vrroom 14 0
--Bigga 16 2
Dolls
--Munchkin 22 4
--Slimer 7 1
(Also, if a transaction had a quantity you wanted to count by then it'd be summing on t.Quantity rather than "1" (transaction count))
Friday, June 4, 2010
Thou shall not transmit localized DateTime values.
Date and time formats come in a variety of flavours, but one combination causes countless bugs, crashes, and potentially numerous lawsuits: dd/mm/yyyy vs. mm/dd/yyyy. If you ever come across "It worked yesterday, but it's crashing today" pretty much anywhere other than North America you've met this little gem.
The important thing to remember about DateTime values is that they are floating point numbers. 06/01/2010 is NOT a date, it is the localization of a date. The computer you give that localization to has to interpret what the actual date is. Is it January 6th, or June 1st? ANY, and EVERY time you need to transmit or transfer a DateTime from one source to another, you MUST take this into account. Generally this means every time the datetime transfers from one machine to another, such as to a Database server, a reporting server, or a web service. Today that may be two machines that have the same regional settings, but it just takes one server or client PC to be set to a different regional date format to totally hose your system. One thing you should NEVER see in code is a ".ToString()" (empty parameters) applied to a DateTime variable.
Now you don't need to go and start passing DateTime values around as floating point numbers to avoid this issue. (though that is one option.) There is one other practical option when transmitting DateTime values is to use ISO formatting. International Standard formatting for DateTime values is: yyyy-MM-dd HH:mm:ss. That is, 2010-01-06 19:04:30. In .Net this is commonly known as "Sortable". The main reason why this is advantageous is that regardless of their local date formats, any application worth its weight in salt *will* accept an ISO DateTime with its 24hr time format. ISO DateTimes also have the advantage that they are fully sortable so you can sort down to the month, day, hour, etc. or generate sequential numbers for things like filenames.
So there's no need to abandon the DateTime field type whether in .Net, SSRS, Crystal Reports, or SQL Server / Oracle or start farting around with regional settings; Just adopt ISO date and time formats when passing around dates.
The important thing to remember about DateTime values is that they are floating point numbers. 06/01/2010 is NOT a date, it is the localization of a date. The computer you give that localization to has to interpret what the actual date is. Is it January 6th, or June 1st? ANY, and EVERY time you need to transmit or transfer a DateTime from one source to another, you MUST take this into account. Generally this means every time the datetime transfers from one machine to another, such as to a Database server, a reporting server, or a web service. Today that may be two machines that have the same regional settings, but it just takes one server or client PC to be set to a different regional date format to totally hose your system. One thing you should NEVER see in code is a ".ToString()" (empty parameters) applied to a DateTime variable.
Now you don't need to go and start passing DateTime values around as floating point numbers to avoid this issue. (though that is one option.) There is one other practical option when transmitting DateTime values is to use ISO formatting. International Standard formatting for DateTime values is: yyyy-MM-dd HH:mm:ss. That is, 2010-01-06 19:04:30. In .Net this is commonly known as "Sortable". The main reason why this is advantageous is that regardless of their local date formats, any application worth its weight in salt *will* accept an ISO DateTime with its 24hr time format. ISO DateTimes also have the advantage that they are fully sortable so you can sort down to the month, day, hour, etc. or generate sequential numbers for things like filenames.
So there's no need to abandon the DateTime field type whether in .Net, SSRS, Crystal Reports, or SQL Server / Oracle or start farting around with regional settings; Just adopt ISO date and time formats when passing around dates.
Saturday, May 1, 2010
Linq2SQL is *NOT* an ORM
I think this needs to be stressed as much as possible. Linq2SQL, and other deviations that generate an object model based on a relational database are NOT ORMs.
ORM: Object-Relational Mapper
"Mapper" is the key missing ingredient with solutions like Linq2SQL. The key driving concept of an ORM is to allow you to develop an object model in a manner that meets the business needs of an application, or suite of applications, completely independently of the data source or sources serving that business. This doesn't mean that a product tied to a particular RDBMS has to be any less of an ORM than one that can serve a number of RDBMS; that isn't the point of the mapping. The point of the mapping element is that the views in your application can be served by domain objects designed specifically for the purpose of serving those views, irregardless of the data structure behind the data of that view. If it helps, think of earlier implementations of Views within an RDBMS, or stored procedures that were designed to flatten and translate highly relational data into a data form designed to serve a specific purpose. An ORM takes over for that role.
Linq2SQL in no way does any of this. A more accurate acronym for Linq2SQL and the like would be a ROG, or Relational Object Generator. Linq2SQL generates objects in accordance to the relational model within the database for the application to consume. Not that this approach does not have its merits for certain situations, but it is a completely different barrel of fish.
Any "ORM" that purportedly generates an object model from a database is flat-out not an ORM. There is no mapping when the relationships are 1-1 between objects and data tables; It's a generator.
ORM: Object-Relational Mapper
"Mapper" is the key missing ingredient with solutions like Linq2SQL. The key driving concept of an ORM is to allow you to develop an object model in a manner that meets the business needs of an application, or suite of applications, completely independently of the data source or sources serving that business. This doesn't mean that a product tied to a particular RDBMS has to be any less of an ORM than one that can serve a number of RDBMS; that isn't the point of the mapping. The point of the mapping element is that the views in your application can be served by domain objects designed specifically for the purpose of serving those views, irregardless of the data structure behind the data of that view. If it helps, think of earlier implementations of Views within an RDBMS, or stored procedures that were designed to flatten and translate highly relational data into a data form designed to serve a specific purpose. An ORM takes over for that role.
Linq2SQL in no way does any of this. A more accurate acronym for Linq2SQL and the like would be a ROG, or Relational Object Generator. Linq2SQL generates objects in accordance to the relational model within the database for the application to consume. Not that this approach does not have its merits for certain situations, but it is a completely different barrel of fish.
Any "ORM" that purportedly generates an object model from a database is flat-out not an ORM. There is no mapping when the relationships are 1-1 between objects and data tables; It's a generator.
Friday, April 9, 2010
When configuration goes pear-shaped.
This one was a rather amusing recent development in the system I am currently assigned to maintain.
The system is highly configurable. Several of the driving queries used within the application are themselves stored as SQL within the database. Even the menu structure is built based on a query stored in the DB. The premise behind this was that if we need to tweak how these queries pull data we can do so without re-deploying the application. (A Silverlight + Web Services business app.) Wonderful!
So an issue comes up where some data is coming back in the wrong sort order. It turns out to be an issue between #null vs. empty string values. A relatively simple change to the stored SQL statement should be all that's necessary so I test and script the change, deploy it into the test environment, then close and re-open my SL client to be sure and....
The old query results are still coming back.
Those beautiful dynamic SQL statements are cached in the application pool which means after changing a script you got to cycle the server. *sigh*.
The drive for configuration is mostly valid because we want to avoid making changes to the Silverlight client as much as possible. (A whopping 3.5MB D/L whenever this changes, [recently shrunk to 2.7MB] which with several hundred clients and more on the way will eat dearly into our ISP upload thresholds.) But queries like this are executed server-side anyways so it's easy enough to update server-side code without re-building the SL client.
Regardless it's not that bad of a scenario, but just goes to show that often the best laid plans still develop sizeable potholes.
The system is highly configurable. Several of the driving queries used within the application are themselves stored as SQL within the database. Even the menu structure is built based on a query stored in the DB. The premise behind this was that if we need to tweak how these queries pull data we can do so without re-deploying the application. (A Silverlight + Web Services business app.) Wonderful!
So an issue comes up where some data is coming back in the wrong sort order. It turns out to be an issue between #null vs. empty string values. A relatively simple change to the stored SQL statement should be all that's necessary so I test and script the change, deploy it into the test environment, then close and re-open my SL client to be sure and....
The old query results are still coming back.
Those beautiful dynamic SQL statements are cached in the application pool which means after changing a script you got to cycle the server. *sigh*.
The drive for configuration is mostly valid because we want to avoid making changes to the Silverlight client as much as possible. (A whopping 3.5MB D/L whenever this changes, [recently shrunk to 2.7MB] which with several hundred clients and more on the way will eat dearly into our ISP upload thresholds.) But queries like this are executed server-side anyways so it's easy enough to update server-side code without re-building the SL client.
Regardless it's not that bad of a scenario, but just goes to show that often the best laid plans still develop sizeable potholes.
Wednesday, March 24, 2010
Using consistency's name in vain.
This is a bit of a pet peeve, and something I've heard repeatedly to justify bad code and/or bad design. "It needs to stay consistent."
Question: How can code or design improve if it must remain consistent?
Code and design should be free to evolve within the lifespan of a project, to freely address concerns that come up with the initial approach and constantly challenge whether or not the original assumptions were really the best decision for the business. Going back through all existing functionality and re-factoring based on new technologies, patterns, or preferences of the customer can be prohibitively expensive, but that should not be an excuse not to adopt it going forward... <caveat> *If* it is what the customers wants. </caveat>
A consistent approach to an application is important, but it's a bad idea to take it so far as to make it extremely rigid. A common pitfall is with UI. For instance, stressing a design that says that all forms will have a default set of action buttons for wizard-like behaviour (Next, Prev, Cancel, Skip) is just asking for trouble when the customer wants to do something different. Pretty soon the "Cancel" button gets re-labelled and on screen X, performs action Y. Designing all forms to be dynamic, based based on some construct of a framework leading to slow, buggy, and not-so-user-friendly experiences is another example.
It's not that designs like this are inherently bad. They aren't, but they aren't guaranteed to be suitable to 100% of cases out there.
Question: How can code or design improve if it must remain consistent?
Code and design should be free to evolve within the lifespan of a project, to freely address concerns that come up with the initial approach and constantly challenge whether or not the original assumptions were really the best decision for the business. Going back through all existing functionality and re-factoring based on new technologies, patterns, or preferences of the customer can be prohibitively expensive, but that should not be an excuse not to adopt it going forward... <caveat> *If* it is what the customers wants. </caveat>
A consistent approach to an application is important, but it's a bad idea to take it so far as to make it extremely rigid. A common pitfall is with UI. For instance, stressing a design that says that all forms will have a default set of action buttons for wizard-like behaviour (Next, Prev, Cancel, Skip) is just asking for trouble when the customer wants to do something different. Pretty soon the "Cancel" button gets re-labelled and on screen X, performs action Y. Designing all forms to be dynamic, based based on some construct of a framework leading to slow, buggy, and not-so-user-friendly experiences is another example.
It's not that designs like this are inherently bad. They aren't, but they aren't guaranteed to be suitable to 100% of cases out there.
Saturday, March 13, 2010
Explicit Interfaces
The explicit use of interfaces is something I really try to follow and encourage others to start using.
Implement Interface Explicity
The second option for implementing interfaces implements the interface member effectively as a guarded public method, accessible solely through an interface reference instead of a public member. I don't think I've come across a development team that has used this option, but after experimenting with it, I think it's something that should be adopted a bit more by default.
My goal when writing code is to define contracts for the interaction between distinct units of work. This gives me the flexibility to swap out classes easily and quickly. This means I'm predominantly working through interfaces. The most significant benefit of implementing interfaces explicitly is that it means any change to the interface is immediately picked up in the implementing member(s) by the compiler. This means if I re-factor code and remove methods from an interface, the compiler notifies me on the next build that I now have dead code in implementing members.
By designing concrete classes with explicit interfaces, the public API for those classes are kept tidy, and it helps enforce usage through the contract interfaces. I hate seeing code "twisted" by having concrete references constructed and passed around. It defeats the point of defining an interface in the first place.
Implement Interface Explicity
The second option for implementing interfaces implements the interface member effectively as a guarded public method, accessible solely through an interface reference instead of a public member. I don't think I've come across a development team that has used this option, but after experimenting with it, I think it's something that should be adopted a bit more by default.
My goal when writing code is to define contracts for the interaction between distinct units of work. This gives me the flexibility to swap out classes easily and quickly. This means I'm predominantly working through interfaces. The most significant benefit of implementing interfaces explicitly is that it means any change to the interface is immediately picked up in the implementing member(s) by the compiler. This means if I re-factor code and remove methods from an interface, the compiler notifies me on the next build that I now have dead code in implementing members.
By designing concrete classes with explicit interfaces, the public API for those classes are kept tidy, and it helps enforce usage through the contract interfaces. I hate seeing code "twisted" by having concrete references constructed and passed around. It defeats the point of defining an interface in the first place.
Friday, March 12, 2010
Regex is dead! Long live the Lambda!
Lambda expressions are cool. Linq is cool. But Lambdas and Linq can be a quazi-pain-in-the-ass when it comes to inspecting and debugging the process flow of your application.
Remember regular expressions?
"Some people, when confronted with a problem, think 'I know, I'll use regular expressions.' Now they have two problems."
A famous quote from Jamie Zawinski. Regexes are cool. You can do a lot with them, but the trouble is that you can do a lot that you really, really shouldn't with them.
Linq and Lamba's fall exactly into this category and I fear in a few years of misuse and abuse, people will be shivering at the sight of Lambda the same as they slink away from regular expressions. I've already seen several examples of Lambda expressions that make my skin crawl.
One common Lambda misuse is the same damn misuse you see with looping structures. "x", cursed "x". And when "x" is used up, you start to see "y", "t", "i", and other letters popping around inside complex nested lambda expressions. I even started doing it myself and then I decided that I deserved a punch to the head if I continued this nonsense.
"x" kills readability.
Lambda is also tough to debug, and unfortunately even with VS2010 the debug windows (such as Watch) still cannot compile them, and they probably never will. The issue with Lambda as I see it is that it is a very powerful and useful tool, but just as prone to misuse as regular expressions. A word of prophetic warning to developers out there: "Just because you can do something with Lambda, doesn't mean you should do it with Lambda."
"I've got string input... After it's been massaged, squeezed, filtered, and transformed by my uber Regular Expression I... I'm not getting the result I'm expecting... WTF?"
"I've got data... After it's been massaged, squeezed, filtered, and transformed by my uber Lambda expressions, I... I'm not getting back the results I'm expecting... WTF?"
I can't tell the difference, can you?
Let me be the first to coin the phrase:
"Some people, when confronted with a problem, think 'I know, I'll use Lambda expressions and LINQ.' Now they have two problems."
Remember regular expressions?
"Some people, when confronted with a problem, think 'I know, I'll use regular expressions.' Now they have two problems."
A famous quote from Jamie Zawinski. Regexes are cool. You can do a lot with them, but the trouble is that you can do a lot that you really, really shouldn't with them.
Linq and Lamba's fall exactly into this category and I fear in a few years of misuse and abuse, people will be shivering at the sight of Lambda the same as they slink away from regular expressions. I've already seen several examples of Lambda expressions that make my skin crawl.
One common Lambda misuse is the same damn misuse you see with looping structures. "x", cursed "x". And when "x" is used up, you start to see "y", "t", "i", and other letters popping around inside complex nested lambda expressions. I even started doing it myself and then I decided that I deserved a punch to the head if I continued this nonsense.
"x" kills readability.
Lambda is also tough to debug, and unfortunately even with VS2010 the debug windows (such as Watch) still cannot compile them, and they probably never will. The issue with Lambda as I see it is that it is a very powerful and useful tool, but just as prone to misuse as regular expressions. A word of prophetic warning to developers out there: "Just because you can do something with Lambda, doesn't mean you should do it with Lambda."
"I've got string input... After it's been massaged, squeezed, filtered, and transformed by my uber Regular Expression I... I'm not getting the result I'm expecting... WTF?"
"I've got data... After it's been massaged, squeezed, filtered, and transformed by my uber Lambda expressions, I... I'm not getting back the results I'm expecting... WTF?"
I can't tell the difference, can you?
Let me be the first to coin the phrase:
"Some people, when confronted with a problem, think 'I know, I'll use Lambda expressions and LINQ.' Now they have two problems."
Subscribe to:
Posts (Atom)