April 20, 2006

X-Men Code 127

One of the more interesting things about SQL Server 2005 is the support for the XML datatype. Instead of playing around with wierd and funky stored procedures to manipulate XML, you get validation and a bunch of other features just by specifying a field or a parameter as XML.
Now, as nice as it is to be able to serialize a .net object and store the entire thing in one field in the database, I have my feelings that there is significant overhead to querying into that XML on a more than ad-hoc basis. So, you *might* want to add some additional fields to the table containing the xml. For example if you had the following XML:

<OrderLine ItemId="123" Quantity="2" Taxable="false" Price="12.34"
<OrderLine ItemId="234" Quantity="2" Taxable="false" Price="56.78"

You *might* want to have the top-level elements like "DateChanged" "CustomerNumber" and "OrderID" in redundant fields. Your data table might look like this:

OrderID UniqueIdentifier
DateChanged DateTime
CustomerNumber Int
OrderXML xml

If you did this, you could create an Insert/Update trigger on the table that would automatically update the (non-key) fields (Thanks Scott!) that used the built-in XML features to update the duplicate fields whenever the xml (or any other field) was updated.

ON [dbo].[OrderXML]
UPDATE [MWI].[dbo].[OrderXML]
[CustomerNumber] = INSERTED.XML.value('(//CustomerNumber)[1]','Int'),
[DateChanged] = INSERTED.XML.value('xs:dateTime((//DateChanged)1])','DateTime'),
[Status] = INSERTED.XML.value('(//Status)[1]','nVarchar(50)')
WHERE [OrderXML].OrderId = INSERTED.OrderId

Anyway, the whole thing is interesting, but pay close attention to the line that moves the date field into the DateTime column of the database table.

[DateChanged] = INSERTED.XML.value('xs:dateTime((//DateChanged)1])','DateTime')

The key to importing a .NET DateTime field into SQL server is to make sure to use the XML to cast the field as dateTime before SQL server gets it. Of course, this may not be necessary if you have a schema associated with the field that is already strongly typed in the XML.

April 6, 2006

What!?? No clever title?!??

No clever title for this one. Just a reminder that you can turn on tracing in an asp.net web page by including the following in your page directive:
<%@ page trace="true" %>

And, lest you tire of remembering which specific pages you have this enabled on, you can turn on tracing for an entire application by including the following in the web.config:
<trace enabled="true" localonly="true" tracemode="SortByTime" pageoutput="true" requestlimit="10"><trace enabled="true">

April 5, 2006

Apple Core...Baltimore...Who's your friend?

I've always had a soft spot in my heart for Apple. While it wasn't my "first" computer, (that was a Commodore Pet), The Apple II was the computer where I learned about data structures, graphics, and really did some useful programming. In college when I needed to get a computer, I bought a Macintosh IIsi. While my computer programming career has been mostly Windows-centric, I still like the elegance and simplicity of products from Apple; especially when they are free.

After playing with Music Match and other music programs, I've pretty much decided to use iTunes in my home as a music library management and MP3 player. I like it's playlist management features, the fact that it is easy to rip and burn physical CDs and the user interface is simple and elegant. The visualizations are cool too. I don't buy much music through the iTunes store (only one to date), but I do buy music on CD and want to be able to listen to it without physically handling the CDs; or more precisely, I don't want my children handling my CDs.

Enter the problem. ITunes is great as a personal mp3 player for a single person. It can automatically organize your music and it sounds good. If I had an iPod, I'm sure that it would be really nice to be able to listen to the music there too. However, the one person - one computer paradigm that it is built around is really limiting. I'm not talking about circumventing DRM here, just being able to have everyone in my household listen to music while protecting my investment in CDs.

Take, for example, my scenario. I am married with seven children. We have four computers in our home. I have a NT domain and each person can log into any of the computers. That means that if I install iTunes on each computer that there are potentially 36 copies of the music around my network! Ok, that is a worst case, but you can begin to see the issues.

Now, iTunes has music sharing capabilities. Using some really cool network technology, you can make your music available over the network to other people in your local subnet. Great, but the person sharing the music must be logged in and running iTunes for that to work. That is no help to me. If my wife has some music that I want to listen to, she has to be logged in. Worse than that, apple has an artificial limit of five users connected to your music in a 24 hour period. Apparently a family should only have five people in it, each with their own personal computer.

What about using my network to have a common music share? Well, here we begin to make some real progress. If I have a common directory for the music folder, then there is only one copy of the music on the network, but because iTunes keeps its database and configuration file in a user directory that is separate from the music directory, each user has to re-import all the music every time anyone imports a new CD. Still not good.

An ideal solution would be to have iTunes use the registry to point to the configuration file and database file (that could be shared) and then have the music stored on a file share from the server. But that is not what they did. Each user's music database and configuration file live in a hard-coded "iTunes" folder that exists in the users "my Music" directory. You can change the name of the "my Music" directory in the registry, but it must always be in the "my Documents" folder which is unique for each user on each machine. If anyone from apple reads this, they might take this into consideration.

Ok, Solution time.

The first part of the solution is something that I already had in place. That is to create a user policy that mapps everyone's "my Documents" folder to a file share (mine is the p: drive). You can accomplish this using a login.bat file with the following line in it:
net use p: \\server\%USERNAME% /PERSIST:NO

Next, in the policy, remap each users "my Documents" directory to their p: drive.

I'm not going to go into the details of how to create user policies here. There are lots of resources available that cover this.

If I stopped at this point, I would have achieved one really cool feature. All of the documents for every person in my family would follow them around from computer to computer. So Daniel could work on his essay on any computer in the house.

Windows does this by using "offline folders" to synchronize the "my Documents" folder and its contents from one machine to another at logon, on demand, and at logoff. One downside is that there are copies of the files on every computer that the person uses. Most people don't have that many working files, however, so this isn't too big a deal, most of the time; as we shall see later.

At this point, we still have the iTunes problem, however. That is that everyone has their own music, configuration, and database. The music issue can go away if we put all the music on a network file share. This is very important because without this, there will be a copy of each song on every computer. Now I know that hard drive space is cheap, but I have about 5 gb of mp3s, multiply that times 4 computers and 9 people and you'll agree that is a huge waste of space.

Once you have a network share for media, you can configure iTunes to point to that directory.

Final problem. Because iTunes doesn't automatically scan it's music folder for changes, you have to manually "refresh" each person's music database when anyone imports a new CD. This is a pain, because it isn't the most intuitive process. You have to re-import the directory. Well, my 9 year old likes to listen to music, but she really isn't going to run the music import function.

What would be really good is to be able to share the iTunes folder...

Enter the Symbolic Link. Microsoft has provided a feature called a symbolic link. A symbolic link allows you to make one directory available at multiple locations in your directory tree. Unix afficionatos will recognize this feature. Unfortunately, there is no built-in way to actually create these links. Do not confuse these with "Shortcuts". Lets say that I have folders "C:\iTunes", "C:\Aaron", and "C:\Daniel". If I create a symbolic link of the "C:\iTunes" directory under both the Aaron and Daniel folders, I will have paths "C:\iTunes", "C:\Aaron\iTunes", and "C:\Daniel\iTunes" that all have the same space on my disk. Or in other words, if I were to put a coniguration file in any of those directories, they would all be the same file, albeit with three different ways to access that one file.

That is exactly what I did. On my server, I created a symbolic link for the iTunes directory (with the config and database files) in each of the users "my Music" folders. That configuration file says that the "iTunes Music" folder is located in my M: drive. You just have to give all users full access to the directory.

Now, whenever any user adds music, all the users configuration files are updated and there is only one copy of the music.