Linked Server and Synonym

Last Friday, I needed to compare the content of both the DEV and QA databases as a preparation for a QA deployment. My machine just got a fresh image so I didn’t have the database tools that could have made the task a breeze. I had to finish ahead of the deployment so instead of rummaging for the installer of the tools, I decided to improvise. Good thing, they were just few reference tables which usually have few columns.

In order to accomplish my task, I should be able to connect to the QA database in a remote machine and query the equivalent tables together local ones in a single query. But SQL Server has no innate ability to query database from other machine. You connect to that machine using the feature called Linked Server. It’s not going to be a smooth procedure though per my experience but the solution is not too complex to stymie anyone. Once connected, you can query the pertinent tables and here, another feature called Synonym, first introduced in SQL Server 2005, can offer a little convenience.

So the first step is to create a linked server. One way to accomplish this is by right clicking the Linked Servers under the Server Objects node and selecting “Add New Linked Server…”. In my case, I was connecting only to a SQL Server instance so in the General Page all I needed to specify was the name of the instance. Be aware that this is not an arbitrary value when connecting to a SQL Server instance. It has to be the name of the instance.

Next is to configure the security aspect. The objective here is to specify a local login which will be mapped to another login in the remote server so that whenever you use the linked server, you actually use a proxy login in the remote server with the correct privileges. If both servers are located in the same machine, you can simply add any login with correct privileges and check “Impersonate” as shown below. This is a good practice since you’re controlling who gets access to the remote server.

Applying the setting above in a remote server in an Active Directory however, produces this vague error message:

I have no idea where the heck this “NT AUTHORITY\ANONYMOUS” account comes from so I googled the last sentence which, as you can see, is pretty much generic. Not surprisingly, the result was a myriad of situations on almost anything that involves NT authentication. Among them, this one, in my opinion gives the closest semblance to my predicament. Although the KB is applicable only to SQL Server 7.0, I still tried the solution. Mapping the local login didn’t work either so I ultimately settled for SQL Server-authenticated account to establish the security context. I skip the mapping list altogether so the configuration dialog should then contain only values similar to the ones below:

After establishing the linked server, one can proceed to cross database query like this:

If you perform considerable number of queries on the remote server, you can leverage on Synonym which can spare you some keystrokes and make the queries a tad shorter. This is because with Synonym, you can create alias on certain objects of the remote database. You create a synonym in the “Synonym” node of your database. With my linked server, a synonym configuration would look like this:

The only arbitrary field is the name. Everything depends on the properties of the local and remote database. You can also create synonyms for other objects like function, stored procedures and view. Note that the account used by the linked server should have the needed privileges to the concerned object in order to succeed with the creation. With that taken cared of, the previous query then becomes:

Like any other database objects, Synonym is “securable” meaning you can control who gets to do what on it. This can be done in the Permission page of the Create Synonym dialog as shown below. First I thought this can be used to grant access for logins other than the one used in the linked server but based on my investigation, it looks like it’s dependent on the linked server logins. This means you cannot grant access on a synonym to a login unless it is also capable of connecting to the linked server. For safety reasons, I would have loved it if I could let a developer access a synonym but not the linked server.

Obviously this not the most efficient way of comparing data between two identical databases but sometimes when you do things manually, you would be compelled to explore some hidden features in a technology. Who knows, those features might come handy for tasks where tools for achieving them are not there yet.

My First Battle

A few months back, I got a notice from Yahoo! that it’s was about to pull the plug on Briefcase, its free online storage service and that I needed to transfer all the files. There, I found a zip file of the very first industrial-strength project I developed almost 9 years ago together with some friends. When I got a copy of Visual Studio 6 from a friend, I decided to look into the codes again and what I discovered are traces of struggle, perseverance, and naiveness that shaped the coder that I am today. Every project is a battle. This is the first one and I had only the slightest idea of what I got myself into.

The project is a VB6 client-server application for an apparel company. It was used to compute wage for factory workers based on the work items (known as job) they’ve done for the week. It was originally a college project but the primary stakeholders, realizing the huge return of investment from automated process, tasked them to continue working on it for a price. Well, we didn’t really know the price at that point but the idea of making money out of the craft you’re trying to learn from school was just too enticing. I joined the team when it was realized that MS Access was no longer up to the job. In my case, my enthusiasm was further bolstered by the fact that I was still beginning to learn SQL Server.

The project became an eye-opener to the real nature of the craft I chose. I didn’t graduate with a computer degree but I decided to pursue programming thru self-thought because it’s the only way I can satisfy the “control freak” in me. I thought it would be fun, after all it’s about doing what you really love, right? Well, nothing could be further from the truth. It turned out to be a nightmare; capable of turning anyone into a zombie due to several sleepless nights and skipped meals. The same is the nature of software development as I experience nowadays but the big differences is that we were still naive then. We lacked the experience and knowledge about good software development practices. Exacerbating the situation were the comparatively primitive technologies we’re using which always compelled us to make compromises in order to achieve the best solutions. This post, and the ones hereafter, will explore the mistakes I made and how they influenced the subsequent projects I had. I will also discuss the by which they should be implemented using the most current technologies.

The Model and the Names

Virtually every business application deals with data so it just makes perfect sense if I start with the data model. I can say that I designed about 90% of the data model. Another member took care of the remaining 10% for an auxiliary applications which he solely developed. The first thing you would probably notice is the application of Hungarian Notation and underscores. I learned this style and the naming convention, which you will see momentarily, from the book I was reading at that time. As a beginner, you tend to be dogmatic and don’t even dare to ponder if what you’re imitating is actually right. I later found out that this style is not just hard to read but totally unnecessary.

There were more tables than what were actually needed. This is because I maintained history on some data which I thought would be valuable for auditing and report generation. This is actually a big mistake. Queries involving temporal data are not straightforward because you’re always dealing with composite unique keys involving dates. Besides, the stakeholder did not ask for it - I just assumed it. The adjustments we made just to cater for temporal data, significantly delayed the delivery of the project. I was guilty of that.

“As a beginner, you tend to be dogmatic and don’t even dare to ponder if what you’re imitating is actually right.”

The model included extraneous tables.

I did not use the IDE to create my database objects. Instead, I painstakingly wrote the script for the entire database! For a beginner, it’s a great way to learning SQL but it’s a bad practice simply because it takes so much time. Companies don’t hire you because you can write an entire script of a database. They hire those who can achieve things within the shortest period of time so that means being adept with tools.

Sample script for a table, complete with constraints.

“The adjustments we made just to cater for temporal data, significantly delayed the delivery of the project. I was guilty of that.”

Later in the development, I’ve had enough of Hungarian Notation and this couldn’t be more evident than the contact number column. From a book I referenced, contact numbers were stored as integer and I just blindly followed that. But having it so requires parsing and formatting in the UI. Worse, changing its data type also means making sure the procedure or views that used it were modified because the column name has changed. Coupling is the biggest headache from Hungarian Notation especially that time when there was still no refactoring tools. Another lesson I learned from this is that a column, even though they can be stored as numbers, should never be numeric unless it's being used in some kind of computation.

 

Some columns did not use Hungarian Notation anymore

What’s next

this point, it’s should be clear that I just scratched the surface of one laborious undertaking. In the next installment of this series, I’ll discuss the complexities involved in dealing with time-aware data.

Spring Season

Codes can be downloaded here

Yesterday, I got the chance to tinker with another wiring framework, the grand daddy of them all – Spring.NET - which is actually the first wiring framework I came to know about. This is already my third post with regard to wiring framework so you can expect some references to the castle and unity posts along the way. Like the last two, I’d like to share the opinions from the point of view of someone who just wants a quick wiring solution.

First, you need to download the framework here. Spring is an application framework encompassing other concerns which include but not limited to data access, logging, and testing. The documentation is pretty impressive, complete with samples. It even explains the concepts that drives the creation of framework. Best of all, it has a PDF format which I prefer because of the bookmark feature.

Since we only want wiring framework, the Spring.Core dll would suffice. This is located in the <spring framework installation folder>\bin\net\2.0\release. Make a reference to it together with the abstraction and the concrete assemblies.

Like any other frameworks that utilize the .NET configuration feature, Spring requires configuration entries. This part is well documented so nube shouldn’t have any problem. As you can see in the illustration below, Spring uses the term “context” for what we usually refer as “container” This may be because the context object has more functions other than resolving objects but I haven’t explored this yet. Among the 3, I find Spring entries to be the most verbose. First, you need a separate handler for the object (type in Unity, component in Castle). Then, the context should have a reference to the section which contains the objects it can resolve. In Unity, the objects are simply nested within the container and Castle is similar but doesn’t give you the option to specify which container the objects belong. One thing I like though is the absence of the interface declaration in the objects. In the declaration for the object “searcher” we don’t have to specify ISearcher.  Doing so would have been redundant since you will specify it in the codes anywise.

Nothing new with the code part. You instantiate the context, which in our case is the default. The resolution however, could have benefitted from generics. Among the 3 framework, this is the only one that uses casting.

Voila!

I’m sure there are still so many features in Spring that need to be explored. One feature which piques my curiosity is its tight integration with NHibernate. For the time being, I think I should be good with Castle.

A Roadblock to Unity

Codes can be downloaded here

In my last post, I showed a simple dependency injection using Castle Windsor. This time, I tinkered Microsoft Unity Framework and tried to find out how it faired against the former. There are nomenclature differences but the structure and code are fairly similar. It wasn’t a smooth coding though for I encountered a roadblock. The problem is on retrieving of the configuration section for Unity:

The message is misleading because the files have always been there and the solution is actually to install the assembly Microsoft.Practices.Unity.Configuration in the GAC. There is a discussion about this problem here. Unfortunately, I wasn’t able to make the other suggested solutions work. After the GAC install, the app configuration became

Take note that I needed to embed the version and the public key token which can be found when browsing the C:\Windows\Assembly folder

I don’t like these because I’m not sure if these values are portable. These just add headaches during deployment.

As I mentioned earlier, the difference are mostly confined to nomenclature. One thing to take note though is the attribute “type” in the node “type” which equivalent in Windsor is “mapTo”, not “type”.

Being a MS implementation, it’s no surprise that Unity uses BCL classes to do its chores. For example, in the initialization of the container, you use the System.Configuration.ConfigurationManager instead of some customized XML interpreter. This is a great welcome to programmers because it significantly lower the learning curve.

At this point, I still prefer Windsor over Unity. I’ve perused the documentation and found Unity to be a little verbose. I might be wrong on that but the roadblock I just experienced would probably justify my stand.

Welcome to the Castle

Codes can be downloaded here.

The proven viability of Aspect Oriented Programming (AOP) as a programming paradigm has led to the porting of popular Inversion of Control frameworks to multiple platforms. One of these frameworks is the Castle Windsor which has its roots in open source and Java communities. One of the tenets of AOP is to promote loose coupling among the different components of your application so that anytime during application’s lifetime, the implementations of the functionalities can be changed without affecting the codes. Changes would involve only adding of entries in a configuration file. In this entry, I will show the most common and the fastest way of initializing Castle Windsor. Figure 1 illustrates the different components of a typical application adhering to AOP. The context, which is the final application, uses only abstraction objects in the form of interface. The implementations of the abstraction, known as concretes, are in separate components and are developed independently. The role of Castle Windsor is to “inject” the desired concrete during runtime. In so doing, the context will also create an association to the concrete but since this activity is dynamic, the diagram does not show it. Which concrete to inject is determined from an entry in the configuration file.

Fig. 1 - Component diagram of the sample solution

The code below shows a very simplistic interface which will be abstracting the application from the concrete implementations.

Fig. 2 - The abstraction interface

The implementation is likewise simplistic. All we need is an indication of what assembly is being injected.

Fig. 3 - Sample implementation of the abstraction

In order for Castle Windsor to inject a particular concrete assembly, that assembly has to be referenced by the application together with its dependency stack. Figure 4 shows all the concretes being referenced but this is not always the case. You just need to reference what you intend to inject.

Fig. 4 - Required assemblies in the final application

Castle Windsor needs a customized section in the configuration file and the list of concretes you want to inject, each identified by a string ID. Each item in the list is defined by the <component> node. Besides the ID, the node also specifies the full type name and the assembly of the abstraction and the concrete. The code will retrieve these data during runtime using the ID as you’ll see in the next section.

 

Fig. 5 - Configuration entries for Castle Windsor

The first thing to do in the application codes is to initialize the IoC container. Figure 6 shows instantiation of the container using an overload that reads the “castle” section in the configuration file. The container is responsible for resolving the correct type based on the entry in the configuration which is illustrated in the next line.

Fig. 6 – Code usage

Since we’re specifying ExtensiveSearcher as our searcher, we get the result in Figure 6. Swapping the entries would affect the display accordingly.

Fig. 7 – Output after injecting ExtensiveSearcher assembly

OUTER JOIN in LINQ

I’m quite disappointed to find out that LINQ has an OrderByDescending() and the “VB-ish” ThenBy() but seems to forget a dedicated extension method for OUTER JOIN. Although not as straightforward as I wanted it to be, SelectMany offers a working solution.

 

For illustration, let’s consider the very naive model below. A left join from Child to Parent should yield 2 rows, with the second row having null values on the Parent columns.

 

Dragging the tables to your LINQ-to-SQL canvass produces the model below. From this we can issue our statements.

The solution uses “statement” syntax, not the SQL-like syntax you always see when someone touts about LINQ features. To help with the breakdown of the LINQ statement structure, I included the SQL syntax and highlight the equivalent parts.

Part labeled 1 is the projection statement with the LINQ version utilizing anonymous type. Notice that type inference compels me to convert the ParentId to nullable type. Not doing so would have led the compiler to use Int32 as the type for ParentId. If this happens, an exception would occur for the second row because the value assigned to that property would be null.

Part labeled 2 qualifies the join. The object from which SelectMany is called returns all the rows. This is exactly what we wanted with the Child table. Also notice that we made sure that the qualifier columns from both table have the same type. In order to achieve this, the Value property of the Nullable<Int32> is called from Child.ParentId. You can also achieve the same by converting the other side into nullable as shown here:

You also need to call the DefaultIsEmpty() on the right table, in this case the Parent table. If you don’t call this method, the compiler would just ignore those rows with null values in the Parent table, effectively reverting to an INNER JOIN. Weird isn’t it?

Running the small snippet gives exactly what we wanted.

Taking Care of Time Zones

When creating an application accessible anywhere around the globe, special attention must be given to data involving date and time. A mere DateTime data type is not enough and sometimes, could be a dangerous proposition. The system should also be aware of the time zone. Fortunately, this is very easy today that SQL Server 2008 has already a data type specifically for this requirement – the DateTimeOffset.

Let’s consider a web application that allows viewing of historical data with regard to the status of a task. A designer who tends to skimp on date and time data analysis would come up with the “wrong way” model similar to the left part of this illustration:

Query for historical data usually involves passing the date time when the query is requested and then qualifying this date time BETWEEN the start date and end date columns. To signify that a record is the latest version, the end date is set to null or unrealistic high value like ‘12/30/9999’. This guarantees a match if the current date is passed. Applying this technique in our query for the wrong model, the following scenario would fail:

A user from Manila, Philippines updates the status of Task 1 on 06/26/2009 0900 local time. Two hours later, a user from Tampa, FL tries to view the latest status updates.

Here is the content of our database after the update in Manila

The scenario fails because in Florida, it’s still 06/25/2009 11:00PM and as far as the system is concern, that is earlier than the update date:

The DateTimeOffset data type of SQL Server 2008 makes it easy to avoids this pitfall. Prior to SQL Server 2008, the solutions would usually involve saving the hour offset in the database and use this to manipulate the timezone-agnostic date time in the UI. We applied this new data type in the right side of the model - the “Right Way”. For this model, I opted for LINQ using LinqPad. I find LinqPad to be a very effective prototyping tool and maybe you should give it a try. So back to our discussion, the codes below first simulate the transaction from different time zones. After that, we use the generated timezone-aware dates for the query. As you can see, our parameter qualifies for the condition, as expected, even though the date parts are very far apart. Because of the offset value in the dates, SQL Server correctly computes and determines that Tampa time is actually just 2-hour later.

The DateTimeOffset is really a very valuable addition to SQL Server. It addresses a small but significant shortcoming SQL Server in an era of outsourcing, e-commerce and cloud-computing. I myself is already wary on using DateTime because I just don’t know when a pathetic application of mine would go global. Oh yeah! ;)