Previously, on In-Memory Database, I talked about building something new that is derived from the SharpHSQL and SQLite code bases.  I have made some progress.

Architecture

I have made a couple of architectural decisions:

First, The database must support a base plus difference structure.  What I mean by is is that I want to be able to create a base in-memory database with tables and views, wired together with referential integrity links, and populated with data.  Once created, this base should be immutable.  One best practice for unit testing is that each unit test reveals/confirms the quality of a single feature.  A unit test with 20 Assert statements cries out for refactoring.  To avoid this messiness, what I have typically done is to create a helper function that sets up the initial environment of the test.  Each of the tests invokes this helper function and then applies a single test assertion against this environment.  A variation on this is to use the helper function to do a partial setup, followed by the completion of the setup and the assertion in the test method.  When the setup is simple and quick, this approach is quite acceptable. 

Setting up an in-memory database to support a set of unit tests that need database support is probably not simple or quick.  If we start out with a "blank slate", we would have to create the schema and then populate the data.  Even doing this "in memory" will take some time.  I know that you should wait until a performance problem rears its ugly head before thinking about optimization, but I do not always do what I know that I should do.  As a minimum, I would want to setup the database once for each test run, store it away in a shared/static variable, and re-use it from there.  Another approach would be to re-build the database as a file during each build process from the schema scripts.  A further step in the build process might be to populate the non-volatile data (such as lookup tables).  The tests could load the database into memory and read into the shared/static variable.

Each unit test would have to have its own overlay of this base data where all of the changes triggered by the unit test would occur.  This overlay layer would implement the same interface as the base layer.  The overlay layer would have to be able to store changes and integrate these changes with the data in the base layer.  The unit test would apply its assertions against the overlay layer.  Once the test was complete, the overlay layer would be discarded, leaving the base layer untouched to support the other tests.  I have not worked out the full details of this but it is most important that all of this worked with multiple tests running, each on its own thread.

Second, it is important to make this whole package extensible.  I have made a decision that the first cut of the package will focus on SQL Server 2005.  I have also decided that I am going to focus on the CRUD operations.  That means that there will be some functions that, at least for now, will not be supported.  I do not want to restrict the in-memory database forever.  That means that I am going to have to give some thought to how I might be able to extend the functionality to meet whatever needs come up.  This is clearly going to affect how parsing is done and how commands are executed.

Coding

I have re-built the parsing logic.  I have built a separate set of classes to handle the tokenization of the input stream.  These classes are specific to the T-SQL dialect and recognize all of the peculiarities of the SQL language.  These classes produce a list of tokens.  Each token knows what it is (that is, reserved keyword, punctuation, text, number, and so on).  I have also built a separate set of parsing classes that slice and dice the tokens to recognize commands.  That is, there is a separate class to handle each of the commands.  I thought about going out to get some generalized parsing mechanism but there seemed to be a couple of issues that worked against doing that.  First, my experience is that SQL is not a neat language when it comes to parsing; that would suggest that I would have do a lot of "work around" effort to get the generalized parser to work on SQL.  Second, I wanted the parsing mechanism to be extensible; I wanted to be able to plug new things into the package as the needs came up.   Learning the details of the parsing process seems to work against that goal.  With all of this in mind, I went with a home-grown solution.  I have tried to separate the parsing mechanism from the rest of the logic to allow for a different parsing solution to be plugged in.  I will blog on what I did in a later post.

I am just starting the specifying the syntax of each command.   I have taken the approach of creating separate parsing classes for each command.  I go down the list of tokens.  When I find a token that corresponds to a reserved command, I instantiate an the corresponding command parsing class.  That class sucks in as much of the token string as it needs, typically leaving the token list positioned to the next command token. When the parsing class is done, it can produce an instance of the command class that will do the real work.  I thought briefly of combining the parsing and action parts of the command in the same class.  I am not doing that for a couple of reasons.  First, I may want to handle different dialects of SQL in the future.  Second, I want to be able to construct the action parts of the commands without ever parsing a single character.  Third, it just seemed wrong to do that.

Observations

This exercise has been interesting on a number of different levels.  The design aspects are obvious.  What has been more interesting, at least to date, is having to learn more about SQL syntax.  I have already learned two or three things that I did not know about. 

In this post I am going to refer to the code and Power Point slides that I used at the Code Camp.  You do not need to have these materials but it might help.

Just to refresh our memories, an ORM maps data between the application domain model objects and the relational database schema.  The domain model objects and the database schema are (or should be) pretty commonplace concepts.  The only wildcard is how to specify the mapping.  This post will cover the several mechanisms that NHibernate provides to specify the mapping. 

(As an aside, the word mechanism strikes me as odd.  It derives from physical machines, but I end up using it quite often to describe digital things such as software programs.  May be we need a word like "digitalism".  Hmmm.) 

I am going to present the different digitalisms in order from least flexible to most flexible.  In each approach, we need to define two basic elements for each of the domain model objects:  First, we need to specify the base table that holds the data elements.  This is the table against which the CRUD (Create, Read, Update, and Delete) operations are performed.  Other tables may be involved, but most of the data elements within the typical domain object are associated with a single (base) table.  I will call this the Table mapping.  Second, we need to specify, for each data element, the mapping between the data element and a corresponding data column (or set of columns or even a part of a column).  I will call this the Column mapping.

Embedded in the Code: NHibernate provides a means to define the Table and Column mappings as annotations.  The table annotation appears as an annotation of the Class declaration:

   1: [Class](Table:="ProgrammingLanguages", Lazy:=False)> _

   2: Public Class ProgrammingLanguage

Each data element may also be annotated.  Here, there is a choice.  The Big Book of Best Practices says that [cue the drum roll, cue the announcer with the basso profundo voice] you should code the data element as a private (or perhaps protected) field, wrapped in get and set property methods.  I rarely do this any other way, but it can cause problems.  I like the objects to be as immutable as possible; the fewer points in the code that can change something, the better.  What this often means is that there are quite a few properties with no setter property methods.  That poses a challenge to the ORM mechanism: how does it get the data into the object when it creates it.  While the class might define a series of factory methods and constructors that provide the mechanisms (digitalisms) to set the values, it is almost certain that these mechanisms are too specific for the ORM to use.  Accordingly, most ORM packages use reflection to gain access to the private fields.  [And, yes it is slower than straight access but the slowdown from reflection is so much less than the time it takes to move the data between the application and the database; it is not going to be a problem.]  If the mapped class does not have setter methods on some of the data elements, you can apply the annotations to the private fields.  NHibernate will find and set the values.  Otherwise, you can apply the annotations on the public property.  Actually, I believe that NHibernate requires the annotations for a class to be all one or the other.  Thus, if you have immutable properties, you must apply the annotations to the fields rather than to the properties.

   1: <[Property](Name:="mLanguageTitle", Column:="Title", Length:=30, NotNull:=True, Access:="field")> _

   2:  Private mLanguageTitle As String

As an observation, while this is the easiest way to define the mapping, this is the least flexible approach.  The annotations are in the code and to make any changes to the mapping values you would have to change the code and re-compile.  What you have to do here is to decide how likely it is for the mapping to change and what the consequences of that change might be.  In my sample application, I included a table of programming languages with a character-based identifier and a title.  This is dead simple and is somewhat unlikely to change.  But it could change: there I would be with the hammer and chisel making my changes.  If the organization is properly paranoid, it might insist that any code change must trigger a full Q/A cycle.  My recommendation is to use the annotations only during the initial development (during the time that the domain model is evolving with some vigor) and transition to one of the next techniques before the first truly serious deployment.

Specification in XML Files.  All of the remaining approaches specify the mapping in XML files.  NHibernate provides two different sets of files.  The first file is the configuration file that defines the connection to the database and identifies the mappings for the individual class/table associations.  This configuration data can be in the standard application configuration or in a separate configuration file that the standard configuration file identifies.  Here is an example of the separate configuration file:

   1: <?xml version="1.0" encoding="utf-8" ?>

   2: <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

   3:    <session-factory>

   4:       <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>

   5:       <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

   6:       <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>

   7:       <property name="connection.connection_string">Server=.;Database=OrmTest;Integrated Security=true</property>

   8:       <property name="show_sql">true</property>

   9:       <mapping assembly="NXCSLAEmployeeAssignmentsLib" />

  10:       <mapping file="ProjectResourceNeed.hbm.xml" />

  11:       <mapping resource ="ManageAssignments.Task.hbm.xml" assembly="ManageAssignments" />

  12:    </session-factory>

  13: </hibernate-configuration>

Lines 4 through 8 in this file tell NHibernate that the database is a MS SQL 2005 database named ORMTest on an instance of SQL Server that is running on my local laptop.

The NHibernate specifications for each class are stored in separate XML files.  Here is a somewhat complicated example from the sample application:

   1: <?xml version="1.0" encoding="utf-8" ?>

   2: <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"

   3:     assembly="NXCSLAEmployeeAssignmentsLib"

   4:     namespace="NXCSLAEmployeeAssignmentsLib">

   5:    <class name="ProjectResourceNeed" lazy="false" mutable="false" table="Tasks">

   6:       <id 

   7:          name="mResourceNeedID" 

   8:          column="TaskID" 

   9:          access="field">

  10:          <generator class="assigned" />

  11:       </id>

  12:      <property 

  13:         name="mTaskTitle"

  14:         access="field"

  15:         column="Title" 

  16:       />

  17:       <property 

  18:          name="mResourceStartOnDate"

  19:          type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate"

  20:          access="field"

  21:          column="StartDate"

  22:       />

  23:       <property 

  24:          name="mResourceEndOnDate"

  25:          type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate"

  26:          access="field"

  27:          column="EndDate"

  28:       />

  29:       <property name="mPercentCovered"

  30:                 column="PercentCovered"

  31:                 access="field"

  32:       />

  33:       <property name="mProjectTitle"

  34:                 type="String"

  35:                 access="field"

  36:                 formula="( SELECT prj.Title 

  37:                            FROM Projects prj  

  38:                            WHERE prj.ProjectID = ProjectID)"

  39:       />

  40:       <property name="mContact"

  41:                 type="String"

  42:                 access="field"

  43:                 formula="( SELECT prj.Contact 

  44:                            FROM Projects prj  

  45:                            WHERE prj.ProjectID = ProjectID)"

  46:       />

  47:       <property name="mRequiredCertification"

  48:                 type="String"

  49:                 access="field"

  50:                 formula="( SELECT CASE WHEN CertificationRequired = 1 THEN cert.Title ELSE 'None' END

  51:                            FROM TaskCertifications taskCert

  52:                            LEFT OUTER JOIN Certifications Cert

  53:                                ON taskCert.CertificationID = Cert.CertificationID

  54:                            WHERE taskCert.IsPrimary = 1 AND taskCert.TaskID = TaskID )" />

  55:        <property name="mResourceLanguage"

  56:                 type="String"

  57:                 access="field"

  58:                 formula="( SELECT Lang.Title

  59:                            FROM ProgrammingLanguages Lang

  60:                            WHERE Lang.LanguageID = ProgrammingLanguageID )" />

  61:    </class>

  62: </hibernate-mapping> 

In essence, this file provides a mapping specification for each data element in ProcessResourceNeed class.  The data for this class comes from several tables in the database. I will not go though this file point by point.  Some of the complexity in the joined columns will go away with the next version of NHibernate which supports a much simpler mechanism for specifying joins.

The final question is where to put these class/table mapping XML files.  Here are the options:

Embedded in the Same Assembly.  You can define the mapping file as an embedded resource in the same assembly as the mapped class.  This is slightly better than using annotations in that it isolates the mapping specification such that it could be moved at a later time.  But the "change the code; trigger Q/A" problem is still there.  If you use annotations or embed the mapping files in the same assembly, you must tell NHibernate where they can be found.  Line 8 in the configuration file tells NHibernate to look in the assembly for annotations and embedded resources.

Embedded in a Different Assembly.  You can define the mapping file as an embedded resource in a different assembly from the assembly that holds the mapped class.  This gives you better flexibility in that it is possible to swap out the other assembly without code changes.  A change to the configuration file changes everything.  You could have several different mapping assemblies, each of which has gone through the Q/A cycle.  If there are compliance/accountability issues, this seems like a good choice.  It does limit the number of possible choices to a well-defined set of well-tested options.  Line 8 with a different target assembly could be used here.

A variation on this approach is to define all of the possible options in a single assembly and point out the specific mapping (embedded resource) file in the configuration.  Line 11 in the configuration file is an example of doing this.  This provides a bit more control albeit at the additional (very minor) cost of calling out each mapping individually. 

A Naked Mapping File.  Finally, it is possible to specify the mapping is a straight (non-embedded resource) XML file.  This provides the maximum flexibility, even if it gives the willies to the Q/A folks.  For example, one might write some precursor logic that determines the environment of the application at start up (that is, disconnected, minimally connected [think, dial-up], full connected but from outside of the firewall, and safely within the firewall) and modifies the mapping files accordingly.  [And, yes, the Q/A folks are right to have the willies.]  In any case, you have the power to make any changes that you want without any code changes.

A Final Note.  While it is nice to have choices, remember that just because you can do it, does not mean that you should do it.

1. Life is what happens as we are planning other things.
2. If You Want to Make God Laugh, Tell Him Your Plans.

All of these solution approaches involve mapping between the application view and the database view. The problem with each of these approaches is that the mapping is defined within the application or the database. What we want is a solution that is outside of the application and outside of the database. We want the application to expose the meta-data about its structure and for the database to do the same thing. With modern languages and databases, this is a very easy thing to do. With all of this meta-data in place, we can create a mechanism to define and implement the mapping.

Here are some requirements for such a mapping mechanism:

First, it should be declarative in format. Creating and changing the definition of the mapping should not require programming skills or a deep knowledge of the database.

Second, it should be independent of the application implementation. For example, in the DotNet space, an ORM mechanism should work equally well for any language that works on the DotNet platform. As a part of this requirement, the domain model should have to contain minimal logic to invoke the mapping mechanism. I am going to address this issue in more detail later.

Third, it should be independent of the database management system. For example, the mapping should work equally well for any major database (with the option to add new database versions or vendors). Again, as part of this requirement, the database should be required to make minimal adjustments to work with the mapping mechanism.

Fourth, it should be able to optimize the interaction between the domain model and the database. In practical terms, because the database is inherently more costly, this optimization applies mostly to the database access. There are two sub points to consider here. A) The mapping mechanism must be able to create "very good" SQL to achieve what the declarative mapping has specified for the individual operations. I think that it also must be able to expose this SQL to the inspection of the DBA team to build their confidence that the optimization is competent. B) The mapping mechanism must be able to defer the execution of the individual operations and "batch" the operations to reduce the number of interactions across the network. This caching approach creates a secondary requirement to keep track of the identity of each object in the cache. If the domain model asks for a persistent object that is on the database and in the application cache, the domain model should get the object in the cache rather than a new copy from the database.

Fifth, it should support testing. Of specific concern here is that I want to be able to test the domain logic (including the logic that invokes the persistence mechanisms) without involving a database. The interaction with the database is a slow process (particularly when the initial setup and restore after any persisted changes is factored in). I want to be able to substitute a difference data store (such as an in-memory database) that avoids the long times associated with testing using a disk-based database.

I have narrowed my search for an  in-memory database to support unit testing down to SQLite (here and here) and SharpHSQL (here and here).  Both are open-source implementations of in-memory SQL databases.  SQLite is written in mostly C# (there is a smattering of straight C code) and SharpHSQL is written in 100% C#.  Both have some tweaks to handle the Pocket PC environment.  Both have the ability to save the contents of the in-memory database to disk and retrieve it later.  To my surprise, neither has a set of unit tests. 

As far as I can make out, both are aimed at the small embedded application space.  I have convinced myself that my needs are sufficiently different that I should create something new.  Both of the packages have a lot of what I want, so I am going to start with their code as a base.  I anticipate that I am going to make a lot of changes (e.g., add unit tests for one).   I am going to start with the SharpHSQL base because it seems less cluttered than SQLite.  I thought for about 30 seconds of converting to VB.NET but I have not done any serious coding in C# for a while and this seems to be a good time to practice. 

Here are some preliminary thoughts on the target architecture:

• I, of course, want to be able to handle the commands that come in as strings but I also want to be able to build what I am calling "command blocks" that have a true object structure.  The command blocks provide support for populating the schema and the database from an existing database and for the database setup/teardown for individual tests.  That means that I will have to decouple the string parsing at least to the extent that the command execution will accept strings (to be parsed to create internal command blocks) and command blocks (to be executed).
• I want to create someway to build up a database that I can re-use for a set of related tests.  That is, create the base, clone it for each test, run the test against the clone, examine it for the assertions, and discard the clone.
• I will need to decouple a lot of the classes so that I can do an adequate job of unit testing.  There is not much point in building a package to support unit testing that is not unit tested.

A key message in the previous ORM posts is that application point of view and the database point of view are both valid.  What we have to do is to create an environment in which the application domain modelers to do their work at the same time that the database administrator do their work without causing or even tolerating warfare. There is a "term of art" in the software world called "separation of concerns". What this means is for the topic at hand is that, to the greatest extent possible, we must decouple the application point of view from the database point of view. Clearly, these two worlds must interact: the data for domain objects must "live" in both worlds and pass across the line dividing these two worlds. As a minimum we want the isolate the part of the application that deals with the database so that it has a very small footprint. When a change in the database occurs, we want to limit the change in the application to this small footprint. Likewise, we want to isolate the part of the database world that has to react to the changes in the structure of the domain model to a similarly small footprint. (And a zero-sized footprint in both cases would be very nice.)  The question is what mechanisms we can use to make this happen.

One way to do this is to create a "data access layer" (DAL) that presents an abstraction of the data to the application. The application says, get me an object representing a "certified software developer" and the DAL executes calls to the database to make this happen.  This is logic that is "outside" of the domain model portions of the application. Whenever the domain model logic wants to talk to the database, it speaks through the DAL. That isolates the domain model logic, which is good, but it has a number of problems associated with it. First, the DAL is code. Even if the database administrator is a crack programmer in the language used to implement the DAL, there are deployment issues that create inertia which in turns creates friction which slows down the process. Chances are the database administrator is not a crack programmer and even if she were a hotshot, the application developers would probably not happy to have her come mess with the application bits. Finally, there is the problem of transparency. There is nothing in the DAL that makes it discoverable. The DBA making the change would have to examine every DAL to see if the potential database change would affect that DAL, assuming that each of these DAL components were known. This is a solution with a lot of friction and that is not good.

Another way to do this is to create database views. We might start with a view of Employee. If the DBA moves a part of the data to a Person table, the DBA adjusts the "Employee" view to recreate the original Employee data using inner joins and what have you. For reading data, this works pretty well. It can in most cases be made to work for inserting or updating data, but it is typically complex.  Complexity leads to errors. Errors lead to testing to ensure the absence of these errors.  Testing leads to inertia and we end up with friction.  I think that this is a marginally better solution than the DAL approach, but not by very much. The main advantage is that there is better discoverability of the views in that they are centralized in the database. We still have the problem that the view is owned by one of the parties (the DBA) and the other party (the Application Developer) must come, hat in hand, to ask for the change to happen. This leads to more friction.

A lot of people are fond of using stored procedures to implement the abstraction between the application and the database. Since you can use multiple stored procedures to handle the individual CRUD (create, read, update, and delete) operations, this approach is more flexible. The downside for me is that when I want to limit the data that I retrieve (or perform other operations on), I have to pass the criteria values in as parameters.  This means that I have to have built-in parameters that anticipate all of the possible needs to filter the scope of the operations. If there are a small number of parameters, this is not so bad.  But if I want to use several criteria values in various combinations, it starts to get ugly.   Once you have written a stored procedure to retrieve data that must accept a dozen parameters, all of which are optional, in any combination, you quickly come to the realization that this is not such a good solution either.  You often end up constructing the SQL command through string concatenation, using a language that is not really suited to string manipulation.  Plus, this is a solution that is clearly under the control of the DBA.  And we have more friction.

I am afraid that I must end this post with the admonition: Move along!  These are not the solutions that you are looking for.

Next time: some requirements for what we are looking for.

As part of the podcast I did a few weeks ago (and that was released to the wild this last Monday), I talked about doing unit tests with an in-memory database.  I have a few cycles available and I thought that I would poke around and see what was available.  As I was poking, I was making some mental notes about what I wanted to do with this functionality.  Since my mind is not a reliable storage unit, I wanted to write them down.  So here I am.

Unit Testing

My interest is in unit testing of things related to the database.  The typical application that I work on contains the following elements in its stack:

• UI: User Interface and Presentation logic that invokes methods in the
• SL: Service layer that contains application-specific logic that, in turn, invokes methods in the
• DMO: Domain Model Objects containing the business logic that, in turn, invoke methods in the
• DML: Data Mapping Layer that mediates between the domain model objects and the
• DBMS: The Database which persists data and retrieves it upon request

If I am building things in the spirit of unit testing, I want to be able to run a series of unit tests against the service layer and the domain model objects with some form of mocked-up or stubbed data mapping layer or database.  Specifically, I want to get the maximum amount of testing intelligence from the software under test with the least amount of overhead.  Accessing the database, at least for most of the test scenarios, is unnecessary and adds a lot of execution overhead, both to execute the database commands and to perform the setup/teardown logic against the database to ensure a valid, isolated test.  If the interface between the DMO and the DML is fairly clean, I could create a mocks or test stubs and convince the DMO layer that it was talking to the actual database without any of the overhead of actually involving the network or the disk. 

There are a couple of problems with this approach.  First, depending upon the exact nature of the DML, I might have to write a fair amount of code to simulate the DML.  I am not opposed to writing code but I am not too excited about repeatedly re-writing the simulation to match an evolving database schema.  Second, if I want to define my tests in terms of the resulting state of the data on the database, I end up writing even more logic to simulate the database.  This introduces even more brittle code into the mix.  Third, if the DML is home-grown or a third-party library, I have no way to test that code without involving the disk-based database.

One of my goals is to be able to run my entire suite of tests after any significant change is made to the code base.  At worst, it would take no more than a handful of minutes to run the whole set.  Ideally, it would take no more time than it would take to execute a hydraulic exchange (that is, fluid out, and more fluid in) or a quick look at email/RSS aggregator.  That is, code a method/test, get the pair to work with each other, and then run the entire test suite to ensure that you did not cause problems elsewhere in the code base.  Repeat every 10 or 15 minutes.

A Technological Approach

It seems to me that an in-memory database provides a lot of "bang for the buck".   Since the data never leaves the memory space, it should take much less time than going to a disk-based database.  Since it is a more-or-less full-up simulation of its disk-based cousin, it should require much less effort to create an experience that replicates what the disk-based database provides.  Finally, it gives me an option to extend "rapid-fire" testing closer to an end-to-end exercise of the entire software stack.  I probably would not use it for everything, but the faster that it ran, the more likely I would be use it.

Requirements and Other Dreams

Here are some of the features that I think that I need and some features that I do not need:

• I need a complete simulation of all of the functional behavior of the disk-based database for all basic SQL processing. This includes:
• Column behaviors including errors if I try to insert a null into a non-null column, or a value outside of a check range.
• Unique primary keys.
• Identity keys.
• Default values.
• Transactions.
• Foreign key referential integrity.
• All of the common join processing.
• Aggregate functions.
• Supporting pretty much anything else that an ORM or ORM-like DML would generate.
• I need a way to create the in-memory database schema from the actual schema.  I do not (repeat do not) want to have to maintain two versions of the database schema and keep them in sync with each other.  No, I do not want to do that at all! 
• I would like to have a way to load data into the in-memory database from the real database.  I am specifically thinking of various read-only lookup tables that are involved with referential integrity linkages.  I would be happy with a initial test setup that created a base in-memory database from which the databases for the individual tests were cloned and fleshed out.  If that setup pulled data once per test suite execution to create the base in-memory database, that would be acceptable.  Or perhaps we could create a base in-memory database, serialize it to disk, and retrieve it at the beginning of the test suite execution.
• I need a way to store test-specific data into the in-memory database.  We could make requests through the DML but I am thinking that the capability to run SQL scripts directly would be a cleaner choice.
• I need a way to retrieve post-test data to determine if the software under test did what I expected it to do.  Again, the ability to run SQL commands directly would seem a cleaner choice.
• I do not care about non-unique indices.
• I do not care about the durability of the data in the in-memory database.  I expect that we would create an instance just before each test and destroy/reset it right after the test.  Sorry, test data, but you are so expendable.
• I do not care very much about the ability to force non-functional error conditions such as timeouts or "disk full".  I would much prefer to intervene between the DMO and the DML; I think that it would be much simpler.

What Happens Next?

I have found two possible candidates: SQLite and SharpHSQL.  I am going to dig into each of them to see what there is to see. 

In my experience, almost all non-demo databases contain data that is associated with multiple domain models. It may be the case that the database starts out in a one-to-one relationship to a domain model (that is, the database was created originally to support the application). That will not last. If the data in that database is valuable, it spawns other domain models in pretty short order. Someone wants a report (or several reports). Someone else wants some analysis statistics. Another organization wants a feed from this database. We discover that someone else in the organization (or the world) is producing some of the data that we need (with better quality, lower cost and more of whatever else turns your crank) and we want to "buy" this data rather than "build" our own version. Another domain model builds on or integrates with the the primary domain model associated with the database.  Each of these is in some sense a domain model that wants to share the same set of persisted data, and each of these domain models will impose slightly different demands of the database.  In my view, this encroaching integration is inevitable and represents some of the more interesting problems in my professional life.

A relational database is a different sort of animal from a domain model. This is true even if there is only a single domain model using the database. Database designers and administrators talk of "normalization", "integrity", "reliability", "scaling", and "availability". They worry about preventing data corruption. They have to figure out how to achieve all this (and much more) while keeping all of the domain models that are accessing the data happy. There is a formulation of Murray's Law that says, "There are a million (or a billion) ways for things to go wrong; there are only a handful of ways for things to go right; you figure the odds!" Database administrators, of necessity, live on the edge of disaster.

The forces on the database are not just static. There are rhythms to the access profiles. Some are daily. Some are weekly. Some are monthly. Some are quarterly. Some are yearly. All sorts of external events can cause peaks of usage.  A database with a static configuration and indices cannot possibly satisfy all of the needs all of the time. Just as the domain modeler is always learning new facts, the database administrator is also always learning and trying to tweak the database setup to respond to foreseen and unforeseen changes in the data environment.  By my definition, a "tweak" does not change the semantic contents of the database; it is just a refactoring of the data.

Some of these tweaks are largely transparent to the application. Adding or subtracting a non-unique index changes performance but does not change the view that the application has. Some of these tweaks can, however, have an impact on the application code, especially if that code ("Oh, the shame of it!") embeds knowledge of the database layouts.  A table that is normalized must be partially de-normalized to achieve performance. A table that was de-normalized must be normalized to address an update anomaly. Another table has to be split to accommodate the needs of a newly arrived domain model. Columns must be added to existing tables. The coding of an existing column must be changed to promote better interoperability. I still consider these changes as tweaks because, most of these changes do not affect the "logical" view of the data. By this, I mean that it is possible to satisfy the application point of view by changing the mapping.  Key point: Changing the mapping is very much different from changing the domain model.

For example, the database administrator might need to split the data about Person off from Employee to satisfy the needs of an application dealing with recruitment. That is, the recruitment process (domain model) needs to capture data about individuals that are not employees right now but may become employees in the future.  The recruitment process may even make the distinction between a potential applicant and someone who has actually filled out an employment application.  Even though the database layout of the data is different, this is still a "tweak" so long as we can still combine some of the contents of the Person table and the (New) Employee table to produce an equivalent image of the (Old) Employee table.   That is, the other domain models do not need to change, because all of the changes are in the mapping between the application domain models and the database.

For another, more extreme example, consider the case where the old database contains the most current data and the new database contains versioned data that includes the most current data as well as past versions of that data.  Clearly, the most current data is still in the database.  Clearly, it is still possible to define a mapping that produces an interface to the data that produces the same contents as the pre-versioned database.  Because we can satisfy the needs of the original data model with a new (albeit more complex) mapping, I would argue that this change is, at least from the perspective of the original domain mode, still "only" a tweak.  Only the mapping should have to change.

The question is how to define and implement the mapping mechanisms so that the domain models are not troubled by these "refactoring" changes.

Ideally, the application point of view is the point of view of the users who will use the application to advance the interests of the organization. While the application must be computationally competent (more on that in a minute), the structure of the application should be guided much more by what the organization needs than by what the technologists are comfortable with. There is a school of thought that suggests that one can define the application in a way that is completely independent of the technologies that might be used to realize the application vision. I have never been able to pull that off. If you can do it, go for it. Meanwhile, back in the real world, we will get on with the work at hand.

Let us spend some time on domain driven design. Domain driven design means that we ask the user community to describe the things (e.g., concepts, documents, physical objects, and actions) that the business deals with. There are whole books written on this topic and I will not try to duplicate the contents of those books. There are a few points that are pertinent to this post.

First, the objects (classes if you like) have behaviors that are derived from the needs of the organization. That is, they are not just clumps of data items. They validate the data from which they are originally created and they validate any changes to that data. They derive other data from the data fed into them. They are greater than the sum of their parts. This also means that they may be valid or invalid and that this state of validity may affect what can be done with the object.

Second, the objects may be associated with other objects: they might own other objects (e.g., an order object owns the order line objects within the order) or they might collaborate with other objects (e.g., they might call an object to determine credit-worthiness of the customer).

Third, we want to test the fidelity of how well the domain model that we have constructed matches up with the real world. Generally, we will want to feed (almost) real data into our model and see if the results match up with what we see in the real world. The common practice is to sit with a user representative and work through a series of tests to test this fidelity. The expectation of this approach is that the user and the application developer will both gain a better understanding of the domain model. Typically, this is an iterative process that produces many versions of the domain model that eventually track fairly closely with reality. It is also possible that this modeling exercise also can trigger changes in the business processes: it may be the case that the questions that the application developer asks will reveal confusion about what various terms mean, gaps in the business process, or inconsistencies in how different users think that the "real" world works. In practice, the domain model and reality converge over time.

When the domain model and reality converge and when the domain model is complete in the sense that the set of tests adequately cover the logic of the domain model, we can say that the domain model is computationally competent. It is unambiguous in the sense that given a specific set of inputs, the domain model yields the same defined set of results each time. It is complete in that every possible set of inputs is tied to some outputs. Nothing falls through the cracks and nothing causes surprises.

This is not to say that it is completely correct. Our user representatives could be wrong. Reality could be in the process of changing. Our coverage metrics might be inadequate. But we can say that the model is valid and useful. And we can hope that when we discover the discrepancy between the model and the then current reality, we can make the changes quickly and with a minimum of fuss.

Note that the domain model does not address a whole raft of things that are really important. The domain model may recognize that the data in the domain model will be persisted to some form of data store, but how that happens is a decision that can be deferred to a later time. The same is true for security (authentication, authorization, and data protection), logging, auditing, and many things related to the "ilities". The key concept here is we could make and re-make the decisions about these matters several times during the lifetime of the domain model and those decisions should not change the content of the domain model. The choice of a SQL Server version or Oracle or MySQL or <Insert your favorite database here> should not affect the contents of the domain model. It certainly may affect the performance or scalability or reliability or ease of development but those are factors that are, at best, secondary to the quality of the domain model. (Did I mention that we were taking things to the extreme?)

Let me re-enforce this point. The application point of view says that we want to develop and validate the domain model without having to consider any of these secondary concerns. Properly done, the domain model will outlast multiple versions of operating systems, database management systems, messaging systems, and what have you. If we have to consider these issues too early in the process, we cause an unnecessary friction in the development process.

This is the first of a series of blog posts about Object Relational Mapping (or ORM). It is an attempt to capture some of the notions in the presentation that I did at the latest Twins Cities Code Camp.

The "Gray Zone" is a reference to approach that rejects the notion that the world is black and white; that only one point of view matters (which not so coincidently is my point of view and if you are not with me, you must be against me) . As you can see I have been watching way too much of the current American political process. In the Gray Zone, all sorts of points of view can coexist. The two points of view that are of interest to us in this series of posts are those of the application developer and of the database administrator. We are going to take these points of view to an extreme to illustrate some points.

I am going to do this in a series of posts. It started out as a single post but got out of hand pretty quickly. The list of topics appears below. As I add the referenced entry in my blog I will update the reference to be a hyperlink.

• ORM: The Application Point of View
• ORM: The Database Point of View
• ORM: The Gray Zone Revisited
• ORM: Prelude to Greatness: the Requirements for ORM Mapping
• ORM with NHiberate
• ORM with Entity Framework
• And a Final Rant on ORM

As a result of my Spring 2008 Code Camp talk on ORM, I got a chance to sit down with Microsoft Evangelist, Jeff Brand, to chat about ORM.  You can listen at http://www.slickthought.net/post/New-Spaghetti-Code-Podcast-Available---Jon-Stonecash-on-ORM.aspx.  This is a audio-only podcast and as a consequence it tends to be a bit more philosophical.  Go there right now and listen.  You will be a better person for it.  (I promised Jeff that I would say that on my blog.)

Here is the code from the Spring, 2008 Code Camp.  I am a little late on this.  I fell into a "rabbit hole" while cleaning up the code to be posted here.  At my age, I should know better: just ship it, you can "improve" it later.

You can find the zip file containing the VS2008 solution as an attachment to this post.

There are a couple of items of note:

• There is ORMDataBase project in the solution.  This contains two scripts that are needed to build and populate the sample database for the enclosed application.
• The BuildOrmDataSchema.sql script creates the database and the tables and triggers in that database.  Run this one time.
• The PopulateORMData.sql script populates the database.  This script can be run many times.  It will clean out the existing data and re-populate.  This is useful for testing.

I am working on a blog post that provides some more detailed notes.  I need to clean up the "rabbit droppings" before I do that.  Apparently, I am limited to one file per post.  The zipped PowerPoint 2003 deck will be attached to the next post.

The final technique for extending machine-generated code is to use events. Events are essentially a special form of delegates. When an event is raised, the event process invokes any delegates that have been attached to the event. Each of these delegates must have a specific method signature. At the appropriate points in the life-cycle, the machine-generated code invokes an extension event. Because event-handler delegates cannot return values to the code that invoke them, the machine-generated code has to pass reference type data structures to the event such that the event handler can optionally manipulate the contents of the data structure. This is similar to the technique that I described for partial methods in an earlier post.

There are only a few points to make about this technique.

First, you will have to have a provisioning method in a human-generated class that the machine-generated code calls to "provision" the newly-created instance of the class. In this case, the provision method adds event handlers to the class to catch the events that are of interest to the extension. If there are no extensions needed, the class instance is returned without any added event handlers. As before, each class in the machine-generated code should be setup with a private constructor to prevent someone creating an instance of the class without going through the shared factory method for the class. As a minimum, there would be a single shared factory method that invoked a standard method on a human-generated class, passing in the newly created object for provisioning. To support unit testing, you might add a second factory method that allows the caller to pass in a different factory object. I explained how this would work in my last post.

Second, you could place the event handlers in the same class by using partial class definitions. That way, the event handler logic has access to everything in the class. I urge caution in using this access, particularly when that access is other than read-only. I have two reasons. First, my experience is that you should define the extension interfaces so that it is possible to use event handlers that are external to the class. For example, the logic to handle the authorization for various operations could be handled in a single place, rather than distributed across multiple classes; typically, only a few people are interested in the details of who can do what to any given class. The machine-generated code can delegate the responsibility to this central class/method. Second, I think that it is more disciplined to avoid "side-door" manipulation of the class. The machine-generated code raises an event passing certain parameters. It is reasonable for the machine-generated code (and the human that may be reading it) to expect that the event will limit its alterations to those parameters. Doing anything else causes surprises and increases down-stream maintenance costs.

Third, the object instance raising the event should be passed as one of the parameters along with any parameters needed to make the event handler aware of the specifics of the operation at hand. This makes it a lot easier to have a single event handler process multiple events that are very similar.

Using event handlers to extend the class instance feels just a little weird to me. I think of events as triggering a chain of actions that do not usually change the initiating class. The event handlers are observers rather than manipulators. But I have written enough Form and Web applications that use events to trigger cascading changes (e.g., a change in one control triggers changes in other controls) that I can get behind this technique without any trouble.

Of all of the techniques that we have considered, the event technique and the partial classes/methods approach require the least amount of infrastructure code in the machine-generated code.

Next time, I want to summarize the techniques and offer some additional commentary on when to use each one of them.

I apparently had the comments feature disabled; this technology stuff is soooo complicated. One of my readers sent this comment in by email. I think that it is a very useful comment and I wanted to add this comment to the mix, along with my response:

Great post. You mentioned that "the very first time that I chose to build an object that implements that interface, I would instantly become responsible for implementing something for every method on that interface."

One approach that I have found useful in avoiding this is to create an interface and then immediately build an abstract class that implements the interface. This class, in essence, serves as the blueprint for common concrete implementations of the interface. It provides as many (virtual) implementations as possible. Everything else is (obviously) left as abstract. This approach allows derived classes to get the maximum functionality "for free" while also leaving the option for customization by overloading the virtual implementations from the base. Everything in the domain is then coded against the interfaces, which are created via factory methods. For websites, I usually specify which concrete version to use via a site configuration entry.

My response via email was: Yes, I have used the virtual base class approach myself a couple of
times, particularly when doing unit testing.

Let me add something additional here. The ideal circumstance is that we are able to create one or more extension interfaces that support "dummy" implementations that can do it all without the need to force the use of a child class. If we stick to methods that have reference-type data structures passed in a parameters (as we did in the post on partial classes) and that optionally manipulate that passed-in structure, we should be able to build a base implementation that only requires a derived child implementation when we actually want to extend a class. If we cannot do this, we need to go back and think through the interface design so that we can achieve this goal.