Friday, September 18, 2009

Developing Temporal/Time-Based Database Solutions

Since I've already gotten a couple of requests for this information, I guess it's time for another blog post.

The following is based on my research on the subject, after having developed a temporal/append-only solution a couple of years ago (before knowing what it was called by academics). Considering that Professor Richard Snodgrass of the University of Arizona is THE person that I've come across that has written the most on the subject, you should check out his list of publications on the subject here first:

http://www.cs.arizona.edu/people/rts/publications.html

The key one from what I've read so far is "Developing Time-Oriented Database Applications in SQL". Most, including this one, are available as electronic downloads.

I've also noticed that Joe Celko (http://www.celko.com/books.htm) has written on the topic in his book titled "Joe Celko's Thinking In Sets - Auxiliary, Temporal, and Virtual Tables in SQL".

Now that I've apparently gotten some people's attention on this subject, perhaps it's time for me to start a series of blog posts on the subject...

Monday, August 31, 2009

Temporal Database Design, Soft Deletes, and Ayende's Razor

This is a formal follow-up to a post on Ayende's blog on "Avoiding Soft Delete's" in your database where I question the lack of temporal database solutions being applied to these types of problems.

After Oren claimed the following, I felt it necessary to expand on it in a blog post of my own, rather than continuing to clutter his comments, and hopefully finally bring some traffic to my own blog :-)

Ayende’s Razor

This is a response to a comment on another post:

Oren, in all seriousness, I thought that problems that were "(a) complex, (b) hard to understand (c) hard to optimize" were the kinds that folks like you and I get paid to solve...

Given two solutions the match the requirements of the problem, the simpler one is the better.

I could just as well call that statement "Jim's Razor", as I believe in it as much as you do Oren, so no arguments there.

But in the same vane, "wise" (i.e., experienced) software architects/developers strategically chose RDBMS's over flat text files for the same class of reasons that I believe we should be making temporal database concerns first-class citizens of the modern IT toolbox. The "additional features/functionality" gained by employing temporal databases, while never stated up front in requirements, would be priceless in the long run for business systems. Those features include, but are not limited to:
  • automatic audit logging, since nothing is ever UPDATE'd or DELETE'd, you've got a constant trail of changes
  • automatic support for infinite undo/roll-back support of data, as you simply load a prior version and then save as usual
  • automatic support for labeling of versions, much like in source/version control systems, at an individual record level, table level, "aggregate root level", or database level
  • automatic support for "back querying" a system, in search of what the situation looked like last month, last year, etc. (though raising this "aspect", as in AOP, to the ORM level would be crucial)
IMHO, switch/case statements are generally simpler than polymorphism (demonstrated by how switch/case statements are typically taught before polymorphism in academic settings), but we all know why polymorphism in the long run is the better strategy, and therefore why as soon as we see switch/case statements propagating their way into our code bases, we typically change to a polymorphic strategy.

Again, the goal in my eyes is to raise temporal database concepts to the level of first class citizens in the IT world, as opposed to the back water, academic debates that they are today. The major players like Microsoft with SQL Server have never bothered to implement the temporal extensions to ANSI-94 (yes, from 1994 - see this). Interestingly enough Oracle has implemented some of them via Oracle 9i and 10g, but per the work of Snodgrass and friends, still have room to perfect things.

This is also where the "minor players", i.e., the open source software community via projects such as NHibernate could step in and heavily promote something like this. In the same line of thinking of not wanting to constantly roll your own ORM, thus why you chose something such as NHibernate, needing to roll your own temporal solution for every project should be equally unnecessary.

In closing, for all of the "pain" of implementing something as complex as this topic would be, I'd love to see a platform such as Microsoft Dynamics (MS CRM) implement this all the way from the database through to the GUI, as it would clearly represent a paradigm shift in business information systems development. Of course perhaps I should just start a company to do just this...

Monday, August 3, 2009

MS SQL Server Named Instances and Aliases For Heterogeneous Developer Environments

On the team that I'm working with, we're supporting MS SQL Server 2000, 2005, and 2008. Depending on when the particular developer joined the team, and thus when they installed the various pieces of software on their development workstation, any of the above listed versions might be the default instance (i.e., "(local)"), while the others might be installed as named instances (i.e., "(local)\SQL2008" or "(local)\SQL2K5").

Every once in a while, a developer has to work on a project with a database installed to a local database server that is on a named instance other than the rest of the development team. With the database server name stored in .config files, altering this for each developer just doesn't make much sense. Thankfully, MS SQL Server has a very simple and straightforward solution for this - aliases.

By using aliases, an application under development can be configured to use an alias in the .config file, and each developer simply needs to create a alias on their workstation pointing to their particular named instance.

Anyway, you can either decipher what MSFT KB Article 265808 has to say, or just follow the step-by-step instructions here (for MS SQL Server 2005):

#1 - Open SQL Server Configuration Manager

#2 - Confirm that TCP/IP is enabled for the named instance for which you are creating the alias.


#3 - Confirm that "Listen All" is set to "Yes" for TCP/IP for that instance.


#4 - Take note of the port number listed next to the "TCP Dynamic Ports" setting under the "IPAll" section.


#5 - Right-click the "Aliases" node and select "New Alias..."


#6 - Fill in the details for the new alias.
  • Alias Name - I like the idea of choosing a name for the alias that a) isn't already a name on the network (obviously) and b) is named for the application that we are developing. It is irrelevant that you have 2, 5, 10, or 500 aliases all pointing to the same database server (web hosting services have done this for years when hosting hundreds of websites on a single server).
  • Port No. - This is the value from "TCP Dynamic Ports" in step #4 above.
  • Protocol - Confirm TCP/IP is selected.
  • Server - This is the database server name you would "normally" use to connect to the particular database server. Note that the SQL Server "(local)" alias works as part of this solution, and in conjunction with or without a named instance name, is what should go in this box.


NOTE #1 - Aliases are "local" to the specific workstation they are created on. In other words, every developer on the team will need to create the alias on their individual computer.

NOTE #2 - Yes, the dev team could standardize the way software is installed on their computers. But on my team, we get paid to do what our client wants us to do, and not to arbitrarily streamline" and tweak every last detail of our development environment. Besides, using aliases just works, and unless someone can comment on why this is technically a bad idea, I'm not going to ever condone wasting my client's money.

Monday, June 29, 2009

Hotmail.com and Live.com email access to your iPhone

So being the proud new owner of an iPhone 3GS after years of dealing with the inferior Windows Mobile and Palm platforms, I'm also learning the ins and outs of "things that should be easy".

Take for instance the fact that MSFT only offers crippled POP3 access to Hotmail/Live.com, thus making those nearly worthless on the iPhone. Thankfully the fine folks at FluentFactory make just the thing to make Hotmail/Live.com mail on the iPhone nearly what it should be (for those of us with a many, many year history with our Hotmail accounts).

http://fluentfactory.com/mboxmail/

But that said, looking at what Google has to offer for synching to the iPhone, I can't help myself from laughing!

http://www.google.com/mobile/apple/sync.html

The following paragraph copied from that page is the key:

Important! Google Sync uses the Microsoft© Exchange ActiveSync© protocol. When setting up a new Exchange ActiveSync account on your iPhone, all existing Contacts and Calendar events will be removed from your phone. Please make sure to back up any important data before you set up Google Sync.

Pretty sweet when Google licenses ActiveSync from MSFT for connecting GMail for use on the iPhone, while MSFT refuses to offer the same functionality for Hotmail/LiveMail users, even the paid ones like me! WTF MSFT?!?!?!