Wednesday, December 12, 2007

Deploying Linked Lists in MOSS 2007

This post contains a process for re-linking lists you've already deployed to SharePoint, which to your chagrin may have un-linked themselves from their previous state in your development environment. You can skip the pre-ramble and read the detailed version I've brewed up or the terse version.

One of the insanely stupid things about developing in SharePoint is how little thought there was given to the process of creating and maintaining discreet development, testing, and deployment environments. For myself, I like to have a development environment where I am currently changing the code (I use a VM for this these days), and test environment where I can push what I think is good code and let the customer hack away at it, and then the production environment where the code is in a "live" state.

For developing regular web sites in .Net this is fairly trivial. Just copy the files from place to place. SharePoint is a completely different beast. For one, most (if not all) of the files you will be working on are handled by http handlers built into the SharePoint namespace (no code-behind for you!). Second, those files are not tangible entities you can browse around for on the server hard disk. They are "hosted" inside the content database for the site you are working on.

That's the biggest WTF you run into when learning to develop in SharePoint. What I really want to grump on today is the process of deploying custom linked lists or document libraries from a dev/test environment to a production environment.

When you think of a list in SharePoint, you think of something akin to a table in a database with columns of different variable types. That's good from an understanding perspective.

All list data for a site in SharePoint is stored in one physical table in SQL Server, though, with an insane number of columns with generic names (nvarchar1, nvarchar2, ..., nvarchar64 and repeat a few more times with a couple other variable types) to store the list data. Further, in order to figure out which of those generic column names correspond to the field names you created through the SharePoint UI, you have to find the list definition in another table and pull the contents of one of the columns out into a text editor. The contents are in XML format. What an abomination.

Where this abomination starts ruining your day is when you have a handful of lists that are linked to each other via a lookup field you've created through the SharePoint environment. When you get ready to push these lists to another server you typically save the lists as templates (with the content included), download the templates to your local machine, upload the templates to the production machine, and then create new lists based on those templates.

Because of the way SharePoint stores the list data it depends on a system of identifying lists with GUIDs, and when you create a new list in SharePoint based on a template you do not get the same GUID identified with the new list as you had on your dev/test environment. What that ultimately means is that any lookup fields you had on a list are hosed -- they point to nowhere.

Todd Bleeker, who has multiple black belts in SharePoint development and administration and who is well known in the SharePoint community, has a process for dealing with this. It involves cracking open the template prior to uploading to the production server and editing the one of the files contained within. It works, but involves uploading each list one at a time, finding out the generated GUID for that list, cracking open the next template, editing the file inside, reassembling the template, uploading, etc.

I've come up with an alternate way of re-linking your lists together after you've uploaded all of them to your production server. It involves opening SQL Studio or Query Analyzer and editing the column holding the field definition for your just-created lists. This alternate way also assumes that you are deploying your new list/library set to the same site. If not, you'll have to find another way yourself.

A caveat here -- everybody in the SharePoint community will tell you not to ever edit any of the data in a SharePoint content database outside of the SharePoint UI. Actually, I agree with that sentiment but it was 3 in the morning when I came up with this scheme and I liked it better than the Bleeker method. So, do this at your own risk and only if you are comfortable with using Microsoft's SQL Server tools and writing SQL. If you hose your content database, I don't know you but I do hope you have good backups.

So, create your templates, upload them to your production server, and create your list/document libraries from them. Then, crack open SQL Studio or Query Analyzer, open a query window to your content database and follow along.

The Detailed, Getting-Paid-By-The-Hour-To-Type-This Version

  1. You need to find your custom list and the field definitions for it in the content database. To do this run this piece of SQL:

    select
        Webs.Id WebId,
        Webs.SiteId,
        Webs.FullUrl SiteUrl,
        Lists.tp_ID ListId,
        Lists.tp_Title ListName,
        Lists.tp_Fields FieldDefs
    from
        Webs
        left join AllLists Lists on Lists.tp_WebId = Webs.Id
    where
        Webs.FullUrl = '[ your relative site url ]' and
        ((Lists.tp_Title = '[ list name 1 ]' and Lists.tp_DeleteTransactionId = 0x) or
         (Lists.tp_Title = '[ list name 2 ]' and Lists.tp_DeleteTransactionId = 0x) or
         (Lists.tp_Title = '[ list name 3 ]' and Lists.tp_DeleteTransactionId = 0x))

    What this SQL does is find the exact lists/libraries you've just created in the content database. You need some pieces of information here -- the relative url to the site where your list is located, and the name you've given to each of your new lists/libraries. These are put into the where clause.

    The first line of the where clause references the relative url to the site where your lists have been deployed. Your relative URL should look something like "sites/TheSite" if you've create a site collection at /sites, or it could look something like "thesite" if your site collection is at the root level of the site.

    The second, third and fourth lines of the where clause finds your lists/libraries based on the names you gave them. I've included three lines here, but you'll want to include a line for each list/library, whether it's referencing another list/library or is being referenced itself. You'll notice here that the where clause on each of these lines filters using the field "tp_DeleteTransactionId." This is because if you create a list/library in the past with the same name, SharePoint doesn't actually delete it's row from the database (at least, not right away). If you have created a list with the same name in the past but deleted it, this additional filter makes sure you are grabbing the list that has not been deleted.

    This query should return exactly one row for each list/library you've specified. If not, abort and try again.
  2. Next we're going to put the field definition info into something we can easily manipulate. For this you are going to need your favorite text editor. In your query results, find the list which needs to have it's external references fixed, copy the contents of the "FieldDefs" column to the clipboard, and paste it into your text editor. It would not be a bad idea to save a copy of what you pasted into your text editor as a backup somewhere should you need to restore the column content to its original state.

  3. Now we're going to find the lookup fields which need adjusting. What you'll get in your text editor is something that starts off resembling this:

    12.0.0.4518.0.0<FieldRef Name="ContentTypeId"/>
    <FieldRef Name="_ModerationComments" ColName="ntext1"/>
    <FieldRef Name="FileLeafRef"/>
    <FieldRef Name="Modified_x0020_By" ColName="nvarchar1"/>
    <FieldRef Name="Created_x0020_By" ColName="nvarchar2"/>
    <FieldRef Name="File_x0020_Type" ColName="nvarchar3"/>

    [ ... so on and so forth ... ]

    I'm sorry, but you'll have to ignore that first bit of creative Microsoft xml mangling at the very beginning (I think it's a version number they stick in there). What you're looking for in this xml are the definitions for your lookup field(s). You can do this in your text editor by doing a search for the word "lookup." You should find an xml node that looks something like this:

    <Field
       Type="Lookup"
       DisplayName="[ display name ]"
       Required="FALSE"
       List="[ a guid string ]"
       ShowField="ID"
       UnlimitedLengthInDocumentLibrary="FALSE"
       ID="{9f437a3e-5507-41e9-ab68-36beb3bd0822}"
       SourceID="{f3296739-dd99-4fd5-b756-020c359d9fbb}"
       StaticName="[ static name ]"
       Name="[ field name ]"
       ColName="int2" RowOrdinal="0" Group="" Version="4"
       WebId="[ a guid string ]"
    />

    I've replaced some attribute values of this node with placeholders, but you'll see that some of the attributes contain the name you've given to the lookup field. Of particular interest here are two attributes containing GUID values -- "List" and "WebId." These help SharePoint define the list being referenced to by this lookup field.
  4. Next we'll replace the GUID values in the xml with the proper ones from the database Go back to the query you ran and find the list that this lookup value is referencing. I'm assuming you know which list this should be, and that it was included in the where clause of the SQL statement you ran. When you find the list in the query results, copy the contents of the "ListId" column and paste it over the contents of the "List" attribute value in the xml node you are currently editing.

    Once you've done that, repeat this process with the WebId attribute by copying the contents of the WebId column in the query results and pasting it over the contents of the WebId attribute value in the xml node1.

  5. Repeat steps 3 and 4 for the other lookup fields in your list, if there are any more.

  6. Now we're going to update the field definition for this list in the content database. Go back to your query editor and stamp out this piece of SQL in a new window pointing at the same content database:

    update Lists set tp_Fields = '[ FieldDefs ]' where Lists.tp_Id = '[ ListId ]'

    Replace the "[ ListId ]" placeholder with the contents of the ListId column (a GUID value) of the list item you are editing in the original query.

    Go to your text editor and copy the entire xml text to the clipboard, then come back to your query editor and replace the "[ FieldDefs ]" placeholder with the contents of the clipboard. It's going to look real ugly, but don't fret it. Just make sure the contents of the clipboard are between the apostrophes.

    Run this SQL, and you should get 1 row update.

  7. You can now go in through the SharePoint UI and examine the lookup field definition for the list. The "Get Information From:" should read with the correct list name, whereas before it was blank.

  8. Repeat steps 2 - 7 for your other lists containing lookup fields.

This seems pretty complicated, but I was intentionally long-winded so that you'll understand what's going on with each step. The less verbose version of this process is here in the event that you don't want to weed through the explanations.

The Terse, Would-You-Please-Get-To-The-Point-Already Version

  1. Open Query Analyzer or SQL Studio and run this piece of SQL:

    select
        Webs.Id WebId,
        Webs.SiteId,
        Webs.FullUrl SiteUrl,
        Lists.tp_ID ListId,
        Lists.tp_Title ListName,
        Lists.tp_Fields FieldDefs
    from
        Webs
        left join AllLists Lists on Lists.tp_WebId = Webs.Id
    where
        Webs.FullUrl = '[ your relative site url ]' and
        ((Lists.tp_Title = '[ list name 1 ]' and Lists.tp_DeleteTransactionId = 0x) or
         (Lists.tp_Title = '[ list name 2 ]' and Lists.tp_DeleteTransactionId = 0x) or
         (Lists.tp_Title = '[ list name 3 ]' and Lists.tp_DeleteTransactionId = 0x))

    Make sure to a) replace the placeholders as appropriate and b) add/subtract lines from the where clause as appropriate for each created list which is referencing another list or is being referenced.
  2. Find the first list in the query results which contains lookup fields and copy the contents of the FieldDefs field to the clipboard and paste into a text editor.

  3. Find the first lookup field which needs to be changed. Do a search on the word "Lookup" and you should find a node which looks like this:

    <Field
       Type="Lookup"
       DisplayName="[ display name ]"
       Required="FALSE"
       List="[ a guid string ]"
       ShowField="ID"
       UnlimitedLengthInDocumentLibrary="FALSE"
       ID="{9f437a3e-5507-41e9-ab68-36beb3bd0822}"
       SourceID="{f3296739-dd99-4fd5-b756-020c359d9fbb}"
       StaticName="[ static name ]"
       Name="[ field name ]"
       ColName="int2" RowOrdinal="0" Group="" Version="4"
       WebId="[ a guid string ]"
    />

  4. Go back to the query and find the list that is being referenced in this xml node. Copy the contents of the ListId column (should be a GUID value) and paste it over the existing value in the "List" attribute.

    Repeat this process using the "WebId" column value from the query and pasting it over the "WebId" attribute value in the xml node.

  5. Repeat steps 3 and 4 for the other lookup values contained in the xml.

  6. Go back to your query editor and open a new window to the same content database:

    update Lists set tp_Fields = '[ FieldDefs ]' where Lists.tp_Id = '[ ListId ]'

    Replace the "[ ListId ]" placeholder with the contents of the ListId column (a GUID value) of the list item you are editing in the original query. Replace the "[ FieldDefs ]" placeholder with the xml from your text editor. Run the SQL and you should get 1 row updated.

  7. Examine the just-editing field through the SharePoint UI. The "Get Information From" should now read with the correct list reference rather than being blank.

  8. Repeat steps 2 - 7 for the other lists containing lookup fields.


Updates, Post-Scripts and What-Not

  1. I'm finding that there are times when the WebId attribute is missing in the XML. Skipping this seems to still glue the lookup field properly as long as you paste the correct ListId in.

1 comment:

Anonymous said...

This is great information.

Just wondering why are we updating the table "Lists" but not "AllLists"?