In this article, I will try to educate you about the various important features that a good O/R mapping tool would provide you and how it can be beneficial to you. I am not discussing any particular O/R mapping tool but rather all tools in general. |
Publication Date: 11/3/2005 3:20:19 PM
By Iqbal Khan
O/R mapping tools are becoming more popular each day and people are realizing the productivity gain they provide to developers. Yet, many people don’t know enough about O/R mapping to consider using these tools and many others are weary of using any code generators (including O/R mapping tools).
/* Note one-to-many self relationship thru “reports_to” */ CREATE TABLE t_employees ( // Domain object class for t_employees table public class Employee String Name { // Persistence object for Employee class public interface IEmployeeFactory // Query methods // Relationship methods Below is an example of how a client application will use this code: public class NorthwindApp // Let's load a employee from Northwind database. // empList is a collection of Employee objects // subList is a collection of Employee's subordinates objects
Feature 1: Flexible object mapping Everything in O/R mapping starts with mapping your objects to your relational tables. Here are some specific features in this area that you should know: 1. Tables & views mapping: The tool should let you map objects to both tables and views in 2. Multi-table mapping: The tool should let you map an object not only to a single table but 3. Naming convention: The tool should let you use a different naming convention in objects and their attributes than in relational databases. If your database table is named t_employees, your object may need to be named Employee. 4. Attribute mapping: There are a number of features that the tool should support: a. Primary key: Your object must distinguish the primary key from other columns. It should also let you use a single-column or multi-column primary key. b. Auto generated columns: Some columns are auto generated (IDENTITY or SEQUENCE) and your object must have code to handle fetching the generated values after an insert. c. Read-only columns: Some columns are not meant to be set by the client but instead their values are system generated (e.g. creation_dtime column using getDate() function in SQL Server). Your object must have appropriate code to fetch these system-generated values. d. Required columns: Your object must do data validation for required columns at the time of insert or update operations. This is much more efficient than wasting a trip to the database just to get an error message back. e. Validation: In most cases, you have defined various constraints on your database columns. It would be nice to have the same validations done in your persistence objects so you can save an unnecessary trip to the database just to receive an error message. f. Formula Fields: There are many situations where when you fetch data from the database, you use a regular expression rather than a column (e.g. Annual Salary object attribute might be a formula field monthly_salary * 12). g. Data type mapping: Sometime, you want to map one data type from the database to another data type in your object. For example, a datetime type might be converted into a string. Your object must have the logic to do this automatically in both directions (read and write). Feature 2: Use your existing domain objects As you saw, a popular design pattern separates persistence objects into “domain” and “factory” objects. One important O/R mapping feature is to let you decide whether you want to generate both domain and factory objects or use your existing domain objects and only generate factory objects that know about your domain objects. Some people do not want to generate “domain” objects and instead develop them by hand and only generate the “factory” objects. The reason behind this is that their domain objects are being used in almost all subsystems of their application and therefore they don’t want them changing frequently through subsequent code regenerations. But, they don’t mind generating the “factory” objects since their use is localized to a few places (for load and save operations). Therefore, the O/R mapping tool should let you use your existing domain objects and map and generate only the factory objects. It should use .NET Reflection to read your domain object definition and after you have done the mapping, it should generate the factory objects in such a way that these factory objects use your domain objects to hold all the data. Feature 3: Transactional operations (CRUD) A database transaction allows you to group multiple operations as one atomic operation so either all operations succeed or none of them succeed. Transactional operations include create, read, update, and delete (also called insert, update, load, and delete). Each transaction operation is performed only on one row of data in a table. You’ll be working in one of two main transactional environments and your O/R mapping tools needs to know both of them so it can generate code accordingly. They options are: 1. COM+/MTS: Microsoft Transaction Server (MTS) manages all transactions of an application. Your objects do not start, commit, or rollback a transaction. They only return success or failure from their methods and MTS figures out when to do “BeginTrans”, “Commit”, or “Rollback”. Additionally, all your factory objects are stateless so MTS can do object pooling on them. This is a specific design pattern that your O/R mapping tool must understand and generate your persistence objects to comply with it. Most common applications for this environment are Asp.Net applications and .NET Web Services. 2. Stand-alone: This is the environment where your application manages all the transactions itself. It needs to know where to go “BeginTrans”, “Commit”, and “Rollback”. And, your O/R mapping tool needs to be aware of this environment and generate code to comply with it. Most common situations for this are Windows Forms based client/server applications that directly talk to the database server. Feature 4: Relationships and life cycle management The foundation of a relational database is that tables have relationships with other tables. Similarly, when you map objects to these tables, your objects also need to establish the same relationships with other mapped objects. Therefore, your O/R mapping tool must support this very important feature by letting you determine which relationships you want to keep in your objects. Below are the different types of relationships you must have: 1. One-to-one relationship: In this, your object must contain a reference to exactly one other object and must handle load and save scenarios for it. 2. Many-to-one relationship: This is very similar to one-to-one where your object must contain a reference to exactly one other object and must handle load and save scenarios. 3. One-to-many relationship: In this, your object must contain a collection of the related objects and must handle loading them with load and also adding and removing them with save operations. 4. Many-to-many relationship: This is the most complex relationship and involves a bridge table in the database to establish the relationship. There are two different situations for the bridge table as described below: a. Bridge table with only primary key: In this situation, the bridge table contains only the primary key (which is actually composed of multiple foreign keys). So, your object need not have any bridge table attributes and only needs to keep a collection of the related objects (similar to one-to-many). In fact, the public interface of your object is usually identical to one-to-many but the underlying code is different because of the bridge table. b. Bridge table with additional columns: This is the most complex situation because the bridge table has additional useful columns that your object must cater for. Your object needs to load a collection of composite objects containing both the bridge table and the related table information. Feature 5: Object inheritance As you already know, a very important aspect of object-oriented programming is inheritance. However, relational databases do not automatically provide inheritance in the relational model. But, there are a number of patterns on how to map object inheritance to a relational database. And, a good O/R mapping tool must provide this capability. 1. One table per object: This is the most popular and flexible pattern. In this, each object is mapped to its own table in the database. And, there is a one-to-one relationship between every base object and its derived object. The foreign key of this relationship is kept in the derived object. It is the most flexible because without changing the structure of any existing tables, we can keep adding to the inheritance hierarchy. However, it is not the most efficient for loading both base and derived objects because a separate “load” is done for each object. 2. One table for all objects: In this pattern, the base object and all the derived objects are represented in one table in the database. This table contains columns representing attributes from all the objects. It is the most efficient for loading and saving data but is very limited because adding a new object to the inheritance requires changing the structure of an existing table in the database that is highly undesirable. a. Insert and update operations: The derived class must first ask the base class to do Insert or Update and then do its own. But both the base and derived class operations must be performed in one transaction. b. Delete operation: Unlike the insert and update operations, the delete operation is performed first on the derived object and then on the base object. However, both must be done in one transaction. c. Load operation: The load operation in the derived class must also call load on the base class and both of these should be done in one transaction. Feature 6: Static and dynamic queries The next most common thing that a database application does is to retrieve rows of data from one or more tables. The application does this done by using SQL queries (SELECT statements). However, an object-oriented application wants to fetch a collection of objects and not rows. So, the O/R mapping tool must provide a way for you to create queries that return collections of objects. Feature 7: Stored procedure calls Stored procedures have become very popular in high transaction environments because they allow you to put all your SQL inside the DBMS and in a compiled form. As a result, your SQL does not have to be compiled at runtime because that is a very expensive process. There are two situations that an O/R mapping tool must support when it comes to stored procedures as described below: 1. Existing Stored Procedures: First is when you already have custom stored procedures in the DBMS and you want to have your persistence objects call them. In this situation, the O/R mapping tool must allow you to define methods in your objects that can call stored procedures. It must also support different parameter types (in, out, in/out) and also whether the stored procedure returns a Recordset or not. If the stored procedure returns a Recordset then the object must return this data to its client. 2. Generate Stored Procedures: The second situation is where all the SQL (minus the dynamic queries) that is going to be generated as a result of your object-relational mapping is put inside the DBMS as stored procedures and your objects code is generated so it calls these stored procedures. If you didn’t generate stored procedures for all the SQL, it would be put inside your object source code as “dynamic SQL”. Feature 8: Object caching If your application is transaction intensive and supports high traffic, you really cannot live without effective caching built into your application. Microsoft provides Asp.Net Cache object but it is not sufficient for clustered environments where your application is running on multiple servers and needs a cache that is also clustered. However, there are commercial caching solutions available in .NET that cater for clustered environments. Whichever caching product you use, you’ll have to make sure that your persistence objects are making caching calls from appropriate locations. And, your O/R mapping tool should provide the ability to generate code that makes caching calls to one or more leading products. Feature 9: Customization of generated code You’ll always have situations where you need to customize generated code. However, if you change the generated code, it will most likely get overwritten the next time you generate code again. And, since software development is an iterative process, you’ll have to generate code many times. To prevent your code from being overwritten, the O/R mapping tool must allow you to mark your code as “Safe Code” which then does not get overwritten in future code regenerations. And, to ensure that your custom code gets called seamlessly, the O/R mapping tool needs to either support the concept of “Hooks” which are calls made from strategic places in the generated code and the result code returned by these “Hooks” determines what happens next. Or, the O/R mapping tool needs to let you derive the generated code and then use polymorphism to actually run your code instead of the generated code. You can then determine whether to call the “base class code” or not. Feature 10: Template customization A good O/R mapping tool is very likely using code templates to determine how to generate the code. The O/R mapping tool combines the templates it has with a combination of your object mapping input and the database schema information to determine exactly how to generate the code.
Conclusion You should seriously consider using an O/R mapping tool as it will save you a lot of development and testing time. And, when it comes to evaluating which tool is best for you, you should know what to look for. I hope this article helps you gain a better understanding of O/R mapping.
Author: Iqbal M. Khan works for Alachisoft, a leading software company providing O/R Mapping and Clustered Object Caching solutions for .NET. You can reach him at iqbal@alachisoft.com or visit Alachisoft at www.alachisoft.com
|