In this short series I want to examine exactly what I need out of a DAL and how I plan to approach solving this problem in a somewhat different way that Steve, Mark or Doug have taken . . .
Speaking in Tongues
What do I need my DAL to do? Well, firstly I need to abstract SQL specific differences between MySQL and MSSQL, so I need it to take declaritive query requirements that can be expressed cross platform and to transform those into SQL specific dialiects if necessary/appropriate.
Secondly, the most common requirement is to get associated has-one or has-many relationships. If a category has-many products, I want to have something like a CategoryService.getAssociatedProducts(CategoryID) for getting all of the products associated to that category.
I also need the ability for deleting composed objects and object specific joins. If I delete product 3, I also want to delete all of the ProducttoCategory joins for product 3. If I delete a user and addresses are composed as opposed to associated in my object model for a given project, I want to delete their addresses (but not associated objects like the company that they work for which could be associated to multiple users).
I also need my DAL to manage joins, so if I save a product with a ProductCategoryIDList of 2,4,5,6, I probably need to write four records to a ProducttoCategory table as well as saving the ID list (for easier editing) in the Product table. I also need a strategy for rich joining tables - such as a ProducttoAttributeOptions table which not only allows you to associate the colors red, green and blue to a given product, but also to change the weight, price or SKU based on the option picked by an end user.
I'm also going to be adding some "magic fields" and naming conventions for my own specific use case to keep track of last updated, created date, who edited the record, who owns it and also a bunch of versioning and revisioning features that I happen to need and will wrap either in or (more likely) just above my DAL.
I considered seriously implementing mapping of property names to database column names, but decided that my use case is for green field apps, so while it is fairly easy to implement, it is a layer of complexity I don't need.
Everything I Need - and Nothing More
True ORMs take on the extremely difficult job of automatically mapping between the object and the relational paradigms. With ORMs you can save a fully loaded object and it'll do all of the necessary inserts and updates to all of the affected tables. However (and maybe it is just me), I really haven't come across a use case where I needed the full power of an ORM.
There are three times I feel like an ORM would be useful. When I want to get an object loaded with some or all of its associated objects (I want to display a category and want its associated products and subcategories), when I want to auto populate joining tables (when I save a product and want to save the ProducttoCategory joins), and when I want to automate the clean up of deleting join records and composed objects on deleting a specific object. Other than those three requirements (and the ability to speak different dialects of SQL), I don't find myself needing a full blown ORM. For instance, while I may need to get the addresses associated to a user, when it is time to save them - I can just save them. I don't need to save them as part of the user - I can just insert or update them based on their unique identifier. Loading them into a User object and then User.save()ing is more work than I need to do to just to ensure that AddressUserID is set correctly.
Any thoughts? Are there any other things you find that your ORM does for you that you couldn't easily achieve in another way? (I'm talking about support for query types - caching and other such features are a discussion/addition for another day).